SQLnote.md · 大一点的小孩/MySQL

# MySQL笔记

### 数据库基本知识

1. 安装路径不能有中文

2. 启动MYSQL服务

如果提示无法找到本地服务，是因为MySQL服务未启动，开启方式：

win+r 输入services.msc，找到MySQL57服务，右键启动

3. “mysql 不是内部或外部命令” 代表未配置环境变量

把mysql/bin的文件夹所在的路径放到：

计算机/右键属性/高级系统设置/环境变量/Path下;

4. 启动mysql

mysql -u root -p密码

5. mysql命令初步

查看字符集：

show variables like 'char%';
		
    修改字符集：

SET NAMES gbk;

创建数据库：

create database 库名;

切换到当前数据库：

use 库名称;

查看用户下数据库：

show databases;

删除数据库：

drop database 库名称;

创建表：

create table 表名
        (id int primary key,
        username varchar(10) not null,
        PASSWORD varchar(10) not null);

添加数据：

insert into user values
            (123,'张三','root', ...),
            (456,'李四','123456', ...);

注：此处为举例说明，实际操作时，请根据你的数据进行填写

查看当前数据库下的表：

show tables;

查看表中的数据：

SELECT * from 表名;

查看表结构：

desc user; -- DESCRIBE 表名;

修改命令结束符：

delimiter 符号;

# 第三章 MySQL数据库和表

### 3.1 MySQL 数据库

##### 1. 创建数据库
1. 创建数据库

create database 库名 [if not EXISTS]
        [character set 字符集] [collate 校对规则];

_例如_ ：

create database stu1;

-- 命名数据库名称并确定字符集和校对规则
        create database stu2 character set gbk collate gbk_chinese_ci;

切换到当前数据库：

use 库名称;

查看用户下数据库：

show databases;
   	
    修改数据库：

alter database 库名 [character set 字符集] [collate 校对规则];

alter database stu2 character set utf8 collate utf8_general_ci;

删除数据库：

drop database 库名称;

### 3.2 MySQL表

##### 1. 创建数据表
1. 创建数据表

a.创建新表

create table 表名(
        列名 数据型 列级约束条件
        [, 列名 数据类型 列级约束条件]
        ...
        [, 表级约束条件]);

_例如_ ：

create table xs
        (学号 char(6) primary key, 
        姓名 char(8) not null,
        专业名 char(10),
        性别 tinyint not null default 1);

-- 添加数据
        insert into xs values
            ('081101','张三','计算机科学与技术',1),
            ('081102','李梦','计算机科学与技术',0);

b.复制现有的表

[1] 复制表结构

create table 表名1 like 表名;
        desc 表名1;

[2] 复制表数据

create table 表名2 as(SELECT * from 表名);
        SELECT * from 表名2;
        desc 表名2;

##### 2. 修改基本表
2. 修改基本表

alter table 表名 add 列名 数据类型; -- 新增一列
        alter table 表名 add primary key(列名); -- 添加主键
        alter table 表名 add FOREIGN key(列名) REFERENCES 表名(列名); -- 添加外键
        alter table 表名 add CONSTRAINT 约束名 check 语句; -- 添加约束

alter table 表名 change 旧列名 新列名 数据类型; -- 修改列名称
        alter table 表名 modify 列名 新数据类型 [约束条件]; -- 修改列的数据类型

alter table 表名 rename to 新表名; -- 修改表名
        alter table 表名 character set 字符集 [collate 校对规则]; -- 修改表的字符集

alter table 表名 DROP 列名; -- 删除一列
        alter table 表名 DROP PRIMARY key; -- 删除主键
        alter table 表名 DROP foreign key 外键名; -- 删除外键	
        alter table 表名 DROP CONSTRAINT 约束名; -- 删除指定约束

修改表名：

rename table 表名 to 新表名;

### 3.3 MySQL表记录的操作

##### 1. 插入记录
1. 插入记录

a. 插入一条新纪录

insert into 表名 value(值1 [值2 [, 值3 ...]])
        insert into 表名 set 列名=值|表达式。。。

_例如_ ：

insert into 表名 value('值1', '值2', '值3' ...); -- 值的顺序要和表的每一列顺序对应
        insert into 表名 set 列1=值1, 列2=值2 ....;
        insert into 表名 (列1, 列2 ...) value (值1, 值2 ...);

b. 插入已有表的值

insert into 表名 select 语句;

create table 表名 (列名 类型);
        insert into 表名 select 列名2 form 列名1;
        select * form 表名;

c. 替换旧纪录

replace into 表名 values (...);

insert into salary values (值1, 值2 ...);
        replace into salary values (值1, 值2 ...);

d. 插入图片

alter table 表名 add 照片 blob; -- 新增一列
        insert into 表名 values (值1, 值2 ...); -- 带路径

##### 2. 修改表记录
2. 修改表记录

创建表（该表仅在  **本小节**  中使用）

create table xs(
            学号 char(6) primary key,
            姓名 char(8) not null,
            专业名 char(10),
            性别 tinyint(1) not null default 1,
            出生日期 date not null,
            总学分 tinyint(1),
            照片 blob,
            备注 text
        );

a. 修改单个表

UPDATE 表名 set 列名=值|表达式 ...[ where 条件];

插入数据：

insert into xs values
            ('081101', '王林', '计算机', 1, '1994-02-10', 50, null, null), 
            ('081102', '程明', '计算机', 1, '1995-02-01', 50, null, null),
            ('081103', '王燕', '计算机', 0, '1993-02-16', 50, null, null),
            ('081107', '李明', '计算机', 1, '1994-05-03', 50, null, '提前修完一门课');

注：此处为举例说明，实际操作时，请根据你的数据进行填写

_eg1_ ：给所有学生的总学分减少10分，并将李明同学的备注修改为“辅修人工智能”，同时学号置为“081104”；

UPDATE xs set 总学分=总学分-10; -- 所有同学总学分减少10分
        UPDATE xs set 备注="辅修人工智能", 学号="081104" where 姓名="李明"; -- 修改学号和备注

_eg2_ ：将王林同学的年龄增加一岁，并将其备注修改为“辅修软件工程”；

UPDATE xs set 出生日期="1993-2-10", 备注="辅修人工智能" where 姓名="王林";

b. 修改多个表

UPDATE 表名1, 表名2[, ...] set 列名=值|表达式[ where 条件];

create table xs1 as (SELECT * from xs); -- 从xs中复制出xs1

_eg3_ ：将xs表和xs1表中程明的备注改为“辅修物联网”，并将其总学分置为68；

UPDATE xs,xs1 
        set xs.备注="辅修物联网", xs1.备注="辅修物联网", xs.总学分="68", xs1.总学分="68" 
            where xs.姓名="程明" and xs1.姓名="程明";

或：

UPDATE xs,xs1 
        set xs.备注="辅修物联网", xs1.备注="辅修物联网", xs.总学分="68", xs1.总学分="68" 
            where xs.姓名="程明" and xs.姓名=xs1.姓名;

_eg4_ ：将所有表的总学分设置为173；

UPDATE xs,xs1 set xs.总学分=173, xs1.总学分=173;

##### 3. 删除表记录
3. 删除表记录

a. 删除满足条件的行

delete from 表名 where 条件; -- 条件不可省略

-- 在xs表中删除李明的记录
        delete from xs where 姓名="李明";

b. 从多个表中删除记录

delete 表名1, 表名2[, ...] form 表名 where 条件; -- 条件不可省略

注：如果某表在删除队列中，若没有为该表添加对应条件，则会清空该表

_eg5_ ：删除xs表和xs1表中“辅修物联网”的同学的记录；

delete xs,xs1 from xs,xs1 where xs.备注="辅修物联网" and xs1.备注="辅修物联网";

c. 清空表数据

delete from 表名; -- 一行一行删除
        truncate table 表名; -- 相比于dalete占用日志文件最少，最不容易恢复

### 3.4 MySQL数据库信息显示

##### 1. 基本操作
1. 基本操作

显示表：

show tables;

显示数据库：

show databases;

查看索引：

show index from 表名;

查看用户权限：

show grants for 用户名;

查看变量：

show variables;

查看创建表的语句：

show create table 表名;

查看创建存储过程的语句：

show create procedure 存储过程名;

查看表结构：

desc 表名;

查看列结构：

desc 表名 列名;

# 第四章 MySQL查询和视图

> 注：本章例题中使用的数据库为 xscj.sql

### 4.1 选择列

##### 1. 选择指定列
1. 选择指定列

SELECT 列名|* FROM 表名;

SELECT 学号 from xs;
        SELECT all 学号 from xs;

##### 2. 定义列别名
2. 定义列别名

select 列名|表达式 [as] 别名 from 表名;

select 学号 sno from xs;

_eg1_ ：查询学生的学号、姓名、年龄；

SELECT 学号,姓名,year(NOW())-year(出生日期) 年龄 from xs;
        SELECT 学号,姓名,year(CURRENT_DATE())-year(出生日期) 年龄 from xs;

-- SELECT year(NOW()); -- 查询当前时间
        -- SELECT year(CURRENT_DATE());

注：如果列名称有空格，列名称需要用单引号括起来，列别名某些条件下不允许跟在where字句后。

##### 3. 替换查询结果中的值
3. 替换查询结果中的值

case ... where ... then ... end

_eg2_ ：查询计算机专业学生的学号、姓名、总学分，如果总学分小于50，则输出‘不及格’，如果总学分大于等于50小于52，则输出‘及格’，如果总学分大于等于52，则输出‘优秀’，空值输出‘没有选课’，总学分这列改为‘等级’；

select 学号,姓名,
            CASE 
                WHEN 总学分 is null then '未选课' 
                WHEN 总学分<50 then '不及格' 
                WHEN 总学分>50 then '优秀' 
            else '合格' 
        END 等级 
        FROM xs WHERE 专业名='计算机';

_eg3_ ：查询计算机专业学生的学号、课程号、成绩的中文等级；

select xs_kc.学号,课程号,
            CASE 
                WHEN 成绩<60             then '不及格' 
                WHEN 成绩>=60 and 成绩<70 then '及格' 
                WHEN 成绩>=70 and 成绩<80 then '中等' 
                WHEN 成绩>=80 and 成绩<90 then '良好' 
            else '合格' 
        END 等级 
        FROM ...

方法1：FROM xs_kc where 学号 IN(SELECT 学号 from xs WHERE 专业名='计算机');
        方法2：from xs_kc,xs where xs_kc.学号=xs.学号 and 专业名='计算机'；

##### 4. 计算列值
4. 计算列值

_eg4_ ：将学分改为150分制；

SELECT 学号,课程号,成绩*1.5 150分制 from xs_kc;

##### 5. 消除重复记录
5. 消除重复记录

DISTINCT

查询选修课程的学号

SELECT DISTINCT 学号 from xs_kc;

查询被选课程的课程号

SELECT DISTINCT 课程号 from xs_kc;

查询通信工程专业选修的课程数

select  count(DISTINCT 课程号) 总计 from xs_kc 
        where 学号 in (
            select 学号 from xs where 专业名='通信工程'
        );

##### 6. 聚合函数
6. 聚合函数

a. count [all|distinct] *|列名|表达式 from 表名;

_eg1_ ：查询计算机专业年龄大于32岁以上的学生数目；

select count(学号) from xs where 专业名='计算机' and year(now())-year(出生日期)>32;

_eg2_ ：查询“206”课程超过80分以上的选课人数；

select COUNT(DISTINCT 学号) 选课人数 from xs_kc where 课程号='206' and 成绩>80;

b. min、max

_eg3_ ：查询通信工程专业学生的最大年龄和最小年龄；

SELECT max(year(now())-year(出生日期)) 最大年龄,min(year(now())-year(出生日期)) 最小年龄 
            from xs where 专业名='通信工程';

c. sum、avg

_eg4_ ：查询学号为“081101”同学的成绩总和；

select sum(成绩) 成绩总和 from xs_kc where 学号='081101';

_eg5_ ：查询计算机专业的平均总学分

select avg(总学分) from xs where 专业名='计算机';

_eg6_ ：查询计算机专业平均成绩在90分以上的学生的姓名；

select 姓名 from xs WHERE 专业名='计算机' and 学号 in (select 学号 from xs_kc group by 学号 HAVING avg(成绩)>90);

_eg7_ ：查询课程的平均成绩在75分以上的课程名

select 课程名 from kc WHERE 课程号 in (select 课程号 from xs_kc group by 课程号 HAVING avg(成绩)>75);

_eg8_ ：统计平均分大于90的课程数；

select count(DISTINCT 课程号) from xs_kc group by 课程号 HAVING avg(成绩)>90;

d. std、VARIANCE

_eg9_ ：查询成绩学生成绩的方差和标准差；

select std(成绩) 标准差,VARIANCE(成绩) 方差 from xs_kc;

e. group_concat  返回值的集合

_eg10_ ：查询选修“102”课程的学号

SELECT GROUP_CONCAT(学号) from xs_kc where 课程号='102';

f. bit_and、bit_or、bit_xor  与、或、异或

_eg11_ ：创建一张表b，有一列整形的值，分别为1、3、7，对应的写出相应的值的内容；

create table b (id int);
        insert into b values(1),(3),(7);

SELECT BIT_XOR(id) from b; -- 5
        SELECT bin(BIT_XOR(id)) from b; -- 101

### 4.2 from子句

语法：`from 表名|子查询 ... join ...;`

##### 1. 引用单个表
1. 引用单个表

select * from 表名;

如果查询其它数据库下的表，需要在表名前加其所在数据库的名称;

select * from 库名.表名;

##### 2. 多表连接
2. 多表连接

a. 全连接（本质： **笛卡尔积** ）

select * from 表名1,表名2[,表名3 ..];

select * from xs,kc,xs_kc;
        select * from xs,kc,xs_kc where xs.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号;

_eg6_ ：查询计算机专业平均成绩在90分以上的学生的姓名；

select 姓名 from xs,xs_kc where 专业名='计算机' and xs.学号=xs_kc.学号 
            group by xs_kc.学号 having avg(成绩)>90;

b. join 连接

select 列名 from 表名1 join 表2 
            on(条件)|using(列名)[ join 表3 on(条件)|using(列名)[, ..]];

注意：using 和 on 不要一起使用

_eg12_ ：查询选修“102”课程的学生姓名、课程名、成绩

select 姓名,课程名,成绩 from xs join xs_kc on(xs.学号=xs_kc.学号) 
            join kc on(kc.课程号=xs_kc.课程号) where xs_kc.课程号='102';
        select 姓名,课程名,成绩 from xs join xs_kc using(学号) 
            join kc using(课程号) where xs_kc.课程号='102';

[1] 内连接（inner join），通常省略inner

_eg13_ ：自身表连接。查询xscj数据库中学生的课程不同，成绩相同的学生的学号、课程号、成绩；

select a.学号,a.课程号,b.课程号,a.成绩 from xs_kc a,xs_kc b 
            where a.学号=b.学号 and a.成绩=b.成绩 and a.课程号 != b.课程号;

_eg14_ ：查询选修程序设计与语言课程且成绩在80分以上的学生学号、姓名、课程号、成绩；

select 学号,姓名,课程号,成绩 FROM xs join xs_kc using(学号) 
        join kc using(课程号) where 课程名='程序设计与语言' and 成绩>80;

[2].外连接

左外连接：LEFT JOIN  
    右外连接：RIGHT JOIN  
    自然连接：NATURAL JOIN

_eg15_ ：查询xs的所有信息和参与选课的学生成绩；

SELECT xs.*,成绩 from xs left join xs_kc on xs.学号=xs_kc.学号; -- 左外连接
        SELECT xs.*,成绩 from xs_kc right join xs on xs.学号=xs_kc.学号; -- 右外连接

_eg16_ ：查询选课的信息和所有课程名

select xs_kc.*,课程名 from xs_kc right join kc on xs_kc.课程号=kc.课程号;

_eg17_ ：查询学生的信息和选课信息，取消重复列；

select * from xs NATURAL JOIN xs_kc;

c.交叉连接

cross join 用法和 inner join 一致  
        STRAIGHT_JOIN 用法和 inner join 一致，但不可以用using做条件的连接

### 4.3 where子句

##### 1. 比较运算
1. 比较运算（运算符[ > | < | = | != | <> | <=> ]）

_eg1_ ：查询通信工程专业性别为女的成绩大于80的学生姓名；

select distinct 姓名 from xs where 专业名='通信工程' and 性别='0' and 学号 in
          (SELECT 学号 from xs_kc WHERE 成绩>80);
        select distinct 姓名 from xs,xs_kc 
          where xs.学号=xs_kc.学号 and 专业名='通信工程' and 性别='0' and 成绩>80;

_eg2_ ：查询xs表中备注为空的学生信息；

select * from xs where 备注 is null;
        select * from xs where 备注 <=> null; -- '<=>'：相等或都等于

##### 2. 模式匹配
2. 模式匹配：like、regexp

有两个通配符
            _：匹配任意单个字符
            %：匹配零个或多个字符
 
        escape 通配符 -- 将通配符转化为普通字符

regexp

'^' -- 匹配字符开始的部分，
        '$' -- 匹配字符结束的部分，
        '.' -- 匹配任意单个字符，
        '*' -- 匹配*之前的任意零个或多个字符，
        [a, b, c] -- 匹配括号内包含的字符

_eg3_ ：查询姓王的女性同学的信息；

select * from xs where 性别='0' and 姓名 like '王%';
        select * from xs where 性别='0' and 姓名 regexp '^王';

_eg4_ ：查询学号倒数第二位为“1”的学生信息；

select * from xs where 学号 like '%1_';
        select * from xs where 学号 regexp '.*1.$'; -- '.*' 等同 '%'

_eg5_ ：查询学生成绩中含有“9”的学生的学号、课程号、成绩；

select 学号,课程号,成绩 from xs_kc WHERE 成绩 like '%9%';
        select 学号,课程号,成绩 from xs_kc WHERE 成绩 regexp '[9]';

_eg6_ ：查询学号开始和结尾都是“1”的学生信息；

select * from xs WHERE 学号 like '1%1';
        select * from xs WHERE 学号 regexp '^1.*1$';

_eg7_ ：查询课程名开头是“软件_”的课程信息；

select * from kc where 课程名 like '软件#_%' escape '#';

##### 3. 范围比较
3. 范围比较

between .. and|in ..

_eg8_ ：查询计算机专业年龄在32到35的学生选课信息；

SELECT * from xs_kc where 学号 IN
          (select 学号 from xs where 专业名='计算机' AND 
            year(now())-year(出生日期) BETWEEN 32 and 35);

_eg9_ ：查询成绩是90或80的学生选课信息；

select * from xs_kc where 成绩 in (80, 90);
        select * from xs_kc where 成绩=80 or 成绩=90;

##### 4. 空值比较

4. 空值比较

> is null  
    > is not null  
    > <=> null

_eg10_ ：查询备注为空的学生信息；

select * from xs where 备注 is null;
        select * from xs where 备注 <=> null;

##### 5. 子查询
5. 子查询

a. in 子查询

_eg11_ ：查询选修“206”号课程的学生的学号和姓名；

select 学号,姓名 from xs where 学号 IN 
          (select 学号 from xs_kc where 课程号='206');

_eg12_ ：查询通信工程专业学生所选修的课程名；

select 课程名 from kc WHERE 课程号 IN 
          (select 课程号 from xs_kc where 学号 IN 
            (select 学号 from xs where 专业名='通信工程'));

_eg13_ ：查询选修离散数学且成绩在“80”分以上的计算机专业的学生姓名；

select 姓名 from xs WHERE 专业名='计算机' and 学号 IN 
          (select 学号 from xs_kc where 成绩>80 and 课程号 IN 
            (SELECT 课程号 from kc where 课程名='离散数学'));

_eg14_ ：查询未选修离散数学课程的学生的学号、姓名；

select 学号,姓名 from xs where 学号 NOT IN 
          (SELECT 学号 from xs_kc where 课程号 IN 
            (select 课程号 from kc where 课程名='离散数学'));

b. 比较子查询

_eg10_ ：查询选修“206”号课程的学生的学号和姓名；

select 学号,姓名 from xs where 学号 =any
          (select 学号 from xs_kc where 课程号='206');

_eg15_ ：查询xs表中比计算机专业年龄都大的学生的学号、姓名、出生日期、专业名；

select 学号,姓名,出生日期,专业名 from xs where 出生日期<all
          (select 出生日期 from xs where 专业名='计算机');
        或
        select 学号,姓名,出生日期,专业名 from xs where 出生日期<any
          (select min(出生日期) from xs where 专业名='计算机');

-- 注意：不能用年龄进行计算，应为无法考虑到同岁比较年龄大小的情况
        select 学号,姓名,出生日期,专业名 from xs where year(NOW())-year(出生日期)>all
          (select max(year(NOW())-year(出生日期)) from xs where 专业名='计算机');

_eg16_ ：查询非计算机专业比计算机专业任意一个年级小的学生的学号、姓名、出生日期、专业名；

select 学号,姓名,出生日期,专业名 from xs where 专业名!='计算机' and 出生日期>any
          (select 出生日期 from xs where 专业名='计算机');

_eg17_ ：查询xs_kc表中课程号为“206”的成绩不高于“101”课程的成绩的学生学号；

select 学号 from xs_kc where 课程号='206' and 成绩<=any
          (select 成绩 from xs_kc where 课程号='101');

c. EXISTS 子查询

exists ：如果子查询中有值，返回true，空值返回false。
        not exists ：如果子查询中有值，返回false，空值返回true。

相关子查询：父查询的结果作为子查询的条件。
        （EXISTS类似于for循环）

_eg18_ ：查询选修206号课程的学生的学号和姓名；

select 学号,姓名 from xs where EXISTS 
          (select * from xs_kc where 课程号='206' and 学号=xs.学号);

分析：

学号     课程号    是否选修    返回值      是否保留该记录
        081101     206         是        true             是
        081102     206         是        true             是
          .        206       是/否     true/false       是/否
          .        206       是/否     true/false       是/否
          .        206       是/否     true/false       是/否

（依次取出xs表中的每条记录在xs_kc表中进行判断。等同于与外层一个for循环，在最内层进行判断）

_eg19_ ：查询选修所以课程的学生学号（等价于：没有一个学生不选修所有课程）；

select 学号 from xs where not EXISTS 
          (select * from kc where not exists 
            (select * from xs_kc where 学号=xs.学号 and 课程号=kc.课程号));

分析：

学号     课程号    是否选修    第一返回值       第二返回值    是否保留该记录
        081101     all        否          1             0               否
        081102     all        否          1             0               否
          .        all       是/否       0/1           1/0            是/否
          .        all       是/否       0/1           1/0            是/否
          .        all       是/否       0/1           1/0            是/否

（先取出xs表中一条记录，然后再依次取出kc表中的每条记录，最后在xs_kc表中进行判断，当把kc表中的记录都比较完成后，再从xs表中取出下一条记录，继续执行上面过程。等同于双层for循环，在最内层进行判断处理）

_eg20_ ：查询至少选修了081111学生所选课程的学生的学号；

select 学号 from xs where not EXISTS 
          (select * from xs_kc y where 学号='081111' and not EXISTS 
            (select * from xs_kc where 学号=xs.学号 and 课程号=y.课程号));

select DISTINCT 学号 from xs_kc x where not EXISTS 
          (select * from xs_kc y where 学号='081111' and not EXISTS 
            (select * from xs_kc where 学号=x.学号 and 课程号=y.课程号));

分析：

学号       课程号       是否选修    返回值       第二返回值    是否保留该记录
        081101  101、102、206       是         0             1               是
        081102  101、102、206       否         1             0               否
          .     101、102、206     是/否       0/1           1/0            是/否
          .     101、102、206     是/否       0/1           1/0            是/否
          .     101、102、206     是/否       0/1           1/0            是/否

（跟 eg19 类似）

### 4.4 条件查询

##### 1. GROUP BY
1. GROUP BY 列名|表达式 ... [with rollup];

> 注意：如果 GROUP BY 后跟需要依据的分组的条件有多个（如eg5），建议不要随意改变所依据条件的顺序

_eg1_ ：查询各专业的专业名；

SELECT 专业名 FROM xs GROUP BY 专业名;

_eg2_ ：查询各专业对应的人数；

select 专业名,count(*) 人数 from xs GROUP BY 专业名;

_eg3_ ：查询选修课程的平均成绩以及选修该课程的人数；

SELECT 课程号,avg(成绩) 平均成绩,count(*) 人数 from xs_kc GROUP BY 课程号;

_eg4_ ：查询每个专业的男、女生人数、总人数；

select 专业名,性别,count(*) from xs GROUP BY 专业名,性别 with ROLLUP;

_eg5_ ：查询每门课各专业的平均成绩、每门课的总平均成绩、所有课程的平均成绩；

select 课程号,专业名,avg(成绩) from xs,xs_kc where xs.学号=xs_kc.学号 
          GROUP BY 课程号,专业名 with ROLLUP; -- 建议不要改变 课程号和专业名 的顺序

_eg6_ ：查询通信工程专业男女同学不同课程的平均成绩；

select 专业名,性别,课程号,avg(成绩) from xs,xs_kc 
          where xs.学号=xs_kc.学号 and 专业名='通信工程' GROUP BY 性别,课程号;

##### 2. having
2. having

**通常其后跟聚合函数**

_eg7_ ：查询选修课程超过两门课且成绩在80分以上的学生学号；

select 学号 from xs_kc where 成绩>80 GROUP BY 学号 HAVING count(课程号)>2;

_eg8_ ：查询通信工程专业平均成绩在85分以上的学生的学号和姓名；

select xs.学号,姓名 from xs,xs_kc 
          where xs.学号=xs_kc.学号 and 专业名='通信工程' 
            GROUP BY 学号 HAVING avg(成绩)>85;

_eg9_ ：查询计算机专业男同学不同课程的平均成绩在80分以上的姓名、课程名、平均成绩；

select 姓名,课程名,avg(成绩) from xs,xs_kc,kc 
          where xs.学号=xs_kc.学号 and xs_kc.课程号=kc.课程号 and 专业名='计算机' and 性别='1' 
            GROUP BY xs_kc.课程号 HAVING avg(成绩)>80;

##### 3. order by
3. order by 列名|表达式|子查询 asc|desc;

asc：升序排列（默认值）  
    desc：降序排列

_eg10_ ：将学生课程表中的信息按照成绩的大小排序；

select * from xs_kc ORDER BY 成绩 desc;

_eg11_ ：将计算机专业的学生信息按照年龄的大小排序；

select * from xs where 专业名='计算机' ORDER BY 出生日期 asc;

_eg12_ ：将计算机专业的学号按照平均成绩的大小排序；

select 学号,avg(成绩) from xs_kc where 学号 in
          (select 学号 from xs where 专业名='计算机') 
            GROUP BY 学号 ORDER BY avg(成绩) desc;
        或
        select 学号 from xs where 专业名='计算机' 
          ORDER BY (select avg(成绩) from xs_kc 
            GROUP BY 学号 HAVING xs.学号=xs_kc.学号) desc;

##### 4. limit
4. limit

limit n; -- 保留前n行结果
        linit m,n; -- 从m+1行开始保留n行 <==> 保留n行忽略前m行
        limit n offset m; -- 保留n行忽略前m行

_eg13_ ：查询xs表中前5行；

select * from xs limit 5;

_eg14_ ：查询xs表中的第2、3行；

select * from xs LIMIT 1,2;
        select * from xs LIMIT 2 OFFSET 1;

_eg15_ ：查询计算机专业的女同学平均成绩大于75分且平均成绩排在第二名学生的信息和其平均成绩；

select * from xs,xs_kc 
          where 专业名='计算机' and 性别='0' and xs.学号=xs_kc.学号
            GROUP BY 学号 HAVING avg(成绩)>75 ORDER BY avg(成绩) desc LIMIT 1,1;

##### 5. union 并集
> R和S的并集表示为：R-(R-S)

5. union 并集

_eg16_ ：查询学号为“081101”和“081102”同学的选课信息；

select * from xs_kc where 学号='081101'
        union
          select * from xs_kc where 学号='081102';

或

select * from xs_kc where 学号='081101' or 学号='081102';
        select * from xs_kc where 学号 in ('081101', '081102');

6. handler

语法格式：

handler 表名 open; -- 打开表
        handler 表名 read first|next [where 条件 [limit 参数]];
        handler 表名 close; -- 关闭表

使用：

handler xs open; -- 打开表
        handler xs read first where 专业名='计算机';
        handler xs read next where 专业名='计算机'; -- 读取表的下一行
        handler xs close; -- 关闭表

### 4.5 MySQL视图

> 跟表类似，除了更新有部分的差别。

> 如果视图包含以下几种结构，则视图是不可更新的：  
> 1）聚合函数  
> 2）DISTINCT 关键字  
> 3）GROUP BY 子句  
> 4）ORDER BY 子句  
> 5）HAVING 子句  
> 6）UNION 运算符  
> 7）位于选择列表中的子查询  
> 8）FROM 子句中包含多个表  
> 9）SELECT 语句中引用了不可更新的视图  
> 10）WHERE 子句中的子查询，引用了 FROM 子句中的表  
> 11）ALGORITHM 选项指定为 TEMPTABLE（使用临时表总会使视图变为不可更新的）

##### 1. 创建视图
1. 创建视图

语法：

create [or replace]
        view 视图名 as select 语句 
        [with check option];

_eg1_ ：创建视图s1，要求该视图中包含计算机专业的学生的所有信息，如果后续对该视图更新，也要保证是计算机专业的学生信息；

create view s1 as select * from xs where 专业名='计算机' with check option;

_eg2_ ：创建视图s2，要求该视图中包含学生的学号、姓名、专业名、课程名、成绩；

create view s2 as select 学号,姓名,专业名,课程名,成绩 
        from xs join xs_kc using(学号) join kc using(课程号);

_eg3_ ：创建视图s3，要求该视图中包含通信工程专业的学生姓名、课程名、成绩；

create view s3 as select 姓名,课程名,成绩 
        from s2 where 专业名='通信工程';

##### 2. 查询视图和修改视图
2. 查询视图和修改视图

语法：

select *|列名|表达式 from 视图名; -- 查询视图
        alter view 视图名 as select 语句; -- 修改视图

##### 3. 更新视图
3. 更新视图

_eg4_ ：将视图s1中学生王燕的名字改为“小五”;

update s1 set 姓名='小五' where 姓名='王燕';

select * from s1;
        SELECT * from xs; -- 可以看到基本表中对应数据也被修改
        -- 如果该视图可以更新，对视图的更新，都会将数据反馈到基本表中；

_eg5_ ：向视图s1中插入一条记录 ('081151', '小王', '软件工程', '1', '2003-05-01', 51, null, null)；

INSERT into s1 values 
          ('081151', '小王', '软件工程', '1', '2003-05-01', 51, null, null);
        -- 报错，因为在创建视图s1时，添加了 with check option 的约束，只能添加计算机专业的学生信息，例如：
        insert into s1 values 
          ('081151', '小王', '计算机', '1', '2003-05-01', 51, null, null);

_eg6_ ：向视图s3中插入一条记录('王五', '数据库原理', 95)；

INSERT into s3 VALUES('王五', '数据库原理', 95);
        -- 不允许插入，因为该视图属于不可更新的

##### 4. 删除视图
4. 删除视图

drop view 视图名;

drop view s2;
        select * from s3; -- 当删除视图s2时，会影响到视图s3

# 第7章 MySQL 过程式数据库对象

### 7.1 存储过程

> 本质：就是一段代码。

##### 1. 创建存储过程
1. 创建存储过程

create PROCEDURE 存储过程名([in|out|inout 参数名 数据类型]) 存储过程主体;

注：参数名一般不要和列名相同。

调用存储过程：

CALL 存储过程名([参数]);

_eg1_ ：创建存储过程s1，要求查询特定学生的信息；

create PROCEDURE s1(in xh char(6))
          -- BEGIN
            SELECT * from xs where 学号=xh;
          -- end;
        call s1('081102'); -- 调用存储过程

注：如果语句块中只有一条语句，则BEGIN、END可以省略

##### 2. 存储过程体
2. 存储过程体

a. 变量

[1] 系统变量：全局变量和会话变量

show VARIABLES;
        set foreign_key_checks=0;
        set character_set_database='gbk';

[2] 用户变量：用户自己定义的变量，仅限当前查询窗口可用

set @变量名 =值|表达式;
        select @变量名 := 值|表达式;

查询用户变量的值：

select @变量名;

set @a='081101';
        select * from xs where 学号=@a;

[3] 局部变量

DECLARE 变量名 数据类型 [DEFAULT 默认值];
        DECLARE kch,kcm char(20);
        DECLARE xf int DEFAULT 5;

b. 使用SET语句赋值

set 变量名=值|表达式;

例如：

set foreign_key_checks=0; -- 关闭外键约束
        set @a='081101';

c. select .. into..

可以把选定的列值直接存储到变量中。

select 列名 into 变量名 from 表名 [where 语句];
        select 课程号,课程名 into kch,kcm from kc;

d. 流程控制语句

[1] if [elseif] else

if 条件 then 语句 
        [elseif 条件 then 语句] 
        [..else 语句] 
        end if;

_eg2_ ：创建存储过程s2，要求输入两个参数进行比较，输出大于、小于或等于；

create PROCEDURE s2(in a1 int,in a2 int,out a3 char(4))
          BEGIN
            if a1>a2 then set a3='大于';
            elseif a1=a2 then set a3='等于';
            else set a3='小于';
            end if;
          end;

call s2(9,6,@a4);
        SELECT @a4;

[2] case then

case 
          when 条件 then 语句 
          [when 条件 then 语句] 
          [else 语句] 
        end case;

_eg3_ ：创建存储过程s3，要求输入两个参数进行比较，输出大于、小于或等于；

create PROCEDURE s3(in a1 int, in a2 int, out a3 char(4))
          BEGIN
            CASE
              when a1>a2 then set a3='大于';
              when a1=a2 then set a3='等于';
              else set a3='小于';
            end case;
          end;

call s3 (7, 3, @a5);
        select @a5;

[3] 循环语句

**while循环** ：

while 条件 do 
          语句 
        end while;

_eg4_ ：创建存储过程s4，要求带有while循环；

create PROCEDURE s4()
          BEGIN
            set @x=3;
            while @x>0 do
            set @x=@x-1;
            end while;
          end;

call s4();
        select @x;

**repeat循环** ：

repeat 
          语句 
          until 条件 
        end repeat;

_eg5_ ：创建存储过程s5，要求带有repeat循环；

create PROCEDURE s5()
          BEGIN
            set @y=3;
            REPEAT set @y=@y-1;
            UNTIL @y<1
            end REPEAT;
          end;

call s5();
        select @y;

**loop循环** ：

loop 
          语句 
        end loop;

_eg6_ ：创建存储过程s6，要求带有loop循环；

create PROCEDURE s6()
          BEGIN
            set @z=3;
            label: LOOP
              set @z=@z-1;
              if @z<1 then LEAVE label;
              end if;
            end loop label;
          end;

call s6();
        select @z;

e. 处理程序和条件

DECLARE continue|exit|undo HANDLER for 错误类型 [存储过程语句];

DECLARE CONTINUE HANDLER for SQLSTATE '23000';

_eg7_ ：创建存储过程s7，向xs表中插入一条记录('081101', '张三', '计算机', 1, '2000-05-07', 50, null, null)，遇到错误继续执行；

create PROCEDURE s7()
          BEGIN
            DECLARE CONTINUE HANDLER for SQLSTATE '23000' set @p=1;
            set @p=2;
            insert into xs VALUES('081101', '张三', '计算机', 1, '2000-05-07', 50, null, null);
            set @p=3;
          end;

call s7();
        select @p; -- @p的作用：验证代码执行到的位置

f. 游标

声明游标：

DECLARE 游标名 cursor for select 语句;

打开游标：

OPEN 游标名;

读取数据：

FETCH 游标名 into 变量名;

关闭游标：

CLOSE 游标名;

_eg8_ ：创建存储过程s8，要求使用游标统计学生人数；

CREATE PROCEDURE s8(out sum int)
          BEGIN
            DECLARE xh char(6);
            DECLARE s int DEFAULT 1; -- 用于结束循环
            DECLARE c1 CURSOR for select 学号 from xs;
            DECLARE CONTINUE HANDLER for not found set s=0;

set sum=0;
            open c1;
            FETCH c1 into xh;
            while s=1 do
              set sum=sum+1;
              FETCH c1 into xh;
            end while;
            CLOSE c1; -- 关闭游标
          end;

call s8(@sum);
        select @sum;

_eg9_ ：创建存储过程s9，使用游标检索kc表中所有的课程号和课程名，并将其插入到一个新的表中；

create table kc1(kcNo char(6), kcName char(20));
        create PROCEDURE s9()
          BEGIN
            DECLARE kcNo char(6);
            DECLARE kcName char(20);
            DECLARE g int DEFAULT 1;
            DECLARE c2 CURSOR for SELECT 课程号,课程名 from kc;
            DECLARE CONTINUE HANDLER for not found set g=0;

open c2;
            FETCH c2 into kcNo,kcName;
            while g=1 DO
              INSERT into kc1 values(kcNo, kcName);
              FETCH c2 into kcNo,kcName;
            end WHILE;
            CLOSE c2;
          END;

call s9();
        select * from kc1;

##### 3. 存储过程的调用、删除和修改
3. 存储过程的调用、删除和修改

a. 调用

call 存储过程名 ([参数]);

_eg10_ ：创建存储过程p1，有两个输入参数xh和kcName，当某学生某门成绩小于60，将其学分修改为0，反之，将其学分改为该课程的学分；

create PROCEDURE p1(in xh char(6), in kcName char(20))
          BEGIN
            DECLARE kch char(6);
            DECLARE cj int;
            DECLARE xf int;

select 课程号,学分 into kch,xf FROM kc where 课程名=kcName;
            select 成绩 into cj from xs_kc where 学号=xh and 课程号=kch;

if cj<60 THEN
              UPDATE xs_kc set 学分=0 where 学号=xh and 课程号=kch;
            else
              UPDATE xs_kc set 学分=xf where 学号=xh and 课程号=kch;
            end if;
          end;

-- p1存储过程自行测试即可

_练习1_ ：创建存储过程t1，输入学生的学号，统计该学生选课的数目；

create PROCEDURE t1(in xh char(6), out sum int)
          select count(课程号) into sum from xs_kc where 学号=xh;

call t1('081103', @sum);
        select @sum;

_练习2_ ：创建存储过程t2，输入学生的学号，查询该学生的信息，如果输入为空，查询所有学生信息；

create PROCEDURE t2(in xh char(6))
          BEGIN
            if xh is null or xh=''
              then select * from xs;
            else
              select * from xs where 学号=xh;
            end if;
          end;

call t2('081101');

_练习3_ ：创建存储过程t3，输入xh和kcNo，当某学生某门课的成绩大于90分时，将其学号、姓名、课程号、成绩记录到新的表中；

create table gra(sno char(6), sname char(20), kcName char(20), grade int);
        create PROCEDURE t3(in xh char(6), in kcNo char(6))
          BEGIN
            DECLARE grade int;
            DECLARE sname char(20);
            DECLARE kcName char(20);

select 成绩 into grade from xs_kc where 学号=xh and 课程号=kcNo;
            select 姓名 into sname from xs where 学号=xh;
            SELECT 课程名 into kcName from kc where 课程号=kcNo;

if grade>90 then
              INSERT into gra values(xh, sname, kcName, grade);
            end if;
          END;

call t3('081111', '101');
        select * from gra;

b. 删除

drop PROCEDURE 存储过程名;

c. 修改

如果修改存储过程的主体，需要先删除，后新建

### 7.2 存储函数

##### 1. 创建存储函数
1. 创建存储函数

create function 存储函数名([参数名 数据类型[, 参数名 数据类型]]) returns 数据类型
          存储函数主体(return);

_eg1_ ：创建存储函数f1，根据学生的学号返回其出生日期；

create function f1(xh char(6)) RETURNS char(20)
          RETURN (select 出生日期 from xs where 学号=xh);

select f1('081101'); -- 存储函数的调用

_eg2_ ：创建存储函数f2，根据输入的学号和课程名，返回该学生的成绩；

create function f2(xh char(6), kcName char(6)) RETURNS int
          BEGIN
            DECLARE grade int;
            DECLARE kcNo char(3);

select 课程号 into kcNo from kc where 课程名=kcName;
            select 成绩 into grade from xs_kc where 学号=xh and 课程号=kcNo;
            return grade;
          END;

select f2('081101', '计算机基础');

_eg3_ ：创建存储函数f1，判断输入的学号对应的姓名是否为王林，如果是就返回其出生日期，反之返回“不是本人”；

create FUNCTION f1(xh char(6)) RETURNS char(20)
          BEGIN
            DECLARE xsName char(10);
            select 姓名 into xsName from xs where 学号=xh;

if xsName='王林' then 
              RETURN(select 出生日期 from xs where 学号=xh);
            else 
              RETURN '不是本人';
            end if;
          end;

select f1('081102');

### 7.3 触发器

##### 1. 创建触发器
1. 创建触发器

CREATE TRIGGER 触发器名 触发时刻 触发事件
          on 表名 for each row 主体语句;

> 触发时刻：  
    > AFTER：触发事件前触发  
    > BEFORE：触发事件后触发  
    > （如果触发动作和执行动作在一张表中，一般用 before）

> 触发事件：  
    > insert：new 变量是合法的；  
    > delete：old 变量是合法的；  
    > update：new 和 old 可以同时使用；

> for each row：行级触发器

_eg1_ ：创建触发器t1，当在学生表中删除某个学生的信息时，对应的在xs_kc表中删除其选课的信息；

create trigger t1 BEFORE DELETE on xs for each row
          BEGIN
            delete from xs_kc where 学号=old.学号;
          end;

delete from xs where 学号='081102';

select * from xs where 学号='081102';
        select * from xs_kc where 学号='081102';

_eg2_ ：创建触发器t2，当修改xs_kc表中的成绩时，如果修改后的成绩小于60，将其学分置0，反之将学分置为该课程学分；

create trigger t2 before update on xs_kc for each row 
          BEGIN 
            DECLARE xf int;
            select 学分 into xf from kc where 课程号=new.课程号;

if new.成绩<60 then 
              set new.学分=0;
            else 
              set new.学分=xf;
            end if;
          end;

update xs_kc set 成绩=59 where 学号='081103';

select * from xs_kc where 学号='081103';

_eg3_ ：创建触发器t3，向xs_kc表中插入一条记录，如果插入记录的分数不小于60分，则在xs表中的总学分上加上该课程的学分，反之不做改变；

create trigger t3 AFTER insert on xs_kc for each row 
          BEGIN 
            DECLARE xf int;
            select 学分 into xf from kc where 课程号=new.课程号;

if new.成绩>=60 then 
              update xs set 总学分=总学分+xf where 学号=new.学号;
            end if;
          end;

insert into xs_kc values('081104', '210', 81, 5);

select * from xs_kc where 学号='081104';
        select * from xs where 学号='081104';

_练习1_ ：创建触发器t4，当对表中的总学分进行修改时，如果总学分增加了一成以上，则将操作的记录插入到新的表中xs1(sno char(6), sname char(20), oldzxf int, newzxf int);

create table xs1(sno char(6), sname char(20), oldzxf int, newzxf int);

create trigger t4 AFTER update on xs for each row 
          BEGIN 
            if new.总学分>=1.1*old.总学分 then 
              insert into xs1 values(new.学号, new.姓名, old.总学分, new.总学分);
            end if;
          end;

update xs set 总学分=60 where 学号='081110';

select * from xs where 学号='081110';
        select * from xs1;

_练习2_ ：创建触发器t5，删除某门课程的信息时，将删除记录的课程号和课程名插入到新的表中；

create table kc1(cno char(6), cname char(20));

create trigger t5 AFTER DELETE on kc for each row 
          insert into kc1 values(old.课程号, old.课程名);

delete from kc where 课程号='302';

select * from kc;
        select * from kc1;

_练习3_ ：创建触发器t6，当向xs_kc表中插入记录时，如果插入的成绩大于90，将其在xs表中的备注改为“优秀学生”;

create trigger t6 AFTER insert on xs_kc for each row 
          BEGIN 
            if new.成绩>=90 then 
              update xs set 备注='优秀学生' where 学号=new.学号;
            end if;
          end;

insert into xs_kc values('081111', '301', 91, 3);

select * from xs_kc where 学号='081111';
        select * from xs where 学号='081111';

##### 2. 删除触发器
2. 删除触发器

drop tirgger 触发器名;

### 7.4 事件

> 在执行事件前，需要打开时间调度器：SET GLOBAL event_scheduler=true;

##### 1. 创建事件
1. 创建事件

create event 事件名 on SCHEDULE 时刻 do sql 语句;

> 时刻：  
    > at now()[+INTERVAL 事件间隔]|every 时间间隔 [STARTS 某时刻[+INTERVAL 事件间隔] ENDS 某时刻[+INTERVAL 事件间隔]];

_eg1_ ：创建一个30秒后执行的事件

CREATE event e1 on SCHEDULE at NOW()+INTERVAL 30 SECOND
          do INSERT into xs_kc values('081101', '208', 50, 4);

show events; -- 查看所有事件
        select * from xs_kc where 学号='081101';

##### 2. 删除事件
2. 删除事件

drop event 事件名;

##### 3. 修改事件
3. 修改事件

alter event 事件名 rename to 新事件名;

# 第8章 数据库的备份与恢复

##### 1. 使用SQL语句实现数据库的备份与恢复
1. 使用SQL语句实现数据库的备份与恢复

备份：

select * from 表名 into outfile '文件名';

select * from xs into outfile 'D:/1.txt'; -- 将数据库备份到D盘下的 1.txt 中

恢复：

load data infile '文件名' into table 表名;

create table xs1 like xs;
        load data infile 'e:/1.txt' into table xs1;
        select * FROM xs1;

##### 2. 使用命令行实现数据库的备份与恢复
2. 使用命令行实现数据库的备份与恢复

用管理员打开命令行，切换到 bin 路径下

备份：

mysqldump[ 主机名 用户名] 数据库名 表名1[ 表名2 ...]>文件名

mysqldump -h localhost -u root -proot xscj xs kc>1.txt

恢复：

musqlimport[ 主机名 用户名] 数据库名 表名<文件名

##### 3. 使用navicat实现数据库的备份与恢复
3. 使用navicat实现数据库的备份与恢复

备份：右键数据库 -> 转储SQL文件

# 第9章 数据库的安全性

##### 1. 创建用户
1. 创建用户

create user 用户名@'localhost' IDENTIFIED by '密码';

-- 在本地创建用户U1，设置密码为‘root’
        create user U1@'localhost' IDENTIFIED by 'root';

查看创建的用户：

select user from mysql.user;

删除用户：

drop user 用户;

##### 2. 授权
2. 授权

grant 权限 on 表名 to 用户[ with grant option];

_eg1_ ：给用户U1授予对xs表的所有权限，并让其具有传播权限的能力；

grant all PRIVILEGES on xs to U1@'localhost' with grant option;

_eg2_ ：切换到用户U1，将xs表的查询权限给用户U2；

-- 切换到用户U1，有两种方式：
        -- 1）命令行方式：mysql -u U1 -p密码
        -- 2）navicat：新建连接 -> 用户名U1，密码***

grant SELECT on xs to U2@'localhost';

查看用户权限：

show grants for 用户;

show grants for U1@'localhost';

##### 3. 收权
3. 收权

revoke 权限 on 表名 from 用户名@'localhost';

revoke select on xs from U2@'localhost';

大一点的小孩/MySQL

简介

发行版

贡献者

近期动态

大一点的小孩/MySQL .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

大一点的小孩/MySQL