1, How many constraints do you master in MYSQL?
In MYQSL, there are some constraints. For example, it cannot be null or must be unique. Like ID cards, everyone's ID number is unique. Gender can be male or female, but not null.
The following describes six constraints in MYSQL
1. NOT NULL: a constraint column cannot be empty.
-- Create a book table, id Cannot be empty create table book(id int not null);
2. UNIQUE: constrains a row of a column to have a UNIQUE value.
-- Create a book table, id Unique, cannot be repeated create table book(id int unique);
3. DEFAULT: a DEFAULT value is given when no value is assigned to the column.
-- Create a Chinese book table, default name by unknown create table book(name varchar(20) default 'unknown');
4. PRIMARY KEY: the combination of NOT NUL and UNIQUE, that is, it cannot be empty, and the value must be UNIQUE. Generally, the ID of the table will be set to primar key, which has the advantage of creating a UNIQUE ID for the table. It is more convenient to find special information in the table. Often used with auto_ It is used in conjunction with increment to represent the self incrementing PRIMARY KEY,
-- Create a book, each book has its own name, price, but the serial number is unique create table book( id int primary key auto_increment, name varchar(20), price int );
5. FOREIGN KEY: FOREIGN KEY constraint. The data of one table matches another table to increase coupling.
-- Create class table, create table class( id int primary key auto_increment, name varchar(20) ); -- Student list,A student corresponds to a class. A class can have multiple students create table student( id int primary key auto_increment, name varchar(20), classes_id int , foreign key (classes_id) references class(id) );
6. CHECK: ensure that the values in the column meet specific conditions.
-- MySQL No error is reported when using, but the constraint is ignored: create table student( id int primary key auto_increment, name varchar(20), sex varchar(1), check (sex='male' or sex='female') );
2, Three relationships between tables
1. One to one relationship
eg: person and ID number
A person can only have one ID number.
2. One to many relationship
eg: the relationship between students and classes
A class can have multiple students, and a student belongs to only one class
3. Many to many relationship
eg: the relationship between teachers and classes
A class can have multiple teachers, and a teacher can teach multiple classes.
3, Inquiry
3.1: aggregate query
The aggregate query is completed through the aggregate function in 4. They are:**
1. count(): returns the quantity queried, regardless of whether the query type is a number.
-- How many books are there in the book list select count(*) from book;
2. sum(): returns the sum of the query data. If the data is not a number, it is meaningless
-- How many students are there in class two of three years select sum(student) from class;
3. avg(): returns the average number of queried data. If the data is not a number, it is meaningless
-- Query the average score of students in class 2 of three years select avg(math) from class;
4. max(): returns the maximum value of the queried data. If the data is not a number, it is meaningless
-- Query the highest score of mathematics in class 2 of three years select max(math) from class;
5. min(): returns the minimum value of the queried data. If the data is not a number, it is meaningless
-- Query the lowest score of mathematics in class 2 of three years select min(math) from class;
3.2,GROUP BY
Use the group by clause to group and query the specified columns. However, it needs to be satisfied. After select
The field must be the field on which the group is based. If other fields want to appear, you must use the aggregate function.
eg:
Prepare the data and create a student table with student id, name, math and Chinese scores.
create table student( -- student id id int primary key auto_increment, -- Student name student_name varchar(20), -- Students' mathematics scores math int, -- Students' Chinese achievement chinese int );
insert data
insert into student(student_name,math,chinese) values
('Zhang San',66,67),
('Li Si',44,98),
('Wang Wu',88,89);
View the data of the current table
Use GROUP BY to query the highest, average and lowest math scores of students
3.3. Using with HAVING and group by
After using the group by sentence, if you still need condition filtering, you can't use where. You need to use having to filter the conditions.
-- Query students with a total score of more than 150 select student_name , sum(math+chinese) from student group by student_name having sum(math+chinses)>150;
As a result, only Wang Wu's total score was more than 150
3.4. Joint query
The above queries are performed in one table, but in the process of practical application, data is often queried in several tables, which uses joint query. Multi table query is to take Cartesian product of these tables and filter useful data. Before learning joint query, you need to know what Cartesian product.
In general, Cartesian product is to list all possible data in two tables.
Prepare data:
drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;
create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));
create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) ,
classes_id int);
create table course(id int primary key auto_increment, name varchar(20));
create table score(score decimal(3, 1), student_id int, course_id int);
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');
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);
insert into course(name) values
('Java'),('Chinese traditional culture'),('Computer principle'),('language'),('Higher order mathematics'),('english');
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),
-- Speak in a normal way
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);
3.4.1 internal connection
Syntax:
1. select field name from table 1 + alias (not writable) inner join table 2 + alias (not writable) on
Connection conditions and other conditions
Example:
Query Bai Suzhen's grades
Query the total scores of all students:
When we write some complex SQL statements, we highly recommend that you do not write them all at once. It's easy to make mistakes, but SQL debugging can't, so you can write it line by line like the following. Write one condition at a time, so it's clear.
select student.sn,student.name,student.qq_mail， sum(score.score) from student join score on student.id=score.student_id group by score.student_id;
2. select field name from table 1 + alias (can not be written), table 2 + alias (can not be written), where connection conditions and other conditions
Example:
To inquire about Bai Suzhen's grades:
Query the qq email address and total score of all students:
The results of these two methods are the same.
3.4.2 external connection
The difference between inner connection and outer connection lies in the difference between "NULL value". If there is no "NULL value" in the table (it is not simply NULL, but generally refers to the data mismatch between the two tables), there is no difference between inner connection and outer connection.
eg:
First delete the grade and student table, create a new one, and add 3 students.
create table student(id int primary key auto_increment,name varchar(20)); create table score(studentId int ,score int ); insert into student values(null,'Zhang San'); insert into student values(null,'Li Si'); insert into student values(null,'Wang Wu');
Let's take a look at the data of the current table
select * from student;
Insert three more rows of data into the grade sheet, but make a little difference.
insert into score values(1,55); insert into score values(2,85); insert into score values(4,91);
Here, the id of Wang Wu, who should have been 3, is deliberately changed to 4.
At this time, the data does not correspond. Wang Wu has no score in the score table, and the student with id 4 has no information in the student table.
What happens if we query two tables?
Query each student's grades and name information.
select student.name,score.score from student join score on student.id=score.studentId;
The final result of the inner connection has neither the score and name information of Wang Wu nor the information of the student with id 4.
Inner join is equivalent to taking the intersection of the above two tables.
Compared with the inner connection, the outer connection is divided into left outer connection and right outer connection. In terms of writing, it is very similar to the inner connection. You only need to add left or right before the join in the original statement to indicate the inner connection or outer connection
Left outer connection: the final result is mainly the table on the left of the join, and the information of the table on the left is displayed as much as possible.
select student.name,score.score from student left join score on student.id=score.studentId;
Right outer connection: the final result is mainly the table on the right side of the join, and the information of the table on the right side is displayed as much as possible.
select student.name,score.score from student right join score on student.id=score.studentId;
3.4.3 self connection
Self join means that the same table joins itself for query.
eg: query the information that Chinese scores are higher than English scores
The difference between this query and the past is that it is a query for rows and rows. For example, the previous query is to query each student's score and name information. This is the relationship between columns.
Since it's hard to solve the problem, I might as well change my mind. Then I won't compare rows. I'll try to convert it into column and column comparison.
1. Let's see what its Cartesian product looks like
Note that if you directly select * from score,score; If you inquire, you will report an error. You need to use as to create an alias.
select * from score as a,score as b;
The final result is 400 lines. I'll put some here.
Next, further screen the useful information and select the information of the same student
select * from score as a,score as b where a.student_id=b.student_id;
Now it's reduced to 62 lines
The last step is directly in place, but you need to look back at the id of Chinese and English. One is 4 and the other is 6
insert into course(name) values
('Java'),('Chinese traditional culture'),('Computer principle'),('language'),('Higher order mathematics'),('english');
In this way, we are filtering through the course id
select * from score as a,score as b where a.student_id=b.student_id and a.course_id=4 and b.course_id=6;
The end result is only one.
Let's practice the information of students with higher scores in java than in computer principle. This time, let's go directly to the step in the figure above.
select * from score as a,score as b where a.student_id=b.student_id and a.course_id=1 and b.course_id=3;
course_ The score with ID 1 is the score of java, course_id 3 is the score of computer principle
In this way, a row contains not only the columns of java, but also the columns of computer principles. The effect of row to column conversion is completed through self connection.
Let's see the final result. The score of computer principle is higher than that of JAVA
select * from score as a,score as b where a.student_id=b.student_id and a.course_id=1 and b.course_id=3 and a.score<b.score;
3.5 sub query
A subquery is a query statement in other sql statements. So it is also called nested query.
3.5.1 single line sub query
A single row subquery returns a single row subquery.
eg: inquire about the classmates of students who "don't want to graduate".
Step by step, first query the class id of the students who don't want to graduate.
select classes_id from student where name='I don't want to graduate';
I don't want to graduate in class one. I'm looking for his classmates through this information in class 1.
select name from student where classes_id=1;
We use two queries to get the desired results. How about a sub query.
select name from student where classes_id=(select classes_id from student where name='I don't want to graduate');
In the above SQL statement, first execute the second SQL statement as the condition for the previous query name, which is the sub query.
3.5.2 multi line sub query
Multi row subquery returns multiple records
eg: query information about Chinese or English scores
1. First query the course id of Chinese or English scores
select id from course where name='language' or name='english';
2. Then, in the course schedule, find the corresponding information according to the course id
select * from score where course_id=4 or course_id=6;
Try multi line sub query again
Multiple records are obtained by multi row sub query, and the in keyword is required for outer query. Only one record is returned in a single line sub query. You can directly use =.
select * from score where course_id in( select id from course where name='language' or name='english');
3.6. Consolidated query
Merging the results of multiple select queries into one result is a merge query. Some are like Union. It contains two keywords
union: de duplication for duplicate
union all does not de duplicate duplicate
The premise of union is that the columns of the results obtained by the queries on both sides are consistent, including quantity and type.
eg: query courses with id less than 3 or name "English"
select * from course where id<3 or name ='english';
Using union queries
select * from course where id<3 union select * from course where name='english';
The effect is the same.
4, End
The above is all the content of this time. I wish you all a smooth work and study. Rush
