MySQL的增删改查也就是CRUD
CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。
1.新增
1.1 语法:
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...
1.2 插入单行数据+全列插入
insert into student values(100,1000,'zhangsan',NULL);
insert into student values(100,1001,'lisi','212112@qq.com');
1.3 插入多行数据+指定列插入
insert into student(id,sn,name) values (10,2000,'fanfan'),(11,2001,'xiaofan');
2.查询
2.1 语法:
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
2.2 全列查询(通常情况下不建议使用*查询,查询的越多数据量就会越大,可能会导致数据IO增多)
select * from exam_result;
2.3 指定列查询
select id,name,english from exam_result; 指定列的顺序,不需要按定义表的顺序来
2.4 查询字段为表达式
表达式不包含字段
select id,name,50 from exam_result;
表达式包含一个字段
select id,name,english+10 from exam_result;
表达式包含多个字段
select id,name,chinese+math+english from exam_result;
2.5 别名
为查询结果中的列指定别名,表示返回的结果集中,以别名作为该列的名称,语法:
SELECT column [AS] alias_name [...] FROM table_name;
示例:
select id,name,chinese + math + english as 总分成绩 from exam_result;
2.6 去重:DISTINCT
示例:
select distinct math from exam_result;
2.7 排序:ORDER BY
语法:
-- ASC 为升序(从小到大)
-- DESC 为降序(从大到小)
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];
按数学成绩排序:
select name,math from exam_result order by math;
select name,math from exam_result order by math desc;
用表达式进行排序:
select name,math+english,chinese from exam_result order by chinese,math+english;
2.8 条件查询:WHERE
比较运算符:
逻辑运算符:
注意:
1. WHERE条件可以使用表达式,但不能使用别名。
2. AND的优先级高于OR,在同时使用时,需要使用小括号()包裹优先执行的部分
基础查询:
select name,math from exam_result where math > 75;
select name,english from exam_result where english <80;
select name,english+chinese+math as 总分 from exam_result where math+english+chinese < 200;
AND与OR:
1、查询英语和数学成绩都大于80分的同学
select name,english,math from exam_result where english > 80 and math > 80;
2、查询英语成绩>70分和总成绩大于200分的同学
select name,english,math+chinese+english from exam_result where english > 70 and math+chinese+english > 200;
范围查询:
1. BETWEEN ... AND ...
查询英语成绩在75到85之间的同学
select name,english from exam_result where english between 75 and 85;
select name,english from exam_result where english >= 75 and english <= 85;
2. IN
查询数学成绩是99,98,97,96,95,94,93的同学
select name,math from exam_result where math in(99,98,97,96,95,94,93);
select name,math from exam_result where math=99 or math = 98 or math = 97 or math = 96;
模糊查询:
-- % 匹配任意多个(包括 0 个)字符
select name from exam_result where name like '%g'; 查询姓名中结尾是g的名字
select name from exam_result where name like '%u%'; 查询姓名中含有u的名字
-- _ 匹配严格的一个任意字符
select name from exam_result where name like 't___';
select name from exam_result where name like '_a_';
NULL的查询:is[not] null
查询英语成绩不为null的同学姓名:
select name,english from exam_result where math is not null;
查询数学成绩为null的同学姓名
select name,math from exam_result where math is null;
2.9 分页查询: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排序数学成绩大于五十的两条数据
select id,name,math from exam_result where math > 50 order by id limit 2;
查询出按id排序数学成绩大于五十的两条数据(第二页)
select id,name,math from exam_result where math > 50 order by id limit 2 offset 1;
查询出按id排序数学成绩大于五十的两条数据(第三页)
select id,name,math from exam_result where math > 50 order by id limit 2 offset 2;
3.修改
语法:
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
示例:
将徐同学的数学成绩改成99
update exam_result set math = 99 where name = 'xu';
将孙同学的英语成绩修改成10分,语文改称20分
update exam_result set english = 10,chinese = 20 where name = 'sun';
给总成绩倒数前三的学生,数学加上十分
update exam_result set math = math + 10 order by chinese + math + english limit 3;
将姓sun的同学,语文成绩改成原来的两倍
update exam_result set chinese = chinese * 2 where name = 'sun';
4.删除
语法:
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
示例:
删除姓名为sun同学的成绩
delete from exam_result where name = 'sun';
删除总成绩大于250的同学
delete from exam_result where english + math + chinese > 250;
删除整个成绩表(删除表的操作一定要谨慎谨慎,不要随意删除,练习的测试表可以删,工作后一定要小心删除)
delete from exam_result;