mysql 拾遗提高(函数、事务、索引)
目录
1、tips
2、事务（transaction）
3、索引(index)
4、数据库的导出和备份
5、函数
6、防SQL注入
7、使用Explain分析SQL语句
8、视图（view）
1、tips
1)、数据库不区分大小写；
2)、插入新数据时忽略重复数据，可在INSERT后加关键字IGNORE即可
3)、通过UNION操作符来连接两个以上的SELECT语句结果组合到一个结果到同一个集合中；
格式：
select... from... where... union distinct select...from...where...
其中distinct是可选的，表示会删除重复项，是默认值；也可使用all来保留所有的数据；
4）、拼音排序
如果汉字使用的是GBK编码，则可直接对某一项进行排序；如果使用的UTF-8，还需要进行转码，
例：select * from tablename order by convert(titlename using gbk);
5）、在group by...后边加with rollup可使数据在分组的基础上再进行聚合函数操作；
例：select name as 名字,sum(times) as 次数 from tablename group by name with rollup;可在对名字分组基础上再求次数的和，最后还有一个总和；
分组后相同项此次的统计不需要使用此关键词,使用count(*)即可；
例：select name as 名字 count(*) from tablename group by name;
6）、正则表达式
mysql也可以使用正则表达式来作为过虑条件
例：select * from tablename where name REGEXP '^s'; (查询以s开头的名字)
7）字符串的截取
select left(string,length) 从左边开始截取string的前length个字符，left可换用right来从右边开始截取；
select substring(string,index) 从左开始截取string从index位开始到结束的字符串（包括index位，且从1开始计数），如果index为负数则从右向左的截取；
select substring_index(string,reg,index) 使用同上，reg表示的匹配原则；
select CONCAT(string1,string2,...)连接字符串，但如果其中有任意一个为null，则最后结果为null;
8）条件语句
if(bool,value1,value2) 当bool为true时使用value1,否则value2; 例：select if(2=1,'对','错') as status; 结果为'错’；
ifnull(value1,value2) 如果value1为null则使用value2;
nullif(value1,value2) 如果value1和value2相同时结果为null，否则结果为value1;
coalesce(tablecolumn1,tablecolumn2,...,'name')
意为如果tablecolumn1为null则使用tablecolumn2,依次顺推，'name'为一个固定的默认值；
select case [columnName] when condition1 then result1 when condition2 then result2 ... end as '别名' from tablename;
9）、对于NULL值的处理
NULL值不能通过=或!=来比较获得结果，查找值为null的需要使用IS NULL,反之使用IS NOT NULL;<=>符号用于比较的两个值都为NULL时返回true;
10）临时表
主要用于保存一些临时数据，临时表在当前连接可见，一旦断开mysql连接则会自动销毁；创建临时表只需要在CREATE后加一个TEMPRARY字段，其他语法与创建表格是一样的；
11）SHOW CREATE TABLE tablename 查看表的创建语句；
12）表格的完整复制
CREATE TABLE newtable LIKE oldtable; #复制表的结构，也可以使用CREATE TABLE newtable SELECT *FROM oldtable WHERE 1=2; INSERT INTO newtable SELECT *FROM oldtable;
13)自增值序列重排：
原理：先删除此列再新建自增列（但是在处理过程中如果有新增数据，可能会使表数据变乱）
ALTER TABLE tablename DROP id; ALTER TABLE tablename ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARTY KEY (id);
设置自增序号开始值：
ALTER TABLE tablename AUTO_INCREMENT = 50; 此命令用于已经建好的表
或者可以在新建表的同时设置，在create table t(...)engine =innodb auto_increment=50 charset=utf8;
14）数据类型转换（CAST,CONVERT)
CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值;
格式：CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)
可相互转换的类型有：BINARY,CHAR,DATE,DATETIME,DECIMAL,INTEGER,TIME
15）表名和字段名都可以用as来设置别名，且as关键字可以省略，但通常不建议省略。
2、事务（transaction）
>事务主要用于处理操作量大，复杂度高的数据；只有使用了lnnodb数据库引擎的数据库才支持事务；
>事务是用来管理insert,update,delete等修改数据库的语句的,必须满足四个条件：
原子性：一个事务中所有操作要么全部完成，要么全部不完成；
一致性：在事务开始前和结束后，数据库的完整性没有被破坏；
隔离性：数据库允许多个并发事务同时进行；
持久性；事务处理结束后，对数据库的修改就是永久的；
格式：
begin; expression; commit;#确认并提交执行
事务控制语句：
begin/start transaction 显示的开启一个事务； commit/commit work 提交事务； rollback/rollback work 回滚事务，会结束事务，并撤销正在进行的所有未提交的修改； savepoint identifier 创建一个保存点identifier,一个事务中可以有多个保存点； release savepoint identifier 删除一个保存点； rollback to identifier 回滚到一个保存点； set transaction 设置事务隔离级别，值有read uncommitted,read committed,repeatable read,serializable set autocommit = 0 禁止自动提交，如果为1则自动提交；
3、索引(index)
>索引可以很大的提高mysql检索速度；索引也是一张表，保存了主键与索引字段，并指向实体表的记录；会占用磁盘空间，会降低表的更新速度；
>索引分单列索引和组合索引，一个表可以有多个单列索引；组合索引即一个索引包含表中的多个列；只对那些将应用在查询条件（一般where后）的列创建索引；
>显示一个表的索引信息:SHOW INDEX FROM tablename; \G 最后的\G是可选的，用于格式化输出信息；
>查询索引使用情况:SHOW STATUS LIKE "handler_read%";结果中的handler_read_rnd_next值越高说明效率越低；（为什么越查越大。。。）
>创建索引：
1)CREATE INDEX indexName ON mytable(username(length)); 如果是char,varchar类型length可小于实际长度，blob或text必须指定length。用UNIQUE INDEX来创建唯一索引(可以为空）； 2)创建表时创建；CREATE TABLE mytable(ID INT NOT NULL,username CHAR(16) NOT NULL, INDEX [indexName] (username(length)));如果唯一，则使用UNIQUE替换INDEX; 3)ALTER TABLE tablename ADD INDEX indexName(columnName); 添加普通索引； 4)ALTER TABLE tablename ADD UNIQUE [indexName] (username(length)); 添加唯一索引，可为NULL; 5)ALTER TABLE tablename ADD PRIMARTY KEY (columnName); 添加主键；唯一且不为空；添加为主键前需要先确认它不为空:ALTER TABLE tablename MODIFY columnName NOT NULL; 6)ALTER TABLE tablename ADD FULLETXT indexName (columnName); 指定索引为FULLTEXT,用于全文索引；
>删除索引 DROP INDEX [indexname] ON mytable;
ALTER TABLE tablename DROP PRIMARTY KEY; 删除主键；删除索引时需要知道索引名；
>如果like的查询条件以%开始，或者where条件没使用=号或条件给的数据类型与字段类型不一致时，不会使用索引；
>注意：
1)为维度度的列创建索引（列的重复值越少维度越高）；
2）为where,on,group by,order by中的条件创建索引；
3）对较小数据列使用索引；
4）为较长字符串使用前缀索引（即限制索引表中值的长度，只取前一部分）；
5）使用组合索引可以减少文件索引大小，速度会优于多个单列索引；
6）只为操作频繁的列创建索引；
4、数据库的导出和备份
1）数据导出到文件
SELECT ... INTO OUTFILE 'fileAddressAndName' #以下两句都是用于设置输出格式的，可选； FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
导出文件存在于服务器主机，且文件名不能是一个已经存在的文件
2）从文件导入到数据库（为导出的逆操作）
LOAD DATA LOCAL INFILE 'filename' INTO TABLE tablename #默认文件结构和表结构顺序一致，如果不一致需要使用tablename(columnName1,...)的方式来指定文件列与仓库中列相对应； FIELDS TERMINATED BY ':' LINES TERMINATED BY '\n';
或者也可以使用mysqlimport语句来导入；
3）数据库备份：mysqldump -u root -p --no-create-info --table=/foldername filename
备份连接的所有数据库：mysqldump -u root -p --all-database > dumpname.txt 其中 --all-database也可为指定仓库名，来备份指定仓库；
将远程主机数据备份到本地：mysqldump -h remoteHost.com -P port -u root -p databaseName > dumpname.txt;
4）恢复备份：mysql -u root -p databaseName < dumpname.txt
5、函数
1>常用函数：
DATE_ADD(columnName,INTERVAL time type) 向日期列添加指定的时间间隔，time为数字，type为单位，可为MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,SECOND_MICROSECOND,MINUTE_MICROSECOND,MINUTE_SECOND,HOUR_MICROSECOND,HOUR_SECOND,HOUR_MINUTE,DAY_MICROSECOND,DAY_SECOND,DAY_MINUTE,DAY_HOURE,YEAR_MONTH; DATE_SUB(columnName,INTERVAL time type) 向日期列减去指定的时间间隔； DATEDIFF(date1,date2) 返回两个日期之间的天数； DATE_FORMAT(columnName,format) 用于定义显示日期/时间的格式，format内容为%a,%b,...等多种不同格式，具体待查； NOW() 返回当前的日期和时间； CURDATE() 返回当前日期； CURTIME() 返回当前时间； DATE(columnName) 返回日期/时间表达式的日期部分； EXIRACT(type FROM columnName) 返回日期/时间表达式的指定部分，type可用所有时间type
2>获取服务器数据：
select version() 服务器版本信息； select database() 当前数据库名； select user() 当前用户名； show status 服务器状态； show variables 服务器配置变量；
6、防SQL注入
SQL注入，即通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，以欺骗服务器执行恶意SQL命令；
1）永远不要信任用户输入，对用户输入进行校验，或使用正则表达式，限制长度，对单引号和双"-"号进行转换等；
2）永远不要使用动态拼装sql，可以使用参数化的sql或直接使用存储过程进行数据查询存取；
3）永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的连接数据库；
4）不要把机密信息直接存放，加密或者hash掉密码和敏感信息；
5）应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装；
6）可采用辅助软件或网站平台来检测是否有SQL注入，如软件有jsky;
7、使用Explain分析SQL语句
使用方法即在正常的SQL语句前加上 EXPLAIN 关键字，会将这条SQL语句的分析结果展示出来（sql语句在执行前都会由分析器进行分析，以判断语句的可行性的）
结果分析：
select_type 查询类型，有简单查询、联合查询、子查询等； table 使用的数据表格； type 连接使用的类型，结果从优到差：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all;const表示一次就得到结果，all表示全表扫描了；好的语句要优化到rang,ref级别； possible_keys 显示能使用哪个索引在该表中找到结果，如果为空，则没有相关索引； key 执行此语句实际使用到的索引； key_len 最长索引宽度，越短越好； ref 显示哪个字段或常数与key一起被使用； rows 表示遍历了多少条数据； extra 执行状态说明；
8、视图（view）
视图是一个虚拟表，内容是由其他表的数据组成的；视图只能查看不能修改；使用视图可以隐藏一些数据，也可以简化用户操作；
创建视图语法：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}] VIEW [db_name]view_name [(column_list)] AS select_statement [WITH [CASCADED|LOCAL] CHECK OPTION]
示例：
CREATE VIEW query_view(id,name,class) AS SELECT B.u_id,B.u_name,A.class_name FROM t_class AS A INNER JOIN t_name AS B ON A.u_id=b.u_id; #创建视图
DROP VIEW IF EXISTS query_view; 删除视图 SELECT * FROM query_view 使用一个视图查询数据 DESCRIBE query_view 查看视图结构 SHOW TABLE STATUS LIKE 'query_view';显示视图状态；
ABS 求绝对值 SQRT 求二次方根 MOD 求余数 CEIL和CEILING 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整 FLOOR 向下取整，返回值转化为一个BIGINT RAND 生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列 ROUND 对所传参数进行四舍五入 SIGN 返回参数的符号 POW和POWER 两个函数的功能相同，都是所传参数的次方的结果值 SIN 求正弦值 ASIN 求反正弦值，与函数 SIN 互为反函数 COS 求余弦值 ACOS 求反余弦值，与函数 COS 互为反函数 TAN 求正切值 ATAN 求反正切值，与函数 TAN 互为反函数 COT 求余切值 LENGTH 计算字符串长度函数，返回字符串的字节长度 CONCAT 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个 INSERT 替换字符串函数 LOWER 将字符串中的字母转换为小写 UPPER 将字符串中的字母转换为大写 LEFT 从左侧字截取符串，返回字符串左边的若干个字符 RIGHT 从右侧字截取符串，返回字符串右边的若干个字符 TRIM 删除字符串左右两侧的空格 REPLACE 字符串替换函数，返回替换后的新字符串 SUBSTRING 截取字符串，返回从指定位置开始的指定长度的字符换 REVERSE 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 CURDATE和CURRENT_DATE 两个函数作用相同，返回当前系统的日期值 CURTIME和CURRENT_TIME 两个函数作用相同，返回当前系统的时间值 NOW 和 SYSDATE 两个函数作用相同，返回当前系统的日期和时间值 UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数 FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数 MONTH 获取指定日期中的月份 MONTHNAME 获取指定日期中的月份英文名称 DAYNAME 获取指定曰期对应的星期几的英文名称 DAYOFWEEK 获取指定日期对应的一周的索引位置值 WEEK 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53 DAYOFYEAR 获取指定曰期是一年中的第几天，返回值范围是1~366 DAYOFMONTH 获取指定日期是一个月中是第几天，返回值范围是1~31 YEAR 获取年份，返回值范围是 1970〜2069 TIME_TO_SEC 将时间参数转换为秒数 SEC_TO_TIME 将秒数转换为时间，与TIME_TO_SEC 互为反函数 DATE_ADD 和 ADDDATE 两个函数功能相同，都是向日期添加指定的时间间隔 DATE_SUB 和 SUBDATE 两个函数功能相同，都是向日期减去指定的时间间隔 ADDTIME 时间加法运算，在原始时间上添加指定的时间 SUBTIME 时间减法运算，在原始时间上减去指定的时间 DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值 DATE_FORMAT 格式化指定的日期，根据参数返回指定格式的值 WEEKDAY 获取指定日期在一周内的对应的工作日索引 MAX 查询指定列的最大值 MIN 查询指定列的最小值 COUNT 统计查询结果的行数 SUM 求和，返回指定列的总和 AVG 求平均值，返回指定列数据的平均值 IF 判断，流程控制 IFNULL 判断是否为空 CASE 搜索语句
可参考好文： 书写高质量SQL的30条建议
