Database Constraints

Constraint type

• NOT NULL: indicates that a column cannot store a NULL value, that is, it must be assigned a value, otherwise an error is reported;
• UNIQUE: ensure that each row of a column must be a UNIQUE value;
• DEFAULT: Specifies the DEFAULT value when no value is assigned to the column;
• PRIMARY KEY: the combination of NOT NULL and UNIQUE ensures that a column (or the combination of two columns and multiple columns) has a UNIQUE identification, which helps to find a specific record in the table more easily and quickly;
• Primary key (field 1, field 2...): When the primary key consists of multiple fields, you cannot declare the primary key constraint directly behind the field, but set it in this way;
• FOREIGN KEY REFERENCES parent table name (parent table field): ensure that the data in one table matches the reference integrity of the value in another table, that is, for the field set as a FOREIGN KEY, the value of the field in the child table must exist in the parent table. If not, an error will be reported. In addition, when deleting, it is not necessary to delete the child table first and then delete the parent table, otherwise an error will be reported;
• AUTO_INCREMENT: Auto increment attribute. It can only be used to shape field variables. The fields set to this attribute can not be assigned a value and will automatically increase from the maximum value in order;
• CHECK: ensure that the values in the column meet the specified conditions. For MySQL database, analyze the CHECK clause, but ignore the CHECK clause;

example

//Class table
CREATE TABLE classes (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20)
);
//Student list
CREATE TABLE student (
	//For integer type primary keys, auto growth is often used_ Increment. When the field corresponding to the inserted data does not give a value, the maximum value + 1 is used
	id INT PRIMARY KEY auto_increment,
	sn INT NOT NULL UNIQUE,
	name VARCHAR(20) DEFAULT 'unkown',
	qq_mail VARCHAR(20),
	classes_id int,//The class information in the student table is a foreign key associated with the class id
	FOREIGN KEY (classes_id) REFERENCES classes(id),
	check (sex ='male' or sex='female')//Ignore the constraint
);

Aggregate function

• now(): get the current time of the system;
• count(*): counts the number of query results;
• Count (field): count the number of non NULL specified fields;
• Sum (field): get the sum of the values of a field;
• Max (field): get the maximum value of a field;
• Min (field): obtain the minimum value of a field;
• AVG (field): get the average value of a field;

-- Count the total number of students in the class
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;
-- Statistics collected by the class qq_mail How many, qq_mail by NULL The data will not be included in the results
SELECT COUNT(qq_mail) FROM student;

-- Total score of Statistical Mathematics
SELECT SUM(math) FROM exam_result;
-- fail, < 60 Total score of, no result, return NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

-- Statistical average total score
SELECT AVG(chinese + math + english) Average total score FROM exam_result;

-- Return to the highest score in English
SELECT MAX(english) FROM exam_result;

-- return > 70 The lowest score in mathematics above
SELECT MIN(math) FROM exam_result WHERE math > 70;

Grouping query

• select the field to be grouped, aggregate function 1, aggregate function 2 From table name group by field 1 to be grouped, field 2 to be grouped...;: Group according to a certain field, and then use the specified aggregation function to count and analyze the data;
• select the field to be grouped, aggregate function 1, aggregate function 2 From table name group by field 1 to be grouped, field 2 to be grouped Having condition filtering;: In grouping query, where cannot be used for filtering, but having can be used for filtering;

Three paradigms

1. Each field in the table must have the characteristic of indivisible atom, which is the basis of other paradigms. If the first paradigm is not followed, the efficiency of a field in query will be reduced;
2. Each field in the table should be fully associated with the primary key, not partially, otherwise there will be a lot of redundant data in the table;
3. Each field in the table should be directly associated with the primary key rather than indirectly, otherwise there will be a lot of redundant data in the table;

Multi table associated query

Create sample

• Create class information table and insert data:

CREATE TABLE classes (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20),
	`desc` VARCHAR(100)
);

insert into classes(name, `desc`) values
('Class 1, grade 2019, computer department', 'Learned the principle of computer C and Java Language, data structure and algorithm'),
('Class 3, grade 2019, Chinese Department','I studied Chinese traditional literature'),
('Class 5, automation level 2019','Learned mechanical automation');

• To create a student information table and insert data:

CREATE TABLE `student` (
  id int(11) PRIMARY KEY AUTO_INCREMENT,
  sn int(11) NOT NULL UNIQUE,
  name varchar(20) NOT NULL,
  qq_mail varchar(20),
  classes_id int(11) NOT NULL,
)

insert into student(sn, name, qq_mail, classes_id) values
('09982','Black Whirlwind Li Kui','xuanfeng@qq.com',1),
('00835','The Grapes ',null,1),
('00391','Bai Suzhen',null,1),
('00031','Xu Xian','xuxian@qq.com',1),
('00054','I don't want to graduate',null,1),
('51234','Speak in a normal way','say@qq.com',2),
('83223','tellme',null,2),
('09527','Foreigners learn Chinese','foreigner@qq.com',2);

• To create a student transcript and insert data:

CREATE TABLE score (
	id INT PRIMARY KEY auto_increment,
	score DECIMAL(3, 1),
	student_id int,
	course_id int,
	FOREIGN KEY (student_id) REFERENCES student(id),
	FOREIGN KEY (course_id) REFERENCES course(id)
);

insert into score(score, student_id, course_id) values
-- Black Whirlwind Li Kui
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- The Grapes 
(60, 2, 1),(59.5, 2, 5),
-- Bai Suzhen
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- Xu Xian
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- I don't want to graduate
(81, 5, 1),(37, 5, 5),

• Create a student class table and insert data:

CREATE TABLE course (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20)
);

insert into course(name) values
('Java'),('Chinese traditional culture'),('Computer principle'),('language'),('Higher order mathematics'),('english');

Inner connection

• Concept: suppose that table A and table B are connected internally, and the connection condition is k, the execution method is to take every piece of data from table A, and then try with every piece of data in table B. If K is true, connect the two rows of data together;
• Syntax:

//use... join...on... realization
select field from Table 1 alias 1 [inner] join Table 2 alias 2 on Connection conditions and Other conditions;
//Or by where... To achieve
select field from Table 1 alias 1,Table 2 alias 2 where Connection conditions and Other conditions;

• Example 1: query the total score and personal information of each student's subjects;

select stu.sn, stu.name, stu.qq_mail, sum(score) total from student stu join score sco on stu.id = sco.student_id group by stu.id;

• Example 2: query the scores of each subject and personal information of each student;

select stu.sn, stu.name, stu.qq_mail, cou.name, sco.score from student stu join score sco on stu.id = sco.student_id join course cou on sco.course_id = cou.id order by stu.id, cou.name;

External connection

• Concept: there are two forms of external connection: left connection and right connection. Assuming that table A and table B are left (right) connected, and the connection condition is k, the execution method is to take each piece of data from table A (b), and then try with each piece of data in table B (A). If K is true, connect the two rows of data together. If K is false;
• Syntax:

-- Left outer connection, fully shown in Table 1
select Field name from Table name 1 left join Table name 2 on Connection conditions;
-- Right outer connection, fully shown in Table 2
select field from Table name 1 right join Table name 2 on Connection conditions;

• Example: query all students' grades and personal information. If the student has no grades, it also needs to be displayed;

#use
select stu.sn, stu.name, stu.qq_mail, cou.name, sco.score from student stu left join score sco on stu.id = sco.student_id left join course cou on sco.course_id = cou.id order by stu.id, cou.name;

Self connection

• Concept: the same table connects itself for query;
• Example: query the information of all students whose scores in "computer principle" are higher than those in "Java";

select stu.sn, stu.name, stu.qq_mail, s1.score Computer principle, s2.score Java from student stu join score s1 on s1.student_id = stu.id join score s2 on s2.student_id = stu.id join course c1 on c1.id = s1.course_id and c1.name = 'Computer principle' join course c2 on c2.id = s2.course_id and c2.name = 'Java' and s1.score > s2.score;

Subquery

concept

• Concept: sub query refers to the select statement embedded in other SQL statements, which uses a sub query as a temporary table, also known as nested query;

single-row subqueries

• Example: query the information of students in the same class as "don't want to graduate";

select * from student where classes_id = (select classes_id from student where name='I don't want to graduate');

Multiline subquery

• [NOT] IN: NOT in square brackets is optional, (NOT) in a query result;
• Example 1: query the score information of "Chinese" or "English" courses;

//in
select * from score where course_id in (select id from course where name = 'language' or name = 'english');
//not in
select * from score where course_id not in (select id from course where name != 'language' and name != 'english');

• Example 2: query duplicate scores of subjects in the grade table;

//First, insert some duplicate score information
insert into score(score, student_id, course_id) values
-- Black Whirlwind Li Kui
(70.5, 1, 1),(98.5, 1, 3),
-- The Grapes 
(60, 2, 1);

select * from score where (student_id, course_id) in (select student_id, course_id from score group by student_id, course_id having count(*) > 1);

• [NOT] EXISTS: first execute the previous statement to get the result, and use some fields in the result for subsequent queries (these fields are required for some conditions in subsequent queries);
• Example: query the score information of "Chinese" or "English" courses;

//exists
select * from score where exists (select * from course where id = score.course_id and (name = 'language' or name = 'english'));
//not exists
select * from score where not exists (select * from course where id = score.course_id and (name != 'language' and name != 'english'));

Using subqueries in the from clause

• Technique: use a subquery as a temporary table;
• Example: query all score information higher than the average score of "Class 3, grade 2019, Chinese Department";

select * from score join (select avg(score) avg_score from score sco join student stu on sco.student_id = stu.id join classes cla on cla.name = 'Class 3, grade 2019, Chinese Department' and cla.id = stu.classes_id) tmp on score.score > tmp.avg_score;

Merge query

• Concept: in practical application, in order to merge the execution results of multiple select ions, the set operators UNION, UNION ALL can be used. When UNION and UNION ALL are used, the fields in the result set of the previous and subsequent queries need to be consistent;
• UNION: this operator is used to obtain the UNION of two result sets. When this operator is used, the duplicate rows in the result set will be automatically removed;
• UNION ALL: this operator is used to obtain the union of two result sets. When this operator is used, the duplicate rows in the result set will not be removed;
• Example: query courses with id less than 3 or name "Java";

//union
select * from course where id < 3 union select * from course where name = 'Java';
//union all
select * from course where id < 3 union all select * from course where name = 'Java';