MySQL（一）SQL语法、数据类型、常用函数、事务

已于 2024-01-26 22:03:29 修改
阅读量1.8k 收藏 9
点赞数 3
分类专栏： 【MySQL】 文章标签： mysql 数据库 sql 事务
于 2021-11-14 09:21:09 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_37741420/article/details/121148972
版权

文章目录

本系列文章：
  MySQL（一）SQL语法、数据类型、常用函数、事务
  MySQL（二）MySQL SQL练习题
  MySQL（三）视图、存储过程、索引
  MySQL（四）存储引擎、锁
  MySQL（五）MySQL架构、数据库优化、主从复制
  MySQL（六）SQL语句优化
  MySQL（七）MySQL和Oracle、PostgreSQL的区别

一、SQL语法

  SQL（Structured Query Languange），结构化查询语言。

1.1 SQL通用语法

 1）SQL语句可以单行或多行书写，以分号结尾。
 2）可使用空格和缩进来增强语句的可读性。
 3）MySQL数据库的SQL语言不区分大小写，关键字建议使用大写，对所有列和表名使用小写，这样做使代码更容易阅读和调试。
 4）3种注释：

单行注释： – 注释内容
单行注释：# 注释内容（mysql特有）
多行注释：/*注释*/

1.2 SQL分类

  Mysql中SQL语句的分类可以分成：数据定义语言DDL、数据操纵语言DML、数据控制语言DCL、数据查询语言DQL。
  DDL：数据定义语言，这些语句主要用来创建、修改、删除数据库的逻辑结构，其中包括表结构，视图和索引等。常用的关键字有主要包括create、drop、alter等，truncate也是DDL关键字。
  DML：数据操纵语言，这些语句用于添加、删除、更新和查询数据库中的数据，并检查数据完整性。常用的关键字有insert、delete、update、select等。
  DCL：数据控制语言，这些语句主要用来控制数据库的访问权限。常用的关键字有主要包括grant、revoke、commit、rollback等。DCL主要用来控制数据库的权限。
  DQL：数据查询语言，各种简单查询，连接查询等 都属于DQL。

1.2.1 DDL语句

  DDL：Data Define Language数据定义语言，主要用来对数据库、表进行一些管理操作。如：建库、删库、建表、修改表、删除表、对列的增删改等等。

  • 库管理
	#创建数据库
	CREATE DATABASE dbname
	#查看系统中都有哪些数据库
	SHOW DATABASES
	#建库通用的写法
	drop database if exists 旧库名;
	create database 新库名;
	#选择某个数据库
	USE dbname
	#删除数据库
	DROP DATABASE dbname
  • 表管理
	--创建表语法
	create table 表名(
    	字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    	字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    	字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
	)[表的一些设置];

	--建表示例
	create table test2(
  		a int not null comment '字段a',
  		b int not null default 0 comment '字段b'
	);

	#删除表
	DROP TABLE tablename
	#删除表更常见的用法
	DROP TABLE IF EXISTS tablename

    -- 只复制表结构
    create table 表名 like 被复制的表名;
    -- 复制表结构+数据
   create table 表名 [as] select 字段,... from 被复制的表 [where 条件];

	#更改表名 
	ALTER TABLE tablename RENAME [TO] new_table
	#示例:将表名由emp改成emp1
	ALTER TABLE emp RENAME emp1
  • 列管理
	#修改字段类型
	ALTER TABLE tablename MODIFY [COLUMN] column_definition
	#示例：将emp字段类型改成VARCHAR(20)
	ALTER TABLE emp MODIFY name VARCHAR(20)
	#增加字段
	ALTER TABLE tablename ADD [column] column_definition
	#示例：增加类型为int(3)的age字段
	ALTER TABLE emp ADD COLUMN age INT(3)
	#删除字段
	ALTER TABLE tablename DROP [column] col_name
	#修改字段名
	ALTER TABLE tablename CHANGE [column] old_col_name column_definition
	#示例：将age改名为age1，并修改字段类型
	ALTER TABLE emp CHANGE age age1 INT(4)

  注意：modify不能修改列名，change可以修改列名。
  使用上面命令增加的字段默认都在表的最后位置，而CHANGE/MODIFY不会修改字段的位置。如果要修改字段的位置，要用AFTER/FIRST等关键字：

	#将birth字段添加到ename之后
	ALTER TABLE emp ADD birth DATE AFTER ename
	#将age字段放在最前面
	ALTER TABLE emp MODIFY age INT(3) FIRST
1.2.2 DML语句

  DML(Data Manipulation Language)数据操作语言，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是必须要掌握的指令，DML和SQL中的select熟称CRUD（增删改查）。

  • 插入数据
	#可以不指定字段名称，但value后面的顺序应该和字段的排列顺序一致
	INSERT INTO tablename (field1,field2,...,fieldn) VALUES (value1,value2,...valuen)

  使用INSERT时，可以一次性插入多条数据：

	INSERT INTO tablename (field1,field2,...,fieldn) 
	VALUES 
	(record1_value1,record1_value2,...record1_valuen)
	(record2_value1,record2_value2,...record2_valuen)	
	...
	(recordn_value1,recordn_value2,...recordn_valuen)
  • 更新数据
	UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen
	#示例
	UPDATE emp SET sal=4000 WHERE ename='lisa'
  • 删除数据
	DELETE FROM tablename [WHERE CONDITION]
	#示例
	DELETE FROM emp WHERE ename='tony'

  如果使用DELETE关键字时，后面没加条件，就会把整张表的数据删掉。

1.2.3 delete、truncate与drop*

  三者都可以删除表中的数据，三者的差别：

deletetruncatedrop
回滚可回滚不可回滚不可回滚
删除内容表结构还在，删除表的全部或者一部分数据行表结构还在，删除表中的所有数据从数据库中删除表结果和所有的数据行，索引和权限也会被删除
删除速度删除速度慢，需要逐行删除删除速度快删除速度最快

  想删除部分数据，用delete；
  保留表结构而删除所有数据，用truncate；
  不再需要一张表，用drop。

  • 1、DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
  • 2、表和索引所占空间。当表被TRUNCATE后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。DROP语句将表所占用的空间全释放掉。
  • 3、一般而言，执行速度：DROP>TRUNCATE>DELETE。
  • 4、应用范围。TRUNCATE只能对TABLE；DELETE可以是TABLE和VIEW。
  • 5、TRUNCATE和DELETE只删除数据，而DROP则删除整个表（结构和数据）。
  • 6、TRUNCATE与不带 WHERE的DELETE：只删除数据，而不删除表的结构（定义）drop 语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
  • 7、truncate、drop是ddl，操作立即生效。

  delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。
  对于由foreign key约束引用的表，不能使用truncate table ，而应使用不带where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。
  delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
  truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
  如果有自增列，truncate方式删除之后，自增列的值会被初始化，delete方式要分情况（如果数据库被重启了，自增列值也会被初始化，数据库未被重启，则不变）。

1.3 SELECT语句

1.3.1 简单查询
	#查询指定列
	SELECT 查询的列1,查询的列2 FROM tablename [WHERE CONDITION]
	#查询所有列
	select * from 表名
1.3.2 列别名
select 列 [as] 别名 from 表;
-- 使用双引号创建别名
select a "列1",b "列2" from test1;
-- 使用单引号创建别名
select a '列1',b '列2' from test1;
-- 不用引号创建别名
select a 列1,b 列2 from test1;
-- 使用as创建别名
select a as 列1,b as 列2 from test1;

  建议给列起别名时，使用as关键字，sql易读。

1.3.3 条件查询
select 列名 from 表名 where 列 运算符 值

  WHERE后面跟的条件中，除了可以使用=，还可以使用>、<、>=、<=、！=等比较运算符，多个条件之间可以用OR、AND等逻辑运算符。

1.3.4 模糊查询
-- ％对应于0个或更多字符,_只是 LIKE 语句中的一个字符
select 列名 from 表名 where 列 like pattern;
1.3.5 区间查询

  操作符 BETWEEN … AND 会选取介于两个值之间的数据范围，这些值可以是数值、文本或者日期，属于一个闭区间查询。

selec 列名 from 表名 where 列名 between 值1 and 值2;
1.3.6 IN查询
select 列名 from 表名 where 字段 in (值1,值2,值3,值4);

  in后面括号中可以包含多个值，对应记录的字段满足in中任意一个都会被返回。
  in列表的值类型必须一致或兼容。
  in列表中不支持通配符。

1.3.7 NOT IN查询

  not in和in刚好相反，in是列表中被匹配的都会被返回，NOT IN是和列表中都不匹配的会被返回。

select 列名 from 表名 where 字段 not in (值1,值2,值3,值4);


-- 使用 not in 的时候，不会查询出来任何数据
-- 理想是查询到 id = 2 的数据，但实际查询为空，没有数据
SELECT * 
FROM dict 
WHERE num NOT IN (100, null)
-- 正确写法
SELECT * 
FROM dict
WHERE num NOT IN (100)
AND num IS NOT NULL

  用 not in 的时候，如果 not in 后面的选项中有 null，不会查询出来任何数据。sql 语句本身直接返回 false ，所以使用 not in 的时候，要保证 in 中的条件不会出现 null 的情况。

1.3.8 NULL值查询
select 列名 from 表名 where 列 is null;
select 列名 from 表名 where 列 is not null;
1.3.9 排序查询
	SELECT * FROM tablename 
	[WHERE CONDITION] 
	[ORDER BY field1 [DESC|ASC],field2 [DESC|ASC],...,fieldn [DESC|ASC]]

  DESC表示按字段进行降序排列，ASC表示升序排列，默认是升序排列。支持多个字段进行排序，多字段排序之间用逗号隔开，order by后越靠前的字段排序优先级越高。

1.3.10 LIMIT语句
select 列 from 表 limit [offset,] count;

  offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)。
  count：跳过offset行之后开始取数据，取count行记录；范围：[0,+∞)。
  limit中offset和count的值不能用表达式。

	#获取前n行记录
	select 列 from 表 limit 0,n;
	select 列 from 表 limit n;
	#获取排名第n到m的记录
	select 列 from 表 limit n-1,m-n+1;
	#示例：显示从第2条数据开始的3条数据
	SELECT * FROM emp ORDER BY sal LIMIT 1,3
1.3.11 分页查询

  LIMIT经常和ORDER BY一起使用，进行数据的分页显示。
  开发过程中，分页我们经常使用，分页一般有2个参数：
  page：表示第几页，从1开始，范围[1,+∞)。
  pageSize：每页显示多少条记录，范围[1,+∞)。
  如：page = 2，pageSize = 10，表示获取第2页10条数据。使用limit分页的语法：

select 列 from 表名 limit (page - 1) * pageSize,pageSize;

  注意事项：

  1. limit中不能使用表达式，只能够跟明确的数字。
  2. limit后面的2个数字不能为负数。
1.3.12 分组查询
	SELECT [filed1,field2,...,fieldn] fun_name
	FROM tablename
	[WHERE where_condition]
	[GROUP BY field1,field2,...,fieldn]
	[HAVING where_condition]

  fun_name表示聚合函数，常用的有sum、count(*)、avg、max、min等。GROUP BY后面跟的是要进行分类聚合的字段。HAVING表示对分类后的结果在进行条件过滤。
  分组中，select后面只能有两种类型的列：1）出现在group by后的列；2）或者使用聚合函数的列。
  Group BY用来创建分组，如果分组中有NULL值，将NULL作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

1.3.13 UNION语句

  将多个查询的数据合并用到的关键字是UNION/UNION ALL：

	SELECT * FROM t1
	UNION|UNION ALL
	SELECT * FROM t2
	...
	UNION|UNION ALL
	SELECT * FROM tn

  UNION和UNION ALL的区别：

  • UNION ALL：把结果集直接合并在一起；
  • UNION：将UNION ALL的结果进行去重。

  因为UNION有个去重的过程，所以UNION ALL的效率较高。

1.3.14 COUNT语句*

  1、COUNT(*)函数返回表中所有行的数量，包括NULL值行。

SELECT COUNT(*) FROM students;

  2、COUNT(column_name)函数返回指定列非NULL值的数量，忽略NULL值行。

SELECT COUNT(age) FROM students;

  3、COUNT(DISTINCT column_name)返回指定列中不同值的数量，忽略NULL值行。

SELECT COUNT(DISTINCT age) FROM students;

  4、COUNT(DISTINCT expr,[expr…])函数返回指定表达式中不同非NULL值的数量，忽略NULL值行。

SELECT COUNT(DISTINCT age, gender) FROM students;

  5、COUNT(IF(condition, column_name, NULL))函数返回满足指定条件的指定列的非NULL值数量。

-- 这个 SQL 语句的含义是统计学生表中性别为 ‘Female’ 的人数。通过 IF 函数，将所有性别
-- 为 ‘Female’ 的行转换成 1，然后 COUNT 函数就会返回这些 1 的数量，即性别为 ‘Female’ 
-- 的学生数量
SELECT COUNT(IF(gender='Female', 1, NULL)) FROM students;
  • count(*)、count(1)、count(列名)的选择
      COUNT(*) 是最常用的方式，它可以统计所有行的数量，包括 NULL 值。
      COUNT(1) 也可以统计所有行的数量，包括 NULL 值。
      COUNT(column_name) 只会统计指定列中非 NULL 值的数量，而忽略 NULL 值。因此，如果需要统计指定列的非 NULL 值数量，那么可以使用 COUNT(column_name)。这种方式的性能相对较低，因为需要扫描整个表，对于每一行都要进行判断。

1.4 关联查询

  Mysql中常用的关联查询有左外连接、右外连接、全连接、自连接等。
  创建两个表：t_employee(员工表)和t_dept(部门表)。建表及插入测试数据示例：

	DROP TABLE t_dept;
	CREATE TABLE t_employee(
		id INT,
		empName VARCHAR(32),
		dept VARCHAR(32),
		bossId VARCHAR(32)
	)CHARSET UTF8;
	
	DROP TABLE t_dept;
	CREATE TABLE t_dept(
		id INT,
		deptName VARCHAR(32)
	)CHARSET UTF8;
	
	INSERT INTO t_employee(id,empName,dept,bossId) VALUES (1,'张三','1',null);
	INSERT INTO t_employee(id,empName,dept,bossId) VALUES (2,'李四','2','1');
	INSERT INTO t_employee(id,empName,dept,bossId) VALUES (3,'王五','2','2');
	INSERT INTO t_employee(id,empName,dept,bossId) VALUES (4,'陈六','3','3');
	INSERT INTO t_employee(id,empName,dept,bossId) VALUES (5,'赵七',null,'4');
	INSERT INTO t_dept(id,deptName) VALUES (1,'软件开发部');
	INSERT INTO t_dept(id,deptName) VALUES (2,'软件测试部');
	INSERT INTO t_dept(id,deptName) VALUES (3,'市场运维部');
	INSERT INTO t_dept(id,deptName) VALUES (4,'人力资源部');

  此时表中的数据：

1.4.1 笛卡尔积

  笛卡尔积简单点理解：有两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。假如A中有m个元素，B中有n个元素，A、B笛卡尔积产生的结果有m*n个结果，相当于循环遍历两个集合中的元素，任意组合。

过程：拿A集合中的第1行，去匹配集合B中所有的行，然后再拿集合A中的第2行，去匹配集合B中所有的行，最后结果数量为m*n。

  sql中笛卡尔积语法：

select 字段 from 表1,表2[,表N];
select 字段 from 表1 join 表2 [join 表N];
1.4.2 内连接

  语法为"INNER JOIN … ON …"，INNER可以省略，内连接查出来的数据是两张表里都有的数据：

  内连接相当于在笛卡尔积的基础上加上了连接的条件。当没有连接条件的时候，内连接上升为笛卡尔积。
  示例：

	SELECT 
	e.empName,d.deptName
	from t_employee e
	JOIN t_dept d
	ON e.dept = d.id;

  结果：

1.4.3 左外连接

  语法为"LEFT JOIN … ON …"，左外连接查出来的数据是左边表中全部数据和右边表中匹配上的数据，右边表中未匹配的数据以null显示：

  示例：

	SELECT e.empName,d.deptName
	FROM t_employee e
	LEFT JOIN t_dept d 
	ON d.id = e.dept;

  结果：

1.4.4 右外连接

  语法为"RIGHT JOIN … ON …"，右外连接查出来的数据是右边表中全部数据和左边表中匹配上的数据，左边表中未匹配的数据以null显示：

  示例：

	SELECT e.empName,d.deptName
	FROM t_employee e
	RIGHT JOIN t_dept d 
	ON d.id = e.dept;

  结果：

1.4.5 全连接

  语法为’UNION’，UNION操作符用于合并两个或多个SELECT语句的结果集。UNION内部的每个SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个SELECT语句中的列的顺序必须相同。
  示例：

	SELECT e.empName,d.deptName
	     FROM t_employee e
	     LEFT JOIN t_dept d
	     ON e.dept = d.id
	UNION
	SELECT e.empName,d.deptName
	     FROM t_employee e
	     RIGHT JOIN t_dept d
	     ON e.dept = d.id;

  结果：

1.5 查询语句的书写和执行顺序

1.5.1 常见书写情况*

  Mysql的一般书写顺写为：

select  <要返回的数据列>
from   <表名>
<join, left join, right join...> join    <join表>
on   <join条件>
where   <where条件>
group by  <分组条件>
having  <分组后的筛选条件>
order by  <排序条件>
limit  <行数限制>
1.5.2 常见执行顺序*

  一个查询语句同时出现了where、group by、having、order by的时候，执行顺序和编写顺序是：

  1. 执行where xx对全表数据做筛选，返回第1个结果集。
  2. 针对第1个结果集使用group by分组，返回第2个结果集。
  3. 针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
  4. 针对第3个结集执行having xx进行筛选，返回第4个结果集。
  5. 针对第4个结果集排序。

  按照执行顺序的关键词首字母分别是W（where）->G（Group）->S（Select）->H（Having）->O（Order），对应汉语首字母可以编成容易记忆的顺口溜：我(W)哥(G)是(SH)偶(O)像。

1.5.3 完整执行顺序
from 
    <表名>          # 笛卡尔积
on 
    <筛选条件>          #对笛卡尔积的虚表进行筛选
<join, left join, right join...> join 
    <join表>  #指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
where 
    <where条件>     #对上述虚表进行筛选
group by
    <分组条件>      #分组
<sum()等聚合函数>   #用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
having 
    <分组筛选>      #对分组后的结果进行聚合筛选
select 
    <返回数据列表>       #返回的单列必须在group by子句中，聚合函数除外
distinct
order by 
    <排序条件>      #排序
limit 
    <行数限制>

  执行流程：

  1. from：select * from table_1, table_2; 与 select * from table_1 join table_2; 的结果一致，都是表示求笛卡尔积；
      用于直接计算两个表笛卡尔积，得到虚拟表VT1，这是所有select语句最先执行的操作，其他操作时在这个表上进行的，也就是from操作所完成的内容。
  2. on：从VT1表中筛选符合条件的数据，形成VT2表；
  3. join：将该join类型的数据补充到VT2表中，例如left join会将左表的剩余数据添加到虚表VT2中，形成VT3表；若表的数量大于2，则会重复1-3步；
  4. where：执行筛选，（不能使用聚合函数）得到VT4表；
  5. group by：对VT4表进行分组，得到VT5表；其后处理的语句，如select，having，所用到的列必须包含在group by条件中，没有出现的需要用聚合函数；
  6. having：筛选分组后的数据，得到VT6表；
  7. select：返回列得到VT7表；
  8. distinct：用于去重得到VT8表；
  9. order by：用于排序得到VT9表；
  10. limit：返回需要的行数，得到VT10。

1.6 三大范式*

  数据库的三大范式的设计目的是减少数据冗余。

1.6.1 第一范式

  第一范式是最基本的范式。如果数据库表中的所有字段值都不可再分解（列不可再分），就说明该数据库表满足了第一范式，确保数据库表字段的原子性。
  第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。
  这样设计才算满足了数据库的第一范式，示例：

1.6.2 第二范式

  第二范式：表必须有一个主键，非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。
  比如这样的表是不合适的，可以拆分成学生表、教师表和中间表：

    学生表和教师表：

    中间表：

1.6.3 第三范式

  满足第三范式（3NF）必须先满足第二范式（2NF）。
  第三范式：非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

  • 2NF和3NF的区别
      2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
      3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。
1.6.4 简单总结
  • 1、第一范式： 列不可分。
  • 2、第二范式：列必须直接依赖主键。
  • 3、第三范式： 表里面的列不能出现其它表的非主键字段。
1.6.5 范式化和反范式化设计的优缺点
  • 范式化
     优点：可以尽量得减少数据冗余，使得更新快，体积小。
     缺点：对于查询需要多个表进行关联，减少写得效率增加读得效率。
  • 反范式化
     优点：可以减少表得关联，可以更好得进行索引优化。
     缺点：数据冗余，数据的修改需要更多的成本。

1.7 约束

  创建表的时候，同时可以指定所插入数据的一些规则，比如说某个字段不能为空值，某个字段的值（比如年龄）不能小于零等等，这些规则称为约束。约束是在表上强制执行的数据校验规则。
   数据库中的五大约束：

  1. 主键约束： 唯一，非空【常用】；
  2. 唯一约束 ：唯一，可以为空，但只能有一个【常用】；
  3. 非空约束：非空【常用】；
  4. 默认约束 ：该列数据的默认值；
  5. 外键约束 ： 两表间的关系；
  6. 检查约束 ： 检查字段值是否符合要求。

  约束示例：

--添加主键约束
Alter table 表名 add Constraint 主键名 primary key(字段)
--添加唯一约束
Alter table 表名 add Constraint 约束名 unique(字段)
--添加默认约束
Alter table 表名 add Constraint 约束名 default(默认内容) for 字段名
--添加检查约束
Alter table 表名 add Constraint 约束名 check (字段表达)
--添加外键约束
Alter table 表名 add Constraint 约束名 foreign key(字段) references 表名(字段名)
--约束常常在建表时添加，如对某个极端添加非空约束
`account_name` varchar(64) NOT NULL COMMENT '账户名称',

1.8 主键、外键

  主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。简单理解：唯一+非空。
  主键的最好习惯：

  1. 不更新主键列中的值；
  2. 不重用主键列中的值；
  3. 不在主键列中使用可能会更改的值。

  外键：在一个表中存在的另一个表的主键称此表的外键。

1.9 SQL相关问题

1.9.1 HAVNG和WHERE的区别*
  1. 语法
      where用表中列名，having用select结果别名；
  2. 是否能使用索引
      where可以使用索引，having不能使用索引，只能在临时结果集操作；
  3. 是否能使用聚集函数
      where 后面不能使用聚集函数，having是专门使用聚集函数的。

一个聚集函数从多个输入行中计算出一个结果，比如count（数目）， sum（和），avg（ 均值 ）， max（最大值）和min（最小值）。

  1. HAVING是对聚合(数据分组)后的结果进行条件的过滤，而WHERE是在聚合(数据分组)前就对记录进行过滤。如果逻辑允许，尽可能用WHERE先过滤条件，因为这样结果集减小，聚合的效率将大大提高，最后再根据逻辑看是否用HAVING进行过滤。
1.9.2 exist和in的区别*
  • IN
SELECT * FROM A WHERE id IN (SELECT id FROM B);

  等价于：

1、SELECT id FROM B ----->先执行in中的查询
2、SELECT * FROM A WHERE A.id = B.id

  以上in()中的查询只执行一次，它查询出B中的所有的id并缓存起来，然后检查A表中查询出的id在缓存中是否存在，如果存在则将A的查询数据加入到结果集中，直到遍历完A表中所有的结果集为止。
  可以看出，使用IN时，子查询中适合放小表（数据量少的表）。

  • EXIST
SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id  = A.id);

  等价于：

1、SELECT * FROM A;
2、SELECT I FROM B WHERE B.id = A.id;

  EXISTS()查询会执行SELECT * FROM A查询，执行A.length次，并不会将  EXISTS()查询结果结果进行缓存，因为EXISTS()查询返回一个布尔值true或flase，它只在乎EXISTS()的查询中是否有记录，与具体的结果集无关。
  EXISTS()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。
  可以看出，使用EXIST时，子查询中适合放大表（数据量大的表）。

1.9.3 [SELECT *] 和 [SELECT 全部字段]的区别
  • 1、是否解析数据字典
      前者要解析数据字典（数据字典是指对数据的数据项、数据结构、数据流、数据存储、处理逻辑、外部实体等进行定义和描述，其目的是对数据流程图中的各个元素做出详细的说明），后者不需要数据字典。
  • 2、是否可指定输出顺序
      前者与建表列顺序相同（建表后不增删字段的话），后者可以指定字段顺序。
  • 3、表字段改名，前者不需要修改，后者需要改。
  • 4、是否可优化
      后者可以建立索引进行优化，前者无法优化。
  • 5、可读性
      后者的可读性比前者要高。
1.9.4 下面的2个sql查询结果一样吗
select * from students;
select * from students where name like '%';

  当name没有NULL值时，返回的结果一样。
  当name有NULL值时，第2个sql查询不出name为NULL的记录。

1.9.5 Mysql分组中的坑

  分组中select后面的列只能有2种：1）出现在group by后面的列；2）使用聚合函数的列。
  这是在Mqsql后期版本加上的功能，在早期版本中没这个要求。建议：在写分组查询的时候，最好按照标准的规范来写，即：select后面出现的列必须在group by中或者必须使用聚合函数。

1.10 数据库开发规范

1.10.1 基础规范

  1）必须使用InnoDB存储引擎解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。
  2）必须使用UTF8字符集解读：万国码，无需转码，无乱码风险，节省空间。
  3）数据表、数据字段必须加入中文注释。
  4）禁止使用存储过程、视图、触发器、Event解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。
  5）禁止存储大文件或者大照片解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URIg。

1.10.2 命名规范

  1）库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用.
  2）表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx.
  3）单实例表数目必须小于500。
  4）单表列数目必须小于30。
  5）表必须有主键，例如自增主键：

  a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用。
  b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率。

  6）禁止使用外键，如果有外键完整性约束，需要应用程序控制解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先。

1.10.3 字段设计规范

  1）必须把字段定义为NOT NULL并且提供默认值：

  a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化。
  b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多。
  c）null值需要更多的存储空间，无论是表还是索引中每行中的null的列都需要额外的空间来标识。
  d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。

  2）禁止使用TEXT、BLOB类型解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。
  3）禁止使用小数存储货币解读，小数容易导致钱对不上。
  4）必须使用varchar(20)存储手机号：

  a）涉及到区号或者国家代号，可能出现±。
  b）varchar可以支持模糊查询，例如：like“138%”。

  5）禁止使用ENUM，可使用TINYINT代替：

  a）增加新的ENUM值要做DDL操作。
  b）ENUM的内部实际存储就是整数。

  6）单表索引建议控制在5个以内。
  7）单索引字段数不允许超过5个。字段超过5个时，实际已经起不到有效过滤数据的作用了。
  8）禁止在更新十分频繁、区分度不高的属性上建立索引：

  a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。
  b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

  9）建立组合索引，必须把区分度高的字段放在前面解读：能够更加有效的过滤数据。
  10）禁止使用SELECT *，只获取必要的字段，需要显示说明列属性：

  a）读取不需要的列会增加CPU、IO、NET消耗。
  b）不能有效的利用覆盖索引。
  c）使用SELECT *容易在增加或者删除字段后出现程序BUG。

  11）禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性解读：容易在增加或者删除字段后出现程序BUG。
  12）禁止使用属性隐式转换解读：SELECT uid FROM t_user WHERE phone=13800000000 会导致全表扫描，而不能命中phone索引。
  13）禁止在WHERE条件的属性上使用函数或者表达式：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-01-15'会导致全表扫描，正确的写法是：SELECT uid FROM t_user WHERE day>=unix_timestamp('2017-01-15 00:00:00')。
  14）禁止负向查询，以及%开头的模糊查询：

  a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描。
  b）%开头的模糊查询，会导致全表扫描。

  15）禁止使用OR条件，必须改为IN查询解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？
  16）应用程序必须捕获SQL异常，并有相应处理.
  17）同表的增删字段、索引合并一条DDL语句执行，提高执行效率，减少与数据库的交互。

二、Mysql基础

2.1 Mysql简介

  Mysql是一个关系型数据库管理系统。在Web应用方面，Mysql是最好的RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

  • 1、RDBMS的一些术语
      数据库：数据库是一些关联表的集合。
      表：表是数据的矩阵。
      列：一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
      行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
      冗余：存储两倍数据，冗余可以使系统速度更快。
      主键：主键是唯一的。一个数据表中只能包含一个主键，可以使用主键来查询数据。
      外键：外键用于关联两个表。
      复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
      索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，类似于书籍的目录。

  • 2、Mysql的特点

  1. Mysql是一个数据库管理系统；
  2. Mysql数据库是关系型的；一个关系型数据库将数据存储在不同的表中，而不是将所有的数据存储在一个大的存储区域中。
  3. Mysql软件是开源的；
  4. Mysql数据库服务器速度快、可靠性高，扩展性强，且易于使用。

2.2 Mysql数据类型

  Mysql提供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。接下来以Mysql5.0为例，详细介绍Mysql中的各种数据类型。

2.2.1 数值类型*
整数类型字节最小值最大值备注
TINYINT1有符号-128(-27)
无符号0		有符号127(27-1)
无符号255(28-1)		很小的整数
SMALLINT2有符号-32768(-215)
无符号0		有符号32767(215-1)
无符号65535(216)		小的整数
MEDIUMINT3有符号- 8388608(-223)
无符号0		有符号8388607(223-1)
无符号1677215(224-1)		中等大小的整数
INT、INTEGER4有符号- 2147483648(-231)
无符号0		有符号2147483647(231-1)
无符号4294967295(232-1)		普通大小的整数
BIGINT8有符号-9223372036854775808(-263)
无符号0		有符号9223372036854775807(263-1)
无符号18446744073709551615(264-1)		大的整数
浮点数类型字节最小值最大值
FLOAT4±1.175494351E-38±3.402823466E+38单精度浮点型，m总个数，d小数位
DOUBLE8±2.2250738585072014E-308±1.7976931348623157E+308双精度浮点型， m总个数，d小数位
DEC(M,D)
DECIMAL(M,D)		M+2最大取值范围与DOUBLE相同，给定DECIMAL的有效取值范围由M和D决定压缩严格的定点数

  对于整型数据，Mysql支持在类型名称后面的小括号内指定显示宽度。例如int(5)表示当数值宽度小于5位的时候在数字前面，默认用空格填满宽度，如果不显示指定宽度则默认为int(11)。
  在创建表时，关于主键字段的类型，用int或bigint均可，示例：

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',

如果需要用哪个数字’0’填充，则需要使用zerofill，并且使用zerofill 时，默认会自动unsigned(无符号)属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128+127，无符号为0256。

  对于小数的表示，MySQL分为两种方式：浮点数和定点数。浮点数包括float（单精度）和double（双精度），而定点数则只有decimal一种表示。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示金额等精度高的数据。
  浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该值一共显示M位数字（整数位+小数位），其中D位于小数点后面。M和D又称为精度和标度。
  float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

decimal插入的数据超过精度之后会触发警告。

  decimal采用的是四舍五入。
  float和double采用的是四舍六入五成双。

四舍六入五成双：就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数字，如果有，则直接进位；如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉。

  如果将数据库中 float 、 double类型的数据进行计算时，会存在精度问题。而使用decimal时是正常的，所以非int数据，一般decimal用的比较多。示例：

`term_amt` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '期供金额',
2.2.2 日期时间类型*
日期和时间类型字节最小值最大值说明
DATE41000-01-019999-12-31YYYY-MM-DD
DATETIME81000-01-01 00:00:009999-12-31 23:59:59YYYY-MM-DD HH:MM:SS
TIMESTAMP4197001010800012038-01-19 03:14:07UTC（格林尼治时间）YYYY-MM-DD HH:MM:SS
TIME3-838:59:59838:59:59HH:MM:SS
YEAR119012155YYYY

  如果要用来表示年月日，通常用DATE来表示。
  如果要用来表示年月日时分秒，通常用DATETIME表示。
  如果只用来表示时分秒，通常用TIME来表示。
  如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP来表示。
  如果只是表示年份，可以用YEAR来表示，它比DATE占用更少的空间。YEAR有2位或4位格式的年。默认是4位格式。在4位格式中，允许的值是1901～2155和0000。在2位格式中，允许的值是70～69，表示从1970～2069年。
  DATETIME是DATE和TIME的组合。

  TIMESTAMP还有一个重要特点，就是和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。这样，两个不同时区的用户看到的同一个日期可能是不一样的。

  尽量使用timestamp，空间效率高于datetime（datetime占用8字节，timestamp占用4字节）。
  如果需要存储微秒，可以使用bigint存储。
  DATETIME表示的时间范围较大，TIMESTAMP表示的时间范围较小。
  关于表中的更新时间字段，使用示例（插入数据时为当前时间，更新数据时也会随之更新）：

`updated_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
2.2.3 字符串类型*
类型范围存储所需字节说明
char(M)[0,m]，m的范围[0, 28-1]m定长字符串
varchar(M)[0,m]，m的范围[0, 216-1]m0-65535 字节
tinyblob0-255(28 -1)字节L+1不超过 255 个字符的二进制字符串
blob0-65535( 216-1)字节L+2二进制形式的长文本数据
mediumblob0-16777215(224 -1)字节L+3二进制形式的中等长度文本数据
longblob0-4294967295( 232-1)字节L+4二进制形式的极大文本数据
tinytext0-255( 28-1)字节L+1短文本字符串
text0-65535(216 -1)字节L+2长文本数据
mediumtext0-16777215(224 -1)字节L+3中等长度文本数据
longtext0-4294967295( 232-1)字节L+4极大文本数据

  char类型占用固定长度，如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息。
  表格中的L表示存储的数据本身占用的字节，L 以外所需的额外字节为存放该值的长度所需的字节数。
  CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串。二者的主要区别在于存储方式的不同：CHAR列的长度固定为创建表时声明的长度，长度可以为从0～255的任何值，存储的字符串长度不足时用空格填充到特定长度；而VARCHAR列中的值为可变长字符串，也就是说申请的只是最大长度，占用的空间为实际字符长度+1，最后一个字符存储使用了多长的空间。
  在检索效率上来讲,char > varchar。
  在检索的时候，CHAR删除了尾部的空格，而VARCHAR则保留这些空格。示例：

	CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
	INSERT INTO vc VALUES ('ab ', 'ab ');
	-- 4  2
	SELECT length(v),length(c) FROM vc;

  BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。
  关于varchar(n)中n的单位，Mysql4.1及之后的版本，VARCHAR的单位是字符；Mysql4.1之前的版本，VARCHAR的单位是字节。
  varchar和char的使用策略：

  1. 对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
  2. 对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
  3. 使用时要注意只分配需要的空间。
  4. 尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

  TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT也称作 非标准字符串类型。

2.3 Mysql数据类型相关问题

2.3.1 若一张表中只有一个字段VARCHAR(N) 类型，utf8编码，则N最大值为多少

  由于utf8的每个字符最多占用3个字节。而MySQL定义行的长度不能超过65535，因此N的最大值计算方法为：(65535-1-2)/3。
  减去1的原因是实际存储从第二个字节开始，减去2的原因是因为要在列表长度存储实际的字符长度，除以3是因为utf8限制：每个字符最多占用3个字节。

2.3.2 选择合适的数据类型
  • 1、CHAR与VARCHAR
      CHAR和VARCHAR类型类似，都用来存储字符串，但它们保存和检索的方式不同。CHAR属于固定长度的字符类型，而VARCHAR属于可变长度的字符类型。
      看一个字符串值保存到CHAR(4)和VARCHAR(4)列的结果对比：

      由于CHAR是固定长度的，所以它的处理速度比VARCHAR快得多，但是缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储。
      在MySQL中，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同，简单概括：

  MyISAM：建议使用固定长度的数据列代替可变长度的数据列。
  MEMORY：目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理。
  InnoDB：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

  • 2、TEXT与BLOB
      在保存较大文本时，通常会选择使用TEXT或BLOB，二者之间的主要差别是BLOB能用来保存二进制数据，比如照片；而TEXT只能保存字符数据，比如一篇文章或者日记。
      TEXT和BLOB中有分别包括TEXT、MEDIUMTEXT、LONGTEXT和BLOB、MEDIUMBLOB、LONGBLOB，它们之间的主要区别是存储文本长度不同和存储字节不同，用户应该根据实际情况选择能够满足需求的最小存储类型。
      BLOB和TEXT值会引起一些性能问题，特别是在执行了大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。
      OPTIMIZE TABLE命令示例：OPTIMIZE TABLE table_name。
      TEXT与BLOB使用建议：

  在不必要的时候避免检索大型的BLOB或TEXT值。
  把BLOB或TEXT列分离到单独的表中。

  • 3、浮点数与定点数
      浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。在MySQL中float、double（或 real）用来表示浮点数。
      定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更加精确的保存数据。
      浮点数和定点数使用的几个原则：
  1. 浮点数存在误差问题；
  2. 对货币等对精度敏感的数据，应该用定点数表示或存储；
  3. 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
  4. 要注意浮点数中一些特殊值的处理。
  • 4、日期类型选择
      MySQL提供的常用日期类型有DATE、TIME 、DATETIME、TIMESTAMP。使用原则：
  1. 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全可以满足，而不需要用4个字节来存储的DATE类型。这样不仅仅能节约存储，更能够提高表的操作效率。
  2. 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，
    而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多    。
  3. 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。
  • 5、ENUM和VARCHAR
      ENUM类型是非常快和紧凑的。在实际上，其保存的是TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。
      如果有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，这些字段的取值是有限而且固定的，那么，就应该使用ENUM而不是VARCHAR。
2.3.3 varchar与char的区别，以及varchar(50)中的50代表的含义*
  • char
      char表示定长字符串，长度是固定的；如果插入数据的长度小于char的固定长度时，则用空格填充；因为长度固定，所以存取速度要比varchar快。因为其长度固定，所以会占据多余的空间，是空间换时间的做法。对于char来说， 多能存放的字符个数为255。
  • varchar
      archar表示可变长字符串，长度是可变的；archar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
    对于varchar来说， 多能存放的字符个数为65532。

  varchar(50)中50的含义 : 最多存放50个字节。早期MySQL版本中， 50代表字节数，现在代表字符数。

2.3.4 varchar(10)和int(10)代表什么含义*

  varchar的10代表了申请的空间长度，也是可以存储的数据的最大长度。而int的10只是代表了展示的长度，不足10位以其他字符（默认是空格）填充。也就是说，int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的，只是在展示时按照长度展示。

int(N)：无论N等于多少，int永远占4个字节。

2.3.5 char(10)和varchar(10)的区别*

  varchar(10)：10位可变字符串，不足补空格，多10个字符。
  char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间。
  varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符。

2.3.6 字段为什么要求定义为Not Null

  Null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

2.3.7 如果要存储用户的密码散列，应该使用什么字段进行存储

  密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

2.3.8 IP地址如何存储*

  用无符号的32位整数存储，不要用字符串存储ip。
  用int unsigned类型存储，ip地址是由32位2进制数组成，也就是4个字节长的整数。
  如果用字符串来存储的话呢，最短是（0.0.0.0）是7个字符，最长是（255.255.255.255）15个字符,在mysql中，还要额外的一个字节来存储字符串的长度，是为了让数据库准确的跟踪列里有多少条数据。这样的话，数据存储的成本需要16个字节。
  这样，每一行数据需要多耗费大约10个字节的额外资源。消耗磁盘的容量不说，如果该字段加了索引，也会同比例的扩大索引文件的大小，还会占据内存，执行此字段的CRUD时候，还会消耗更多的cpu资源。
  mysql中有内置函数：

INET_ATON(）将ip转换成整数。
INET_NTOA(）将整数转换成ip。

  示例：

insert into ip(`ip`) VALUES(INET_ATON('127.0.0.1'));
SELECT INET_NTOA(IP) FROM ip

2.3.9 数据类型选择的一些建议*
  • 选小不选大
      一般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小。
  • 简单就好
      简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
  • 尽量避免NULL
      尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值比较更加复杂。
  • 浮点类型的建议统一选择decimal
  • 记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进行存储，方便走索引
2.3.10 FLOAT和DOUBLE的区别

  FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
  DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

2.3.11 数据库表结构的优化

  数字类型：尽量不要使用double。
  字符类型：尽量不要使用text类型。
  时间类型：尽量使用timestamp类型，因为其存储空间只需要datetime类型的一半。对于只需要精确到某一天的数据类型，建议使用date类型，因为其存储空间只需要3个字节，比timestamp还少。

2.3.12 如果一个表有一列定义为TIMESTAMP，将发生什么

  TIMESTAMP使用示例：

`date_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  • 1、自动更新
     当你插入新行或更新现有行时，如果该列定义为TIMESTAMP并且没有指定具体的值，MySQL会自动将该列设置为当前的日期和时间。这是通过使用当前的系统时间来填充该字段的。
  • 2、自动更新功能可选
     在创建或修改表时，你可以选择是否启用TIMESTAMP列的自动更新功能。通过设置列的DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性，你可以控制这个行为。如果你不设置这些属性，TIMESTAMP列将默认不进行自动更新。
  • 3、时区问题
     TIMESTAMP类型存储的时间戳值是与时区无关的，即不会考虑时区的影响。它会根据服务器的当前时区设置来存储和读取时间戳。因此，在进行跨时区应用时，需要特别注意时区的处理，以避免时间转换错误。
  • 4、时间范围
     TIMESTAMP类型支持的范围是从1970年1月1日00:00:01到2038年1月19日03:14:07，因此，任何超出这个范围的时间都无法被正确地表示。

  总结：TIMESTAMP类型在MySQL中用于存储日期和时间，它具有自动更新的功能，并且需要考虑时区问题。如果你需要存储更大范围的日期和时间，可以使用DATETIME类型，它支持的范围更广，但没有自动更新的功能。

2.3.13 为表中的字段选择合适的数据类型

  字段类型优先级: 整形>date,time>enum、char>varchar>blob、text。优先考虑数字类型，其次是日期或者二进制类型，最后是字符串类型，同级别的数据类型，应该优先选择占用空间小的数据类型。

2.3.14 使用int做主键和使用string有什么优劣*
  • 使用整数（int）作为主键
      优势：
  1. 性能：整数类型通常占用较少的存储空间，并且在进行比较和排序操作时速度更快。
  2. 索引效率：整数类型的索引通常比字符串类型的索引更加紧凑和高效。
  3. 自增属性：许多数据库系统支持整数类型的自增属性，这可以自动为新记录分配一个唯一的标识符，无需应用程序干预。
  4. 空间效率：整数类型占用的存储空间较小，有助于减少数据库的总体大小。

  优势：

  1. 可读性：整数类型不如字符串类型直观，不容易从主键值中直接获取有关记录的信息。
  2. 扩展性：在某些情况下，如果整数主键达到其最大值，可能需要更复杂的方案来处理。
  • 使用字符串（string）作为主键
      优势：
  1. 可读性：字符串类型的主键通常更容易理解，因为它们可能包含描述性信息。
  2. 灵活性：字符串类型可以容纳更多种类的数据，包括字母、数字和特殊字符，使得它们在处理复杂的主键需求时更加灵活。
  3. 扩展性：字符串类型的主键在达到存储限制之前可以容纳更多的信息。

  优势：

  1. 性能：字符串类型通常比整数类型占用更多的存储空间，并且在进行比较和排序操作时速度较慢。
  2. 索引效率：字符串类型的索引通常比整数类型的索引更大且效率较低。
  3. 错误风险：在处理字符串类型的主键时，需要更加小心地处理大小写敏感性、空格、特殊字符等问题，以避免潜在的错误。
  4. 存储空间：由于字符串的长度可变，因此可能会浪费存储空间，特别是在存储较短的字符串时。
  • 如何选择合适的主键类型
      1、根据业务需求：根据具体业务场景来选择合适的主键类型。例如，如果业务需求中需要处理大量数值类型的数据，可以使用 int 作为主键；如果需要处理字符串类型的数据，可以使用 string 作为主键。
      2、考虑数据量的大小：如果数据量较大，可以使用 int 作为主键，以提高查询和更新的效率；如果数据量较小，可以使用 string 作为主键，以提高数据的可读性和可操作性。
      3、考虑数据类型的稳定性和一致性：如果数据类型需要保持稳定性和一致性，可以使用 int 作为主键；如果数据类型可能会发生变化，可以使用 string 作为主键。 综上所述，使用 int 和 string 作为主键各有优劣。在实际应用中，需要根据具体业务场景和数据特点来选择合适的主键类型，以满足数据的存储、管理和处理需求。

2.4 Mysql运算符

  • 1、算术运算符
运算符作用
+加法
-减法
*乘法
/，DIV除法，返回商
%，MOD除法，返回余数

  除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。示例：

  • 2、比较运算符
运算符作用
=等于
!= 或 <>不等于
<小于
<=小于等于
>大于
>=大于等于
BETWEEN存在于指定范围
IS NULL为NULL
IS NOT NULL不为NULL
LIKE通配符匹配
REGEXP或RLIKE正则表达式匹配

  BETWEEN的用法格式：a BETWEEN min AND max。

  • 3、逻辑运算符
运算符作用
NOT 或！逻辑非
AND 或&&逻辑与
OR逻辑或
XOR

2.5 常用函数

2.5.1 字符串函数

  常用字符串函数：

函数功能
CANCAT(S1,S2,…Sn)连接 S1,S2,…Sn 为一个字符串
LENGTH(str)计算字符串长度函数，返回字符串的字节长度
LEFT(s，n)函数返回字符串 s 最左边的 n 个字符，s=1表示第一个字符
RIGHT(s，n)函数返回字符串 s 最右边的 n 个字符
LOWER(str)将字符串str中所有字符变为小写
UPPER(str)将字符串str中所有字符变为大写
LTRIM(str)去掉字符串str左侧的空格
RTRIM(str)去掉字符串str行尾的空格
REPLACE(str,a,b)用字符串b替换字符串str中所有出现的字符串a
TRIM(str)去掉字符串行尾和行头的空格
substr/substring截取字符串，返回从指定位置开始的指定长度的字符换
reverse(str)字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

  substring使用示例：

-- 12  6
select length('javacode2018'),length('路人');
-- 路人    路人甲JAVA 
select left('路人甲JAVA',2),left('路人甲JAVA',10);
/** 第三个字符之后的子字符串：inese **/
SELECT substring('chinese', 3);
/** 倒数第三个字符之后的子字符串：ese **/
SELECT substring('chinese', -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese', 3, 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese', -3, 2);
/** 第三个字符之后的子字符串：inese **/
SELECT substring('chinese' FROM 3);
/** 倒数第三个字符之后的子字符串：ese **/
SELECT substring('chinese' FROM -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese' FROM 3 FOR 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese' FROM -3 FOR 2);
2.5.2 数值函数

  常用数值函数：

函数功能
ABS(x)返回 x 的绝对值
SQRT(x)返回 x 的求二次方根
CEIL(x)/CEILING(x)返回大于 x 的最小整数值
FLOOR(x)返回小于 x 的最大整数值
MOD(x，y)返回 x/y 的模
RAND()返回 0 到 1 内的随机值。传入整数参数时，用来产生重复序列
ROUND(x,y)返回参数 x 的四舍五入的有 y 位小数的值
POW(x,y)/POWER(x,y)返回计算x的y次方

  使用示例：

-- 5  NULL
select sqrt(25),sqrt(-9);
-- -2  3
select ceil(-2.5),ceiling(2.5);
-- 5  5  -4  -5
select floor(5),floor(5.66),floor(-4),floor(-4.66);
-- -7   -8   3
select round(-6.6),round(-8.44),round(3.44);
-- 0.04   1000
select pow(5,-2),pow(10,3);
2.5.3 日期时间函数*
函数功能
CURDATE() / current_date()当前日期
CURTIME() / current_time()当前时间
NOW() / sysdate()当前的日期和时间
UNIX_TIMESTAMP(date)获取日期date的UNIX 时间戳
FROM_UNIXTIME将UNIX 时间戳格式化
dayofweek获取指定日期是一周中是第几天，返回值范围是1~7,1=周日
WEEK(date)日期 date 为一年中的第几周，返回值的范围是否为 0-52 或 1-53
dayofyear获取指定曰期是一年中的第几天，返回值范围是1~366
YEAR(date)日期 date 的年份
dayofmonth获取指定日期是一个月中是第几天，返回值范围是1~31
HOUR(time)time 的小时值
MINUTE(time)time 的分钟值
DATE_FORMAT(date,fmt)按字符串 fmt 格式化日期 date 值
DATE_ADD(date,INTERVAL expr type)/adddate一个日期或时间值加上一个时间间隔的时间值
DATE_SUB/subdate都是向日期减去指定的时间间隔
DATEDIFF(expr,expr2)起始时间 expr 和结束时间 expr2 之间的天数

  使用示例：

-- 2019-09-17 16:13:28   2019-09-17 16:13:28
select now(),sysdate();
-- 12
select month('2017-12-15');
-- 1
select dayofmonth('2019-01-01');
-- 2019-01-11
select date_add('2019-01-01',INTERVAL 10 day);
-- 2018-12-22
select date_sub('2019-01-01',INTERVAL 10 day);
-- 1
select datediff('2017-11-30','2017-11-29');
-- 20171130
select date_format('2017-11-30','%Y%m%d');
-- 1
select weekday(now());
-- 1303195194 
select unix_timestamp();
-- 2020-06-19 00:00:00
SELECT FROM_UNIXTIME(1592515200, '%Y-%m-%d %H:%i:%s');
2.5.4 流程函数

  主要指case…when…函数：

函数功能
IF(expr,v1,v2)当 expr 为真是返回 v1 的值，否则返回 v2
IFNULL(v1,v2)v1为空返回v2，否则返回v1
case多条件判断

  case语句有2种写法：

CASE <表达式>
 WHEN <值1> THEN <操作>
 WHEN <值2> THEN <操作>
 ...
 ELSE <操作>
END CASE;

CASE
 WHEN <条件1> THEN <命令>
 WHEN <条件2> THEN <命令>
 ...
 ELSE commands
END CASE;

  使用示例：

-- 1 x yes
select if(1<2,1,0) c1,if(1>5,'√','×') c2,if(strcmp('abc','ab'),'yes','no') c3;
-- 12345  非空
select ifnull(null,'12345'),ifnull('非空','为空');
2.5.5 聚合函数

  分组时，可以使用聚合函数。

函数功能
max查询指定列的最大值
min查询指定列的最小值
count统计查询结果的行数
sum求和，返回指定列的总和
avg求平均值，返回指定列数据的平均值

2.6 Mysql字符集

  简单地说，字符集就是一套文字符号及其编码、比较规则的集合。
  Mysql常用字符集：

字符集是否定长编码方式说明
ACSII是单字节 7 位编码最早的奠基性字符集
GBK是双字节编码
UTF-32是4 字节编码目前很少采用
UTF-16否2 字节或 4 字节编码Java和Windows XP/NT等内部使用UTF-16
UTF-8否1 至 4 字节编码互联网和UNIX/Linux 广泛支持的Unicode字符集；MySQLServer也使用UTF-8

  选择字符集时，要考虑的因素：

  • 1、满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择Unicode字符集。对MySQL来说，目前就是UTF-8。
  • 2、如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。
  • 3、如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如GBK。因为，相对于UTF-8而言，GBK比较“小”，每个汉字只占2个字节，而UTF-8汉字编码需要3个字节，这样可以减少磁盘I/O、数据库cache，以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择UTF-8更好，因为GBK、UCS-2、UTF-16的西文字符编码都是2个字节,会造成很大不必要的开销。
  • 4、如果数据库需要做大量的字符运算，如比较、排序等，选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。

  MySQL服务器可以支持多种字符集，在同一台服务器、同一个数据库、甚至同一个表的不同字段都可以指定使用不同的字符集。

  • 总结
      总的来说，建议在能够完全满足应用的前提下，尽量使用小的字符集。因为更小的字符集意味着能够节省空间、减少网络传输字节数，同时由于存储空间的较小间接地提高了系统的性能。
      有很多字符集可以保存汉字，比如utf8、gb2312、gbk等等，但是常用的是gb2312和gbk。因为gb2312字库比gbk字库小，有些偏僻字（如：洺）不能保存。因此在选择字符集时一定要权衡这些偏僻字在应用出现的几率以及造成的影响，不能做出肯定答复的最好选用gbk。
      在实际项目开发时，使用utf8mb4即可，即：CHARSET=utf8mb4。

2.7 相关问题

2.7.1 MySQL中有关NULL的一些坑*
  • 1、MySQL中sum函数没统计到任何记录时，会返回null而不是0，可以使用IFNULL函数把null转换为0；
  • 2、MySQL中count(字段)不统计null值，COUNT(*)才是统计所有记录数量的正确方式。
  • 3、MySQL中使用诸如 =、<、> 这样的算数比较操作符比较NULL的结果总是NULL，这种比较就显得没有任何意义，需要使用IS NULL、IS NOT NULL或IFNULL()函数来比较。
  • 4、当IN和NULL比较时，无法查询出为NULL的记录。示例：
  • 5、当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空。示例：
  • 6、当字段为主键的时候，字段会自动设置为not null。

  综上所述，NULL的情况确实比较难以处理，容易出错，最有效的方法就是避免使用NULL。所以，强烈建议创建字段的时候字段不允许为NULL，设置一个默认值。

2.7.2 如何随机获取一条记录
	select  * from table_name order by rand() limit 1;
2.7.3 常见的编码方式

  计算机中存储的最小单元是一个字节即8bit，所能表示的字符范围是255个，而人类要表示的符号太多，无法用一个字节来完全表示，固需要将符号编码，将各种语言翻译成计算机能懂的语言。
  ASCII码：总共128个，用一个字节的低7位表示，0〜31控制字符如换回车删除等；32~126是打印字符，可通过键盘输入并显示出来。
  ISO-8859-1：用来扩展ASCII编码，256个字符，涵盖了大多数西欧语言字符。
  GB2312：双字节编码，总编码范围是A1-A7，A1-A9是符号区，包含682个字符，B0-B7是汉字区，包含6763个汉字。
  GBK：为了扩展GB2312，加入了更多的汉字，编码范围是8140~FEFE，有23940个码位，能表示21003个汉字。
  UTF-16：ISO试图想创建一个全新的超语言字典，世界上所有语言都可通过这本字典Unicode来相互翻译，而UTF-16定义了Unicode字符在计算机中存取方法，用两个字节来表示Unicode转化格式。不论什么字符都可用两字节表示，即16bit，固叫UTF-16。
  UTF-8：UTF-16统一采用两字节表示一个字符，但有些字符只用一个字节就可表示，浪费存储空间，而UTF-8采用一种变长技术，每个编码区域有不同的字码长度。 不同类型的字符可以由1~6个字节组成。

2.7.4 utf-8编码中的中文占几个字节

  utf-8是一种变长编码技术，utf-8编码中的中文占用的字节不确定，可能2个、3个、4个。

2.7.5 临时表

  临时表：MySQL用于存储一些中间结果集的表，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
  为什么会产生临时表：一般是由于复杂的SQL导致临时表被大量创建。
  下列操作会使用到临时表：

  1. union查询
  2. 对于视图的操作，比如使用union
  3. 子查询
  4. join，包括not in、exist等
  5. 复杂的group by和order by
  6. Insert select 同一个表，Mysql会产生一个临时表缓存select的行
  7. 多个表更新
  8. COUNT(DISTINCT) 语句

2.8 Mysql中的常用命令

  • 1、查看数据库版本
      mysql --version或者mysql -V：用于在未登录情况下，查看本机mysql版本。示例：

      select version();：登录情况下，查看版本。示例：
  • 2、显示所有数据库
      show databases;。
  • 3、进入指定的库
      use 库名;。
  • 4、显示当前库中所有的表
      show tables;。
  • 5、查看其他库中所有的表
      show tables from 库名;。
  • 6、查看表的创建语句
      show create table 表名;。
  • 7、查看表结构
      desc 表名。
  • 8、查看当前所在库
      select database();。
  • 9、查看当前mysql支持的存储引擎
      SHOW ENGINES;。
  • 10、查看系统变量及其值
      SHOW VARIABLES;。
  • 11、查看某个系统变量
      SHOW VARIABLES like '变量名';。示例：

2.9 正则表达式

2.9.1 语法

  正则表达式用来描述或者匹配符合规则的字符串。正则表达式需要使用REGEXP命令，匹配上返回"1"匹配不上返回"0"，默认不加条件REGEXP相当于like ‘%%’。在前面加上NOT相当于NOT LIKE。

符号说明
^在字符的首部进行匹配
$在字符的末尾处进行匹配
.匹配任何字符（包括回车和新行）
[….]匹配括号内的任意单个字符
[m-n]匹配m到n之间的任意单个字符，例如[0-9],[a-z],[A-Z]
[^…]不能匹配括号内的任意单个字符
a*匹配0个或多个a,包括空,可以作为占位符使用
a+匹配一个或多个a,不包括空
a?匹配一个或0个a
`a1a2`
a{m}匹配m个a
a{m,}匹配m个或者更多个a
a{m,n}匹配m到n个a
a{,n}匹配0到n个a
(….)将模式元素组成单一元素，例如（do）*意思是匹配0个多或多个do
2.9.2 例子
  1. ^：在字符串开始处进行匹配
	# 1
	SELECT  'abc' REGEXP '^a';
  1. $：在字符串末尾开始匹配
	# 0
	SELECT  'abc' REGEXP 'a$';
	# 1
	SELECT  'abc' REGEXP 'c$';
  1. .：匹配任意字符
	# 0
	SELECT  'abc' REGEXP '.a';
	# 1
	SELECT  'abc' REGEXP '.b';
	# 1
	SELECT  'abc' REGEXP '.c';
	# 1
	SELECT  'abc' REGEXP 'a.';
  1. [...]：匹配括号内的任意单个字符
	# 0
	SELECT  'abc' REGEXP '[xyz]';
	# 1
	SELECT  'abc' REGEXP '[xaz]';
  1. [^...]：^只有在[ ]内才是取反的意思，在别的地方都是表示开始处匹配
	# 0
	SELECT  'a' REGEXP '[^abc]';
	# 1
	SELECT  'x' REGEXP '[^abc]';
	# 1
	SELECT  'abc' REGEXP '[^a]';
  1. a*：匹配0个或多个a,包括空字符串。 可以作为占位符使用
	# 1
	SELECT 'stab' REGEXP '.ta*b';
	# 1
	SELECT 'stb' REGEXP '.ta*b';
	# 1
	SELECT '' REGEXP 'a*';
  1. a+：匹配1个或者多个a,但是不包括空字符
	# 1
	SELECT 'stab' REGEXP '.ta+b';
	# 0
	SELECT 'stb' REGEXP '.ta+b';
  1. a?：匹配0个或者1个a
	# 1
	SELECT 'stb' REGEXP '.ta?b';
	# 1
	SELECT 'stab' REGEXP '.ta?b';
	# 0
	SELECT 'staab' REGEXP '.ta?b';
  1. a1|a2：匹配a1或者a2
	# 1
	SELECT 'a' REGEXP 'a|b';
	# 1
	SELECT 'b' REGEXP 'a|b';
	# 1
	SELECT 'b' REGEXP '^(a|b)';
	# 1
	SELECT 'a' REGEXP '^(a|b)';
	# 0
	SELECT 'c' REGEXP '^(a|b)';
  1. a{m}：匹配m个a
	# 1
	SELECT 'auuuuc' REGEXP 'au{4}c';
	# 0
	SELECT 'auuuuc' REGEXP 'au{3}c';
  1. a{m,}：匹配m个或者更多个a
	# 1
	SELECT 'auuuuc' REGEXP 'au{3,}c';
	# 1
	SELECT 'auuuuc' REGEXP 'au{4,}c';
	# 0
	SELECT 'auuuuc' REGEXP 'au{5,}c';
  1. a{m,n}：匹配m到n个a,包含m和n
	# 1
	SELECT 'auuuuc' REGEXP 'au{3,5}c';
	# 1
	SELECT 'auuuuc' REGEXP 'au{4,5}c';
	# 0
	SELECT 'auuuuc' REGEXP 'au{5,10}c';
  1. (abc)：将abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号
	# 1
	SELECT 'xababy' REGEXP 'x(abab)y';
	# 1
	SELECT 'xababy' REGEXP 'x(ab)*y';
	# 1
	SELECT 'xababy' REGEXP 'x(ab)*y';

2.10 权限管理简述

  环境：mysql5.7.25。

2.10.1 Mysql权限工作原理

  Mysql为了安全性考虑，采用 主机名+用户名 来判断一个用户的身份，因为在互联网中很难通过用户名来判断一个用户的身份，但是我们可以通过ip或者主机名判断一台机器，某个用户通过这个机器过来的，我们可以识别为一个用户，所以mysql中采用用户名+主机名来识别用户的身份。当一个用户对mysql发送指令的时候，mysql就是通过用户名和来源（主机）来断定用户的权限。
  Mysql权限验证分为2个阶段：

  • 阶段1
      连接数据库，此时mysql会根据你的用户名及你的来源（ip或者主机名称）判断是否有权限连接。
  • 阶段2
      对mysql服务器发起请求操作，如create table、select、delete、update、create index等操作，此时mysql会判断你是否有权限操作这些指令。
2.10.2 权限生效时间

  用户及权限信息放在库名为mysql的库中，mysql启动时，这些内容被读进内存并且从此时生效，所以如果通过直接操作这些表来修改用户及权限信息的，需要 重启mysql 或者执行flush privileges;才可以生效。
  用户登录之后，mysql会和当前用户之间创建一个连接，此时用户相关的权限信息都保存在这个连接中，存放在内存中，此时如果有其他地方修改了当前用户的权限，这些变更的权限会在下一次登录时才会生效。

2.10.3 用户操作

  Mysql的用户信息存在 mysql库的user表中。

  • 创建用户
      语法：
create user 用户名[@主机名] [identified by '密码'];
  1. 主机名默认值为%，表示这个用户可以从任何主机连接mysql服务器。
  2. 密码可以省略，表示无密码登录。
  • 修改密码
-- 方式1：通过管理员修改密码
SET PASSWORD FOR '用户名'@'主机' = PASSWORD('密码');
-- 方式2：create user 用户名[@主机名] [identified by '密码'];
set password = password('密码');
-- 方式3：通过修改mysql.user表修改密码
use mysql;
update user set authentication_string = password('321') where user = 'test1' and
host = '%';
flush privileges;

  通过表的方式修改之后，需要执行 flush privileges; 才能对用户生效。
  5.7中user表中的authentication_string字段表示密码，老的一些版本中密码字段是password。

  • 用户授权
      语法：
grant privileges ON database.table TO 'username'[@'host'] [with grant option]

  grant命令说明：

  1. priveleges (权限列表)，可以是 all ，表示所有权限，也可以是 select、update 等权限，多个权限之间用逗号分开。
  2. ON 用来指定权限针对哪些库和表，格式为 数据库.表名 ，点号前面用来指定数据库名，点号后面用来指定表名， . 表示所有数据库所有表。
  3. TO 表示将权限赋予某个用户, 格式为 username@host ，@前面为用户名，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。
  4. WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。 备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。

  授权sql示例：

-- 给test1授权可以操作所有库所有权限，相当于dba
grant all on *.* to 'test1'@'%';
-- test1可以对seata库中所有的表执行select、update
grant select,update on seata.* to 'test1'@'%';
  • 查看用户权限
-- 主机可以省略，默认值为%
show grants for '用户名'[@'主机']
-- 查看当前用户的权限
show grants;
  • 撤销用户的权限
revoke privileges ON database.table FROM '用户名'[@'主机'];
  • 删除用户
-- drop的方式删除用户之后，用户下次登录就会起效
drop user '用户名'[@'主机']
-- 注意通过表的方式删除的，需要调用 flush privileges; 刷新权限信息（权限启动的时候在内存中保存着，通过表的方式修改之后需要刷新一下）。
delete from user where user='用户名' and host='主机';
flush privileges;
2.10.4 授权原则

  只授予能满足需要的最小权限，防止用户干坏事，比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
  创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
  初始化数据库的时候删除没有密码的用户，安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。
  为每个用户设置满足密码复杂度的密码。
  定期清理不需要的用户，回收权限或者删除用户。

2.11 子查询

  条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果。
  嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

  按结果集的行列数不同分为4种：标量子查询（结果集只有一行一列）、列子查询（结果集只有一列多行）、行子查询（结果集有一行多列）、表子查询（结果集一般为多行多列）。
  按子查询出现在主查询中的不同位置分：

select后面：仅仅支持标量子查询。
from后面：支持表子查询。
where或having后面：支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）。
exists后面（即相关子查询）：表子查询（多行、多列）。

2.11.1 select后面的子查询

  示例：

SELECT
	 a.*,
	(SELECT count(*)
	 FROM employees b
	 WHERE b.department_id = a.department_id) AS 员工个数
FROM departments a;
2.11.2 from后面的子查询

  将子查询的结果集充当一张表，要求必须起别名，否者这个表找不到；然后将真实的表和子查询结果表进行连接查询。示例：

SELECT
 t1.department_id,
 sa AS '平均工资',
 t2.grade_level
FROM (SELECT
    department_id,
    avg(a.salary) sa
   FROM employees a
   GROUP BY a.department_id) t1, job_grades t2
WHERE
 t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
  • exists/相关子查询
      语法：exists(完整的查询语句)。
      exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。
      一般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。
      exists语句中，先执行主查询，然后主查询查询的结果，在根据子查询进行过滤。子查询中涉及到主查询中用到的字段，所以叫相关子查询。
2.11.3 where和having后面的子查询

  where或having后面，可以使用

  1. 标量子查询（单行单列行子查询）
  2. 列子查询（单列多行子查询）
  3. 行子查询（一行多列）

  特点：

  1. 子查询放在小括号内。
  2. 子查询一般放在条件的右侧。
  3. 标量子查询，一般搭配着单行单列操作符使用 >、<、>=、<=、=、<>、!=。
  4. 列子查询，一般搭配着多行操作符使用，如：in(not in)、any、some、all。
  5. 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。

三、事务

  事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。
  事务最主要的目的是为了数据一致性。

3.1 事务的四大特性(ACID)*

  • 1、原子性(Atomicity)
      事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  • 2、一致性(Consistency)
      一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

  拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

  • 3、隔离性(Isolation)
     并发访问数据库时，即一个事务内部的操作及使用的数据对并发的其他事务是隔离
    的，并发执行的各个事务之间不能互相干扰。
  • 4、持久性(Durability)
     一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

3.2 Mysql中的事务操作

  Mysql中事务默认是隐式事务，执行insert、update、delete操作的时候，数据库自动开启事务、提交或回滚事务。
  事务分为隐式事务和显式事务。是否开启隐式事务是由变量 autocommit 控制的。可以通过set session autoCommit = on/off来设置mysql事务是否自动开启。如果我们设置autoCommit为off的时候，需要手动开启mysql事务。

3.2.1 隐式事务

  事务自动开启、提交或回滚，比如insert、update、delete语句，事务的开启、提交或回滚由Mysql内部自动控制的。
  查看变量 autocommit 是否开启了自动提交命令：

show variables like 'autocommit';

  实际项目开发时，用隐式事务即可。

3.2.2 显式事务

  需要手动开启、提交或回滚，由开发者自己控制。有两种方式：

-- 方式1
//设置不自动提交事务
set autocommit=0;
//执行事务操作
commit|rollback;

-- 方式2
start transaction;//开启事务
//执行事务操作
commit|rollback;
3.2.3 savepoint

  如果在事务中我们执行了一大批操作，可只想回滚部分数据。此时可以将一大批操作分为几个部分，然后指定回滚某个部分，通过savepoint(保存点)来实现。示例：

start transaction;
insert into test1 values (1);
-- 设置一个保存点
savepoint part1;
insert into test1 values (2);
-- 将savepint = part1的语句到当前语句之间所有的操作回滚
rollback to part1;
-- 提交事务
commit;
3.2.4 只读事务

  表示在事务中执行的是一些只读操作，如查询，但是不会做insert、update、delete操作。开启只读事务后，执行修改操作会报错。

3.3 事务产生的问题

3.3.1 脏读(Drity Read)*

  已知有两个事务A和B, A读取了已经被B更新但还没有被提交的数据。之后，B回滚事务，A读取的数据就是脏数据。即：一个事务在执行的过程中读取到了其他事务还没有提交的数据。

此处的"脏"主要指由于回滚，导致了数据的无效性，也就是读到了脏数据。

  脏读，可以简单理解为读到了无效数据。

3.3.2 不可重复读(Non-repeatable read)*

  已知有两个事务A和B，A多次读取同一数据，B在A多次读取的过程中对数据作了修改并提交，导致A多次读取同一数据时，结果不一致。

此处的"重复"指的是由于修改，某个事务重复读取的某个值发生了变化。

  不可重复读，可简单理解为多次重复读取，读取到了不一样的数据。

3.3.3 幻读(Phantom Read)*

  幻读在可重复读的模式下才会出现，其他隔离级别中不会出现。
  有两个事务A和B，A从一个表中读取了数据，然后B在该表中插入了一些新数据，导致A再次读取同一个表, 就会多出几行。简单地说，一个事务中先后读取一个范围的记录，但每次读取的纪录数不同，多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

  • 不可重复读和幻读区别
      不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

3.4 事务的隔离级别*

  为了解决多个事务之间数据可见性及数据正确性的问题，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted(读未提交)、Read committed(读已提交)、Repeatable read(可重复读)、Serializable(串行)。
  事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
  InnoDB存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

	-- 查看隔离级别命令
    show variables like 'transaction_isolation';

  要修改隔离级别的话，可以进行全局修改，也就是修改配置文件（my.ini），示例：

transaction-isolation=READ-UNCOMMITTED

  也可以通过命令修改当前session的隔离级别：

set session transaction isolation level 事务级别;
3.4.1 各种隔离级别中会出现的问题
隔离级别脏读不可重复读幻读
读未提交有有无
读已提交无有无
可重复读无无有
串行化无无无

  表格中和网上有些不一样，主要是幻读这块，幻读只会在可重复读级别中才会出现，其他级别下不存在。

3.4.2 READ-UNCOMMITTED(读未提交)*

  最低的隔离级别，允许一个事务可以读取另外一个事务未提交的事务。多次读取结果不一样，会出现了脏读、不可重复读问题。
  这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用。

3.4.3 READ-COMMITTED(读已提交)*

  允许一个事务读取另一个并发事务已经提交的数据。读已提交情况下，无法读取到其他事务还未提交的数据，可以读取到其他事务已经提交的数据，多次读取结果不一样，未出现脏读，出现了读已提交、不可重复读。

3.4.4 REPEATABLE-READ(可重复读)*

  对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改。即使数据被其他事务修改， 当前事务也不会读取到新的数据。重复读事务中的查询看到的是事务开始时的快照， 而不是该事务当前查询开始时的快照。
  可重复读情况下，未出现脏读，未读取到其他事务已提交的数据，多次读取结果一致，即可重复读。

3.4.5 SERIALIZABLE(串行)*

  最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰（多个事务之间读写、写读、写写会产生互斥，效果就是串行执行，多个事务之间的读读不会产生互斥）。

  该级别可以防止脏读、不可重复读以及幻读。

3.4.6 隔离级别小结

  四种隔离级别和能解决问题情况：

事务隔离级别脏读不可重复读幻读
读未提交不能解决不能解决不能解决
读已提交能解决不能解决不能解决
可重复读能解决能解决不能解决
串行化能解决能解决能解决

  Mysql默认采用的REPEATABLE_READ（可重复读）隔离级别。
  事务隔离机制的实现基于锁机制和并发调度。因此，隔离级别越高，并发性也低，比如最高级别 SERIALIZABLE 会让事物串行执行，并发操作变成串行了，会导致系统性能直接降低。
  因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读已提交)，但是InnoDB存储引擎默认使用REPEATABLE-READ（可重复读），并不会有任何性能损失。
  InnoDB存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

3.5 ACID的保证

  事务的原子性是通过 undo log 来实现的。
  事务的持久性是通过 redo log 来实现的。
  事务的隔离性是通过 (读写锁+MVCC)来实现的。
  事务的一致性是通过原子性、持久性、隔离性来实现的。

  • 原子性
      1、每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,并且回滚日志必须先于数据持久化到磁盘上。
      2、所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert，insert的逆向操作为delete，update的逆向为update等。
  • 持久性
  • 隔离性
      MySQL定义了4种隔离级别。隔离性是要做到什么呢？ 隔离性是要管理多个并发读写请求的访问顺序。MVCC和读写锁都能达到一定的隔离效果。
  • 一致性
      数据库总是从一个一致性的状态转移到另一个一致性的状态。通过回滚，以及恢复，和在并发环境下的隔离可以达到一致性的效果。
解梦者
关注
  • 3
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySql复习–（1）基础SQL语法
12-15
为了能够从实际操作上更深入理解MySql，提升个人的知识水平，决定这一周跟着网课把MySql再过一遍，同时刷一些leetcode题目，特意在这里总结 数据库好处： #1.持久化数据到本地 2. 结构化查询 常见概念 DB：数据库 DBMS：数据库管理系统 SQL：结构化查询语言 存储数据特点 1.数据存到表，表存到库 2.一个库多个表，一个表用唯一表名标识 3.表中一个多个字段 常见DBMS mysql、oracle、db2、 mysql背景 前身瑞典公司mysqlAB 08 年被收购 #09 年sun被oracle 收购 mysql 优点 1.开源免费，成本低 2/ 性能高、移植性好 3.
MySQL中几种数据统计查询的基本使用教程
12-15
统计平均数 SELECT AVG() FROM 语法用于从数据表中统计数据平均数。 语法： SELECT AVG(column) FROM tb_name 该 SQL 语法用于统计某一数值类型字段的平均数，AVG() 内不能是多个字段，字符串等类型虽然可以执行，但无意义。 例子： SELECT AVG(uid) FROM user 得到查询结果： 2.5000 当然在此统计 uid 的平均数是无实际生产意义的，只是为了演示 AVG() 语法的用法。 统计数据之和 SELECT SUM() FROM 语法用于从数据表中统计数据之和。 语法： SELECT SUM(colum
PHP用mysql_insert_id()函数获得刚插入数据或当前发布文章的ID
12-18
前言 最近在工作中又遇到了这个问题，PHP中如何获得刚插入数据的ID（或当前发布文章的ID）呢？觉得有必要整理下详细的解决方法，方便自己也给有需要的朋友们提供以帮助，那么话不多说了，来看看详细的解决介绍。 解决方法 其实用 mysql_insert_id() 函数就可以实现的。 定义和用法 mysql_insert_id() 函数返回上一步 INSERT 操作产生的 ID。 注意：如果上一查询没有产生 AUTO_INCREMENT 的 ID，则 mysql_insert_id() 返回 0。 语法 mysql_insert_id(connection) 参数描述 connecti
MySQL 常用函数的使用
12-14
目录I. MySQL 常用函数1. 字符串函数a. 常用函数b. 导入数据c. 示例题目2. 日期函数a. 常用函数b. 示例题目3. 数字函数a. 常用函数b. 示例题目4. 高级函数a. CASE 表达式i. 语法ii. 示例题目b. IF 表达式i. 语法ii. 示例题目II. MySQL 函数综合案例III. 附录1. 数学函数2. 聚合函数3. 字符串函数4. 日期和时间函数> DATE_FORMAT 可以使用的格式5. 加密函数6. 控制流函数7. 格式化函数8. 类型转化函数9. 系统信息函数 I. MySQL 常用函数 为了简化操作，MySQL 提供了大量的函数给程序员使用
解析在MYSQL语法中使用trim函数删除两侧字符
01-21
在实际开发中，你总是会遇到这种情况 或者遇到这种情况 数据多了也会令你抓狂，如果还在用select fields from table where like ‘/%’ 绝对是一个吃力不讨好的做法，那么，仔细研究MYSQL自带函数那绝对是首要工作。我第一时间就是想到，PHP函数中的trim是不是也可能用到mysql中呢，回答是肯定的。你可以从MYSQL手册中找到，当然，你可以百度一下，我下面说的与前人相同。SELECT *,trim(LEADING ‘/’ FROM `path`) as paths FROM `ts_back_pic` limit 10隆重介绍第一个，LEADING，可以删
MySQL中常用查看锁和事务的SQL语句
最新发布
weixin_45626288的博客
07-02 4357
这条语句将返回一个包含当前正在等待锁的事务的结果集，包括等待锁的事务ID、等待的锁类型和被等待的锁类型等信息。这条语句将显示InnoDB引擎的状态信息，其中包含了当前执行的事务信息、锁等待的信息以及其他相关的统计数据。这条语句将返回一个包含当前正在被锁定的锁的结果集，包括锁的类型、锁定的对象和持有锁的事务ID等信息。这条语句将返回InnoDB引擎的状态信息，其中包含了关于死锁的详细描述，包括涉及的事务和锁的信息。这条语句将返回一个包含所有当前活动事务的结果集，包括事务ID、开始时间、等待锁的状态等信息。
MySQL 常用SQL语句
05-13 4477
mysql教程 ■  在DOS命令行启动MYSQL服务： net start mysql ■在DOS命令行停止MYSQL服务： net stop mysql ■查看被监听的端口: netstat –na | findstr 3306 findstr用于查找后面的端口是否存在。 ■创建数据库用户：只有根用户（root）才有创建新用户的权限 CREATE USER
数据库事务处理SQL语句
tianyin的博客
04-08 1504
<br />BEGIN TRANSACTION<br />begin<br />insert into dbo.bu_list values('11111111111','','','')<br />insert into dbo.ccc_list values('11111111111','')<br />end<br />IF(@@error!= 0)<br />BEGIN<br /> ROLLBACK TRANSACTION<br />END<br />ELSE<br />BEGIN<br /> CO
mysql数据库之常用SQL语句及事务学习笔记（一）
•一个写代码的• •知识星球•https://t.zsxq.com/15rGFbGEk
05-15 279
mysql学习笔记。 数据库介绍 几个常见的缩写： DB：数据库。全称：DataBase。 DBMS：数据库管理系统。全称：DataBase Management System。 DBS：数据库系统。全称：DataBase System。 DBA：数据库管理员。全称：DataBase Administrator。 DBS、DBMS、DB三者关系：DBS包含DB、DBMS。 数据库定义...
MYSQL关于事务的SQL
you are sherlocked by me!
10-08 359
事务 事务:  一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务  一个完整的业务通常需要批量的DML(data manupulation language)语句共同联合完成(insert, update, delete)  事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同 事务(ACID)的四大特征:  原子性(Atomi...
MySql学习笔记
yjh_________的博客
06-13 129
数据库（DataBase）是按照一定格式存储数据的一些文件的组合。数据存储的仓库，实际上就是一堆文件，这些文件中存储了具有特定格式的数据。数据库管理系统（DataBaseManagement）是专门用来管理数据库中文件的，数据库管理系统可以对数据库中数据进行增删改查。常见的数据库管理系统：MySQL、Oracle等SQL（Structured Query Language）是结构化查询语言，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的CRUD。
mysql学习之路
weixin_43382980的博客
01-28 254
一、数据库简介 1.1、简介 人类在进化的过程中，创造了数字、文字、符号等来进行数据的记录，但是承受着认知能力和创造能力的提升，数据量越来越大，对于数据的记录和准确查找，成为了一个重大难题 计算机诞生后，数据开始在计算机中存储并计算，并设计出了数据库系统 数据库系统解决的问题：持久化存储，优化读写，保证数据的有效性 当前使用的数据库，主要分为两类 文档型，如sqlite，就是一个文件，通过对文件的...
数据库MySQL学习——内含34道MySQL练习题及答案
risroy的博客
09-25 6292
数据库MySQL 1 MySQL数据库简介 1.1 sql、DB、DBMS分别是什么，关系？ DB：DataBase 数据库 DBMS：DateBase Management System 数据库管理系统 SQL：结构化查询语言、sql语句的编译有dbms完成 DBMS负责执行sql语句，通过之心sql语句来操作DB当中的数据 1.2 什么是表？ table 是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强 行：被称为数据/记录（data） 列：被称为字段（column） 学号（
数据库设计三范式及事务的详解
ls_wifi的博客
07-31 491
数据库设计三范式: 定义：设计数据库的时候所依据的规范，共有三个规范; 第一范式:要求有主键，数据库中不能出现重复记录，每一个字段是原子性不能再分; 第二范式:第二范式是建立在第一范式基础之上，要求数据库中所有非主键字段完全依赖主键，不能产生部份依赖;(严格意义上说:尽量不要使用联合主键) 第三范式:建立在第二范式基础之上,要求非主键字段不能产生传递依赖...
数据库三大范式与事务
m0_69969811的博客
05-21 101
数据库三大范式 数据库的范式:是设计数据库一种规范要求,每一个范式呈递次规范; 第一范式1NF:数据库表中每一列是不可在拆分的原子性,不能出现复合列!(集合数据) 第二范式2NF:在1NF基础上,每一张表描述一件事情;非主键字段完全依赖于主键字段; 第三范式3NF:在2NF基础上,非主键字段和主键字段不能产生传递依赖,使用外键约束来操作 事务 事务:是关系型数据库的一种机制: 当批量操作sql或者多张表同时操作(update,inseret into,delete),这...
【数据库】期末复习：SQL语句、关系代数的运算、范式的定义和判断、求最小函数依赖集、ER图转关系模式
karshey的博客
06-08 2124
学生表s，课程表c，学生选课表sc。查询被学生(学号为A)选修的课程有几个？ 查询年龄在18-19之间的学生信息。 查询姓刘的同学的信息。 如果名字是两个字且姓刘，则为’刘_'。查询没有先修课的课程信息。 求各门课学生的平均成绩，显示课程号及相应的平均成绩 查询有2个以上学生选修的课程的课程号。 group by了就不能where 要having查询学生选修课程的情况要求，显示学生名、课程名、成绩（三表查询)。 查询选修了全部课程的学生姓名 其他 创建表dept_age，包含2个字段：系别和平均年龄。按系分
数据类型
Technology Blog
03-01 1648
整数类型 根据数值取值范围的不同MySQL 中的整数类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围: 其中，我们最常用的整数类型为INT；在某些教程中也将其称为INTEGER 浮点数类型和定点数类型 在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。下图列举了 MySQ
MySQL学习笔记——MySQL数据类型（拉勾教育数据分析实战训练营学习笔记）
weixin_44699915的博客
08-09 263
MySQL学习笔记——MySQL数据类型 MySQL数据库中，每一条数据都有其数据类型，主要可以分为数值型，字符串型和日期时间型三大类。 说明如下所示： 数值类型 TINYINT 一个非常小的整数，占1字节。 如果是有符号，范围是（-128—127）；如果是无符号，范围是（0—255）。 INT 正常大小的整数，占4字节。 如果是有符号的，范围是（-2147483648—2147483647）；如果是无符号，范围是（0—4294967295）。 BIGINT 一个大的整数，占8字节。 如
MySQL类型 - 实战练习
骆驼胡杨的博客
11-24 533
创建一个用户信息表，用来存储用户的如下信息：姓名，手机号码，家庭地址，个人简介，性别，年龄，身份证号。数据库表名可定义为userinfo, 同时增加一个列id 用来作为每一条记录的唯一标识，并设为主键，自加！ ...
mysql5 和 mysql8 sql语法有区别吗
05-11
是的，MySQL 5 和 MySQL 8 的 SQL 语法有一些差异。以下是一些主要的区别： 1. JSON 数据类型 MySQL 8 开始支持 JSON 数据类型，可以存储和查询 JSON 数据，而在 MySQL 5 中并不支持。 2. CTE MySQL 8 引入了公共表表达式（CTE），这是一种创建命名临时结果集的方法。MySQL 5 中不支持 CTE。 3. Window Functions MySQL 8 引入了窗口函数（Window Functions），这是一种在结果集的一部分上执行聚合函数（如 SUM、AVG、COUNT）的方法。MySQL 5 中不支持窗口函数。 4. Indexes MySQL 8 改进了索引的性能和功能。例如，MySQL 8 支持函数和表达式索引，而 MySQL 5 不支持。 5. 改进的 JOIN 性能 MySQL 8 改进了 JOIN 的性能，特别是在处理大型数据集时。MySQL 5 的 JOIN 性能不如 MySQL 8。 总之，MySQL 8 的 SQL 语法相对于 MySQL 5 有了很多改进和扩展。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值