MySQL完整版详解
本文包含SQL基础语法、函数、索引、sql编程、事务、事务原则、备份恢复，权限数据库设计、三大范式、JDBC、SQL注入、数据库连接池等所有知识都在这里了
一、数据库的操作
1.创建数据库
若在可视化软件上创建数据库，参考如下图
如果要创建的数据库不存在，则创建成功
create database if not exists westos;
2.删除数据库
drop database if exists westos;
3.使用数据库
use tmalldemodb;
//tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``
4.查看数据库
show databases;
5.清空当前命令行界面命令
clear;
6.创建表
-
auto_increment：自增
-
字符串使用单引号括起来
-
所有语句后面加逗号，英文的，最后一个不用加
-
primary key主键，一般一个表只有一个唯一的主键，且必须要有
-
engine=innodb：数据库表的引擎
mysql> 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;
创键成功之后的图
7.常用命令
(1)查看创建数据库的语句
show create database 数据库名;
运行结果图
(2)查看表的创建语句
show create table 表名;
(3)显示表的结构
desc 表名；
8.数据表的类型
(1)数据库的引擎
innodb //默认使用
myisam//早些年使用的
-
事务：假设有两个SQL都执行，要么都成功，要么都失败，如果有一个成功一个失败，它就提交不上去
-
数据行锁定：假设有两条SQL去查同一个表，他会把这个表先锁住，剩下的数据再查的时候就要排队等待
-
外键约束：一张表连接到另一张表
-
全文索引：比如在百度上根据你输入的关键词(在数据库叫字段)区搜索你想要的结果
| myisam | innodb | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 （支持表锁） | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为myisam的2倍 |
常规使用操作：
-
myisam 节约空间，速度较快
-
innodb安全性高，事务的处理，多表多用户操作
(2)在物理空间存在的位置
所有的数据库文件都存在data目录下 本质还是文件的存储！
9.修改和删除表的字段
(1)修改表名
alter table 旧表名 rename as 新表名;alter table teacher rename as teacher1;
(2)增加表的字段
alter table 表名 add 字段名 列属性;alter table teacher1 add age int(11);
(3)修改表的字段（重命名，修改约束）
①修改约束modify(不能重命名)： alter table 表名 modify 字段名 新的列属性;alter table teacher1 modify age varchar(11);
②字段重命名（既可以给字段重命名，又可以修改约束）
alter table 表名 change 旧字段名 新字段名 列属性;
alter table teacher1 change age age1 int(11);
(4)删除表的字段
alter table 表名 drop 字段名;alter table teacher1 drop age1;
(5)删除表
如果要删除的表存在，则删除 drop table if exists 表名;drop table if exists teacher1;
所有的创建和删除操作都尽量加上判断，以免报错
二、列的数据类型详解
1.数值
-
tinyint 十分小的数据 1个字节
-
smallint 较小的数据 2个字节
-
int 标准的整数 4个字节 常用
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节
-
decimal 字符串形式的浮点数
2.字符串
-
char 字符串固定的大小 0-255
-
varchar 可变字符串 0-65535 常用
-
tinytext 微型文本 2^8-1
-
text 保存大文本 2^16-1
3.时间和日期
-
date 日期格式：YYYY-MM-DD
-
time 时间格式：HH:mm:ss
-
datetime 日期格式：YYYY-MM-DD HH:mm:ss 常用
-
timestamp 时间戳，从1970.1.1至现在的毫秒数 常用
-
year 年份表示
4.null
-
没有值，未知
-
注意，不要使用NULL进行运算，结果为NULL
三、数据库的字段属性（重点）
1.unsigned
-
无符号的整数
-
不能声明为负数
2.zerofill
-
0填充的
-
假设你现在要写一个长度为10的int类型，但是你只写了个1，则他会用自动给你在1前面填充9个零
3.自增
-
通常理解为自增，自动在上一条记录的基础上+1（默认）
-
通常用来设计唯一的主键~index,必须是整数类型
-
可以自定义设置主键自增的起始值和步长
4.非空
-
假设设置为not null ，如果不给它赋值，就会报错！
-
Null,如果不填写值，默认就是null！
5.默认
-
设置默认的值
-
sex,默认值为男，如果不指定该列的值，则会有默认的值！
-
设置默认的值
四、MySQL数据管理
1.外键(了解即可)
删除有外键关系的表的时候，必须要先删除字表，才能删除父表
2.DML语言（全部背住）
-
insert
-
update
-
delete
3.添加
insert into 表名(字段名1，字段名2，字段名3，....) value(值1，值2，值3，....) ==注意：一般写插入语句，我们一定要数据和字段一一对应！==(1)插入一行数据
insert into grade(gradename) value('大三');
(2)插入多行数据
==注意：给一个字段添加多行值时，每个值都用括号括起来，且中间用逗号隔开。==insert grade(gradename) value('大二'),('大一');
insert into student(name,pwd,sex) values('张三','aaaa','男'),('李四','vvvv','女');
4.修改
(1)修改一条数据
格式：update 表名 set colnum_name = value where 条件 下面这行代码的意思是将student表中id=2的name值设置为TWQupdate student set name='TWQ' where id =2;
(2)修改多条数据
条件：where子句，运算符id等于某个值，大于某个值，或在某个区间内修改| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或！= | 不等于 | 5<>6 | true |
| < | 小于 | 5<6 | true |
| > | 大于 | 5>6 | false |
| <= | 小于等于 | 5<=6 | true |
| >= | 大于等于 | 5>=6 | false |
| between a and b | 在a到b这个闭包区间内 | [a,b] | true |
| and | 我和你 && | 5>1 and 1>2 | false |
| or | 我或你 | 5>1 or 1>2 | true |
5.删除
(1)delete命令
语法：delete from 表名 where 条件;delete from student where id =1;
(2)truncate命令
作用：完全清空一个数据库表，表的结构和索引不会变truncate table student;
(3)truncate和delete命令区别
-
相同点：都能删除数据，都不会删除表结构
-
不同：①truncate重新设置自增列，计数器会归零。②truncate不会影响事务
使用delete删除表中所有数据，不会影响自增的值
拓展（了解即可）关于delete删除的问题：删除后重启数据库 -
若该表使用的引擎为innodb，重启数据库后，自增列会从1开始（存在内存当中断电即失）
-
若采用的引擎是myisam 重启数据库后，会继续从上一个自增变量开始（存储在文件中，不会丢失）
delete from grade;
使用truncate删除表中所有数据，自增变量会归零
truncate table grade;
五、DQL查询数据（重点中的重点）
select语法
5.1指定查询字段
(1)查询某个表中全部的数据
如查询student表中所有的数据select * from student;
(2)查询某个表中指定字段
①查询student表中name和pwd字段的值select name,pwd from student;
②给字段或表名起别名(as)
select name as 姓名,pwd as 密码 from student;
(3)函数concat(a,b)
select concat('姓名:',name) as 新名字 from student;
(4)去重distinct
①查询有哪些同学参加了考试(可能有一个同学考了多个学科，造成的数据重复，需要去重)select distinct Student_id from result;
(5)查询系统版本
select version();
(6)用来计算
select 100*3-1 as 计算结果;
(7)查看学生成绩+1分之后的结果
select Student_id,StudentResult+1 as 加一分后 from result;
5.2 where条件子句
作用：检索数据中符合条件的值 搜索的条件由一个或者多个表达式组成！结果为布尔值(1)逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and | a and b | 逻辑与 |
| or | a or b | 逻辑或 |
| Not | not a | 逻辑非 |
(2)where的运用
①查询23(学科编号)这个学科成绩在95到100之间的学生//方式一：
select student_id as 学生编号,studentresult as 分数 from result
where studentresult>=95 and studentresult<=100 and subject_id=23;
//方式二：
select student_id as 学生编号,studentresult as 分数 from result
where studentresult between 95 and 100 and subject_id=23;
(3)模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果操作符为null,结果为真 |
| is not null | a is not nul | 如果操作符为不为null,结果为真 |
| between | a between b and c | 若a在b和c之间，则结果为真 |
| like | a like b | sql匹配，如果匹配b,则结果为真 |
| in | a in(a1,a2,a3,.....) | 假设a在a1,或者a2....其中的一个值中，结果为真 |
①查询姓唐的同学
like 结合 %(代表0到任意个字符) _（一个字符）
select name,birthday from student
where name like '唐%';
②查询姓唐的同学,并且名字后面仅有一个字的
select name,birthday from student
where name like '唐_';
③查询姓唐的同学,并且名字后面仅有个字的
select name,birthday from student
where name like '唐__';
④查询名字中含有嘉字的同学 %嘉%
select name,birthday from student
where name like '%嘉%';
⑤查询id为3,4,5号的学生
select name,birthday from student
where id in (3,4,5);
⑥查询家住在宁波和上海的学生
select name,address from student
where address in ('宁波','上海');
⑦查询邮箱为空的学生
select name,email from student
where email is null;
⑧查询邮箱不为空的同学
select name,email from student
where email is not null;
5.3联表查询
(1)join对比
①查询参加了考试的同学（学号，姓名，科目编号，分数）使用inner join来查询
思路：
-
分析需求，分析查询的字段来自哪些表，（连接查询）
-
确定使用哪种连接查询？ 7种
-
确定交叉点（这两个表中那个数据是相同的）判断条件：学生表中的student_id = 成绩表的student_id
-- 这里给student表和result表起别名，就是因为这两张表中都有student_id这个属性，为了不扯皮，所以起别名并写明用哪个表的id
select s.student_id,name,subject_id,studentresult
from student as s
inner join result as r
where s.student_id=r.student_id;
②使用right join来实现上述操作
join（连接的表） on(判断的条件) 连接查询
where 等值查询
select s.student_id,name,subject_id,studentresult
from student s
right join result r
on s.student_id=r.student_id;
③使用left join操作上述问题
select s.student_id,name,subject_id,studentresult
from student s
left join result r
on s.student_id=r.student_id;
由结果可知left join会将没有参与考试的学生也查了出来
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行（hang） |
| left join | 会从左表中返回所有的值 ，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值 ，即使左表中没有匹配 |
| ④查询缺考的同学 |
select s.student_id,name,subject_id,studentresult
-- 起别名的时候as也可以省略
from student s
left join result r
on s.student_id=r.student_id
where studentresult is null;
⑤查询参加了考试的同学的信息：学号，姓名，科目名，分数
思路：
-
分析需求，分析查询的字段来自哪些表，student，result，subject（连接查询）
-
确定使用哪种连接查询？ 7种
-
确定交叉点（这两个表中那个数据是相同的）判断条件：学生表中的student_id = 成绩表的student_id ，成绩表中的subject_id =科目表中的subject_id;
select s.student_id,name,subject_name,studetresult
from student s
right join result r
on s.student_id=r.student_id
inner join subject sub
on r.subject_id=sub.subject_id;
⑥查询参加了高等数学考试的同学的信息：学号，姓名，科目名，分数
select s.student_id,name,subject_name,studentresult
from student s
right join result r
on s.student_id=r.student_id
inner join subject sub
on r.subject_id=sub.subject_id
where subject_name ='高等数学';
总结：
-
我要查询哪些数据 select.....
-
从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
-
假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
(2)自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可 对于下面这张表进行拆分
父类（找pid等于1的为一棵树的最顶端）
| categoryid | categoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术技术 |
子类
| pid | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 3 | 6 | web |
| 5 | 7 | ps技术 |
| 2 | 8 | 办公信息 |
①操作：查询父类对应的子类关系
| 父栏目 | 子栏目 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web |
| 美术技术 | ps技术 |
采用联表查询得到上表只需要让父类的categoryid等于子类的pid即可
//将一张表看成一模一样的表
select a.categoryname as 父栏目,b.categoryname as 子栏目
from category as a,category as b
where a.categoryid = b.pid;
5.4分页(limit)和排序(order by)
(1)排序
①升序asc
查询参加了考试同学的信息（姓名，考试科目，成绩）并将成绩按从小到大排序select name,subject_name,studentresult
from student s
inner join result r
on s.student_id=r.student_id
inner join subject sub
on r.subject_id = sub.subject_id
order by studentresult asc;
②降序desc
只需在上述例子中将asc改为desc即可(2)分页
语法：==limit(查询起始下标，pagesize)==第一页： limit 0,5 起始查询下标:(1-1)5
第二页： limit 5,5 起始查询下标:(2-1)5
第三页： limit 10,5 起始查询下标:(3-1)5
第N页： limit (n-1)5,5 起始查询下标:(n-1)*pagesize
pagesize:页面大小
（n-1)*pagesize:起始查询下标
n:当前页
数据总数/页面大小 = 总页数
5.5子查询
①查询高等数学 的所有考试结果（学生id,科目编号，成绩）降序排列 方式一：使用连接查询select student_id,r.subject_id,studentresult
from result r
inner join subject sub
on r.subject_id=sub.subject_id
where subject_name='高等数学'
order by studentresult desc;
方式二：使用子查询
select student_id,subject_id,studentresult
from result
where subject_id =(
select subject_id
from subject
where subject_name='高等数学'
);
六、MySQL函数
1.常用函数
①数学运算
-- 绝对值
select abs(-8)
-- 向上取整
select ceiling(9.4)
-- 向下取整
select floor(9.4)
-- 返回一个0-1之间的随机数
select rand()
-- 判断一个数的符号 0-0 返回-1，正数返回1
select sign（10）
②字符串函数
-- 字符串长度
select char length('发来的法计算')
-- 拼接字符串
select concat('我','adf','asd')
-- 查询，从某个位置开始替换某个长度
select insert('我爱变成helloworld',1,2,'超级热爱')
-- 小写字母
select lower('KjfdsSJK')
-- 大写字母
select upper('KjfdsSJK')
-- 返回第一次出现的子串的索引
select instr('twq123','3')
-- 替换出现的指定字符串
select replace('坚持就是胜利','坚持','努力')
-- 返回指定的字符串(源字符串，截取的位置，截取的长度)
select substr('坚持就是胜利',3,2)
-- 反转
select reverse('清晨我上马')
运用
将姓唐的同学姓式改为姓汤
select replace(name,'唐','汤') from student
where name like '唐%';
③时间和日期函数
-- 获取当前的日期
select current date()
-- 获取当前日期
select curdate()
-- 获取当前的时间
select now()
-- 获取本地时间
select localtime()
-- 获取系统时间
select system()
select year(now())
select month(now())
select day(now())
select hour(now())
select minute(now())
select second(now())
--系统
select system_usr()
select user()
select version()
2.聚合函数(常用)
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| .... |
查询student表中一共有多少人
-- 都能够统计，表中的数据(想查询一个表中有多少个记录，就是用这个count())
select count(name) from student -- count(字段)，会忽略所有的null值
select count(*) from student; -- count(*) 不会忽略null值 ,本质在计算行数
select count(1) from result; -- 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
-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
select subject_name, avg(studentresult),max(studentresult),min(studentresult)
from result r
inner join subject sub
on r.subject_id =sub.subject_id
group by r.subject_id -- 通过什么字段来分组
having 平均分>80; -- 分组之后再添加条件必须使用having，详见select语法的图
注意：执行以上命令可能会如果没有修改MySQL的mode将会报以下错误
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.sub.subject_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方式：
在Navicat或者sqlyog的命令中执行以下命令即可
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
5.3数据库级别的MD5加密(扩展)
-- 明文密码
insert into testmd5 values(1,'zhanshan','123456'),(2,'lisi','abcdefg'),(3,'wangwu','asdfgh');
-- 加密
update testmd5 set pwd=MD5(pwd) where id =1; -- 对单个数据进行加密
update testmd5 set pwd=MD5(pwd); -- 加密全部数据
-- 插入的数据的时候加密
insert into testmd5 values(4,'xiaming',MD5('567890'));
七、事务
7.1什么是事务
要么都成功，要么都失败就是下面这个转账的事务，必须要子啊两条SQL执行完成之后该事务才算结束，体现事物的原子性
①、SQL执行 A给B转账 A最开始有1000 B最开始有200 A给B转200
②、SQL执行 B收到A的钱 此时A有800 B有400
7.2事务原则
ACID 原则：原子性，一致性，隔离性，持久性(1)一致性
还是A给B转账这个事，事务完成后，符合逻辑运算也就是总钱数始终是1200(2)隔离性
所谓的独立性是指并发（多个用户同时操作）的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响 隔离所导致的一些问题：①脏读：
指一个事务读取另外一个事务未提交的数据.
②不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同.（这个不一定是错误，只是某些场合不对）.
③虚读（幻读）：
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致.
(3)持久性
事务因提交不可逆转，被持久化到数据库中
（4）事务的实际操作
-- MySQL是默认开启事务自动提交的
set autocommit =0 -- 关闭
set autocommit =1; --开启（默认的）
-- 手动处理事务
set autocommit =0 -- 关闭自动提交
-- 事务开启
start transaction -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
-- 提交：持久化（成功）
commit
-- 回滚：回到原来的样子（失败）
rollback
-- 事务结束
set autocommit=1 -- 开启自动提交
（5）模拟事务场景
-- 创建一个转账的数据库
create database shop character set utf8 collate utf8_general_ci;
-- 在此数据库中创建表
create table account(
id int(3) not null auto_increment,
name varchar(20) 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);
--模拟转账，事务
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; -- 开启自动提交
八、索引
索引的定义：索引是帮助MySQL高效获取数据的数据结构，
提取句子主干，就可以得到索引的本质：索引就是数据结构
8.1索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个- 主键索引(primary key)
- 唯一的标识，主键不可重复，只能有一个列作为主键
*唯一索引 (unique key)
-
避免重复列出现，唯一索引可以重复，多个列都可以标识唯一索引
-
常规索引（key / index）
- 默认的，index，key关键字来设置
-
全文索引（fulltext）
- 快速定位数据
(1)索引的使用
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
show index from student; -- 显示所有的索引信息
-- 增加一个全文索引(索引名) 列名
alter table school.student add fulltext index name (name);
-- explain 分析sql执行的状况
explain select * from student;-- 非全文索引
explain select * from student where match(name) against('唐');
给student表中的name添加字段之后结果图
8.2测试索引
-- 插入100万条数据
-- 写函数之前必须要写，标志
delimiter $$
create function mock_data()
returns int
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),'1430953131@qq.com',concat('18',floor(rand()*((999999999-100000000)+100000000))),floor(rand()*2),uuid(),floor(rand()*100));
set i =i+1;
end while;
return i;
end;
执行上述函数可能会报以下错误
1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
解决方式：
临时解决方法（重启MySQL后失效）：
在命令行界面执行以下命令
set global log_bin_trust_function_creators=TRUE;
永久解决方法：
在配置文件/etc/my.cnf的[mysqld]配置log_bin_trust_function_creators=1
8.3索引的原则
-
索引不是越多越好
-
不要对进程变动数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上
(1)索引的数据结构
Hash类型的索引 Btree：innodb的默认数据结构九、权限管理和数据库备份
1.SQL命令操作
用户表：mysql.user 本质：读这张表进行增删改查-- 创建用户create user 用户名 identified by '密码';
create user jianduan identified by '123456';
--修改密码（修改当前用户密码）
set password = password('123456');
--修改指定用户密码
setpassword for jianduan = password('111111');
--给用户重命名rename user 原来名字 to 新名字;
rename user jianduan to jianduan2;
--用户授权（最高没有root高）all privileges on *.*(库点表，*表示所有的库和表) to 用户名;
-- all privileges除了给别人授权，其它都能干
grant all privileges on *.* to jianduan2;
-- 查询权限
show grants for jianduan2; -- 查看指定用户的权限
show grants for root@localhost;
--撤销权限 revoke 哪些权限 ，在哪个库撤销，给谁撤销
revoke all privileges on *.* from jianduan2;
用户创建成功之后的图
2.MySQL备份
MySQL备份的方式：-
直接拷贝物理文件
-
在sqlyog或Navicat这种可视化工具手动导出
- （以Navicat为例）在想要导出的表或者库中，右键，选择转储SQL文件，然后选择结构+数据
-
使用命令行导出 mysqldump 命令行界面(windows的cmd或Mac的终端)使用
-- mysqldump -h主机 -u用户名 -p密码 数据库名 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot123456 school student >/Users/twq/Downloads/a.sql
-- mysqldump -h主机 -u用户名 -p密码 数据库名 表1 表2 表3 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot123456 school student result >/Users/twq/Downloads/b.sql
-- mysqldump -h主机 -u用户名 -p密码 数据库名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot123456 school >/Users/twq/Downloads/c.sql
--将表导入指定的数据库（在终端或则cmd界面需要先登录(mysql -u用户名 -p密码)然后输入以下命令）
source /Users/twq/Downloads/b.sql
十、三大范式(了解)
10.1第一范式(1NF)
原子性：保证每一列不可再分10.2第二范式(2NF)
前提：满足第一范式 每张表只描述一件事情10.3第三范式(3NF)
前提：满足第一范式和第二范式10.4规范性和性能的问题
关联查询的表不得超过三张表-
考虑商业化的需求和目标，(成本，用户体验！)数据库的性能更加重要
-
在规范性能的问题的时候，需要适当的考虑以下规范性
-
故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
-
故意增加一些计算列(从大数据量降低为小数据量的查询：索引)
十一、JDBC
SUN公司为了简化开发人员的(对数据库的统一)操作，提供了一个Java操作数据库的规范，俗称JDBC。这些规范的实现由具体的厂商去做 对于开发人员来说11.1导入mysql-connector的jar包
(1)进入官网下载
https://downloads.mysql.com/archives/c-j/ 根据自己MySQL的版本选择对应版本的jar包
(2)拷贝jar包
(3)粘贴到IDEA的lib目录下
如果没有lib目录可新建一个(4)将lib目录添加为库
11.2第一个JDBC程序
package com.Tang.jdbc;
import java.sql.*;
public class jdbcTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
// Class.forName("com.mysql.jdbc.Driver");//固定写法，我现在的MySQL版本是8.0.28，在新版本里他是会自动加载驱动，因此没必要手动加载驱动
//2.用户信息和url
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&&characterEncoding=utf8&&useSSL=true";
String username="root";
String password="root123456";
//3.连接成功，数据库对象 connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL对象 statement执行SQL的对象
Statement statement=connection.createStatement();
//5.使用SQL的对象去执行SQL，可能存在结果，查看返回结果
String sql = "select * from student";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部的查询出来的结果，只有查询才会有结果集，更新(增加，删除，修改)没有返回值。
while(resultSet.next()){
//以下的字段名必须和数据库表中的字段名一一对应
System.out.println("student_id=" + resultSet.getObject("student_id"));
System.out.println("name=" + resultSet.getObject("name"));
System.out.println("pwd=" + resultSet.getObject("pwd"));
System.out.println("sex=" + resultSet.getObject("sex"));
System.out.println("birthday=" + resultSet.getObject("birthday"));
System.out.println("address=" + resultSet.getObject("address"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("=====================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
运行结果图
11.3JDBC中对象的解释
(1)URL
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&&characterEncoding=utf8&&useSSL=true";
//mysql默认端口号是3306
//jdbc:mysql://主机号:端口号/数据库名?参数1&参数2&参数3
//oralce默认端口号为1521
//jdbc:oralce: thin:@locakhost:1521:sid
(2)DriverManager
Connection connection = DriverManager.getConnection(url, username, password);
//connection :代表数据库
//数据库设置自动提交
connection.setAutoCommit();
//事务提交
connection.commit();
//事务回滚
connection.rollback();
(3)Statement和PrepareStatement
首先这俩都是执行SQL的对象的String sql = "select * from student";//编写SQL语句
statement.executeQuery();//查询操作返回ResultSet(结果集)
statement.execute();//执行任何SQL
statement.executeUpdate();//更新、插入、删除。都是用这个，返回一个受影响的行数
statement.executeBatch();//可执行多个SQL语句
(4)ResultSet
查询的结果集：封装了所有的查询结果resultSet.getobject();//在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
//遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(2);//移动到指定行
11.4 statement对象详解
JDBC中statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查的语句即可
statement 对象的executeUpdate方法，用于向数据库发送增、删、改的SQL语句，executeupdate执行完后，将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)
statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象
代码实现 使用executeUpdate(String sql)方法完成数据添加操作，实例操作：①提取工具类
package com.Tang.jdbc;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class jdbcutils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static{
try{
InputStream in = jdbcutils.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(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
提取连接数据库的db.properties代码
//这里不要随便加空格
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&&useSSL=true
username=root
password=root123456
②测试更新(插入,删除,修改)
package com.Tang.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertTest {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rt = null;
try {
conn = jdbcutils.getConnection();//获取数据库的连接
st = conn.createStatement();//获得SQL的执行对象
//测试插入
String sql = "insert into student(name,pwd,sex)"+
"values('twq121','cccccc','男')";
//测试删除
String sql = "delete from student where name='twq121'";
//测试修改
String sql = "update student set name='twqaa',pwd='123qwe' where student_id=1";
int i = st.executeUpdate(sql);//除了查询(select用executeQuery，增删改都用update)
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
运行结果图
③SQL注入问题
SQL存在漏洞，会被攻击导致数据泄露 SQL会被拼接or
package com.Tang.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlInjection {
public static void main(String[] args) {
//login("唐昊","123456");正常的查询只会输出name等于唐昊，密码等于123456的数据
login(" 'or '1=1"," ' or '1=1");//会将表中所有数据都输出所有数据都输出，传入的username的意思见下方注释
}
public static void login(String username,String password){
Connection conn = null;
Statement st = null;
try {
conn = jdbcutils.getConnection();
st = conn.createStatement();//为了防止SQL注入的问题可以使用PrepareStatement
// String sql = "select * from student where name='唐昊' and pwd = '123456'";
// String sql = "select * from student where name=' 'or '1=1' and pwd = '123456'";//等价于判断name=null,或者1=1是否为真的判断
//这里username 和 password前后有 + 只是为了使用函数传进来的参数
String sql = "select * from student where name='"+username+"' and pwd = '"+password+"'";
ResultSet resultSet = st.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getObject("name"));
System.out.println(resultSet.getObject("pwd"));
System.out.println("=============");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
运行结果图
11.5 PreparedStatement对象详解
PreparedStatement可以防止SQL注入，效率更好(1)更新操作(增删改)
package com.Tang.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class preparestatementTest {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = jdbcutils.getConnection();
//1.测试插入数据
//使用?占位符来代表参数
String sql1 = "insert into student(name,pwd,sex,birthday,address) values (?,?,?,?,?)";
st=conn.prepareStatement(sql1);//预编译SQL，先写sql但不执行
//手动给参数赋值
st.setString(1,"tang123");//给第一个问号赋值
st.setString(2,"qwerty");//给第二个问号赋值
st.setString(3,"女");//给第三个问号赋值
//注意点：
//sql.Date 是数据库的
//Date.getTime 是当前的时间属于Java
st.setDate(4,new java.sql.Date(new Date().getTime()) );//给第四个问号赋值
st.setString(5,"北京");//给第五个问号赋值
//执行
int i = st.executeUpdate();
if(i > 0){
System.out.println("插入成功");
}
//2.测试删除数据
String sql2 = "delete from student where student_id=?";
st=conn.prepareStatement(sql2);//预编译SQL，先写sql但不执行
//手动给参数赋值
st.setInt(1,8);//给问号赋值，将student_id=5的数据删除
//执行
int i1 = st.executeUpdate();
if(i1 > 0){
System.out.println("删除成功");
}
//3.测试修改数据
String sql3 = "update student set name=? where student_id=?;";
st=conn.prepareStatement(sql3);//预编译SQL，先写sql但不执行
//手动给参数赋值
st.setString(1,"唐三");
st.setInt(2,1);
//执行
int i3 = st.executeUpdate();
if(i3 > 0){
System.out.println("修改成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
插入成功图
删除成功图
修改成功图
(2)查询操作
package com.Tang.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class preparestatementTest {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = jdbcutils.getConnection();
//测试查询
String sql4 = "select * from student where name=?";
st = conn.prepareStatement(sql4);
st.setString(1,"唐晨");
rs = st.executeQuery();
while(rs.next()){
System.out.println(rs.getObject("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
运行结果图
(3)解决SQL注入问题
package com.Tang.jdbc;
import java.sql.*;
public class SqlInjection {
public static void main(String[] args) {
login("唐晨","888888");//正常的查询只会输出name等于唐昊，密码等于123456的数据
//login("' ' or 1=1"," '123456");//这里就不会查询到数据
}
public static void login(String username,String password){
Connection conn = null;
PreparedStatement st = null;
try {
conn = jdbcutils.getConnection();
//PreparedStatement防止SQL注入的本质，把传递进来的参数当做字符
//假设其中存在转义字符，比如说 ' 就会被直接转义
String sql = "select * from student where name=? and pwd =?";
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
ResultSet resultSet = st.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getObject("name"));
System.out.println(resultSet.getObject("pwd"));
System.out.println("=============");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
运行结果图
11.6 IDEA连接数据库
连接的前提是将mysql-connect的jar包导入才行
连接成功之后可以选择数据库
更新数据
11.9数据库连接池
池化技术：准备一些预先的资源，过来就连接预先准备好的
编写连接池，实现一个接口DataSource
(1)开源数据源实现
DBCP C3P0 Druid:阿里巴巴 使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了①DBCP
需要用到jar包 commons-pool-1.6.jar点我下载commons-dbcp-1.4.jar点我下载 dbcpconfig.properties里的代码
#连接设置，这里的名字是dbcp数据源中定义好的
# driverClassName=com.mysql.jdbc.Driver可以省略不写，新驱动是自动加载的
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&&useSSL=true
username=root
password=root123456
jdbcutils_DBCP代码
package com.Tang.jdbc.DBCP;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import javax.xml.crypto.Data;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class jdbcutils_DBCP {
private static DataSource dataSource = null;
static{
try{
InputStream in = jdbcutils_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(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
使用DBCP实现数据的删除测试
package com.Tang.jdbc.DBCP;
import com.Tang.jdbc.jdbcutils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class dbcpTest {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rt = null;
try {
conn = jdbcutils_DBCP.getConnection();//获取数据库的连接
st = conn.createStatement();//获得SQL的执行对象
// String sql = "insert into student(name,pwd,sex)"+
// "values('twq121','cccccc','男')";
String sql = "delete from student where student_id=9";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
②C3P0
需要的jar包 新打开界面打开此链接c3p0-0.9.2.1.jar点我下载
mchange-commons-java-0.2.3.4.jar.zip点我下载
c3p0-config.xml的配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<named-config name="MySQL">
<!--指定连接数据源的基本属性-->
<property name="user">root</property>
<property name="password">root123456</property>
<!-- <property name="driverClass">com.mysql.jdbc.Driver</property>-->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/school?useUnicode=true;characterEncoding=utf8;useSSL=true</property>
<!--初始化数据库连接池时连接的数量-->
<property name="initialPoolSize">5</property>
<!--若数据库中连接数不足时，一次向数据库服务器申请多少个连接-->
<property name="acquireIncremen">5</property>
<!--数据库连接池中最大的数据库连接数-->
<property name="maxPoolSize">20</property>
<!--数据库连接池中最小的数据库连接数（空连对象个数为这个数时就去申请更多连接对象）-->
<property name="minPoolSize">2</property>
</named-config>
</c3p0-config>
jdbcutils_c3p0代码
package com.Tang.jdbc.C3P0;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class jdbcutils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static{
try{
//创建数据源 工厂模式
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(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
c3p0增加数据测试
package com.Tang.jdbc.C3P0;
import com.Tang.jdbc.DBCP.jdbcutils_DBCP;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class C3P0Test {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rt = null;
try {
conn = jdbcutils_C3P0.getConnection();//获取数据库的连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "insert into student(name,pwd,sex)"+
"values('twq121','cccccc','男')";
// String sql = "delete from student where student_id=10";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
