数据库头歌实训（MYSQL）：头歌数据库symql实训作业相关知识点_头歌复合条件连接查询

数据库语言大小写不敏感

头歌上面，不用随便加注释，一加就会报错

一、初识MySQL

第1关、创建数据库

连接数据库

mysql -u用户名 -p密码 （-h127.0.0.1）

在头歌上要加上“-h127.0.0.1”

创建数据库

create database name;

查看已存在的数据库

show databases；

二、数据库和表的基本操作（一）

前提（所有操作都需要先打开相应的数据库）

打开数据库：

use 库名；

第1关、查看表结构与修改表名

查看数据库下数据表的列表：

show table;

查看数据表的基本结构：

语句：describe 表名；

表中各个字段的含义为：

• NULL：表示该列是否能存储 NULL 值；
• Key：表示该列是否已编制索引；
• PRI：表示该列是此表主键的一部分；
• UNI：表示该列是 UNIQUE 索引的一部分；
• MUL：表示在列中某个给定值允许出现多次；
• Default：表示该列是否有默认值，如果有的话值是多少；
• Extra：表示可以获取的与给定列有关的附加信息。

查看表详细结构：

语句：show create table 表名(\g)；

使用 SHOW CREATE TABLE 语句，不仅仅可以返回给我们建表时所写的详细语句，而且还可以查看存储引擎和字符编码。

如果觉得显示的结果排版有点乱，可以在后面加入 \G。不需要空格。

修改表名：

语句：alter table 旧表名 rename 新表名;

第2关、修改字段名与字段数据类型

修改字段名（列名）：

语句：alter table 表名 change 旧字段名 新字段名 新数据类型;

如果不需要修改字段的数据类型，可以把新字段的数据类型设置为和原来一样，但是！千万不要空着它！

修改字段数据类型

语句：alter table 表名 modify 字段名 数据类型;

第3关、添加与删除字段

添加字段：

语句：alter table 表名 add 新字段名 数据类型 [约束条件] [first|after] 已存在字段名;

在表的最后一列添加字段（默认）：

alter table 表名 add 新字段名 数据类型;

在表的第一列添加字段：

alter table 表名 add 新字段名 数据类型 first;

在表的指定列后添加字段：

alter table 表名 add 新字段名 数据类型 after 指定列名;

以下是在 MySQL 中常用的约束：

NOT NULL 约束：确保某列不能有 NULL 值。

DEFAULT 约束：当某列没有指定值时，为该列提供默认值。

UNIQUE 约束：确保某列中的所有值是不同的。

PRIMARY Key 约束：唯一标识数据库表中的各行/记录。

CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

删除字段：

语句：alter table 表名 drop 字段名;

第4关、修改字段的排列位置

修改字段的排列位置：

语句：alter table 表名 modify 字段1 数据类型 first|after 字段2;

修改字段为表的第一个字段：

alter table 表名 modify 字段1 数据类型 first;

修改字段到表的指定列之后：

alter table 表名 modify 字段1 数据类型 after 字段2;

第5关、删除表的外键约束

删除表的外键约束：

语句：alter table 表名 drop foreign key 外键约束名;

三、单表查询（三）

第1关、对查询结果进行排序

对查询结果进行排序：

语句：select 字段名1 from 表名 [where | like] order by 字段名2 [asc | desc];

字段名1：所要展示的字段，全部展示用“*”

字段名2：所要进行排序的字段

asc：升序

desc：降序

比较时，没有||、&&，有and、or

LIKE 通常与 % 一同使用，类似于一个元字符的搜索。(LIKE '%COM' 表示：获取 runoob_author 字段中以 COM 为结尾的的所有记录)

你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

第2关、分组查询

分组查询（查询的是每个分组中 首次出现的一条记录。）不常用

语句：select 字段名1 from 表名 group by 字段名2;

字段名1：所要展示的字段，全部展示用“*”

字段名2：所要分组查询的字段

第3关、使用 LIMIT 限制查询结果的数量

使用limit限制查询结果的数量：

语句：select 字段名1 from 表名 limit [offset ,] 记录数;

offset：可选参数，表示偏移量。如果不指定，默认值为0，表示从查询结果的第一条记录开始，若偏移量为1，则从查询结果中的第二条记录开始，以此类推。

记录数：表示返回查询结果的条数。

即可以没有offset但不可以没有记录数，两者均有时用“，”隔开

四、连接查询

第1关、内连接查询

内连接查询：

仅将两个表中满足连接条件的行组合起来作为结果集，称为内连接；(即不满足条件的不显示)

语句：表1 [inner] join 表2 on 表1.字段=表2.字段;

从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同，最终才会保留结果，否则不保留。inner 关键字可省略不写；on 表示连接条件：条件字段就是代表相同的业务含义，大多数情况下为两张表中的主外键关系

给表或列另起别名：

eg：select tb_student.name as studentName,tb_class.name as className from tb_student join tb_class on tb_student.class_id=tb_class.id;

当满足条件时，输出的不是列名name，而是studentName，另一个同上。

第2关、外连接查询

外连接查询：

语句：表1 left/right [outer] join 表2 on 表1.字段=表2.字段;

左外连接：（left）

在内连接的基础上，还包含表1中所有不符合条件的数据行，并在其中的表2列填写 NULL；

右外连接：（right）

在内连接的基础上，还包含表2中所有不符合条件的数据行，并在其中的表1列填写 NULL；

第3关、复合条件连接查询

总结：

所有查询语句顺序为：

select 所要查询的字段（用“，”隔开，可以用as更换别名） from 表1 [left | right] join 表2 on 连接条件 [where | like] order by [asc | desc]

eg:select tb_student.name as studentName,tb_student.score,tb_class.name as className from tb_student left join tb_class on tb_student.class_id=tb_class.id where tb_student.score>90 order by tb_student.score desc;

五、子查询

第1关、带比较运算符的子查询

求平均值

语句：select avg (列名) from 表

带有比较运算符的子查询：

（该语句只用于子查询返回一行一列，如下只返回一个平均值）

语句：select 所要查询的字段 from 表1 where 字段1 [= | > | < | >= | <= | != | <>] (子查询);

子查询为： select 所要查询的字段 from 表 [where | like]；

比较运算符<>表示：不等于

eg:查询大于所有平均年龄的员工姓名与年龄

select name,age from tb_emp where tb_emp.age>(select avg(age) from tb_emp);

第2关、关键字子查询

关键字子查询：

all 关键字：

all必须跟在一个比较运算符后面，表示与子查询返回的所有值比较都为true则返回ture

any | some 关键字：

any与比较运算符联合使用，表示只要存在与子查询返回的任何一个值比较为ture，则返回ture。some是any的别名，一般用的少

in 关键字：

in不和比较运算符使用，表示指定的一个值是否存在在子查询返回值中，在就返回ture，否则返回false。

eg:

1、查询薪资表中比Java最高工资高的所有员工职位名称和薪资；

select position, salary from tb_salary where salary > all(select salary from tb_salary where position="Java");

2、查询薪资表中比Java最低工资高的所有员工职位名称和薪资；

select position, salary from tb_salary where salary > any(select salary from tb_salary where position="Java");

3、查询薪资表中职位为Java的所有员工职位名称和薪资。

select position, salary from tb_salary where position in(select position from tb_salary where position="Java");

六、复杂查询（二）

相关表如上所示，以下几关均适用。

第1关、查询学生平均分

根据提供的表和数据，查询平均成绩小于60分的同学的学生编号（s_id）、学生姓名（s_name）和平均成绩（avg_score），要求平均成绩保留2位小数点。（注意：包括有成绩的和无成绩的，无成绩的 score = 0.00）

题解：

select student.s_id,student.s_name,round(avg(score.s_score),2) as avg_score from student left join score on student.s_id=score.s_id group by student.s_id having avg(score.s_score)<60

union

select student.s_id,student.s_name,0 as avg_score from student where student.s_id not in (select distinct s_id from score);

分析：常见的关键词不分析，上面都有。

①round

round函数返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果。上例中的round(avg(score.s_score),2)意为对该平均值四舍五入，并保留小数点后两位。

②having

HAVING语句通常与GROUP BY子句及聚集函数COUNT，AVG，SUM，MAX，MIN语句联合使用，用来过滤由group by 语句返回的记录集，对其记录集的作用相当于where。根据上例分析，avg_score是显示时构造出来的字段，并没有哪个表真正含有该字段名，所以直接运用该字段进行比较过滤时系统会报错，因为它找不到该字段。所以，having就可以解决该问题。

③distinct

关键字distinct用于去重，返回唯一不同的值。根据上例select distinct s_id from score可知，返回根据s_id的值去重。

④union

将两个 SQL 语句的结果合并起来。常用语法为 [select ......] union [select ......]。这和join不一样，就是纯粹的联接，重复的也不管。所以，可以和distinct结合使用。

⑤0

上述例子是指将avg_score全部的值都直接置为0

第2关、查询修课相同学生信息

根据提供的表和数据，查询与s_id=01号同学学习的课程完全相同的其他同学的信息（学号s_id，姓名s_name，性别s_sex）。

题解：

select s_id,s_name,s_sex from student where student.s_id in (

select score.s_id from score group by score.s_id having group_concat(c_id)=(select group_concat(c_id) from score where score.s_id="01") and score.s_id <> "01"

);

分析：该题的难点在于怎么让score的多个数据自身比较。

①group_concat()函数：

将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

上例的两个group_concat（）均是对group by后的记录集进行操作

②对两个子查询分析

黄括号：查询“01”号同学的课程，然后和外面的作比较，进行筛选

红括号：显示出和"01"号同学课程一样的同学id。

其他关不写了，先把后面的基础写了，写这章就简单好理解了。

七、使用聚合函数查询

第1关、count()函数

count()函数：

用来统计记录的总条数

语法：select count (*/字段名) from 数据表 [where] ;

第2关、sum()函数

sum()函数：

对数据表的某列进行求和操作

语法：select sum (字段名) from 数据表 [where];

第3关、avg()函数

avg()函数：

对数据表的某列进行求平均值操作

语法：select avg(字段名) from 表 [where];

第4关、max函数

max函数：

求某列的最大数值

语法：select max (字段名) from 数据表 [where];

第5关、min()函数

min()函数：

求某列的最小数值

语法：select min(字段名) from 数据表 [where];

八、其他函数的使用

第1关、字符函数

依此分析：

• concat('aaa','bbb'); ----->显示 aaabbb
• concat_ws('-','aaa','bbb'); ----->显示 aaa-bbb ----->最少需要三个参数
• left('abcdefg',2); ----->显示：ab
• right('abcdefg',3); ------>显示：efg
• ltrim(' abc '); ------>显示：abc ----->去掉字符串前导空格，rtrim\trim作用类似
• trim(leading '?' from '??abc???'); --->显示：abc??? --->上面操作就是将??abc???中的前导?删除，若要删除后导的?，只需将leading替换成trailing，若要将其中的?都删掉，替换成both关键字即可。
• replace('??ab??cde???','?',''); --->显示：abcde --->表示将字符串中的?全部替换成''，但使用时并不表示只能一个字符替换一个字符，可以一对多形式或多对一形式：replace('??ab??cde???','?','!!');
• substring('abcdef',1,2); --->显示：ab --->表示从第一位（注意第一位为1而非0）开始截取字符串的2位字符
• substring_index('aa.bb.cc','.',1); --->显示：aa --->表示将字符串aa.bb.cc通过 . 进行分隔，可从前后取字符串：substring_index('aa.bb.cc','.',-1); --->显示：cc

第2关、数学函数

依此分析：

ceil(3.01); --->显示：4

ceil(3.99); --->显示：3

3/4=0.7500；3 div 4 =0；--->div整除，只取整数位

5%3=2；5 mod 3=2; --->%和mod等价

truncate(3.14169,3); --->显示：3.141，只截取，不发生四舍五入

select 10 between 5 and 20; --->显示：1

select 10 in (5,10,15,20); --->显示：1

select null is null; --->显示：1

update set命令：

用来修改表中的数据

格式：update 表名 set 字段=新值，... where 条件;

第3关、日期时间函数和流程控制类函数

日期时间函数

依次分析：

date_add('2019-07-04', interval 365 day); --->显示：2020-07-03 --->第一个参数为指定时间，第二个参数为增加（减少）的单位值（YEAR、MONTHS、WEEK、DAY）；

datediff('2019-07-04','2020-07-04'); --->显示：-366

data_format('2019-07-04','%m/%d/%y'); --->显示：07/04/2019 --->其他format显示格式查询上图2

流程控制类函数

• IF()函数：IF(a,b,c) 如果a为真返回b，否则返回c；
• IFNULL()函数：IFNULL(a,b) 如果a不为null返回a，否则返回b；
• CASE：CASE WHEN a then b [else c] end 判断如果a为真返回b,否则返回c；

题解：

########## Begin ##########
 
##########  查询学生出生年份及年龄 ##########
select year(s_birth) year,'2019-01-01'-s_birth '年龄' from Student;
//select year(Student.s_birth) as year,ceil(datediff('2019-01-01',Student.s_birth)/366) as "年龄" from Student;
//这个语句不行，为什么
 
##########  查询课程的最高分、最低分、平均分和及格率 #########
select Course.c_id as "课程id",Course.c_name as "课程名",max(s_score) as "最高分",min(s_score) as "最低分",round(avg(s_score),2) as "平均分",round((count(s_score>=60 or null)/count(s_score))*100,2) as "及格率"
from Score,Course
where Score.c_id=Course.c_id  //这已经将两个表连接在一块了
group by Score.c_id;  //group by已经划分好组，select反而是最后实行的，所有的函数都是对每个组里面的数据对于组这个个体来分析。如上，max求的是每个组的最高分，min求的也是每个组的最低分
 
 
########## End ##########

第4关、自定义函数

写题的时候有很多容易遗漏的点，所以从题出发：

白色圈：圈住的两个//，一个都不能少。

红色圈：是returns，不要忘记s

青色圈：每一次给变量名赋值时都得加上set，否则会报错

绿色圈：if语句里面不能当作一句话，正常还得加；

黄色圈：数据库里面的if语句不需要加括号

橙色圈：delimiter和 ; 之间得空一格 （重中之重）

自定义函数

两个必备条件：

• 参数：不一定要有；（任意类型）
• 返回值：一定要有。（任意类型）

创建自定义函数语法：

1. delimiter // #如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略
2. create function 函数名(形参列表) returns 返回类型 #注意是retruns
3. begin
4. 函数体 #函数内定义的变量如：set @x = 1; 变量x为全局变量，在函数外面也可以使用
5. 返回值，即return
6. end
7. //
8. delimiter ;

关于函数体：

1. 由合法的SQL语句构成；
2. 函数体可以是简单的SELECT或INSERT语句；
3. 函数体如果为复合结构则使用begin...end语句；
4. 复合结构可以包含声明，循环，控制结构。

删除自定义函数：

drop function [if exists] 函数名;

自定义函数中定义变量

全局变量

声明变量并初始化：SET @用户变量名=值; 或者 SET @用户变量名:=值;

局部变量

声明：DECLARE 变量名类型; 或者 DECLARE 变量名类型 DEFAULT 值;

赋值：SET 局部变量名=值; 或者 SET 局部变量名:=值;

流程控制

if语句

语法：

if 条件 then 满足条件执行语句；

[else 否则执行else语句；]

[else 否则执行else语句；]

end if;

case语句

也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。在此先不做说明

while语句

[begin_label:] while 条件 do

执行语句；

end while [end_label]

注意：begin_label参数和end_label参数分别表示循环开始和结束的标志，两个标志必须相同，而且都可以省略。

九、分组选择数据

第1关、group by 与 聚合函数

照例分析：

group by与聚合函数的使用：

基本格式：select [聚合函数] 字段名 from 表名 [where查询条件] [group by 字段名]

注意点：group by后面可以跟多个字段名，用逗号隔开；聚合函数是根据分组后对每个组进行的操作；在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

第2关、使用having 与 order by

having子句

作用：having子句用来对分组后的数据进行筛选，即having针对查询结果中的列发挥筛选数据作用。因此having通常与Group by连用。（where 用来过滤数据，将不符合条件的行删去，而having 过滤满足条件并且分好组的组）

基本格式：select [聚合函数] 字段名 from 表名 [where 查询条件] [group by 字段名] [having 字段名筛选条件]

select语句中，各个子句执行次序：

1.where子句从数据源中去除不符合条件的数据；

2.然后group by子句搜集数据行到各个组中；

3.接着统计函数为各个组计算统计值；

4.最后having子句去掉不符合其组搜索条件的各组数据行。

order by子句

作用：将分组后的组按序排列

基本格式：select [聚合函数] 字段名 from 表名 [where 查询条件] [group by 字段名][order by 字段名排序方向]

【拓展】

and 就可以将两个条件联系在一起，不要想其他太复杂的东西

十、视图

第1关、视图

视图的定义：

是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。简单来说，视图是数据表满足条件的数据的一种映射，不满足条件的根本不需要展示。

创建视图：

create [or replace] [algorithm = {umdefined | merge | temptable}] view 视图名 [(column_list)]

as select_statement

[with [cascaded | local] check option]

参数说明：

• or replace：表示替换已有视图；
• algorithm：表示视图选择算法，默认算法是UNDEFINED(未定义的)： MySQL 自动选择要使用的算法 ；merge合并；temptable临时表；
• column_list：可选参数，指定视图中各个属性的名词，默认情况下与select语句中查询的属性相同；
• select_statement：表示select语句；
• [with [cascaded | local] check option]：表示视图在更新时保证在视图的权限范围之内；cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件；local表示更新视图的时候，要满足该视图定义的一个条件即可。

eg1:

eg2:

可以进行操作的视图规范：

用户可以在视图中无条件地使用select语句查询数据。但使用insert（添加）、update（修改）和delete（删除）操作需要在创建视图时满足以下条件（满足以下条件的视图称为可更新视图）：

• from子句中只能引用有1个表（真实表或可更新视图）；
• 不能包含 with、distinct、group by、having、limit等子句；
• 不能使用复合查询，即不能使用union、intersect、except等集合操作；
• select子句的字段列表不能包含聚合、窗口函数、集合返回函数。

删除视图：

语句：drop view 视图名字；

【提示】

创建多表视图时，select语句后面的多个字段一定要表明所来源的表，否则无法输出。

十一、索引

第1关、索引

创建索引：

普通索引

①创建表时创建索引：

create table mytable (

id int not null,

username varchar(16) not null,

index [indexName] (username)

);

②建表后创建索引：

create index 索引名称 on 表名（字段名）；

或者

alter table 表名 add index 索引名称（字段名）；

唯一索引

create unique index 索引名称 on 表名（字段名）；

或者

alter table 表名 add unique (字段名) ；

主键索引

主键索引一般在建表时创建，会设为 int 而且是 AUTO_INCREMENT自增类型的，例如一般表的id字段。

create table mytable (

id int(11) not null auto_increment,

primary key (id)

);

组合索引

组合索引就是在多个字段上创建一个索引。（应用场景：当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度）。

create index 索引名称 on 表名（字段1，字段2，字段3）；

或者

alter table 表名 add index 索引名称（字段1，字段2，字段3）；

删除索引

使用drop删除

drop index index_name on table_name ;

使用alter删除

alter table table_name drop index index_name ;

alter table table_name drop primary key ;#删除主键索引

十二、查询、索引和完整性（*）

第1关、基本查询的学习

这一关有些时候复制通关代码过去都报错。大部分知识点上面都有，在这里只写特殊点。

找出所有姓名是w开头的雇员的部门号：

select did from emp where name like 'w%';

注：%表示任意符号

第2关、深入学习查询语句

函数的调用，上面有，这里就不赘述了。

第3关、视图的创建和使用

有空再补，刚考完试没心情

十三、授权及回收权限

第1关、授予某数据库的所有权限

grant语句

语法：grant 权限名 on 对象 to 用户名 [identified by '密码'] [with grant option]；

权限名：该部分最后图上有

用户名：后面常跟@localhost

[identified by '密码']：给用户添加一个密码

[with grant option]：可以将该用户的权限转移给其他用户

示例：使用 grant 语句创建一个新用户 test1，密码为 test123，该用户对所有的数据又查询、插入的权限，并授予 grant 权限。

grant select,insert on *.* to test1@localhost identified by ‘test123’ with grant option;

revoke语句

使用 revoke 语句收回 test1 用户对 course 数据库中 student 表的 update 权限示例：

revoke update on course.student from test1@localhost;

查看权限

例子： 查用用户 test1 的权限信息

show grants for test1@localhost;

限制权限

max_queries_per_hour count：表示每小时可以查询数据库的次数。 max_connections_per_hour count：表示每小时可以连接数据库的次数 max_updates_per_hour count：表示每小时可以修改数据库的次数。

示例:授予 test1 每小时可以发出的查询数为 50 次，每小时可以连接数据库 10 次，每小时可以发出的更新数为 5 次。

grant all on *.* to test1@localhost identified by ‘test123’

with max_queries_per_hour 50

with max_connections_per_hour 10

with max_updates_per_hour 5;

权限表

第2关、

同上

第3关、

同上

第4关、授予某表某列上的修改权限

eg：给 user2 授予 score 表 grade 字段上的 UPDATE 权限，user2 的 host 为本地主机，即 localhost 。

这篇文章诞生于刚考完期中的我。希望可以为大家提供帮助。加油！