一、 增(create)
1. 语法
INSERT [INTO] table_name [(column[,column]...)] VALUES
(value_list)[,(value_list)]...
value_list:value,[,value]...
例:
drop table if exists student;
create table student(
id int,
sn int comment'学号',
name varchar(20) comment'姓名',
qq_mail varchar(20) comment'QQ邮箱'
);
2. 单行数据全列插入
插入时,value_list 数量必须和定义表的列的数量、顺序一致
insert into student values(100,10000,'唐三藏',NULL);
insert into student values(101,10001,'孙悟空',NULL);
3. 多行数据指定列插入
value_list 数量必须和指定列数量、顺序一致
insert into student(id,sn,name) values
(102,20001,'曹孟德'),
(103,20002,'孙仲谋');
二、 删
语法
DELETE FROM table_name [WHERE...] [ORDER BY...] [LIMIT...]
例:
-- 删除孙悟空同学的考试成绩
delete from exam_result where name='孙悟空';
-- 删除整张表数据
-- 准备测试表
drop table if exists for_delete;
create table for_delete(
id int,
name varchar(20)
);
-- 插入测试数据
insert into for_delete(name) VALUES
('A'),
('B'),
('C');
-- 删除整表数据
delete from for_delete;
三、 改
语法
UPDATE table_name SET column = expr [,column=expr...] [WHERE...] [ORDER BY...] [LIMIT...]
例:
-- 将孙悟空同学的数学成绩变更为80分
update exam_result set math=80 where name='孙悟空';
-- 将曹孟德同学的数学成绩变更为60分,语文成绩变更为70分
update exam_result set math=60,chinese=70 where name='曹孟德';
-- 将总成绩倒数前三的3位同学的数学成绩加上30分
update exam_result set math=math+30 order by chinese+math+english limit 3;
-- 将所有同学的语文成绩更新为原来的2倍
update exam_result set chinese=chinese*2;
四、查
1.语法
SELECT
[DISTINCT] {* | {column [,column] ...}
[FROM table_name]
[WHERE...]
[ORDER BY column [ASC | DESC], ...]
LIMIT...
例:
-- 创建考试成绩表
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(name,chinese,math,english) values
('唐三藏',67,98,56),
('孙悟空',87.5,78,77),
('猪悟能',88,98,90),
('曹孟德',82,84,67),
('刘玄德',55.5,85,45),
('孙权',70,73,78.5),
('宋公明',75,65,30);
2.全列查询
select * from exam_result;
【注】通常情况下不建议使用*进行全列查询
因为:
查询的列越多,意味着需要传输的数据量越大;
可能会影响到索引的使用;
3.指定列查询
select id,name,english from exam_result;
指定列查询时,查询列表的顺序不需要按定义表的顺序来
4.查询字段为表达式
-- 表达式不包含字段
select id,name,10 from exam_result;
-- 表达式包含一个字段
select id,name,english+10 from exam_result;
-- 表达式包含多个字段
select id,name,chinese+math+english from exam_result;
5.别名
语法:
SELECT column [AS] alias_name [...] FROM table_name;
为查询结果中的列指定别名,即:返回的结果集中,以别名作为该列的名称
-- 结果集中:表头的列名=别名
select id,name,chinese+math+english 总分 from exam_result;
6.去重(distinct)
使用 distinct 关键字可对某列数据进行去重
例:对于之前创建的表 exam_result 中,math列里98分重复了
select distinct math from exam_result;
7.排序(order by)
语法:
SELECT ... FROM table_name [WHERE...] ORDER BY column [ASC | DESC],[...];
升序 ---- ASC(小 -> 大)
降序 ---- DESC(大 -> 小)
一般默认为ASC
【注】
1.没有 order by 子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序
2.NULL数据排序,视为比任何值都小,升序出现在最上面,降序出现在最下面
例:
-- 查询同学姓名和qq_mail,按qq_mail排序显示
select name,qq_mail from student order by qq_mail;
select name,qq_mail from student order by qq_mail desc;
3.使用 表达式+别名 排序
--查询同学及总分,由高到低
select name,chinese+english+math from exam_result order by chinese+english+math desc;
select name,chinese+english+math total from exam_result order by total desc;
4.可以对多个字段进行排序,排序优先级随书写顺序
--查询同学各门成绩,依次按数学降序,英语升序,语文升序的方式显示
select name,math,english,chinese from exam_result order by math desc,english,chinese;
8.条件查询(where)
- 比较运算符:
运算符 | 说明 |
---|---|
>、>=、<、<= | 大于、大于等于、小于、小于等于 |
= | 等于,NULL不安全,例如NULL=NULL的结果是NULL |
<=> | 等于,NULL安全,例如NULL<=>NULL的结果是TRUE(1) |
!=、<> | 不等于 |
between a0 and a1 | 范围匹配[a0,a1],如果a0<=value<=a1,返回TRUE(1) |
in(option,…) | 如果是option中的任意一个,返回TRUE(1) |
is null | 是null |
is not null | 不是null |
like | 模糊匹配。%表示任意多个(包括0个)任意字符;_表示任意一个字 符 |
- 逻辑运算符:
运算符 | 说明 |
---|---|
and | 多个条件必须都为TRUE(1),结果才是TRUE(1) |
or | 任意一个条件为TRUE(1),结果为TRUE(1) |
not | 条件为TRUE(1),结果为FALSE(0) |
注:
1.where 条件可以使用表达式,但不能使用别名
2.and 的优先级高于 or ,在同时使用时,需要使用小括号()包裹优先执行的部分
- 例:
基本查询:
-- 查询英语成绩<60的同学
select name,english from exam_result where english<60;
-- 查询语文成绩好于英语成绩的同学
select name,chinese,english from exam_result where chinese>english;
-- 查询总分在200分以下的同学
select name,chinese+math+english 总分 from exam_result where chinese+math+english<200;
and 与 or:
-- 查询语文成绩大于80分 且 英语成绩大于80分的同学
select * from exam_result where chinese>80 and english>80;
-- 查询语文成绩大于80分 或 英语成绩大于80分的同学
select * from exam_result where chinese>80 or english>80;
--观察 and 和 or 的优先级:
select * from exam_result where chinese>80 or math>70 and english>70;
select * from exam_result where (chinese>80 or math>70) and english>70;
范围查询:
between … and…
-- 查询语文成绩在[80,90]分的同学及语文成绩
select name,chinese from exam_result where chinese between 80 and 90;
-- 使用 and 也可以实现
select name,chinese from exam_result where chinese>=80 and chinese <=90;
in
-- 查询数学成绩是58或者59或者98或者99分的同学及数学成绩
select name,math from exam_result where math in(58,59,98,99);
-- 使用OR也可以实现
select name,math from exam_result where math=58 or math=59 or math =98 or math=99;
模糊查询:like
-- %匹配任意多个(包括0个)字符
select name from exam_result where name like '孙%';--匹配到孙悟空、孙权
-- _匹配严格的一个任意字符
select name from exam_result where name like'孙_';--匹配到孙权
null 查询:is [not] null
--查询qq_mail已知的同学姓名
select name,qq_mail from student where qq_mail is not null;
--查询qq_mail未知的同学姓名
select name,qq_mail from student where qq_mail is null;
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 进行分页,每页3条记录,分别显示第1、2、3页
--第1页
select id,name,math,english,chinese from exam_result order by id limit 3 offset 0;
--第2页
select id,name,math,english,chinese from exam_result order by id limit 3 offset 3;
--第3页,如果结果不足3个,不会有影响
select id,name,math,english,chinese from exam_result order by id limit 3 offset 6;