当前位置：Gxlcms > 数据库问题 > 2_MySQL

2_MySQL

时间：2021-07-01 10:21:17 帮助过：6人阅读

MySQL

关系型数据库

1970s - E.F.Codd - IBM研究员 - System R
理论基础：关系代数和集合论
具体表象：用二维表来保存数据
- 行：一条记录 - 例如：学生的信息
- 列：一个字段 - 例如：学生的某个属性（姓名、年龄、性别等）
- 主键列：能够唯一标识一条记录的列，例如：学生的学号
编程语言：SQL - 结构化查询语言
- DDL - 数据定义语言 - 建立库、二维表等使用
  - create - 创建
  - drop - 丢弃/删除
  - alter - 修改
- DML - 数据操作语言 - 库、二维表里的数据进行增、删、改、查
  - insert / delete / update / select
- DCL - 数据控制语言 - 指定用户权限，分配权限
  - grant - 赋予权限
  - revoke - 召回权限

关系型数据库保证数据的完整性和一致性：

完整性：
- 实体完整性：每一个实体，都是独一无二的，没有冗余 - 主键/唯一索引
- 参照完整性：外键
- 域完整性：存储的数据都是有效的数据 - 数据类型/数据长度/非空约束/默认值约束/检查约束（mysql不支持）

Window下安装

解压：下载的zip文件解压，将解压之后的文件夹放到任意目录下，这个目录就是mysql的安装目录。
配置：打开目录，会看到my-default.ini配置文件，复制这个配置文件可以重命名为my.ini或者my.cnf

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=C:\Program Files\mysql-5.6.39-winx64 
# 设置mysql数据库的数据的存放目录
datadir=C:\Program Files\mysql-5.6.39-winx64\data 
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

环境变量：在系统变量PATH后面添加: 你的mysql bin文件夹的路径（如C:\Program Files\mysql-5.6.41-winx64\bin）
安装MySQL服务：以管理员身份打开cmd窗口后，将目录切换到你解压文件的bin目录，输入mysqld install回车运行
启动mysql服务：以管理员身份在cmd中输入:net start mysql
服务启动成功之后，就可以登录了，输入mysql -u root -p（第一次登录没有密码，直接按回车过）
- net stop mysql # 停止mysql

? 注意：在windows操作系统上没有重启mysql服务的命令，如果要重启服务，只能先stop再start

启动客户端连接server
- mysql -uroot -p 无密码
- mysql -uroot -p123 有密码
- mysql -uroot -p123 -h 192.138.14.12 连接到指定ip的MySQL服务器（远程登录）

MySQL时间配置

1，查看当前时间，时区

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-07-12 15:02:48 |
+---------------------+
1 row in set (0.00 sec)
　　
mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

-- time_zone说明mysql使用system的时区，system_time_zone说明system使用UTC时区

2，修改时区

mysql> set global time_zone = ‘+8:00‘;
Query OK, 0 rows affected (0.00 sec)
mysql> set time_zone = ‘+8:00‘;
Query OK, 0 rows affected (0.00 sec)

3，设置重启永久生效

修改配置文件/etc/my.cnf或my.ini

[mysqld]
default-time_zone = ‘+8:00‘

常用命令

用户相关命令

select user(); 查看当前登录的用户
set password = password(‘123’); 给当前用户设置密码

创建用户

create user ‘Jack’@’192.138.12.14’ 指定某机器可以通过用户Jack连接
create user ‘Jack’@’192.138.12.%’ 指定某网段可以通过用户Jack连接
create user ‘Jack’@’%’ 指定所有机器可以通过用户Jack连接

创建用户并设置密码

create user ‘Jack’@’192.138.12.14’ identified by ‘123’ 给Jack设置密码为123

用户授权

show grants for ‘Jack’@’192.138.12.%’ - 查看某个用户的权限
grant 权限类型 on 数据库.* to ‘Jack’@’192.138.12.%’ - 数据库下的所有表都可以使用
grant 权限类型 on . to ‘Jack’@’192.138.12.%’ -所有数据库下的所有表
grant 权限类型 on . to ‘Jack’@’192.138.12.%’ identified by ‘123’ -创建用户设置密码并授权使用所有数据库下的所有表

权限类型

all 所有权限
select 查询权限
select,insert 查和改

召回权限

召回用户对数据库hrs的增、删、改权限，只能查询
-revoke insert,delete,update on hrs.* from ‘ljjadmins‘@‘%‘

刷新

flush privileges; 刷新使授权立即生效

数据库相关命令

查看当前数据库

select database();

查看所有数据库

show databases;

创建一个数据库

create database 数据库名;

切换数据库

use 数据库名;

查看数据库表

show tables;

删除数据库(慎用)

drop database 数据库名;

表相关命令

创建表

create table 表名(字段名1 类型[(宽度) 约束条件], tb_id int,name char(16) );

查看表结构

desc 表名; (describe的缩写)可以看到字段\类型\长度，看不到编码\引擎\约束信息(比较直观)
show create table 表名 [/G];能查看到字段\类型\长度\编码\引擎(比较全面)

删除表

drop table 表名;

存储引擎

存储数据的方式
一张表：
- 数据
- 表结构
- 索引(查询的时候使用一个目录结构)

mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。

数据存储在硬盘上 ==> 数据持久化存储

Innodb存储引擎(mysql5.6默认存储引擎)

特点：

支持事务：为了保证数据的完整性，将多个操作变成原子性操作：保证数据安全
支持行级锁：修改的行少的时候使用：修改数据频繁的操作
支持表级锁：修改的行多的时候使用：大量数据同时修改
支持外键：约束两张表中的关联字段不能随意添加/删除：降低数据增删改查的出错率。

数据和索引存储在一起 - 2个文件

数据索引/表结构

Myisam存储引擎(mysql5.5之前默认存储引擎)

特点：

只支持表级锁

数据和索引不存储在一起 - 3个文件

数据/索引/表结构

Memory存储引擎

特点：

都不支持

数据存储在内存中，数据断电消失 - 1个文件

表结构

面试题

你了解mysql的存储引擎吗？
了解一些，Innodb、Myisam、Memory
你的项目用了什么存储引擎，为什么？
Innodb
多个用户操作的过程中对同一张表的数据同时做修改
Innodb支持行级锁，所以使用了这个存储引擎
为了适应程序未来的扩展性，扩展新功能的时候可能会用到...涉及到要维护数据的完整性
项目中有一两张xx xx表，之间的外键关系是...，一张表的修改或删除比较频繁，怕出错所以使用外键约束。

查看当前的默认存储引擎

show variables like “default_storage_engine”;

查看当前数据库的存储引擎

show engines;

创建表时指定存储引擎

create table ai(id bigint(12),name varchar(128)) ENGINE=MYISAM;
create table country(id int(4),cname varchar(64)) ENGINE=InnoDB;
也可以使用alter table修改已存在的表的存储引擎
alter table ai ending = innodb;

Mysql的数据类型

数字类型

整数：

tinyint : 1字节(8位) 小整数(默认有符号) 无符号unsigned约束
smallint : 2字节(16位) 大整数(默认有符号) 无符号unsigned约束
mediumint : 3字节(24位) 大整数(默认有符号) 无符号unsigned约束
int或integer : 4字节(32位) 大整数(默认有符号) 无符号unsigned约束
bigint : 8字节(64位)

浮点型：

float ：4字节单精度浮点数值 float(5,2) 表示总共5位，保留两位小数 153.25 (四舍五入)
double ：8字节双精度浮点数值 double(10,3) 表示总共10位，保留三位小数 1531234.252 (四舍五入)
decimal ：小数值，精确到小数点后第30位默认decimal(10,0)十位整数，

字符串

char ：0-255字节定长字符串多余的补空格浪费空间，节省时间（常用）
varchar ：0-65535字节变长字符串后面默认加字符的长度，节省空间，存取速度慢
tinytext ：0-255字节短文本字符串
text ： 0-65535字节长文本字符串
longtext ：0-4294967295字节极大文本数据

时间

date：三字节 20200613 格式：YYYY-MM-DD
用途：生日（出生日期），入职日期，生产日期
time：三字节 164252 格式HH:MM:SS
用途：课程表，比赛时长
datetime：八字节 20200613164252 格式YYYY-MM-DD HH:MM:SS
用途：上班打卡，日志，记录用户访问时间
year：一字节 1901-2155 格式：YYYY
timestamp：四字节 1970-01-01 00:00:00 到2038 格式YYYYMMDDHHMMSS；

默认约束：
- NOT NULL # 非空约束
- DEFAULT CURRENT_TIMESTAMP # 默认当前时间
- ON UPDATE CURRENT_TIMESTAMP # 更新时使用当前时间自动更新,如果想让其他时间都拥有自动填写和更新可以将约束写成一样的。

enum和set

enum ：单选行为
- enum(‘male’,’female’) 约束必须从两个或多个中选择一个，比如：性别，国旗...
set ：多选行为
set(‘orange’,’apple’,’banana’,’powl’) 约束必须选择多个
insert into tb (fruits) values (‘orange,apple,banana’) 插入时写在一个引号中，用逗号隔开

表的完整性约束

无符号：int unsigned - unsigned约束前面的int位无符号
不为空：not null - 可能会无效，设置严格模式如下
默认值： default - 默认设置，性别默认为：default ‘male’,
唯一约束：unique - 约束为不可重复，用于id，唯一性标识（但可以写入多个Null值）
- 联合唯一约束：unique()
  - unique (id,id_card) 联合约束，如果id和id_card同时相同会报错，如果单个重复可以写入。
主键： primary key ，如果没有定义主键，定义非空+唯一的第一个字段会自动成为这张表的主键(primary) 一张表只有一个主键。
- 联合主键：primary key()
  - primary key(id, id_card) 联合主键，两个键都不能为空，不能重复
自增：auto_increment
- 只对数字有效，自带非空约束
- 至少是unique约束之后才可以使用auto_increment
外键：foreign key (本表字段) references 外表(外表字段)
- 外表字段必须至少是唯一的
- on update cascade 级联修改 - on delete update 级联删除
  - . cascade方式，父表删除/更新时，子表跟着删除/更新
  - . set null 方式，父表删除/更新时，子表匹配列设为null，子表外键烈不能为not null
  - . no action 方式，子表中有匹配记录，则不允许父表删除/更新
  - . restrict 方式，同no action
  - . set default 方式，父表变更，子表外键列设置成一个默认值，但innodb不能识别

补充：not null非空约束无效时（可以插入null时）
- 设置严格模式：
  - 不支持对not null字段插入null值
  - 不支持对自增长字段插入”值
  - 不支持text字段有默认值
- 直接在mysql中生效(重启失效):
  mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
- 配置文件添加(永久失效)：
  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

修改表结构

1.修改表名

alter table 表名 rename 新表名;

2.增加字段

alter table 表名
- add 字段名数据类型 [完整性约束条件…],
  - add 字段名数据类型 [完整性约束条件…];

3.删除字段

alter table 表名 drop 字段名;

4.修改字段

alter table 表名 modify 字段名数据类型 [完整性约束条件…];
alter table 表名 change 旧字段名新字段名旧数据类型 [完整性约束条件…];
alter table 表名 change 旧字段名新字段名新数据类型 [完整性约束条件…];

5.修改字段排列顺序/在增加的时候指定字段位置

alter table 表名 add 字段名数据类型 [完整性约束条件…] first; # 添加到第一个
alter table 表名 add 字段名数据类型 [完整性约束条件…] after 字段名; # 添加到某个字段之后
alter table 表名 change 字段名旧字段名新字段名新数据类型 [完整性约束条件…] first;
alter table 表名 modify 字段名数据类型 [完整性约束条件…] after 字段名;

6.alter操作非空和唯一（了解）

create table t(id int unique,name char(10) not null);

去掉null约束

alter table t modify name char(10) null;

添加null约束

alter table t modify name char(10) not null;

去掉unique约束

alter table t drop index id;

添加unique约束

alter table t modify id int unique;

添加联合唯一

alter table t add unique index(aa,bb);

7.alter操作主键（了解）

首先创建一个数据表table_test：
create table table_test(`id` varchar(100) NOT NULL,`name` varchar(100) NOT NULL,PRIMARY KEY (`name`));
如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
先删除主键
- alter table table_test drop primary key;
然后再增加主键
- alter table table_test add primary key(id);

注:在增加主键之前,必须先把反复的id删除掉。

8.为表添加外键

创建press表

CREATE TABLE `press` (`id` int(11) NOT NULL,`name` char(10) DEFAULT NULL,PRIMARY KEY (`id`)) ;

创建book表

CREATE TABLE `book` (`id` int(11) DEFAULT NULL,`bk_name` char(12) DEFAULT NULL,`press_id` int(11) NOT NULL,KEY `press_id` (`press_id`)) ;

为book表添加外键

alter table book add constraint fk_id foreign key(press_id) references press(id);

删除外键

alter table book drop foreign key fk_id;

多表结构的创建和分析

一对多
一对一
多对一

操作数据

插入数据

insert into 表名 values (值1，值2);和表名中的字段对应
insert into 表名 values (‘值’，’值’),(‘值’，’值’),(‘值’，’值’)...;写入多条数据
insert into 表名 (字段1,字段2) values (‘值’，’值’);指定字段名写入，可以写任意的选择表中你需要写入的字段
insert into 表名(字段1,字段2) select 字段1,字段2 from 表名2 - 将表名2的数据查询插入到表里面

删除数据

delete from 表名 where 条件; 删除某条数据
delete from 表; - 清空表
如果有自增id，新增的数据，是以删除的最后一个数据作为起始，不会清空自增字段的offect（偏移量）值
truncate table 表; 数据量大，删除速度要比上一条快，offect（偏移量）值直接从零开始

修改数据

update 表名 set 字段=值 where 条件; 修改单个字段
update 表名 set 字段=值,字段1=值1,... where 条件; 修改多个字段

查询数据

单表查询语法

SELECT DISTINCT 字段1,字段2... FROM 表名
                              WHERE 条件
                              GROUP BY field
                              HAVING 筛选
                              ORDER BY field
                              LIMIT 限制条数

简单查询

select */字段 from 表; 查询所有数据或单个字段的数据。

as 重命名（显示时）

select 字段1 as 别名1,字段2 as 别名2 from 表;

distinct 去掉重复

selelct distinct 字段,字段1 from 表; 多个数据去掉同时重复的

四则运算：+ - * /

select 字段+ - * / number from 表;

定义显示格式

concat(); 字符串拼接
concat_ws(‘|’, ‘a’,’b’,’c’); - ‘a|b|c’ 以第一个参数为分隔符进行拼接
- concat(‘a’,’b’,’c’); - ‘abc’
- select concat(字段1,字段2) from 表; 多个字段拼接显示成一个字段

case语句相当于if条件

case ... end
case when 字段1=值1 then 值2 when 字段3=值4 then 值5 else 值5 end

where约束

根据条件筛选符合的行,where字句中可以使用：

比较运算符：> < >= <= <> !=
- is / si not 判断是否为Null时不用比较运算符
范围：between 80 and 100 值在80到100之间 - 闭合区间
- 独立的值：in(80,90,100) 值是80或90或100
逻辑运算符：
- 在多个条件直接可以使用逻辑运算符 and or not
  - 优先级 not > and > or
模糊匹配
- like ‘e%‘ ：通配符可以是%或_，
  - %表示任意多字符
  - _表示一个字符
REGEXP正则表达式查询
- SELECT * FROM employee WHERE emp_name REGEXP ‘^ale‘; 以ale开头
- SELECT * FROM employee WHERE emp_name REGEXP ‘on$‘; 以on结尾
- SELECT * FROM employee WHERE emp_name REGEXP ‘m{2}‘;
- 对字符串匹配的方式
  - WHERE emp_name = ‘egon‘;
  - WHERE emp_name LIKE ‘yua%‘;
  - WHERE emp_name REGEXP ‘on$‘;

分组group by

分组可以与聚合函数使用，求总人数，最大值，最小值，平均值，求和，但求出来的值只和分组字段对应，并不和其他字段对应，这个时候查出来的其他字段都不生效。
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;

注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名拼接返回
SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;
GROUP BY与聚合函数一起使用
select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
强调：
- 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
- 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

聚合函数

count : 个数 max : 最大值 min : 最小值 sum : 求和 avg : 求平均值
- 强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：
- SELECT COUNT(*) FROM employee;
- SELECT COUNT(*) FROM employee WHERE depart_id=1;
- SELECT MAX(salary) FROM employee;
- SELECT MIN(salary) FROM employee;
- SELECT AVG(salary) FROM employee;
- SELECT SUM(salary) FROM employee;

过滤having

根据分组之后的内容进行租的过滤；先分组后过滤
HAVING与WHERE不一样的地方在于

执行优先级从高到低：where > group by > having

Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
SELECT SUM(salary) FROM employee WHERE depart_id=3;

排序order by

默认ASC : 升序
单列排序
- SELECT * FROM employee ORDER BY salary;
- SELECT * FROM employee ORDER BY salary ASC; ASC：升序
- SELECT * FROM employee ORDER BY salary DESC; AESC：降序
多列排序
- 先按照age排序，如果年纪相同，则按照薪资排序
  SELECT * from employee
  ORDER BY age ASC,
  salary DESC;

LIMIT 限制查询的记录数

limit n offset m ==> limit m,n 从m+1条开始取n项

示例：

SELECT * FROM employee ORDER BY salary DESC LIMIT 3;  #默认初始位置为0 从0开始取三条

SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

查询顺序

from 表 -> where 行 -> group by 分组 -> having 筛选 -> select 列 -> order by 排序 -> limit m,n 限制;
where 条件中不能用select字段的重命名
order by 或 having 可以使用select字段的重命名
having经过了mysql的特殊处理，使可以感知到select字段的重命名

扩展：

select now();获取当前时间

select id (select now()) from 表名; 显示表id和当前时间

多表查询

连表查询

select * from 表1,表2 where 条件; 交叉连接：不适合任何匹配条件，生成笛卡尔积
- 连接的时候创建一张大表，里面存放两张表的笛卡尔积
- 在根据条件进行筛选就可以了

语法：

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

表与表之间的连接方式

inner join ... on ... 内连接，只连接匹配的行
- select from 表1 inner join 表2 on 条件; 只显示两表的匹配项，不匹配的不显示。
- select from 表1 inner join 表2 on 表1.id =表2.id where 条件 group by 分组 having 筛选 order by 排序 limit m,n 偏移量;
left join ... on ... 外连接，优先显示左表全部记录
- select from 左表 left join 右表 on 条件; 根据左表全内容进行匹配，全显示，右表只显示条件相对应的数据
right join ... on ... 外连接，优先显示右表全部记录
- select from 左表 left join 右表 on 条件; 根据右表全内容进行匹配，全显示，左表只显示条件相对应的数据
full join 全外连接使用union关键字，显示左右两个表全部记录（mysql不支持）
- select from 左表 left join 右表 on 条件 union select from 左表 left join 右表 on 条件;

子查询

子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、>、>= 、<、<=等

注意使用exists子查询不返回查询的值，而是返回一个真假值，True或False，当可以使用连表查询也可以使用子查询时，推荐使用连表查询

索引

加快查询速度
函数：处理简单的逻辑
存储过程：处理的逻辑比较复杂
数据准备：
- 读取一次硬盘的时间开销
- 大约 0.1ms
- CPU 0.1ms运行50000条指令
- 减少读取操作
- 预读取，linux读文件每次会加载4096字节
新的数据结构 -- 数
- root 根
- branch 分支
- leaf 叶
二叉树：
- 每个分支只有两个节点
平衡树(btree)：- balance tree
- 让查找每一个之经历的查找速度尽量平衡
b+树：
- 分支节点不存储数据 - 让树的高低尽量矮，让查找一个数据的效率尽量稳定
- 在所有叶子节点之间加入了双向的地址链接 -- 查找范围非常快

两种索引的差别

聚集索引（聚簇索引）
- Innodb 必有且仅有一个（索引和数据都存储在一个文件）
- innodb存储引擎中主键默认创建一个聚集索引
非聚集(簇)索引(辅助索引)
- innodb（索引和数据分开存储）
- myisam

索引的创建和删除

创建主键 primary key 聚集索引+非空+唯一
创建唯一约束 unique 辅助索引+唯一

添加一个普通索引：

create index 索引名 on 表(字段);

删除索引;

drop index 索引名 on 表;

正确使用索引

查询的条件字段不是索引字段
- 对哪个字段创建索引，就用这个字段条件查询
在创建索引的时候应该对区分度比较大的列进行创建
- 1/10以下重复率比较适合创建索引
范围
- 范围越小越快，越大越慢
- like ‘a%’ 快，like ‘%a’ 慢
条件列不能参与计算/使用函数
and和or
- -and连接多个条件其中一列含有索引，可以加快查找速度。
- or 连接多个条件必须所有列都含有索引，才能加快查找顺序。
联合索引 - 最左前缀原则（必须带着最左边的列做条件，出现范围后索引失效）

数据备份

数据库的逻辑备份

语法：

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

示例：

#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

数据恢复

方法一：

[root@egon backup]# mysql -uroot -p123 < /backup/all.sql

方法二：

mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   #关闭二进制日志，只对当前session生效
mysql> source /root/db1.sql

事务和锁

begin;  # 开启事务
select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务

一致性 - 事务 - 要么全成功要么全失败，操作不可分割 - ACID特性
- A - Atomicity - 原子性 - 不可分割
- C - Consistency - 一致性 - 事务前后数据状态要一致
- I - Isolation - 隔离性 - 并发的多个事务不知道彼此的中间状态
- D - Duration - 持久性 - 事务完成后数据要做持久化（写进物理存储）

注意事项

给数据库和表命名时尽量选择全小写
作为筛选条件的字符串是否区分大小写，看设置的校对规则 collate 例如：
- utf8_bin 中是区分大小写的
- utf8_general_ci 中ci (case insensitive) 表示不区分大小写
删除表注意事项：外键关系，多对一，先删除多的一方
- emp 员工 dept 部门，员工多对一部门，员工里有外键，先删除员工
  - drop table if exists tb_emp;
  - drop table if exists tb_dept;

视图：查询的快照（简化查询操作）

通过视图可以将用户的访问权限限制到某指定的列上

-- 创建视图
create view vw_na_no as 
select ename,eno,sal from tb_emp;

-- 查询视图

select * from vw_na_no;

-- 删除视图
drop view vw_na_no;

过程

（存储）过程/函数：把一系列的SQL可以封装到一个过程中，而且可以加上分支和循环，将来通过过程的名字直接调用过程即可，因为创建过程时已经提前编译了 SQL语句，所以比直接执行SQL语句性能更好

-- 重新定义定界符为$$ - (命令行模式下出现 ; 下直接接受，修改以使语句完整)
delimiter $$;

-- 创建存储过程(查询模式下末尾直接写 ; 命令行模式下需要修改定界符，填写$$)
-- float -> decimal: 有效数字 decimal(6,1) 六位有效数字，1位小数
create procedure sp_dept_avg_sal(deptno int,out avgsal float)
begin 
  select avg(sal) into avgsal from tb_emp where dno=deptno;
end;

-- 调用存储过程
call sp_dept_avg_sal(10,@a);

-- 通过输出参数取出部门平均成绩
select @a;

-- 删除存储过程
drop procedure sp_dept_avg_sal;

-- 将定界符还原回;
delimiter ;

触发器

在执行增删改操作时可以触发其他的级联操作，但是有可能导致“锁表”现象，实际开发中应该尽量避免使用触发器

update tb_dept set dno=11 where dno=10;

-- 重新定义定界符
delimiter $$

-- 创建触发器
create trigger tr_dept_update 
after update on tb_dept for each row 
begin 
  update tb_emp set dno=new.dno where dno=`OLD_PASSWORD`(str).dno;
end;

-- 修改定界符为 ; 
delimiter ;

-- 删除触发器
drop trigger tr_dept_update;

事务（transaction）

把多个增删改操作做成原子性操作（不可分割）
- 要么全做（全成功），要么不做（全失败）
- 例如转账信息，一个账户扣钱，一个账户加钱，原子性操作
  - 要么全部成功，要么全部失败

-- 开启事务两种方法 begin/start transaction
-- start transaction; -- 开启事务环境1 
begin; -- 开启事务环境2

-- 删除全表
-- 视图看不到数据，但数据还在
-- 并没有反映到实际的物理存储
delete from tb_emp;

-- 提交（事务中的操作全都生效）
commit;

-- 回滚（事务中的所有操作全部撤销）
rollback;

2_MySQL

标签：注意 ssi 不用串匹配一个数据库 cad iso class integer

2_MySQL

MySQL

关系型数据库

关系型数据库保证数据的完整性和一致性：

Window下安装

MySQL时间配置

1，查看当前时间，时区

2，修改时区

3，设置重启永久生效

常用命令

用户相关命令

创建用户

创建用户并设置密码

用户授权

权限类型

召回权限

刷新

数据库相关命令

查看当前数据库

查看所有数据库

创建一个数据库

切换数据库

查看数据库表

删除数据库(慎用)

表相关命令

创建表

查看表结构

删除表

存储引擎

Innodb存储引擎(mysql5.6默认存储引擎)

特点：

数据和索引存储在一起 - 2个文件

Myisam存储引擎(mysql5.5之前默认存储引擎)

特点：

数据和索引不存储在一起 - 3个文件

Memory存储引擎

特点：

数据存储在内存中，数据断电消失 - 1个文件

查看当前的默认存储引擎

查看当前数据库的存储引擎

创建表时指定存储引擎

Mysql的数据类型

数字类型

整数：

浮点型：

字符串

时间

默认约束：

enum和set

表的完整性约束

修改表结构

1.修改表名

2.增加字段

3.删除字段

4.修改字段

5.修改字段排列顺序/在增加的时候指定字段位置

6.alter操作非空和唯一（了解）

去掉null约束

添加null约束

去掉unique约束

添加unique约束

添加联合唯一

7.alter操作主键（了解）

8.为表添加外键

创建press表

创建book表

为book表添加外键

删除外键

多表结构的创建和分析

操作数据

插入数据

删除数据

修改数据

查询数据

单表查询语法

简单查询

as 重命名（显示时）

distinct 去掉重复

四则运算 ：+ - * /

定义显示格式

四则运算：+ - * /

case语句相当于if条件