yonversion: mysql Ver 8.0.21 for Win64 on x86_64 (MySQL Community Server - GPL)

MySQL

mysql基础

创建数据库

CREATE DATABASE 数据库名;

使用 mysqladmin工具   mysqladmin -u root -p create 数据库名;

约束

对表中的数据或者字段做一个限制权限

主键约束(primary key)


自增长约束（ auto_increment ）
auto_increment 默认为第1开始自增，该字段必须有唯一约束，不为空not null,只能用于整型，最大限制条件跟数据类型的最大空间有关。


删除 auto_increment 的字段时，用 delete 删除该字段是从 断点 开始自增， 用 truncate 将表删除再插入数是从1开始。   


非空约束( not null)


唯一约束( unique)


默认约束(default)
 字段名 字段数据类型 default '设置默认值' 
例子：  address varchar(100) default '广州' ; 再插入的时候也可以不用指定该位置值，默认是广州。


零填约束(zerofill)
 定义字段类型时，插入的数据小于定义的数据类型长度，就会再前面扑0填充。当使用zerofill时，默认会加载 unsigened(无符号)属性，使用该属性数值范围是原来的2倍，


外键约束(foreign key(本外键id) references  目标表(目标表主键)) 注意： 给外键起名时不能使用大小写，格式要和目标关联的主键一致，否则外键不起作用！

创建表

选项解释
【
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。ENGINE=InnoDB DEFAULT CHARSET=utf8;
】
CREATE TABLE table_name（
	'表头信息' 数据类型 权限 ,
）
如果不存在该表则创建
//unsigned 无符号  auto_increment 自增
create table if not exists  `runoob_tbl`(
   `runoob_id` INT unsigned auto_increment, 
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
    primary key ( `runoob_id` )
)engine = innodb default charset=utf8;

table 的基础操作

1.SHOW COLUMNS from  table_name ; 查看表的基本属性信息。

2.删除，添加或修改表字段 ：

​ 添加 alter 修改 drop

情况：如果数据表中只剩余一个字段则无法使用 DROP 来删除字段。可以使用 add 先添加一个字段再把整个删除

​ alter table tablename add i init; 给改表添加一个字段 i 类型为init

3. MODIFY（修改） CHANGE（改变)

alter table tablename modify username char(20) username 字段数据类型增加20个

4.修改默认值

mysql 默认值为null

alter table 表名 alter set id dafult 100

5. 修改表名

   alter tabel 旧表名 RENAME TO 新表名 
   

alter 修改

ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：

{  ADD COLUMN <列名> <类型>   ---> 增加新的字段
 | CHANGE COLUMN <旧列名> <新列名> <新列类型> --->修改旧字段
 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
 | MODIFY COLUMN <列名> <类型>  ---> 修改旧的字段类型
 | DROP COLUMN <列名>  ---> 删除字段
 | RENAME TO <新表名> }  ---> 重写命名表名
 
 

mysql中的键

主键 PRIMARY KEY。

防止恶意外键插入

外键 FOREIGN KEY。

唯一键 UNIQUE KEY

CONSTRAINT 限制
REFERENCES 参考
		FOREIGN KEY (sid) REFERENCES shanping(sid)

#-----------------------------商品-----------------------------------------------
create table if not exists shanping (
	
	sid int primary key auto_increment,
	s_name varchar(100) not null,
	s_jiage float(50) not null,
	s_time  timestamp 

)engine = innodb default charset=utf8;

insert into shanping values(null,'水果',33.5,now());
select * from shanping;



#-----------------------------库存量----------------------------------------------

create table if not exists kucun (

	kid int auto_increment,
    #外键
	sid int ,
    #编号
	bianhao int not null ,
    #剩余量
	sun int not null,
    #时间
	k_time  timestamp,
	PRIMARY KEY (kid),
	#将sid作为与商品表的外键
	FOREIGN KEY (sid) REFERENCES shanping(sid)

)engine = innodb default charset=utf8;

select * from kucun where sid=1;
insert into kucun values(null,1,6522,1000,now());


#-----------------------------删除外键----------------------------------------------

ALTER TABLE 表明
DROP FOREIGN KEY 外键字段名

使用数据库

show databases 查找所有数据库
show tables 查询所以表
use 数据库名
use 数据库表

删除数据库

drop database <数据库名>;

使用mysqladmin工具删除数据库  mysqladmin -u root -p drop RUNOOB

删除数据库表

DROP TABLE 表名 ;

插入数据

INSERT INTO table_name ( 目标头1, 目标头2,...... ) VALUES  ( "字符串", 整型, ......);
INSERT INTO table_name ( 目标 ) VALUES  ( 插入内容编辑区 );

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

update 更新修改

update tabelname set 更新字段 where 更新的目标字段

delete 更新修改

delete from tablename where 条件 

查询表数据

select * from

select * from 表名;	

where

条件筛选
不区分大小写
select * from tablename where username = 'lihua';
区分大小写 binary
select * from tablename where binary  username = 'LIHUA'； 

执行顺序 
FROM, including JOINs
WHERE
GROUP BY
HAVING
WINDOW functions
SELECT
DISTINCT
UNION
ORDER BY
LIMIT and OFFSET

操作符	where 追加条件筛选 id = ? or id <?.....	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

update

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
update 表名 set 字段名 = ‘更改内容’ ,字段名2 = ‘更改内容2’... where id = ?

Like

 通常跟在where后面做细化筛选
 selet 字段1，字段2 from 表名 where 目标条件 like ‘%带有的一些特征’
 
筛选条件解释：
 %  字符来表示任意字符，
'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

Union

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
关联多个 select 查询条件，通常是查询两张表是否有目标条件
例子 查询两张表带有张三这个人的信息
select *from 表名 where username = 'zhangsan'
union // union all 同意重复数据
select *from 表名2 where username = 'zhangsan'

Order by 排序查询

ASC 升序 最小的排最前
DESC 降序 最大的排最前
命名：
select * from tablename order by  可以为日期可以为id..  ASC

order by 添加转码 CONVERT(该字段为中文就使用 using gbk);

mysql分组

GROUP BY  语句根据一个或多个列对结果集进行分组。
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
coalesce(a,b,c) 

select name,count(*) from employee_tbl group by name WITH ROLLUP;

select coalesce(name,'总数'),count(*) from employee_tbl group by name WITH ROLLUP;

mysql连接

INNER JOIN（内连接,或等值连接）=两张数据表的交集**：获取两个表中字段匹配关系的记录。

LEFT JOIN（左连接）=表的右：**获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）：** 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

多表查询

一对多

通常需要设一个外键 foreign key

创建外键

#constraint限制
constraint 外键名 foreign key 字段名 references 目标表名 目标主键;

环境搭建
#创建部门表
create table if not exists Department(
	d_number int primary key auto_increment,
	d_name varchar(100) not null
)engine = innodb default charset=utf8;
#员工表
create table if not exists Staff(
	s_id int primary key auto_increment,
	s_name varchar(100) not null,
	DS_FK int not null,
	constraint staff_fk foreign key(DS_FK) references Department(d_number) #创建外键指向目标表的主键
)engine = innodb default charset=utf8;

select * from Department;
select * from Staff;
#插入数据
insert into Department values(1001,'测量部'),(1002,'施工部'),(1003,'土木部'),(1004,'水电部');
insert into Staff values(1,'刘德华',1001),(2,'张国荣',1001),(3,'陈冠希',1002),(4,'李德华',1003),(5,'东华',1004);

创建方式：

创建表时创建外键指定目标表

修改表时添加外键指定目标表

alter table table_name add constraint 外键名 foreign key 字段名 references 目标表名 目标主键;

删除外键

alter table table_name drop foreign key 外键名 ;

多对多

需要根据两张表的 id 匹配创建一张中间表

环境搭建

#创建学生表
create table if not exists student(
	sid int primary key auto_increment,
	sname varchar(100),
	age int
)engine = innodb default charset=utf8;

#课程表
create table if not exists curriculum(
	cid int primary key auto_increment,
	cname varchar(100)
)engine = innodb default charset=utf8;

#中间表
create table if not exists sa_table(
	sid int,
	cid int,
	info varchar(100)
)engine = innodb default charset=utf8;

#通过修改表添加：中间表关联两个目标表
alter table sa_table add foreign key (sid) references student(sid);
alter table sa_table add foreign key (cid) references curriculum(cid);

select * from student;
select * from curriculum;
select * from sa_table;

insert into student values(1,'刘德华',22),(2,'费玉清',23),(3,'张国',22);
insert into curriculum values(1,'导演十二式'),(2,'如何表演'),(3,'如何唱歌'),(4,'编剧');
insert into sa_table values(1,1,'会三种'),(1,2,'会三种'),(1,3,'会三种'),(2,3,'会一种'),(3,2,'会两种'),(3,3,'会两种');

查询

查询的种类

1交叉连接查询[会产生笛卡尔积]
笛卡尔积：会产生数据冗余
	select * from T1,T2..;

2内连接查询[使用的关键字inner join --inner 可以省略]
	隐式内连接 : select * from T1,T2 where 条件;
	显式内连接 : select * from T1 inner join T2 on 条件;

3外连接拆线呢[outer join --outer 可以省略]
	左连接： left outer join 
	select * from T1 left outer join  T2 on 条件；

	左连接： right outer join 
	select * from T1 right outer join  T2 on 条件；

#mysql不支持  可以用unico代替
	满外连接： full outer join
	select * from T1 full outer join  T2 on 条件；

4子查询
规定 x = 行   y = 列

  select 的多层嵌套
  可以查询4个特点：
  单行单列 
  #查询 名字为a id为1 的一行一列
  select * from T2 where id = 1 and ( select * from T1 where name = 'a');

  多行单列 
   select * from T1 where x = 1 or x = 2 or x = 3  and ( select * from T1 where y = 'a') --多行 

  
  单行多列
   select * from T1 where y = users  and ( select * from T1 where x = 'username' x='password') --多列
  
  
  多行多列
  select * from T1 where y = 1 y=2 and ( select * from T1 where x = 'username' x='password') --多列多行
  
  

查询条件关键字
 
 all 是否满足所有条件
 in 查看一些条件是否满足在in 子语句所包含的信息
 any 任意一个满足条件
 some 一些条件满足
 exists 存在条件
 
聚合函数： 会将目标字段的数据进行一行输出，并使用分隔符
	group_concat( [distinct排除重复值] 目标字段 [order by 进行排序查询 asc/desc] [设置分隔符 separator ''] )
 
  
5表自关联
 将一张表当成多表来用
 
 将外键指向内表，并给该表起别名作为第二张表进行sql操作。
 
 create table if not exists student(
	sid int primary key auto_increment,
	sname varchar(100),
	s2_id int,
    foreign key (s2_id) references stduent(sid) --表设置一个主键一个外键，外键连向主键
)engine = innodb default charset=utf8;
 
 select * from student s1 , student s2 where s1.sid = s2.s2_id; 
 
 

冗余数据：笛卡尔积

自连接

交叉查询和内连查询

#----------------------------------多表查询之交叉查询（一对多） -------------------------------
select * from Department,Staff;
#隐式
select * from Department,Staff where Department.d_number =  Staff.ds_fk;
#显式
select * from Department inner join Staff on Department.d_number =  Staff.ds_fk ;
#增加条件筛选
select * from Department inner join Staff on Department.d_number =  Staff.ds_fk and d_name = '测量部';

子查询

将多层查询进行拆分， 并通过外键进行关联查询。 再做一些查询条件的设置
查询测量部和施工部有多少个人，
分两个部份 
#查看员工表id和名字
select s_id, s_name from Staff
#查看部门信息
select * from Department where d_name = '测量部' or d_name = '施工部'
#通过外键匹配两张表，并用 in 做拼接
select s_id, s_name from Staff where ds_fk in (select d_number from Department where d_name = '测量部' or d_name = '施工部');

跨库查询

mysql null 空值设置

Sql server 常用函数

求平均数

​ SELECT AVG(字段) FROM table_name

返回匹配条件的行数

​ count(目标字段)

​ count(*) 函数返回表中的行的总数：

​ count(distinct 目标字段) 返回列的不同不重复字段

最大值

​ max(目标字段)

​ select max(目标字段) from table_name;

最小

​ min(目标字段)

总数

​ sum(目标列字段)

把字段的值转换为大写。

​ UCASE() 函数

把字段的值转换为小写

​ LCASE()

提取目标字段字符

​ MID(目标字段，start从第0个开始提,end提到第n个结束)

返回目标字段 字符长度

​ LEN() 函数返回文本字段中值的长度。

把数值字段舍入为指定的小数位数。(取舍小数点后数，float)

​ ROUND(column_name必需。要舍入的字段。,decimals可选。规定要返回的小数位数。) 函数

NOW() 函数

​ NOW() 函数返回当前系统的日期和时间。

格式话字符

​ FORMAT(column_name必需，要格式化的字段。,format必需。规定格式。)

​ 列子： 对日期格式 DATE_FORMAT(Now(),'%Y-%m-%d')

别名 as

对表的别名

对字段的别名

SELECT 原字段 as 别名 FROM 原表名 AS 别名表 ；

通识符

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符

**REGEXP ** 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

---

mysql进阶

mysql 事务执行原理 ： 数据库的多版本并发控制（MVCC）。即通过回滚（rollback操作）

事务

事务隔离级别设置

show engines 查看引擎

start transaction 开启默认事务

show variables like 'transaction_isolation'; 查看隔离别

select @@transaction_isolation;查看隔离别

----------------------------------------------隔离级参数解释------------------------------------------------------------------
设置隔离级别
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
set global | session transaction isolation level 4中隔离级别 ;
session当前事务隔离级别
global 全局事务隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

mysql默认的隔离级别为 可重复读 repeatable read

读未提交（READ UNCOMMITTED）
读已提交（READ COMMITTED）
可串行化（SERIALIZABLE）
可重复读（REPEATABLE READ）


其中level有4种值：
level: {
  REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}

关键词：GLOBAL
SET GLOBAL TRANSACTION ISOLATION LEVEL level;
* 只对执行完该语句之后产生的会话起作用
* 当前已经存在的会话无效

关键词：SESSION
SET SESSION TRANSACTION ISOLATION LEVEL level;
* 对当前会话的所有后续的事务有效
* 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务
* 如果在事务之间执行，则对后续的事务有效。

无关键词
SET TRANSACTION ISOLATION LEVEL level;
* 只对当前会话中下一个即将开启的事务有效
* 下一个事务执行完后，后续事务将恢复到之前的隔离级别
* 该语句不能在已经开启的事务中间执行，会报错的

在启动中就设置隔离级别：
--transaction-isolation= 4个隔离级别

----------------------------------------------事务执行---------------------------------------------------------------------
事务的启动：
将自动提交改为手动提交
Set autocommit = 0 ; 开启事务 

两种情况执行事务： 一个是提交了，一个是还在操作中还没提交， 一个是操作中，回滚了[rollback]撤回。

1.begin 
 		事务操作
	commit

2. begin 
		事务操作
	rollback //回滚

事务并发出现的情况

脏读（Dirty Read）A读之前 ， B就对数据做了修改，但还没提交， A就读了B修改的内容，B又回滚了，A再去读发现数据又变了！（读了还没提交的内容，另一方回滚的时候，数据又变会原来的样子）

不可重复读（Non-Repeatable Read）A读了，B修改了，A又读了发现数据被修改了重复：A读到的永远在B修改的最新数据（重复读取最新内容）

幻读（Phantom）

脏读（Dirty Read）

一个事务读到了另一个未提交事务修改过的数据

会话B开启一个事务，把id=1的name为武汉市修改成温州市，此时另外一个会话A也开启一个事务，读取id=1的name，此时的查询结果为温州市，会话B的事务最后回滚了刚才修改的记录，这样会话A读到的数据是不存在的，这个现象就是脏读。（脏读只在读未提交隔离级别才会出现）

不可重复读（Non-Repeatable Read）

一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值。（不可重复读在读未提交和读已提交隔离级别都可能会出现）

会话A开启一个事务，查询id=1的结果，此时查询的结果name为武汉市。接着会话B把id=1的name修改为温州市（隐式事务，因为此时的autocommit为1，每条SQL语句执行完自动提交），此时会话A的事务再一次查询id=1的结果，读取的结果name为温州市。会话B再此修改id=1的name为杭州市，会话A的事务再次查询id=1，结果name的值为杭州市，这种现象就是不可重复读。

幻读（Phantom）

一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。（幻读在读未提交、读已提交、可重复读隔离级别都可能会出现）

会话A开启一个事务，查询id>0的记录，此时会查到name=武汉市的记录。接着会话B插入一条name=温州市的数据（隐式事务，因为此时的autocommit为1，每条SQL语句执行完自动提交），这时会话A的事务再以刚才的查询条件（id>0）再一次查询，此时会出现两条记录（name为武汉市和温州市的记录），这种现象就是幻读。

事务的隔离级别

MySQL的事务隔离级别一共有四个，分别是读未提交、读已提交、可重复读以及可串行化。

MySQL的隔离级别的作用就是让事务之间互相隔离，互不影响，这样可以保证事务的一致性。

隔离级别比较：可串行化>可重复读>读已提交>读未提交

隔离级别对性能的影响比较：可串行化>可重复读>读已提交>读未提交

由此看出，隔离级别越高，所需要消耗的MySQL性能越大（如事务并发严重性），为了平衡二者，一般建议设置的隔离级别为可重复读，MySQL默认的隔离级别也是可重复读。

读未提交（READ UNCOMMITTED）

在读未提交隔离级别下，事务A可以读取到事务B修改过但未提交的数据。

可能发生脏读、不可重复读和幻读问题，一般很少使用此隔离级别。

读已提交（READ COMMITTED）

在读已提交隔离级别下，事务B只能在事务A修改过并且已提交后才能读取到事务B修改的数据。

读已提交隔离级别解决了脏读的问题，但可能发生不可重复读和幻读问题，一般很少使用此隔离级别。

可重复读（REPEATABLE READ）

在可重复读隔离级别下，事务B只能在事务A修改过数据并提交后，自己也提交事务后，才能读取到事务B修改的数据。

可重复读隔离级别解决了脏读和不可重复读的问题，但可能发生幻读问题。

提问：为什么上了写锁（写操作），别的事务还可以读操作？

因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。

可串行化（SERIALIZABLE） 加锁实现阻塞队列

读读操作

读写

写读

写写

各种问题（脏读、不可重复读、幻读）都不会发生，通过加锁实现（读锁和写锁）。

隔离级别的实现原理

使用MySQL的默认隔离级别（可重复读）来进行说明。

每条记录在更新的时候都会同时记录一条回滚操作（回滚操作日志undo log）。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。即通过回滚（rollback操作），可以回到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

提问：回滚操作日志（undo log）什么时候删除？

MySQL会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。

提问：什么时候不需要了？

当系统里么有比这个回滚日志更早的read-view的时候。

存储过程

基本介绍

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

#delimiter分隔符

#创建
delimiter $$  #(两个符号 $$ // 前后都要一致)
create procedure 存储过程名 ( 变量或参数 [in out inout] 参数名 数据类型[integer cache]  )
	begin #开始
        #业务逻辑
		#变量赋值
        set @形参 = x 
        #声明变量#int, float, date,varchar(length)
        declare 变量名 int(数据结构) unsigned default xxxx;
        ..........
        判断语句
        if 条件 then 
                mysql语句 ；
            else 
                mysql语句 ；
	 	end if;  
	end 
$$  #结束
delimiter ;  结束

-----------------------------------------其他用法------------------------------------------------------

参数体解释
 in 输入参数 表示调用时需要传入特定值
 out 输出参数
 inout 输出输入参数
 
 
嵌套开始和结束语块 begin end 
begin 
	begin
		begin
		.....
        end 
    end 
end 
给开始结束语句贴标签
标签1:begin 
	标签2:begin
		begin
		....
        end 
    end 标签2 ;
end 标签1;


存储过程变量
set 变量名 = @语句

存储过程条件控制语句

if-then-else 语句

case语句：

while ···· end while

repeat···· end repeat

它在执行操作后检查结果，而 while 则是执行前进行检查。

. loop ·····endloop

loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

LABLES 标号：

标号可以用在 begin repeat while 或者 loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

ITERATE迭代

ITERATE 通过引用复合语句的标号,来从新开始复合语句:

MySQL存储过程的控制语句

(1). 变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3()  
     -> begin 
     -> declare x1 varchar(5) default 'outer';  
     -> begin 
     -> declare x1 varchar(5) default 'inner';  
      -> select x1;  
      -> end;  
       -> select x1;  
     -> end;  
     -> //  
mysql > DELIMITER ;

(2). 条件语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

2. case语句：
mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> case var  
     -> when 0 then   
     -> insert into t values(17);  
     -> when 1 then   
     -> insert into t values(18);  
     -> else   
     -> insert into t values(19);  
     -> end case;  
     -> end;  
     -> //  
mysql > DELIMITER ; 
case
    when var=0 then
        insert into t values(30);
    when var>0 then
    when var<0 then
    else
end case

(3). 循环语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

while 条件 do
    --循环体
endwhile

2. repeat···· end repeat

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc5 ()  
     -> begin   
     -> declare v int;  
     -> set v=0;  
     -> repeat  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> until v>=5  
     -> end repeat;  
     -> end;  
     -> //  
mysql > DELIMITER ;
-----------------------------
repeat
    --循环体
until 循环条件  
end repeat;

3. loop ·····endloop

   loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

4. LABLES 标号：

标号可以用在 begin repeat while 或者 loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

(4). ITERATE迭代

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> if v=3 then   
     -> set v=v+1;  
     -> ITERATE LOOP_LABLE;  
     -> end if;  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v>=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

存储函数

触发器

当前触发器只能执行 行级语句，不能执行语块。 作用： 可以做数据库日志，数校验，和保存数据完整性！

查看触发器

show triggers;

删除触发器

drop triggers name;

创建

单表触发
create trigger 触发器名 before|after  触发类型（inster update select delect..）
on 目标表名 for each row 
业务执行语句；

delimiter $$ or //
多表触发
create trigger 触发器名 before|after  触发类型（inster update select delect..）
on 目标表名 for each row 
begin
	业务执行语句
end 
$$ or //
delimiter ;

案例
定义一张用户表 ，一张日志表 ，当用户表增删改查时，会被记录到日志表中。
用户表
create table tb_users (
	u_id int primary key auto_increment,
	u_username varchar(100) not null,
	u_password varchar(100) not null
)engine = innodb default charset=utf8;

#添加字段
alter table tb_users add u_time TIMESTAMP ;


日志表
create table user_log (
	l_id int primary key auto_increment,
	l_time timestamp,
	l_info varchar(100)
)engine = innodb default charset=utf8;


#设置触发器，执行一次触发器后，我们在对用户表进行操作时就会自动触发日志表
#定义触发器
create trigger triggeruser after insert 
on tb_users for each row
#当对用户表进行插入时，就往日志更新一条数据。
insert into user_log values (null,now(),'新插入数据');

对用户表进行操作
insert into tb_users values (1,'zhangsna','123456',now());

用户表：

日志表

增加返回值：

更新日志时返回操作数据信息

insert  ==> new 表示新增的字段
update  ==> new 更新后   old 更新前 
delete  ==> old 
new.目标数据库字段
old.目标数据库字段
#拼接
concat('',new/old.字段)
#定义触发器
create trigger triggeruser after insert 
on tb_users for each row
#当对用户表进行插入时，就往日志更新一条数据。
insert into user_log values (null,now(),concat('新插入数据',new.目标表字段));

视图

创建视图的目的： 减少重复的sql语句（抽出sql并封装到视图中），过滤敏感信息，匿名数据库！

#------------------------c创视图--------------------------------
create or replace view #viewname
as 
#sql语句 增 ， 删 ， 改， 查 

#使用视图时直接查询视图
select * from viewname;

删除视图

DROP VIEW view_name

更新视图 a{*&:OR REPLACE VIEW

CREATE OR REPLACE VIEW old_view_name 
AS
跟新内容sql语句

分页查询

Limit star,end 第star页，到第end页

top x percent 返回该表百分之x 显示到页面出来

索引

解释： 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引类型 和 索引方法

单例索引

普通索引创建

create index 索引名 On 目标表名 （ 字段1....  ）;

修改索引
ALTER table tableName ADD INDEX indexName(columnName)


#------------------------------1创建表的时候直接指定--------------------------------

CREATE TABLE mytable(  
 
ID INT NOT NULL,   

username VARCHAR(16) NOT NULL,  
 
#绑定目标字段做为索引
INDEX [索引名] (username(length))  
 
);

#------------------------------2外部创建索引--------------------------------

create index 索引名 on tablename(目标字段)

#------------------------------3以修改表来添加索引--------------------------------

alter table 目标表名 add index 索引名（目标字段名）


删除索引
DROP INDEX [indexName] ON mytable; 

查看索引

固定写法
查看数据库索引
select * from mysql.'innodb_index_stats'.a where a.'database_name' = '目标数据库'

查看数据库中表的索引
select * from mysql.'innodb_index_stats'.a where a.'database_name' = '目标数据库' and a.table_name like '%目标表名%'；

查看表索引
show index from 表名
 SHOW INDEX FROM table_name\G   ;  可以通过添加 \G 来格式化输出信息。

唯一 索引

唯一索引： 该字段时唯一的，不能重复出现的，可以为空值。

#------------------------------创建唯一索引--------------------------------
创建表时创建
在外添加唯一
修改表创建唯一
create unique 唯一索引名 on tablename(目标字段)
alter table 目标表名 add unique 索引名（目标字段名）

组合索引

组合索引是 在指定目标表时，同时指定多个字段作为索引,该索引可以时唯一索引和普通索引

create index index_name on tablename(字段1，字段2..);
注意：
 查询组合索引时，以最左原则生效， 
 #不符合最左原则所以不会用到索引！
 select * from tablename where 字段2； 
 #符合
 select * from tablename where 字段1；
 select * from tablename where 字段1 and 字段2 ； 
 select * from tablename where 字段2 and 字段1 ； 

全文索引

版本要求 mysql 5以上的 myisam 和 innodb 存储引擎 才支持全文索引。

创建条件：

字符数据类型 char varchar text 才能创建索引。

全文索引限制

#目标全文索引需要在所限定的长度范围内才能失效！ 
#查看默认的全文索引

show variables like '%ft%'

variable_name    |    values

ft_boolean_syntax	|  + -><()~*:""&|
ft_max_word_len	| 84
ft_min_word_len	| 4
ft_query_expansion_limit	|20
ft_stopword_file	|   (built-in)
innodb_ft_aux_table	
innodb_ft_cache_size	|  8000000
innodb_ft_enable_diag_print	OFF
innodb_ft_enable_stopword	|  ON
innodb_ft_max_token_size	| 84
innodb_ft_min_token_size	| 3  #模糊查询字符数 不能 低于3个
innodb_ft_num_word_optimize  |	2000
innodb_ft_result_cache_limit	| 2000000000
innodb_ft_server_stopword_table	
innodb_ft_sort_pll_degree	| 2
innodb_ft_total_cache_size	| 640000000
innodb_ft_user_stopword_table	

创建全文索引

创建表时指定
外部创建时指定
create fulltext index 全文索引名 on 表名（目标字段）；

修改表时添加
alter table 表明 add fulltext index 全文索引名(目标字段)

全文索引特有的模糊查询

match(目标字段)
against('模糊查询字符')
查询
select * from table_names where match(目标字段) against('模糊查询字符')；

普通的模糊查询'like'
select * from table_names where 目标字段 like '%模糊查询字符%'

#-----------------------------------------案例--------------------------------------------------------

show variables like '%ft%'

select * from Book;

创建图书表
create table if not exists Book(
	bid int primary key auto_increment,
	title varchar(100) not null,
	body varchar(500)
)engine = innodb default charset=utf8;

#插入文本
insert into Book values (null,'爱丽丝','White Queen: Alice, you cannot live your life to please others. The choice must be yours, because when you step out to face that creature, you will step out alone. ');

insert into Book values (null,'福尔摩斯','Do you know the big problem with a disguise,Mr Holmes? However hard you try, its always a self-portrait.I think you re damaged, delusional and believe in a higher power. In your case, its yourself.');


#全文索引
create fulltext index bodyindex on Book(body);

#模糊查询，带有your 的字段
select * from Book where match(body) against('your');

空间索引

geometry 空间数据	 
point  点
linestring  线
polygon  多边形

临时表

​ MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

创建临时表, 断开mysql连接时，临时表自动删除。

create temporary table 表名 

复制表

1. 最快的 ： show create table 目标table名 ；  反向查看创建的代码块，通过复制代码块来再创建一个表。 

show create table employee_tbl ；
反推结果体
CREATE TABLE `employee_tbl` (
  `id` int NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `signin` tinyint NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


2. create table newtable like oldtable ; 只能复制表体，不能复制内容

3. create table newtable select 字段.... 

元数据（mysql系统数据）

获取服务器元数据

以下命令语句可以在 MySQL 的命令提示符使用，也可以在脚本中 使用，如PHP脚本。

命令	描述
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

序列的使用

一张表只有一个主键自增，如果其他也想实现自增就加入字段属性 AUTO_INCREMENT

获取自增值 LAST_INSERT_ID( )

处理重复数据

统计重复数据

过滤重复数据 distinct(不同的 )

select distinct 字段1字段2... from tablename group by (字段1字段2...);

删除重复数据

mysql8.0之后加的函数

窗口函数

重在分组并对各组信息进行操作

分类

创建
window_funtion (参数) over (
	partition by : 对目标字段分区
    order by : 对目标字段分组排序
    frame_claue : 窗口大小
)
#partition 分区
窗口函数之序号函数
row_number | rank | dense_rank  over (
	partition by : 对目标字段分区
    order by : 对目标字段分组排序
)
    
分布式函数: 可以计算
    #percent_rank 排名百分比
    
    #cume_dist  重复的当前值 / 分组的总数
percent_rank() | cume_dist()  over (
	partition by : 对目标字段分区
    order by : 对目标字段分组排序
)

序号函数
row_number()  | rank() | dense_rank() over (
	partition by : 对目标字段分区
    order by : 对目标字段分组排序
)


前后函数
lag(参数,n) | laead(参数,n) over (
	partition by : 对目标字段分区
    order by : 对目标字段分组排序
)

头尾函数
first_value(参数,n) | laste_value(参数,n) over (
	partition by : 对目标字段分区
    order by : 对目标字段分组排序
)


其他函数
ntile: 将分区中的有序数据分成n 组，记录等级数
nth_value(参数,n) | ntile(n) over (
	partition by : 对目标字段分区
    order by : 对目标字段分组排序
)

环境搭建

#一张部门表 一张员工表，使用外键连接

#创建一张部门表
create table if not exists win_department(
	did int primary key,
	dname varchar(100)
)engine = innodb default charset=utf8;

#创建一张部门表
create table if not exists win_itstaff(
	sid int primary key auto_increment,
	sname varchar(100),
	age int,
	hiredate date, #注意插入日期格式为'19991212' 
	salary int,
	defk int,
	foreign key (defk) REFERENCES win_department(did)
)engine = innodb default charset=utf8;

select * from win_itstaff;
select * from win_department;
select * from win_department a inner join win_itstaff b on a.did = b.defk; 

    insert into win_department values (1001,'前端部'),(1002,'后台部'),(1003,'前端部'),(1004,'运维部'),(1005,'测试部');
insert into win_itstaff values 
(null,'李华',22,20210618,4500,1001),
(null,'张三',23,20210718,4500,1002),
(null,'李四',26,20210315,5000,1003),
(null,'王五',28,20190618,6000,1004),
(null,'陈辉',35,20190718,12000,1005),
(null,'东风',30,20211108,7000,1005),
(null,'科华',27,20210208,7000,1001),
(null,'键东',23,20210312,4500,1002),
(null,'子良',23,20221203,8500,1002);

#---------------------------------------测试区-------------------------------------------------

select 
	dname,
	sname,
	salary,
#---------------------分布式函数------------------
	#查出当前工资所占该组的比例
	cume_dist() over (
		#对部门进行分组
		partition by dname 
		#对工资进行排序
		order by salary desc
	) as cumedist,
	#不常用  （排序row - 1）/ (分组的总数 -1 ) = percent_rank值
	percent_rank() over ( 
		#对部门进行分组
		partition by dname 
		#对工资进行排序
		order by salary desc
	) as percentranks,
	
#---------------------序号函数------------------
    #查出再该部门工资的高低，
    #工资相同时，则以顺序进行排序	
    row_number() over (
            partition by dname
            order by salary desc 
    ) as rown,
    #工资相同时，则排序相同
    rank() over (
            partition by dname
            order by salary desc 
    ) as rankover,
    #工资相同时，则排序相同
    dense_rank() over (
        partition by dname
      order by salary desc 
    ) as denserank

#---------------------前后函数------------------
    #
    lag(hiredate,1,'2019-01-01') over (
        partition by dname
        order by hiredate
        as lagtest,
    lag(hiredate,1) over (
        partition by dname
        order by hiredate
    ) as lagtest,

#---------------------头尾函数------------------
    #返回比较早入职的员工工资
     first_value(hiredate) over (
        partition by dname
        order by salary asc
     ) as first1,
    laste_value(hiredate) over (
       	partition by dname
        order by salary asc
     ) as laste


#---------------------其他的窗口函数------------------
        
    #根据日期时间将每个部门的员工分成3组
    ntile(3) over (
        partition by dname
        order by hiredate asc
    ) as nitle

from win_department a inner join win_itstaff b on a.did = b.defk; 

跨库查询

同服务器的跨库查询

select 不同数据库的不同目标字段.. from 数据库1.数据库1.table1  t1 inner join 数据库2.table2 t2 on t1.id1 = t2.id2;

#跨库查询 根据id做关联查询，两表id值类型必须相同。
select 
	sname,dname 
from  
	user01.win_itstaff t1 
inner join 
	user02.win_department t2 
on 
	t1.defk = t2.did;

不同服务器的跨库查询

---

mysql管理

用户创建和使用

用户添加

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
          (host, user, password, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', 
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

1. 查看表信息： SHOW COLUMNS FROM 表名
2. 查看索引信息：SHOW INDEX FROM 表名
3. SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] \G:
   该命令将输出Mysql数据库管理系统的性能及统计信息。

数据库备份

数据库导入导出

开始之前查看 文件保存指定的位置： show global variables like '%secure_file_priv%'; 更具位置保存再该文件夹下面，或者直接通过配置mysql.ini配置到自己常用的导出路径。

导出

1. SELECT ... INTO OUTFILE

语句： SELECT ... INTO OUTFILE 'filepath';    filepath=secure_file_privd指定的路径/ 保存文件名
         select * from techar into outfile 'D:/ProgramData/MySQL/MySQL Server 8.0/Uploads/tb_techar.txt';
         LOAD DATA INFILE  文件读回数据库

mysqldump 备份工具

参数

参数名	缩写	含义
--host	-h	服务器IP地址
--port	-P	服务器端口号
--user	-u	MySQL 用户名
--pasword	-p	MySQL 密码
--databases		指定要备份的数据库
--all-databases		备份mysql服务器上的所有数据库
--compact		压缩模式，产生更少的输出
--comments		添加注释信息
--complete-insert		输出完成的插入语句
--lock-tables		备份前，锁定所有数据库表
--no-create-db/--no-create-info		禁止生成创建数据库语句
--force		当出现错误时仍然继续备份操作
--default-character-set		指定默认字符集
--add-locks		备份数据库表时锁定数据库表

备份所有数据库：格式为 .db

mysqldump -uroot -p --all-databases > "保存指定文件路径.db"

备份指定数据库：

mysqldump -uroot -p 数据库名 >  "保存指定文件路径.db"

备份指定数据库指定表(多个表以空格间隔)

mysqldump -uroot -p  数据库名 表1 表2 ... >  "保存指定文件路径.db"

备份指定数据库排除某些表

mysqldump -uroot -p 数据库名 --ignore-table=数据库名.排除表名1 --ignore-table=数据库名.排除表名2.... > "保存指定文件路径.db"

逆向操作： 将.db 还原到新建的数据库

mysqladmin -uroot -p create newdb_name 
mysql -uroot -p  newdb_name < “file path .db”

注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入。

mysql导出

1、mysql 命令导入

使用 mysql 命令导入语法格式为：

mysql -u用户名    -p密码    <  要导入的数据库数据(runoob.sql)

2. soure 方法

mysql > use db_name
mysql > source /backup/mysqldump/db_name.db

3. 使用 load local infile 导入表数据

# 将该文件中的数据插入到当前数据库的 mytbl 表中。
load data local infile ‘导入的目标文件路径.sql .db ...’ into table ’插入目标数据库表‘;

#增加分隔符和换行符
fields terminated by '分隔符号' , lines terminated by '对行的操作' (\n换行 , \r);

mysqlimport的常用选项介绍

选项	功能
-d or --delete	新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force	不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignore	mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。
-l or -lock-tables	数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。
-r or -replace	这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。
--fields-enclosed- by= char	指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
--fields-terminated- by=char	指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab）
--lines-terminated- by=str	此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。

mysqlimport 命令常用的选项还有 -v 显示版本（version）， -p 提示输入密码（password）等。

1. 从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

$ mysqlimport -u root -p --local 数据库名 “目标文件路径”

2. mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  数据库名  “目标文件路径”

3. mysqlimport 语句中使用 --columns 选项来设置列的顺序：

$ mysqlimport -u root -p --local --columns=b,c,a \
    数据库名 “目标文件路径”

---

mysql安全

加密函数

mysql注入

---

Springboot集成mysql

数据库驱动源 druid

url: 数据库?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE

设置时区

mysql -uroot -p
p = >zhuangzeyong

第一： show variables like '%time_zone%';
第二： set global time_zone = '+8:00';

set time_zone = '+8:00';
flush privileges;

---

mysql集群

percona 高可用数据库

---

MYSQL在实际业务场景中的应用

快速查看数据类型

MySQL 数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

---

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

---

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

---

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。