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
#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);
select [distinct] *| listing, listing From table Where condition
1.2. Simple query
#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;
|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);|
#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.
Select * from table name order by sort field asc|desc# ASC ascending (default) #desc descending
#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;
#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.
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.
#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)
The provided sqlyog software is an installation free version and can be used directly
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
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
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
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
- 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);
#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
- 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);
#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';