MySQL Ⅱ add, delete, check and modify

MySQL

sql statement

Table operations

Addition, deletion, review and modification (CURD)

Take this studenttable as an example:

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

increase

  1. 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]");
  1. 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

  1. 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_resulttable 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;
// * 代表当前表中的所有字段
Insert picture description here

Specify column query

select id,name from exam_result;
Insert picture description here

The query field is an expression

select id,name,10 from exam_result;
//10:此列都为10
Insert picture description here

Check all math scores +10

select id,name,math+10 from exam_result;
Insert picture description here

Check the total score

select id,name,math+chinese+english from exam_result;
Insert picture description here


Alias:

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


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:

Insert picture description here
select distinct math from exam_result;
Insert picture description here
note

If written as

//1.
select id,name,distinct math from exam_result;
Insert picture description here


Errors must be put distinctfirst

//2.
select distinct math, id,name from exam_result;
Insert picture description here


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;
Insert picture description here
Descending

Sort math scores from high to low, including null

select * from exam_result order by math desc;
Insert picture description here
note

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

Insert picture description here
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;
Insert picture description here

Conditional query (where)

  1. Query students with math scores> 80 and their scores
select id,name,math from exam_result where math > 80;
Insert picture description here
  1. 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;
Insert picture description here
  1. Query students with math score=98 and their scores
select id,name,math from exam_result where math = 98;
Insert picture description here


Note: If the result is null, it cannot be used =. It cannot be found. Use it<=>

  1. Check math scores!=98 students and scores
select id,name,math from exam_result where math != 98;
Insert picture description here


Note: If the result is null, it cannot be used !=. It cannot be found. Use it<>

  1. Query math scores in 80~90
select id,name,math from exam_result where math between 80 and 90;
Insert picture description here


Is equivalent to:

select id,name,math from exam_result where math >= 80 and math <= 90;
Insert picture description here


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

  1. Query students whose math scores are 98, 85, 73
select id,name,math from exam_result where math in (98,85,73);
Insert picture description here


Is equivalent to:

select id,name,math from exam_result where math = 98 or math = 85 or math = 73;
Insert picture description here
  1. 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);
Insert picture description here


Query math score is null

select id,name,math from exam_result where math is null;
Insert picture description here

Query math score is not null

select id,name,math from exam_result where math is not null;
Insert picture description here
  1. 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;
Insert picture description here


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;
Insert picture description here

modify

  1. Modify the mathematics score of class B to 99
update exam_result set math = 99 where name = 'B';
Insert picture description here
  1. 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';
Insert picture description here
  1. 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;
Insert picture description here


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;
Insert picture description here