-- 实验一
-- ===========================================================================
drop table if exists `student_info`;
drop table if exists `curriculum`;
drop table if exists `grade`;
-- （1）使用SQL语句创建数据库studentsdb。
CREATE DATABASE IF NOT EXISTS `studentsdb`;
-- --------------------------------------------------------------------------
-- （2）使用SQL语句选择studentsdb为当前使用数据库。
USE `studentsdb`;
-- （3）使用SQL语句在studentsdb数据库创建数据表student_info表
CREATE TABLE IF NOT EXISTS `student_info`(
`id` CHAR(4) COMMENT '学号',
`name` CHAR(8) NOT NULL COMMENT '姓名',
`sex` CHAR(2) DEFAULT NULL COMMENT '性别',
`birthday` DATE DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(50) DEFAULT NULL COMMENT '家庭住址',
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET utf8;
-- 查看字符编码
-- show variables like 'char%';
CREATE TABLE IF NOT EXISTS `curriculum`(
`id` CHAR(4) COMMENT '课程编号',
`name` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`credit` INT DEFAULT NULL COMMENT '学分',
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET utf8;
CREATE TABLE IF NOT EXISTS `grade`(
`student_id` CHAR(4) COMMENT '学号',
`curriculum_id` CHAR(4) COMMENT '课程编号',
`score` INT DEFAULT NULL COMMENT '分数',
UNIQUE KEY (`student_id`,`curriculum_id`),
CONSTRAINT `FK_stuID` FOREIGN KEY (`student_id`)REFERENCES student_info(`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `FK_curId` FOREIGN KEY (`curriculum_id`)REFERENCES curriculum(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE = INNODB DEFAULT CHARSET utf8;
-- ------------------------------------------------------------------------------
-- (4) 使用SQL语句INSERT向studentsdb数据库的student_info、curriculum、grade表插入数据
-- delete from `student_info`;
-- select * from `student_info`;
-- update student_info set id='2', name='1', sex='1', birthday='2020-1-1',address = '1' where id = '0005';
INSERT INTO `student_info` VALUES
('0001','张青平','男','2000-10-01','衡阳市东风路77号'),
('0002','刘东阳','男','1998-12-09','东阳市八一北路33号'),
('0003','马晓夏','女','1995-05-12','长岭市五一路763号'),
('0004','钱忠理','男','1994-09-23','滨海市洞庭大道279号'),
('0005','孙海洋','男','1995-04-03','长岛市解放路27号'),
('0006','郭小斌','男','1997-11-10','南山市红旗路113号'),
('0007','肖月玲','女','1996-12-07','东方市南京路11号'),
('0008','张玲珑','女','1997-12-24','滨江市新建路97号');
-- delete from `curriculum`;
-- SELECT * FROM `curriculum`;
INSERT INTO `curriculum` VALUES
('0001','计算机应用基础',2),
('0002','C语言程序设计',2),
('0003','数据库原理及应用',2),
('0004','英语',4),
('0005','高等数学',4),
('0006','数据挖掘',3),
('0007','数据结构与算法',6);
-- DELETE FROM `grade`;
-- SELECT * FROM `grade`;
INSERT INTO `grade` VALUES
('0001','0001',80),
('0001','0002',91),
('0001','0003',88),
('0001','0004',85),
('0001','0005',77),
('0002','0001',73),
('0002','0002',68),
('0002','0003',80),
('0002','0004',79),
('0002','0005',73),
('0003','0001',84),
('0003','0002',92),
('0003','0003',81),
('0003','0004',82),
('0003','0005',75);
-- ------------------------------------------------------------------------------
-- (5) 使用SQL语句ALTER TABLE修改curriculum表的“课程名称”列，使之为空
ALTER TABLE `curriculum` CHANGE `name` `NULL` VARCHAR(50);
-- ------------------------------------------------------------------------------
-- (6) 使用SQL语句ALTER TABLE修改grade表的“分数”列，使其数据类型为decimal(5,2)。
ALTER TABLE `grade` MODIFY `score` DECIMAL(5,2);
-- ------------------------------------------------------------------------------
-- (7)使用SQL语句ALTER TABLE为student_info表添加一个名为“备注”的数据列，
-- 其数据类型为varchar(50)
ALTER TABLE `student_info` ADD `description` VARCHAR(50) AFTER `address`;
ALTER TABLE student_info DROP description
-- ------------------------------------------------------------------------------
-- (8) 使用SQL语句创建数据库studb，并在此数据库下创建表stu，
-- 表结构与数据与studentsdb的student_info表相同。
DROP TABLE `studb`.`stu`
CREATE DATABASE IF NOT EXISTS `studb`;
CREATE TABLE IF NOT EXISTS `studb`.`stu` SELECT * FROM `studentsdb`.`student_info`;
SELECT * FROM `studb`.`stu`;
-- ------------------------------------------------------------------------------
SHOW CREATE TABLE `studb`.`stu`;
USE `studb`;
-- (9) 使用SQL语句删除表stu中学号为0004的记录。
DELETE FROM `studb`.`stu`
WHERE `id` = '0004';
-- ------------------------------------------------------------------------------
-- (10) 使用SQL语句更新表stu中学号为0002的家庭住址为“滨江市新建路96号”.
UPDATE `stu`
SET address = '滨江市新建路96号'
WHERE `id` = '0002';
-- ------------------------------------------------------------------------------
-- (11) 删除表stud的“备注”列。
ALTER TABLE `stu`
DROP `description`;
-- ------------------------------------------------------------------------------
-- (12) 删除表stu。
DROP TABLE `stu`;
-- ------------------------------------------------------------------------------
-- (13) 删除数据库 studb
DROP DATABASE `studb`;
-- 三、实验思考
-- 1. 能通过一个CREATE DATABASE语句创建两个及以上的数据库吗？
-- 答： 不能
-- 2. 删除了的数据库还极可能恢复吗？
-- 答：如果数据库文件有备份的话则能，否则不能
-- 3. 对于studentsdb数据库的student_info表而言，如果输入相同学号的记录将出现什么现象？为什么？
-- 答：会发生主键冲突,因为根据mysqld的建表规则：同一个表中不能有两个或多个主键
-- 实验二
-- ===============================================================================
USE `studentsdb`;
-- 1. 在studentsdb数据库中使用SELECT语句进行基本查询。
-- （1）在student_info表中，查询每个学生的学号、姓名、出生日期信息。
SELECT `id` '学号',`name` '姓名',`birthday` '出生日期'
FROM `student_info` ;
-- （2）查询student_info表学号为 0002的学生的姓名和家庭住址。
SELECT `name` '姓名',`address` '家庭住址'
FROM `student_info`
WHERE `id` = '0002';
-- （3）查询student_info表所有出生日期在95年以后的女同学的姓名和出生日期。
SELECT `name` '姓名',`birthday` '出生日期'
FROM `student_info`
WHERE `birthday`>='1995-01-01';
-- ------------------------------------------------------------------------------
-- 2. 使用select语句进行条件查询。
-- （1）在grade表中查询分数在70-80范围内的学生的学号、课程编号和成绩。
SELECT `student_id` '学号',`curriculum_id` '课程编号',`score` '成绩'
FROM `grade`
WHERE `score` >= 70 && `score` <= 80;
-- （2）在grade表中查询课程编号为0002的学生的平均成绩。
SELECT SUM(`score`) AS `总成绩`
FROM `grade`
WHERE `student_id`='0002';
SELECT SUM(`score`)/COUNT(`score`) AS `平均成绩`
FROM `grade`
WHERE `student_id`='0002';
SELECT AVG(`score`) AS `平均成绩`
FROM `grade`
WHERE `student_id`='0002';
-- （3）在grade表中查询选修课程编号为0003的人数和该课程有成绩的人数。
SELECT COUNT(`student_id`) AS '人数',COUNT(`score`)'有成绩的人数'
FROM `grade`
WHERE `curriculum_id` = '0003' && `score`>0;
-- （4）查询student_info的姓名和出生日期，查询结果按出生日期从大到小排序。
SELECT `name`,`birthday`
FROM `student_info`
ORDER BY `birthday` DESC;
-- （5）查询所有姓名“张”的学生的学号和姓名。
SELECT `id` AS '学号',`name` AS '姓名'
FROM `student_info`
WHERE `name` LIKE '张%';
-- ------------------------------------------------------------------------------
-- 3. 对student_info表，查询学生的学号、姓名、性别、出生日期及家庭住址，
-- 查询结果先按照性别的由小到大排序(升序)，性别相同的再按学号由大到小排序（降序）。
SELECT *
FROM `student_info`
ORDER BY `sex` ASC , `id` DESC;
-- ------------------------------------------------------------------------------
-- 4. 使用GROUP BY子句查询grade表中各个学生的平均成绩
SELECT `student_id`,AVG(`score` ) AS '平均成绩'
FROM `grade`
GROUP BY `student_id`;
-- ------------------------------------------------------------------------------
-- 5. 使用UNION运算符针student_info表中姓“刘”的学生的学号、
-- 姓名与姓“张”的学生的学号、姓名返回在一个表中。
SELECT `id` AS '学号',`name` AS '姓名'
FROM `student_info` WHERE `name` LIKE '刘%'
UNION
SELECT `id` AS '学号',`name` AS '姓名'
FROM `student_info` WHERE `name` LIKE '张%'
-- ------------------------------------------------------------------------------
-- 6. 嵌套查询
-- （1）在student_info表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。
SELECT `name`,`birthday`
FROM `student_info`
WHERE `sex` = (SELECT `sex`
FROM `student_info`
WHERE `name` = '刘东阳');
-- （2）使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。
SELECT `id` '学号',`name` '姓名', `sex` '性别'
FROM `student_info`
WHERE `id` IN (SELECT `student_id`
FROM `grade`
WHERE `curriculum_id`='0002' OR `curriculum_id` = '0005');
-- （3）使用ANY子查询查找学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。
SELECT `student_id` '学号', `curriculum_id` '课程编号',`score` '分数'
FROM `grade`
WHERE `student_id` = '0001' && score > ANY (SELECT score FROM `grade` WHERE `student_id` ='0002');
SELECT * FROM `grade` WHERE `student_id` ='0002'
UPDATE grade SET score = 85 WHERE `student_id` = '0002' && `curriculum_id` = '0005'
-- （4）使用ALL子查询查找学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。
SELECT `curriculum_id` '课程编号',`score` '最高分数'
FROM `grade`
WHERE `student_id` = '0001' && score > ALL (SELECT score FROM `grade` WHERE `student_id` ='0002');
-- ------------------------------------------------------------------------------
-- 7. 连接查询
-- （1）查询分数在80-90范围内的学生的学号、姓名、分数。
SELECT `id` '学号', `curriculum_id` '课程编号', `name` '姓名', score '分数'
FROM `student_info`
JOIN `grade` ON `id` = `student_id`
WHERE score >= 80 && score <= 90;
-- （2）使用INNER JOIN连接方式查询学习“数据库原理及应用”课程的学生学号、姓名、分数。
SELECT stu.id '学号',stu.name '姓名',clazz.id '课程号',clazz.name '课程名称',g.score '成绩'
FROM student_info stu
JOIN grade g ON g.student_id = stu.id
JOIN curriculum clazz ON clazz.id=g.curriculum_id
WHERE clazz.name = '数据库原理及应用';
-- （3）查询每个学生所选课程的最高成绩，要求列出学号、姓名、最高成绩。
SELECT stu.id '学号',stu.name '姓名', MAX(score) '最高分数'
FROM `grade`
JOIN `student_info` stu ON `id` = `student_id`
GROUP BY (`student_id`);
-- （4）使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。
SELECT `id` '学号' , `name` '姓名', SUM(`score`) '总成绩'
FROM `student_info`
LEFT JOIN `grade` ON id = `student_id`
GROUP BY (`id`);
-- （5）为grade表添加数据行：学号为0004、课程编号为0006、分数为76。
INSERT INTO `grade` VALUES ('0004','0006',76);
-- 使用右外连接查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。
SELECT `id` '课程编号', `name` '课程名称', COUNT(`student_id`) '选修人数'
FROM `grade`
RIGHT JOIN `curriculum` ON `curriculum_id` = `id`
GROUP BY (`id`);
-- 三、实验思考
-- 1. 查询所有没有选修课程的学生的学号、姓名。
SELECT `id` '未选修课程的学生',`name` '姓名'
FROM `student_info`
WHERE id NOT IN (SELECT `student_id` FROM `grade`);
-- 2. 查询选修课程的人数。
SELECT `id` '课程编号', `name` '课程名称', COUNT(`student_id`) '选修人数'
FROM `grade`
RIGHT JOIN `curriculum` ON `curriculum_id` = `id`
GROUP BY (`id`);
-- 3. 查询选课人数大于等于3人的课程编号、课程名称、人数。
SELECT `id` '课程编号', `name` '课程名称', COUNT(`student_id`) '选修人数'
FROM `grade`
RIGHT JOIN `curriculum` ON `curriculum_id` = `id`
GROUP BY (`id`)
HAVING COUNT(`id`) >=3;
-- 4. 在查询的FROM子句中实现表与表之间的连接有哪几种方式？对应的关键字分别是什么？
-- 3 种,
-- 交叉连接 cross join
-- 内连接 inner join ... on ...
-- 外连接
-- left outer join ... on ...
-- right outer join ... on ...
浙公网安备 33010602011771号