Master the core knowledge of SQL, go out to brag and never worry about it anymore

Chapter One Introduction to SQL

1.1, what is sql

• SQL: Structure Query Language. (Structured query language), operate the database through sql (operation database, operation table, operation data)
• SQL was determined by the American National Standards Institute (ANSI) as an American standard for relational database languages, and later adopted by the International Standards Organization (ISO) as an international standard for relational database languages
• All database vendors (MySql, oracle, sql server) support ISO's SQL standard.
• Various database vendors have made their own extensions on the basis of the standard. Each database has its own specific syntax

1.2, the classification of sql

• Data Definition Language (DDL data definition language) such as: operating database, operating table
• Data Manipulation Language (DML data manipulation language), such as: adding, deleting and modifying records in the table
• Data Query Language (DQL data query language), such as: query operations on the data in the table
• Data Control Language (DCL data control language), such as: setting user permissions

1.3, MySQL grammar specifications and requirements

(1) The sql grammar of mysql is not case sensitive

MySQL keywords and function names are not case-sensitive, but whether the data values are case-sensitive is related to the character set and collation rules.

ci (case insensitive), cs (case sensitive), _bin (binary, that is, the comparison is based on the value of the character encoding and has nothing to do with language, case sensitive)

(2) When naming: try to use 26 English letters in upper and lower case, numbers 0-9, underscore, do not use other symbols user_id

(3) It is recommended not to use mysql keywords as table names, field names, etc. If you use them accidentally, please use `(floating sign)

(4) Do not include spaces between database and table names, field names, and other object names

(5) In the same mysql software, the database cannot have the same name, the table cannot have the same name in the same database, and the field cannot have the same name in the same table

(6) Punctuation marks:

• Must be in pairs
• Half-width input method must be in English
• Single quotes can be used for string and date types''
• You can use double quotation marks "" for column aliases, and do not use double quotation marks to alias table names. As can be omitted when aliasing
• If the column alias does not contain spaces, you can omit the double quotation marks, and if there are spaces, the double quotation marks cannot be omitted.

(7) How to add comments in SQL scripts

• Single-line comment: #Comment content
• Single-line comment:-space comment content where-the following space must be
• Multi-line comment: / comment content /

#以下两句是一样的，不区分大小写
show databases;
SHOW DATABASES;

#创建表格
#create table student info(...); #表名错误，因为表名有空格
create table student_info(...); 

#其中name使用``飘号，因为name和系统关键字或系统函数名等预定义标识符重名了。
CREATE TABLE t_stu(
    id INT,
    `name` VARCHAR(20)
);

select id as "编号", `name` as "姓名" from t_stu; #起别名时，as都可以省略
select id as 编号, `name` as 姓名 from t_stu; #如果字段别名中没有空格，那么可以省略""
select id as 编 号, `name` as 姓 名 from t_stu; #错误，如果字段别名中有空格，那么不能省略""

Chapter 2-DDL Operation Database

2.1, create a database (master)

• grammar

create database 数据库名 [character set 字符集][collate  校对规则]     注: []意思是可选的意思

Character set (charset): is a set of symbols and codes.

• Exercise

Create a database for day01 (default character set)

create database day01;

Create a database of day01_2, specify the character set as gbk (understand)

create database day01_2 character set gbk;

2.2. View all databases

• View all databases

grammar

show databases; 

• View the definition structure of the database [understand]

grammar

show create database 数据库名;

View the definition of the database day01

show create database day01; 

2.3, delete the database

grammar

drop database 数据库名;

Delete the day01_2 database

drop database day01_2;

2.4, modify the database [understand]

grammar

alter database 数据库名 character set 字符集;

Modify the character set of the database day01 (gbk)

alter database day01 character set gbk;

Notice:

• Is utf8, not utf-8
• Not modify the database name

2.5, other operations

Switch database, which database is selected

use 数据库名;           //注意: 在创建表之前一定要指定数据库. use 数据库名

Exercise: use day01

use day01;

View the database in use

select database();

Chapter Three-DDL Operation Table

3.1, create a table

• grammar

  create table 表名(
    列名 类型 [约束],
    列名 类型 [约束]
    ...
        
  );

• type

Numerical type

Integer series: xxxInt

• int(M), must be used together with unsigned zerofill to make sense

Floating point series: float, double (or real)

• double(M,D): indicates that the longest is M digits, of which D digits after the decimal point

  例如：double(5,2)表示的数据范围[-999.99,999.99]，如果超过这个范围会报错。

  Fixed-point series: decimal (the bottom layer is actually a string for storage)

• decimal(M,D): indicates that the longest is M digits, of which D digits after the decimal point

Bit type: bit

• The byte range is: 1-8, the value range is: bit(1)~bit(64), the default bit(1)

Used to store binary numbers. For bit fields, using the select command directly will not see the results. You can use the bit() or hex() function to read. When inserting a bit type field, use the bit() function to convert it to a binary value and then insert it, because the binary code is "01".

Date Time Type

Date and time types: year, date, datetime, timestamp

Pay attention to the representation range of each date and time

The difference between timestamp and datetime:

• The timestamp range is relatively small

• timestamp is related to time zone

  • show variables like ‘time_zone’;
  • set time_zone = ‘+8:00’;
• timestamp is greatly affected by the MySQL version and the SQLMode of the server
• The first non-empty timestamp field in the table will be automatically set to the system time if it is inserted and updated as NULL

string type

MySQL provides a variety of storage types for character data, and different versions may differ. Common ones are:

char,varchar，xxtext，binary，varbinary，xxblob，enum，set等等

• String type char, varchar(M)

  • If char width is not specified, the default is 1 character
  • varchar(M), the width must be specified

Binary and varbinary are similar to char and varchar, except that they contain binary strings and do not support fuzzy queries and the like.

Generally, when saving a small number of strings, we will choose char and varchar; when saving larger text, we usually choose to use text or blob series. Blob and text values will cause some performance problems, especially when a large number of delete operations are performed, they will leave a large "hole" in the data table. In order to improve performance, it is recommended to use the optimize table function on such tables regularly. Defragment. You can use a synthetic index to improve the query performance of large text fields. If you need to perform fuzzy queries on large text fields, MySql provides a prefix index. But still avoid retrieving large blobs or text values when unnecessary.

Enum enumeration type, its value range needs to be explicitly specified by enumeration when creating the table, for an enumeration of 1 to 255 members, 1 byte storage is required; for 【 255`65535】 16 members, 2 byte storage is required. For example: gender enum('男','女'). If you insert a value other than the enumerated value, it will be processed as the first value. Only one of the enumerated values can be selected at a time.

The set collection type can contain 0 to 64 members. You can select multiple members from the set at a time. If a set of 1-8 members is selected, it takes 1 byte, 2 and 3 in turn. . 8 bytes. For example: hoppy set('eat','sleep','play game','travel'), when selecting'eat, sleep' or'sleep, play game, travel'

Example

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| eid            | int(11)      | NO   | PRI | NULL    | auto_increment |
| ename          | varchar(20)  | NO   |     | NULL    |                |
| tel            | char(11)     | NO   |     | NULL    |                |
| gender         | char(1)      | YES  |     | 男        |                |
| salary         | double       | YES  |     | NULL    |                |
| commission_pct | double(3,2)  | YES  |     | NULL    |                |
| birthday       | date         | YES  |     | NULL    |                |
| hiredate       | date         | YES  |     | NULL    |                |
| job_id         | int(11)      | YES  |     | NULL    |                |
| email          | varchar(32)  | YES  |     | NULL    |                |
| mid            | int(11)      | YES  |     | NULL    |                |
| address        | varchar(150) | YES  |     | NULL    |                |
| native_place   | varchar(10)  | YES  |     | NULL    |                |
| did            | int(11)      | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

constraint

• That is, rules, rules and restrictions;
• Role: to ensure that the data inserted by the user is stored in the database in compliance with the specification

Types of constraints:

• not null: not empty; eg: username varchar(40) not null username column cannot have a null value
• unique: unique constraint, the following data cannot be repeated with the previous; eg: cardNo char(18) unique; there can be no duplicate data in the cardNo column

• primary key; primary key constraint (non-empty + unique); generally used on the id column of the table. A table basically has an id column, and the id column is used as a unique identifier

  • auto_increment: Automatic growth, you must set the primary key before you can use auto_increment
• id int primary key auto_increment; id does not need to be maintained by ourselves. When inserting data, directly insert null, and automatically grow and fill it in to avoid duplication.

Note :

• Set the primary key first and then auto_increment can be set
• Only when auto_increment is set can you insert null, otherwise inserting null will report an error

id column:

• Set id to int type, add primary key constraint, automatic growth
• Or set the id to a string type, add a primary key constraint, and cannot set automatic growth

practice

Create a student table (contains the id field, the name field cannot be repeated, the gender field cannot be empty, the default value is male. The id is the primary key and automatically grows)

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长
    NAME VARCHAR(30) UNIQUE, -- 唯一约束
    gender CHAR(1) NOT NULL DEFAULT '男'
);

3.2. View the table [understand]

View all tables

show tables;

View the definition structure of the table

grammar

desc 表名；

Exercise: View the definition structure of the student table

desc student;

3.3. Modify the table [Master, but don’t memorize]

grammar

• Add a column

alter table 【数据库名.]表名称 add 【column】 字段名 数据类型;
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 first;
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 after 另一个字段;

• Modify column type constraints: alter table table name modify field type constraints;
• Modify column name, type, constraint: alter table table name change old column new column type constraint;
• Delete a column: alter table table name drop column name;
• Modify the table name: rename table old table name to new table name;

Exercise

Add a grade field to the student table, the type is varchar(20), cannot be empty

ALTER TABLE student ADD grade VARCHAR(20) NOT NULL;

Change the gender field of the student table to int type, it cannot be empty, the default value is 1

alter table student modify gender varchar(20);

Modify the grade field of the student table to the class field

ALTER TABLE student CHANGE grade class VARCHAR(20) NOT NULL;

Delete the class field

ALTER TABLE student DROP class;

Modify the student table to the teacher table (understand)

RENAME TABLE student TO teacher;

3.4. Delete table [Master]

grammar

drop table 表名；

Delete the teacher table

drop table teacher;

Chapter 4-DML Operation Table Record-Addition, Deletion and Modification [Key Points]

Preparation: Create a product table (product id, product name, product price, product quantity.)

create table product(
    pid int primary key auto_increment,
    pname varchar(40),
    price double,
    num int
);

4.1, insert record

grammar

• Method 1: Insert the specified column, if this column is not listed, it will be automatically assigned with null.

eg: I just want to insert pname, price, insert into t_product(pname, price) values('mac',18000);

insert into 表名(列,列..) values(值,值..);

Note: If the column is not inserted and the non-null constraint is set, an error will be reported

• Method 2: Insert all columns, if any column does not want to insert a value, you need to assign a value to null

insert into table name values(value, value...);

eg:

insert into product values(null,'苹果电脑',18000.0,10);
insert into product values(null,'华为5G手机',30000,20);
insert into product values(null,'小米手机',1800,30);
insert into product values(null,'iPhonex',8000,10);
insert into product values(null,'iPhone7',6000,200);
insert into product values(null,'iPhone6s',4000,1000);
insert into product values(null,'iPhone6',3500,100);
insert into product values(null,'iPhone5s',3000,100);

insert into product values(null,'方便面',4.5,1000);
insert into product values(null,'咖啡',11,200); 
insert into product values(null,'矿泉水',3,500);

4.2, update records

• grammar

update 表名 set 列 =值, 列 =值 [where 条件]

• Exercise

Modify the price of all goods to 5000 yuan

update product set price = 5000;

Modify the price of the product name Apple Computer to 18,000 yuan

UPDATE product set price = 18000 WHERE pname = '苹果电脑';

The product name is Apple Computer's price is changed to 17000, and the quantity is changed to 5

UPDATE product set price = 17000,num = 5 WHERE pname = '苹果电脑';

Increase the price of the product whose product name is instant noodles by 2 yuan on the original basis

UPDATE product set price = price+2 WHERE pname = '方便面';

4.3, delete records

• delete

According to the conditions, delete one by one data

grammar

delete from 表名 [where 条件]    注意: 删除数据用delete,不用truncate

type

Delete the record named'Apple Computer' in the table

delete from product where pname = '苹果电脑';

Delete the record of products whose price is less than 5001

delete from product where price < 5001;

Delete all records in the table (delete statement is generally not recommended to delete, delete statement is executed line by line, too slow)

delete from product;

• truncate
DROP the table directly, and then create the same new table. The deleted data cannot be retrieved. Execution speed is faster than DELETE

  truncate table 表;

• Delete data at work

  • Physical deletion: The real deletion, the data is not there, using delete is a physical deletion
  • Logical deletion: There is no real deletion, the data is still there. Make a mark, in fact, logical deletion is to update eg: state 1 enable 0 disable

Chapter 5-DQL Operation Table Record-Query [Key Points]

5.1, basic query syntax

select 要查询的字段名 from 表名 [where 条件] 

5.2, simple query

Query the records of all rows and all columns

• grammar

  select * form 表

  Query all the columns in the product table

  select * from product;

Query the records of a specific column of a table

• grammar

  select 列名,列名,列名... from 表

  Check product name and price

  select pname, price from product;

  Deduplication query distinct

• grammar

  SELECT DISTINCT 字段名 FROM 表名;   //要数据一模一样才能去重

  Re-check the name of the product

  SELECT DISTINCT pname,price FROM product

  Note for a column, and the column name cannot appear before distinct

Alias lookup

• grammar

  select 列名 as 别名 ,列名  from 表   //列别名  as可以不写
  select 别名.* from 表 as 别名      //表别名(多表查询, 明天会具体讲)

  Query product information, use alias

  SELECT pid ,pname AS '商品名',price AS '商品价格',num AS '商品库存' FROM product

  Operational query (+, -, *, /, %, etc.)

Query the product name, and the product price +10: we can add a fixed value to a field, or perform arithmetic query on multiple fields

select pname ,price+10 as 'price' from product;
select name,chinese+math+english as total from student

Note

• Operate query fields, between fields is possible
• Types such as strings can be used for calculation queries, but the results are meaningless

5.3, conditional query (very important)

• grammar

  select ... from 表 where 条件 
  //取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

  Operator

1. Comparison operators

大于：>
小于：<
大于等于：>=
小于等于：<=
等于：=   不能用于null判断
不等于：!=  或 <>
安全等于: <=>  可以用于null值判断

2. Logical operators (words are recommended for readability)

逻辑与：&& 或 and
逻辑或：|| 或 or
逻辑非：! 或 not
逻辑异或：^ 或 xor

3. Scope

区间范围：between x  and  y
        not between x  and  y
集合范围：in (x,x,x) 
        not  in (x,x,x)

4. Fuzzy query and regular matching (only for string type, date type)

like 'xxx'  模糊查询是处理字符串的时候进行部分匹配
如果想要表示0~n个字符，用%
如果想要表示确定的1个字符，用_

regexp '正则'

5. Special null value handling

#（1）判断时
xx is null
xx is not null
xx <=> null
#（2）计算时
ifnull(xx,代替值) 当xx是null时，用代替值计算

• Exercise

Query products with a product price> 3000

select * from product where price > 3000;

Query products with pid=1

select * from product where pid = 1;

Query the product of pid<>1 (!=)

select * from product where pid <> 1;

Check the price of goods between 3000 and 6000

select * from product where price between 3000 and 6000;

Query products whose pid is in the range of 1, 5, 7, 15

select * from product where id = 1;
select * from product where id = 5;
select * from product where id = 7;
select * from product where id = 15;

select * from product where id in (1,5,7,15);

Search for products whose product name starts with iPho (iPhone series)

select * from product where pname like 'iPho%';

Query products whose price is greater than 3000 and the quantity is greater than 20 (conditions and conditions and...)

select * from product where price > 3000 and num > 20;

Query products with id=1 or with a price less than 3000

select * from product where pid = 1 or price < 3000;

5.4, sort query

Sorting is written after the query, which means sorting after the data is queried

• Environmental preparation

  # 创建学生表(有sid,学生姓名,学生性别,学生年龄,分数列,其中sid为主键自动增长)
  CREATE TABLE student(
    sid INT PRIMARY KEY auto_increment,
    sname VARCHAR(40),
    sex VARCHAR(10),
    age INT,
    score DOUBLE
  );
  
  INSERT INTO student VALUES(null,'zs','男',18,98.5);
  INSERT INTO student VALUES(null,'ls','女',18,96.5);
  INSERT INTO student VALUES(null,'ww','男',15,50.5);
  INSERT INTO student VALUES(null,'zl','女',20,98.5);
  INSERT INTO student VALUES(null,'tq','男',18,60.5);
  INSERT INTO student VALUES(null,'wb','男',38,98.5);
  INSERT INTO student VALUES(null,'小丽','男',18,100);
  INSERT INTO student VALUES(null,'小红','女',28,28);
  INSERT INTO student VALUES(null,'小强','男',21,95);
  

• Single column sort

Syntax: Sort by only one field, single column sort

SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC];  //ASC: 升序，默认值; DESC: 降序

Case: Query all students in descending order of scores

SELECT * FROM student ORDER BY score DESC

• Combination sort

Syntax: Sort multiple fields at the same time, if the first field is equal, sort by the second field, and so on

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

Practice: Query all students in descending order of scores, if the scores are the same, then descending by age

SELECT * FROM student ORDER BY score DESC, age DESC

5.5, aggregate functions

Aggregate functions are used for statistics, and are usually used together with grouping queries to count the data of each group

• List of aggregate functions

grammar

SELECT 聚合函数(列名) FROM 表名 [where 条件];

Case study

-- 求出学生表里面的最高分数
SELECT MAX(score) FROM student
-- 求出学生表里面的最低分数
SELECT MIN(score) FROM student
-- 求出学生表里面的分数的总和(忽略null值)
SELECT SUM(score) FROM student
-- 求出学生表里面的平均分
SELECT AVG(score) FROM student
-- 求出学生表里面的平均分（缺考了当成0分处理）
SELECT AVG(IFNULL(score,0)) FROM student
-- 统计学生的总人数 (忽略null) 
SELECT COUNT(sid) FROM student
SELECT COUNT(*) FROM student

Note: The aggregate function ignores the null value NULL

We found that NULL records will not be counted. It is recommended that you do not use columns that may be null if you count the number, but what if you need to count NULLs as well? We can use the IFNULL (column name, default value) function to solve this problem. If the column is not empty, return the value of this column. If it is NULL, the default value is returned.

-- 求出学生表里面的平均分（缺考了当成0分处理）
SELECT AVG(IFNULL(score,0)) FROM student;

5.6, group query

GROUP BY treats the same content in the grouping field results as a group, and returns the first data of each group, so separate grouping is useless. The purpose of grouping is for statistics, generally grouping will be used with aggregate functions

• Grouping

grammar

SELECT 字段1,字段2... FROM 表名  [where 条件] GROUP BY 列 [HAVING 条件];

Case study

-- 根据性别分组, 统计每一组学生的总人数
SELECT sex '性别',COUNT(sid) '总人数' FROM student GROUP BY sex

-- 根据性别分组，统计每组学生的平均分
SELECT sex '性别',AVG(score) '平均分' FROM student GROUP BY sex

-- 根据性别分组，统计每组学生的总分
SELECT sex '性别',SUM(score) '总分' FROM student GROUP BY sex

• Filter after grouping

The conditions after grouping cannot be written after where, and the where keyword must be written before group by

According to gender grouping, count the total number of students in each group> 5 (filtered after grouping)

SELECT sex, count(*) FROM student GROUP BY sex HAVING count(sid) > 5

According to gender grouping, only those whose age is greater than or equal to 18 are counted, and the number of persons in the group is required to be greater than 4

SELECT sex '性别',COUNT(sid) '总人数' FROM student WHERE age >= 18 GROUP BY sex HAVING COUNT(sid) > 4

• The difference between where and having [Interview]

The role of where clause

• 1) Before grouping the query results, remove the rows that do not meet the where condition, that is, filter the data before grouping, that is, filter first and then group.
• 2) Aggregate functions cannot be used after where

The role of having words

• 1) The function of the having clause is to filter the groups that meet the conditions, that is, to filter the data after grouping, that is, to group and then filter.
• 2) Aggregate functions can be used behind having

5.7, pagination query

• grammar

  select ... from .... limit a ,b

• Case study

  -- 分页查询
  -- limit 关键字是使用在查询的后边，如果有排序的话则使用在排序的后边
  -- limit的语法: limit offset,length  其中offset表示跳过多少条数据，length表示查询多少条数据
  SELECT * FROM product LIMIT 0,3
  -- 查询product表中的前三条数据(0表示跳过0条，3表示查询3条)
  
  SELECT * FROM product LIMIT 3,3
  -- 查询product表的第四到六条数据(3表示跳过3条，3表示查询3条)
  -- 分页的时候，只会告诉你我需要第几页的数据，并且每页有多少条数据
  -- 假如，每页需要3条数据，我想要第一页数据: limit 0,3
  -- 假如,每页需要3条数据，我想要第二页数据: limit 3,3
  -- 假如，每页需要3条数据，我想要第三页数据: limit 6,3
  -- 结论: length = 每页的数据条数，offset = (当前页数 - 1)*每页数据条数
  -- limit (当前页数 - 1)*每页数据条数, 每页数据条数

  5.8. Summary of query syntax

  select...from...where...group by...order by...limit
  
  select...from...where...
  select...from...where...order by...
  select...from...where...limit...
  select...from...where...order by...imit

  Chapter VI Three Paradigms of Database

Good database design will have an important impact on data storage performance and later program development. The establishment of a scientific, standardized database needs to satisfy some rules to optimize the design and storage of data. These rules are called paradigms.

6.1. First Normal Form: Ensure that each column remains atomic

The first paradigm is the most basic paradigm. If all the field values in the database table are non-decomposable atomic values, it means that the database table satisfies the first normal form.

The reasonable compliance of the first paradigm needs to be determined according to the actual needs of the system. For example, some database systems need to use the "address" attribute, and originally designed the "address" attribute directly as a field in a database table. However, if the system frequently accesses the "city" part of the "address" attribute, then the "address" attribute must be re-divided into multiple parts such as provinces, cities, and detailed addresses for storage. Part of the operation will be very convenient. This design is considered to meet the first paradigm of the database, as shown in the following table.

If you do not follow the first normal form, the query data needs further processing (inconvenient query). Comply with the first normal form, query what data is needed for any field data (convenient to query)

6.2, the second normal form: ensure that each column in the table is related to the primary key

The second paradigm is a step beyond the first paradigm. The second paradigm needs to ensure that each column in the database table is related to the primary key, and not only related to a certain part of the primary key (mainly for the combined primary key). That is to say, in a database table, only one type of data can be stored in a table, and multiple types of data cannot be stored in the same database table.

For example, to design an order information table, because there may be multiple products in the order, the order number and product number should be used as the combined primary key of the database table, as shown in the following table

This creates a problem: this table uses the order number and the product number as the combined primary key. In this way, the product name, unit, product price and other information in the table are not related to the primary key of the table, but only related to the product number. So here is a violation of the design principles of the second normal form.

And if this order information table is split, the product information is separated into another table, and the order item table is also separated into another table, it is perfect. As follows

<img src="imgs/tu_13.png" style="zoom: 67%;" />

This design greatly reduces the redundancy of the database. If you want to get the product information of the order, use the product number to query in the product information table

6.3. Third Normal Form: Ensure that each column is directly related to the primary key column, not indirectly related

The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, but not indirectly related.

For example, when designing an order data table, you can use the customer number as a foreign key to establish a corresponding relationship with the order table. It is not possible to add fields about other customer information (such as name, company, etc.) in the order table. The design shown in the following two tables is a database table that satisfies the third normal form.

<img src="imgs/tu_14.png" style="zoom:67%;" />

In this way, when querying order information, you can use the customer number to refer to the records in the customer information table, and you don’t have to enter the content of customer information multiple times in the order information table, reducing data redundancy

Chapter 7 Foreign Key Constraints

7.1, the concept of foreign key constraints

Under the premise of following the three paradigms, many times we have to split the table and store the data in multiple tables to reduce redundant data. But there is an association relationship between the split table and the table, we must use a constraint to agree on the relationship between the table and the table, this constraint is the foreign key constraint

7.2, the role of foreign key constraints

The foreign key constraint is to ensure the referential integrity between one or two tables, and the foreign key is constructed on the two fields of a table or the referential relationship between the two fields of two tables.

7.3. Syntax for creating foreign key constraints

• Specify foreign key constraints when creating a table

  create table [数据名.]从表名(
    字段名1 数据类型  primary key ,
    字段名2 数据类型 ,
    ....,
    [constraint 外键约束名] foreign key (从表字段) references 主表名(主表字段) [on update 外键约束等级][on delete 外键约束等级]
    #外键只能在所有字段列表后面单独指定
    #如果要自己命名外键约束名，建议 主表名_从表名_关联字段名_fk
  );

• Specify foreign key constraints after creating the table

  alter table 从表名称 add [constraint 外键约束名] foreign key (从表字段名) references 主表名(主表被参照字段名) [on update xx][on delete xx];

  7.4, delete the syntax of foreign key constraints

  ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;
  #查看约束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
  #删除外键约束不会删除对应的索引，如果需要删除索引，需要用ALTER TABLE 表名称 DROP INDEX 索引名;
  #查看索引名 show index from 表名称;

  7.5. Requirements for foreign key constraints

• Create a foreign key on the slave table, and the master table must exist first.
• A table can establish multiple foreign key constraints
• Under normal circumstances, the foreign key column of the secondary table must point to the primary key column of the primary table
• The name of the foreign key column of the slave table and the referenced column of the main table can be different, but the data type must be the same

7.6, foreign key constraint level

• Cascade mode : When updating/delete records on the primary table, synchronize update/delete to delete matching records from the secondary table
• Set null method : When updating/delete records on the main table, set the column of the matching record from the table to null, but note that the foreign key column of the child table cannot be not null
• No action method : If there are matching records in the child table, the update/delete operation of the candidate key corresponding to the parent table is not allowed
• Restrict method : same as no action, both check foreign key constraints immediately
• Set default method (it may be blank in the visualization tool SQLyog): When the parent table changes, the child table sets the foreign key column to a default value, but Innodb cannot recognize it
• does not specify a level, it is equivalent to Restrict mode

7.7, foreign key constraint exercises

-- 部门表
create table dept( 
    id int primary key,
    dept_name varchar(50),
    dept_location varchar(50)
);
-- 员工表
CREATE TABLE emp(
    eid int primary key,
    name varchar(50) not null,
    sex varchar(10),
    dept_id int
);
-- 给员工表表的dept_id添加外键指向部门表的主键
alter table emp add foreign key(dept_id) references dept(id)

Chapter 8 Relationship Between Multiple Tables

8.1, one-to-many relationship

• concept

The one-to-many relationship means: a row of data in the master table can correspond to multiple rows of data in the slave table at the same time, and conversely, multiple rows of data in the slave table point to the same row of data in the master table.

• Application scenario

Classification table and product table, class table and student table, user table and order table, etc.

• Principles of table building

Take one side as the primary table, and the more side as the secondary table, and specify a field in the secondary table as a foreign key, pointing to the primary key of the primary table

• Table building statement exercise

  -- 创建分类表
  CREATE TABLE category(
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(50)
  );
  
  -- 创建商品表
  CREATE TABLE product(
    pid INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(50),
    price DOUBLE,
    cid INT
  )
  -- 给商品表添加一个外键
  alter table product add foreign key(cid) references  category(cid)

  8.2. Many-to-many relationship

• concept

Both tables are more than one side. A row of data in table A can correspond to multiple rows of data in table B at the same time, on the contrary, a row of data in table B can also correspond to multiple rows of data in table A at the same time

• Application scenario

Order table and product table, student table and course table, etc.

• Principles of table building

Because the two tables are multiple parties, it is impossible to create foreign keys in both tables. Therefore, you need to create a new intermediate table and define two fields in the intermediate table. These two fields are used as foreign keys to point to each of the two tables. Primary key

• Table building statement exercise

  -- 创建学生表
  CREATE TABLE student(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(50)
  );
  
  -- 创建课程表
  CREATE TABLE course(
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(20)
  );
  
  -- 创建中间表
  CREATE TABLE s_c_table(
    sno INT,
    cno INT
  );
  -- 给sno字段添加外键指向student表的sid主键
  ALTER TABLE s_c_table ADD CONSTRAINT fkey01 FOREIGN KEY(sno) REFERENCES student(sid);
  -- 给cno字段添加外键指向course表的cid主键
  ALTER TABLE s_c_table ADD CONSTRAINT fkey03 FOREIGN KEY(cno) REFERENCES course(cid);

  8.3, one-to-one relationship (understanding)

• The first one-to-one relationship

We have learned about one-to-many relationships before. In a one-to-many relationship, a row of data in the master table can correspond to multiple rows of data in the slave table, while a row of data in the slave table can only correspond to one row of data in the master table. This kind of relationship between one row of data and one row of data, we can regard it as a one-to-one relationship

• The second one-to-one relationship

A row of data in table A corresponds to a row of data in table B. On the contrary, a row of data in table B corresponds to a row of data in table A. At this time, we can regard table A as the main table and table B as the slave table, or use B Table as the main table A table as the slave table

• Principles of table building

Specify a field in the secondary table to create a foreign key and point to the primary key of the primary table, and then add a unique constraint to the foreign key field of the secondary table

Chapter 9 Multi-table Association Query

Multi-table related query is to use a SQL statement to query the data of multiple related tables

9.1. Environmental preparation

-- 创建一张分类表(类别id,类别名称.备注:类别id为主键并且自动增长)
CREATE TABLE t_category(
        cid INT PRIMARY KEY auto_increment,
        cname VARCHAR(40)
);
INSERT INTO t_category values(null,'手机数码');
INSERT INTO t_category values(null,'食物');
INSERT INTO t_category values(null,'鞋靴箱包');


-- 创建一张商品表(商品id,商品名称,商品价格,商品数量,类别.备注:商品id为主键并且自动增长)

CREATE TABLE t_product(
        pid INT PRIMARY KEY auto_increment,
        pname VARCHAR(40),
        price DOUBLE,
        num INT,
        cno INT
);

insert into t_product values(null,'苹果电脑',18000,10,1);
insert into t_product values(null,'iPhone8s',5500,100,1);
insert into t_product values(null,'iPhone7',5000,100,1);
insert into t_product values(null,'iPhone6s',4500,1000,1);
insert into t_product values(null,'iPhone6',3800,200,1);
insert into t_product values(null,'iPhone5s',2000,10,1);
insert into t_product values(null,'iPhone4s',18000,1,1);

insert into t_product values(null,'方便面',4.5,1000,2);
insert into t_product values(null,'咖啡',10,100,2);
insert into t_product values(null,'矿泉水',2.5,100,2);

insert into t_product values(null,'法拉利',3000000,50,null);

-- 给 商品表添加外键
ALTER TABLE t_product ADD FOREIGN KEY(cno) REFERENCES t_category(cid);

9.2. Cross query [understand]

Cross query is actually to unconditionally connect the data of multiple tables for display

• grammar

  select a.列,a.列,b.列,b.列 from a,b ;  
  
  select a.*,b.* from a,b ;  
  --或者 
  select * from a,b;

• Exercise

Use cross-check categories and products

select * from t_category,t_product;

Through the query results, we can see that cross query is actually a wrong approach. There are a lot of wrong data in the query result set. We call the result set of cross query a Cartesian product.

• Cartesian Product

Assuming set A={a,b} and set B={0,1,2}, then the Cartesian product of the two sets is {(a,0),(a,1),(a,2),( b,0),(b,1),(b,2)}. Can be extended to multiple sets.

9.3, inner join query

Cross-query produces such a result is not what we want, so how to remove the wrong, unwanted records, of course, through the conditional filtering. Usually there is an association relationship between multiple tables to be queried, then the Cartesian product is removed association relationship (primary and foreign key relationship) This kind of query that removes the Cartesian product through conditional filtering is called a join query. Join query can be divided into inner join query and outer join query, we first learn inner join query

• Implicit inner join query

There is no inner join keyword in implicit inner join query

select [字段,字段,字段] from a,b where 连接条件 (b表里面的外键 = a表里面的主键 ) 

• Explicit inner join query

The explicit inner join query contains the inner join keyword

select [字段,字段,字段] from a [inner] join b on 连接条件 [ where 其它条件]

• Inner join query exercise

Query product information under all categories, if there is no product under that category, it will not be displayed

-- 1 隐式内连接方式 
select *from t_category  c, t_product  p WHERE c.cid = p.cno;

-- 2 显示内连接方式
-- 查询手机数码这个分类下的所有商品的信息以及分类信息
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid WHERE tc.cname = '手机数码';

SELECT * from t_category c INNER JOIN t_product p ON c.cid = p.cno

• Features of inner join query

The data in the master table and the slave table can be queried if it meets the connection conditions, and will not be queried if the connection conditions are not met

9.4, outer join query

We found that the inner join query is the common part that satisfies the connection condition. If you want to ensure that all the data of a table is queried, the join query is performed. Then you must use the outer join query. The outer join is divided into left outer join and right Outer join

• Left outer join query

concept

Take the table on the left of the join as the main table, display all the data of the main table, query and connect the data of the table on the right according to the conditions, display if the conditions are met, and display as null if not. It can be understood as: on the basis of internal connection, ensure that all the data in the table on the left are displayed

grammar

select 字段 from a left [outer] join b on 条件

Exercise

Query the product information under all categories, even if there is no product under the category, you need to display the category information

SELECT * FROM t_category c LEFT OUTER JOIN t_product p ON c.cid = p.cno

• Right outer join query

concept

Take the table on the right of the join as the main table, display all the data of the table on the right, query the data of the table on the left of the join according to the conditions, display it if it is satisfied, and display it as null if it is not. It can be understood as: on the basis of internal connection, ensure that all the data in the table on the right are displayed

grammar

select 字段 from a right [outer] join b on 条件

Exercise

Query the category information corresponding to all commodities

SELECT * FROM t_category c RIGHT  OUTER JOIN t_product p ON c.cid = p.cno

9.5. Union joint query realizes full outer join query

First of all, it must be clear that joint query is not a way of multi-table join query. Union query is to combine the query results of multiple query statements into one result and remove duplicate data.

The full outer join query means to query the data of the left table and the right table, and then connect according to the connection conditions

• Syntax of union

  查询语句1 union 查询语句2 union 查询语句3 ...

• Exercise

  # 用左外的A union 右外的B
  SELECT * FROM t_category c LEFT OUTER JOIN t_product p ON c.cid = p.cno
  union
  SELECT * FROM t_category c RIGHT  OUTER JOIN t_product p ON c.cid = p.cno

  9.6, self-join query

Self-join query is a special multi-table join query, because the tables of two related queries are the same table, and the two tables are virtualized by aliasing, and then the two tables are connected to the query

• Ready to work

  -- 员工表
  CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2) -- 工资
  );
  -- 添加员工
  INSERT INTO emp(id,ename,mgr,joindate,salary) VALUES 
  (1001,'孙悟空',1004,'2000-12-17','8000.00'),
  (1002,'卢俊义',1006,'2001-02-20','16000.00'),
  (1003,'林冲',1006,'2001-02-22','12500.00'),
  (1004,'唐僧',1009,'2001-04-02','29750.00'),
  (1005,'李逵',1006,'2001-09-28','12500.00'),
  (1006,'宋江',1009,'2001-05-01','28500.00'),
  (1007,'刘备',1009,'2001-09-01','24500.00'),
  (1008,'猪八戒',1004,'2007-04-19','30000.00'),
  (1009,'罗贯中',NULL,'2001-11-17','50000.00'),
  (1010,'吴用',1006,'2001-09-08','15000.00'),
  (1011,'沙僧',1004,'2007-05-23','11000.00'),
  (1012,'李逵',1006,'2001-12-03','9500.00'),
  (1013,'小白龙',1004,'2001-12-03','30000.00'),
  (1014,'关羽',1007,'2002-01-23','13000.00');
  #查询孙悟空的上级
  SELECT employee.*,manager.ename mgrname FROM emp employee,emp manager where employee.mgr=manager.id AND employee.ename='孙悟空'

• Self-join query exercise

Query the employee’s number, name, salary and his leader’s number, name, salary

#这些数据全部在员工表中
#把t_employee表，即当做员工表，又当做领导表
#领导表是虚拟的概念，我们可以通过取别名的方式虚拟
SELECT employee.id "员工的编号",emp.ename "员工的姓名" ,emp.salary "员工的薪资",
    manager.id "领导的编号" ,manager.ename "领导的姓名",manager.salary "领导的薪资"
FROM emp employee INNER JOIN emp manager
#emp employee：employee.，表示的是员工表的
#emp manager：如果用manager.，表示的是领导表的
ON employee.mgr = manager.id  # 员工的mgr指向上级的id

#表的别名不要加""，给列取别名，可以用""，列的别名不使用""也可以，但是要避免包含空格等特殊符号。

Chapter 10 Subqueries

If a query statement is nested in another query statement, then this query statement is called a subquery, which is divided into: where type, from type, exists type according to different positions. Note: No matter where the subquery is, the subquery must be enclosed in ().

10.1 where type

①The subquery is a single-value result (single row and single column), so you can use it (=,> and other comparison operators)

# 查询价格最高的商品信息
select * from t_product where price = (select max(price) from t_product)

②Sub-query is a multi-value result, then you can use it ([not] in (sub-query result), or >all (sub-query result), or >=all (sub-query result), <all (sub-query result) , <=all (sub-query result), or >any (sub-query result), or >=any (sub-query result), <any (sub-query result), <=any (sub-query result))

# 查询价格最高的商品信息
SELECT * FROM t_product WHERE price >=ALL(SELECT price FROM t_product)
select * from t_product order by price desc limit 0,1

10.2 from type

The result of the subquery is the result of multiple rows and multiple columns, similar to a table.

The subquery must be aliased, that is, the name of the temporary table. Do not add "" and spaces to the alias of the table.

-- 思路一: 使用连接查询
-- 使用外连接，查询出分类表的所有数据
SELECT tc.cname,COUNT(tp.pid) FROM t_category tc LEFT JOIN t_product tp ON tp.cno = tc.cid GROUP BY tc.cname

-- 思路二: 使用子查询
-- 第一步:对t_product根据cno进行分组查询，统计每个分类的商品数量
SELECT cno,COUNT(pid) FROM t_product GROUP BY cno
-- 第二步: 用t_category表去连接第一步查询出来的结果，进行连接查询,此时要求查询出所有的分类
SELECT tc.cname,IFNULL(tn.total,0) '总数量' FROM t_category tc LEFT JOIN (SELECT cno,COUNT(pid) total FROM t_product GROUP BY cno) tn ON tn.cno=tc.cid

10.3, exists type

# 查询那些有商品的分类
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno = tc.cid);

Link: blog.csdn.net/qq_42076902/article/details/121701974