MySQL-基础

1、初识数据库

安装数据库这里省略；

1.1my.ini 基本配置

[mysqld]
basedir=F:\java\MySQL  #基本目录
datadir=F:\java\MySQL\data\  #数据目录
port=3306  #端口
skip-grant-tables   # 跳过密码验证

1.2常用命令

命令	说明
net start mysql	启动mysql服务
net stop mysql	关闭mysql服务
update mysql.user set authentication_string=password('root')	where user='root' and Host = 'localhost';修改mysql密码
flush privileges	刷新权限
mysql -u root -p	登录mysql
show databases	查看所有数据库
use 数据库名	切换数据库
describe 表名	显示表信息
crate 表名	创建数据库
exit	退出

1.4注释

符号	说明
--	单行注释
/* */	多行注释

2、数据库操作

2.1结构化查询语言分类

名称	说明	命令
数据定义语言(DDL)	定义和管理数据对象，如数据库，数据表等	CREATE,DROP,ALTER
数据操作语言(DML)	用于操作数据库对象中所包含的数据	INSERT,UPDATE,DELETE
数据查询语言(DQL)	用于查询数据库的数据	SELECT
数据控制语言(DCL)	用于管理数据的语言，包括全选及数据更改	GRANT,COMMIT,ROLLBACK

2.2操作数据库

2.2.1创建数据库

create database [if not exists] 数据库名;
-- 如果表名或者字段名是一个特殊的符号，就要带``

2.2.2 删除数据库

drop database [if exists] 数据库名; 

2.2.3 切换数据库

use 数据库名;

2.2.4 查看所有的数据库

show databases --查看所有的数据库

2.3 数据库的列信息

2.3.1数值

类型	说明	字节
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节
bigint	较大的整数	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数decimal(m,d)	m个字节

• decimal(13,2),表示这个数字有13位，小数点后又2位；

  • decimal(m,d)
    m是数字的最大位数，他的范围是从1-65；
    d是小数点后的位数，他的范围是0-30，并且不能大于m。

    如果m被省略了，那么m的值默认为10，
    如果d被省略了，那么d的值默认为0.

• 数字的长度跟取值范围没有关系(它的取值范围是确定了的)，只有zerofill的时候才有关系(会填充到0到指定的位数，比如int(4) 填写11->0011,填写12345->12345 )；

2.3.2 字符串

类型	说明	长度
char	字符串固定大小	0~255
varcahr	可变字符串	0~65535
tinytext	微型文本(存博客等)	2^8-1
text	文本串(存大文本)	2^16-1

char 和 varchar区别

 -- char(1):
 --字符串类型，小括号中指定的是字符数，固定字符长度，不足指定长度，自动使用空格，尾部补空格(查出来的时候就自动去掉空格)，会额外占用空间，长度范围：1~255
-- varchar(10):
--字符串类型，小括号中指定的是字符数，可变装都字符串，可以是存储小于指定长度的字符串，不可以超出长度（不会自动扩充），长度范围：1~65535

2.3.3 时间日期

类型	时间格式
date	YYYY-MM-DD
time	HHH:mm:ss
datetime	YYYY-MM-DD HH:mm:ss
timestamp	时间戳
year	年份

2.3.4 null

类型	说明
null	没有值，未知，注意不要使用null运算，结果位null

2.4 数据库的字段属性

2.4.1 Unsigned

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

2.4.2 zerofill

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

2.4.3 auto_Increment

• 自动增长的，每条数据，自动在上一个记录数上加1(默认)
• 通常设置主键，且必须是整数类型
• 可以自定义设置主键自增的起始值和步长

2.4.4 null 和 not null

• null ,如果不填写值，默认就是null
• not null,如果不填写值就会报错

2.4.5 业务中每个表必须存在的5个字段

字段名	说明
id	主键
version	乐观锁
is_delete	逻辑删除
gmt_create	创建时间
gmt_update	修改时间

2.5 创建数据库表

2.5.1 建表格式

create table [if not exists] `表名`(
   '字段名1' 列类型 [属性][索引][注释],
   '字段名2' 列类型 [属性][索引][注释],
  #...
   '字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];


-- 快速建表,直接将查询的数据建成一张表
-- crate table table_name (select_SQL)
create table employees_temp11(select * from employees where department_id in(50,60));

2.5.2 建表案例

-- auto_increment 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加，英文的逗号，最后一个不用加
-- primary key 主键，一般一个表只有一个唯一的主键

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email

-- 创建表之前, 一定要先选择数据库
USE school;

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;

2.5.3 常用命令

-- 查看创建数据库的语句
SHOW CREATE DATABASE school;

-- 查看创建表的语句
SHOW CREATE TABLE student;

-- 显示表结构
DESC student;  

-- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

2.6 数据表的类型

2.6.1 MYSIAM 和 INNODB 对比

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

常规使用操作：

• MYISAM 节约空间，速度快
• INNODB 安全性高，事务的处理，多表多用户操作

2.6.1 物理空间存在的位置

所有的数据库文件都存在date目录下，每一个文件对应一个数据库;

本质还是文件的存储！

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

• InnoDB在数据库表中只用一个*.frm文件，以及上级目录的ibdata1文件

• MYISAM对应文件

  • *.frm 表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）

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

charset=utf8
--不设置的话，回是mysql默认的字符编码（不支持中文）

2.7 修改删除表

2.7.1 修改表 alter

-- 修改表名 :
--ALTER TABLE 旧表名 RENAME AS 新表名；
ALTER TABLE teacher RENAME AS teacher1;

-- 添加字段 :
--ALTER TABLE 表名 ADD 字段名 列属性[属性]；
ALTER TABLE teacher1 ADD age INT(12);

-- 修改字段 : 
--ALTER TABLE 表名 MODIFY 字段名 列类型[属性]；
ALTER TABLE teacher1 MODIFY age VARCHAR(12);	 -- 修改约束（注意，如果修改字段类型，一定要兼容之前的类型）
-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型 [属性]
ALTER TABLE teacher1 CHANGE age age1 INT(12);    -- 字段重命名

-- 删除字段 : 
--`ALTER TABLE 表名 DROP 字段名；`
ALTER TABLE teacher1 DROP age1;

2.7.2 删除表

-- 删除表（如果表存在再删除）:
--DROP TABLE [IF EXISTS] 表名；
DROP TABLE IF EXISTS teacher1;
--所有的创建和删除操作尽量加上判断，以免报错

3、MySQL数据管理

3.1 外键（了解）

方式一 再创建表的时候，增加约束

-- 年级表 (id\年级名称)
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
-- 1、定义外键
	-- KEY 外键名 (字段名)
-- 2、给这给外键添加约束(执行索引)
	-- constraint 外键名 foreign key (被引用表的字段名) references 被引用表 (被引用字段)
-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student2` (
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
    `gradeid` INT(10) DEFAULT NULL COMMENT '年级',
    `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
    `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
    `borndate` DATETIME DEFAULT NULL COMMENT '生日',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
   -- KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

删除有外键关系的表的时候，必须要先删除引用别人表的表(从表)，再删除被引用的表(主表)；

** 创建表的时候没有外键关系，后面再修改表添加外键**

-- ALTER TABLE 表
--add constraint 约束名(外键名) foreign key (作为外键的列) references 被引用表 (被引用字段);
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

以上的操作都是物理外键，数据库级别的外键，我们不建议使用(避免数据库过多造成干扰)；

最佳实践

• 数据库就是单纯的表，只用来存数据，只用行（数据）和列（字段）;

• 我们想使用多张表的数据，想使用外键（程序去实现）;

3.2 DML语言（全部记住）

数据库操作语言

• insert
• update
• delete

3.3 添加 insert

-- 插入语句（添加）
-- 语法 : INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')

INSERT INTO `student`(`name`) VALUES ('张三'); --插入单个字段值

INSERT INTO `student`(`name`,`address`,`sex`) VALUES ('张三','admin','男'); --插入多个字段值

INSERT INTO `student`(`name`,`address`,`sex`) 
VALUES ('李四','pppppp','女'),('王五','tttttt','男'); -- 插入多条信息


-- 将同一个数据库中的数据 一个表中的数据 导入到另外一个表中
-- 将grades_temp表中的年级数据，迁移或者备份到grades_his
-- 表结构一样
insert into grade_his select * from grade_temp;
-- 表结构不一样，指定字段名
insert into grade_his(grade_id,grade_name)  select grade_id,grade_name from grade_temp;

-- 不同数据库转储数据
-- 将数据插入到指定数据库中
-- 插入到test数据库的grade_his表(grade_id,grade_name)字段中
insert into test.grade_his(grade_id,grade_name)  select grade_id,grade_name from grade_temp;

注意事项：

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

3.4 修改 update

-- UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHEREcondition];

UPDATE `student` SET `name`='subei',`birthday`=CURRENT_DATE WHERE id = 5;

条件：where 字句 运算符 id 等于某个值，再某个区间修改。。。

运算符	说明
=	等于
<> 或 !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
between...and...	再某个范围内[num1,num2]
and	并集
or	交集

3.5 删除 delete

-- DELETE FROM 表名 [WHERE condition];

-- 删除表数据(不带where条件的delete)
DELETE FROM `student`; -- 删除所有的数据

TRUNCATE 完全清空一个数据库表，表的结构和索引约束不会变

--清空 student表
--truncate table 表名
truncate table `student`;
-- 或 truncate 表名
truncate `student`;

** delete 和 truncate 区别**

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

delete删除的问题，重启数据库，现象 （了解）

• InnoDB 自增列会从1开始（存在内存中的，断电及失去）
• MyISAM 继续从上一个自增量开始（存在文件中，不会失去）

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

4.1 DQL

（Data Query Language:数据查询语言）

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

select语法

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

4.2 指定查询字段

4.2.1 基本查询

-- 语法： select 字段... from 表
-- 查询全部的学生  select 字段 from 表
select * from student;

-- 查询指定字段
select `studentno`,`studentname` from student;

4.2.2 as 别名

有的时候，列名字不是那么的见名知意。我们起别名 as 字段名 as 别名

-- 别名，给结果其一个名字   as  可以给字段起别名，也可以给表起别名
select `studentno` as '学号',`studentname` as '学生姓名' 
from student as s

-- 函数 concat(a,b)
select concat('姓名：',studentname) as '新名字' from student;

4.2.3 distinct 去重

select distinct `studentno` from result; -- 去重

4.2.4 数据库表达式

-- select 表达式 from 表
select version() -- 查询系统版本（函数）
select 100*3-1 '计算结果' -- 用来计算（表达式）
select @@auto_increment_increment  -- 自增步长（变量）

-- 学员考试 +1 分查看
select `studentno`,`studentresult`+1 as '提分后' from result;

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

注意

-- 用表达式
-- 查询员工表中，年薪大于9W的所有员工信息
select *
from employees
where Salary*12 > 90000;

-- 尽量不要在列上进行计算，因为如果列上有索引，会影响索引失效，导致查询速度降低
select *
from employees
where Salary > 90000/12;

4.3 where 条件子句

4.3.1 逻辑运算符

作用:检索数据中符合条件的值；

运算符	语法	描述
and &&	a and b a && b	逻辑与
or ||	a or b a || b	逻辑或
not !	not a ! a	逻辑非

尽量使用英文字母；

4.3.1 模糊查询比较运算符

运算符	语法	说明
is null	a is null	如果操作符为null,结果为真
is not null	a is not null	如果操作符不为null,结果为真
between	a between b and c	若a在b和c之间，则结果为真
like	a like b	sql匹配，如果a匹配b，则结果为真
in	a in (a1,a2,a3...)	假设a在a1,或则a2...其中的某个值中，结果为真

-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符) _（代表一个字符）
select `studentno`,`studentname` from student
where studentname like '刘%';

-- 查询姓刘的同学，名字后面只有一个字
select `studentno`,`studentname` from `student`
where studentname like '刘_';


-- 查询名字中间有奋字的同学 %嘉%
select `studentno`,`studentname` from `student`
where studentname like '%奋%';

-- ============ in ================
-- 查询 1001，1002，1003号学员
select `studentno`,`studentname` from `student`
where studentno in (1001,1002,1003,1004);

-- 查询在北京的学生
select `studentno`,`studentname` from `student`
where address in ('安徽','河南洛阳');

-- ======= null not null ======
-- 查询地址为空的学生 null ''
select `studentno`,`studentname` from `student`
where address = '' or address is null;

-- 查询有出身日期的学生  不为空
select `studentno`,`studentname` from `student`
where borndate is not null;

-- 查询没有出生日期的同学  为空
select `studentno`,`studentname` from `student`
where borndate is null;

注意点

在大表中使用模糊查询，如果当前查询列有索引，要避免使用左模糊查询，会导致索引失败，影响查询速度;

4.4 联表查询

4.4.1 等值连接，左连接，右连接

-- inner join 
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno


-- right join
select s.studentno,studentname,subjectno,studentresult
from student s
right join result r
on s.studentno = r.studentno


-- left join
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
on s.studentno = r.studentno

操作	说明
inner join	如果表中至少有一个匹配，就返回行（取交集）
left join	会从左表中返回所有的值，即使右表中没有匹配（左表为基准）
right join	会从右表中返回所有的值，即使左表中没有匹配（右表为基准）

-- =========p20 联表查询 join============
-- 查询参加了考试的同学(学号，姓名，科目编号，分数)
select * from student
select * from result

/*
 思路
 1.分许需求，分析查询的字段来自哪里，(连接查询)
 2. 确定使用娜总联接查询？ 7 种
 确定交叉点(这两个表种那个数据是相同的)
 判断条件： 学生表种的 studentno = 成绩表 studentno
*/

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

-- inner join 
-- 等值连接
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno


-- right join
select s.studentno,studentname,subjectno,studentresult
from student s
right join result r
on s.studentno = r.studentno


-- left join
-- (查询了所有同学，不考试的也会查出来)
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
on s.studentno = r.studentno


-- 查询缺考的同学
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
on s.studentno = r.studentno
where studentresult is null;


-- 思考题 （查询了参加考试的学生信息：学号，学生姓名，科目名，分数）
/*
 思路
 1.分许需求，分析查询的字段来自哪些表，student,result，subject(连接查询)
 2. 确定使用娜总联接查询？ 7 种
 确定交叉点(这两个表种那个数据是相同的)
 判断条件： 学生表种的 studentno = 成绩表 studentno
*/

select s.studentno,studentname,subjectname,studentresult
from student s
right join result r
on s.studentno = r.studentno
inner join subject sub
on r.subjectno = sub.subjectno

-- 我要查询哪些数据 select ...
-- 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表，再慢慢增加

-- from a left join b  以左边的a为基准
-- from a right join b  以右边的b为基准

4.4.自连接

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

自连接的表

categoryid	pid	categoryName
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	ps技术
8	2	办公信息

拆成两张表

父类表

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类表

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息 把一张表看为两张一摸一样的表
select a.`categoryname` as '父栏目',b.`categoryname` as '子栏目'
from `category` as a,`category` as b
where a.`categoryid` = b.`pid`;

4.5 分页limit 和排序order by

4.5.1 排序

-- 排序： 升序 asc  , 降序 desc

-- order by 通过哪个字段排序，怎么排
-- 查询的结果根据 成绩升序 排序
select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult asc;

4.5.2 分页

注意limit后面的参数必须是具体值，不能是表达式；

-- 100 万条数据
-- 为什么要分页？
-- 缓解数据库的压力，给人的体验感更好(瀑布流，无限刷)

-- 分页，每页只显示五条数据
-- 语法：limit 起始数据下标，页面大小
-- 网页应用：当前页，总页数，页面大小
-- limit 0,5   1~5
-- limit 1,5   2~6
-- limit 5,5   6~10

-- 注意limit后面的参数必须是具体值，不能是表达式

select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult asc
limit 5,5;

-- 第一页  limit 0,5   (1-1)*5
-- 第二页  limit 5,5   (2-1)*5
-- 第三页  limit 10,5  (3-1)*5
-- 第N页   limit       (n-1)*pagesize,pagesize
-- pagesize:页面大小
-- (n-1)*pagesize 起始值
-- n当前页
-- 总页数 = 数据总数/页面大小

语法：limit(起始下标，pagesize)

4.6 子查询

-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名
select distinct s.studentno,studentname
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where sub.subjectname = '高等数学-2' and studentresult >= 80

-- 子查询
select distinct s.studentno,studentname,subjectno
from student s
inner join result r
on r.studentno = s.studentno
where studentresult >= 80 and subjectno = (
		select subjectno from subject 
		where subjectname = '高等数学-2'
)

--  子查询
-- 查高等数学-2的科目号->查成绩大于80且是高等数学的学号->查高等数学成绩大于80的学号和姓名
select s.studentno,studentname
from student s
where studentno in(
		select distinct studentno from result
		where studentresult >= 80
		and subjectno =(
			select subjectno from subject
			where subjectname = '高等数学-2'
		)
)

-- 练习
-- 查询C语言-1 前5名同学的成绩的信息（学号，姓名，分数）
-- 使用子查询
select s.studentno,studentname,studentresult
from student s
inner join result r
on s.studentno = r.studentno
where r.subjectno = (
	select subjectno from subject
	where subjectname = 'C语言-1'
)
order by r.studentresult desc
limit 0,5 

4.6.1子查询遇到的问题

1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

select s.stu_no '学号',s.stu_name '姓名'
from students s
where s.stu_no in(
		-- 查询C语言成绩前5 的学生
		select r.stu_no
		from results r
		where r.subj_no = (
				--  查询 C语言-1 的课程编号 5
				select sub.subj_no
				from subjects sub
				where sub.subj_name = 'C语言-1'
		)
		order by r.stu_result desc
		limit 0,5
-- 1001,1000,1006,1005,1017
);
-- 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

解决方法

-- 解决方法
-- 保证in 里面是一个结果集，先将limit 分页出来的数据 作为一张表，在从这这张表中查数据
select s.stu_no '学号',s.stu_name '姓名'
from students s
where s.stu_no in(
		-- 查询C语言成绩前5 的学生
		select t.* from   (select r.stu_no
		from results r
		where r.subj_no = (
				--  查询 C语言-1 的课程编号 5
				select sub.subj_no
				from subjects sub
				where sub.subj_name = 'C语言-1'
		)
		order by r.stu_result desc
		limit 0,5) as t
-- 1001,1000,1006,1005,1017
);

4.6.2 扩展 form 子查询

将查询的结果集作为一张表；

-- 查询成绩表，使用子查询，根据课程名称，查询所有的学生编号和成绩
-- 1.查询成绩表，使用子查询，根据课程名称，查询所有的学生编号和成绩
select r.stu_no,r.stu_result
from results r
where r.subj_no = (
		--  查询 C语言-1 的课程编号 5
		select sub.subj_no
		from subjects sub
		where sub.subj_name = 'C语言-1'
);

-- 将第一步的查询结果，作为一张表，跟学生表关联
select st.stu_no,st.stu_name,sr.stu_result
from students st,(
		select r.stu_no,r.stu_result
		from results r
		where r.subj_no = (
				--  查询 C语言-1 的课程编号 5
				select sub.subj_no
				from subjects sub
				where sub.subj_name = 'C语言-1'
		)
) sr -- 给结果集去别名，使用该结果集的时候直接使用别名
where st.stu_no = sr.stu_no
order by r.stu_result desc
limit 0,5

4.6.3 扩展exists用法

注意学习这种子查询的思维；

子查询中的语句可以使用外层查询的字段进行条件查询；

-- 查询员工表中，所有管理者的信息
-- 子查询写法:使用 in 子查询，先确定所有的管理者(manager_id中都是管理者)，注意去重

select e.employee_id '编号',e.last_name '姓名',e.department_id '部门编号'
from employees e
where e.employee_id in(
	select distinct manager_id
	from employees
	where manager_id is not null
)


-- 扩展用法1：使用exists,性能优于in
-- 原理：自动将外层的employee_id的值，带入exists子查询中，如果查询到记录，返回true,外层就将此记录保留，否则丢弃
select e.employee_id '编号',e.last_name '姓名',e.department_id '部门编号'
from employees e
where exists (
	select e2.employee_id
	from employees e2
	where e2.manager_id = e.employee_id
)

-- 练习
-- 查询员工表，各个部门中，比员工所在部门平均工资高的员工信息

-- 子查询常规用法
-- 各部门的平均工资
select e.department_id '部门编号',avg(e.salary) '平均工资'
from employees e
group by e.department_id;

-- 将员工表和上一步的部门平均工资结果集，通过部门编号进行连接查询（等值），可以关联为一张表数据，将进行不同字段的比较
select e.employee_id '编号',e.last_name '姓名',e.department_id '部门编号',e.salary '工资',t.avgsalary '部门平均工资'
from employees e,(
  -- 各部门的平均工资  的结果集作为一张表，与其员工表进行查询
	select department_id,avg(salary) as 'avgsalary'
	from employees
	group by department_id
) as t
where e.department_id = t.department_id
and e.salary > t.avgsalary;

-- 子查询高级用法:借助exists的原理，但是效率不是很好
select e1.employee_id '编号',e1.last_name '姓名',e1.department_id '部门编号',e1.salary '工资'
from employees e1
where e1.salary > (
	select avg(salary) 
	from employees e2
	where e2.department_id = e1.department_id
);

4.7 分组和过滤 group by having

-- 分组查询，一般配合分组（多行函数）函数使用，不可以直接使用单行函数使用
-- group by  又去重的效果，也可以作为去重的方法
-- 查询不同课程的平均分，最高分，最低分，平均分大于80
select subjectname,avg(studentresult) '平均分',max(studentresult) ' 最高分',min(studentresult) '最低分'
from result r
inner join `subject` sub
on r.subjectno = sub.subjectno
group by r.subjectno -- 通过说明字段分组
having 平均分 > 80

5、MySQL常用函数

5.1 常用函数

5.1.1 数学函数(单行函数)

函数	说明
select abs(-8)	绝对值
select ceiling(9.3)	向上取整
select floor(9.8)	向下取整
select round(num,x)	四舍五入，保留为小数
select rand()	0到1之间的随机数
select sign(-10)	判断一个数的符号 0返回0，负数返回，整数返回1
select truncate(96.88,1)	截断，第二个参数表示小数位

5.1.2 字符函数

函数	说明
select char_length('我爱MySQL')	字符串长度
select concat('l','love','you')	字符串拼接
select insert('我爱你',1,2,'小可爱')	查询，从指定替换指定长度
select lower('HUAYU')	转小写
select upper('huayu')	转大写
select instr('i love mysql','l')	返回第一次出现的字串的索引
select replace('坚持就能成功','坚持','努力')	替换指定字符
select substr('狂神说坚持就能成功',4,6)	截取指定字符substr(源字符，字符起始下标，字符长度)
select reverse('开始反转吧')	反转
select replace(str,'-','');	字符替换replace(uuid(),'-','');

5.1.3 时间日期函数(单行函数

函数	说明
select current_date()	返回当前日期
select curdate()	返回当前日期
select now()	获取当前的时间
select localtime()	获取本地时间
select sysdate()	获取系统时间
select year(now())	年
select month(now())	月
select day(now())	日
select hour(now())	时
select minute(now())	分
select second(now())	秒
select date_format(now(),'%Y年%m月%d日 %H:%i:%s');	日期格式化

5.1.4 系统函数

函数	说明
select system_user()	系统用户
select user()	用户
select version()	版本

5.2 聚合函数（多行函数）

注意聚合函数，不能放在where之后，必须在一个已经确定的集合中使用聚合函数；大多放在select或者having之后，对你确定的集合进行过滤；

函数	说明
count()	计数 count(1) count(主键)
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

-- 都能够统计  表中的数据 （查询一个表中有多少个记录）
-- 统计查询结果总数，复合条件的记录数，就相当于一个计数器，有一条记录就加1，跟函数传入的值无关
-- 注意对大的表，避免使用count(*)，效率第
-- 推荐使用count(1),或者 count(主键列)
-- 强调：使用count(非主键列)，如果当前列值允许为空，统计时遇到值为空的记录时，不会计数
select count(studentname) from student; -- count(指定字段) 会忽略所有的null值
select count(*) from student;			-- count(*) 不会忽略所有的null

null值 ，本质 计算行数
select count(1) from student;			-- count(1) 不会忽略所有的null值 ， 本质计算行数

select sum(studentresult) as '总和' from result;
select avg(studentresult) as '平均分' from result;
select max(studentresult) as '最高分' from result;
select min(studentresult) as '最低分' from result;

5.1.5 ifnull

空值处理函数,因为 null 值跟任何数运算结果都是null，而我们有时候又需要对null进行计算；

-- 语法： ifnull(数据列，如果为null的默认值),这个时mysql特有的，oracle没有，它里面是nvl()

-- 查询员工表中所有人的年薪 (工资+绩效)*12  注意：有的人没有绩效，绩效为null
-- (salary + commission_pct)*12 当commission_pct为null 时结果为null
-- (salary+salary*ifnull(commission_pct,0))*12 
-- 当commission_pct为null 时 commission_pct 为0，结果就不会为null了；
selecte.last_name '姓名',(salary+salary*ifnull(commission_pct,0))*12 '年薪'
from employees e;

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

5.3.1 什么是MD5?

主要增强算法复杂程度和不可逆性；

MD5不可逆，具体的值的MD5是不一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值；

5.3.2 MD5加密，校验

-- md5(字段或指定值)
-- 加密
update testmd5 set pwd=md5(pwd) where id = 1;

-- 加密全部的数据
update testmd5 set pwd=md5(pwd) 

-- 插入的时候加密
insert into testmd5 values (4,'xiaoming',md5('123456'));


-- 如何校验
-- 将用户传递进来的密码，进行MD5加密，然后对比加密后的值
select * from testmd5 where name = 'xiaoming'
and pwd = md5('123456');

5.4 行转列

原数据

目标数据

案例代码

--语法： SUM(CASE  行转列的字段 WHEN '行转列的字段匹配的数据' THEN 行专列后的数据来源字段 ELSE 0 END) as '行转列的字段匹配的数据'
/*
查询公司在2015-2018年之间，每年雇用的人数，结果类似下面的格式
		 total	2015	2016	2017	2018
		 74	     4	    9	   30	   31
*/
-- 查询并分组每年的人 得到原数据
select total.*
from
(
select year(hire_date) 'year',count(year(hire_date)) 'num'
from employees e
group by year(hire_date)
) as total
order by total.year;

-- 行转列 得到目标数据
select sum(total.num) 'total',
SUM(CASE  total.`year` WHEN '2000' THEN total.num ELSE 0 END) as '2000',
SUM(CASE  total.`year` WHEN '2006' THEN total.num ELSE 0 END) as '2006',
SUM(CASE  total.`year` WHEN '2007' THEN total.num ELSE 0 END) as '2007',
SUM(CASE  total.`year` WHEN '2009' THEN total.num ELSE 0 END) as '2009',
SUM(CASE  total.`year` WHEN '2010' THEN total.num ELSE 0 END) as '2010',
SUM(CASE  total.`year` WHEN '2011' THEN total.num ELSE 0 END) as '2011',
SUM(CASE  total.`year` WHEN '2013' THEN total.num ELSE 0 END) as '2013',
SUM(CASE  total.`year` WHEN '2014' THEN total.num ELSE 0 END) as '2014',
SUM(CASE  total.`year` WHEN '2015' THEN total.num ELSE 0 END) as '2015',
SUM(CASE  total.`year` WHEN '2016' THEN total.num ELSE 0 END) as '2016',
SUM(CASE  total.`year` WHEN '2017' THEN total.num ELSE 0 END) as '2017',
SUM(CASE  total.`year` WHEN '2018' THEN total.num ELSE 0 END) as '2018',
SUM(CASE  total.`year` WHEN '2019' THEN total.num ELSE 0 END) as '2019'
from
(
	-- 查询并分组每年的人
	select year(hire_date) as 'year',count(year(hire_date)) as 'num'
	from employees e
	group by year(hire_date)
) as total;

6、事务

6.1 什么是事务

• 要么都成功，要么都失败。
• 事务就是将一组SQL语句放在同一批次内去执行；
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行

6.2 ACID原则

6.2.1 原子性 (Atomicity)

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

6.2.2 一致性 (Consistency)

事务前后的数据完整性要保证一致(比如转账中事务前一共有100元，事务后也要有100元)；

6.2.3 隔离性（Isonlation）

事务的隔离性是指多个用户并发访问数据时，数据库为每一个用户开启事务，不能被其他的事务的操作数据所干扰，事务之间要相互隔离；(比如 A给B转账 和 C给D转账 是隔离的)；

6.2.4 持久性（Durability）

事务一旦提交则不可逆转，被持久化到数据库中；

6.3 事务的隔离级别

6.3.1 脏读

一个事务读取了另一个事务没有提交的数据；

6.3.2 不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同；

(多次读到的值不同可能是在两次读取中又有事务对数据进行了修改)；

(这个不一定是错误，只是某些场合不对)；

6.3.3 幻读(虚读)

是指在一个事务内读取到了别的是事务新插入的数据，导致前后读取不一致；(一般是行数不一致，多了行数)；

6.4 执行事务的过程

-- mysql 是默认开启事务自动提交的
set autocommit = 0; -- 关闭
set autocommit = 1; -- 提交

-- 手动处理事务
set autocommit = 0; -- 关闭自动提交

-- 事务开启
start transaction  -- 标记一个事务的开始，从这个之后的sql都在同一个事务内 
-- 可省略，写了表示立即启动一个事务，不写系统会自动开启一个事务

insert xx
insert xx

-- （成功）提交：持久化（成功）
commit

-- （失败）回滚：回滚到原来的样子

rollback

-- 事务结束
set autommit =1 -- 开启自动提交


savepoint 保存点名 --   设置一个事务的保存点，事务比较长的时候
rollback savepoint 保存点名  -- 回滚到保存点
release savepoint  保存点名 -- 撤销（删除）保存点

案例：模拟转账

-- 模拟转账
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'  -- A增加500

commit; -- 提交事务 注意提交后才看到数据库中的数据发生改变
rollback; -- 回滚

set autocommit = 1;  -- 注意 一定要 恢复自动提交模式

7、索引

7.1索引概述

索引（index）是帮助 MysQL高效获取数据的数据结构。

提取句子主千，就可以得到索引的本质：索引是数据结构。

7.2 索引的分类

索引名	说明
PRIMARY KEY	主键索引
UNIQUE KEY	唯一索引
KEY/INDEX	常规索引
FullText	全文索引(MyISAM)

7.2.1 PRIMARY KEY 主键索引

• 唯一的标识，主键不可以重复，只能由一个列作为主键；

7.2.2 UNIQUE KEY 唯一索引

• 避免重复的列出现，唯一索引可以重复，多个列多可以标识为唯一索引；

7.2.3 KEY/INDEX 常规索引

• 默认的，index,key关键字来设置；

7.2.4 FullText 全文索引

• 在的顶的数据引擎下才有，MyISAM；

• 快速定位；

-- 显示所有的索引信息 
show index from student;

-- 增加一个全文索引
alter table `kuangshen_school`.`student` add fulltext index `studentname` (`studentname`);

-- explain 分析sql执行的状况
explain select * from student; -- 非全文索引

explain select * from student where match(studentname) against('刘'); -- 全文索引

7.3 测试索引

添加数据

-- 建表app_user：
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表';

-- 批量插入数据：100w
-- 插入100万条数据
DROP FUNCTION IF EXISTS mock_data;

DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION `mock_data`()
RETURNS INT
deterministic
BEGIN
	declare num int default 1000000;
    DECLARE i INT DEFAULT 0;
    WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`) values(CONCAT('用户',i),'xxxxxx@qq.com',CONCAT('18',FLOOR(RAND()*999999999)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
        SET i = i+1;
	END WHILE;
    RETURN i;
END;

-- 查询 执行函数
select mock_data();

-- 删除 函数
drop function mock_date;

测试

-- 没有加索引之前
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 查看耗时 时间: 1.218s
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 时间: 1.228s
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 时间: 1.218s

EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999';

-- id_表名_字段名
-- CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

-- 加上索引后查询
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 时间: 0.007s
EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999';

索引在小数据量的时候，用处不大，但在大数据的时候，区别十分明显；

7.4 索引原则

• 索引不是越多越好；
• 不要对经常变动的数据加索引；
• 小数据量的表不需要加索引；
• 索引一般加在常用来查询的字段上;

索引的数据结构

Hash类型的索引

Btree：InnoDB的默认数据库结构

8、权限管理

8.1 可视化操作

8.2 SQL命令操作

-- 创建用户
-- create user 用户名 identified '密码'
create user huayu identified by '123456';

-- 修改密码（修改当前用户密码）
set password = password('123456');

-- 修改密码（修改指定用户密码）
set password for huayu = password('123456');

-- 重命名
-- rename user 原来的名字 to 新的名字
rename user huayu to hauyu2

-- 用户授权 all privileges 全部的权限  库.表
-- all privilege 除了给别人授权，其他权限都有
grant all privileges on *.* to huayu 

-- 查询权限
show grants for huayu; -- 查看指定用户的权限
show grants for root@localhost;

-- 撤销权限 revoke 哪些权限，在哪个库撤销，给谁撤销
revoke all privileges on *.* from huayu;

-- 删除用户
drop user huayu;

9、数据库备份

9.1 为什么要备份

• 保证重要的数据不丢失
• 数据转移

9.2 MySQL数据库备份的方式

• 直接拷贝物理文件
• 在可视化界面手动导出
• 使用命令行导出 mysqldump 命令行使用

-- 使用命令行导出
-- mysqldump -h 主机 -u 用户名 -p 密码 数据库  [表名...] > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

-- 导入
-- 登录的情况下，切换到只当数据库
-- source 备份的文件
source D:/a.sql

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

10、规范数据库设计

10.1 为什么需要设计

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】
• 程序的性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统

软件开发中，关于数据库的设计：

• 分析需求：分析业务和需求需要处理的数据库的需求
• 概要设计：设计关系图 E-R 图

设计数据库的步骤：（个人博客）

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章信息）
  • 友情链接表
  • 自定义表（系统信息，某个关键字，或者一些主字段） key:value
  • 说说表（发表心情。。id..content...create_time）
• 标识的实体（把需求落地到字段）
• 标识实体之间的关系
  • 写博客：user->blog
  • 创建分类： user->category
  • 关注：user->user
  • 友链：links
  • 评论：user->user->blog

10.2 三大范式

10.2.1为什么需要数据规范化

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效的信息

10.2.2 三大范式

10.2.2.1 第一范式（1FN）

原子性:保证每一列不可再分；

10.2.2.2 第二范式（2FN）

前提：满足第一范式；

非主键必须完全依赖于主键，不能是部分依赖主键（完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分）；

10.2.2.3 第三范式（3FN）

前提：满足第一范式 和 第二范式；

第三范式，需要确保数据表中的每一列数据和主键直接相关，而不能间接相关；

10.2.3 规范性 和 性能的问题

关联查询的表不得超过三张表

• 考虑商业化的需求和目标，（成本，用户体验）数据库的性能更加重要；
• 在规范性能的问题的时候，需要适当的考虑一下规范性；
• 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）；
• 故意增加一些计算列（从大数据量降低为小数据量的查询，有时候会增加索引（索引需要索引树，占用内存））

11、JDBC（重点）

11.1 数据库驱动

我们安装好数据库之后，我们的应用程序也是不能直接使用数据库的，必须要通过相应的数据库驱动程序，通过驱动程 序去和数据库打交道；

11.2 JDBC

SUN公司为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范（接口），称之为JDBC。

这套接口由数据库厂商去实现，这样，开发人员只需要学习jdbc接口，并通过jdbc加载具体的驱动，就 可以操作数据库。

• JDBC全称为：Java Data Base Connectivity（java数据库连接），它主要由接口组成。
• 组成JDBC的２个包：java.sql、javax.sql
• 开发JDBC应用需要以上2个包的支持外，还需要导入相应JDBC的数据库实现(即数据库驱动包—mysql-connector-java-5.1.47.jar 具体版本根据实际需求)。

mysql-connector-java-5.1.47.jar。

11.3 第一个JDCBC程序

11.3.1 创建测试数据库表

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,'zhansan','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');

11.3.2 步骤

1. 新建一个Java项目；

2. 添加lib目录（放jar包）；

   jar包链接

3. 编写测试代码；

package com.huayu.lesson01;

import java.sql.*;

//第一个JDBC数据库
public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.添加驱动
        Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动

        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/kuangshen_school?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8";
        String username = "root";
        String password = "root";

        //3.连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行SQL的对象
        Statement statement = connection.createStatement();

        //5.执行SQL的对象 去 执行SQL  可能存在加过，查看结果
        String sql = "select * from users";

        ResultSet resultSet = statement.executeQuery(sql); //返回的结果集,结果集中封装了我们查询的所有结果

        System.out.println("id\tname\tpassword\temail\t\t\t\tbirthday");
        while(resultSet.next()){
            //字段名查询
            System.out.print(resultSet.getObject("id"));
            System.out.print("\t"+resultSet.getObject("name"));
            System.out.print("\t"+resultSet.getObject("password"));
            System.out.print("\t\t"+resultSet.getObject("email"));
            System.out.println("\t\t"+resultSet.getObject("birthday"));

            //小标查询 下表从1开始
//            System.out.print(resultSet.getObject(1));
//            System.out.print("\t"+resultSet.getObject(2));
//            System.out.print("\t"+resultSet.getObject(3));
//            System.out.print("\t\t"+resultSet.getObject(4));
//            System.out.println("\t\t"+resultSet.getObject(5));
        }

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

}

运行结果

id	name	password	email				birthday
1	zhansan	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

11.3.3 步骤总结

1. 加载驱动 Class.forName

    Class.forName("com.mysql.jdbc.Driver");
   

2. 连接数据库 DriverManager

   Connection connection = DriverManager.getConnection(url, username, password);
   

3. 获得执行sql的对象 Statement PreparedStatement

   Statement statement = connection.createStatement();
   //PreparedStatement preparedStatement = connection.prepareStatement();
   

4. 获得返回结果集 ResultSet

   ResultSet resultSet = statement.executeQuery(sql);

5. 释放连接

resultSet.close();
statement.close();
//preparedStatement.close()
connection.close();

11.4 JDBC中对象解释

11.4.1 DriverManager

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

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

11.4.2 URL

 String url = "jdbc:mysql://localhost:3306/kuangshen_school?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8";

//(协议)://主机地址:端口号/数据库名?参数1&参数2&参数3
// mysql 默认端口3306
jdbc:mysql://localhost:3306/sid
//如果连接的是本地的Mysql数据库，并且连接使用的端口是3306，那么的url地址可以简写为
jdbc:mysql:///sid
//oralce 默认端口1521
jdbc:oracle:thin:@localhost:1521:sid
//SqlServer写法 默认端口 1433
SqlServer写法：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid

11.4.3 Statement执行SQ的对象

String sql = "select * from users"; //编写sql

statement.executeQuery(); //查询操作返回ResultSet
statement.execute();      //执行任何SQL
statement.executeUpdate();//跟新，插入，删除，返回受影响的行数

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

获得指定类型的数据

//在不知道列类型的清空下使用
resultSet.getObject();
//如果知道列的类型就使用指定类型
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();

遍历，指针

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

11.4.5 释放资源

resultSet.close();
statement.close();
//preparedStatement.close()
connection.close();

11.5 Statement对象CRUD

11.5.1db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/kuangshen_school?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8
username=root
password=root

11.5.2 JdbcUtil工具类

package com.huayu.lesson02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtil {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static{
        try {
            InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.驱动只加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(url, username, password);
    }

    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }

    }


}

11.5.3 CRUD操作-create

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

package com.huayu.lesson02.utils;

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

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

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

        try {
            conn = JdbcUtil.getConnection(); //获取数据库连接
            st = conn.createStatement();
            String sql = "insert into users (id,`name`,`password`,email,birthday)" +
                    "values (5,'huauyu','123456','123456@qq.com','2000-01-01');";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,st,rs);
        }
    }
}

11.5.4 CRUD操作-delete

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

//其他代码一样
String sql = "delete from users where id = 4";

int i = st.executeUpdate(sql);
if(i>0){
    System.out.println("删除成功！");
}

11.5.5 CRUD操作-update

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

//其他代码一样
 String sql = "update users set `name` = 'huayu',email = '12345@qq.com' where id = 2";

int i = st.executeUpdate(sql);
if(i>0){
    System.out.println("修改成功！");
}

11.5.6 CRUD操作- read

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

//其他代码一样
 String sql = "select * from users where id = 1";

rs = st.executeQuery(sql);
while (rs.next()){
    System.out.println(rs.getString("name"));
}

11.6 Statement SQL 注入的问题

sql存在漏洞，sql存在拼接sql的漏洞；

package com.huayu.lesson02.utils;

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

public class SQL注入 {
    public static void main(String[] args) {

        //正常登录
//        login("huayu","123456");

        //sql注入
        login(" 'or ' 1=1 "," 'or ' 1=1 ");

    }

    //登录业务
    public static  void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtil.getConnection();
            st = conn.createStatement();

            //select * from users where `name` = 'huayu' and `password` = '123456';
            //select * from users where `name` = '' or '1=1' and `password` = '123456';
            String sql = "select * from users where `name` = '"+ username +"' and `password` = '"+password+"'";

            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.print("name:"+rs.getString("name"));
                System.out.println("\tpassword:"+rs.getString("password"));
            }

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,st,rs);
        }
    }

}

11.7 PreparedStatement 对象 CRUD

11.7.1 PreparedStatement 对象简介

PreparedStatement 对象，可以防止SQL注入，效率更高；

PreparedStatement是Statement的子类；

PreparedStatement源码

// PreparedStatement 是 Statement 的子类
public interface PreparedStatement extends Statement

11.7.2 create

package com.huayu.lesson03;

import com.huayu.lesson02.utils.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

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

        try {
            conn = JdbcUtil.getConnection();

            //区别
            //使用问号？ 占用符代替参数
            String sql = "insert into users (id,`name`,`password`,email,birthday) values (?,?,?,?,?);";

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

            //手动设置参数

            pst.setInt(1,4);
            pst.setString(2,"xiaoyu");
            pst.setString(3,"123456");
            pst.setString(4,"123@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 sqlException) {
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,pst,null);
        }


    }
}

11.7.3 delete

//其他代码一样
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("删除成功！");
}

11.7.4 update

//其他代码一样
 String sql = "update users set `name` = ?  where id = ? ;";

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

//手动设置参数

pst.setString(1,"huayu");
pst.setInt(2,1);

int i = pst.executeUpdate();
if(i > 0){
    System.out.println("更新入成功！");
}

11.7.5 read

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"));
}

11.8 PrepareStatement 对象SQL 不会被注入

package com.huayu.lesson03;

import com.huayu.lesson02.utils.JdbcUtil;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {

        //正常登录
//        login("huayu","123456");

        //sql注入
        login(" 'or ' 1=1 "," 'or ' 1=1 ");

    }

    //登录业务
    public static  void login(String username,String password){
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtil.getConnection();

            //prepareStatement 防止SQL注入的本质，把传递进来的参数当作字符
            //假设其中存在转移字符，单引号 ' 会被直接转义
            String sql = "select * from users where name = ? and password = ?";
            pst = conn.prepareStatement(sql);

            //手动设置参数
            pst.setString(1,username);
            pst.setString(2,password);

            rs = pst.executeQuery();

            while (rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,pst,rs);
        }

    }
}

11.9 事务

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

11.9.1ACID原则

原子性：要么全部完成，要么都不完成；

一致性：总数不变；

隔离性：多个进程互不干扰；

持久性：一旦提交不可逆，持久化到数据库了；

隔离性的问题：

脏读：一个事务读取到了另外一个没有提交的事务；

不可重复度：在同一个事务内，重复读取表中的数据，表数据发生了改变；

虚度（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致；

11.9.2 代码实现

步骤：

1. 开启事务 conn.setAutoCommit(false); 关闭自动提交的同时会开启事务；
2. 一组事务执行完毕，提交事务
3. 可以在catch语句中显示的定义 回滚语句，但默认失败就会回滚

事务案例

package com.huayu.lesson04;

import com.huayu.lesson02.utils.JdbcUtil;

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

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

        try {
            conn = JdbcUtil.getConnection();

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

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

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

            //模拟错误
            //int x = 1/0;

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


        } catch (SQLException sqlException) {
            try {
                //如果失败，则默认回滚
                conn.rollback(); //如果失败则回滚事务

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,pst,rs);
        }
    }
}

11.10 数据库连接池

11.10.1 池化技术

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

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

池化技术：准备一些预先得资源，过来就连接预先准备好；

最小连接数，最大连接数，等待超时；

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

11.10.2 开源数据源实现

• DBCP
• C3P0
• Druid:阿里巴巴

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

10.10.3 DBCP

使用的jar包

Commons-dbcp-1.4.jar

Commons-pool-1.6.jar

• 在src目录下加入dbcp的配置文件：dbcpconfig.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED,REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

工具类

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtil_DBCP {

    private static DataSource dataSource = null;

    static{
        try {
            InputStream in = JdbcUtil_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式 -- >创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }

    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }

    }


}

测试案例

//只需要将获取连接换一下就好
conn = JdbcUtil_DBCP.getConnection();

11.10.4 C3P0

• C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。
• c3p0与dbcp区别：
  • dbcp没有自动回收空闲连接的功能；
  • c3p0有自动回收空闲连接功能。
• 要使用C3P0数据源，需要导入如下两个 jar 文件：
  • c3p0-0.9.5.5.jar
  • mchange-commons-java-0.2.19.jar
• 在src目录下加入C3P0的配置文件：c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    C3P0的缺省(默认)配置，
    如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource();”这样写
    就表示使用的是C3P0的缺省(默认)配置信息来创建数据源
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy? useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    <!--
    C3P0的命名配置，
    如果在代码中“ComboPooledDataSource ds = new
    ComboPooledDataSource("MySQL");”这样写就表示使用的是name是MySQL的配置
    信息来创建数据源
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

工具类

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class JdbcUtil_C3P0 {

    private static ComboPooledDataSource dataSource = null;

    static{
        try {
            //代码版配置
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setJdbcUrl();
//
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();

            //创建数据源 工厂模式 -- >创建
            dataSource = new ComboPooledDataSource("MySQL"); //配置文件写入

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }

    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }

    }

}

JAVA 折叠 复制 全屏

测试

//获取连接
conn = JdbcUtil_C3P0.getConnection();  //原来是自己实现的，现在用别人实现的

结论：无论使用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变；