# MySQL Ⅱ add, delete, check and modify

### MySQL

- sql statement
- Table operations
- increase
- delete
- Inquire
- Full column query
- Specify column query
- The query field is an expression
- Check all math scores +10
- Check the total score
- Deduplication (distinct)
- note
- Order by
- Ascending
- Descending
- note
- to sum up
- Conditional query (where)
- Paging query (limit)
- modify

# sql statement

## Table operations

Addition, deletion, review and modification (CURD)

Take this `student`

table as an example:

```
drop table if exists student;
create table student(
id INT,
sn INT,
name VARCHAR(20),
qq_mail VARCHAR(20)
);
```

### increase

- Single-column full-column insertion,

only one row can be inserted at a time, and each field must correspond to the database

```
insert into student values(1,1001,"张三","[email protected]");
insert into student values(2,1002,"李四","[email protected]");
```

- Insert multiple rows in a specified column

```
insert into student(id,sn,name) values(3,1003,"王五"),(4,1004,"赵六");
//(id,sn,name)为指定列，数据之间用逗号隔开
```

If the value of a certain field is not specified, then the default is NULL

- Insert data, if it exists, please ignore

```
insert ignore into student(id,sn) values(1,1001);
```

### delete

Delete the entire table

```
delete from student;
```

Delete a certain data in the table

```
delete from student where id=1;
//删除id=1的行
```

MySQL does not allow deleting the original table data while querying. You

must give the original data table an alias and then delete it.

### Inquire

Take the `exam_result`

table as an example

```
drop table if exists exam_result;
create table exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
```

```
insert into exam_result (id,name, chinese, math, english)
values
(1,'A', 67, 98, 56),
(2,'B', 87.5, 78, 77),
(3,'C', 88, 98, 90),
(4,'D', 82, 84, 67),
(5,'E', 55.5, 85, 45),
(6,'F', 70, 73, 78.5),
(7,'G', 75, 65, 30),
(8,'H', 78, 32, 98);
```

#### Full column query

```
select * from exam_result;
// * 代表当前表中的所有字段
```

#### Specify column query

```
select id,name from exam_result;
```

#### The query field is an expression

```
select id,name,10 from exam_result;
//10：此列都为10
```

#### Check all math scores +10

```
select id,name,math+10 from exam_result;
```

#### Check the total score

```
select id,name,math+chinese+english from exam_result;
```

Alias:

```
select id,name,math+chinese+english as 总成绩 from exam_result;
//as 可以省略
```

Acts as an alias:

Simplify the display or column name, easy to view, do not change the name of the table itself

#### Deduplication (distinct)

Math scores:

```
select distinct math from exam_result;
```

##### note

If written as

```
//1.
select id,name,distinct math from exam_result;
```

Errors must be put `distinct`

first

```
//2.
select distinct math, id,name from exam_result;
```

The reason why there is no de- duplication is: **de-** duplication **is for all fields** , and only when three fields are repeated at the same time, will the de-duplication be removed

#### Order by

##### Ascending

Sort math scores from lowest to highest, including null

```
select * from exam_result order by math asc;
```

##### Descending

Sort math scores from high to low, including null

```
select * from exam_result order by math desc;
```

###### note

desc is the keyword

eg: if you want to create a table named desc, you need to add `

###### to sum up

- order by asc and order by default to sort from low to high
- order by desc is sorted from high to low
- For MySQL keywords, when used as variable names, add the symbol `
- When the column to be sorted is NULL, asc NULL is at the top and desc NULL is at the end
- Multiple fields can be sorted, and the priority follows the writing order

Query the results of each subject, displayed in descending order of mathematics, ascending English, and ascending Chinese

```
select * from exam_result order by math desc,chinese,english;
```

#### Conditional query (where)

- Query students with math scores> 80 and their scores

```
select id,name,math from exam_result where math > 80;
```

- Query students with math scores> 80 and their scores, and sort them in ascending order

```
select id,name,math from exam_result where math > 80 order by math;
```

- Query students with math score=98 and their scores

```
select id,name,math from exam_result where math = 98;
```

Note: If the result is null, it cannot be used `=`

. It cannot be found. Use it`<=>`

- Check math scores!=98 students and scores

```
select id,name,math from exam_result where math != 98;
```

Note: If the result is null, it cannot be used `!=`

. It cannot be found. Use it`<>`

- Query math scores in 80~90

```
select id,name,math from exam_result where math between 80 and 90;
```

Is equivalent to:

```
select id,name,math from exam_result where math >= 80 and math <= 90;
```

Therefore, the interval between… and is a closed interval, eg: [80,90]

- Query students whose math scores are 98, 85, 73

```
select id,name,math from exam_result where math in (98,85,73);
```

Is equivalent to:

```
select id,name,math from exam_result where math = 98 or math = 85 or math = 73;
```

- Query whether the math score is null

Let's first insert a few students whose math scores are null

```
insert into exam_result (id,name,chinese,english) values(9,'I',78,97);
insert into exam_result (id,name,chinese,english) values(10,'J',56,32);
```

Query math score is null

```
select id,name,math from exam_result where math is null;
```

Query math score is not null

```
select id,name,math from exam_result where math is not null;
```

- Fuzzy query

Query names containing "three"

```
select * from exam_result where name like '%三%';
```

```
//代表一定是 三 结尾的
select * from exam_result where name like '三%';
//代表一定是 三 开头的
select * from exam_result where name like '%三';
```

%: represents a wildcard, represents any number of characters

-: represents one character

```
//代表一定是 三× 两个字
select * from exam_result where name like '三_';
//代表一定是 三×× 三个字
select * from exam_result where name like '三_ _';
```

Note: and has a higher priority than or, and parentheses should be added when used at the same time

#### Paging query (limit)

The reason: When the data is too big, one-time finding data, the system will execute SQL statements, query needs time, then the system will likely be stuck, so, in general, the optimal solution is to use **paging query**

Principle: Only query the data that needs to be displayed on the current page each time.

If there are 10 pieces of data per page, then only 10 pieces of data are queried. Each time you click on the next page, you will be asked to query 10 pieces of data. This improves efficiency

```
//方法1
select * from exam_result limit s,n;
//s 表示偏移位置 n 表示数据个数
//即从s开始取n个数据
```

eg:

```
select * from exam_result limit 0,5;
```

If the value of n is too large, only the content that can be queried will be queried.

If the value of s is too large, SQL will not report an error, but nothing will be found.

If s is not written, the default is to take n from offset 0

```
select * from exam_result limit 5;
```

```
//方法2
select * from exam_result limit n offset s;
```

eg:

```
select * from exam_result limit 5 offset 0;
```

### modify

- Modify the mathematics score of class B to 99

```
update exam_result set math = 99 where name = 'B';
```

- Change the mathematics score of C student to 60, and the Chinese score to 80

```
update exam_result set math = 60,chinese = 80 where name = 'C';
```

- The mathematics score of the students whose total score is the bottom three is +30

For complex sql statements, you can use step-by-step thinking, and finally write the statement

①Find the students with the bottom three grades

```
select id,name,chinese+math+english sum from exam_result order by chinese+math+english limit 3;
```

Note: As long as one of the scores is null, the total score will be null.

Because null cannot use any operator to perform operations with other fields or variables,

it is not recommended to use order by and limit together

②Update math scores

```
update exam_result set math = math + 30;
```

The final wording is:

```
update exam_result set math = math + 30 order by chinese+math+english limit 3;
```