meethigher-MySQL从入门到会用

我为啥要写着篇文章呢？在实际开发中，我经常百度，比方说sql语句我给忘了，比方说触发器我不会写了，网上文章太繁琐，所以我就打算自己系统整理一篇，为我自己所用，ctrl+f的好处，懂得都懂。

本篇文章参照了好多网文，有一些网上视频，也有一些大佬博客，整理而成，故此篇虽由我一字字手敲，加入了自己的体会，但其实是转载文。

最新的内容，我会更新在我自己的网站

本文以MySQL8.0+为例，仅做入门参考。具体内容，参照MySQL官方文档。

一、数据库概念

英文：DataBase 简称DB

概念：用于存储和管理数据的仓库

数据库的特点：

1. 持久化存储数据。本质是一个文件系统
2. 方便存储和管理数据
3. 使用了统一的方式（SQL）操作数据库

常见的数据库：

全球数据库排名DB-Engines Ranking

• Oracle：收费的，大型关系数据库，Oracle公司的产品。Oracle收购Sun公司，收购MySQL
• MySQL：开源免费的数据库，小型关系数据库。被Oracle收购之后，开始收费
  • 免费： 免费版本的具有开源协议，在免费版本上进行的任何修改所发布的软件，也必须是开源的（开源牛逼！）
  • 收费： 如果你想将修改后的软件出售获利，就必须得购买收费版本才可以 。 此外，收费版本卖的不是软件，而是服务，当数据出现问题的时候，购买的服务可以帮助处理发生问题的数据。
• SQLServer：MicroSoft公司收费的中型关系型数据库。C#，.Net等语言常使用。
• DB2：IBM公司的大型关系数据库产品，收费的。常用在银行系统中
• SQLite：嵌入式的小型关系型数据库，内置到软件或系统中去，比方说浏览器。应用在Android手机端
• SyBase：已经淡出历史舞台。提供了一个非常专业的数据建模工具PowerDesigner

二、SQL概念

SQL：结构化查询语言（Structured Query Language ），其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，就类似于“方言”。

SQL通用语法：

1. SQL语句可以单行或者多行书写，以分号结尾
2. 使用空格和缩进来提高语句可读性
3. SQL语句不区分大小写，关键字建议使用大写
4. 注释
   • 单行注释：-- 注释内容 或者 # 注释内容（MySQL特有）
   • 多行注释：/* 注释 */

-- 单行注释
# 单行注释，MySQL特有
/* 注释 */

SQL分类：

1. DDL(Data Definition Language)数据定义语言。用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
2. DML(Data Manipulation Language)数据操作语言。用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
3. DQL(Data Query Language)数据查询语言。用来查询数据库中表的记录(数据)。关键字：select, where 等
4. DCL(Data Control Language)数据控制语言。用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

三、MySQL使用

数据库可视化操作工具：

1. Navicat Premium（目前为止，感觉最好用的一款）
2. SQLyog（最新版的界面依旧是上古风格，不过还是挺好用的，推荐）
3. DBeaver（别人推荐，我还没用过）

题外话，讲讲使用技巧

Navicat生成ER关系图并导出

SQLyog生成关系图

Navicat查看sql语句

SQLyod查看sql语句

好，言归正传

MySQL下载：地址

推荐解压版，拒绝傻瓜式。身为开发者，动手能力必须要强。我某同学，至今还以为mysql是个软件。我曾经给人安装了解压版，然后人家跟我说，这不是mysql，有海豚标志的才是mysql（可视化工具sqlyog的图标）。我就回了一句，哦，我还不太会安，你找别人吧。

常用命令行（进入到mysql的bin目录下）：

• mysql -u 用户名 -p 用户名 或mysql -u 用户名 -p; - - 连接数据库

• show databases; - - 展示全部数据库

• create database name; - - 创建name数据库

• use name; - - 使用name数据库

• show tables; - - 展示其中的表

• create table name (id int,name varchar(20),age int); - - 创建name表，其中有3列，int型id列，varchar型长度为20的name列，int型age列

• desc name; - - 查看name表的结构

• source sql.sql; - - 执行本地的sql.sql文件

• drop table name; - - 删除name表

• drop database name; - - 删除name数据库

• exit或quit; - - 退出数据库终端

windows下用命令行操作，是件极爽的事情，最进下载了一套数学视频，文件名都被打了小广告，cmd一键批处理，爽地不要不要的

本来想着写一篇cmd命令学习笔记，但奈何内容太庞大了，等哪天有足够的时间，来系统学习的话，我会写一篇的。

四、SQL操作

以下例子都是基于MySQL

4.1 DDL：操作数据库、表

操作数据库：CRUD

也就是所谓的增删改查

1. C(Create)：创建
   创建beauty数据库，判断是否存在，并设置字符集为UTF-8：create database if not exists beauty character set utf8;

2. R(Retrieve)：读取
   查询所有数据库：show databases;

3. U(Update)：更新

   查看数据库的字符集：show create database beauty;
   修改数据库的字符集：alter database beauty character set gbk;

4. D(Delete)：删除
   删除数据库：drop database if exists beauty;

5. 使用数据库：use beauty;

查看当前使用的数据库：select database();

操作表：CRUD

1. C(Create)：创建
   语法：create table 表名 (列名1 数据类型1,列名2 数据类型2,…,列名n 数据类型n);

   创建表：create table girls(id int,name varchar(20),score double(4,1),birthday date,insert_time timestamp);

   复制表：create table 表名 like 被复制的表名;

2. R(Retrieve)：读取
   查询所有表：show tables;
   查询表结构：desc 表名

3. U(Update)：更新
   修改表名：alter table 表名 rename to 新表名;
   查看表的字符集：show create table 表名;
   修改表的字符集：alter table 表名 character set utf8;
   添加一列：alter table 表名 add 列名 数据类型;
   修改列的类型：alter table 表名 modify 列名 新类型;
   修改列名称 类型： alter table 表名 change 列名 新列名 新类型;
   删除列：alter table 表名 drop 列名;

4. D(Delete)：删除
   删除表：drop table if exists 表名;

数据类型的注意点：

double(4,1)表示总长度为4的数，其中小数有1位

date表示日期，只包含年月日，yyyy/MM/dd。我在开发starry的时候，发现在ios的safari中，只识别yyyy/MM/dd，而不识别yyyy-MM-dd，使用的时候注意。

datetime表示日期，包含年月日时分秒，yyyy/MM/dd HH:mm:ss

timestamp表示时间戳，包含年月日时分秒，yyyy/MM/dd HH:mm:ss。有种说法，“如果将来不给这个字段赋值，或者赋值为null，则默认使用系统当前时间，来自动赋值。”，经测验无效。

4.2 DML：增删改表中数据

添加数据

语法：insert into 表名(列名1,列名2,…,列名n) values(值1,值2,…,值n);

INSERT INTO fairy(id,NAME,score) VALUES(1,'胡列娜',80.1);

注意：

1. 列名和值，要一一对应。
2. 如果表名后，不定义列名，则默认给所有列添加值。insert into 表名 values(值1,值2,…,值n);
3. 除了数字类型，其他类型都需要使用引号（单双引号都可以）引起来

删除数据

语法：delete from 表名 where 条件;

DELETE FROM fairy WHERE NAME='水冰儿';

注意：

1. 如果不加条件，则会删除表中所有记录。
2. 如果要删除所有记录。
   • delete from 表名; 不推荐使用，有多少条记录，就会执行多少次删除操作。效率比较低。
   • truncate table 表名; 推荐使用，先删除表，然后再创建一张一模一样的表。效率比较高。

修改数据

语法：update 表名 set 列名1=值1,列名2=值2,…,列名n=值n where 条件;

UPDATE fairy SET NAME='水月儿' WHERE id=2;

注意：

1. 如果不加任何条件，则会将表中所有记录全部修改

4.3 DQL：查询表中记录

导入数据：

USE `beauty`;

/*Table structure for table `fairy` */

DROP TABLE IF EXISTS `fairy`;

CREATE TABLE `fairy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `gender` varchar(2) NOT NULL,
  `age` int(11) NOT NULL,
  `grade` int(11) NOT NULL,
  `school` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `position` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*Data for the table `fairy` */

insert  into `fairy`(`id`,`name`,`gender`,`age`,`grade`,`school`,`position`) values (1,'胡列娜','女',22,52,'武魂殿学院','武魂殿'),(2,'邱若水','女',18,37,'天水学院','天斗帝国'),(3,'水冰儿','女',17,43,'天水学院','天斗帝国'),(4,'水月儿','女',17,36,'天水学院','天斗帝国'),(5,'邪月','男',22,53,'武魂殿学院','武魂殿'),(6,'唐三','男',16,44,'史莱克','天斗帝国'),(7,'戴沐白','男',21,48,'史莱克学院','天斗帝国'),(8,'胡列娜','女',22,52,'武魂殿学院','武魂殿');

语法：select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件限定 order by 排序规则 limit 分页限定

基础查询

1. 多个字段的查询
   select 字段名1,字段名2,… from 表名;
   如果查询所有字段，可以使用 * 来替代字段列表
2. 去除重复
   DISTINCE关键字
3. 计算列
   一般可以使用四则运算，来计算列的值
   IFNULL(表达式1,表达式2) 表达式1是指需要判断为NULL的字段，表达式2是指NULL的替换值。
4. 起别名
   AS关键字，也可以省略

-- 多个字段查询
SELECT NAME,school FROM fairy;

-- 去除重复
SELECT DISTINCT school FROM fairy;
SELECT DISTINCT NAME,school FROM fairy;

-- 计算age跟grade之和
SELECT NAME,age,grade,age+grade FROM fairy;
-- 如果有NULL参与运算，将NULL换位0
SELECT NAME,age,grade,IFNULL(age,0)+IFNULL(grade,0) AS '年龄+等级' FROM fairy;

条件查询

1. where子句后跟条件

2. 运算符

运算符	含义
>、<、<=、>=、=、<>	<>在 SQL 中表示不等于，在 mysql 中也可以使用!=，没有==这个运算符
BETWEEN…AND…	在一个范围之内，如：between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾
IN(集合)	集合表示多个值，使用逗号分隔
LIKE ‘张%’ 或 LIKE ‘张_’	模糊查询。%指多个占位符，_指单个占位符
IS NULL 或 IS NOT NULL	查询某一列为 NULL 的值，注：不能写=NULL
AND 或 &&	与，SQL 中建议使用前者，后者并不通用。
OR 或 ||	或
NOT 或 !	非

代码

-- 查询年龄大于等于20岁的
SELECT NAME,age FROM fairy WHERE age>=20;

-- 查询年龄等于22岁的
SELECT NAME,age FROM fairy WHERE age=22;

-- 查询年龄不等于22岁的
SELECT NAME,age FROM fairy WHERE age!=22;
SELECT NAME,age FROM fairy WHERE age<>22;

-- 查询年龄大于等于18岁，小于等于22岁
SELECT NAME,age FROM fairy WHERE age>=18 AND age<=22;
SELECT NAME,age FROM fairy WHERE age>=18 && age<=22;
SELECT NAME,age FROM fairy WHERE age BETWEEN 18 AND 22;

-- 查询所有22岁，18岁，性别为女的人
SELECT * FROM fairy WHERE age=22 OR age=18;
SELECT * FROM fairy WHERE age IN (22,18);

-- 查询等级不为NULL的人
SELECT * FROM fairy WHERE grade IS NOT NULL;

-- 查询姓水的人
SELECT * FROM fairy WHERE NAME LIKE '水%';

-- 查询姓名中含有水字的人
SELECT * FROM fairy WHERE NAME LIKE '%水%';

排序查询

语法：order by 排序字段1 排序方式1,…;

排序方式：

1. ASC：升序排。默认值
2. DESC：降序排。

注意：

1. 如果有多个排序条件，则当前面的值一样时，才会去判断第二条件

代码：

-- 按age升序排
SELECT * FROM fairy ORDER BY age;

-- 按age降序排
SELECT * FROM fairy ORDER BY age DESC;

-- 按照年龄排序，如果年龄一样，则按照等级排序
SELECT * FROM fairy ORDER BY age ASC,grade ASC;

聚合函数

将一列数据作为一个整体，进行纵向计算。

1. count：计算个数
   一般选择非空的列：主键
   可以通过count(*)，但实际开发中，少用这个
2. max：计算最大值
3. min：计算最小值
4. sum：计算和
5. avg：计算平均值

注意：聚合函数的计算，排除NULL值

解决方案：

1. 选择不包含非空的列进行计算
2. IFNULL(grade,0)

代码：

-- 显示总数
SELECT COUNT(NAME) FROM fairy;

-- 如果grade为NULL的时候，聚合函数就不会统计，可以这样处理
SELECT COUNT(IFNULL(grade,0)) FROM fairy;

-- 获取最大值与最小值
SELECT MAX(age) FROM fairy;
SELECT MIN(age) FROM fairy;

-- 获取等级之和
SELECT SUM(grade) FROM fairy;

-- 获取平均等级
SELECT AVG(grade) FROM fairy;

分组函数

语法：group by 分组字段；

注意：

1. 分组之后查询的字段：分组字段或者聚合函数；
2. where和having的区别？
   • where在分组之前进行限定，如果不满足条件，则不参与分组；having在分组之后进行限定，如果不满足条件，不会被查询出来
   • where后跟不可跟聚合函数；having可以进行聚合函数的判断

-- 按照性别分组，求总人数，并且求不同组的平均等级
SELECT 
  gender,
  AVG(grade) '平均等级',
  COUNT(NAME) '人数' 
FROM
  fairy 
GROUP BY gender ;

-- 按照性别分组，求总人数，并且求不同组的平均等级。
-- 要求：等级低于44的，不参与分组
SELECT 
  gender,
  AVG(grade) '平均等级',
  COUNT(NAME) '人数' 
FROM
  fairy 
WHERE grade >= 44 
GROUP BY gender ;

-- 按照性别分组，求总人数，并且求不同组的平均等级。
-- 要求：等级低于44的，不参与分组。分组之后，人数要大于2人
SELECT 
  gender,
  AVG(grade) '平均等级',
  COUNT(NAME) '人数' 
FROM
  fairy 
WHERE grade >= 44 
GROUP BY gender 
HAVING COUNT(NAME) > 2 ;

SELECT 
  gender,
  AVG(grade) '平均等级',
  COUNT(NAME) '人数' 
FROM
  fairy 
WHERE grade >= 44 
GROUP BY gender 
HAVING 人数 > 2 ;

分页查询

语法：limit 开始的索引，每页查询的条数;

公式：开始的索引=(当前的页码-1)x每页显示的条数

注意：limit是MySQL的一个“方言”。不同的数据库分页操作是不同的

-- 从第1条数据开始，显示3条
SELECT * FROM fairy LIMIT 0,3;

-- 从第4条数据开始，显示3条
SELECT * FROM fairy LIMIT 3,3;

多表查询

笛卡尔积：A、B两个集合，取这两个集合的所有组成情况。要完成多表查询，要消除无用的数据。

内连接

使用内连接的要求：

1. 从哪些表中查询数据
2. 条件是啥
3. 查询哪些字段

隐式内连接

语法：select 字段列表 from 表名列表 where …;

-- 查询两张表所有数据
SELECT 
  * 
FROM
  memeber,
  position1 
WHERE memeber.`p_id` = position1.`id` ;

-- 查询第一张表的name,grade和第二张表的school,position
SELECT 
  NAME,
  grade,
  school,
  POSITION
FROM
  memeber,
  position1 
WHERE memeber.`p_id` = position1.`id` ;

-- 用表的别名进行优化，先将表命名为别名，再来写select后的别名.xx
SELECT 
  t1.`name`,
  t1.`grade`,
  t2.`school`,
  t2.`position` 
FROM
  memeber t1,
  position1 t2 
WHERE t1.`p_id` = t2.`id` ;

显式内连接

语法：select 字段列表 from 表1 [inner] join 表2 on 条件;

[]这个符号表示可以省略的意思

-- 查询两张表所有数据
SELECT 
  * 
FROM
  memeber 
  JOIN position1 
    ON memeber.`p_id` = position1.`id` ;

-- 查询第一张表的name,grade和第二张表的school,position
SELECT 
  NAME,
  grade,
  school,
  POSITION
FROM
  memeber 
  JOIN position1 
    ON memeber.`p_id` = position1.`id` ;

-- 用表的别名进行优化。注意写法，先将表命名为别名，再来写select后的别名.xx
SELECT 
  t1.name,
  t1.`grade`,
  t2.`school`,
  t2.`position` 
FROM
  memeber t1 
  JOIN position1 t2 
    ON t1.`p_id` = t2.`id` ;

外连接

左外连接

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;

-- 查询所有人信息，如果有位置，则查询位置信息；如果没有位置，则不显示
SELECT 
  t1.*,
  t2.school 
FROM
  memeber t1,
  position1 t2 
WHERE t1.`p_id` = t2.`id` ;

-- 此时，如果有数据的外键是空的，就不会查出来了
-- 为解决这个问题，我们需要用到外连接
SELECT 
  t1.*,
  t2.school 
FROM
  memeber t1 
  LEFT JOIN position1 t2 
    ON t1.`p_id` = t2.`id` ;

左外连接：

查询的是左边表的所有数据，以及连接表的交集部分

右外连接

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;

-- 右外连接，跟上面左外连接的结果一样，写法的区别就是表的顺序，左换右，右换左了
SELECT 
  t1.*,
  t2.school 
FROM
  position1 t2 
  RIGHT JOIN memeber t1 
    ON t1.`p_id` = t2.`id` ;

右外连接：

查询的是右边表的所有数据，以及连接表的交集部分

子查询

概念：查询中嵌套查询，称嵌套的这个查询，为子查询

-- 查询等级最高的人
-- 1.查询最高等级
SELECT 
  MAX(grade) 
FROM
  memeber ;

-- 2.查询等级等于最高的人
SELECT 
  * 
FROM
  memeber 
WHERE grade = 54 ;

-- 使用子查询
SELECT 
  * 
FROM
  memeber 
WHERE grade = 
  (SELECT 
    MAX(grade) 
  FROM
    memeber) ;

子查询的不同情况

1. 子查询的结果是单行单列的
   子查询可以作为条件，使用运算符去判断。>,>=, <, <=, =
2. 子查询的结果是多行单列的
   子查询可以作为条件，使用运算符in来判断
3. 子查询的结果是多行多列的
   子查询可以作为一张虚拟表参与查询

-- 子查询结果是单行单列的
-- 查询等级最高的人
SELECT 
  * 
FROM
  memeber 
WHERE grade = 
  (SELECT 
    MAX(grade) 
  FROM
    memeber) ;

-- 子查询结果是多行单列的
-- 查询武魂殿和天斗帝国的所有成员信息
SELECT 
  * 
FROM
  memeber 
WHERE p_id IN 
  (SELECT 
    id 
  FROM
    position1 
  WHERE POSITION= "武魂殿" 
    OR POSITION= "天斗帝国") ;

-- 子查询结果是多行多列的
-- 查询成员年龄为18以上的成员信息跟位置信息
SELECT 
  * 
FROM
  position1 t1,
  (SELECT 
    * 
  FROM
    memeber 
  WHERE age > 18) t2 
WHERE t1.`id` = t2.p_id ;

-- 可以使用普通内连接，会更好理解
SELECT 
  * 
FROM
  memeber t1,
  position1 t2 
WHERE t1.`p_id` = t2.`id` 
  AND t1.`age` > 18 ;

练习

数据库的架构如图

-- 需求：
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT 
  t1.id,
  ename,
  salary,
  jname,
  description 
FROM
  emp t1,
  job t2 
WHERE t1.`job_id` = t2.`id` ;

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT 
  t1.id,
  ename,
  salary,
  jname,
  description,
  dname,
  loc 
FROM
  emp t1,
  job t2,
  dept t3 
WHERE t1.`job_id` = t2.`id` 
  AND t1.`dept_id` = t3.`id` ;

 
-- 3.查询员工姓名，工资，工资等级
SELECT 
  t1.`ename`,
  t1.`salary`,
  t2.`grade` 
FROM
  emp t1,
  salarygrade t2 
WHERE t1.`salary` BETWEEN t2.`losalary` 
  AND t2.`hisalary` ;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT 
  t1.`ename`,
  t1.`salary`,
  t2.`jname`,
  t2.`description`,
  t3.`dname`,
  t3.`loc`,
  t4.`grade` 
FROM
  emp t1,
  job t2,
  dept t3,
  salarygrade t4 
WHERE t1.`dept_id` = t3.`id` 
  AND t2.`id` = t1.`job_id` 
  AND t1.`salary` BETWEEN t4.`losalary` 
  AND t4.`hisalary` ;

-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT 
  t1.`id`,
  t1.`dname`,
  t1.`loc`,
  t2.`总人数` 
FROM
  dept t1,
  (SELECT 
    dept_id,
    COUNT(id) 总人数 
  FROM
    emp 
  GROUP BY emp.`dept_id`) t2 
WHERE t1.`id` = t2.dept_id ;

-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
SELECT 
  t1.`id`,
  t1.`ename`,
  t2.ename 
FROM
  emp t1 
  LEFT JOIN 
    (SELECT 
      id,ename 
    FROM
      emp) t2 
    ON t1.`mgr` = t2.id ;

4.4 DCL：管理用户与权限

管理用户

查询用户

SELECT * FROM USER;
注意：通配符%表示可以在任意主机使用用户登录

-- 查询用户
-- 1.切换到MySQL数据库
USE mysql;
-- 2.查询user表
SELECT * FROM USER;
-- 通配符%表示可以在任意主机使用用户登录

添加用户

create user ‘用户名’@‘主机名’ identified by ‘密码’;

-- 创建用户
create user '用户名'@'主机名' identified by '密码';
create user 'huliena'@'localhost' identified by '123456';

删除用户

DROP USER ‘用户名’@‘主机名’;

-- 删除用户
DROP USER '用户名'@'主机名';
DROP USER 'huliena'@'localhost';

修改用户密码

-- 修改密码
-- mysql5.7版本
UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';
SET PASSWORD FOR 'huliena'@'localhost' ='123456';
-- mysql8.0+版本
ALTER USER '用户名'@'主机名' IDENTIFIED BY '密码';
ALTER USER 'huliena'@'localhost' IDENTIFIED BY '654321';
-- DCL特有的方式，高低版本都支持
SET PASSWORD FOR '用户名'@'主机名'='密码';

-- MySQL忘记了root用户的密码,管理员方式打开cmd
-- 1.停止mysql服务
net STOP mysql
-- 2.使用无验证方式启动mysql服务
mysqld --skip-GRANT-TABLES;
-- 3.另开一个窗口登录mysql,直接输入mysql即可
mysql
-- 4.修改密码后，关闭所有命令窗口，并去任务管理器里面关闭mysqld.exe
-- 5.启动mysql服务
net START mysql

管理权限

查询权限

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'huliena'@'localhost';
SHOW GRANTS FOR 'root'@'localhost';

授予权限

-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT SELECT,UPDATE,DELETE ON * TO 'huliena'@'localhost';
-- 授予所有权限
GRANT ALL ON *.* TO 'huliena'@'localhost';

撤销权限

-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
revoke all on *.* from 'huliena'@'localhost';

刷新权限

-- 刷新权限
FLUSH PRIVILEGES;

4.5 约束

概念：对表中的数据进行限定，从而保证数据的正确性、有效性和完整性

约束：

1. 主键约束：primary key
2. 非空约束：not null
3. 唯一约束：unique
4. 外键约束：foreign key

非空约束

NOT NULL，值不能为NULL

1. 创建表时，添加非空约束
2. 已创建表，添加非空约束
3. 删除非空约束

代码：

-- 创建表时添加非空约束
create table hero (
id INT,
NAME VARCHAR(20) NOT NULL
);

desc hero;

-- 删除name的非空约束
alter table hero modify name varchar(20);

-- 已经创建表后，添加约束
alter table hero modify name varchar(20) not null;

唯一约束

UNIQUE，值不能重复(但是允许存在多个NULL)

1. 创建表时，添加唯一约束
2. 已创建表，添加唯一约束
3. 删除唯一约束

代码：

-- 创建表时，添加唯一约束
CREATE TABLE hero (
id INT,
phone_number VARCHAR(11) UNIQUE
);
-- 注意mysql中，唯一约束限定的列的值可以有多个NULL

-- 删除唯一约束，index索引，唯一约束也叫做唯一索引
ALTER TABLE hero DROP INDEX phone_number;

-- 在创建表之后，添加唯一约束
ALTER TABLE hero MODIFY phone_number VARCHAR(11) UNIQUE;

DESC hero;

主键约束

含义：表示非空且唯一

一张表只能有一个字段表示主键。主键就是表中记录的唯一标识。

主键约束：

1. 创建表时，添加主键约束
2. 已创建表，添加主键约束
3. 删除主键约束

代码

-- 创建表时，添加主键约束
CREATE TABLE hero(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);

-- 删除主键
ALTER TABLE hero DROP PRIMARY KEY;

-- 已创建表，添加主键约束
ALTER TABLE hero MODIFY id INT PRIMARY KEY;

DESC hero;

自动增长

概念：如果某一列是数值类型的，使用auto_increment 可以完成自动增长

自动增长：

1. 创建表时，添加主键约束，并完成自动增长
2. 已创建表，添加自动增长
3. 删除自动增长

-- 创建表时，添加自动增长
CREATE TABLE hero(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

-- 删除自动增长
ALTER TABLE hero MODIFY id INT;

-- 已创建表，添加自动增长
ALTER TABLE hero MODIFY id INT AUTO_INCREMENT;

DESC hero;

注意：

自动增长是根据上一条数据的大小来增加的。比防说上一条是9，如果不传值，下一条就会是10

外键约束

一张表的数据，会过于冗余，我们可以将其进行拆分，然后再将两张表，通过外键关联起来。

外键约束：

1. 创建表时，添加外键约束

   create table 表名(
       ....
       外键列
       constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
   );
   

2. 已创建表，添加外键约束

3. 删除外键约束

4. 级联操作(级联更新和级联删除)

   -- 添加级联操作
   alter table 表名 add constraint 外键名称 foreign key (外键列名称) referencs 主表名称(主表列名称) on update cascade on delete cascade
   
   --也可以只添加其中某一个，也可以都添加，
   --实现的功能，就是在删除外键表的内容的时候，主键表与其相关的内容也会一起被删掉
   

代码：

-- 创建表时，添加外键约束
CREATE TABLE memeber1 (
  id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL,
  gender VARCHAR(2) NOT NULL,
  age INT(11) NOT NULL,
  grade INT(11) NOT NULL,
  p_id INT(11) NOT NULL,
  CONSTRAINT m_p_fk FOREIGN KEY (p_id) REFERENCES position1(id)
);

-- 已创建表，添加外键约束
ALTER TABLE memeber1 ADD CONSTRAINT m_p_fk FOREIGN KEY (p_id) REFERENCES position1(id);

-- 删除外键约束
ALTER TABLE memeber1 DROP FOREIGN KEY m_p_fk;

-- 添加级联更新和级联删除
ALTER TABLE memeber ADD CONSTRAINT m_p_fk FOREIGN KEY (p_id) REFERENCES position1(id) ON UPDATE CASCADE ON DELETE CASCADE;

五、数据库的设计

5.1 多表之间的关系

分类

• 一对一关系：如人和身份证号。一个人只有一个身份证号，一个身份证号对应一个人。开发中很少使用
• 一对多（多对一）关系：如部门和员工。一个部门对应多个员工，一个员工对应一个部门。
• 多对多关系： 如学生跟课程。一个课程对应多个学生，一个学生对应多个课程。

这三种关系可以表示所有的事务之间的关系。

实现关系

• 一对一（一般情况合成一张表）
  一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。
• 一对多（多对一）
  在多的一方建立外键，指向一的一方的主键。
• 多对多
  多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

案例

代码

-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100) NOT NULL UNIQUE
);
-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键，自动增长
rname 旅游线路名称非空，唯一，字符串 100
price 价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
CREATE TABLE tab_route(
    rid INT PRIMARY KEY AUTO_INCREMENT,
    rname VARCHAR(100) NOT NULL UNIQUE,
    price DOUBLE,
    rdate DATE,
    cid INT,
    FOREIGN KEY (cid) REFERENCES tab_category(cid)
);
/*创建用户表 tab_user
uid 用户主键，自增长
username 用户名长度 100，唯一，非空
password 密码长度 30，非空
name 真实姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，字符串 11
email 邮箱，字符串长度 100
*/
CREATE TABLE tab_user (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    PASSWORD VARCHAR(30) NOT NULL,
    NAME VARCHAR(100),
    birthday DATE,
    sex CHAR(1) DEFAULT '男',
    telephone VARCHAR(11),
    email VARCHAR(100)
);
/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
    rid INT, -- 线路id
    DATE DATETIME,
    uid INT, -- 用户id
    -- 创建复合主键
    PRIMARY KEY(rid,uid), -- 联合主键
    FOREIGN KEY (rid) REFERENCES tab_route(rid),
    FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

打开架构设计器，就会自动生成如下关系图

5.2 数据库设计的范式

概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须要遵循前边所有的范式要求

分类：

• 第一范式
• 第二范式
• 第三范式
• 其他范式

第一范式

第一范式理解：每一列都是不可分割的原子数据项。

像这样的一张表，就满足第一范式了，但是也存在问题

1. 存在非常严重的数据冗余（重复）：姓名、系名、系主任
2. 数据添加存在问题：添加新开设的系和系主任时，数据不合法
3. 数据删除存在问题：如果有人毕业了，删除信息时，会将系一起删掉。

第二范式

第二范式理解：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

函数依赖：A–>B，如果A属性（属性组）的值，可以唯一确定B属性的值。则成B依赖于A。例如，学号–>姓名；（学号，课程名称）–>分数

**完全函数依赖：**A–>B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中的所有的属性值。例如，（学号，课程名称）–>分数

**部分函数依赖：**A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可。例如，（学号，课程名称）–>姓名

**传递函数依赖：**A–>B，B–>C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值可以确定唯一C属性的值，则称C传递函数依赖于A。例如，学号–>系名，系名–>系主任

**码：**如果在一张表中，一个属性或者属性组，被其他所有属性所依赖，则称这个属性（属性组）为该表的码。例如，在上表中的码，就是（学号，课程名称）。

• 主属性：码属性组中的所有属性
• 非主属性：除码属性组的属性

详细了解，请移步这里

现在根据第二范式的理解，在1NF基础上基础上消除非主属性对主码的部分函数依赖。

第三范式

第三范式理解：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

其他范式

巴德斯科范式(BCNF)、第四范式(4NF)和第五范式(5NF)

六、数据库的备份与还原

数据库的备份和还原目的：防止出现问题，导致数据丢失的情况。

6.1 命令行

备份语法：mysqldmp -u用户名 -p密码 数据库名称 > 保存的路径

还原语法：

1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件：source 文件路径

6.2 可视化工具

不同的可视化工具，具体操作不同，不多赘述

七、事务

7.1 事务的了解

概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

举个例子，支付宝转钱。A给B转5块钱，成功的话，A账户金额-5，B账户金额+5。如果在A账户减少金额，或者在B账户增加金额的过程中出错，则所有事件回滚。

事务的操作

具体操作

1. 开启事务
2. 回滚
3. 提交

开启事务：start transaction;

回滚：rollback;

提交：commit;

-- zs给ls转账500
-- 0 开启事务
start transaction;

-- 1 zs账户-500
update account set balance=balance-500 where name='zs';

-- 1.5 模拟中间异常
-- 我来打断，嘿咻嘿咻！

-- 2 ls账户+500
update account set balance=balance+500 where name='ls';

-- 2.5 模拟中间状态，此时可以通过查询看看中间结果
-- select * from account;
-- 会发现数据已经变了

-- 3 发现执行没有问题，提交事务，执行成功
commit;

-- 3 有问题的话，就回滚事务，执行失败
rollback;

事务提交的两种方式：

1. 自动提交
   MySQL就是自动提交的，然而Oracle默认是手动提交的。
   一条DML（增删改）语句会自动提交一次事务。
2. 手动提交
   需要先开启事务，再提交

修改事务的默认提交方式：

1. 查看事务的默认提交方式：SELECT @@autocommit; – 1代表自动提交 0代表手动提交
2. 修改默认提交方式：set @@autocommit=0;

7.2 事务的四大特征

特征：

1. **原子性：**是不可分割的最小操作单位，要么同时成功，要么同时失败。
2. **持久性：**当事务提交或者回滚后，数据库会持久化地保存数据。
3. **隔离性：**多个事务之间，相互独立。但是，真实的情况下，事务之间是相互影响的，所以，我们要了解事务的隔离级别
4. **一致性：**事务操作前后，数据总量不变。

7.3 事务的隔离级别

概念：多个事务之间是隔离的，即相互独立的。但是，如果多个事务，操作同一批数据，则会引发一些问题，设置不同的隔离级别，就可以解决这些问题。

存在问题：

1. 脏读：一个事务，读取到另一个事务中没有提交的数据。
2. 不可重复读（虚读）：在同一个事务中，两次读取到的事务不一样。
3. 幻读：一个事务操作（DML）数据表中所有记录，A事务添加了一条数据，B事务查询不到修改。

数据库并发事务存在的问题

隔离级别：

1. read uncommited
   含义：读未提交
   产生的问题：脏读、不可重复读、幻读
2. read commited（Oracle中默认隔离级别）
   含义：读已提交
   产生的问题：不可重复读、幻读
3. repeatable read（MySQL中默认隔离级别）
   含义：可重复读
   产生问题：幻读
4. serializable
   含义：串行化（相当于把表锁上，A事物不提交，B事物是没法查询得到的）
   可以解决任何问题

注意：

隔离级别从小到大，安全性越来越高，但是效率越来越低。我们要选择合适级别，既保证相对比较安全，又能保证效率较高

修改数据库的默认隔离级别：

1. 查询隔离级别：
   mysql5.6-5.7：select @@tx_isolation;
   mysql8.0+：select @@transaction_isolation;
2. 设置隔离级别：set global transaction isolation level 级别字符串;

MySQL8.0+系统变量文档

MySQL5.6系统变量文档

修改隔离级别之后，查询，如果没有改变。需要断开连接，重新连接之后，才会查出来。

八、触发器

本篇内容是做课程设计时，需要用触发器，由此就来总结一下。参考文章

8.1 概念

定义：触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

作用：协助应用在数据库端确保数据的完整性。

举例，比如我现在有两个表，unit和person表，person与unit通过unitid进行外键约束。当创建person就需要在其对应的unit表中更新总人数字段，我们可以通过编写程序语言逻辑来实现，如果通过触发器，会自动更新字段，更容易了。

触发器的类型

1. INSERT型触发器：在执行insert、load data、replace语句时触发
2. UPDATE型触发器：在执行update语句时触发
3. DELETE型触发器：在执行delete、replace时触发

load data语句是将文件的内容插入到表中，相当于是insert语句

replace语句在一般的情况下和insert差不多，但是如果表中存在primary或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后再增加一条新的护具。

可以理解成，replace在没有重复的时候，相当于insert；若有重复，则会删除原来的，再执行insert

8.2 语法

要素

1. 触发器名称
2. 触发时机
   • BEFORE：之前
   • AFTER：之后
3. 触发事件
   • INSERT
   • UPDATE
   • DELETE
4. 表名：需要建立触发器的表名
5. 执行语句
   • 一条SQL语句
   • 多条SQL语句，通过begin和and包含起来

创建触发器

-- 一条执行语句
CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 表名 FOR EACH ROW 执行语句 

-- 多条执行语句
CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 表名 FOR EACH ROW
BEGIN
	执行语句列表 
END

{% asset_img 17.png %}

以上图为例，创建触发器，来实现person的数据发生变化时，修改unit表的总人数

CREATE TRIGGER insert_trigger AFTER INSERT ON person FOR EACH ROW
UPDATE unit AS a
JOIN ( SELECT count(*) sum, unitid FROM person GROUP BY unitid ) AS b ON a.unitid = b.unitid 
SET a.sum = b.sum;

CREATE TRIGGER delete_trigger AFTER DELETE ON person FOR EACH ROW
UPDATE unit AS a
JOIN ( SELECT count(*) sum, unitid FROM person GROUP BY unitid ) AS b ON a.unitid = b.unitid 
SET a.sum = b.sum;

在执行多条语句的时候，触发器中间的sql语句分支是用;来分割的。如果有多个触发器的话，最后也是需要加一个结束符号。而MySQL默认是以;作为结束执行语句，为避免与触发器的内部分支起冲突，我们可以通过使用DELIMITER $来将结束符号修改为$

一般用在命令行的时候会这样，直接使用可视化工具，可以不用写

下面这个触发器，是我在实际使用中，用到的，项目已开源，可以来看看

DELIMITER $

CREATE TRIGGER insert_trigger AFTER INSERT ON tb003 FOR EACH ROW
BEGIN
		UPDATE tb002 AS a
		JOIN ( SELECT unitaccnum, sum( basenumber ) sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
		SET a.basenumber = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, sum( unitmonpaysum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.unitpaysum = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, sum( permonpaysum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.perpaysum = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, count( accnum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.persnum = b.sum;
END $

CREATE TRIGGER delete_trigger AFTER DELETE ON tb003 FOR EACH ROW
BEGIN
		UPDATE tb002 AS a
		JOIN ( SELECT unitaccnum, sum( basenumber ) sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
		SET a.basenumber = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, sum( unitmonpaysum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.unitpaysum = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, sum( permonpaysum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.perpaysum = b.sum;
	UPDATE tb002 AS a
	JOIN ( SELECT unitaccnum, count( accnum ) AS sum FROM tb003 WHERE peraccstate = 0 GROUP BY unitaccnum ) AS b ON a.unitaccnum = b.unitaccnum 
	SET a.persnum = b.sum;
END $

DELIMITER ;

可以通过导出sql文件，来进行查看。

删除触发器

-- 删除触发器
DROP TRIGGER 触发器名称

定义变量

在begin…end中可以自定义变量，但是只能在begin…end中使用

语法

-- 在begin...and中定义变量
DECLARE
	变量名 变量类型 [默认值] 
	SET 变量名 =值

New&Old

NEW和OLD使用

1. NEW：表示将要或者已经增加（或修改或删除）的数据，用于INSERT、UPDATE型触发器
2. OLD：表示将要或者已经被删除的数据，用于UPDATE、DELETE型触发器

语法

-- 新增或修改行的某列数据
NEW.列名 

-- 删除或修改行的某列数据
OLD.列名

8.3 案例

需求

当在users中插入一条数据江厌离，就会在logs中生成一条日志信息江厌离 is created。

创建users表

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `add_time` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `name`(`name`(250)) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建logs表

DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '日志说明',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '日志表' ROW_FORMAT = Dynamic;

实现

DROP TRIGGER IF EXISTS `user_log`;
delimiter ;;
CREATE TRIGGER `user_log` AFTER INSERT ON `users` FOR EACH ROW begin
declare s1 varchar(40);
declare s2 varchar(40);
set s2=' is created';
set s1=concat(new.name,s2);
insert into logs(log) values(s1);
end
;;
delimiter ;

所有的触发器都存在information_schema数据库下的triggers表中