0x01 sql基本概念
基本表:
- 本身独立存在的表
- sql中一个关系就对应一个基本表
- 一个(或多个)基本表对应一个存储文件
- 一个表可以带若干索引
视图:
- 从一个或几个基本表导出的表
- 数据库中只存放视图的定义而不存放视图对应的数据
- 视图是一个虚表
- 用户可以在视图上再定义视图
0x02 基本表的定义
sql的动词:
SQL功能 | 动词 |
---|---|
数据查询 | SELECT |
数据定义 | CREATE、DROP、ALTER |
数据操纵 | INSERT、UPDATE、DELETE |
数据控制 | GRANT、REVOKE |
1、创建数据库
create database st;
2、创建student,course,sc表
create table <表名>
(<列名> <数据类型> [<列级完整性约束条件>],
<列名> <数据类型> [<列级完整性约束条件>]
);
create table student
(sno char(9) primary key,
sname char(20),
ssex char(2),
sage smallint,
sdept char(20)
);
create table course
(cno char(4) primary key,
cname char(40),
cpno char(4),
ccredit smallint,
foreign key(cpno) references course(cno)
);
create table sc
(sno char(9),
cno char(4),
grade smallint,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);
3、修改基本表
alter table <表名>
①向student表中增加“入学时间”列,其数据类型为日期型。新增的一列均为空值。
alter table student add s_sentrance date;
②将年龄的数据类型由字符型改为整型。
alter table student modify sage int(20);
③修改表名
alter table student rename to stu;
④删除表中字段
alter table student drop column s_sentrance;
4、删除基本表
drop table student cascade;
#cascade,删除基本表的同时,将相关的依赖对象一起删除。
0x03数据查询
查询语句:
- select子句:指定要显示的属性列
- from子句:指定查询对象(基本表或视图)
- where子句:指定查询条件
- group by子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组,通常会在每组中作用聚集函数。
- having短语:只有满足指定条件的组才能予以输出。
- order by子句:对查询结果表按指定列值的升序或降序排列。
1、单表连接
①查询全体学生的学号与姓名
select sno,sname from student;
②查询全体学生的姓名、学号、所在系
select sname,sno,sdept from student;
③查询全系学生的详细记录
select * from student;
④查询全体学生的姓名及出生年份
select sname,2020-sage from student;
⑤查询全体学生的姓名、出生年月和所在的院系,用小写字母表示系名
select sname,'year of birth:',2020-sage,lower(sdept) fromm student;
⑥查询选修了课程的学生学号
select distinct sno from sc;
#指定distinct关键字,去掉表中重复的行
⑦常用的查询条件
查询条件 | 谓词 |
---|---|
比较 | =,>,<,>=,<=,!=,<>,!>,!<,not+上述比较运算符 |
确定范围 | between and,not between and |
确定集合 | in,not in |
字符匹配 | like,not like |
空值 | is null,is not null |
多重条件(逻辑运算) | and,or,not |
⑧查询计算机科学系全体学生名单
select sname from student where sdept='cs';
⑨查询所有年龄在20岁以下的学生姓名及年龄
select sname,sage from student where sage<20;
①查询考试成绩不及格的学生的学号
select distinct sno from sc where grade<60;
②查询年龄在20-23之间的学生的姓名,系别和年龄
select sname,sdept,sage from student where sage between 20 and 23;
③查询年龄不在20-23岁之间的学生姓名,系别和年龄
select sname,sdept,sage from student where sage not between 20 and 23;
④查询计算机科学系(cs)、数学系(ma)和信息系(is)学生的姓名和性别
select sname,ssex from student where sdept in ('cs','ma','is');
⑤查询既不是计算机科学系,数学系,也不是信息系的学生的姓名和性别。
select sname,ssex from student where sdept not in ('is','ma','cs');
⑥查询学号为201215121的学生的详细情况
- %,代表任意长度(可以为0)的字符串,例a%b,表示以a开头,以b结尾的任意长度的字符串
- _(下横线),代表任意单个字符
select * from student where sno like '201215121';
⑦查询所有姓刘学生的姓名,学号和性别
select sname,sno,ssex from student where sname like '刘%';
⑧查询姓“欧阳”且全名为三个汉字的学生的姓名。
select sname from student where sname like '欧阳_';
⑨查询名字中第二个字为“阳”字的学生的姓名和学号
select sname,sno from student where sname like '_阳%';
①查询所有不姓刘的学生姓名,学号和性别
select sname,sno,ssex from student where sname not like '刘%';
②查询db_desing课程的课程号
select cno from course where cname like 'db\_design';
#反斜杠\,用于转义
③查询缺少成绩的学生的学号和相应的课程号
select sno,cno from sc where grade is null;
#‘is’不能用‘=’替代
④查询所有有成绩的学生学号和课程号
select sno,cno from sc where grade is not null;
⑤查询计算机系年龄在20岁以下的学生姓名
select sname from student where sdept='cs' and sage<20;
⑥order by子句
- 按一个或多个属性列排序
- 升序:asc。降序:desc,默认升序
查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列
select sno,grade from sc where cno='3' order by grade desc;
⑦查询全体学生情况,按所在的系号升序,同一系按年龄降序排列
select * from student order by sdept,sage desc;
⑧聚集函数
- 统计元组个数:count(*)
- 统计一列中值的个数:count(distinct <列名>)
- 计算一列值的总和(此列必须是数值型):sum(distinct <列名>)
- 计算一列值的平均值(此列必须是数值型):avg(distinct <列名>)
- 计算一列中的最大值和最小值:max(distinct <列名>),min(distinct <列名>)
⑨查询学生总人数
select count(*) from student;
①查询选修了课程的学生人数
select count(distinct sno) from sc;
②计算1号课程的学生平均成绩
select avg(grade) from sc where cno='1';
③查询选修一号课程的学生的最高分数
select max(grade) from sc where cno='1';
④查询学生201215121选修课程的总学分数
select sum(ccredit) from sc,course where sno='201215121' and sc.cno=course.cno;
⑤group by子句
- 细化聚集函数的作用对象
- 如果未对查询结果分组,聚集函数将作用于zhengge查询结果
- 对查询结果分组后,聚集函数将分别作用于每个组
- 按指定的一列或多列值分组,值相等的为一组
⑥求各个课程号及相应的选课人数
select cno,count(sno) from sc group by cno;
⑦查询选修了3门以上课程的学生学号
select sno from sc group by sno having count(*)>3;
⑧查询平均成绩大于等于90分的学生学号和平时成绩
select sno,avg(grade) from sc group by sno having avg(grade)>=90;
#where子句中是不嗯给你用聚集函数作为条件表达式
⑨having短语与where子句的区别
- 作用对象不同
- where子句作用于基表或视图,从中选择满足条件的元组
- having短语作用于组,从中选择满足条件的组