MySQL database study notes (3) Constraints and indexes, MySQL functions, related queries, 6 words and sub-queries of the Select statement

MySQL database study notes (3)

Earlier we learned about relational database design rules:

  • Follow the ER model and the three paradigms (E entity represents the meaning of an entity, which corresponds to a table in the database; R relationship represents the meaning of a relationship.)
  • Three paradigms: 1. Columns cannot be split 2. Unique identification 3. Relationship references primary key.

Specific performance:

  • Put the data in the table, and then put the table in the library.
  • There can be multiple tables in a database, and each table has a name to identify itself. The table name is unique.
  • Table has some characteristics, these characteristics define how data is stored in the table, similar to the design of "class" in java.
  • The table is composed of columns, which we also call fields . Each field describes the meaning of the data it contains, the design of the data table is actually the design of the field .
  • The data in the table is stored in rows, and a row is a record. Each line is similar to "object" in java or python.

This article will continue to learn about MySQL database knowledge.


1. Constraints and Indexes

Data integrity refers to the accuracy and reliability of data.

Data integrity considerations
1. Entity integrity: For example, there cannot be two identical and indistinguishable records in the same table.
2. Domain completeness: For example, the age range is 0-120, and the gender range is "male/female".
3. Referential integrity: For example, the employee's department should be able to find this department in the department table.
4. User-defined integrity: For example, the user name is unique, the password cannot be empty, etc.

Constraints are used to implement and maintain data business rules and data integrity. The scope of constraints is limited to the current database. Constraints can be treated as database objects. They have names and association modes. Note: Constraints are logical constraints and will not occupy additional space due to setting constraints.

According to the characteristics of constraints, constraints can be divided into the following types of constraints:

According to the characteristics of constraints, classify constraints
1. Key constraints: primary key constraints, foreign key constraints, unique key constraints
2. Not NULL constraint: non-empty constraint
3.Check constraints: check constraints
4.Default constraint: default value constraint
5. Self-increment constraint

An index is a separate, physically stored database structure on a data page. It is a collection of one or several column values ​​in a table and a corresponding logical pointer list that points to the physical identification data page of the data value in the table ( similar to the Xinhua dictionary Catalog index page ).

The existence of the index will increase the storage space of the database, and the time overhead of inserting and modifying data will increase (because the index will also change when inserting and modifying the data), but it can greatly speed up the query. Therefore, indexes should be created on key columns, or other columns that often need to be queried, sorted, and searched by range, and should not be created on columns that are rarely used and referenced in queries, and columns that are frequently modified.

Precautions:

  • MySQL will automatically create indexes on primary key, unique key, and foreign key columns. If you need to create indexes on other columns, you need to create them manually.
  • When the primary key is deleted, the corresponding index will also be deleted.
  • The way to delete the unique key is also achieved through the corresponding index.
  • To delete a foreign key, the index on the foreign key column still exists. If you need to delete it, you need to delete the index separately.

2.DDL (constraint)

2.1 View the constraints of a table

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
或
SHOW CREATE TABLE 表名;

2.2 View the index of a table

SHOW INDEX FROM 表名称;

2.3 Primary key constraints: primary key

Primary keys are divided into single-column primary keys and composite primary keys.

The characteristics of the primary key
1. Unique and non-empty
2. There can only be only one primary key constraint in a table
3. The primary key constraint name is PRIMARY KEY
4. Create the primary key will automatically correspond to the index, and delete the index corresponding to the primary key will also be deleted

(1) Specify the primary key constraint when building the table

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	....
);
或
create table 【数据名.】表名(
	字段名1 数据类型,
	....,
    primary key(字段名1)
);
或
create table 【数据名.】表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表)#如果是复合主键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加primary key
);

(2) Specify the primary key constraint after the table is built

alter table 表名称 add primary key (主键字段列表);

(3) How to delete the primary key constraint

alter table 表名称 drop primary key;

2.4 Unique key constraint: unique key

Unique key constraint characteristics
1. The same table can have multiple unique constraints.
2. The unique constraint can be the unique value of a certain column, or the unique combination of multiple column values.
3. MySQL will create a unique constraint by default on the column of the unique constraint.
4. The unique key can only be deleted by deleting the corresponding index, and the unique key index name needs to be specified when deleting.

(1) Specify the unique key constraint when building the table

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 unique key,
	....
);

create table 【数据名.】表名(
    字段名1 数据类型  primary key ,
    字段名2 数据类型,
    字段名3 数据类型,
	....,
    unique key(复合唯一字段列表)#如果是复合唯一键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加unique key
);

(2) Add a unique key constraint after the table is built

alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名列表);
#如果没有指定约束名,(字段名列表)中只有一个字段的,默认是该字段名,如果是多个字段的默认是字段名列表的第1个字段名。也可以通过show index from 表名;来查看

(3) How to delete the unique key constraint

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
#注意:如果忘记名称,可以通过“show index from 表名称;”查看

2.5 Foreign key constraints: foreign key

Foreign key features
1. The foreign key constraint is to ensure the referential integrity between one or two tables, and the foreign key is to construct a referential relationship between two fields of a table or two fields of two tables.
2. When creating a foreign key constraint, if you do not give a foreign key constraint name, the default name is not a column name, but a foreign key name is generated. You can also specify a foreign key constraint name.
3. When creating a foreign key constraint, the system will create a corresponding ordinary index on the column by default. But the index name is the column name, not the constraint name of the foreign key.
4. When deleting a foreign key, the ordinary index on the foreign key column needs to be deleted separately.

Requirements for establishing foreign key constraints:

  • 1. Create a foreign key in the slave table, and the main table must exist first.
  • 2. A table can establish multiple external constraints.
  • 3. From the foreign key column of the table, only the key column (primary key, unique key, foreign key) can be referenced in the main table.
  • 4. 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.

The foreign key constraint has 5 constraint levels:

  • Cascade method: When updating/delete records on the parent table, synchronize the update/delete to delete the matching records of the child table.
  • Set null method: When updating/delete records on the parent table, set the column of the matching record on the child table to null, but note that the foreign key column of the child table cannot be not null.
  • No action mode: If there are matching records in the child table, update/delete operations are not allowed on the candidate key corresponding to the parent table.
  • Restrict mode: Same as no action, both check foreign key constraints immediately. Note: If the level is not specified, it is equivalent to the Restrict mode.
  • Set default method: When the parent table changes, the child table sets the foreign key column to a default value, but Innodb cannot recognize it.

(1) Specify foreign key constraints when building a table

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】,
	....,
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

create table 【数据名.】表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表),#如果是复合主键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

(2) Specify the foreign key constraint after the table is built

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

(3) Delete foreign key constraints

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

2.6 Non-empty constraint: Not NULL

Not NULL is a non-empty constraint, which stipulates that a certain field cannot be empty.

(1) Specify a non-empty constraint for a field when building a table

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】 【not null】,
	....,
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

create table 【数据名.】表名(
    字段名1 数据类型 【not null】,
    字段名2 数据类型 【not null】,
	....,
    primary key(复合主键字段列表),#如果是复合主键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

(2) Specify that a certain field is not empty after the table is built

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL 【default 默认值】;
#如果该字段原来设置了默认值约束,要跟着一起再写一遍,否则默认值约束会丢失

(3) Cancel a non-empty field after the table is built

ALTER TABLE 表名称 MODIFY 字段名 数据类型 【default 默认值】;
#如果该字段原来设置了默认值约束,要跟着一起再写一遍,否则默认值约束会丢失

2.7 Default value constraint: Default

(1) Assign a default constraint to a field when building a table

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】 【not null】 【default 默认值】,
	....,
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

create table 【数据名.】表名(
    字段名1 数据类型 【not null】 【default 默认值】,
    字段名2 数据类型 【not null】 【default 默认值】,
	....,
    primary key(复合主键字段列表),#如果是复合主键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键,那么就需要在所有字段列表后面使用这种形式指定,不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

(2) Specify the default value constraint of a certain field after the table is built

ALTER TABLE 表名称 MODIFY 字段名 数据类型  【default 默认值】 【NOT NULL】;
#如果该字段原来设置了非空约束,要跟着一起再写一遍,否则非空约束会丢失

(3) Cancel the default value constraint of a field after the table is built

ALTER TABLE 表名称 MODIFY 字段名 数据类型 【NOT NULL】;
#如果该字段原来设置了非空约束,要跟着一起再写一遍,否则非空约束会丢失

2.8 Self-increment constraint: auto_increment

About auto_increment
1. A table can only have at most one self-increasing column
2. The self-increasing column must be a key column (primary key column, unique key column, foreign key column), and requires non-empty
3. Auto-increment column must be of integer type
4. The auto-growth column of the InnoDB table can be inserted manually, but if the inserted value is empty or 0, the actual inserted value will be the value after the auto-growth

(1) Specify the self-increasing column when building the table

create table 【数据名.】表名(
	字段名1 数据类型  primary key auto_increment,
	字段名2 数据类型 【unique key】 【not null】 【default 默认值】,
	....
);

或
create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key  not null】 auto_increment,
	....
);

(2) Specify the self-increasing column after the table is built

alter table 【数据名.】表名 modify 自增字段名 数据类型 auto_increment;

(3) How to delete the self-increment constraint

alter table 【数据名.】表名 modify 自增字段名 数据类型;

3. DML (constraint)

  • 1. If a column has an auto-increment constraint, how to add the value of the field

When adding data, for auto-incrementing columns:

insert into 【数据库名.]表名称 values(值列表);#在值列表中,对应自增列可以赋值为null和0

insert into 【数据库名.]表名称(部分字段列表) values(值列表);#自增列在(部分字段列表)中不写就可以
  • 2. If a column has a default value constraint, how to add or modify the value of the field

For default values, when adding data:

insert into 【数据库名.]表名称 values(值列表);#在值列表中,对应默认值列,如果想用默认值,用default

insert into 【数据库名.]表名称(部分字段列表) values(值列表);#对应默认值列,如果想用默认值,在(部分字段列表)中不写就可以

change the data:

update 【数据库名.]表名称 set 字段名1 = 值1, 字段名2 = 值2 。。。 【where 条件】; #对应默认值列,如果想用默认值,写字段名 = default就可以

4. Functions in MySQL

Two kinds of SQL functions: single-line functions and multi-line functions.

4.1 One-line functions

Features of one-line functions:

  • Transform only one row, and return one result per row
  • Can be nested
  • The parameter can be a field or a value or an expression

4.1.1 String functions

functionusage
CONCAT(S1,S2,...,Sn)Connect S1, S2,..., Sn as a string
CONCAT_WS(s, S1,S2,...,Sn)Same as CONCAT(s1,s2,...) function, but s must be added between each string
CHAR_LENGTH(s)Returns the number of characters in the string s
LENGTH(s)Returns the number of bytes in the string s, which is related to the character set
INSERT(str, index, len, instr)Replace the string str starting from the index position and the substring len characters long with the string instr
UPPER(s) or UCASE(s)Convert all letters of the string s to uppercase letters
LOWER(s) or LCASE(s)Convert all letters of the string s to lowercase letters
LEFT(s,n)Returns the leftmost n characters of the string s
RIGHT(s,n)Returns the rightmost n characters of the string s
LPAD(str, len, pad)Fill the leftmost side of str with the string pad until the length of str is len characters
RPAD(str ,len, pad)Fill the rightmost side of str with the string pad until the length of str is len characters
LTRIM(s)Remove the space on the left side of the string s
RTRIM(s)Remove the space on the right side of the string s
TRIM(s)Remove the spaces at the beginning and end of the string s
TRIM(【BOTH 】s1 FROM s)Remove s1 at the beginning and end of the string s
TRIM(【LEADING】s1 FROM s)Remove the s1 at the beginning of the string s
TRIM(【TRAILING】s1 FROM s)Remove the s1 at the end of the string s
REPEAT(str, n)Return the result of str repeated n times
REPLACE(str, a, b)Replace all occurrences of string a in string str with string b
STRCMP(s1,s2)Compare strings s1, s2
SUBSTRING(s,index,len)Return len characters from the index position of the string s
  • Example 1: Case control function
functionresult
LOWER('SQL Course')sql course
UPPER('SQL Course')SQL COURSE
Such functions change the case of characters.
  • Example 2: Character control function
functionresult
CONCAT('Hello','World')HelloWorld
SUBSTR('HelloWorld',1,5)Hello
LENGTH('HelloWorld')10
INSTR('HelloWorld','W')6
LPAD(salary,10,'*')*****24000
RPAD(salary,10,'*')24000* * ***
TRIM('H' FROM'HelloWorld')elloWorld
REPLACE('abcd','b','m')amcd

4.1.2 Numerical functions

functionusage
ABS(x)Returns the absolute value of x
CEIL(x)Returns the smallest integer value greater than x
FLOOR(x)Returns the largest integer value less than x
MOD(x,y)Returns the modulus of x/y
RAND()Return a random value from 0 to 1
ROUND(x,y)Returns the rounded value of the parameter x with y decimal places
TRUNCATE(x,y)Returns the result of the number x truncated to y decimal places
SQRT(x)Returns the square root of x
POW(x,y)Returns x to the power of y

Example 1: ROUND: round off

ROUND(45.926, 2)     -->  45.93
  • Example 2: TRUNCATE: truncated
TRUNCATE(45.926)      --> 45
  • Example 3: MOD: Find the remainder
MOD(1600, 300)	 --> 100

4.1.3 Date functions

functionusage
CURDATE() or CURRENT_DATE()Return current date
CURTIME() or CURRENT_TIME()Return current time
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()Return the current system date and time
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)Return a specific time value
WEEK(date) / WEEKOFYEAR(date)Returns the week of the year
DAYOFWEEK()Return the day of the week, note: Sunday is 1, Monday is 2. . . Saturday is 7
WEEKDAY(date)Return the day of the week. Note that week 1 is 0 and week 2 is 1. . . Sunday is 6
DAYNAME(date)Return week: MONDAY,TUESDAY.....SUNDAY
MONTHNAME(date)Return month: January,. . . . .
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2)Returns the date interval of date1-date2 / returns the time interval of time1-time2
DATE_ADD(datetime, INTERVAL expr type)Returns the date and time of the INTERVAL time period from the given date and time
DATE_FORMAT(datetime ,fmt)Format the date datetime value according to the string fmt
STR_TO_DATE(str, fmt)Parse str according to the string fmt and parse it as a date

among them:

(1) DATE_ADD(datetime,INTERVAL expr type)

Expression type:

Parameter TypeParameter Type
YEARYEAR_MONTH
MONTHDAY_HOUR
DAYDAY_MINUTE
HOURDAY_SECOND
MINUTEHOUR_MINUTE
SECONDHOUR_SECOND
 MINUTE_SECOND

For example:

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #需要单引号

(2) DATE_FORMAT (datetime, fmt) and STR_TO_DATE (str, fmt)

Format specifierDescriptionFormat specifierDescription
%Y4 digits for the year%yRepresents two digits for the year
%MThe month name indicates the month (January,...)%mTwo digits indicate the month (01, 02, 03...)
%bAbbreviated month name (Jan., Feb.,...)%cNumbers indicate month (1,2,3,...)
%DThe English suffix indicates the number of days in the month (1st, 2nd, 3rd,...)%dTwo digits indicate the number of days in the month (01, 02...)
%eNumerical representation of the number of days in the month (1,2,3,4,5...)  
%HTwo digits represent decimals, 24-hour format (01,02..)%h and %ITwo digits for hour, 12-hour clock (01,02..)
%kNumerical hour, 24-hour clock (1,2,3)%lHour in number form, 12-hour clock (1,2,3,4...)
%iTwo digits for minutes (00,01,02)%S and %sTwo digits indicate seconds (00, 01, 02...)
%WThe name of the week (Sunday...)%aAbbreviation of the week of the week (Sun., Mon., Tues., ..)
%wNumber of days of the week (0=Sunday, 1=Monday...)  
%jUse 3 digits to indicate the number of days in the year (001, 002...)%UUse numbers to indicate the week of the year, (1,2,3...) where Sunday is the first day of the week
%uUse a number to indicate the week of the year, (1,2,3...) where Monday is the first day of the week  
%T24-hour clock%r12-hour clock
%pAM or PM%%Represents%

4.1.4 Process function

functionusage
IF(value,t ,f)If value is true, return t, otherwise return f
IFNULL(value1, value2)If value1 is not empty, return value1, otherwise return value2
CASE WHEN condition 1 THEN result1 WHEN condition 2 THEN result2 .... [ELSE resultn] ENDEquivalent to Java's if...else if...else...
CASE expr WHEN constant value 1 THEN value 1 WHEN constant value 1 THEN value 1 .... [ELSE value n] ENDEquivalent to Java's switch...case...

Example 1:

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "年薪"
FROM employees;

Example 2:

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "实发工资"
FROM   employees;

4.1.5 Other functions

functionusage
database()Returns the current database name
version()Returns the current database version
user()Return the currently logged in user name
password(str)Returns the encrypted version of the string str, a 41-bit string
md5(str)Returns the md5 value of the string str, which is also an encryption method

4.2 Grouping function

  • The meaning of the
    grouping function : the grouping function acts on a group of data and returns a value for a group of data.
Insert picture description here
Group function type
avg(): average function
sum(): sum function
max(): Maximum value function
min(): minimum value function
count)(): counting function

(1) You can use avg() and sum() functions on numerical data

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

(2) The min and max functions can be used for data of any data type

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

(3) COUNT(*) returns the total number of records in the table, applicable to any data type.

SELECT COUNT(*)
FROM	  employees
WHERE  department_id = 50;

(4) COUNT(expr) returns the total number of records where expr is not empty

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

5. Association query

Insert picture description here
Seven results of related query
(1) A∩B
(2) A
(3) A-A∩B
(4) B
(5) B-A∩B
(6) A ∪ B
(7) A ∪B- A∩B or (A-A∩B) ∪ (B-A∩B)

How to achieve?

  • (1) Internal connection
  • (2) External connection: left external connection, right external connection, full external connection (mysql uses union instead of full external connection)

1. Internal connection: realize A∩B

select 字段列表
from A表 inner join B表
on 关联条件
where 等其他子句;

或

select 字段列表
from A表 , B表
where 关联条件 and 等其他子句;

Code example:

#查询员工的姓名和他所在的部门的名称
#员工的姓名在t_employee
#部门的名称在t_department
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did

SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee , t_department
WHERE t_employee.did = t_department.did


#查询薪资高于20000的男员工的姓名和他所在的部门的名称
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
WHERE salary>20000 AND gender = '男'

2. Left outer connection

#实现查询结果是A
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句;

#实现A -  A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 关联字段 is null and 等其他子句;

Code example:

#查询所有员工的姓名和他所在的部门的名称
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did


#查询所有没有部门的员工
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

3. Right outer connection

#实现查询结果是B
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

#实现B -  A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 关联字段 is null and 等其他子句;

Code example:

#查询所有部门,以及所有部门下的员工信息
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did

#查询那些没有员工属于它的部门
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

4. Use union instead of full external connection

#实现查询结果是A∪B
#用左外的A,union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句

union 

select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;



#实现A∪B -  A∩B  或   (A -  A∩B) ∪ (B - A∩B)
#使用左外的 (A -  A∩B)  union 右外的(B - A∩B)
select 字段列表
from A表 left join B表
on 关联条件
where 关联字段 is null and 等其他子句

union

select 字段列表
from A表 right join B表
on 关联条件
where 关联字段 is null and 等其他子句

Code example:

#查询所有员工,所有部门,包括没有员工的部门,和没有部门的员工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did

UNION

SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did


#查询那些没有部门的员工和所有没有员工的部门

#没有部门的员工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

UNION 

#所有没有员工的部门
SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

5. Self-connection

The two associated query tables are the same table, which is virtualized into two tables by aliasing

select 字段列表
from 表名 别名1 inner/left/right join 表名 别名2
on 别名1.关联字段 = 别名2的关联字段
where 其他条件

Code example:

#查询员工的编号,姓名,薪资和他领导的编号,姓名,薪资
#这些数据全部在员工表中
#把t_employee表,即当做员工表,又当做领导表
#领导表是虚拟的概念,我们可以通过取别名的方式虚拟
SELECT emp.eid "员工的编号",emp.ename "员工的姓名" ,emp.salary "员工的薪资",
	mgr.eid "领导的编号" ,mgr.ename "领导的姓名",mgr.salary "领导的薪资"
FROM t_employee emp INNER JOIN t_employee mgr
#t_employee emp:如果用emp.,表示的是员工表的
#t_employee mgr:如果用mgr.,表示的是领导表的
ON emp.mid = mgr.eid

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


6. The 6 major clauses of the select statement

The 6 major sequences of select statements:

The 6 major sequences of select statements
(1) from: From which tables to filter
(2) where: the conditions to filter from the table
(3) group by: group by
(4) having: filter again in the statistical results
(5) order by: sort, descending order: desc, ascending order: asc
(6) limit: paging; limit m, nm= (the number of pages-1) * the number of each page, n = the number of each page

Code example:

#查询每个部门的男生的人数,并且显示人数超过5人的,按照人数降序排列,
#每页只能显示10条,我要第2页
SELECT did,COUNT(*) "人数"
FROM t_employee
WHERE gender = '男'
GROUP BY did
HAVING COUNT(*)>5
ORDER BY 人数 DESC
LIMIT 10,10

6.1 group by and grouping function

You can use the GROUP BY clause to divide the data in the table into several groups**

SELECT column, group_function(column)
FROM table
[WHERE	condition]
[GROUP BY	group_by_expression];
Clear: WHERE must be placed after FROM and before GROUP BY

All columns in the SELECT list that are not included in the grouping function should be included in the GROUP BY clause

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;

The columns included in the GROUP BY clause do not have to be included in the SELECT list

SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

Use multiple columns to group

SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;

6.2 having and grouping functions

SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>8000 ;

The difference between having and where?

  • (1) Where is the condition to filter from the table, and having is to filter again from the statistical results
  • (2) "Grouping/aggregation function" cannot be added after where, and having can be followed by
#统计部门平均工资高于8000的部门和平均工资
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000 #错误
GROUP BY department_id;;
#统计部门平均工资高于8000的部门和平均工资
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary)>8000 ;;
#查询每个部门的男生的人数,并且显示人数超过5人的,按照人数降序排列,
#每页只能显示10条,我要第2页
SELECT did,COUNT(*) "人数"
FROM t_employee
WHERE gender = '男'
GROUP BY did
HAVING COUNT(*)>5
ORDER BY 人数 DESC
LIMIT 10,10

7. Subqueries

Subquery: means that one query is embedded in another query.

Classification according to the location of the subquery
1. Where type: (1) Subquery is a single-value result, you can use (=,> and other comparison operators); (2) Subquery is a multi-value result, then you can use (in, all, any, etc. operators)
2.from type: subquery must be aliased
3.exists type
Note: No matter where the subquery is, the subquery must be enclosed in ()
查询全公司最高工资的员工信息
select * from 员工表 where 薪资 = (select max(薪资) from 员工表);

查询每个部门的编号,名称,平均工资
select 部门编号, 部门名称, 平均工资
from 部门表 inner join (select 部门编号,avg(薪资) from 员工表  group by 部门编号) temp
on 部门表.部门编号 = temp.部门编号

查询那些有员工的部门
select 部门编号, 部门名称 from 部门表
where exists (select * from 员工表  where 部门表.部门编号 = 员工表.部门编号);

to sum up

In this section, we will learn about constraints and indexes, functions in MySQL, associative queries, and the 6 words and sentences of the Select statement. Among them, the most important thing that needs to be mastered is the 6 words and sentences of the Select statement. These are the most basic knowledge in the database, and a careful mastery will be of great help to later learning.

Insert picture description here