mysql aesc_2_MySQL

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;