文章目录
1.CRUD
- 注释:在sql中可以使用"–+空格+描述"来表示注释说明
- CRUD即增加(Create)、查询(Retrieve)、更新(update)、删除(delete)四个单词的首字母缩写。
2.新增(Create)
语法:
insert [into]table_name
[(column [,column]...)]
values(value_list[,(value_list)]..
value_list:value,[value]...
案例:
-- 创建一张学生表
create table student(
id int,
age int,
name varchar(20),
sex varchar(20),
birthday timestamp
);
2.1单行数据+全列插入
-- 插入两条记录,value_list数量必须与定义表的列的数量,顺序及数据类型一致
insert into student values (1,18,'tom','nan','2001-04-02 03:00:00');
insert into student values(2,20,'danny','mail','1999-08-09 20:00:00');
2.2多行插入
-- 插入两条记录:
insert into student values
-> (3,21,'jenny','female','2000-06-12 20:00:00'),
-> (4,22,'car','female','1999-01-23 20:00:00');
2.3指定列插入
insert into student(id,age,name)values(5,18,'tony');
3.查询(Retrieve)
语法:
select
[distinct]{*|{,column]...}
[from table_name]
[where ...]
[order by column [asc|desc],...]
limit...
3.1全列查询
-- 通常情况下不建议使用*进行全列查询
-- 1.查询的列越多,意味着传输的数据量越大
-- 2.可能会影响索引的使用
select * from student;
3.2指定列查询
-- 指定的顺序不需要按定义表的顺序来进行
select id,name,birthday from student;
3.3查询的字段为表达式
为了更好的体会后续的增删改查,我们重新创建表,及重新插入数据
use test1;
create table exam_result(
id int,
name varchar(20),
english decimal(3,1),
math decimal(3,1),
chinese decimal(3,1)
);
-- 插入数据
insert into exam_result values
(1,'tom',89,78,96),
(2,'danny', 78,90,67),
(3,'jenny', 89,78,78),
(4,'car' ,78,90,89),
(5,'bird',78,69,97),
(6,'ni',55.5, 78,78);
-- 表达式不包含字段
select id,name,10 from student;
-- 表达式包含一个字段
select id,age+10,name from student;
-- 表达式包含多个字段
select id,name,chinese+math+english from exam_result;
3.4别名
-- 为查询结果中的列的指定别名,表示返回的结果集中,以别名作为该列的名称
select column [as] alias_name [...] from table_name;
select name,english+math+chinese as total from exam_result;
3.5去重
-- 使用distinct 关键字对某列数据进行去重
select english from exam_result;
select distinct english from exam_result;
3.6排序
3.6.1普通排序
语法:
-- asc 为升序
-- desc 为降序
-- 如果不写,则默认为asc
select ...from table_name [where ...]
order by colum [asc|desc],[...];
-- 1.没有order by子句的查询,返回的顺序是为定义的,永远不要依赖这个函数
-- 2.Null数据排序,视为比任何值都小,升序在最上面,降序出现在最下面
-- 案例:英语成绩从低到高进行排序
select * from exam_result order by english asc;
3.6.2 使用表达式与别名排序
-- 查询同学的总分,并且由高到低进行排序
select name,english+math+chinese from exam_result order by english+math+chinese desc;
-- 使用别名进行排序
select name,chinese+math+english as total from exam_result order by total desc;
3.6.3可以对多个字段进行排序,排序优先级随书写顺序
如上面的对英语成绩排序,当出现多个78时,此时可以多个字段进行排序。
-- 查询同学各门成绩,按照英语升序,数学,语文降序排列
select * from exam_result order by english,math,chinese desc;
3.7条件查询:where
3.7.1比较运算符
运算符 | 说明 |
---|---|
>,>=,<,<= | 大于,大于等于,小于,小于等于 |
= | 等于,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 true | 是null |
is not true | 不是null |
like | 模糊匹配。%表示任意多个(包括0个)任意字符;_表示任意一个字符 |
3.7.2逻辑运算符
运算符 | 说明 |
---|---|
and | 多个条件必须都为true(1),结果才为true(1) |
or | 任意一个条件为true(1),结果都为true(1) |
not | 条件为true(1),结果为false(0) |
注意:
1.where条件可以使用表达式,但是不能使用别名;
2.and的优先级高于or,在同时使用时,需要使用小括号()包裹优先执行的部分。
3.7.3案例
1.基本查询
-- 查询英语成绩低于70的同学
select name,english from exam_result where english<70;
-- 语文成绩高于英语成绩的同学
select name,chinese,english from exam_result chinese>english;
-- 查询总分在250分以上
select name,chinese+math+english from exam_result where chinese+math+english>250;
2.and与or
-- 查询语文成绩大于80分,且数学成绩大于80分
select name,chinese,math from exam_result where chinese>80 and math>80;
--查询语文成绩大于80分或数学成绩大于80分
select name,chinese,math from exam_result where chinese>80 or math>80;
-- 观察and与or的优先级
select * from exam_result where chinese >80 or math>70 and english>80;
select * from exam_result where (chinese>80 0r math>70) and english>80;
3.范围查询
(1)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;
(2)in
-- 查询数学成绩是68,69,70,71分的学生及他们的数学成绩
select name,math from exam_result where math in(68,69,70,71);
-- 用or也可以使用
select name,math from exam_result where math=68 or math=69 or math=70 or math=71;
4.模糊查找:like
-- % 匹配任意多个(包括0个字符)
select name from exam_result where name like 't%';
-- _匹配严格的一个字符
select name from exam_result where name like 't__';
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 offest s;
按id分页,每页3条记录,分别显示第1,2,3页
-- 第一页
select * from exam_result order by id limit 3;
-- 第二页
select * from exam_result order by id limit 3,3;
-- 第三页
select * from exam_result order by id limit 3 offset 6;
4.修改
语法:
update table_name set column =exper[,column=expr...]
[where ...][order by...][limit ...]
案例:
-- 将car同学的语文成绩改为80
update exam_result set chinese=80 where name='car';
-- 将danny的数学成绩改为89,语文成绩改为80
update exam_result set math=89,chinese=80 where name='danny';
--将所有的同学的数学成绩加2
update exam_result set math=math+2;
5.删除(delete)
语法:
delete from table_name[where...][order by...][limit...]
案例:
-- 删除名字叫car同学的成绩
delete from exam_result where name='car';