数据库
DataBase(DB)
用于存储和管理数据的仓库
特点:
- 持久化存储数据
- 方便存储和管理数据
- 使用了统一的方式操作数据库
MySQL的安装 卸载 配置
-
安装
-
卸载
-
配置
-
启动MySQL服务
-
MySQL登录
mysql -uroot -proot
mysql -hip -proot
mysql --host=ip --user=root --password=root
-
MySQL退出
exit
quit
-
MySQL目录结构
-
MySQL安装目录
配置文件 my.ini
-
MySQL数据目录
数据库: 文件夹
表: 文件
-
-
SQL
SQL(结构化查询语言)定义了操作关系型数据库的规则.每一种数据库操作的方式存在不一样的地方,称为"方言".
通用语法
- SQL语句可以多行或单行书写,以分号结尾
- 使用tab缩进提高可读性
- SQL不区分大小写,关键字推荐大写
- 3种注释:
- 单行: – 注释内容 或 # 注释内容(mysql 特有)
- 多行: /* 注释 */
SQL分类
- DDL(操作数据库/表) create drop alter
- DML(增删改表中的数据) insert delete update
- DQL(查询表中的数据) select where
- DCL(授权) GRANT REVOKE
DDL
-
操作数据库: CRUD
# 1. C 创建 create # 创建数据库 create database databasename; # 判断不存在 create database if not exists databasename; # 指定字符集 create database databasename character set gbk; # 2. R(Retrieve) 查询 show # 所有数据库的名称 show databases; # 某个数据库的创建语句 show create database databasename; # 3. U 修改 alter # 修改数据库字符集 alter database databasename character set utf8; # 4. D 删除 drop drop database databasename; drop database if exists databasename; # 5. 使用数据库 using # 查询当前数据库 select database(); # 使用数据库 use databasename;
-
操作表
table数据类型:
- int :整数
- age int
- double : 小数
- score double(5,2) : 2位小数点,5个数
- date : 日期
- yyyy-mm-dd
- datetime : 日期
- yyyy-mm-dd hh:mm:ss
- timstamp: 时间戳
- yyyy-mm-dd hh:mm:ss (默认为当前系统时间)
- varchar : 字符串
- name varchar(20) : 姓名最大20个字符
- text : 文本
- int :整数
# 1. C 创建 create create table tablename( line1 type1, line2 type2, line3 type3, ..... line4 type4 ) # 复制表 create table tablename1 like tablename2; # 例子: create table student( id int, name varchar(32), age int, score double(4,1), birthday date, insert_time timestamp ); # 2. R(Retrieve) 查询 show # 查询库中所有的表的名称 show tables; # 查询表结构 desc tablename; # show create table 表名; # 3. U 修改 alter # 修改表名 alter table 表名 rename to 新表名; # 修改表的字符集 alter table 表名 character set 字符集名称; # 增加一列 alter table 表名 add 列名 数据类型; # 修改列名称 类型 alter table 表名 change 列名 新列名 新数据类型; alter table 表名 modify 列名 新数据类型; # 删除列 alter table 表名 drop 列名; # 4. D 删除 drop drop table tablename; drop table if exists tablename;
-
DML
#1. 添加数据
insert into 表名(列名1,列名2,...) values (值1,值2,...);
注意:
列名和值必须以一一对应
如果表名后,不定义列名,默认给全部列赋值
除了数字类型,其他类型都可以使用单双引号引起来
#2. 删除数据
delete from 表名 [where 条件]
truncate table 表名; # 删除表后创建一个一样的表
注意:
如果delete语句不加条件,则逐个删除表中的所有数据(多次)
truncate只会执行一次
#3. 修改数据
updata 表名 set 列名1=值1,列明2=值2,... [where 条件];
注意:
如果不加任何条件,则每条数据都会被修改
DQL
-
语法:
select 字段列表
from 表名列表
where 条件列表
group by 分组字段
having 分组后条件
order by 排序
limit 分页限定
-
基础操作
-
多个字段的查询
select 字段名1,字段名2 from 表名;
-
去除重复
distinct
-
计算列
ifnull(表达式1,表达式2):
表达式1,哪个字段需要判断是否为null
表达式2,替换值
-
别名
as ,as可以省略
-
-
条件查询
-
where子句后解条件
-
运算符
-
> < <= >= = <>
-
between…and
-
in(集合)
-
like : 模糊查询
_: 单个任意字符
%:多(零)个任意字符
-
is null : 判断值是否为 null
-
and 或&&
-
or 或||
-
not 或!
-
-
-
排序查询
order by 字段1 方式1,字段2 方式2....
方式默认asc(升);desc(降)
-
聚合函数
将一列数据按照整体来计算,使用方式 函数名(列名):
- count: 计算个数(null值会被排除,选用主键列)
- max: 计算最大值
- min: 计算最小值
- sum: 计算和
- avg: 计算平均值
注意: 聚合函数的计算,排除null值
-
分组查询
group by 分组字段
注意:
- 分组之后的查询字段,分组字段,聚合函数
- where和having的区别
- where在分组前限定,如果不满足条件,则不参与分组,having在分组后筛选
- where不可接聚合函数,having可以
-
分页查询
limit 开始的索引,每页查询的条数;
索引从0开始limit分页操作是一个MySQL"方言".
约束
概念: 对表中的数据进行限定, 保证数据的正确性 有效性 和 完整性.
分类:
主键约束: primary key
非空约束: not null
唯一约束: unique
外键约束: foreign key
非空约束: not null 值不能为空
-
在创建表添加非空约束
CREATE TABLE stu( id INT, name VARCHAR(20) NOT NULL -- 非空约束 )
-
创建表后添加非空约束
ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;
-
删除非空约束
ALTER TABLE stu MODIFY name VARCHAR(20);
唯一约束: unique 值不能重复
-
在创建表添加唯一约束
CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE --唯一约束 )
-
创建表后添加唯一约束
ALTER TABLE stu MODIFY name VARCHAR(20) UNIQUE;
-
删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
-
注意
- myslq中唯一约束的列可以有多个null.
主键约束: primary key
- 非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录钉钉唯一标识
-
在创建表添加唯一约束
CREATE TABLE stu( id INT PRIMARY KEY, -- 为id添加主键约束 phone_number VARCHAR(20) )
-
创建表后添加唯一约束
ALTER TABLE stu MODIFY name VARCHAR(20) INT PRIMARY KEY;
-
删除主键
ALTER TABLE stu DROP PIRIMARY KEY;
-
自动增长 auto_increment
外键约束 : foreign key
-
在创建表时可以添加外键
CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, CONSTRAINT )
级联操作
- 添加级联操作
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名成(主表列名称) on update cascade
- 分类
- 级联更新
on update cascade
- 级联删除
on delete cascade
- 级联更新
数据库设计
多表之间的关系
- 一对一
- 在任意一方添加外键指向另一方的主键
- 一对多(One to More)
- 在More建立外键,指向One的主键
- 多对多
- 借助中间表,存储表中指向另外两表的主键
范式
- 第一范式: 每一列都是不可分割的原子数据项
- 第二范式: 在1NF的基础上,非码属性必须完全依赖于候选码(消除部分依赖)
- 函数依赖:A–>B,如果通过A属性的值可以确定唯一B属性的值,
- 例如:学号–>姓名 (学号,课程名称)–> 分数
- 完全函数依赖: A–>B,如果A是一个属性组,则B属性值依赖于A的属性组中所有的属性
- 例如:(学号,课程名称)–> 分数
- 部分函数依赖:A–>B,如果A是一个属性组,则B属性值依赖于A的属性组中部分的属性
- 例如:(学号,分数)–> 姓名
- 传递函数依赖:A–>B,B–>C.则A–>C
- 学号–>系名,系名–>系主任 ,则 学号–> 系主任
- 码:如果一张表中,一个属性或属性组,被其他所有属性所完全依赖,称为码
- 函数依赖:A–>B,如果通过A属性的值可以确定唯一B属性的值,
- 第三范式: 在2NF的基础上,任何非主属性不依赖于其他非朱属性(消除传递依赖)
数据库的备份和还原
- 命令行
- 备份
mysqldump -u -p > path
(生成 .sql文件) - 还原
- 登录
- 创建
- 使用
source path
- 备份
多表查询
笛卡尔积
有两个集合A,B,取两个集合的所有组合结果
select * from a,b
内连接
两表交集
隐式内连接
使用where条件筛选
select * from a,b where a.id = b.id
显式内连接
语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件
外连接
左外连接
语法:select 字段列表 from 表名1 left [outer] join 表名2 on 条件
左表所有数据及其交集部分
右外连接
语法:select 字段列表 from 表名1 right[outer] join 表名2 on 条件
右表所有数据及其交集部分
子查询
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
情况
- 单行单列
- 子查询可以作为条件,使用运算符去判断
- 多行单列
- 子查询可以作为条件,使用集合运算符去判断
- 多行多列
- 子查询可以作为一张虚拟表参与查询
事务
事务的基本介绍
介绍:如果一个包含多个步骤的事务,那么这些事务要么全部执行,要么全部不执行
步骤:
- 开启事务:
start transation;
- 回滚:
rollback
- 提交:
commit
- MySQL数据库中事务默认自动提交事务
- 一条DML语句会自动提交一次事务
- 事务提交的两种方式:
- 自动提交
- 手动提交
- 开启事务,提交
- 修改事务的默认提交方式
-
select @@autocommit
1 为自动提交 - 关闭自动提交后,执行dml语句需要使用
commit
提交事务
-
事务的四大特征
- 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
- 持久性:事务一旦提交或回滚后,数据库会持久化的保存数据
- 隔离性:多个事务之间 相互独立
- 一致性:事务操作前后,数据总量不变
事务的隔离级别
概念: 多个事务之间是隔离的,相互独立的,如果多个事务操作同一批数据,则会引发一些问题
存在问题:
- 脏读: 一个事务读取到另一个事务中没有提交的数据
- 不可重复读 :在同一事务中,两次读取到的数据不一样
- 幻读 :一个事务操作数据表中的所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
隔离级别:
- read uncommitted: 读未提交 发生问题 123
- read committed:读已提交 发生问题23 (Oracle)
- repeatable read:可重复读 发生问题3 (MySQL)
- serializable : 串行化
查询隔离级别: …
DCL
管理用户
-
添加用户
CREATE USER 'USER'@'HOST' IDENTIFIED BY 'PSW';
-
删除用户
DROP USER 'USER'@'HOST';
-
修改密码
update user set password = password('newpsw') where name = 'user'
set password for 'name@host' = password('newpsw')
MySQL忘记root用户密码:
- cmd -->
net stop mysql
停止mysql服务 - 使用无验证启动mysql服务:
mysqld --skip-grant-tables
- 打开新的cmd窗口,直接输入mysql即可登录
- 修改密码
- 关闭mysqld,启动mysql服务
-
查询
- USE mysql;
- SELECT * FROM USER;
权限管理
-
查询权限:
show grants for 'user'@'host'
-
授予权限:
grant 权限列表 on 数据库.表名 to 'user'@'host'
- 超级权限
grant all on *.* to 'user'@'host'
- 超级权限
-
撤销权限:
revoke 权限列表 on 数据库.表名 from 'user'@'host'
SQL练习
学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-
查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
1.1 查询同时存在" 01 “课程和” 02 "课程的情况
1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
-
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-
查询在 SC 表存在成绩的学生信息
-
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
4.1 查有成绩的学生信息
-
查询「李」姓老师的数量
-
查询学过「张三」老师授课的同学的信息
-
查询没有学全所有课程的同学的信息
-
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-
查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-
查询没学过"张三"老师讲授的任一门课程的学生姓名
-
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
-
检索" 01 "课程分数小于 60,按分数降序排列的学生信息
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-
查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
-
按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次
-
查询学生的总成绩,并进行排名,总分重复时保留名次空缺
16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
-
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-
查询各科成绩前三名的记录
-
查询每门课程被选修的学生数
-
查询出只选修两门课程的学生学号和姓名
-
查询男生、女生人数
-
查询名字中含有「风」字的学生信息
-
查询同名同性学生名单,并统计同名人数
-
查询 1990 年出生的学生名单
-
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
-
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
-
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
-
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-
查询不及格的课程
-
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
-
求每门课程的学生人数
-
成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-
查询每门功成绩最好的前两名
-
统计每门课程的学生选修人数(超过 5 人的课程才统计)。
-
检索至少选修两门课程的学生学号
-
查询选修了全部课程的学生信息
-
查询各学生的年龄,只按年份来算
-
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
-
查询本周过生日的学生
-
查询下周过生日的学生
-
查询本月过生日的学生
-
查询下月过生日的学生
-- 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select S.*,sc1.score,sc2.score
from SC sc1 join SC sc2 on sc1.SId = sc2.SId
join Course C on sc1.CId = C.CId
join Student S on sc1.SId = S.SId
where sc1.score > sc2.score and sc1.CId='01' and sc2.CId='02';
-- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select S.SId, Sname, Sage, Ssex, sc1.score,sc2.score
from SC sc1
join SC sc2 on sc1.SId = sc2.SId and sc1.CId='01' and sc2.CId='02'
join Student S on sc1.SId = S.SId;
-- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select S.SId, Sname, Sage, Ssex, sc1.score,sc2.score
from SC sc1 left join SC sc2 on sc1.SId = sc2.SId and sc1.CId='01' and sc2.CId='02'
join Student S on sc1.SId = S.SId group by S.SId;
-- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
-- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.SId studentID,
s.Sname studentName,
avg(sc.score) avgGrade
from Student s join SC sc on s.sid = sc.SId
group by s.SId having avg(sc.score) >= 60;
-- 3. 查询在 SC 表存在成绩的学生信息
select sid,sname,sage,ssex from Student where sid in (select sid from SC);
select S.SId,S.Sname,S.Sage,S.Ssex from SC left join Student S on SC.SId = S.SId group by S.SId;
-- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) ***
select S.*,count(score),sum(score)
from Student S
left join SC on S.SId = SC.SId
group by S.SId;
-- 4.1 查有成绩的学生信息 ****
select S.* from SC join Student S on S.SId = SC.SId group by S.SId;
-- 5. 查询「李」姓老师的数量 ****
select count(Tname) from Teacher where Tname like '李%';
-- 6. 查询学过「张三」老师授课的同学的信息 ****
select Student.*
from Student join SC S on Student.SId = S.SId
join Course C on S.CId = C.CId
join Teacher T on C.TId = T.TId
where T.Tname = '张三';
-- 7. 查询没有学全所有课程的同学的信息 ****
select S.SId, Sname, Sage, Ssex,count(SC.CId)
from SC join Student S on SC.SId = S.SId
group by SC.SId
having count(SC.CId) = (select count(Course.CId) from Course);
-- 8. 查询至少有一门课与学号为" 06 "的同学所学相同的同学的信息
select SId
from SC join (select CId from SC where SId='06') SUB
on SC.CId=SUB.CId
where SId != '06' group by SId;
-- 9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select SId,count(*),count(SUB.CId)
from SC left join (select CId from SC where SId='01') SUB
on SC.CId=SUB.CId
where SId != '01'
group by SId
having count(*)=(select count(CId) from SC where SId='01');
-- 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
select SId,Sname
from Student
where SId not in
(select SC.SId
from SC
where SC.CId in (select CId from Course join Teacher T on Course.TId = T.TId where Tname='张三')
group by SId);
-- 11. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select S.SId,S.Sname,avg(score),count(CId)
from SC join Student S on SC.SId = S.SId
where score<60 group by s.SId
having count(CId)>1;
-- 12. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
select S.*,score
from SC join Student S on SC.SId=S.SId
where CId='01' and score<60
order by score desc;
-- 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select S.SId,avg(score)
from SC join Student S on SC.SId = S.SId
group by S.SId
order by avg(score) desc;
-- 14. 查询各科成绩最高分、最低分和平均分:
select CId,max(score),min(score),avg(score)
from SC
group by CId;
-- 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
-- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
-- 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
-- select C.CId,
-- max(score) max,
-- min(score) min,
-- avg(score) avg
-- from SC join Course C on SC.CId = C.CId
-- group by C.CId;
-- 15. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
--
-- 15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次
--
-- 16. 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
-- select SId,sum(score) s from SC group by SId;
select count(b.SId)+1,a.s
from (select SId,sum(score) s from SC group by SId) a
left join (select SId,sum(score) s from SC group by SId) b
on a.SId != b.SId and a.s < b.s
group by a.SId
order by a.s desc,a.SId;
-- 16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
-- 17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-- 18. 查询各科成绩前三名的记录
-- 19. 查询每门课程被选修的学生数
select CId,count(SId)
from SC
group by CId;
-- 20. 查询出只选修两门课程的学生学号和姓名
select S.Sname,S.SId
from SC join Student S on SC.SId = S.SId
group by S.SId
having count(CId)=2;
-- 21. 查询男生、女生人数
select Ssex,count(SId)
from Student
group by Ssex;
-- 22. 查询名字中含有「风」字的学生信息
select Student.*
from Student
where Sname like '%风%';
-- 23. 查询同名同性学生名单,并统计同名人数
select s1.Sname,s1.Ssex,count(s1.Sname)
from Student s1 join Student s2 on s1.SId != s2.SId
where s1.Sname = s2.Sname and s1.Ssex = s2.Ssex group by s1.Sname;
-- 24. 查询 1990 年出生的学生名单
select *
from Student
where strftime('%Y',Sage)='1990';
-- 25. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select CId,avg(score)
from SC
group by CId
order by avg(score) desc,CId;
-- 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select SC.SId,Sname,avg(score),count(CId),sum(score)
from Student join SC on Student.SId=SC.SId
group by SC.SId
having avg(score) > 85;
-- 27. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
select Sname,Cname,score
from Student join SC S on Student.SId = S.SId
join Course C on S.CId = C.CId
where Cname='数学' and score < 60;
-- 28. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
-- 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select Sname,Cname,score
from SC join Student S on SC.SId = S.SId
join Course C on SC.CId = C.CId
where score > 70;
-- 30. 查询不及格的课程
select Student.SId, Sname, Cname, score, Ssex
from SC join Student on Student.SId=SC.SId
join Course C on SC.CId = C.CId
where score < 60;
-- 31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select Student.SId,Student.Sname,CId,score
from SC join Student on SC.SId = Student.SId
where CId='01' and score >= 80;
-- 32. 求每门课程的学生人数
select CId,count(SId) from SC group by CId;
-- ! 33. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select score,SId
from SC join Course C on SC.CId = C.CId join Teacher T on C.TId = T.TId
where Tname='张三'
order by score desc
limit 0,1;
-- 34. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select max(score),SId
from SC join Course C on SC.CId = C.CId join Teacher T on C.TId = T.TId
where Tname='张三';
-- 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select *
from SC s1 join SC s2
where s1.CId != s2.CId and s1.score = s2.score and s1.SId=s2.SId
group by s1.SId;
-- 36. 查询每门功成绩最好的前两名
select a.sid,a.cid,a.score
from sc as a
left join sc as b
on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<2
order by a.cid;
-- 37. 统计每门课程的学生选修人数(超过 5 人的课程才统计)。
select CId
from SC
group by CId
having count(CId) > 5;
-- 38. 检索至少选修两门课程的学生学号
select SId
from SC
group by SId
having count(SId) > 1;
-- 39. 查询选修了全部课程的学生信息
select SId,count(SId)
from SC
group by SId
having count(SId) = (select count(CId) from Course);
-- 40. 查询各学生的年龄,只按年份来算
select date('now') as now, Sage , date('now')- Sage Age from Student;
-- 41. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
-- 42. 查询本周过生日的学生
select * from Student
where strftime('%m',Sage)=strftime('%m',date('now'));
-- 43. 查询下周过生日的学生
select * from Student
where strftime('%m',Sage)=strftime('%m',date('now'));
-- 44. 查询本月过生日的学生
select * from Student
where strftime('%m',Sage)=strftime('%m',date('now'));
-- 45. 查询下月过生日的学生
select * from Student
where strftime('%m',Sage)=strftime('%m',date('now','+1 month'));
select strftime('%m',Sage) from Student;