The constraint

Constraints: Restrict the data in the table to ensure that the data added to the table is accurate and reliable! Any data that does not conform to the constraint will fail to be inserted! Constraints can be used when creating a table or added when modifying a table

S_name VARCHAR(10) not null, # not null

2) default: default constraint, ensure that the field will always have a value, even if there is no inserted value, there will be a default value! Age INT DEFAULT 18, # DEFAULT constraint

S_seat INT unique,#

S_sex CHAR(1) check (s_sex=’ male ‘OR s_sex=’ female ‘),#

Id INT primary key,# primary key (unique, non-null)

6) Foreign key: used to restrict the relationship between two tables, ensure that the value of this field from the table is from the value of the primary table associated with the field! Teacher_id INT REFERENCES teacher(id) teacher_id INT REFERENCES teacher(id

Mysql > alter table select * from ‘foreign key’ where ‘foreign key’ = ‘foreign key’ where ‘foreign key’ = ‘foreign key’; Table level constraints are not null and are not supported by default.

Grammar:

Other: [constraint name] constraint type,

[Constraint constraint name] Foreign Key references [constraint name]

 
Copy the code

1. DROP TABLE IF EXISTS students;
2. CREATE TABLE students(
3. id INT ,
4. S_name VARCHAR(10) not null, #
5. S_sex CHAR(1) default 'male ', #
6. s_seat INT,
7. age INT ,
8. teacher_id INT ,
9.  
10. The column level constraint is set above the table level constraint
11.  
12. [CONSTRAINT pk PRIMARY KEY(id)
13. CONSTRAINT uq UNIQUE(s_seat), #
14. CONSTRAINT ck CHECK(s_sex=' male 'OR s_sex=' female ')
15. CONSTRAINT fk_students_teacher FOREIGN KEY(Teacher_id) REFERENCES Teacher (id) #
16. );

MySQL > alter table add or drop constraint

That is, modify the data type or constraint of a table field

ALTER TABLE DROP CONSTRAINT DROP CONSTRAINT name

Alter table STUDENTS modify column s_name vARCHar (20) not null; Alter table students modify column s_name varchar(20); # delete do not write constraint

Alter table STUDENTS modify column age int default 18; Alter table students modify column age; # remove

Alter table students modify column seat int unique; Alter table students drop index seat; # delete show index from students; # check unique constraints

Alter table students modify column ID int primary key; Alter table students drop primary key; # delete constraint name

Alter table STUDENTS add Foreign key(major_id) references majors(ID); Alter table students drop foreign key fk_students_teacher; # delete constraint name

Alter table auto_increment

id int primary key auto_increment,

There is only one self-growing column in a table, and self-growing columns are usually paired with primary keys

Alter table t_indentity modify column ID int primary key auto_increment; alter table t_indentity modify column ID int primary key auto_increment;

Alter table t_indentity modify column id int;

The query

Before querying, let’s also prepare some data

CREATE DATABASE db2; USE DB2; Alter table student CREATE

TABLE student (

Id INT, — id

NAME VARCHAR(20), — The NAME

Age INT, — Age

Sex VARCHAR(5) — gender

Address VARCHAR(100), — Address

Math INT, — Math

English INT — English);

Add table records

INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1, ‘Mr. Ma, 55,’ male ‘, ‘city of hangzhou, 66,78), (2,’ ma ‘, 45, ‘female’ and ‘shenzhen’ 98,87), (3, ‘Steve, 55,’ male ‘, ‘Hong Kong’, 56,77), (4, ‘Ada’, 20, ‘female’ and ‘in hunan province, 76,65), (5,’ willow blue ‘, 20 And ‘male’, ‘hunan, 86, NULL), (6,’ Andy lau, 57, ‘male’, ‘Hong Kong’, 99, 13), (7, ‘mudd, 22,’ female ‘and’ Hong Kong ‘, 99, 13), (8, ‘DE Marcia, 18,’ male ‘, ‘nanjing’, 56,65);

The following data

DQL Sorting query

The following figure shows the result set sorted for the last SQL statement

DQL Aggregation function

COUNT function

— Note: If a class has a NULL value, it does not count

Select * from student where math = 8

SELECT COUNT(math) FROM student;

Select * from student where English is NULL

SELECT COUNT(english) FROM student;

Select * from table_name where table_name = 1;

SELECT COUNT(*) FROM student;

The execution result is

The MIN function

MAX function

— Query the maximum math value

SELECT MAX(math) FROM student;

SUM function

AVG function

— Query the math average

SELECT AVG(math) FROM student;

DQL Grouping query

Group queries for all data

— Query the average of male and female Math by last name

SELECT sex,AVG(math) FROM student GROUP BY sex;

SELECT * FROM math, AVG(math), COUNT(id) FROM math, AVG(math) SELECT * FROM math, AVG(math), COUNT(id) FROM math

student GROUP BY sex;

Pre-group screening

Post group screening

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

And the number of people, then filter the number of people >2 data

SELECT sex, AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;

You can use the AS statement to alias the aggregate function to improve the SQL statement above

SELECT sex, AVG(math) AS 平均分,COUNT(id) AS 个数 FROM student WHERE math>70 GROUP BY sex HAVING

Number > 2;

DQL Paging query

Query the first 3 records in index 0

SELECT * FROM student LIMIT 0,3; – page 1

— Query the first three records of index 3

— Query the first three records of index 6

SELECT * FROM student LIMIT 6,3; – page 3