一、数据库的操作
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
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 95 100 and subject_id="23;" 方式二： select student_id as 学生编号,studentresult 分数 from result where studentresult between < code></=100>
(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
执行上述函数可能会报以下错误
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这种可视化工具手动导出
- 使用命令行导出 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包 新打开界面打开此链接
mchange-commons-java-0.2.3.4.jar.zip点我下载
c3p0-config.xml的配置文件
root
root123456
jdbc:mysql://localhost:3306/school?useUnicode=true;characterEncoding=utf8;useSSL=true
5
5
20
2
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();
}
}
}
Original: https://www.cnblogs.com/twq46/p/16485022.html
Author: 剑断青丝ii
Title: MySQL完整版详解
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/609850/
转载文章受原作者版权保护。转载请注明原作者出处！