cmd 登录

mysql -h localhost -u root -p

SHOW语句

返回可用数据库列表
show databases;

返回当前选择的数据库内可用表的列表
show tables;

显示表中所有的列（xxx：表名）
show columns from xxx; or describe xxx;

显示服务器状态信息
show status;

显示创建特定数据库
show create database xxx;

显示创建特定表
show create table xxx;

显示授予用户(所有用户或特定用户)的安全权限
show grants;

显示服务器错误消息
show errors;

显示服务器警告消息
show warnings;

选择数据库（xxx：数据库名）
use xxx;

SELECT语句

检索表中的列 SELECT column... FROM table

SELECT username FROM t_user;
SELECT username, age FROM t_user;
SELECT * FROM user;

DISTINCT 去除列中的重复值（必须放在列名的前面），一般用来查询不重复的字段的条数(count(destinct username))，如果要查询不重复的记录，用group by

SELECT DISTINCT addr FROM t_user;

如果这样写，mysql会认为需要过滤掉age和addr都重复的的记录
SELECT DISTINCT age, addr FROM t_user;

LIMIT限制查询结果

SELECT username FROM t_user LIMIT 2;

表示从第三行开始返回一行
SELECT username FROM t_user LIMIT 2,1;

跟上面的一样
SELECT username FROM t_user LIMIT 1 OFFSET 2;

使用完全限定的表名和列名（t_user为表名 test_daily为数据库名）

SELECT t_user.username FROM test_daily.t_user;

ORDER BY排序检索数据（用非检索的列排序数据是完全合法的）

SELECT username FROM t_user ORDER BY username;
多个列排序时按顺序进行，如果username都是唯一，则不会按age排序
SELECT username, age FROM t_user ORDER BY username, age;

指定排序方向 升序 ASC（默认）和降序`DESC （只应用到直接位于其前面的列名）
SELECT username, age FROM t_user ORDER BY username DESC, age;

使用 ORDER BY 和 LIMIT 组合（order by必须位于from之后，limit必须位于order by之后）

例：找出年龄最大的前两个人
SELECT username, age FROM t_user ORDER BY age DESC LIMIT 2;

过滤数据 WHERE

SELECT username, age FROM t_user WHERE age = 10;
默认不区分大小写
SELECT username, age, addr FROM t_user WHERE addr = 'beijing';

空值检查 IS NULL

SELECT username, age, addr FROM t_user WHERE addr IS NULL;

操作符 AND 和 OR（and优先级高于or，所以在组合使用时优先执行and）

SELECT username, age , addr FROM t_user WHERE age = 20 AND addr = 'beijing';
SELECT username, age , addr FROM t_user WHERE age = 10 OR addr = 'bejing';
SELECT username, age , addr FROM t_user WHERE username = 'zhangsan' OR addr = 'beijing' AND age >10;

IN 操作符（用来指定条件范围"（,）"）

SELECT username, age FROM t_user WHERE username IN ('zhangsan', 'lisi');

in操作符与or的功能相同，但是为什么还要使用in操作符？

• 在使用长的合法选项清单时，in操作符的语法更清楚且更直观
• 计算的次序更容易管理（因为使用的操作符更少）
• 一般比or操作符清单执行更快
• 可以包含其他select语句，使得能够更动态地建立where子句

NOT 操作符（否定它之后所跟的任何条件，mysql支持NOT对IN、BETWEEN和EXISTS子句取反）

SELECT username, age FROM t_user WHERE username NOT IN ('zhangsan', 'lisi');

LIKE 操作符

百分号（%）通配符（可以区分大小写，跟mysql的配置有关，默认是不区分） %不能匹配NULL
SELECT username, age FROM t_user WHERE username LIKE 'z%';
SELECT username, age FROM t_user WHERE username LIKE '%a%';
SELECT username, age FROM t_user WHERE addr LIKE '%';

下划线（_）通配符（与%用途一样，但只匹配一个字符）

SELECT username, age FROM t_user WHERE username LIKE '_hangsan';

通配符使用技巧

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符
• 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

REGEXP正则表达式进行搜索

SELECT username, age FROM t_user WHERE username REGEXP 'z';

BINARY 区分大小写
SELECT username, age FROM t_user WHERE username REGEXP BINARY 'Z';

"."表示匹配任意一个字符
SELECT username, age FROM t_user WHERE addr REGEXP '.';

or匹配
SELECT username, age FROM t_user WHERE username REGEXP 'z|l';

[123]相当于[1|2|3]
SELECT username, age FROM t_user WHERE username REGEXP '[123] z';

集合中使用^匹配除这些字符以外的，否则指串的开始处
SELECT username, age FROM t_user WHERE username REGEXP '[^123] z';

^串的开始处
SELECT username, age FROM t_user WHERE username REGEXP '^[0-9]';

范围匹配相当于[123456]
SELECT username, age FROM t_user WHERE username REGEXP '[1-6] z';

匹配特殊字符用\\
SELECT username, age FROM t_user WHERE username REGEXP '\\.';

CONCAT 拼接串

AS：别名
SELECT CONCAT(username, '(', age, ')') AS info FROM t_user;

函数

文本处理函数

SELECT UPPER(username), age FROM t_user;

常用文本处理函数
| 函数 | 说明|
| ------ | ------ | ------ |
| Left()| 返回串左边的字符 |
| Length() | 返回串的长度 |
| Locate() | 找出串的一个子串 |
| Lower() | 将串转换为小写 |
| LTrim() | 去掉串左边的空格 |
| Right() | 返回串右边的字符 |
| RTrim() | 去掉串右边的空格 |
| Soundex() | 返回串的SOUNDEX值 根据发音字符和音节进行比较 |
| SubString() | 返回子串的字符 |
| Upper() | 将串转换为大写 |
日期和时间处理函数

按日查询
SELECT username, age, createdate FROM t_user WHERE DATE(createdate) = '2019-07-10'; 

按月查询
SELECT username, age, createdate FROM t_user WHERE DATE_FORMAT(createdate,'%Y-%m') = '2019-07'; 

按年查询
SELECT username, age, createdate FROM t_user WHERE DATE_FORMAT(createdate,'%Y') = '2019';

数值处理函数
常用文本处理函数
| 函数 | 说明 |
| ------ | ------ | ------ |
| Abs()| 返回一个数的绝对值 |
| Cos() | 返回一个角度的余弦 |
| Exp() | 返回一个数的指数值 |
| Mod() | 返回除操作的余数 |
| Pi() | 返回圆周率 |
| Rand() | 返回一个随机数 |
| Sin() | 返回一个角度的正弦 |
| Sqrt() | 返回一个数的平方根 |
| Tan() | 返回一个角度的正切 |

聚集函数
| 函数 | 说明 |
| ------ | ------ | ------ |
| AVG()| 返回某列的平均值 忽略NULL |
| COUNT() | 返回某列的行数 如果为*则不忽略NULL，为列时忽略 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |

SELECT username, AVG(age) AS avg_age FROM t_user;

数据分组 GROUP BY 和 HAVING
GROUP BY子句经常在聚合函数中使用，而HAVING配合GROUP BY使用

SELECT COUNT(age) AS p_age FROM t_user GROUP BY age;
SELECT age, COUNT(age) AS p_age FROM t_user GROUP BY age HAVING COUNT(age) > 1;-- HAVING：过滤分组
SELECT age, COUNT(age) AS p_age FROM t_user WHERE addr = 'tianjin' GROUP BY age HAVING COUNT(age) > 1;

SELECT子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出顺序排序	否
LIMIT	要检索的行数	否

子查询

例：列出订购物品TNT2的所有客户，具体步骤
检索包含TNT2所有订单的编号 orderitems表
检索前一步列出的订单编号的所有客户id orders表
检索前一步返回的客户id的客户信息 customers表

SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (
SELECT cust_id 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) AS orders FROM customers;

联结

联结是一种机制，用来在一条SELECT语句中关联表
内部联结

SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num AND prod_id = 'TNT2';

INNER JOIN ON

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

INNER JOIN ON 连接三个数据表的用法：

SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

INNER JOIN 连接四个数据表的用法：

SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号)

INNER JOIN 表4 ON Member.字段号=表4.字段号

INNER JOIN 连接五个数据表的用法：

SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号)

INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

自联结（相同的表查询两次）

SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.vend_id = "DTNTR";

自然联结
排除多次出现，使每个列只返回一次

外部联结LEFT | RIGHT OUTER JOIN ON 返回包括没有的列

SELECT vendors.vend_name, products.prod_name FROM vendors LEFT OUTER JOIN products ON vendors.vend_id = products.vend_id;

带聚集函数的联结
例：检索所有客户及每个客户所下的订单数

SELECT customers.cust_name, customers.cust_id, COUNT(orders.num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

组合查询UNION(与多个WHERE条件完成相同的工作)

任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);

UNION使用规则

• 必须由两条或两条以上的SELECT语句组成，语句之间用UNION分隔
• 每个查询必须包含相同的列、表达式或聚集函数（次序可以不同）
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换类型

UNION默认自动取消重复的行，如果想返回所有匹配的行使用UNION ALL

全文搜索

使用MyISAM引擎，一般在创建表时启用全文本搜索

CREATE TABLE productnotes
(
note_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
note_text text NULL,
FULLTEXT(note_text)
) ENGINE=MyISAM;

SELECT note_text FROM productnotes WHERE Match(note_text) Against('zhangsan');

插入数据INSERT INTO VALUES

INSERT INTO t_user(username, age, addr) VALUES('zhangba', 17, 'shanghai');

插入多条
INSERT INTO t_user(username, age, addr) VALUES('zhangjiu', 18, 'shanghai'), ('lishi', 20, 'beijing'), ('wangyi', 17, 'shanghai');

LOW_PRIORITY 降低INSERT语句的优先级
INSERT LOW_PRIORITY INTO t_user(username, age, addr) VALUES('zhangba', 17, 'shanghai');

将一个表的查询结果插入插入另一个表中
INSERT INTO t_user(username, age, addr) SELECT n_username, n_age, n_addr FROM t_new_user;

更新数据UPDATE SET

UPDATE t_user SET age = 20 WHERE username = 'zhangsi';

IGNORE 即使发生错误，也继续进行更新
UPDATE IGNORE t_user SET createdate = '2019-07-15' WHERE addr = 'beijing';

删除数据DELETE FROM

DELETE FROM t_user WHERE username = 'liujiu';

删除表中所有数据，删除原来的表并重新创建一个表，而delete是逐行删除，比delete快
TRUNCATE TABLE t_user;

表

创建表

CREATE TABLE test_one(one_id int auto_increment, one_name char(20), one_age int, PRIMARY KEY(one_id)) ENGINE = INNODB;

返回最后一个auto_increment
SELECT LAST_INSERT_ID(); 

修改表ALTER TABLE

向表中添加字段
ALTER TABLE t_user ADD createdate datetime;

从表中删除字段
ALTER TABLE t_user DROP COLUMN createdate;

修改表中列的类型
ALTER TABLE t_user MODIFY createdate char(20);

修改表中列名
ALTER TABLE t_user CHANGE createdate starttime datetime;

添加外键
ALTER TABLE test_one ADD CONSTRAINT fk_t_user_test_one FOREIGN KEY (外键名) REFERENCES t_new_user (主键名); 

ON DELETE CASCADE ON UPDATE CASCADE 联合删除 更新
ALTER TABLE test_one ADD CONSTRAINT fk_t_user_test_one FOREIGN KEY (n_id) REFERENCES t_new_user (n_id) ON DELETE CASCADE ON UPDATE CASCADE;

删除外键
ALTER TABLE test_one DROP FOREIGN KEY fk_t_user_test_one;

删除表DROP TABLE

DROP TABLE test_two;

重命名表RENAME TABLE 原表名 TO 新表名

RENAME TABLE test_two TO test_three;
或
ALTER TABLE test_two RENAME test_three;

设置自增id从0开始

alter table tablename auto_increment=0

视图

定义：

具有连接的SQL SELECT查询语句。视图是虚拟表或逻辑表，视图是动态的，因为它与物理模式无关。当表的数据发生变化时，视图也反映了这些数据的变化。
视图可以看作是java中封装的方法，方便调用

作用：

隐藏复杂的sql，主要用来对select语句层次的封装

优点：

• 重用SQL语句
• 简化复杂的SQL操作
• 使用表的组成部分而不是整个表
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

缺点：

• 性能问题：把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，sql server也要把它变成一个复杂的结合体，需要花费一定的时间
• 表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

使用场景

• 需要权限控制的时候。
• 如果某个查询结果出现的非常频繁，就是要经常拿这个查询结果来做子查询，使用视图会更加方便。
• 关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作；

创建视图
create view v_getuser as select t_user.username, t_user.age from t_user;
select * from v_getuser where age > 20;

查看创建视图的语句
show create view v_getuser;

删除视图
drop view v_getuser;

更新视图 也可先drop再create
create or replace view;

存储过程

CREATE PROCEDURE getavg()
  BEGIN
    SELECT AVG(DISTINCT age) AS age_avg FROM t_user;
  END;

调用存储过程
CALL getavg();

删除存储过程
DROP PROCEDURE IF EXISTS getavg;

显示存储过程的语句
SHOW CREATE PROCEDURE getavg;

显示所有存储过程
SHOW PROCEDURE STATUS;

过滤显示所有存储过程
SHOW PROCEDURE STATUS LIKE '%user%';

// 带输出参数
CREATE PROCEDURE procedure_age(OUT age_min DECIMAL, OUT age_max DECIMAL)
  BEGIN
    SELECT MIN(DISTINCT age) INTO age_min FROM t_user;
    SELECT MAX(DISTINCT age) INTO age_max FROM t_user;
  END;

CALL procedure_age(@ageMin, @ageMax);
SELECT @ageMin as minage, @ageMax as maxage;

游标cursor

CREATE PROCEDURE p_username()
  BEGIN
    -- 定义局部变量
    DECLARE u CHAR(20);
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE cur_user CURSOR FOR SELECT username FROM t_user;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    -- 开启游标
    OPEN cur_user;
    --循环每一行
    REPEAT
    -- 检索当前行的列
    FETCH cur_user INTO u;
      INSERT INTO p_users VALUES(u);
    --结束循环
    UNTIL done END REPEAT;
     -- 关闭游标
    CLOSE cur_user;
  END;

触发器

MySQL响应 DELETE、UPDATE 和 INSERT 而自动执行的一条MySQL语句（或位于 BEGIN 和 END 语句之间的一组语句）
只有表才支持触发器，视图不支持，临时表也不支持

创建触发器
CREATE TRIGGER testtrigger AFTER INSERT ON t_user FOR EACH ROW SELECT 'add';

删除
DROP TRIGGER testtrigger;

insert触发器
CREATE TRIGGER insertuser AFTER INSERT ON t_user FOR EACH ROW SELECT NEW.id INTO @insert_id;
INSERT INTO t_user(username, age, addr) VALUES('zhangyiyi', 23, 'tianjin');
SELECT @insert_id;

delete触发器 
-- begin end 块的好处是触发器能容纳多条sql语句
CREATE TRIGGER deleteuser BEFORE DELETE ON t_user FOR EACH ROW 
BEGIN
SELECT OLD.id INTO @delete_id;
END;

update触发器 OLD虚拟表访问更新之前的值，NEW访问更新之后的值
CREATE TRIGGER updateuser BEFORE UPDATE ON t_user FOR EACH ROW SET NEW.username = UPPER(NEW.username);

事务

用来维护数据库的完整性，保证成批的mysql操作要么完全执行，要么完全不执行

回滚ROLLBACK

SELECT * FROM t_user;
开启事务
START TRANSACTION;
DELETE FROM t_user;
SELECT * FROM t_user;
回滚
ROLLBACK;
SELECT * FROM t_user;

提交COMMIT

开启事务之后，只有都成功才会执行commit，出错都会撤销
START TRANSACTION;
DELETE FROM t_user WHERE id = 10;
DELETE FROM t_user WHERE id = 11;
提交
COMMIT;

保留点SAVEPOINT

START TRANSACTION;
INSERT INTO t_user(username, age, addr) VALUES ('zhangyier', 19, 'tianjin');
SAVEPOINT insesrt_user;
DELETE FROM t_user WHERE addr = 'tianjin';
ROLLBACK TO insesrt_user;

安全管理

用户管理

用户存储在mysql数据库的user表中

USE mysql;
SELECT user FROM user;

创建账号
CREATE USER zyw IDENTIFIED BY 'zywrxq1224';

重命名
RENAME USER zyw TO zyw1;

删除账号
DROP USER zyw1;

显示账号权限
SHOW GRANTS FOR zyw;

授权
GRANT SELECT, INSERT ON test_daily.* TO zyw;

取消授权
REVOKE SELECT ON test_daily.* FROM zyw;

整个服务器
GRANT ALL ON *.* TO zyw;
整个数据库
GRANT ALL ON test_daily.* TO zyw;
整个表
GRANT ALL ON test_daily.t_user TO zyw;

修改账号密码
ALTER user 'root'@'localhost' IDENTIFIED BY '新密码';

常见错误

Cannot truncate a table referenced in a foreign key constraint

删除表中数据时，提示有外键，需要先取消外键约束

SET foreign_key_checks = 0;

然后执行删除

TRUNCATE TABLE t_user;

启动外键约束

SET foreign_key_checks = 1;

未完待续。。。