MySQL笔记

DQL（数据查询语言）：查询语句，凡是select语句都是DQL；
DML（数据操作语言）：insert delete update，对表当中的数据进行增删改；
DDL（数据定义语言）：create drop alter，对表结构的增删改
TCL（事物控制语言）：commit提交事物，rollback回滚事务
DCL（数据控制语言）：grant授权，revoke撤销权限
============================================================
授权用户主机访问
mysql> grant all privileges on *.* to 'root'@'%' identified by 'rootpassword' with grant option;
mysql> flush privileges;
============================================================
获取服务器上的MySQL元数据
mysql> SELECT VERSION();            #查看mysql版本
mysql> SELECT DATABASE();           #查看当前使用的库
mysql> SELECT USER();               #查看当前登陆的用户
mysql> SHOW STATUS;                 #查看当前服务器的状态信息
mysql> SHOW VIRIABLES;              #查看服务器的配置变量
============================================================
查看,进入库
mysql> SHOW DATABASES;
mysql> USE runoob;
============================================================
查看表
mysql> SHOW TABLES;
============================================================
创建表
mysql> CREATE TABLE IF NOT EXISTS `runoob_tbl`(
     > ‘runoob_id’ INT UNSIGNED AUTO_INCREMENT,
     > ‘runoob_title’ VARCHAR(100) NOT NULL,
     > ‘runoob_author’ VARCHAR(40) NOT NULL,
     > ‘submission_date’ DATE,
     > PRIMARY KEY (‘runoob_id’)
     > )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    释：
       AUTO_INCREMENT：该列为自增列
       NOT NULL：数据不能为空
       PRIMARY KEY：定义主键
       ENGINE=InnoDB：开启表事务
       CHARSET=utf-8：定义字符编码
===========================================================
查看表结构
mysql> DESC runoob_tbl;
===========================================================
插入数据
mysql> INSERT INTO runoob_tbl
     > (runoob_title,runoob_author,submission_date)
     > VALUES
     > ("学习 MySQL","菜鸟教程",NOW());
===========================================================
查询数据
mysql> SELECT runoob_author,submission_date FROM runoob_tbl WHERE runoob_title="MySQL";
mysql> SELECT runoob_author,submission_date FROM runoob_tbl WHERE BINARY runoob_title="MySQL";
    释：
       BINARY：区分大小写
===========================================================
更新数据
mysql> UPDATE runoob_tbl SET runoob_title="Mongodb",runoob_author="初级教程" WHERE runoob_id=3;
===========================================================
删除数据
mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;
===========================================================
MySQL IS NULL 和 IS NOT NULL
mysql> SELECT ENAME FROM EMP WHERE COMM IS NULL;
===========================================================
MySQL IN 与 NOT IN
mysql> SELECT ename FROM EMP WHERE job IN ('CLERK','MANAGER');
===========================================================
MySQL AND 与 OR
mysql> SELECT ENAME FROM EMP WHERE JOB = 'CLERK' OR JOB = 'SALESMAN';
mysql> SELECT ENAME FROM EMP WHERE JOB = 'CLERK' AND (DEPTNO = 20 OR DEPTNO = 30);
注：
    这里的优先级，先算AND，所以如果需要先算or需要括号括起来
===========================================================
MySQL BETWEN ... AND ...区间
mysql> SELECT ENAME FROM EMP WHERE DEPTNO BETWEEN 10 AND 30;
注：
    这里左区间要小于右区间
===========================================================
MySQL LIKE子句
两种用法：
        "%"代表一个或多个字符
        "_"代表任意一个字符
mysql> SELECT * FROM runoob_tbl WHERE runoob_author LIKE '%COM';
mysql> SELECT * FROM runoob_tbl WHERE runoob_author LIKE '___BAIDU.C__';
===========================================================
MySQL UNION检索表
mysql> SELECT country FROM websites
     > UNION
     > SELECT country FROM apps
     > ORDER BY country;
    注：ORDER BY country中的country是第一条sql中的country
===========================================================
MySQL排序（字符编码）
正序
    mysql> SELECT * FROM runoob_tbl ORDER BY submission_date ASC;
倒序
    mysql> SELECT * FROM runoob_tbl ORDER BY submission_date DESC;
自定义排序
    mysql> SELECT * FROM runoob_tbl ORDER BY FIELD(ID,3,2,4) ASC;
    mysql> SELECT * FROM runoob_tbl ORDER BY FIELD(ID,3,2,4) DESC;
    注：FIELD为指定参数不可更改，ID为字段名
============================================================
MySQL正则匹配
“^”：匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置
“$”：匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置
“.”：匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式
“[...]”：字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'
“[^...]”：负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'
“p1|p2|p3”：匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"
“*”：匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}
=============================================================
MySQL分组
分组函数：
  avg：平均值
  sum：和
  min：最小值
  max：最大值
只对JOB进行分组后算MGR的平均值
mysql> SELECT JOB,AVG(IFNULL(MGR,0)) AS TEST FROM EMP GROUP BY JOB;
同时对JOB,DEPTNO两个字段进行分组后算MGR的平均值
mysql> SELECT JOB,AVG(IFNULL(MGR,0)),DEPTNO AS TEST FROM EMP GROUP BY JOB,DEPTNO;
WITH ROLLUP统计数据
mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
==============================================================
MySQL修改表字段ALTER
#删除表i字段
mysql> ALTER TABLE testalter_tbl DROP i;
#再添加i字段
mysql> ALTER TABLE testalter_tbl ADD i INT;
#修改字段c的数据类型
mysql> ALTER TABLE testallter_tbl MODIFY c CHAR(10);
#修改字段名i为j，并且修改字段类型
mysql> ALTER TABLE testallter_tbl CHANGE i j BIGINT;
#修改字段i的默认值
mysql> ALTER TABLE testallter_tbl ALTER i SET DEFAULT 1000;
#删除字段i的默认值
mysql> ALTER TABLE testallter_tbl ALTER i DROP DEFAULT;
#修改表名
mysql> ALTER TABLE testallter_tbl RENAME TO alter_tbl;
===============================================================
MySQL事务
特性：
    原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会单单结束中间的某个环节，事务在执行过程中发生错误，会被回滚到事务的之前的状态。
    一致性：在事务开始之前和事务结束之后数据库的完成性没有破坏，写入的资料必须完全符合所有的预设规则
    隔离性：数据库允许多个并发事务同事对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行导致数据的不一致，事务隔离分为不同级别，包括读未体提交，读提交，可重复读和串行化
    持久性：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失
事务控制语句：
    BEGIN/START TRANSACTION：开启一个事务
    COMMIT/COMMIT WORK：COMMIT会提交事务，并对数据库进行的修改成为永久性
    ROLLBACK/ROLLBACK WORK：回滚并结束事务，撤销正在未提交的事务
    SAVEPOINT save1：允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT
    RELEASE SAVEPOINT save1：删除一个事务的保存点，当没有该保存点，会报出一条错误
    ROLLBACK TO save1：将事务回滚到指定保存点，但是保存点到最后中间的保存点会被清掉
    SET TRANSACTION：用来设置事务的隔离级别，InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ和SERIALIZABLE
============================================================
MySQL临时表
保存一些临时数据，临时表只在当前连接可见，当关闭连接，MySQL会自动删除并释放所有空间,当你去show 查看表时，不会显示该表
mysql> CREATE TEMPORARY TABLE SalesSummary (
     > product_name VARCHAR(50) NOT NULL
     > , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
     > , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
     > , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
     > );
默认断开MySQL连接，临时表也会被删除，也可以手动删除
mysql> DROP TABLE SalesSummary;
==========================================================
MySQL复制表
#先查看被复制表的表结构
mysql> SHOW CREATE TABLE EMP \G;
#复制表结构，表字段以及表属性(这条语句是上面输出的结构，注意修改表名)
mysql> CREATE TABLE `EMP_CLONE` (
     >   `EMPNO` int(4) NOT NULL,
     >   `ENAME` varchar(10) DEFAULT NULL,
     >   `JOB` varchar(9) DEFAULT NULL,
     >   `MGR` int(4) DEFAULT NULL,
     >   `HIREDATE` date DEFAULT NULL,
     >   `SAL` double(7,2) DEFAULT NULL,
     >   `COMM` double(7,2) DEFAULT NULL,
     >   `DEPTNO` int(2) DEFAULT NULL,
     >   PRIMARY KEY (`EMPNO`)
     > ) ENGINE=InnoDB DEFAULT CHARSET=utf8
#将数据导入到复制表
mysql> INSERT INTO EMP_CLONE (
     > EMPNO,
     > ENAME,
     > JOB,
     > MGR,
     > HIREDATE,
     > SAL,
     > COMM,
     > DEPTNO
     > )
     > SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP;
=========================================================
MySQL序列
一张表只有能有一个字段自增主键，其他字段实现自增可以使用AUTO_INCREMENT
mysql> CREATE TABLE insect 
     > (
     > id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     > PRIMARY KEY(id),
     > name VARCHAR(30) NOT NULL,
     > date DATE NOT NULL,
     > origin VARCHAR(30) NOT NULL
     > );
mysql> INSERT INTO insect (id,name,date,origin) VALUES
     > (NULL,'housefly','2001-09-10','kitchen');
重置序列
mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect 
     > ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
     > ADD PRIMARY KEY (id);
设置序列的开始值(eg:100)
mysql> CREATE TABLE insect 
     > (
     > id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     > PRIMARY KEY(id),
     > name VARCHAR(30) NOT NULL,
     > date DATE NOT NULL,
     > origin VARCHAR(30) NOT NULL
     > )engine=innodb auto_increment=100 charset=utf8;
#或者
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
========================================================
MySQL处理重复数据
不设置主键及索引默认会禁止插入重复数据，会弹出一条报错信息
mysql> CREATE TABLE person_tbl (
     > first_name CHAR(20) NOT NULL,
     > last_name CHAR(20) NOT NULL,
     > sex CHAR(10),
     > PRIMARY KEY (last_name,first_name)
     > );
插入重复数据(INSERT IGNORE INTO ...)
mysql> INSERT IGNORE INTO person_tbl (last_name,first_name)
     > VALUES('Jay','Thomas');
mysql> INSERT IGNORE INTO person_tbl (last_name,first_name)
     > VALUES('Jay','Thomas');
统计重复数据
mysql> SELECT COUNT(*) AS repetitions,last_name,first_name
     > FROM person_tbl 
     > GROUP BY last_name,first_name
     > HAVING repetitions > 1;
删除重复数据
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;
#或者(添加INDEX索引和PRIMAY KEY主键简单的方法来删除表中的重复记录)
mysql> ALTER IGNORE TABLE person_tbl
     > ADD PRIMARY KEY (last_name, first_name);
=======================================================
索引

普通索引
#创建索引
mysql> CREATE INDEX indexName ON table_name (column_name);
#修改表结构(添加索引)
mysql> ALTER TABLE tableName ADD INDEX indexName(columeName);
#创建表的时候指定索引
mysql> CREATE TABLE mytable(
     > ID INT NOT NULL,
     > username VARCHAR(16) NOT NULL,
     > INDEX indexName (username(length))
     > );
#删除索引
mysql> DROP INDEX indexName ON tableName;

唯一索引
与普通索引类似，索引列的值必须是唯一，但允许有空值
#创建索引
mysql> CREATE UNIQUE INDEX indexName ON tableName(username(length));
#修改表结构
mysql> ALTER TABLE tableName ADD UNIQUE indexName (username(length));
#创建表的时候直接指定
mysql> CREATE TABLE tableName(  
     > ID INT NOT NULL,   
     > username VARCHAR(16) NOT NULL,  
     > UNIQUE indexName (username(length))  
     > );  
显示索引信息
mysql> SHOW INDEX FROM tableNamel; \G
=====================================================
MySQL导出数据
输出到"/tmp"目录下，其他目录会报权限问题
mysql> SELECT * FROM EMP INTO OUTFILE "/tmp/emp.txt";
导出CSV格式
mysql> SELECT * FROM EMP INTO OUTFILE '/tmp/emp1.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
以逗号隔开数据
mysql> SELECT * INTO OUTFILE '/tmp/emp2.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM EMP;
导出SQL格式的表数据
##mysqldump -uroot -p bjpowernode EMP > /tmp/test1.txt
导出整个数据库的数据
##mysqldump -uroot -p bjpowernode > /tmp/bjpowernode.txt
导出所有库的数据
##mysqldump -uroot -p --all-databases > database_dump.txt
恢复数据
##mysql -uroot -p bjpowernode < /tmp/bjpowernode.txt
===================================================
MySQL导入数据
mysql命令导入
##mysql -uroot -p bjpowernode < /tmp/bjpowernode.txt
source命令导入
mysql> create database bjpowernode;
mysql> use bjpowernode;
mysql> set name utf8;
mysql> source /tmp/bjpowernode.sql
LOAD DATA导入数据
mysql> LOAD DATA LOCAL INFILE '/tmp/bjpowernode.txt' INTO TABLE mytbl LINES TERMINATED BY '\r\n';

====================================================
SQL优化

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。    
    
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：    
select id from t where num is null    
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：    
select id from t where num=0    
    
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。    
    
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：    
select id from t where num=10 or num=20    
可以这样查询：    
select id from t where num=10    
union all    
select id from t where num=20    
    
5.in 和 not in 也要慎用，否则会导致全表扫描，如：    
select id from t where num in(1,2,3)    
对于连续的数值，能用 between 就不要用 in 了：    
select id from t where num between 1 and 3    
    
6.下面的查询也将导致全表扫描：    
select id from t where name like '%abc%'    
    
7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：    
select id from t where num/2=100    
应改为:    
select id from t where num=100*2    
    
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：    
select id from t where substring(name,1,3)='abc'--name以abc开头的id    
应改为:    
select id from t where name like 'abc%'    
    
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。    
    
10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。    
    
11.不要写一些没有意义的查询，如需要生成一个空表结构：    
select col1,col2 into #t from t where 1=0    
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：    
create table #t(...)    
    
12.很多时候用 exists 代替 in 是一个好的选择：    
select num from a where num in(select num from b)    
用下面的语句替换：    
select num from a where exists(select 1 from b where num=a.num)    
    
13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。    
    
14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，    
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。    
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。    
    
15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。    
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。    
    
16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，    
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。    
    
17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。    
    
18.避免频繁创建和删除临时表，以减少系统表资源的消耗。

19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。    
    
20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，    
以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。    
    
22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。    
    
23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

25.尽量避免大事务操作，提高系统并发能力。

26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理