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 codeDROP TABLE IF EXISTS students;CREATE TABLE students(id INT ,S_name VARCHAR(10) not null, #S_sex CHAR(1) default 'male ', #s_seat INT,age INT ,teacher_id INT ,The column level constraint is set above the table level constraint[CONSTRAINT pk PRIMARY KEY(id)CONSTRAINT uq UNIQUE(s_seat), #CONSTRAINT ck CHECK(s_sex=' male 'OR s_sex=' female ')CONSTRAINT fk_students_teacher FOREIGN KEY(Teacher_id) REFERENCES Teacher (id) #);
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
An aggregation function is a function that evaluates a column of data vertically while querying the data. Master the following aggregate functions.
SELECT aggregate function (column name) FROM table name
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
The MIN function— Query the minimum value of math
SELECT MIN(math) FROM student;
MAX function
— Query the maximum math value
SELECT MAX(math) FROM student;
SUM function
— Query math’s sum
SELECT SUM(math) FROM student;
AVG function
— Query the math average
SELECT AVG(math) FROM student;
DQL Grouping query
Group the data according to specific conditions, treat each group as a whole, and calculate a certain group of data separately.
Group query syntax, the field list can only be group columns or aggregate function SELECT field list FROM table name WHERE condition before grouping
GROUP BY GROUP column name HAVING condition after GROUP
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
Select * from where where clause * Select * from where where = >70 * select * from where where = >70
SELECT sex, AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex;
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
Paging query refers to the method of querying fixed records at a time when a large amount of data is to be queried.
— Start index =(current page number -1)* Number of pages
Select field list from table name LIMIT Number of start indexes
Query the first 3 records in index 0
SELECT * FROM student LIMIT 0,3; – page 1
— Query the first three records of index 3
SELECT * FROM student LIMIT 3,3; – page 2
— Query the first three records of index 6
SELECT * FROM student LIMIT 6,3; – page 3
