CREATE DATABASE IF NOT EXISTS cms DEFAULT CHARACTER SET utf8;
USE cms;
-- Administrator table cms_admin
CREATE TABLE cms_admin(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'admin@qq.com',
role ENUM('General administrator','Super administrator') DEFAULT 'General administrator'
);
INSERT cms_admin(username,password,email,role) VALUES('admin','admin','admin@qq.com',2);

INSERT cms_admin(username,password) VALUES('king','king'),

('wheat','maizi'),

('queen','queen'),

('test','test');

-- Create classification table cms_cate
CREATE TABLE cms_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(200) NOT NULL DEFAULT ''
);

INSERT cms_cate(cateName,cateDesc) VALUES('home news','Focus on today's hottest domestic news'),
('international news ','Focus on today's hottest international news'),
('sports news','Focus on today's hottest sports news'),
('Military News','Focus on today's hottest military news'),
('Educational news','Focus on today's hottest Education News');

-- Create a news table cms_news
CREATE TABLE cms_news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(50) NOT NULL UNIQUE,
content TEXT,
clickNum INT UNSIGNED DEFAULT 0,
pubTime INT UNSIGNED,
cId TINYINT UNSIGNED NOT NULL COMMENT 'The category to which the news belongs corresponds to the category in the classification table id',
aId TINYINT UNSIGNED NOT NULL COMMENT 'Which administrator published it corresponds to the administrator in the administrator table id'
);
INSERT cms_news(title,content,pubTime,cId,aId) VALUES('AirAsia passenger plane lost contact with search and rescue, but the wireless cable has not sent a distress signal','A Malaysia Asian Airlines passenger plane carrying 155 passengers lost contact with the air traffic control tower on the way from Indonesia to Singapore on the morning of 28.',1419818808,1,2),
('Four new subway lines opened in Beijing to welcome the first guest','12 In the early morning of June 28, with the opening and trial operation of the second phase of Beijing Metro Line 6, line 7, the west section of line 15 and the east section of line 14, the operating mileage of rail transit in Beijing will add another 62 kilometers, reaching a total of 527 kilometers. At about 5 a.m. that day, the Chinatown transfer station of Beijing Metro Line 7 welcomed the first passenger to open the new line.',1419818108,2,1),
('***','Beijing News (reporter Xu Luyang) (micro-blog))APEC ***',1419818208,3,2),
('depth-Zeng Xuelin: pelan, don't repeat the mistakes of Camacho','12 June 25 is the 85th birthday of former national football coach Zeng Xuelin. It coincides with Christmas. The old man with uremia spent his birthday with a Christmas hat. Previously, Tencent reporter had an exclusive interview with Zeng Xuelin. Although he had retired for many years, the old man was still concerned about Chinese football and worried about the national football team. For the recent coaches of the national football team, he only appreciated Gao Hongbo. For the national football team who will play in the Asian Cup, the old man hopes that pelan will not repeat the mistakes of Camacho',1419818308,2,4),
('domestic JAD-1 Pistol rack put into use"Submachine gun"','Recently, JAD-1 The multifunctional pistol rack has passed the inspection of the special police equipment quality supervision and inspection center of the Ministry of public security and has been officially put into production. This multifunctional gun rack is made by Jing'an dun(Beijing)Developed by the police equipment Co., Ltd., it was tried out by the public security special police detachment of Jiangmen City, Guangdong Province, and was highly praised.',1419818408,4,4),
('MAIZI college won Sina Education Award','The biggest occupation of Maizi College IT Education platform, won the prize',1419818508,1,5),
('Wheat college won Tencent Education Award','The biggest occupation of Maizi College IT Education platform, won the prize',1419818608,1,5),
('New courses of wheat college launched','Wheat College PHP The course will go online soon. Come and sign up to learn, little buddy',1419818708,1,5);

-- Create identity table provinces
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(proName) VALUES('Beijing'),
('Shanghai'),
('Shenzhen'),
('Guangzhou'),
('Chongqing');

-- Create user table cms_user
CREATE TABLE cms_user(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'user@qq.com',
regTime INT UNSIGNED NOT NULL,
face VARCHAR(100) NOT NULL DEFAULT 'user.jpg',
proId TINYINT UNSIGNED NOT NULL COMMENT 'User's Province'
);

INSERT cms_user(username,password,regTime,proId)

VALUES('Zhang San','zhangsan',1419811708,1),
('Zhang Sanfeng','zhangsanfeng',1419812708,2),
('Zhang Ziyi','zhangsan',1419813708,3),
('long','long',1419814708,4),
('ring','ring',1419815708,2),
('queen','queen',1419861708,3),
('king','king',1419817708,5),
('blek','blek',1419818708,1),
('rose','rose',1419821708,2),
('lily','lily',1419831708,2),
('john','john',1419841708,2);

1, Single table query

SELECT field FROM table name WHERE condition
cms database and several tables are established

The simplest query statement is SELECT * FROM cms_admin; It represents all query fields, that is, all columns in the query table. The effect is the same as SELECT cms_admin. FROM cms_admin;

The second method is used because there are multiple tables in the same database. If you query a field, it may exist in multiple tables, so you need to specify the table name in front

SELECT id,username FROM cms_admin;

If you want to query only a few fields, the fields need to be separated by commas. When querying a column, there must be at least one column

SELECT id,username,role FROM cms.cms_admin;

It can also be a library name Table name

SELECT cms_admin.username,cms_admin.id FROM cms.cms_admin;

You can add the table name before the field and the library name before the table name

 SELECT id,username FROM cms_admin [AS] a; among as Can be omitted

Alias the table name a

SELECT a.id,a.username,a.role FROM cms_admin AS a;

Alias fields

SELECT id AS 'number',username AS 'user name',role AS 'role' FROM cms_admin;

Aliasing tables and fields

SELECT a.id AS i,a.username AS u,a.role AS r FROM cms.cms_admin AS a;

2, Conditional query

Query the related fields of the first record
SELECT id,username,email FROM cms_user WHERE id=1;

Query user name is king Related fields of
SELECT id,username,email FROM cms_user WHERE username='King';

query id Data not equal to 1
SELECT id,username,email FROM cms_user WHERE id!=1;

perhaps SELECT id,username,email FROM cms_user WHERE id<>1;

Add fields to existing tables

ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;

INSERT cms_user(username,password,regTime,proId,age) VALUES('test1',123456,1419811708,1,NULL);
Added an age of null Value, the query value is null Data

SELECT * FROM cms_user WHERE age=NUll;

You can't find it directly in the form of equal sign
Empty set appears
You can only use < = > to query data equal to null value. This symbol has one more function than the equal sign, that is, to detect null value

SELECT * FROM cms_user WHERE age<=>NULL;

SELECT * FROM cms_user WHERE age IS NULL;

In addition to detecting null, < = > and = have the same effect

SELECT * FROM cms_user WHERE age<=>18;

SELECT * FROM cms_user WHERE age IS NOT NULL;

3, Range query

1,between and

SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;

2. Specify the set in or not in

Case does not affect query results

SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,15,100,1000);

SELECT * FROM cms_user WHERE proID IN(1,3);

SELECT * FROM cms_user WHERE username IN('king','queen','Zhang San','Zhang Ziyi');

SELECT * FROM cms_user WHERE username IN('King','Queen','Zhang San','Zhang Ziyi');

4, Fuzzy query

Matching character: LIKE | NOT LIKE
%: 0 represents one or more arbitrary characters
_: Represents 1 arbitrary character

SELECT * FROM cms_user WHERE username LIKE '%Zhang%';

Query Zhang's data
SELECT * FROM cms_user WHERE username LIKE 'Zhang%';

Query user name contains in These two letter data
SELECT * FROM cms_user WHERE username LIKE '%in%';

The percent sign represents any character, and all data can be queried
SELECT * FROM cms_user WHERE username LIKE '%';

Query the user with 3-digit user name, which is represented by three underscores
SELECT * FROM cms_user WHERE username LIKE '___';

The query user name has 4-digit data
SELECT * FROM cms_user WHERE username LIKE '___';

The second digit of the query is i，I don't know if there are any characters after it, so it is represented by a percent sign
SELECT * FROM cms_user WHERE username LIKE '_i%';

add not It has the effect of taking the opposite

5, Logical operator

AND | OR
and requires two conditions to be met at the same time | or means that one of the two conditions can be met

SELECT * FROM cms_user WHERE username='king' AND password='king';
SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;

SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proID=3;

SELECT * FROM cms_user WHERE 5<=id<=10 AND username LIKE '____';

SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';

 SELECT * FROM cms_user WHERE username LIKE 'Zhang%' OR proID IN(2,4);

6, Group query GROUP BY

Group those with the same field value, and only the first record in the group will be displayed after grouping

SELECT * FROM cms_user GROUP BY proID;

Dynamically add fields: Gender
ALTER TABLE cms_user ADD sex ENUM('male','female','secrecy');

UPDATE cms_user SET sex='male' WHERE id IN(1,3,5,7,9);
UPDATE com_user SET sex='female' WHERE id IN(2,4,6,8,10);
UPDATE cms_user SET sex='secrecy' WHERE id=11;

Group by gender field

SELECT * FROM cms_user GROUP BY sex;

You can also know the location of each field according to the location
SELECT * FROM cms_user GROUP BY 7

Similarly, it can be seen that according to age, it is divided into
SELECT * FROM cms_user GROUP BY 9;

Group by multiple fields
SELECT * FROM cms_user GROUP BY sex,proID;

First by gender, then by province

If there are conditions, first filter out some results through where, and then display them according to groups
Users with query number greater than or equal to 5 are grouped according to sex

SELECT * FROM cms_user WHERE id>5 GROUP BY sex;

7, Grouping query with aggregate function

Query the user name details grouped by gender and the name of the first record in the group id and sex
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

View table by province id Gender, registration time and user name details after grouping
SELECT proID,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) FROM cms_user GROUP BY proID;

UPDATE cms_user SET age=11 WHERE id=1;

Changed data


UPDATE cms_user SET age=11 WHERE id=1;
UPDATE cms_user SET age=21 WHERE id=2;
UPDATE cms_user SET age=33 WHERE id=3;
UPDATE cms_user SET age=44 WHERE id=4;
UPDATE cms_user SET age=25 WHERE id=5;
UPDATE cms_user SET age=77 WHERE id=6;
UPDATE cms_user SET age=56 WHERE id=7;
UPDATE cms_user SET age=88 WHERE id=8;
UPDATE cms_user SET age=12 WHERE id=9;
UPDATE cms_user SET age=32 WHERE id=10;
UPDATE cms_user SET age=65 WHERE id=11;

Query number, gender, user name details and the total number of people in the group are grouped by sex

SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalusers FROM cms_user GROUP BY sex;

If the name is not redefined, the field length is too long

All records in the statistical table
SELECT COUNT(*) AS totalusers FROM cms_user;

null value records are not counted when count (field) is used
SELECT COUNT(id) AS totalusers FROM cms_user;

Query the number, gender, user name details, total number of people in the group, maximum age, minimum age, average age and total age in the group, and group them by gender

SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalusers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex;

SELECT id,sex,
COUNT(*) AS totalusers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user GROUP BY sex WITH ROLLUP;

WITH ROLLUP Each aggregate function will be summarized at the end

SELECT id,sex,
COUNT(*) AS totalusers,
MAX(age) AS max_age,
MIN(age) AS min_age,
SUM(age) AS sum_age
FROM cms_user GROUP BY sex;

8, having secondary query statement

The having statement is only useful if it is combined with GROUP BY

having statement performs secondary filtering on grouping results
Can only be used after grouping
Query gender and user name details. The total number of people in the group, the maximum age and the total age are grouped according to gender

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalusers,
MAX(age),
SUM(age) 
FROM cms_user GROUP BY sex;

Conduct secondary screening on the results after grouping, and screen the groups with more than 2 people in the group

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalusers,
MAX(age),
SUM(age) 
FROM cms_user GROUP BY sex HAVING COUNT(*)>2;

At this point, it can be seen that the data of the security group has been filtered out

The number of people in the query group is greater than 2 and the maximum age is greater than 60

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalusers,
MAX(age),
SUM(age) 
FROM cms_user GROUP BY sex HAVING COUNT(*)>2 AND MAX(age)>60;

Note: alias cannot be used in secondary query, only aggregate function can be used

Now when there are conditions in grouping, you should write the conditions before grouping

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalusers,
MAX(age),
SUM(age) 
FROM cms_user 
WHERE id>2
GROUP BY sex HAVING COUNT(*)>2 AND MAX(age)>60;

9, ORDER BY sorts query results

The default is by id Sort in ascending order
SELECT * FROM cms_user ORDER BY id;
Or SELECT * FROM cms_user ORDER BY id ASC;

In descending order DESC
SELECT * FROM cms_user ORDER BY id DESC;
The same here id Position 1 can be used instead
SELECT * FROM cms_user ORDER BY 1 DESC;

 In ascending order of age
SELECT * FROM cms_user ORDER BY age ASC;

Sort by multiple fields, ascending by age and id In descending order
SELECT * FROM cms_user ORDER BY age ASC,id DESC;

SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age 
FROM cms_user 
WHERE id>=2 
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC;

SELECT * FROM cms_user ORDER BY RAND();
Randomly extract records, and the effect of each arrangement is different

10, Limit the number of displays through limit

First 3 records in query table
SELECT * FROM cms_user LIMIT 3;

The first 5 records will be displayed after descending order
SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

The first record in the query table
SELECT * FROM cms_user LIMIT 0,1;

SELECT * FROM cms_user LIMIT 1,1;
The previous 1 indicates that the second 1 is offset by 1 relative to the first data, and 1 record is displayed on each page

SELECT * FROM cms_user LIMIT 0,5;
next page
SELECT * FROM cms_user LIMIT 5,5;

11, Update and delete order by and limit

Update the 4-digit user name to make it old-3
UPDATE cms_user SET age=age-3 WHERE username LIKE '____';

Update the first three records to make them old+10
 When updating or deleting, limit Function can only have one parameter and cannot set offset
UPDATE cms_user SET age=age+10 LIMIT 3;

according to id In descending order, update the first three
UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3

DELETE FROM cms_user WHERE sex='male' ORDER BY age DESC LIMIT 1;

12, Connection query

1. What is a join query?

It means that two or more tables are connected according to a certain condition to select the required data. Join query is used to query two or more tables at the same time. When fields with the same meaning exist in different tables, these tables can be connected through this field

2. Internal connection query

JOIN|CROSS JOIN INNER JOIN
Connection condition via ON
Displays records of composite join conditions in two tables
Take two tables as an example

SELECT id,username,proID FROM cms_user;
But the provinces here use id We don't know what each province represents
 And the name of this province comes from provinces Province table

SELECT id,username,proID FROM cms_user,provinces;
An error is reported when querying through this statement because there are errors in both tables id This field
1052 - Column 'id' in field list is ambiguous

SELECT cms_user.id,username,proName FROM cms_user,provinces;

But this is not the result we want. Everyone corresponds to 5 provinces, so we need to query through internal connection

SELECT cms_user.id,username,proName FROM cms_user,provinces WHERE cms_user.proID=provinces.id;

This method can be realized by internal connection

-- query cms_user In the table id,username,email,sex
-- query provinces surface proName

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proID=p.id;

The effect is equivalent to:

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
CROSS JOIN provinces AS p
ON u.proID=p.id;

Also equivalent to

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id;

-- query cms_user id,username,sex
-- query provinces proName
-- with the understanding that cms_user Users whose gender is male

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='male';

– grouped by proName

SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='male'
GROUP BY p.proName;

– filter the grouping results and select the group with number > = 1

SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='male'
GROUP BY p.proName
HAVING COUNT(*)>=1;

– in ascending order of id

SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='male'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC;

– limit the number of displays to the first 2

SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='male'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY id ASC
LIMIT 2;

DESC cms_news;

Where cID corresponds to the id in the classification table, and aID corresponds to the id in the administrator

--　query cms_news Medium id,title,
--  query cms_cate Medium cateName

SELECT n.id,n.title,c.cateName
FROM cms_news AS n
JOIN cms_Cate AS c
on n.cID=c.id;

-- cms_news id ,title
-- cms_cate cateName
-- cms_admin username,role

SELECT n.id,n.title,c.cateName,a.username,a.role
FROM cms_news AS n
JOIN cms_Cate AS c
on n.cID=c.id
JOIN cms_admin AS a
ON n.aID=a.id;

3. External connection query

Insert a piece of invalid data that does not conform to the rules and cannot be found during internal connection
We can see that there are only five provinces

In order to test, we use CMS again_ Insert a piece of data beyond the range into the user table
INSERT cms_user(username,password,regTime,proID) VALUES('TEST1','TEST2',1231203,20);

When the internal connection query is performed again, we find that there is no way to find the secondary record


The left outer surface is dominated by the left table, and the right outer surface is dominated by the right table

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.proID=p.id;

The table before LEFT is the main table. First find out the contents of the main table, and replace the values outside the range in the right table with null
The query result is:

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
LEFT JOIN cms_user AS u
ON u.proID=p.id;

Exchange the order, and the provinces becomes the main table

Since province is the main table, you can query the contents of the main table first, and then query according to the rules. There is no record of TEST1

Similarly, the principle of right outer join is the same. The table after right join is used as the main table

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
RIGHT JOIN cms_user AS u
ON u.proID=p.id;

13, Foreign key operation

A foreign key is a special field of a table. The referenced table is the main table, and the table of the field where the foreign key is located is a sub table. The principle of setting foreign keys is to rely on the primary key of existing tables in the database. The function of the foreign key is to establish the association between the table and its parent table. When you operate on a record in the parent table, the corresponding information in the child table will change accordingly.

The function of foreign keys is to maintain the consistency and integrity of data.

be careful:
(1) Child tables and parent tables must use the same storage engine, and temporary tables are prohibited;
(2) The storage engine of the data table can only be INNODB;
(3) Foreign key columns and reference columns must have similar data types. The length of the number or whether there is a sign must be the same; The character length can be different

Create master table:
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depname VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;

Insert data:
INSERT department(depname) VALUES('Teaching Department'),('Marketing Department'),('Operation Department'),('Supervision department');

Create employee table

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depID TINYINT UNSIGNED
)ENGINE=INNODB;

insert data

INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('Zhang San',3),
('Li Si',4),
('Wang Wu',1);

SELECT e.id,e.username,p.depname
FROM department as p
JOIN employee as e
ON e.depID=p.id;

DELETE FROM department WHERE depname = 'supervision department';
But there are still people in this department under the employee list

But then there are some problems. Before we dissolve the Department, we should arrange the original employees of the Department first

When there are fields associated with the main table in the attached table, some restrictions should be made on the attached table - foreign key constraint operation

Therefore, the personnel of department 4 in the attached table should be deleted before the supervision department in the main table can be deleted

If you want to ensure this effect, you should use the appearance

Main table
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depID TINYINT UNSIGNED
FOREING KEY(depID) REFERENCES department(id)
)ENGINE=INNODB;
 

Employee table

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depID TINYINT UNSIGNED
FOREING KEY(depID) REFERENCES department(id)
)ENGINE=INNODB;

INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('Zhang San',3),
('Li Si',4),
('Wang Wu',1);

At this time, if we still want to delete the data in the Department table, an error will be reported

DELETE FROM department WHERE id=1;

1451 - Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))

With foreign key constraints, employees in this department must be deleted first if they want to delete this department

Delete the person belonging to department 1 in the employee table

Only employees who delete Department 1 first can delete Department 1

mysql> DELETE FROM employee WHERE depID=1;
Query OK, 1 row affected

mysql> DELETE FROM department WHERE id=1;
Query OK, 1 row affected

Inserting invalid data can succeed without foreign key constraints before, but now with foreign key constraints, invalid data insertion fails

14, Add or remove foreign keys

Add a name constraint EMP to the foreign key_ fk_ dep

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB;

Let's look at the definition of the table

Delete foreign key
ALTER TABLE table name DROP FOREIGN KEY name

ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;

If the foreign key is deleted successfully, all restrictions will be eliminated. At this time, you can delete the data of the Department at will

Add foreign keys dynamically

ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depID) REFERENCES department(id);

CASCADE: delete or update from the parent table and automatically delete and update the matching data in the child table
SET NULL: delete or update data from the parent table and set the foreign key column of the child table to NULL. However, if this option is used, it must be ensured that NOT NULL is not specified in the child table

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE
)ENGINE=INNODB;

At this time, records related to primary keys can be deleted synchronously between parent and child tables

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB;

At this time, the Department id can be updated synchronously between the parent and child tables

UPDATE department SET id=id+10;

The id in the employee table is also updated

After the records in the parent table are deleted, the data in the main table will automatically become NULL

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
)ENGINE=INNODB;

UPDATE department SET id=id+10;

15, Joint query

UNION will remove the same records. UNION ALL is simply merged together

SELECT username FROM employee UNION SELECT username FROM cms_user;

union eliminated duplicate records

SELECT username FROM employee UNION ALL SELECT username FROM cms_user;

Merge query results only

You must ensure that the number of fields queried by the two query statements is the same

SELECT id,username FROM employee UNION ALL SELECT username,age FROM cms_user;

16, Subquery

Subquery is to put one query statement in another query statement. The query result of the inner query statement can provide conditions for the outer query statement.
Issuing sub queries?

SELECT id,username FROM employee WHERE depID IN(SELECT id FROM department);

INSERT employee(username,depId) VALUES('testtest',8);
SELECT id,username FROM employee WHERE depID NOT IN(SELECT id FROM department);

Create student form and scholarship form

--　Create student form student
-- id username score
CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20)  NOT NULL UNIQUE,
score TINYINT UNSIGNED
);

INSERT student(username,score) VALUES('king',95),
('king1',35),
('king2',45),
('king3',55),
('king4',65),
('king5',75),
('king6',80),
('king7',90),
('king8',25);

-- Create Scholarships scholarship
-- id ,level

CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
level TINYINT UNSIGNED
);
INSERT scholarship(level) VALUES(90),(80),(70);

Look up the students who won the first-class scholarship

First, check the score section of the first-class scholarship

SELECT level FROM scholarship WHERE id=1;
--You can see that the first-class scholarship requires a score greater than or equal to 90

SELECT id,username FROM student WHERE score>=90;

--One line problem solving:
SELECT id,username FROM student WHERE score>(SELECT level FROM scholarship WHERE id=1);

Inner queries must be placed in parentheses

There is no department with id=5 in the Department table, so we can use exists to query

If it exists, it returns true. If it is true, the result can be found and the outer statement can be executed

SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);

17, Subquery using any | home or ALL

Check the number and name of all students who have won the scholarship

SELECT id,username FROM student WHERE score>=ANY(SELECT level FROM scholarship);

SELECT id,username FROM student WHERE score>=SOME(SELECT level FROM scholarship);

--Query the information of students who have won the first-class scholarship
 SELECT id,username FROM student WHERE score>=ALL(SELECT level FROM scholarship);

--Check the information of students who have not received scholarships
SELECT id,username FROM student WHERE score<ALL(SELECT level FROM scholarship);

SELECT id,username FROM student WHERE score=ANY(SELECT level FROM scholarship);
--The effect is equivalent to
SELECT id,username FROM student WHERE score IN (SELECT level FROM scholarship);
--The query has 90 errors,80,70 The number and name of the student who scored

Write query results to a new table

Create a new table

CREATE TABLE test1 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
);

INSERT test1(id,num) SELECT id,score FROM student;

Or directly set the inserted data when creating a table

CREATE TABLE test2 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
)SELECT id,score FROM student;

Because the fields do not match, the result is that the num field is null. You can change the id field to num field

18, Regular expression query

-- ^Matches the beginning of the character
-- Query user name to t Start user
SELECT * FROM cms_user WHERE username REGEXP '^t';
-- $Matches the end of the string
SELECT * FROM cms_user WHERE username REGEXP 'g$';
-- .Represents any character
SELECT * FROM cms_user WHERE username REGEXP '.';
--All results can be queried

SELECT * FROM cms_user WHERE username REGEXP 'r..g';
--The effect is equivalent to
SELECT * FROM cms_user WHERE username LIKE 'r__g';

-- [Character set] [lto]
SELECT * FROM cms_user WHERE username REGEXP '[LTO]';

-- [^Character set] Not all the contents of the character set
SELECT * FROM cms_user WHERE username REGEXP '[^LTO]';

--Add some new records
INSERT cms_user(username,password,regTime,proId)
VALUES('lll','lll',138212349,2),
('ttt','lll',138212349,2),
('ooo','lll',138212349,2);

#Execute again
SELECT * FROM cms_user WHERE username REGEXP '[^LTO]';

All the records in the user name are l/o/t. you can't find them

SELECT * FROM cms_user WHERE username REGEXP '[a-k]';
--Appears in the user name a-k All the letters can be queried

--Query user name includes ng perhaps qu Data
SELECT * FROM cms_user WHERE username REGEXP 'ng|ku';

SELECT * FROM cms_user WHERE username REGEXP 'kin*';
--among n It can occur any time, 0 times, 1 times or more

#*Represents 0 or 1 or more characters before *

#+Represents 1 or more + preceding characters

SELECT * FROM cms_user WHERE username REGEXP 't+';
--username There must be t This character

#{n} Indicates that the preceding character appears n times
SELECT * FROM cms_user WHERE username REGEXP 'l{1}';
SELECT * FROM cms_user WHERE username REGEXP 'l{1,3}';
#Indicates the record of l letter appearing 1-3 times in the query user name