mysql入门总结

1.数据库基本常识

1.什么是数据库

存储数据的仓库，本质就是一个文件

2.数据的存储方式

1.数据保存在内存中

int[] arr = new int[]{1, 2, 3, 4};
ArrayList<Integer>list = new ArrayList<Integer>();
list.add(1);
list.add(2);

2.数据保存到普通文件中

优点：永久保存

缺点：查找，增加，修改，删除数据比较麻烦，效力低‘

3.数据保存到数据库中

优点：永久保存，通过SQL语句比较方便的操作数据库，数据库是对大量的信息进行管理的高效的解决方案

3.常见的数据库

4.常用的关系型数据库

MYSQL，Oracle

在web应用中，使用的最多的就是MySQL数据库，原因如下：

1. 开源、免费
2. 功能足够强大，足以应付web应用开发（最高支持千万级别的并发访问）

小结：

1. 数据库的概念： 存储数据的仓库
2. 常用的关系型数据库 MySQL Oracle

5.MySQL的目录结构

了解Mysql的目录结构

│-- bin：mysql相关的可执行文件*.exe
│-- MySQLInstanceConfig.exe mysql的配置程序
│-- data: mysql自带的数据库文件(不用关注)
│-- include: c语言的头文件(不用关注)
│-- lib: 存放mysql使用到的dll动态库(相当于jar包，不用关注)
│-- my.ini mysql的配置文件,配置了mysql的相关信息

6.命令行连接数据库

登录格式1

mysql -uroot -proot

登录格式2

mysql -uroot -p回车
下一行输入密码

或者mysql -hip地址 -u用户名 -p密码

mysql -h127.0.0.1 -uroot -proot

退出MySQL

exit

2.SQL语句的分类和语法

1.什么是SQL?

结构化查询语言(Structured Query Language)简称SQL。
SQL语句就是对数据库进行操作的一种语言。

2.SQL作用

通过SQL语句我们可以方便的操作数据库、表、数据。
SQL是数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

3.SQL语句的分类

1. DDL(Data Definition Language) 数据定义语言
   用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter等

2. DML(Data Manipulation Language) 数据操作语言
   用来对数据库中表的数据进行增删改。关键字：insert, delete, update等

3. DQL(Data Query Language) 数据查询语言
   对数据库进行数据查询，关键字select

4. DCL(Data Control Language)数据控制语言(了解)

   是用来设置或更改数据库用户或角色权限的语句，这个比较少用到

4.SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 3种注释
   单行注释: – 注释
   多行注释: /*注释*/
   MYSQL特有的单行注释：# 注释

小结：

1.SQL的作用？

操作数据库

2.SQL的分类

DDL: 数据定义语言,操作数据库和表示
DML: 数据操作语言,对数据进行增删改
DQL: 数据查询语言,对数据进行查询
DCL: 数据控制语言(了解),创建和删除数据库用户

3.SQL的语法特点？

1. SQL语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

3.DDL

1.创建数据库

1.直接创建数据库

CREATE DATABASE 数据库名;
创建      数据库   名字

2.判断是否存在并创建数据库

CREATE DATABASE IF NOT EXISTS 数据库;

3.创建数据库并指定字符集(编码表)

CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 编码;

具体操作

-- 直接创建数据库
CREATE DATABASE db1;


-- 判断是否存在并创建数据库db2
CREATE DATABASE IF NOT EXISTS db2;

--创建数据库db3并指定字符集为gbk
CREATE DATABASE db2 CHARACTER SET gbk;

2.查询数据库

1.查询所有数据库

SHOW DATABASES;

2.查看某个数据库的定义信息

SHOW CREATE DATABASE 数据名;

小结：

1.创建数据库语法

CREATE DATABASE 数据名;

2.查看数据库

SHOW DATABASES;

3.修改数据库

1.修改数据库字符集

ALTER DATABASE 数据名 DEFAULT CHARACTER SET 新编码;
修改   数据库             默认  字符编码

操作

ALTER DATABASE db3 DEFAULT CHARACTER SET utf8;

4.删除数据库

DROP DATABASE 数据库名;
删除  数据库

操作 删除db2数据库

DROP DATABASE db2;

小结：

修改数据库的字符集格式？

ALTER DATABASE 数据名 DEFAULT CHARACTER SET 字符集;

删除数据库格式？

DROP DATABASE 数据库名;

5.使用数据库

1.查看正在使用的数据库

SELECT DATABASE();

2.使用和切换数据库

use  数据库名

6.创建表

1.创建表

CREATE TABLE 表名(
    字段名1 字段类型1, 
    字段名2 字段类型2
);

操作如下

创建student表包含id,name,birthday字段

CREATE TABLE student (
      id INT,
      name VARCHAR(20),
      birthday DATE
);

7.查看表

1.查看某个数据库中的所有的表

SHOW TABLES;

2.查看表结构

DESC 表名;

3.查看创建表的SQL语句

SHOW CREATE TABLE 表名;

8.删除表

快速创建一个表结构

CREATE TABLE 表名 LIKE 其他表;

操作

创建s1表，s1表结构和student表结构相同

CREATE TABLE s1 LIKE student;

删除表

DROP TABLE s1;

判断表是否存在并删除s1表

DROP TABLE IF EXISTS s1;

9.修改表结构

1.添加一列

ALTER TABLE 表名 ADD 字段名 字段类型;

DCL

操作

为学生表添加一个新的字段remark类型为varchar(20)

ALTER TABLE student ADD remark VARCHAR(20);

2.修改列类型’

ALTER TABLE 表名 MODIFY 字段名 新类型;

具体操作：

• 将student表中的remark字段的改成varchar(100)

• ALTER TABLE student MODIFY remark VARCHAR(100);
  
  

3.修改列名

ALTER TABLE 表名 CHANGE 老字段名 新字段名 类型;

具体操作：

• 将student表中的remark字段名改成intro，类型varchar(30)

ALTER TABLE student CHANGE remark intro varchar(30);

4.删除列

ALTER TABLE 表名 DROP 字段名;

具体操作：

• 删除student表中的字段intro

ALTER TABLE student DROP intro;

5.修改表名

重名名  表
RENAME TABLE 表名 TO 新表名;

具体操作：

• 将学生表student改名成student2，再删除student2表

 RENAME TABLE student TO student2;
 DROP TABLE student2;

6.修改表的字符集

ALTER TABLE 表名 DEFAULT CHARACTER SET 新字符集;

小结

1. 所有修改表前面的语法都是相同的？

   ALTER TABLE 表名 xxx
   
   

2. 添加一列：

   ALTER TABLE 表名 ADD 字段名 字段类型;
   
   

3. 修改类型：

   ALTER TABLE 表名 MODIFY 字段名 新类型;
   
   

4. 修改字段名和类型：

   ALTER TABLE 表名 CHANGE 老字段名 新字段名 类型;
   
   

5. 删除一列：

   ALTER TABLE 表名 DROP 字段名;
   
   

4.DML

DML是对表中的数据进行增删改

1.插入记录

创建student表包含id,name,birthday,sex,address字段。

CREATE TABLE student (
      id INT,
      name VARCHAR(20),
      birthday DATE,
      sex char(2),
      address varchar(50)
);

插入全部数据

1.所有的字段名都写出来

INSERT INTO 表名 (字段名1, 字段名2, 字段名3, ...) VALUES (值1, 值2, 值3, ...);

2.不写字段名

INSERT INTO 表名 VALUES (值1, 值2, 值3, ...);

3.插入部分数据

只需要指定要插入数据的字段
INSERT INTO 表名 (字段名1, 字段名2...) VALUES (字段值1, 字段值2);

操作

-- 插入全部字段

INSERT INTO student(id,NAME,birthday,sex,address) VALUES (1,'张三','1998-01-04','男','北京')


-- 插入部分数据
INSERT INTO student(id,NAME,sex,address) VALUES(2,'李四','女','广州')


-- 不写字段名
INSERT INTO student VALUES(3,'王五','2021-2-2','女','虎门')

注意

• 值与字段必须对应，个数相同，类型相同
• 值的数据大小必须在字段的长度范围内
• 除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
• 如果要插入空值，可以不写字段，或者插入null

小结：

向表中添加一条完整的记录

所有的字段都写出来
INSERT INTO 表名 (字段名1, 字段名2...) VALUES (字段值1, 字段值2);

不写字段名
INSERT INTO 表名 VALUES (字段值1, 字段值2);

2.更新记录

1.不带条件的更新

UPDATE 表名 SET 字段名=新值;

2.带条件的更新

UPDATE 表名 SET 字段名=新值 WHERE 条件;

3.关键字说明

UPDATE 表示修改数据

4.操作

-- 不带条件的更新数据

UPDATE student SET sex='女';

-- 不带条件修改数据，将所有的性别修改成了女


-- 带条件修改数据
UPDATE student SET sex='男' WHERE id= 2;

小结：

1.不带条件的更新数据

UPDATE 表名 SET 字段名=新值;

2.带条件的更新数据

UPDATE 表名 SET 字段名=新值 WHERE 条件;

3.删除数据

1.带条件删除数据

DELETE FROM 表名 WHERE 条件;

2.不带条件删除数据

DELETE FROM 表名;

4.操作

-- 带条件删除数据
DELETE FROM student WHERE id=1;

-- 不带条件的删除   不带条件就会删除表中所有的数据
DELETE FROM student;

小结：

1.指定删除条件

DELETE FROM 表名 WHERE 条件;

2.没有指定删除条件 就会删除所有的记录

DELETE FROM 表名;

5.约束

1.数据库约束的概述

目前根据字段的类型可以对数据进行限制，但是这个限制不够全面

数据库约束的作用

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

约束种类

• PRIMARY KEY: 主键约束
• UNIQUE: 唯一约束
• NOT NULL: 不为空
• DEFAULT: 默认约束
• FOREIGN KEY: 外键约束

2.主键约束

1.为什么需要主键的约束

有些记录的 name,age,score 字段的值都一样时，那么就没法区分这些数据,造成数据库的记录不唯一，这样就不方便管理数据。

每张表都应该有一个主键 ，并且每张表都只能有一个主键

2.主键的作用

唯一的区分一条记录

那个字段可以作为主键

通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

3.创建主键方式

1.在创建表的时候给字段添加主键

CREATE TABLE 表名 (
	字段名 字段类型 PRIMARY KEY,
	字段名 字段类型
);

2.在已有的表添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

操作：

创建表学生表st5, 包含字段(id, name, age)将id做为主键

CREATE TABLE st5 (
	id INT PRIMARY KEY, -- id是主键
	NAME VARCHAR(20),
	age INT
);

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

删除st5表的主键

ALTER TABLE st5 DROP PRIMARY KEY;

小结：

1.主键的约束作用？唯一区分表中的一条记录

2.主键的特点？ 唯一和非空

3.添加和删除主键？

创建表时添加主键
CREATE TABLE 表名 (
	字段名 字段类型 PRIMARY KEY,
	字段名 字段类型
);

在已有表上添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名)

删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

3.主键自增

主键让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值

字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

AUTO_INCREMENT 表示自动增长(字段类型必须是数值类型)

操作

创建学生表st6, 包含字段(id, name, age)将id做为主键并自动增长

CREATE TABLE st6 (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT
);

查询数据

-- 主键默认从1开始自动增长
INSERT INTO st6 (NAME, age) VALUES ('唐僧', 22);
INSERT INTO st6 (NAME, age) VALUES ('孙悟空', 26);
INSERT INTO st6 (NAME, age) VALUES ('猪八戒', 25);
INSERT INTO st6 (NAME, age) VALUES ('沙僧', 20);

4.唯一约束

唯一约束的作用

让字段的值唯一，不能重复

唯一约束的格式

CREATE TABLE 表名 (
	字段名 字段类型 UNIQUE,
	字段名 字段类型
);

操作

创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生

CREATE TABLE st7 (
	id INT,
	NAME VARCHAR(20) UNIQUE
);

添加一些学生

INSERT INTO st7 VALUES (1, '貂蝉');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '王昭君');
INSERT INTO st7 VALUES (4, '杨玉环');

-- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name'
INSERT INTO st7 VALUES (5, '貂蝉');

-- 出现多个null的时候会怎样？因为null是没有值，所以不存在重复的问题
INSERT INTO st3 VALUES (5, NULL);
INSERT INTO st3 VALUES (6, NULL);

小结：

1.唯一约束的作用 ： 让某个字段的值唯一，不能重复

添加唯一约束的格式

字段名 字段类型 UNIQUE

5.非空约束

非空约束的作用

让字段不能为空

语法格式

CREATE TABLE 表名 (
	字段名 字段类型 NOT NULL,
	字段名 字段类型
);

操作

• 创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL

CREATE TABLE st8 (
	id INT,
	NAME VARCHAR(20) NOT NULL,
	gender CHAR(2)
);

• 添加一些完整的记录

INSERT INTO st8 VALUES (1, '郭富城', '男');
INSERT INTO st8 VALUES (2, '黎明', '男');
INSERT INTO st8 VALUES (3, '张学友', '男');
INSERT INTO st8 VALUES (4, '刘德华', '男');

-- 姓名不赋值出现姓名不能为null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, '男');

小结：

主键唯一,非空,普通的字段我们也可以添加唯一和非空,有区别吗?

主键: 一张表只能有一个,主键可以自动增长

普通字段: 可以可以字段非空唯一,不能自动增长

6.默认约束

默认约束的作用

如果这个字段不设置值，就使用默认值

默认值格式

CREATE TABLE 表名 (
	字段名 字段类型 DEFAULT 值,
	字段名 字段类型
);

操作

创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州

CREATE TABLE st9 (
	id INT,
	NAME VARCHAR(20),
	address VARCHAR(50) DEFAULT '广州'
);

添加一条记录,使用默认地址

INSERT INTO st9 (id, NAME) VALUES (1, '刘德华');

添加一条记录,不使用默认地址

INSERT INTO st9 VALUES (2, '张学友', '香港');

小结：

默认值的作用？

当这个字段不设置值，就是用默认值

7.外键约束

准备数据

-- 创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 创建员工表
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
);

-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');

-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES 
('张三', 20, 1), 
('李四', 21, 1), 
('王五', 20, 1), 
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

问题：
当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id

需要达到目的:需要约束dep_id只能是department表中已经存在id
解决方式: 使用外键约束

什么是外键

一个表中的某个字段引用其他的主键，这个字段称为外键

主表： 主键所在的表,约束别人的表,将数据给别人用
副表/从表： 外键所在的表,被约束的表,使用别人的数据

创建外键约束

新建表时增加外键约束：

CREATE TABLE 表名 (
	字段名 字段类型,
    字段名 字段类型,
    -- 添加外键
    CONSTRAINT 外键约束名 FOREIGN KEY (外键字段名) REFERENCES 主表(主键名)
);

关键字解释：
CONSTRAINT: 表示约束

外键约束名： 给新增的外键约束取个名字

FOREIGN KEY(外键字段名): 指定哪个字段作为外键

REFERENCES 主表名(主键字段名) : 指定引用哪张表的主键

1. 已有表增加外键：

   ALTER TABLE 从表 ADD CONSTRAINT 外键约束名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
   
   

具体操作：

具体操作：

• 副表/从表: 被别人约束,表结构添加外键约束
• 删除副表/从表 employee
• 创建从表 employee 并添加外键约束

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	-- 添加一个外键
	-- 外键取名公司要求,一般fk结尾
	CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);

• 正常添加数据

INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

• 部门错误的数据添加失败

INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

具体操作：

• 删除employee表的emp_depid_ref_dep_id_fk外键

ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;

• 在employee表情存在况下添加外键

ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);

小结

什么是外键?

一个字段引用另一张表的主键,这个字段就是外键

外键约束什么好处: 保证外键的值用的是主键的值.

8.级联操作

了外键约束后能直接修改和删除数据吗?

要把部门表中的id值2，改成5，能不能直接修改呢？

UPDATE department SET id=5 WHERE id=2;

不能直接修改:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接修改主表(部门表)主键

要删除部门id等于1的部门, 能不能直接删除呢？

DELETE FROM department WHERE id = 1;

不能直接删除:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接删除主表(部门表)数据

什么是级联操作

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
ON UPDATE CASCADE ：级联更新 主表主键修改后，从表的数据也跟着修改
ON DELETE CASCADE ：级联删除 主表主键删除后，从表数据也跟着删除

具体操作：

• 删除employee表
• 重新创建employee表，添加级联更新和级联删除

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_id INT,
	-- 添加外键约束,并且添加级联更新和级联删除
	CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
);

• 再次添加数据到员工表和部门表

INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

• 把部门表中id等于1的部门改成id等于10

  UPDATE department SET id=10 WHERE id=1;
  
  

• 删除部门号是2的部门

  DELETE FROM department WHERE id=2;
  
  

  小结

  级联更新：ON UPDATE CASCADE 主表主键修改,从表外键的值也跟着修改

  级联删除：ON DELETE CASCADE 主表的主键删除,从表外键值相同的也删除

6.DCL学习：

创建用户给用户授权 ，撤销用户

我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

1.创建用户

格式：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

关键字说明

1.'用户名':  即将创建的用户名
2.'主机名': 指定用户在那个主机可以登录，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
3.'密码' : 该用户的登录密码，密码可以为空如果为空，该用户不需要密码登录服务器

具体操作

-- user1 用户只能在 localhost 这个IP登录服务器
CREATE USER 'user1' @'localhost' IDENTIFIED BY '123'
-- user2用户可以在任何电脑上登录mysql
CREATE USER 'user2' @'%' IDENTIFIED BY '123';

2.用户授权

授权格式

GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';

关键字说明

1.`GRANT` 授权关键字
2.授予用户的权限，如`SELECT`，`INSERT`，`UPDATE`等。如果要授予所的权限则使用`ALL`
3.`数据库名.表名`：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如`*.*`
4.`'用户名'@'主机名'`: 给哪个用户授权

1.给user1用户分配day16数据库操作权限

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON day16.* TO 'user1'@'localhost'

2.给user2用户分配对所有数据库操作的权限

GRANT ALL ON *.* TO 'user2'@'%';

3.撤销授权

格式

REVOKE 权限1, 权限2... ON 数据库名.表名 FROM '用户名'@'主机名';

具体操作

撤销user1用户对test操作的权限

REVOKE ALL ON test.* FROM 'user1'@'localhost';

4.查看权限

SHOW GRANTS FOR '用户名'@'主机名';

操作如下：

查看user1用户的权限

SHOW GRANTS FOR 'user1'@'localhost';

1. 创建用户

   CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
   
   

2. 添加权限

   GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';
   
   

3. 删除权限

   REVOKE 权限, ... ON 数据库名.表名 FROM '用户名'@'主机名';
   
   

5.删除用户

DROP USER '用户名'@'主机名';

删除user2

 DROP USER 'user2'@'%';

6.修改密码

1.修改管理员密码

注意需要在为登录的情况下

mysqladmin -uroot -p密码 password 新密码  -- 新密码不需要加上引号

具体操作

mysqladmin -uroot -p password 123456
输入老密码

2.修改普通用户密码

注意：需要登陆MySQL的情况下操作。

set password for '用户名'@'主机名' = password('新密码');

具体操作

set password for 'user1'@'localhost' = password('666666');

小结

1. 删除用户？

   DROP USER '用户名'@'主机名';
   
   

2. 修改用户密码？

   修改root用户: 不需要登录: 在DOS命令行输入: mysqladmin -uroot -p密码 password 新密码
   修改普通用户: 需要登录: set password FOR '用户名'@'主机名' = password('新密码');
   
   

7.数据备份

1.命令行的方式备份

备份的应用场景

​ 在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

备份格式

注意这个操作不用登录

mysqldump -u用户名 -p密码 数据库 > 文件的路径

备份格式：

注意：还原的时候需要先登录MySQL，并选中对应的数据库

SOURCE 导入文件的路径

具体操作

备份day16数据库中的数据

mysqldump -uroot -proot day16 > H:\bak.sql

2.命令行还原数据

1.删除数据库中所有的表

2.登录MySQL

mysql -uroot -proot

3.选中数据库

use day22;
select database();

4.使用SOURCE命令还原数据

 source  H:\bak.sql

3.图形化界面备份

4.图形化界面导入

8.数据库三大范式

1.什么是范式

范式是指:设计数据库表的规则(Normal Form)
好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储

范式的基本分类

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

2.第一范式

即数据库表的每一列都是不可分割的原子数据项，而不能是集合、数组、记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中每个列的值只能是表的一个属性或一个属性的一部分。简而言之，第一范式每一列不可再拆分，称为原子性。

表中每一列不能再拆分

3.第二范式

​ 第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分。

​ 第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

1. 一张表只描述一件事情
2. 表中的每一个字段都依赖于主键

4.第三范式

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。

一张表的字段必须引用另一张表的主键

小结

第一范式 ：表中的字段不可拆分，直接使用

第二范式 ：一张表表示一件事情（一个实体）,给表添加主键

第三范式：表中的字段引用其他表的主键

9.DQL查询

1.DQL没有条件的简答查询

1.添加数据

CREATE TABLE st9 (
	id INT,
	NAME VARCHAR(20),
	address VARCHAR(50) DEFAULT '广州'
);

INSERT INTO st9 VALUES 
(1, '张学友', '香港'),
(2, '刘德华', '广州'),
(3, '孙悟空', '西游记'),
(4, '唐僧',   '西游记')

2.查询列表所有的数据

 -- 查询列表所有的数据
 SELECT * FROM st9;

3.查询指定列

 -- 查询指定列的数据
 
 SELECT id, NAME,address FROM st9;

4.查询指定字段

-- 查询name字段
SELECT NAME FROM st9;

5.别名查询

  --  别名查询
SELECT NAME 名字, address 地址 FROM st9; 

6.清楚重复值

-- 清除重复值

SELECT DISTINCT address FROM st9;

7.查询结果参与运算

-- 添加年龄，数学，英语成绩列，给每条记录添加对应的数学和英语成绩
ALTER TABLE st9 ADD math INT;
ALTER TABLE st9 ADD english INT;
ALTER TABLE st9 ADD age INT;

-- 查询 math + english 的和

SELECT math+english 总成绩 FROM st9;

-- 查询姓名年龄  将每个人的数学加十分
SELECT  NAME AS 姓名 , age 年龄, math +10 AS 数学成绩 FROM st9;

小结：

1.简单查询格式

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

2.定义别名

SELECT 字段名1 AS 别名1, 字段名2 AS 别名2 FROM 表名;
省略AS
SELECT 字段名1 别名1, 字段名2 别名2 FROM 表名;

3.去除重复

SELECT DISTINCT 字段名 FROM 表名;

2.DQL查询语句-条件查询(重要)

准备数据

-- 条件查询准备数据
CREATE TABLE student3 (
  id INT,
  NAME VARCHAR(20),
  age INT,
  sex VARCHAR(5),
  address VARCHAR(100),
  math INT,
  english INT
);



INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
 (1,'马云',55,'男','杭州',66,78),
 (2,'马化腾',45,'女','深圳',98,87),
 (3,'马景涛',55,'男','香港',56,77),
 (4,'柳岩',20,'女','湖南',76,65),
 (5,'柳青',20,'男','湖南',86,NULL),
 (6,'刘德华',57,'男','香港',99,99),
 (7,'马德',22,'女','香港',99,99),(
 8,'德玛西亚',18,'男','南京',56,65);

1.比较运算符

>大于
<小于
<=小于等于
>=大于等于
=等于
<>、!=不等于

 -- 查询所有的数据
 
 SELECT * FROM  `student3`
-- 比较运算符

 
 -- 查询math分数大于80分的学生
   
SELECT * FROM   `student3` WHERE math > 80;


-- 查询english分数小于或等于80分的学生

SELECT * FROM student3 WHERE english<= 80;

-- 查询age等于20岁的学生 
SELECT * FROM  student3 WHERE age=20;


-- 查询age不等于20岁的学生
SELECT * FROM student3 WHERE age!=20;
SELECT * FROM student3 WHERE age<>20;

小结：

比较运算符
   >
   <
   >=
   <=
   !=,<>
   =

2.逻辑运算符

and(&&) 多个条件同时满足
or(||) 多个条件其中一个满足
not(!) 不满足

-- 逻辑运算符

-- 查询age大于35且性别为男的两个同学

SELECT * FROM student3 WHERE age>35 AND sex='男';


-- 查询age大于35且性别为男的两个同学(满足其中一个条件即可)
SELECT * FROM student3 WHERE age>35 OR sex="男"

in关键字

语法格式：

SELECT * FROM 表名 WHERE 字段名 in (值1, 值2, 值3);

in里面的每个数据都会作为一次条件，只要满足条件的就会显示

操作如下：

-- 查询id 是1或者3或者5 的学生
SELECT * FROM student3 WHERE id=1 OR id=2 OR id=5;

-- in 关键字 括号的每个数字都会作为一次条件，只要满足条件就会显示
SELECT * FROM student3 WHERE id IN(1,2,5);

-- 查询id不是1，3 ，5
SELECT * FROM student3 WHERE id NOT IN(1,3,5)

-- 查询english成绩大于等于75，且小于等于90的学生
SELECT * FROM student3 WHERE  english >=75 AND english<=90;
 SELECT * FROM student3 WHERE english BETWEEN 75 AND 90;

小结：

逻辑运算符
  && and
  || or
  ! not

3.模糊查询

模糊查询统配符

%：表示任意多个字符

_ : 表示一个字符

-- 查询姓马的同学
SELECT * FROM `student3` WHERE NAME LIKE '马%';

-- 查询姓名中标包含德字的学生
SELECT * FROM `student3` WHERE NAME LIKE '%德%';


-- 查询姓马的同学且姓名有三个字的学生
SELECT * FROM `student3` WHERE NAME LIKE '马__';

4.排序查询

通过ORDER BY子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)

SELECT * FROM 表名 WHERE 条件 ORDER BY 字段名 [ASC|DESC];

ASC ：升序

DESC : 降序

1.单排排序

-- 查询所有的数据，使年龄降序 
SELECT  * FROM `student3`  ORDER BY age DESC;

2.组合排序

-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * FROM `student3` ORDER BY age DESC,  math DESC;

小结：

1.排序关键字

ORDER BY 字段名 [ASC|DESC];

2.升序：ASC(默认)

3.降序：DESC

5.聚合函数

五个聚合函数

count： 统计指定列记录数，记录为NULL的不统计
sum： 计算指定列的数值和，如果不是数值类型，那么计算结果为0
max： 计算指定列的最大值
min： 计算指定列的最小值
avg： 计算指定列的平均值

具体操作

-- 聚合函数
-- 统计数量
SELECT COUNT(*) FROM  student3 ;

-- 统计年龄大于40的总数

SELECT COUNT(*) FROM student3 WHERE  age>40;


-- 查询数学成绩的总分
SELECT  SUM(math) FROM  student3

-- 查询数学成绩最高分
SELECT MAX(math) FROM student3;


-- 查询数学成绩最低分
SELECT MIN(math) FROM student3;



-- 查询数学成绩平均分

SELECT AVG(math) FROM student3

小结：

函数	作用
sum(列名)	求和
count(列名)	统计数量
max(列名)	最大值
min(列名)	最小值
agv(列名)	平均值

6.分组查询

1.分组查询是指使用 GROUP BY语句对查询信息进行分组

SELECT * FROM 表名 WHERE 条件 GROUP BY 字段;

2.怎么进行分组

将分组字段结果中相同内容作为一组

SELECT * FROM student3 GROUP BY sex;

3.安性别分组

SELECT sex FROM student3 GROUP BY sex;

4.查询男女各多少人

-- 1.查询所有数据,按性别分组
-- 2.统计每组人数
SELECT sex, COUNT(*) FROM student3 GROUP BY sex;

5.查询年龄大于25岁的人,按性别分组,统计每组的人数

-- 1.先过滤掉年龄小于25岁的人
-- 2.再分组
-- 3.最后统计每组的人数
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex;

6.查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
有很多同学可能会将SQL语句写出这样:

SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2;

注意：对于分组后的条件需要用 having 子句

SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT(*) >2;
-- 只有分组后人数大于2的`男`这组数据显示出来

小结

• having是在分组后对数据进行过滤
• where是在分组前对数据进行过滤
• having后面可以使用聚合函数
• where后面不可以使用聚合函数

1.分组的原理

1.将字段值相同的作为一组
2.返回每组的第一条数据

2.where 和having的区别

having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合函数

7.分页查询

准备数据

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES 
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);

LIMIT是限制的意思，所以LIMIT的作用就是限制查询记录的条数。

LIMIT语句格式:

SELECT * FROM 表名 WHERE 条件 LIMIT offset, length;

offset是指偏移量，可以认为是跳过的记录数量，不写则默认为0。
length是指需要显示的总记录数

具体操作

查询表中数据，跳过前两条，显示六条

我们可以认为跳过前面2条，取6条数据
SELECT * FROM student3 LIMIT 2,6;

假设我们一每页显示5条记录的方式来分页，SQL语句如下：

-- 每页显示5条
-- 第一页： LIMIT 0,5;	跳过0条，显示5条
-- 第二页： LIMIT 5,5;  跳过5条，显示5条
-- 第三页： LIMIT 10,5; 跳过10条，显示5条
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5,5;
SELECT * FROM student3 LIMIT 10,5;

小结：

• 如果第一个参数是0可以简写：
  SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5;
• LIMIT 10，5; – 不够5条，有多少显示多少

3.多表查询介绍

1.什么是多表查询？

查询多张表才能的到我们想要的数据

准备数据

-- 创建部门表
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT
);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

– 笛卡尔积现象

SELECT * FROM dept,emp;

以上数据其实就是左表的每条数据和右表的每条数据组合，左表由三条，右表5条，最终3*5=15条数据

也就是说，左表和右表的每条数据的组合，这种效果称为笛卡尔积

如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

SELECT * FROM dept,emp  WHERE  emp.`dept_id`=dept.`id`;

小结：

1.什么是笛卡尔积？

​ 左表与右表的每条数据的组合，这种效果称为笛卡尔积

2.如何消除笛卡尔积

从表的外键值等于主表的外键值

1.内连接

什么是内连接

多表查询获取符合条件的数据

隐式内连接

隐式内连接：看不到JOIN关键字，条件使用WHERE指定

SELECT * FROM 表1, 表2 WHERE 条件;

显式内连接

显式内连接：使用INNER JOIN ... ON语句, 可以省略INNER

SELECT * FROM 左表 INNER JOIN 右表 ON 表连接条件 WHERE 查询条件;

案例如下：

-- 确定查询条件，我们查询的是唐僧的信息，部门表.name='唐僧'
SELECT * FROM dept d INNER JOIN emp e ON d.`id`=e.`dept_id` WHERE e.`NAME`='唐僧'

-- 确定查询字段，查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
SELECT  e.`id`    员工id,
        e.`NAME`  员工姓名,
        e.`gender` 员工性别,
        e.`salary` 员工工资,
        d.`NAME` 部门名称

  FROM dept d INNER JOIN emp e ON d.`id`=e.`dept_id` WHERE e.`NAME`='唐僧';

小结：

1.隐式内连接和显示内连接的格式？

隐式内连接

SELECT * FROM 表1, 表2 WHERE 条件;

显示内连接

SELECT * FROM 左表 INNER JOIN 右表 ON 表连接条件 WHERE 查询条件;

2.左外连接

左连接 ：使用LEFT OUTER JOIN ... ON，OUTER可以省略

SELECT * FROM 左表 LEFT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件;

左连接效果图

由图可以看出查询数据左边满足的数据全部全部显示

在部门中添加一个销售部

INSERT INTO dept (NAME) VALUES ('销售部');

-- 使用内连接查询


SELECT * FROM dept INNER JOIN emp ON dept.`id`= emp.`dept_id`;

-- 使用左连接查询

SELECT * FROM dept LEFT OUTER JOIN  emp ON dept.`id`=emp.`dept_id`;

小结：

左外连接 查询格式？

SELECT * FROM 左表 LEFT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件;

左外连接查询特点？

满足条件的显示，左表不满足条件的页显示

3.右外连接

右外连接：使用RIGHT OUTER JOIN ... ON，OUTER可以省略

SELECT * FROM 左表 RIGHT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件;

右外连接可以理解为：满足要求的数据显示,并且右表不满足要求的数据也显示(在内连接的基础上保证右边的数据全部显示)

操作如下：

-- 在员工表中添加成员

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',6666,'2013-02-24',NULL);


-- 使用内连接查询
SELECT * FROM dept INNER JOIN emp ON dept.`id`=emp.`dept_id`;

-- 使用右外连接查询

SELECT * FROM  dept RIGHT OUTER JOIN emp ON dept.`id`=emp.`dept_id`;

小结：

右外连接查询格式？

SELECT * FROM 左表 RIGHT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件;

右外连接查询特点？

满足条件的数据显示，右表不满足条件的数据也要显示

4.子查询

什么是子查询

一个查询语句的结果作为另一个查询语句的一部分

SELECT 查询字段 FROM 表 WHERE 条件;

SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

子查询结果的三种情况

1. 子查询结果是单行单列
2. 子查询结果是多行单列
3. 子查询结果是多行多列

小结：

1. 什么是子查询？

   一个查询的结果作为另一个查询的一部分

2. 子查询结果的三种情况？

   1. 单行单列

3. 多行单列

   3. 多行多列

1.单行单列子查询

-- 查询工资最高的员工

-- 1.查询工资最高
SELECT  MAX(salary) FROM emp;

-- 2.根据最高工资 查询员工的信息 单行单列子查询
SELECT * FROM emp WHERE salary=(SELECT  MAX(salary) FROM emp);


-- 查询工资小于平均工资的员工有哪些？

-- 1.查询平均工资是多少
SELECT AVG(salary) FROM emp;
-- 到员工表查询小于平均的员工信息
SELECT * FROM emp WHERE  salary<(SELECT AVG(salary) FROM emp);

小结：

子查询的结果是将单行单列时父查询如何处理

放到查询条件的地方

2.子查询结果是多行单列

-- 子查询多行单列

-- 查询工资大于5000的员工，来自那个部门
SELECT emp.`id` FROM emp WHERE salary>5000;

-- 在查询在那些部门中的名字

SELECT dept.`NAME` 部门名称  FROM dept WHERE dept.`id` IN(SELECT emp.`id` FROM emp WHERE salary>5000)

小结

子查询的结果是多行单列时父查询如何处理？

作为条件,使用in处理

3.子查询的结果是多行多列

讲解

子查询结果是多列，在FROM后面作为表

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

子查询作为表需要取别名，否则这张表没有名称无法访问表中的字段

-- **查询出2011年以后入职的员工信息，包括部门名称**

-- 查出2011年后入职的员工
SELECT * FROM emp WHERE join_date>2011-1-1
-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找到所有的部门id等于dept_id

SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date > '2011-1-1') e WHERE e.dept_id = d.id;

小结

三种子查询情况

1. 单行单列
2. 多行单列
3. 多行多列

5.多表查询练习案例

准备数据

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门位置
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');


-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);


-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);



-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,
  losalary INT,
  hisalary INT
);



-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

分析表关系

练习如下

-- 练习一
-- 查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
-- 确定查询的表 emp jop  dept

SELECT * FROM emp e  INNER JOIN job j INNER JOIN dept d;

-- 确定条件
SELECT 
  *
FROM
  emp e 
  INNER JOIN job j 
    ON e.`job_id` = j.`id` 
  INNER JOIN dept d 
    ON e.`dept_id` = d.`id` ;


-- 确定查询字段：员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT 
  e.`id` 员工id,
  e.`mgr` 员工编号,
  e.`ename` 员工姓名,
  e.`salary` 员工工资,
  j.`jname` 职务名称,
  j.`description` 职务描述,
  d.`dname` 部门内名称,
  d.`loc` 部门位置
FROM
  emp e 
  INNER JOIN job j 
    ON e.`job_id` = j.`id` 
  INNER JOIN dept d 
    ON e.`dept_id` = d.`id` ;
    
    
    
    
  -- 练习二
  
    -- 查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
    
    -- 需要查询的表  emp  jop  dept  salarygrade
    

SELECT * FROM emp e INNER JOIN job  j   INNER JOIN dept d INNER JOIN  salarygrade s;  
    
-- 连接的条件
SELECT 
  * 
FROM
  emp e 
  INNER JOIN job j 
    ON e.`job_id` = j.`id` 
  INNER JOIN dept d 
    ON e.`dept_id` = d.`id` 
  INNER JOIN salarygrade s 
    ON e.`salary` BETWEEN s.`losalary` AND s.`hisalary` ;
    
    
    
-- 额外的条件
SELECT 
  * 
FROM
  emp e 
  INNER JOIN job j 
    ON e.`job_id` = j.`id` 
  INNER JOIN dept d 
    ON e.`dept_id` = d.`id` 
  INNER JOIN salarygrade s 
    ON e.`salary` BETWEEN s.`losalary` AND s.`hisalary` WHERE j.`jname`='经理'
    
    
   -- 练习三
   -- 查询出部门编号、部门名称、部门位置、部门人数
   
   -- 需要查询的表  dept emp  
   
   SELECT * FROM dept d INNER JOIN emp e ;
    
    -- 连接的条件
    
    -- select  d.`dname`,e.`dept_id`, count(*) from dept d inner join emp e  on e.`dept_id`=d.`id` group by e.`dept_id`;
      SELECT  d.`dname`, COUNT(*) FROM dept d INNER JOIN emp e  ON e.`dept_id`=d.`id` GROUP BY e.`dept_id`;
    

小结

多表查询三大步

1. 明确查询那些表
2. 明确连接条件（表连接的条件数量是表的数量-1,而且每张表都要参与）
3. 后续查询

10.事务

1.事务概念

什么是事务

由多条SQL语句组成一个功能，这多条SQL语句就组成了一个事务。一个事务中的多条SQL要么都执行，要么都不执行，事务是一个不可分割的工作单位（原子性）

事务案例说明

张三给李四转钱

-- 创建数据表
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);

-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

模拟张三给李四转500元钱，一个转账的业务操作最少要执行下面的2条语句：

1. 张三账号-500
2. 李四账号+500

-- 1. 张三账号-500
UPDATE account SET balance = balance - 500 WHERE id=1;
-- 2. 李四账号+500
UPDATE account SET balance = balance + 500 WHERE id=2;

​ 假设当张三账号上-500元,服务器崩溃了。李四的账号并没有+500元，数据就出现问题了。我们需要保证其中一条SQL语句出现问题，整个转账就算失败。只有两条SQL都成功了转账才算成功。这个时候就需要用到事务。

事务的四大特性(ACID)

事务特性	含义
原子性（Atomicity）	事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）	事务前后数据的完整性必须保持一致。
隔离性（Isolation）	是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响。
持久性（Durability）	指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

小结

什么是事务？

多条SQL语句组成一个功能，这么多条SQL就是一个事务

事务的四大特性？

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

2.手动提交事务（重要）

事务有关的SQL语句

SQL语句	描述
START TRANSACTION;	开启事务
COMMIT;	提交事务
ROLLBACK;	回滚事务

手动提交事务使用步骤

​ 第1种情况：开启事务 -> 执行SQL语句 -> 成功 -> 提交事务
​ 第2种情况：开启事务 -> 执行SQL语句 -> 失败 -> 回滚事务

案例一

模拟张三给李四转500块钱

1. 使用DOS控制台进入MySQL
2. 执行以下SQL语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500

START TRANSACTION;
UPDATE account SET balance = balance - 500 WHERE id=1;
UPDATE account SET balance = balance + 500 WHERE id=2;

3.使用SQLYog查看数据库：发现数据并没有改变

4.在控制台执行commit提交任务：

5.使用SQLYog查看数据库：发现数据改变

案例二

模拟张三给李四转500元钱（失败）

1.在控制台执行以下SQL语句：1.开启事务， 2.张三账号-500

START TRANSACTION;
UPDATE account SET balance = balance - 500 WHERE id=1;

2.使用SQLYog查看数据库：发现数据并没有改变

3.在控制台执行rollback回滚事务：

4.使用SQLYog查看数据库：发现数据没有改变

小结：

1.如何开启事务 ：start transaction;

2.如何提交事务：commit;

3.如何回滚事务: rollback;

3.自动提交事务（重要）

​ MySQL的每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务。

1.j将金额重置为1000

2.执行以下SQL语句

UPDATE account SET balance = balance - 500 WHERE id=1;

3.使用SQLYog查看数据库：发现数据已经改变

使用SQL语句查看MySQL是否开启自动提交事务

show variables like '%commit%';
-- 或
SELECT @@autocommit; -- 推荐

通过修改MySQL全局变量"autocommit"，取消自动提交事务

0:OFF (关闭自动提交)

1：ON （开启自动提交）

4.取消自动提交事务，设置自动的参数为OFF,执行SQL语句：set autocommit = 0;

5.在控制台执行以下SQL语句：张三-500

UPDATE account SET balance = balance - 500 WHERE id=1;

6.使用SQLYog查看数据库，发现数据并没有改变

7.在控制台执行commit提交任务

8.使用SQLYog查看数据库，发现数据改变

小结：

1. 查询事务提交状态：SELECT @@AUTOCOMMIT;

2. 关闭事务自动提交：SET AUTOCOMMIT = 0;

4.事务原理（重要）

​ 事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

1. 用户登录MySQL服务器,会创建一个临时的事务日志文件
2. 当开启事务后,后续的SQL语句会保存到临时的事务日志文件中
3. 提交事务,将临时日志文件中的所有SQL语句作用到数据上
4. 回滚事务,删除临时日志文件

小结：

说出事务的原理？

1.用户登录数据库Mysql服务器，会创建一个临时文件的事务日志

2.当开启事务后，后续的SQL语句会保存到临时的事务日志文件中

3.提交事务，将临近时日的日志文件中的所有SQL作用到数据上

4.回滚事务，删除临时日志文件

事务的操作	MySQL操作事务的语句
开启事务	start transaction;
提交事务	commit;
回滚事务	rollback;
查询事务的自动提交情况	select @@autocommit;
设置事务的自动提交方式	set autocommit = 0|1;

5.事务隔离级别

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题。

事务并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致
幻读	一个事务内读取到了别的事务插入的数据，导致前后读取记录行数不同

MySQL数据库有四种隔离级别：上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

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

小结

1. 能够理解并发访问的三个问题

   脏读：一个事务读取到另一个事务还未提交的数据

   不可重复读：一个事务读取多次,每次的内容不一样

   幻读：一个事务读取多次,数量不一样

2. 能够说出mysql的四种隔离级别

   1. 读未提交 read uncommitted

3. 读已提交 read committed

   3. 可重复读 repeatable read

4. 串行化 serializable

6.脏读演示

查询和设置隔离级别

1.查询全局事务隔离级别

show variables like '%isolation%';
-- 或
select @@tx_isolation; -- 推荐使用

2.设置事务隔离级别 ，需要退出MSQL再进入MYSQL才能看到隔离级别的变化

set global transaction isolation level 级别字符串;
-- 例如:
set global transaction isolation level read uncommitted;

脏读： 一个事务读取了另一个事务中尚未提交的数据

1.打开A窗口登录MySQL，设置全局的隔离级别为最低

mysql -uroot -proot
set global transaction isolation level read uncommitted;

2.打开B窗口,AB窗口都开启事务

use day23;
start transaction;

3.A窗口更新2个人的账户数据，未提交

update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;

4.B窗口查询账户

select * from account;

5.A窗口回滚

rollback;

6.B窗口查询账户，钱没了

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入500块钱，然后打电话给李四说钱到了，李四一查询账号钱已经到账，发货给张三，张三收到货后回滚事务，李四在查账钱就没了

7.解决脏读的问题

1.在A窗口设置全局的隔离级别为read committed

set global transaction isolation level read committed;

2.B窗口退出MySQL，B窗口再进入MySQL

3.AB窗口同时开启事务

4.更新

update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;

5.B窗口查询账户

6.A窗口commit提交事务

7.B窗口查看账户

小结

1.查询全局事务隔离级别？ SELECT @@TX_ISOLATION;

2.设置全局事务隔离级别？set global transaction isolation level 级别字符串;

3.如何解决赃读? 将隔离级别提升为read committed;

8.不可重复读的演示

不可重复读：一个事务中两次读取的数据内容不一致，这就是事务update时引发的问题。

1.开启窗口A

set global transaction isolation level read committed;

2.开启B窗口，在B窗口开启事务，并查询数据

start transaction;
select * from account;

3.在A窗口开启事务，并更新数据

start transaction;
update account set balance=balance+500 where id=1;
commit;

4.B窗口查询

select * from account;

两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。
很多人认为这种情况就对了，无须困惑，当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

9.解决不可重复读问题

将全局的隔离级别进行提升为：repeatable read

1.A窗口设置隔离级别为：repeatable read

set global transaction isolation level repeatable read;

2.B窗口退出MySQL，B窗口再进入MySQL，并查询数据

start transaction;
select * from account;

、

3.A窗户更新数据

start transaction;
update account set balance=balance+500 where id=1;
commit;

4.B窗口查询

select * from account;

结论：同一个事务中为了保证多次查询数据一致，必须使用repeatable read隔离级别

10.幻读的演示

幻读： 一个事务中指的是 多次读取，数据量不一样，这是insert或delete时引发的问题

1.开启A窗口，开启事务，并查询id>1的数据

2.开启B窗口，开启事务，添加一条数据，并提交事务

3.在A窗口修改id>1数据的balance为0，并重新查询id>1的数据

我么可以将事务隔离修改为最高级别，一挡住幻读的发生

1.开启A窗口

set global transaction isolation level serializable; -- 设置隔离级别为最高

2.A窗口退出MySQL，A窗口重新登录MySQL

start transaction;
select count(*) from account;

3.再开启B窗口，登录MySQL

4.在B窗口中开启事务，添加一条记录

start transaction; -- 开启事务
insert into account (name,balance) values ('LaoWang', 500);

5.在A窗口中commit提交事务，B窗口中insert语句会在A窗口事务提交后立马运行

6.在A窗口中接着查询，发现数据不变

select count(*) from account;

7.B窗口中commit提交当前事务

8.A窗口就能看到最新的数据

论：使用serializable隔离级别，一个事务没有执行完，其他事务的SQL执行不了，可以挡住幻读。

通过提高隔离级别到串行化，可以避免并发访问的所有的问题，但效率太低。