MYSQL语法
mysql大小写问题：
1、数据库名与表名是严格区分大小写的；2、表的别名是严格区分大小写的；3、列名与列的别名在所有的情况下均是忽略大小写的；4、字段内容默认情况下是大小写不敏感的。
使用MYSQL
选择数据库： USE database_name;
展示所有数据库：SHOW DATABASES；
展示指定数据库的所有表：SHOW TABLES from database_name；
展示指定表的所有列属性：SHOW COLUMNS from table_name;
MYSQL检索
- 检索单个列：select column_name from table_name;
多个列之间用逗号隔开，所有列用* ； - 检索不同的行；select distinct column_name from table_name;
限制返回行的数量：select column_name from table_name limit 5;
限制返回行的范围：select column_name from table_name limit k offset n; 从行n开始的k行
使用完全限定的表名和列名：select table_name.colume_name from database_name.table_name; - 排序检索
排序检索单列：select column_name from table_name order by column_name;
排序检索多列：select column_name_1, column_name_2,column_name_3 from table_name order by column_name_1, column_name_2;
注：对多个column_name_1值相同的行，按column_name_2进行排序。
默认排序顺序为升序（从A到Z），降序排序在待排序的列后加desc。 - 数据过滤
过滤数据：select column_name from table_name where 过滤条件；
where 子句操作符包括 =、 !=、<>（不等于）、< 、>、<=、>=、between and、is null、in、 not in。
多个过滤条件之间可以用and、or连接，and的优先级比or高，因此在多个过滤条件之间，可以通过加括号来消除由于优先级造成的语句歧义。
where column_name in (取值集合)，in 相较于or操作符的优点在于：更清楚直观；计算次序更容易管理；执行速度更快；可以嵌套其他select 语句； - 通配符进行搜索过滤
使用通配符进行搜索过滤：select column_name from table_name where column_name like '通配符与字符串组合';
通配符%表示任意字符出现任意次数，包括0次；通配符_表示任意字符（一次）。
通配符处理时间长，不要过度使用，且若非有必要，不要将其用于搜索模式的开始处。
like后若无通配符则对整个列值进行精确匹配，即使匹配的文本在列值中出现也不会匹配到，只有匹配的文本与列值完全相同才会返回。 - 正则表达式进行搜索
基本字符匹配： select column_name from table_name where column_name regexp '正则表达式'；
regexp在列值内进行搜索，只要匹配的文本在列值中出现即返回该行。
mysql 正则表达式匹配不区分大小写，若需要区分大小写，在正则表达式前加关键字binary。
串1|串2表示串1或者串2；
正则表达式中单个字符匹配：
[字符组]表示匹配该字符组内任意一个字符；[^字符组]表示匹配除字符组的字符外的任意字符；[0-9]表示所有数字字符；[a-z]表示匹配任意字母字符；匹配如.[]等特殊字符或换行等元字符时需要使用转义符\，mysql要求使用两个\来表示转义，其中mysql自己解释一个，正则表达式库解释另一个。
其他字符类：
[:alnum:] 任意字母和数字；[:alpha:] 任意字母字符；[:blank:] 空格和制表；[:cntrl:] ASCII控制字符；[:digit:]任意数字；[:print:] 任意可打印字符；[:graph:]同[:print:]；[:lower:]任意小写字母；[:upper:]任意大写字母；[:punct:]不再[:alnum:]和[:cntrl:]的任意字符；[:xdigit:]任意十六位进制数字；[:space:]包括空格在内的任意空白字符；
正则表达式匹配多个字符：
通过正则表达式中的重复元字符完成，*表示0个或多个匹配；+表示1个或多个匹配；？表示0个或1个匹配；{n}表示指定数目n个匹配；{n,}表示不少于n个匹配；{n,m}表示匹配数目的范围在n到m间；
正则表达式匹配指定位置文本：
正则表达式中还可通过定位符匹配指定位置的文本：^表示文本的开头，$表示文本的结尾；[[:<:]]表示词的开头；[[:>:]表示词的结尾。
注意^在[]内表示否定该集合，即匹配集合外的任意内容，其他情况下^表示定位文本的开头。通过^与$的组合使用可以使regexp达到like匹配整个串的效果，即^字符串$。
MYSQL数据处理
- 计算字段的创建使用
计算字段：从数据库中检索出转换、计算或格式化过的数据。
创建计算字段例子：select Concat(column_name1,column_name2) as new_column_name from table_name order by column_name1;
拼接字段函数：concat(colunm_name1,column_name2)，as关键字赋予字段别名。 - 数据处理函数
数据处理函数包括文本串处理函数、数值函数、日期和时间函数、系统信息函数。
（1）文本串处理函数包括left()、right()、length()、locate()、lower()、upper()、ltrim()、rtrim()、trim()、soundex()、substring()。
left(column_name)函数返回值最左的字符，right(column_name)返回值最右的字符，length(column_name)返回值的长度，locate(substring,column_name)返回子串在值中的位置，lower(column_name)将值变成小写，upper(column_name)将值变成大写，rtrim(column_name)函数去掉值右边的所有空格，ltrim(column_name)去掉值左边的所有空格，trim(column_name)去掉值左右两边的空格，soundex(column_name)=soundex(‘string')返回列中与string读音相似的行、substring(column_name,position,length)返回指定位置和指定长度的子串。
（2）日期和时间函数包括：adddate()、addtime()、curdate()、curtime()、date()、datediff()、date_add()、date_format()、day()、dayofweek()、hour()、minute()、month()、now()、second()、time()、year()。
（3）数值处理函数包括：abs()、cos()、exp()、mod()、pi()、rand()、sin()、sqrt()、tan()。
具体的函数用法可以百度或者使用help()查询。 - 数据汇总
聚集函数包括：avg()、count()、max()、min()、sum()。
avg()自动忽略null行且只适用于单个列。
count()函数两种用法：count(*)对表中行的数目进行计数，不管是否为Null值，count(column_name)对列中有值得行进行计数，忽略null行。min max的参数若为文本列，分别返回排序后的首行和最后一行。 - 数据分组
数据分组把数据分为多个逻辑组，以便能对每个组进行聚集计算。
创建分组例子：select column_name, count(*) as new_column_name from table_name group by column_name；
GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。
注：group by 后可跟多个列以对分组进行细分；
group by 后的列为检索列或有效的表达式，但不能为聚集函数；
group by 子句不能使用别名；SELECT语句中的每个列除了聚集计算语句都必须在GROUP BY子句中给出；
如果分组列中具有NULL值，则NULL将作为一个分组返回；
group by位置在where 子句后，order by子句前;
分组过滤
关键字having代替where，后接过滤条件。
SELECT语句中子句的顺序： from、where、group by、having、order by、limit。
MYSQL子查询
子查询：嵌套在其他查询中的查询。虽然MYSQL对子查询的数目不做限制，但实际中因为子查询并不是性能最优的方法，所以并不嵌套太多子查询。
联结
分解数据为多个表能够更有效地存储数据，更方便地处理数据，并且具有更好的可伸缩性。
对于存储在多个表中的数据，使用联结进行检索。
- 叉联结
select column_name1,column_name2,column_name3 from table_name1,table_name2 where table_name1.column_name4=table_name2.column_name4 order by column_name1, column_name2;
联结两个表时，当没有where限制条件时，对两个表做笛卡尔积，往往返回过量冗余数据。 - 内部联结（又名等值联结）
select column_name1,column_name2,column_name3 from table_name1 inner join table_name2 on table_name1.column_name4=table_name2.column_name4 ; - 自联结
使用表别名来对相同的表进行引用，值得注意的是表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机。
例：select new_name1.column_name1, new_name1.column_name2 from table_name as new_name1, table_name as new_name2 where new_name1.column_name3=new_name2.column_name3 and new_name2.column_name1 = 'string‘；
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。往往处理联结远比处理子查询快得多。 - 自然联结
表的联结至少有一个列出现在不止一个表中（被联结的列）。标准的联结返回所有数据，甚至相同的列多次出现。自然联结使每个列只返回一次。
通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来实现自然联结。
所有的内部联结都是自然联结。 - 外部联结
外部联结不仅返回表与表之间有联结的行，而且返回表中不存在联结的行。例如对客户的订单数进行统计时，包括那些未下单的客户。
例句：select table_name1.column_name1,table_name2.column_name2 from table_name1 left outer join table_name2 on table_name1.column_name3=table_name2.column_name3;
注：left outer join 返回outer join左边表的所有的行，right outer join 返回outer join 右边的表的所有行。
组合查询
组合查询又称并或复合查询，指执行多个查询并将结果作为单个查询结果集返回。包括两种基本情况：在单个查询中从不同的表返回类似结构的数据； 对单个表执行多个查询，按单个查询返回数据。
组合查询使用关键字union 将多个select子句连接起来。
多数情况下，组合相同表的多个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。
注： UNION中的每个查询必须包含相同的列、表达式或聚集函数；列数据类型必须兼容。
union语句会自动去除select子句返回的重复出现的行，可以通过union all 使重复的行也返回。
全文本搜索
MYSQL四种常见的数据引擎：InnoDB、MyISAM、MEMORY、Archive。MyISAM支持全文本搜索，而InnoDB不支持。
- 启用全文本搜索支持
创建表时通过FULLTEXT将待搜索的列指定为索引。FULLTEXT(column_name)，fulltext也可指定多个列，定义之后，MySQL自动维护该索引。
可以在创建表时指定FULLTEXT，或者先导入所有数据，然后再修改表，定义FULLTEXT。不要在导入数据时使用FULLTEXT。 - 全文本搜索
Match()和Against()执行全文本搜索，Match()指定被搜索的列，Against()指定要使用的搜索表达式。即在where子句 match(column_name) against(string)；match指定的列必须与fulltext指定的列完全一致，且顺序相同（若包含多个列）。 - 使用查询扩展
match(column_name) against(string with query expansion)能够返回可能相关的结果，即使不包含待查找的词。 - 布尔文本搜索
布尔文本搜索除了可以指定要匹配的词还可以指定要排斥的词、指定排列提示（指定某些词比其他词更重要，更重要的词等级更高）、表达式分组等。
match(column_name) against(string in boolean mode)
全文本布尔操作符包括+、-、>、<、()、、*、“”。+包含，-排除，>包含且赋予其更大的重要性，<包含且赋予其较小的重要性，()把词组成一个整体（子表达式）去被包含、排除等， 取消词的排序值，*词尾通配符，""定义一个短语去被包含排除等。
例：match(note_text) against('+safe +(<combination)' in boolean mode)
搜索匹配词safe和combination，降低后者的等级。 - 使用说明
具有3个或3个以下字符的短词被忽略且从索引中排除。
仅在MyISAM数据库引擎中支持全文本搜索。
少于3行的表进行全文本搜索不返回结果。
忽略词中的单引号，不具有词分隔符（包括汉语）的语言不支持全文本搜索。
MYSQL内建的非用词集和出现频率过高（超过50%）的词会被忽略。
数据插入
- 插入完整的行
insert into table_name values(value_1,....,value_n)，values给出的值必须按表中列的顺序逐一给出。对于没有值的列或者是自增的键，value应为null。这种做法由于表结构的变动会产生很多问题，因此不建议使用。
insert into table_name (column_name_1,.....,column_name_n) values(value_1,....,value_n)这种做法虽然语句繁琐，但不依赖于表中列的顺序。同时，给出的列忽略自增键之外，还可以省略允许为null值或给出默认值的列。
INSERT操作可能很耗时，而且它可能降低等待处理的SELECT语句的性能。可以通过在insert into之间加low_priority关键字降低Insert语句的优先级。这方法也适用于update 和delete语句。 - 插入多个行
单条INSERT语句后接多组值，每组值用一对圆括号括起来，用逗号分隔。 - 插入检索出的数据
把select语句返回的值插入表中。insert into table_name1 (column_name_1,.......,column_name_n) select column_name_1,......,column_name_n from table_name2; 为了避免自增键的重复语句中应忽略自增键，让系统自动赋值。
更新和删除数据
- 更新数据
update table_name set column_name = value where 过滤条件；更新多个列值时，用逗号隔开，即set column_name1=value1,column_name2=value2 where 过滤条件。
为了防止更新某个行或某些行失败导致整个update 操作被取消，可以在update 后加关键字ignore。 - 删除数据
delete from table_name where 过滤条件； - 注意
除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
保证每个表都有主键。
在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录。
使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。
创建和操纵表
- 创建表
可以通过交互管理式工具创建表或者通过mysql语句创建。
create table table_name(
column_name_1 type null或者not null,
........
column_name_n type null或者not null,
primary key (column_name)
)engine = InnoDB(或者其他引擎）;
作为主键的列必须为not null；默认列设置为null，即列值可为null。在not null 后加关键字default 指定默认值，默认值为常值，不可以为函数。
注：关于自增长的知识：
在字段定义末尾加关键字 AUTO_INCREMENT，具体的自增行为如下：（1）如果插入数据时字段指定为0、null或未指定值，把当前的AUTO_INCREMENT值填到自增字段（2）如果指定了具体的值（未出现过），则直接使用具体的值，假设，插入的值X，当前自增值Y，X<Y，自增值不变，X>=Y，就需要把当前自增值修改为新的自增值，从auto_increment_offset开始，以auto_increment_increment为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值。（3）如果待插入值已存在，则报冲突。
SELECT last_insert_id()可以获取当前自增主键值。
mysql引擎的区别单独讲。 - 更新表的定义
alter table table_name 对表的操作。
增加列：alter table table_name add column_name type;
删除列：alter table table_name drop column column_name;
定义外键（InnoDB引擎）：alter table table_name_1 add foreign key (column_name_1) references table_name_2(column_name_2);
对从表table_name1进行操作，将从表的column_name_1设置为外键，并将其值映射到主表table_name_2的主键column_name_2上。 - 删除整个表
drop table table_name; - 重命名表
rename table old_table_name to new_table_name;
使用视图
视图是虚拟的表。视图不包含表中应该有的任何列或数据，它包含的是一个SQL查询。
视图的应用：（1）重用SQL语句（2）简化复杂的SQL操作（3）使用表的组成部分而不是整个表（4）保护数据（5）更改数据格式和表示。
视图的规则：（1）命名必须唯一（2）创建视图必须具有足够的访问权限（3）视图不能索引，也不能有关联的触发器或默认值（4）ORDER BY可以用在视图中，但利用视图检索数据的SELECT语句中的ORDER BY，会覆盖视图中的ORDER BY。
视图的使用：
CREATE VIEW view_name as SQL子句；创建视图。
SHOW CREATE VIEW viewname；来查看创建的视图。
DROP VIEW viewname;删除视图。
可以先用DROP再用CREATE更新视图，也可以直接用CREATE ORREPLACE VIEW viewname as SQL子句。
视图的更新：
如果视图中不包含分组、联结、子查询、并、 聚集函数、DISTINCT、导出列。那么该视图是可更新的（即可以进行插入、更新、删除）。视图的更新其实是对其基表的更新。
使用存储过程
存储过程：为以后的使用而保存的一条或多条MySQL语句的集合。存储过程优点：简单、安全、高性能。缺点：编写复杂，编写存储过程要求具有足够的安全访问权限。
- 存储过程执行
call procedure_name(@parameter_1,....,@parameter_n); - 创建存储过程
create procedure procedure_name(IN/OUT/INOUT parameter_1 type,....IN/OUT/INOUT parameter_n type)
begin
SQL语句
end;
SQL语句中用declare variable_name type default default_value对变量进行声明，默认值可省略。
注释行前面加--。 - 删除存储过程
drop procedure procedure_name;
或者 drop procedure procedure_name if exists; - 存储过程参数
MYSQL参数有三种类型：IN（传递给存储过程）、 OUT（从存储过程传出）、 INOUT（对存储过程传入传出，调用时可传入值，调用过程中可修改值，也可以返回值）
存储过程不能通过一个参数返回多个行和列。 - 检查存储过程
show create procedure procedure_name;显示存储过程具体内容；
show procedure status; 显示所有存储过程属性，包括创建时间，创建者等；
show procedure status like 'procedure_name'；显示特定存储过程属性。
使用游标
游标（cursor）：存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。MYSQL游标只能用在存储过程（和函数）。
- 游标使用步骤
（1）使用前先声明。 （2）声明后，必须打开游标以供使用。（3）根据需要取出（检索）各行。❑ 结束游标使用，必须关闭游标。 - 声明游标
declare cursor_name cursor for select语句。 - 打开和关闭游标
open cursor_name;
close cursor_name;
如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它。 - 使用游标数据
fetch cursor_name into variable_name;
fetch语句检索当前游标到变量中（默认第一行）；
使用repeat 结构使游标依次读取所有行：
declare continue handler for sqlstate '02000' set done=1;
repeat
fetch cursor_name into variable_name;
sql语句；
until done end repeat;
首先定义了一个continue handler,在for条件满足的情况下执行set后的语句；done 是一个事先声明的默认值为0的变量；repeat语句一直重复直到done为真即为1结束重复，此时满足条件sqlstate为'02000‘，'02000'的sqlstate代表未找到，即此时游标找不到更多的行来循环执行语句。
使用触发器
触发器：MySQL响应删除、插入、更新语句而自动执行的一条MySQL语句。除了删除、插入、更新，其他语句不支持触发器。
- 声明触发器
create trigger trigger_name before(after) delete(insert、update) on table_name for each row 执行SQL语句;
在对表进行插入、更新、删除操作前（后），执行sql语句。
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。触发器仅在表中使用，视图不支持。
可以通过begin end结构执行多条sql语句 - 删除触发器
drop trigger trigger_name; - INSERT 触发器
在INSERT触发器代码内，通过引用一个名为NEW的虚拟表，访问被插入的行；
在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；通常BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。
对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。 - DELETE触发器
在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
OLD中的值全都是只读的，不能更新； - update触发器
在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
OLD中的值全都是只读的，不能更新。
事务处理管理
InnoDB支持事务处理管理，而MyISAM不支持。
事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。如果其中某行语句发生错误，则进行回退以恢复数据库到一个已知的安全状态。
事务处理关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
start transaction标志事务处理开始；
rollback; 回退
事务管理只能回退INSERT/DELETE/UPDATE操作，不能回退SELECT/DROP/CREATE操作。
普通的sql语句提交（写或保存）操作是自动进行的，而事务管理中不会，使用commit;进行显式的提交，commit仅在事务管理所有语句无误时提交； 当COMMIT或ROLLBACK语句执行后，事务会自动关闭；
复杂的事务处理需要部分提交或回退使用savepoint savepoint_name；语句来创建占位符，需要回退时使用rollback to savepoint_name；回退到该占位符，savepoint在执行rollback或者commit后自动释放。
除了事务管理外，还可以通过set autocommit = 0；使语句不自动提交。
安全管理
MySQL的访问控制和用户管理。MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。
需要获得所有用户账号列表时，可以访问名为mysql的MySQL数据库，具体语句：
use mysql;
select user from user;
创建用户：
create user 'user_name' identified by 'user_password';
用户改名：
rename user 'old_name' to ‘new_name';
删除用户
drop user 'user_name’；
设置访问权限：
grant 权限 on 数据库或表 to user_name;
新创建的用户没有任何权限，只能登陆数据库，不能看到数据，不能进行任何数据库操作。
权限关键字表
显示权限：show grants for ‘user_name’;
撤销权限：revoke 权限 on 数据库或表 from 'user_name';
若为整个数据库，用database_name.*,若为表，则用database_name.table_name;
一次授予或撤销多个权限，权限之间用逗号隔开。
更改密码： set password for user_name = Password('user_password');
如果不指定user，则为当前登录账户修改密码。
数据库维护
- 备份数据
（1）使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。
（2） 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
（3）可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。 - 数据库维护
ANALYZE TABLE table_name；检查表键是否正确
check table table_name option；发现并修复表。
option可选项包括CHANGED/EXTENDED/FAST/MEDIUM/QUICK。CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删除的链接并进行键检验，QUICK只进行快速扫描。
MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表。
一个表中删除大量数据，使用OPTIMIZE TABLE来收回所用的空间，优化表的性能。 - 查看日志文件
主要的日志文件包括错误日志、查询日志、二进制日志和缓慢查询日志。都位于data目录。
错误日志。它包含启动和关闭问题以及任意关键错误的细节。名为hostname.err。用--log-error命令行选项更改。
查询日志。它记录所有MySQL活动，在诊断问题时非常有用。通常名为hostname.log。用--log命令行选项更改。
二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。通常名为hostname-bin。用--log-bin命令行选项更改。
缓慢查询日志。此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log。用--log-slow-queries命令行选项更改。
使用FLUSH LOGS语句来刷新和重新开始所有日志文件。
网友评论