MySql-Day-02

Time:2021-10-19

MySQL query DQL & multi table relation

  • Ability to query data using SQL statements
  • Ability to use SQL statements for conditional queries
  • Ability to sort using SQL statements
  • Ability to use aggregate functions
  • Ability to use SQL statements for grouping queries
  • Be able to complete data backup and recovery
  • Be able to use visualization tools to connect and operate the database
  • Be able to tell the relationship between multiple tables and their table building principles
  • Ability to understand foreign key constraints

1、 SQL statement (DQL)

1.1 DQL preparation and syntax

preparation

#Create item table:
create table product( 
  pid int primary key,
  pname varchar(20),
  price double,
  category_id varchar(32)
);
Insert into product (PID, pname, price, category_id) values (1, 'Lenovo', 5000, 'C001');
Insert into product (PID, pname, price, category_id) values (2, 'Haier', 3000, 'C001');
Insert into product (PID, pname, price, category_id) values (3, 'Thor', 5000, 'C001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
Insert into product (PID, pname, price, category_id) values (5, 'JeansWest', 200, 'C002');
Insert into product (PID, pname, price, category_id) values (6, 'Playboy', 440, 'C002');
Insert into product (PID, pname, price, category_id) values (7, 'Jinba', 2000, 'C002');
Insert into product (PID, pname, price, category_id) values (8, 'Chanel', 800, 'C003');
Insert into product (PID, pname, price, category_id) values (9, 'suitable materia medica', 200, 'C003');
Insert into product (PID, pname, price, category_id) values (10, 'Mac', 5, 'C003');
Insert into product (PID, pname, price, category_id) values (11, 'miss you so much', 56, 'C004');
Insert into product (PID, pname, price, category_id) values (12, 'fragrant milk tea', 1, 'C005');
Insert into product (PID, pname, price, category_id) values (13, 'fruit 9', 1, null);

grammar

select [distinct] 
	*| listing, listing 
From table 
	Where condition

1.2. Simple query

practice

#Query all products 
select * from product;
#Query the commodity name and price 
select pname,price from product;
#Alias query. The keyword used is as (as can be omitted). Table alias: 
select * from product as p;
#Alias query. The keyword used is as (as can be omitted). Column alias: 
select pname as pn from product;
#Remove duplicate values 
select distinct price from product;
#The query result is an expression (operation query): display the price of all commodities + 10 yuan 
select pname,price+10 from product;

1.3Condition query

Comparison operator < <= = = <> Greater than, less than, greater than (less than) equal to, not equal to
BETWEEN…AND… Value displayed in a certain interval (including head and tail)
IN(set) The value displayed in the in list, for example: in (100200)
Like ‘pattern’ In a fuzzy query, in a like statement,% represents zero or more arbitrary characters_ Represents a character, for example: first_ name like ‘_ a%’;
IS NULL Judge whether it is empty
Logical operator and Multiple conditions hold at the same time
or Any of several conditions holds
not Not established, for example: where not (salary > 100);

practice

#To query all information about a product named "playboy": 
Select * from product where pname = 'Playboy';
#Inquire about goods with a price of 800 

SELECT * FROM product WHERE price = 800;
#Query all items whose price is not 800
SELECT * FROM product WHERE price != 800 SELECT * FROM product WHERE price <> 800 SELECT * FROM product WHERE NOT(price = 800);
#Query all commodity information with commodity price greater than 60 yuan
SELECT * FROM product WHERE price > 60;
#Query all products with commodity prices between 200 and 1000
SELECT * FROM product WHERE price >= 200 AND price <=1000; SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
#Query all items with a price of 200 or 800
SELECT * FROM product WHERE price = 200 OR price = 800; SELECT * FROM product WHERE price IN (200,800);
#Query all products containing the word 'BA'
Select * from product where pname like '%';
#Query all products starting with 'fragrance'
Select * from product where pname like 'fragrance%';
#Query all products with the second word 'want'
SELECT * FROM product WHERE pname LIKE '_ Want to% ';
#Goods not classified
SELECT * FROM product WHERE category_id IS NULL;
#Query goods with classification
SELECT * FROM product WHERE category_id IS NOT NULL;

1.4 Sorting Query

​ Through the order by statement, you can sort the query results. Temporarily placed at the end of the select statement.

  • Format:
Select * from table name order by sort field asc|desc# ASC ascending (default) #desc descending
  • practice:
#Use price sort (descending)
SELECT * FROM product ORDER BY price DESC;
#On the basis of price sorting (descending order), sort by category (descending order)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
#Display the price of goods (de duplication) and sort them (descending order)
SELECT DISTINCT price FROM product ORDER BY price DESC;

1.5. Aggregate query

​ The previous queries are horizontal queries, which are judged according to the conditions line by line, while the query using aggregate function is vertical query, which is
Calculate the value of a column, and then return a single value; In addition, the aggregate function ignores null values.

Today we will learn the following five aggregate functions:

  • Count: counts the number of record rows whose specified column is not null;
  • Sum: calculate the numerical sum of the specified column. If the specified column type is not a numerical type, the calculation result is 0;
  • Max: calculates the maximum value of the specified column. If the specified column is of string type, the string sorting operation is used;
  • Min: calculate the minimum value of the specified column. If the specified column is of string type, the string sorting operation is used;
  • AVG: calculates the average value of the specified column. If the specified column type is not numeric, the calculation result is 0;

practice:

#Total number of items queried
SELECT COUNT(*) FROM product;
#Query the total number of items with a price greater than 200
SELECT COUNT(*) FROM product WHERE price > 200;
#Query the sum of all goods classified as' C001 '
SELECT SUM(price) FROM product WHERE category_id = 'c001';
#Query the average price of all goods classified as' C002 '
SELECT AVG(price) FROM product WHERE category_id = 'c002';
#Query the maximum price and minimum price of goods
SELECT MAX(price),MIN(price) FROM product;

1.6 group query

​ Group query refers to grouping query information with the word group by.

  • Format:
Select field 1, field 2... From table name group by grouping field having grouping condition;

​ The having sub statement in grouping operation is used to filter data after grouping. Its function is similar to where condition.

  • The difference between having and where:

    • Having is filtering data after grouping

      Where is to filter data before grouping

    • Grouping function (statistical function) can be used after having

      Grouping functions cannot be used after where.

practice:

#Count the number of commodities in each category
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
#Count the number of commodities in each category, and only display the information with the number greater than 1
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

2、 Sqlyog (MySQL graphical development tool)

  • Installation:

    The provided sqlyog software is an installation free version and can be used directly

  • use:

    Enter the user name and password, and click the connect button to access the MySQL database for operation

​ In the query window, enter the SQL code, select the SQL code to execute, press F8 to run, or press the execute button to run.

3、 SQL backup and recovery

3.1. SQL backup

Database backup refers to converting the database into the corresponding SQL file

MySQL command backup

​ Format of database export SQL script:

MySQL dump - U user name - P password database name > generated script file path

For example:

mysql dump -uroot -proot day04>d:\day03.sql

​ The user name and password are required in the above command to back up the database, which means that the command should be used without login

Visualizer backup

​ Select the database, right-click backup / export, specify the export path, and save it as an. SQL file.

3.2. SQL recovery

​ Database recovery refers to using the SQL file generated by backup to recover the database, that is, the database content can be recovered by executing the SQL statement in the SQL file.

MySQL command recovery

​ When using the database command to back up, only the database content is backed up. There is no SQL statement to create the database in the generated SQL file. Before restoring the database
You need to create the database yourself before.

  • Restore outside the database

    • Format: MySQL – uroot – P password database name < file path
    • For example: MySQL – uroot – proot day03 < D: \ day03.sql
  • Restore within database

    • Format: source SQL script path
    • For example: source D: \ day03.sql
    • Note: to restore data in this way, first log in to the database

Visualizer recovery

​ In the database list area, right-click import database from SQL dump file to specify the SQL file to execute.

4、 Multi table operation

​ In actual development, a project usually needs many tables to complete. For example, a mall project requires multiple tables such as category, products, and orders. And there is a certain relationship between the data of these tables. Next, we will learn the knowledge of multiple tables on the basis of single table.

4.1, table to table relationships

  • One to many relationship:
    • Common examples: customers and orders, categories and goods, departments and employees.
    • One to many table creation principle: create a field in the slave table (multi-party), and the field is used as a foreign key to point to the primary key of the primary table (one party)

  • Many to many relationship:
    • Common examples: students and courses, users and roles
    • Many to many table building principle: the third table needs to be created. There are at least two fields in the middle table. These two fields are used as foreign keys to point to the primary key of each party

  • One to one relationship: (understand)
    • It is not widely used in practical development because one-to-one can be created into a table
    • Two table building principles:
      • Unique foreign key: the primary key of the master table and the foreign key (unique) of the slave table form the primary foreign key relationship. The foreign key is unique.
      • A foreign key is a primary key: the primary key of the primary table and the primary key of the secondary table form a primary foreign key relationship.

4.2, foreign key constraints

Now we have two tables “classification table” and “commodity table”. In order to indicate which category commodities belong to, we will usually add a column on the commodity table,

It is used to store the information of classified CID. This column is called foreign key

​ At this time, the classification table category is called the primary table, and the CID is called the primary key. The “products table” is called: slave table, category_ IDs are called foreign keys. I

We use the primary key of the master table and the foreign key of the slave table to describe the master foreign key relationship. The presentation is a one to many relationship.

  • Foreign key features:

    • The value of the foreign key of the slave table is a reference to the primary key of the primary table.
    • The foreign key type of the slave table must be consistent with the primary key type of the primary table.
  • Declare foreign key constraints

Syntax: 
Alter table slave table add [constraint] [foreign key name] foreign key references main table 
Primary key of the table); 

[foreign key name] is used to delete foreign key constraints. It is generally recommended to end with "_fk" 
Alter table drop foreign key name from the table;
  • Purpose of using foreign keys:
    • Ensure data integrity

4.3One to many operation

analysis

  • The category classification table is one party, that is, the primary table, and must provide the primary key CID
  • The products product table is a multi-party, that is, a slave table, and must provide a foreign key category_ id

Realization: classification and commodity

#Create classification table 
create table category( 
	cid varchar(32) PRIMARY KEY , 
	CNAME varchar (100) -- Classification name 
);
#Commodity list 
CREATE TABLE `products` ( 
	`pid` varchar(32) PRIMARY KEY , 
	`name` VARCHAR(40) , 
	`price` DOUBLE 
);
#Add foreign key field 
alter table products add column category_id varchar(32); 
#Add constraint 
alter table products add constraint product_fk foreign key (category_id) references 
category (cid);

operation

#1 add data to the classification table 
Insert into category (CID, CNAME) values ('c001 ',' clothing '); 
#2 add common data to the commodity table. There is no foreign key data. It is null by default 
Insert into products (PID, pname) values ('p001 ',' commodity name '); 
#3 add common data to the commodity table, including foreign key information (this data exists in the category table) 
Insert into products (PID, pname, category_id) values ('p002 ',' commodity name 2 ',' C001 '); 
#4. Adding common data to the commodity table containing foreign key information (this data does not exist in the category table) -- failed, exception 
Insert into products (PID, pname, category_id) values ('p003 ',' commodity name 2 ',' c999 '); 
#5 delete specified classification (classification is used by commodity) -- execution exception
DELETE FROM category WHERE cid = 'c001';

4.4 many to many

analysis

  • The many to many relationship between goods and orders will be split into two one to many.
  • The products table is one of the one to many primary tables, and the primary key PID needs to be provided
  • The orders order table is another one to many main table, and the primary key oid needs to be provided
  • The orderitem intermediate table, which is the third additional table, needs to provide two foreign keys, oid and PID

Realization: orders and goods

#Item table [already exists] 
#Order form 
create table `orders`( 
	`oid` varchar(32) PRIMARY KEY , 
	`Totalprice ` double # total 
);
#Order item table 
create table orderitem( 
	Oid varchar (50), -- order ID 
	PID varchar (50) -- commodity ID 
);
#Order form和订单项表的主外键关系 
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references 
orders(oid); 
#Primary foreign key relationship between commodity table and order item table 
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) 
references products(pid); 
#Joint primary key (can be omitted) 
alter table `orderitem` add primary key (oid,pid);

operation

#1 add data to the item table 
Insert into products (PID, pname) values ('p003 ',' commodity name '); 
#2 add data to the order table 
INSERT INTO orders (oid ,totalprice) VALUES('x001','998'); 
INSERT INTO orders (oid ,totalprice) VALUES('x002','100'); 
#3 add data to the intermediate table (data exists) 
INSERT INTO orderitem(pid,oid) VALUES('p001','x001'); 
INSERT INTO orderitem(pid,oid) VALUES('p001','x002'); 
INSERT INTO orderitem(pid,oid) VALUES('p002','x002'); 
#4 delete the data in the intermediate table 
DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002'; 
#5 add data to the intermediate table (data does not exist) -- execution exception 
INSERT INTO orderitem(pid,oid) VALUES('p002','x003'); 
#6 delete the data of the commodity table -- execution exception 
DELETE FROM products WHERE pid = 'p001';

Recommended Today

SQL statement of three-level linkage of provinces, cities and counties

The first is the table creation statement Copy codeThe code is as follows: CREATE TABLE `t_address_province` ( `id` INT AUTO_ Increment primary key comment ‘primary key’,`Code ` char (6) not null comment ‘province code’,`Name ` varchar (40) not null comment ‘province name’)Engine = InnoDB default charset = utf8 comment = ‘province information table’; CREATE TABLE […]