当前位置：网站首页>Adding, deleting, querying and modifying MySQL tables (II)

Adding, deleting, querying and modifying MySQL tables (II)

2022-07-20 23:39:00 【51CTO】

One 、 Database Constraints

There are six ：

NOT NULL： Can't store NULL value
UNIQUE：
DEFAULT： The default value is given when there is no assignment
PRIMARY KEY：
FOREIGN KEY
CHECK

       
       //
       
       not 
       
       null constraint ： Create table time , A specified column cannot be empty 
       
       // for example ：name 
       
       varchar
       
       (
       
       20
       
       ) 
       
       not 
       
       null
       
       ;
       
       //      here name Can no longer be assigned to a null value ;
       
       //unique constraint ： Specify that a column is unique 
       
       // for example ：name 
       
       varchar
       
       (
       
       20
       
       ) unique
       
       ;
       
       //      At this time, items with duplicate names cannot appear in the table ;（ By default, the same data can be repeated ）
       
       //default constraint ： Set the default value , No settings. The default is null
       
       // for example ：name 
       
       varchar
       
       (
       
       20
       
       ) default 
       
       ' unnamed '
       
       ;
       
       //      If it is not named, it is automatically set to “ unnamed ”;
       
       //primary key constraint ： Primary key constraint ： Indicates the identity of a record , Used to distinguish from other records 
       
       //                 Gradually cannot be empty ; Can't repeat ; There can only be one ;
       
       // Since the primary key ：auto_increment
       
       //foreign key constraint ： Foreign key constraints ： Primary key or unique key used to associate other tables ;
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

MySQL Addition, deletion, check and modification of tables （ Two ）_ union

summary ：

The default value is null Can pass default To modify the ;
primary key Can't be empty , Can't repeat ;
The auto increment primary key can be set to null, It can also be set manually ;
When using foreign keys , When the parent table is empty , Cannot insert data into sub table ;

Two 、 Add and query

       
       // newly added ： Insert data from one table into another ;
       
       //     
       
       insert 
       
       into  Table name 1
       
       ( Name 1
       
       ,,...
       
       ) 
       
       select  Name 1
       
       ,... 
       
       from  Table name 2
       
       ;
       
       // for example ：insert 
       
       into class 
       
       (studentID
       
       ,studentName
       
       ) 
       
       select ID
       
       ,Name 
       
       from student
       
       ;
      
1.
2.
3.

2.1、 Aggregate query

Aggregate functions ：

count( Name )： Return the number of data queried ;
sun( Name )： Return the sum of the query data ;
avg( Name )： The average value of the data returned to the query ;
max( Name )： Return to the maximum value of the queried data ;
min( Name )： Return to the minimum value of the queried data ;

       
       // Aggregate query ：
       
       //
       
       group 
       
       by Clause ：
       
       // for example ：select gender
       
       ,max
       
       (score
       
       )
       
       ,min
       
       (score
       
       )
       
       ,avg
       
       (score
       
       ) 
       
       from student 
       
       group 
       
       by genfer
       
       ;
       
       //      Group according to sex ;
       
       // Query after aggregation ：
       
       // stay group 
       
       by After Clause grouping , Need to use having Filter and group again ;
       
       // for example ：select gender
       
       ,avg
       
       (score
       
       ) 
       
       from student 
       
       group 
       
       by gender 
       
       having avg
       
       (score
       
       )
       
       >
       
       70
       
       ;
       
       //      Check the gender and average score and place them in 80 The above is grouped by gender ;
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

MySQL Addition, deletion, check and modification of tables （ Two ）_ polymerization _02

2.2、 The joint query

The cartesian product ：select * from Table name , Table name 2;

2.2.1、 Internal connection

       
       select  Table name 1. Name  
       
       ,  Table name 2. Name  
       
       from  Table name 1 
       
       join  Table name 2 
       
       on  Conditions 
       
       ;
       
       // for example ：select score
       
       .student_id 
       
       , student
       
       .student_id 
       
       from student 
       
       join score 
       
       on student
       
       .student_id 
       
       = score
       
       .score_id
       
       ;
       
       //
       
       select  Table name 1. Name 
       
       , Table name 2. Name  
       
       from
      
1.
2.
3.
4.
5.

2.2.2、 External connection

The outer connection is divided into left outer connection and right outer connection

       
       // Left connection ： send join Left table 1 All the data are displayed 
       
       select  Name  
       
       from  Table name 1 left 
       
       join  Table name 2 
       
       on  Connection condition 
       
       ;
       
       // The right connection ： yes join Table on the right 2 All the data are displayed 
       
       select  Name  
       
       from  Table name 1 right 
       
       join  Table name 2 
       
       on  Connection condition 
       
       ;
      
1.
2.
3.
4.

2.2.3、 Self join

       
       // Query in its own table 
       
       , Row to column query 
       
       select 
       
       * 
       
       from  Table name  
       
       as s1
       
       , Table name  
       
       as s2
       
       ;
       
 In the case of self connection, you need to alias the table ;
      
1.
2.
3.

2.2.4、 Subquery

       
       // for example ：
       
       // When there is only one query result , use ‘
       
       =’
       
       //
       
       select name
       
       ,score 
       
       from student 
       
       where student
       
       .id 
       
       = 
       
       (
       
       select id 
       
       from class 
       
       where name 
       
       = 
       
       ' Zhang San '
       
       )
       
       ;
       
       // When there are multiple query results , use ‘in’
       
       ;
       
       select name 
       
       from student 
       
       where student
       
       .name 
       
       in 
       
       (
       
       select id 
       
       from class 
       
       where student
       
       .id 
       
       = 
       
       '123' 
       
       or student
       
       .id 
       
       = 
       
       '456'
       
       )
       
       ;
       
1.
2.
3.
4.
5.
6.

2.2.5、 Merge query

       
       //
       
       union duplicate removal ：
       
       // for example ： Age is less than 18 Or the score is less than 60
       
       select 
       
       * 
       
       from student 
       
       where age 
       
       < 
       
       18 
       
       union 
       
       select 
       
       * 
       
       from student 
       
       where score 
       
       < 
       
       60
       
       ;
       
       //
       
       union all No weight removal ：
       
       // for example ： Age is less than 18 Or the score is less than 60
       
       select 
       
       * 
       
       from studnet 
       
       where age 
       
       < 
       
       18
       
       union all
       
       select 
       
       * 
       
       from student 
       
       where score 
       
       < 
       
       60
       
       ;
       
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

summary ：

When there are multiple tables for conditional filtering , First join, In the after on Add filter criteria later ;
The inner connection represents the data shared by two tables , The left outer connection indicates join The data on the left contains , The right outer connection indicates join The data on the right contains （ If it doesn't work null Add ）;
When self connecting , You need to alias the table ;
Subquery has only one result ‘=’, Multiple results ‘in’;
union duplicate removal ,union all No weight removal ;

原网站

版权声明
本文为[51CTO]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/201/202207192244465904.html