1. constraint

mysql For data insertion / Modify some restrictions and verifications .

1.1 Constraint type

1.2 null constraint

Create table time , Specifies that a column is not empty

create table student(
  id int not null,
  name varchar(20),
  qq_mail varchar(20)
  );

1.3 unique Unique constraint

Specify that a column is unique Not repeated

1.4 default Default constraint

Specify when inserting , name The default value is unkown :

create table student(
  id int not null,
  name varchar(20) default 'unkown',
  qq_mail varchar(20)
  );

1.5 primary key Primary key constraint

Appoint id List as primary key

create table student(
  id int not null primary key,
  name varchar(20) default ‘unkown’,
  qq_mail varchar(20)
); 

For integer type primary keys , Often accompanied by self growth auto_increment To use . When the field corresponding to the inserted data does not give a value , Use the largest
value +1.

The primary key is NOT NULL and UNIQUE The combination of , Don't have to NOT NULL

1.6 foreign key Foreign key constraints

Foreign keys are used to associate with other tables Primary key or The only key

create table classes (
id nt primary key auto_increment,
name VARCHAR(20),
);

create table student (
   id int primary key auto_increment,
   name varchar(20) default 'unkown',
   qq_mail varchar(20),
   classes_id int,
   foreign key (classes_id) references   classes(id)
);

here classes Tables are responsible for constraining be called Parent table
student Table constrained be called Sub table

In the sub table class_id It must be the data existing in the parent table
If the parent table is empty Cannot insert sub table

When the parent table restricts the child table, it cannot be inserted at will / modify
The restriction of child table on parent table cannot be modified or deleted at will

2. Aggregate query

2.1 Aggregate functions

Common calculations can use mysql To implement :

2.2.1 count

Returns the number of rows owned by the column .(null Not included in the number of rows )

2.2.2 sum

Other uses are similar .

2.3 group by

Group and query the specified columns

select role,max(salary),min(salary),avg(salary) from emp group by role;

2.4 having

because group by after Can't use where Conditions inherent having Conditional clause .

Query average salary by role <1500 Information about