MySQL学习笔记（模块一）
什么是数据库
- 数据库(DataBase) 就是存储和管理数据的仓库
- 其本质是一个文件系统, 还是以文件的方式,将数据保存在电脑上
启动和关闭mysql服务命令
net start mysql57
net stop mysql57
通过命令登录数据库
SQL的分类
DDL操作数据库
查看/选择数据库
修改数据库字符集
删除数据库
快速创建一张表结构相同的表（复制表结构）
查看表
删除表
修改表名
修改表的字符集
向表中添加列，关键字 ADD
修改表中列的 数据类型或长度 ， 关键字 MODIFY
修改列名称 , 关键字 CHANGE
删除列 ，关键字 DROP
DML 操作表中数据
插入数据
修改数据
删除数据
清空表数据
两种删除表中所有数据做法比较
1. delete from 表名; 不推荐. 有多少条记录 就执行多少次删除操作. 效率低；
2. truncate table 表名: 推荐. 先删除整张表, 然后再重新创建一张一模一样的表. 效率高
MySQL DQL书写顺序和执行顺序
- 书写sql语句时各关键字的顺序：
select
from
where
group by
having
order by
- 执行顺序：
from
where
group by
having
select
order by
修改主键自增的起始值
默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式
-- 创建主键自增的表,自定义自增其实值 CREATE TABLE emp2( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), sex CHAR(1) )AUTO_INCREMENT=100; -- 插入数据,观察主键的起始值 INSERT INTO emp2(ename,sex) VALUES('张百万','男'); INSERT INTO emp2(ename,sex) VALUES('艳秋','女');
约束
- 包括唯一约束、主键约束、外键约束等，可以给列添加默认值，用default关键字。
事务及事务回滚
- 事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败
手动提交事务
- START TRANSACTION
- 这个语句显式地标记一个事务的起始点。
- COMMIT
- 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
- ROLLBACK
- 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态
手动提交事务流程
- 执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
- 执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
登录mysql，查看autocommit状态。
SHOW VARIABLES LIKE 'autocommit';
设置自动提交与取消自动提交
SET @@autocommit=off;
事务的四大特性
并发访问会产生的问题
设置隔离级别以及可以防止的并发问题，✔ 会出现问题，✘ 不会出现问题
查看隔离级别
设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
事务隔离级别总结
- serializable 串行化可以彻底解决幻读,但是 事务只能排队执行,严重影响效率,数据库不会使用这种隔离级别
什么是外键
- 外键指的是在 从表 中 与 主表 的主键对应的那个字段,比如员工表的 dept_id,就是外键
- 使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性
创建外键约束
1. 新建表时添加外键
2. 已有表添加外键
删除外键约束
级联删除操作
如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作
ON DELETE CASCADE
多表关系设计
第一范式1NF
- 原子性, 做到列不可拆分
- 第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。
第二范式2NF
- 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
- 一张表只能描述一件事
第三范式 3NF
- 消除传递依赖
- 表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放（最省空间原则）
数据库反三范式
- 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
- 浪费存储空间,节省查询时间 (以空间换时间)
三范式和反三范式总结
- 反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
- 浪费存储空间,节省查询时间 (以空间换时间)
常见的索引分类
索引的优缺点总结
- 添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
MySQL 视图
1. 视图是一种虚拟表。2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.4. 视图向用户提供基表数据的另一种表现形式
视图的作用
创建视图
create view 视图名 [column_list] as select语句;
MySQL 存储过程
- 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程优缺点
- 优点：
存储过程一旦调试完成后，就可以稳定运行，（前提是，业务需求要相对稳定，没有变化）存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，与数据库服务器不在同一个地区）
- 缺点：
在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较少使用，并且互联网行业需求变化较快也是原因之一尽量在简单的逻辑中使用，存储过程移植十分困难，数据库集群环境，保证各个库之间存储过程变更一致也十分困难。阿里的代码规范里也提出了禁止使用存储过程，存储过程维护起来的确麻烦；
创建简单的存储过程
DELIMITER $$ -- 声明语句结束符，可以自定义 一般使用$$ CREATE PROCEDURE 过程名称() -- 声明存储过程 BEGIN -- 开始编写存储过程 -- 要执行的操作 END $$ -- 存储过程结束
调用存储过程
call 存储过程名
IN 输入参数：表示调用者向存储过程传入值
CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型)
变量赋值
SET @变量名=值
OUT 输出参数：表示存储过程向调用者传出值
OUT 变量名 数据类型
存储过程案例
# 创建存储过程 接收参数插入数据, 并返回受影响的行数 DELIMITER $$ CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT, OUT out_num INT) BEGIN -- 执行插入操作 INSERT INTO orders VALUES(o_oid,o_gid,o_price); -- 设置 num的值为 1 SET @out_num = 1; -- 返回 out_num的值 SELECT @out_num; END $$
END $$
MySQL触发器
触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。——百度百科
触发器语法格式
delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误 CREATE TRIGGER Trigger_Name -- 触发器名，在一个数据库中触发器名是唯一的 before/after（insert/update/delete） -- 触发的时机 和 监视的事件 on table_Name -- 触发器所在的表 for each row -- 固定写法 叫做行触发器, 每一行受影响，触发事件都执行 begin -- begin和end之间写触发事件 end $ -- 结束标记
DCL
MySql默认使用的都是 root 用户，超级管理员，拥有全部的权限。除了root用户以外，我们还可以通过DCL语言来定义一些权限较小的用户, 分配不同的权限来管理和维护数据库。
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
用户授权
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
查看权限
SHOW GRANTS FOR '用户名'@'主机名';
删除用户
DROP USER '用户名'@'主机名';
命令行数据库数据备份和恢复
mysqldump -u 用户名 -p 密码 数据库 > 文件路径 source sql文件地址
