SQL复习

1.准备

student表结构：

字段名称	数据类型	是否允许空值	约束	说明
SNo	char(8)	×	主键	学号
SName	varchar(15)	×	唯一值	姓名
SSex	char(1)	×	检查约束	性别（男或女）
Sdept	varchar(20)	√	默认值(软件工程)	所在系
SBir	datetime	√		出生日期
Scredits	int	√		总学分

create table student(
	SNo char(8) primary key,
	SName varchar(15) not null unique,
	SSex enum('男','女') not null,
	Sdept varchar(20) default '软件工程' ,
	SBir datetime,
	Scredits int
);

course表结构:

字段名称	数据类型	是否允许空值	约束	说明
CNo	char(5)	×	主键	课程编号
CName	varchar(30)	×	唯一值	课程名称
CPno	char(5)	√	外码	先修课程
CTime	decimal(3,0)	√		总学时
CCredit	int	√	默认值4	学分
CTerm	char(1)	×		学期

create table course(
	CNo char(5) primary key,
	CName varchar(30) not null ,
	CPno char(5),
	CTime decimal(3,0),
	CCredit int default 4,
	CTerm char(1) not null,
	foreign key(CPno) references `course`(CNo)
);

SC表结构：

字段名称	数据类型	是否允许空值	约束	说明
SNo	char(8)	×	外码(联合主键)	学号
CNo	char(5)	×	外码(联合主键)	课程编号
Score	decimal(3,1)	√	0~100	成绩

create table sc(
	SNo char(8) not null,
	CNo char(5) not null,
	Score decimal(3,1) ,
	primary key(SNo,CNo),
	foreign key(SNo) references `student`(SNo),
	foreign key(CNo) references `course`(CNo)
);

# 给分数创建触发器
DELIMITER $
create trigger scorecheck before insert on SC for each row
begin
if new.Score<0 or new.Score>100 then set new.Score=-1;end if;
end $
DELIMITER ;

2.增删改

# 插入数据
insert into student values('00000001','张三','男','软件工程','1800-05-19',11);
insert into fstu select * from student where SSex='女';

# 删除记录
delete * from sc;
delete from student where SNo='00000001';

# 修改
update student set SSex='女',Scredits=10 WHERE SName='刘宏';
update sc set Score=Score*0.9 where cno = (select cno from course where cname="c语言");

3.查询

1. 单表查询

简单查询

select * from student where Sname in ("张三","李四");			# in 散点查询
select sname from student where sno like "%01";					# like 模糊查询
select sno from student where scredits between 6 and 11;		# between...and...范围查找
select sname from student where scredits>6 and scredits<=11;

# 聚集函数
count()、substring()、max()、min()、avg()、sum()

分组查询

# 统计各个班级的的学生人数,按照学生人数降序显示
select substring(sno,1,6)班级,count(substring(sno,1,6))人数
from student 
group by substring(sno,1,6)
order by count(substring(sno,1,6)) desc;

# 查询不同学时的课程数大于等于3门课程的学时及门数
select ctime"课时",count(*)"数目"
from course
group by Ctime
having count(cno)>=3;

# 找出每门课程分数相同的人数
select cno"课程号",score"分数",count(*)"数目"
from sc
group by cno,score
having count(cno)>=2;

# 查找岁数相同的同学的数目
select year(Sbir)`出生年`,count(*)数目
from student 
group by year(Sbir);

# 按照不同性别，不同出生年份分组统计相应人数
select ssex`性别`,year(Sbir)`出生年`,count(*)数目
from student 
group by ssex,year(Sbir);

Limit限制结果

# 数据分成3页显示，每页5条记录。现查看第2页数据信息
select * from student limit 5,5;

# 查看5-10条学生信息
select * from student limit 6 offset 4;

2.多表查询

自然连接查询(=)

# 查询成绩在70至85之间（含边界值）的“男”同学的学号、姓名、课程名及成绩，并按成绩降序排列

select student.sno"学号",sname"姓名",cname"课程名",score"成绩"
from student,course,sc
where sc.cno=course.cno and
	student.sno=sc.sno and
	sc.score between 70 and 85 and
	student.ssex="男"
order by score desc;

select student.sno"学号",sname"姓名",cname"课程名",score"成绩"
from student
natural join course natural join sc
where ssex="男" and score between 70 and 85
order by score desc;

内连接查询(inner JOIN…ON)

# 查询成绩为空的"男"同学的学号 姓名 课程名 成绩

select student.sno"学号",sname"姓名",cname"课程名",score"成绩"
from student
inner join sc
on student.sno=sc.sno and ssex="男"
inner join course
on course.cno=sc.cno and score is null;

(左)外连接查询(left JOIN…ON)

select s.sno"学号",sname"姓名",cname"课程名",score"成绩"
from student as s
left join sc
on s.sno=sc.sno
left join course as c
on c.cno=sc.cno
order by s.sno;

嵌套查询

# 查询和“数据结构”课程在同一学期开设并且超过36学时的课程名

select cname
from course
where cterm=(
	select cterm
	from course
	where cname="数据结构"
) and ctime>36;

基于派生表的查询

# 查询“00009”号课程成绩最高分的同学的学号、姓名

select sno"学号",sname"姓名"
from student,(select sno as m_sno,max(score) as m_score
		from sc
		where score=(
			select max(score)
			from sc
			where cno="00009")) as max_score
where student.sno=max_score.m_sno;

4.其他

1.视图

# 显示软件工程 课程号为“00004”的分数加5
create view v_addscore(学号,姓名,课程号,课程,分数)
	as
	select s.sno,sname,c.cno,cname,score+5
	from student s,course c,sc
	where s.sno=sc.sno and c.cno=sc.cno and s.sdept="软件工程" and sc.cno="00004";

如果视图包含下述结构中的任何一种，那么它就是不可更新的：
（1）聚合函数；
（2）DISTINCT关键字；
（3）GROUP BY子句；
（4）ORDER BY子句；
（5）HAVING子句；
（6）UNION运算符；
（7）位于选择列表中的子查询；
（8）FROM子句中包含多个表；
（9）SELECT语句中引用了不可更新视图；
（10）WHERE子句中的子查询，引用FROM子句中的表；
（11）ALGORITHM 选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）

2.存储过程

delimiter更改SQL语句结束符
不带参数的

# 创建一个存储过程，返回软件专业本班大于20岁的学生学号，姓名，性别
delimiter $
create procedure ageup20()
begin
select sno"学号",sname"姓名",ssex"性别",year(curdate())-year(sbir)"年龄"
from student
where sdept="软件工程" and (year(curdate())-year(sbir))>20;
end $
delimiter ;

call ageup20();		# 调用

带参数的

# 创建一个存储过程，实现查询某门课程成绩在60~90之间的男学生名单
delimiter $
create procedure stu_score(in s_cname varchar(20))
begin
if s_cname!=null and s_cname!="" then
select sname
from student s,course c,sc
where s.sno=sc.sno and c.cno=sc.cno
      and s.ssex="男" and c.cname=s_cname
      and score>=60 and score<=90;
end if;
end $
delimiter ;

delimiter $
create procedure stu_score1 (in s_cname varchar(20))
begin
if s_cname!=null and s_cname!="" then
select sname
from student
where sno in (
    select sno
    from sc
    where cno in (
        select cno
        from course
        where cname=s_cname
) and score>=60 and score<=90
) and ssex="男";
end if;
end $
delimiter ;

call stu_score("数据库系统");

带输入、输出参数的存储过程

# 创建一个存储过程，实现计算全体学生某门功课的平均成绩的功能
delimiter $
create procedure s_avg(in c_name varchar(25),out c_no varchar(20),out c_avg decimal(5,2))
begin
if c_name!=null or c_name!="" then
select cno,avg(score) into c_no,c_avg
from sc
where cno=(
    select cno
    from course
    where cname=c_name
)
group by cno;
end if;
end$
delimiter ;

call s_avg("c语言",@no,@res);

select @no,@res;

3.存储函数

# 创建一个存储函数，返回sc表中某门课的数目，有结果返回实际选课数，无结果显示“无人选此课”

delimiter $
create function c_count(c_name varchar(20))
returns varchar(20)
begin
declare num int;
set num=(select count(*)
	from sc
	where cno=(select cno from course where cname=c_name));
if(num!=0) then
     return(concat(num,""));
else
    return("无人选此课");
end if;
end$
delimiter ;

select c_count("c语言");

5.数据库的备份与还原

• 使用mysqldump备份chapter08数据库 mysqldump -uroot -proot chapter08>d:文件数据库备份chapter08.sql
• 备份多个数据库 mysqldump -uroot -proot --database chapter08 sm1>d:文件数据库备份 wo.sql
• 还原chapter08数据库 mysql -u root -p root chaptr08<d:文件数据库备份chapter08.sql
• 还原多个数据库 mysql -u root -proot <d:文件数据库备份 wo.sql

6.用户管理(这里是旧版[5.0],新版本[8.0]将创建与授权分开)

改变数据库 use mysql;

• 使用SELECT语句查看mysql.user表内容
  select * from mysql.user G;

• 使用CREATE USER语句创建用户(创建用户aaa 可远程登陆 密码为：123456789)
  create user "aaa"@"%" identified by "123456789";

• 使用GRANT语句创建用户(创建用户aaa 所有权限 只能本地登陆 密码为：123456789)
  grant all privileges on *.* to aaa@localhost identified by '123456789' with grant option;

• 使用INSERT语句创建用户(创建用户ai 本地连接 密码：123456789)
  insert into mysql.user(Host,User,Password) values("localhost","ai",password("123456789"));
  错误的原因是mysql默认配置严格模式，该模式禁止通过insert的方式直接修改mysql库中的user表进行添加新用户。
  解决方法是修改my.ini(Windows系统)或my.conf(Linux系统)配置文件
  sql-mode=" STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  修改为：sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" =>重启mysql服务

• 删除普通用户(删除用户'ai'@'localhost)
  drop user 'ai'@'localhost';

• 修改用户密码(修改’aaa’@’%’密码为987654321)
  update mysql.user set password = password('987654321') where user = 'ahb' and host = '%';
flush privileges; 刷新MySQL系统权限相关表，否则会拒绝访问

• 权限管理..........