《MySQL必知必会》学习笔记

基础

表中每一行都应该有可以唯一标识自己的一列（或一组列）
Primary key 唯一标识表中每一行的这个列称为主键
主键的最好习惯：
1.应该保证每张表都拥有一个主键
2.不更新主键列的值
3.不重用主键的值

在执行任意数据库操作前，都需要选择一个数据库。为此，使用USE 关键字（USE 库名）

SQL语句规范：所有SQL关键字大写，所有表名和列名等使用小写

了解数据库和表

Show databases ; – 返回所有数据库列表
Show tables; – 返回所选数据库的所有表
Show columns 表名; – 返回该表的结构
Describe表名; – 返回该表的结构

创建表

CREATE TABLE mytable (
  -- int 类型，不为空，自增
  id INT NOT NULL AUTO_INCREMENT,
  -- int 类型，不可为空，默认值为 1，不为空
  col1 INT NOT NULL DEFAULT 1,
  -- 变长字符串类型，最长为 45 个字符，可以为空
  col2 VARCHAR(45) NULL,
  -- 日期类型，可为空
  col3 DATE NULL,
  -- 设置主键为 id
  PRIMARY KEY (‘id’));

更新表

给表添加一个列
ALTER TABLE mytable ADD newCol; 数据类型
删除表中的一个列
ALTER TABLE mytable DROP col;
添加外键约束
ALTER TABLE mytable ADD 
CONSTRAINT 外键名 FOREIGN KEY(列名) REFERENCES orderTable(关联的字段)

删除表

DROP TABLE mytable

重命名表

RENAME TABLE mytable TO mytable1,
table1 TO table2,
table3 TO table4;

插入数据

插入单行数据
INSERT INTO table(col1,col2)
VALUES(val1,val2);

如果，每条INSERT语句中的列名和次序相同，则可以组合各语句
一条INSERT语句处理多个插入要比多个INSERT处理单条数据要快
INSERT INTO table(col1,col2)
VALUES(val1,val2),
VALUES(val3,val4);

更新

UPDATE语句由3部分组成
1.要更新的表名
2.列名和它们的新值
3.更新行的过滤条件

UPDATE table
SET col1 = val1
WHERE id = 1 ;

IGNORE关键字：UPDATE如果在更新时出现错误会把错误发生之前更新的数据复原。如果要即使出现错误也要进行更新那么可以使用IGNORE关键字 UPDATE IGNORE table

删除数据

DELETE FROM table
WHERE id = 1 ; 

如果要删除整张表的数据不要使用DELETE去逐行删除，应该使用TRUNCATE TABLE去执行
TRUNCATE TABLE删除整张表，然后再创建一张一模一样的表，而DELETE则是逐行删除表中的数据

插入检索出的数据

把所有检索出的数据插入到table1中，SELECT后列出的每个列要对应INSERT后面列出的每个列

INSERT INTO table1(col1,col2)
SELECT col1,col2
FROM table2;

检索数据

Select语句从一张或多张表中检索数据，select至少给出两条信息——想要选取得信息，从那张表中选择
Select 需要选取得列1，需要选取得列2 from 表名
Distincy 关键字：只返回不相同的值
注意: SELECT DISTINCY col1,col2 from table1 ;
只有DISTINCY是应用于所有列，上面的col1 和 col2 都相同才认为是相同的
LIMIT ： 限制返回的行数，有两个参数，第一个参数是初始行，第二个参数是返回的总行数
注意：索引从0开始

排序检索数据

ORDER BY关键字：对返回记录进行排序
ASC  升序（默认）
DESC 降序
SELECT * FROM table1 
ORDER BY col1 ASC, col2 DESC;

过滤数据

不进行过滤的数据非常大，导致通过网络传输了多余的数据。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。

WHERE子句指定搜索条件进行过滤
SELECT * FROM table1
WHERE col1 = ‘fruit’ ; – MySQL在执行匹配时默认不区分大小写，所以fruit和Fruit是一样的
注意：
空值的检测需要使用IS NULL

ADD 和 OR
WHERE子句可以包含任意数目的ADD和OR操作符。
注意：
操作符ADD的优先级毕OR高

通配符

在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较
‘%’：匹配 >=0 个字符
‘_’:：匹配 1个字符

SELECT prod_id,prod_name 
FROM products 
WHERE prod_name 
LIKE '%anvil%';

注意：
1.通配符搜索比其他搜索所花时间更长
2.不要过度使用通配符
3.除非绝对有必要，否则不要把通配符用在开始处。通配符置于搜索模式的开始，搜索是最慢的

正则表达式

正则表达式是用来匹配文本的特殊串
使用REGEXP关键字告诉MySQL：后面跟的语句作为正则表达式

SELECT prod_id,prod_name 
FROM products 
WHERE prod_name 
REGEXP '.000';

LIKE 和 REGEXP 的区别：
SELECT prod_id,prod_name 
FROM products 
WHERE prod_name 
LIKE '1000';


SELECT prod_id,prod_name 
FROM products 
WHERE prod_name 
REGEXP '1000';

LIKE匹配整个字段，REGEXP匹配列中包含的值
除非LIKE使用通配符

多数正则表达式使用单个’\’来转义字符，但MySQL使用两个，MySQL自己解析一个，正则表达式解析另一个

计算字段

我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户端中重新格式化，在数据库服务器上完成这些操作比在客户机中完成要快得多，而且转换和格式化后的数据更少的化可以减轻网络通信量。

在mysql中使用Concat()函数来拼接两个列，

SELECT CONCAT(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name ;

Concat把多个串连接起来形成

Trim函数可以去除首位空格

函数

文本处理
LEFT() 左边的字符
RIGHT() 右边的字符
LOWER() 转换为小写字符
UPPER() 转换为大写字符
LTRIM() 去除左边的空格
RTRIM() 去除右边的空格
LENGTH() 长度
SOUNDEX() 转换为语音值

各个DBMS实现的函数有可能不相同，因此函数的移植性不高
Upper() 将文本转换为大写

日期和时间处理
日期格式：YYYY-MM-DD
时间格式：HH:MM:SS
函 数 说 明
ADDDATE() 增加一个日期（天、周等）
ADDTIME() 增加一个时间（时、分等）
CURDATE() 返回当前日期
CURTIME() 返回当前时间
DATE() 返回日期时间的日期部分
DATEDIFF() 计算两个日期之差
DATE_ADD() 高度灵活的日期运算函数
DATE_FORMAT() 返回一个格式化的日期或时间串
DAY() 返回一个日期的天数部分
DAYOFWEEK() 对于一个日期，返回对应的星期几
HOUR() 返回一个时间的小时部分
MINUTE() 返回一个时间的分钟部分
MONTH() 返回一个日期的月份部分
NOW() 返回当前日期和时间
SECOND() 返回一个时间的秒部分
TIME() 返回一个日期时间的时间部分
YEAR() 返回一个日期的年份部分

Data() 指示mysql仅提取列的日期部分
有时候datetime这种类型存储日期及时间值，需要使用data函数截取日期
如果是要对日期进行匹配，请使用Data()，即使你知道相应的列只包含日期也是如此

SELECT cust_id,order_num
FROM orders
WHERE DATE(order_date) = ‘2005-09-01’

如果想检索2005年9月中的所有订单，那么简单的相等测试不行，应该使用如下方法

SELECT cust_id
FROM orders
WHERE DATE(order_date) BETWEEN ‘2005-09-01’ AND ‘2005-09-30’

另一种方法不需要记住一个月有多少天，也不需要操心闰年2月的办法
SELECT cust_id
FROM orders
WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9

聚集函数

运行在行组上，计算和返回单个值的函数
函 数 说 明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

分组

按照指定字段中具有相同的数据值的行进分组，
可以为每一个组使用聚合函数，例如求每个组的平均值

SELECT col,COUNT(*) AS num
FROM products 
GROUP BY col ; 

按照col来进行分组，col相同数据值的为一组，然后分别对每一组使用聚合函数count，而不是对整个结果集使用聚集

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

过滤分组

Mysql允许过滤分组，规定包括那些分组，排除那些分组
where来过滤行，having来过滤组。事实上having支持所有的where操作符
SELECT col,COUNT(*) AS num
FROM table
GROUP BY col
HAVING num >= 2 – 使用每个分组的聚合字段进行组过滤

Where要优于having，也就是说先对行进行过滤，被where过滤掉的行不包含在分组中，
然后再使用having进行组过滤（这也就是说where的条件将会影响到having的组过滤）

GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前
NULL 的行会单独分为一组；

子查询

查询中嵌套查询，称嵌套查询为子查询

子查询的不同情况：
1.子查询的结果是单行单列的，即查询结果返回一个数据，那么可以将这个子查询的结果作为where语句的过滤条件
2.子查询的结果是多行单列的，子查询作为条件，使用in来判断
3.子查询返回多行多列，把该子查询的结果作为一张虚拟表来进行查询

SELECT * 
FROM (SELECT * FROM table2 WHERE join_date > '2011-11-11') AS t1,
dept AS t2
WHERE t1.dept_id = t2.id ; 

建立子查询的步骤：
1.建立和测试最内层的查询
2.获得最内层查询结果，然后用硬编码的方式建立和测试外层查询
3.在确认正常后把子查询嵌入到外层查询中

联结

如果数据存储在多个表中，怎么样用单条SELECT语句检测出数据呢？
答案是使用联结，联结是一种机制，用来在一条SELECT 语句中关联多个表，可以联结多个表返回一组数据

SELECT t1_name,t2_name
FROM table1,table2
WHERE table1.t2_id = table2.id ;

FROM给出了两个表名，它们就是SELECT语句中要联结的两个表
在联结两个表时，实际上是将第一个表的每一行数据与第二个表中每一行的数据进行配对，WHERE子句的作用就是进行条件过滤，让他只返回包含那些匹配给定条件的行。
没有WHERE子句，他将第一个表中的每行与第个表中的每行进行配对，而不管它们逻辑上是否可以匹配到一起
笛卡尔积——由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行数等于 表1的行数成表2的行数

内部联结

SELECT t1_name,t2_name
FROM table1 INNER JOIN table2
ON table1.t2_id = table2.id ;

与上面的等值联结得到相同的结果，不过使用这种语法时，联结条件用特定的ON子句而不是WHERE子句。传递给ON的实际条件与传递给WHERE的相同

自联结

自联结是内联结的一种，它联结的表是自身
一张员工表，包含员工姓名和所属部门。现在我要在这张表中查找’Jim’这个人所在部门的所有员工

子查询版本
SELECT name,dept
FROM employee 
WHERE dept = (SELECT dept 
FROM employee 
WHERE name = ‘JIM’)

内联结
SELECT e1.name,e1.dept
FROM employee as e1 , employee as e2
WHERE e1.dept = e2.dept 
AND e2.name = ‘JIM’

能用内联结就不要用子查询，处理内联结远比处理子查询快得多

外部联结

许多联结将一个表中的行于另一个表中的行相关联。但有时候会需要包含没有关联的那些行
例如：对每个客户下了多少订单进行计数，也包含那些没有下过订单的客户
列出所有产品以及订购数量，包含那些至今没有人订购的商品
外部联结保留了没有进行关联的那些行。分为左联结，右联结，其中左联结就是保留左表中没有关联到的行

组合查询
使用UNION来组合两条SELECT语句
使用UNION的两个查询必须包含相同的列、表达式或聚集函数
假设查询1返回M条记录，查询2返回N条记录，一般情况下会返回M+N条记录
但是UNION默认会去除相同的行，如果需要保留相同的行，则使用UNION ALL
对组合查询使用ORDER BY排序，要求放在最后一条SELECT语句之后，因为对结果集只能用一种方式排序

SELECT col
FROM table 
WHERE col = 1 
UNION
SELECT col
FROM table 
WHERE col = 1 OR 2 --  重复的col=1的行会被去除掉

视图

视图是虚拟的表，与包含数据的表不同，视图只包含使用时动态检索数据的查询（包含的是一个SQL查询）
对视图的操作和对普通表一样
使用视图有以下好处：
1.简化复杂的SQL操作。在编写查询后，可以方便地重用它而不用知道它的基本查询细节
2.只使用实际表的一部分数据

视图本身不包含数据，它们返回的数据是从其他表中检索出来的
视图可以嵌套，即可以利用从其他视图中检索的数据来构造一个视图，因为视图不包含数据，所以每次使用视图时，都必须处理查询检索，所以嵌套视图可以会导致性能下降的很厉害

创建视图

CREATE OR REPLACE VIEW myview AS
SELECT col1,col2 * col3 AS computeCol
FROM mytable
WHERE col4 = val ;

删除视图

DROP VIEW myview

SELECT * FROM myview
WHERE col1 = val ;

MySQL处理此查询时， 它将指定的WHERE子句添加到视图查询中已有的WHERE子句中，即将视图内的WHERE子句和使用视图的WHERE子句进行组合

存储过程

存储过程可以看作是保存多条SQL语句的集合
使用存储过程的好处
1.对实现进行封装：保证操作的安全性，和提高复用率
2.提高性能：将SQL代码转换为存储过程会预先编译，因此具有很高的性能

注意：默认的mysql语句结束符为 ’; ’，而在存储过程中也包含了’;’，这会导致创建存储过程时出现错误，使用DELIMITER 关键字重新定义结束符就好了

创建存储过程：

DELIMITER //
CREATE PROCEDURE myprocedure(
IN inputid INT,
IN flag BOOLEAN,
OUT result DECIMAL(4,2)
)
BEGINE
DECALER temp INT; -- 声明一个int类型的局部变量 temp

SELECT SUM(col2) 
FROM mytable
WHERE col1 = inputid
INTO temp ;

IF flag THEN
SELECT temp * temp 
INTO temp;
END IF;

SELECT temp INTO result ; -- 将计算好的局部变量存储到result变量，返回出去
END//
DELIMITER ;

执行存储过程
CALL myprocedure(@val1,@val2,@result);

查看创建存储过程的语句
SHOW CREATE PROCEDURE myprocedure

游标

游标可以中存储过程中对结果集进行遍历

使用游标的几个步骤：
1.声明游标，该过程实际上没有检索数据
2.打开游标，SELECT语句把数据检索出来填充游标中
3.根据需要取出各行
4.关闭游标

创建游标

CREATE PROCEDURE myprocedure()
BEGIN
DECLARE mycursor CURSOR 
FOR
SELECT col1 FROM mytable ;
END;

打开和关闭游标
OPEN mycursor;
CLOSE mycursor;

使用游标的例子

DELIMITER //  -- 重定义结束符为//
CREATE PROCEDURE myprocedure() -- 创建存储过程
BEGIN 
-- 声明一些局部变量
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);
	-- 创建游标
	DECLARE mycursor CURSOR
	FOR
	SELECT col FROM mytable;
	-- 设置结束标志（SQLSTATE为02000表示一个未找到条件，当REPEAT没有更多的行进	--行循环时，出现该条件）
	DECLARE CONTINUE HANDLER 
	FOR SQLSTATE '02000' 
	SET done = 1;
	-- 创建一张新的表存储我们等会儿检索到的数据
	CREATE TABLE IF NOT EXISTS mytable2(
		col1 INT,col2  DECIMAL(8,2)
	);
	
	

    OPEN mycursor ; -- 打开游标
    
        REPEAT 
        	FETCH mycursor INTO o;		-- FETCH取出数据 ，然后使内部指针指向下一行
        	CALL orderprocedure(o,1,t) ; -- 调用另外一个存储过程处理我们检索出来的数据
        	
        	INSERT INTO mytable2(col1,col2)
        	VALUES(o,t) ; 
        UNTIL done END REPEAT;  -- 当 done 为真时结束REPEAT
        
        CLOSE mycursor ;  -- 使用完，关闭游标

END //
DELIMITER ;

触发器

触发器在某些表发生更改时自动处理，即当对表执行INSERT、UPDATE、DELETE操作时自动执行某些语句。
触发器需要指定在这些语句的前或后执行
每个表最多支持6个触发器，即INSERT、UPDATE、DELETE这些语句的前后

通常来说，BEFORE用于数据验证和净化
AFTER用于审计跟踪

INSERT触发器
– 在INSERT触发器代码内，有一个虚拟表NEW，访问被插入的行，在BEFORE INSERT触发器中 NEW的值也可以被更新
对于ATUO_INCREMENT类，NEW在INSERT之前时0，INSERT之后才包含新的自动生成值
CREATE TRIGGER mytrigger1 AFTER INSERT ON mytable
FOR EACH ROW
BEGIN
SELECT NEW.col1 INTO @result ;
END;
SELECT @result ;

DELETE触发器
DELETE触发器代码内，有一张名为OLD的虚拟表，访问被删除的行，该表中值全部都是只读的

UPDATE触发器
在UPDATE触发器代码内，有一张名为OLD的虚拟表，可以访问修改前的值。有一张名为NEW的表，可以访问修改后的值

事务处理

1.事务的基本介绍
(1)概念：
事务处理可以用来维护数据库的完整性，它保证了成批的MySQL操作要么完全执行，要么完全不执行。事务处理是一种机制，用来管理必须成批执行的MySQL语句，以确保数据库不包含不完整的操作结果，利用事务处理，可以保证一组操作不会中途停止。如果没有错误发生，整组语句提交给数据库。如果发生错误，则进行回退，以恢复到数据库某个已知状态。
(2)操作：
①开启事务：start transaction
②回滚:rollback
③提交:commit
④保留点：在事务中设置临时的占位符，如果改事务没有设置占位符，ROLLBACK会回退到start transaction语句，；如果设置了保留点，则ROLLBACK会回退到该保留点
⑤在MySQL数据库中事务默认提交 一条DML（增删改）语句会自动提交一次事务，而Oracl数据库默认手动提交数据
1)事务提交的两种方式
a.自动提交：一条DML（增删改）语句会自动提交一次事务
b.手动提交：需要先开启事务，再提交
2)修改事务的默认提交方式：SELECT @@autocommit – 1代表自动提交
a.修改默认提交方式：set @@autocommit = 0; – 0代表手动提交
2.事务的四大特征
(1)原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
(2)持久性：如果事务提交或者回滚，数据库持久化的保存数据
(3)隔离性：多个事务之间，相互独立
(4)一致性：事务操作前后，数据总量不变
3.事务的隔离级别
(1)概念：多个事务之间相互独立的，但是多个事务操作同一批数据，则会引发一些问题，通过设置不同的隔离级别就能够解决这些问题
(2)存在问题：
①脏读：一个事务，读取到另一个事务没有提交的数据
②不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
③幻读：一个事务操作（DML）数据库中所有记录，另一个事务添加一条数据，
则第一个事务查询不到自己的修改
(3)隔离级别：
①read uncommitted：读未提交
1)产生的问题：脏读、不可重复读、幻读
②read committed：读以提交 （Oracle默认）
1)产生的问题：不可重复读、幻读
③repeatable read：可重复读：（MySQL默认）
1)产生的问题：幻读
④serializable：串行化 ：对表进行加锁，如果一个事务处理A对某个表进行操作，那么在其余事务中需要等待A提交或回退才能对该表进行数据操作

注意：隔离级别从小到大安全性越来越高，但是效率越来越低
数据库设置隔离级别
select @@tx_isolation; – 查询隔离界别
set global transaction isolation level 级别字符串; – 设置隔离级别

安全管理

SQL分类：
1.DDL：操作数据库和表
2.DML：增删改表中数据
3.DQL：查询表中数据
4.DCL：管理用户，授权

DBA:数据库管理员
1.管理用户
(1)添加用户：
①-- 创建用户
②CREATE USER ‘用户名’@‘主机名’IDENTIFIED BY ‘密码’
③CREATE USER’Jason’@‘localhost’IDENTIFIED BY’123’;
(2)删除用户：
①-- 删除用户
②DROP USER’用户名’@‘主机名’;
③DROP USER’Jason’@‘localhost’;
(3)修改用户密码：
①-- 修改Harry用户的密码为 abc
②SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
③SET PASSWORD FOR ‘Harry’@’%’ = PASSWORD(‘abc’);
(4)查询用户：
①-- 1. 切换到mysql数据库
②USE mysql;
③-- 2. 查询user表
④SELECT * FROM USER ;
注意： 通配符：”%” 表示可以在任意主机使用用户登陆数据库

2.权限管理：
(1)查询权限
①-- 查询权限
②SHOW GRANTS FOR ‘用户名’@‘主机名’;
③SHOW GRANTS FOR ‘Harry’@’%’;
(2)授予权限
①-- 授予权限
②GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@’主机名’ ;
③GRANT SELECT,DELETE,UPDATE ON db3.account TO ‘Harry’@’%’;
④-- 给Harry用户授予所有权限，在任意数据库任意表上
⑤GRANT ALL ON . TO ‘Harry’@’%’;
(3)撤销权限
①-- 撤销权限
②REVOKE 权限列表 ON 数据库名.表名 TO ‘用户名’@’主机名’
③REVOKE UPDATE ON db3.account FROM ‘Harry’@’%’