Mysql基础知识概括

@TOC

数据库简介

数据库相关概念：

• DB：数据库，保存一组有组织的数据的容器
• DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
• SQL：结构化查询语言，用于和DBMS通信的语言

结构化与非结构化：

• 结构化数据：即行数据，存储在数据库里，可以用二维表结构来逻辑表达实现的数据。我们可以清楚的看到能够形式化存储在数据库中，每一个列都有具体的含义，如下图所示：

Size	name
100	george
101	dam
102	jordan

• 非结构化数据：不方便用数据库二维逻辑表来表现的数据，包括所有格式的办公文档、文本、图片、XML、HTML、各类报表、图像和音频/视频信息等等。

为什么要学习数据库？

• 持久化数据到本地
• 可以实现结构化查询，方便管理

市场中的数据库系统：

• IBM公司开发的关系数据库，典型代表产品：DB2。
• IBM公司开发的层次数据库，代表产品：IMS层次数据库。
• Oracle 公司开发的Oracle关系型数据库。
• 瑞典MySQL AB公司开发的小型关系型数据库管理系统MySQL。

数据库存储数据的特点：

• 将数据放到表中，表再放到库中
• 一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
• 表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
• 表中的数据是按行存储的，每一行类似于java中的“对象”。

数据库中的Schema与database的关系：

• MySQL官方文档指出，从概念上讲，模式是一组相互关联的数据库对象，如表，表列，列的数据类型，索引，外键等等。但是从物理层面上来说，模式与数据库是同义的。你可以在MySQL的SQL语法中用关键字SCHEMA替代DATABASE，例如使用CREATE SCHEMA来代替CREATE DATABASE。
• 在关系型数据库中，分三级：database.schema.table。即一个数据库下面可以包含多个schema，一个schema下可以包含多个数据库对象，比如表、存储过程、触发器等。但并非所有数据库都实现了schema这一层，比如mysql直接把schema和database等效了，PostgreSQL、Oracle、SQL server等的schema也含义不太相同。所以说，关系型数据库中没有catalog的概念。但在一些其它地方（特别是大数据领域的一些组件）有catalog的概念，也是用来做层级划分的，一般是这样的层级关系：catalog.database.table。

MySQL简介

MySQL简介：

• MySQL是一个关系型数据库管理系统，由g典MySQLAB公司开发，目前属于Oracle公司。
• MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
• Mysql是开源的，所以你不需要支付额外的费用。
• Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL使用标准的SQL数据语言形式。
• Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Per、PHP、Eifel、Ruby和Tcl等。Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
• MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

MySQL的语法规范：

• 不区分大小写,但建议关键字大写，表名、列名小写
• 每条命令最好用分号结尾
• 每条命令根据需要，可以进行缩进 或换行
• 注释： ①单行注释：#注释文字 ②单行注释：-- 注释文字 ③多行注释：/* 注释文字 */

SQL的语言分类：

• DQL（Data Query Language）：数据查询语言 select
• DML(Data Manipulate Language):数据操作语言 insert 、update、delete
• DDL（Data Define Languge）：数据定义语言 create、drop、alter
• TCL（Transaction Control Language）：事务控制语言 commit、rollback

Mysql安装：

• Linux版本的安装：
• Window版本的安装： ①链接：window10下安装MySQL详解 ②配置环境变量配置以便在任何目录可执行mysql命令。 ③mysql安装可以分为免安装版和安装板。

Mysql目录：

• Window版本中： ①bin目录：用于放置一些可执行文件，如mysql.exe、mysqld.exe、mysqlshow.exe等。 ②data目录：用于放置一些日志文件以及数据库。 ③include目录：用于放置一些头文件，如：mysql.h、mysql_ername.h等。 ④lib目录：用于放置一系列库文件。 ⑤share目录：用于存放字符集、语言等信息。 ⑥my.ini：是MySQL数据库中使用的配置文件。
• Linux版本中： ①mysql数据库目录： /var/lib/mysql ②mysql配置文件目录： /usr/share/mysql ③相关命令目录 ：/usr/bin

DQL语言

基础查询

语法:

select 查询列表 from 表名;

特点:

• 查询列表可以是字段、常量、表达式、函数，也可以是多个
• 查询结果是一个虚拟表

示例:

• 查询单个字段

select 字段名 from 表名;

• 查询多个字段

select 字段名，字段名 from 表名;

• 查询所有字段

select * from 表名

• 查询常量 ①注意：字符型和日期型的常量值必须用单引号或双引号引起来，数值型不需要 ②单引号和双引号没区别。

select 常量值;

• 查询函数

select 函数名(实参列表);

• 查询表达式

select 100/1234;

• 起别名 ①as：写as。 ②空格：不写as，起个空格。 ③as和空格两者没啥区别 ④使用双引号：会将别名解析成双引号里的内容.引号是什么.别名就是什么。 ⑤不使用双引号：即使别名全部命名成小写，也会被解析成大写字母。所以，双引号一般会用在最外层的select子句中，保证列名的大小写是你想要的结果。 ⑥注意：如一句sql语句中,使用"tname"做别名,后面调用"tname"需要使用双引号,不使用双引号的tname会被解析成TNAME,此时会找不到对应的列,会报错.因为对应的列被我们解析成了tname, 和TNAME是不相同的列。

• 去重

select distinct 字段名 from 表名;

• +号 ①在java中+号的： <1>运算符，两个操作数都为数值型。 <2>连接符：只要有一个操作数为字符串。 ②在mysql中只有一个作用：运算符，做加法运算 <1>select 数值+数值; 直接运算 <2>select 字符+数值;先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算 ③select null+值;结果都为null

• 【补充】concat函数 ①功能：拼接字符

select concat(字符1，字符2，字符3,...);

• 【补充】ifnull函数 ①功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值

select ifnull(commission_pct,0) from employees;

• 【补充】isnull函数 ①功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0
• 【补充】操作符：IS NULL和IS NOT NULL ①功能：和isnull函数类似。
• 总结：查询空值的运行速度基本上为：IFNULL() > IS NULL > ISNULL()

注意：

• Mysql 一个字段定义成int类型，查询时传入String，会截取字符串（ Mysql会将从左到右的第一个非数值开始，将后面的字符串转成0，在和数值类型相加。）
• 但是一个字段定义成int类型，插入时传入String将会报错

条件查询

条件查询分类：

• 一、条件表达式: ①示例：salary>10000 ②条件运算符：> < >= <= = != <>

• 二、逻辑表达式: ①示例：salary>10000 && salary<20000 逻辑运算符： ②and（&&）:两个条件如果同时成立，结果为true，否则为false ③or(||)：两个条件只要有一个成立，结果为true，否则为false ④not(!)：如果条件成立，则not后为false，否则为true

• 三、模糊查询：LIKE ①示例：last_name like 'a%' ②NOT LIKE：NOT LIKE的使用方式与之相同，用于获取匹配不到的数据

• 四、范围查询：BETWEEN...AND... 与 IN 与EXISTS ①BETWEEN...AND...和 >= and <=是等价的。 ②NOT IN：NOT IN的使用方式与之相同，用于获取匹配不到的数据 ③EXISTS：SELECT ... FROM table WHERE EXISTS subquery <1>该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE)来决定主查询的数据结果是否得以保留。 <2>提示： 1、EXISTS (subquery)只返回TRUE或FALSE，因此子查询中的SELECT'也可以是SELECT1l或select oK，官方说法是实际执行时会忽略SELECT洁单，因出没有区别 2、EXSTS子查询的实际执行过程可能经过了忧化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以碗定是否有效率问题。 3、EXISTS子查询往往也可以用条件表达式、其他子查询或者JON来替代，何种最优需要具体问题具体分析

排序查询

简介：

• 语法：

select
	要查询的东西
from
	表
where 
	条件
order by 排序的字段|表达式|函数|别名 【asc|desc】

• order by 默认升序(从小到大)，升序为asc(a为首字母)，降序为desc。
• MySQL将null算作最小值,因此： <1>将null强制放在最前： if(isnull(字段名),0,1) asc //asc可以省略 <2>将null强制放在最后 if(isnull(字段名),0,1) dsc
  if(isnull(字段名),1,0) asc //asc可以省略
• order by 后面加rand()可以随机排序。
• order by 多个字段时，优先排序写在前面的字段，后面的字段的排序是在前面排序好的数据中相同的区间里排序(即后面的字段的排序不会破坏前面的字段的排序)

常见函数

单行函数：

• 字符函数 concat拼接 substr截取子串 upper转换成大写 lower转换成小写 trim去前后指定的空格和字符 ltrim去左边空格 rtrim去右边空格 replace替换 lpad左填充 rpad右填充 instr返回子串第一次出现的索引 length 获取字节个数
• 数学函数 round 四舍五入 rand 随机数 floor向下取整 ceil向上取整 mod取余 truncate截断
• 日期函数 now当前系统日期+时间 curdate当前系统日期 curtime当前系统时间 str_to_date 将字符转换成日期 date_format将日期转换成字符
• 流程控制函数 if 处理双分支 case语句 处理多分支 情况1：处理等值判断 情况2：处理条件判断
• 其他函数 version版本 database当前库 user当前连接用户
• 链接：MySQL 函数

interval日期函数：

• 链接：MySql时间处理及interval函数运用

• 当函数使用时，即interval(),为比较函数，如：interval(10,1,3,5,7); 结果4；

• 原理：10为被比较数，后面1,3,5,7为比较数，将后面四个依次与10比较，看后面数字组有多少个少于10，则返回其个数。前提是后面数字组为从小到大排列，否则返回结果0。

• 当关键词使用时，表示为设置时间间隔，常用在date_add()与date_sub()函数里，

• 如：interval 1 day，解释为将时间间隔设置为1天。

单行函数，日期函数的优劣：

• mysql的一些个性： ①单表千万级别(优化到极致能达到亿级别)行记录存储，简单条件(最好条件上有索引，当然也需要看具体case)查询 ②mysql喜欢大内存(可以将大量的索引直接放到内存中)，喜欢高性能IO(比如SSD) ③高并发的时候，CPU资源消耗也是非常严重的。如果峰值请求的时候，给遇上一个mysql函数(需要CPU做计算)，那就很可能因为一个简单mysql函数酿成了悲剧。mysql出事故的时候，load很容易飙到100+
• mysql不是系统的瓶颈 ①该场景下可以随意的使用mysql提供的特性功能，例如msyql函数，多方便好用啊。较少了应用层的工作量。而且对你系统性能没有多大的影响。 ②举例说明：小型系统，请求量小，数据存储量小，mysql server内存充足，统计需求(一个sql跑一个晚上你也不担心)
• mysql即将(或者正在)是系统的瓶颈 ①这个时候mysql最好仅仅当做存储来用，尽量不做任何额外的计算。优化的时候，会尽可能的把计算消耗的资源移到应用层去做。尽量保证mysql仅仅做储存工作。另外使用mysql函数很可能走不了索引，那个更悲剧了，这样系统更没办法抗住大并发。

分组函数：

• sum 求和

• max 最大值

• min 最小值

• avg 平均值

• count 计数

• 特点： 1、以上五个分组函数都忽略null值，除了count(*) 2、sum和avg一般用于处理数值型。max、min、count可以处理任何数据类型 3、都可以搭配distinct使用，用于统计去重后的结果 4、count的参数可以支持：字段、*、常量值，一般建议使用 count(*)。

分组查询

简介：

• 语法：

select 查询的字段，分组函数
from 表
group by 分组的字段

• 特点： 1、可以按单个字段分组 2、和分组函数一同查询的字段最好是分组后的字段 3、可以按多个字段分组，字段之间用逗号隔开 4、可以支持排序 5、having后可以支持别名 6、分组筛选

针对的表	针对的表	位置	关键字
分组前筛选：	原始表	group by的前面	where
分组后筛选：	分组后的结果集	group by的后面	having

• MySQL group by 不对 null 进行分组统计：在使用 group by某列名进行分组统计时，该列名的数据有些为 null, 因而会出现 null 的数据行全部分成一组导致数据错误，所以 null 列名的数据行不能执行 group by ①网上有类似的解决方案，通过IFNULL()函数搭配UUID()函数即可解决。

多表连接查询

简介：

• 笛卡尔乘积：如果连接条件省略或无效则会出现，具体来说就是：表1 有m行,表2有n行,结果=m*n行
• 产生原因:没有有效的连接条件
• 解决办法：添加上连接条件： ①传统模式下的连接 ：等值连接——非等值连接 1.等值连接的结果 = 多个表的交集 2.n表连接，至少需要n-1个连接条件 3.多个表不分主次，没有顺序要求 4.一般为表起别名，提高阅读性和性能 ②sql99语法（1999年推出的sql语法）：通过join关键字实现连接。支持的有：

连接类型	连接具体分类
内连接:	等值连接、 非等值连接、自连接
外连接:	左外连接、右外连接、全外连接（mysql不支持，oracle支持）
交叉连接:	~~

• 注意： ①其中cross交叉连接为笛卡尔积。 ②由于mysql不支持full join全外连接，可以用left outer join一次,加上union，再来right outer join。

笛卡尔积：

• 笛卡尔积又叫笛卡尔乘积，是一个叫笛卡尔的人提出来的。 简单的说就是两个集合相乘的结果。
• 假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b,0), (b, 1), (b, 2)}。
• 这样冗余的数据可不是我们想要，所以想要你的结果避免笛卡尔积，既要做到以下几点： ①关联范围在最小粒度的列(就是选择性最高的列，例如主键id和人员的编号等等)。 ②如果是三张表连接，并且是1：n：n的关系，就要先关联两张表，然后将两张表关联的结果与第三表在进行关联，这样就可以取得我们想要的结果啦！多张表同理！

sql99语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

• 好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

自连接:

• 案例：查询员工名和直接上级的名称
• sql99示范：

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;

• sql92示范：

SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

子查询

简介：

• 含义：一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询，在外面的查询语句，称为主查询或外查询。
• 特点： 1、子查询都放在小括号内 2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧 3、子查询优先于主查询执行，主查询使用了子查询的执行结果 4、子查询根据查询结果的行数不同分为以下两类： ① 单行子查询：结果集只有一行。一般搭配单行操作符使用：> < = <> >= <= 。非法使用子查询的情况： <1>子查询的结果为一组值 <2>子查询的结果为空 ②多行子查询：结果集有多行。一般搭配多行操作符使用：any、all、in、not in <1>in：属于子查询结果中的任意一个就行 <2>any和all往往可以用其他查询代替

分页查询

简介：

• 应用场景：实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
• 语法：

select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;

• 特点： ①起始条目索引从0开始。 ②limit子句放在查询语句的最后。 ③公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage。假如：每页显示条目数sizePerPage，要显示的页数 page。
• 注意： ①如果SQL 要求limit 100, 10，也就是查询第101到110个数据，但是 MySQL 会查询前110行，然后将前100行抛弃，最后结果集中就只剩下了第101到110行，执行结束。总结：limit a, b会查询前a+b条数据，然后丢弃前a条数据。 ②因此limit 的偏移量越大，执行时间越长，因为偏移量越大，查询的条数越多。

联合查询

简介：

• union 联合、合并
• 语法：

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

• 特点： ①多条查询语句的查询的列数必须是一致的 ②多条查询语句的查询的列的类型几乎相同 <1>UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。 ③union代表去重，union all代表不去重

DML语言

插入:

• 语法：

insert into 表名(字段名，...)  values(值1，...);

• 特点： 1、字段类型和值类型一致或兼容，而且一一对应 2、可以为空的字段，可以不用插入值，或用null填充 3、不可以为空的字段，必须插入值 4、字段个数和值的个数必须一致 5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致

修改:

• 修改单表语法：

update 表名 
set 字段=新值,字段=新值【where 条件】

• 修改多表语法：

update 表1 别名1,表2 别名2 
set 字段=新值，字段=新值 
where 连接条件
and 筛选条件

• 几种更新(Update语句)查询的方法：

1.从外部输入:
1)这种比较简单
2)例：update tb set UserName="XXXXX" where UserID="aasdd"
 
2.一些内部变量，函数等，比如时间等
1)直接将函数赋值给字段
2)update tb set LastDate=date() where UserID="aasdd"
 
3.对某些字段变量+1，常见的如：点击率、下载次数等
1)这种直接将字段+1然后赋值给自身
2)update tb set clickcount=clickcount+1 where ID=xxx
 
4.将同一记录的一个字段赋值给另一个字段
1)update tb set Lastdate= regdate where XXX
 
5.将一个表中的一批记录更新到另外一个表中
1)先要将table2中的f1 f2 更新到table1(相同的ID)
2)update table1,table2 set table1.f1=table2.f1,table1.f2=table2.f2 where table1.ID=table2.ID
3)
 table1
ID f1 f2
 table2
ID f1 f2
 
 
6.将同一个表中的一些记录更新到另外一些记录中
1)
 表：a
 ID   month   E_ID     Price
 1       1           1        2
 2       1           2        4
 3       2           1         5
 4       2           2        5
2)先要将表中2月份的产品price更新到1月份中
 显然，要找到2月份中和1月份中ID相同的E_ID并更新price到1月份中
 这个完全可以和上面的方法来处理，不过由于同一表，为了区分两个月份的，应该将表重命名一下
 update a,a as b set a.price=b.price where a.E_ID=b.E_ID and a.month=1 and b.month=2
3)当然，这里也可以先将2月份的查询出来，在用5.的方法去更新
4)update a,(select * from a where month=2)as b set a.price=b.price where a.E_ID=b.E_ID and a.month=1

删除：

• 单表的删除：

delete from 表名 
【where 筛选条件】

• 多表的删除：

delete 别名1，别名2
from 表1 别名1，表2 别名2
where 连接条件
and 筛选条件;

• truncate语句

truncate table 表名

• truncate和delete的区别： <1>truncate不能加where条件，而delete可以加where条件 <2>truncate的效率高一丢丢 <3>truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始。 <4>delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始。 <5>truncate删除不能回滚，delete删除可以回滚。

DDL语言

库的管理：

• 创建库
• 详细链接：MySQL创建数据库

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

①[ ]中的内容是可选的。语法说明如下： <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。 ②IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。 ③[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。 ④[DEFAULT] COLLATE：指定字符集的默认校对规则。校对规则定义了比较字符串的方式。

CREATE DATABASE IF NOT EXISTS table_name 
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

• 删除库

drop database 库名

表的管理：

• 创建表(创建时可以指定引擎和存储字符类型)

CREATE TABLE IF NOT EXISTS stuinfo(
		stuId INT,
		stuName VARCHAR(20),
		gender CHAR,
		bornDate DATETIME
)ENGINE=INNODB  DEFAULT CHARSET=utf8 ;

• 查看表详细信息

DESC studentinfo;

• 修改表 alter

语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段类型】;

①修改字段名

ALTER TABLE studentinfo CHANGE  COLUMN sex gender CHAR;

②修改表名

ALTER TABLE stuinfo RENAME [TO]  studentinfo;

③修改字段类型和列级约束

ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;

④添加字段

ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;

⑤删除字段

ALTER TABLE studentinfo DROP COLUMN email;

• 删除表

DROP TABLE [IF EXISTS] studentinfo;

添加注释(comment): MySQL 添加注释(comment)

• =在MySQL数据库中， 字段或列的注释是用属性comment来添加,创建新表的脚本中， 可在字段定义脚本中添加comment属性来添加注释。示例代码如下：

create table test( 
    id int not null default 0 comment '用户id' ) 

• 如果是已经建好的表， 也可以用修改字段的命令，然后加上comment属性定义，就可以添加上注释了。示例代码如下：

alter table test 
change column id id int not null default 0 comment '测试表id'

• 查看已有表的所有字段的注释可以用命令：show full columns from table 来查看， 示例如下：

show full columns from test;

数据类型

简述：

• MySQL支持多种类型的SQL数据类型：数值，日期和时间类型，字符串（字符和字节）类型，空间类型（MySQL为SQL几何类型环境实现了一个空间扩展子集）和 JSON数据类型（一般用json字符串用字符串类型存储）等
• 数据类型描述使用以下约定： ①M表示整数类型的最大显示宽度，最大值取决于数据类型。 ②对于浮点和定点类型， M是可以存储的总位数（精度）。D也适用于浮点和定点类型，并指示小数点后面的位数。最大可能值为30，但不应大于 M-2。 ③对于字符串（字节，字符）类型， M是存储的最大字符长度。（但对于text和blog数据类型是无效的，他们最大字符长度取决于数据类型）。
• 注意： ①允许的最大值M取决于数据类型。 ②[ ]表示类型定义的可选部分。
• 此外： ①字符串 ：底层字节对应字符集 ②字节：对应字节 ③数值：对应字节 ④时间：对应字节

在MySQL中常用数据类型主要分为以下几类：

• 数值类型
• 字符串（字节，字符）类型
• 日期时间类型

数值类型包括：

• 整数型
• 浮点型
• 定点型

整型（精确值）：

• TINYINT[(M)] [UNSIGNED] [ZEROFILL] 范围非常小的整数，有符号的范围是-128到127，无符号的范围是0到 255
• SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 范围较小的整数，有符号的范围是-32768到32767，无符号的范围是0到 65535
• MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 中等大小的整数，有符号的范围是-8388608到8388607，无符号的范围是0到 16777215。
• INT或INTEGER[(M)] [UNSIGNED] [ZEROFILL] 正常大小的整数，有符号的范围是 -2147483648到2147483647。无符号的范围是 0到4294967295。
• BIGINT[(M)] [UNSIGNED] [ZEROFILL] 大整数，有符号的范围是 -9223372036854775808到9223372036854775807，无符号的范围是0到 18446744073709551615。

• 上面出现了两个词，有符号和无符号（UNSIGNED）。 ①在计算机中，可以区分正负的类型，称为有符号类型。无正负的类型，称为无符号类型。 ②简单的理解为就是有符号值可以表示负数，0以及正数，无符号值只能为0或正数。 ③关于有符号和无符号详解可以看这篇文章或者自己百度百科。 ④如果不手动指定UNSIGNED，那么默认就是有符号的。
• 下面我们用一下整数型数据做个案例：

①首先创建一个表

CREATE TABLE int_db(
a TINYINT,
b SMALLINT,
c MIDDLEINT,
d INT,
e BIGINT
);

②查看表结构 ③我们来看看type这一列，可以看到，每个字段类型后面都有一个括号，括号里面的有个数值，这个数值实际上就是字段的显示宽度，也就是M的值，M表示整数类型的最大显示宽度。最大显示宽度为255.显示宽度与类型可包含的值范围无关 ④我们在创建表的时候并没有指定字段类型的显示宽度，那么，默认的显示宽度则是该字段类型最大的显示宽度 ⑤例如字段a的显示宽度为4，是因为TINYINT有符号值的范围是-128到127，-128的长度为4（负号、1、2、8共四位），所以默认的显示宽度最大为4，其他的以此类推。

• 下面我们再新建一个表，将字段a的修改为无符号类型的。再看看a字段的默认显示宽度。

①可以看到，默认显宽度就变成3了，因为无符号的TINYINT的值范围为0-255，没有负号，所以最多是3位。

• 下面我们来试试ZEROFILL约束，当使用该约束后当数据的长度比我们指定的显示宽度小的时候会使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED。

①下面我们新建个表试一下，这次我们来指定一下显示宽度

CREATE TABLE int_db2(
a TINYINT(8) ZEROFILL,
b TINYINT(5) UNSIGNED);

②然后插入一条记录：

INSERT int_db2() VALUES(12,12);

③可以看到，12变成了00000012，自动在前面补了0，这是因为指定的显示宽度是8，但是12只有两位，所以在前面补0，使长度为8。这就是ZEROFILL的效果

浮点型：

• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 一个小的（单精度）浮点数。允许值是-3.402823466E+38 到-1.175494351E-38， 0以及1.175494351E-38 到3.402823466E+38,M是总位数，D是小数点后面的位数。若不指定，默认为实际精度，即最大精度。
• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 正常大小（双精度）浮点数。允许值是 -1.7976931348623157E+308到-2.2250738585072014E-308，0以及 2.2250738585072014E-308到 1.7976931348623157E+308。M是总位数，D是小数点后面的位数。若不指定，默认为实际精度，即最大精度。
• 下面我们来用一下浮点型做个案例：

①创建表

CREATE TABLE float_db(
a FLOAT(3,2),
b DOUBLE(5,3)
)

②我们指定a字段为FLOAT类型，总长度为3，小数点后两位为2,b字段总长度为5，小数点后两位长度为3 ③插入数据

INSERT float_db VALUES(1.111,2.113);

④可以看到，我们给a字段的值是1.111，但是只存进去了1.11浮点数存在精度丢失的问题，如果涉及到小数运算，尽量不要用浮点型。

定点型:

• DECIMAL[(M,D)] [UNSIGNED] [ZEROFILL] 常用于存储精确的小数，M是总位数，D是小数点后的位数。小数点和（负数） -符号不计入 M。如果 D为0，则值没有小数点或小数部分。最大位数（M）为 65. 最大支持小数（D）为30.如果D省略，则默认值为0.如果M省略，则默认值为10。M的范围是1到65。D范围为0到30，且不得大于M。
• 我们来用一下DECIMAL类型做个案例：

①首先创建表，先不指定M和D

CREATE TABLE decimal_db(a DECIMAL);

②可以看到，默认的总长度（M）为10，小数点位数(D)默认为0. ③插入一条数据

INSERT decimal_db VALUES(30.556)

④可以看到，存进去的数值被四舍五入阶段了，也就是说，DECIMAL也在存储时存在精度丢失的问题。

超出范围和溢出处理：

• 当MySQL将值存储在超出列数据类型允许范围的数值列中时，结果取决于当时生效的SQL模式：
• 如果启用了严格的SQL模式，则MySQL会根据SQL标准拒绝带有错误的超出范围的值，并且插入失败。
• 如果未启用限制模式，MySQL会将值截断到列数据类型范围的相应端点，并存储结果值，并产生一个警告。
• 在我们的配置文件中可以看到SQL模式的配置：

字符串类型:

• 常用的字符串类型有如下：
• CHAR[(M)] 一个固定长度的字符串，在存储时始终用空格填充指定长度。 M表示以字符为单位的列长度。M的范围为0到255.如果M省略，则长度为1，存储时占用M个字节
• VARCHAR(M)可变长度的字符串，M表示字符的最大列长度，M的范围是0到65,535，如果M省略，则长度为1，存储时占用L+1（L<=M,L为实际字符的长度）个字节
• TINYTEXT[(M)] 不能有默认值（如果指定长度不报错但无效），占用L+1个字节，L<2^8
• TEXT[(M)] 不能有默认值（如果指定长度不报错但无效），占用L+2个字节，L<2^16
• MEDIUMTEXT[(M)] 不能有默认值（如果指定长度不报错但无效），占用L+3个字节，L<2^24
• LONGTEXT[(M)] 不能有默认值（如果指定长度不报错但无效），占用L+4个字节，L<2^32
• ENUM('value1','value2',...) ENUM是一个字符串对象，其值从允许值列表中选择，它只能有一个值，从值列表中选择,最多可包含65,535个不同的元素
• SET('value1','value2',...) 字符串对象，该对象可以有零个或多个值，最多可包含64个不同的成员
• CHAR和VARCHAR

①创建表

CREATE TABLE str_db(
a CHAR(4),
b VARCHAR(4));

②插入数据

INSERT str_db() VALUES("","");
INSERT str_db() VALUES("ab","ab");
INSERT str_db() VALUES("abcd","abcd");
INSERT str_db() VALUES("abcdefg","abcdefg");//在严格模式下，该条数据会插入失败，非严格模式则会对数据进行截取

③我们看到查询的结果是一样的，但实际上他们存储时占用的长度是不一样的。 CHAR类型不管存储的值的长度是多少，都会占用M个字节，而VARCHAR则占用实际长度+1个字节。 ④但是CHAR的查询效果要高于VARCHAR，所以说，如果字段的长度能够确定的话，比如手机号，身份证号之类的字段，可以用CHAR类型，像地址，邮箱之类的就用VARCHAR

• TEXT系列

①TEXT系列的存储范围比VARCHAR要大，当VARCHAR不满足时可以用TEXT系列中的类型。需要注意的是TEXT系列类型的字段不能有默认值，在检索的时候不存在大小写转换，没有CHAR和VARCHAR的效率高

• ENUM

①枚举类型 ②创建表

CREATE TABLE enum_db(gender ENUM("男","女"));

③插入数据

INSERT enum_db() VALUES("男");
INSERT enum_db() VALUES(1); 也可以使用编号插入值，等同于"男"，序号从1开始
INSERT enum_db() VALUES("女");
INSERT enum_db() VALUES(2);等同于"女"

④下面我们插入一条不是枚举集合中的数据试一下

• SET

①在ENUM中我们只能从允许值列表中给字段插入一个值，而在SET类型中可以给字段插入多个值 ②创建表

CREATE TABLE set_db(
a SET('1','2','3','4','5')
)

③插入数据

INSERT set_db() VALUES('1')
INSERT set_db() VALUES('1,2,3')

日期时间类型:

• TIME 范围是’-838:59:59.000000’ 到’838:59:59.000000’
• DATE 支持的范围是 ‘1000-01-01’到 ‘9999-12-31’
• DATETIME 日期和时间组合。支持的范围是 ‘1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’。
• TIMESTAMP 时间戳。范围是’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。
• YEAR 范围是 1901到2155
• TIME:

①我们可以看到TIME的存储范围是’-838:59:59’到 ‘838:59:59’，因为TIME类型不仅可以用于表示一天中的时间（，还可以用于表示两个事件之间的经过时间或时间间隔

②TIME的完整的显示为 D HH:MM:SS D：表示天数，当指定该值时，存储时小时会先乘以该值 HH：表示小时 MM：表示分钟 SS:表示秒 ③创建表：

CREATE TABLE time_db(
a TIME
)

④插入值：

INSERT time_db() VALUES('22:14:16');
--   -2表示间隔了2两天
INSERT time_db() VALUES('-2 22:14:16');
-- 有冒号从小时开始
INSERT time_db() VALUES('14:16');
-- 没有冒号且没有天数则数据从秒开始
INSERT time_db() VALUES('30');
-- 有天数也从小时开始
INSERT time_db() VALUES('3 10');
-- 直接使用数字代替也可以
INSERT time_db() VALUES(253621);

• DATE:

①创建表

CREATE TABLE date_db(
a DATE)

②插入数据

INSERT date_db() VALUES(20180813);
INSERT date_db() VALUES(“2018-06-1”);
INSERT date_db() VALUES(“2018-4-1”);
INSERT date_db() VALUES(“2018-04-07”);

• DATETIME:

①创建表

CREATE TABLE datetime_db(
a DATETIME
)

②插入数据

INSERT datetime_db() VALUES(20180102235432);
INSERT datetime_db() VALUES("2015-04-21 21:14:32");
INSERT datetime_db() VALUES("2015-04-23");

• TIMESTAMP:

①TIMESTAMP和DATETIME使用上差不多，但是范围相对较小。 ②创建表

CREATE TABLE timestamp_db(
a TIMESTAMP
)

③插入数据

INSERT timestamp_db() VALUES(20020121);
INSERT timestamp_db() VALUES(20020121142554);
INSERT timestamp_db() VALUES("2015-12-16 21:14:15");
INSERT timestamp_db() VALUES("2015-12-17");
INSERT timestamp_db() VALUES(NULL);
INSERT timestamp_db() VALUES(CURRENT_TIMESTAMP);
INSERT timestamp_db() VALUES();

• YEAR:

①创建表

CREATE TABLE year_db(
a YEAR
)

②插入数据

INSERT year_db() VALUES("1993");
INSERT year_db() VALUES(1993);

二进制字符串类型：

• 二进制字符串类型也叫字符串字节类型，也就是存储二进制对象的数据类型，一般叫二进制数据，而常说的字符串是字符串字符类型。两者的区别在于字符字符串类型有字符集，而二进制字符串无字符集。
• 二进制字符串一般用于存储二进制的大对象，二进制字符串类型有 ①BIT 位数据类型，M表示最大字节长度。 ②BINARY固定二进制字符串，M表示最大字节长度。 ③VARBINARY可变二进制字符串，M表示最大字节长度。 ④BLOB (Binary large objects)储存二进制资料，不能有默认值（如果指定长度不报错但无效）（分为Blob、TinyBlob、MediumBlob、LongBlob）
• 与字符串一样，二进制字符串也是一个字节序列。但与通常包含文本格式信息的字符串不同，二进制串用于存储非传统数据，如图像、音频和视频文件、程序可执行文件等。

常见约束

常见的约束：

• NOT NULL：非空，该字段的值必填
• UNIQUE：唯一，该字段的值不可重复
• DEFAULT：默认，该字段的值不用手动插入有默认值
• CHECK：检查，mysql不支持
• PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
• FOREIGN KEY：外键，该字段的值引用了另外的表的字段

主键和唯一:

• 区别： ①一个表至多有一个主键，但可以有多个唯一 ②主键不允许为空，唯一可以为空
• 相同点： ①都具有唯一性 ②都支持组合键，但不推荐

外键：

• 用于限制两个表的关系，从表的字段值引用了主表的某字段值
• 外键列和主表的被引用列要求类型一致，意义一样，名称无要求
• 主表的被引用列要求是一个key（一般就是主键）
• 插入数据，先插入主表

删除数据先删除从表,再删除主表。可以通过以下两种方式来删除主表的记录：

• 方式一：级联删除

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN 
KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

• 方式二：级联置空

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN 
KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

创建表时添加约束：

• 代码示例：

create table 表名(
	字段名 字段类型 not null,#非空
	字段名 字段类型 primary key,#主键
	字段名 字段类型 unique,#唯一
	字段名 字段类型 default 值,#默认
	constraint 约束名 foreign key(字段名) references 主表（被引用列）
)

注意：

• 列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求。

~	支持类型	可以起约束名
列级约束	除了外键	不可以
表级约束	除了非空和默认	可以，但对主键无效

• MySQL建表时中有四种Key： Primary Key, Unique Key, Key 和 Foreign Key。 ①Foreign Key，Unique Key，Primary Key不多解释。 ②其实某个字段标记为Key，是不能保证这个字段的值在表中是唯一出现的。它的目的就是建立普通索引。

修改表时添加或删除约束

• 非空

添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;

• 默认

添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;

• 主键

添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;

• 唯一

添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;

• 外键

添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;

自增长列:

• 特点： 1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1 auto_increment_increment 如果要更改起始值：手动插入值. 如果要更改步长：更改系统变量 set auto_increment_increment=值; 2、一个表至多有一个自增长列 3、自增长列只能支持数值型 4、自增长列必须为一个key
• 创建表时设置自增长列

create table 表(
	字段名 字段类型 约束 auto_increment
)

• 修改表时设置自增长列

alter table 表 modify column 字段名 字段类型 约束 auto_increment

• 删除自增长列

alter table 表 modify column 字段名 字段类型 约束 

TCL语言

事务含义：

• 通过一组逻辑操作单元（一组DML——sql语句），将数据从一种状态切换到另外一种状态。

• 特点：（ACID） ①原子性：要么都执行，要么都回滚 ②一致性：保证数据的状态操作前和操作后保持一致 ③隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰 ④持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

• 相关步骤： ①开启事务 ②编写事务的一组逻辑操作单元（多条sql语句） ③提交事务或回滚事务

• 事务的分类： ①隐式事务，没有明显的开启和结束事务的标志 比如insert、update、delete语句本身就是一个事务 ②显式事务，具有明显的开启和结束事务的标志 1、开启事务 取消自动提交事务的功能或者以begin/start transaction开始 2、编写事务的一组逻辑操作单元（多条sql语句） insert update delete 3、提交事务或回滚事务 commit rollback

数据库事务处理相关命令:

进行的操作	命令
查看存储引擎	SHOW CREATE TABLE 表名；
更改引擎	ALTER TABLE 表名 ENGINE=新引擎名；
回滚	ROLLBACK;
BEIGIN/start transaction声明事务开始	BEIGIN/start transaction;
事务提交	COMMIT;
查询自动提交功能状态	SELECT @@AUTOCOMMIT;
设置自动提交功能	SET AUTOCOMMIT=0或1;
设置分离水平	SET SESSION TRANSACTION ISOLATION LEVEL 分离水平;

部分回滚 SAVEPOINT:

• 直接ROLLBACK会回滚到BEGIN开始之前的地方，而通过SAVEPOINT可以保存一个点，通过ROLLBACK TO SAVEPOINT就可以回滚到保存点了，也就实现了“想去哪就去了哪”。

begin和 autocommit的关系：

• mysql使用InnoDB的引擎，那么是自动开启事务的，也就是每一条sql都是一个事务（除了select）。
• 由于第一条的原因，所以我们需要autocommit为on，否则每个query都要写一个commit才能提交。
• 在mysql的配置中，默认缺省autocommit就是为on，这里要注意，不用非要去mysql配置文件中显示地配置一下。
• 最关键的来了，当我们显示地开启一个事务，也就是写了begin的时候，autocommit对此事务不构成影响。而不是网上大家说的，必须要写一个query临时设置autocommit为off，否则比如三个query只能回滚最后一个query，这是完全不对的。

MYSQL的事务处理主要有两种方法:

• 用begin,rollback,commit来实现 begin 开始一个事务 rollback 事务回滚 commit 事务确认
• 直接用set来改变mysql的自动提交模式 MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们可以通过 set autocommit=0 禁止自动提交 set autocommit=1 开启自动提交 来实现事务的处理。
• 但注意当你用 set autocommit=0的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务，第二种方法则一个事务结束后又开始一个事务！
• 个人推荐使用第一种方法！ MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！（切记！）

事务并发问题如何发生？

• 答：当多个事务同时操作同一个数据库的相同数据时

事务的并发问题有哪些？

• 脏读：一个事务读取到了另外一个事务未提交的数据

• 不可重复读：同一个事务中，多次读取到的数据不一致

• 幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据

如何避免事务的并发问题？

• 通过设置事务的隔离级别 ①READ UNCOMMITTED（读未提交） 都不避免 ②READ COMMITTED（读已提交） 可以避免脏读 ③REPEATABLE READ（可重复读） 可以避免脏读、不可重复读和一部分幻读 ④SERIALIZABLE （串行化） 可以避免脏读、不可重复读和幻读

• 设置隔离级别： set session|global transaction isolation level 隔离级别名;

• 查看隔离级别： select @@tx_isolation;

并发问题：

• 脏读：在两个事务中，一个事务可以看到另外一个事务中未提交的数据。（如果另外一个事务回滚，那么此事务查看的数据则为脏数据，即无效数据）
• 不可重复读：在两个事务中，一个事务可以读取另外一个事务中提交前的和提交后的数据（因为提交后，数据发生了改变，那么两次读取的数据都不一样，所以称为不可重复度。）
• 幻读：在两个事务中，一个事务可以读取另外一个事务中提交后的数据（特指插入后的数据）。（因为提交后的数据发生了改变，所以读取的数据多了，因此称为幻读，意指视觉上出现了幻觉，读多了）
• 但不可重读和幻读本质都是一样的东西啊，就是一个事务内两次读取同一些数据，结果不一样。这就是统一的幻读。行级锁可以解决更新和删除导致的幻读，间隙锁可以解决插入导致的幻读。

事务和批处理的区别：

• 事务： ①事务底层是在数据库方存储SQL，没有提交事务的数据放在数据库的临时表空间。 ②最后一次提交是把临时表空间的数据提交到数据库服务器执行 ③消耗的是数据库服务器内存
• 批处理： ①批处理底层是在客户端存储SQL ②最后一次执行批处理是把客户端存储的数据发送到数据库服务器执行。 ③消耗的是客户端的内存。
• 总结：事务和批处理单独使用都可以提高效率，当然结合起来使用更加可以提高效率!

Mysql视图

视图和表的区别：

• | 使用方式| 占用物理空间
  

-------- | ----- | ----- 视图 | 完全相同| 不占用，仅仅保存的是sql逻辑 表 | 完全相同| 占用

视图含义：

• 视图可以理解成一张虚拟的表
• 视图的好处： ①sql语句提高重用性，效率高 ②和表实现了分离，提高了安全性

视图的创建语法：

CREATE VIEW  视图名
AS
查询语句;

视图的增删改查:

• 查看视图的数据 ①SELECT * FROM my_v4; ②SELECT * FROM my_v1 WHERE last_name='Partners';

• 插入视图的数据 ①INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);

• 修改视图的数据 ①UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';

• 删除视图的数据 ①DELETE FROM my_v4;

• 注意： ①某些视图不能更新包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all、常量视图、Select中包含子查询、join、from一个不能更新的视图、where子句的子查询引用了from子句中的表

• 视图逻辑的更新 ②CREATE OR REPLACE VIEW test_v7 AS SELECT last_name FROM employees WHERE employee_id>100; ②ALTER VIEW test_v7 AS SELECT employee_id FROM employees;

• 视图的删除 ①DROP VIEW test_v1,test_v2,test_v3;

• 视图结构的查看 ①DESC test_v7; ②SHOW CREATE VIEW test_v7;

变量

MySQL会话变量和系统变量：

• 系统变量又分为全局变量与会话变量。全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
• 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
• （也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。）
• 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。

系统变量：

• 全局变量，服务器每次启动将为所有的全局变量赋初始值，作用域：针对于所有会话（连接）有效，但不能跨重启 ①查看所有全局变量 SHOW GLOBAL VARIABLES; ②查看满足条件的部分系统变量 SHOW GLOBAL VARIABLES LIKE '%char%'; ③查看指定的系统变量的值 SELECT @@global.autocommit; ④为某个系统变量赋值 方式一：SET @@global.autocommit=0; 方式二：SET GLOBAL autocommit=0;

• 会话变量，作用域：针对于当前会话（连接）有效 ①查看所有会话变量 SHOW SESSION VARIABLES; ②查看满足条件的部分会话变量 SHOW SESSION VARIABLES LIKE '%char%'; ③查看指定的会话变量的值 SELECT @@autocommit; SELECT @@session.tx_isolation; ④为某个会话变量赋值 方式一：SET @@session.tx_isolation='read-uncommitted'; 方式二：SET SESSION tx_isolation='read-committed';

区别：

系统变量	区别
全局变量	所有会话中都有效，即重新登录后还是有效
会话变量	当前会话中有效，即重新登录后无效了
注意：

• 若是把mysql服务重启后全局变量和会话变量都是失效了，因此需要到my.init配置文件中去修改即可保证mysql服务重启仍可生效
• @的作用： ①@x 是 用户自定义的变量 (User variables are written as @var_name) ②@@x 是系统 global或系统session变量 (@@global @@session )
• 设置变量时不指定global,session或local，默认使用session。

自定义变量：

• 用户变量 ①作用域：针对于当前连接（会话）生效 ②位置：begin end里面，也可以放在外面使用。 ③声明并初始化： SET @变量名=值; SET @变量名:=值; SELECT @变量名:=值; ④赋值： 方式一：一般用于赋简单的值 SET 变量名=值; SET 变量名:=值; SELECT 变量名:=值; 方式二：一般用于赋表中的字段值 SELECT 字段名或表达式 INTO 变量 FROM 表; ⑤使用： select @变量名;

• 局部变量 ①作用域：仅仅在定义它的begin end中有效 ②位置：只能放在begin end中，而且只能放在第一句使用。 ③声明： declare 变量名 类型 【default 值】; ④赋值： 方式一：一般用于赋简单的值 SET 变量名=值; SET 变量名:=值; SELECT 变量名:=值; 方式二：一般用于赋表中的字段值 SELECT 字段名或表达式 INTO 变量 FROM 表; ⑤使用： select 变量名

用户变量和局部变量的区别：

作用域	定义位置	语法
用户变量	当前会话 会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END中 BEGIN END的第一句话	一般不用加@,需要指定类型

存储过程和函数

存储过程和函数简介：

• 存储过程和函数是在数据库中定义一些SQL语句的集合(包含if/else,case,while等控制语句)，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。
• 存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。

BEGIN…END复合声明语法：

• BEGIN … END语法用于写复合声明，它能出现在被存储的程序中（存储过程，函数，触发器和事件）。
• 一个复合声明能包含多条声明，由BEGIN和END关键字包含。statement_list描述了一条或多条声明的列表，每行的结束符号是分号（;）。statement_list它自己本身是可选的，所以一个空的复合声明也是合法的。
• BEGIN … END块可以嵌套。
• 使用多条声明需要客户端能发送声明结束符（；）。在MYSQL命令行客户端中，使用delimiter命令来处理改变换行符的操作。如：delimiter //,可以将分号（;）换行符，改成双斜线（//）；用于程序体中使用。

存储过程简介：

• 含义：一组经过预先编译的sql语句的集合。

• 好处： ①提高了sql语句的重用性，减少了开发程序员的压力 ②提高了效率 ③减少了传输次数 ④更加安全，不容易出bug。

• 分类： ①无返回无参 ②仅仅带in类型，无返回有参 ③仅仅带out类型，有返回无参 ④既带in又带out，有返回有参 ⑤带inout，有返回有参 ⑥注意：in、out、inout都可以在一个存储过程中带多个

创建存储过程语法：

• 语法描述：

create procedure 存储过程名(in|out|inout 参数名  参数类型,...)
begin
	存储过程体
end

• 类似于方法：

修饰符 返回类型 方法名(参数类型 参数名,...){
	方法体;
}

存储过程注意的点：

• 需要设置新的结束标记，语法： ①delimiter 新的结束标记
• 示例：

delimiter $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
BEGIN
	sql语句1;
	sql语句2;
END $

• 存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end

• 参数前面的符号的意思 ①in:该参数只能作为输入 （该参数不能做返回值） ②out：该参数只能作为输出（该参数只能做返回值） ③inout：既能做输入又能做输出

• 调用存储过程： ①call 存储过程名(实参列表) <1>mysql的存储过程参数没有默认值，所以在调用Mysql存储过程时，不能省略参数，但是可以使用null代替。

• 通常begin-end用于定义一组语句块，在各大数据库中的客户端工具中可直接调用，但在mysql中不可用。begin-end、流程控制语句、局部变量只能用于函数、存储过程内部、游标、触发器的定义内部。

delimiter的详细解释：

• 其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。 默认情况下，delimiter是分号“；”。

• 在命令行客户端中，如果有一行命令以分号结束， 那么回车后，mysql将会执行该命令。如输入下面的语句 mysql> select * from test_table; 然后回车，那么MySQL将立即执行该语句。

• 但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中包含有分号。 默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句。 因为mysql一遇到分号，它就要自动执行。例如在语句RETURN '';时，mysql解释器就要执行了。

• 这种情况下，就需要事先把delimiter换成其它符号，如//或$$。

函数定义：

• 学过的函数：LENGTH、SUBSTR、CONCAT等
• 创建函数语法：

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体
END

• 调用函数: ①SELECT 函数名（实参列表）

函数和存储过程的区别：

项目	关键字	调用语法	返回值	应用场景
函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个
存储过程	PROCEDURE	CALL存储过程()	可以有0个或多个 一般用于更新	一般用于更新

流程控制结构

分支结构定义：

• if函数 ①语法：if(条件，值1，值2) ②特点：可以用在任何位置
• case语句（重点） ①语法： 情况一：类似于switch case 表达式 when 值1 then 结果1或语句1(如果是语句，需要加分号) when 值2 then 结果2或语句2(如果是语句，需要加分号) ... else 结果n或语句n(如果是语句，需要加分号) end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要） 情况二：类似于多重if case when 条件1 then 结果1或语句1(如果是语句，需要加分号) when 条件2 then 结果2或语句2(如果是语句，需要加分号) .. else 结果n或语句n(如果是语句，需要加分号) end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要） ②特点：可以用在任何位置
• if else if语句 ①语法： if 情况1 then 语句1; elseif 情况2 then 语句2; ... else 语句n; end if; ②特点：只能用在begin end中！
• 三者比较：

。	应用场合
if函数	简单双分支
case结构	等值判断的多分支
if结构	区间判断的多分支

循环结构定义：

• 位置：只能放在begin end中
• 特点：都能实现循环结构
• 注意：这三种循环都可以省略名称，但如果循环中添加了循环控制语句（leave或iterate）则必须添加名称
• 对比： <1>loop 一般用于实现简单的死循环 <2>while 先判断后执行 <3>repeat 先执行后判断，无条件至少执行一次
• while语法：

【名称:】while 循环条件 do
		循环体
end while 【名称】;

• loop语法：

【名称：】loop
		循环体
end loop 【名称】;

• repeat语法：

【名称:】repeat
		循环体
until 结束条件 
end repeat 【名称】;

• 循环控制语句： ①leave：类似于break，用于跳出所在的循环 ②iterate：类似于continue，用于结束本次循环，继续下一次

触发器与游标与事件

触发器简介：

• 触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行。
• 简单来说就是你执行一条sql语句，这条sql语句的执行会自动去触发执行其他的sql语句。
• 超简说明：sql1->触发->sqlN，一条sql触发多个sql。
• MySQL触发器概念、原理与用法详解
• 触发器创建的四个要素： ①监视地点（table） ②监视事件（insert/update/delete） ③触发时间（after/before） ④触发事件（insert/update/delete）
• after和before的区别： ①after操作，是在执行了监视动作后，才会执行触发事件 ②before操作，是在执行了监视动作前，会执行触发事件
• 触发器语法分析： ①触发器的名称为t1，触发时间为after，监视动作为insert，监视ord表。 ②for each row: <1>在oracle触发器中，触发器分为行触发器和语句触发器。 <2>遗憾的是mysql目前不支持语句级触发器。 ③begin和end之间写触发事件，这里是一个update语句。

create trigger t1 
after
insert
on ord
for each row
begin
 update goods set num=num-2 where gid = 1;
end$

游标简介：

• CURSOR游标：简单通俗的说，游标就是游动的标识/标志 1条sql，对应N条结果集的资源，取出资源的接口/句柄，就是游标，沿着游标，可以一次取出1行。
• 什么是游标，说的简单直白点，游标的作用就是用来取多条数据，遍 历数据(一句话概括，其实就是处理多行数据)有点像java集合中的迭代器一样。
• 定义游标的语法： DECLARE 声明 DECLARE 游标名 CURSOR FOR SELECT查询语句; OPEN 打开 OPEN 游标名; FETCH 取值 FETCH 游标名 INTO 变量1,变量2,变量3; CLOSE 关闭 CLOSE 游标名;
• 注意：MySQL游标只能用于存储过程(和函数)。游标主要用于交互式应用。
• 链接：游标详解
• 游标与视图比较： ①游标和视图的本质是不同：一个是作为指针操作，一个是作为数据库对象来展现给用户。 ②占用的资源不用：游标占用的资源很大，而视图占用的资源很小。 ③工作方式不同：游标对需要的数据操作是针对行进行操作的，而视图是针对基表的一个整体的查询。

事件简介：

• 在MySQL 5.1中新增了一个特色功能事件调度器（Event Scheduler），简称事件。它可以作为定时任务调度器，取代部分原来只能用操作系统的计划任务才能执行的工作。另外，更值得一提的是，MySQL的事件可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下是非常实用的。
• 事件调度器是定时触发执行的，从这个角度上看也可以称作是“临时触发器”。但是它与触发器又有所区别，触发器只针对某个表产生的事件执行一些语句，而事件调度器则是在某一段（间隔）时间执行一些语句。

总结：

• 链接：mysql自定义函数、存储过程、游标、触发器、事件(定时任务)
• 可以将业务层的代码写到存储过程或者函数中。