MySQL-DML语句

Create

单行插入数据

insert into tb_name [(要插入的属性列...)]---全列插入时可省略 values(属性值);
1

🚀像学生表中插入一条记录

insert into student values('1003','李四','男');
1

多行插入数据

insert into ... values(),(),()...;//多条记录用分号隔开
1

🚀向学生表中插入多条数据

insert into student values('1004','王五','女'),('1005','赵六','男'),('1006','田七','女');
1

插入否则更新

insert ... on duplicate key update 属性名 = 属性值，属性名 = 属性值...；
1

🚀当我们插入某条记录时可能会和表中已经存在的数据发生键值冲突，此时使用上述语句当发生冲突时修改原表中冲突的语句。

insert into student values('1002','王二麻子','男') on duplicate key update sno='1002',sname='王二麻子',sex='男';
1

replace into

🚀replace into的意思是，当插入记录时不发生键值冲突那就相当于普通的insert into，如果发生冲突那么就将新插入的数据替换老的数据。

replace into student values('1008','孙悟空','男');
replace into student values('1006','沙悟净','男');
1
2

插入学号为1008的数据时没有发生冲突，插入学号为1006的数据时发生了冲突并且将老数据进行了替换。

查看受影响行数的函数

🚀在用户进行CRUD操作结束时，通常会显示此条请求影响了几行数据。

select row_count(); //也可以使用此函数查看
1

Retrieve

SELECT [DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
1
2
3
4
5

🚀后序的查/改/删都建立在下面表结构的基础上。

create table exam(
	id int primary key,	
	name varchar(10) not null,
	chinese int,
	math int,
	english int);
1
2
3
4
5
6

🚀在表中插入一些数据。

insert into exam values
    (1,'张三',67,98,56),
    (2,'李四',87,78,77),
    (3,'王五',88,98,90),
    (4,'赵六',82,84,67),
    (5,'田七',55,85,45),
    (6,'小明',70,73,78),
    (7,'小红',75,65,30);
1
2
3
4
5
6
7
8

全列查询

select *from tb_name;
1

指定列查询

select col1,col2... from tb_name;
1

🚀查询成绩表中的人员信息与数学成绩

select id,name,math from exam;
1

select+函数/表达式

🚀select后可以跟函数调用，例如查看当前时间等。

select current_time();
1

🚀select 后面还可以跟表达式。

select 10 + 20;
1

对查询出的列重命名-as

select col1 as 名字,... ...;
1

🚀查询出每个人的信息与总成绩

select id,name,chinese + math + english as total from exam;
1

distinct

select distinct ...; //可以对查询的结果做去重
1

order by

select ... order by col1 [asc | desc],col2[asc|desc],...;
//默认为asc升序排序
//desc降序排序
//可以根据多列进行排序，规则是当col1数据相同时，再根据col2数据排序...
1
2
3
4

🚀根据学生的总成绩进行排序

select id,name,chinese + math + english as total from exam order by total desc;
1

🚀按数学降序，语文升序，英语升序的规则查询

select * from exam order by math desc,chinese asc,english asc;
1

limit

1.limit n//显示前n行数据
2.limit s,n//从s行开始显示n行数据，行数是从第0行开始的
3.limit n offset s//显示n行数据从第s行开始显示
1
2
3

🚀找出总分前三名的学生信息

select id,name,chinese + math + english as total from exam order by total desc limit 3;
//或者
select id,name,chinese + math + english as total from exam order by total desc limit 0,3;
//或者
select id,name,chinese + math + english as total from exam order by total desc limit 3 offset 0;
1
2
3
4
5

🚀找出数学成绩后三名的学生信息（成绩只需显示数学成绩）

select id,name,math from exam order by math asc limit 3 offset 0;
1

🚀找出总成绩第2到5名学生

select id,name,chinese + math + english as total from exam order by total desc limit 4 offset 1;
1

where 子句

🚀where子句用于作筛选条件，筛选出符合要求的记录。

select ... where col...;
1

🚀找出总分高于240分的学生信息

select id,name,chinese + math + english as total from exam where total > 240;
//错误的
1
2

注意：上面这中写法是错误的，where子句是优先于select执行的，where作为筛选条件，而select是将通过筛选条件的相关列显示出来。并且还要注意一点的是在where子句处不能对相关列作重命名。

select id,name,chinese + math + english as total from exam where chinese+math+english > 240;
1

比较运算符

🚀找出英语不及格的学生信息（成绩只包含英语成绩）

select id,name,english from exam where english < 60;
1

🚀找出语文成绩在80-90的学生信息。

select * from exam where chinese between 80 and 90;
1

🚀找出数学成绩是98或者99的同学信息

select * from exam where math in(98,99);
1

🚀找出王某同学，和王同学的信息。

insert into exam values (8,'王小利',88,88,88); //先插入一条记录
1

select * from exam where name like'王_' or name like '王%';
1

逻辑运算符

🚀找出数学成绩为98或99的同学

select * from exam where math=98 or math=99;
1

Update

update tb_name set column=expr [,column=expr,column=expr,column=expr...]
	[where...][order by][limit ]
1
2

🚀将王小利同学的英语成绩改为80分

update exam set english=80 where id=8;
1

🚀将倒数前三名的学生数学成绩加上30分

update exam set math=math+30 order by math+english+chinese asc limit 3 ;
1

Delete

🚀为了测试新建一张新的表

create table test_delete (id int auto_increment primary key,name varchar(10));
1

🚀在表中插入数据

insert into test_delete (name) values 
    ('张三'),
    ('李四'),
    ('王五');
1
2
3
4

delete

delete from tb_name [where][order by][limit];
1

🚀删除id为2的记录

delete from test_delete where id=2;
1

🚀清空表中的数据

delete from test_delete;

//查看auto_increment的值
show create table test_delete\G;
1
2
3
4

truncate

truncate [table] tb_name;
1

重新插入数据：

insert into test_delete (name) values 
    ('张三'),
    ('李四'),
    ('王五');
1
2
3
4

🚀清空表中的数据

truncate table test_delete;
//查看auto_increment的值
show create table test_delete\G;
1
2
3

insert into test_delete (name) values('测试');
1

🚀事实证明truncate清空表的数据时，auto_increment也会被清空。

delete和truncate的区别

1.delete 走事务，truncate不走事务，也就是说不会将truncate记录在日志中。
2.truncate只能整体删除表的所有数据，不能选择的删除
3.delete清除表的数据后auto_increment值不会被重置，truncate清空表的数据 auto_increment也会被重置