1. mysql安装连接

1.1 安装mysql5.7

1、解压 2、把这个包放到自己的电脑环境目录下 3、配置环境变量 4、新建mysql配置文件 ini

[mysq1d]
# 目录一定要换成自己的
basedir=D:\Environment mysq1-5.7.19\
datadir=D:\Environment\mysq1-5.7.19\data\
port=3306
skip-grant-tables

5、启动管理员模式下的CMD，运行所有的命令 6、安装mysql服务 7、初始化数据库文件 8、启动mysql，进去修改密码~ 9、进入mysql 通过命令行(-p后面不要加空格)，修改密码 (sql语句后面一定要加分号！) 10、注掉 ini 中的跳过密码 11、重启mysql。连接测试，如果连接成功就OK了!

1.2. 连接数据库

命令行连接！

mysql -uroot -p123456 --连接数据库

update mysql.user set authentication_string=password('123456') where user='root'and Host ='localhost'; -- 修改用户密码
flush privileges; -- 刷新权限

---------------------------------------------
-- 所有的语句都使用; 结尾
show databases; -- 查看所有的数据库

mysql> use school-- 切换数据库 use 数据库名
Database changed

show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息

create database westos; -- 创建一个数据库

exit; --退出连接

-- 单行注释(SQL 的本来的注释)
/*  (sg1的多行注释)
hello
22
23asdas
24dasdas
*/

2. 操作数据库

2.1 操作数据库（了解）

1、创建数据库

CREATE DATABASE IF NOT EXISTS westos;

2、删除数据库

DROP DATABASE IF EXISTS westos

3、使用数据库

-- tab 键的上面,如果你的表名或者字段名是一个特殊字符，就需要带``
USE `school`

4、查看数据库

SHOW DATABASES -- 查看所有的数据库

2.2 数据库的列类型

1、数值

tinyint 十分小的数据 1字节
smallint 较小的数据 2字节
mediumint 中等大小的数据 3字节
int 标准的整数 4个字节
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数用于金融计算

2、字符串

char 字符串固定大小的 0-255
varchar 可变字符串 0-65535 常用的变量 String
tingtext 微型文本 2^8-1
text 文本串 2^16-1 保存大文本

3、时间日期

date YY-MM-DD 日期格式
time HH:MM:SS 时间格式
datetime yy-mm-dd hh:mm:ss 最常用的日期格式
timestamp 时间戳 1970.1.1 到现在的毫秒数较为常用
year 年份表示

4、null

没有值，未知
$\textcolor{red}{}$注意，不要使用null进行运算，结果为null

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

Unsigned：

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

zerofill：

0填充的
不足的位数，使用0来填充， int (3) ， 5 -- 005

非空：null not null

假设设置为 not null ，如果不给它赋值，就会报错！
NUII ，如果不填写值，默认就是null!

默认：

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

2.4 创建数据库表（重点）

-- 目标: 创建一个school数据库
-- 创建学生表(列，字段) 使用SQL 创建
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,emai1
-- 注意点，使用英文() ， 表的名称 和 字段 尽量使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来!
-- 所有的语句后面加 ， (英文的) ，最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键!
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456'  COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

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

==常用命令==

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构

2.5 数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用~
MYISAM 早些年使用的
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约2倍

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹对应一个数据库本质还是文件的存储！

InnoDB 在数据库表中只有一个*.frm 文件，以及上级目录下的ibdata1 文件
MYISAM对应文件（了解）
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）

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

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码~（不支持中文）

MySQL的默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

2.6 修改删除表

修改

-- 修改表  ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student1 ADD age INT(11)
-- 修改表的字段 （重命名，修改约束）    
-- ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE student1 MODIFY age VARCHAR(11)  -- 修改约束
-- ALTER TABLE 表名 MODIFY 旧字段名 新字段名 列属性
ALTER TABLE student1 CHANGE age  age1 INT(1)  -- 字段重命名

删除

-- 删除表的字段
ALTER TABLE student1 DROP age1

-- 删除表
DROP TABLE IF EXISTS student1

==所有的创建和删除操作尽量加上判断，以免犯错==

注意点：

``字段名使用飘包裹
注释 -- /**/
sql关键字大小写不敏感，建议小写好认

3. MySQL数据管理

3.1 外键（了解即可）

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

-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束 (执行引用) references 引用
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456'  COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
		`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`),
		KEY FK_gradeid(`gradeid`),
		CONSTRAINT `FK_gradeid` FOREIGN KEY (gradeid ) REFERENCES  grade( `gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

数据库级别的外键不建议使用

3.2 DML语言（记）

DML语言：数据操作语言

Insert
update
delete

3.3 添加 INSERT

# 语法格式
1. 插入一行数据
   INSERT INTO 基本表名(列名1，列名2,...,列名n) VALUES(数据1,数据2，...,数据n);
2. 同时插入多行数据
   INSERT INTO 基本表名(列名1，列名2,...,列名n) 
   VALUES
   (数据11,数据12,...，数据1m),
   (数据21,数据22,...，数据2m),
   ......
   (数据n1,数据n2,...，数据nm);

# 示例1：给基本表emp插入一行记录：empno=1001,empname='小明',sex='男'
INSERT INTO emp(empno,empname,sex) VALUES(1001,'小明','男');

# 示例2：同时给emp插入两行数据
INSERT INTO emp(empno,empname,sex) VALUES
(1002,'小红','女'),
(1003,'小亮','男');

注意事项:

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

3.4 修改 UPDATE

# 语法格式
UPDATE 基本表名 SET 目标列1=目标值1,目标列2=目标值2,...,目标列n=目标值n [where 条件语句]

# 示例：将emp表中empnp=1001的empname修改为二明
UPDATE emp SET empname='二明' where empno=1001;

where条件语句是用来限定修改哪一行记录的数据的，如果不加where语句，那么会将目标列所有的值都修改为同一个值。

条件：where 子句运算符 ID等于某个值，大于某个值，在某个区间内修改

操作符	含义	范围	结果
=	等于	a=a	True
<>或 !=	不等于	a!=b	True
>	大于	1>2	True
<	小于	1<2	True
=	大于等于	3>=2	True
<=	小于等于	2<=3	True
between…and,	在某个范围内，闭合区间	[2,5]	True
and	和	2<3 and 1< 4	True
or	或	2<3 or 3 >5	True

3.5 删除 DELETE、TRUNCATE

DELETE FROM 基本表名 [where 条件语句]

DELETE操作用于删除基本表中的数据。

TRUNCATE TABLE 基本表名;

TRUNCATE 操作用于删除整个表的数据，其过程是先将这个表DROP掉，然后再建立一个相同的表格。

delete 和 TRUNCATE的区别

相同点：都能删除数据，都不会删除表结构
不同点：
- truncate：清空，自增列会归零。不会影响事务
- delete：清空，自增列不归零

4. DQL查询数据（最重点）

4.1 DQL语句完整语法格式

SELECT 要查询的列名
FROM 基本表名 			# 要查询的基本表名字
WHERE 限定条件			# 条件限定语句，用来筛选符合条件的记录
GROUP BY 分组列			# 按照分组列分组，比如说按照部门列分组，分组之后一个部门是一条记录
HAVING 限定条件			# 分组之后的条件，可以按照这个条件筛选分组之后的结果
ORDER BY 排序规则		# 按照排序规则对结果集进行排序
LIMIT 开始下标，结果数量;	# 显示指定条数的结果记录

4.2 基础查询

（1）查询表中单个字段

select 查询列表 from 表名

（2）查询多个字段

select 查询列表,查询列表,...,查询列表 from 表名

（3）查询所有字段

select * from 表名

（4）查询常量值

select 100;
select 'jack';

（5）查询表达式

select 100*2;
select 50%2;

（6）查询函数

select version();

（7）起别名

as 来给字段起别名的好处：

便于理解
如果查询的字段有重名的情况，可以使用别名来区分
as可以省略掉

SELECT 100*50 AS "结果" ;

（8）去重(distinct)

# 查询员工表中所有 部门编号 (不要重复)
select DISTINCT department_id from employees;

（9）CONCAT 连接

# 拼接字段内容
SELECT
	CONCAT( last_name, first_name ) AS "结果" 
FROM
	employees;

4.3 条件查询

语句：select 查询列表 from 表名 where 筛选条件;

分类：

按条件表达式筛选 > >= < <= = != <>
按逻辑表达式筛选 &&（and） ||（or） ! (not)
模糊查询 1>、like 2>、between and 3>、in 4>、is null

（1）按条件表达式

例如：查询工资大于12000的员工信息

select * from employees where salary>12000;

（2）按逻辑表达式

例如：查询工资在10000到15000之间的员工姓、工资

select last_name,salary 
from employees 
where salary>=10000 and salary<=15000;

# 案例：查询部门编号不在 90-110 之间 或者 工资高于 15000 的员工信息
SELECT
	* 
FROM
	employees 
WHERE
	NOT ( department_id >= 90 AND department_id <= 110 ) 
	OR salary >= 15000;

（3）模糊查询

①简单通配符

% : 任意多个字符(包含0个字符)
_ : 任意单个字符

②转义符

\
escape

③like 关键字

练习一 (%)：

# 案例：查询 first_name 为 d 开头的员工信息
SELECT
	* 
FROM
	employees 
WHERE
	first_name LIKE 'd%';

练习二 (% _)：

# 案例：查询 first_name 中第一个字符为d，第三个字符为n的员工信息
SELECT
	* 
FROM
	employees 
WHERE
	first_name LIKE 'd_n%';

练习三 (% _ \ )：

# 案例：查询 last_name 中第二个字符为_的员工信息
SELECT
	* 
FROM
	employees 
WHERE
	last_name LIKE '_\_%';

练习四 (% _ escape )：

此时 $ 相当于 \ （推荐）

# 案例：查询 last_name 中第二个字符为_的员工信息
SELECT
	* 
FROM
	employees 
WHERE
	last_name LIKE '_$_%' ESCAPE '$';

④between and 关键字

# 案例：查询 工资在 10000-20000 的员工信息
SELECT
	* 
FROM
	employees 
WHERE
	salary BETWEEN 10000 AND 20000 ;

⑤in 关键字

# 案例：查询 工作编号 为 AD_VP,SA_MAN,PR_REP 的员工信息
SELECT
	* 
FROM
	employees 
WHERE
	job_id IN ("AD_VP" , "SA_MAN" , "PR_REP") ;

⑥is null 关键字

# 案例：查询 奖金率 为 null的员工编号,奖金率
SELECT
	employee_id,
	commission_pct
FROM
	employees 
WHERE
	commission_pct IS NULL ;

⑦ is not null 关键字

# 案例：查询 奖金率 不为 null的员工编号,奖金率
SELECT
	employee_id,
	commission_pct
FROM
	employees 
WHERE
	commission_pct IS NOT NULL ;

（4）排序查询

DESC: 降序排序 (从高到低)

ASC: 升序排序 （从低到高）

练习一 (DESC , ASC)：

# 案例：查询员工信息，并按照工资从高到低排序
SELECT
	* 
FROM
	employees 
ORDER BY
	salary DESC;

(可以不加ASC，默认排序方式为ASC)

# 案例：查询员工信息，并按照工资从低到高排序
SELECT
	* 
FROM
	employees 
ORDER BY
	salary ASC;

练习二 (添加筛选条件)：

# 案例：查询员工编号>=90的员工信息，按入职时间的先后进行排序
SELECT
	* 
FROM
	employees 
WHERE
	employee_id >= 90 
ORDER BY
	hiredate ASC;

练习三 (按表达式的别名排序)：

# 案例：按年薪的高低显示员工的信息和年薪
SELECT
	*,
	salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM
	employees 
ORDER BY
	年薪 DESC;

练习四 (按函数排序)：

# 案例：按姓的长度显示员工的姓和工资
SELECT
	LENGTH( last_name ) 姓的长度,
	last_name,
	salary 
FROM
	employees 
ORDER BY
	LENGTH( last_name ) DESC;

练习五 (按多个字段主次排序)：

# 案例：将员工信息先按工资降序排序，再按员工编号升序排序
SELECT
	* 
FROM
	employees 
ORDER BY
	salary DESC,
	employee_id ASC;

（5）常见函数

单行函数 — 字符函数：

① length() 字符长度

SELECT LENGTH('abcde')

② concat() 字符拼接

SELECT CONCAT("aaa","bbb","ccc") 结果;

③ upper() ，lower() 大写，小写

# 将姓变大写，名变小写，然后进行拼接
SELECT
	CONCAT(UPPER( last_name ),LOWER( first_name )) 姓名 
FROM
	employees;

④ substr() (全称：substring）截取

ps ： 下标从 1 开始

练习一：

# 截取李四,从第四个开始一直到最后
SELECT SUBSTR("张三和李四",4) 结果;

练习二：

# 截取张三
SELECT SUBSTR("张三和李四",1,2) ;

练习三（concat，substr，upper，lower）：

# 案例：姓中首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT
	CONCAT(
		UPPER(SUBSTR( last_name, 1, 1 )),
		"_",
		LOWER(SUBSTR( last_name, 2 ))) 结果
FROM
	employees;

⑤ trim()

默认去除前面和后面的空格前面指的是：第一个不为空格的字符之前的所有空格后面指的是：最后一个不为空格的字符之后的所有空格

练习一 (去空格)：

# 去除前面和后面的空格
SELECT TRIM("      a  a      a     ") 结果 ;

练习二 (去指定字符)：

前面 aa 的个数是奇数，所以剩下一个a

SELECT TRIM("aa" FROM "aaaaaaaaabbbaaaaaaaa") 结果 ;

⑥ lpad() 左填充长度

用指定的字符实现左填充指定长度

SELECT LPAD("aaa",6,"b") 结果 ;

⑦ rpad() 右填充长度

用指定的字符实现右填充指定长度

SELECT RPAD("aaa",6,"b") 结果 ;

⑧ replace() 置换

用 cc 去替换 aa

SELECT REPLACE("aaaabbaaaa","aa","cc") 结果 ;

单行函数 — 数字函数：

① round() 四舍五入

# 四舍五入
SELECT ROUND(5.5) ;

# 四舍五入 (第二个参数表示保留的小数位数)
SELECT ROUND(5.55,1) ;

② ceil() 向上取整

# 向上取整
SELECT CEIL(2.00002) ;

③ floor() 向下取整

# 向下取整
SELECT floor(2.99999) ;

④ truncate() 截断

# 截断，保留几位小数(不进位)
SELECT TRUNCATE(2.9999,1) ;

⑤ mod() 取余

# 取余
SELECT MOD(10,3) ;

单行函数 — 日期函数：

① now() 返回当前系统日期+时间

SELECT NOW() ;

② curdate() 返回当前系统日期，不包含时间

SELECT CURDATE() ;

③ curtime() 返回当前时间，不包含日期

SELECT CURTIME() ;

④ str_to_date() 字符转日期

SELECT STR_TO_DATE("21-2-2021","%d-%c-%Y") ;

⑤ year() ，month() ，day() 分别获取年月日

SELECT YEAR(NOW()) ;

单行函数 — 流程控制函数：

① if()

参数一：表达式参数二：表达式为真时的结果参数三：表达式为假时的结果

SELECT IF(1<2,"Yes","No") ;

② case()

/*
		案例：查询员工的工资
		当部门号为30 ， 显示工资为2倍
		当部门号为40 ， 显示工资为3倍
		当部门号为50 ， 显示工资为4倍
		其他部门，显示工资不变
*/
SELECT salary 原工资,department_id 部门号,
CASE department_id
	WHEN 30 THEN salary*2
	WHEN 40 THEN salary*3
	WHEN 50 THEN salary*4
	ELSE salary
END 新工资
FROM employees;

分组函数

常见的分组函数:sum()求和,avg()平均,max()最大值,min()最小值,count()个数

特点：
1：sum() 和 avg() 一般用于处理数值型

2：max(),min(),count() 可以处理任何类型

3：以上分组函数都忽略null值

4：可以和distinct进行搭配使用

5：count()函数,一般使用count(*)统计行数

6：和分组函数一同查询的字段要求是group by后的字段
(分组函数一般用来统计,只有一个结果,而查询的字段有多个结果,表格不规则)

分组函数 — 简单使用：

① sum()，avg()，max()，min()，count()

SELECT
	SUM( salary ) 总工资,
	AVG( salary ) 平均工资,
	MAX( salary ) 最高工资,
	MIN( salary ) 最低工资,
	COUNT( salary ) 工资份数 
FROM
	employees;

分组函数 — 搭配distinct ：

① sum() + distinct

SELECT
	SUM( salary ) 总工资,
	SUM( DISTINCT salary ) 去重后的总工资 
FROM
	employees;

② count() + distinct

SELECT
	COUNT( department_id ) 部门数,
	COUNT( DISTINCT department_id ) 去重后的部门数 
FROM
	employees;

（6）分组查询 (group by)

1：分组函数作条件时要放在having子句中

2：能用分组前筛选的,就优先考虑分组前筛选

3：group by 子句支持单个字段分组,多个字段分组,表达式和函数分组(不常用)

4：也可以添加排序 (排序放在整个分组查询的最后)

① 简单使用 group by

#查询每个工种的最高工资
SELECT
	job_id 工种,
	MAX( salary ) 最高工资 
FROM
	employees 
GROUP BY
	job_id

② 添加分组前筛选

练习一：

#案例1：查询邮箱中包含a字符的每个部门的平均工资
SELECT
	department_id 部门,
	AVG( salary ) 平均工资 
FROM
	employees 
WHERE
	email LIKE "%a%" 
GROUP BY
	部门

练习二：

#案例2：查询每个领导手下有奖金的员工的最高工资
SELECT
	manager_id 领导,
	MAX( salary ) 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
GROUP BY
	领导

③ 添加分组后筛选 (having)

个人理解：

案例一：查询哪些部门的员工个数>2

当前面的结果(即每个部门的员工个数)不为表中的字段时
需要先查询出前面的结果后才可以找出哪些员工个数>2，此时需要使用having

ps：一开始错误的觉得可以直接放在where中。

where只能对表中已存在的字段进行操作(不知道理解的对不对)

而此时每个部门的员工个数在表中并不存在,需要先进行统计,再找>2的

mysql中这些关键字是按照如下顺序进行执行的：from, Where, Group By
Having, Order by。 从这看出,假如在where中找员工个数大于2的，那么
还没进行分组,找的就不是每个部门的了,即使没报错结果也不对了。(xia bi bi)

练习一：

#案例1：查询哪些部门的员工个数>2
SELECT
	department_id 部门,
	COUNT(*) 员工个数 
FROM
	employees 
GROUP BY
	部门 
HAVING
	员工个数 > 2

练习二：

#案例二：查询每个工种有奖金的最高工资>12000的工种编号和最高工资
SELECT
	job_id 工种,
	MAX( salary ) 最高工资 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
GROUP BY
	job_id 
HAVING
	最高工资 > 12000

练习三：

/*案例三：查询领导编号>102的每个领导手下的员工最低工资>5000
          的领导编号是哪个,以及其最低工资
*/
SELECT
	manager_id 领导编号,
	MIN( salary ) 最低工资 
FROM
	employees 
WHERE
	manager_id > 102 
GROUP BY
	领导编号 
HAVING
	最低工资 > 5000

④ 按函数分组

#案例：按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
SELECT
	LENGTH( last_name ) 员工姓名长度,
	COUNT(*) 员工个数 
FROM
	employees 
GROUP BY
	员工姓名长度 
HAVING
	员工个数 > 5

⑤ 按多个字段分组

#案例：查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
SELECT
	AVG( salary ) 平均工资,
	department_id 部门编号,
	job_id 工种 
FROM
	employees 
GROUP BY
	部门编号,
	工种 
ORDER BY
	平均工资 DESC

（7）连接查询 join on

① 等值连接 (sql92标准)

练习一：

# 案例一：查询员工名和对应的部门名
SELECT
	last_name,
	department_name 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id

练习二：

# 案例二：查询员工名,工种编号,工种名
SELECT
	last_name,
	e.job_id,
	job_title 
FROM
	jobs j,
	employees e 
WHERE
	j.job_id = e.job_id

练习三 (加筛选)：

# 案例三：查询有奖金的员工名，部门名
SELECT
	last_name,
	d.department_id 
FROM
	employees e,
	departments d 
WHERE
	commission_pct IS NOT NULL 
	AND e.department_id = d.department_id

练习四 (加筛选)：

# 案例四：查询城市名中第二个字符为o的部门名和城市名
SELECT
	department_name,
	city 
FROM
	departments d,
	locations l 
WHERE
	d.location_id = l.location_id 
	AND city LIKE "_o%"

练习五 (加分组)：

# 案例：查询每个城市的部门个数
SELECT
	COUNT(*),
	city 
FROM
	departments d,
	locations l 
WHERE
	d.location_id = l.location_id 
GROUP BY
	city

练习六 (加排序)：

#案例：查询每个工种的工种名和员工的个数,并且按员工个数降序
SELECT
	job_title 工种名,
	COUNT(*) 员工个数 
FROM
	jobs j,
	employees e 
WHERE
	j.job_id = e.job_id 
GROUP BY
	工种名 
ORDER BY
	员工个数 DESC

练习七 (三表连接)：

#案例：查询员工名,部门名和所在的城市
SELECT
	last_name 员工名,
	department_name 部门名,
	city 城市 
FROM
	employees e,
	departments d,
	locations l 
WHERE
	e.department_id = d.department_id 
	AND d.location_id = l.location_id

② 非等值连接 (sql92标准)

#案例：查询员工的工资和工资级别
SELECT
	salary 工资,
	grade_level 工资级别 
FROM
	employees e,
	job_grades j 
WHERE
	salary BETWEEN j.lowest_sal 
	AND j.highest_sal

③ 自连接 (sql92标准)

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

先从 employees表中找到员工名以及他所对应的领导编号然后再通过领导编号找到领导名 (相当于要查询两次employees表,所以将一张表起两个别名,当作两张表来操作)

#案例：查询员工名和上级的名称
SELECT
	e.employee_id 员工编号,
	e.last_name 员工名,
	m.employee_id 上级编号,
	m.last_name 上级名 
FROM
	employees e,
	employees m 
WHERE
	e.manager_id = m.employee_id

① 等值连接 (sql99标准)（了解）

练习一：

# 案例：查询员工名,部门名
SELECT
	last_name 员工名,
	department_name 部门名 
FROM
	employees e
	INNER JOIN departments d ON e.department_id = d.department_id

练习二 (加筛选)：

# 案例：查询名字中包含e的员工名和工种名
SELECT
	last_name 员工名,
	j.job_title 工种名 
FROM
	employees e
	INNER JOIN jobs j ON e.job_id = j.job_id 
WHERE
	last_name LIKE "%e%"

练习三 (添加分组加筛选)：

# 案例：查询部门个数>3的城市名和部门个数
SELECT
	city,
	COUNT(*) 部门个数 
FROM
	departments d
	INNER JOIN locations l ON d.location_id = l.location_id 
GROUP BY
	city 
HAVING
	部门个数 >3

练习四 (加排序)：

# 案例：查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序
SELECT
	department_name 部门名,
	COUNT(*) 员工个数 
FROM
	departments d
	INNER JOIN employees e ON d.department_id = e.department_id 
GROUP BY
	部门名 
HAVING
	员工个数 > 3 
ORDER BY
	员工个数 DESC

练习五 (三表连接)：

# 案例：查询员工名,部门名,工种名,并按部门名降序
SELECT
	last_name 员工名,
	department_name 部门名,
	job_title 工种名 
FROM
	employees e
	INNER JOIN departments d ON e.department_id = d.department_id
	INNER JOIN jobs j ON e.job_id = j.job_id 
ORDER BY
	部门名 DESC

② 非等值连接 (sql99标准)

练习一：

# 案例：查询员工的工资级别
SELECT
	salary 工资,
	grade_level 工资级别 
FROM
	employees e
	INNER JOIN job_grades j ON salary BETWEEN j.lowest_sal 
	AND j.highest_sal

练习二 (加筛选，排序，分组) ：

# 案例：查询员工个数>20的工资级别,并且按工资级别降序
SELECT
	grade_level 工资级别,
	COUNT(*) 个数 
FROM
	employees e
	INNER JOIN job_grades j ON salary BETWEEN j.lowest_sal 
	AND j.highest_sal 
GROUP BY
	工资级别 
HAVING
	个数 > 20 
ORDER BY
	工资级别 DESC

外连接的应用场景和特点

应用场景：用于查询一个表中有,另一个表中没有的记录
(需要不匹配的记录null时)

特点：
1、 外连接的查询结果为主表中的所有记录
	如果 从表 中有和他匹配的,则显示匹配的值
	如果 从表 中没有和他匹配的,则显示null
	外连接查询结果 = 内连接结果 + 主表 中有而 从表 中没有的记录

2、 左外连接,left join 左边的是主表	
	右外连接,right join 右边的是主表

① 左(右)外连接

练习一：

# 案例：查询男朋友不在boys表中的女生名
SELECT
	b.NAME,
	bo.id,
	bo.boyName 
FROM
	beauty b
	LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id 
WHERE
	bo.id IS NULL

练习二：

# 案例：查询哪个城市没有部门
SELECT
	city,
	d.* 
FROM
	locations l
	LEFT JOIN departments d ON l.location_id = d.location_id 
WHERE
	department_id IS NULL

练习三：

# 查询部门名为SAL或IT的员工信息
SELECT
	d.department_id 部门编号,department_name 部门名,
	e.* 
FROM
	departments d
	LEFT JOIN employees e ON d.department_id = e.department_id 
WHERE
	department_name IN ("SAL","IT")

② 交叉连接

展示结果类似两表的笛卡尔乘积

SELECT
	b.*,
	bo.* 
FROM
	beauty b
	CROSS JOIN boys bo

kuang-联表查询 JoinOn

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

-- Right Join
-- student name=studen1 name 查询年龄为18的人

SELECT s.name,age,s.sex
FROM student s
RIGHT JOIN student1 s1
ON s.name = s1.name 
WHERE age=18

联表分组过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心:(根据不同的课程分组)
SELECT SubjectName,AVG(StudentResut) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) As 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectNo = sub.subjectNo
GROUP BY r.subjectNo -- 通过什么字段来分组
HAVING 平均分>80

（8）子查询

① where 后面的标量子查询

标量子查询：结果集只有一行一列

练习一：

# 查询工资比 Abel 高的员工信息
SELECT * 
FROM employees
WHERE salary > (

	SELECT salary 
	FROM employees 
	WHERE last_name = "Abel"
)

练习二：

# 案例：返回job_id 与 141号员工相同,salary 比143号员工多的员工姓名,job_id 和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
)
AND salary > (
	SELECT salary 
	FROM employees
	WHERE employee_id = 143
)

练习三：

#案例：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
)

练习四：

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
	FROM employees
	GROUP BY department_id
	HAVING MIN(salary) > (
			SELECT MIN(salary)
			FROM employees
			WHERE department_id = 50
)

② where 后面的列子查询 (in,any,some,all)

列子查询：结果集只有一列多行

练习一：

#列子查询(多行子查询)
# 案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees e
WHERE department_id IN (
		SELECT department_id
		FROM departments d
		WHERE location_id IN(1400,1700)
)

练习二：

#案例2：返回其它工种中比job_id为"IT_PROG"部门任一工资低的员工的员工号，姓名，job_id 以及 salary
SELECT employee_id,last_name,job_id,salary
FROM employees 
WHERE salary < ANY(
		SELECT salary 
		FROM employees
		WHERE job_id = "IT_PROG"
) AND job_id <> "IT_PROG"

③ where 后面的行子查询

行子查询：结果集只有一行多列

#行子查询(结果集一行多列或多行多列)
# 案例：查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM employees
WHERE (employee_id,salary) = (
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
)

④ select 后面的标量子查询

# 案例：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.department_id
) 个数
FROM departments d

⑤ from 后面的表子查询

表子查询：结果集一般为多行多列

#案例：查询每个部门的平均工资的工资等级
SELECT ag.*,g.grade_level
FROM (
		SELECT department_id,AVG(salary) sa
		FROM employees
		GROUP BY department_id
) ag
INNER JOIN	job_grades g
ON ag.sa BETWEEN g.lowest_sal AND g.highest_sal

（9）分页查询 (limit)

分页查询的应用场景和特点

应用场景：当要显示的数据,一页显示不全,需要分页提交sql请求

limit offset,size
offset：要显示条目的起始索引(起始索引从0开始) 若为0可以省略
size：要显示的条目个数

特点：
1、 limit语句放在查询语句的最后面
2、 公式：
当要显示的页数为page, 每页的条目数为size
limit (page-1)*size , size

例如size=10
page   起始
0       0
1       10
2       20

练习一：

# 案例1：查询前五条员工信息
SELECT
	* 
FROM
	employees 
	LIMIT 5

练习二：

# 案例2：查询第11条 - 第25条员工信息
SELECT
	* 
FROM
	employees 
	LIMIT 10,15

练习三：

	# 案例3：查询有奖金的员工信息,并且工资较高的前10名显示出来
SELECT
	* 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
ORDER BY
	salary DESC 
	LIMIT 10

（10）联合查询 (union)

联合查询：将多条查询语句的结果合并为一个结果

语法：
查询语句1
union
查询语句2
union
查寻语句3
.....


特点：
1、要求多条查询语句的查询列数是一致的
2、要求多条查询语句的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all可以包含重复项

5. MySQL函数

5.1 常用函数

-- 时间和日期函数 (记住)
SELECT CURRENT_DATE() -- 获当前日期
SELECT CURDATE() -- 当前日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIMEO -- 本地时间
SELECT SYSDATE() -- 系统时间

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NoW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER(
)SELECT USER()
SELECT VERSION()

5.2 聚合函数（常用）

AVG()
SUM()
MAX()
MIN()
COUNT()  -- 计数

-- 都能够统计 表中的数据 (想查询一个表中有多少个记录，就使用这个count()） 
SELECT COUNT(`BornDate`) FROM student; -- Count(字段)，会忽略所有的 null值
SELECT COUNT(*) FROM student; -- Count (*) ,不会忽略null值，本质 计算行数
SELECT COUNT(1) FROM result; -- Count (1).不会忽略忽略所有的 null值，本质 计算行数

5.3 数据库级别的MD5加密（扩展）

-- 测试MD5 加密

CREATE TABLE `testmd5`(
    `id` INT(4) NOT NULL,`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL，PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=UtE8

-- 明文密码
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2, 'lisi','123456'), (3,'wangwu','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5 (pwd) WHERE id = 1
UPDATE testmd5 SET pwd=MD5 (pwd)-- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))

6. 事务

事务原则：ACID原则原子性，一致性，持久性，隔离性（脏读，幻读......）

参考博客链接：https://blog.csdn.net/dengjili/article/details/82468576/

原子性 (Atomicity) 要么都成功，要么都失败

一致性 (Consistency) 事务前后的数据完整性要保证一致，1000

持久性(Durability) --- 事务提交事务一旦提交则不可逆，被持久化到数据库中

隔离性 (Isolation) 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干务之间要相互隔离

CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=UtF8

INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)

-- 模拟转账：事务
SET autocommit=0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务

UPDATE account SET money=money-500 WHERE `name`='A';     -- A-500
UPDATE account SET money=money+500 WHERE `name`='B';     -- B+500

COMMIT; -- 提交事务
ROLLBACK; -- 回滚

SET autocommit=1; -- 恢复默认值

show variables like 'autocommit'; -- 查看是否开启自动提交

7. 索引

MysQL官方对索引的定义为: 索引(lndex)是帮助MySL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1 索引的分类

主键索引（PRIMARY KEY ）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
常规索引（KEY/INDEX）
- 默认的，index。key 关键字来设置
全文索引（FullText）
- 在特定的数据库引擎下才有，MyISAM（5.5版前MySQL的默认数据库引擎，不支持事务处理）
- 快速定位数据

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student；

-- 增加一个全文索引 (索引名)列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);

-- EXPIAIN 分析sq1执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('元宝');

8. 权限管理和备份

8.1 用户管理

用户表：mysql.user

本质：对这张表进行增删改查

-- 新建用户
CREATE USER yuanbao IDENTIFIED BY '123456';

-- 修改密码（修改当前用户密码）
SET PASSWORD=PASSWORD('123456')

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR yuanbao=PASSWORD('123456')

-- 用户重命名
RENAME USER yuanbao TO yuanbao2

-- 用户授权 ALL PRIVILEGES 所有的权限，库，表
GRANT ALL PRIVILEGES ON *.* TO yuanbao2

-- 查询权限
SHOW GRANTS FOR yuanbao2  -- 查看指定用户权限
SHOW GRANTS FOR root@localhost  -- 查看管理员权限

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM yuanbao2

8.2 MySQL备份

备份方式：

直接拷贝物理文件
用可视化工具导出数据
命令行 mysqldump 导出

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物磁盘位置:/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 物磁盘位置:/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql


# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql

mysql -u用户名 -p密码 库名< 备份文件

9. 规范数据库设计

==当数据库比较复杂就需要设计==

第一范式(1NF）
- 1NF的定义为：符合1NF的关系中的每个属性都不可再分
- 可以理解为：指数据库中表的每一列都是不可分割的基本数据项，同一列的数据不能有多个值。
第二范式(2NF）
- 2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖
- 每张表只描述一件事情
第三范式(3NF）
- 3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖
- 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

10. JDBC（重点）

10.1 数据库驱动

10.2 JDBC

没有什么是加一层解决不了的

java.sql

javax.sql

还需要导入一个数据库 mysql-connector-j-5.1.47.jar

10.3 第一个JDBC程序

创建测试数据库

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

创建一个普通项目
导入数据库驱动

编写测试代码

package com.yuanbao.lesson01;

import java.sql.*;
//第一个JDBC程序
public class JdbcFirstDemo  {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法！加载驱动

        //2.用户信息和url
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="123456";

        //3.连接成功，数据库对象  Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.执行sql的对象 Statement 执行sql对象
        Statement statement=connection.createStatement();

        //5.执行sql的对象 去 执行sql。可能存在结果，查看返回结果
        String sql="SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集，结果集中封装了我们全部查询出来的结果

        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
            System.out.println("================================");
        }

        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

加载驱动
连接数据库DriverManager
获取执行sql的对象 Statement
获得返回的结果集
释放连接

DriverManager

 //DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
 Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法！加载驱动
Connection connection = DriverManager.getConnection(url, username, password);

// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

//mysql -- 3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
//oracle -- 1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement 执行SQL的对象 PreparedStatement执行SQL的对象

 String sql="SELECT * FROM users"; //编写SQL

statement.executeQuery();//查询操作返回 ResultSet（结果集）
statement.execute();//执行任何sql
statement.executeUpdate();//更新、插入、删除，都用这个，返回一个受影响的行数

ResultSet 查询的结果集：封装了所有的查询结果

获得指定的数据类型

resu1tSet.getObject(); // 在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resu1tSet.getString();
resu1tSet.getInt();
resu1tSet.getFloat();
resu1tSet.getDate();
resu1tSet.getObject();
...

遍历，指针

resu1tSet.beforeFirst(); // 移动到最前面
resu1tSet.afterLast(); // 移动到最后面
resu1tSet.next(); //移动到下一个数据
resu1tSet.previous();//移动到前一行
resu1tSet.absolute(row); //移动到指定行

释放资源

//6.释放连接
resultSet.close();
statement.close();
connection.close(); //耗资源！用完关掉！

10.4 statement对象

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作:

Statement st = conn.createStatement();
String sql = "insert into user(....) values(....)"; 
int num = st.executeUpdate(sql);
if(num>0){
	System.out.printIn("插入成功!!!");
}

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作，示例操作:

Statement st = conn.createStatement();
String sql = "delete from user where id=1"; 
int num = st.executeUpdate(sql);
if(num>0){
	System.out.printIn("删除成功!!!");
}

CRUD操作-update

使用executeUpdate(String sql)方法完成数据修改操作，示例操作:

Statement st = conn.createStatement();
String sql = "update user set name ='' where name =''"; 
int num = st.executeUpdate(sql);
if(num>0){
	System.out.printIn("修改成功!!!");
}

CRUD操作-read

使用executeQuery(String sql)方法完成数据查询操作，示例操作:

Statement st = conn.createStatement();
String sql = "select * from user where id=1"; 
int num = st.executeQuery(sql);
while(rs.next()){
	//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

代码实现

提取工具类

编写增删改的方法，executeUpdate

package com.yuanbao.lesson02;

import com.yuanbao.lesson02.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {

        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            conn= JdbcUtils.getConnection();//获取数据库连接
            st=conn.createStatement();//获得SQL的执行对象
            String sql="INSERT users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES (4,'yuanbao','123456','1186286729@qq.com','1998-02-11')";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);

        }

    }
}

package com.yuanbao.lesson02;

import com.yuanbao.lesson02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {

        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            conn= JdbcUtils.getConnection();//获取数据库连接
            st=conn.createStatement();//获得SQL的执行对象
            String sql="delete from users where id=4";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);

        }

    }
}

package com.yuanbao.lesson02;

import com.yuanbao.lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {

        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            conn= JdbcUtils.getConnection();//获取数据库连接
            st=conn.createStatement();//获得SQL的执行对象
            String sql="update users set name ='dongdong' where name ='zhangsan'";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("修改成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);

        }
    }
}

查询 executeQuery

package com.yuanbao.lesson02;

import com.yuanbao.lesson02.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {

        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            conn= JdbcUtils.getConnection();

            st=conn.createStatement();//获得SQL的执行对象
            String sql="select * from users where id=1"; //SQL
            rs = st.executeQuery(sql);//查询完毕会返回一个结果集
            while(rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL 注入的问题

sql 存在漏洞，会被攻击导致数据泄露，==SQL会被拼接 or==

package com.yuanbao.lesson02;

import com.yuanbao.lesson02.utils.JdbcUtils;

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

//SQL注入
public class SQLInjection {
    public static void main(String[] args) {
        //login("dongdong","123456");  正常登陆
        login("'or '1=1","123456");
    }
    public static void login(String username,String password){
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            conn= JdbcUtils.getConnection();

            st=conn.createStatement();//获得SQL的执行对象

            //SELECT * FROM users WHERE `NAME`='dongdong' AND `PASSWORD`='123456'
            //SELECT * FROM users WHERE `NAME`=''or '1=1' AND `PASSWORD`=''or '1=1'
            String sql="SELECT * FROM users WHERE `NAME`='"+username+"' AND `PASSWORD`='"+password+"'"; //SQL
            rs = st.executeQuery(sql);//查询完毕会返回一个结果集
            while(rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("=====================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

    }

10.5 PrepareStatement对象

PrepareStatement 可以防止SQL 注入，效率更高！

1. 新增

package com.yuanbao.lesson03;

import com.yuanbao.lesson02.utils.JdbcUtils;

import java.sql.*;

import java.util.Date;


public class TestInsert {
    public static void main(String[] args) {

        Connection conn=null;
        PreparedStatement pst=null;
        ResultSet rs=null;


        try {
             conn = JdbcUtils.getConnection();

             //区别
            //使用? 占位符代替参数
            String sql="INSERT users(id,`NAME`,`PASSWORD`,`email`,`birthday`)VALUES (?,?,?,?,?)";

            pst= conn.prepareStatement(sql); //预编译SQL：先写SQL，然后不执行

            //手动给参数赋值
            pst.setInt(1,4);//id
            pst.setString(2,"yuanbao");//name
            pst.setString(3,"123");//密码
            pst.setString(4,"1186286729@qq.com");//邮箱
            //注意：sql.Date    数据库用的      java.sql.Date
            //     Util.Date   Java用的       new Date().getTime()  获得时间戳
            pst.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }



        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

2. 删除

package com.yuanbao.lesson03;

import com.yuanbao.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestDelete {
    public static void main(String[] args) {

        Connection conn=null;
        PreparedStatement pst=null;
        ResultSet rs=null;


        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用? 占位符代替参数
            String sql="delete from users where id=?";

            pst= conn.prepareStatement(sql); //预编译SQL：先写SQL，然后不执行

            //手动给参数赋值
            pst.setInt(1,4);

            //执行
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }



        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

3. 修改

package com.yuanbao.lesson03;

import com.yuanbao.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement pst=null;
        ResultSet rs=null;


        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用? 占位符代替参数
            String sql="update users set name =? where id =?";

            pst= conn.prepareStatement(sql); //预编译SQL：先写SQL，然后不执行

            //手动给参数赋值
            pst.setString(1,"zhangsan");
            pst.setInt(2,1);

            //执行
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("修改成功！");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

4. 查询

package com.yuanbao.lesson03;

import com.yuanbao.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;


        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用? 占位符代替参数
            String sql = "select * from users where id=?";

            pst = conn.prepareStatement(sql); //预编译SQL：先写SQL，然后不执行

            //手动给参数赋值
            pst.setInt(1, 1);

            //执行
            rs = pst.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, pst, rs);
        }
    }
}

5. 防止SQL注入

package com.yuanbao.lesson03;

import com.yuanbao.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQLInjection {
    public static void main(String[] args) {
        //login("zhangsan","123456");  正常登陆
        login("''or 1=1","123456");//技巧
    }
    public static void login(String username,String password){
        Connection conn=null;
        PreparedStatement pst=null;
        ResultSet rs=null;

        try {
            conn= JdbcUtils.getConnection();

            //PreparedStatement 防止SQL注入的本质：把传进来的参数当做字符
            //假设其中存在转义字符，就直接忽略。 比如说 ' 会被直接转义
            String sql="SELECT * FROM users WHERE `NAME`=? AND `PASSWORD`=?"; //SQL
            pst=conn.prepareStatement(sql);//预编译
            pst.setString(1,username);
            pst.setString(2,password);


            rs = pst.executeQuery();//查询完毕会返回一个结果集
            while(rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("=====================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

10.6 使用IDEA连接数据库

==连接成功后，可以选择数据库==

10.7 事务

==要么都成功，要么都失败==

ACID原则

事务原则：ACID原则原子性，一致性，持久性，隔离性（脏读，幻读......）

代码实现

开启事务
```
conn.setAutoCommit(false);
```
一组业务执行完毕，提交事务

可以在catch 语句中显示的定义回滚语句，但默认失败就会回滚

package com.yuanbao.lesson04;

import com.yuanbao.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction2 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;


        try {
            conn = JdbcUtils.getConnection();

            //关闭数据库的自动提交功能，自动会开启事务
            conn.setAutoCommit(false);

            String sql1 = "update account set money=money-100 where name='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            int x=1/0;//报错

            String sql2 = "update account set money=money+100 where name='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //业务完毕，执行事务
            conn.commit();
            System.out.println("成功！");

        } catch (SQLException e) {
            try {
                conn.rollback();//如果失败就回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.8 数据库连接池

数据库连接 --- 执行完毕 --- 释放

连接 -- 释放十分浪费系统资源

池化技术: 准备一些预先的资源，过来就连接预先准备好的

最小连接数: 10 最大连接数: 15 等待超时: 100ms

编写连接池，实现一个接口 DataSource

开源数据源实现（拿来即用）

DBCP C3PO Druid: 阿里巴巴

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了!

DBCP

需要用到的jar包

commons-dbcp2-2.9.0.jar

commons-pool2-2.11.1.jar

C3P0

需要用到的jar包

c3p0-0.9.5.5.jar

mchange-commons-java-0.2.19.jar

结论

无论使用什么数据源，本质都一样。DataSource接口不会变，方法就不会变

posted on 2023-03-30 19:17 暗狱碳水阅读(38) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

公告

导航

1. mysql安装连接

1.1 安装mysql5.7

1.2. 连接数据库

2. 操作数据库

2.1 操作数据库（了解）

2.2 数据库的列类型

1、数值

2、字符串

3、时间日期

4、null

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

2.4 创建数据库表（重点）

2.5 数据表的类型

2.6 修改删除表

3. MySQL数据管理

3.1 外键（了解即可）

3.2 DML语言（记）

3.3 添加 INSERT

3.4 修改 UPDATE

3.5 删除 DELETE、TRUNCATE

4. DQL查询数据（最重点）

4.1 DQL语句完整语法格式

4.2 基础查询

（1）查询表中单个字段

（2）查询多个字段

（3）查询所有字段

（4）查询常量值

（5）查询表达式

（6）查询函数

（7）起别名

（8）去重(distinct)

（9）CONCAT 连接

4.3 条件查询

（1）按条件表达式

（2）按逻辑表达式

（3）模糊查询

①简单通配符

②转义符

③like 关键字

练习一 (%)：

练习二 (% _)：

练习三 (% _ \ )：

练习四 (% _ escape )：

④between and 关键字

⑤in 关键字

⑥is null 关键字

⑦ is not null 关键字

（4）排序查询

练习一 (DESC , ASC)：

练习二 (添加筛选条件)：

练习三 (按表达式的别名排序)：

练习四 (按函数排序)：

练习五 (按多个字段主次排序)：

（5）常见函数

单行函数 — 字符函数：

① length() 字符长度

② concat() 字符拼接

③ upper() ，lower() 大写，小写

④ substr() (全称：substring） 截取

⑤ trim()

⑥ lpad() 左填充长度

⑦ rpad() 右填充长度

⑧ replace() 置换

单行函数 — 数字函数 ：

① round() 四舍五入

② ceil() 向上取整

③ floor() 向下取整

④ truncate() 截断

⑤ mod() 取余

单行函数 — 日期函数：

① now() 返回当前系统日期+时间

② curdate() 返回当前系统日期，不包含时间

③ curtime() 返回当前时间，不包含日期

④ str_to_date() 字符转日期

⑤ year() ，month() ，day() 分别获取年月日

单行函数 — 流程控制函数：

① if()

② case()

分组函数

④ substr() (全称：substring）截取

单行函数 — 数字函数：

分组函数 — 简单使用：

练习一：

练习二：

练习一：

练习二：

练习三：

练习一：

练习二：

练习一：

练习一：

练习一：

练习二：

练习三：