微信交流群：https://732903.hlcode.cn/?id=NK1fWUR

SQL分类

DDL（Data Definition Language）数据库定义语言，用来定义数据库对象：数据库，表，列等（操作数据库，表等）
DML（Data Manipulation Language）数据库操作语言，用来对数据库中表的数据进行增删改（对表中的数据进行增删改）
DQL（Data Query Language）数据库查询语言，用来查询数据库中表的数据（对表中的数据进行查询）
DCL（Data Control Language）数据库控制语言，用来定义数据库的访问权限和安全级别，及创建用户（对表中的数据进行控制）

DDL — 操作数据库

1.查询

SHOW DATABASES;

2.创建

创建数据库

CREATE DATABASE 数据库名称;

创建数据库（判断，如果不存在则创建）

CREATE DATABASE IF NOT EXISTS 数据库名称;

3. 删除

删除数据库

DROP DATABASE 数据库名称;

删除数据库（判断，如果存在则删除）

DROP DATABASE IF EXISTS 数据库名称;

4.使用数据库

查看当前使用的数据库

SELECT DATABASE();

使用数据库

use 数据库名称;

DML — 操作表

创建（Create）
查询（Retrieve）
修改（Update）
删除（Delete）

查询表

查询当前数据库下所有的表名称

SHOW TABLES

查询表结构

DESC 表名称;

创建表

创建表格式

CREATE TABLE 表名(
    字段1  数据类型1,
    字段2  数据类型2,
	...
    字段3  数据类型3
)

注意：最后一行末尾，不能加逗号

CREATE TABLE student(
	id int,
    name varchar(5),
    address varchar(50)
);

删除表

删除表

DROP TABLE 表名;

删除表时判断表是否存在

DROP TABLE IF EXISTS 表名;

修改表

修改表名

ALTER TABLE 表名 RENAME TO 新的表明;

添加一列

ALTER TABLE 表名 ADD 列名 数据类型;

修改数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

修改列名和数据类型

ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;

删除列

ALTER TABLE 表名 DROP 列名;

DML – 操作表中的数据（增删改）

添加数据

给指定列添加数据

INSERT INTO 表名(列名1,列名2...)VALUE(值1,值2,...);

给全部列添加数据

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

批量添加数据

INSERT INT0表名（列名1,列名2,...） VALUES(值1,值2,),（值1,值2,...）...;
INSERT INTO0表名VALUES(值1,值2,),（值1,值2,…),（值1，值2,...)...;

SELECT * FROM student; -- 查看所有数据

-- 给全部列添加数据
INSERT INTO student ( id, NAME, sex, birthday, score, emil, address, tel )
VALUES( 003, '张三', '男', '1998-12-15', 88.88, 'zhangsan.@email', '广州', 138888888 ); 
	
-- 给全部列添加数据（简化）
INSERT INTO student VALUES( 003, '张三', '男', '1998-12-15', 88.88, 'zhangsan.@email', '广州', 138888888 );

-- 批量添加数据
INSERT INTO student VALUES
( 004, '张三', '男', '1998-12-10', 88, 'zhangsan.@email', '广州', 1388888889),
( 006, '张三', '男', '1998-12-19', 88, 'zhangsan.@email', '广州', 1388888882);

修改数据

修改表数据
```
UPDATE 表名 SET 列名1=值1,列名2=值2,... WHERE 条件
```
注意：修改语句中如果不加条件，则将所有数据都修改

-- UPDATE 表名 SET 列名1=值1,列名2=值2,... WHERE 条件
-- 将id为6的张三性别改为女
UPDATE student SET sex='女' WHERE id=6;

-- 将id为6的张三性别改为男,分数改为100
UPDATE student SET sex='女',score=100 WHERE id=6;

-- 注意：修改语句中如果不加条件，则将所有数据都修改

删除数据

删除数据
```
DELETE FROM 表名 WHERE 条件;
```

注意：删除语句如果不加条件，则将所有数据都删除

-- DELETE FROM 表名 WHERE 条件;
DELETE FROM student WHERE id=3;

-- 删除所有数据
DELETE FROM student;

-- 注意：删除语句如果不加条件，则将所有数据都删除**

DQL – 数据库查询语言

基础查询

查询多个字段

SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据

去除重复记录

SELECT DISTINCT 字段列表 FORM 表明;

起别名
```
AS : AS 可省略
```

新建表格并添加数据

CREATE TABLE prodect_lists (
NAME VARCHAR(10),
price DOUBLE,
sales_volume INT,
produced_date DATE,
category VARCHAR(20)
);

INSERT INTO prodect_lists VALUES
('华为P40',5999,1000,'2020-08-20','手机'),
('小米11',4999,5000,'2020-12-28','手机'),
('红米K30',2999,22000,'2020-03-11','手机'),
('糯米',8.99,200,'2016-06-08','食物'),
('米糊',7.99,30,'2013-11-22','食物'),
('iPhone12',6799,12000,'2020-10-28','手机'),
('DELL7590',8799,300,'2019-06-18','电脑'),
('立白洗衣粉',12.9,39000,'2018-02-13','日用品'),
(NULL,88,666,NULL,NULL),
('联想电脑',8799,700,'2017-03-13','电脑'),
('惠普电脑',8799,50,'2008-12-13','电脑');

扩展

-- 扩展：插叙每个商品的销售额：ROUND(price*sales_volume,2)保留两位小数
SELECT `name`,price,sales_volume,ROUND(price*sales_volume,2) FROM prodect_lists;
-- 所有商品价格打八折
SELECT `name`,ROUND(price*0.8)FROM prodect_lists;
-- AS起别名，AS可以省略
SELECT `name` AS 商品名,price AS 价格,sales_volume AS 销量,ROUND(price*sales_volume,2) AS 销售额 FROM prodect_lists;

条件查询

条件查询语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN…AND…	在某个范围之内（都包含）
IN(…)	多选一
LINK 占位符	模糊查询 _单个任意字符 %多个任意字符
IS NULL	是NULL
IS NOT NULL	不是NULL
AND 或 &&	并且
OR 或 \|\|	或者
NOT 或 !	非，不是

案例综合

-- 查询name是华为P40或小米11或米糊的商品
SELECT * FROM prodect_lists WHERE `name`='华为P40' OR `name`='小米11' OR `name`='米糊';
-- IN查询
SELECT * FROM prodect_lists WHERE `name` IN ('华为P40','小米11','米糊');
-- 范围（between and）
SELECT * FROM prodect_lists WHERE price BETWEEN 2999 AND 5999;
-- 扩展查询商品名为null的商品 （is null）
SELECT * FROM prodect_lists WHERE `name` IS NULL;
-- 扩展查询商品名不为null的商品 （is not null）
SELECT * FROM prodect_lists WHERE `name` IS NOT NULL;

模糊查询

% 百分号匹配查询0~n个字符
_ 下划线匹配查询1个字符

案例：LIKE模糊查询

-- 查询姓“马”成员信息
SELECT * FROM stu WHERE name LIKE '马%';
-- 查询第二个子是“华”的学员信息
SELECT * FROM stu WHERE name LIKE '_华%';
-- 查询名字中包含“云”的学员信息
SELECT * FROM stu WHERE name LIKE '%云%';

排序查询

排序查询语法

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1[排序方式1],排序字段名2[排序方式2]...;

排序方式：

ASC：升序排列（默认）
DESC：降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

-- 查询学生信息，按照数学成绩降序排列
select from stu order by math desc;
-- 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select from stu order by math desc,english asc;

聚合函数

概念：将一列数据作为一个整体，进行纵向运算

聚合函数分类：

函数名	功能
count(列名)	统计数量（一般选用部位null的值）
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

聚合函数语法：

SELECT 聚合函数名（列名） FROM 表;

-- 查询总人数
SELECT COUNT(*) FROM stu; 
-- 查询最高分
SELECT MAX(score) FROM stu;
-- 查询最低分
SELECT MIN(score) FROM stu;
-- 查询平均分
SELECT AVG(score) FROM stu;
-- 查询总分
SELECT SUM(score) FROM stu;

分组查询

分组查询语法

SELECT 字段列表 FROM 表名 WHERE分组前条件限定 GROUP BY 分组字段名 HAVING分组后条件过滤

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
在新版本中，select后面的字段必须出现在group by后面，select后面还可以出现聚合函数（mysql 8）

where和having区别：

执行时机不一样：where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
可判断的条件不一样：where不能对聚合函数进行判断，having可以

执行顺序：where>聚合函数>having

-- 查询男同学和女同学各自的平均分数
SELECT sex,AVG(score) FROM stu GROUP BY sex;
-- 注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
-- 查询男同学和女同学各自的平均分数,以及各自人数
SELECT sex,AVG(score),COUNT(score) as 人数 FROM stu GROUP BY sex;
-- 查询男同学和女同学各自的平均分数,以及各自人数，要求：分数低于70分的不参与分组
SELECT sex,AVG(score),count(score) FROM stu WHERE score>70 GROUP BY sex;  
-- 查询男同学和女同学各自的平均分数,以及各自人数，要求：分数低于70分的不参与分组,分组后人数大于等于2
SELECT sex,AVG(score),count(score) FROM stu WHERE score>70 GROUP BY sex HAVING COUNT(*)>=2;

分页查询

分页查询语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;

起始索引：从0开始
计算公式：起始索引 =（当前页码 - 1） 每页显示的条数*

tips:

分页查询limit是MySQL数据库的方言
Oracle分页查询使用rownumber
SQL Server分页查询使用top

-- 从0开始查询，查询2条数据
SELECT * FROM stu LIMIT 0,2;
-- 每页显示2条数据，查询第1页
SELECT * FROM stu LIMIT 0,2;
-- 每页显示2条数据，查询第2页
SELECT * FROM stu LIMIT 2,2;
-- 每页显示2条数据，查询第3页
SELECT * FROM stu LIMIT 4,2;
-- 起始索引 = （当前页码 - 1） * 每页显示的条数

总结

扩展查询的七个关键字的顺序：

SELECT —字段名
FROM —表明
WHERE —条件
GROUP BY —分组字段
HAVING —分组后过滤
ORDER BY —排序
LIMIT —分页

 -- 先查询表里面所有的数据并进行过滤。（此时用where关键字过滤的是表里面的数据，把name为null的给过滤掉了）
SELECT * FROM prodect_lists WHERE `name` IS NOT NULL;
-- 然后进行分组，并统计每一组有多少条数据。
SELECT category,COUNT(*) 个数 FROM prodect_lists WHERE `name` IS NOT NULL GROUP BY category;
-- 利用HAVING关键字对查询的结果再次过滤 把个数大于2的展示出来。
SELECT category,COUNT(*) 个数 FROM prodect_lists WHERE `name` IS NOT NULL GROUP BY category HAVING COUNT(*)>2;
-- 对having过滤之后的结果按照个数进行排序
SELECT category,COUNT(*) 个数 FROM prodect_lists WHERE `name` IS NOT NULL GROUP BY category HAVING COUNT(*)>2 ORDER BY 个数;
-- 最后再跳过第一个，展示一条数据
SELECT category,COUNT(*) 个数 FROM prodect_lists WHERE `name` IS NOT NULL GROUP BY category HAVING COUNT(*)>2 ORDER BY 个数 LIMIT 1,1;

约束

1.约束的概念

约束是作用于表中列上的规则，用于限制加入表的数据
约束的存在保证了数据库中数据的正确性、有效性和完整性

约束的分类

约束名称	描述	关键字
非空约束	保证列中所有数据不能有null值	NOT NULL
唯一约束	保证列中所有数据各不相同	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
检查约束	保证列中的值满足某一条件	CHECK
默认约束	保存数据时，未指定值则采用默认值	DEFAULT
外键约束	外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性	FOREIGN KEY

/* =========== 主键约束 =========== */
-- 创建表学生表st1, 包含字段(id, name, age)将id做为主键
-- 创建表时添加主键
CREATE TABLE st1(
id INT PRIMARY KEY auto_increment,
`name` VARCHAR(5),
age INT
);
SELECT * FROM st1;
-- 演示主键约束: 唯一非空
INSERT INTO st1 VALUES(1,"张三",18);
-- auto_increment主键自增长
INSERT INTO st1 VALUES(NULL,"李四",18);
-- 重复添加报错
INSERT INTO st1 VALUES(1,"张三",18); -- INSERT INTO st1 VALUES(1,"张三",18);
-- 删除主键约束
ALTER TABLE st1 DROP PRIMARY KEY;
-- 在已有表中添加主键约束
ALTER TABLE st1 ADD PRIMARY KEY (id);
-- 修改自动增长的开始值(面试题) 1000
ALTER TABLE st1 auto_increment = 1000;
INSERT INTO st1 VALUES(NULL,"王五",21);


/*
   创建员工表emp
   员工id，主键且自增长
   员工姓名ename，非空并且唯一
   入职joindate 日期，非空
   工资salary，非空，保留2位小数
   奖金bonus，如果没有奖金默认为1000
*/
CREATE TABLE emp(
		id INT PRIMARY KEY auto_increment,
		ename VARCHAR(10) NOT NULL UNIQUE,
		join_date DATE NOT NULL,
		salary DECIMAL(6,2) not NULL,
		bouns DOUBLE DEFAULT 1000
);
-- 演示默认约束     扩展MySQL中的now()表示获取现在的时间
SELECT NOW();
-- 写法1
INSERT INTO emp VALUES(NULL,'张三',NOW(),6000,DEFAULT);
-- 写法2
INSERT INTO emp(ename,join_date,salary)VALUES('蔡徐坤','2800-11-11',4000);

AUTO_INCREMENT的字段类型必须是数值类型，并且必须是主键

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

主键的作用是用来唯一标识每一条记录，主键会产生索引，提升查询速度
– 主键的作用是用来唯一标识每一条记录，主键会产生索引，提升查询速度

外键约束

1.概念：

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

2.语法

（1)添加约束

-- 创建表时添加外键约束
CREATE ABLE表名(
	列名数据类型，
[CONSTRAINT] [外键名称] FOREIGN KEY (外键列名) REFERENCES 主表（主表列名）;
    
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称（主表列名称）;

(2)删除约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称；

CREATE TABLE emp (
	id INT primary key auto_increment, -- 编号
	name VARCHAR (20), -- 姓名
	age INT, -- 年龄
	dep_id int  -- 对应部门
	-- 添加外键dep_id，关联dept表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id);
);
INSERT INTO emp VALUES
(1,'张三',20,1),
(2,'李四',20,1),
(3,'王五',20,1),
(4,'赵六',20,2),
(5,'初七',22,2),
(6,'周八',18,2);


CREATE TABLE dept 
(ID INT primary key auto_increment,
DEP_NAME VARCHAR(10),
ADDR VARCHAR(20)
);
INSERT INTO dept
VALUES
(1,'研发部','广州'),
(1,'销售部','深圳');

-- 添加外键
ALTER TABLE  emp   ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id);

数据库设计

一对一实现方式

在任意一方建立外键，关联对方主键，并设置外键唯一

CREATE TABLE tb_user(
id INT PRIMARY KEY,
photo VARCHAR(30),
nickname VARCHAR(20),
age INT,
gender CHAR(1)
);

CREATE TABLE tb_user_desc(
id INT PRIMARY KEY,
city VARCHAR(30),
edu VARCHAR(10),
income DOUBLE,
`status` VARCHAR(10),
`desc` VARCHAR(200),
-- 创建外键，这张表的主键就是外键
FOREIGN KEY(id) REFERENCES tb_user(id)
);

一对多实现方式

在多的一方建立外键关联一的一方主键

CREATE TABLE dept (  -- 主键
    ID INT primary key auto_increment,
    DEP_NAME VARCHAR(10),
    ADDR VARCHAR(20)
);

-- 外键
CREATE TABLE emp (
	id INT primary key auto_increment, -- 编号
	name VARCHAR (20), -- 姓名
	age INT, -- 年龄
	dep_id int  -- 对应部门
	-- 添加外键dep_id，关联dept表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id);
);

多对多实现方式

建立第三张中间表

中间表至少包含2个外键，分别关联双方主键

-- 订单表
CREATE TABLE tb_order(
	id INT PRIMARY KEY auto_increment,
	pryment DOUBLE,
	payment_type VARCHAR(20),
	`status` VARCHAR(10)
);

-- 商品表
CREATE TABLE tb_goods(
	id INT PRIMARY KEY auto_increment,
	titlt VARCHAR(20),
	price DOUBLE
);

-- 订单商品中间表
CREATE TABLE tb_order_goods(
	order_id int,
	goods_id INT,
	-- 创建两个外键
	FOREIGN KEY (order_id) REFERENCES tb_order(id),  -- 订单外键
	FOREIGN KEY (goods_id) REFERENCES tb_goods(id)   -- 商品外键
);

多表查询

笛卡尔积：取A,B集合所有组合情况

多表查询：从多张表查询数据

连接查询
- 内连接：相当于查询AB交集数据
- 外连接：
  - 左外连接：相当于查询A表所有数据和交集部分数据
  - 右外连接：相当于查询B表所有数据和交集部分数据
子查询

1.内连接查询语法

-- 隐式内连接
SELECT 字段列表 FROM 表1，表2 WHERE条件;
-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件；
-- 内连接相当于查询AB交集数据

2.外连接查询语法

-- 左连接(左外连接)
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件；
-- 右连接(右外连接)
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON条件；
-- 左外连接：相当于查询A表所有数据和交集部分数据
-- 右外连接：相当于查询B表所有数据和交集部分数据

案例

-- 准备数据
-- 创建部门表
CREATE TABLE tb_dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(20)
);
INSERT INTO tb_dept (`name`) VALUES ('开发部'),('市场部'),('财务部'),('销售部');

-- 创建员工表
CREATE TABLE tb_emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT
);
INSERT INTO tb_emp(`name`,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO tb_emp(`name`,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO tb_emp(`name`,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO tb_emp(`name`,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO tb_emp(`name`,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
INSERT INTO tb_emp VALUES (NULL, '白龙马', '男', 1, '2020-02-02', NULL);

SELECT * FROM tb_dept;
SELECT * FROM tb_emp;


-- 查询孙悟空员工的信息, 包括所在的部门名称
-- 一次查询多张表
-- 左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积。
SELECT * FROM tb_dept,tb_emp; 

-- 去掉笛卡尔积
-- 去掉笛卡尔积的条件称为: 表连接条件
SELECT * FROM tb_dept,tb_emp WHERE tb_emp.id = tb_dept.id; 
-- 在加上查询员工名字为孙悟空
SELECT * FROM tb_dept,tb_emp WHERE tb_emp.id = tb_dept.id AND tb_emp.`name`='孙悟空';
-- 扩展:给表取别名
SELECT * FROM tb_dept d,tb_emp e WHERE e.id = d.id AND e.`name`='孙悟空';

/* ===========显式内连接=========== */
-- 显式内连接 INNER JOIN...ON
-- INNER可以省略,初学者不建议省略
SELECT * FROM tb_emp e INNER JOIN tb_dept d ON e.dept_id=d.id;
/* ===========左外连接查询=========== */
-- 左外连接查询 (满足要求的显示,保证左表不满足要求的也显示)
SELECT * FROM tb_emp e LEFT JOIN tb_dept d ON e.dept_id=d.id;
/* ===========右外连接=========== */
-- 右外连接
SELECT * FROM tb_emp e RIGHT JOIN tb_dept d ON e.dept_id=d.id;

3.子查询概念：

查询中嵌套查询，称嵌套查询为子查询
子查询根据查询结果不同，作用不同：
- 单行单列
- 多行单列
- 多行多列

子查询根据查询结果不同，作用不同：

单行单列：作为条件值，使用=！=><等进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 =（子查询）;

/* ===========子查询的结果是单行单列=========== */
-- 查询工资最高的员工是谁？
-- 1.找到最高工资
SELECT MAX(salary) FROM tb_emp; 
-- 2.根据最高工资找出员工姓名
SELECT * FROM tb_emp WHERE salary = (SELECT MAX(salary) FROM tb_emp);
-- 子查询心得:建议先写好一条SQL,再复制到另一个SQL语句中

多行单列：作为条件值，使用等关键字进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询)；

/* ===========子查询的结果是多行单列=========== */
-- 查询工资大于5000的员工, 来自于哪些部门的名字
-- 1.查询工资大于5000的员工所在部门id
SELECT dept_id FROM tb_emp WHERE salary >= 5000; 
-- 2.根据部门id查找部门名称
SELECT * FROM tb_dept WHERE id IN (SELECT dept_id FROM tb_emp WHERE salary >= 5000);

多行多列：作为虚拟表

SELECT 字段列表 FROM (子查询) WHERE 条件；

/* ===========子查询的结果是多行多列=========== */
-- 查询出2011年以后入职的员工信息, 包括部门名称
-- 1.查询出2011年以后入职的员工信息
SELECT * FROM tb_emp WHERE join_date >='2011-1-1';
-- 2.找到对应的部门信息
SELECT * FROM (SELECT * FROM tb_emp WHERE join_date >='2011-1-1') e LEFT JOIN tb_dept d ON e.dept_id = d.id;

案例

/* ===========多表查询练习=========== */
CREATE DATABASE day16_Test CHARSET utf8;

USE day16_Test;

-- 准备数据
-- 部门表
CREATE TABLE dept (
  id INT 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);


-- 多表查询规律
-- 1.根据需求明确查询哪些表
-- 2.明确表连接条件去掉笛卡尔积
-- 3.后续的查询

-- 练习1
-- 查询所有员工信息。显示员工编号, 员工姓名, 工资, 职务名称, 职务描述
-- 1.根据需求明确查询哪些表: emp, job
-- 2.明确表连接条件去掉笛卡尔积
-- 3.后续的查询
SELECT * FROM emp e INNER JOIN job j ON e.job_id=j.id;

SELECT e.id 员工编号, e.ename 员工姓名, e.salary 工资, j.jname 职务名称, j.description 职务描述 FROM emp e INNER JOIN job j ON e.job_id=j.id ORDER BY e.id;

-- 练习2
-- 查询经理的信息。显示员工姓名, 工资, 职务名称, 职务描述, 部门名称, 部门位置, 工资等级
-- 1.根据需求明确查询哪些表: emp, job, dept, salarygrade
-- 2.明确表连接条件去掉笛卡尔积
-- 3.后续的查询
SELECT e.ename 员工姓名,e.salary 工资,j.jname 职务名称,j.description 职务描述,d.dname 部门名称,d.loc 部门位置,s.grade 工资等级 
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='经理';



-- 练习3
-- 查询出部门编号、部门名称、部门位置、部门人数
-- 1.根据需求明确查询哪些表: dept, emp
-- 2.明确表连接条件去掉笛卡尔积
-- 3.后续的查询
SELECT * FROM dept;
SELECT *，COUNT(e.id) FROM dept d LEFT JOIN emp e ON d.id=e.dept_id GROUP BY dname;

SELECT d.id 部门编号,d.dname 部门名称,d.loc 部门位置,COUNT(e.id) 部门人数 
FROM dept d LEFT JOIN emp e ON d.id=e.dept_id GROUP BY dname ORDER BY d.id;
-- 练习4
-- 列出所有员工的姓名及其直接上级领导的姓名, 没有上级领导的员工也需要显示,显示自己的名字和领导的名字
-- 1.根据需求明确查询哪些表: emp pt, emp ld
-- 2.明确表连接条件去掉笛卡尔积
-- 3.后续的查询
-- 保证所有的员工都出现，使用左连接
SELECT e.ename 员工名,m.ename 上级名 FROM emp e LEFT JOIN emp m ON e.mgr=m.id;

-- 扩展：IFNULL(字符段，默认值)，如果字段名为空，则显示默认值，不为空显示它原来的值
SELECT e.ename 员工名,IFNULL(m.ename,'老板') 上级名 FROM emp e LEFT JOIN emp m ON e.mgr=m.id;

事务

事务简介

数据库的事务(Transaction)是一种机制、一个操作序列，包含了一组数据库操作命令
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同
时成功，要么同时失败
事务是一个不可分割的工作逻辑单元

-- 开启事务
START TRANSACTION; 或者  BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

-- 案例

CREATE table counta(
id int PRIMARY KEY auto_increment,
name VARCHAR(5),
money double
);

INSERT INTO counta VALUES
(1,"张三",1000),
(2,"李四",1000);

SELECT * from counta;
UPDATE counta set money = 1000;


-- 转账操作
-- 开启事务
BEGIN;

-- 查询李四账户余额
SELECT name,money from counta WHERE name = "张三";
-- 李四余额减去500
UPDATE counta SET money = money - 500 WHERE name = "李四";
-- 张三账户加上500
UPDATE counta set money = money + 500 where name = "张三";

-- 提交事务（永久的更改）
COMMIT;

-- 回滚事务
ROLLBACK;

事务四大特征

原子性(Atomicity):事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态
隔离性(Isolation):多个事务之间，操作的可见性
持久性(Durability）:事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

MySQL事务默认自动提交

-- 查看事务的默认提交方式
SELECT @@autocommit;
-- 1 自动提交日手动提交
-- 0 修改事务提交方式
set @@autocommit = 0;

JDBC

JDBC概念：

JDBC就是使用Java语言操作关系型数据库的一套API
全称：(Java DataBase Connectivity)Java数据库连接

JDBC本质：

官方(sun公司)定义的一套操作所有关系型数据库的规侧，即接口
各个数据库厂商去实现这套接口，提供数据库驱动jar包
我们可以使用这套接口(JDBC)编程，真正执行的代码是驱动jar包中的实现类

JDBC好处：

各数据库厂商使用相同的接口，Java代码不需要针对不同数据库分别开发
可随时替换底层数据库，访问数据库的Java代码基本不变

步骤：

//1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/msl?useSSL=false";
        String usernam = "root";
        String password = "Shang1204@";
        Connection con = DriverManager.getConnection(url, usernam, password);

        //3.定义sql
        String sql = "UPDATE counta set money = money + 500 where name = \"张三\"";

        //4.获取执行sql的对象 Statement
        Statement stmt = con.createStatement();

        //5.执行sql（参数为要执行的sql语句）返回值为更改的行数
        int i = stmt.executeUpdate(sql);
        
        //6.返回执行结果
        System.out.println(i);

        //7.释放资源
        stmt.close();
        con.close();

API详解

1. DriverManager

static Connection  getConnection(String url, String usernam, String password);
//示例
Connection con = DriverManager.getConnection(url, usernam, password);

2. Connection

1.获取执行SQL对象

普通执行SQL对象
```
Statement  createStatement()
```
预编译SQL的执行SQL对象：防止SQL注入
```
PreparedStatement  prepareStatement(sql)
```
执行存储过程的对象
```
CallableStatement  prepareCall (sql)
```

2.事务管理

MySql事务管理

-- 开启事务
START TRANSACTION; 或者  BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

-- MYSQL默认自动提交事务

JDBC事务管理：Connection接口中定义了3个对应的方法

-- 开启事务：
setAutoCommit(boolean autoCommit):true为自动提交事务；false为手动提交事务，即为开启事务
-- 提交事务：
commit()
-- 回滚事务：
rollback()

3. Statement

Statement作用：执行sql语句

int executeUpdate(sql):执行DML、DPL语句
返回值：(1)DML语句影响的行数(2)DDL语句执行后，执行成功也可能返回0
    
ResultSet executeQuery(sql):执行DQL语句
返回值：ResultSet结果集对象

4. ResultSet

ResultSet(结果集对象)作用：

ResultSet  stmt.executeQuery(sql):执行DQL语句，返回ResultSet对象

获取查询结果

boolean next():(1)将光标从当前位置向前移动一行(2)判断当前行是否为有效行
返回值：
    true:有效行，当前行有数据
	false:无效行，当前行没有数据
        
XXX  getXxx(参数)：获取数据
xxx:数据类型；如：int getInt(参数)；String getString(参数)
参数：
    int: 列的编号，从1开始
    String: 列的名称

代码示例：

package com.jdbc_demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCResult {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/msl?useSSL=false";
        String usernam = "root";
        String password = "Shang1204@";
        Connection con = DriverManager.getConnection(url, usernam, password);

        //3.定义sql语句
        String sql = "select * from counta";

        //4.获取执行sql对象
        Statement sta = con.createStatement();

        //5.执行sql（执行DQL语句）
        ResultSet re = sta.executeQuery(sql);

        //6.进行条件判断
        while (re.next()){
            int id = re.getInt(1);
            String name = re.getString(2);
            double money = re.getDouble(3);
            System.out.println(id+","+name+","+money);
        }

        //7.关闭资源
        sta.close();
        con.close();
    }
}

ResultSet案例：

需求：查询Counta账户表数据，封装为Counta对象中，并且存储到ArrayList集合中

//自定义Counta类，属性为int id,String name,Double money

package com.jdbc_Result案例;

//需求：查询account账户表数据，封装为Account对象中，并且存储到ArrayLists集合中

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class ResultDemo {
    public static void main(String[] args) throws Exception{
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //获取连接
        Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/msl?useSSL=false", "root", "Shang1204@");

        //定义sql
        String sql = "select * from counta";

        //获取执行sql对象
        Statement sta = con.createStatement();

        //执行sql（DQL）
        ResultSet re = sta.executeQuery(sql);

        //创建ArrayList集合
        ArrayList<Counta> list = new ArrayList<>();

        while (re.next()){
            int id = re.getInt(1);
            String name = re.getString(2);
            double money = re.getDouble(3);

            Counta c = new Counta(id,name,money);
            list.add(c);
        }
        System.out.println(list);
    }
}

5. SQL注入攻击

JDBC实现登录案例：使用statement（存在sql注入问题“a’ or ‘1’='1”）

package com.demo_01;

import java.sql.*;
import java.util.Scanner;

public class Demo_登陆案例_sql注入 {
    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String name = scanner.nextLine();
        System.out.println("请输入密码：");  		//a' or '1'='1
        String password = scanner.nextLine();

        //注册驱动，创建连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day17_db", "root", "Shang1204@");
        //创建sql执行对象
        Statement statement = connection.createStatement();

        String sql = "select * from `user` where `name` = '" + name + "' and `password` = '" + password + "'";

        //执行sql语句
        ResultSet resultSet = statement.executeQuery(sql);

        //判断结果集中是否有记录
        if (resultSet.next()) {
            System.out.println("登陆成功，欢迎您" + name);
        } else {
            System.out.println("登录失败");
        }

        //释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

问题分析

"SELECT*FROM user WHERE name="+name +TAND password=T+password +"
∥将用户输入的账号密码拼接后
"SELECT FROM user WHERE name='newboy'AND password='a'or'1'='1';"

SQL注入攻击的原理：

按照正常道理来说，我们在密码处输入的所有内容，都应该认为是密码的组成
但是现在Statement对象在执行sql语句时，将密码的一部分内容当做查询条件来执行了

PreparedStatement预编译执行者对象：

预编译：SQL语句在执行前就已经编译好了，执行速度更快
安全性更高：没有字符串拼接的SQL语句，所以避免SQL注入的问题
代码的可读性更好，因为没有字符串拼接

案例：使用PreparedStatement解决sql注入问题

package com.demo_01;

import java.sql.*;
import java.util.Scanner;

public class Demo_03登录案例 {
    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String name = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();

        //注册驱动，创建连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day17_db","root","Shang1204@");
        //创建sql执行对象
        String sql = "select * from `user` where `name` = ? and `password` = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //替换占位符为真正的值
        preparedStatement.setString(1,name);
        preparedStatement.setString(2,password);
        //执行sql语句
        ResultSet resultSet = preparedStatement.executeQuery();

        //判断结果集中是否有记录
        if (resultSet.next()){
            System.out.println("登陆成功，欢迎您"+name);
        }else {
            System.out.println("登录失败");
        }

        //释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

案例：PreparedStatement的增删改查

1.创建表格

-- 创建tb_brand表
create table tb_brand
(
    -- id 主键
    id  int primary key auto_increment,
    -- 品牌名称
    brand_name   varchar(20),
    -- 企业名称
    company_name varchar(20),
    -- 排序字段
    ordered   int,
    -- 描述信息
    description  varchar(100),
    -- 状态：0：禁用  1：启用
    `status`   int
);
-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
       ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),
       ('小米', '小米科技有限公司', 50, 'are you ok', 1);

SELECT * FROM tb_brand;
desc tb_brand;

2.代码

package com.demo_01;

import com.demo_01.pojo.Brand;
import org.junit.Test;
import java.sql.*;
import java.util.ArrayList;

public class Demo_06prepareStatement增删改查 {
//查询数据
    @Test
    public void methodSelect() throws SQLException {
        //1.创建连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day17_db", "root", "Shang1204@");
        //2.创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement("select * from tb_brand");
        //3.执行查询语句，得到结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //4.封装成一个集合
        ArrayList<Brand> brands = new ArrayList<>();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String brandName = resultSet.getString("brand_name");
            String companyName = resultSet.getString("company_name");
            int ordere = resultSet.getInt("ordered");
            String desctiption = resultSet.getString("description");
            int status = resultSet.getInt("status");
            Brand brand = new Brand(id, brandName, companyName, ordere,desctiption, status);
            brands.add(brand);
        }
        //5.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
        //6.输出集合
        brands.forEach(System.out::println);
    }

//增加数据
    @Test
    public void methodInsert() throws SQLException {
        //创建连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day17_db", "root", "Shang1204@");

        //创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement("insert into tb_brand values(null,?,?,?,?,?)");
        preparedStatement.setString(1,"两只老虎");
        preparedStatement.setString(2,"母老虎有限公司");
        preparedStatement.setInt(3,8);
        preparedStatement.setString(4,"跑得快");
        preparedStatement.setInt(5,1);

        //执行sql语句
        int i = preparedStatement.executeUpdate();
        System.out.println(i);

        //释放资源
        preparedStatement.close();
        connection.close();
    }

//更改数据
    @Test
    public void methodUpdate() throws SQLException {
        //创建连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day17_db", "root", "Shang1204@");

        //创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement("update tb_brand set description = ?,status =? where id=?");
        preparedStatement.setString(1,"一只没有耳朵，一直没有尾巴");
        preparedStatement.setInt(2,0);
        preparedStatement.setInt(3,5);

        //执行sql语句
        int i = preparedStatement.executeUpdate();
        System.out.println(i);

        //释放资源
        preparedStatement.close();
        connection.close();
    }


//删除数据
    @Test
    public void methodDelet() throws SQLException {
        //创建连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day17_db", "root", "Shang1204@");

        //创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement("delete from tb_brand where id = 4");

        //执行sql语句
        int i = preparedStatement.executeUpdate();
        System.out.println(i);

        //释放资源
        preparedStatement.close();
        connection.close();
    }
}

数据库连接池

之前JDBC访问数据库的步骤：

创建数据库连接→运行SQL语句→关闭连接(每次数据库访问执行这样重复的动作)

每次创建数据库连接的问题

获取数据库连接需要消耗比较多的资源，而每次操作都要重新获取新的连接对象，执行一次操作就把连接关闭，而数据库创建连接通常需要消耗相对较多的资源。这样数据库连接对象的使用率低

数据库连接池简介

连接池的概念：连接池就是一个容器，连接池中保存了一些数据库连接，这些连接是可以重复使用的

连接池的原理

启动连接池，连接池就会初始化一些连接
当用户需要使用数据库连接，直接从连接池中取出
当用户使用完连接，会将连接重新放回连接池中

连接池好处

连接池中会保存一些连接，这些连接可以重复使用，降低数据资源的消耗

常用连接池的介绍

javax.sql.DataSource表示数据库连接池，是DK中提供的一个接口，没有具体的实现，它的实现由连接池的厂商去实现。我们只需要学习这个工具如何使用即可

public interface DataSource{
    Connection getConnection();
}

常用的连接池实现组件有以下这些

阿里巴巴-德鲁伊Druidi连接池：Druid是阿里巴巴开源平台上的一个项目
C3PO是一个开源的连接池，目前使用它的开源项目有Hibernate,Spring等
DBCP(DataBase Connection Pool)数据库连接池，是Tomcat使用的连接池组件

Druid常用的配置参数

方法名	说明
initialSize	列刚启动连接池时，连接池中包含连接的数量
maxActive	连接池中最多可以放多少个连接
maxWait	获取连接时最大等待时间，单位毫秒

Druid连接池介绍

import com.alibaba.druid.pool.DruidDataSourceFactory //类有创建连接池的方法
    
public static DataSource createDataSource(Properties properties)
创建一个连接池，连接池的参数使用propertiest中的数据

Druid连接池在创建的时候需要一个Properties对象来设置参数，所以我们使用properties.文件来保存对应的参数。Druid连接池的配置文件名称随便，放到src目录下面方便加载

druid.properties文件内容

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/day17
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000

Druid连接池使用步骤:

1.导入druid-1.0.0.jar的jar包
2.复制druid.properties.文件到src下，并设置对应参数
3.加载properties文件的内容到Properties对象中
4.创建Druidi连接池，使用配置文件中的参数
5.从Druid连接池中取出连接
6.执行SQL语句
7.关闭资源

package com.demo_01;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class Demo_07DataSource连接池 {
    public static void main(String[] args) throws Exception {
        //1.读取文件属性
        Properties info = new Properties();
        //加载到Properties对象中
        /*
        注:从类路径下加载输入流对象，通过类对象的方法获取
             1）类对象.getResourceAsStream() 在当前类所在的包下读取文件
             2）类加载器.getResourceAsStream() 始终从类的根目录下读取
         将文件读取，转换为Inputstream对象
        */
        //1.类对象.getResourceAsStream() 在当前类所在的包下读取文件
        InputStream inputStream = Demo_07DataSource连接池.class.getResourceAsStream("/druid.properties");
        //2.类加载器.getResourceAsStream() 始终从类的根目录下读取
        InputStream re = Demo_07DataSource连接池.class.getClassLoader().getResourceAsStream("druid.properties");
        info.load(inputStream);

        //通过数据源工厂创建数据源，需要提供创建数据源的属性
        DataSource dataSource = DruidDataSourceFactory.createDataSource(info);

        for (int i = 1; i <=11 ; i++) {
            Connection connection = dataSource.getConnection();
            System.out.println("第"+i+"个连接对象:"+connection);

            if (i == 3){
                connection.close();
            }
        }
    }
}

从类路径下加载输入流对象，通过类对象的方法获取

优点：路径名没有写死，文件更改不会影响程序的路径

package com.demo_01;

import javax.sql.DataSource;
import java.io.InputStream;
import java.util.Properties;

public class Demo_07DataSource连接池 {
    public static void main(String[] args) throws Exception {
        //1.读取文件属性
        Properties info = new Properties();
        //加载到Properties对象中
        /*
        注:从类路径下加载输入流对象，通过类对象的方法获取
             1）类对象.getResourceAsStream() 在当前类所在的包下读取文件
             2）类加载器.getResourceAsStream() 始终从类的根目录下读取
         将文件读取，转换为Inputstream对象
        */
//1.类对象.getResourceAsStream() 在当前类所在的包下读取文件
        InputStream inputStream = Demo_07DataSource连接池.class.getResourceAsStream("/druid.properties");
//2.类加载器.getResourceAsStream() 始终从类的根目录下读取
        InputStream re = Demo_07DataSource连接池.class.getClassLoader().getResourceAsStream("druid.properties");
        info.load(inputStream);
    }
}

本文含有隐藏内容，请开通VIP 后查看

JavaWeb --MYSql(MySql基础，MySql高级，JDBC，从类路径下加载输入流对象）