_SQL语句练习06

2023-11-23 22:58:27 浏览数 (1)

前言：

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。

SQL语言1974年由Boyce和Chamberlin提出，并首先在IBM公司研制的关系数据库系统SystemR上实现。由于它具有功能丰富、使用方便灵活、语言简洁易学等突出的优点，深受计算机工业界和计算机用户的欢迎。1980年10月，经美国国家标准局（ANSI）的数据库委员会X3H2批准，将SQL作为关系数据库语言的美国标准，同年公布了标准SQL，此后不久，国际标准化组织（ISO）也作出了同样的决定。

SQL从功能上可以分为3部分：数据定义、数据操纵和数据控制。

SQL的核心部分相当于关系代数，但又具有关系代数所没有的许多特点，如聚集、数据库更新等。它是一个综合的、通用的、功能极强的关系数据库语言。其特点是：

1、数据描述、操纵、控制等功能一体化。

2、两种使用方式，统一的语法结构。SQL有两种使用方式。一是联机交互使用，这种方式下的SQL实际上是作为自含型语言使用的。另一种方式是嵌入到某种高级程序设计语言（如C语言等）中去使用。前一种方式适合于非计算机专业人员使用，后一种方式适合于专业计算机人员使用。尽管使用方式不向，但所用语言的语法结构基本上是一致的。

3、高度非过程化。SQL是一种第四代语言（4GL），用户只需要提出“干什么”，无须具体指明“怎么干”，像存取路径选择和具体处理操作等均由系统自动完成。

4、语言简洁，易学易用。尽管SQL的功能很强，但语言十分简洁，核心功能只用了9个动词。SQL的语法接近英语口语，所以，用户很容易学习和使用。

一、建表

  • 一、建立如下学生表(命名格式“姓名拼音_三位学号_week8student”，如LBJ_023_week8student）

create table LYL_116_week8student(SNO int primary key, SNAME char(8) not null unique,SEX char(2), DEPTNO int foreign key references LYL_116_week8dept(DEPTNO)) 

  • 二、建立如下课程表(命名格式“姓名拼音_三位学号_week8course”，如LBJ_023_week8course）

create table LYL_116_week8course(CNO int, CNAME char(20) not null,TNO int,CREDIT int, primary key(CNO,TNO), foreign key (TNO) references LYL_116_week8teacher(TNO)) 

  • 三、建立如下选课表(命名格式“姓名拼音_三位学号_week8sc”，如LBJ_023_week8sc）

create table LYL_116_week8sc(SNO int,CNO int,GRADE int, primary key(SNO,CNO), foreign key (SNO) references LYL_116_week8student(SNO), foreign key (CNO) references LYL_116_week8course(CNO)) 

  • 四、建立如下教师表(命名格式“姓名拼音_三位学号_week8teacher”，如LBJ_023_week8teacher）

create table LYL_116_week8teacher(TNO int primary key, TNAME char(8) not null, DEPTNO int foreign key references LYL_116_week8dept(DEPTNO)) 

  • 五、建立如下系表(命名格式“姓名拼音_三位学号_week8dept”，如LBJ_023_week8dept）

create table LYL_116_week8dept(DEPTNO int primary key, DEPTNAME char(20) not null) 

二、SQL语句练习

  • 1）在Student表中加入属性SAGE(INT型)

alter table LYL_116_week8student add SAGE int

  • 2）将Student表中的属性SAGE类型改为SMALLINT型

alter table LYL_116_week8student  alter column SAGE smallint

  • 3）在Student表上建立关于SNO的唯一索引

alter table LYL_116_week8student add unique(SNO)

  • 4）在Course表上建立关于CNO降序的唯一索引

alter table LYL_116_week8course add unique(CNO desc)

  • 5）在五张表中插入如右侧的数据
代码语言：javascript复制
 insert into LYL_116_week8student values(1001,'张天','男',10,20)
 insert into LYL_116_week8student values(1002,'李兰','女',10,21)
 insert into LYL_116_week8student values(1003,'陈铭','男',10,21)
 insert into LYL_116_week8student values(1004,'刘茜','女',20,21)
 insert into LYL_116_week8student values(1005,'马朝阳','男',20,22)

代码语言：javascript复制
 insert into LYL_116_week8course values(1,'数据结构',101,4)
 insert into LYL_116_week8course values(2,'数据库',102,4)
 insert into LYL_116_week8course values(3,'离散数学',103,4)
 insert into LYL_116_week8course values(4,'C语言程序设计',101,2)
代码语言：javascript复制
 insert into LYL_116_week8sc values(1001,1,80)
 insert into LYL_116_week8sc values(1001,2,85)
 insert into LYL_116_week8sc values(1001,3,78)
 insert into LYL_116_week8sc values(1002,1,78)
 insert into LYL_116_week8sc values(1002,2,82)
 insert into LYL_116_week8sc values(1003,1,92)
 insert into LYL_116_week8sc values(1004,1,87)
 insert into LYL_116_week8sc values(1004,4,90)
 insert into LYL_116_week8sc values(1005,1,85)
 insert into LYL_116_week8sc values(1005,4,92)

代码语言：javascript复制
 insert into LYL_116_week8teacher values(101,'张星',10)
 insert into LYL_116_week8teacher values(102,'李珊',10)
 insert into LYL_116_week8teacher values(103,'赵天应',10)
 insert into LYL_116_week8teacher values(104,'李田',20)
代码语言：javascript复制
insert into LYL_116_week8dept values(10,'计算机')  
insert into LYL_116_week8dept values(20,'信息') 

  • 6）查询全体学生的姓名和所在的系名

select s.SNAME'姓名',d.DEPTNAME'所在系名' from LYL_116_week8student s, LYL_116_week8dept d where s.DEPTNO = d.DEPTNO

  • 7）查询数据结构这门课的平均成绩

select avg(sc.GRADE)'数据结构的平均成绩'  from LYL_116_week8sc sc,LYL_116_week8course c where sc.CNO = c.CNO and c.CNAME='数据结构' group by sc.CNO

  • 8）为计算机系的学生记录建立一个视图CS_STUDENT

create view CS_STUDENT as select s.SNO,s.SNAME,s.SEX,s.DEPTNO,d.DEPTNAME from LYL_116_week8student s,LYL_116_week8dept d where s.DEPTNO=d.DEPTNO and d.DEPTNAME='计算机'

  • 9）利用视图，列出所有计算机学生的姓名，选课名和成绩

select cs.SNAME,c.CNAME,sc.GRADE from CS_STUDENT cs,LYL_116_week8sc sc,LYL_116_week8course c where c.CNO=sc.CNO and cs.SNO = sc.SNO

  • 10）查询所有女生的姓名

select s.SNAME from LYL_116_week8student s where s.SEX='女'

  • 11）查询成绩在80到89之间的所有学生选课记录，查询结果按成绩的降序排列

select * from LYL_116_week8sc sc where sc.GRADE between 80 and 89 order by sc.GRADE desc

  • 12）查询各个系的学生人数

select count(s.DEPTNO)'人数',d.DEPTNAME'系名' from LYL_116_week8student s,LYL_116_week8dept d where s.DEPTNO=d.DEPTNO group by s.DEPTNO,d.DEPTNAME

  • 13）查询信息系年龄在21岁以下（含21岁）的女生姓名及其年龄

select s.SNAME,s.SAGE from LYL_116_week8dept d,LYL_116_week8student s where d.DEPTNAME='信息' and s.SEX='女' and s.SAGE<=21

  • 14）查询选修总学分在10学分以上的学生姓名

select s.SNAME'选修总学分在10分以上' from LYL_116_week8course c,LYL_116_week8sc sc,LYL_116_week8student s where c.CNO=sc.CNO and s.SNO=sc.SNO  group by s.SNAME having sum(c.CREDIT)>10

  • 15）查询各门课程取得最高成绩的课程号、学生姓名及其成绩

select m.CNO,s.SNAME,m.maxG from LYL_116_week8sc sc2,LYL_116_week8student s, (select sc1.CNO,max(sc1.GRADE)'maxG' from LYL_116_week8sc sc1 group by sc1.CNO)m where m.CNO=sc2.CNO and sc2.GRADE=m.maxG and sc2.SNO=s.SNO

  • 16）查询选修了学号为1002的学生选修的全部课程的学生学号

select distinct SNO from LYL_116_week8sc sc1 where not exists(   select * from LYL_116_week8sc sc2   where sc2.SNO='1002'   and not exists(     select * from LYL_116_week8sc sc3     where sc3.SNO = sc1.SNO     and sc3.CNO = sc2.CNO))

  • 17）查询选修了张星老师开设的全部课程的学生姓名

select s.SNAME from LYL_116_week8student s where not exists (   select * from LYL_116_week8course c   where c.TNO in(     select t.TNO from LYL_116_week8teacher t     where t.TNAME='张星'     and not exists(       select * from LYL_116_week8sc sc       where sc.SNO=s.SNO and sc.CNO=c.CNO)) )

  • 18）查询选修张星老师数据结构课的学生的姓名和成绩

select s.SNAME,sc1.GRADE from LYL_116_week8sc sc1,LYL_116_week8student s where sc1.CNO in  (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and sc1.SNO = s.SNO

  • 19）将张星老师数据结构课的学生成绩全部加2分

update LYL_116_week8sc set GRADE = GRADE 2 where CNO in (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and SNO in ( select sc1.SNO from LYL_116_week8sc sc1,LYL_116_week8student s where sc1.CNO in  (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and sc1.SNO = s.SNO)

  • 20）再次查询选修张星老师数据结构课的学生的姓名和成绩

select s.SNAME,sc1.GRADE from LYL_116_week8sc sc1,LYL_116_week8student s where sc1.CNO in  (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and sc1.SNO = s.SNO

  • 21）查询马朝阳同学的所有选课记录

select * from LYL_116_week8student s,LYL_116_week8sc sc where s.SNAME='马朝阳' and s.SNO=sc.SNO

  • 22）删除马朝阳同学的所有选课记录

delete from LYL_116_week8sc where SNO in (select SNO from LYL_116_week8student where SNAME='马朝阳')

  • 23）再次查询马朝阳同学的所有选课记录

select * from LYL_116_week8student s,LYL_116_week8sc sc where s.SNAME='马朝阳' and s.SNO=sc.SNO

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！

2023腾讯·技术创作特训营第三期

0 人点赞