Complete syntax, constraints and other query keywords for creating tables

Table of Contents

1. Complete syntax for creating a table

2. Constraints

1. unsigned —–》Unsigned

2. zerofill0 —–》Filling

3. default —–》Default value

4. not null —–》not empty

5. unique —–》only

6. primary key —–》primary key

7.auto_incrment

3. The function of numbers in brackets of integers

4. Two ways to clear the table

5. Add some other SQL statements

6. Data preparation

7. Query keywords

1. where

2. group by group

3.having filter

4. distinct deduplication

5. order by sorting

6. limit paging

7. regexp regular

1. Complete syntax for creating a table

Syntax:

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

Example:

create table db1.t1(
id int,
    name varchar(43),
    age int
);

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

Note: 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 actually constraints based on data types.

Category:

1. unsigned —–》Unsigned

id int unsigned

2. zerofill0 —–》Fill

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 —–》only

Single column unique

create table t6 (id int, name varchar(32) unique);

Multiple columns unique

create table t7 (
        id int,
        ip varchar(32),
        port varchar(32),
        unique(ip, port)
);

6. primary key —–》Primary key

From a purely constraint perspective, the primary key is equivalent to being non-null and unique, unique not null

The primary key itself is an index, and the index can speed up queries.

id unique not null ---------> id primary key
create table t8 (id int primary key);
create table t8 (id int unique not null);

The InnoDB storage engine stipulates that every table must have a primary key. However, the tables I created before did not specify a primary key. How was the table created successfully?

It’s because 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 successfully create the table. Therefore, when we create a table in the future, we will actively create a primary key. The primary key we create can speed up the query because it is an index.

In general, which field should be created for the primary key?

Most of them have added the id field. Therefore, every table must have an id field, and a table not only has one primary key, but can have multiple primary keys. However, in most cases, there is only one.

We can determine the only record in a table through the primary key!!!

The primary key is generally given to id aid sid uid pid...
create table t(
    id int primary key,
    namevarchar(32)
)

7. auto_incrment

Auto-increment: Each time you take the initiative, it increases by 1 from the previous time.

Generally, it is used with primary keys.

create table t9 (
    id int primary key auto_increment,
    namevarchar(32)
);

3. The function of numbers in brackets of 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);

4. Two ways to clear the table

truncate: It is recommended to use truncate. Use this. If you clear it by mistake, you still have a chance to recover.

mysql has many log files. The binlog log—–” can recover data and records all your SQL statements.

like:

delete from t; -----> will not reset the id value

truncate t9; -----> Clear the table and reset the id value

5. Add some other SQL statements

1. Modify table name

ALTER TABLE table name
                          RENAME new table name;

2. Add fields

ALTER TABLE table name
                          ADD field name data type [integrity constraints...],
                          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;

3. Delete fields

ALTER TABLE table name
                          DROP field name;

4. 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...];
      ALTER TABLE table name
                          CHANGE old field name new field name new data type [integrity constraints...];

Supplement: In addition to the above SQL statements, there are many others. If you don’t know how to write them, just use the search engine directly.

6. Data preparation

company.employee
    employee id id int
    Name emp_name varchar
    sex sex enum
    age age int
    Join date hire_date date
    Post post varchar
    Job description post_comment varchar
    salary salary double
    office office int
    Department number depart_id int

Create table

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', # Most of them are 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
);

View table structure

mysql> desc employee;
 + -------------- + ----------------------- + ------ + --- -- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
 + -------------- + ----------------------- + ------ + --- -- + --------- + ---------------- +
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
 + -------------- + ----------------------- + ------ + --- -- + --------- + ---------------- +

Insert record
Three departments: teaching, sales, operations

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);

ps: If you insert Chinese characters in the Windows system and the select result is blank, you can set all character encodings to gbk

7. Query keywords

1. where

where ——>filter conditions

"""
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%';
"""

where filter function

"""
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%se';
"""

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 the 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 come into contact with mysql queries, it is recommended to 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 and salary if the employee’s name is composed of four characters

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 not between 3 and 6;

6. Query data whose salary is not in the range of 20,000, 18,000, and 17,000

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 ''''

2. group by group

Grouping: Divide individual individuals into wholes according to certain specified conditions.

eg: Group people according to men and women
Group by skin color
Group by age

After grouping, by default you can only go directly to the basis of the grouping, and no other data can be obtained directly.

For 5.6, you need to set sql_mode yourself

set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

Grouping is generally used 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.

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, otherwise the 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'

Get the highest salary for each department

'Statistics on group data in groups>>>Aggregation query (aggregate 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.

Group supplementary functions

group_concat —> used after grouping

If you really need to get data fields other than grouping, 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;

3. having filter

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

here Filter data before grouping
having to filter data after grouping

Q: 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;

Group them again 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;

4. distinct deduplication

distinct: remove duplicates

Is it meaningful to deduplicate data with primary keys? No, the primary key itself is unique.

select distinct id,age from emp;

5. order by sort

select * from emp order by salary; ---> Default ascending order
select * from emp order by salary desc; ---> Sort in 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

# First sort by age in descending order, and then sort by salary in ascending order if they are the same age.
select * from emp order by age desc,salary;

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

'Ascending'
select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000;

'Descending'
select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000 order by avg(salary) desc;

6. limit paging

Limit paging: limit the number of display items

select * from emp limit 3;# Display three pages

Find details 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, and the second parameter indicates the number of items, not the index position.
select * from emp limit 5,5;

7. regexp regular

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