MySQL基础练习

MySQL基础练习

本章节答案并非完全标准，请自行判断，相关库表SQL代码见：MySQL基础练习.sql。

1、 什么是事务，事务有那些基本特性，事务操作语句中，提交、回滚事务的命令是什么？

• 事务：事务维护数据库完整性，保证在执行SQL时要么全部完成，要么全部不完成。
• 基本特性：
  • 原子性：事务是一个不可再分割的工作单元，事务中的操作要么都发生，要么都不发生。
  • 一致性：事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；比如：转账前后，两个用户的金额总数不变。多条DML语句要么同时成功，要么同时失败。
  • 隔离性：多个事务之间，相互独立，互不干扰。
  • 持久性：当事务提交或回滚后，将数据库数据持久化到硬盘中。
• 在MySQL中，事务操作语句有：
  • 1、 开启事务：start transaction;
  • 2、 事务提交：commit;
  • 3、保存回滚点save point [ponit 名称]
  • 4、事务回滚：rollback;（rollback to [ponit 名称] 回滚到指定位置）

2、以下是表结构–Comp公司表

名称	描述	类型长度	是否主外键
Comp_id	公司编号	Integer（20）
Comp_name	公司名称	Vachar(10)
Comp_type	公司类型	Vachar(20)

（1）sql写出公司表的建表语句DDL

create table comp(
	comp_id INTEGER(20),
	comp_name varchar(10),
	comp_type varchar(20)
)

（2） Comp表追加定义主码（Comp_id）的语句

alter table comp add primary key(comp_id);

（3）给公司表添加一个地址字段comp_addr，类型为varchar(20)

alter table comp add comp_addr varchar(20);

（4） 将comp_name最大列宽增加到20个字符

alter table comp modify comp_name varchar(20);

（5） 将comp_name字段设置为索引

create index comp_name_index on comp(comp_name);

3、数据库设计范式，举例描述第一范式、第二范式、第三范式

• 第一范式：任何一张表都有主键，每一个字段都是不可再分的原子项；
• 第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖；
• 第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。

4、基础查询

Teacher 老师表
字段名称	描述	类型	长度	是否pk/fk	备注
Tno	老师编号	Integer	20	Pk
Tname	老师姓名	Varchar	20
State	当前状态	Integer	4		0离职1在职
Student 学生表
字段名称	描述	类型	长度	是否pk/fk	备注
sno	学生编号	Integer	20	Pk
Sname	学生姓名	Varchar	20
Ssex	性别	Integer	4
Sdept	系别单位	Varchar	20
Sage	年龄	Integer	4
Course 课程表
字段名称	描述	类型	长度	是否pk/fk	备注
cno	课程编号	Integer	20	Pk
cname	课程姓名	Varchar	20
Cscon	学分	Integer	4
Tno	老师编号	Integer	4	Fk	Teacher(tno)
Sc 选课表
字段名称	描述	类型	长度	是否pk/fk	备注
sno	学生编号	Integer	4	Pk	Student(sno)
Cno	课程编号	Integer	4	Pk	Course(cno)
Grade	成绩	Integer	4

（1）使用标准SQL嵌套语句查询选修课程名称为’数据库’的学员学号和姓名

select s.sno, s.sname
from student s
left join sc sc1 on s.sno = sc1.sno
left join course c on sc1.cno = c.cno
where c.cname = '数据库'

（2）使用标准SQL嵌套语句查询选修课程编号为2001的学员姓名和所属系别单位

select sname, sdept
from student
where sno in (
		select sno from sc where cno = '2001'
	)

（3）使用标准SQL嵌套语句查询不选修课程编号为1001的学员姓名和所属系别单位

select sname, sdept
from student
where sno not in (
		select sno from sc where cno = '1001'
	)

（4）使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属系别单位

select s.sname, s.sdept 
from student s 
where not exists (
	select * from course c where not exists (
		select * from sc where sc.sno = s.sno and sc.cno = c.cno
		) 
	)

（5）查询选修了课程的学员人数

select count(*)
from student
where exists (select * from sc)

（6）查询选修课程超过5门（含）的学员学号和所属系别单位

select s.sno, s.sdept from student s
where exists (
	select * from sc sc1 
	where s.sno=sc1.sno 
	group by sc1.sno having count(sc1.cno) >= 5
)

（7）找出没有选修过“李耀成”老师讲授课程的所有学生姓名

select sname from student s
where not exists (
		select * from sc sc1, course c, teacher t
		where
			sc1.cno = c.cno
		and c.tno = t.tno
		and s.sno = sc1.sno
		and t.tname = '李耀成'
	)

（8）列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

select s.sname, avg(sc.grade)
from student s, sc ,( 
	select sno, grade from sc where grade < 60
	group by sno having count(distinct cno) >= 2
) a
where s.sno = a.sno and sc.sno = a.sno
group by s.sname

（9）列出既学过“1001”号课程，又学过“1002”号课程的所有学生姓名

select s.sname from student s
inner join sc sc1 on s.sno = sc1.sno and sc1.cno='1001'
inner join sc sc2 on s.sno = sc2.sno and sc2.cno='1002'

（10）列出“1001”号课成绩比“1002”号同学该门课成绩高的所有学生的学号

select distinct(s.sno)
from student s, sc sc1, sc sc2
where
	sc1.cno = '1001'
and sc2.sno = '1002'
and sc1.grade > sc2.grade

（11）列出“1001”号课成绩比“1002”号课成绩低的所有学生的学号及其“1001”号课和“1002”号课的成绩

select s.sno, sc1.grade, sc2.grade
from student s, sc sc1, sc sc2
where
	sc1.cno = '1001'
and sc2.cno = '1002'
and sc1.grade < sc2.grade

5、统计查询

– 人员情况表（employee）:里面有一字段文化程度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,现在我要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少。

select
	wh as '学历',
	eage as '年龄',
	count(*) as '人数',
	count(*) / (select count(*) from employee) as '百分比'
from employee group by wh, eage

6、

表一(AAA) — 商品总量表

– 商品名称mc 商品总量sl

– A 100

– B 120

– 表二(BBB) — 商品出库表

– 商品名称mc 出库数量sl

– A 10

– A 20

– B 10

– B 20

– B 30

– 用一条SQL语句算出商品A,B目前还剩多少？

select a.mc, a.sl - (
		select sum(b.sl) from bbb b where b.mc = a.mc
	) s
from aaa a;

7、将good表中数据写sql输出为右表中的数据（结果要先按照品种汇总,再按照日期汇总）

表good（商品表）

SELECT
	NAME AS '品种 cat',
	date AS '日期 date',
	sum(num) AS '数量 cnts（汇总）'
FROM
	good
GROUP BY
	NAME,
	date

8、表diary

id：编号

title：标题

content：内容

recdate：书写时间

（1）查找最新的7条日记

（2）删除标题，内容均重复的日记记录

（1）select * from diary order BY recdate desc limit 0,7

（2）delete from diary where id not in (select max(id) from diary group by title,content)

9、用一句sql把A表中存在于B表中的a字段（1,3,5）对应的b字段的值跟新为c字段的值

​ A表 B表

a(pk) b a(pk) c

​ 1 1 1 2

​ 2 1 3 2

​ 3 1 5 2

​ 4 1

​ 5 1

update a,b set a.b=b.c where a.a=b.a

8、分析设计题

网络的普及带给了人们更多的学习途径，随之而来的管理远程网络教学的“远程网络教学系统”诞生了。

“远程网络教学系统”的功能需求如下：

（1）学生登录网站后，可以浏览课件、查找课件、下载课件、观看教学视频。

（2）教师登录网站后，可以上传课件、上传教学视频、发布教学心得、查看教学心得、修改教学心得。

（3）系统管理员负责对网站页面的维护、审核不合法课件和不合法教学信息、批准用户注册。

1）学生需要登录“远程网络教学系统”后才能正常使用该系统的所有功能。如果忘记密码，可与通过“找回密码”功能恢复密码。请画出学生参与者的用例图。

2）教师如果忘记密码，可以通过“找回密码”功能找回密码。请画出教师参与者的用例图。

3）请依据第三范式设计数据库结构。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nTQC6Vdc-1680103080788)(D:\XuDeZhi\Desktop\md\assets\clip_image002.png)]

用户表
字段名	描述	类型	长度	是否PK/FK	备注
user_id	用户ID	varchar	8	PK
user_name	用户名	varchar	12
account	登录帐户	varchar	12
password	登录密码	varchar	16
role_id	角色ID	varchar	8
enabled	启用状态	int	2		0禁用1启用
join_date	注册时间	datetime			now()

角色表
字段名	描述	类型	长度	是否PK/FK	备注
role_id	角色ID	varchar	8	PK
role_name	角色名称	varchar	12
role_desc	角色描述	varchar	200
role_type	角色类型	int	11		0:管理员 1:老师 2:学生

用户角色关系表
字段名	描述	类型	长度	是否PK/FK	备注
id	主键ID	int	11	PK
user_id	用户ID	varchar	8
role_id	角色ID	varchar	8

课件表（Courseware）
字段名	描述	类型	长度	是否PK/FK	备注
cw_id	课件ID	varchar	8	PK
cw_name	课件名称	varchar	30
cw_type	课件类型	varchar	20		0普通1视频
cw_status	课件状态	int	2		0禁用1启用
cw_message	课件留言	text
cw_addr	课件地址				云存储地址

课件操作记录表
字段名	描述	类型	长度	是否PK/FK	备注
cw_ operate_id	课件操作记录ID	varchar	8	PK
cw_ operate_type	课件操作类型	int	2		0上传 1下载 2修改 3浏览
user_id	用户ID	varchar	8
cw_id	课件ID	varchar	8

         |

| cw_ operate_id | 课件操作记录ID | varchar | 8 | PK | |
| cw_ operate_type | 课件操作类型 | int | 2 | | 0上传 1下载 2修改 3浏览 |
| user_id | 用户ID | varchar | 8 | | |
| cw_id | 课件ID | varchar | 8 | | |