MySQL_学习笔记

一、MySQL通过命令行操作

1. 开启数据库服务

可以通过 DOS 命令启动 MySQL 服务，点击“开始”菜单，在搜索框中输入“cmd”，以管理员身份运行，按回车键，弹出命令提示符界面。然后输入net start mysql，按回车键，就能启动 MySQL 服务，停止 MySQL 服务的命令为net stop mysql。mysql为数据名称。
还可以通过任务管理器中的服务栏找到MySQL的服务点击开启。

2. 连接数据库

格式: mysql -h 主机地址 -u用户名 -p用户密码
注意-p后面是不加空格的。可以直接输入密码，或者回车再输入密码
flush privileges; --刷新权限

mysql -hlocalhost -P3306 -uroot -p****

3. 查看所有的数据库

注意后面有s，而且语句以；结束

show databases;

4. 切换数据库

mysql> use 数据库名

5. 查看数据库中所有的表

同样注意后面有s。如果use数据库了，可以省略from 数据库

show tables;
show tables from mysql;
-- 此处mysql指的是数据库名；

6. 显示数据库中所有的表的信息

describe 表名
--或者
show columns from 表名;

7. 创建一个数据库

create database 数据库名 charset utf8;
-- create database 数据库名 [数据集 utf8];

8. 退出连接

-- 三选一
quit
exit
\q

9. 注释（多行，当行）

--单行注释
/*
多行注释
*/

10. 数据库XXX语言

DDL 定义
DML 操作
DQL 查询
DCL 控制

---

---

---

二、MySQL操作数据库

1. MySQL

2. 操作数据库

2.1 数据库的列类型

数值

类型	作用	大小
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数（常用）	4个字节
bigint	较大的数据	8个字节
float	浮点数（单精度）	4个字节
double	浮点数（双精度）	8个字节
decimal	字符串形式的浮点数	金融运算时一般都使用

字符串

类型	大小
char	字符串固定的大小，0~255
varchar	可变字符串， 0~65535 （常用）
tinytext	微型文本，2^8 - 1
text	文本串，2^16 - 1（保存大文本）

时间日期

• java.util.Date

yyyy-MM-dd HH:mm:ss年-月-日 时:分:秒 大写是区分“ 月 ”与“ 分 ” HH为什么大写，是为了区分 12小时制 与 24小时制 。 小写的h是12小时制，大写的H是24小时制

类型	格式
date	YYYY-MM-DD，日期格式
time	HH:mm:ss，时间格式
datetime	YYYY-MM-DD HH:mm:ss，最常用的时间格式
timestamp	**时间戳，1970.1.1到现在的毫秒数！**常用
year	年份表示

null

• 没有值，未知
• 注意，不要使用NULL进行运算，结果为NULL

2.2 数据库的字段属性（重点）

Unsigned：

• 无符号的整数
• 声明了该列不能声明为负数

zerofill

• 0填充的
• 不足的位数，用0来补充，例如，int(3) ,5的真实表示为：005

自增

• 通常理解为自增，自动在上一条记录的基础上 +1（默认）
• 通常用来设计唯一的主键 index，必须为整数类型
• 可以自定义设计主键自增的起始值和步长

非空 NULL，NOT NULL

• 假设设置为not null，如果不赋值会报错
• NULL，如果不填写，默认值为NULL

默认

• 设置默认的值！
• 例如 ： sex，默认为男，如果不指定该列的值，则会有默认的值！

拓展

每一个都必须有的5个字段

1. id 主键
2. version 乐观锁
3. is_delete 伪删除
4. gmt_create 创建时间
5. gmt_update 修改时间

2.3 创建表

目标 ： 创建一个School表
创建学生表（列，字段）使用SQL创建
学号，登录密码，姓名，性别，出生日期，地址，邮箱

--注意，使用英文()，表名  和  字段  尽量使用  ``  括起来
--AUTO_INCREMENT 自增
--字符串用   单引号括起来！
--所有的语句后面加   ,   英文的，最后一个语句不用加
--PRIMARY_KEY主键，一般一个表只有唯一的主键
CREATE TABLE IF NOT EXISTS 'student'  (
  `id`  INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email`  VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`)--主键
)ENGINE=INNODB DEFAULT CHARSET=utf8
--`列名` 值类型 [NOT　NULL]　DEFAULT　[默认值] COMMENT '注释'
--NOT NULL后面不接DEAFAULT NULL!

创建表一般语法

CREATE TABLE [IF NOT EXISTS] '表名' (
  '字段名' 列类型 [属性] [索引] [注释],
  '字段名' 列类型 [属性] [索引] [注释],
  ...........
  '字段名' 列类型 [属性] [索引] [注释],
)

2.4 数据表类型

数据库引擎

INNODB 默认使用
MYISAM 早期使用

	MYISAM	INNODB
事务支持	不支持（最新版支持）	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，2倍前者

常规使用操作

• MYISAM 节约空间，速度较快
• INNODB 安全性高，多表多用户操作

在物理空间存在的位置

MySQL引擎在物理文件上的区别

• INNODB 在数据库表只有一个 *.frm 文件，以及上级目录下的 ibdata1 文件
• MYISAM对应的文件
  1- *.frm 表结构的的定义文件
  2- *.MYD 数据文件（data）
  3- *.MYI 索引文件

设置数据库表的字符集编码

CHARSET=utf8

不设置的话，会是MySQL默认的字符集编码！（不支持中文）
MySQL的默认编码时Latin1，不支持中文
在my.ini中配置默认的编码

character-set-server=utf8

2.5 修改删除表

修改

关键字： alter

--修改表名：ALTER　TABLE　旧表名　RENAME　AS　新表名
ALTER TABLE teacher RENAME AS teacher1
--增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

--修改表的字段（重命名，修改约束）
--1、ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teachaer1 MODIFY age VARCHAR(11)
--2、ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1)

--删除表的字段：ALTER　TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

MODIFY 和 CHANGE的区别

change用来字段重命名，不能修改字段类型和约束；
modify不用来字段重命名，只能修改字段类型和约束；

--删除表（如果表存在再删除）
DROP TABLE IF EXISTS teacher1

所有的创建和删除操作都尽量加上判断，以免报错
注意点：

• `` 字段名，使用这包裹
• 注释： – 或者 /**/
• sql关键字大小写不敏感
• 所有的符号全部使用英文

---

---

---

三、MySQL简单数据管理

1、外键

外键的添加

方式一，在创建表的时候，就加上约束（麻烦，有点复杂）

CREATE TABLE `grade ` (
  `gardeid` INT(10) NOT NULL AUTO_INCRMENT COMMENT '年级id',
  `gardename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的 gradeid 字段要去引用年级表的  gradeid
-- 定义外键key
--给这个外键添加约束  （执行引用）  references 引用

CREATE TABLE IF NOT EXISTS 'student'  (
  `id`  INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email`  VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  `gradeid` INT(10) NOT NULL COMMENT '学生年级',
  PRIMARY KEY(`id`), 
  KEY FK_gradeid (`gradeid`),
  CONSTRAINT FK_gradeid FOREIGN KEY ('gradeid`) REFERENCESE `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

• 删除有外键关系的表的时候，必须要删除引用其他表的从表，再删除被引用的主表。例如要先删除student表才可以删除grade表

方式二：创建表成功后，添加外键约束

CREATE TABLE `grade ` (
  `gardeid` INT(10) NOT NULL AUTO_INCRMENT COMMENT '年级id',
  `gardename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的 gradeid 字段要去引用年级表的  gradeid
-- 定义外键key
--给这个外键添加约束  （执行引用）  references 引用

CREATE TABLE IF NOT EXISTS 'student'  (
  `id`  INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
  `email`  VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  `gradeid` INT(10) NOT NULL COMMENT '学生年级',
  PRIMARY KEY(`id`)--主键
)ENGINE=INNODB DEFAULT CHARSET=utf8

--创建表的时候没有外键关系
ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (gradeid)

--ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪一个表(哪一个字段)

以上操作都是物理外键，数据库级别的外键一般不建议使用，避免数据库过多时造成困扰

最佳实践

• 数据库就是单纯的表，只存数据，只有行（数据）和列（字段），关系逻辑一般不负责
• 想使用多张表的数据或者外键时，用程序实现

2、插入

关键字： insert

--插入语句
-- insert into 表名 ([字段1，字段2，字段3]) values ('值1','值2','值3'),('值1','值2','值3'),('值1','值2','值3'),....
INSERT INTO `grade` (`gradename`) VALUES ('大四') 

--由于主键自增，我们可以省略（如果不写的表的字段，他会一样匹配，但可以用null占位
INSERT INTO `grade` VALUES ('大三')

-- 一般写插入语句，一定要数据和字段一一对应！

--插入多个字段
INSERT INTO `grade` (`gradename`)
VALUES ('大二'),('大一')

INSERT INTO `student` (`name`) VALUES ('张三')

INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES ('张三',`123456`,`男`)

INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES ('张三',`123456`,`男`),VALUES ('李四',`123456`,`女`)

• 语法： insert into 表名 ([字段1，字段2，字段3]) values ('值1','值2','值3'),('值1','值2','值3'),('值1','值2','值3'),....

注意事项：

1. 字段和字段之间使用 英文逗号 隔开
2. 字段是可以省略的，但是后面的值必须一一对应
3. 可以同时插入多条数据，VALUES后面的值，需要使用，隔开即可VALUES(),()......

3、修改

关键字： update 修改谁 (条件) set 原来的值=新值

--修改学员名字，带了简介
UPDATE `student` SET `name` = `小李` WEHRE id = 1

--不指定条件的情况下，会改动所有的表
UPDATE `student` SET `name` = '阿福'

--修改多个属性，逗号隔开
UPDATE `student` SET `name`='阿尔', `email`='xxxxx@163.com' WHERE id = 1

--语法：
--UPDATE 表名 SET colunm_name = value,[colunm_name = value，colunm_name = value.......] WHERE [条件]

条件： where语句运算符，在某个确切的范围内修改
操作符会返回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<> 或 !=	不等于	5<>6	true
>	大于	5>6	false
<	小于	5<6	true
<=	小于等于	5<=6	true
>=	大于等于	5>=6	false
BETWEEN…AND…	在某个范围内	[2,5]闭区间
AND	&&	5<1 && 0>6	false
OR	或	5>1 or 8=6	true

--通过多个条件定位数据
UPDATE `student` SET `name` = '达瓦' WHERE `name` = '小李' AND `sex` = '女'

语法： UPDATE 表名 SET colunm_name = value,[colunm_name = value，colunm_name = value.......] WHERE [条件]
注意：

• colnum_name 是数据库的列，尽量带上``
• 条件，筛选的条件，如果没有指定，则会修改所有的列
• value，是一个具体的值也可以是变量
• 多个设置的属性之间，使用英文逗号隔开

4、删除

关键字： delete命令

语法：delete from 表名 [where 条件]

--删除数据（避免这样写，会全部删除）
DELETE FROM `student`

--删除指定数据
DELETE FROM `student` WHERE id = 1

关键字： TRUNCATE命令

作用：完全清空一个数据库表，表的结果和索引约束不会变！

--清空student表
TRUNCATE STUDENT

delete 和 truncate 区别

• 相同点： 都能删除数据，都不会删除表的结构
• 不同：（1）truncate 重新设置 自增列 计数器归零（2）truncate 不会影响事务

--测试delete 和 truncate 区别
CREATE TABLE 'text' (
  `id`  INT(4) NOT NULL AUTO_INCREMENT,
  `coll` VARCHAR(20) NOT NULL,
  PRIMARY KEY('id')
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `text` (`coll`) VALUES('1'),('2'),('3')

DELETE FROM `text`    --不会影响自增

TRUNCATE TABLE `text`    --会影响自增

delete删除的问题

重启数据库，

• INNODB 自增列会从1开始（该计数器存在内存中，断电即失）
• MYISAM 继续从上一个自增量开始（存在文件中，不会丢失）

---

---

---

四、MySQL_DQL查询数据

1、DQL

Data Query Language：数据查询语言

• 所有的查询操作都使用 Select
• 简单的查询，复杂的查询都能做
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

Select 完整的语法：

SELECT
      [ALL | DISTINCT | DISTINCTROW]
           [HIGH_PRIORITY]
           [STRAIGHT_JOIN]
           [SQL_SMALL_RESULT]  [SQL_BIG_RESULT]  [SQL_BUFFER_RESULT]
           [SQL_CACHE  | SQL_NO_CACHE]  [SQL_CALC_FOUND_ROWS]
      select_expr  [, select_expr  ...]
      [FROM table_references
           [PARTITION partition_list]
      [WHERE where_condition}
      [GROUP BY {col_name  |  expr  |  position}
            [ASC  |  DESC],  ...   [WITH  ROLLUP]]
      [HAVING   where_condition]
      [ORDER  BY {col_name  |  expr  | positon}
            [ASC  |  DESC],  ...]
      [LIMIT  {[offset,]  row_count  | row_count  OFFSET   offset}]
      [PROCEDURE   procedure_name(argument_list)]
      [INTO  OUTFILE  'file_name'
            [CHARACTER SET charset_name]
            export_options
          |  INTO  DUMPFILE  'file_name'
          |  INTO  var_name  [,var_name]]
      [FOR  UPDATE  |  LOCK  IN  SHARRE  MODE]]  

2、 指定查询字段

--查询全部的学生    select  字段  from  表
SELECT * FROM student

--查询指定的字段
SELECT    `studentNo` ,`studentName`  FROM stundet

--别名，给结果起一个名  AS  可以给字段起别名，也可以给表起别名
SELECT `student`  AS  '学号', 'studentName'  AS  '学生名字'  FROM student  AS  s

--函数   Concat(a,b)，将a和b拼接为一个结果
SELECT CONCAT('姓名：',studentName)  AS   新名字   FROM  student

语法：SELECT 字段 .... FROM 表

去重 distinct
作用：去除 SELECT 查询出来的结果中重复的数据，重复的数据只显示一条

--查询一下有哪些同学参加了考试
SELECT DISTINCT student from result

数据库的列（表达式）

SELECT VERSION()  --查询系统版本
SELECT 100*3-1  AS '计算结果'    --计算表达式
SELECT @@auto_increment_increment    --  查询自增步长（变量）

--学员成绩  +1  查看
SELECT `studentNO`，`studentResult` + 1 AS '提分后' FROM result

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量

3、where条件子句

作用：检索数据中符合条件的值
搜索的条件由一个或多个表达式组成，结果为布尔值

运算符	语法	描述
and	a and b	逻辑与，一假则假
or	a or b	逻辑或，一真则真
not	not a	逻辑非，真为假，假为真

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果字段 a 为空，则结果为真
IS NOT NULL	a is not null	如果字段 a 不为空，则结果为真
BETWEEN	a between b and c	若 a 在 b 和 c 之间，则结果为真
LIKE	a like b	SQL匹配，如果a 匹配 b，则结果为真
IN	a in (a1,a2,a3…)	假设a在a1，或者a2，其中某一个值中，则结果为真

注意：

• like结合 %
  %代表包含，有开头、结尾的位置。%此%意思为包含 此 的字符。
  而 _ 代表一个字符。例如，声_代表字符以声开头并且后面只跟一字
• in （具体的一个或者多个值，是完全匹配其中一个才为真）

4、联表查询

SELECT语法

• 注意：[]括号代表可选的，{}括号代表必选的

SELECT [ALL  |  DISTINCT]
{*  |  table.*  |  [table.field1[as  alias1][,table.field2[as  alias2]][,.....]]}
FROM  table_name  [as  table_alias]
    [left  |  right  |  inner  join  table_name2]  --  联合查询
    [WHERE  ...]  --  指定结果需满足的条件
    [GROUP  BY  ...]   --  指定结果按照那几个字段分组
    [HAVING]  --  过滤分组的记录必须满足的次要条件
    [ORDER  BY ...]  --  指定查询记录按一个或多个条件排序
    [LIMIT  {[offset,]row_count  |  row_countOFFSET  offset}];
    --  指定查询的记录从哪天到哪条

DQL七种联表

三个基本连接

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

连表查询的思路

1. 我要查询哪些数据 select…
2. 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
3. 确定交叉点（表之间有哪些数据是相同的，可以作为连接条件的）
4. 假设存在多张表连接，就一个一个的连接，直到所有的表连接起来
   from a left join b on xxx
   from a right join b on xxx

• join (连接的表) on (判断条件) --连接查询
• where 等值查询

SELECT s.studentNO,studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNO = r.studentNO

-- Right Join
SELECT s.studentNO,studentName,subjectNo,studentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNO = r.studentNO

--left join
SELECT s.studentNO,studentName,subjectNo,studentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNO = r.studentNO

自连接

自己的表和自己的表连接，核心：一张表拆开两（多）张表看

• 自连接练习的数据库

CREATE TABLE `school`.`category`( 
`categoryid` INT(3) NOT NULL COMMENT 'id', 
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');

• 分开为两个表来看：

（一）学科分类表：

学科id（categoryid）	学科名(categoryname)
2	信息技术
3	软件开发
5	美术设计

（二）课程隶属表

课程id（categoryid）	课程名(categoryname)	学科id（pid）
4	数据库	3
6	web开发	3
7	ps技术	5
8	办公信息	2

• 可以看出上面两个表的交叉点是学科id
• 表结构

--查询课程名和课程所属学科名
--=====使用inner连接=====那么结果只有课程表里面的课程
SELECT c1.categoryname AS '课程名',c2.categoryname AS '学科名'
FROM category AS c1    --当作 课程表
INNER JOIN category AS c2    --当作 学科表
ON c1.pid = c2.categoryid      --课程的隶属学科id等于学科的id

-=====使用left连接=====结果以左表为基础，左表数据全保留，然后去匹配右表，（right join右连接则反过来）
-=====但是在自连接中使用左连接或右连接要注意交叉条件的改变，因为查询逻辑会随着表连接方式不同而改变，不能单纯的改变连接关键字就想得到一样的逻辑查询结果

自然连接的结果
左连接的结果
右连接的结果

5、分页和排序

排序

关键字： order by [ASC | DESC]
不写，默认是升序。
ASC 表示升序，DESC 表示降序。

分页

关键字： limit
limit (n - 1) * pageSize , pageSize

• pagaSize：页面大小

• n：当前页

• (n - 1)* pageSize：起始值

• 总页数：（向上取整）数据总数/页面大小

• 语法：limit（查询起始的下标，总共查询的总数）
  从第 (n - 1) * pageSize 数据开始取，直到取够了 pageSize 这么多的数量

6、子查询和嵌套查询

嵌套查询指的是一个查询语块可以嵌套在另外一个查询语句块的where子句或者having子句中，前者为子查询或内查询，后者为父查询或外查询。

• 不相关子查询：子查询里面没有依赖父查询。
• 相关子查询：子查询条件依赖于父查询。

in，子查询

• in 关键字用于判断表达式是否存在多值列表中，有则true，无则false。返回在多值列表中的记录。

带比较运算符的子查询

• 指父查询与子查询之间通过比较运算符连接，并且子查询返回的是单值，才可以用 = 、<、 >、 != 、>=、 <= 等比较运算符连接。

any、all，子查询

• 使用条件：子查询返回多值序列

• any：只需要满足与多值序列中的一个值，满足比较关系就返回true。

• all：满足与多值序列中的全部值，满足比较关系才返回true。

exists，子查询

• exists代表存在量词，带有EXISTS的子查询不返回任何数据，只产生逻辑真值“true”或者逻辑假值“false”。

• 使用exists的嵌套语句，若子查询结果不为空，则exists返回true，否则返回false。

• 使用exists引出的子查询，其目标表达式列可以使用*，因为带exists的子查询只返回真值或假值，给出列名无实际含义。

派生表查询

• select 查询的结果也是一张表，可以作为出现在from子句后面作为派生表进行查询。

7、分组和过滤

聚合函数 [aggregate_function (expression) ]

因为聚合函数对一组值进行操作，所以它通常与SELECT语句的GROUP子句一起使用。 GROUP BY子句将结果集划分为值分组，聚合函数为每个分组返回单个值。

COUNT()	计数
SUN()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

• 除COUNT(*)函数外，SQL聚合函数忽略null值。只能将聚合函数用作表达式。而COUNT(确定的字段)这个是会忽略null值。

GROUP BY

• GROUP BY语句用来与聚合函数(aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.)联合使用来得到一个或多个列的结果集。

--语法：
SELECT column1, column2, ... column_n, aggregate_function (expression)            

FROM tables            

WHERE predicates            

GROUP BY column1, column2, ... column_n;

注意
因为聚合函数通过作用于一组数据而只返回一个单个值，因此，在SELECT语句中出现的元素，要么为一个聚合函数的输入值，要么为GROUP BY语句的参数，否则会出错。

HVAING

• having是分组（group by）后的筛选条件，分组后的数据组内再筛选。
  而where则是在分组前筛选

• HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

• HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

--语法：

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

补充

count(1)、count(*)与count(列名)的执行区别

官方文档：SQL Function and Operator Reference

8、ACID

数据库中的事务（Transaction）有四个特性，分别是：原子性（Atomicity），一致性（Consistency），隔离性（lsolation），持久性（Durability）

所谓事务，它是一系列操作的序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。（执行逻辑功能的一组指令或者操作称之为事务）

1）原子性

一个事务是一个不可再分割的工作单位，事务中的操作要么都执行，要么都不执行。

2） 一致性

执行一个事务前和后，数据库的完整性约束没有被破坏。也就是说事务不能破坏数据库的完整性以及业务逻辑的一致性。

3） 隔离性

多个事务并发时，每个事务应该是隔离的，一个事务不应影响其他事务的运行效果。

4） 持久性

事务执行完成后，该事务对数据库的更改便持久到了数据库中，这个更改是永久的。

9、事务并发问题

1） 脏读：

事务A读取了事务B的更新的数据，但是事务B回滚了，导致A读取的为脏数据。

2） 不可重复读：

事务A读取同一数据两次，但是在两次之间事务B对该数据进行了修改并提交，导致事务A读取两次读取不一致。

3）虚读（幻读）：

一个事务读取到了别的事务插入的数据，导致前后读取不一致。

四种隔离级别设置

设置	描述
Serializable	可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read	可避免脏读、不可重复读情况的发生。（可重复读）
Read committed	可避免脏读情况发生（读已提交）。
Read uncommitted	最低级别，以上情况均无法保证。(读未提交)

10、索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构，索引是一种数据结构。

分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引（PRIMARY KEY）：
  唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（UNIQUE KEY）：
  避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
• 常规索引（KEY/INDEX）
  默认的，index、key关键字来设置
• 全文索引（FULLTEXT）
  在特定数据库引擎才有，例如MyISAM。可以快速定位数据

索引原则

• 索引并非越多越好
• 不要对经常变动的数据添加索引
• 小数据量的表不需要添加索引
• 索引一般加在常用来查询的字段上
• 可以理解索引为服务查询的一个结果

【MySQL】索引背后的数据结构及算法原理

【MySQL优化】——看懂explain