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 | | |