This paper has participated in theProject Digg”To win a creative gift package and challenge for creative incentive money.

@TOC

How many constraints do you know about MYSQL?

In MYQSL, there are some constraints. Cannot be null, for example, or must be unique. Just like an ID card, each person’s ID number must be unique. It can be male or female, but not null.

Here are six constraints in MYSQL

NOT NULL: specifies that a column cannot be NULL.

Create a book table with id not null
create table book(id int  not null);
Copy the code

Constraint that a row in a column has a UNIQUE value.

Create a book table with unique id that cannot be repeated
create table book(id int  unique);
Copy the code

3. DEFAULT: give a DEFAULT value if no column is assigned.

Create a list of Chinese books with default name unknown
create table book(name varchar(20) default
'unknown');
Copy the code

4, The PRIMARY KEY must NOT be null and must be UNIQUE. Typically, the ID of the table will be set as the primary key, which has the advantage of creating a unique identity for the table. Make it easier to find specific information in the table. Commonly used with auto_increment to indicate that the primary key is incremented.

Create a book. Each book has its own name, price, but unique serial number
create table book(
id int primary key auto_increment,
name varchar(20),
price int );
Copy the code

5. FOREIGN KEY: FOREIGN KEY constraint. Data from one table matches data from another table, increasing coupling.

Create class table;
create table class(
id int primary key auto_increment,
name varchar(20));-- Student table, a student for a class, a class can have more than one student
create table student(
id int primary key auto_increment,
name varchar(20),
classes_id int ,
foreign key (classes_id) references class(id)
);
Copy the code

6. CHECK: Ensures that the values in the column meet specific conditions.

MySQL does not report errors, but ignores this constraint:
create table student(
id int primary key auto_increment,
name varchar(20),
sex varchar(1),
check (sex='male' or sex='woman'));Copy the code

Two, the table between the three kinds of relations

1. One-to-one relationship

A person can only have one id number The relationship between students and their class

A class can have multiple students, and a student belongs to only one class The relationship between teacher and class

A class can have more than one teacher, and a teacher can teach more than one class.

Third, the query

3.1: Aggregate query

Aggregate queries are done through the aggregate functions in 4. They are: ** 1, count() : returns the number of queries, regardless of whether the query type is numeric.

-- Query the number of books in the book table
select  count(*) from book;
Copy the code

2. Sum () : Returns the sum of the query data. If the data is not a number, it is meaningless

Select * from class 3 where are the students
select sum(student) from class;

Copy the code

3. Avg () : Returns the average of the queried data. It is meaningless if the data is not numeric

Select count (*) from class 3 where count (*
select avg(math) from class;
Copy the code

4. Max () : Returns the maximum value of the queried data. If the data is not a number, it is meaningless

Select * from class 3; select * from class 3
select max(math) from class;
Copy the code

5. Min (): Returns the minimum value of the queried data. It is meaningless if the data is not a number

Select * from class 3 where 'math' = 'math
select min(math) from class;
Copy the code

3.2, the GROUP BY

Use the group by clause to group queries on specified columns. However, the selected field must be the field by which the grouping is based. The aggregate function must be used if the other fields are to appear.

Eg: Prepare the data, create a student table with student IDS, names, math and Chinese scores.

create table  student(
- student id
id int primary key auto_increment,
-- Student name
student_name varchar(20),
-- Students' math scores
math int.-- Students' Chinese scores
chinese int 
);
Copy the code

Insert data

insert into student(student_name,math,chinese) values
('Joe'.66.67),
('bill'.44.98),
('Cathy'.88.89);

Copy the code

View the data for the current table

Use GROUP BY to query the highest, average and lowest math scores of students

3.3. HAVING and group by

After using the group by clause, if you need to filter conditions, instead of where, you need to filter conditions using HAVING.

Select * from student where total score > 150
select student_name , sum(math+chinese) from student group by student_name having sum(math+chinses)>150;


Copy the code

Only Wang Wu scored more than 150.

3.4. Joint query

The above queries are all queries in a table, but in the actual application process, often is in several tables to query data, which uses the joint query. A multi-table query takes a Cartesian product of these tables to filter useful data. Before we learn about federated queries, we need to know what cartesian products are.

In general, the Cartesian product is just a list of two tables with all the possible numbers.

Data preparation:

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 
('Computer Department, Class 1, Grade 2019'.'Learned computer principles, C and Java languages, data structures and algorithms'),
('Class 3, Grade 2019, Chinese Department'.'Learned traditional Chinese literature'),
(Automation Class 5, Grade 2019.'Learned mechanical automation');

insert into student(sn, name, qq_mail, classes_id) values
('09982'.'Black Whirlwind Li Kui'.'[email protected]'.1),
('00835'.'Bodhi patriarch'.null.1),
('00391'.'Bai Suzhen'.null.1),
('00031'.'x'.'[email protected]'.1),
('00054'.'Don't want to graduate'.null.1),
('51234'.'Speak well'.'[email protected]'.2),
('83223'.'tellme'.null.2),
('09527'.'Foreigners learn Chinese'.'[email protected]'.2);

insert into course(name) values
('Java'), ('Traditional Chinese Culture'), ('Computer Principles'), ('Chinese'), ('Advanced 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),
-- Bodhi Lao Zu
(60.2.1), (59.5.2.5),
- su-cheng
(33.3.1), (68.3.3), (99.3.5),
- xu xian
(67.4.1), (23.4.3), (56.4.5), (72.4.6),
-- Don't want to graduate
(81.5.1), (37.5.5),
-- Be nice.
(56.6.2), (43.6.4), (79.6.6),
-- tellme
(80.7.2), (92.7.6);
Copy the code

3.4.1 track in connection

Select id from inner JOIN id from inner join id from inner join id from inner join ID from inner join ID

Example: query bai Suzhen’s scoreQuery the total score of all students:When writing complex SQL statements, it is highly recommended not to write them all at once. This is very easy to write wrong, SQL debugging can not, so the actual writing can be as follows, line by line. One condition at a time, so it’s clear.

selectStudent. Sn, student name, student. Qq_mail,sum(score.score) from student
join score
on student.id=score.student_id 
group by score.student_id;
Copy the code

2, select the field name from table 1 + alias (can write), table 2 + alias (can not write) where the connection condition and other conditions

Example: Query bai Suzhen’s score:

Query all students’ QQ mailbox and total score:

The results of the two methods are the same.

3.4.2 outer join

The difference between an inner join and an outer join is the difference between null values. There is no difference between an inner join and an outer join if there are no “NULL values” in the table.

First delete the grades and students table, create a new one and add three 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.'Joe');
insert into student values(null.'bill');
insert into student values(null.'Cathy');
Copy the code

Let’s look at the data for the current table

select * from student;
Copy the code

Insert three more rows of data into the score table, but make a little difference.

insert into score values(1.55);
insert into score values(2.85);
insert into score values(4.91);
Copy the code

This is a deliberate attempt to change the king’s ID from a 3 to a 4.This is where a data mismatch occurs. 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 the results of two tables?Query each student’s result, name information.

select student.name,score.score from student join 
score on student.id=score.studentId;
Copy the code

In the final result of the inner link, there is no information about Wang Wu’s grade and name, and there is no information about the student whose ID is 4.

An inner join is equivalent to taking the intersection of the two tables above.

Outer connection is divided into left outer connection and right outer connection relative to inner connection. Join left or right before join left or right before join

Left-outer join: The final result is to join the left table, as much as possible to display the information of the left table.

select student.name,score.score from student left
 join 
score on student.id=score.studentId;
Copy the code

Right-outer join: The final result is to join the table on the right and display as much information about the table on the right as possible.

select student.name,score.score from student right
 join 
score on student.id=score.studentId;
Copy the code

The rule 3.4.3 connection

Self-join is the same table joins itself to query.

Query information where Chinese score is higher than English score

The difference between this query and the past is that it is for the row and row query, such as the query of each student’s performance, name information. It’s all column to column.

And since it’s not easy to solve this problem, let’s think about it differently, so instead of comparing rows to rows, I’ll try to convert to comparing columns to columns.

1. Let’s look at what the Cartesian product looks like. The thing to notice is that if you select * from score,score; Queries will report errors. You need to create an alias using as.

select * from score as a,score as b;
Copy the code

I ended up with 400 lines, so I’ll put some of them here.

The next step is to further screen the useful information and pick out the information of the same classmate

select * from score as a,score as b where a.student_id=b.student_id;
Copy the code

Now it’s reduced to 62 linesThe last step is to get to the right place, but you need to go back to the Chinese and English ID, one is 4, the other is 6

insert into course(name) values
('Java'), ('Traditional Chinese Culture'), ('Computer Principles'), ('Chinese'), ('Advanced mathematics'), ('English');

Copy the code

So we’re going through the course ID filter

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;
Copy the code

And you’re left with one. To practice the information for those of you who scored higher in Java than in computer science, let’s go straight to 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;
Copy the code

Course_id is 1 for Java, and course_id is 3 for computer principles At this point, a row contains not only Java columns, but also computer principles columns. The effect of row to column is accomplished by self-concatenation.

Let’s look at the end result, the information that computer principles scored higher than 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;
Copy the code

3.5. Sub-query

A subquery is a query statement in another SQL statement. So it’s also called a nested query.

3.5.1 Single-row SubQuery

A single row subquery returns a single row subquery. Query the classmate of the student who does not want to graduate.

Or step by step, first query the class ID of the student who does not want to graduate.

select classes_id from student where name='Don't want to graduate';
Copy the code

Don’t want to graduate students in class one. Look for his classmates through this information in class one.

select name from student where classes_id=1;
Copy the code

We got the result we wanted with two queries, so how about a sub-query?

select name from student where
 classes_id=(select classes_id from student where name='Don't want to graduate');
Copy the code

In the above SQL statement, the second SQL statement is executed as a condition for the previous query name, which is the subquery.

3.5.2 Multi-Row Sub-Query

A multirow subquery returns multiple records

Select * from course id where English or Chinese is selected

select id from course where name='Chinese' or name='English';
Copy the code

2. In the course schedule, find the corresponding information according to the course ID

select * from score where course_id=4 or course_id=6;
Copy the code

Try again with multiple rowsMulti-line sub-query results in multiple records, and the in keyword is used for outer query. A single row subquery returns only one record using =.

 select * from score where course_id in(
select id from course where name='Chinese' or name='English');
Copy the code

3.6 merge query

Combining the results of multiple SELECT queries into one result is called a merge query. It’s kind of like taking a union. It contains two keywords union: ununion for duplicates. Union all does not ununion for duplicates. This is provided that both queries return the same number and type of columns.

Select a course whose ID is less than 3 or whose name is “English”

select * from course where id<3 or name ='English';
Copy the code

Using union query

select * from course where id<3 union select * from course where name='English';
Copy the code

The effect is the same.

Fourth, the end

That’s all for this time. Wish you all the best in your work and study. One unit