CRUD is what we often call addition, deletion, query and modification. It is also the most basic operation of the database
C: create add
R: retrieve find
U: update modification
D: Delete delete

Comment: in SQL - indicates a comment, which needs to be on a single line, or use comment comment.

1, Create

Let's create a student table first

-- Create a student table
create table student (
    id int,
    -- Student number
    sn int,
    -- full name
    name varchar(16),
    -- qq mailbox
    qq_mail varchar(17)
);

Use the insert into table name values to insert data into the table

Note: the quantity must be consistent with the quantity and order of the columns in the definition table

1. Single row data + full column insertion

insert into student values (1,20210001,'Liu Bei','12345@qq.com');
insert into student values (2,20210002,'Fei Zhang','1234566@qq.com');

2. Multi row data + full column insertion

insert into student values (3,20210003,'Guan Yu','1234567@qq.com'),
(4,20210003,'Zhuge Liang','10001@qq.com');

3. Multi row data + specified column insertion

We can also specify several separate columns for insertion. Note: the insertion order should be corresponding

-- Specify insert student number and name
insert into student (sn,name) values (5,'Cao Cao'), (6,'Zhou Yu'); 

Note: it is more recommended to insert multiple pieces of data in one statement, which will involve network transmission. If multiple pieces of data are inserted multiple times, it will involve multiple network transmission, and the overhead will be greater.

2, Query (Retrieve)

Let's create a test report first

-- Create test scores
create table exam_result (
    -- Student number
    id int,
    -- full name
    name varchar(20),
    -- language
    chinese decimal(3,1),
    -- mathematics
    math decimal(3,1),
    -- English
    english decimal(3,1)
);
-- insert data
insert into exam_result (id,name,chinese,math,english) values
    (1,'Liu Bei',77.5,98,56),
    (2,'Fei Zhang',80,66,77),
    (3,'Guan Yu',67,87,56),
    (4,'Cao Cao',77.5,78,66),
    (5,'Zhuge Liang',87,98.5,88),
    (6,'Song Jiang',80,76,65),
    (7,'Li Kui',50,30.5,45);

We use the select keyword to search

1. Full column query

Query all columns by select * from
Generally, it is not recommended to use * for full column query

1. The more columns you query, the more data you need to transmit;
2. It may affect the use of the index

select * from exam_result;

Query results

2. Specify column query

This method is more recommended than full column interpolation

Query data by select ing multiple column names from table names

-- Insert students' names and their corresponding math scores
 select name,math from exam_result;

Operation results:

3. The query field is an expression

We can add some simple expressions to the specified query results. The table queried by this operation is only a temporary table and does not affect the data of the original table.

1. Include a field

-- Add 10 points to the language score of the query
select id,name,chinese+10 from exam_result;

Operation results:

2. Contains multiple fields

-- Add 10 points for math and 5 points for English
select id,name,chinese,math+10,english+5 from exam_result;

Operation results:

3. Summation of multiple fields

We can sum the query results to get the total score

select name,chinese+math+english from exam_result;

Operation results:

4. Alias (as)

Specify an alias for the column in the query result, which indicates the returned result set, and take the alias as the name of the column

 select id,name,chinese+math+english Total score from exam_result;

Operation results

In order to better distinguish, we can also add as before the alias, and the effect is the same

 select id,name,chinese+math+english as Total score from exam_result;

Alias each field query

select id as Student number,name as full name,chinese as language, math as mathematics,english as English, chinese+math+english as Total score from exam_result;

5. Distinct

Use the query keyword select with the distinct keyword to remove duplicates

select distinct to duplicate column name from table name

-- Query without de duplication
select chinese from exam_result;

After weight removal

-- De duplication code
select distinct chinese from exam_result;

You can also specify multiple columns for de duplication. When specifying multiple columns, each column needs to be the same before de duplication.

-- Before weight removal
select chinese,math,english from exam_result;

After weight removal

select distinct chinese,math,english from exam_result;

Note: each de duplication operation actually involves comparison operation. There is no problem comparing integers. If you compare double or float, there may be errors.

decimal can be accurately compared

When comparing strings, the comparison rules will be specified through the keyword COLLATE when creating the database

6. Order by

Sort the query data through the order by keyword.

• ASC is in ascending order (from small to large)
• DESC is in descending order (from large to small)
• The default is ASC

be careful:
1. For queries without ORDER BY clause, the return order is undefined. Never rely on this order
2.NULL data sorting is regarded as smaller than any value. Ascending order appears at the top and descending order appears at the bottom

-- According to math scores
 select name,math from exam_result order by math;

Operation results:

Sort using expressions
Sort by total score from high to low

select id,name,chinese+math+english from exam_result order by chinese+english+math desc;

Operation results:

Sort alias using expression sort

select id,name,chinese+math+english as Total score  from exam_result order by chinese+math+english desc

Operation results:

Multiple fields are sorted, and the sorting priority varies with the writing order

select name,chinese,math,english from exam_result order by chinese ,math,chinese;

First sort according to the ascending order of Chinese. If the Chinese scores are the same, sort in descending order with the math scores. Similarly, if the Chinese math scores are the same, sort in descending order with English

Data can be sorted after de duplication

Before sorting

After de reordering

-- Reordering Chinese scores
select distinct chinese from exam_result order by chinese;

7. Query criteria (where)

Comparison operator:

Logical operators:

be careful:

1. WHERE conditions can use expressions, but not aliases.
2. AND takes precedence over OR. When used at the same time, you need to use parentheses () to wrap the priority part

Basic query:

 -- Query students who fail in English
 select name,english from exam_result where english < 60;

-- Query students whose Chinese scores are better than those in mathematics
select name, chinese, math from exam_result where chinese > math;

-- Query students whose total score is less than 250
select name,chinese+math+english as Total score from exam_result where chinese+math+english < 250；

-- Query the students whose total score is less than 250 and sort them in ascending order
select name,chinese+math+english as Total score from exam_result where chinese+math+english < 250  order by chinese+math+english;

AND and OR:

-- Query students whose Chinese score is greater than 80 and whose mathematics score is greater than 80
select name,chinese,math from exam_result where chinese > 80 and math > 80;

-- Query students whose math scores are greater than 80 or whose English scores are greater than 80
select name,math,english from exam_result where math > 80 or english > 80;

Range query

BETWEEN ... AND .
Note: between and, the left and right are closed intervals

-- Query language scores in[80,90]A classmate
select name,chinese from exam_result where chinese between 80 and 90;

This query can also be implemented with and

-- use and It can also be achieved
mysql> select name,chinese from exam_result where chinese >= 80 and chinese <= 90;

in

-- Find students whose math scores are 98 or 66 or 80 or 99
select name,math from exam_result where math in (98,66,80,99);
-- use or The same effect can be achieved
select name,math from exam_result where math = 98 or math = 66 or math = 80 or math = 90;

Fuzzy query: LIKE

Use% and_ You can use fuzzy queries
'%': can replace n characters
'': each can replace one character

All students in the current table

-- Check all students surnamed sun
select name from exam_result where name like 'Sun%';

Operation results

-- Query all students with two words of name and sex
select name from exam_result where name like 'Sun_';

Operation results

null query

When querying NULL, you cannot directly use =, you can use

1. <=>
2. is null

-- Both methods can judge whether it is null
select name chinese from exam_result where chinese <=> null;
select name chinese from exam_result where chinese is null;

Example:

Query is not null

-- Query English score is not null My classmate
select name chinese from exam_result where english is not null;

8. Paging query (limit)

If there are thousands of queried data, it is not suitable to be displayed on one page.

Syntax:

-- The starting subscript is 0
-- Filter from 0 n Article results
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- from s Start, filter n Article results
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- from s Start, filter n The result is more explicit than the second usage, and it is recommended to use
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

-- Query the first three data from 0
select id,name,chinese,math,english from exam_result limit 3;
-- The last 8 pieces of data from 3 in the query table
select id,name,chinese,math,english from exam_result limit 3,8;
-- The last 8 pieces of data from 3 in the query table are clearer and more recommended than the above method
select id,name,chinese,math,english from exam_result limit 8 offset 3;

-- Query 8 pieces of data from 3 and sort them by math scores
select id,name,chinese,math,english from exam_result order by math limit 8 offset 3;

Note: if you operate an online database, each SQL statement must carry a limit

9. Insert query results

Syntax:

INSERT INTO table_name [(column [, column ...])] SELECT ...

We can insert the query results of one table into another. We need to pay attention to the following points:

1. The fields in the results in the query table should correspond to the field types in the inserted table
2. The fields in the two tables do not necessarily correspond to each other, but the fields in the query results must exist in the inserted table and have the same type

Code example:
Create a student table and a user table, and insert two pieces of data

-- User table
create table user (
    id int primary key auto_increment,
    name varchar(20),
    age int,
    sex char
);
-- Student list
create table student (
    name varchar(10),
    sex char
);
insert into student values ('Li Kui','male');
insert into student values ('Song Jiang','male');

Insert the queried student table records into the user table through the select statement

insert into user (name,sex) select name,sex from student;

3, Modify (Update)

Modify the data through the update... set keyword

Note: when modifying data, you must add where. If you forget to add where, you may modify all data

Syntax:

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

Before modification

-- Change Sun Quan's math score to 80
update exam_result set math = 80 where name = 'Sun Quan';
-- Change Liu Bei's math score to 60 and his Chinese score to 70
update exam_result set math = 60,chinese = 70 where name = 'Liu Bei';

Before modification

-- Reduce the math scores of the top three students by 10 points
update exam_result set math = math-10 order by chinese+math+english desc limit 3;

-- Update the language scores of all students to twice the original
update exam_result set chinese = chinese*2；

4, Delete

Syntax:

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

-- Delete Sun Bin's score
delete from exam_result where name = 'Sun Bin';

Delete the data of the whole table

The difference between drop and drop is that the table is still there and the data is gone

delete from Table name;

summary

1. insert data

-- Single line insertion
insert into surface(Field 1, ..., field N) values (value1, ..., value N);
-- Multiline insertion
insert into surface(Field 1, ..., field N) values
(value1, ...),
(value2, ...),
(value3, ...)

1. Query: select

-- Full column query
select * from surface
-- Specify column query
select Field 1,Field 2... from surface
-- Query expression field
select Field 1+100,Field 2+Field 3 from surface
-- alias
select Field 1 alias 1, Field 2 alias 2 from surface
-- duplicate removal DISTINCT
select distinct field from surface
-- sort ORDER BY
select * from surface order by sort field
-- Condition query WHERE: 
-- (1)Comparison operator (2)BETWEEN ... AND ... (3)IN (4)IS NULL (5)LIKE (6)AND (7)OR
(8)NOT
select * from surface where condition
update surface set Field 1=value1, Field 2=value2... where condition
delete from surface where condition
 Bitko

1. Modify: update

update surface set Field 1=value1, Field 2=value2... where condition

1. Delete: delete

delete from surface where condition

be careful:

1. where conditions can use expressions, but not aliases
2. To judge whether it is null, use < = > or is null. If it is not null, use not null
3. When modifying data, you must add where. Once you forget to add where, you may modify all data
4. If you operate an online database, each SQL statement must be accompanied by a limit