Mysql常用语句帅哥特供版

[TOC]

Mysql常用语句帅哥特供版

• 没错,这里的帅哥指的就是我自己

limit命令 查询

# limit命令 有两个参数, 第一个代表起始，从第几条开始。 第二个代表步长，取多少条。
# 但是部分封装的DB,LIMIT只支持一个参数,即步长.
SELECT * FROM `user` LIMIT 10,20

# 如:ZendDb的某个版就不支持LIMIT方法使用两个参数,但是提供了一个另一个参数,offset
# MySQL里用OFFSET的时候OFFSET的值就相当于LIMIT的第一个参数,即起始值，第几条开始。
SELECT * FROM `user` LIMIT 20 OFFSET 10

# 这里需要注意一点MySQL的顺序(不是说id)是从零开始不间断的。从第几条开始和取多少条，都是说着的顺序不是ID。

• mysql中utf8和utf8mb4区别

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

   二、内容描述

   那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等。

   三、问题根源

   最初的 UTF-8 格式使用一至六个字节，最大能编码 31 位字符。最新的 UTF-8 规范只使用一到四个字节，最大能编码21位，正好能够表示所有的 17个 Unicode 平面。

   utf8 是 Mysql 中的一种字符集，只支持最长三个字节的 UTF-8字符，也就是 Unicode 中的基本多文本平面。

   Mysql 中的 utf8 为什么只支持持最长三个字节的 UTF-8字符呢？我想了一下，可能是因为 Mysql 刚开始开发那会，Unicode 还没有辅助平面这一说呢。那时候，Unicode 委员会还做着 “65535 个字符足够全世界用了”的美梦。Mysql 中的字符串长度算的是字符数而非字节数，对于 CHAR 数据类型来说，需要为字符串保留足够的长。当使用 utf8 字符集时，需要保留的长度就是 utf8 最长字符长度乘以字符串长度，所以这里理所当然的限制了 utf8 最大长度为 3，比如 CHAR(100)  Mysql 会保留 300字节长度。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持，我想一个是为了向后兼容性的考虑，还有就是基本多文种平面之外的字符确实很少用到。

   要在 Mysql 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持(查看版本： select version();)。我觉得，为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8.  对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR  替代 CHAR。

show命令

MySQL中有很多的基本命令，show命令也是其中之一，在很多使用者中对show命令的使用还容易产生混淆，本文汇集了show命令的众多用法。

show tables或show tables from database_name; -- 显示当前数据库中所有表的名称
show databases; -- 显示mysql中所有数据库的名称
show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称
show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令
show index from table_name; -- 显示表的索引
show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量
show variables; -- 显示系统变量的名称和值
show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间
show privileges; -- 显示服务器所支持的不同权限
show create database database_name; -- 显示create database 语句是否能够创建指定的数据库
show create table table_name; -- 显示create database 语句是否能够创建指定的数据库
show engines; -- 显示安装以后可用的存储引擎和默认引擎。
show charset; -- 显示当前MySQL所有支持的字符集
show innodb status; -- 显示innoDB存储引擎的状态
show logs; -- 显示BDB存储引擎的日志
show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知
show errors; -- 只显示最后一个执行语句所产生的错误
show [storage] engines; --显示安装后的可用存储引擎和默认引擎
show procedure status --显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
show create procedure sp_name --显示某一个存储过程的详细信息
show profiles 查询SQL语句执行时所使用的资源使用show profiles for query ID可以查看详细信息,使用前需要设置SET profiling = 1;

注解

select ------>  from -----> where -------> group by -------> having
                ------> order by -------> limit   顺序不能颠倒。
\G作为结束符  将查询后的数据立起来。
charset='utf8' 是 character set='utf8'的简写
Mysql 字段类型设置默认值时, int类型不能设置为空,一般设置为0.
字段类型为字符串时,应该指定类型的长度.如: varchat(255);
一般在字段名和表明中都会使用反引号`,而值都会使用单引号或双引号
如:
UPDATE `sabi_prize` SET `email` = 'json@163.com' WHERE `id` = '1';

ON DUPLICATE KEY UPDATE
当我们在使用insert语句时,遇到数据不存在插入,存在则更新某些字段的时候.
如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。
可以在VALUES()后使用ON DUPLICATE KEY UPDATE 字段名=数据....来操作
即数据存在更新指定字段,不存在则插入.不需要再通过SELECT 查询来判断存在与否,然后在进行UPDATE,效率也会高很多.
需要注意的是, 该语句只有在命中唯一索引或主键索引的冲突时才会有效.
同时如果一句SQL中有多个索引冲突,
如: 
此处的UID为主键索引,name为唯一索引,当遇到冲突时会触发更新效果.
INSERT INTO user(`uid`,`name`,`mobile`,`update`) VALUES(20,'哇咔咔',15011017921,99999) ON DUPLICATE KEY UPDATE `update`=99999,name='哇咔咔',mobile=15011017921


使用一下语句:
SELECT min(price) pmin, max(price) pmax, sum(amount) amount, min(tid) idmin, max(tid) idmax
,concat(FROM_UNIXTIME(date,'%Y-%m-%d %H:%i:'),floor(FROM_UNIXTIME(date,'%s')/60)*60) dt
FROM
bt_kline_cache WHERE date>(unix_timestamp()-31104000) and flag='btctrade_btc_cny' GROUP BY dt ORDER BY idmin ASC

实例:
从MySQL外部进行查询,同时使用逻辑判断,查询存在的字段.
select 
uid as 用户uid,
name as 姓名,
if(email='',mobile,email) as 账户,	# 如果=空则查询mobile,否则查询email
(select cny_balance from user_finance where uid =用户uid) as 可用人民币,
(select cny_lock from user_finance where uid =用户uid) as 冻结人民币,
(select kgtc_balance from user_finance where uid =用户uid) as 可用积分,
(select kgtc_lock from user_finance where uid =用户uid) as 冻结积分,
(select sum(pay) from transfer_cny where uid=用户uid and opt_type='out' and status='成功') as 提现金额

#(select sum(pay) from  transfer_kgtc where uid =用户uid and opt_type='in' and status='成功') as 积分来源_充值
from user 

常用语句

常用语句:
# 从外部执行MySQL命令,从外部执行命令
echo "UPDATE mysql.user SET host='%' where user='root'; flush privileges;" | mysql -uroot -psss

# 查看当前连接Id
select connection_id();

# InnoDB 状态查看(可以通过这个来查看是否有死锁等);
SHOW ENGINE INNODB STATUS;

# sleep()可以使SQL暂停/休眠指定的秒数
SELECT sleep(10);	# SQL走到这里的时候休眠10秒
SLELECT sleep(10) FROM `user`; # USER表每输出一行,休眠10秒

# 查看当前系统时间(MySQL时间默认采用当前服务器的时区,即服务器所在的时区)
SELECT now();	# 查看当前时间格式
SELECT now(),id,name FROM `user`; # 查询表数据的时候,把当前时间格式一同输出
SELECT sysdate();	# 和now的用法都是一样的
SELECT current_date; # 用法和上面的两个一样,不同的时候该函数仅精确到日
show variables like "%time_zone%";  # 查看当前所属时区
set global time_zone = '+8:00';  ##修改mysql全局时区为北京时间，即我们所在的东8区
set time_zone = '+8:00';  ##修改当前会话时区
flush privileges;  #立即生效
通过修改my.cnf配置文件来修改时区
vim /etc/my.cnf  ##在[mysqld]区域中加上
default-time_zone = '+8:00'
##重启mysql使新时区生效
如果不方便重启mysql，又想临时解决时区问题，可以通过php或其他语言在初始化mysql时初始化mysql时区
这里，以php为例，在mysql_connect()下使用mysql_query(“SET time_zone = ‘+8:00′”)。

# 查看当前库的默认字符集
SHOW VARIABLES LIKE 'character_set_database';

# 查看所有字段详细信息 比desc要全的多,将整个字段都会显示出来,甚至包括Comment
SHOW FULL FIELDS FROM 表名
SHOW FULL COLUMNS FROM 表名
SHOW FIELDS FROM 表名 输出的结果和desc一样

# 将查询结果插入到数据表中:
SELECT 字段名1,字段名2 into 新表名 from 原表名
insert into 表名(字段名1,字段名2) select 字段名1,字段名2 from 表名 #一般只用于同表复制数据, 因为目标表不存在则报错.如果ID是自动的可能会冲突,这时候可以不查询和插入ID

# 表复制,复制表内所有的内容同时基于该表新建一个表
create table 新表名 (select * from 原表名);

# 只查询数据库字段名,类型, 是否为空, 默认值		
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
[AND table_schema = 'db_name']
[AND column_name LIKE 'wild']
# 查看建表/建库语句
show create database/table `库名`/`表名`; //MySQL5.7 下不加``会爆语法错误
# 查看表信息,包括用来查看表用的什么引擎.
show table status from 数据库库名 where name='表名',

# 查看MySQL正在跑的线程(检测MySQL语句的性能,在SQL运行完毕之后,使用该语句.用以查看该SQL的性能)
set profiling=1;		开启检测功能
show processlist;		查看上一句运行的SQL的性能

# 去重
distinct 注意,只能放在select紧挨着后面, 否则报错.
有时候,也不可以不再select后面, 如下面所示.
SELECT
  count(DISTINCT buy_uid),
  count(buy_uid)                 AS             buyUser,
  sum(buy_fee * price)           AS             buy_fee,
  sum(sell_fee)                  AS             sell_fee,
  FROM_UNIXTIME(created, '%Y%m') AS             month
FROM 
	order_bts
GROUP BY month
ORDER BY month DESC


# 查询该表所有字段名(只有字段名)
select COLUMN_NAME from information_schema.COLUMNS where table_name = '表名';


查看版本号:
外部: MySQL -v;
内部: status;
连接MySQL:
mysql -h IP地址 -P 端口 -u 用户名 -p密码
 -u 用户名 root
     	-p 密码
        -h 主机名 localhost
        -P 端口号 3306
        -b 关闭beep 蜂鸣器。

创建表:
DROP TABLE IF EXISTS `表名`;	# 如果存在即删除
create table  [if not exists] 表名(列的信息);
# 复制表,复制整张表到一个新表
CREATE TABLE 新表 SELECT * FROM 旧表  #原表的主键、外键、约束、触发 器、索引都不会被复制过来

查看表结构:
    desc 表名;

查看建表语句:
show create table  表名;

删除表:
    drop table 表名

建表语句格式：
	  create table 表名(
	   字段名 类型 [字段约束] [COMMENT '注释'],
	   字段名 类型 [字段约束] [COMMENT '注释'],
	   字段名 类型 [字段约束] [COMMENT '注释'],
	   ...
	  )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表注释';
查询:
select 字段名 from 表名;

插入:
 插入单条数据
insert into 表名 set 字段名1=值1,字段名2=值2 ......
插入多条数据
insert into 表名(字段名1,字段名2....) values (值1,值2.....);

更新数据:
update 表名 set 字段名1=新值1,字段名2=新值2  where 条件
UPDATE `user_finance` SET btc_balance = btc_balance + 1 WHERE id = 1 
UPDATE `alerts_newsflash` SET `support`=`support`+1,`update`=1528362262 WHERE id=1
 
删除数据:
    delete from 表名 where 条件

删除整个表:
truncate table 表名	#快速删除,查看库得知该语句是删除库之后又重建.

--------------------------------------------------------------------------------

创建库:
DROP DATABASE IF EXISTS `库名`;	# 如果存在即删除
create database [if not exists] 数据库名;
创建Utf8数据库utf8_general_ci;
   	CREATE DATABASE `数据库名称` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# 查看当前库的默认字符集
SHOW VARIABLES LIKE 'character_set_database';


建库语句:
create database 数据库名 default character set utf8 collate utf8_general_ci; # 建库的同时指定字符集

删除数据库:
drop database 数据库名	

查看当前所有数据库:
show databases;

选择数据库:
use 数据库名

查看数据库中的表:
show tables;	#该操作必须建立在use 数据库名 的基础上;

查看当前所在库
select database();





--------------------------------------------------------------------------------
操作符:

SQL标准在处理OR操作符之前优先处理AND操作符你可以加括号来进行明确的分组
比较运算符
                <
                <=
                >
                >=
                =
                !=
逻辑运算符
                and 逻辑与
                or 逻辑或
                not 逻辑非
注意：and和or可以多次使用。

in操作符：格式：in(值1，值2，值3);指定条件范围，范围中的每个条件都可以匹配。in里面由逗号将值的清单分隔在括号中        
between操作符，在两个指定的值之间（检查范围）
not操作符，否定后面的操作。可以用于in和between
like，not like两个操作符：需要包含通配符的字符串
like的使用格式：like '字符串';
                      not like '字符串'
通配符：用来匹配值的一部分的特殊字符。
                        %：表示任何字符出现任意次。可以放在任意位置。
                        %值%：包含任意值
                        %值:以值结尾。
                        值%:以值开头。

UNION				合并1个以上sql的结果集(默认不重复)
UNION ALL			合并1个以上sql的结果集(默认所有)
注意:
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。两者差别:	 UNION用的比较多union all是直接连接，取到得是所有值，记录可能有重复   union 是取唯一值，记录没有重复,唯一索引和直接用代码做唯一相比,唯一索引可以抑制并发.




--------------------------------------------------------------------------------
约束条件:
表的字段约束：
KEY索引，增加查询速度
		unsigned 无符号(正数)
		zerofill 前导零填充
		auto_increment  自增
		default	默认值
		not null  非空
		PRIMARY KEY 主键 （非null并不重复）
		unique 唯一性   （可以为null但不重复）
		index 常规索引
		设置typeid为type表中的id的外键///typeid int references type(id),

公用的约束条件：
null、not null：默认的时候为null。指定了not null必须在插入数据的时候在此字段插入值如果不插这个字段没有数据。
default 值：在不插入该字段的时候默认的插入值。
  整型约束条件:
        1、zerofill，表示0填充。一般和整型后面的设定的宽度一起使用。不够指定的位数的时候使用0进行填充。
2、unsigned 无符号，默认的时候是有符号的，如果要无符号必须使用unsigned
3、auto_increment，自增在产生唯一标识或顺序值的时可以使用该属性。这个属性只能用于整型。该字段中先定义primary key 或unique 这两个东西。

索引操作

注意:不能命中索引的部分

如果不太确定能不能命中索引的话,可以使用explain来看一下要运行的SQL语句

• 前导模糊查询不能利用索引(like '%XX'或者like '%XX%')
• 条件中有or，即使其中有条件带索引也不会使用,要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
• 对于多列索引，不是使用的第一部分，则不会使用索引
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
• where语句中使用<>和!=在非主键索引和非唯一索引时,MySQL的<>也是不等的意思.
• where语句中对字段表达式操作,如: amount*2=1000
• where语句中使用not in, not exist
• B-tree 索引 is null 不会走索引， is not null 会走索引

常规索引

索引:
查看索引
查看表索引:
show index from 表名
show keys from 表名

删除索引:
DROP INDEX 索引名 ON 表名
ALTER table 表名 CHANGE `字段名`  `字段名` 字段类型
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KEY
因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。
如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引

创建索引:
ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

联合索引

联合索引:
一个索引可以对应多个列,叫做联合索引,创建联合索引时在后面的括号中填写多个列即可.
符合索引遵守,最左前缀原则

CREATE INDEX index_name ON table_name (column_list,column_list2,column_list3);

查询时使用联合索引的一个字段，如果这个字段在联合索引中所有字段的第一个，那就会用到索引，否则就无法使用到索引。
例如联合索引 IDX(字段A,字段B,字段C,字段D)，当仅使用字段A查询时，索引IDX就会使用到；
如果仅使用字段B或字段C或字段D查询，则索引IDX都不会用到。  
这个规则在oracle和mysql数据库中均成立。
两个或更多个列上的索引被称作复合索引。
利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

使用联合索引应该注意：
符合索引遵守,最左前缀原则
即MySQL使用联合索引只能使用左侧的部分
例如INDEX(a,b,c)，当条件为a或a,b或a,b,c时都可以使用索引，但是当条件为b,c时将不会使用索引。
这好比一本先根据姓，再根据名进行排序的电话簿，当查找的时候有姓的条件，效率会比没有任何条件高；
如果在姓的基础上还有名的条件，效率会更高；
但若只有名的条件，电话簿将不起作用。

离散度更高的索引应该放在联合索引的前面，因为离散度高索引的可选择性高。考虑一种极端的情况，数据表中有100条记录，若INDEX(a,b)中a只有两种情况，而b有100种情况。这样对于查询唯一记录a = …，b = …时，先遍历全部索引看满足a条件的有50个索引节点，接下来还要再一个个遍历这50个索引节点。如果是INDEX(b,a)，先遍历全部索引发现满足b条件的索引节点只有一个，再遍历这个节点发现也满足a条件。虽然最后都能找到那个唯一的索引节点，但是第二种索引顺序对引擎遍历索引效率有很大的提高（用电话薄的思想去思考问题）。

创建索引

1、常规索引：最基本的索引，没有任何限制。
创建索引的三种方式：
     建表时创建:
create table 表名(
建表语句.......,
 index 索引名 (列名)
);
直接创建:
 	create index 索引名 on 表名(列名);
修改表结构的形式创建:
ALTER TABLE 表名 ADD INDEX 索引名 (列名);

 2、唯一索引unique，于普通索引相似，但是索引列的值必须唯一。
 唯一索引和直接用代码做唯一相比,唯一索引可以抑制并发.
    创建索引：
        create table 表名(
        .........,unique 索引名(列名)
        )
创建唯一索引
    create unique index 索引名 on 表名(列名)

添加唯一索引
    alter table `表明` add unique index `索引名`(`字段名1`,`字段名2`......);
如:
ALTER TABLE `sabi_round` ADD UNIQUE INDEX `round_date` (`round`, `date`);
同时将多个列指定多个列时,必须同时两列同时重复,才会触发该条件.

  3、主键索引primary key,于唯一索引类似，且一个表中只能有一列作为主键。
创建索引：
    	create table 表名(
   	 		 字段名1 类型 约束条件 primary key,
   			 字段名2....
    	);

索引须知:
1.索引不会包含有Null列的值
2.使用短索引
3.索引列排序,Mysql查询只使用一个索引, 如果where中已经有一个索引order中的索引不会被使用.
4.like语句操作, like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上做运算,将导致索引失效而进行全表扫描;以下操作符才使用索引：<,<=,=,>,>=,between,in,以及like不以通配符%或_开头的情形。

注意：
1.因为auto_increment 中必须有一个primary key或unique如果先将primary key或unique删除掉的话，那么auto_increment将不允许。所以应该先删除auto_increment;
2.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

修改表结构

修改表：

语法：alter table 表名 action	

修改字段名和字段类型以及约束:
如:  alter table `表名` change 旧字段名 新字段名 字段类型 字段约束
alter table `表名` modify 字段名 字段类型 字段约束
modify与change用法相同    区别：change可以改变字段名，modify不可以。

例子:	
alter table t5 change age ages tinyint not null default'18';
ALTER TABLE `user` MODIFY `mobile` char(16) NOT NULL DEFAULT '' COMMENT '手机';

插入字段:
alter table 表名 add 字段名 字段类型 约束条件。
删除字段:
alter table 表名 drop 字段名
修改表名:
alter table 旧表名 rename as 新表名

设置密码

设置密码:
    方法1：在数据库的外面
        mysqladmin -u 用户 -p password 新密码
        C:\Users\lgzhyan>mysqladmin -u root -p password 1234@abcd

    方法2：在数据库的里面
        set password for '用户名'@'登录主机'=password('新密码');

表引擎

查看表引擎:
show engines;
查看表默认引擎
show variables like 'default_storage_engine';
创建引擎
create table 表名(列)engined=存储引擎

字符集

字符集:
查看使用的字符集
SHOW VARIABLES LIKE 'collation_%';

查看系统中可用的字符集:
show character set;

设置数据库字符集:
create database 数据库名 default character set utf8;

修改字符集:
alter database或者table  库名 character set utf-8;

查看建库语句:
show create database 数据库名.

设置表的字符集
create table t11(
            -> id int,
            -> name char(32)
            -> )engine=myisam default charset=utf8;

更改已存在的表的字符集。
        alter table 表名 default character set utf8;

    注意：
            1、他们之间的关系，如果没有设置本级，就使用上级作为默认的字符集，如果设置了本级的字符集那么就直接使用了。编码优先级 字段>表>数据库

---

正则

正则:
REGEXP 使用正则匹配(一般用于where条件之后)

例子: 
SELECT sum(gname) FROM rotate_log WHERE rid=17 and gname REGEXP '暗网币'
select coin,namelang from coin where namelang REGEXP '[A-Z]+'
select coin,name from region_coin where name REGEXP '[^A-Z]+'

---

剪切字符串

剪切字符串:
left(str, length) 					从左开始截取字符串	
right(str, length)					从右开始截取字符串
substring(str, pos,[length])			截取字符串
substring_index(str,delim,count)	按关键字截取字符串

例：
select right(content,200) as abstract from my_content_t
select substring(content,5) as abstract from my_content_t
select substring(content,5,200) as abstract from my_content_t
select substring_index(”blog.chinabyte.com”，”。”，2) as abstract from my_content_t

---

时间处理

时间处理:
(MySQL时间默认采用当前服务器的时区,即服务器所在的时区)
now();					获取当前时间(执行开始时)
sysdate();				获取当前时间(在函数执行时,动态的)不常用
current_timestamp();		获取当前时间格式的时间(括号有没有都行);
str_to_date(str,format);		将杂乱的时间格式转换为固定年月日顺序(format:原有顺序的格式);
dateidff();				日期时间格式相减获得天数(两个参数类型必须一致);



DATE_FORMAT();			将时间格式转换为时间戳('%Y%m%d%H%i%s'作为第二个参数是必须这样的);
FROM_UNIXTIME();		将Unix时间戳转换为时间格式('%Y%m%d%H%i%s'作为第二个参数是必须这样的);
UNIX_TIMESTAMP();		获取当前Unix时间戳(如果括号中输入日期时间,也可以将指定日期转换为时间戳).
昨天零时时间戳： UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY)
今天零时时间戳：UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE))

---

逻辑处理

逻辑处理

使用case ... when.... then... else ... end来做查询字段时的判断.
语法结构:
语法形式1：
    CASE expression
            WHEN expression_11 THEN expression_12
             …
            WHEN expression_n1 THEN expression_n2
            [ELSE expression_m]
            END

语法形式2：
     CASE WHEN condition_expression_1 THEN expression_1
             		 …
           	    WHEN condition_expression_n THEN expression_n
                    [ELSE expression_m]
                    END
例子:
SELECT uid, CASE `status` WHEN 0 THEN '正常' ELSE '异常' END as 状态判断 FROM user_info

---

外键约束

外键约束:
只有InnoDB类型才支持外键约束.
如果一个表A(从表)中的某个字段,指向另一个表B(主表)中的一个主键.表A中的这个字段被称为,外键. 表B的字段为主键.
机制:
外键关系的两个表的列必须是数据类型相似.比如int和tinyint可以.如果主键是int外键是char则不行  
一定要匹配主表中 引用的列（所要创建的外键是主表中的主键）  
主键和外键的字符编码必须一致，如果主表为gbk，则此表也要为gbk 
插入数据时, 如果主表没有,从表不能插入.
删除表时,先从后主.


作用:
用于约束处于关系的表,增加表记录时,是否有与之对应的父表记录,没有则,从表不能插入.(外键十分影响MySQL的性能)


	创建外键语法：

	[CONSTRAINT [symbol]] FOREIGN KEY
	[index_name] (index_col_name, ...)
	REFERENCES tbl_name (index_col_name,...)
	[ON DELETE reference_option]
	[ON UPDATE reference_option]

 用法:
constraint FK_子表的外键 foreign key(本表外键) references 父表名称(父表外键)  #通过外键创建链接(FK_可以不写,应该是外键的简称吧)
注意:
本表外键,int类型需要加上unsigned(无符号), 
假设外键类型是int而引用的父表主键类型也是 int，如果你的父表主键设置auto_increment（那么你的字段类型实际上是unsigned_int）,这时候需要在子表中外键设置unsigned(无符号). 否则报错:Cannot add
该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。

 
详解:

reference_option:
	RESTRICT | CASCADE | SET NULL | NO ACTION


如果子表试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT:

  1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。

  2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。

  3. NO ACTION: InnoDB拒绝删除或者更新父表。

  4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

  5. SET DEFAULT: InnoDB目前不支持。

外键约束使用最多的两种情况：

  1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；

  2）父表更新时子表也更新，父表删除时子表匹配的项也删除。

  前一种情况，在外键定义中，我们使用ON UPDATE CASCADE ON DELETE RESTRICT；
  后一种情况，可以使用ON UPDATE CASCADE ON DELETE CASCADE。


例子:
 	
创建外键约束①：

		ALTER TABLE `article`
		ADD CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`);

创建外键约束②:
ALTER TABLE `article` ADD CONSTRAINT `fk_1` FOREIGN KEY ( `category_id` )
		REFERENCES `category` ( `id` )
		ON DELETE CASCADE ON UPDATE CASCADE

创建外键约束③:()建表时使用
CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`);

删除外键:
ALTER TABLE article DROP FOREIGN KEY fk_1;


例子:
CREATE TABLE `dage` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(12) NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `xiaodi` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) UNSIGNED NOT NULL,
  `name` char(12) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  constraint uid foreign key(uid) references dage(uid)  #通过外键创建链接
) ;

---

子查询

注意:

尽量避免子查询，而用join 往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询。如下：

# 不推荐
insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id); 

# 推荐
insert into t1(a1)  
select b1 from t2  
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   
where t1.id is null;  

子查询详解

子查询

子查询:
SELECT * FROM types AS t1 WHERE t1.pid = (SELECT t2.id FROM types AS t2 WHERE t2.name = "影片");
SELECT *,(SELECT count(*) FROM types AS t1 WHERE t1.pid = types.id) AS counts, concat(path, '-', id) AS npath FROM types ORDER BY npath;
SELECT name, (SELECT count(*) FROM types AS t2 WHERE t2.pid = t1.id) FROM types AS t1 WHERE t1.pid = 0;

子查询的前置查询的果作为where条件
有时候子查询时我们在使用前置子查询时,想把查询的结果作为where条件时可以使用下面这种方式:

将所有需要查询的结果使用()包起来作为一个派生表(主要派生表必须要有别名),然后使用select*将所有的结果全部查询出来.
select *
from (select
        `uid`   as `finance_uid`,
        `coin`  as `finance_coin`,
        `lock`  as `finance_lock`,
        (select sum(amount_left) from trade where uid = finance_uid and coin = finance_coin and side = 2 and status = 1) as `trade_table_sum`,
        (select sum(amount) from `order` where sell_uid = finance_uid and coin = finance_coin and status < 3) as `order_table_sum` 
        from finance
     ) as tmp
where `finance_lock` != (`trade_table_sum` + `order_table_sum`)

---

连表查询

注意：

mysql不支持FULL JOIN,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.全连接产生的所有记录（双方匹配记录）在表A和表B。如果没有匹配,则对面将包含null。

在MySQL中的FULL JOIN 其实质上就是inner join和cross join.

在MySQL中CROSS和INNER JOIN被划分在一起

FULL JOIN和inner join和cross join在不指定on的时候,一定会出现笛卡尔积.

left join在任何场景下都不会比inner join的执行效率高 因为left join除了需要所有inner join的结果集以外还需要左表的所有没有关联上的数据,但是如果有一个表特别小的做,一般我会将做个表作为left 然后使用left join

left join除了要求关联字段有索引以外，最好将小表作为左表，因为检索的循环次数更少，前提是你的业务逻辑没问题，因为不同的写法逻辑是不一样的

inner join会自动选择合适的表作为基础表，也仍然要求有关联字段索引，并且最好是int型检索效率更高

内连接

实际上，在 MySQL 中（仅限于 MySQL） CROSS JOIN 与 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。
inner join和cross join在不指定on的时候,一定会出现笛卡尔积.

内连接:
内连接，也叫等值连接，inner join产生同时符合A和B的一组数据。
select * from A inner join B on A.name = B.name;

定义：仅将两个表中满足连接条件的行组合起来作为结果集. 在内连接中，只有在两个表中匹配的行才能在结果集中出现 .
关键词：INNER JOIN  INNER是默认方式，可以省略
格式：SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON或WHERE 条件表达式 
实例: SELECT * FROM USER INNER JOIN user_finance ON user.uid = user_finance.uid WHERE user.uid=2

左连接

左连接:
左（外）连接 

left join,（或left outer join:在Mysql中两者等价，推荐使用left join.）左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。
也就是说A表将全部展示,而B表只显示匹配的内容.
select * from A left join B on A.name = B.name;

定义：在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL 
关键字：LEFT JOIN
实例: SELECT * FROM user i LEFT OUTER JOIN user_finance t ON i.uid = t.uid 其中outer可以省略。
注意:  当在内连接查询中加入条件是，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况就不同了。当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。如果将条件放到where子句 中，SQL Server将会首先进行连接操作，然后使用where子句对连接后的行进行筛选

右连接

右连接: 
定义：在内连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NULL 
select * from A right join B on A.name = B.name;
关键字：RIGHT JOIN 

交叉连接

cross join：交叉连接，得到的结果是两个表的乘积，即笛卡尔积

把表A和表B的数据进行一个N*M的组合，即笛卡尔积。如本例会产生4*4=16条记录，在开发过程中我们肯定是要过滤数据，所以这种很少用。

补充一点：按照sql标准CROSS JOIN是笛卡尔积。但对于mysql来说，CROSS JOIN 相当于 INNER JOIN。

实际上，在 MySQL 中（仅限于 MySQL） CROSS JOIN 与 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。
INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字，因此下面的 SQL 效果是一样的：

... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2


案例:

对比下面的SQL语句,前两天条的结果是一样的.
inner join和cross join在不指定on的时候,一定会出现笛卡尔积.

select * from A cross join B;
select * from A inner join B; 
select * from A cross join B on A.name = B.name; 

全连接Full join

# 全连接产生的所有记录（双方匹配记录）在表A和表B。如果没有匹配,则对面将包含null。

select * from A left join B on B.name = A.name union select * from A right join B on B.name = A.name;

自身连接

自身连接:
定义: 自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据。例如人力资源数据库中雇员与老板的关系。
全连接产生的所有记录（双方匹配记录）在表A和表B。如果没有匹配,则对面将包含null。


例子:	
SELECT
  i.id       AS i_id,
  i.UID      AS i_uid,
  i.opt_type AS i_type,
  i.status   AS i_status,
  o.id       AS o_id,
  o.UID      AS o_uid,
  o.opt_type AS o_type,
  o.status   AS o_status
FROM transfer_btc AS i
  JOIN transfer_btc AS o
    ON i.UID = o.UID 

select
  a.uid,
  ceil(a.cp),
  ceil(a.nu),
  u.email,
  u.name,
  u.mobile
from (
  (
    select c.uid,c.cp,t.nu FROM (select uid,max(coin_pay) as cp from c2c_order WHERE type = 0 AND status = 2 AND created > 1532275200 AND created < 1532880000 group by uid) c
    left join
    (select uid,max(number) as nu FROM transfer_usdt WHERE opt_type = 'in' AND status = 6 AND created > 1532275200 AND created < 1532880000 group by uid) t on c.uid = t.uid
  )
union
  (
    select t.uid,c.cp,t.nu FROM (select uid,max(coin_pay) as cp from c2c_order WHERE type = 0 AND status = 2 AND created > 1532275200 AND created < 1532880000 group by uid) c
    right join
    (select uid,max(number) as nu FROM transfer_usdt WHERE opt_type = 'in' AND status = 6 AND created > 1532275200 AND created < 1532880000 group by uid) t on c.uid = t.uid
  )
) a
join service_ucenter.user as u on a.uid = u.uid

连表查询性能优化

1.显式inner join 和 隐式inner join

显式inner join 和 隐式inner join

如：

    select * from
    table a inner join table b
    on a.id = b.id;
和
    select a.*, b.*
    from table a, table b
    where a.id = b.id;

实操案例:
    select * from user inner join user_info on user.id=user_info.uid;
    select `user`.*,`user_info`.* from `user`,`user_info` where  `user`.`id`=`user_info`.`uid`;
    如果要取所有也可以这么写:
    select user.*,user_info.* from user,user_info where  user.id=user_info.uid;

我在数据库中比较(1068870条数据)得之，它们用时几乎相同，第一个是显示的inner join，后一个是隐式的inner join。个人认为隐式的通过where条件做的还快个1-2秒.

2.left join/right join 和 inner join

尽量用inner join.避免 LEFT JOIN 和 NULL.

left join在任何场景下都不会比inner join的执行效率高 因为left join除了需要所有inner join的结果集以外还需要左表的所有没有关联上的数据,但是如果有一个表特别小的做,一般我会将做个表作为left 然后使用left join

left join除了要求关联字段有索引以外，最好将小表作为左表，因为检索的循环次数更少，前提是你的业务逻辑没问题，因为不同的写法逻辑是不一样的

inner join会自动选择合适的表作为基础表，也仍然要求有关联字段索引，并且最好是int型检索效率更高

在使用left join（或right join）时，应该清楚的知道以下几点：

• (1). on与 where的执行顺序

1. ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。
2. 如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。
3. 仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。如：

不推荐

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;

推荐

select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1

从上面例子可以看出，尽可能满足ON的条件，而少用Where的条件。从执行性能来看第二个显然更加省时。

• (2).注意ON 子句和 WHERE 子句的不同

on语句

SELECT *
FROM user
  LEFT JOIN user_info
    ON (user.id = user_info.id)
       AND user_info.id = 2;

列出了user表所有的数据,user_info表这边只列出了一条符合要求的数据,剩下的全部补null.

where语句

SELECT *
FROM user
  LEFT JOIN user_info
    ON (user.id = user_info.id)
WHERE user_info.id = 2;

只列出了一条符合要求的数据.

从上可知，第一条查询使用 ON 条件决定了从 LEFT JOIN的 user_info表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

• (3).尽量避免子查询，而用join

往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询。如下：

不推荐

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id); 

推荐

insert into t1(a1)  
select b1 from t2  
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   
where t1.id is null; 

• (4).MySQL STRAIGHT_JOIN 与 NATURAL JOIN的使用

注意:

用explain进行分析，发现执行顺序为Table1->Table2，这时就由Table1来作为驱动表了，Table1中相应的索引也就用上了，执行时间竟然低于1s了。

• STRAIGHT_JOIN只适用于inner join，并不使用与left join，right join。（因为left join，right join已经代表指定了表的执行顺序）
• 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用STRAIGHT_JOIN一定要慎重，因为啊部分情况下认为指定的执行顺序并不一定会比优化引擎要靠谱。

straight_join 完全等同于inner join 只不过，join语法是根据“哪个表的结果集小，就以哪个表为驱动表”来决定谁先载入的，而 straight_join 会强制选择其左边的表先载入。

往往我们在分析mysql处理性能时，如(Explain),如果发现mysql在载入顺序不合理的情况下，可以使用这个语句，但往往mysql能够自动的分析并处理好。

straight_join 实现强制多表的载入顺序，从左到右，如：...A straight_join B on A.name = B.name

sql案例

select t1.*
from t1
inner join t2
on t1.CommonID = t2.CommonID

对sql进行改造：

select t1.*
from t1
STRAIGHT_JOIN t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

---

Mysql慢查询日志:

# 查看慢查询日志是否开启  通过slow_query_log 是否是off判断
show variables like '%quer%';

#  通过配置文件的方式进行设置  # 设置慢查询后 MySQL重启报错 Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
# /etc/my.cnf 里面可以设置上面MYSQL全局变量的初始值。 
long_query_time = 1 				# 超过多少秒记录
log_slow_queries = /tmp/slow.log 	# 记录保存位置 此处很多地方用slow_query_log_file 如果前者报错可以添加后者
# 两者区别  未验证
log_slow_queries    ：  指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留) 
slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log 


# 通过命令行方式进行设置 按照先开启日志在设置时间的顺序, 下次打开窗口即生效
mysql> set global slow_query_log='ON';      # 启用慢查询, 加上global，不然会报错的。
mysql> set global long_query_time=2;	#设置成2秒，加上global,下次进mysql已然生效

# 将MySQL慢查询日志写入表,随后在mysql.slow_log表中可以看得到
set global log_output='TABLE';

# 通过一下方式测试慢查询
SELECT SLEEP(10);
# 查看慢查询一共执行了几次
show global status like '%slow%';

# 查看刚才慢查询的日志
select * from mysql.slow_log;

慢查询日志格式:
聚合方式, 按照出现频率, 每次多久,排序,去重.

--------------------------------------------------------------------------------
优化相关:

查看sql性能:
查看语句的执行情况,通过使用explain或者desc一条sql语句获得.
如:
explain  select * from user;
desc select * from user;

例子:
mysql> desc select * from user\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

查询结果的列说明:
 select_type： 表示SELECT的类型，常见的有下面几种
        	SIMPLE： 简单表，不使用连接或子查询的
        PRIMARY： 主查询，即外层的查询
        	UNION： UNION中的第二个或者后面的查询语句
        SUBQUERY： 子查询中的第一个SELECT

table:	输出结果集的表

type:	表示表的连接类型，性能由好到差的连接类型为下面顺序
        system： 表中只有一行，即常量表
        const： 单表中最多有一个匹配行，如primary key或unique index
        eq_ref： 对于前面的每一行，在此表中只查询一条记录，也就是多表连接中使用primary key或unique index
        ref： 与eq_ref类似，区别在于不是使用primary key或unique index，而是使用普通索引
        ref_or_null： 与ref类型，区别在于条件中包含对null的查询
        index_merge： 索引合并优化
        unique_subquery： in的后面是一个查询主键字段的子查询
        index_subquery： 与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询  www.2cto.com  
        range： 单表中的范围查询
        index： 对于前面的每一行，都通过查询索引来得到数据
        all： 对于前面的每一行，都通过扫描全表来得到数据

possible_keys:	查询时可能用到的key

key:	查询时实际用到的key

key-len:	索引字段的长度

rows:	扫描行数量

Extra:	执行情况的说明和描述


MySQL临时表的应用:




--------------------------------------------------------------------------------

MySQL编码:
查看当前数据库编码的SQL语句为：
show variables like 'character_set_database';

下面，我们来修改xxx数据库的编码，把它修改为gb2312
alter database xxx CHARACTER SET gb2312;


这里同样做了两件事情：
1、使用SQL语句：alter database xxx CHARACTER SET gb2312; 把xxx数据库的编码设置为了gb2312.
2、再次使用show variables like 'character_set_database'; 来确认一下当前xxx是什么编码。经过确认，数据库编码已经修改为gb2312了。
3、当然很多情况下都是修改为utf-8编码

--------------------------------------------------------------------------------
Mysql临时表

在MySQL中，临时表是一种特殊类型的表，它允许您存储一个临时结果集，可以在单个会话中多次重用。

MySQL临时表具有以下特殊功能：
  ● 使用CREATE TEMPORARY TABLE语句创建临时表。请注意，在CREATE和TABLE关键字之间添加TEMPORARY关键字。
  ● 当会话结束或连接终止时，MySQL会自动删除临时表。当您不再使用临时表时，也可以使用DROP TABLE语句来显式删除临时表。
  ● 一个临时表只能由创建它的客户机访问。不同的客户端可以创建具有相同名称的临时表，而不会导致错误，因为只有创建临时表的客户端才能看到它。 但是，在同一个会话中，两个临时表不能共享相同的名称。
  ● 临时表可以与数据库中的普通表具有相同的名称。 例如，如果在示例数据库(yiibaidb)中创建一个名为employees的临时表，则现有的employees表将变得无法访问。 对employees表发出的每个查询现在都是指employees临时表。 当删除您临时表时，永久employees表可以再次访问。

注意：
即使临时表可以与永久表具有相同的名称，但不推荐。 因为这可能会导致混乱，并可能导致意外的数据丢失。例如，如果与数据库服务器的连接丢失，并且您自动重新连接到服务器，则不能区分临时表和永久性表。 然后，你又发出一个DROP TABLE语句，这个时候删除的可能是永久表而不是临时表，这种结果是不可预料的。

创建MySQL临时表
要创建临时表，只需要将TEMPORARY关键字添加到CREATE TABLE语句的中间。
例如，以下语句创建一个临时表，按照收入存储前10名客户：
CREATE TEMPORARY TABLE top10customers
SELECT p.customerNumber, 
       c.customerName, 
       FORMAT(SUM(p.amount),2) total
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY total DESC
LIMIT 10;


现在，可以从top10customers临时表中查询数据，例如：
SELECT * FROM top10customers;



删除MySQL临时表
您可以使用DROP TABLE语句来删除临时表，但最好添加TEMPORARY关键字如下：
DROP TEMPORARY TABLE table_name;


DROP TEMPORARY TABLE语句仅删除临时表，而不是永久表。 当将临时表命名为永久表的名称相同时，它可以避免删除永久表的错误
例如，要删除top10customers临时表，请使用以下语句：
DROP TEMPORARY TABLE top10customers;


请注意，如果尝试使用DROP TEMPORARY TABLE语句删除永久表，则会收到一条错误消息，指出您尝试删除的表是未知的。
如果开发使用连接池或持久连接的应用程序，则不能保证临时表在应用程序终止时自动删除。
因为应用程序使用的数据库连接可能仍然打开并放置在其他客户端使用的连接池中。 因此，当不再使用它们时马上删除临时表，这是一个很好的做法。

数据导入导出

Mysql数据库备份与恢复:

    小数据量可以使用mysqldump命令进行导入导出，这种方式简单便捷。
    数据量较大，且有足够的迁移耐心时，可以选择自己写脚本，选择合适的并行方案迁移数据，这种方式编码成本较高。
    数据量较大，且希望能在短时间内完成数据迁移时，可以通过mysql导入导出文件的方式来迁移，这种方式效率较高。


导入MySQL数据库:
    方法1：退出MySQL的情况下使用(只支持SQL文件导入)
        mysql -u 用户名 -p 数据库名 < 数据库文件

    方法2：在进入的情况下使用，并且已经选择了数据库的情况下。(只支持SQL文件导入)
        source 数据库文件
    

MySQLdump 数据导出SQL文件:
    (退出MySQL时进行操作)
    注意:
        1. MySQLdump导表或单一库默认导出格式有 DROP TABLE IF EXISTS 表名.
        2. MySQLdump导全库默认CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test_com` /*!40100 DEFAULT CHARACTER SET utf8 */;
        
    mysqldump -u 用户名 -p 数据库名 [表名] > 导出的文件名     导出指定库或指定表的数据内容和表结构,不仅有insert还有create table语句。
    
    mysqldump -u 用户名 -p -d 数据库名 [表名] > 导出的文件名   导出数据库中表的结构
    
    mysqldump -u 用户名 -p -t 数据库名 [表名] > 导出的文件名   仅导出数据库中表的数据,而不添加CREATE TABLE语句的表结构,而只有insert语句.
    
    mysqldump -u root -p -A > 路径	导出所有库的数据
    
    mysqldump其他参数:
    --add-drop-database
    每个数据库创建之前添加drop数据库语句。
    
    --add-drop-table
    每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
    mysqldump  -uroot -p --all-databases  (默认添加drop语句)
    mysqldump  -uroot -p --all-databases –skip-add-drop-table  (取消drop语句)
    
    --add-locks
    在每个表导出之前增加LOCK TABLES并且之后UNLOCK  TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
    mysqldump  -uroot -p --all-databases  (默认添加LOCK语句)
    mysqldump  -uroot -p --all-databases –skip-add-locks   (取消LOCK语句)
    
    --comments
    附加注释信息。默认为打开，可以用--skip-comments取消
    mysqldump  -uroot -p --all-databases  (默认记录注释)
    mysqldump  -uroot -p --all-databases --skip-comments   (取消注释)
    
    --force
    在导出过程中忽略出现的SQL错误。
    
    --ignore-table
    不导出指定表。
    指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
    mysqldump  -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
    
    --default-character-set
    设置默认字符集，默认值为utf8
    mysqldump  -uroot -p --all-databases --default-character-set=latin1

本次优化我感觉最大最明显的变化是，去除索引后，导入速度极快，索引，重要的事情再说一遍：

由于项目要求三个字段都要有索引，所以我在建表的时候就加了索引，导致耗时遥遥无期；

导入时候可以先去掉索引，导入完之后再添加

索引是要占空间的，如果导入三个字段都要加索引，代表了我要每个字段都写入索引一次，耗时比不加索引多了几倍；

优化方法：
导入前把表的索引去掉留自增id一个，等导入完之后再添加

用MySQL自带导出/导入优点是速度极快，缺点是：只能导出文件是在服务器主机所在的本机地址，对于bi之类拿到不数据库主机权限的同事这个方式可能奢望了。不过好在对于字段/内容较少的报表第三方客户端工具导出速度也不算特别慢；


通过into outfile导出数据
    select * from `表名` into outfile '保存路径' ;
    通过命令选项将数据导出为指定格式以csv为例:
        这个SQL就是通过手动设置格式达到CSV文件的目的
        SELECT * FROM `user`            --可以加where条件 
        INTO OUTFILE '/var/lib/mysql-files/1test_com.user.csv'   --导出文件位置
        FIELDS TERMINATED BY ','        --用于设定字段的分隔符, 如:A,B
        OPTIONALLY ENCLOSED BY '"'      --用于设定字段由什么符号包裹,如:"A","B"
        LINES TERMINATED BY '\n';     --用于设定每条记录分隔符,每条数据换行区分.
        
通过LOAD DATA INFILE导入数据
    注意：如果导出时用到了FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'语句，那么LODA时也要加上同样的分隔限制语句。还要注意编码问题。
    LOAD DATA INFILE '/var/lib/mysql-files/1test_com.user.csv' INTO TABLE `user`
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
    [IGNORE number {LINES | ROWS}] --如果要忽略，可以IGNORE n LINES，代表忽略前n行
    
load和insert最大的区别是：load只操作语法一次，之后就是一直是数据导入，而insert 是每一个数据操作一次，也遍历一次字段索引，所以insert本身对于大数据来说是极慢的。

    
从外部shell执行SQL,将该表信息查出来,保存为为csv文件,直接当表格看.
而且这种的导出后,字段名还会作为表头.
mysql -h127.0.0.1 -u用户名 -p密码 -e "SQL语句" 库名 > ./文件名.csv

可以用shell脚本操作mysql数据库，使用mysql的-e参数可以执行各种sql的(创建，删除，增，删，改、查)等各种操作 。

• MySQL自带导入方式load data 语法格式

原文上说的用load data能速度极快的导入数据到数据库中，但是如果要使用fields和lines参数，则必须要带一个参数值且fields必须在lines参数之前；

LOAD DATA 、
[LOW_PRIORITY | CONCURRENT]--无人使用数据库再执行/立即执行
[LOCAL]--带这个参数指服务端即不是服务器主机上读取文件，不带这个参数是默认在服务器主机上读取文件
INFILE 'file_name' --读取文件地址、文件名
    [REPLACE | IGNORE]--遇到重复数据是：替换/重复写入，建议使用ignore重复写入
    INTO TABLE tbl_name --导入到那个表
    [PARTITION (partition_name [, partition_name] ...)]--这行参数可以不要，建议用后面的fields
    [CHARACTER SET charset_name]--设定导入内容字符格式，utf-8还是GBK等都可以指定
 
 
    [{FIELDS | COLUMNS}  --fields标识符
        [TERMINATED BY 'string'] --系统字段通过什么符号区分
        [[OPTIONALLY] ENCLOSED BY 'char']--系统字段本身的起始和结束用什么符号区分
        [ESCAPED BY 'char']--转义符，如果是文本文件，在文本字段中有特殊字符如双引号，可通过定义转义符忽略文本文件特殊字符
    ]
    [LINES  --lines标识符
        [STARTING BY 'string'] --定义行开头的字符串，如果行开头没有字符标识，一般可以不写
        [TERMINATED BY 'string']--行结束字符串标识，通过定义字符来区分行与行的数据
    ]
    [IGNORE number {LINES | ROWS}]--忽略文件中前面多少行，一般都不写
--后面都是指定插入到哪些字段
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

时间相关

时间相关

    1.UNIX_TIMESTAMP(date); 时间函数
    如 果没有参数调用，返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一个date参数被调用，它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的 本地时间的一个数字。

        示例:
            mysql> select UNIX_TIMESTAMP();
            -> 882226357
            mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
            -> 875996580

    2.FROM_UNIXTIME(unix_timestamp,format) 将MYSQL中以INT(11)存储的时间以"YYYY-MM-DD"格式来显示的字符。
    unix_timestamp可以是字段名,也可以直接是Unix 时间戳，format主要是将返回值格式化。  
    若format已经给出，则结果的格式是根据format 字符串而定。
    根据format字符串格式化date值。

　　下列修饰符可以被用在format字符串中：
        %M 月名字(January……December)
        %W 星期名字(Sunday……Saturday)
        %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
        %Y 年, 数字, 4 位
        %y 年, 数字, 2 位
        %a 缩写的星期名字(Sun……Sat)
        %d 月份中的天数, 数字(00……31)
        %e 月份中的天数, 数字(0……31)
        %m 月, 数字(01……12)
        %c 月, 数字(1……12)
        %b 缩写的月份名字(Jan……Dec)
        %j 一年中的天数(001……366)
        %H 小时(00……23)
        %k 小时(0……23)
        %h 小时(01……12)
        %I 小时(01……12)
        %l 小时(1……12)
        %i 分钟, 数字(00……59)
        %r 时间,12 小时(hh:mm:ss [AP]M)
        %T 时间,24 小时(hh:mm:ss)
        %S 秒(00……59)
        %s 秒(00……59)
        %p AM或PM
        %w 一个星期中的天数(0=Sunday ……6=Saturday ）
        %U 星期(0……52), 这里星期天是星期的第一天
        %u 星期(0……52), 这里星期一是星期的第一天
        %% 一个文字“%”。

    示例:
    
        mysql>SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d' )
        ->20071120
        mysql>SELECT FROM_UNIXTIME( 1249488000, '%Y年%m月%d' )
        ->2007年11月20
        mysql> Select FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
        mysql> Select FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300

二: 数学函数

    在mysql中，当处理数值时，会用到数值处理函数，如有一个float型数值2.13，你想只要整数2，那就需要下面的函数floor与round。
 
    floor：函数只返回整数部分，小数部分舍弃。
    round：函数四舍五入，大于0.5的部分进位，不到则舍弃。与floor不同。如下测试

转载于:https://my.oschina.net/chinaliuhan/blog/3065294