MySQL入门~入土（详细版）

目录

一、据库的介绍及安装

1.1数据库的介绍

1.2常见的数据库

1.3数据库的安装和使用

1.4MySQL数据库的启动和登录

        1.4.1通过控制台访问MySQL       

1.4.2Navicat图形化工具连接MySQL

二、SQL概念

2.1 什么是SQL

2.2 SQL的作用

 2.3 SQL语句的分类

2.4 MySQL语法

三、DDL操作数据库

3.1创建数据库

3.2查看数据库

3.3修改数据库

3.4删除数据库

3.5使用数据库

MySQL基础

四、DDL操作表

4.1创建表

4.2MySQL中数据类型

4.2.1常用的数据类型

4.2.2详细的类型

4.3在MySQL中创建表

4.4修改表结构

4.4.1 添加列 ：ADD

4.4.2 修改列类型：modify

4.4.3 修改列名称：change

4.4.4 删除列：drop

4.4.5 修改表名

4.4.6 修改字符集

五、DML操作数据

5.1 插入记录

5.1.1语法

5.1.2批量插入数据（蠕虫复制）

5.1.3INSERT注意事项

5.2 更新表记录

5.2.1基本语法格式

5.2.2不带条件的数据进行修改

5.2.3带条件的数据修改

5.3 删除表记录

5.3.1 语法格式

5.3.2 不带条件删除数据

5.3.3 带条件的删除数据

六、DQL查询数据

6.1 简单查询

6.2 带条件查询

6.3 除重

MySQL多表操作和数据库设计

七、DQL中的数据查询

7.1 查询的结果参与运行

7.2 条件查询

7.3 排序

7.4 聚合函数

7.5 分组

7.6 Limit

7.7 查询总结

7.7.1 SQL语句编写顺序

7.7.2 SQL语句执行顺序

八、表约束

8.1 约束类型

8.2 主键约束

MySQL链表操作

九、数据库表的约束

9.1 唯一约束

9.2 非空约束

9.3 默认值

9.4 外键约束

9.5 数据约束总结

十、表与表之间的关系

10.1 表之间的关系

10.2 一对多

10.3 多对多

10.4 一对一

十一、数据库设计规范

11.1 1NF

11.2 2NF

11.3 3NF

11.4 总结

十二、连表查询

12.1 笛卡尔积现象

12.2 内连接

12.3 左外连接

12.4 右外连接

十三、子查询

13.1 子查询作为条件

13.2 子查询作为枚举条件查询

13.2.1 查询与名为'King'同一部门的员工信息

13.2.2 工资高于60部门所有人的信息

13.3 子查询（作为一张表）

13.3.1 查询员工表中工资排名前 5 名的员工信息

MySQL事务

十四、事务

14.1 事务的基本使用

14.2 事务的原理

十五、事务的隔离级别

15.1 事务的四大特征ACID

15.2 事物的隔离级别

十六、视图

16.1 概念

16.2 视图的特点

16.3 视图的创建

16.3.1创建视图

16.3.2使用视图

16.4 视图的修改

16.4.1修改视图

16.5 视图的删除

16.5.1删除视图

16.6 视图的注意事项

十七、DCL （Date  Controller  Language）

17.1 创建新用户

17.2 给用户授权

17.3 撤销权限

---

一、据库的介绍及安装

1.1数据库的介绍

数据的存储方式:

        JAVA中创建对象 Student   s   =  new  Student (1,川川) 存在内存中

        

储存位置	优点	缺点
内存	快	不能持久保存
文件	持久保存	操作不方便
数据库	1、持久保存 2、方便查询 3、对数据管理方便	需要购买，占用资源

1.2常见的数据库

Oracle：收费，Oracle公司的大型产品

DB2：IBM收费，大型

Microsoft SQL server：微软，中型

Access：微软，小型

MySQL：中小型，免费开源，Sun

SQLite：嵌入式的小型数据库，应用在手机端

1.3数据库的安装和使用

                                                 

 

 

 

***表空间路径

 

***并发连接上限：15

 

是否启动TCP/IP连接，设置端口（MySQL默认端口3306），如果不启动的话，就只能在自己电脑上使用MySQL了。“Enable Strict Mode”启动标准模式，不允许MySQL有任何细小的语法错误！

 

    编码：utf-8

注意：选择最后一个选项，选择utf-8编码

 

把MySql设置为WIndow服务

注意：选中第二选项，自动配置window的环境变量

 

设置root用户的密码 

 

 

安装成功

 1.4MySQL数据库的启动和登录

        1.4.1通过控制台访问MySQL       

默认安装好MySQL之后，会自动启动MySQL服务

启动MySQL服务的方式1：

直接去window的服务器中去找MySQL服务

 也可以在DOS窗口中通过命令启动

 说明该DOS窗口中没有启动MySQL服务权限，这个时候DOS窗口必须是管理员身份去启动

 

在控制台中连接MySQL数据库

登录方式1：

mysql -u用户名 -p密码 #默认登录的是本机数据库

说明MySQL安装的时候没有将环境变量配置在PATH中

所以，需要重新配置MySQL的环境变量

 说明已经以root用户身份登录进MySQL数据库中了

登录方式2:

mysql -hIP地址 -u用户名 -p密码

“10.11.52.46”

退出MySQL:

exit或者quit

1.4.2Navicat图形化工具连接MySQL

 

连接名：给当前连接起个名称

主机名或IP地址：如果是本机就是localhost，如果是远程的则写上对方服务器的ip地址

端口：默认就是3306

用户名：root

密码：

---

二、SQL概念

2.1 什么是SQL

Structured Query Language:结构化查询语言

2.2 SQL的作用

1.是数据库中一种规范语言，各种数据库都支持

2.通用的数据库操作语言

3.不同的数据库还有存在个别的SQL差异

 2.3 SQL语句的分类

1. DDL(data definition language):数据定义语言，创建数据库，创建表

2. DML(data manager language):数据操作语言， 增加数据、删除数据、修改数据

3. DQL：数据查询语言，查询操作

4. DCL：数据控制语言，用户权限

2.4 MySQL语法

1.SQL中不区分大小写，建议关键词用大写

2.每条SQL语句都需要一个分号结束

3.3种注释

注释的语法	说明
--	单行注释
/*   */	多行注释
#	MySQL特有的注解方式

---

三、DDL操作数据库

数据库的定义语言，主要用来创建库和表 库：可以理解成各种数据的集合 学科 Java学科 表：某类数据的集合

班级   xxxx

学生   数据

3.1创建数据库

• 方式1

CREATE DATABASE 数据库名;--创建数据库，如果存在则报错

• 方式2

CREATE DATABASE  IF NOT EXISTS 数据库名;--如果数据库不存在则创建

• 方式3

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

CREATE DATABASE  MyFirst;  #创建一个MyFirst数据库，如果存在则提示错误

CREATE DATABASE IF NOT EXISTS MyFirst; #如果不存在则创建一个MyFirst数据库

CREATE DATABASE MyThird CHARACTER SET utf8; #创建一个指定utf8编码的数据格式，默认Mysql的编码是按照安装时指定的编码格式

编码：ANSII,Unicode,GBK,GB2312,BIG5,UTF-8

3.2查看数据库

SHOW DATABASES; #显示所有数据库

SHOW CREATE DATABASE MySecond;  #显示MySecond数据库信息

3.3修改数据库

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集；

ALTER DATABASE MyFirst CHARACTER SET UTF8;

3.4删除数据库

DROP DATABASE 数据库名；

DROP DATABASE MyFirst;

3.5使用数据库

USE 数据库名;

存储引擎：Innodb/myisam

---

MySQL基础

四、DDL操作表

创建好数据库之后，接下来我们可以考虑向该库中存放数据，但是数据不能直接存储在库中，数据需要存储在库中的“货架”中。这个货架就是数据库中表

4.1创建表

CREATE TABLE 表名(
   字段名1  字段类型1，
   字段名2  字段类型2，
   ...
)

create table student(
 	学号 int,
    姓名 varchar(10),
    年龄 int,
    性别 char(2),
    地址 varchar(200),
    身高 double/float/deciaml(3,2),//BigDecimal
    注册时间 date
)

 字段、列：描述表中的标题

行、记录：表中描述一个信息的记录

4.2MySQL中数据类型

4.2.1常用的数据类型

类型	说明
int	整数
double	浮点类型
varchar/char	字符串类型
date	日期类型 格式yyyy-MM-dd

整型：byte short int long
浮点类型：float double
字符类型：char
布尔类型：boolean

4.2.2详细的类型

• 数字类型

类型	大小	范围（有符号）	范围（无符号）
TINYINT	1 byte	(-128，127)	（2， 255）	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

• 字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

char和varchar的区别(面试题)

char：指定好大小之后就固定了，如果没有占用则用空格补充

varchar：指定好大小之后，会根据实际大小进行计算

所以，varchar占用空间比char灵活，但是会耗费效

• 日期类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:mm:ss	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

Date/Time/Datetime

4.3在MySQL中创建表

显示当前库

mysql> show tables;

删除表

mysql> drop table bb;
mysql> drop table if exists bb;

创建表

mysql> create table student(
    -> id int,
    -> name varchar(10),
    -> birthday date,
    -> height decimal(3,2),
    -> gender char(2)
    -> );
Query OK, 0 rows affected (0.04 sec)

查看表结构

mysql> desc student;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Id       | int(11)      | YES  |     | NULL    |       |
| name     | varchar(10)  | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
| height   | decimal(3,2) | YES  |     | NULL    |       |
| gender   | char(2)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.04 sec)

4.4修改表结构

4.4.1 添加列 ：ADD

alter table 表名 add 列名 类型；

#给学生表添加一个分数字段
mysql> alter table student add score double;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.4.2 修改列类型：modify

alter table 表名 modify 列名 新类型；

#修改学生表分数字段类型
mysql> alter table student modify score decimal(3,1);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.4.3 修改列名称：change

alter table 表名 change 旧名称 新名称 类型;

mysql> alter table student change score avg_score decimal(3,1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.4.4 删除列：drop

alter table 表名 drop 列名；

#删除student表中的height字段
mysql> alter table student drop height;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

总结:

修改表中类型和字段名的命令：add:添加，modify：修改类型，change：修改字段名称，drop：删除字

4.4.5 修改表名

rename table 表名 to 新表名；

mysql> rename table student to student_info;
Query OK, 0 rows affected (0.03 sec)


mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student_info  |
+---------------+
1 row in set (0.00 sec)

4.4.6 修改字符集

alter table 表名 character set 字符集；

mysql> create table scores(
    -> id int,
    -> subject_name varchar(20),
    -> student_name varchar(20),
    -> score double
    -> ) character set utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table scores;
                                                         
CREATE TABLE `scores` (
  `id` int(11) DEFAULT NULL,
  `subject_name` varchar(20) DEFAULT NULL,
  `student_name` varchar(20) DEFAULT NULL,
  `score` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

注意：在表名和字段名上添加，这个``符号的作用是为了防止与关键词重名

---

五、DML操作数据

5.1 插入记录

5.1.1语法

insert [into] 表名 ([字段名1,字段名2,字段名N...]) values (字段值1,字段值2,字段值N...)

• insert into 表名：表示向哪张表中添加数据

• 字段名1,字段名2,字段名N...：表示给该表的哪些字段添加值

• values (字段值1,字段值2,字段值N...)：给字段添加的具体值

insert into employee(employee_id,first_name, last_name, email, phone_number,hire_date, job_id, salary, commission_pct, manager_id, department_id) values(1001,'san','zhang','zhangsan@126.com','110','2020-2-28','100', 2000,0.1,1003,2001);

#如果给当前表中的所有字段都添加了值，则可以将字段名省略
insert into employee
values(1002,'si','li','lisi@126.com','112','2020-3-25','200', 2400,0.5,1003,2002);

insert into employee(employee_id,first_name, last_name) values(1003,'wu','wang');


#将表中所有的字段全部都写出来了
insert into student_info(id, name, birthday,gender,avg_score) values (1001,'tom','1998-3-4','N', 89.9);

#如果需要向该表所有字段添加数据，则字段名可以省略
insert into student_info values (1003,'jack','1997-12-5','N', 90);

#也可以插入部分字段数据
insert into student_info(id,birthday,name) values (1003,'jack','1997-12-5');

通过DOS创建向表中添加中文

mysql> insert into student_info(id, name) values(1004,'川川');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC2\xFC\xD3\xF1' for column 'name' at row 1
mysql>

设置dos窗口编码格式

mysql>

#只能在当次窗口有效 Query OK, 0 rows affected (0.00 sec)

5.1.2批量插入数据（蠕虫复制）

insert into 表名（字段1，字段2，字段3...） select 字段1，字段2，字段3... from 表名；

mysql> insert into student_info(id, name, birthday,gender,avg_score) select id,name,birthday,gender,avg_score from student_info;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

insert into employee  select * from employee;

5.1.3INSERT注意事项

1.插入的数据应该要和字段的类型保持一致 2.数据的大小应该在列类型的范围之间 3.在values中的值位置要和字段名的位置一一对应 4.字符和日期都需要用引号括起来 5.不指定列名或使用null，表示插入空值了

5.2 更新表记录

5.2.1基本语法格式

update 表名 set 字段1=值1,字段2=值2... [where 条件表达式]

• update 表名 ：修改对应表的数据

• set 字段1=值1：表示修改字段1的值为值1

• where：满足条件的数据进行修改

5.2.2不带条件的数据进行修改

mysql> update student_info set gender='男';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

不带条件会影响表中的所有行字段数据

5.2.3带条件的数据修改

update 表名 set 字段=值 where 字段名=值

mysql> update student_info set birthday='1995-3-4' where name='jack';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

修改的动作是一行记录一行记录的进行where条件判断，如果该行的判断成立则会修改该行对应字段值。

mysql> update student_info set birthday='1996-3-4',gender='女',avg_score=99 where name='jack';
Query OK, 1 row affected (0.01 sec)

where后面的条件可以有多种操作符

操作符	说明
=	判断是否相等
!= <>	判断不相同
>	大于
<	小于
>=	大于等于
<=	小于等于
between and	在范围之中

#将编号大于1003的性别修改成男的
mysql> update student_info set gender='男' where id>1003;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#空判断需要使用is或者 is not
mysql> update student_info set birthday='1997-3-9' where avg_score is null;


update employee set last_name='song' where email = null

update t_employees set last_name='james' where salary between 17000 and 24000;(推荐)
update t_employees set last_name='james' where salary>=17000 and salary<=24000;

5.3 删除表记录

5.3.1 语法格式

delete from 表名 [where 条件表达式]

• 如果不指定where则表示将表中的所有数据全部删除

5.3.2 不带条件删除数据

#将student_info表中的所有数据全部删除
delete from  student_info;

#删除表中的所有数据，比delete更彻底
truncate table student_info;

delete和truncate的区别是，truncate相当于删除了该表然后重新创建了一个

5.3.3 带条件的删除数据

delete from student_info where 条件

delete from student_info where avg_score is null;

DML总结：

• insert: insert into 表名（字段名）values(值)

• update：update 表名 set 字段名=值 where 条件

• delete： delete from 表名 where 条件

---

六、DQL查询数据

DQL:数据查询语言，通过select实现数据的查询

6.1 简单查询

SELECT 字段1，字段2，字段3... FROM 表名;

select id,name,gender,birthday,avg_score from student_info;

mysql> select id,name,gender,birthday,avg_score from student_info;
+------+------+--------+------------+-----------+
| id   | name | gender | birthday   | avg_score |
+------+------+--------+------------+-----------+
| 1001 | tom  | 女      | 1998-03-04 |      89.9 |
| 1003 | jack | 女      | 1996-03-04 |      99.0 |
+------+------+--------+------------+-----------+
2 rows in set (0.01 sec)

select * from 表名;#将表中的所有字段全部显示出来

#显示所有字段
mysql> select * from student_info;
+------+------+------------+--------+-----------+
| Id   | name | birthday   | gender | avg_score |
+------+------+------------+--------+-----------+
| 1001 | tom  | 1998-03-04 | 女      |      89.9 |
| 1003 | jack | 1996-03-04 | 女      |      99.0 |
+------+------+------------+--------+-----------+
2 rows in set (0.00 sec)

#显示部分字段的数据
mysql> select name, birthday from student_info;
+------+------------+
| name | birthday   |
+------+------------+
| tom  | 1998-03-04 |
| jack | 1996-03-04 |
+------+------------+
2 rows in set (0.00 sec)

给字段起别名:as

#as可以省略
mysql> select name,gender as 性别, birthday from student_info;
+------+------+------------+
| name | 性别    | birthday   |
+------+------+------------+
| tom  | 女    | 1998-03-04 |
| jack | 女    | 1996-03-04 |
+------+------+------------+
2 rows in set (0.00 sec)

mysql> select name,gender 性别, birthday from student_info;
+------+------+------------+
| name | 性别    | birthday   |
+------+------+------------+
| tom  | 女    | 1998-03-04 |
| jack | 女    | 1996-03-04 |
+------+------+------------+
2 rows in set (0.00 sec)

6.2 带条件查询

select 字段1，字段2... from 表 where [条件表达式]

mysql> select * from student_info where gender='男';
+------+-------+------------+--------+-----------+
| Id   | name  | birthday   | gender | avg_score |
+------+-------+------------+--------+-----------+
| 1004 | jerry | 1997-12-03 | 男       |      58.0 |
| 1005 | trump | 1945-03-06 | 男       |      10.0 |
+------+-------+------------+--------+-----------+
2 rows in set (0.01 sec)

Java中&& 和& 区别？

表达式1 && 表达式2：如果第一个表达式的结果为false,则第二个表达式不执行了

表达式1 & 表达式2：第二个表达式不管第一个表达式的结果是什么都需要执行

mysql> select * from student_info where gender='男' and avg_score>50;
+------+-------+------------+--------+-----------+
| Id   | name  | birthday   | gender | avg_score |
+------+-------+------------+--------+-----------+
| 1004 | jerry | 1997-12-03 | 男       |      58.0 |
+------+-------+------------+--------+-----------+
1 row in set (0.01 sec)


表达式1||表达式2   | 

6.3 除重

将对应字段中值相同的进行合并

select distinct 字段名1，字段2... from 表名

mysql> select gender from student_info;
+--------+
| gender |
+--------+
| 女      |
| 女      |
| 男      |
| 男      |
+--------+
4 rows in set (0.00 sec)

mysql> select distinct gender from student_info;
+--------+
| gender |
+--------+
| 女     |
| 男     |
+--------+
2 rows in set (0.01 sec)

---

MySQL多表操作和数据库设计

七、DQL中的数据查询

select 字段1，字段2... from 表名 [where 条件表达式]

7.1 查询的结果参与运行

某列数据和固定值运行

select 列名1+固定值 from 表名

mysql> select name,avg_score+10 from student_info;
+-------+--------------+
| name  | avg_score+10 |
+-------+--------------+
| tom   |         99.9 |
| jack  |        109.0 |
| jerry |         68.0 |
| trump |         20.0 |
+-------+--------------+
4 rows in set (0.01 sec)

mysql> select name,avg_score+10 as new_score from student_info;
+-------+-----------+
| name  | new_score |
+-------+-----------+
| tom   |      99.9 |
| jack  |     109.0 |
| jerry |      68.0 |
| trump |      20.0 |
+-------+-----------+
4 rows in set (0.00 sec)

某列和其他列数据参与运算

select 列名1 + 列名2 from 表名；

注意：参入运行的列类型必须是数值类型

有时候我们可能会需要将两个列进行字符串拼接

mysql> select name+gender from student_info;
+-------------+
| name+gender |
+-------------+
|           0 |
|           0 |
|           0 |
|           0 |
+-------------+
4 rows in set, 8 warnings (0.00 sec)
#注意在mysql中+符合不能直接进行字符串拼接
#concat(字符串1，字符串2，字符串...):表示将多个字符串进行拼接
mysql> select concat(name, '-',gender) from student_info;
+--------------------------+
| concat(name, '-',gender) |
+--------------------------+
| tom-女                    |
| jack-女                   |
| jerry-男                   |
| trump-男                   |
+--------------------------+
4 rows in set (0.00 sec)

#now():显示当前时期
#year():获取年
#month():获取月
#day():获取日
"2020年6月11日"
select concat(year(now()),'年',month(now()),'月', day(now()),'日');

7.2 条件查询

没有条件则会将所有的数据全部查询显示，一般我们需要对记录做一些过滤，就需要添加条件进行查询了

select 字段名 from 表名 where 条件；

1、运算符

比较运算符	说明
=，>，<，<=，>=，!=，<>	判断
between ... and ...	"包头又包尾"
in(集合)	集合表示多个值，值之间用，隔开
is null/ is not null	判断是否为Null
like '值'	模糊查询
and && 、or || 、 ! not	逻辑运行

mysql> select employee_id, salary from t_employees where salary>=10000 and salary<=17000;

mysql> select employee_id, salary from t_employees where salary between 10000 and 17000;

mysql> select employee_id,salary,job_id from t_employees where JOB_ID in ('IT_PROG','AC_ACCOUNT');
mysql> select employee_id,salary,job_id from t_employees where JOB_ID='IT_PROG' or JOB_ID='AC_ACCOUNT'

select 字段名 from 表名 where 字段名 like '通配符字符串'

模糊查询、通配符查询：_ %

% : 表示任意多个字符

_ : 表示任意一个字符

mysql> select first_name from t_employees where first_name like 'A%';
+------------+
| first_name |
+------------+
| Alexander  |
| Alexander  |
| Adam       |
| Alberto    |
| Allan      |
| Amit       |
| Alyssa     |
| Alexis     |
| Anthony    |
| Alana      |
+------------+
10 rows in set (0.00 sec)

mysql> select first_name from t_employees where first_name like '_A%';
+------------+
| first_name |
+------------+
| David      |
| Valli      |
| Nancy      |
| Daniel     |
| Karen      |

#多种条件组合查询
mysql> select * from t_departments where manager_id is not null and location_id > 1700;
+---------------+------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME  | MANAGER_ID | LOCATION_ID |
+---------------+------------------+------------+-------------+
| 20            | Marketing        | 201        | 1800        |
| 40            | Human Resources  | 203        | 2400        |
| 70            | Public Relations | 204        | 2700        |
| 80            | Sales            | 145        | 2500        |
+---------------+------------------+------------+-------------+
4 rows in set (0.00 sec)

2、分支结构查询

CASE
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	WHEN 条件3 THEN 结果3
	ELSE 结果
END

• 注意：通过使用CASE END进行条件判断，每条数据对应生成一个值。

• 经验：类似 Java 中的switch。

#查询员工信息（编号，名字，薪资 , 薪资级别<对应条件表达式生成>）
SELECT employee_id , first_name , salary , department_id , 
       CASE
           WHEN salary>=10000 THEN 'A'
           WHEN salary>=8000 AND salary<10000 THEN 'B'
           WHEN salary>=6000 AND salary<8000  THEN 'C'
           WHEN salary>=4000 AND salary<6000  THEN 'D'
   ELSE 'E'
       END as "LEVEL"
FROM t_employees;

3、时间查询

语法：SELECT 时间函数([参数列表])

经验：执行时间函数查询，会自动生成一张虚表（一行一列

时间函数	描述
SYSDATE()	当前系统时间（日、月、年、时、分、秒）
CURDATE()	获取当前日期
CURTIME()	获取当前时间
WEEK(DATE)	获取指定日期为一年中的第几周
YEAR(DATE)	获取指定日期的年份
HOUR(TIME)	获取指定时间的小时值
MINUTE(TIME)	获取时间的分钟值
DATEDIFF(DATE1,DATE2)	获取DATE1 和 DATE2 之间相隔的天数
ADDDATE(DATE,N)	计算DATE 加上 N 天后的日期

#查询当前时间
SELECT SYSDATE();

#查询当前时间
SELECT NOW();

#获取当前日期
SELECT CURDATE();

#获取当前时间
SELECT CURTIME();

4、字符串查询

语法： SELECT 字符串函数 ([参数列表])

字符串函数	说明
CONCAT(str1,str2,str....)	将 多个字符串连接
INSERT(str,pos,len,newStr)	将str 中指定 pos 位置开始 len 长度的内容替换为 newStr
LOWER(str)	将指定字符串转换为小写
UPPER(str)	将指定字符串转换为大写
SUBSTRING(str,num,len)	将str 字符串指定num位置开始截取 len 个内容

#拼接内容
SELECT CONCAT('My','S','QL');

#字符串替换
SELECT INSERT('这是一个数据库',3,2,'MySql');#结果为这是 MySql 数据库

#指定内容转换为小写
SELECT LOWER('MYSQL');#mysql

#指定内容转换为大写
SELECT UPPER('mysql');#MYSQL

#指定内容截取
SELECT SUBSTRING('JavaMySQLOracle',5,5);#MySQL

7.3 排序

select 字段 from 表名 where 字段=值 order by 字段 asc|desc

• asc：升序，默认值

• desc：降序

可以通过将查询出来的结果按照指定的字段排序显示，不会影响数据库中数据的顺序

mysql> select employee_id,first_name,salary from t_employees order by salary asc;

组合排序：同时对多个字段进行排序，如果第一个字段相同，则按照第二字段进行排序，依次类推！

select 字段 from 表名 where 字段=值 order by 字段1 asc|desc,字段2 asc|desc

mysql> select employee_id,first_name,salary from t_employees order by salary asc,employee_id desc;

#注意：先过滤数据（where条件），后排序
mysql> select employee_id,first_name,salary from t_employees where salary>10000 order by salary asc,employee_id desc;

7.4 聚合函数

将表中某列的数据进行统计，统计过程中所使用的一些函数我们成为聚合函数。

注意：聚合函数默认会忽略空值NULL

SQL中的聚合函数	说明
min(列名)	求这一列的最小值
max(列名)	求这一列的最大值
sum(列名)	求这一列的总和
avg(列名)	求这一列的平均值
count(列名、*)	统计该列有多少行

mysql> select avg(comm) from t_employees;
mysql> select sum(comm) from t_employees;
mysql> select min(comm) from t_employees;
mysql> select max(comm) from t_employees;

#注意会自动忽略该列的空行
mysql> select count(comm) from t_employees;

mysql> select count(*) from t_employees;

mysql> select job_id,count(job_id),max(salary) max,min(salary) min,avg(salary) avg from t_employees where count(job_id)>10 group by job_id;
ERROR 1111 (HY000): Invalid use of group function
#在where后面不能直接使用聚合函数

#显示薪水最低员工的信息
#错误的，where后面不能直接使用聚合函数
mysql> select * from t_employees where salary=min(salary);

select min(salary) from t_employees;

#子查询
select * from t_employees where salary=(select min(salary) from t_employees);

7.5 分组

根据指定的列将对应数据放在一个组中

select 字段名 from 表名 group by 分组字段 [having 条件]

注意：分组之后只能显示该组的公共信息

先筛选年龄大于18岁的，然后按照性别分组，之后显示每个组中最大的年龄！

select gender,max(age) from student_info where age >18 group by gender

#将Job_id相同的员工放在一个组中，然后显示组名
mysql> select job_id  from t_employees group by job_id;

分组一般是和聚合函数在一起使用！

#job_id:是组信息
#count(job_id):是统计该组中的数据个数
mysql> select job_id,count(job_id)  from t_employees group by job_id;
+------------+---------------+
| job_id     | count(job_id) |
+------------+---------------+
| AC_ACCOUNT |             1 |
| AC_MGR     |             1 |

分组以后如果需要对组中数据进行筛选则需要使用having

#筛选出员工个数大于10以上的组
mysql> select job_id,count(job_id),max(salary) max,min(salary) min,avg(salary) avg from t_employees group by job_id having count(job_id)>10;
+----------+---------------+-------+------+-----------+
| job_id   | count(job_id) | max   | min  | avg       |
+----------+---------------+-------+------+-----------+
| SA_REP   |            30 | 11500 | 6100 | 8350.0000 |
| SH_CLERK |            20 |  4200 | 2500 | 3215.0000 |
| ST_CLERK |            20 |  3600 | 2100 | 2785.0000 |
+----------+---------------+-------+------+-----------+
3 rows in set (0.00 sec)

having是分组以后的条件判断

7.6 Limit

用来控制显示的记录个数，默认是全部都显示。主要用在分页上

limit 起始位置, 个数

limt 0, 5;#从第0个位置开始，显示5条件记录

mysql> select *from t_employees order by salary asc limit 0,1;
# select *from t_employees limit 0, 10  第一页
# select * from t_employees limit 10, 10  第二页
# select * from t_employees limit (currentPage-1)*pageSize, pageSize  第78页

select * from t_employees limit 770, 10

总结：

select 字段 from 表名 where 条件查询 group by 字段 having 分组条件 order by 排序字段 limit 起始位置，个数

7.7 查询总结

7.7.1 SQL语句编写顺序

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列（asc|desc）LIMIT 起始行，总条数

7.7.2 SQL语句执行顺序

1.FROM ：指定数据来源表

2.WHERE : 对查询数据做第一次过滤

3.GROUP BY ： 分组

4.HAVING : 对分组后的数据第二次过滤

5.SELECT : 查询各字段的值

6.ORDER BY : 排序

7.LIMIT : 限定查询结果

---

八、表约束

对表中的数据进行限制，保证数据具有正确性、有效性和完整性。

8.1 约束类型

约束名	关键词
主键	primary key
唯一	unique
非空	not null
外键	foreign key
检查约束	check约束：mysql不支持

8.2 主键约束

用来唯一标识表中的每一条记录 创建主键：

• primary key

• 主键的特点：1、唯一 2、非空 not null

• 自动排序

1、创建表的时候直接指定主键约束

create table 表( ​

字段名1 类型1 primary key，#主键约束 ​

字段2 类型2

)

2、通过修改的方式给表添加主键约束

alter table 表名 add primary key (字段名)；

mysql> alter table student_info add primary key(Id);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student_info;
+------+-------+------------+--------+-----------+
| Id   | name  | birthday   | gender | avg_score |
+------+-------+------------+--------+-----------+
| 1001 | tom   | 1998-03-04 | 女      |      89.9 |
| 1002 | bush  | 1921-12-02 | 男       |      90.0 |
| 1003 | jack  | 1996-03-04 | 女      |      99.0 |
| 1004 | jerry | 1997-12-03 | 男       |      58.0 |
| 1005 | trump | 1945-03-06 | 男       |      10.0 |
+------+-------+------------+--------+-----------+
5 rows in set (0.00 sec)

mysql> insert into student_info values(1002, 'bush', '1921-12-2', '男', 90);
ERROR 1062 (23000): Duplicate entry '1002' for key 'PRIMARY'
mysql> insert into student_info values(null, 'bush', '1921-12-2', '男', 90);
ERROR 1048 (23000): Column 'Id' cannot be null

总结： 一般每张表都需要设置一个主键字段，而且该主键字段不作为业务字段。大部分我们都是设置一个id字段，把这个id字段作为主键。

删除主键： alter table 表名 drop primary key (字段名)；

自增主键 这种主键的特点是数据是自动增长，在添加数据时可以不用指定

auto_increment 表示自动增长（字段类型必须是整数类型）

#默认auto_increment从1开始自增
mysql> create table  bb(
    -> id int primary key auto_increment,
    -> name varchar(200)
    -> );
Query OK, 0 rows affected (0.03 sec)

#null:表示自增生成
mysql> insert into bb values(null, 'aaa');
Query OK, 1 row affected (0.01 sec)

mysql> select * from bb;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

#表示只给name字段提供值，id是自增
mysql> insert into bb(name) values('bbbb');

#默认auto_increment从1000开始自增
mysql> create table  bb(
    -> id int primary key auto_increment,
    -> name varchar(200)
    -> )auto_increment=1000;
    
alter table 表名 auto_increment=1000;

---

MySQL链表操作

九、数据库表的约束

9.1 唯一约束

保证该列的数据不会重复

语法格式： 字段名 字段类型 uniqu

create table 表名(
	id int primary key,
    name varchar(20) unique
)

mysql> alter table student_info modify name varchar(200) unique;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
#设置唯一约束时会生成一个唯一索引，而该索引长度不能太长

mysql> alter table student_info modify name varchar(20) unique;
ERROR 1062 (23000): Duplicate entry 'tom' for key 'name'

#能否重复插入null值？
mysql> insert into student_info values(1007, null, '1996-10-3', '男', 78);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student_info values(1008, null, '1999-10-3', '男', 78);
Query OK, 1 row affected (0.00 sec)

9.2 非空约束

就是该列不能为Null

字段名 字段类型 not null

create table 表名(
	id int primary key,
    name varchar(20) unique,
    birthday date not null
)

#通过修改表结构给该字段添加非空约束
mysql> alter table student_info modify birthday date not null;
Query OK, 8 rows affected (0.04 sec)
Records: 8  Duplicates: 0  Warnings: 0


mysql> insert into student_info values(1009, null, null, '男', 78);
ERROR 1048 (23000): Column 'birthday' cannot be null

非空+唯一：可以适用于表中的多个字段，但是主键在一张表中只能适用一次

9.3 默认值

如果添加数据时没有提供具体数则有数据库默认提供

字段名 字段类型 default 默认值

create table users
(
	id int primary key auto_increment,
    name varchar(20) unique not null,
    birthday date not null,
    address varchar(200) default '地址不详'
)

9.4 外键约束

create table emp(
	id int primary key auto_increment,
    name varchar(30),
    age int,
    dep_name varchar(30),
    dep_location varchar(20)
);

insert into emp values(null, '曹操', 20, '研发部', '上海');
insert into emp values(null, '曹仁', 21, '研发部', '上海');
insert into emp values(null, '曹真', 19, '市场', '北京');
insert into emp values(null, '曹爽', 19, '市场', '北京');

上述数据表的问题：

• 数据冗余：存在大量重复的数据

• 后期修改数据会存在问题

解决方案：拆表

拆成部门和员工表

#员工表
create table emp(
	eid int primary key auto_increment,
    emp_name varchar(30),
    emp_age int,
    dep_id int #存在该员工的部门编号
);

#部门表
create table dep(
	did int primary key auto_increment,
    dep_name varchar(30),
    dep_location varchar(30)
);

insert into dep values(null, '研发部', '上海');
insert into dep values(null, '市场', '北京');
insert into dep values(null, '财务', '成都');
insert into dep values(null, '人事', '郑州');

insert into emp values(null, '曹操', 20, 1);
insert into emp values(null, '曹仁', 21, 1);
insert into emp values(null, '曹真', 19, 2);
insert into emp values(null, '曹爽', 19, 2);


#这数据就是有问题的数据了，因为9这个编号没有对应的部门信息
insert into emp values(null, '曹德', 16,9);

可以通过外键约束进行数据约束

• 主表：用来约束别人的数据

• 从表：被约束的表

• constraint 约束名称 foreign key (字段) references 主键表(字段)

create table emp(
	eid int primary key auto_increment,
    emp_name varchar(30),
    emp_age int,
    dep_id int, #存在该员工的部门编号
    constraint dep_id_fk foreign key (dep_id) references dep(did)
);

mysql> insert into emp values(null, '朱顺永', 16,9);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`did`))

外键级联问题：对主表的数据进行修改需要考虑从表的感受

mysql> delete from dep where did=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`did`))

级联操作：在修改或删除主表的主键时，同时修改或删除从表的外键值

语法	说明
on update cascade	级联更新，只能在创建表时候创建级联更新。在更新主表中的主键，从表的外键列也会自动同步更新
on delete cascade	级联删除

create table emp(
	eid int primary key auto_increment,
    emp_name varchar(30),
    emp_age int,
    dep_id int, #存在该员工的部门编号
    constraint dep_id_fk foreign key (dep_id) references dep(did) on update cascade on delete cascade
);

mysql> select * from emp;
+-----+----------+---------+--------+
| eid | emp_name | emp_age | dep_id |
+-----+----------+---------+--------+
|   1 | 张三         |      20 |      1 |
|   2 | 刘会操         |      21 |      1 |
|   3 | 缪家成       |      19 |      2 |
|   4 | 汪玮         |      19 |      2 |
+-----+----------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from dep;
+-----+----------+--------------+
| did | dep_name | dep_location |
+-----+----------+--------------+
|   1 | 研发部        | 上海            |
|   2 | 市场        | 北京             |
+-----+----------+--------------+
2 rows in set (0.00 sec)

#修改主表的数据，因为我们设置了级联操作，则从表的外键数据会自动更新
mysql> update dep set did=5 where did=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+-----+----------+---------+--------+
| eid | emp_name | emp_age | dep_id |
+-----+----------+---------+--------+
|   1 | 曹操         |      20 |      1 |
|   2 | 曹仁         |      21 |      1 |
|   3 | 曹操       |      19 |      5 |
|   4 | 曹爽         |      19 |      5 |
+-----+----------+---------+--------+
4 rows in set (0.00 sec)

#删除主表时，会自动级联删除从表的数据
mysql> delete from dep where did=5;
Query OK, 1 row affected (0.01 sec)

mysql> select *from dep;
+-----+----------+--------------+
| did | dep_name | dep_location |
+-----+----------+--------------+
|   1 | 研发部        | 上海            |
+-----+----------+--------------+
1 row in set (0.00 sec)

mysql> select * from emp;
+-----+----------+---------+--------+
| eid | emp_name | emp_age | dep_id |
+-----+----------+---------+--------+
|   1 | 曹操        |      20 |      1 |
|   2 | 曹仁         |      21 |      1 |
+-----+----------+---------+--------+
2 rows in set (0.00 sec)

9.5 数据约束总结

约束名	关键词	特点
主键	primary key	非空、唯一、自动排序
唯一	unique	不重复
非空	not null	值不能为空
默认	default	如果没有值，则使用默认值
外键	foreign key	从表的数据参照主表对应的数据

---

十、表与表之间的关系

10.1 表之间的关系

在现实中存在对象之间的关系，比如：老师和学生，领导和员工。那么我们在设置这些表的时候也需要考虑关系问题，表关系

表与表之间的关系	说明
一对多	老师和学生
多对多	课程和学生，一门课程可以被多个学生选，一个学生也可以选择多门课程
一对一	员工和简历，老婆和老公

10.2 一对多

一对多：(1:N)，班级和学员

一般创建一对多关系的原则是：在多的一方定义一个外键字

create table 班级表(
	id int primary key,
    name varchar(200)
);

create table 学员表(
	id int primary key,
    name varchar(200),
    age int,
    classId int ,
    constraint classId_fk foreign key(classId) references 班级表(id)
)

10.3 多对多

多对多（N:M）:学生和课程，用户和角色

多对多的关系表建立原则：需要创建第三方表，这个第三方表至少需要两个字段，这两个字段是外键字段分别指向每一方的主键

create table 学生表(
	id int primary key,
    name varchar(200)
)

create table 课程表(
	id int primary key,
    name varchar(200)
)

#创建关系表
create table 学生课程关系表(
	学生编号 int ,
    课程编号 int,
    constraint 学生编号_fk foreign key(学生编号) references 学生表(id),
    constraint 课程编号_fk foreign key(课程编号) references 课程表(id),
)

10.4 一对一

一对一（1：1）：在实际中很好用到，因为可以设计成一张表

创建一对一关系的原则：

• 外键唯一：主表的主键和从表外键，形成主外键关系，外键唯一unique

• 外键为主键

create table 学生表(
	id int primary key,
    name varchar(200)
)

create table 身份证(
	id int unique,
    cardId varchar(18) not null,
    constraint id_fk foreign key(id) references 学生表(id)
)

create table 学生表(
	id int primary key,
    name varchar(200)
)

create table 身份证(
	id int primary key,
    cardId varchar(18) not null
)

insert into 学生表 values(1, '张三');
insert into 身份证 values(1, '111111111111');

总结：

表关系	关系的维护
1：M	在多的一方通过外键维护
N：M	通过第三方表中的外键字段维护
1：1	外键唯一，主键相同

---

十一、数据库设计规范

范式：在设计数据库是需要遵守的一些规则

第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就行了。所以这里就只记录三范式相关的知识。

11.1 1NF

表中每一个列都是不可分割的，具有原子性

学号	姓名	年龄
1001	张三	20
1002	李四	19

11.2 2NF

第二范式：在满足第一范式的基础上，每个字段都完全依赖于主键

特点：

1.一张表只描述一件事

2.当前表中的所有字段都和主键直接依赖

不满足第二范式的学员信息表

学号	姓名	学院编号	学院名称
1001	张三	A0002	计院
1002	李四	B004	法院

分成多张表

学号	姓名
1001	张三

学员编号	学员名称
A0002	计院

11.3 3NF

第三范式：满足第二范式的基础上，当前表中每个列直接依赖于主键，而不是通过其他字段间接依赖于主键

不符合第三范式要求的数据

学号	姓名	所在学院	学院地址
1001	张三	计院	浦东新区

拆表

学号	姓名	学院编号（外键）
1001	张三	1

11.4 总结

范式	特点
1NF	原子性：每个列都不可拆分
2NF	不能存在局部依赖，每张表值描述一件事情
3NF	不能有传递依赖，每个列都必须直接依赖于主键

---

十二、连表查询

今后的数据查询可能需要来源于多张表的综合数据

12.1 笛卡尔积现象

什么是笛卡尔积？

mysql> select * from t_countries, t_locations;

mysql> select *from t_countries,t_locations where t_countries.country_id=t_locations.country_id;

mysql> select *from t_countries,t_locations where t_countries.country_id=t_locations.country_id and t_countries.country_id='CN';

select 字段 from 表1,表2 where 表1.字段 = 表2.字段

select 字段 from 表1 [inner / left/ right] join 表2 on 表1.字段=表2.字段 where 条件表达式

12.2 内连接

前后两张表同时存在记录则显示

select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件表达式

mysql> select * from b;
+----+-------+------+
| id | title | pid  |
+----+-------+------+
|  1 | james |    1 |
|  2 | clerk |    2 |
|  3 | cat   |    3 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> select * from a;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)

mysql> select *from a inner join b on a.id=b.pid;
+----+------+----+-------+------+
| id | name | id | title | pid  |
+----+------+----+-------+------+
|  1 | aa   |  1 | james |    1 |
|  2 | bb   |  2 | clerk |    2 |
+----+------+----+-------+------+
2 rows in set (0.00 sec)

12.3 左外连接

select 字段 from 表1 left join 表2 on 表1.字段=表2.字段 where 条件表达式

表1的数据全部显示，而表2的数据是满足条件的才会显示

mysql> select *from a left join b on a.id=b.pid;
+----+------+------+-------+------+
| id | name | id   | title | pid  |
+----+------+------+-------+------+
|  1 | aa   |    1 | james |    1 |
|  2 | bb   |    2 | clerk |    2 |
|  4 | cc   | NULL | NULL  | NULL |
+----+------+------+-------+------+
3 rows in set (0.01 sec)

12.4 右外连接

select 字段 from 表1 right join 表2 on 表1.字段=表2.字段 where 条件表达式

表2的数据全部显示，而表1的数据是满足条件的才会显

mysql> select *from a right join b on a.id=b.pid;
+------+------+----+-------+------+
| id   | name | id | title | pid  |
+------+------+----+-------+------+
|    1 | aa   |  1 | james |    1 |
|    2 | bb   |  2 | clerk |    2 |
| NULL | NULL |  3 | cat   |    3 |
+------+------+----+-------+------+
3 rows in set (0.00 sec)

---

十三、子查询

一个查询的结果是另外一个查询的条件，这也叫嵌套查询，内部的查询我们称为子查询，注意子查询需要用括号

13.1 子查询作为条件

SELECT 列名 FROM 表名 Where 条件 (子查询结果)

#查询工资大于Bruce 的员工信息

#1.先查询到 Bruce 的工资（一行一列）
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';#工资是 6000

#2.查询工资大于 Bruce 的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;

#3.将 1、2 两条语句整合
SELECT * FROM t_employees WHERE SALARY > （SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce' ）;

• 注意：将子查询 ”一行一列“的结果作为外部查询的条件，做第二次查询

• 子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断

13.2 子查询作为枚举条件查询

SELECT 列名 FROM 表名 Where 列名 in(子查询结果);

13.2.1 查询与名为'King'同一部门的员工信息

#思路：
#1.	先查询 'King' 所在的部门编号(多行单列)
SELECT department_id
FROM t_employees
WHERE last_name = 'King'; //部门编号：80、90

#2.	再查询80、90号部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (80,90); 

#3.SQL：合并
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name = 'King'); #N行一列

将子查询 ”多行一列“的结果作为外部查询的枚举查询条件，做第二次查询

13.2.2 工资高于60部门所有人的信息

#1.查询 60 部门所有人的工资（多行多列）
SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;

#2.查询高于 60 部门所有人的工资的员工信息（高于所有）
select * from t_employees where SALARY > ALL(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);

#。查询高于 60 部门的工资的员工信息（高于部分）
select * from t_employees where SALARY > ANY(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);

#1.查询 60 部门所有人的工资（多行多列）
SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;

#2.查询高于 60 部门所有人的工资的员工信息（高于所有）
select * from t_employees where SALARY > ALL(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);

#。查询高于 60 部门的工资的员工信息（高于部分）
select * from t_employees where SALARY > ANY(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);

13.3 子查询（作为一张表）

SELECT 列名 FROM（子查询的结果集）WHERE 条件;

13.3.1 查询员工表中工资排名前 5 名的员工信息

#思路：
#1.	先对所有员工的薪资进行排序（排序后的临时表）
select employee_id , first_name , salary
from t_employees
order by salary desc

#2.	再查询临时表中前5行员工信息
select employee_id , first_name , salary
from (临时表) 
limit 0,5;

#SQL：合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from t_employees order by salary desc) as temp
limit 0,5;

• 将子查询 ”多行多列“的结果作为外部查询的一张表，做第二次查询。

• 注意：子查询作为临时表，为其赋予一个临时表名

---

MySQL事务

十四、事务

transaction事务

在实际的业务中，完成某个功能可能需要多条SQL语句共同参入。如果其中某条出现了问题，怎么办？

张三-->500-->李四

update account set money=money-500 where name='张三';

update account set money=money+500 where name='李四';

事务：将业务当做一个执行的整体，所有的SQL语句都必须成功执行，如果其中某条SQL出现问题，所有的SQL语句都可以回滚，整个业务就算失败，如果都没有问题，我们可以提交事务，则业务算执行成功！

通过转账案例：

create table account(
	id int primary key auto_increment,
    name varchar(200),
    balance double
);

insert into account values(null, '张三', 1000);
insert into account values(null, '李四', 1000);

update account set balance=balance-500 where name='张三';
update account set balance=balance+500 where name='李四';

14.1 事务的基本使用

MySQL中可以有两个事务的使用方式：

1、手动提交事务

2、自动提交事务

• 手动提交事务

  • 开启事务：start transaction;

  • 提交事务：commit;

  • 回滚事务：rollback

• 手动提交事务的过程：

1） 执行成功 : 开启事务--》执行N条件SQL--》提交事务

2） 执行失败 : 开启事务--》执行N条件SQL--》回滚事务

#在事务中实现转账过程
# 1.开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=balance-500 where name='张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set balance=balance+500 where name='李四';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | 张三     |     500 |
|  2 | 李四     |    1500 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

自动事务提交：MYSQL中默认每条(DML语句)都是一个单独事务，每条SQL语句都会自动开启一个事务，语句执行完毕自动提交事务，MYSQL默认开启自动提交事务！

关闭自动提交

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
##@@autocommit是mysql中记录自动提交的状态，1表示开启了自动提交，0,表示关闭了自动提交事务

mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

注意：一旦关闭了自动提交，则今后每条DML语句执行结束后都需要手动提交事务

14.2 事务的原理

1、客户端连接数据库服务器之后，服务器会自动创建一个日志临时文件

2、开始事务后，所有的DML操作都是写入到临时日志文件了

3、所有的查询操作，也会将结果写到日志文件，然后再返回给客户端

4、如果提交事务则将日志数据同步到数据库，否则清空日志文件

---

十五、事务的隔离级别

15.1 事务的四大特征ACID

事务特性	说明
原子性（Atomicity）	每个事务都是一个整理，不在拆分
一致性(Consistency)	事务在执行前和执行的数据状态保持一致
隔离性(Isolation)	事务之间不应该有任何相互影响
持久性(Durability)	事务执行结束后，对数据库的修改就是持久的

15.2 事物的隔离级别

因为事务具有隔离性，就是多个事务之间是互不影响。因为并发操作，多个用户存在访问同一数据的出现并发问题

并发问题	说明
脏读	在一个事务中读取了另外一个事务还没结束的数据
不可重复读	在一个事务中两次读取的数据内容不一样，主要是因为update操作引起的。
幻读	在一个事务中两次读取数据的数量不一致。主要是因为insert和delete操作引起的。

在MySQL中提供了四种隔离级别

从低到高的隔离级别分别

级别	名称		脏读	不可重复读	幻读	数据库默认
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle默认的隔离级别
3	可重复读	repeatable read	否	否	是	MySQL默认隔离级别
4	串行化	serializable	否	否	否

隔离级别越高，性能就越差，安全性就越高

#查看隔离级别：@@tx_isolation
select @@tx_isolation;

#修改隔离级别
set global transaction isolation level 级别字符串;

• 脏读问题

打开两个窗口（A,B）,来模拟两个事务的操作情

#打开第一个窗口，并修改隔离级别
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

在当前窗口中修改好隔离级别之后需要重新打开窗口才能看到！

 

解决脏读的办法：提高隔离级别

set global transaction isolation level read committed;

需要退出重新登录才能看到最新的隔离级别

• 不可重复读

将数据恢复现场之后，开启A窗口，设置数据库的隔离级别为read committed

set global transaction isolation level read committed
#退出mysql，并重新进入，隔离级别才会生效

 在B窗口中，同一事务内两次读取数据不一致，这个主要是因为read committed不能解决不可重复读问题！

解决不可重复读问题：提高隔离级别，提高为repeatable read

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

• 幻读问题

mysql中无法直接看出幻读现象，但是我们可以通过提高隔离级别，解决幻读问题

总结：使用Serializable隔离级别，一个事务没有结束，其他事务的SQL执行不了，可以解决幻读问题！

 DDL、DML、DQL、DCL

---

十六、视图

16.1 概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

16.2 视图的特点

• 优点

  • 简单化，数据所见即所得。

  • 安全性，用户只能查询或修改他们所能见到得到的数据。

  • 逻辑独立性，可以屏蔽真实表结构变化带来的影响。

• 缺点

  • 性能相对较差，简单的查询也会变得稍显复杂。

  • 修改不方便，特变是复杂的聚合视图基本无法修改。

16.3 视图的创建

语法：CREATE VIEW 视图名 AS 查询数据源表语句;

16.3.1创建视图

#创建 t_empInfo 的视图，其视图从 t_employees 表中查询到员工编号、员工姓名、员工邮箱、工资
CREATE VIEW t_empInfo 
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

16.3.2使用视图

#查询 t_empInfo 视图中编号为 101 的员工信息
SELECT * FROM t_empInfo where employee_id = '101';

16.4 视图的修改

• 方式一：CREATE OR REPLACE VIEW 视图名 AS 查询语句

• 方式二：ALTER VIEW 视图名 AS 查询语句

16.4.1修改视图

#方式 1：如果视图存在则进行修改，反之，进行创建
CREATE  OR  REPLACE VIEW t_empInfo 
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY,DEPARTMENT_ID from t_employees;

#方式 2：直接对已存在的视图进行修改
ALTER VIEW t_empInfo
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

16.5 视图的删除

DROP VIEW 视图名

16.5.1删除视图

#删除t_empInfo视图
DROP VIEW t_empInfo;

• 注意：删除视图不会影响原表

16.6 视图的注意事项

• 注意：

  • 视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。

  • 如果视图包含以下结构中的一种，则视图不可更新

    • 聚合函数的结果

    • DISTINCT 去重后的结果

    • GROUP BY 分组后的结果

    • HAVING 筛选过滤后的结果

    • UNION、UNION ALL 联合后的结果

---

十七、DCL （Date  Controller  Language）

• DDL:create、drop、alter

• DML:insert、update、delete

• DQL：select、show

• DCL:grant、revoke

DCL:主要用来进行权限的分配，授予某个用户拥有某些权限，我们现在都是root用户身份，root拥有最高权限。

17.1 创建新用户

create user '用户名'@'主机名' identified by '密码';

• 用户名：创建的用户名

• 主机名：指定用户将来可以通过哪台主机访问该服务器，如果是本机可以使用localhost,如果想让用户通过任意一台服务器进行访问可以使用'%'

• 密码：用户的登录密码，可以为空，如果为空则表示不需要密码就可以直接登录

#创建一个user1用户，密码为123,并且只能通过本机登录
create user 'user1'@'localhost' identified by '123';

A:192.168.1.222；
B:192.168.1.120;
create user 'user2'@'192.168.1.222' identified by '123';

17.2 给用户授权

grant 权限1，权限2，权限3... on 数据库.表名 to '用户名'@'主机名'

• grant .... on .... to ... :授权的关键词

• 权限：授予用户的具体权限，create、alter、select、update等等。也可以使用all

• 数据库.表名:该用户可以操作哪个库的哪张表。如果想给库中的所有表设置对应权限可以使用：数据库.*【*.*:所有库的所有表】

• '用户名'@'主机名'：给哪个用户了

#给user1设置权限：创建表、查询、修改、删除、更新权限
grant create,select,update,delete,alter on db1.* to 'user1'@'localhost';

#因为user用户没有insert权限
mysql> insert into account values(null, 'aaa', 20);
ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'account'

#将user1用户设置所有权限
grant all on *.* to ‘user1’@'localhost'

17.3 撤销权限

evoke 权限1,权限2,权限3.... on 数据库.表名 from '用户名'@'主机名'

• revoke:撤销对应权限命令

mysql> revoke delete on db1.* from 'user1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> use db1;
Database changed
mysql> delete from account;
ERROR 1142 (42000): DELETE command denied to user 'user1'@'localhost' for table 'account'

查看用户权限:show grants for 'user1'@'localhost';

mysql> show grants for 'user1'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, UPDATE, CREATE, ALTER ON `db1`.* TO 'user1'@'localhost'                                        |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

usage:表示登录权限