学习 MySQL 必须要知道的知识

学习 MySQL 必须要知道的知识

SQL（Structure Query Language）语言是数据库的核心语言。

SQL 是一个标准的数据库语言，是面向集合的描述性非过程话语言。它功能强，效率高，简单易学易维护。然而SQL语言由于以上优点，同时也出现了这样一个问题：它是非过程性语言，即大多数语句都是独立执行的，与上下文无关，而绝大部分应用都是一个完整的过程，显然用SQL完全实现这些功能是很困难的。所以大多数数据库公司为了解决此问题，作了如下两方面的工作：

• 扩充SQL，在SQL中引入过程性结构；
• 把SQL嵌入到高级语言中，以便一起完成一个完整的应用。

1. SQL 语言的分类

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

SELECT <表字段名>

FROM <表或视图名>

WHERE <查询条件>

2. 数据操纵语言DML

数据操纵语言DML主要有三种形式：

1. 插入：INSERT
2. 更新：UPDATE
3. 删除：DELETE

3. 数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象-------表、视图、索引、同义词、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

​ | | | | |

​ 表 视图 索引 同义词 簇

DDL操作是隐形提交的！不能 rollback

4. 数据控制语言DCL

数据控制语言DCL 用来授予或回收访问数据库的某种特权，并控制数据库操作事务发生的时间及效果，对数据库实行监视等。如 ：

（1） GRANT ：授权。

（2） ROLLBACK [WORK] TO [SAVEPOINT] ：回退到某一点。

回滚—ROLLBACK

回滚命令是数据库状态回到上次最后提交的状态。其格式为：

SQL>ROLLBACK;

（3） COMMIT [WORK] ：提交.

​ 在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交后才可以看到。

​ 提交数据有三种类型：显示提交、隐式提交及自动提交。下面分别说明这三种类型。

1. 显示提交

用COMMIT命令直接完成的提交为显示提交。其格式为：

SQL>COMMIT;

2. 隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

3. 自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：

SQL>SET AUTOCOMMIT ON

还有事务控制语句TCL：

SAVEPOINT ：保存点

ROLLBACK ：回退到某点

COMMIT ：提交事务

超键、候选键、主键、外键分别是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系新模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
• 候选键：是最小超键，即没有冗余元素的超键。
• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为控制（Null）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

SQL 约束有哪几种？

• NOT NULL ：用户控制字段的内容一定不能为空（NULL）。
• UNIQUE：控件字段内容不能重复，一个表允许有多个Unique约束。
• PRIMARY KEY ：也是用于控制字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY：用于预防破环表之间连接的动作，也能防止非法数据插入外间列，因为它必须是它指向的那个表中的值之一。
• CHECK：用于控制字段的值范围。

2. MySQL 存储引擎

引擎就是数据库处理数据的一种机制。

不同的殷勤发执行的策略/机制不同。

不同的需求选用不同的引擎，到达最优选择。

1.概述

​ MySQL数据库使用不同的机制存取表文件，差别在于不同的存储方式、索引技巧、锁定水平，不同的技术及配套的功能称为存储引擎。通过选择不同的技术，你能够获取额外的速度或者功能，从而改善你的应用的整体功能。

​ 在关系型数据库中数据的存储是以表的形式进行存储的，存储引擎也饿可以称为表类型。

​ Oracle，SqlServer等数据库只有一种存储引擎，MySQL支持的存储引擎有多种，最常用的三种是InnoDB、MyISAM、MEMORY。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以根据需要选择不同的存储引擎，以便在选择任何存储你的信息，如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

​ 数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而企业内大多数需要处理大量数据的应用程序的要求。

2. 查看支持的引擎

SHOW ENGINES;
1

查看表引擎：

SHOW TABLE STATUS LIKE '表名'
1

修改引擎

方式1：将 mysql.ini中 default-storage-engine=InnoDB，重启服务

方式2：建表时指定

CREATE TABLE 表名(...)ENGINE=MYISAM; 
1

方式3：建表后修改

ALTER TABLE 表名 ENGINE = INNODB;
1

存储引擎主要有：1.MyISAM ，2.InnoDB，3.Memory，4.Blackhole，5.CSV，6.Performance_Schema，7.Archive，8.Federated，9.Mrg_MyISAM。

在此主要分析 MyISAM 和 InnoDB。

3. 常用的存储引擎

InnoDB

InnoDB：默认的存储引擎

InnoDB 是一个事务型的存储引擎，有行级锁定和外键约束。

行级锁定意思是锁的力度比较小馒头、由于锁的粒度更小，写操作不会锁定全表，索引在并发较高时，使用InnoDB 引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时，MySQL不能确定要扫描的范围，InnoDB同样会锁住全表。

适用场景：

• 适合处理多重并发的更新请求；
• 支持事务（只有InnoDB 是支持事务的）；
• 外键约束（只有InnoDB是支持外键的）；
• 支持自动增加列属性auto_increment（主键自增记录自增后的数值）。

以前不支持全文检索，mysql 8支持全文检索（FULLTEXT）。

MyISAM

MyISAM也是MySQL的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当 INSERT(插入) 或 UPDATE(更新) 数据时即写操作需要锁定整个表，效率便会低一些。

适用场景：

• 不支持事务的设计
• 不支持外键的表设计。

MyISAM极度强调快速读取操作。

MyISAM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE 时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyISAM也是很好的选择。

小结：

• 不支持事务，外键
• 不支持行级锁，支持表锁
• 查询快

MEMORY

**支持表锁，数据存储在内存中，速度快，不安全，适合小量快速访问的数据。**它将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。

MEMORY 的缺陷就是对表的大小有限制，太大的表无法缓存再内存中，其次是要确保表的数据可以恢复，数据库异常终止后表的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

3. 视图

什么是视图？

​ 为了提供复杂SQL语句的复用性和表操作的安全性，MySQL 数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。 但是，视图并不在数据库中以存储的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

​ 视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是试图所引用表中的数据，从而提供了数据库中数据的安全性。

视图是基于查询的虚拟表，可以理解为：视图就是一条SELECT语句执行后返回的结果集。

SELECT 语句所查询的表称为视图的基表，而查询的结果集称为虚拟表，视图本身并不存储具体的数据，视图的数据存在于视图的基表中，基本表数据发生了改变，视图的数据也会跟着改变。

特点

视图的特点如下：

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
• 视图是由基本表（实表）产生的表（虚表）。
• 视图的建立和删除不影响基本表。
• 对视图内容的更新（添加，删除和修改）直接影响基本表。
• 当驶入来自多个基本表时，不允许添加和删除数据。

视图的操作包括创建视图，查看视图，删除视图和修改视图。

使用场景

视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。

下面是视图的常见使用场景：

• 重用SQL语句；
• 简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；
• 使用表的组成部分而不是整个表；
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个标配的访问权限；
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的优点

1. 查询简单化。视图能简化用户的操作
2. 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
3. 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

视图的缺点

1. 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即便是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
2. 修改限制。当用户视图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的。

这些视图有如下特征：

1. 有UNIQUE 等集合操作符的视图。
2. 有 GROUP BY 子句的视图。
3. 有诸如 AVG/SUM/MAX等聚合函数的视图。
4. 使用 DISTINCT 关键字的视图。
5. 连接表的视图（其中有些例外）

使用视图

使用视图是为了方便复杂的查询语句。基本思路是将复杂的查询语句定义在视图内部，然后对视图进行查询，从而简化复杂的查询语句。

定义视图

CREATE VIEW 视图名 AS SELECT 列 1，列 2... FROM 表(查询语句);
1

使用视图

SELECT * FROM 视图名
1

删除视图

drop view 视图名
1

什么是游标？

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

4. 存储过程

什么是存储过程？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后再该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

如果实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现，那么可以将这组复杂的SQL语句集编写在数据库中，由JDBC调用来执行这组SQL语句，把编写在数据库中的SQL语句集称为存储过程。

存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程类似于 JAVA 语言中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为 IN 、OUT 、INOUT 三种类型。

IN 类型的参数表示接收调用者传入的数据

OUT 类型的参数表示向调用者返回数据

INOUT 类型的参数可以接收调用者传入的参数，也可以向调用者返回数据。

优点

1. 存储过程是预编译过的，执行效率高。
2. 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3. 安全性高，执行存储过程需要有一定权限的用户。
4. 存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

1. 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2. 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3. 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包 将需要重新编译（不过也可以设置成运行时刻自动编译）。
4. 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

MySQL 存储过程的定义

创建存储过程的语法格式

create procedure 存储过程名（[in 变量名 类型，out 参数 2，…]） begin
	[declare 变量名 类型 [DEFAULT 值];] 
	存储过程语句块; 
end;
1
2
3
4

语法解析：

1. 存储过程的参数分为 in ，out ， inout 三种类型
2. in 代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定。
3. out 代表输出参数，表示该参数的值经存储过程计算后，将out 参数的计算结果返回给调用程序。
4. inout 代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序指定，又可以将 inout 参数的计算结果返回给调用程序。
5. 存储过程中的语句必须包含在begin 和 end 之间。
6. declare 用来声明变量，变量默认复制使用 default ，语句块中改变变量值，要使用set 变量 = 值；

存储过程的使用

定义一个存储过程

-- 开始位置 
DELIMITER$$ 
CREATE PROCEDURE test() 
BEGIN 
	-- 声明变量
    DECLARE v_name VARCHAR(20) DEFAULT 'jim'; 
    SET v_name = 'tom'; -- 变量赋值 
    SELECT v_name; -- 测试输出语句 
END$$ -- 结束位置 
-- 调用存储过程 
CALL test1()
1
2
3
4
5
6
7
8
9
10
11

定义一个有参数的存储过程

DELIMITER$$ 
CREATE PROCEDURE findUserCount(IN p_type INT,OUT p_count INT) 
BEGIN 
	-- 把 sql 中查询的结果赋给 变量 
	SELECT COUNT(*) INTO p_count FROM USER WHERE TYPE = p_type; 
	SELECT p_count;
END$$ 
-- 测试
CALL findUserCount(1,@p_count); -- @p_count 测试输出参数
1
2
3
4
5
6
7
8
9

流程控制语句 if else

DELIMITER$$
CREATE PROCEDURE test(IN p_day INT)
BEGIN 
	IF p_day=0 THEN 
		SELECT "星期天"; 
	ELSEIF p_day=1 THEN 
		SELECT "星期一"; 
	ELSEIF p_day=2 THEN 
		SELECT "星期二"; 
	ELSE 
		SELECT "无效日期"; 
	END IF; 
END$$ 
-- 测试
CALL test(2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

case when

DELIMITER$$ 
CREATE PROCEDURE test(IN p_day INT) 
BEGIN 
	CASE WHEN p_day = 0 THEN 
		SELECT "星期天"; 
	ELSE
		SELECT "星期一"; 
	END CASE;
END$$ 
CALL test(2)
1
2
3
4
5
6
7
8
9
10

循环

DELIMITER$$ 
CREATE PROCEDURE test7() 
BEGIN
	DECLARE v_num INT DEFAULT 0; 
	-- 循环开始 
	addnum:LOOP 
		SET v_num = v_num+1; -- 循环语句 
	-- 循环结束条件
	IF v_num = 10 THEN
		LEAVE addnum;
	END IF;
    END LOOP; -- 循环结束 
    SELECT v_num; 
END$$ 
CALL test7()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

使用存储过程插入信息

DELIMITER$$ 
CREATE PROCEDURE saveUser(IN p_account VARCHAR(20),IN p_sex CHAR(1),OUT res_mark INT) 
BEGIN 
	DECLARE v_count INT DEFAULT 0;
    	SELECT COUNT(*) INTO v_count FROM t_user WHERE account = p_account;
        IF v_count = 0 THEN 
        	INSERT INTO t_user(account,sex)VALUES(p_account,p_sex); 
        	SET res_mark = 0;
        ELSE
        	SET res_mark = 1;
        END IF;
END$$
1
2
3
4
5
6
7
8
9
10
11
12

5. 函数

函数语法

create function 函数名([参数列表]) returns 数据类型 
begin 
	DECLARE 变量； 
		sql 语句; 
	return 值; 
end;
1
2
3
4
5
6

注意：

1. 参数列表包含两部分：参数名 参数类型
2. 函数体：必须要有 return 语句，如果没有会报错
3. 函数体中仅有一句话，则可以省略 begin end
4. 使用delimter 语句设置结束标记

设置函数可以没有参数

SET GLOBAL log_bin_trust_function_creators=TRUE;
1

删除函数

DROP FUNCTION 函数名;
1

不带参数

DELIMITER$$ 
CREATE FUNCTION test() RETURNS INT 
BEGIN
	DECLARE v_num INT;
    SELECT COUNT(*) INTO v_num FROM t_user;
   RETURN v_num;
END$$ 
-- 测试
select test() from dual;
1
2
3
4
5
6
7
8
9

带参数

DELIMITER$$
CREATE FUNCTION findDeptNameById(p_id INT) RETURNS VARCHAR(10) 
BEGIN
	DECLARE v_name VARCHAR(10); 
	SELECT NAME INTO v_name FROM dept WHERE id = p_id; 		RETURN v_name;
END$$
SELECT account,findDeptNameById(dept_id) FROM user
1
2
3
4
5
6
7

有参数，有判断

DELIMITER$$ 
CREATE FUNCTION checkUserType(p_type INT) RETURNS VARCHAR(4) 
BEGIN
	IF p_type = 0 THEN 
		RETURN '管理员';
    ELSE 
    	RETURN '业务用户';
    END IF; 
END$$ 
SELECT tu.account,checkUserType(tu.type) utype FROM user tu
1
2
3
4
5
6
7
8
9
10

6. JDBC 调用存储过程

mybatis 调用存储过程

<parameterMap type="map" id=“usermap">
	<parameter property="addend1" jdbcType="VARCHAR" mode="IN"/> 
	<parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>
1
2
3
4

jdbcType 必须制定

<insert id="saveUserDemo" parameterMap="usermap" statementType="CALLABLE"> 
{call saveuser(?, ?)} 
</ insert > 
    
Map<String, Object> parms = new HashMap<String, Object>(); 		parms.put("addend1", 3);
userDao.saveUserDemo(parms); 
parms.get(“result”);//获得输出参数
1
2
3
4
5
6
7

7. 触发器

什么是触发器？

触发器( trigger )是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。

MySQL 中都有哪些触发器？

在MySQL数据库中有如下6种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

特点

触发器具有以下特点：

1. 与表相关联

触发器定义在特定的表上，这个表称为触发器表。

2. 自动激活触发器

当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。

3. 不能直接调用

与存储过程不同，触发器不能被直接调用，也不能传递或接收参数。

4. 作为事务的一部分

触发器与激活触发器的语句一起作为对一个单一的事务来对待，可以从触发器中的任何位置回滚。

触发器的语法规则

定义触发器的语法规则：

CREATE TRIGGER 触发器名称 触发时机 触发事件 
ON 表名称 
FOR EACH ROW -- 行级触发
BEGIN
	语句
END;
1
2
3
4
5
6

语法解析：

1. 触发器名称：是用来标识触发器的，有用户自定义。
2. 触发时机：其值是before 或 after。
3. 触发事件：其值是 insert，update 和 delete
4. 表名称：标识建立触发器的表名，即在哪张表上建立触发器
5. 语句：是触发器程序体，触发器程序可以使用 begin 和 end 作为开始和结束，中间包含多条语句。

eg：

删除用户时，自动触发删除用户菜单关系

DELIMITER $$
CREATE TRIGGER delete_user_menu BEFORE DELETE 
ON t_user 
FOR EACH ROW 
BEGIN
	DELETE FROM t_user_menu WHERE user_id = old.id; 
END$$;
1
2
3
4
5
6
7

新增用户时，自动向其他表插入数据

DELIMITER $$ 
CREATE TRIGGER save_user_log AFTER INSERT ON user 
FOR EACH ROW 
BEGIN 
	INSERT INTO test(id,NAME)VALUES(new.id,new.account); 
END$$;
    
INSERT INTO user(account)VALUES('jim')
1
2
3
4
5
6
7
8

在行级触发器代码中，可以使用 old 和 new 访问到该行的旧数据和新数据，old和new 是对应表的行记录类型变量。

8. 关联查询

五种关联查询

• 交叉连接（CROSS JOIN）
• 内连接（INNER JOIN）
• 外连接（LEFT JOIN/RIGHT JOIN）
• 联合查询（UNION 与 UNION ALL）
• 全连接（FULL JOIN）

交叉连接（CROSS JOIN）

SELECT * FROM A,B(,C)
或者
SELECT * FROM A CROSS JOIN B (CROSS JOIN C)
#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义
很少使用内连接（INNER JOIN）
SELECT * FROM A,B WHERE A.id=B.id
或者
SELECT * FROM A INNER JOIN B ON A.id=B.id
多表中同时符合某种条件的数据记录的集合
INNER JOIN可以缩写为JOIN
1
2
3
4
5
6
7
8
9
10

内连接分为三类

• 等值连接：ON A.id=B.id
• 不等值连接：ON A.id=B.id
• 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.id

外连接（LEFT JOIN / RIGHT JOIN）

• 左外连接：LEFT OUTER JOIN，以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
• 右外连接：RIGHT OUTER JOIN，以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

联合查询（UNION / UNION ALL）

SELECT * FROM A 
UNION 
SELECT * FROM B UNION ...
1
2
3

• 就是把多个结果集 集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
• 如果使用UNION ALL，不会合并重复的记录行
• 效率UNION高于UNION ALL

全连接（FULL JOIN）

• MySQL 不支持全连接
• 可以使用LEFT JOIN 和 UNION 和RIGHT JOIN 联合使用

SELECT * 
FROM A LEFT JOIN B ON A.id=B.id 
UNION
SELECT * 
FROM A RIGHT JOIN B ON A.id=B.id
1
2
3
4
5

什么是子查询

1. 条件：一条SQL语句的查询结果作为另一个查询语句的条件或查询结果
2. 嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

子查询的三种情况

1. 子查询是单行单列的情况：结果集是一个值，付查询使用：=、<、> 等运算符

-- 查询工资最高的员工是谁？ 
select  * 
from employee
where salary=(select max(salary) from employee);   
1
2
3
4

2. 子查询是多行单列的情况：结果集类似于一个数组，父查询使用：in 运算符

-- 查询工资最高的员工是谁？ 
select  * 
from employee 
where salary in(select salary from employee);    
1
2
3
4

3. 子查询是多行多列的情况：结果集类似于一张虚拟表，不能用于where条件，用于select子句中作为子表

-- 1) 查询出2011年以后入职的员工信息
-- 2) 查询所有的部门信息，与上面的虚拟表中的信息比对，找出所有部门ID相等的员工。
select * 
from dept d,  (select * from employee where join_date > '2011-1-1') e 
where e.dept_id =  d.id;    

-- 使用表连接：
select d.*, e.* 
from  dept d inner join employee e on d.id = e.dept_id 
where e.join_date >  '2011-1-1'  
1
2
3
4
5
6
7
8
9
10

9. 一些需要注意的点

mysql中 in 和 exits 区别

mysql 中的 in 语句是把外表和内标作hash连接，而exists 语句是对外表作loop 循环，每次loop 循环再对内表进行查询。至于exists 与 in 语句的效率问题，要具体区分环境。

1. 如果查询的两个表大小相当，那么用 in 和 exists 差别不大。
2. 如果两个表中一个较小，一个是大表，则子查询表达的用exists，子查询表小的用in。
3. not in 和 not exists ：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以 无论哪个表大，用 not exists 都比 not in 要快。

varchar 与 char 的区别

char 的特点

• char 表示定长字符串，长度是固定的；
• 如果插入数据的长度小于char的笃定长度时，则用空格填充；
• 因为长度固定，所以存取速度要比varchar 快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
• 对于char 来说，最多能存放的字符个数为255，和编码无关

varchar 的特点

• varchar 表示可变长字符串，长度是可变的；
• 插入的数据是多长，就按照多长来存储；
• varchar 在存取方面与 char 相反，它存取慢，因为长度不都订，但正因如此，不占据多余的空间，是时间换空间的做法；
• 对于 varchar 来说，最多能存放的字符个数为65535

总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是稳妥的做法。

varchar(50) 中50的含义

最多存放 50个字符，varchar(50) 和 (200) 存储hello 所占空间一样，但后者在排序时会消耗更多内存，因为order by col 采用 fixed_length 计算col 长度（memory引擎也一样）。在早期 MySQL 版本中，50 代表字节数，现在代表字符数。

int(20) 中20的含义

是指显示祖父的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示。

mysql中int(10)和char(10)以及varchar(10)的区别

• int(10)的19 表示显示的数据的长度，不是存储数据的大小；char(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。
  • int(10) 10位的数据长度 9999999999，占32个字节，int型4位
  • char(10) 10位固定字符擦混，不足补空格 最多10个字符
  • varchar(10) 10位可变字符串，不足补空格 最多10个字符
• char(10) 表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间
• varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示展位不算一个字符

FLOAT和DOUBLE 的区别

• FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
• DOUBLE 类型数据可以存储至多18位十进制数，并在内存中占8字节。

drop、delete与truncate的区别

三者都表示删除，但是三者有一些差别：

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

UNION 与 UNION ALL的区别

• 如果使用UNION ALL，不会合并重复的记录行
• 效率 UNION 高于 UNION ALL

10. SQL优化

为什么要对SQL进行优化

项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断 SQL对程序的运行效率有多大，故很少针对 SQL 进行专门的优化，随着时间的积累，业务数据量的增多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

SQL优化的一些方法

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在where及 order by 涉及的列上建立索引。

2. 应尽量避免索引失效

1、在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null
1

可以在 num 上设置默认值为0，确保表中 num 列没有null 值，然后这样查询：

select id from t where num=0
1

2、应尽量避免 在where子句中使用 != 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描。

3、应尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20
1

4、in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)
1

对于连续的数据，能用 between 就不要用 in 了：

select id from t where num between 1 and 3
1

5、使用LIKE 会导致全表扫描：

select id from t where name like '%abc%'
1

6、应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100
1

应改为：

select id from t where num=100*2
1

7、应尽量避免在where子句中对字段进行函数在操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'
-- name 以 abc 开头的 id
1
2

应改为：

select id from t where name like 'abc%'
1

3. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率。因为 insert 或update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数量最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
4. 尽量使用数据型字段，若置函数值信息的字段尽量不要涉及为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
5. 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
6. 任何地方都不要使用 select * from t ，用具体的字段列代替“*”，不要返回用不到的任何字段。
7. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

SQL 的生命周期

1. 应用服务器与数据库服务器建立一个连接
2. 数据库进程拿到请求sql
3. 解析并生成执行计划，执行
4. 读取数据到内存并进行逻辑处理
5. 通过步骤一的连接，发送结果到客户端
6. 关掉连接，释放资源

mysql 分页

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。

LIMIT 接受一个或两个数字参数。参数必须是一个整形常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是0（而不是1）

SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 
1

为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1：

SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
1

如果只给定一个参数，它表示返回最大的记录行数目：

SELECT * FROM table LIMIT 5; //检索前 5 个记录行 
1

即 LIMIT n 等价于 LIMIT 0,n;