实验一
简单语句
问题
SQL语句
创建一个名为 mydb 的数据库。
create database mydb;
创建一个名为 xsgl 的数据库，要求在创建之前先判断同名数据库是否存在。
create database if not exists xsgl;
查看当前服务器中的所有数据库。
show databases;
删除数据库 mydb。
drop database mydb;
再次查看当前服务器中的所有数据库，验证删除操作是否成功。
show databases;
向表 xs 中增加"入学时间"属性列，其列名为 rxsj，数据类型为 datetime 型。
alter table xs add column rxsj datetime;
将表 xs 中 nl（年龄）列的数据类型改为 int 型。
alter table xs modify column nl int;
删除表 kc。
drop table kc;
将表 xs 重命名为 Students。
rename table xs to Students;
复杂语句
第1题
问题描述： 在数据库 xsgl 中，创建表格 xs（学生基本情况表），包含以下字段定义。
列名
含义
数据类型
长度
能否取空值
备注
xh
学号
int
no
主码
xm
姓名
char
8
yes
xb
性别
char
2
yes
nl
年龄
tinyint
yes
zy
专业
char
16
yes
jtzz
家庭住址
char
50
yes
SQL语句：
1
2
3
4
5
6
7
8
9
use xsgl ;
create table xs
( xh int not null primary key ,
xm char ( 8 ),
xb char ( 2 ),
nl tinyint ,
zy char ( 16 ),
jtzz char ( 50 )
);
第2题
问题描述： 向表格 xs 中添加如下学生记录。
数据记录表：
xh（学号）
xm（姓名）
xb（性别）
nl（年龄）
zy（专业）
jtzz（家庭住址）
2009412
庄小燕
女
24
计算机
上海市中山北路12号
2009415
洪波
男
25
计算机
青岛市解放路105号
2109102
肖辉
男
23
计算机
杭州市凤起路111号
2109103
柳嫣红
女
22
计算机
上海市邯郸路1066号
2307121
张正正
男
20
应用数学
上海市延安路123号
2307122
李丽
女
21
应用数学
杭州市解放路56号
SQL语句：
1
2
3
4
5
6
7
INSERT INTO xs VALUES
( 2009412 , '庄小燕' , '女' , 24 , '计算机' , '上海市中山北路12号' ),
( 2009415 , '洪波' , '男' , 25 , '计算机' , '青岛市解放路105号' ),
( 2109102 , '肖辉' , '男' , 23 , '计算机' , '杭州市凤起路111号' ),
( 2109103 , '柳嫣红' , '女' , 22 , '计算机' , '上海市邯郸路1066号' ),
( 2307121 , '张正正' , '男' , 20 , '应用数学' , '上海市延安路123号' ),
( 2307122 , '李丽' , '女' , 21 , '应用数学' , '杭州市解放路56号' );
第3题
问题描述： 在数据库 xsgl 中，创建表格 kc（课程情况表），包含以下字段定义。
列名
含义
数据类型
长度
能否取空值
备注
kch
课程号
char
4
no
主码
kcm
课程名
char
20
yes
xss
学时数
int
yes
xf
学分
int
yes
SQL语句：
1
2
3
4
5
6
create table kc
( kch char ( 4 ) primary key ,
kcm char ( 20 ),
xss int ,
xf int
);
实验二
简单语句
问题
SQL语句
建立实验用数据库，名为xscjglxt。
CREATE DATABASE IF NOT EXISTS xscjglxt;
使用xscjglxt数据库。
USE xscjglxt;
运行指定路径下的SQL脚本文件。
SOURCE e:\lihui\xscjglxt.sql
在数据库xscjglxt中建立一个名为History_Student的表，其结构与表xs完全一样。
create table History_Student like xs;
在表History_Student中插入一条记录，学号为210604101，姓名为黄艳春，性别为女，出生日期为2003-02-14，入学年份为2021，所学专业为计算机科学与技术。
insert into History_Student values('210604101','黄艳春','女','2003-02-14',2021,'计算机科学与技术');
将表xs中的所有记录插入到表History_Student中去。
insert into History_Student select * from xs;
将表xs中学号为"210604223"的学生的所学专业改为应用数学。
update xs set sxzy='应用数学' where xh='210604223';
将表xs中所有学生的入学年份增加1年。
update xs set rxnf=rxnf+1;
删除表xk中学号为"210604223"的学生选修的课号为"0690252"的记录。
delete from xk where xh='210604223' and kch='0690252';
删除所有学生的选课记录。
delete from xk;
实验三
简单语句
问题
SQL语句
依次执行以下语句，建立实验用数据库，名为xscjglxt。
CREATE DATABASE IF NOT EXISTS xscjglxt;USE xscjglxt;SOURCE e:\lihui\xscjglxt.sql
查询2020年入学的学生的学号和姓名，要求查询结果按学号的升序排列。
select xh,xsxm from xs where rxnf=2020 order by xh;
查询学时数为48或者64的课程的课程名。
select kcm from kc where xss=48 or xss=64;
查询所有课程名称中包含“数据”的课程的信息。
select * from kc where kcm like '%数据%';
查询学号为“210604223”的学生所选课程的课程编号和所得的成绩。
select kch,cj from xk where xh='210604223';
查询选修了课程的学生学号。
select distinct xh from xk;
查询选修了课程号为“0626050”的课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。
select xh,cj from xk where kch='0626050' order by cj desc,xh;
查询选修了课程号为“0626050”的课程且成绩在80~90之间的学生学号和成绩，并将成绩乘以0.8之后输出。
select xh,cj*0.8 from xk where kch='0626050' and cj between 80 and 90;
查询海洋食品工程专业或计算机科学与技术专业的姓张的学生的信息。
select * from xs where xsxm like '张%' and sxzy in ('海洋食品工程','计算机科学与技术');
查询缺少了成绩的学生的学号和课程号。
select xh,kch from xk where cj is null;
实验四
简单语句
问题
SQL语句
建立实验用数据库，名为xscjglxt。
CREATE DATABASE IF NOT EXISTS xscjglxt;
使用xscjglxt数据库。
USE xscjglxt;
运行指定路径下的SQL脚本文件。
SOURCE e:\lihui\xscjglxt.sql
删除xuesheng表中sfzh字段上的唯一约束。
alter table xuesheng drop index sfzh;
删除xuesheng表中nl字段上的默认值。
alter table xuesheng modify nl tinyint not null;
设置xuesheng表中sfzh字段不能取空值。
alter table xuesheng modify sfzh char(18) not null;
删除xuesheng表中zybh字段上的外键约束。
alter table xuesheng drop foreign key xuesheng_ibfk_1;
在xuesheng表中的zybh字段上，添加引用自zhuanye表中的zybh字段的名为“fk_zybh”的外键约束，并要求级联修改。
alter table xuesheng add constraint fk_zybh foreign key (zybh) references zhuanye (zybh) on update cascade;
复杂语句
第1题
问题描述： 在数据库 xscjglxt 中，创建表格 zhuanye（专业基本情况表），包含以下字段定义。
列名
含义
数据类型
长度
空值
约束
备注
zybh
专业编号
char
4
no
主键约束
zymc
专业名称
varchar
20
no
zyjs
专业介绍
varchar
200
yes
SQL语句：
1
2
3
4
5
create table zhuanye
( zybh char ( 4 ) primary key ,
zymc varchar ( 20 ) not null ,
zyjs varchar ( 200 )
);
第2题
问题描述： 在数据库 xscjglxt 中，创建表格 xuesheng（学生基本情况表），包含以下字段定义。
列名
含义
数据类型
长度
空值
约束
备注
xh
学号
int
no
主键约束
自动增长
xm
姓名
char
6
no
sfzh
身份证号
char
18
yes
唯一约束
xb
性别
char
1
yes
nl
年龄
tinyint
no
默认值为20
zybh
专业编号
char
4
yes
外键约束
引用自zhuanye表
jtzz
家庭住址
varchar
50
yes
SQL语句：
1
2
3
4
5
6
7
8
9
10
create table xuesheng
( xh int primary key auto_increment ,
xm char ( 6 ) not null ,
sfzh char ( 18 ) unique ,
xb char ( 1 ),
nl tinyint not null default 20 ,
zybh char ( 4 ),
jtzz varchar ( 50 ),
foreign key ( zybh ) references zhuanye ( zybh )
);
实验五
简单语句
问题
SQL语句
建立实验用数据库，名为xscjglxt。
CREATE DATABASE IF NOT EXISTS xscjglxt;
使用xscjglxt数据库。
USE xscjglxt;
运行指定路径下的SQL脚本文件。
SOURCE e:\lihui\xscjglxt.sql
查询所有学生的学号、姓名及其所选修的课程名和成绩。
select xs.xh,xsxm,kcm,cj from xs left join xk on xs.xh=xk.xh left join kc on xk.kch=kc.kch;
查询选修了“高等数学1”且成绩在75以上的学生学号、姓名和成绩。
select xs.xh,xsxm,cj from xs join xk on xs.xh=xk.xh join kc on xk.kch=kc.kch where kcm='高等数学1' and cj>75;
统计xs表中轮机与驾驶专业的学生人数。
select count(*) from xs where sxzy='轮机与驾驶';
统计xk表中“0690051”号课程的平均分。
select avg(cj) from xk where kch='0690051';
统计选修了课程的学生人数。
select count(distinct xh) from xk;
分组统计xk表中各门课程的平均分和选修人数。
select kch 课程号,avg(cj) 平均分,count(*) 选修人数 from xk group by kch;
查询xk表中课程最高分和最低分之差大于12分的课程号。
select kch from xk group by kch having max(cj)-min(cj)>12;
查询xk表中多于2名学生选修的并且课程号以“06”开头的课程的平均分。
select kch,avg(cj) from xk where kch like '06%' group by kch having count(*)>2;
查询选修超过1门课的学生学号。
select xh from xk group by xh having count(*)>1;
查询没有选修“0690051”号课程的学生姓名。
select xsxm from xs where xh not in (select xh from xk where kch='0690051');
查询xs表中与学号为“200604222”的学生同年出生的学生的姓名和出生日期。
select xsxm,xscsrq from xs where year(xscsrq)=(select year(xscsrq) from xs where xh='200604222');
查询课程号为“0690051”的课程成绩高于宁兵兵的学生学号和成绩。
select xh,cj from xk where kch='0690051' and cj>(select cj from xk where kch='0690051' and xh=(select xh from xs where xsxm='宁兵兵'));
查询其他专业中比计算机科学与技术专业的学生年龄都小的学生。
select * from xs where sxzy!='计算机科学与技术' and year(now())-year(xscsrq) < all (select year(now())-year(xscsrq) from xs where sxzy='计算机科学与技术');
查询至少选修了学号为“190604132”的学生所选修全部课程的学生学号和姓名。
select xh,xsxm from xs where not exists (select * from xk xk1 where xh='190604132' and not exists (select * from xk xk2 where xk2.xh=xs.xh and xk2.kch=xk1.kch));
实验六
简单语句
问题
SQL语句
建立实验用数据库，名为xscjglxt。
CREATE DATABASE IF NOT EXISTS xscjglxt;
使用xscjglxt数据库。
USE xscjglxt;
运行指定路径下的SQL脚本文件。
SOURCE e:\lihui\xscjglxt.sql
为表kc按xfs（学分）降序建立名为xfs_index的索引。
create index xfs_index on kc(xfs desc);
为表kc按kcm（课程名）升序建立名为kcm_index的唯一索引。
alter table kc add unique index kcm_index (kcm);
为表kc按xfs（学分）和xss（学时数）建立名为xfs_xss_index的复合索引。
alter table kc add index xfs_xss_index (xfs,xss);
为表xs按nl（年龄）升序建立名为nl_index的索引。
create index nl_index on xs (xscsrq desc);
删除第5题中建立的索引。
drop index nl_index on xs;
建立计算机科学与技术专业学生的视图C_Student，并要求进行修改和插入操作时仍需保证该视图只有计算机科学与技术专业的学生，视图的属性名为xh，xsxm，xsxb，xscsrq，sxzy。
create view C_Student as select xh,xsxm,xsxb,xscsrq,sxzy from xs where sxzy='计算机科学与技术' with check option;
建立学生的学号(xh)、姓名(xsxm)、选修课程名(kcm)及成绩(cj)的视图xscjd。本视图由三个基本表xs、xk、kc的连接操作导出。
create view xscjd as select xs.xh,xsxm,kcm,cj from xs join xk on xs.xh=xk.xh join kc on xk.kch=kc.kch;
定义一个反映学生出生年份的视图Student_birth，其中有xh(学号)、xsxm(学生姓名)、xscsnf(学生出生年份)三个字段。
create view Student_birth(xh,xsxm,xscsnf) as select xh,xsxm,year(xscsrq) from xs;
删除视图Student_birth。
drop view Student_birth;
在计算机科学与技术专业的学生视图C_Student中找出年龄小于20岁的学生姓名(xsxm)和出生日期(xscsrq)。
select xsxm,xscsrq from C_Student where year(now())-year(xscsrq)<20;
在xscjd视图中查询成绩在85分以上的学生学号(xh)、姓名(xsxm)和课程名称(kcm)。
select xh,xsxm,kcm from xscjd where cj>85;
将计算机科学与技术专业的学生视图C_Student中学号为“190604132”的学生姓名改为“黄海”。
update C_Student set xsxm='黄海' where xh='190604132';
向计算机科学与技术专业的学生视图C_Student中插入一个新的学生记录，其中学号为“190604114”，姓名为“王海”，性别为“男”。
insert into C_Student(xh,xsxm,xsxb,sxzy) values('190604114','王海','男','计算机科学与技术');
删除计算机科学与技术专业的学生视图C_Student中学号为“210604126”的记录。
delete from C_Student where xh='210604126';
实验七
简单语句
问题
SQL语句
建立实验用数据库，名为xscjglxt。
CREATE DATABASE IF NOT EXISTS xscjglxt;
使用xscjglxt数据库。
USE xscjglxt;
运行指定路径下的SQL脚本文件。
SOURCE e:\lihui\xscjglxt.sql
创建一个名为test_proc1的存储过程，其功能是从xs、kc和xk表中查询出所有选修过课程的学生的学号、姓名以及其所选课程的课程名和成绩。
delimiter $$create procedure test_proc1()begin select xs.xh,xsxm,kcm,cj from xs join xk on xs.xh=xk.xh join kc on xk.kch=kc.kch;end $$delimiter ;
创建一个名为test_proc2的存储过程，其功能是根据以输入参数形式给定的学生的所学专业从xs、kc和xk表中查询出这些学生的学号、姓名以及其所选课程的课程名和成绩。
delimiter $$create procedure test_proc2(in zymc varchar(40))begin select xs.xh,xsxm,kcm,cj from xs left join xk on xs.xh=xk.xh left join kc on xk.kch=kc.kch where sxzy=zymc;end $$delimiter ;
创建一个名为test_proc3的存储过程，其功能是根据以输入参数形式给定的学生的学号统计出该学生所选课程的平均成绩，并由输出参数带回其平均成绩。
delimiter $$create procedure test_proc3(in no char(9),out pjf decimal(4,1))begin select avg(cj) pjcj from xk where xh=no; select avg(cj) into pjf from xk where xh=no;end $$delimiter ;
调用test_proc1存储过程，验证其功能。
call test_proc1;
调用test_proc2存储过程，验证其功能。
call test_proc2('计算机科学与技术');
调用test_proc3存储过程，验证其功能。
call test_proc3('200604219',@pjcj);
查看test_proc3存储过程输出参数返回的平均成绩。
select @pjcj;
在js表上创建名为tr_js_ins的触发器，它的作用是在js表中插入新的数据后，统计教师总人数，并将结果赋给变量@jsrs。
delimiter $$create trigger tr_js_ins after inserton js for each rowbegin select count(*) into @jsrs from js;end $$delimiter ;
在kc表上创建名为tr_kc_del的触发器，它的作用在kc表中删除数据后，级联删除xk表中对应的选课记录。
delimiter $$create trigger tr_kc_del after deleteon kc for each rowbegin delete from xk where kch=old.kch;end $$delimiter ;
查询js表中的教师总人数。
select count(*) from js;
向js表中插入一条教师记录，验证tr_js_ins触发器。
insert into js(jsbh,jsxm) values('010055','梅超风');
查看触发器tr_js_ins赋给变量@jsrs的教师总人数。
select @jsrs;
查询xk表中的所有选课记录。
select * from xk;
删除kc表中课程号为“0690051”的课程，验证tr_kc_del触发器。
delete from kc where kch='0690051';
查询xk表中课程号为“0690051”的选课记录，验证是否被级联删除。
select * from xk where kch='0690051';
实验八
简单语句
问题
SQL语句
建立实验用数据库，名为xscjglxt。
CREATE DATABASE IF NOT EXISTS xscjglxt;
使用xscjglxt数据库。
USE xscjglxt;
运行指定路径下的SQL脚本文件。
SOURCE e:\lihui\xscjglxt.sql
创建一个名为“user_a”的登录账户，其登录密码是“12345678”。
create user user_a identified by '12345678';
在数据库xscjglxt中，将kc表的SELECT权限授予给用户“user_a”。
grant select on xscjglxt.kc to user_a;
运用Windows的命令提示符窗口，使用用户“user_a”的身份登录MySQL服务器。
mysql -uuser_a -p12345678
在用户“user_a”登录的会话窗口中，查询kc表中的所有信息。
select * from xscjglxt.kc;
在用户“user_a”登录的会话窗口中，删除kc表中的所有数据，验证该操作是否能够成功执行。
delete from xscjglxt.kc;
在用户“user_a”登录的会话窗口中，查询xs表中的所有信息，验证该操作是否能够成功执行。
select * from xscjglxt.xs;
思考第8题和第9题为什么没有成功执行。
用户user_a只被授予了xscjglxt.kc表的SELECT权限，没有DELETE权限，也没有xscjglxt.xs表的SELECT权限。
将数据库xscjglxt备份，生成一个备份文件“E:\mydata\xscjbak.sql”。
mysqldump -uroot -pAdmin xscjglxt > e:\mydata\xscjbak.sql
删除数据库xscjglxt。
drop database xscjglxt;
重新创建数据库xscjglxt。
create database xscjglxt;
使用xscjglxt数据库。
use xscjglxt;
恢复数据库xscjglxt。
source e:\mydata\xscjbak.sql
大总结
序号
语句类型
代表语句 / 关键字
作用
考试记忆重点
1
数据库操作语句
create database、use、show databases、drop database创建、选择、查看、删除数据库
建库、选库、查库、删库
2
表结构操作语句
create table、alter table、drop table、rename table创建表、修改表结构、删除表、重命名表
属于 ddl 数据定义语句
3
复制表结构语句
create table 表名 like 原表名创建一个结构和原表相同的新表
只复制表结构，不复制数据
4
插入数据语句
insert into ... values向表中插入一条或多条记录
注意字段顺序和数据类型
5
插入查询结果语句
insert into ... select ...把查询结果插入到另一个表中
常用于表之间复制数据
6
修改数据语句
update ... set ... where ...修改表中已有数据
不加 where 会修改所有记录
7
删除数据语句
delete from ... where ...删除表中指定记录
不加 where 会删除表中所有记录
8
基本查询语句
select ... from ... where ...从表中查询数据
数据库考试最核心语句
9
条件查询语句
and、or、like、in、between、is null按条件筛选查询结果
like '%数据%' 表示包含“数据”
10
排序与去重语句
order by、desc、distinct对查询结果排序或去重
desc 降序，默认升序
11
多表连接查询语句
join ... on、left join ... on查询多个表之间相关联的数据
学生表、选课表、课程表三表连接是重点
12
聚合统计语句
count()、avg()、max()、min()统计人数、平均分、最高分、最低分
常和 group by 一起使用
13
分组查询语句
group by、having按字段分组统计，并筛选分组结果
where 是分组前筛选，having 是分组后筛选
14
子查询语句
in、not in、all、exists、not exists在一个查询中嵌套另一个查询
复杂查询常考，尤其是“没有选修”“至少选修”
15
索引语句
create index、drop index、alter table ... add index创建或删除索引
索引用来提高查询效率
16
视图语句
create view、drop view、with check option创建、删除视图
视图是由查询结果形成的虚表
17
存储过程语句
create procedure、call、in、out创建和调用存储过程
注意 delimiter、参数输入输出
18
触发器语句
create trigger、after insert、after delete、old.列名在插入、删除等操作后自动执行语句
old.列名 表示删除或修改前的旧值
19
用户管理语句
create user ... identified by ...创建数据库用户
属于权限管理相关内容
20
授权语句
grant select on 数据库.表 to 用户给用户授予指定权限
只授权了什么权限，就只能做什么操作
21
外键约束语句
foreign key、references、on update cascade建立表与表之间的引用关系
外键用于保证数据完整性
22
唯一约束与默认值语句
unique、default、not null限制字段取值规则
常在建表或改表时出现
23
mysql 脚本执行命令
source 路径执行指定 sql 脚本文件
mysql 命令，不是标准 sql
24
mysql 登录命令
mysql -u用户名 -p密码使用指定用户登录 mysql
在 windows 命令提示符中执行
25
数据库备份命令
mysqldump -u用户 -p密码 数据库名 > 文件.sql备份数据库
命令行工具，不是普通 sql
房子 收录于 笔记