MySQL的增删改查
1. 什么是CRUD?
CRUD在MySQL中指的是增加(create),查询(retrieve),更新(update),删除(delete)。
2. 增加(create)
先创建一个学生表
create table student(
id int,
name varchar(32),
sex varchar(2),
age int
);
2.1 单行插入
例如:插入两行记录
insert into student values(001,'张三','男',12);
insert into student values(002,'李四','男',13);
注意:value_list的数量必须和定义表的列的数量及顺序一致。
2.2 多行插入和指定列插入
insert into student(id,name,sex,age) values(003,'王五','男',14),(004,'赵六','男',15);
注意:value_list 数量必须和指定列数量及顺序一致。
3. 查询(Retrieve)
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
3.1 全列查询
select *from table_name;
例如:查看上述student表
select *from student;
注意:1.通常情况下不建议使用全列查询,查询的列越多,传输的数据量越大,现阶段练习数据少,使用没什么关系,但是在工作时,数据量庞大,应用程序的性能会降低,也可能会导致其他无可挽回的结果。
2.数据量庞大使用全列查询可能会导致索引失效。(索引后面会学)
3.2 指定列查询
select 列名 from table_name;
例如:查询学生学号和姓名
select id,name from student;
3.3 查询字段为表达式
create table if not exists student(
id int,
name varchar(32),
math decimal(3,1),
chinese decimal(3,1),
english decimal(3,1)
);
insert into student(id,name,math,chinese,english) values(1,'张三',87,98,46),(2,'李四',67,54.5,90),(3,'王五',92,69,60.5),(4,'赵六',98,54,60),(5,'钱七',90.5,62.5,80),(6,'小明',87,23,35),(7,'李华',56,79,15);
- 表达式不含字段
select id,name,10 from student;
- 表达式包含一个字段
select id,name,math + 10 from student;
思考:如果math的值大于90,那么运行会报错吗?让我们运行一下
可以看出并不会报错,我们需要注意,我们对表中的数据进行运算时并不会改变表中的值,原表并不受影响。
- 表达式包含多个字段
select id,name,math + chinese + english from student;
3.4 别名
关键字:AS
给我们查询结果中的列命名,并返回结果集。
SELECT column [AS] alias_name [...] FROM table_name;
例如:
select id,name,math + chinese + english as total from student;
3.5 去掉重复值
关键字:DISTINCT
我们查询数学成绩可以看到,数学成绩中有重复的分数,87重复
去重后
select distinct math from student;
3.6 排序
关键字:ORDER BY
在没有排序的表中,返回的顺序都是随机未定义的。
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
注意:
- ASC 为升序(从小到大)
- DESC 为降序(从大到小)
- 默认为 ASC
- 如果表中出现NULL数据,视为比任何值都小。
例如:我们插入一条数据三科的成绩皆为null
- 查询学生的姓名和数学成绩,按照数据成绩排序
select name,math from student order by math;//升序
select name,math from student order by math desc;//降序
2.分别使用表达式和别名来排序(降序)
select name,math + chinese + english from student order by math + chinese + english desc;
select name,math + chinese + english as total from student order by total desc;
3.对多个字段进行排序,排序的优先级是按照书写的顺序
select name,math,chinese,english from student order by math desc,english,chinese;
先按照math来进行排列,如果math的值相同则按照english的值来排序,如果math和english都一样则按照chinese来排序。
3.7 条件查询
关键字:WHERE
1. 比较运算符
运算符 | 说明 |
---|---|
> , >= , < , <= | 分别为大于、大于等于、小于、小于等于 |
= | 等于(不安全,例如NULL=NULL的结果为NULL) |
<=> | 等于(安全,例如NULL=NULL的结果为NULL,建议用这个) |
!= , <> | 不等于 |
BETWEEN a AND b | 范围匹配,[a,b],如果在范围内返回TRUE |
IN(option,……) | 如果是option中的任意一个则返回TRUE |
IS NULL | 是NULL |
IS NOT NULL | 不是NULL |
LIKE | 模糊匹配。 % 表示任意多个(也包括0)任意字符;_ 表示任意一个字符 |
2. 逻辑运算符
运算符 | 说明 |
---|---|
NOT | 条件为TRUE结果为FALSE |
AND | 多个条件必须都为TRUE结果才为TRUE |
OR | 任意一个条件为TRUE结果就为TRUE |
注意:
- WHERE表达式可以使用表达式,但是不可以使用别名。
- AND的优先级高于OR。
示例:
1.查询英语不及格的同学及英语成绩(小于60)
select name,english from student where english < 60;
2.查询数学成绩高于英语成绩的同学
select name,math,english from student where math > english;
3. 查询总分在200分以下的同学
select name,math + chinese + english from student where math + chinese + english < 200;
肯定有人这样写
看看上面的注意,不可以用别名!!!
4.查询数学成绩大于80,且语文成绩大于90的同学
select name,math,chinese from student where math > 80 and chinese > 90;
5. 查询语文成绩大于90 或者 数学成绩大于90的同学
select name,math,chinese from student where math > 90 or chinese > 90;
6. 查询语文成绩在[50,70]中间的同学及语文成绩
select name,chinese from student where chinese between 50 and 70;
- 查询数学成绩为92或者98或者97的同学及数学成绩
select name,math from student where math in(92,98,97);
- 查询姓王的同学
select name from student where name like '王%';
- 查询姓王的两个名字的同学
select name from student where name like '王_';
- 查询缺考的同学(成绩都为NULL)
select *from student where math is null and chinese is null and english is null;
- 查询来考试的同学(成绩都不为NULL)
select *from student where math is not null and chinese is not null and english is not null;
3.8 分页查询
关键字:LIMIT
-- 起始下标为 0
-- 从 0 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
示例:按 id 进行分页,每页 3 条记录,分别显示 第 1、2、3 页
第一页
select *from student order by id limit 3;
第二页
select *from student order by id limit 3 offset 3;
第三页
select *from student order by id limit 3 offset 6;
4. 更新(Update)
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
- 例如:将张三同学的年龄改为18
update student set age = 18 where name = '张三';
- 例如,将李四同学的学号改为006,年龄改为17
select id,name,age from student where name = '李四';
3. 将总成绩倒数前三的3名同学的数学成绩加10(上述带成绩的student表)
update student set math = math + 10 order by math + chinese + english limit 3;
- 将所有同学的英语成绩更新为原来的2倍
update student set chinese = chinese * 2;
5. 删除(Delete)
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
示例:
1.删除张三同学的成绩
delete from student where name = '张三';
- 删除整张学生表
delete from student;
今天就到这里啦!