MySQL必知必会

数据检索

LIMIT使用，第一个参数为开始位置，第二个参数为要检索的行数，例如 SELECT * FROMcityLIMIT 1,1
where子句中，and的优先级高于or
like 通配符
- - 只匹配单个字符
- %配置多个字符
使用正则表达式 REGEXP
- 搜索两个串之一，匹配 | 例如SELECT * FROMcityWHERE CountryCode REGEXP 'ABW|AFG';
- 匹配几个字符之一，使用[123],[^123] 例如SELECT * FROMcityWHERE CountryCode REGEXP '[AB]FG'
- 匹配范围 [1-3] [a-z]
- 匹配特殊字符使用\\ 作为前导；
- 匹配字符类[:字符类:]，匹配多个实例（重复元字符）
- 定位符（定位元字符）
- 参考链接

排序

order by 条件 (desc) ，如果使用LIMIT，必须放在Order By 之后

创建计算字段、文本处理函数

拼接串Concat()
出去右边所有的空格 RTrim()
算数运算操作符 + - * /
转大写 Upper() 等
数值处理函数等
时间处理函数日期格式必须为yyyy-mm-dd

聚集函数

AVG() : 返回某列的平均值（忽略值为null的行）
COUNT() : 返回某列的行数（具体列为参数时，忽略值为null的行）
MAX() : 返回某列的最大值（忽略值为null的行）
MIN() : 返回某列的最小值（忽略值为null的行）
SUM() : 返回某列值之和（忽略值为null的行）

分组

GROUP BY 规则
过滤分组 HAVING (数据分组后进行过滤) 例如SELECT CountryCode,count(*) FROMcityGROUP BY CountryCode HAVING COUNT(*) >=20
一般在使用GROUP BY子句时，也应该给出ORDER BY子句，这是保证数据正确排序的唯一方式；
WHERE... GROUP BY ... HAVING ... ORDER BY ...

子查询

SELECT * from table WHERE 列 in (SELECT ....)
作为计算字段使用子查询 例如SELECT CountryCode,(SELECT count(*) fromcountryWHEREcountry.Code=city.CountryCode) as countrys FROMcity`

联接（内连接，左外连接，右外连接）

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。 交集
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

组合查询（union）

在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询，按单个查询返回数据
- 重复的行会被自动取消
- union之后进行ORFER BY 排序，必须出现在最后一条SELECT语句之后；

全文本搜索 (FULLTEST,Match,Against)

并非所有的的引擎都支持全文本搜索，最常用的引擎为MyISAM和InnoDB，前者支持，而后者不支持；
性能------通配符和正则表达式通常要求MySQL 尝试匹配表的所有行
使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行；
使用全文本搜索，必须索引被搜索的列，一般在创建表时启动全文本搜索， 创建表时，通过FULLTEXT(列名...)进行索引，MySQL会自动维持该索引；
使用两个函数执行全文本搜索，Match()：指定被搜索的列 Against() ：指定要使用的搜索表达式；例如：SELECT * from表名WHERE MATCH(字段名) AGAINST('qqaa');
使用查询扩展实例 AGAINST('qqaa' WITH QUERY EXPANSION);
全文本布尔操作符
使用全文本搜索注意点：
- 注意 MATCH (字段…) 里面的值必须是前面建立全文索引的字段不能少
- mysql 默认支持全文检索的字符长度是4
- 忽略词中的单引号例如do’nt 索引为dont

插入数据

INSERT into 表名(列名) values (列数据),(),(); 插入单条或多条
插入检索出的数据 例如INSERT INTO tableName(COLUMN) SELECT (COLUMN) FROM tableName;

更新数据、删除数据

UPDATE IGNORE table_name set column_name = value WHERE .... 加IGNORE 忽略更新失败；
删除某列的值，可把它设置为null，set column_name = null;
DELETE FROM table_name WHERE ... 删除数据
删除整张表，可使用TRUNCATE tabel 速度更快，删除原来的表并重新创建一张表；

创建表、操作表

每张表只允许一个AUTO_INCREMENT，而且它必须被索引。
DEFAULT 指定默认值
ENGINE=InnoDB（引擎类型），引擎类型可以混用
- InnoDB 一个可靠的事务处理引擎，不支持全文本搜索；
- MEMORY功能上等同于MyISAM ,但是数据存储在内存中，速度很快；
- MyISAM是一个性能极高的引擎，它支持全文本搜索；
更新表
- 添加列 ALTER TABLE table_name add COLUMN_NAME VARCHAR(20);
- 删除列 ALTER TABLE table_name drop COLUMN_NAME;
- 添加外键约束 alter table 表名 add constraint外键名称foreign key （外键字段）references 主表（主键）属性）；
  - 参数说明：
    - CONSTRAINT ：用于设置外键约束名称，可以省略
    - FOREIGN KEY：外键设置，用于指定外键字段
    - REFERENCES：主表及主键设置，用于指定主表和主键
  - 属性说明：
    - CASCADE：主表删除或修改记录时，从表也会对关联记录的外键字段进行修改。
    - RESTRICT：删除或修改主表记录，子表中若有关联记录，则不允许主表删除或修改。
    - SET NULL：主表删除或修改主表记录时，从表会将关联记录的外键字段设为null。
    - ON UPDATE CASCADE：主表修改记录时，从表关联记录的外键字段也会修改。（将CASCADE改为RESTRICT，意思相反）
    - ON DELETE CASCADE：主表删除记录时，从表关联记录的外键字段也会删除。（将CASCADE改为RESTRICT，意思相反)
删除表 DROP TABLE table_name;
重命名表 RENAME TABLE table_name to table_name2;

视图

视图是虚拟的表，视图只包含使用时动态检索数据的查询，视图提供了一种MySQL的SELECT语句层次的封装
视图的优点：
- 重用SQL语句
- 简化复杂的SQL
- 使用表的组成部分而不是整张表
- 保护数据，可以给用户授予表的特定部分的访问权限，而不是整个表的访问权限
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据；
每次使用视图时，都必须处理查询执行时所需的任一个检索，如果你用了多个连接和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降的很厉害；
视图的规则和限制
- 与表一样，视图必须唯一命名；
- 对于可以创建的视图数目没有限制
- 必须有足够的访问权限
- 视图可以嵌套
- ORDER BY也可用在视图中，如果查询该视图的语句中也含有ORDER BY，那么视图中的ORDER BY 将被覆盖；
- 视图不能索引，也不能有关联的触发器或默认值；
- 视图可以和表一起使用；
使用视图
- 创建视图：CREATE VIEW
- 显示视图：SHOW CREATE VEW viewname
- 删除视图：DROP VIEW viewname
- 更新视图: CREATE ON REPLACE VIEW
  例如：
  CREATE VIEW name_info_view AS SELECT * FROM nameinfo WHERE MATCH ( name_info ) AGAINST ( 'qqaa' WITH QUERY EXPANSION );
- 如果从视图检索数据时使用了一个where子句，则两组子句（一组在视图中，另一组是传递给视图的）将会自动组合。
- 通常，**视图是可更新的，更新一个视图将更新其基表。**如果你对一个视图增加或删除行，实际上就是对其基表增加或删除行；但是，并非是所有视图都是可更新的，如果视图中有（分组，连接，子查询，并，聚集函数，DISTINCT，导出计算列），便不可更新；

存储过程

存储过程：为了以后的使用而保存的一条或多条MySQL语句的集合；
使用存储过程比使用单独的SQL语句要快
MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL
删除存储过程：DROP PROCEDURE name IF EXISTS;
存储过程可接受参数
- IN 传递给存储过程使用
- OUT 从存储过程中传出
- INOUT 对存储过程传入并传出

CREATE PROCEDURE demo_search(
IN a1 VARCHAR(255),
IN flag BOOLEAN,
OUT a2 INT,
INOUT a3 INT
)COMMIT '在状态中显示'
BEGIN
	-- 查询结果赋值给a2
	SELECT COUNT(*) from `nameinfo` WHERE name_info like CONCAT(a1,'%') INTO a2;
		-- Declare 定义临时变量/或赋值默认值
	DECLARE total1 INT DEFAULT 6;
	-- 执行 判断
	IF flag THEN
		-- true 执行
		select total1 INTO a3;
	END IF;	
END


-- 使用存储过程
CALL demo_search('a',TRUE,@a2,@a3);
SELECT @a2,@a3;
-- 显示
show CREATE PROCEDURE demo_search;

游标

游标是一种存储在MySQL服务器上的数据库查询，MySQL游标只能用于存储过程（和函数）
使用游标涉及几个明确的步骤
- 使用游标之前，必须声明定义它，这个过程实际上并没有检索数据，它只是定义要使用的SELECT语句。
- 一旦声明后，必须打开游标以供使用（这个过程用前面定义的SELECT语句吧数据实际检索出来）
- 对于填有数据的游标，根据需要取出（检索）各行
- 在结束游标使用后，必须关闭游标 ，没有关闭的话，在END语句会自动关闭；
游标用DECLARE语句来创建


CREATE PROCEDURE youbao_demo(
OUT a int
)
BEGIN
	-- 创建用于接收游标值的变量
	DECLARE info VARCHAR(255);
	DECLARE total int DEFAULT 0;
	-- 游标结束时的标志
	DECLARE done int DEFAULT 0;
	
	-- 变量声明要放在定义游标的前面
	-- 定义游标 游标为中文时需要给变量指定 字符集为utf8
	DECLARE number CURSOR 
	FOR 
	SELECT name_info from `nameinfo`;
	
	-- 指定游标循环结束时的返回值
	DECLARE CONTINUE HANDLER FOR not FOUND set done = 1;
	
	-- 打开游标
	OPEN number;
	-- 循环
	REPEAT
		-- 处理
		FETCH number into info;
		set total = total+1;
	UNTIL done END REPEAT;
	-- 关闭游标
	CLOSE number;
	SELECT total into a;
END

-- 调用
CALL youbao_demo(@a);
SELECT @a;

触发器

触发器是MySQL响应Delete、Insert、Update语句而自动执行的一条MySQL语句（或位于BEGIN和END之间的一组语句）
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;
创建触发器时，需要给出4条信息
- 唯一的触发器名；
- 触发器关联的表；
- 触发器应该响应的活动（DELETE、INSERT或UPDATE）
- 触发器何时执行（处理之前或之后）
删除触发器 DROP TRIGGER 触发器名；
使用触发器
- INSERT触发器
  - 在INSERT触发器代码内，可引用一个名为NEW虚拟表，访问被插入的行；
  - 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
  - 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值；
- DELETE触发器
  - 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；
  - OLD 中的值全都是只读的，不能更新；
- UPDATE触发器
  - 修改前的数据 ,用old来表示, old.列名引用被修改之前行中的值
  - 修改后的数据,用new 来表示, new.列名引用被修改之后行中的值

CREATE TRIGGER newrowinsertafter
AFTER INSERT ON nameinfo
FOR EACH ROW 
BEGIN
	-- 往另外一张表插入数据时间
	INSERT into ddd VALUES(now());
end


-- 已经不支持 触发器带返回值 ，以下是带返回值的一种写法 
CREATE TRIGGER newrowinsertafter
AFTER INSERT ON nameinfo
FOR EACH ROW 
BEGIN
	-- 往另外一张表插入数据时间
	select NEW.name_info INTO @asd;
end
-- 两句一块执行
INSERT into nameinfo(name_info) VALUES ('aaa');
SELECT @asd;

管理事务处理

术语：
- 事务（transaction）：一组SQL
- 回退（rollback）: 撤销指定SQL语句
- 提交（commit）：将未存储的SQL语句的结果写入数据库表
- 保留点（savepoint）：指事务中设置的临时占位符，可以对它发布回退（与回退整个事务不同）；
设置自动提交 Set autocommit =0 (不自动提交);

START TRANSACTION;
-- 执行一组SQL

-- 设置保留点
SAVEPOINT save1;
-- 执行一组SQL
-- 回退到指定保留点
ROLLBACK TO save1;

-- 回退
-- ROLLBACK;
-- 提交
COMMIT;

全球化和本地化

术语：
- 字符集：为字母和符号的集合
- 编码：某个字符集成员的内部表示
- 校对：为规定字符如何比较的指令

-- 显示字符集
show CHARACTER set;	
-- 显示校对
SHOW COLLATION;

安全管理

考虑内容
- 多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表；
- 某些用户需要读表，但可能不需要更新表；
- 你可能想允许用户添加数据，但不允许他们删除数据；
- 某些用户（管理员）可能需要处理用户账户的权限，但多数用户不需要。
- 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据
- 你可能想根据用户登录的地点限制对某些功能的访问

数据库维护

改善性能

-- show variables    查看系统参数，系统默认设置或者dba调整优化后的参数，静态的。可以通过set或者修改my.cnf配置文件修改
SHOW VARIABLES;

-- show status    查看系统运行的实时状态，便于dba查看mysql当前运行的状态，做出相应优化，动态的，不可认为修改，只能系统自动update
SHOW STATUS;
-- 显示哪些线程正在运行。通过Kill命令终结某个特定的进程；
SHOW PROCESSLIST;