Mysql梳理

DCL

Data Control Language

（数据库的系统管理员）控制用户的访问权限。使用语句包括grant[授权]、revoke[回收权限]

eg1:创建一个数据库用户 z1，具有对 zz 数据库中所有表的 SELECT/INSERT 权限：

grant select, insert on zz.* to 'z1'@'localhost' identified by 'z123';

eg2:将 z1 的权限变更，收回 INSERT，只能对数据进行 SELECT 操作：

revoke insert on zz.* from 'z1'@'localhost';

这又是什么？

GRANT update ON inventory TO joe WITH GRANT OPTION;

一个对象权限被授予用户 JOE。

with admin option 只能在赋予 system privilege 的时使用；

with grant option 只能在赋予 object privilege 的时使用。

系统权限

对象权限

DDL

Data Definition Language

定义数据库、表、视图、索引和触发器等。create[创建]、alter[修改表定义]、drop[删除]truncate。

创建数据库

CREATE DATABASE 数据库名;

如果创建的数据表中有值为中文的字段，在创建数据库时需要指定一个支持中文字符的字符集编码。推荐使用UTF-8编码。

CREATE DATABASE db_1 CHARACTER SET UTF-8 COLLATE utf_general_ci;

ps：

1.数据库名可以由任意字母、数字、下画线(_)和美元符号($)组成，但不能由单独的数字组成，也不能为 MySQL 关键字，而且长度还不能超过64个字符。

2.在windows系统下，数据库名不区分大小写，在UNIX、 Linux 系统下，数据库名、表名是区分大小写的，但MySQL语句不区分大小写。

3.完整的MySQL语句必须以‘;’结尾的语句。或者用g代替；（仅限命令行，有的版本可能不支持了）。

删除数据库

DROP DATABASE 数据库名;

创建表

语法格式如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS]  tbl_name    [(create_definition....)][table_options] [select_statement];

TEMPORARY：该参数用于创建临时表。只有在当前连接下，临时表才是可见的；当连接关闭时，临时表被自动取消。

数据表的名称、命名要求与数据库名一样，由任意字母、数字、下画线(_)和美元符号($)组成，但tbl_name：表名不能由单独的数字组成，也不能为MySQL的关键字,而且长度还不能超过64个字符。

Table_options：该参数项用于设置数据库表的一些特性。

Select_statement：该参数用于嵌入一个SELECT查询语句，从而快速创建数据表。

每个字段的语法格式如下:

col_name  type [NOT NULL|NULL][DEFAULT default_value][AUTO_INCREMENT][UNIQUE_KEY]|[PRIMARY_KEY][COMMENT ‘String’]
[reference_definition](size)

type：字段的数据类型,如INT,REAL,DOUBLE,FLOAT,DATE,VARCHAR等。

NOT NULL,NULL：如果设置为NULL,或者省略不设置，那么表明该字段允许为空值；如果设置为NOT NULL，那么表明该字段不允许为空值。

DEFAULT default_value：该参数用来设置字段的默认值，default_value为该字段默认的数值。

AUTO_INCREMENT：用于设置一个整数类型的字段为自动增长字段。所谓自动增长，就是当试图向该字段插入一个NULL值或0时，该字段的值会被设置成当前列的最大值+1，每张数据表只能有一个AUTO_INCREMNET字段，且该字段必须设置索引，同时不能为有默认值。默认从1开始，后面加n。

UNIQUE或UNIQUE KEY：该参数用于设置UNIQUE索引。所谓UNIQUE索引（mysql里叫索引INDEX而非constraint），就是插入该字段的值必须不相等，否则会出错。例外情况是，如果该字段被设置为允许NULL值，则插入该字段的值可以包含多个NULL值。但此例外情况不适用于使用Berkley DB(BDB)引擎的数据表。

PRIMARY KEY或 KEY：该参数用于设置主键。一张数据表只能有一个PRIMARY KEY,即只能有一个主键。设置为主键的字段必须定义为NOT NULL，如果该字段没有被明确地定义为NOT NULL,则MySQL会隐含地定义该字段为NOT NULL。

主键的指定也可以在()里最后一列：primary key(colname)。

COMMENT ‘string’：该参数用于设置字段的注释。字符串string中即为注释的内容。

Reference_definition：该参数用于引用其他数据表中的字段。

size：最大长度。

通过复制其他表创建表

create table test1 as select * from test;

后面加上WHERE 1=2则只复制表结构；

约束

删除表

语法格式如下:

DROP TABLE [IF EXISTS] 数据表名[,数据表名......];

可删除一个或多个数据表，多个数据表名之间用英文逗号‘，’隔号。

如果仅仅需要删除表内的数据，但并不删除表本身，使用 TRUNCATE TABLE 语句：

TRUNCATE TABLE table_name;

在功能上与不带 WHERE 子句的 DELETE 语句相同，二者均删除表中的全部行。区别在于：

truncate 只能对TABLE，delete 可以是table,view,synonym。

在内存中，用delete删除数据，表空间中其被删除数据的表占用的空间还在，便于以后的使用，另外它是“假相”的删除，相当于windows中用delete删除数据是把数据放到回收站中，还可以恢复，当然如果这个时候重新启动系统（OS或者RDBMS），它也就不能恢复了！

而用truncate清除数据，内存中表空间中其被删除数据的表占用的空间会被立即释放，相当于windows中用shift+delete删除数据，不能够恢复（回滚）！

delete一般用于删除少量记录的操作，而且它是要使用回滚段并且要进行显示的提交 。

truncate 则使用于大量删除数据，而且隐式提交事务，其速度要比使用 delete快。

ALTER修改表结构:

包括重命名数据表名称、重命名字段名称、修改字段类型、修改字段默认值、增加和删减字段等。

重命名数据表名

ALTER TABLE 原数据表名 RENAME 新数据表名;

重命名数据表中的字段名以及字段定义

ALTER TABLE 数据表名 CHANGE 原字段名 新字段的定义;

增加和删减字段

ALTER TABLE 数据表名 ADD 增加的字段定义;

删减字段使用DROP子语句:

···

ALTER TABLE 数据表名 DROP 要删减字段的名称;

···

修改字段排列顺序

默认 ADD 增加的新字段是加在表的最后位置，而 CHANGE/MODIFY 默认都不会改变字段的位置。

alter table emp add birth date after ename;
alter table emp modify age int(3) first;

ps:

ALTER TABLE语句在执行时，会对原数据表进行临时复制，并在副本上进行修改，修改完成后删除原数据表，再对新表进行重命名。在执行ALTER TABLE语句时，其他用户可以阅读原数据表，但是对该数据表的更新和修改操作将被延迟，直到新数据表生成为止。新表生成后，这些更新和修改信息会自动转移到新表上。

备份数据库

在MySQL的bin目录下，有一个名为mysqldump的可执行文件，执行：

mysqldump  --opt 要备份的数据库名 -h MySQL数据库所在的域名或IP地址 -u 账户名 -p -r 备份文件的路径backup.mysql

或者：

Mysqldump --opt 要备份的数据库名 -h MySQL数据库所在的域名或IP地址 -u 账户名 -p > 备份文件的路径backup.mysql

还原数据库

通过使用mysqldump程序备份的文件，可以将数据库恢复到指定的状态。语法格式:

mysql -h MySQL数据库所在的域名或IP地址 -u 账户名 -p 要恢复的数据库名 < 备份文件的路径

视图

视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

视图是虚拟的表，它只是一个查询语句对应的结果集。在 SQL 语句中体现的角色与表一致，但视图不能索引，也不能有关联的触发器或默认值。

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno, ename, sal, deptno 
FROM emp 
WHERE deptno = 10

使用视图好处：

1.简化用户操作：利用视图简化复杂的联结利用视图，可一次性编写基础的 SQL ，然后根据需要多次使用。

2.对重构数据库提供了一定程度上的逻辑独立性：视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

3.安全性：通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上。

使用视图坏处：

1.性能：如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，它会变成一个复杂的结合体，需要花费一定的时间。许多 DBMS 禁止在视图查询中使用ORDER BY子句。

2.修改限制：当用户试图修改视图的某些行时，对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的。

常见应用：

重用 SQL 语句。
简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表。
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

简单视图

对应的子查询不含有函数，表达式，分组，去重，关联查询。

简单视图可以进行DML操作（只能对视图可见的字段进行），对该视图的操作就是对该视图数据来源的基础表进行的操作，所以不能违反基础表的约束条件。

为了禁止在视图上执行DML操作，可以在建立视图时设置READ ONLY约束。

复杂视图

除了简单视图就是复杂视图。

复杂视图不允许进行DML操作。

避免对视图的不当DML操作

对视图的不当DML操作会污染基表数据，不当DML操作即:对视图进行DML操作后，视图对基础表对应数据进行该DML操作，但是操作后视图却对该记录不可见。

如有一个视图，只检索带有电子邮件地址的顾客，如果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于视图。

DELETE不会产生污染现象。why？因为数据都被删了，相当于查出来是空的。

为视图添加检查选项，可以避免对视图操作而导致的对基表的数据污染。

WITH CHECK OPTION 该选项要求对视图进行DML操作后，该记录必须对视图可见。

CREATE OR REPLACE VIEW v_emp_10
AS SELECT empno id,ename name,sal salary,deptno
FROM emp WHERE deptno=10
WITH CHECK OPTION

为视图添加只读选项，要求对视图仅能进行查询操作不能进行任何DML操作。

WITH READ ONLY

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,
       sal salary,deptno
FROM emp
WHERE deptno=10
WITH READ ONLY

索引

是为了帮助mysql高效获取数据，对数据库表中一个或多个列的值进行 排序 的结构，用户无法看到索引，它们只能被用来加速搜索/查询。

注意：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

分为：普通索引、唯一索引（确保每一行数据的唯一性）、主键索引、全文索引。

优化tips: 避免在索引列上使用计算、not、in、<>等操作。

eg1:在 "Persons" 表的 "LastName" 列上创建一个名为 "PIndex" 的索引：

CREATE INDEX PIndex ON Persons (LastName);

如果希望索引不止一个列，可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PIndex ON Persons (LastName, FirstName);

删除索引：

ALTER TABLE table_name DROP INDEX index_name

存储过程

触发器

DQL

主要是SELECT。

SELECT 字段名[字段名,...] FROM 数据表名 WHERE 条件表达式;

1.“数据表名”可以是一个子查询得到的结果。

WHERE子语句

条件表达式可以是包含‘>’、‘<’ 、‘>=’、‘<=’、‘=’(相等)、‘<>’(不等)、‘BETWEEN’、'LIKE'、‘IN’、‘and’(逻辑与)、‘or’(逻辑或)、'IS NULL'等运算符。

但里面不可以放聚合函数！如果需要，可以用GROUP BY 或子查询。

GROUP BY

用于结合聚合函数，根据一个或多个列对结果集进行分组。

有时需要对表中的记录按照字段进行分组，然后对每个分组分别运用聚合函数进行查询或者计算。语法格式如下:

SELECT [聚合函数] 起的名[,字段名 ... ] FROM 表名
WHERE 查询条件
GROUP BY 字段名

eg1:统计80分及以上各个成绩的学生人数：

SELECT score, count(*) FROM stu_ WHERE score>=80 GROUP BY score;

HAVING

GROUP BY 子语句常常有HAVING子语句连用，接在GROUP BY后面。

增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING子语句与WHERE子语句的区别在于， having 是对聚合后的结果进行条件的过滤，而 where 是在聚合前就对记录进行过滤。

优化tips:如果逻辑允许，我们尽可能用 where 先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用 having 进行再过滤。

LIMIT（oracle没有）

SELECT 字段名[字段名,...] FROM 数据表名
WHERE 条件表达式
LIMIT [OFFSET,]  row_count;

从索引为offset（默认为0）的记录开始，返回row_count条记录。

offset和row_count 必须为非负整数常量。

SELECT * FROM city limit 10,5;        --从索引为10（第11条）的记录开始，读取5条，等同于下面
SELECT * FROM city limit 5 offset 10;    --从索引为10（第11条）的记录开始，读取5条数据。

ORDER BY

LIMIT常常与ORDER BY配合使用，先对查询结果进行排序，再从中挑出指定位置的记录。‘ASC’与‘DESC’为可选参数，分别表示升序（默认，小到大）排列和降序排列。ORDER BY 多列的时候，先按照第一个column name排序，在按照第二个column name排序。

SELECT 字段名[字段名，...] FROM 数据表名
WHERE 条件表达式
ORDER BY P字段名[字段名,...] [ASC|DESC]
LIMIT [offset,] row_count;

还有一种写法：

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

ORDER BY 2表示按SELECT清单中的第二个列prod_name进行排序。ORDER BY 2，3表示先按prod_price，再按prod_name进行排序。

聚合函数

DISTINCT

一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值

它会作用于所用列，怎么使得它只作用于指定的列？

LIKE

与NOT连用，反向选择:

SELECT * FROM Websites WHERE name NOT LIKE '%oo%';

调优tips：通配符搜索一般比其他搜索要耗费更长的处理时间，不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。

sql中的正则表达式

% 表示1或多个字值， _ 下划线表示一个字符；

M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。

%M% : 表示查询包含M的所有内容。

%M_ : 表示查询以M在倒数第二位的所有内容。

[charlist] 字符列中的任何单一字符；

[^charlist] 或 [!charlist] 不在字符列中的任何单一字符。

REGEXP或 NOT REGEXP 正则匹配

eg1:选取 name 以 "G"、"F" 或 "s" 开始的所有网站：

SELECT * FROM Websites WHERE name REGEXP '^[GFs]';

IN

允许在 WHERE 子句中规定多个值。

Select * from emp where sal in (5000,3000,1500);

IN操作符完成了与OR相同的功能，其优点在于：

1.语法更清楚，更直观。

2.在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。

3.IN操作符一般比一组OR操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）。

4.IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。

BETWEEN

可加NOT反向选择

起别名AS

可以为表名称或列名称指定别名

还把多个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名：

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites;

EXISTS/NOT EXISTS

查询条件为EXISTS关键字后面跟的SELECT语句的查询结果不为空即可！

Exists：若子查询的结果集非空时，返回“True”；若子查询的结果集为空时，返回“False” 。

NOT EXISTS ：若子查询结果为空，返回“TRUE”值；若子查询的结果集非空时，返回 “FALSE。

与IN的区别

但是，查询数据库所消耗的性能比内存遍历比较大！所以最后结果是，EXISTS适合子查询表很大的情况！其他情况差不多。

DML

Data Manipulation Language语句。操作数据库的表如insert[插入]，update[更新]，delete[删除]数据。

插入语句(INSERT)

插入一行数据：

INSERT INTO 数据表名 VALUES(字段的值,...);

高度依赖于表中列的定义次序，如果表结构变了，会出错。

插入行的部分数据：

INSERT INTO 数据表名(字段名,...) VALUES(字段的值,...);

其中，字段名与字段值必须一一对应。字段名可以不列全，此时插入的这条记录中，省略的字段会被默认值填充。

或者使用INSERT SET语句：

INSERT INTO 数据表名 SET 字段名1=字段值1, 字段名2=字段值2;

从一个表apps复制（查询）数据，然后把数据插入到另一个表Websites中INSERT INTO…SELECT…：

INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;

带子查询的：

insert into vehicle_nonetwork (time,areacode,vincode,color,vintype) 
SELECT 201507,areacode,vincode,color,tran_new FROM `vehicle_201507` 
where  vincode not in 
(SELECT DISTINCT v.vincode 
from vehi v1,vehicle_201507 v 
where v.areacode=v1.areacode and v.color=v1.color and v.vincode=v1.vincode) 
order by areacode;

SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;

更新语句(UPDATE)

UPDATE 数据表名
SET 字段名=新字段值,...
[WHERE 查询条件];

注意：update 后面可以做任意的查询，这个作用等同于from；但更新的表不能在set和where中用于子查询。

同时更新多个表的复杂更新：

update ta a,(select * from tb where name='ee') b set a.Bid=b.id ,b.Aid=a.id
where a.name=b.name and a.time=b.time;

多表更新的语法更多地用在了根据一个表的字段，来动态的更新另外一个表的字段:

update  emp  a,dept b  set  a.sal=a.sal*b.deptno,b.deptname=a.ename  where
a.deptno=b.deptno;

set值也可以带子查询：

update aaaCountV set 客车总数=(select sum from akeche1 where  akeche1.owner_id=aaaCountV.owner_id);

ps：要删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。

存在即更新(REPLACE)

用REPLACE插入一条记录时，如果不重复，REPLACE就和INSERT的功能一样，如果有重复记录，REPLACE就使用新记录的值来替换原来的记录值。

但是，在使用REPLACE时，表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则REPLACE就和INSERT完全一样的。

REPLACE tablename(列名…) VALUES(列值);

或者：

REPLACE tablename SET column_name1 =value1, column_name2 = value2，…;

删除语句(DELETE)

用来删除表中的记录。常与WHERE子语句连用，用来定位需要删除的记录。

语法格式如下:

DELETE t1,t2…tn FROM t1,t2…tn [WHERE CONDITION];

如果没有WHERE子语句，则该表中的所有数据将被删除，DELETE不删除表本身，表结构、属性、索引将保持不变。同 DELETE * FROM table_name 。

如果 from 后面的表名用别名，则 delete 后面的也要用相应的别名，否则会提示语法错误：

delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

存储引擎

查询当前数据库支持的存储引擎： mysql> SHOW ENGINES \G; 得到9条记录。

ps： \G 选项的含义是使得记录能够按照字段竖着排列，对于内容比较长的记录更易于显示（仅限命令行）

通过查看建表语句，可以查看表的存储引擎： SHOW CREATE TABLE table_name;

MyISAM

MySQL 的默认存储引擎。MyISAM 不支持事务、也不支持外键，其优势是访

问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用

这个引擎来创建表。

InnoDB

支持外键、行锁的锁机制、提供了具有提交、回滚和崩溃恢复能力的事务安全。

但是对比 MyISAM的存储引擎，InnoDB 写的处理效率（包括批量处理）差一些并且会占用更多的磁盘空间以保留数据和索引。

如果应用对事务的完整性有比较高的要求， 在并发条件下要求数据的一致性， 数据操作除了插入和查询以外， 还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback） ，

对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。