1. MySQL multi-table query
1.1 Foreign Key Constraints
In order to eliminate the Cartesian product in multi-table queries, MySQL can use foreign key Association queries when building tables and performing key queries between multiple tables.
Foreign key: Refer to the value of one column in Table 1 (sub) and another column in Table 2 (main), and call the column in Table 1 the foreign key of the column in table 2.
Setting and Use of 1.2 Foreign Key
For example, the simplest employee and department mentioned above, set the foreign key dept_id to associate with ID.
The steps are as follows:
(1) Setting fk foreign key in multi-table, using foreign key dept_id to associate the primary key ID in one table, and choosing a good reference table;
(2) The storage engine in the modification option setting is InnoDB, which supports setting foreign key operations;
Note: In MySQL, InnoDB supports transactions and foreign keys. MyISAM does not support transactions and foreign keys.
The above operations can also modify the storage engine to InnDB using SQL statements:
ALTER TABLE table name ENGINE='InnoDB';
For example, enter the following statement in the command line interface:
Also use SQL statements to create foreign key associations:
ALTER TABLE employee ADD CONSTRAINT employee_fk (foreign key name) FOREIGN KEY (dept_id) REFERENCES dept(dept_id);
1.3 Classification of Connection Queries
The general classification of join queries can be simply described by a graph, which can be divided into outer join queries (left outer join, right outer join, full join) and inner join queries (inner join, self join).
1.4 Connection Query
The inner join of two tables is shown in the following figure. The join query results in the intersection of the two tables.
The writing of SQL statements can be divided into explicit and implicit writing.
Implicit inner join writing:
select <selectList> From table1,table2 where table1.column = table2.column;
Explicit inner join writing (recommended writing):
select <selectList> From table1 [inner] join table2 on table1.column = table2.column;
Query examples:
Note: All the following queries are exemplified by three tables: product, product_category and product_stock.
Requirements: Query the names and classifications of all commodities:
Implicit Writing:
SELECT p.product_name, pc.category_name FROM product p, product_category pc WHERE p.category_id = pc.id;
Explicit Writing:
SELECT p.product_name, pc.category_name FROM product p INNER JOIN product_category pc ON p.category_id = pc.id; SELECT p.product_name, pc.category_name FROM product p JOIN product_category pc ON p.category_id = pc.id;
(2) Requirements: inquiring the id of the goods, the name of the goods and the classification name of the goods;
Implicit Writing:
SELECT * FROM product p, product_category pc WHERE p.category_id = pc.id AND p.sale_price > 200 AND pc.category_name = 'Wireless mouse';
Explicit Writing:
SELECT * FROM product p JOIN product_category pc ON p.category_id = pc.id AND p.sale_price > 200 AND pc.category_name = 'Wireless mouse';
1.5 External Connection Query
External join queries are divided into left outer join queries and right outer join queries.
Left outer join: Query out all data of JOIN left table and the part of right table satisfying ON condition. Data that does not match JOIN right table fills data rows with NULL.
Right External Connection: Query out all data of JOIN right table and the part of left table satisfying ON condition. Data that does not match JOIN left table fills data rows with NULL.
Grammatical Format:
select <selectList> from table1 left/right [outer] join table2 on table1.column = table2.Column;
Query examples:
Query the name of each commodity, the name of the classification and the total number of specific commodities contained (storeNum)
SELECT p.product_name, pc.category_name, IFNULL(ps.store_num,0) FROM product p LEFT JOIN product_category pc ON p.category_id = pc.id LEFT JOIN product_stock ps ON p.id = ps.product_id;
Note: IFNULL(expr1,expr2) is used to display the value of expr2 if the current expr1 is NULL.
1.6 Self-Connecting Query
In specific query scenarios (commodity classification, region, permission), table design method of self-joined query may be used when data in tables need to be classified or secondary association.
Self-connection mode:
Query examples:
Query the name of each commodity category and the parent category name
#Implicit Writing SELECT pare.category_name, sub.category_name FROM product_category sub, product_category pare WHERE sub.id = pare.parent_id;
# Display Writing SELECT pare.category_name, sub.category_name FROM product_category sub JOIN product_category pare ON sub.id = pare.parent_id;
Query results:
1.7 sub-query
Subquery (nested query): A query statement is nested in another query statement, and the result of the inner query can be used as the outer query condition. (equivalent to querying out a result and then querying the result as a table)
Generally, it is nested in WHERE or FROM sentences.
Subqueries (nested queries) are generally divided into single-row single-column sub-queries and single-row multi-column sub-queries.
Query examples:
(1) Single row single column sub-query
# Single row single column sub-query # Search for all goods with retail prices higher than Logitech MX1100 SELECT * FROM product WHERE sale_price > ( SELECT sale_price FROM product WHERE product_name = 'Logitech MX1100' );
Query results:
(2) Single row multi-column sub-query
# Query all commodity information with the same category number and discount as Logitech M100 SELECT * FROM product WHERE (category_id, cutoff) IN (SELECT category_id, cutoff FROM product WHERE product_name = 'Logitech MX1100'); SELECT * FROM product WHERE (category_id, cutoff) = (SELECT category_id, cutoff FROM product WHERE product_name = 'Logitech MX1100');
Query results:
2.MySQL Data Operation DML Statement
2.1 insert statement
Insert statement: Insert only one line at a time.
Grammar:
insert into table_name (column1,column2,column3...) values (value1, value2, value3...);
Insert multiple rows of data records.
Grammar:
insert into table_name (column1,column2,column3...) values (value1, value2, value3...), (value4, value5, value6...), (value7, value8, value9...);
Example:
# Insert a row
INSERT INTO mytable(id, name, age, sex) VALUES (1, 'Lin', 21, 0);
INSERT INTO mytable(sex, age, name, id) VALUES (1, 18, 'Xiao Bai', 2); #The order can be disrupted as long as the key values inserted correspond to each other.
# Insert multiple lines-- MySQL specific INSERT INTO mytable(id, name, age, sex) VALUES (3, 'test01', 26, 0), (4, 'test02', 27, 1), (5, 'test03', 28, 1);
2.2 Amendment Statement (update)
Modify the grammar:
update table_name set column1 = value1, column2 = value2, column3 = value3... [where condition];
Note: If the where statement is omitted, the data in the whole table is modified.
Modification examples:
# Modify data # Increase the retail price of goods whose retail price is more than 300 by 0.2 times UPDATE product SET sale_price = sale_price * 1.2 WHERE sale_price > 300; # Increase the retail price of wired mouse products whose retail price is more than 300 by 0.1 times UPDATE product p JOIN product_category pc ON p.category_id = pc.id SET sale_price = sale_price * 1.1 WHERE sale_price > 300 AND pc.category_name = 'Wired mouse';
2.3 Delete statement (delete)
Grammar:
delete from table_name [where condition];
Note: If where is omitted, all table data will be deleted.
Example:
# Delete one item DELETE FROM mytable WHERE id = 4; # Delete multiple entries DELETE FROM mytable WHERE id >=3;
3. MySQL Data Backup
There are two ways to backup MySQL data: import/export through the SQL of the Navicat tool and import/export using the command line. Here's how to use the command line.
Grammar:
Export: mysqldump -u account - p password database name > script file storage place
MySql's own database maintenance
Enter dos window through cmd command:
mysqldump -uroot -padmin jdbcdemo> C:/shop_bak.sql
Import: mysql -u account - p password database name < script file storage address
mysql -uroot -padmin jdbcdemo< C:/shop_bak.sql