一、单表
创建表 / 删除表
- 创建表:
create table 表名(列名1 类型(size), 列名2 类型(size),...);
create table student(
ID int,
xingming varchar(10),
banji varchar(2),
zhuanye varchar(10),
xuehao varchar(8)
);
drop table student;
增 —— 插入数据
- 对 指定列 插入数据:
insert into 表名 (字段 类型,) values(值,);
- 对 所有列 插入数据
insert into 表名 values(值,);
- 所有
串类型
值,都要用''
号或""
号括上.
insert into student(ID,xingming,banji,zhuanye,xuehao) values(1,'张三','2','英语','20180220');
insert into student(ID,xingming,banji,zhuanye,xuehao) values(2,'李四','2','英语','20180221');
insert into student(ID,xingming,banji) values(3,'王五','3',null,null); -- 未注明的项,都填null
insert into student values(4,'赵六','3','电子工程','20181202');
insert into student values(5,'郑七','4','电子工程','20181201');
insert into student values(6,'胡八','3',null,'20181203');
ID |
xingming |
banji |
zhuanye |
xuehao |
1 |
张三 |
2 |
英语 |
20180220 |
2 |
李四 |
2 |
英语 |
20180221 |
3 |
王五 |
3 |
null |
null |
4 |
赵六 |
3 |
电子工程 |
20181202 |
5 |
郑七 |
4 |
电子工程 |
20181201 |
6 |
胡八 |
3 |
null |
20181203 |
删 —— 删除数据
- 删除符合条件的数据:
delete from 表名 where 条件;
delete from student where ID = 1;
delete from student where xingming = '李四';
ID |
xingming |
banji |
zhuanye |
xuehao |
3 |
王五 |
3 |
null |
null |
4 |
赵六 |
3 |
电子工程 |
20181202 |
5 |
郑七 |
4 |
电子工程 |
20181201 |
6 |
胡八 |
3 |
null |
20181203 |
改 —— 修改数据
- 修改符合条件的数据:
update 表名 set 字段名 = 新值 where 条件;
- 将'王五'的专业名改为'英语':
update student set zhuanye = '英语' where xingming = '王五';
ID |
xingming |
banji |
zhuanye |
xuehao |
3 |
王五 |
3 |
英语 |
null |
4 |
赵六 |
3 |
电子工程 |
20181202 |
5 |
郑七 |
4 |
电子工程 |
20181201 |
6 |
胡八 |
3 |
null |
20181203 |
查 —— 查询数据
-
普通单表查询:
select 内容 form 表名 where 条件;
'''展示表内所有的数据: '''
select * from student;
ID |
xingming |
banji |
zhuanye |
xuehao |
3 |
王五 |
3 |
英语 |
null |
4 |
赵六 |
3 |
电子工程 |
20181202 |
5 |
郑七 |
4 |
电子工程 |
20181201 |
6 |
胡八 |
3 |
null |
20181203 |
'''展示3班所有学生的数据: '''
select * from student where banji = '3';
ID |
xingming |
banji |
zhuanye |
xuehao |
3 |
王五 |
3 |
英语 |
null |
4 |
赵六 |
3 |
电子工程 |
20181202 |
6 |
胡八 |
3 |
null |
20181203 |
'''展示三班、电子工程专业的所有学生的信息: '''
select * from student where banji = '3' and zhuanye = '电子工程';
ID |
xingming |
banji |
zhuanye |
xuehao |
4 |
赵六 |
3 |
电子工程 |
20181202 |
'''展示三班、电子工程专业的学生姓名: '''
select xingming as 姓名 where banji = '3' and zhuanye = '电子工程';
-
分组查询:
select 字段1, 字段2 from 表名 group by 字段1;
'''查询每个班级有几人(按班级分组, 且分别计算人数, 展示为两列): '''
select banji as 班级,count(*) as 人数 from student group by banji;
-
给分组增加过滤条件:
... group by 字段名 having 函数;
'''对分组查询,增加过滤条件,使用 having + 条件, having 后接函数:'''
select banji as 班级 from student group by banji having count(*) >= 2;
-
结果排序:
order by 字段名 asc/desc;
按照某字段, 正序 / 逆序排列. (默认正序)
步骤:
1. 创建一个表scores:
create table scores(
ID int,
xingming varchar(10),
kemu varchar(10),
score int
);
2. 插入对应的数据:
insert into scores values(1,'赵一','数学',98);
insert into scores values(2,'钱二','语文',99);
insert into scores values(3,'孙三','数学',99);
insert into scores values(4,'李四','数学',98);
insert into scores values(5,'周五','语文',99);
insert into scores values(6,'吴六','数学',97);
ID |
xingming |
kemu |
score |
1 |
赵一 |
数学 |
98 |
2 |
钱二 |
语文 |
99 |
3 |
孙三 |
数学 |
99 |
4 |
李四 |
数学 |
98 |
5 |
周五 |
语文 |
99 |
6 |
吴六 |
数学 |
97 |
3. 查询: 将学生的数学成绩,按照ID由大到小排序(逆序排列):
select * from scores where kemu = '数学' order by ID desc;
ID |
xingming |
kemu |
score |
6 |
吴六 |
数学 |
97 |
4 |
李四 |
数学 |
98 |
3 |
孙三 |
数学 |
99 |
1 |
赵一 |
数学 |
98 |
-
单表_综合查询:
1. 建表 + 增加数据:
create table books(
id int primary key,
shuhao varchar(20),
shuming varchar(20) not null,
zuozhe varchar(20),
yeshu int,
chubanshe varchar(20),
chubanriqi date,
zhuangtai varchar(20)
);
insert into books values(1, 001, '高等数学', '张三', 430, '中华书局', 1992-08-01, '上架' );
insert into books values(2, 002, '线性代数', '张三', 380, '华南书局', 1993-08-01, '上架' );
insert into books values(3, 003, '实用英语', '李四', 600, '中华书局', 1992-08-01, '上架' );
insert into books values(4, 004, '商务英语', '李四', 500, '山东书局', 1992-08-01, '上架' );
insert into books values(5, 005, '材料科学', '王五', 800, '华南书局', 1992-08-01, '上架' );
insert into books values(6, 006, '微积分学', '王五', 880, '中华书局', 1992-08-01, '上架' );
insert into books values(7, 007, '化学工业', '王五', 680, '中华书局', 1992-08-01, '上架' );
id |
shuhao |
shuming |
zuozhe |
yeshu |
chubanshe |
chubanriqi |
zhuangtai |
1 |
001 |
高等数学 |
张三 |
430 |
中华书局 |
1991-08-01 |
上架 |
2 |
002 |
线性代数 |
张三 |
380 |
华南书局 |
1992-08-01 |
上架 |
3 |
003 |
实用英语 |
李四 |
600 |
中华书局 |
1991-08-01 |
上架 |
4 |
004 |
商务英语 |
李四 |
500 |
山东书局 |
1993-08-01 |
上架 |
5 |
005 |
材料科学 |
王五 |
800 |
华南书局 |
1991-08-01 |
上架 |
6 |
006 |
微积分学 |
王五 |
880 |
中华书局 |
1992-08-01 |
上架 |
7 |
007 |
化学工业 |
王五 |
680 |
中华书局 |
1993-08-01 |
上架 |
2. 查询: 和英语相关的书籍有哪些?
select shuming as 书籍名称 from books where shuming like '%英语%';
3. 查询: [高等数学]和[实用英语]的书号是多少?
select shuming as 书籍名称,shuhao as 书号
from books
where shuming = '高等数学' or shuming = '实用英语';
书籍名称 |
书号 |
高等数学 |
001 |
实用英语 |
003 |
4. 查询: 哪些书的[页数]在300~499之间?
select shuming as 书籍名称,yeshu as 页数
from books
where yeshu between 300 and 499;
书籍名称 |
页数 |
高等数学 |
430 |
线性代数 |
380 |
5. 查询: 每个出版社发行图书的数量是多少?
select chubanshe as 出版社,count(*) as 发行的图书数量
from books
group by chubanshe;
出版社 |
发行的图书数量 |
中华书局 |
4 |
华南书局 |
2 |
山东书局 |
1 |
6. 查询: 哪个出版社至少发行过2本书?
select chubanshe as 出版社,count(*) as 发行图书数
from books
group by chubanshe
having count(*) >= 2;
7. 计算: 王五比张三多出了几本书?
select
(select count(*) from books where zuozhe = '王五')-
(select count(*) from books where zuozhe = '张三');
3 - 2 = 1(本)
8. 查询: 哪本书的[页数]比线性代数多?
select shuming as 书名, yeshu as 页数
from books
where yeshu > (
select sum(yeshu)
from books
where shuming = '线性代数'
);
书名 |
页数 |
高等数学 |
430 |
实用英语 |
600 |
商务英语 |
500 |
材料科学 |
800 |
微积分学 |
880 |
化学工业 |
680 |
9. 查询: 张三出的两本书的平均页数是多少?
select avg(yeshu) as 平均页数
from books
where zuozhe = '张三';
10. 查询: 和高等数学是相同初版日期的书籍信息?
select *
from books
where chubanriqi = (
select chubanriqi
from books
where shuming = '高等数学'
);
id |
shuhao |
shuming |
zuozhe |
yeshu |
chubanshe |
chubanriqi |
zhuangtai |
1 |
001 |
高等数学 |
张三 |
430 |
中华书局 |
1991-08-01 |
上架 |
3 |
003 |
实用英语 |
李四 |
600 |
中华书局 |
1991-08-01 |
上架 |
5 |
005 |
材料科学 |
王五 |
800 |
华南书局 |
1991-08-01 |
上架 |
11. 查询: 高等数学的[页数]比线性代数多多少?
select yeshu - (
select yeshu
from books
where shuming = '线性代数'
)
from books
where shuming = '高等数学';
430 - 380 = 50(页)
12. 查询: 每个作者各出版了多少页的书籍?
select zuozhe as 作者,sum(yeshu) as 总页数
from books
group by zuozhe;
作者 |
总页数 |
张三 |
810 |
李四 |
1100 |
王五 |
2360 |
13. 查询: 哪个作者出版的所有书籍都少于500页?
select 作者 as 出版的所有书籍都少于500页的作者
from (
select zuozhe as 作者,max(yeshu) as 最大页数
from books
group by zuozhe
) as T
where 最大页数 < 500;
T表如下:
作者 |
最大页数 |
张三 |
430 |
李四 |
600 |
王五 |
880 |
14. 查询: 哪个作者出版的图书最多?
select 作者 as 出版图书最多的作者
from (
select zuozhe as 作者,count(*) as 出版图书数量
from books
group by zuozhe
) as U
where 出版图书数量 = (
select max(出版图书数量)
from (
select zuozhe as 作者,count(*) as 出版图书数量
from books
group by zuozhe
) as U
);
U表:
15. 查询: 页数最多的书籍是哪本书?
select 书名 as 页数最多的书籍
from (select shuming as 书名,yeshu as 页数
from books
) as M
where 页数 = (
select max(yeshu)
from (
select shuming as 书名,yeshu as 页数
from books
) as M
);
M表:
书名 |
页数 |
高等数学 |
430 |
线性代数 |
380 |
实用英语 |
600 |
商务英语 |
500 |
材料科学 |
800 |
微积分学 |
880 |
化学工业 |
680 |
二、双表查询