创建以及修改表

第一关：创建表

create table Student (
    Sno char(10) primary key,
    Sname varchar(20),
    Ssex char(2),
    Sage smallint,
    Sdept varchar(20),
)

第二关：修改表

USE CS_yx_DB
GO
SET NOCOUNT ON


-- ********** Begin ********** --
-- ********** 此处写“3、修改sdept列”的SQL语句 ********** --

alter table Student alter column Sdept varchar(30);

-- ********** End ********** --

GO

USE CS_yx_DB
GO
SET NOCOUNT ON

-- ********** Begin ********** --
-- ********** 此处写“2、删除Cpno列”的SQL语句 ********** --

alter table Course drop column Cpno;

-- ********** End ********** --

GO

第三关：删除表

USE CS_yx_DB
GO
SET NOCOUNT ON

-- ********** Begin ********** --
-- ********** 此处写“1、删除三张表”的SQL语句 ********** --

drop table SC
drop table Student 
drop table Course

-- ********** End ********** --
GO

❤️实验二：对表的插入，修改，删除数据

⭐️第一关：插入数据

❄️插入功能格式

insert into 表名 values("....")
注意：括号内的值如果不规定顺序就是默认顺序

❄️例题

实验完成要求
根据代码区的提示，将插入数据的SQL语句书写在对应的代码区中。

1、为Student表添加两行记录如下表所示。

sno	sname	ssex	sage	sdept
001	Smith	m	18	CS
002	Ketty	f	19	MA

insert into Student(sno,sname,ssex,sage,sdept) 
values('001','Smith','m',18,'CS')
或者
insert into Student 
values('002','Ketty','f',19,'MA')

⭐️第二关：更新数据（修改数据）

❄️修改数据格式

UPDATE语句由三部分组成：
要更新的表；
列名和它们的新值；
要更新行的过滤条件。

⛅️更新单个列

update sc
set grade=grade+5
where grade<60

⛅️更新多个列

update sc
set ssex=ssex+5,grade=grade+5
where grade<60

⛅️删除列

update sc
set ssex=null（如果可以取值为null的话）
where grade<60

⛅️例题

将不及格的学生成绩加5分。

update Sc
set grade=grade+5
where grade<60 

❄️字符串拼接

⛅️concat函数

语法格式

CONCAT(char c1, char c2, ..., char cn) 
其中char代表字符串，定长与不定长均可以

⛅️+

直接将两个字符串相加

Sno（char)='S'+Sno(char)

❄️字符串修改

⛅️substring函数

语法

substring(字符串,x,y)
从第x位开始取，一直取到y位

⛅️例题

将学生的学号前的‘S’删掉

update Student
set Sno=substring(Sno,2,len(Sno)-1)

注意：数据库中的起始序号是1不是0

⛅️rtrim函数

rtrim是一个函数，返回删除字符串右边的空格后的字符串
语法

rtrim(字符串)

⛅️例题

在学生学号的后面加上‘S’（注：**‘S’要大写，学号的类型是char(10)）

update Student set Sno=RTRIM(Sno)+'S';

注意：一定要先去掉空格才能+‘S’，否则会加到后面没有用到的空格上

⭐️第三关：删除数据

❄️delete函数

语法：
DELETE FROM 表名称 WHERE 列名称 = 值

delete from sc
where grade is null;

注意：数据库中判断值是否为空可以的语法是xx is null

⛅️例题

删除年龄等于18岁的女（f）同学

delete from Student
where sage=18 and ssex='f'

❤️实验三：简单查询

⭐️第一关 简单查询

❄️select函数

语法

select 数据类型
from 表名
where 条件

⛅️例题

查询先行课程不为空的课程。（使用*表示查询结果）

select * 
from course
where cpno <> ''

注意：
数据库中的不等于可以用！=也可以用<>表示
数据库中”空“值得单引号内不用加空格

❄️like语句

% ：代表一串字符串
_：代表一个字符
当所查询的字符中有%或者_时，可以用escape‘\’来表示‘\’后面的一个字符为转义字符
语法：

'DV\_%i_ _' escape '\'

表示的意思就是查找DV_…i_ _得字符串

❄️distinct关键字

取出重复的数据
语法

⛅️例题

使用distinct关键字查询学生表中不同的系
并列出系

select distinct sdept 
from student 在这里插入代码片

❄️数据之间有交集的查找

⛅️例题

查询90分以上学生的选课信息，
列出学号，姓名，课程号，成绩。

SELECT A.sno,A.sname,C.cno,C.grade
FROM Student A,SC C
WHERE grade>90 and A.Sno=C.Sno

其中得A.Sno=C.Sno意思就是：先在c中查到的grade>90的学生学号，因为sc表中没有存储姓名，姓名存储在student表中，所以需要用学号（sc和student的公共项数）进行映射

⛅️为什么不能这样

查询90分以上学生的选课信息，列出学号，姓名，课程号，成绩。

⛅️因为前者时查找的是有选课分数>90的学生的所有选课成绩

select student.sno,sname,cno,grade
from sc,student
where sc.sno=student.sno 
and student.sno in
(
   select sno 
    from sc
    where grade>90 
)

这才是正确答案

select student.sno,sname,cno,grade
from sc,student
where grade > 90 
and student.sno=sc.sno

⭐️第二关：嵌套查询

⛅️第一题

查询CS系学生选择的课程，列出学号，课程号，成绩

select sno,cno,grade
from sc
where sno in 
(
    select sno
    from Student
    where student.sdept='CS'
)

⛅️第二题

查询没有选C06（课程号）课程的同学的学号，姓名，性别

select sno,sname,ssex
from student
where sno not in
(
    select sno 
    from sc
    where cno ='C06'
)

⛅️第三题

查询CS系没有选择'DB'课程学生的姓名，列出学生姓名。

select sname
from Student
where sdept='CS' and sno not in
(
    select sno 
    from SC
    where cno  in
    (
        select cno 
        from Course
        where cname='DB'
    )
)

注意：最好不要再嵌套内的条件中写not，因为很难判断，最好就在第一个循环中写not

⛅️第四题

查询‘DB’课程考最高分的选课信息。
列出学号，课程号，成绩

select sno,cno,grade
from SC
where grade in
(
    select max(grade) 
    from SC
    where cno in
    (
        select cno 
        from Course
        where cname='DB'
    )
)

⭐️总结

1.如果是查询的数据都在一个表中，那么就用嵌套循环，如果是不同表中的数据就用连接循环
2.嵌套循环时，按照语句顺序，首先写最内部的循环
比如

查询CS系没有选择'DB'课程学生的姓名，列出学生姓名

可以先查找“没有选择db课程”
即

not in
(
	select cno
	from course 
	where cname='DB'
)

然后查找学生姓名，即用sc，将cno和sno结合就能查找到姓名

select sname
from Student
where sdept='CS' and sno not in
(
    select sno 
    from SC
    where cno  in
    (
        select cno 
        from Course
        where cname='DB'
    )
)在这里插入代码片

⭐️实验四第四关不会！

❤️实验四：聚合查询

和前面的查询最主要的区别就是，聚合查询可以查找以某个学号或者是课程号作为第一列的集合表
select 后面加的，group by后面都要加上去

⭐️eg1：

查询每个学生不及格的课程数，列出学号和不及格课程数
（列名为：scnt）

select sno,count(*) as scnt
from sc
where grade<60
group by sno意思就是以不同的学号作为第一列元素

⭐️eg2：

查询每个系女同学的平均年龄，列出所在系和平均年龄
（列名为：sageavg）。

select sdept,avg(sage) as sageavg
from student 
where ssex='f'
group by sdept

⭐️稍微难一点的eg3：

查询平均分最高的学生的姓名。

select sname
from student 
where sno in 
(
    select sno 
    from sc
    group by sno 
    having avg(grade)>= all(select avg(grade)
    from sc group by sno)
)

⭐️稍微难一点的eg4：

注意：课程号（course表），课程名（course表），不及格人数（sc表）不在同一个表中，所以要使用连接查询

查询不及格人数大于等于2人的课程，列出课程号，
课程名，不及格人数(列名为scnt)

select sc.cno,cname,count(*) scnt
from sc,course 
where sc.cno=course.cno and grade<60 
group by sc.cno,cname having count(*)>=2;

⭐️有点小难的eg5：

查询E系平均成绩最高的同学的姓名，列出姓名

select sname from Sc,Student
where Sc.Sno=Student.Sno and Sdept ='E'
group by Sname
having avg(grade)>=all
(
    select avg(grade) from Sc
    where sno in 
    (
        select sno from Student  where Sdept='E'
    )
    group by sno 
)

1.只要select语句内有聚合函数的话需要group by
2.只要语句前面加了group by xxx，那么having里面的语句也要加group by xxx

❤️实验五：约束和索引

⭐️约束

❄️主键约束

当一个列被指定为主键之后，该列就不为空，而且不会有重复值出现，这样逐渐才能唯一标志一条记录

⛅️建表时创建主键

create table 表名(
    id int(11) primary key
}

⛅️修改表的指定主键

alter table 表名 add primary key(主键列)

⛅️删除主键

alter table 表名 drop primary key;

❄️非空约束not null

某些列不能插入为null值，所以可对列添加非空约束：

create table 表名(
    id int(11) primary key auto_increment,
    name varchar(255) not null
);

❄️唯一值约束unique

唯一约束 unique
某些列不能插入重复的值，所哟一可对列添加唯一约束

create table 表名(
    id int(11) primary key auto_increment,
    name varchar(255) not null unique
);

❄️外键约束

1.外键必须是其他表的主键
2.外键可以为空
3.外键可以重复

create table sc(
	sno char (9),
	cno char(5)
	primary key (sno ,cno),
	foreign key(sno) reference student(sno)
	foreign key(cno) reference course(cno),
	意思就是在student和course中有存在的(sno/cno)的才能存入sc
)

❄️检查约束

eg：

alter table student add constraint stu_def_ssex 
default 'm' for ssex

❄️缺省约束

eg：

alter table student add constraint stu_def_ssex 
default 'm' for ssex

❄️例题

1、为Student表的Sage列添加约束，使其取值小于30岁(约束名：stu_chk_sage)
2、为student表的Ssex列添加约束，使其只能取值‘m’或‘f’(约束名：stu_chk_ssex)
3、为Student表的ssex列添加缺省约束，缺省值为‘m’(约束名：stu_def_ssex)
4.为SC表的grade列添加检查约束（1到100分）（约束名：stu_chk_grade）

alter table Student add constraint stu_chk_sage
check(sage<30);

alter table student add constraint stu_chk_ssex 
check(ssex='f' or ssex='m')

alter table student add constraint stu_def_ssex 
default 'm' for ssex

alter table sc add constraint stu_chk_grade 
check (grade>=1 and grade<=100)

⭐️索引

❄️为什么要引入索引

索引是对数据库表中的一列或者多列进行排序的一种结构，可以大大加快mysql检索的速度

❄️索引的优缺点

优点：提高了检索效率
缺点：占据了内存空间，创建索引和维护所以都要耗费时间
所以可以在经常用到的列上建立索引，但是在重复值多或者是数据值很少的列上就不加了

❄️索引的建立和删除

⛅️建立唯一索引

唯一索引是不允许表中任何两行具有相同索引值的索引

create unique/cluster index 索引名 on 表名（列名）

⛅️递增递减索引

eg：按学号递增的索引

create unique index snoindex on student(sno asc)
asc：升序 dec：降序

❤️实验六：视图

⭐️视图的作用

1.试图能简化用户的操作，比如需要连接查询时，本来需要连接多个表，但是现在是需要连接一个表
2.视图是一个虚拟的表，能增加数据的安全性，试图屏蔽了底层的表结构，从而更好的保护了数据的安全性

⭐️创建视图

❄️例题

1、创建视图vcg，查询课程名为’Math’的课程中，成绩不及格的学生，列出学号，课程号，成绩。
2、修改视图vcg中的数据，将成绩加5分
3、删除视图vcg中的数据，将成绩小于40分的选课信息删除。

create view vcg as
(
    select sno,cno,grade 
    from sc
    where grade<60 and cno in 
    (
        select cno 
        from course 
        where cname='Math'
    )
)

update vcg
set grade = grade + 5 

delete from vcg 
where grade < 40

⭐️使用视图

❄️例题

1、创建视图vsumc，使其包含每个选了课的学生获得的学分（成绩及格才能得学分）。列出学号和总学分（列名：ssumc）。
2、使用vsumc视图，查询男同学总学分高于12分的学生的学号，姓名，性别。
3、使用vsumc视图，查询总学分最高的学生，列出学号，姓名，性别，年龄，所在系
4、使用vsumc视图，查询平均分大于等于65分的学生的学号，平均分(列名：savg)，总学分（列名：ssumc）
1.

create view vsumc as 
(
    select sno,sum(Ccredit) as ssumc from sc,course
    where grade>=60 and sc.cno=course.cno
    group by sno
)

2:
因为视图也是一张表，所以可以这样写：

select student.sno,sname,ssex 
from student,vsumc
where ssex='m' and ssumc>12 and student.sno=vsumc.sno

3:

select vsumc.sno,sname,ssex,sage,sdept
from vsumc,Student
where  ssumc >= all(
	select ssumc from vsumc
) and Student.sno = vsumc.sno

4:
attention:where—group by—having

select SC.sno, avg(grade) savg,ssumc from vsumc,SC 
where SC.sno=vsumc.sno 
group by SC.sno,ssumc
having avg(grade)>=65

实验七：用户权限