MySQL完整版详解

一、数据库的操作

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值设置为TWQ

update 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();
        }
    }
}