SQL statement constraints

Table of Contents

1. Complete syntax for creating a table

2. Constraints

3. Supplement

1. The role of the numbers in brackets in the integer type

2. Two ways to clear the table

3. Some other SQL statements

4. Query keywords

1. Data preparation

2.where keyword query

3.group by keyword grouping

4.Having keyword filtering

5.order by keyword sorting

6.limit keyword paging

7.regexp keyword regular

---

1. Complete syntax for creating a table

create table t1(
id int,
    name varchar(32),
    age int
);

create table library name.table name(
Field name 1 Data type Constraints Constraints Constraints Constraints,
Field name 2 Data type Constraints Constraints Constraints Constraints,
Field name 3 Data type Constraints Constraints Constraints Constraints,
Field name 4 Data type Constraints Constraints Constraints Constraints,
Field name 5 Data type Constraints Constraints Constraints Constraints,
Field name 6 Data type Constraints Constraints Constraints Constraints
);

insert into library name.t1 values(‘1’,2,3,4,5,6);

1. The field name and data type must be written and cannot be omitted.
2. Constraints are optional, dispensable, and there can be multiple
3. The last field in the table structure cannot have a comma

2. Constraints

Constraints are constraints based on data types.

1. unsigned (unsigned)
   id int unsigned
2. zerofill (0 padding)
3. default (default value)
   create table t4 (id int, name varchar(32) default ‘kevin’);
   insert into t4 values(1, ‘jerry’);
   insert into t4(id) values(1);
4. not null (not empty)
   create table t5 (id int, name varchar(32) not null);
   insert into t5(id) values(1);
5. unique
   1. Single column unique:
      create table t6 (id int, name varchar(32) unique);
   2. Unique in multiple columns:
      create table t7 (
      id int,
      ip varchar(32),
      port varchar(32),
      unique(ip, port)
      );
6. primary key
   From a purely constraint perspective, the primary key is equivalent to being non-null and unique, unique not null
   id unique not null ———> id primary key
   create table t8 (id int primary key);
   create table t8 (id int unique not null);
   **The primary key itself is an index, and the index can speed up the query**
   ######There is a hidden primary key inside the InnoDB storage engine. We cannot see this primary key, and it cannot speed up the query. It is just to help us create the table successfully. Therefore, when we create the table in the future, Actively create a primary key. The primary key we create can speed up the query because it is an index######
7. auto_incrment (self-increment)
   Each active time increases by 1 compared to the previous time. Generally, it is used with the primary key.
   create table t9 (
   id int primary key auto_increment,
   namevarchar(32)
   );

Three.Supplement

1. The role of numbers in brackets in integer type

id int(10) # Numbers do not represent ranges
name varchar(32) #The number represents the storage range

create table t1(id int(3));
create table t2(id int(9));
insert into t2 values(9);


create table t3(id int(9) zerofill);
insert into t3 values(9);

2. Two ways to clear the table

• delete from table name; (the id value will not be reset)
• truncate t9; (clear the table and reset the id value)

truncate: It is recommended to use truncate. Use this. If you clear the wrong table, you still have a chance to recover.

MySQL has many log files. The binlog log records all SQL statements used and can be used to restore data.

3. Some other SQL statements

• Modify table name
  • ALTER TABLE table name RENAME new table name;
• Add field
  • ALTER TABLE table name ADD field name data type [integrity constraints…],
    ALTER TABLE table name ADD field name data type [integrity constraints…] FIRST;
    ALTER TABLE table name ADD field name data type [integrity constraints…] AFTER field name;
• Delete field
  • ALTER TABLE table name DROP field name;
• Modify fields (modify can only change the field data type integrity constraints, but cannot change the field name, but change can!)
  • ALTER TABLE table name MODIFY field name data type [integrity constraints…];
    ALTER TABLE table name CHANGE old field name new field name old data type [integrity constraints…];

4. Query keywords

1. Data preparation

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', #mostly men
  age smallint(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #one department and one room
  depart_id int
);

insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('tom','male',78,'20150302','teacher',1000000.31,401,1),#The following is the teaching department
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('haha','female',48,'20150311','sale',3000.13,402,2),#The following is the sales department
('hehe','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('Lele','female',18,'20160513','sale',3000.29,402,2),
('Lara','female',28,'20170127','sale',4000.33,402,2),
('Senglong','male',28,'20160311','operation',10000.13,403,3), #The following is the operation department
('Cheng Yaojin','male',18,'19970312','operation',20000,403,3),
('Cheng Yaoyin','female',18,'20130311','operation',19000,403,3),
('Cheng Yaotong','male',18,'20150411','operation',18000,403,3),
('Cheng Yatie','female',18,'20140512','operation',17000,403,3);

2.where keyword query

where: filter conditions to use

Fuzzy query: no clear filter conditions

Keywords: like

Key symbols: %: matches any number of any characters

_: Matches any single number of characters

show variables like ‘%mode%’;

# 1. Query data with ID greater than or equal to 3 and less than or equal to 6
select id,name from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;

# 2. Query data whose salary is 20,000 or 18,000 or 17,000
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000); # Abbreviation

# 3. Query the names and salaries of employees whose names contain the letter o
# When you first get started with mysql queries, it is recommended that you spell out your sql statements in order of query priority.
"""
First check which table from emp
Then according to what conditions to check where name like %o%’
Then filter and display the queried data select name,salary
"""
select name,salary from emp where name like '%o%';

# 4. Query the employee’s name which is composed of four characters and their salary
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;

# 5. Query data with ID less than 3 or greater than 6
select * from emp where id is not between 3 and 6;

# 6. Query data whose salary is not in the range of 20000, 18000, 17000
select * from emp where salary not in (20000,18000,17000);

# 7. Query the employee name and position name whose job description is empty. You cannot use the equal sign for null, you can only use is.
select name,post from emp where post_comment = NULL; # The query is empty!
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

'''In sql, NULL is different from ''''

3.group by keyword group

Grouping: Divide individual individuals into wholes according to a specified condition

For example: group by height, group by age, group by gender, etc.

After grouping in MySQL, you can only get the basis for grouping! According to which field is grouped, you can only get the value of this field, but not other fields.

Simple grouping is meaningless. Grouping is generally used together with aggregate functions:

sum max min avg count

Grouping keyword: group by

Data grouping application scenarios: average salary of each department, male to female ratio, etc.

After grouping, all field information can be obtained by default
The data displayed after grouping is the first piece of data in each group

# 1.Group by department
select * from emp group by post; # After grouping, the first piece of data of each group is taken out
select id,name,sex from emp group by post; # Verify
"""
Setting sql_mode to only_full_group_by means that in the future, only the basis for grouping can be obtained.
You should not get the value of a single element in the group. In that case, grouping will be meaningless, because not grouping is to obtain the information of a single element at will.
"""
set global sql_mode="strict_trans_tables,only_full_group_by";
# Reconnect client
select * from emp group by post; # Report error
select id,name,sex from emp group by post; # Report error
select post from emp group by post; # Get department information
# Emphasis: As long as it is grouped, it is no longer possible to "directly" find individual data information, only the group name can be obtained


# 2. Get the maximum salary of each department
# Statistics of data within the group in units of groups >>> Aggregation query (aggregated together into one result)
#Maximum salary per department
select post,max(salary) from emp group by post;
Supplement: You can also give aliases to fields when displaying them
select post as 'department',max(salary) as 'maximum salary' from emp group by post;
as can also be omitted, but it is not recommended because the meaning is unclear.
# Minimum wage per department
select post,min(salary) from emp group by post;
# Average salary per department
select post,avg(salary) from emp group by post;
#The total salary of each department
select post,sum(salary) from emp group by post;
# Number of people in each department
select post,count(id) from emp group by post;
When counting, as long as it is a non-empty field, the effect will be the same.
Here the age, salary, id are displayed and finally the special case post_comment

is demonstrated.

Grouping supplementary function

# group_concat is used after grouping
If you really need to get data fields other than the group, you can use group_concat()
# Employee names for each department
select post,group_concat(name) from emp group by post;

select post,group_concat(name,'|',sex) from emp group by post;

select post,group_concat(name,'|',sex, '|', gender) from emp group by post;

select post,group_concat(distinct name) from emp group by post;

select post,group_concat(distinct name separator '%') from emp group by post;
# concat is not used in groups
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;

#concat_ws()
select post,concat_ws('|', name, age, gender) from emp group by post;

4.having keyword filtering

Where and having are both filtering functions, but there are differences

• where to filter data before grouping
• having to filter data after grouping

Calculate the average salary of employees over 30 years old in each department, and retain departments with an average salary greater than 10,000.

# First filter out those over 30 years old
select * from emp where age > 30;

# Grouping by department
select avg(salary) as avg_salary from emp where age > 30 group by post;

# Retain departments with average salary greater than 10,000
select avg(salary) as avg_salary from emp where age > 30 group by post having avg(salary) > 10000;

5.order by keyword sorting

• select * from emp order by salary; default ascending order
• select * from emp order by salary desc; descending order

Multi-field sorting, if you want the subsequent field sorting to take effect, the premise is: the previous sorting fields must be the same

Calculate the average salary of employees over 20 years old in each department, and retain departments with an average salary greater than 1,000, and then sort the average salary

# Employees over 20 years old
select * from emp where age > 20;
# Average salary by department
select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000;
#
select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000 order by avg(salary) desc;

6.limit keyword paging

# Limit the number of display items
select * from emp limit 3;
# Query the detailed information of the person with the highest salary
select * from emp order by salary desc limit 1;

#Page display
select * from emp limit 0,5; # The first parameter indicates the starting position, the second parameter indicates the number of items, not the index position
select * from emp limit 5,5;

7.regexp keyword regular expression

select * from emp where name regexp "^j.*(n|y)$";

'''
Starts with j and ends with n, anything in between
Start with a certain letter and end with a certain letter
'''