MySQL进阶教程：权限管理、SQL操作与范式实践-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_55928120/article/details/125282811

MySQL

一、SQL语句分类(前两个会使用客户端就行了)

DCL(Data Control Language)：数据控制语言，用来定义访问权限和安全级别。

mysql中的权限无非是针对不同的用户而言，不同的用户的权限提现在以下几点：可否链接mysql服务、可否访问数据库、可否访问某张数据库表、可否对表进行一些操作等。

DDL(Data Definition Language)：数据定义语言，用来定义数据库对象：库、表、列等。功能：创建、删除、修改库和表结构。
1.创建表
create table 表名 (

字段名1（列名）类型(宽度) 约束条件,

字段名2 类型(宽度) 约束条件,

字段名3 类型(宽度) 约束条件,

…

);
2.建表约束
NOT NULL 非空约束
UNIQUE 唯一约束，取值不允许重复,
PRIMARY KEY 主键约束（主关键字），自带非空、唯一、索引
DEFAULT 默认值（缺省值）
FOREIGN KEY 外键约束（外关键字）
对表的修改操作
查看当前数据库中所有表:SHOW TABLES;

3.查看表结构:DESC 表名;

desc authors;
修改表有5个操作，但前缀都是一样的:ALTER TABLE 表名…（了解）

修改表之添加列:ALTER TABLE 表名 add (列名列类型，…，列名列类型);

alter table author add (hobby varchar(20),address varchar(50));
修改表之修改列类型:ALTER TABLE 表名 MODIFY 列名列的新类型;

alter table author modify address varchar(100);
修改表之列名称列类型一起修改:ALTER TABLE 表名 CHANGE 原列名新列名列名类型;

alter table author change address addr varchar(60);
修改表之删除列:ALTER TABLE 表名 DROP 列名;

alter table author drop addr;
修改表之修改表名:ALTER TABLE 表名 RENAME TO 新表名

alter table author rename authors;
删除表：

drop table if exists 表名;

DML(Data Manipulation Language)：数据操作语言，用来定义数据库记录：
增（insert into ）、
删(delete)、
改(update)表记录。
insert into 表名 (列名1,列名2,列名3) values (‘值1’,‘值2’,'值3’);
update 表名 set 列名1=列值1，列名2=列值2 【where 条件】 ;
delete from 表名【where 条件】;
DQL(Data Query Language)：数据查询语言，用来查询记录
select 列1,列2,列n from 表名;（会有练习题）
多表查询

内连接（一下三个等效）
SELECT * from teacher t ,course c where c.t_id = t.id
SELECT * from teacher t join course c on c.t_id = t.id
SELECT * from teacher t inner join course c on c.t_id = t.id

外连接（左外连接，右外连接）
对于左外连接查询的结果会包含左表的所有数据
对于右外连接查询的结果会包含右表的所有数据
全连接就是不做任何删选的笛卡尔积

二、常用函数介绍（知道几个就行了，用到的时候查）
1、数值型函数
数值型函数主要是对数值型数据进行处理，得到我们想要的结果，常用的几个列举如下：
CEILING(x)：返回大于x的最小整数值，向上取整
FLOOR(x)：返回小于x的最大整数值，向下取整
ROUND(x,y)：返回参数x的四舍五入的有y位小数的值四舍五入
TRUNCATE(x,y)：返回数字x截短为y位小数的结果
PI()：返回pi的值（圆周率）
RAND()：返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
2、字符串型函数
LENGTH(s)：计算字符串长度函数，返回字符串的字节长度
CONCAT(s1,s2…,sn)：合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
LOWER(str)：将字符串中的字母转换为小写
UPPER(str)：将字符串中的字母转换为大写
LEFT(str,x)：返回字符串str中最左边的x个字符
RIGHT(str,x)：返回字符串str中最右边的x个字符
TRIM(str)：删除字符串左右两侧的空格
REPLACE：字符串替换函数，返回替换后的新字符串 REPLACE(name,‘白’,‘黑’)
SUBSTRING：截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE(str)：返回颠倒字符串str的结果
3、日期和时间函数
【CURDATE】和 CURRENT_DATE】两个函数作用相同，返回当前系统的【日期值】
【CURTIME 和 CURRENT_TIME】两个函数作用相同，返回当前系统的【时间值】
【NOW】和【SYSDATE】两个函数作用相同，返回当前系统的【日期和时间值】

时间戳或日期转换函数：

【UNIX_TIMESTAMP】获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
【FROM_UNIXTIME】将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数

根据日期获取年月日的数值

【MONTH】获取指定日期中的月份
【MONTHNAME】获取指定日期中的月份英文名称
【DAYNAME】获取指定曰期对应的星期几的英文名称
【DAYOFWEEK】获取指定日期对应的一周的索引位置值
【WEEK】获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
【DAYOFYEAR】获取指定曰期是一年中的第几天，返回值范围是1~366
【DAYOFMONTH】获取指定日期是一个月中是第几天，返回值范围是1~31
【YEAR】获取年份，返回值范围是 1970〜2069

时间日期的计算

【DATE_ADD】和【ADDDATE】两个函数功能相同，都是向日期添加指定的时间间隔
【DATE_SUB】和【 SUBDATE】两个函数功能相同，都是向日期减去指定的时间间隔
【ADDTIME】时间加法运算，在原始时间上添加指定的时间
【SUBTIME】时间减法运算，在原始时间上减去指定的时间
【DATEDIFF】获取两个日期之间间隔，返回参数 1 减去参数 2 的值
【DATE_FORMAT】格式化指定的日期，根据参数返回指定格式的值

三、三范式

第一范式

：要求有主键，并且要求每一个字段原子性不可再分

第二范式

：要求所有非主键字段完全依赖主键，不能产生部分依赖
（在第一范式的基础上）

第三范式

：所有非主键字段和主键字段之间不能产生传递依赖
（在第二范式的基础上）
sql建设简单模型：
sql模型

附录
以下是sql练习题需要的数据：

创建表

drop TABLE if EXISTS student;
CREATE TABLE student (
    id INT(10) PRIMARY key,
    name VARCHAR (10),
    age INT (10) NOT NULL,
    gander varchar(2)
);

drop TABLE if EXISTS course;
CREATE TABLE course (
  id INT (10)  PRIMARY key,
  name VARCHAR (10) ,
  t_id INT (10) 
) ;

drop TABLE if EXISTS teacher;
CREATE TABLE teacher(
  id INT (10)  PRIMARY key,
  name VARCHAR (10) 
);

drop TABLE if EXISTS scores;
CREATE TABLE scores(
  s_id INT ,
  score INT (10),
  c_id INT (10) ,
	PRIMARY key(s_id,c_id)
) ;

添加数据

insert into  student (id,name,age,gander)VALUES(1,'白杰',19,'男'),(2,'连宇栋',19,'男'),(3,'邸志伟',24,'男'),(4,'李兴',11,'男'),(5,'张琪',18,'男'),(6,'武三水',18,'女'),(7,'张志伟',16,'男'),(8,'康永亮',23,'男'),(9,'杨涛瑞',22,'女'),(10,'王杰',21,'男');

insert into  course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);


insert into  teacher (id,name)VALUES(1,'张楠'),(2,'李子豪'),(3,'薇薇姐'),(4,'猴哥'),(5,'八戒');


insert into  scores (s_id,score,c_id)VALUES(1,80,1);
insert into  scores (s_id,score,c_id)VALUES(1,56,2);
insert into  scores (s_id,score,c_id)VALUES(1,95,3);
insert into  scores (s_id,score,c_id)VALUES(1,30,4);
insert into  scores (s_id,score,c_id)VALUES(1,76,5);

insert into  scores (s_id,score,c_id)VALUES(2,35,1);
insert into  scores (s_id,score,c_id)VALUES(2,86,2);
insert into  scores (s_id,score,c_id)VALUES(2,45,3);
insert into  scores (s_id,score,c_id)VALUES(2,94,4);
insert into  scores (s_id,score,c_id)VALUES(2,79,5);

insert into  scores (s_id,score,c_id)VALUES(3,65,2);
insert into  scores (s_id,score,c_id)VALUES(3,85,3);
insert into  scores (s_id,score,c_id)VALUES(3,37,4);
insert into  scores (s_id,score,c_id)VALUES(3,79,5);

insert into  scores (s_id,score,c_id)VALUES(4,66,1);
insert into  scores (s_id,score,c_id)VALUES(4,39,2);
insert into  scores (s_id,score,c_id)VALUES(4,85,3);

insert into  scores (s_id,score,c_id)VALUES(5,66,2);
insert into  scores (s_id,score,c_id)VALUES(5,89,3);
insert into  scores (s_id,score,c_id)VALUES(5,74,4);


insert into  scores (s_id,score,c_id)VALUES(6,80,1);
insert into  scores (s_id,score,c_id)VALUES(6,56,2);
insert into  scores (s_id,score,c_id)VALUES(6,95,3);
insert into  scores (s_id,score,c_id)VALUES(6,30,4);
insert into  scores (s_id,score,c_id)VALUES(6,76,5);

insert into  scores (s_id,score,c_id)VALUES(7,35,1);
insert into  scores (s_id,score,c_id)VALUES(7,86,2);
insert into  scores (s_id,score,c_id)VALUES(7,45,3);
insert into  scores (s_id,score,c_id)VALUES(7,94,4);
insert into  scores (s_id,score,c_id)VALUES(7,79,5);

insert into  scores (s_id,score,c_id)VALUES(8,65,2);
insert into  scores (s_id,score,c_id)VALUES(8,85,3);
insert into  scores (s_id,score,c_id)VALUES(8,37,4);
insert into  scores (s_id,score,c_id)VALUES(8,79,5);

insert into  scores (s_id,score,c_id)VALUES(9,66,1);
insert into  scores (s_id,score,c_id)VALUES(9,39,2);
insert into  scores (s_id,score,c_id)VALUES(9,85,3);
insert into  scores (s_id,score,c_id)VALUES(9,79,5);

insert into  scores (s_id,score,c_id)VALUES(10,66,2);
insert into  scores (s_id,score,c_id)VALUES(10,89,3);
insert into  scores (s_id,score,c_id)VALUES(10,74,4);
insert into  scores (s_id,score,c_id)VALUES(10,79,5);

题目：

查询‘01’号学生的姓名和各科成绩。 难度：两颗星
查询各个学科的平均成绩，最高成绩。 难度：两颗星
查询每个同学的最高成绩及科目名称。 难度：四颗星
查询所有姓张的同学的各科成绩。 难度：两颗星
查询每个课程最高分的同学信息。 难度：五颗星
查询名字中含有“张”和‘李’字的学生信息和各科成绩 。 难度：两颗星
查询平均成绩及格的同学的信息。 难度：三颗星
将学生按照总分数进行排名。 难度：三颗星
查询数学成绩的最高分、最低分、平均分。 难度：两颗星
将各科目按照平均分排序。 难度：两颗星
查询老师的信息和他所带科目的平均分。 难度：三颗星
查询被“张楠”和‘‘李子豪’教的课程的最高分和平均分。 难度：三颗星
查询每个同学的最好成绩的科目名称。 难度：五颗星
查询所有学生的课程及分数。 难度：一颗星
查询课程编号为1且课程成绩在60分以上的学生的学号和姓名。 难度：两颗星
查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。 难度：三颗星
查询有不及格课程的同学信息。 难度：四颗星
求每门课程的学生人数。 难度：两颗星
查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。 难度：两颗星
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。 难度：三颗星
查询有且仅有一门课程成绩在90分以上的学生信息； 难度：三颗星
查询出只有三门课程的全部学生的学号和姓名。难度：三颗星
查询有不及格课程的课程信息 。 难度：三颗星
检索至少选修5门课程的学生学号。难度：三颗星
查询没有学全所有课程的同学的信息 。难度：四颗星
查询学全所有课程的同学的信息。难度：四颗星
查询各学生都选了多少门课。难度：两颗星
查询课程名称为”java”，且分数低于60的学生姓名和分数。 难度：三颗星
查询学过”张楠”老师授课的同学的信息 。 难度：四颗星
查询没学过“张楠”老师授课的同学的信息 。 难度：五颗星

答案：

-- 1.查询‘01’号学生的姓名和各科成绩。 难度：两颗星
SELECT s.id,s.`name`,c.`name` cname,r.score 'score' FROM student s
LEFT JOIN  scores r on  s.id=r.s_id
LEFT JOIN course  c on  r.c_id=c.id
GROUP BY c.`name` HAVING s.id=1


-- 2.查询各个学科的平均成绩，最高成绩。 难度：两颗星
SELECT c.id,MAX(r.score),AVG(r.score), c.`name` cname FROM course c
LEFT JOIN scores r ON r.c_id=c.id
GROUP BY c.id,c.`name`;


-- 3.查询每个同学的最高成绩及科目名称。 难度：四颗星
select t.id,t.name,c.id,c.name,r.score from
(select s.id,s.name,(select max(score) from scores r where r.s_id = s.id) score from student s)  t 
left join scores r on r.s_id = t.id and r.score = t.score
left join course c on r.c_id = c.id;
-- 4.查询所有姓张的同学的各科成绩。 难度：两颗星
SELECT s.name sname ,c.`name` cname ,r.score from student s 
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
where s.`name`like '张%'
-- 5.   不会 查询每个课程最高分的同学信息。 难度：五颗星
select * from student s where id in
(
    select  r.s_id from
        (
        select c.id,c.name,max(score) score from student s
        left join scores r on r.s_id = s.id
        left join course c on c.id = r.c_id 
        group by c.id,c.name
        ) t 
    left join scores r on t.id = r.c_id and t.score = r.score
);
-- 6.查询名字中含有“张”和‘李’字的学生信息和各科成绩 。 难度：两颗星
SELECT s.*  ,c.`name` cname ,r.score from student s 
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
where s.`name`like '张%' or s.`name` LIKE '李%'
-- 7.查询平均成绩及格的同学的信息。 难度：三颗星
SELECT avg(r.score)  score ,s.* from student s
left join scores r on s.id = r.s_id
left join course c on c.id = r.c_id
GROUP BY s.id HAVING score>60
-- 8.将学生按照总分数进行排名。 难度：三颗星
SELECT s.*,sum(r.score)  score from student s
left join scores r on s.id = r.s_id
GROUP BY s.id ,s.name
ORDER BY score desc;

-- 9.查询数学成绩的最高分、最低分、平均分。 难度：两颗星
SELECT MAX(r.score),MIN(r.score),AVG(r.score)
from course  c 
left join scores r on c.id = r.c_id
WHERE c.`name`='数学'

-- 10.将各科目按照平均分排序。 难度：两颗星
SELECT  c.`name`,AVG(r.score) score
from course  c 
left join scores r on c.id = r.c_id
GROUP BY c.`name`  ORDER BY score desc


-- 11.查询老师的信息和他所带科目的平均分。 难度：三颗星
SELECT  t.*,AVG(r.score) score
from teacher  t 
left join course c on c.t_id = t.id
LEFT  join scores r on c.id= r.c_id
GROUP BY t.`name`  

-- 12.查询被“张楠”和‘‘李子豪’教的课程的最高分和平均分。 难度：三颗星
SELECT  MAX(r.score),AVG(r.score) ,t.`name`,c.`name`
from teacher  t 
left join course c on c.t_id = t.id
LEFT  join scores r on c.id= r.c_id
WHERE t.`name`='张楠' or t.`name`='李子豪'
GROUP BY c.`name`

-- 13.  不会  查询每个同学的最好成绩的科目名称。 难度：五颗星
SELECT s.name `sname`, c.name cname from student s 
LEFT JOIN scores r on s.id=r.s_id 
LEFT JOIN course c on r.c_id=c.id 
GROUP BY s.name,c.name HAVING  r.score=(SELECT r.score FROM r ORDER BY r.score LIMIT 1 )
 


select t.id,t.sname,r.c_id,c.id,c.name,t.score  from
(select s.id,s.name sname,max(r.score) score 
from student s
left join scores r on r.s_id = s.id
group by s.id,s.name) t
left join scores r on r.s_id = t.id and r.score = t.score
left join course c on r.c_id = c.id ;
-- 14.查询所有学生的课程及分数。 难度：一颗星 
SELECT s.name 'sname',c.`name` 'cname' ,r.score from student s 
LEFT JOIN scores r on s.id=r.s_id
LEFT JOIN course c on r.c_id=c.id


-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名。 难度：两颗星
SELECT s.id ,s.`name`from student s
LEFT JOIN scores r on s.id=r.s_id
LEFT JOIN course c  on c.id=r.c_id
where c.id=1 and r.score >60

-- 16.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。 难度：三颗星

SELECT s.id ,s.`name`,avg(r.score)from student s
LEFT JOIN scores r on s.id=r.s_id
WHERE r.score>=85

-- 17.查询有不及格课程的同学信息。 难度：四颗星
SELECT s.*,r.score ,c.name  cname from student s
LEFT JOIN scores r on s.id=r.s_id
LEFT JOIN course c on c.id=r.c_id
WHERE r.score<60

-- 18.求每门课程的学生人数。 难度：两颗星
SELECT COUNT(*) '每门课程的学生人数' , c.`name` from student s
LEFT JOIN scores r on s.id=r.s_id
LEFT JOIN course c  on c.id=r.c_id
GROUP BY c.`name`

-- 19.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。 难度：两颗星
SELECT  c.id, c.`name`,AVG(r.score) score from course c
LEFT JOIN scores r  on c.id=r.c_id
GROUP BY c.`name` ORDER BY  score desc,c.id


-- 20.查询平均成绩大于等于70分的同学的学生编号和学生姓名和平均成绩。 难度：三颗星

SELECT s.id ,s.`name`,AVG(r.score)from student s
LEFT JOIN scores r on s.id=r.s_id
LEFT JOIN course c  on c.id=r.c_id
GROUP BY s.id,s.name HAVING AVG(r.score)>=70;

-- 21.查询有且仅有一门课程成绩在90分以上的学生信息； 难度：三颗星
SELECT * from  student s WHERE s.id in
(SELECT r.s_id from scores r WHERE r.score>90
GROUP BY r.s_id HAVING COUNT(*)=1
);



-- 22.查询出只有三门课程的全部学生的学号和姓名。难度：三颗星
SELECT * from  student s WHERE s.id in
(SELECT r.s_id from scores r  
GROUP BY r.s_id HAVING COUNT(*)=3
);

-- 23.查询有不及格课程的课程信息 。 难度：三颗星
SELECT * from course c where c.id in
(SELECT r.c_id  from scores r 
GROUP BY r.c_id HAVING MIN(r.score) < 60 
);

-- 24.检索至少选修5门课程的学生学号。难度：三颗星
SELECT s.id ,s.`name`from student s
LEFT JOIN scores r on s.id=r.s_id
GROUP BY s.id,s.name HAVING COUNT(*)>=5;


-- 25.查询没有学全所有课程的同学的信息 。难度：四颗星
SELECT s.id ,s.`name`from student s
LEFT JOIN scores r on s.id=r.s_id
GROUP BY s.id,s.name HAVING COUNT(*)< (SELECT COUNT(c.id) from course c);

-- 26.查询学全所有课程的同学的信息。难度：四颗星
SELECT s.id ,s.`name`from student s
LEFT JOIN scores r on s.id=r.s_id
GROUP BY s.id,s.name HAVING COUNT(*)= (SELECT COUNT(c.id) from course c);
-- 27.查询各学生都选了多少门课。难度：两颗星
SELECT   s.id ,s.`name`,COUNT(*) '选课数'from student s
LEFT JOIN scores r on s.id=r.s_id
GROUP BY s.id

-- 28.查询课程名称为”java”，且分数低于60的学生姓名和分数。 难度：三颗星
select s.`name`,r.score from student s
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
where c.name='java' and score<60

-- 29.查询学过”张楠”老师授课的同学的信息 。 难度：四颗星
select s.* from student s
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
left JOIN teacher t on t.id=c.t_id
where t.name ='张楠'
-- 30.查询没学过“张楠”老师授课的同学的信息 。 难度：五颗星
select  s.* from student s where id not in(
SELECT s.id from student s 
left join scores r on r.s_id = s.id
left join course c on c.id = r.c_id
left JOIN teacher t on t.id=c.t_id
where t.name ='张楠'
)