• 基本语句
  use test; --使用某一个数据库

DESCRIBE customers; --查看表的结构
show status; --查看数据库状态
show ERRORS; --结果为空
show grants; --结果如 GRANT ALL PRIVILEGES ON . TO '632'@'%' WITH GRANT OPTION

show tables; --列出当前数据库所有的表

• 基本的查找语句
  SELECT * FROM products; --查找语句

SELECT * FROM products limit 0,2; --分页,前2条数据（从 0开始）
SELECT * FROM products limit 2 OFFSET 1; --分页，前两条数据，2表示共2条记录，1表示重第一条记录开始（从1开始）

SELECT * FROM products ORDER BY prod_price DESC; --降序排序

• 简单查询条件
  SELECT * FROM products where prod_price BETWEEN 10 AND 20; -- 区间查找
  SELECT * FROM products where prod_price IN (10,20); -- in条件限制
  SELECT * FROM products where prod_price IS NOT NULL; -- 不为 NULL的记录

• 通配符
  通配符查询时间更长，不要在搜索模式的开始处使用通配符，这是最慢的
  _ 匹配单个字符 % 匹配任意字符
  SELECT * FROM products where prod_name LIKE '_e%'; --查找第二个字母为e的产品名

• 正则表达式
  使用关键字 REGEXP，MYSQL只是实现了正则表达式的一部分
  注意：REGEXP和LIKE的区别在于，like是匹配整个列，完全匹配，而REGEXP匹配的是子串，如果有子串符合就匹配成功。
  如果要使用REGEXP匹配像LIKE的效果，在匹配串前后分别加上 '^' 和 '$'即可。
  --匹配子串，'.'表示任意一个字符
  SELECT * FROM products where prod_name REGEXP '.000';

--或OR 匹配
SELECT * FROM products where prod_name REGEXP BINARY '1000|2000';

--默认匹配是不区分大小写，需要区分使用 binary
SELECT * FROM products where prod_name REGEXP BINARY 'JET';

--[123]表示 1或2或3。 1或2或3连接一个任意字符再加上3个0
SELECT * FROM products where prod_name REGEXP BINARY '[123]000';

--效果和上面的一样，1或2或3连接一个任意字符再加上3个0
SELECT * FROM products where prod_name REGEXP BINARY '(1|2|3)000';

--范围匹配，上面的[123]可以写成[1-3]，当然字母也可以[a-z]
SELECT * FROM products where prod_name REGEXP BINARY '[1-3]000';

--^表示在[]里面表示的否定，除这些之外的意思。下面是是指包含除2000和3000的列
SELECT * FROM products where prod_name REGEXP BINARY '[^2-3]000';

--特殊字符匹配需要加上 \,例如 \.匹配. ,\-匹配-
SELECT * FROM products where prod_name REGEXP BINARY '\.';

--还有一些通用的字符集的特殊符号
如 [:alnum:],[:alpha:],[:digit:],[:xdigit:],[:lower:],[:upper:]
SELECT * FROM products where prod_name REGEXP '[[:digit:]]000';

--匹配多个实例
* 0-n, + 1-n, ? 0-1, {n} 指定n个， {n,} , {n,m} n-m个
SELECT * FROM products where prod_name REGEXP '[[:digit:]]+'; --包含一个数字的
SELECT * FROM products where prod_name REGEXP '[[:digit:]]{3}'; --至少3个数字的

--不涉及数据库记录，用字符串测试正则表达式。返回0不匹配，1匹配
SELECT 'hello' REGEXP '[0-9]'

• 拼接多个列
  MYSQL使用 concat函数拼接，但是其他数据库可能使用+或者||等符号，AS使用别名
  SELECT CONCAT(prod_id,'(',vend_id,')') AS id_vend, prod_name FROM products;

• 计算
  基本的+-/都可以直接在select使用
  SELECT products., prod_price +1 FROM products;

• MYSQL函数使用

  • 文本处理函数

函数有很多如left(),right(),locate(),substring()
--转为大写字母
SELECT UPPER(prod_name) FROM products;

--子串，计数从1开始，从第二个字符开始，截取长度为3
SELECT prod_name, SUBSTRING(prod_name,2,3) FROM products;

--返回remark字段后面2个字符，发音类似函数 soundex，可以发现 返回了 jianye的列，因为mysql认为 jianye和jienye读音相似
SELECT * FROM products WHERE SOUNDEX(prod_name) = SOUNDEX('1 ton anvil'); --会发现出现一些相同读音的记录

* 日期和时间处理

date() 返回日期时间的日期部分，同理time返回的是时间部分，day()返回日期的天数，还有year,month等
SELECT CURDATE(),date(note_date),time(note_date),day(note_date) from productnotes;

查找 8月的记录
SELECT CURDATE(),date(note_date),time(note_date),day(note_date) from productnotes where month(note_date) = 8;

* 数值处理函数

对于各个DBMS来说比较统一的函数
常用函数有 abs(), cos(),exp(),mod(),pi(),rand(),rand(种子),sin(),sqrt(),tan()等
select pi(), mod(9,3),exp(2),rand(),rand(10);

• 聚集函数 aggregate function
  avg(),count(),max(),min(),sum(),除了这些函数，还有一些标准偏差聚类函数
  --avg(id) 返回id的平均数，只能统计一列，而且会忽略NULL值（max()，min(),sum()也一样
  SELECT AVG(ALL prod_price) FROM products; --ALL是默认可以不加
  SELECT AVG(DISTINCT prod_price) FROM products; --只包含不同的值平均

--avg，sum等 里面可以进行计算合并,可以多列运算之后调用函数
SELECT AVG(prod_price * 2) FROM products;

--max(),min() 如果用在文本就返回默认排序中，对应的最后一行和第一行的该列数据
SELECT MIN(prod_name), MAX(prod_name) FROM products; ORDER BY prod_name

--count()，方法有两种用法count() 统计行数，或者count(column)统计非NULL的行数。
SELECT COUNT() FROM products; --返回记录数，总行数
SELECT COUNT(prod_name) FROM products; --返回prod_name不为NULL的数目

--还可以多个聚集函数结合使用
SELECT AVG(DISTINCT prod_price),SUM(prod_price) FROM products;

• 分组数据
  • 在select中使用表达式，则Gruop by中指定相同的表达式

  除聚集计算外，select中的每个列都要在group by中给出
  NULL值也会作为一个分组返回
  SELECT vend_id, COUNT(*) FROM products GROUP BY vend_id; --统计wend_id的products数量

  • 过滤分组 having

having 支持where的所有操作符，可以同时使用。区别在于where过滤行（分组前过滤），having过滤分组（分组后过滤）
SELECT vend_id, COUNT() FROM products GROUP BY vend_id HAVING COUNT() > 2;

使用order by 进行分组排序
SELECT vend_id, COUNT() FROM products GROUP BY vend_id HAVING COUNT() > 2 ORDER BY vend_id desc;

• 子查询
  MYSQL4.1后引入，嵌套的数量没有限制，但性能会有所影响
  推荐写法是先保证内层查询正确后再编写外层查询
  SELECT * FROM orders WHERE order_num IN (
  SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

--作为计算字段的子查询，返回用户的订单数量
SELECT cust_name,
(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id )
FROM customers ;

• 联结表
  联结多少个表没有限定，但是关联太多表会影响性能
  --等值联结，返回笛卡尔积结果，假设两个表的行是m和n则返回m x n的记录，每一行两两联结
  SELECT * FROM vendors, products;

--内部联结INNER JOIN
SELECT * FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

• 高级联结
  • 自联结

--联结同样可以使用聚集函数
--自联结，顾名思义就是联结同一个表
--譬如要找出生产‘DTNTR’产品的厂商的其他产品，我们可以使用子查询
SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products where prod_id = 'DTNTR');

--也可以使用自联结，注意使用别名 AS，区分不同的表
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'

* 外部联结

--左联结 LEFT OUTER JOIN
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

--右联结 RIGHT OUTER JOIN
SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

• 组合查询 UNION
  --组合两个查询结果，ALL 表示不过滤重复，默认会过滤重复的行
  SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <=5
  UNION ALL
  SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);
  order by排序，只能使用一条order by，且必须出现在最后的一条查询语句中
  SELECT vend_id, prod_id, prod_price, prod_name FROM products WHERE prod_price <=5
  UNION
  SELECT vend_id, prod_id, prod_price,prod_name FROM products WHERE vend_id IN (1001,1002)
  ORDER BY vend_id, prod_price ;

• 全文搜索
  InnoDB不支持全文搜索，MyISAM支持
  使用的时候主要该表设置为MyISAM引擎，然后设置字段为 FULLTXT
  建表语句大概如此
  CREATE TABLE productnotes
  (
  note_id int NOT NULL AUTO_INCREMENT,
  note_text text NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
  ) ENGINE=MyISAM;
  主要函数 Match() 指定搜索的列, Against()指定搜索的表达式。默认不区分大小写，可以使用Binary
  SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('rabbit');

• 数据插入
  INSERT INTO customers(cust_name) VALUES ('jam');
  --连续插入多条记录
  INSERT INTO customers(cust_name) VALUES ('jam'), ('haha');
  插入检索出来的记录
  INSERT INTO customers(cust_name) SELECT vendors.vend_name FROM vendors LIMIT 2 OFFSET 1; --插入两条记录

• 更新数据
  UPDATE customers SET cust_email = 'new@111.com' WHERE cust_id = '10005';
  UPDATE customers SET cust_email = 'new@111.com', cust_city = 'China' WHERE cust_id = '10005'; --更新多列
  --默认有错会撤销所有的操作，加上IGNORE可以忽略个别错误
  UPDATE IGNORE customers SET cust_email = NULL, cust_city = 'USA' WHERE cust_id = '10005'; --set为NULL清空某一列

• 删除数据
  DELETE FROM customers WHERE cust_id = '10013'; --请注意添加条件，不然会清空整个表

• 表创建
  基本的建表语句 （IF NOT EXISTS 可选）
  PRIMARY KEY() 指定主键
  DEFAULT 后面跟默认值
  AUTO_INCREMENT 表示自增，该列必须被索引（例如设置它为主键，主键默认有索引）
  ENGINE 表引擎InnoDB，支持事务，但不支持FULLTEXT；MyISAM，不支持事务，但支持FULLTEXT；MEMORY 内存版的MyISAM，数据在内存，性能高。
  CREATE TABLE IF NOT EXISTS customers
  (
  cust_id int NOT NULL AUTO_INCREMENT,
  cust_name char(50) NOT NULL ,
  cust_email char(255) NULL DEFAULT 'haha',
  PRIMARY KEY (cust_id)
  ) ENGINE=InnoDB;

--增加外键，建立表关联
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
更新表 ALERT TABLE
ALTER TABLE vendors ADD vend_phone CHAR(20); --增加一列
ALTER TABLE vendors DROP vend_phone; --删除一列

• 删除表
  DROP TABLE cust;

• 重命名表
  RENAME TABLE customers to customers1;
  --多个多重命名
  RENAME TABLE customers to customers1, orders to orders1;

• 视图
  MYSQL 5添加了视图的支持，使用视图的好处有
  1.重用SQL语句，视图本来就是有一些SQL组成

  1. 简化复杂的SQL操作, 隐藏实现细节
  2. 使用表的组成部分而不是使用整个表
  3. 保护数。可以让用户不能访问特定的表，但是可以通过视图访问部分数据
  4. 更改数据格式和表示

性能限制：因为视图相当于嵌套了查询。
注意：视图名唯一；必须有足够的权限；视图可以嵌套；视图有ORDER BY，但是select 检索该视图时，视图的ORDER BY会被覆盖；视图不能索引
--创建视图
CREATE VIEW myview AS SELECT * FROM customers ;

--像表一样使用，select的约束全部适用
SELECT * FROM myview;

--可以使用计算字段
CREATE VIEW OrderItemsExpanded AS
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20009;

• 存储过程
  优点： 简单、安全、高性能。
  简化复杂的操作。
  封装一系列的SQL保证数据的安全的，简化变动的管理。
  存储过程通常以编译过的形式存储，提高性能。
  缺点： 不同DBMS中的存储过程语法有所不同。
  相对复杂，容易出错，需要更多的测试。
  --创建存储过程
  --DELIMITER $$ 表示重新定义结束符为$$,因为在命令行环境中我们的语句也是;号结束的，与存储过程的结束符冲突了，在图形界面环境则没这个问题
  --创建前先删除原来的，并且判断是存在才删除

DELIMITER $$
DROP PROCEDURE IF EXISTS MailingListCount;

CREATE PROCEDURE MailingListCount (IN src int, OUT s DOUBLE)
BEGIN
IF src = 1 THEN
SELECT COUNT(*) into s FROM Customers;
ELSEIF src = 2 THEN
SELECT pi() into s;
ELSE
SELECT pi()*pi() into s;
END IF;
END $$
DELIMITER ;

--调用,s为变量，mysql所有的变量都是以@开头，set设置变量的值
set @src = 3;
call MailingListCount(@src, @s);
select @s;
列出所有的存储过程
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE '%M%'; --支持条件查找
查看对应的存储过程
SHOW CREATE PROCEDURE MailingListCount;

• 游标
  MYSQL游标在MYSQL5添加，而且只能用在存储过程中
  明确步骤：使用前必须先声明（定义）
  声明后，必须打开游标以供使用，这个过程会把定义的语句的检索出来
  填有数据的游标，根据需要检索出各行
  结束时必须关闭游标

DROP PROCEDURE IF EXISTS testCursor;
CREATE PROCEDURE testCursor (IN src int, OUT s DOUBLE)
BEGIN
DECLARE omCursor CURSOR
FOR
SELECT order_num FROM orders;

    DECLARE o INT; -- 定义变量

    OPEN omCursor; -- 打开游标
       FETCH omCursor INTO o; -- 获取单行游标数据
    CLOSE omCursor; -- 关闭游标

END;
循环遍历数据
存储过程部分省略，只给出变化的部分，从 DECLARE o INT; 后 面开始
-- DECLARE CONTINUE HANDLER 相当于终止条件 ，declare有特定的次序
-- 02000是一个未找到的条件，当SQLSTATE找不到行是就设置done=1，结束循环
DECLARE CONTINUE HANDLER FOR SQLSTATE '0200' SET done = 1;

    OPEN omCursor;        -- 打开游标
        REPEAT
            FETCH omCursor INTO o; -- 遍历游标数据
            -- 这里可以对o进行处理，可以调用存储过程之类的
        UNTIL done END REPEAT;
    CLOSE omCursor;-- 关闭游标

• 触发器
  在某些改动的前后自动执行一些语句，例如这些改动 DELETE，INSERT，UPDATE
  用于保持数据的完整性或记录数据库操作信息方面，触发器不能够被直接调用，只能够在某些事件发生时被触发
  每个表每个事件只能存在一个触发器，因此每个表最多6个触发器
  触发器需要4条信息：触发器名；触发器关联的表；触发器相应的活动；触发器何时执行(before还是 after)
  PS:虽然可以不同表有相同名字的触发器，但是不推荐
  --创建触发器，在products表插入后 返回Product added
  --书本原句报错 CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'
  --查找找问题后发现，必须要加变量以返回数据
  CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @arg;

测试，可以发现arg=Product added
INSERT INTO products (prod_id,vend_id,prod_name,prod_price) VALUES ('1112',1001,'newPP',23);
SELECT @arg;
删除触发器
DROP TRIGGER newproduct;

*  instert触发器

如果我们需要获取新插入的数据的主键ID，是自增的。触发器提供一个NEW虚拟表，访问新插入的行
update after 也有 new关键字提供访问新的记录
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @arg;

* delete触发器

delete 可以提供 OLD 虚拟表，访问删除行的数据

* update触发器

可以有 OLD也有 NEW 关键字

• 事务
  是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
  并不是所有的数据库引擎都支持事务，例如MyISQM就不支持事务
  事务拥有相关属性（ACID）：
  原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
  隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。隔离又有四个级别（未提交读： 无读锁，允许“脏读”，事务可以看到其他事务“尚未提交”的修改；提交读： 读锁在SELECT操作完成后马上释放；可重复读： 在同一个事务内的查询都是事务开始时刻一致的， 读写锁一直保持到事务结束，读写不阻塞；可序列化： 读写相互都会阻塞，同时只允许一个事务提交。）
  隔离级别
  脏读 dirty reads

不可重复读幻影读未提交读可能发生可能发生可能发生提交读-可能发生可能发生可重复读--可能发生可序列化--

不可重复读：在一次事务中，当一行数据获取两遍得到不同的结果表示发生了“不可重复读”
InnoDB 默认是不可重复读的隔离级别
持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。
基本的使用方式
START TRANSACTION;
INSERT INTO orders (order_date,cust_id) VALUES (CURDATE(),10001);
COMMIT; --提交结束事务，不能载回退
roolback 回退
只能回退instert,update, delete，事务中可以使用，但是不能回退 create和drop
START TRANSACTION;
INSERT INTO orders (order_date,cust_id) VALUES (CURDATE(),10001);
ROLLBACK; --rollback后发现没有插入数据
使用保留点，因为简单的rollback和commit是整个事务的，不够灵活，保留点可以rollback到指定保留点
没有保留点的数量限制，所以尽量多保留点使操作更灵活
START TRANSACTION;

INSERT INTO orders (order_date,cust_id) VALUES (CURDATE(),10002);
SAVEPOINT insert1;
INSERT INTO orders (order_date,cust_id) VALUES (CURDATE(),10003);
SAVEPOINT insert2;
ROLLBACK TO insert1; -- rollback到指定保留点
RELEASE SAVEPOINT insert2; -- MYSQL5以后可以手动释放保留点，commit自动释放还原点
COMMIT;

默认的SQL语句都是自动提交的
SET autocommit = 0; --设置为不自动提交，然后可以使用rollback回退或者commit提交

• 字符集，全球化和本地化
  查看支持的字符集（校对集可以理解为排序规则等）如：
  ci: case insensitive，大小写不敏感，不区分大小写比较
  cs: case sensitive，大小写敏感，区分大小写比较
  bin: binary,二进制，使用二进制比较
  SHOW CHARACTER SET; (show charset;) --可用字符和默认校对，如 utf8
  SHOW COLLATION; --支持校对的完整列表，如 utf8_general_ci
  服务器默认字符集 -- 数据库默认字符集 -- 表默认字符集 -- 甚至到列默认字符集 ---某个级别没有指定，那么继承上级。
  查看数据库当前的字符集和校对规则
  show variables like '%character%';
  show variables like '%collation%';

--查看表的校对规则
show table status from db_name like '%table_name%' ;

--查看列的校对规则
show full columns from table_name;

注意获取长度的区别
select length('你'); /结果是3，3个字节，数据库采用的utf8编码/
select char_length('你'); /结果是1,1个字符/

order by 中指定 校对集 collate
SELECT * FROM customers ORDER BY cust_name COLLATE utf8_unicode_ci;

• 安全管理
  查看用户
  USE mysql;
  SELECT user FROM user;
  创建用户 ben,密码是haha
  CREATE USER ben IDENTIFIED BY 'haha';
  重命名用户
  RENAME USER ben to ben1;
  删除用户
  DROP USER ben1;
  DROP USER IF EXISTS ben1; --可以先判断，一面出错

权限
SHOW GRANTS FOR ben; --查看ben的权限
分配权限GRANT（撤销权限为 REVOKE）
其中 整个服务器：GRANT ALL（REVOKE）, 整个数据库： ON database.* , 特定表 ON database.table
权限较多，查对应的权限表
GRANT SELECT ON test.* to ben; --分配数据库test所有表的select权限给 ben

更改口令
SET PASSWORD FOR ben = PASSWORD('hahaha'); --PASSWORD('hahaha')表示加密

• 数据库维护
  ANALYZE TABLE orders; --分析表是否正确
  CHECK TABLE orders;

导出
mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

导入
use 数据库
--set names utf8; --设置编码
source 导入的文件名

日志
位于 data目录下。一般日志名为hostname.err ，可以使用 --log-error 修改名字

• 性能建议
  索引，检索快，但是其他操作会变慢
  正确的select
  减少嵌套关联
  可以尝试 UNION 代替 复杂的一系列OR
  like 很慢
  以上每个建议都可能在某个条件下打破