文章首页 Web开发 Windows开发 编程语言 数据库技术 移动平台 系统服务 微信 设计 布布扣 其他

MySQL

时间：2021-08-30 03:51:54   收藏：0   阅读：12

数据库

数据库是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。用来管理数据库的计算机系统称为数据库管理系统（DBMS）。

特点：

  1. 持久化存储数据，DB就是一个文件系统
  2. 方便存储和管理数据
  3. 使用了统一的方式操作数据库—SQL语言

MySQL

卸载

  1. 去mysql的安装目录找到my.ini文件，复制datadir="C:/ProgramData/MySQL/MySQL Server
  2. 在控制面板卸载MySQL
  3. 删除C:/ProgramData目录下的MySQL文件夹。

配置

  1. MySQL服务启动
    1. cmd–>services.msc 打开服务窗口
    2. 使用管理员打开cmd
      • net start mysql：启动MySQL服务
      • net stop mysql：关闭MySQL服务
  2. MySQL登录
    1. 本地的：mysql -uroot -p密码
    2. 远程的：mysql -hip地址 -uroot -p连接目标的密码
    3. mysql --host=ip --user=root --password=连接目标的密码
  3. MySQL退出
    1. exit
    2. quit
  4. MySQL目录结构
    1. 安装目录：basedir="D:/develop/MySQL/"，配置文件my.ini
    2. 数据目录：datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
  5. 数据库的备份和还原
    1. mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
    2. 还原
      1. 登录数据库
      2. 创建数据库
      3. 使用数据库
      4. 执行文件。source 文件路径

SQL

什么是SQL：Structured Query Language：结构化查询语言。其实就是定义了操作所有关系型数据库的规则，当然不同的数据库软件有各自的特殊语言，称为“方言”。

通用语法

  1. SQL语句可以单行或多行书写，以分号结尾。
  2. 可使用空格和缩进来增强语句的可读性。
  3. SQL语句不区分大小写，关键字建议使用大写。
  4. SQL语句由子句构成，有些子句是必需的，有些是可选的。
  5. 3 种注释
    • 单行注释: -- 注释内容 或 # 注释内容(mysql 特有)
    • 多行注释: /* 注释 */

SQL分类

  1. DDL(Data Definition Language)数据定义语言
    DDL主要用针对是数据库对象进行创建、修改和删除操作，主要包括
    1. CREATE：创建数据库对象
    2. ALTER：修改数据库对象
    3. DROP：删除数据库对象
  2. DML(Data Manipulation Language)数据操作语言
    DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：
    1. INSERT：增加数据
    2. UPDATE：修改数据
    3. DELETE：删除数据
  3. DQL(Data Query Language)数据查询语言
    DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。
  4. DCL(Data Control Language)数据控制语言
    DCL用来授予或回收访问数据库的权限。其主要包括：
    1. GRANT：授予用户某种权限
    2. REVOKE：回收授予的某种权限
  5. TCL(Transaction Control Language)事务控制语言
    用于数据库的事务管理。其主要包括：
    1. START TRANSACTION：开启事务
    2. COMMIT：提交事务
    3. ROLLBACK：回滚事务
    4. SET TRANSACTION：设置事务的属性

技术分享图片

操作数据库、表

操作数据库：

  1. Create：创建

    • 创建数据库
      create database 数据库名称;

    • 创建数据库，判断不存在，再创建
      create database if not exists 数据库名称;

    • 创建数据库，并指定字符集
      create database 数据库名称 character set 字符集名;

  2. Retrieve：取出

    • 查看所有数据库的名称
      show databases;
    • 查询某个数据库的创建语句
      show create database 数据库名称;

  3. Alter：修改

    • 修改数据库的字符集
      alter database 数据库名称 character set 字符集名称;

  4. Drop：删除

    • 删除数据库
      drop database 数据库名称;
    • 判断数据库存在，再删除
      drop database if exists 数据库名称;

  5. 使用数据库

    • 查询正在使用的数据库名称
      select database();
    • 使用数据库
      use 数据库名称;

操作表：

  1. C(Create):创建

    • 语法：

      create table 表名(

      ? 列名1 数据类型1,

      ? 列名2 数据类型2,

      ? ……

      ? 列名n 数据类型n

      );

    • 类型：

      1. 整型int
      2. 浮点型double
        height double(5,2);
      3. 日期date，只含有年月日，yyyy/MM/dd
      4. 日期datetime，含有年月日时分秒，yyyy/MM/dd HH:mm:ss
      5. 时间戳类型timestamp，包含年月日时分秒，此字段如果不赋值，或赋值为null，则默认使用系统当前时间自动赋值
      6. 字符串varchar
        name varchar(20)：保存姓名，最大20个字符（汉字算一个字符），字符串用单引号括起来，MySQL也可以用双引号

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

  2. R(Retrieve)：取出

    • 取出某个数据库中所有的表名
      show tables;
    • 查看表的创建语句
      show create table 表名;
    • 取出表的所有字段
      desc 表名; describe
      asc 表名; ascend升序，MySQL不支持

  3. Alter:修改

    • 修改表名
      rename table 表名 to 新的表名;
    • 修改表的字符集
      alter table 表名 character set 字符集名称;
    • 添加一列
      alter table 表名 add 列名 数据类型;
    • 添加一列放在最前面
      alter table 表名 add 列名 数据类型 first;
    • 添加一列放在gender列的后面
      alter table 表名 add 列名 数据类型 after gender;
    • 修改列名称 类型
      alter table 表名 change 列名 新列名 新数据类型;
      alter table 表名 modify 列名 新数据类型; 只改数据类型
    • 删除列
      alter table 表名 drop 列名;

  4. Drop:删除
    drop table 表名;
    drop table if exists 表名;

增删改表中数据

  1. INSERT添加数据

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

    • 注意

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

  2. DELETE删除数据

    • 语法
      delete from 表名 [where 条件]
    • 注意
      如果不加条件，则删除表中所有记录。
      如果要删除所有记录
      • delete from 表名; 不推荐，有多少条记录就会执行多少次删除操作
      • TRUNCATE TABLE 表名; 推荐，效率更高。先删除表，然后再创建一张一样的空表。

  3. UPDATE更新数据

    • 语法

      update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

      update student set name = ‘jack‘ where id = 5;

    • 注意
      如果不加任何条件，则会将表中所有记录全部修改。

查询表中数据

  1. 语法
    select
    字段列表
    from
    表名列表
    where
    条件列表
    group by
    分组字段
    having
    分组之后的条件
    order by
    排序
    limit
    分页限定
  2. 简单查询
    1. 多个字段的查询
      select 字段名1，字段名2... from 表名；
    2. 查询所有字段
      select * from 表名;
    3. 去除重复
      distinct
    4. 计算列
      一般可以使用四则运算计算一些列的值。null参与的运算，计算结果都为null
      ifnull(表达式1,表达式2)：如果表达式1是null，就把它替换成表达式2
    5. 起别名
      用as，as也可以省略
  3. 过滤
    1. where子句后跟条件
    2. 过滤条件
      1. 比较运算符
      2. BETWEEN...AND
      3. IN( 集合)
      4. IS NULL，判断某个值等于null不可以使用=
      5. IS NOT NULL
      6. and 或 &&
      7. or 或 ||
      8. not 或 !
    3. 模糊查询
      • 占位符
        • _:单个任意字符
          查询姓名第二个字是化的人：select * from student where name like “_化%”
        • %：多个任意字符
          查询名字中有德的人：select * from student where name like “%德%”;

DQL高级

  1. 排序查询
    语法：order by 子句
    order by 列名1 排序方式1,列名2 排序方式2...
    排序方式：asc升序，默认的，一般不指定。desc降序

  2. 聚合函数：将一个字段取出，对里面的数据进行计算

    1. count(field)：计算个数
      一般选非空的列：主键。因为会排除null值。
    2. max
    3. min
    4. sum
    5. avg

  3. 分组查询
    语法：group by field
    注意：分组之后可以查询的字段：分组字段、聚合函数

    • where 和 having 的区别：
      • where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
      • where 后不可以跟聚合函数，having可以进行聚合函数的判断
    -- 按照性别分组。分别查询男、女同学的平均分
select sex, avg(math) from student group by sex;
-- 按照性别分组。分别查询男、女同学的平均分,人数
select sex, avg(math), count(id) from student group by sex;
-- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
select sex,avg(math),count(id) from student where math > 70 group by sex;
-- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
select sex,avg(math),count(id) from student where math >70 group by sex having count(id)>2;

  4. 分页查询

    语法： limit 显示的行数 offset 开始的索引; MySQL的方言：limit 开始的索引, 显示的行数;
    公式：开始的索引=（当前页码 -1 )*每页显示的条数

    -- 显示名字的第一页，每页3行，下面三条结果相同
select name from student limit 0,3;
select name from student limit 3; 
select name from student limit 3 offset 0;

约束

约束就是对表中的数据进行限制，保证数据的正确性、有效性和完整性。

分类

  1. 非空约束：not null

    1. 创建表时添加约束

      CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL -- name为非空
);

    2. 创建表完后，添加非空约束
      ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

    3. 删除name的非空约束
      ALTER TABLE stu MODIFY NAME VARCHAR(20);

  2. 唯一约束：unique，某一列的值不能重复

    1. 唯一约束可以有NULL值，但是只能有一条记录为null

    2. 在创建表时，添加唯一约束

      CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE -- 手机号
);

    3. 删除唯一约束
      ALTER TABLE stu DROP INDEX phone_number;

    4. 在表创建完后，添加唯一约束
      ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

  3. 主键约束：primary key

    1. 注意

      • 含义：非空且唯一
      • 一张表只能有一个字段为主键
      • 主键就是表中记录的唯一标识

    2. 在创建表时，添加主键约束
      create table stu(
      id int primary key,-- 给id添加主键约束
      name varchar(20)
      );
      或者
      create table stu(
      id int ,
      name varchar(20),

      ? constraint stu_pk primary key (id)
      );

    3. 删除主键
      -- 错误 alter table stu modify id int ;
      ALTER TABLE stu DROP PRIMARY KEY;

    4. 创建完表后，添加主键
      ALTER TABLE stu MODIFY id INT PRIMARY KEY;

  4. 自动增长

    1. 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值的自动增长
    2. 在创建表时，添加主键约束，并且完成主键自增长
      create table stu(
      id int primary key auto_increment-- 给id添加主键约束
      );
    3. 删除自动增长
      ALTER TABLE stu MODIFY id INT;
    4. 添加自动增长
      ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

  5. 检查约束：check。限制某些字段的取值范围

    create table student{
	gender varchar(5) CHECK (gender in (‘男‘,‘女‘))
};
-- 也可以使用枚举
create table student{
	gender varchar(5) enum(‘男‘,‘女‘)
}

  6. 默认值约束：default

  7. 外键约束：foreign key，约束表与表之间的关系。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

    1. 在创建表时，可以添加外键
      create table 表名(
      ...
      constraint 外键名 foreign key (外键列名称) references 主表名称(主表列名称)
      );
    2. 删除外键
      ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
    3. 创建表之后，添加外键
      ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

  8. 级联操作

    1. 添加级联操作
      ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
    2. 分类：
      1. 级联更新：ON UPDATE CASCADE
      2. 级联删除：ON DELETE CASCADE，很危险，通常不用

多表查询

准备表

查询语法：

select 列名列表 from 表名列表 where....

如果从两个表名中查询某字段，返回两个表字段的笛卡尔积。下图看出笛卡尔积有很多重复内容，需要去除。

select * from emp,dept;

技术分享图片

多表查询分类

  1. 内连接查询：查询两表的交集

    1. 隐式内连接：使用where条件消除无用数据

      -- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id`=dept.`id`;
-- 查询员工表的名称，性别。部门表的名称
SELECT emp.`NAME`,emp.`gender`,dept.`NAME` FROM emp,dept WHERE emp.`dept_id`=dept.`id`;

-- 常用格式
SELECT 
	t1.name, -- 员工表的姓名
	t1.gender,-- 员工表的性别
	t2.name -- 部门表的名称
FROM
	emp t1,-- 起别名
	dept t2
WHERE 
	t1.`dept_id` = t2.`id`;

    2. 显式内连接
      select 字段列表 from 表名1 [inner] join 表名2 on 条件
      on子句称为连接条件，其实和where子句的过滤条件用法一样。

      SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
-- 连接三个及以上的表，注意多表连接时顺序随意
select 
	a.account_id, c.fed_id, e.name
from employee e inner join account a
	on e.emp_id = a.open_emp_id
	inner join customer c
	on a.cust_id = c.cust_id
where c.cust_type_cd = ‘B‘;

-- 自连接：查询员工对应的主管，因为主管也是员工，所以使用员工表两次
SELECT e.`name` ‘员工名字‘, mgr.`name` ‘主管名字‘
FROM employee e INNER JOIN employee mgr -- 员工表使用两次，需要起两个别名
ON e.`superior_emp_id`=mgr.`emp_id`;

  2. 外连接查询

    1. 左外连接：查询的是左表所有数据以及其交集部分。
      select 字段列表 from 表1 left [outer] join 表2 on 条件；

      -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

    2. 右外连接：查询的是右表所有数据以及其交集部分。
      select 字段列表 from 表1 right [outer] join 表2 on 条件；

      SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

  3. 子查询

    • 概念：查询中嵌套查询，称嵌套查询为子查询。

      -- 查询工资最高的员工信息
SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);

    • 子查询分类：

      • 子查询结果是单行单列的：查询可以作为条件，使用运算符去判断。

        -- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

      • 查询的结果是多行单列的：子查询可以作为条件，使用运算符in来判断

        -- 查询‘财务部‘和‘市场部‘所有的员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = ‘财务部‘ OR NAME = ‘市场部‘);

      • 子查询的结果是多行多列的：

视图

-- 视图创建
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
-- 查询视图创建语句
SHOW CREATE VIEW 视图名称;
-- 修改数据 。注意通过视图修改数据，会自动修改源表的数据
UPDATE 视图名称 SET 列名=值 WHERE 条件;
-- 修改视图表的结构
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;
-- 删除视图
DROP VIEW [IF EXISTS] 视图名称;

示例：

-- 查询001号部门的员工信息并生成视图
create view myview001
as
select empid,empname,job,empsalary from emp
where depid=001;
-- 查看视图
select * from myview001;

函数

SQL中的函数分为存储过程和存储函数。二者的区别是存储函数必须有返回值，存储过程可以没有返回值

函数的优点

创建存储过程

/*
	DELIMITER用来声明sql语句的分隔符，告诉MySQL该段命令已经结束！
	sql语句默认的分隔符是分号，但是有的时候我们需要多条sql语句，不希望分号作为结束标识。
	这个时候就可以使用DELIMITER来指定分隔符了！
*/
-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句;
END$

-- 修改分隔符为分号
DELIMITER ;

-- 修改分隔符为$
DELIMITER $

-- 创建存储过程，封装分组查询学生总成绩的sql语句
CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$

-- 修改分隔符为分号
DELIMITER ;

调用存储过程

-- 标准语法
CALL 存储过程名称(实际参数);

-- 调用stu_group存储过程
CALL stu_group();

查看存储过程

-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db=‘数据库名称‘;

删除存储过程

-- 标准语法
DROP PROCEDURE [IF EXISTS] 存储过程名称;

-- 删除stu_group存储过程
DROP PROCEDURE stu_group;

变量的使用

-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内

-- 定义一个int类型变量、并赋默认值为10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
	DECLARE num INT DEFAULT 10;   -- 定义变量
	SELECT num;                   -- 查询变量
END$

DELIMITER ;

-- 调用pro_test1存储过程
CALL pro_test1();

-- 标准语法
SET 变量名 = 变量值;

-- 定义字符串类型变量，并赋值
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	DECLARE NAME VARCHAR(10);   -- 定义变量
	SET NAME = ‘存储过程‘;       -- 为变量赋值
	SELECT NAME;                -- 查询变量
END$

DELIMITER ;

-- 调用pro_test2存储过程
CALL pro_test2();

-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

-- 定义两个int变量，用于存储男女同学的总分数
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE men,women INT;  -- 定义变量
	SELECT SUM(score) INTO men FROM student WHERE gender=‘男‘;    -- 计算男同学总分数赋值给men
	SELECT SUM(score) INTO women FROM student WHERE gender=‘女‘;  -- 计算女同学总分数赋值给women
	SELECT men,women;           -- 查询变量
END$

DELIMITER ;

-- 调用pro_test3存储过程
CALL pro_test3();

if语句的使用

-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

/*
	定义一个int变量，用于存储班级总成绩
	定义一个varchar变量，用于存储分数描述
	根据总成绩判断：
		380分及以上    学习优秀
		320 ~ 380     学习不错
		320以下       学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test4()
BEGIN
	-- 定义总分数变量
	DECLARE total INT;
	-- 定义分数描述变量
	DECLARE description VARCHAR(10);
	-- 为总分数变量赋值
	SELECT SUM(score) INTO total FROM student;
	-- 判断总分数
	IF total >= 380 THEN 
		SET description = ‘学习优秀‘;
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = ‘学习不错‘;
	ELSE 
		SET description = ‘学习一般‘;
	END IF;
	
	-- 查询总成绩和描述信息
	SELECT total,description;
END$

DELIMITER ;

-- 调用pro_test4存储过程
CALL pro_test4();

参数的传递

DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
	执行的sql语句;
END$
/*
	IN:代表输入参数，需要由调用者传递实际数据。默认的
	OUT:代表输出参数，该参数可以作为返回值
	INOUT:代表既可以作为输入参数，也可以作为输出参数
*/
DELIMITER ;

case语句的使用

-- 标准语法
CASE 表达式
WHEN 值1 THEN 执行sql语句1;
[WHEN 值2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;

-- 标准语法
CASE
WHEN 判断条件1 THEN 执行sql语句1;
[WHEN 判断条件2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;

/*
	输入总成绩变量，代表学生总成绩
	定义一个varchar变量，用于存储分数描述
	根据总成绩判断：
		380分及以上  学习优秀
		320 ~ 380    学习不错
		320以下      学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test7(IN total INT)
BEGIN
	-- 定义变量
	DECLARE description VARCHAR(10);
	-- 使用case判断
	CASE
	WHEN total >= 380 THEN
		SET description = ‘学习优秀‘;
	WHEN total >= 320 AND total < 380 THEN
		SET description = ‘学习不错‘;
	ELSE 
		SET description = ‘学习一般‘;
	END CASE;
	
	-- 查询分数描述信息
	SELECT description;
END$

DELIMITER ;

-- 调用pro_test7存储过程
CALL pro_test7(390);
CALL pro_test7((SELECT SUM(score) FROM student));

while循环

-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;

/*
	计算1~100之间的偶数和
*/
DELIMITER $

CREATE PROCEDURE pro_test8()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- while循环
	WHILE num <= 100 DO
		-- 偶数判断
		IF num%2=0 THEN
			SET result = result + num; -- 累加
		END IF;
		
		-- 让num+1
		SET num = num + 1;         
	END WHILE;
	
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;

-- 调用pro_test8存储过程
CALL pro_test8();

repeat循环

-- 标准语法
初始化语句;
REPEAT
	循环体语句;
	条件控制语句;
	UNTIL 条件判断语句
END REPEAT;

-- 注意：repeat循环是条件满足则停止。while循环是条件满足则执行

/*
	计算1~10之间的和
*/
DELIMITER $

CREATE PROCEDURE pro_test9()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- repeat循环
	REPEAT
		-- 累加
		SET result = result + num;
		-- 让num+1
		SET num = num + 1;
		
		-- 停止循环
		UNTIL num>10
	END REPEAT;
	
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;

-- 调用pro_test9存储过程
CALL pro_test9();

loop循环

-- 标准语法
初始化语句;
[循环名称:] LOOP
	条件判断语句
		[LEAVE 循环名称;]
	循环体语句;
	条件控制语句;
END LOOP 循环名称;

-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！
--      如果不加退出循环的语句，那么就变成了死循环。

/*
	计算1~10之间的和
*/
DELIMITER $

CREATE PROCEDURE pro_test10()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- loop循环
	l:LOOP
		-- 条件成立，停止循环
		IF num > 10 THEN
			LEAVE l;
		END IF;
	
		-- 累加
		SET result = result + num;
		-- 让num+1
		SET num = num + 1;
	END LOOP l;
	
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;

-- 调用pro_test10存储过程
CALL pro_test10();

游标

-- 创建stu_score表
CREATE TABLE stu_score(
	id INT PRIMARY KEY AUTO_INCREMENT,
	score INT
);

/*
	将student表中所有的成绩保存到stu_score表中
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
	-- 定义成绩变量
	DECLARE s_score INT;
	-- 创建游标,查询所有学生成绩数据
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	
	-- 开启游标
	OPEN stu_result;
	
	-- 使用游标，遍历结果,拿到第1行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第2行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第3行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第4行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 关闭游标
	CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test11存储过程
CALL pro_test11();

-- 查询stu_score表
SELECT * FROM stu_score;


-- ===========================================================
/*
	出现的问题：
		student表中一共有4条数据，我们在游标遍历了4次，没有问题！
		但是在游标中多遍历几次呢？就会出现问题
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
	-- 定义成绩变量
	DECLARE s_score INT;
	-- 创建游标,查询所有学生成绩数据
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	
	-- 开启游标
	OPEN stu_result;
	
	-- 使用游标，遍历结果,拿到第1行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第2行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第3行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第4行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第5行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 关闭游标
	CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test11存储过程
CALL pro_test11();

-- 查询stu_score表,虽然数据正确，但是在执行存储过程时会报错
SELECT * FROM stu_score;

/*
	当游标结束后，会触发游标结束事件。我们可以通过这一特性来完成循环操作
	加标记思想：
		1.定义一个变量，默认值为0(意味着有数据)
		2.当游标结束后，将变量值改为1(意味着没有数据了)
*/
-- 1.定义一个变量，默认值为0(意味着有数据)
DECLARE flag INT DEFAULT 0;
-- 2.当游标结束后，将变量值改为1(意味着没有数据了)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

/*
	将student表中所有的成绩保存到stu_score表中
*/
DELIMITER $

CREATE PROCEDURE pro_test12()
BEGIN
	-- 定义成绩变量
	DECLARE s_score INT;
	-- 定义标记变量
	DECLARE flag INT DEFAULT 0;
	-- 创建游标，查询所有学生成绩数据
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	-- 游标结束后，将标记变量改为1
	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
	
	-- 开启游标
	OPEN stu_result;
	
	-- 循环使用游标
	REPEAT
		-- 使用游标，遍历结果,拿到数据
		FETCH stu_result INTO s_score;
		-- 将数据保存到stu_score表中
		INSERT INTO stu_score VALUES (NULL,s_score);
	UNTIL flag=1
	END REPEAT;
	
	-- 关闭游标
	CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test12存储过程
CALL pro_test12();

-- 查询stu_score表
SELECT * FROM stu_score;

存储函数

/*
	定义存储函数，获取学生表中成绩大于95分的学生数量
*/
DELIMITER $

CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
	-- 定义统计变量
	DECLARE result INT;
	-- 查询成绩大于95分的学生数量，给统计变量赋值
	SELECT COUNT(*) INTO result FROM student WHERE score > 95;
	-- 返回统计结果
	RETURN result;
END$

DELIMITER ;

-- 调用fun_test1存储函数
SELECT fun_test1();

触发器

触发器类型 OLD的含义 NEW的含义
INSERT 型触发器 无 (因为插入前状态无数据) NEW 表示将要或者已经新增的数据
UPDATE 型触发器 OLD 表示修改之前的数据 NEW 表示将要或已经修改后的数据
DELETE 型触发器 OLD 表示将要或者已经删除的数据 无 (因为删除后状态无数据)

创建触发器

DELIMITER $

CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
	触发器要执行的功能;
END$

DELIMITER ;

查看触发器

-- 标准语法
SHOW TRIGGERS;

-- 查看触发器
SHOW TRIGGERS;

删除触发器

-- 标准语法
DROP TRIGGER 触发器名称;

-- 删除DELETE触发器
DROP TRIGGER account_delete;

触发器的总结

事务

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

操作：

  1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
  2. 执行sql语句：执行具体的一条或多条sql语句
  3. 结束事务(提交|回滚)
    • 提交：没出现问题，数据进行更新
    • 回滚：出现问题，数据恢复到开启事务时的状态

例子：

CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES (‘zhangsan‘, 1000), (‘lisi‘, 1000);
INSERT INTO account (NAME, balance) VALUES (‘lisi‘, 1000), (‘lisi‘, 1000);
-- 张三向李四转账500
-- 开启事务，通知执行账户的增减
START TRANSACTION;
UPDATE account SET balance = balance - 500 WHERE NAME = ‘zhangsan‘;
UPDATE account SET balance = balance + 500 WHERE NAME = ‘lisi‘;
-- 发现执行没有问题，提交事务
COMMIT;
-- 发现出问题了，回滚事务
ROLLBACK;

事务的提交方式

MySQL数据库中事务默认自动提交。就是每执行一条语句就提交一次。Oracle默认手动提交，需要先开启事务，再commit。

修改事务默认提交方式：

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

事务的特征

  1. 原子性(atomicity)
    • 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
  2. 一致性(consistency)
    • 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
    • 拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性
  3. 隔离性(isolcation)
    • 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
  4. 持久性(durability)
    • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

事务的隔离级别

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

存在问题：

  1. 脏读：一个事务，读取到另一个事务中没有提交的数据
  2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
  3. 幻读：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功

隔离级别：

  1. read uncommitted：读未提交。产生的问题：脏读、不可重复读、幻读
  2. read committed：读已提交（Oracle）。产生的问题：不可重复读、幻读
  3. repeatable read：可重复读（MySQL默认）。产生的问题：幻读
  4. serializable：串行化。可以解决所有的问题
  5. 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
  6. 数据库查询隔离级别：select @@tx_isolation;
  7. 数据库设置隔离级别：set global transaction isolation level 级别字符串;

存储引擎

MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力，在MySQL中 , 将这些不同的技术及配套的功能称为存储引擎。

在关系型数据库中数据的存储是以表的形式存进行储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

Oracle , SqlServer等数据库只有一种存储引擎 , 而MySQL针对不同的需求, 配置MySQL的不同的存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能。

通过选择不同的引擎 ,能够获取最佳的方案 , 也能够获得额外的速度或者功能，提高程序的整体效果。所以了解引擎的特性 , 才能贴合我们的需求 , 更好的发挥数据库的性能。

MySQL5.7支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等

其中较为常用的有三种：InnoDB、MyISAM、MEMORY

特性 MyISAM InnoDB MEMORY
存储限制 有(平台对文件系统大小的限制) 64TB 有(平台的内存限制)
事务安全 不支持 支持 不支持
锁机制 表锁 表锁/行锁 表锁
B+Tree索引 支持 支持 支持
哈希索引 不支持 不支持 支持
全文索引 支持 支持 不支持
集群索引 不支持 支持 不支持
数据索引 不支持 支持 支持
数据缓存 不支持 支持 N/A
索引缓存 支持 支持 N/A
数据可压缩 支持 不支持 不支持
空间使用 低 高 N/A
内存使用 低 高 中等
批量插入速度 高 低 高
外键 不支持 支持 不支持

引擎操作

-- 标准语法
SHOW ENGINES;

-- 查询数据库支持的存储引擎
SHOW ENGINES;

-- 表含义:
  - support : 指服务器是否支持该存储引擎
  - transactions : 指存储引擎是否支持事务
  - XA : 指存储引擎是否支持分布式事务处理
  - Savepoints : 指存储引擎是否支持保存点

-- 标准语法
SHOW TABLE STATUS FROM 数据库名称;

-- 查看db9数据库所有表的存储引擎
SHOW TABLE STATUS FROM db9;

-- 标准语法
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = ‘数据表名称‘;

-- 查看db9数据库中stu_score表的存储引擎
SHOW TABLE STATUS FROM db9 WHERE NAME = ‘stu_score‘;

-- 标准语法
CREATE TABLE 表名(
	列名,数据类型,
    ...
)ENGINE = 引擎名称;

-- 创建db11数据库
CREATE DATABASE db11;

-- 使用db11数据库
USE db11;

-- 创建engine_test表，指定存储引擎为MyISAM
CREATE TABLE engine_test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
)ENGINE = MYISAM;

-- 查询engine_test表的引擎
SHOW TABLE STATUS FROM db11 WHERE NAME = ‘engine_test‘;

-- 标准语法
ALTER TABLE 表名 ENGINE = 引擎名称;

-- 修改engine_test表的引擎为InnoDB
ALTER TABLE engine_test ENGINE = INNODB;

-- 查询engine_test表的引擎
SHOW TABLE STATUS FROM db11 WHERE NAME = ‘engine_test‘;

引擎的选择

索引

概念

技术分享图片

分类

索引的操作

-- 创建db12数据库
CREATE DATABASE db12;

-- 使用db12数据库
USE db12;

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT,
	score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,‘张三‘,23,98),(NULL,‘李四‘,24,95),
(NULL,‘王五‘,25,96),(NULL,‘赵六‘,26,94),(NULL,‘周七‘,27,99);

-- 标准语法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]  -- 默认是B+TREE
ON 表名(列名...);

-- 为student表中姓名列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);

-- 为student表中年龄列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);

-- 标准语法
SHOW INDEX FROM 表名;

-- 查看student表中的索引
SHOW INDEX FROM student;

-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

-- 为student表中name列添加全文索引
ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);

-- 查看student表中的索引
SHOW INDEX FROM student;

-- 标准语法
DROP INDEX 索引名称 ON 表名;

-- 删除student表中的idx_score索引
DROP INDEX idx_score ON student;

-- 查看student表中的索引
SHOW INDEX FROM student;

索引效率的测试

-- 创建product商品表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品id
	NAME VARCHAR(10),		    -- 商品名称
	price INT                           -- 商品价格
);

-- 定义存储函数，生成长度为10的随机字符串并返回
DELIMITER $

CREATE FUNCTION rand_string() 
RETURNS VARCHAR(255)
BEGIN
	DECLARE big_str VARCHAR(100) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ‘;
	DECLARE small_str VARCHAR(255) DEFAULT ‘‘;
	DECLARE i INT DEFAULT 1;
	
	WHILE i <= 10 DO
		SET small_str =CONCAT(small_str,SUBSTRING(big_str,FLOOR(1+RAND()*52),1));
		SET i=i+1;
	END WHILE;
	
	RETURN small_str;
END$

DELIMITER ;



-- 定义存储过程，添加100万条数据到product表中
DELIMITER $

CREATE PROCEDURE pro_test()
BEGIN
	DECLARE num INT DEFAULT 1;
	
	WHILE num <= 1000000 DO
		INSERT INTO product VALUES (NULL,rand_string(),num);
		SET num = num + 1;
	END WHILE;
END$

DELIMITER ;

-- 调用存储过程
CALL pro_test();


-- 查询总记录条数
SELECT COUNT(*) FROM product;



-- 查询product表的索引
SHOW INDEX FROM product;

-- 查询name为OkIKDLVwtG的数据   (0.049)
SELECT * FROM product WHERE NAME=‘OkIKDLVwtG‘;

-- 通过id列查询OkIKDLVwtG的数据  (1毫秒)
SELECT * FROM product WHERE id=999998;

-- 为name列添加索引
ALTER TABLE product ADD INDEX idx_name(NAME);

-- 查询name为OkIKDLVwtG的数据   (0.001)
SELECT * FROM product WHERE NAME=‘OkIKDLVwtG‘;


/*
	范围查询
*/
-- 查询价格为800~1000之间的所有数据 (0.052)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;

/*
	排序查询
*/
-- 查询价格为800~1000之间的所有数据,降序排列  (0.083)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;

-- 为price列添加索引
ALTER TABLE product ADD INDEX idx_price(price);

-- 查询价格为800~1000之间的所有数据 (0.011)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;

-- 查询价格为800~1000之间的所有数据,降序排列  (0.001)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;

索引的实现原则

磁盘存储
BTree

查找顺序：

模拟查找15的过程 : 

1.根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
	比较关键字15在区间（<17），找到磁盘块1的指针P1。
2.P1指针找到磁盘块2，读入内存。【磁盘I/O操作第2次】
	比较关键字15在区间（>12），找到磁盘块2的指针P3。
3.P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】
	在磁盘块7中找到关键字15。
	
-- 分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。
-- 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree使用较少的节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。
B+Tree

技术分享图片

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在24层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。

总结：索引的设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，
对列name列、address和列phone列建一个联合索引

ALTER TABLE user ADD INDEX index_three(name,address,phone);

联合索引index_three实际建立了(name)、(name,address)、(name,address,phone)三个索引。所以下面的三个SQL语句都可以命中索引。

SELECT * FROM user WHERE address = ‘北京‘ AND phone = ‘12345‘ AND name = ‘张三‘;
SELECT * FROM user WHERE name = ‘张三‘ AND address = ‘北京‘;
SELECT * FROM user WHERE name = ‘张三‘;

上面三个查询语句执行时会依照最左前缀匹配原则，检索时分别会使用索引

(name,address,phone)
(name,address)
(name)

进行数据匹配。

索引的字段可以是任意顺序的，如：

-- 优化器会帮助我们调整顺序，下面的SQL语句都可以命中索引
SELECT * FROM user WHERE address = ‘北京‘ AND phone = ‘12345‘ AND name = ‘张三‘;

Mysql的优化器会帮助我们调整where条件中的顺序，以匹配我们建立的索引。

联合索引中最左边的列不包含在条件查询中，所以根据上面的原则，下面的SQL语句就不会命中索引。

-- 联合索引中最左边的列不包含在条件查询中，下面的SQL语句就不会命中索引
SELECT * FROM user WHERE address = ‘北京‘ AND phone = ‘12345‘;

锁

概念

分类

存储引擎 表级锁 行级锁 页级锁
MyISAM 支持 不支持 不支持
InnoDB 支持 支持 不支持
MEMORY 支持 不支持 不支持
BDB 支持 不支持 支持

InnoDB锁

-- 创建db13数据库
CREATE DATABASE db13;

-- 使用db13数据库
USE db13;

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT,
	score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,‘张三‘,23,99),(NULL,‘李四‘,24,95),
(NULL,‘王五‘,25,98),(NULL,‘赵六‘,26,97);

-- 标准语法
SELECT语句 LOCK IN SHARE MODE;

-- 窗口1
/*
	共享锁：数据可以被多个事务查询，但是不能修改
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录。加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询分数为99分的数据记录。加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;

-- 窗口2
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询，可以查询)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(可以查询。共享锁和共享锁兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id为1的姓名为张三三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME=‘张三三‘ WHERE id = 1;

-- 修改id为2的姓名为李四四(修改成功，InnoDB引擎默认是行锁)
UPDATE student SET NAME=‘李四四‘ WHERE id = 2;

-- 修改id为3的姓名为王五五(注意：InnoDB引擎如果不采用带索引的列。则会提升为表锁)
UPDATE student SET NAME=‘王五五‘ WHERE id = 3;

-- 提交事务
COMMIT;

-- 标准语法
SELECT语句 FOR UPDATE;

-- 窗口1
/*
	排他锁：加锁的数据，不能被其他事务加锁查询或修改
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录，并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 提交事务
COMMIT;

-- 窗口2
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询id为1的数据记录，并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id为1的姓名为张三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME=‘张三‘ WHERE id=1;

-- 提交事务
COMMIT;

MyISAM锁

-- 创建product表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	price INT
)ENGINE = MYISAM;  -- 指定存储引擎为MyISAM

-- 添加数据
INSERT INTO product VALUES (NULL,‘华为手机‘,4999),(NULL,‘小米手机‘,2999),
(NULL,‘苹果‘,8999),(NULL,‘中兴‘,1999);

-- 标准语法
-- 加锁
LOCK TABLE 表名 READ;

-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

-- 窗口1
/*
	读锁：所有连接只能读取数据，不能修改
*/
-- 为product表加入读锁
LOCK TABLE product READ;

-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改华为手机的价格为5999(修改失败)
UPDATE product SET price=5999 WHERE id=1;

-- 解锁
UNLOCK TABLES;

-- 窗口2
-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改华为手机的价格为5999(不能修改，窗口1解锁后才能修改成功)
UPDATE product SET price=5999 WHERE id=1;

-- 标准语法
-- 加锁
LOCK TABLE 表名 WRITE;

-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

-- 窗口1
/*
	写锁：其他连接不能查询和修改数据
*/
-- 为product表添加写锁
LOCK TABLE product WRITE;

-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改小米手机的金额为3999(修改成功)
UPDATE product SET price=3999 WHERE id=2;

-- 解锁
UNLOCK TABLES;

-- 窗口2
-- 查询product表(不能查询。只有窗口1解锁后才能查询成功)
SELECT * FROM product;

-- 修改小米手机的金额为2999(不能修改。只有窗口1解锁后才能修改成功)
UPDATE product SET price=2999 WHERE id=2;

悲观锁和乐观锁

锁的总结

DCL管理用户

  1. 管理用户

    1. 添加用户：
      ATE USER ‘用户名‘@‘主机名‘ IDENTIFIED BY ‘密码‘;

    2. 删除用户：
      DROP USER ‘用户名‘@‘主机名‘;

    3. 修改用户

      1. 修改用户密码：

        -- UPDATE USER SET PASSWORD = PASSWORD(‘新密码‘) WHERE USER = ‘用户名‘;
UPDATE USER SET PASSWORD = PASSWORD(‘abc‘) WHERE USER = ‘lisi‘;
-- SET PASSWORD FOR ‘用户名‘@‘主机名‘ = PASSWORD(‘新密码‘);
SET PASSWORD FOR ‘root‘@‘localhost‘ = PASSWORD(‘123‘);

        忘记root用户的密码怎么办？

        1. 管理员运行cmd -- > net stop mysql 停止mysql服务
        2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
        3. 打开新的cmd窗口,直接输入mysql命令，敲回车，就可以登录了
        4. use mysql; 所有的用户信息都保存在mysql库的user表中
        5. update user set password = password(‘你的新密码‘) where user = ‘root‘;
        6. 关闭两个窗口
        7. 打开任务管理器，手动结束mysqld.exe 的进程
        8. 启动mysql服务，使用新密码登录。

    4. 查询用户
      use mysql;
      SELECT * FROM USER;

  2. 权限管理：

    1. 查询权限：
      SHOW GRANTS FOR ‘用户名‘@‘主机名‘;
      SHOW GRANTS FOR ‘user01‘@‘%‘;

    2. 授予权限：

    grant 权限列表 on 数据库名.表名 to ‘用户名‘@‘主机名‘;

grant select on db3.account to ‘zhangsan’@‘localhost’;

-- 给张三用户授予所有权限，在任意数据库任意表上

GRANT ALL ON \*.\* TO ‘zhangsan‘@‘localhost‘;
    1. 撤销权限：
    revoke 权限列表 on 数据库名.表名 from ‘用户名‘@‘主机名‘;
REVOKE UPDATE ON db3.`account` FROM ‘lisi‘@‘%‘;

原文：https://www.cnblogs.com/yellowchives/p/15202924.html

评论（0）
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣，分享程序人生！