MySQL实训练习

总和！

#################################################￥

#先创建数据库
CREATE DATABASE jxyy CHARACTER SET 'utf8';
USE jxyy;

#创建第一个学生表
CREATE TABLE `Student table`
(学号 CHAR(6),
姓名 VARCHAR(20),
性别 CHAR(1),
出生日期 datetime,
所在系 VARCHAR(20),
备注 text
);
#课程表
CREATE TABLE Curriculum
(课程号 char(3),
课程名 VARCHAR(20),
先修课程 CHAR(3),
学分 INT,
开课学期 INT
);
#成绩表07
CREATE TABLE Grades
(学号 CHAR(6),
课程号 CHAR(3),
成绩 INT
);

INSERT INTO `Student table`
VALUES(060101,'钟文辉','男','1997-05-01','计算机系','优秀毕业生'),
(060102,'吴细文','女','1997-03-24','计算机系','爱好音乐'),
(060103,'吴朝西','男','1998-07-01','计算机系',NULL)；
(070101,'王冲瑞','男','1998-05-04','机电系','爱好音乐'),
(070102,'林涛涛','女','1997-04-03','机电系','爱好体育'),
(070103,'李修雨','女','1996-03-03','机电系',NULL),
(070301,'李奇','男','1998-09-17','信息管理系',NULL);

INSERT INTO Curriculum
VALUES('C01','高等数学',NULL,4,1),
('C02','程序设计',NULL,4,2),
('C03','数据库结构','C02',3,3),
('C04','数据库原理','C03',3,4),
('C05','音乐欣赏',NULL,1,4),
('C06','大学物理','C01',4,2),
('C07','计算机网络','C02',2,4);

SELECT *
FROM Curriculum;

INSERT INTO Grades
VALUES(060101,'C01',91),
(060101,'C03',88),
(060101,'C04',95),
(060101,'C05',NULL),
(060102,'C02',81),
(060102,'C03',76),
(060102,'C04',92),
(070101,'C01',50),
(070101,'C03',86),
(070101,'C04',90),
(070101,'C05',NULL),
(070103,'C04',52),
(070103,'C06',47),
(070301,'C03',87),
(070301,'C04',93);

SELECT *
FROM Grades;

#1.查询计算机系全体学生的信息。
SELECT *
FROM `Student table`;

#2.查询姓“李”的学生的学号和姓名。
SELECT 学号,姓名
FROM `Student table`
WHERE 姓名 LIKE '%李%';

#3.查询课程表中先修课为空的课程名。
SELECT 课程名
FROM Curriculum
WHERE 先修课程 IS NULL;

#4.查询考试成绩有不及格的学生的学号。
SELECT s.学号,s.姓名,g.成绩
FROM `student table` s RIGHT JOIN Grades g
ON s.学号 = g.学号
WHERE 成绩 < 60;

#5.求选修了C01课程或C02课程的学生的学号及成绩。
SELECT s.学号,s.姓名,g.成绩,g.课程号
FROM `student table` s RIGHT JOIN Grades g
ON s.学号 = g.学号
WHERE 课程号 IN('C01','C02');

#查询全体计算机系学生的姓名及其年龄。
SELECT 姓名,YEAR(CURDATE())-YEAR(出生日期) "年龄"
FROM `Student table`
WHERE 所在系 = '计算机系';

SELECT YEAR(CURDATE()),YEAR(NOW())
FROM DUAL;

#查询计算机系在1996-1997年之间出生的学生的姓名。
SELECT 姓名
FROM `Student table`
WHERE 出生日期 BETWEEN '1996-01-01' AND '1998-01-01'
AND 所在系 ='计算机系';

#查询姓“李”的前两个学生的学号和姓名。
SELECT 学号,姓名
FROM `Student table`
WHERE 姓名 LIKE '%李%'
LIMIT 0,2;

#查询选修了两门以上课程的学生学号与选课门数。
SELECT 学号,COUNT(*)"选课门数"
FROM Grades
GROUP BY 学号
HAVING COUNT(*)>2;

#查询选修课程数大于等于2的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。
SELECT 学号,COUNT(*)"选课门数",AVG(成绩)"平均成绩"
FROM Grades
GROUP BY 学号
HAVING COUNT(*)>=2
ORDER BY 平均成绩 DESC;

#查询选修了【数据库原理】的计算机系的学生学号和姓名。
#1.多表查询方式
SELECT DISTINCT g.学号,s.姓名,c.课程名
FROM Curriculum c JOIN Grades g
ON c.课程号 =g.课程号
JOIN `student table` s ON g.学号= s.学号
WHERE s.所在系= '计算机系' AND c.课程名='数据库原理';
#2.子查询
SELECT 学号,姓名
FROM `student table`
WHERE 学号 in(
               SELECT 学号
               FROM Grades
               WHERE 课程号= (
                              SELECT 课程号
                              FROM Curriculum
                              WHERE 课程名= '数据库原理'
                             )
             ) AND 所在系 = '计算机系';

#查询每一门课的间接先行课（即先行课的先行课）。
SELECT 课程名
FROM Curriculum
WHERE 先修课程 IS NOT NULL;

#查询学生的学号、姓名、选修课程的名称和成绩。
SELECT s.学号,s.姓名,c.课程名,g.成绩
FROM `student table` s JOIN Grades g
ON s.`学号` = g.`学号`
JOIN Curriculum c ON g.`课程号` = c.`课程号`;

#查询选修了课程的学生姓名。
SELECT DISTINCT s.学号,s.姓名
FROM `student table` s JOIN Grades g
ON s.`学号` = g.`学号`
JOIN Curriculum c ON g.`课程号` = c.`课程号`
WHERE g.`成绩` IS NOT NULL;

#查询所有学生的信息和所选修的课程的课程号。
SELECT *
FROM `student table` s JOIN grades g
ON s.`学号` = g.`学号`;

#查询已被选修的课程的情况和所有课程的名字。
SELECT g.*,c.课程名"被选修课程名"
FROM curriculum c LEFT JOIN grades g
ON c.课程号 = g.`课程号`;


#列出学生所有可能的选修情况。
SELECT c1.课程名,c2.课程名,c3.课程名 
FROM curriculum c1 LEFT JOIN curriculum c2
ON c1.先修课程 = c2.`课程号`
LEFT JOIN curriculum c3 ON c2.`先修课程` = c3.课程号;

#查找计算机系的学生选修课程大于2的学生的姓名、平均成绩和选课门数，并按平均成绩降序排列。
SELECT s.姓名,AVG(成绩) "平均成绩",COUNT(*) "选课门数"
FROM `student table` s JOIN grades g
ON s.`学号` = g.`学号`
JOIN curriculum c ON c.`课程号` = g.`课程号`
WHERE `所在系` = '计算机系'
GROUP BY s.学号
HAVING COUNT(*) >2
ORDER BY 平均成绩;

#统计选修了[数据库原理]课程的学生人数。
SELECT COUNT(*) "学生人数"
FROM grades
WHERE 课程号 =(
               SELECT 课程号
               FROM curriculum
               WHERE 课程名 = '数据库原理'
               );
          

#查询没有选修[数据库原理]课程的学生的学号、姓名和所在系。
SELECT 学号,姓名,所在系
FROM `student table`
WHERE  `学号`NOT in (

SELECT `学号`
FROM grades
WHERE 课程号 =(
                SELECT 课程号
                FROM curriculum
                WHERE 课程名 = '数据库原理'
                )
);
#查询其他系中比计算机系学生年龄都小的学生的学号、姓名、所在系和年龄。
SELECT 姓名,学号,所在系,YEAR(NOW())-YEAR(出生日期) "年龄"
FROM `student table`
WHERE 所在系 != '计算机系'
AND 出生日期 > ALL
               (SELECT `出生日期`
                FROM `student table`
                WHERE 所在系 = '计算机系'
               );

#查询被060101学生或060102学生所选修的课程的课程号（用UNION组合查询与IN条件查询两种方法实现）。
SELECT 学号,课程号
FROM grades
WHERE 学号 = 60101
UNION
SELECT 学号,课程号
FROM grades
WHERE 学号 = 60102;

SELECT 学号,课程号
FROM grades
WHERE 学号 in(60101,60102)

#向学生表中插入(050101，赵林，男，1999-09-08，计算机)的记录。
INSERT INTO `student table`
VALUES(050101,'赵林','男','1999-09-08','计算机系',NULL);


#向成绩表中添加一个学生的选课记录，学号为060001，所选的课程号为C02。成绩表中有学号、课程号、成绩这3个列。这里只知道学号和课程号，不知道成绩值。
INSERT INTO grades
VALUES (060001,'C02',NULL);

#将姓名为[赵林]的同学的所在系改为[机电系]，爱好改为[足球]。
UPDATE `student table`
SET `所在系` = '机电系',备注= '爱好：足球'
WHERE 姓名='赵林';


#将选修了课程名为[数据库原理]的学生成绩加5分。
UPDATE grades
SET `成绩` = 成绩+5
WHERE 课程号 =(
SELECT 课程号
FROM curriculum
WHERE 课程名 = '数据库原理'
)

#用CREATE TABLE语句实现把学生表中1998年后（包含1998年）出生的学生的学号、姓名存储到一个新表“学生表_1998”。
DROP TABLE `学生表_1998`;

CREATE TABLE 学生表_1998
SELECT 学号,姓名
FROM `student table`
WHERE YEAR(出生日期) >=1998;

#将学生表中所有姓赵的同学删除。
DELETE FROM `student table`
WHERE 姓名 = '赵%';

#删除计算机系选修成绩不及格的学生的选修记录
DELETE FROM grades
WHERE  学号 in
(SELECT 学号
FROM `student table`
WHERE 所在系 = '计算机系'
AND 成绩 < 60
);
#将“学生表_1998”表中所有行删除。
DELETE FROM 学生表_1998;

#用户权限管理

@@@@

#创建用户
CREATE USER 'test1'@'localhost' IDENTIFIED BY'123456';
#查询
SELECT USER,HOST FROM mysql.`user`;
#权限授予
GRANT ALL ON *.* TO 'test1'@'localhost';

SHOW GRANTS FOR 'test1'@'localhost';

#回收权限
REVOKE SELECT ON *.* FROM 'test1'@'localhost';

#设置更改密码
SET PASSWORD FOR 'test1'@'localhost' = PASSWORD('654321');

SELECT `USER`();

#删除用户
DROP USER'test1'@'localhost';

SELECT USER,HOST FROM mysql.`user`;