MySQL chapter 02: SQL basic operation

Core overview: in the development, we use SQL commands to operate the database and the data in the database. The common operations include the addition, deletion, modification and query of the database and tables. In this article, we will first learn how to use SQL statements to realize the basic operations of the database and tables.

Chapter one: understanding SQL

1.1 – what is SQL (understanding)

Structured Query LAnguage structured queryLanguage is a kind of command specification for operating database.

The role of 1.2-sql (understanding)

1. It is a query specification for all relational databases, which is supported by different databases.
2. General database operation language can be used in different databases.
3. Different database SQL statements have some differences

1.3-sql statement classification (understanding)

1. Data definition language (DDL data definition language), such as: building database, building table
2. Data manipulation language (DML)For example, adding, deleting and modifying records in the table
3. Data query language (DQL)For example, query operations in tables
4. Data control language (DCL), such as the setting of user permissions

1.4-sql syntax considerations (understanding)

1: Each statement ends with a semicolon if it is not required in sqlog.

2: SQL is not case sensitive, and the upper case and lower case are the same in keywords.

3: There are three kinds of notes:

Annotation grammar	explain
--Space	Single-Line Comments
/**/	multiline comment
#	This is the unique annotation method of MySQL

Chapter 2: DDL operation database

2.1 – create database (key)

Create data

Create database database name;

Judge whether the database already exists, and create the database if it does not exist

Create database if not exists database name;

Create a database and specify a character set

Create database database name character set;

demonstration:

--Create a database called db1
create database db1;
--If not, create a database called DB2
create database if not exists db2;
--Create database db3 with GBK character set
create database db3 character set gbk;

2.2 – view database (key)

View all databases

show databases;

View the specified database

Show create database name

demonstration:

--View all databases
show databases;
--View the database named db1
show create database db1;

2.3 – database modification (key points)

Modifying the character set of a database

Alter database name default character set character set;

demonstration:

ALTER DATABASE db1 character set utf8;

2.4 – delete database (key)

grammar

Drop database database name;

demonstration:

DROP DATABASE db1;

2.5 – using databases (key points)

View databases used

SELECT DATABASE();

Switch databases used

Use database name;

demonstration

select database();

Chapter 3: DDL operation table structure

The premise is to use a database first.

3.1 – creating tables (key points)

format

Create table name（
    Field name 1, field type 1,
    Field name 2 field type 2
);

Keyword description

• CREATEestablish
• TABLEsurface

3.2-mysql data type (key)

type

Date:TIMESTAMPtime stamp

demonstration

create table student (
    Id int, - integer
    Name varchar (20), -- string
    Birthday date -- there is no comma at the end
);

3.3 – view table (key)

View all tables in a database

SHOW TABLES;

View table structure

Desc table name

View the SQL statement that created the table

Show create table name

3.4 – quickly create a table with the same structure as the table (emphasis)

grammar

Create table new table name like old table name;

demonstration

CREATE TABLE student LINKE stu;

3.5 – delete table (key)

Delete table directly

Drop table name;

Determine whether the table exists, and delete the table if it exists

Drop table if exists table name

demonstration

DROP TABLE student;
DROP TABLE IF EXISTS student

3.6 – modify table structure (key points)

Add a column

Alter table name add column name type

Modify the type of the column

Alter table name modify column name new type

Modify column name

Alter table name change old column name new column name type;

Delete column

Alter table name DROP column name;

Modify table name

Rename table name to new table name;

Modify table name character set

Alter table name character set character set;

Chapter 4: data in DML operation table

4.1 – insert records (key points)

All field names

Insert into table name (field name 1, field name 2, field name 3 ）Values (value 1, value 2, value 3...);

Do not write the field name

Insert into table name values (value 1, value 2, value 3 );

Insert partial data

Insert into table name (field name 1, field name 2,...) values (value 1, value 2,...);

matters needing attention

1. The inserted data should be of the same data type as the field
2. The size of the data should be within the specified range of the column. For example, a string of length 80 cannot be added to a column of length 40.
3. The data position listed in values must correspond to the arrangement position of the added column.
4. Value can be used in mysql, but it is not recommended. Its function is the same as values.
5. Character and date data should be enclosed in single quotation marks. Double quotation marks can also be used as separators in MySQL.
6. Do not specify a column or use null to insert a null value.

4.2 – update records (key points)

Update table name set column name = value [where conditional expression]
#Update: the name of the table that needs to be updated
#Set: modified column value
#Where: only qualified records are updated
#You can update one or more fields at the same time.
#You can specify any condition in the where clause.

Modifying data without conditions

Update table name set field name = value; -- modify all rows

Modify data with conditions

Update table name set field name = value where field name = value;

demonstration

--Modify the data without conditions, change all gender to female
Update student set sex ='female ';
--Modify the data with conditions, and change the gender of the student whose ID number is 2 to male
Update student set sex ='male 'where id = 2;
--Modify multiple columns at a time, change the age of students with ID 3 to 26, and change the address to Beijing
Update student set age = 26, address ='Beijing 'where id = 3;

4.3 – delete records (key points)

Delete from table name [where conditional expression]
#If the where clause is not specified, all records in the MySQL table will be deleted.
#You can specify any condition in the where clause

Delete data without conditions

Delete from table name

Delete data with conditions

Delete from table name where field name = value;

Use truncate to delete all records in the table

Truncate table name;

The difference between truncate and delete

Truncate is equivalent to deleting the structure of a table and creating another table.

demonstration

--Delete data with conditions and delete the record with ID 1
delete from student where id=1;
--Delete data without conditions, delete all data in the table
delete from student;

Chapter 5: data in DQL query table

Select column name from table name [where conditional expression];

5.1 – simple query (key)

Query the data of all rows and columns in a table

Select * from table name;

Query the specified column

Select field name 1, field name 2, field name 3,... From table name;

demonstration:

select * from student;
select name,age,sex from student;

5.2 – specify the alias of the column (emphasis)

The advantage of using aliases: use a new name when displaying, and do not modify the structure of the table.

Why tables use aliases: for multi table query operations.

Assign aliases to columns

Select field name 1 as alias, field name 2 as alias... From table name;

Assign aliases to column and table names

Select field name 1 as alias, field name 2 as alias... From table name as table alias;

demonstration

--Use alias
Select name as name, age as age from student;
--Table use alias
select  st.name  As name, age as age from student as St

5.3 – remove duplicate values (emphasis)

Query the specified column without duplicate data

Select distinct field name from table name;

demonstration

--Where do students come from
select address from student;
--Remove duplicate records
select distinct address from student;

5.4 – query results participate in operation (key)

Note: the number type must be involved in the operation

A column of data and fixed value operation

Select column name 1 + fixed value from table name;

One column data and other column data participate in the operation

Select column name 1 + column name 2 from table name;

demonstration

Demand – prepare data: add the math and English score columns, add the corresponding math and English scores to each record, and add the math and English scores when querying

select * from student;
--Give five points to all the math
select math+5 from student;
--Query the sum of math + English
select * from student;
(math + English) as total score from student;
--As can be omitted
Select *, (math + English) total score from student;

5.5 – conditional query (key)

Why query by condition?

If there is no query condition, all rows are queried each time. In practical application, it is necessary to specify the query conditions. Filtering records

grammar

Select field name from table name where condition;

Process: take out each data in the table, return the records that meet the conditions, and do not return the records that do not meet the conditions

Data preparation

CREATE TABLE student3 (
 Id int, - number
 Name varchar (20), -- name
 Age int, - age
 Sex varchar (5), -- sex
 Address varchar (100), -- Address
 Math int, -- Mathematics
 English int
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) 
VALUES 
    (1), 'ma Yun', 55, 'male', 'Hangzhou', 66,78),
    (2), 'ma Huateng', 45, 'female', 'Shenzhen', 98,87),
    (3, 'ma Jingtao', 55, 'male', 'Hong Kong', 56,77),
    (4, 'Liuyan', 20, 'female', 'Hunan', 76,65),
    (5, 'Liu Qing', 20, 'male', 'Hunan', 86, null),
    (6, 'Andy Lau', 57, 'male', 'Hong Kong', 99,99),
    (7, 'Mulder', 22, 'female', 'Hong Kong', 99,99),
    (8, 'demacia', 18, 'male', 'Nanjing', 56,65);

Comparison operator

Comparison operator	explain
>、<、<=、>=、=、<>	<>In SQL, it means not equal to. It can also be used in MySQL!=No,==
BETWEEN...AND	Within a certain range, for example, between 100 and 200 is equivalent to the condition between 100 and 200, including the head and the tail
In (set)	Sets represent multiple values, separated by commas
Like 'Zhang%'	Fuzzy query
IS NULL	Query the value of a column that is null. Note: cannot write = null

--Query the students whose math score is more than 80
select * from student3 where math>80;
--Query the students whose English score is less than or equal to 80
select * from student3 where english <=80;
--I'm a 20-year-old student
select * from student3 where age = 20;
--Query age is not equal to 20 years old students, note: does not mean there are two ways to write
select * from student3 where age <> 20;
select * from student3 where age != 20;

Logical operators

Logical operators	explain
And or&&	The former is recommended in SQL, while the latter is not universal.
orOr two vertical bars	or
Not or!	wrong

--Query the students whose age is greater than 35 and gender is male (both conditions are met)
Select * from student3 where age > 35 and sex ='male ';
--Query the students whose age is greater than 35 or whose gender is male (one of the two conditions is met)
Select * from student3 where age > 35 or sex ='male ';
--Query the students whose ID is 1 or 3 or 5
select * from student3 where id=1 or id=3 or id=5;

In keyword

Select field name from table name where field in (data 1, data 2...);

In each data will be as a condition, as long as meet the conditions will be displayed.

--Query the students whose ID is 1 or 3 or 5
select * from student3 where id in(1,3,5);
--Query students whose ID is not 1 or 3 or 5
select * from student3 where id not in(1,3,5);

Range query

The value of between 1 and 2 indicates the range from value 1 to value 2, including the beginning and the end

For example:age BETWEEN 80 AND 100amount to:age>=80 && age<=100

Query the students whose English score is greater than or equal to 75 and less than or equal to 90

select * from student3 where english between 75 and 90;

Like keyword

Select * from table name where field name like 'wildcard string';

Like stands for fuzzy query

MySQL wildcard

wildcard	explain
%	Matches any number of strings
_	Match one character

--Search for students surnamed Ma
Select * from student3 where name like 'ma%';
Select * from student3 where name like 'horse';
--Search for students whose names contain 'de'
Select * from student3 where name like '% de%';
--Search for students with two characters surnamed ma
Select * from student3 where name like '_ ';

5.6 – ranking (key points)

Through the order by clause, you can sort the query results (sorting is only the display mode, and will not affect the order of data in the database)

Select field name from table name where field = value order by field name [ASC | desc];

ASC: ascending, default desc: descending

Single column sorting

Sort by only one field, single column sort.

--Query all data, using age descending sort
select * from student order by age desc;

Combinatorial sorting

Multiple fields are sorted at the same time. If the first field is equal, the second field is sorted, and so on.

Select field name from table name where field = value order by field name 1 [ASC | desc], field name 2 [ASC | desc];
--Query all the data, on the basis of descending order of age, if the age is the same, then sort it in ascending order of mathematics score
select * from student order by age desc, math asc;

5.7-aggregate function (emphasis)

The previous queries we made are horizontal queries, which are judged line by line according to the conditions, while the aggregate function query is vertical query, which calculates the value of a column, and then returns a result value. Aggregate functions ignore null values.

Aggregate function in SQL	effect
Max (column name)	Find the maximum in this column
Min (column name)	Find the minimum value of this column
AVG (column name)	Find the average of this column
Count (column name)	Count how many records there are in this column
Sum (column name)	Sum this column

Grammar:Select aggregate function (column name) from table name;

--Query the total number of students
Select count (ID) as total number of students;
Select count (*) as total number of students;

Null records will not be counted. It is recommended that if the number of records is counted, columns that may be null should not be used. But what about counting null?

Ifnull (column name, default)If the column name is not empty, return the value of the column. If NULL, the default value is returned.

--Query the ID field. If it is null, use 0 instead
select ifnull(id,0) from student;

We can use the ifnull() function, if the record is null, give a default value, so that the statistical data will not be missed.

select count(ifnull(id,0)) from student;
--Query the total number of people older than 20
select count(*) from student where age>20;
--Query the total score of Mathematics
Select sum (Math) total score from student;
--Query math grade average
The average score of select AVG (Math) was from student;
--Query the highest math score
Select max (Math) from student;
--Query the lowest score in Mathematics
The lowest score of select min (Math) was from student;

5.8-grouping (focus)

Group query refers to the use of group by statement to group query information, the same data as a group

Select field 1, field 2... From table name group by [having condition];

How to group by

Take the same content in the result of grouping field as a group, such as dividing students into two groups according to gender.

Group by takes the same content in the results of grouped fields as a group, and returns the first data of each group, so it is useless to group them separately. The purpose of grouping is for statistics. Generally, grouping is used with aggregate function.

--Divide them into groups according to gender and get the average score of boys and girls
select sex, avg(math) from student3 group by sex;

The effect is as follows

In fact, the math of each group is averaged and the statistical results of each group are returned

Note: when we use a field group, we need to query this field when querying, otherwise we can’t see which group the data belongs to

How many people are male and female

Query all data,

1. They were grouped by gender.
2. Count the number of people in each group

select sex, count(*) from student3 group by sex;

Query the people over 25 years old, group them by gender, and count the number of each group

1. Filter out people younger than 25.
2. Group them again.
3. Finally, count the number of each group

select sex, count(*) from student3 where age > 25 group by sex ;

Query the people over 25 years old, group them by gender, count the number of people in each group, and only display the data that the number of people in gender is more than 2

--Filter the result of group query
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;

The difference between having and where

Subname	effect
whereclause	1. Before grouping the query results, remove the rows that do not meet the where condition, that is, filter the data before grouping, that is, filter before grouping. 2. Aggregate function cannot be used after where
havingclause	1. The function of having clause is to filter the groups that meet the conditions, that is, to filter the data after grouping, that is, to filter after grouping. 2. Aggregate function can be used after having

5.9-limit statement (emphasis)

Data preparation

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(9, Tang Seng, 25, male, Chang'an, 87, 78),
(10, Monkey King, 18, male, Huaguo Mountain, 100, 66),
(11, 'Zhu Bajie', 22, 'male', 'Gao Laozhuang', 58,78),
(12, 'Shaseng', 50, 'male', 'Liushahe', 77,88),
(13, 'Baigujing', 22, 'Nu', 'Baihuling', 66,66),
(14, 'spider sperm', 23, 'female', 'pansidong', 88,88);

effect

Limit means limit, so the function of limit is to limit the number of query records.

Select * | field list [as alias] from table name [where clause] [group by clause] [having clause] [order by clause] [limit clause];

Grammatical format

LIMIT offset,length;

Offset: the number of starting lines, counting from 0. If omitted, it is 0 by default

Length: the number of rows returned

--Query the data in the student table, and display it from the third item, and display 6 items.
select * from student3 limit 2,6;

Usage scenarios

paging: for example, if we log on to Jingdong and Taobao, we may return tens of thousands of product information, not all of them will be displayed at one time. Is a page display fixed number. Suppose we paginate by displaying 5 records per page

--If the first parameter is 0, you can omit writing:
select * from student3 limit 5;
--Finally, if there are not enough 5 items, how many are displayed
select * from student3 limit 10,5;

Chapter 6: constraints of database tables

6.1 – overview of Database Constraints (key points)

The role of constraints

Limit the data in the table to ensure the correctness, validity and integrity of the data. If a constraint is added to a table, incorrect data cannot be inserted into the table. It is appropriate to add constraints when creating tables.

Types of constraints

Constraint name	Constraint keywords
Primary key	primary key
only	unique
Not empty	not null
Foreign key	foreign key
Check constraints	Check note: MySQL does not support

6.2 – primary key constraint (key)

The role of primary key

Used to uniquely identify each record in the database

Which field should be the primary key of the table

Usually, instead of using the business field as the primary key, an ID field is designed for each table, and the ID is used as the primary key.The primary key is for the database and program, not for the final customer。 So it doesn’t matter whether the primary key has meaning or not, as long as it doesn’t repeat and is not empty.
Such as: ID card, student number is not recommended to be made into primary key.

Create primary key

1. Primary key keyword:primary key

2. Features of primary key:

   • Not null
   • only

3. How to create a primary key

   1. To add a primary key to a field when creating a table:Field name field type primary key;
   2. To add a primary key to an existing table:Alter table name add primary key;

--Create a student table ST5, which contains fields (ID, name, age) and takes ID as the primary key
create table st5 (
 Id int primary key, - ID is the primary key
 name varchar(20),
 age int
)
desc st5;

Delete primary key

--Delete the primary key of ST5 table
alter table st5 drop primary key;

Add primary key

alter table st5 add primary key(id);

Primary key auto increment

If we add the primary key ourselves, it is likely to be repeated. We usually hope that the database will automatically generate the value of the primary key field every time we insert a new record

AUTO_ Increment indicates auto growth (field type must be integer type)
--Insert data
Insert into ST6 (name, age) values ('xiao Qiao ', 18);
Insert into ST6 (name, age) values ('daqiao ', 20);
--Another way of writing
Insert into ST6 values (null, 'Zhou Yu', 35);
select * from st6;

Modify the default starting value of self growth

Auto by default_ The start value of increment is 1. If you want to modify the start value, use the following SQL syntax.

Specify the starting value when creating

Create table name（
Column name int primary key auto_ INCREMENT
) AUTO_ Increment = starting value;
--Specify a starting value of 1000
create table st4 (
 id int primary key auto_increment,
 name varchar(20)
) auto_increment = 1000;
Insert into ST4 values (null, 'Kongming');

Modify start value

Alter table name Auto_ Increment = starting value;
alter table st4 auto_increment = 2000;

6.3 – unique constraints (key points)

What is a unique constraint: a column in a table cannot have duplicate values

Basic format

Field name field type unique

demonstration

--Create a student table ST7, which contains fields (ID, name). The name column is set with a unique constraint. Students with the same name cannot appear
create table st7 (
 id int,
 name varchar(20) unique
)
--Add a student with the same name
Insert into ST7 values (1, 'Zhang San');
select * from st7;
--Duplicate entry 'Zhang San' for key 'name'
Insert into ST7 values (2, 'Zhang San');
--What happens when multiple nulls are inserted repeatedly?
insert into st7 values (2, null);
insert into st7 values (3, null);
--Null has no data, and there is no duplication problem

6.4 – nonempty constraints (emphasis)

What is a non NULL constraint: a column cannot be null.

Basic format

Field name field type not null
--Create a student table ST8, which contains fields (ID, name, gender), where name cannot be null
create table st8 (
    id int,
    name varchar(20) not null,
    gender char(1)
)

Default value

Field name field type default
--Create a student table ST9, which contains fields (ID, name, address). The default address is Guangzhou
create table st9 (
 id int,
 name varchar(20),
 Address varchar (20) default 'Guangzhou'
)
--Add a record and use the default address
Insert into ST9 values (1, 'Li Si', default);
select * from st9;
Insert into ST9 (ID, name) values (2, 'Li Bai');
--Add a record without using the default address
Insert into ST9 values (3, 'Li Siguang', 'Shenzhen');

be careful

Question: if a field is set with non null and unique constraints, what is the difference between the field and the primary key?

1. There can only be one primary key in a table. Cannot have more than one primary key. The primary key can be single column or multi column.
2. Self growth can only be used on primary keys.

6.5 – foreign key constraints (key)

Disadvantages of single table

Create an employee table containing the following (ID, name, age, DEP_ name, dep_ Location), ID primary key and automatically grow, add 5 pieces of data

CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
--Add data
INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values (Zhang San, 20, R & D department, Guangzhou);
INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values (Li Si, 21, R & D department, Guangzhou);
INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values (Wang Wu, 20, R & D department, Guangzhou);
INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values ('lao Wang ', 20,' sales department ',' Shenzhen ');
INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values ('dawang ', 22,' sales department ','shenzhen');
INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values ('xiao Wang ', 18,' sales department ','shenzhen');

Disadvantages of the above table:

1. data redundancy
2. Later, there will be the problem of addition, deletion and modification

Solution

--Solution: divide into 2 tables
--Create department table (ID, DEP)_ name,dep_ location)
--One side, main table
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
--Create employee table (ID, name, age, DEP)_ ID)
--Multi party, from table
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_ Id int -- the primary key of the main table corresponding to the foreign key
)
--Add 2 departments
Insert into department values (null, 'R & D department', 'Guangzhou'), (null, 'sales department', 'Shenzhen');
select * from department;
--Add employee, DEP_ ID indicates the Department of the employee
INSERT INTO employee (NAME, age, dep_ ID) values ('zhang San ', 20, 1);
INSERT INTO employee (NAME, age, dep_ ID) values ('li Si ', 21, 1);
INSERT INTO employee (NAME, age, dep_ ID) values ('wang Wu ', 20, 1);
INSERT INTO employee (NAME, age, dep_ ID) values ('lao Wang ', 20, 2);
INSERT INTO employee (NAME, age, dep_ ID) values ('dawang ', 22, 2);
INSERT INTO employee (NAME, age, dep_ ID) values ('xiao Wang ', 18, 2);
select * from employee;

New problems: when we are in employee’s dep_ If you enter a non-existent department in the ID, the data can still be added. However, there is no corresponding department, which can’t happen in practical application. Dep of employee_ The data in ID can only be the ID that exists in the Department table

target: constraint dep is required_ ID can only be an ID that already exists in the Department table

Solutions: use foreign key constraints

What are foreign key constraints

What are foreign keys: the column corresponding to the primary key of the master table in the slave table, such as DEP in the employee table_ ID

Main tableOne side, a table used to constrain others

From tableA table that is constrained by others

Syntax for creating constraints

• Add foreign key when creating a new table:[constraint] [foreign key constraint name] foreign key references main table name (primary key field name)
• Add foreign key to existing table:Alter table slave table add [constraint] [foreign key constraint name] foreign key (foreign key field name) references main table (primary key field name);

--1) delete sub table / sub table employee
drop table employee;
18 / 26
--2) create the slave table employee and add the foreign key constraint EMP_ depid_ FK
--Multi party, from table
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_ ID, int, -- foreign key corresponds to the primary key of the main table
--Creating foreign key constraints
constraint emp_depid_fk foreign key (dep_id) references department(id)
)
--3) add data normally
INSERT INTO employee (NAME, age, dep_ ID) values ('zhang San ', 20, 1);
INSERT INTO employee (NAME, age, dep_ ID) values ('li Si ', 21, 1);
INSERT INTO employee (NAME, age, dep_ ID) values ('wang Wu ', 20, 1);
INSERT INTO employee (NAME, age, dep_ ID) values ('lao Wang ', 20, 2);
INSERT INTO employee (NAME, age, dep_ ID) values ('dawang ', 22, 2);
INSERT INTO employee (NAME, age, dep_ ID) values ('xiao Wang ', 18, 2);
select * from employee;

Delete foreign key

Alter table drop foreign key name from the table;
--Delete EMP from employee table_ depid_ FK foreign key
alter table employee drop foreign key emp_depid_fk;

New problems

select * from employee;
select * from department;
--If you want to change the ID value from 2 to 5 in the Department table, can you update it directly?
-- Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where id=2;
--If you want to delete a department whose department ID is equal to 1, can you delete it directly?
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;

Cascade operation:When the primary key of the primary table is modified or deleted, the foreign key value of the secondary table is updated or deleted at the same time, which is called cascade operation

Cascading operation syntax	describe
ON UPDATE CASCADE	Cascade update can only create a cascade relationship when creating a table. The primary key in the master table is updated, and the foreign key columns in the slave table are also updated automatically
ON DELETE CASCADE	cascading deletion

--Delete employee table, re create employee table, add cascade update and cascade delete
drop table employee;
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_ ID, int, -- foreign key corresponds to the primary key of the main table
--Creating foreign key constraints
constraint emp_depid_fk foreign key (dep_id) references
 department(id) on update cascade on delete cascade
)
--Add data to employee table and department table again
INSERT INTO employee (NAME, age, dep_ ID) values ('zhang San ', 20, 1);
INSERT INTO employee (NAME, age, dep_ ID) values ('li Si ', 21, 1);
INSERT INTO employee (NAME, age, dep_ ID) values ('wang Wu ', 20, 1);
INSERT INTO employee (NAME, age, dep_ ID) values ('lao Wang ', 20, 2);
INSERT INTO employee (NAME, age, dep_ ID) values ('dawang ', 22, 2);
INSERT INTO employee (NAME, age, dep_ ID) values ('xiao Wang ', 18, 2);
--Delete department table? Can you delete it directly?
drop table department;
--Change the Department ID equal to 1 in the Department table to ID equal to 10
update department set id=10 where id=1;
select * from employee;
select * from department;
--Delete the Department whose department number is 2
delete from department where id=2;

6.6 – Summary of data constraints (key points)

Constraint name	keyword	explain
Primary key	primary key	1) Unique, 2) nonempty
default	default	If a column has no value, the default value is used
Not empty	not null	This column must have a value
only	unique	This column cannot have duplicate values
Foreign key	foreign key	Primary key column in master table, external key column in slave table