MYSQL数据库常用命令

1. 查看当前所有的数据库
   show databases;
2. 打开指定的库
   use 库名;
3. 查看当前库的所有表
   show tables;
4. 查看其它库的所有表
   show tables from 库名;
5. 创建表
   create table IF NOT EXISTS 表名(
   列名 列类型 【(长度)约束】；
   列名 列类型 【(长度)约束】;
   )
6. 查看表结构
   desc 表名;
7. SELECT 查询
   SELECT * FROM 表名;
   SELECT 字段名，表达式，产量，函数 FROM 表名;
   SELECT 字段名 AS 别名 FROM 表名;
   SELECT 字段名 别名 FROM 表名;
   SELECT DISTINCT 字段名 FROM 表名; //去重
   >注:别名含有空格或特殊字符需要单双引号。
   SELECT CONCAT('str','str') FROM 表名; //字符串拼接
   SELECT IFNULL(字段名,0) FROM 表名; //如果字段为空替换为0
   SELECT * FROM 表名 WHERE 字段名 运算符 111; //例SELECT * FROM user WHERE Id > 3,userid大于3的所有数据
   SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '通配符' ; //通配符 %为任意数量字符(包括0), _一个任意字符
   SELECT 字段名 FROM 表明 WHERE 字段名 LIKE '通配符' ESCAPE '自定义转义字符'; //默认\为转义，可自定义转移字符
   SELECT * FROM 表名 WHERE 字段名 BETWEEN 值 AND 值; //相当于大于第一个值，小于第二个值;(包含前后两个值)
   SELECT * FROM 表名 WHERE 字段名 IN('字段值','字段值','字段值'); //任意一字段值满足即输出
   SELECT * FROM 表名 WHERE 字段名 <=> NULL;
   SELECT * FROM 表名 ORDER BY 字段名 DESC; //降序，如果不写，默认升序
   SELECT * FROM 表名 ORDER BY 字段名 ASC; //升序

函数:

1. 字符函数
   SHOW VARIABLES LIKE '%char%'; //查询客户端与服务端字符编码
   SELECT LENGTH('字符串'); //查询字符长度
   SELECT CONCAT(字段名,'_',字段名) FROM 表名; //拼接字符串
   SELECT UPPER('字符串'); //大写
   SELECT LOWER('字符串'); //小写
   SELECT SUBSTR('字符串',值(Int)); //截取从指定索引处后面所有字符
   SELECT SUBSTR('字符串',值(Int),值(Int)); //截取从指定索引处指定字符长度的字符
   SELECT INSTR('字符串','字符串'); //返回第二个字符串在第一个字符串中第一次出现的索引，如果找不到则返回0
   SELECT TRIM(' 字符串 '); //去除首尾空格
   SELECT TRIM('字符串'FROM'字符串'); //去除第二个字符串的含有第一个字符串的首尾
   SELECT LPAD('字符串',值(Int),'字符串'); //用第二个字符串对第一个字符串从左填充至指定的长度
   SELECT RPAD('字符串',值(Int),'字符串'); //用第二个字符串对第一个字符串从右填充至指定的长度
   SELECT REPLACE('字符串','字符串','字符串'); //使第一个字符串中含有第二个字符串替换为第三个字符串
   SELECT ROUND(值(Int)); //四舍五入
   SELECT ROUND(值(Int),值(Int)); //第二个值为保留小数位
   SELECT CEIL(值(Int)); //向上取整
   SELECT FLOOR(值(Int)); //向下取整
   SELECT TRUNCATE(值(Int),值(Int)); //截断，从第一个值中阶段第二个值位数的小数位
   SELECT MOD(值(Int),值(Int)); //取余，第一个值%第二个值，符号跟着第一个值，mod(a,b) => a-a/b*b
   SELECT NOW(); //返回当前系统日期+时间
   SELECT CURDATE(); //返回当前系统日期,不包含时间
   SELECT CURTIME(); //返回当前时间,不包含日期
   SELECT YEAR(NOW()); //年
   SELECT YEAR('1990-1-1'); //年
   SELECT YEAR(字段名) FROM 表名; //年
   SELECT MONTH(NOW()); //月
   SELECT MONTHNAME(NOW()) //月(英文单词)
   //其他年月日小时分钟秒都一样
   NOW //获取当前日期
   STR_TO_DATE //将日期格式的字符转换成指定格式的日期
   例: STR_TO_DATE('9-13-1999','%m-%d-%Y'); //1999-09-13
   DATE_FORMAT('2018/6/6','%Y年%m月%d日'); //2018年6月6日
   格式符|功能
   :-:|:-:
   %Y|四位的年份
   %y|2位的年份
   %m|月份(例:01,02,03)
   %c|月份(例:1,2,3)
   %d|日
   %H|小时(二十四小时制)
   %h|小时(12小时制)
   %i|分钟
   %s|秒
   其他函数：
   SELECT VERSION();
   SELECT DATABASE();
   SELECT USER();
   SELECT IF(boolean,第一段,第二段); //如果boolean位trun则执行第一个，否则执行第二段
   CASE boolean
   WHEN 值1 THEN 值或表达式
   WHEN 值2 THEN 值或表达式
   ELSE
   END //如果等于值1执行对应的表达式，如果等于值2执行对应的表达式，否则执行else
   CASE
   WHEN boolean THEN 要显示的值1或语句1
   WHEN boolean THEN 要显示的值2或者语句2
   ELSE 要显示的值n或语句n
   END
   分组函数:
   功能: 用作统计使用，又称聚合函数或统计函数或组函数
   分类: sum求和、 avg平均值、max最大值、min最小值、count计算格式
   SELECT SUM(表字段) FROM 表;
   SELECT AVG(表字段) FROM 表;
   忽略NULL值;
   效率:
   MYISAM存储引擎下,COUNT(*)的效率高;
   INNODB存储引擎下,COUNT(*)的效率和COUNT(1)的效率差不多,比COUNT(字段)要高一些
   SELECT DATEDIFF('date','date'); //查询两个日期的相差天数
   SELECT 分组函数,列(要求出现在group by的后面)
   FROM 表
   【WHERE 】
   GROUP BY 分组的列表
   【HAVING 】
   sql92语法:
   多表查询(笛卡尔积消除):
   SELECT 字段1,字段2 FROM 表1,表2
   WHERE 表2.字段1 = 表2.字段2
   sql99语法:
   SELECT 查询列表
   FROM 表1【连接类型】
   JOIN 表2
   ON 连接条件
   【WHERE 筛选条件】
   【GROUP BY 分组】
   【HAVING 筛选条件】
   【ORDER BY 排序列表】
   分类：
   内连接：INNER
   外连接
   左外：LEFT 【OUTER】
   右外：RIGHT【OUTER】
   全外：FULL【OUTER】
   交叉连接：CROSS JOIN

   子查询：
   按子查询出现位置
   SELECT 后面
   仅仅支持表子查询
   FROM 后面
   支持表子查询
   WHERE或HAVING 后面
   标量子查询
   列子查询
   行子查询
   EXISTS 后面(相关子查询)
   表子查询
   按结果集的行列数不同：
   标量子查询(结果集只有一行一列)
   列子查询(结果集只有一列多行)
   行子查询(结果集只有一行多列)
   表子查询(结果集)

   特点:

   1. 子查询放在小括号内
   2. 子查询一般放在条件右侧
   3. 标量子查询，一般搭配着胆寒操作符使用
      > < >= <= = <>
   4. 列子查询，一般搭配多行操作符使用
      in、any/some、all

      操作符	含义
      IN/NOT IN	等于列表中的任意一个
      ANY/SOME	和子查询返回某一个值比较
      ALL	和子查询返回所有值比较

   union 联合查询
   查询语句1
   union
   查询语句2
   注：

   1. 要求多条查询与的查询列数是一致的！
   2. 要求多条查询语句的查询的每一列类型和顺序最好一致
   3. union关键字默认去重，如果使用union ALL 可以显示所有项

插入语句
INSERT INTO 表名(例名,...) values(值1,...);
INSERT INTO 表名
SET 列名=值,列名=值,...
修改语句
修改单表：
UPDATE 表名
SET 列=新值,列=新值,...
WHERE 筛选条件;
修改多表:
SQL92语法:
UPDATE 表1 , 表2
SET 列=值,...
WHERE 连接条件
AND 筛选条件;

SQL99语法:
UPDATE 表1
inner|left|right join 表2
ON 连接条件
SET 列=值,...
WHERE 筛选条件

删除语句
单表的删除：
DELECT FROM 表名 WHERE 筛选条件
多表的删除
sql92
DELETE 表1
FROM 表1,表2
WHERE连接条件
AND筛选条件
sql99
DELECT 表1,表2
FROM 表1
INNER|LEFT|RIGHT JOIN 表2 ON 连接条件
WHERE 筛选条件;

删除一个表
TRUNCATE TABLE 表名

数据库,表管理:
创建CREATE
修改ALTER
DROP

CREATE DATABASE IF NOT EXISTS 数据库名;

RENAME DATABASE 旧数据库名 TO 新数据库名(已废弃);

ALTER DATABASE 数据库名 CHARACTER SET 字符集类型;
DROP DATABASE IF EXISTS 库名;

表的修改:
1. 修改别名：
ALTER TABLE 表名 CHANGE 【COLUMN】 旧列名 新列名 列类型
2. 修改列的类型或约束：
ALTER TABLE 表名 MODIFY 【COLUNM】 列名 列类型
3. 添加新列:
ALTER TABLE 表名 ADD 【COLUMN】列名 列类型
4. 删除列：
ALTER TABLE 表名 DROP 【COLUMN】 列名
5. 修改表名：
ALTER TABLE 表名 RENAME TO 新表名
总结：
ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN

复制表
1. 仅仅复制表的结构
CREATE TABLE 新表名 LIKE 原表名;
2. 复制表的结构+数据
CREATE TABLE 新表名;
3. 只复制部分数据
CREATE TABLE 新表名
SELECT 字段名,字段名
FROM 原表名
WHERE 字段名=字段值;
4. 仅仅复制部分字段
CREATE TABLE 新表名
SELECT 字段名,字段名
FROME 原表名
WHERE 1=2; //等同于false

整形:

整形类型	字节	范围
Tinyint	1	有符号:-128-127 无符号:0-255
Smallint	2	有符号:-32768-32767 无符号:0-65535
Mediumint	3	有符号:-8388608-8388607 无符号:0-1677215
Int、integer	4	有符号:-2147483648-2147483647 无符号:0-4294967295
Bigint	8	有符号:-9223372036854775808-9223372036854775807 无符号:0-9223372036854775807*2+1

无符号:INT UNSIGNED
0填充: ZEROFLILL (附带更改类型为为无符号)

小数:
浮点数类型|字节|范围
:-:|:-:|:-:
float:4:±1.75494351E-38~±3.402823466E+38
double|8|±2.2250738585072014E-308~±1.7976931348623157E+308

定点数类型	字节	范围
DEC(M,D) DECIMAL(M,D)	M+2	最大取值范围与double相同，给定decimal的有效取值范围由M和D决定

float【(M,D)】
double【(M,D)】
dec(M,D)
M是小数和整数一共的位数,D是小数位

位类型	字节	范围
Bit(M)	1-8	Bit(1)-Bit(8)

字符串类型	最多字符数	描述及存储需求
char(M)	M	M为0-255之间的整数
varchar(M)	M	M为0-65535之间的整数

binary和varbinary:类似于char和varchar,不同的是他们包含二进制字符串而不包含非二进制字符串

Enum类型:
又称之为枚举类型,要求插入的值必须属于列表指定的值之一。
如果列表成员为1-255,则需要一个字节存储
如果列表成员为255-65535,则需要两个字节存储
最多需要65535个成员
注:可以理解成列表,但仅插入一项，不区分大小写

Set类型:
和Enum类型相似,里面可以保存0-64个成员。he Enum类型最大的区别是:SET类型一个可以选取多个成员,而Enum只能选一个，根据成员个数不同，存储的字节也不同

成员数	字节数
1-8	1
9-16	2
17-24	3

text:较长的文本
blob:较大的二进制

日期型:

日期和事件类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999:12:31 23:59:59
timetamp	4	1970-01-01 08:00:01	2038-01-19 03:14:07
time	3	-838:59:59	838:59:59
year	1	1901	2155

PS:timetamp:'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC.
注:
时间戳字段在MySQL各版本的使用差异

1、在MySQL 5.5及之前版本中，仅能对一个时间戳字段定义DEFUALT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP，但在MySQL 5.6和MySQL 5.7版本中取消了该限制;

2、在MySQL 5.6版本中参数explicit_defaults_for_timestamp默认值为1，在MySQL 5.7版本中参数explicit_defaults_for_timestamp默认值为0;

3、在MySQL 5.5和MySQL 5.7版本中timestamp类型默认为NOT NULL,在在MySQL 5.6版本中timestamp类型默认为NULL;

4、当建表语句中定于c1 timestamp 时，

在MySQL 5.5中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

在MySQL 5.6中等价于c1 timestamp NULL DEFAULT NULL;

在MySQL 5.7中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

5、当建表语句中c1 timestamp default 0时，

在MySQL 5.5中等价于c1 timestamp NOT NULL DEFAULT '0000-01-01 00:00:00';

在MySQL 5.6中等价于c1 timestamp NULL DEFAULT '0000-01-01 00:00:00';

在MySQL 5.7中等价于c1 timestamp NOT NULL DEFAULT '0000-01-01 00:00:00';
PS1: MySQL 5.6版本和MySQL 5.7版本中主要差异受参数explicit_defaults_for_timestamp的默认值影响。
PS2：当时间戳列的默认值为'0000-01-01 00:00:00'时，使用“不在时间戳取值范围内”的该默认值并不会产生警告。
时间戳类型引发的异常

当MySQL参数time_zone=system时，查询timestamp字段会调用系统时区做时区转换，而由于系统时区存在全局锁问题，在多并发大数据量访问时会导致线程上下文频繁切换，CPU使用率暴涨，系统响应变慢设置假死。

tiemstamp和实际的时区有关,更能反映实际的日期,而datetime则只能反映插入时的当时时区
timestamp的属性受mysql版本和SQLMode的影响很大

union:
合并、联合,将多次查询结果合并成一个结果
查询语句1
union 【all】
查询语句2
union 【all】

查询总结:
SELECT 查询列表
FROM 表1 别名
链接类型 JOIN 表2
ON 链接条件
WHERE 筛选
GROUP BY 分组列表
HAVING 筛选
ORDER BY 排序列表
LIMIT 起始条目索引,条目数

修改单表记录 update ... set
修改多表的记录
update 表1 别名 left|right|inner join 表2 别名
on 链接条件 set 字段=值, 字段=值 【where筛选条件】

delete from 表名 【where 筛选条件】

delete from 表1 inner|left|right join 表2 【where 筛选条件】 on 链接条件 【where 筛选条件】

DELETE FROM 表名 LIMIT 行数; 删除某行

alter table 表名 add column 列名 类型 【first|after 字段名】
注: first 第一个 after 某一字段后面

约束:
NOT NULL
DEFAULT 默认值
PRIMARY KEY 主键
UNIQUE 唯一
CHECK 检查约束 【mysql中不支持】
FOREIGN KEY 外键，用于限制两个表的关系，用于保证该字段的值必须来自主表关联列的值

约束分 列级约束，表级约束
列级约束 外键约束没有效果
表级约束 除了非空、默认其他的都支持

查询索引:
SHOW INDEX FROM 表名;
主键 外键自动添加索引

表级约束:
CONSTRAINF 表级约束名 PRIMARY KEY(主键的字段)
...
CONSTRAINF 表级约束名 FOREIGN KEY(外键字段) REFERENCES 另一个表的名称(该表外键字段)
【CONSTRAINF 表级约束名】约束类型(字段名)

修改表时添加约束
1. 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;
2. 添加表级约束
ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(字段名) 【外键的引用】;

标识列:
AUTO_INCREMENT 自动增长
SHOW VARIABLES LIKE 'auto_increment%'
auto_increment_increment 步长
auto_increment_offset 起始值(mysql 不支持)可以提前插入第一个值解决

一个表只能由一个标识列
标识列只能是数字型

innodb支持事务，其他不支持事务
事务的四个特点，事务的ACID：
1. 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务的操作要么都发生，要么都不发生
2. 一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另一个一致性状态
3. 隔离性(Isolation)
事务的隔离性使指一个事务的执行不能被其他事务干扰,即一个事务内部操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相符干扰
4. 持久性(Durability)
持久性是指一个事务一旦被提交,他对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响

事务的创建
隐式事务:事务没有明显的开启和结束的标记，比如insert、update、delete语句
显示事务: 前提:必须先设置自动提交功能为禁用 开启事务:(set autocommit=0; 【start transaction;】)
编写事务中的sql语句(select instert update delete);
...
结束事务:
commint;提交事务
rollback;回滚事务

脏读:对于两个事务T1、T2,T1读取了已经被T2更新但没有被提交的字段，之后，若T2回滚,T1读取的内容就是临时且无效的
不可重复读:对于两个事务T1、T2,T1读取了一个字段，然后T2更新了该字段，之后，T1再次读取同一字段,值就不同了
幻读:对于两个事务T1、T2，T1从一个表读取了一个字段,然后T2在该表中插入了新的行.之后,如果T1再次读取同一个表,就会多出几行

一个事务与其他事务隔离程度称之为隔离级别，数据库规定了多种隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱

隔离级别	描述
READ UNCOMMITTED(读未提交数据)	允许事务读取未被其他事务提交的变更,脏读,不可重复读和幻读的问题都会出现
READ COMMITED(读已提交数据)	只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读问题仍可能出现
REPEATABLE READ(可重复读)	确保事务可以多次从一个字段中读取相同的指，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但幻读问题仍然存在。
SERIALIZABLE(串行化)	确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作，所有并发问题可以得到避免，但性能十分低下。

Oracle支持两种事务隔离 READ COMMITED,SERIALIZABLE.Oracle默认的事务隔离级别为:READ COMMITED
Mysql支持4种事务隔离级别.Mysql的默认事务隔离级别为:REPEATABLE READ
查看默认事务级别 select @@tx_isolation
设置默认事务级别 set transaction isolation level 事务级别
设置数据库系统全局事务级别 set 数据库名 transaction isolation level 事务级别;

savepoint 节点名; 节点名设置，设置保存点
rollback to 节点名; 回滚到保存点

视图:
CREATE VIEW 视图名称:
AS
查询语句

视图的修改(如果存在就修改，如果不存在就创建):
CREATE OR REPLACE VIEW 视图名
AS
查询语句;

对视图可以添加、修改和删除:增删改查会触发原表
但包含以下关键字的sql语句不能更新：
分组函数、distinct、group by、having、union或者union all
常量视图
select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表

级联删除
ALTER TABLE 表名 ADD CONSTRAINT FOREIGN KEY(字段名#主表) REFERENCES 表名(字段名 #外键附表) ON DELETE CASCADE;
级联置空
ALTER TABLE 表名 ADD CONSTRAINT FOREIGN KEY(字段名#主表) REFERENCES 表名(字段名 #外键附表) ON DELETE SET NULL;

变量:
系统变量:
查看所有的系统变量:
show global|session variables; //全局|会话
查看满足条件的部分系统变量:
show global|【session】 variables like '%char%'; //查询含有char的系统变量
查看指定的某个系统变量的值:
select @@global|【session】 .系统变量名;
为某个系统变量赋值
方式一:set global|【session】 系统变量 = 值;
方式二:set @@global|【session】 .系统变量 = 值;