Previously, we learned the design rules of relational database:

• Follow the ER model and three paradigms (E entity represents the meaning of entity, corresponding to a table in the database; R relationship represents the meaning of relationship.)
• Three paradigms: 1 Column cannot be disassembled 2 Unique identification3 The relationship refers to the primary key.

Specific performance:

• Put the data in the table, and then put the table into the library.
• There can be multiple tables in a database. Each table has a name to identify itself. Table names are unique.
• Tables have some features that define how data is stored in tables, similar to the design of "classes" in java.
• A table consists 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 by row, and a row is a record. Each line is similar to the "object" in java or python.

This article will continue to learn 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 integrity: for example, the age range is 0-120 and the gender range is "male / female".
3. Referential integrity: for example, an employee's department should be found in the Department table.
For example, user name and password cannot be unique.

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 patterns. Note: constraints are logical constraints and do not take up additional space due to setting constraints.

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

According to the characteristics of constraints, constraints are classified
1. Key constraint: primary key constraint, foreign key constraint and unique key constraint
2.Not NULL constraint: non NULL constraint
3.Check constraints: check constraints
4.Default constraint: default value constraint
5. Self increasing constraint

Index is a separate and physical database structure stored on the data page. It is a collection of one or several column values in the table and a list of logical pointers corresponding to the physical identification data page of the data values in the table (similar to the directory index page of Xinhua Dictionary).

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

matters needing attention:

• MySQL will automatically create indexes on primary key, unique key and foreign key columns. If indexes need to be created on other columns, they need to be created manually.
• When the primary key is deleted, the corresponding index will also be deleted.
• The method of deleting unique keys is also realized through the corresponding index.
• If you 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 viewing constraints of a table

SELECT * FROM information_schema.table_constraints WHERE table_name = 'Table name';
or
SHOW CREATE TABLE Table name;

2.2 view the index of a table

SHOW INDEX FROM Table name;

2.3 primary key constraint: primary key

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

Characteristics of primary key
1. Unique and non empty
2. There can only be one primary key constraint in a table
3. The PRIMARY KEY constraint name is PRIMARY KEY
4. Creating a primary key will automatically delete the corresponding index. Similarly, deleting the index corresponding to the primary key will also delete it

(1) Specify primary key constraint when creating table

create table [Data name.]Table name(
	Field name 1 data type  primary key ,
	....
);
or
create table [Data name.]Table name(
	Field name 1 data type,
	....,
    primary key(Field name 1)
);
or
create table [Data name.]Table name(
    Field name 1 data type,
    Field name 2 data type,
	....,
    primary key(Composite primary key field list)#If it is a composite primary key, you need to specify it in this form after the list of all fields. You cannot add a primary key directly after the field
);

(2) Specify primary key constraint after creating table

alter table Table name add primary key (List of primary key fields);

(3) How to delete a primary key constraint

alter table Table name drop primary key;

2.4 unique key constraint

Unique key constraint feature
1. The same table can have multiple unique constraints.
2. The unique constraint can be the unique value of a column or the unique value of multiple column combinations.
3.MySQL will create a unique constraint on the column with unique constraint by default.
4. The unique key can only be deleted by deleting the corresponding index. When deleting, you need to specify the unique key index name.

(1) Specify a unique key constraint when creating a table

create table [Data name.]Table name(
	Field name 1 data type  primary key ,
	Field name 2 data type unique key,
	....
);

create table [Data name.]Table name(
    Field name 1 data type  primary key ,
    Field name 2 data type,
    Field name 3 data type,
	....,
    unique key(Composite unique field list)#If it is a compound unique key, you need to specify it in this form after all field lists. You cannot add a unique key directly after the field
);

(2) Adding a unique key constraint after creating a table

alter table Table name add [constraint Constraint name] unique [key] (List of field names);
#If no constraint name is specified, if there is only one field in the (field name list), the default is the field name. If there are multiple fields, the default is the first field name in the field name list. You can also use show index from table name; To see

(3) How to delete a unique key constraint

ALTER TABLE Table name DROP INDEX Uniqueness constraint name;
#Note: if you forget the name, you can use "show index from table name" see

2.5 foreign key constraint: foreign key

Foreign key characteristics
1. Foreign key constraint is to ensure the reference integrity between one or two tables. Foreign key is to build the reference 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. You can also specify a foreign key constraint name.
3. When creating a foreign key constraint, the system will establish a corresponding common 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 general 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 master table must exist first.
• 2. A table can create multiple external constraints.
• 3. Only key columns (primary key, unique key and foreign key) can be referenced in the primary table from the foreign key column of the secondary table.
• 4. The foreign key column of the slave table and the referenced column of the master table may have different names, but the data type must be the same.

Foreign key constraints have five constraint levels:

• Cascade method: when updating / deleting records on the parent table, update/delete the matching records of the child table synchronously.
• Set null method: when updating / deleting records on the parent table, set the column of matching records on the child 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 on the candidate key corresponding to the parent table is not allowed.
• Restrict mode: the same as no action, it is to check the foreign key constraints immediately. Note: if no grade is specified, it is equivalent to 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 creating tables

create table [Data name.]Table name(
	Field name 1 data type  primary key ,
	Field name 2 data type[ unique key],
	....,
    foreign key (From table field) references Main table name(Main table field) [on update Foreign key constraint level][ on delete Foreign key constraint level[
    #Foreign keys can only be specified individually after the list of all fields
);

create table [Data name.]Table name(
    Field name 1 data type,
    Field name 2 data type,
	....,
    primary key(Composite primary key field list),#If it is a composite primary key, you need to specify it in this form after the list of all fields. You cannot add a primary key directly after the field
    unique key(Composite unique field list),#If it is a compound unique key, you need to specify it in this form after all field lists. You cannot add a unique key directly after the field
    foreign key (From table field) references Main table name(Main table field) [on update Foreign key constraint level][ on delete Foreign key constraint level[
    #Foreign keys can only be specified individually after the list of all fields
);

(2) Specify foreign key constraint after creating table

alter table Table name add [constraint Constraint name] foreign key (Field name from table) references Main table name(Name of the referenced field in the main table) [on update xx][on delete xx];

(3) Delete foreign key constraint

ALTER TABLE Table name DROP FOREIGN KEY Foreign key constraint name;
#View constraint nameselect * from information_ schema. table_ constraints WHERE table_ Name = 'table name';
#Deleting the foreign key constraint will not delete the corresponding index. If you need to delete the index, you need to use the ALTER TABLE table name DROP INDEX index index name;
#View the index name show index from table name;

2.6 non NULL constraint: Not NULL

The Not NULL non NULL constraint specifies that a field cannot be empty.

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

create table [Data name.]Table name(
	Field name 1 data type  primary key ,
	Field name 2 data type[ unique key] [not null],
	....,
    foreign key (From table field) references Main table name(Main table field) [on update Foreign key constraint level][ on delete Foreign key constraint level[
    #Foreign keys can only be specified individually after the list of all fields
);

create table [Data name.]Table name(
    Field name 1 data type[ not null],
    Field name 2 data type[ not null],
	....,
    primary key(Composite primary key field list),#If it is a composite primary key, you need to specify it in this form after the list of all fields. You cannot add a primary key directly after the field
    unique key(Composite unique field list),#If it is a compound unique key, you need to specify it in this form after all field lists. You cannot add a unique key directly after the field
    foreign key (From table field) references Main table name(Main table field) [on update Foreign key constraint level][ on delete Foreign key constraint level[
    #Foreign keys can only be specified individually after the list of all fields
);

(2) After creating a table, specify a field that is not empty

ALTER TABLE Table name MODIFY Field name data type NOT NULL [default [default];
#If the default value constraint is set in this field, write it again along with it, otherwise the default value constraint will be lost

(3) Cancel a non empty field after creating a table

ALTER TABLE Table name MODIFY Field name data type[ default [default];
#If the default value constraint is set in this field, write it again along with it, otherwise the default value constraint will be lost

2.7 Default value constraint: Default

(1) Specify a default constraint for a field when creating a table

create table [Data name.]Table name(
	Field name 1 data type  primary key ,
	Field name 2 data type[ unique key] [not null] [default [default],
	....,
    foreign key (From table field) references Main table name(Main table field) [on update Foreign key constraint level][ on delete Foreign key constraint level[
    #Foreign keys can only be specified individually after the list of all fields
);

create table [Data name.]Table name(
    Field name 1 data type[ not null] [default [default],
    Field name 2 data type[ not null] [default [default],
	....,
    primary key(Composite primary key field list),#If it is a composite primary key, you need to specify it in this form after the list of all fields. You cannot add a primary key directly after the field
    unique key(Composite unique field list),#If it is a compound unique key, you need to specify it in this form after all field lists. You cannot add a unique key directly after the field
    foreign key (From table field) references Main table name(Main table field) [on update Foreign key constraint level][ on delete Foreign key constraint level[
    #Foreign keys can only be specified individually after the list of all fields
);

(2) Specify the default value constraint of a field after creating a table

ALTER TABLE Table name MODIFY Field name data type[ default [default][ NOT NULL];
#If the field was originally set with a non NULL constraint, write it again along with it, otherwise the non NULL constraint will be lost

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

ALTER TABLE Table name MODIFY Field name data type[ NOT NULL];
#If the field was originally set with a non NULL constraint, write it again along with it, otherwise the non NULL constraint will be lost

2.8 self increasing constraint: auto_increment

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

(1) Specify self growing columns when creating tables

create table [Data name.]Table name(
	Field name 1 data type  primary key auto_increment,
	Field name 2 data type[ unique key] [not null] [default [default],
	....
);

or
create table [Data name.]Table name(
	Field name 1 data type  primary key ,
	Field name 2 data type[ unique key  not null] auto_increment,
	....
);

(2) Specify self growth column after creating table

alter table [Data name.]Table name modify Self incrementing field name data type auto_increment;

(3) How to delete a self increasing constraint

alter table [Data name.]Table name modify Self incrementing field name data type;

3.DML (constraint)

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

When adding data, for auto increment column:

insert into [Database name.]Table name values(Value list)；#In the value list, the corresponding self incrementing column can be assigned null and 0

insert into [Database name.]Table name(Partial field list) values(Value list)；#Self incrementing columns can be added without writing in (partial field list)

• 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 [Database name.]Table name values(Value list)；#In the value list, the corresponding default value column. If you want to use the default value, use default

insert into [Database name.]Table name(Partial field list) values(Value list)；#Corresponding to the default value column, if you want to use the default value, you can leave it blank in (partial field list)

Modify data:

update [Database name.]Table name set Field name 1 = Value 1, Field name 2 = Value 2... [ where Conditions]; #Corresponding to the default value column, if you want to use the default value, write the field name = default

4. Functions in MySQL

There are two kinds of SQL functions: single line function and multi line function.

4.1 single line function

Features of single line function:

• Only one row is transformed, and each row returns a result
• Can be nested
• A parameter can be a field or a value or an expression

4.1.1 string function

function	usage
CONCAT(S1,S2,......,Sn)	Connect S1, S2, SN is a string
CONCAT_WS(s, S1,S2,......,Sn)	Same as CONCAT(s1,s2,...) Function, but add s between each string
CHAR_LENGTH(s)	Returns the number of characters in the string s
LENGTH(s)	Returns the number of bytes of string s, which is related to the character set
INSERT(str, index , len, instr)	Replace the string str with the string instr from the index position and the substring of len characters in length
UPPER(s) or UCASE(s)	Converts all letters of the string s to uppercase letters
LOWER(s) or LCASE(s)	Converts all letters of the string s to lowercase
LEFT(s,n)	Returns the leftmost n characters of string s
RIGHT(s,n)	Returns the rightmost n characters of string s
LPAD(str, len, pad)	Fill the leftmost part of str with the string pad until the length of str is len characters
RPAD(str ,len, pad)	Fill the rightmost part of str with the string pad until the length of str is len characters
LTRIM(s)	Remove the space to the left of the string s
RTRIM(s)	Remove the space to the right 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 from the beginning and end of string s
TRIM([LEADING]s1 FROM s)	Remove s1 at the beginning of string s
TRIM([TRAILING]s1 FROM s)	Remove s1 at the end of string s
REPEAT(str, n)	Returns 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)	Returns len characters from the index position of string s

 

• Example 1: case control function

function	result
LOWER('SQL Course')	sql course
UPPER('SQL Course')	SQL COURSE

Such functions change the case of characters.

• Example 2: character control function

function	result
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 function

function	usage
ABS(x)	Returns the absolute value of x
CEIL(x)	Returns the smallest integer value greater than x
FLOOR(x)	Returns the maximum integer value less than x
MOD(x,y)	Returns the modulus of x/y
RAND()	Return random values from 0 to 1
ROUND(x,y)	Returns the value of the parameter x rounded to y decimal places
TRUNCATE(x,y)	Returns the result of truncating the number x to y decimal places
SQRT(x)	Returns the square root of x
POW(x,y)	Returns the y-th power of x

Example 1: ROUND: ROUND

ROUND(45.926, 2)     -->  45.93

• Example 2: TRUNCATE: TRUNCATE

TRUNCATE(45.926)      --> 45

• Example 3: MOD: remainder

MOD(1600, 300)	 --> 100

Function date

function	usage
Current() or CURRENT_DATE()	Returns the current date
CURTIME() or CURRENT_TIME()	Returns the current time
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	Returns the current system date and time
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)	Return specific time value
WEEK(date) / WEEKOFYEAR(date)	Returns the week ordinal of the year
DAYOFWEEK()	Return to the day of the week. Note: Sunday is 1 and Monday is 2,... Saturday is 7
WEEKDAY(date)	Return to 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)	Return the date interval of date1 - date2 / return the time interval of time1 - time2
DATE_ADD(datetime, INTERVAL expr type)	Returns the date and time of the INTERVAL period different 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)	str is parsed according to the string fmt to a date

Of which:

(1)DATE_ADD(datetime,INTERVAL expr type)

Expression type:

Parameter type	Parameter type
YEAR	YEAR_MONTH
MONTH	DAY_HOUR
DAY	DAY_MINUTE
HOUR	DAY_SECOND
MINUTE	HOUR_MINUTE
SECOND	HOUR_SECOND
	MINUTE_SECOND

give an example:

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   #Can be negative
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #Single quotation mark required

 

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

Formatter	explain	Formatter	explain
%Y	Four digits indicate the year	%y	Two digit year
%M	Month name means month (January,...)	%m	Two digit numbers indicate the month (01, 02, 03...)
%b	Abbreviated month name (Jan., Feb.,...)	%c	Numbers represent months (1, 2, 3,...)
%D	English suffix indicates the number of days in a month (1st,2nd,3rd,...)	%d	Two digits indicate the number of days in the month (01,02...)
%e	The number of days in a month (1, 2, 3, 4, 5,...)
%H	Two digits represent decimals, 24-hour system (01,02..)	%h and% I	Two digit number indicates hour, 12 hour system (01,02..)
%k	Hours in digital form, 24-hour system (1,2,3)	%l	Digital form represents hour, 12 hour system (1,2,3,4...)
%i	Two digits represent minutes (00,01,02)	%S and% s	Two digits represent seconds (00,01,02...)
%W	Name of the week in the week (Sunday...)	%a	Abbreviation of week in a week (Sun., Mon.,Tues.,..)
%w	The number of days in the week (0=Sunday,1=Monday...)
%j	The number of days in the year is expressed in 3 digits (001002...)	%U	Number the week ordinal of the year, (1,2,3.) Sunday is the first day of the week
%u	Number the week ordinal of the year, (1,2,3.) Monday is the first day of the week
%T	24-hour system	%r	12 hour system
%p	AM or PM	%%	Represents%

4.1.4 process function

function	usage
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] END	Equivalent to Java if else if... else...
CASE expr WHEN constant value 1 THEN value 1 WHEN constant value 1 THEN value 1 [else value n] END	Equivalent to Java switch case...

Example 1:

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "Annual salary"
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     "net salary"
FROM   employees;

4.1.5 other functions

function	usage
database()	Returns the current database name
version()	Returns the current database version
user()	Returns the current login user name
password(str)	Returns the encrypted version of the string str, a 41 bit long string
md5(str)	Returns the md5 value of the string str, which is also an encryption method

4.2 grouping function

• Meaning of grouping function:
  The grouping function acts on a group of data and returns a value to a group of data.
  

Group function type
avg(): average function
sum(): summation function
max(): maximum function
min(): minimum value function
Count (): counting function

(1) You can use avg() and sum() functions for numeric data

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

(2) You can use the min and max functions for 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 whose expr is not empty

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

5. Association query

Seven results of association 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?

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

1. Internal connection: realize A ∩ B

select Field list
from A surface inner join B surface
on Association condition
where Other clauses such as;

or

select Field list
from A surface , B surface
where Association condition and Other clauses such as;

Code example:

#Query the employee's name and the name of his department
#The employee's name is in t_employee
#The name of the Department is in t_department
SELECT ename "Employee's name",dname "Department name"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did

SELECT ename "Employee's name",dname "Department name"
FROM t_employee , t_department
WHERE t_employee.did = t_department.did


#Query the name of a male employee with a salary of more than 20000 and the name of his department
SELECT ename "Employee's name",dname "Department name"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
WHERE salary>20000 AND gender = 'male'

2. Left outer connection

#The query result is A
select Field list
from A surface left join B surface
on Association condition
where Other clauses such as;

#Achieve a - a ∩ B
select Field list
from A surface left join B surface
on Association condition
where Associated field is null and Other clauses such as;

Code example:

#Query the names of all employees and the names of their departments
SELECT ename "Employee's name",dname "Department name"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did


#Query all employees without Department
SELECT ename "Employee's name",dname "Department name"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

3. Right outer connection

#The query result is B
select Field list
from A surface right join B surface
on Association condition
where Other clauses such as;

#Realize B - a ∩ B
select Field list
from A surface right join B surface
on Association condition
where Associated field is null and Other clauses such as;

Code example:

#Query all departments and employee information under all departments
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did

#Query departments that do not have employees
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

4. Using union instead of full external connection

#The query result is A ∪ B
#Use A outside the left and B outside the right
select Field list
from A surface left join B surface
on Association condition
where Other clauses such as

union 

select Field list
from A surface right join B surface
on Association condition
where Other clauses such as;



#Implement A ∪ B - A ∩ B or (A - A ∩ B) ∪ (B - A ∩ B)
#Use left outer (a - a ∩ b) union right outer (B - A ∩ b)
select Field list
from A surface left join B surface
on Association condition
where Associated field is null and Other clauses such as

union

select Field list
from A surface right join B surface
on Association condition
where Associated field is null and Other clauses such as

Code example:

#Query all employees and all departments, including departments without employees and employees without departments
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


#Query employees without departments and all departments without employees

#Employees without Department
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

UNION 

#All departments without employees
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 tables of two associated queries are the same table, which can be virtualized into two tables by taking aliases

select Field list
from Table name alias 1 inner/left/right join Table name alias 2
on Alias 1.Associated field = Associated field of alias 2
where Other conditions

Code example:

#Query the number, name and salary of the employee and the number, name and salary of his leader
#These data are all in the employee table
#Put t_ The employee table is regarded as both the employee table and the leader table
#Leadership table is a virtual concept. We can use alias to make it virtual
SELECT emp.eid "Employee number",emp.ename "Employee's name" ,emp.salary "Employee's salary",
	mgr.eid "Leader's number" ,mgr.ename "Name of leader",mgr.salary "Salary of leaders"
FROM t_employee emp INNER JOIN t_employee mgr
#t_ Employee: if emp. Is used, Represents the of the employee table
#t_employee mgr: if mgr is used, It means the leadership table
ON emp.mid = mgr.eid

#Do not add "" to the alias of the table to alias the column. You can use "" instead of "" for the alias of the column, but avoid including special symbols such as spaces.

6. Six major sub clauses of the select statement

Six major orders of select statements:

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

Code example:

#Query the number of boys in each department, and display those with more than 5 people in descending order,
#Only 10 items can be displayed on each page. I want page 2
SELECT did,COUNT(*) "Number of people"
FROM t_employee
WHERE gender = 'male'
GROUP BY did
HAVING COUNT(*)>5
ORDER BY Number of people DESC
LIMIT 10,10

6.1 group by and grouping function

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

SELECT column, group_function(column)
FROM table
[WHERE	condition]
[GROUP BY	group_by_expression];

Clear: WHERE must be placed behind FROM and before GROUP BY

In the SELECT list, all columns 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 ;

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 ;

Group using multiple columns

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 ;

What is the difference between having and where?

• (1) where is the condition for filtering from the table, while having is the condition for filtering again from the statistical results
• (2) where cannot be followed by "grouping / aggregation function", while having can be followed by

#Departments and average wages with average wages higher than 8000 in statistical departments
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000 #error
GROUP BY department_id;;

#Departments and average wages with average wages higher than 8000 in statistical departments
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary)>8000 ;;

#Query the number of boys in each department, and display those with more than 5 people in descending order,
#Only 10 items can be displayed on each page. I want page 2
SELECT did,COUNT(*) "Number of people"
FROM t_employee
WHERE gender = 'male'
GROUP BY did
HAVING COUNT(*)>5
ORDER BY Number of people DESC
LIMIT 10,10

summary

In this section, you will learn about constraints and indexes, functions in MySQL, associated queries, and the 6-character sentences of the Select statement. The most important one is to master the 6-character sentences of the Select statement. These are the most basic knowledge in the database. Mastering them carefully will be of great help to the later study.