注意事项
有道云笔记8-22-一
1.数据库简介
1.1DBMS
DBMS是数据库管理系统(database management system)，如mysql(开源)、oracla(付费)、sql server(微软)、pgSQL…
1.2DB
DB是数据库(database)，database中有多个表，如下就是四个数据库
1.3关系型数据库
mysql、oracle…都是关系型数据库
1.4非关系型数据库(no sql)
存k-v对，比如redis就是存k-v对
1.5mysql
开源的关系型数据库，已经被oracle收购了，可以支持千万条数据，mariaDB社区版
2.常用命令
2.1登录数据库
mysql -uroot -p，用户名是root，密码是root
注：数据库中的数据存在硬盘中，我们想操作这些数据，如插入一条，查询…等但数据在磁盘的文件中，我们没法直接操作，我们可以让mysql这个数据库管理系统，帮我们操作，但是mysql要求我们登录后才能操作，登录后，需要使用mysql提供的指令来具体操作
2.2显示所有数据库
show databases;
2.3查看数据库版本
select version();
2.4进入某个数据库
use information_schema，进入后会提示Database changed
2.5查看某库中所有的表
show tables;
2.6查看某个表中的数据
select * from 表名;
2.7创建数据库
create database 数据库名;
注：mysql的数据库命名用下划线，大小写要统一，windows不区分大小写
2.8删除数据库
drop database 数据库名;;如果不存在要删的这个数据库，我们还要删就会报错，安全的删除方式为drop database if exists bb;
2.9创建表
- 首先要进入想要创建表的数据库—
use 数据库名 - 建表—
create table test1(username varchar(10),age int);，其中test1是表名；username varchar(10)代表表中其中一列是字符串类型的username；age int代表表中另一列是int类型的age - 查询建表是否成功—
show tables
2.10查看表结构
desc 表名;
2.11 查看建表语句
show create table test1;
2.12删除表
- 删除表—
drop table test1; - 安全的删除表—
drop table if exists test1;
3.navicat
- 如果没有navicat，我们可以在dos窗口中，通过mysql提供的指令来操作数据库，但在小黑屏看着比较麻烦
- navicat是一个可视化工具，它不是数据库，它只是一个软件，我们可以通过这个软件，直观的看到数据库的内容，比小黑屏方便
- 使用navicat来连接数据库
4.主键
主键唯一不能为空，如学生的学号，表中通常都会有主键，通常管主键叫id
5.SQL语句
5.1数据库定义语言—DDL(Data Definition Language）
create、drop、use…
5.2数据查询语言—DQL(Data Query Language)
select * from.......
5.3数据操纵语言—DML(Data Manipulation Language)
insert、update、delete…
5.4数据控制语言—DCL(Data Control Language)
commit、rollback…
6.数据查询语言—DQL(Data Query Language)
6.1注释
-- 单行注释，有空格
#单行注释
/*
我是多行注释
......
*/
6.2Select
语法：select * from 表
-- 从menu表中查询所有数据，*代表所有
Select * from menu;
-- 通过列名，查询某些列的数据
select id,menu_name,price from menu;
-- 可以使用表名.字段的方式，这样会给提示信息
select menu.menu_name,menu.price,menu.id from menu;
-- 可以给表起别名,如下,m就是别名
select m.menu_name,m.url_image,m.menu_type_id from menu m;
-- 可以给列起别名，其中as可以省略，如果别名中有特殊字符，单引号不能省
select menu_name as '菜名',price as '菜价' from menu;
select menu_name '菜名',price '菜价' from menu;
-- 查询的字段不一定都是表中的字段，如下，'不好吃'和'真贵'都是我们自己加的字段
select m.menu_name as '菜名','不好吃',m.price as '原价','真贵',m.price+10 as '跳楼价','还敢涨价！' from menu m;
6.3过滤(where)
- 过滤关键字为where
- 不能用别名
select * from menu where price<10;
select * from menu where price>10 and price<50;
select * from menu where price<10 or price>50;
-- 查询id不是5的所有的菜的菜名和菜价
select menu.id,menu.menu_name as '菜名',menu.price as '菜价' from menu where id=5;
-- 查询学生表中生日为'2013-08-03'的学生的信息
select * from student where birthday = '2013-08-03';
-- 查询学生表中生日为'2013-08-03'之前出生的学生的信息
select * from student where birthday < '2013-08-03';
-- 查询id在5-10之间的菜
select * from menu where id > 5 and id < 10;
select * from menu where id between 5 and 10;
-- 查询id=2,5,18的菜，注意这里没有18这条记录，但不属于错误，只是没查到
select * from menu where id=2 or id=5 or id=18;
select * from menu where id in(2,5,18);
-- 模糊查询
-- 查询所有有"鱼"字的菜，%代表0-多个字符
select menu.menu_name from menu where menu_name like '%鱼%';
-- 查询第二个字是"鱼"字的菜
select menu.menu_name from menu where menu_name like '_鱼%';
-- not，取非，相当于Java中的!
-- 查询id不是2,5,8的菜们
select menu.id,menu_name from menu where id not in(2,5,8);
-- 查询学生中没录入性别的同学
-- mysql中null的判断不能用=或!=来处理，要用is
select * from student where Ssex is null;
-- 查询学生中已录入性别的同学
select * from student where Ssex is not null;
-- 查询价格在0-50之间有图片信息的菜
select menu.menu_name,menu.url_image,menu.price from menu where price between 20 and 40 and url_image is not null and url_image != '';
-- mysql允许我们在where后面写true和false，0相当于false，非0相当于true
select * from menu where 1=2;
6.4排序(order by)
- 排序关键字为order by
- 格式：排序 order by 字段名 排序规则:升序asc(默认)/降序desc
select * from menu order by price desc;
-- 菜价按升序排列，如果菜价相同按id降序排列,升序(asc)可以省略
select * from menu order by price asc,id desc;
-- 查找50元以下的菜并按上述方式排序
select * from menu where price < 50 order by price asc,id desc;
6.5限制(limit)
- 限制关键字为limit
- 常用于分页，如：百度搜索出来的页面分成好多页
-- 一个参数，下标从1开始
select * from menu limit 5;
-- 两个参数，下标从0开始算，从下标为3(第三条)开始截取3个
select * from menu limit 3,3;
select * from menu limit 0,4;
-- 一共上三道菜，挑最贵的来
select * from menu order by price desc limit 0,3;
select menu.id,menu.menu_name,menu.price from menu where price < 30 order by price desc limit 3;
-- api
-- mysql、oracle都有自己的API，但有些使用上可能不太相同
select 5*4;
6.6单行处理函数
6.6.1字符串相关的函数
select
UPPER("yeonjun"), -- 转大写
LOWER("YEONJUN"), -- 转小写
3+"4", -- 这种会得7，因为mysql做了隐式转换
CONCAT("i","loveu"), -- 用这种方式进行字符串的拼接
CONCAT(3,4),
SUBSTR("iloveyeonjun",2,4), -- 下标从1开始，取4个
SUBSTR("iloveyeonjun",2), -- 从2开始到最后
LENGTH("yeonjun"), -- 长度
LENGTH("你好啊"), -- 计算汉字时不准确，因为是按照按utf-8计算，每个汉字三个字节
CHAR_LENGTH("你好啊"); -- 使用CHAR_LENGTH计算汉字准确
-- 显示这样的菜单：辣子鸡(28元)，皮蛋豆腐(15元)
select CONCAT(menu_name,'(',price,'元)') as '菜品价格' from menu;
6.6.2Math相关的函数
select
ROUND(3.64), -- 四舍五入到整数
ROUND(3.5678,2), -- 保留两位小数
ROUND(35.456,-1), -- 40，-1相当于向前移一位，在十位四舍五入，所以相当于*10
ROUND(34.456,-1), -- 30
TRUNCATE(3.5678,2), -- TRUNCATE是截取的意思，没有四舍五入，要求两个参数，只写一个会报错
TRUNCATE(35.456,-1), -- 30
CEIL(3.1), -- 向上取整
FLOOR(3.9), -- 向下取整
ABS(-2), -- 绝对值
SIGN(9),SIGN(-7),SIGN(0), -- 判断正负
LEAST(4,6,1),GREATEST(4,6,1) -- LEAST最小值，GREATEST最大值
;
6.6.3日期相关的函数
select
NOW(), -- 获取系统时间
YEAR(NOW()), -- 获取指定日期对应的年
MONTH(NOW()), -- 获取指定日期对应的月
DAY(NOW()), -- 获取指定日期对应的日
date(NOW()), -- 获取指定日期的date部分
time(NOW()), -- 获取指定日期的time部分
YEAR('2022-8-15'),
CURDATE(), -- 获取当前时间的date值
CURTIME(), -- 获取当前时间的time值
date('2022-8-15 09:46:34'),
WEEKDAY(NOW())+1 as '星期', -- 获取当前时间的星期，从0开始
NOW()-INTERVAL 12 day, -- 12天之前
NOW()-INTERVAL 10 year+INTERVAL 12 day, -- 10年前的12天之后
adddate(NOW(),interval - 10 year), -- 这种也表示10年前
DATEDIFF(NOW(),'2001-08-15'), -- 两个参数相隔多少天
DATE_FORMAT(NOW(),'%Y-%m-%d'), -- 对日期格式化
STR_TO_DATE('20230815','%Y%m%d') -- 字符串转日期
-- 查询2019年出生的同学
select * from student where YEAR(birthday) = 2019;
6.6.4ifnull
- 格式：ifnull(expr1,expr2)
- 如果第一个参数的值是null，就显示第二个参数，否则显示第一个参数
select menu_name,url_image,IFNULL(url_image,'❤') from menu;
6.6.5if
- if(boolean表达式,值1,值2)
select id,menu_name as '菜品',url_image as '原图片信息',IF(url_image is null or url_image = '','❤',url_image) as '图片信息' from menu;
-- 用if实现：20元以下好吃不贵，辣子鸡 28 好吃不贵，米饭 2 经济实惠
select menu_name,price,IF(price < 20,'好吃不贵',if(price between 20 and 40,'经济实惠','萝卜开会')) as '介绍' from menu;
6.6.6case when
select
Ssex,
case Ssex
when '男' then 1
when '女' then 0
else '-1'
end as '性别符号'
from student;
-- case的另一种写法
select
Ssex,
case
when Ssex = '男' then 1
when Ssex = '女' then 0
else -1
end
from student;
select
price,
case
when price < 20 then '实惠'
when price < 40 then '好吃'
when price < 50 then '好贵'
else '天价'
end
from menu;
-- 按学生年龄分为90后，00后，10后，老年人，小崽子
select
birthday,
case
when year(birthday) < 1990 then '老年人'
when year(birthday) < 2000 then '90后'
when year(birthday) < 2010 then '00后'
else '小崽子'
end as '你是几世代'
from student;
6.6.7类型转换(convert)
-- convert可以进行类型转换，SIGNED不忽略符号，UNSIGNED忽略符号，a是原数据，b是要转换的类型
select convert(5.4,UNSIGNED integer)
select NOW(),CONVERT(NOW(),date)
-- CAST(a AS b)可以进行类型转换，a是原数据，b是要转换的类型
select CAST(NOW() as date)
6.7多行处理函数(统计函数、聚合函数)
多行处理函数会忽略为null的那一行记录，不进行统计，不会忽略空串
注：统计函数不能在where里出现
-- count(*)可查出共多少条记录
select count(*) from menu; -- 一共多少道菜
select COUNT(price) from menu; -- 会忽略price为null的那条记录
-- avg求平均值，没有要求一定是表上的某列，可能是处理过的数据，如AVG(price + 100)
select AVG(price) as '平均菜价',AVG(price + 100) from menu;
-- 如果不想忽略值为null的行，可以加ifnull来处理当前字段的值
select avg(IFNULL(price,0)) as '菜品' from menu;
-- sum求和
select SUM(price) from menu;
-- max、min，最大值、最小值
select MAX(price),min(price) from menu;
-- 查询有“鱼”字的菜一共有多少个，最高价最低价均价总价格是多少
select
COUNT(*) as '数量',
MAX(price) as '最高价',
min(price) as '最低价',
avg(price) as '均价',
SUM(price)as '总价格'
from menu where menu_name like '%鱼%';
6.7.1分组(group by)
- 分组，关键字为group by，分多少组，就有多少条记录
-- 按菜的类别分了个组并求出每组有多少道菜
select menu_type_id as '菜类',count(*) as '类的个数' from menu group by menu_type_id;
-- 求每类菜的平均价
select menu_type_id as '菜类',avg(IFNULL(price,0)) as '平均价格' from menu group by menu_type_id;
-- 查询每种菜卖了多少份，如皮蛋豆腐卖了两份
select menu_id,SUM(num) from orders group by menu_id;
-- 查询每笔付完款的订单的价格
select orders_id,SUM(sum_price) from orders where orders_status = 0 group by orders_id;
-- 按桌和订单分组
select table_num as '桌子编号',orders_id as '订单编号',sum(sum_price) as '营业额' from orders group by table_num,orders_id;
6.7.2对已形成的组进行过滤(having)
- having在分组之后将某些组过滤掉，having不能用带引号的别名
-- 查询订单价格大于100的订单
select
orders_id as 订单序号,
SUM(sum_price) as '总价格'
from orders
where orders_status = 0
group by orders_id
having 总价格 > 100;
6.7.3去重(distinct)
-- distinct去重，前面不能再有其他字段
select distinct menu_type_id from menu;
-- distinct后面可以有多个字段，当多个字段的值都相同时才可以去重
-- 如下，此时distinct按照menu_type_id,menu_name这两个字段来进行去重
select distinct menu_type_id,menu_name from menu;
-- 按照menu_type_id,price这两个字段进行去重
select distinct menu_type_id,price from menu;
-- distinct可以和聚合函数一起使用
select count(distinct menu_type_id) from menu;
select avg(distinct price) from menu;
select price from menu order by price;
select count(distinct price) from menu;
6.8多表查询
- 多个表进行关联会以笛卡尔积的方式生成一个新的表
- 多表关联通常需要关联条件，否则就会是笛卡尔积
select * from menu,menu_type where menu.menu_type_id = menu_type.id;
-- 可以给表起别名方便调用
select * from menu m,menu_type mt where m.menu_type_id = mt.id;
-- 显示菜名，菜价，菜的类别名称
select m.id,menu_name,price,menu_type_name from menu m,menu_type mt where m.menu_type_id = mt.id order by m.id;
-- 等值连接
-- 查询订单表中的信息，显示订单id，菜名，菜价，份数，菜的类别名称
select
o.orders_id as '订单编号',
m.menu_name as '菜品名称',
m.price as '菜品价格',
o.num as '菜品份数',
mt.menu_type_name as '菜品类别'
from menu m,menu_type mt,orders o
where m.id=o.menu_id and m.menu_type_id=mt.id;
-- 不等值连接
-- 通过查询score表和score_grade表显示成绩及对应的等级
select
s.student_id as '学号',
s.course_id as '课程id',
s.number as '成绩',
sg.grade as '成绩等级'
from score s,score_grade sg
where s.number between sg.lower_score and sg.upper_score;
-- 多表关联的另一种写法 join on，写全为inner join on
-- 格式：select * from 表1 join 表2 on 关联条件
select *
from menu m
join menu_type mt on m.menu_type_id = mt.id
join orders o on o.menu_id = m.id
where m.menu_name = '东北乱炖'
select *
from score s
join score_grade sg on s.number between sg.lower_score and sg.upper_score
-- 查询所有的菜并显示订单相关信息
-- 正常来说做连接是取交集，数据可能会丢失
-- 左连接，以左表为主，保证数据不丢，写全为：left outer join
select *
from menu m
left join orders o on m.id = o.menu_id
-- 右连接，以右表为主，写全为：right outer join
select *
from orders o
right join menu m on m.id = o.menu_id
-- 查找没被点过的菜
select *
from menu m
left join orders o on m.id = o.menu_id
where orders_id is null;
-- 查询有绩效的所有员工的姓名，部门名称
-- 注意，有时可能需要使用left join，避免数据丢失
select
concat(e.first_name,'.',e.last_name) as '员工姓名',
d.department_name as '部门名称'
from employees e
left join departments d on e.department_id = d.department_id
where e.commission_pct is not null;
6.9子查询
-- 子查询
-- 查找最便宜的那道菜的菜名和菜价
-- 先查找价格最便宜的菜的菜名和菜价
select menu_name,price from menu where price = 2
-- 根据上面的查出来的价格进行查询
select * from menu where price = (select min(price) from menu)
-- 查找和Bruce一个部门的员工
-- 先找到Bruce的部门id
select department_id from employees where first_name = 'Bruce'
-- 根据上面的部门id查找员工
select
concat(first_name,'.',last_name) as '名称',
department_id as '部门id'
from employees
where department_id =
(
select department_id from employees where first_name = 'Bruce'
)
and first_name != 'Bruce'
-- 找到最热销的菜，显示它的菜名
-- 第一种写法：子查询
-- 先找到最热销的菜的id
select menu_id as '菜品序号',sum(num) as '销售数量' from orders group by menu_id order by 销售数量 desc limit 1
-- 根据上面找到的id显示菜名
select menu_name
from menu
where id =
(
select menu_id as '菜品序号' from orders group by menu_id order by sum(num) desc limit 1
)
-- 第二种写法：多表关联
select
menu_name as '菜品名称',
price as '菜品单价',
menu_type_id as '菜品类别序号',
test1.菜品序号,
test1.销售数量,
test1.销售总额
from menu m
join (select menu_id as '菜品序号',sum(num) as '销售数量',sum(sum_price) as '销售总额' from orders group by menu_id order by 销售数量 desc limit 1) test1
on m.id = test1.菜品序号
-- 子循环也可以如下这样写，子循环写在列的位置时只能有一个值
select menu_name,(select count(price) from menu) from menu;
6.10自连接
-- 自连接
-- 查询员工的id和名字，以及该员工经理的id和名字
select
e.employee_id as '员工id',
e.first_name as '员工名称',
m.employee_id as '经理id',
m.first_name as '经理名称'
from employees e
left join employees m on e.manager_id = m.employee_id
6.11sql语句书写顺序和执行顺序
-- sql语句书写顺序
select ... from ... 表 where 条件 group by 分组 having 条件 order by ... limit ...
-- 执行顺序
from ..where..group by..having..select ..distinct ..order by..limit;
1.from 笛卡尔积-->虚表 VT1 select * from t1 join t2
2.on过滤 --> 将两个表中符合条件的再存一个新的虚表VT2 on 关联条件
3.看一下，如果有left、right，就把主表中有、子表中没有的那部分添进去，行成VT3
4.where过滤-->形成VT4
5.group by-->形成VT5
6.having对已行成的组进行过滤-->形成VT6
7.select 将不需要的字段去掉了-->形成VT7
8.distinct-->形成VT8
9.order by-->形成VT9
10.limit-->形成VT10
6.12union
- union用来整合两个查询的结果，会自动去重，如果不想去重，使用union all
- 查询的列数和顺序需要相同，顺序不同有时可能不会报错但结果是不正确的
select * from menu where id < 5
union all
select * from menu where id < 10
6.13是否存在(exists)
- 先查一条主表，在进入子查询对应查找
- 只要子查询中能返回结果，不管几条不管内容是什么，结果都是true
- 如果子查询中一条记录都没查出来，结果就是false，主表这条记录就不会输出
-- 卖过的菜
select * from menu m where exists(
select * from orders o where m.id = o.menu_id
)
-- 没卖过的菜
select * from menu m where not exists(
select * from orders o where m.id = o.menu_id
)
7.数据操纵语言—DML(Data Manipulation Language)
- 增删改
7.1插入数据(insert)
-- 插入数据到menu表中
-- insert into 表 (字段1,字段2,...)values(字段1对应的值)
insert into menu
(id,menu_name,url_image,price,menu_status,menu_type_id)
values
(null,'测试1','asda',33,null,8);
select * from menu
-- 如果所有字段都需要插入，可以省略第一个括号
-- 要求后面括号中的数据个数和顺序都要和建表语句一致
-- 使用时要考虑到顺序问题，如果写死了代码，万一表结构有变化这里就出错了
insert into menu
values
(null,'测试1','asda',33,null,8);
-- 可以选择性的插入数据
-- 但是不能为空的字段还是要填
-- 即使主键自增，但是选择性插入时，主键写几，插入的记录主键就为几，在这之后若插入数据时不写主键，那么主键随着最大的数进行自增
insert into menu(id,menu_name,price,menu_type_id)values(null,'测试4',90,2)
insert into menu_copy1 (id,menu_name,menu_type_id)values(6,'gfjs',8)
-- 主键是自增的，当进行选择性插入时，可以省略不写
insert into menu(menu_name,price,menu_type_id)values('测试2',90,2)
-- 批量插入
insert into menu
(menu_name,price,menu_type_id)
values
('测试3',90,2),('测试5',90,2),('测试6',90,2),('测试7',90,2)
-- 可以从其他表中查询数据，并插入本表
insert into menu_copy1 select * from menu where id in (8,9,7)
-- 主键不能重复不能为空
-- 如果想插入个别字段
insert into menu_copy1(id,menu_name,menu_type_id) select id,menu_name,menu_type_id from menu where id in (13)
insert into menu_copy1(menu_name,price,menu_type_id) select menu_name,price,menu_type_id from menu where id in (2,3,4)
7.2修改数据(update)
- 格式：update 表名 set 字段1 = 值1,字段2 = 值2,…
- 切记，通常做修改时都需要加where条件
- 它会将符合条件的记录都改了
update menu set price = 100 where id = 8
7.3删除数据(delete、truncate)
- 格式：delete from 表名 where…
delete from menu_copy1 where id = 5
-- 添加一条记录，发现id值不是从1开始
-- 使用delete进行删除操作，自增不会重新计数，自增的值是mysql自动帮我们维护的
insert into menu_copy1 (menu_name,menu_type_id)values('test1',2)
-- truncate删除数据后自增从头开始记录
-- 其实是把表给删了又重新建了一个表
-- 不可以写where
truncate table menu_copy1
-- 伪删除
-- 我们可能并不会进行真正的删除操作，因为这样数据会丢失
-- 可以对表添加一个状态字段
8.数据库定义语言—DDL(Data Definition Language）
-- 库、表的创建、删除和修改
-- 创建数据库
create database gg;
-- 删除数据库
drop database if exists gg;
-- 创建表
create table test1(
username varchar(10) not null,
age int
);
-- 查看建表语句
show create table test1
-- 查看表结构
desc test1
-- 按menu表的样式创建一个新表，只有表结构，没有数据
create table test2 like menu;
-- 删除表
drop table if exists test2;
-- 修改表结构
-- 给tset1表增加一列，该列名为t1，类型是int
alter table test1 add t1 int;
alter table test2 add tt int;
-- 删除test1表中的t1这一列
alter table test1 drop t1;
alter table test2 drop tt;
desc test2;
-- 修改列名
-- 修改列的属性时要注意
alter table test2 change gg t2 int;
alter table test1 change t2 t3 varchar(10) not null;
alter table test2 change tt gg varchar(10) not null;
desc test2;
-- 修改列的数据类型，没有改名字的想法
alter table test1 modify t3 int not null;
alter table test2 modify gg int;
-- 想改表名
alter table test1 rename test2;
9.数据类型
9.1日期类型
- date(‘2021-1-1’)
- time(‘12:12:12’)
- datetime(‘2021-1-1 12:12:12’)
- timestamp
create table test3(
t1 date,
t2 time
);
alter table test3 drop t2;
-- 测试date可以添加成功的格式：('20220101')，('2022-1-1')，('2022-01-01')，(20220101),('2022/1/1')
insert into test3 values('2022/1/1')
desc test3;
select * from test3;
-- 测试time可以添加成功的格式：('11:11:11')，('11:11')，('111111')，11:11:11
create table test4(
t1 time
);
insert into test4 values(111111)
select * from test4;
-- datetime
-- 范围'1000-1-1 00:00:00---9999-12-31'
create table test5(
t1 datetime
);
insert into test5 values(now());
select * from test5;
-- 修改test5表，将字段t1改为非空并给默认值
alter table test5 modify t1 datetime not null default CURRENT_TIMESTAMP;
-- 给test5表添加一个字段名叫t2，类型是timestamp
-- timestamp是时间戳，可以设置为非空，默认添加系统当前时间，并可以在记录被修改时改时间
-- timestamp范围：1970...~2038...
-- 可以按时区进行显示
alter table test5 add t2 timestamp;
desc test5;
-- 测试正常添加数据，会成功
insert into test5 values(now(),now())
-- 测试不处理t1这一列，mysql会给我们添加默认值，值为当前系统时间
insert into test5 (t2)values(now())
-- 测试只传入date，mysql会将time部分补充为00:00:00
insert into test5 values('20230825','20230825');
select * from test5;
-- 测试timestamp在记录被修改时的变化
-- 给test5表添加一列
alter table test5 add t3 int
update test5 set t3 = 7
9.2数值类型
-- int和integer没什么区别
-- bigint，相当于java中的long
-- float和double会失精度
-- int中的那个11和取值范围无关，是和ZEROFILL这个关键字配合使用的，长度不够会左补0
create table test6(
t1 int
);
desc test6;
-- 2147483647整数最大值 ，这句会超范围
insert into test6 values(2147483650);
-- unsigned无符号，可以多存一倍
alter table test6 modify t1 int unsigned;
select * from test6;
-- decimal(m,n)，总长度为m，保留n个小数，会四舍五入
-- 测试decimal，总长度为5，小数为2，整数位有3位
create table test7(
t1 decimal(5,2)
);
insert into test7 values(343.566);
select * from test7;
-- tinyint，相当于java中的byte
create table test8(
t1 tinyint
);
insert into test8 values(127) -- 128会超范围
alter table test8 modify t1 tinyint unsigned;
insert into test8 values(200)
select * from test8
9.3字符类型
-- char(固定长度)和varchar(变长)
-- char固定大小，假设设置char(100)，那么就算只存一个字符，mysql底层也会留出100这么大的位置，最多到255
-- varchar不固定大小，假设设置varchar(100)，那么就算只存一个字符，mysql底层就按一个字符算，最多到65535
create table test9(
t1 char(10)
);
-- char长度为10，就可以存10个汉字
insert into test9 values('哈哈哈哈哈哈哈哈哈哈')
9.4enum
create table test10(
t1 enum('男','女')
);
insert into test10 values('男'),(1),('女')
select * from test10;
10.约束
-- 对表中的数据设置一些限制条件，比如非空，不重复
-- 非空约束
create table test11(
t1 int not null,
t2 int
);
insert into test11 values(2,3)
-- default，设置默认值
create table test12(
t1 varchar(10) not null default 'gg',
t2 varchar(10) default 'pg',
t3 int
);
-- 主键传空值不会报错
insert into test12 (t3)values(2)
insert into test12 (t2,t3)values('gg',3)
select * from test12;
-- 唯一约束(列级约束)，unique
-- 只能处理单列
create table test13(
t1 int unique
);
insert into test13 values(5); -- 执行第二遍就会报错
-- 表级约束
-- 可以处理多列
create table test14(
t1 int,
t2 int,
t3 int,
unique(t1,t2) -- t1和t2合在一起必须唯一
);
insert into test14 values(1,2,3);
insert into test14 values(2,2,3);
insert into test14 values(1,2,5);
select * from test14;
-- 以下这种不是表级约束
create table test18
(
t1 int unique,
t2 int unique,
t3 int
)
insert into test18 values(1,2,3)
insert into test18 values(1,2,6)
insert into test18 values(1,5,3)
select * from test18
-- 主键约束
-- 主键非空且唯一
-- 主键如果不自增是不可以设置为null的
create table test15
(
id int primary key auto_increment,
username varchar(10),
age int
-- primary key (id)
-- auto_increment 自动递增
)
insert into test15 values(null,'gg',2)
insert into test15 values(3,'gg',2)
insert into test15 ()values()
select * from test15
-- 复合主键：多个列作为主键
create table test16
(
username varchar(10),
age int,
gender char(1),
primary key (username,age)
)
insert into test16 values('gg',2,'女');
insert into test16 values('gg',3,'女');
insert into test16 values('gg',2,'女');
select * from test16
desc test16
-- 外键约束
-- 外键应引用引用表中primary key的字段
-- mysql对外建的约束管理有多种方式
-- RESTRICT 默认的方式：如果有冲突不让增删改
-- CASCADE级联处理：如果menu_type表删了id为6的记录，menu表中所有menu_type_id为6的记录，会被级联删除
-- SET NULL 置空：如果menu_type表删了id为6的记录，menu表中所有menu_type_id为6的记录，中的menu_type_id字段会被置为null
-- 现在通常会在代码中进行外键操作的约束，而不是将这个约束功能交给mysql
create table test17
(
id int auto_increment,
name varchar(10),
primary key (id)
)
insert into test17 values(null,'开发部'),(null,'营销部')
select * from test17;
create table test17_emp
(
id int auto_increment,
username varchar(10),
dept_id int,
primary key (id),
foreign key (dept_id) references test17(id)
)
insert into test17_emp values(null,'gg',2)
insert into test17_emp values(null,'pg',1)
insert into test17_emp values(null,'pg',1)
truncate table test17_emp
delete from test17_emp where id in (1,2,3)
select * from test17_emp
11.事务
- 事务能够保证sql代码们一起成功一起失败
- 通常事务都是针对多条sql语句
- 提交或回滚都会结束一个事务
11.1事务的特点(ACID)
- 原子性
- 事务不可再分，要么都成功，要么都失败
- 一致性
- 事务从一个一致性状态到另一个一致性状态
- 隔离性
- 多个事务一起工作相互不被打扰
- 隔离是有级别的
- 持久性
- 是指事务一旦被提交，对数据库的影响就是永久性的
11.2事务的隔离级别
- 读未提交(B事务读到了A还未提交的事务)
- 会造成脏读
- 读已提交(B事务可以读到A已提交的事务)
- 可以处理脏读
- 可重复读
- B事务修改数据库记录并提交，不影响A事务对该条记录的读取结果
- 如花呗月末结算，发现用户借款30元，但此时用户又借了50元，花呗此时结算只需要看30元的借款，而不能查看用户又借的50元
- 串行化
12.存储引擎
- 在mysql中的数据可以用不同的技术存储在文件或内存中
show engines;查看mysql支持的引擎- InnoDB是默认引擎；MyISAM是以前版本使用的默认引擎；MEMORY是内存级的引擎
- InnoDB：支持事务，支持外键，支持崩溃修复和并发，支持行锁
- MyISAM：插入数据快，空间和内存使用比较低，但不支持事务，不支持行锁
- MEMORY：所有数据都存在内存中，处理速度快，但不支持事务，不支持外键
13.索引
- 索引的目的是提高查询效率
- 如果我们查询某条记录，像for循环一样遍历可能会比较慢，但是如果有“目录”就会快很多
- 创建索引也是耗时的，不能随便使用
- 聚簇索引(聚集索引)，辅助索引(二级索引)，全文索引
- 聚簇索引(聚集索引)基本上就是指主键
- 辅助索引如唯一约束就是由辅助索引来处理的
- 主键索引，唯一索引，普通索引，全文索引…
- mysql中的数据存储是使用B+树来存的
- 叶子节点存的是具体的数据，非叶子结点存的都是主键和页的关系
- 我们给表每建一个索引，mysql都会维护一棵树
- 只有聚簇索引树里存着表中的所有数据
- 其他的小索引树只存索引值+id
- 什么场景会创建索引
- 数据量大
- 经常在where中查询的字段可以考虑给这个字段建个索引
- 经常进行order by或group by的字段
- join时那个连接的字段
- 不适合建索引
- 经常有修改的列不建议
- 比例均衡的，比如性别就不适合建索引
- 记录少的不适合建索引
14.视图
-- 虚拟表
-- 比如可以出于安全考虑只提供有限的数据给用户
-- 或不同的权限可以查看不同的内容
-- 或者为了方便用户查询，将复杂的查询生成一个视图
-- 或者进行一些统计操作，统计结果放到视图中
-- 视图用show tables可以查询到
create view pg as select id,menu_name,price from menu where price < 20;
select * from pg
update pg set price = 21 where id = 5
15.范式
-- 表和表之间的关系
-- 一对一，一对多，多对多
select * from menu m
join menu_type mt on m.menu_type_id = mt.id
-- 三范式
-- 1.第一范式：关系R中的属性都是不可分割的项
select id,stu_info from student;-- 1 '念念,10岁,女,没上学'
select id,name,age,gender,status from student;-- 1 '念念' 10 '女''没上学'
-- 2.第二范式：在一范式的基础上，每个非主属性(普通字段)完全函数依赖于码
-- 需要有主键
-- 联合主键：每一个普通的字段都需要同时和两个联合的主键都有关系
-- 3.第三范式：任何非主属性(普通字段)不依赖于其他的非主属性，就是指不建议orders表中添加menu_name或price，可以只添加menu_id，然后通过多表关联来查询到menu的详细信息
-- 不过有时为了空间换时间，我们可能会不按三范式走
16.数据导入导出
16.1导出
- 在dos命令行，某盘符下
- 执行
mysqldump -uroot -p --databases my_db1>my_db1.sql，会在该盘符下 生成my_db1.sql文件 - 或执行
mysqldump -uroot -p my_db1 menu > menu01.sql，可以导出my_db1库中的menu表
16.2导入
- 先进入mysql—>
mysql -uroot -p - 进入某数据库—>
use my_db2 - 将d盘下的aa.sql中的数据导入my_db2库中—>
source d:\aa.sql
17.登录
mysql -uroot -p- 写全后：
mysql -uroot -hlocalhost -P3306 -p，想要连别人的数据库必须把ip和端口写全，平时不写是因为我们连的是本机的数据库 - 如果不写-h或-P默认就找localhost和3306
- 用
mysql -uroot -h10.11.0.86 -P3306 -proot登不上去，是因为数据库权限问题，也就是说数据库没设置其他人可以访问的权限，如下图，在mysql这个数据库中查看users表，root用户的权限为localhost，也就是说仅本机访问，使用ip地址访问不了
17.1权限
-- 进入mysql这个数据库中
use mysql;
-- 创建一个新用户，用户名是gg，密码是123，本机可以访问，使用ip地址不能访问
create user 'gg'@'localhost' identified by '123'
-- 创建一个用户，用户名是gg，密码是123，本机或者使用ip地址都可以访问
-- 我的ip10.11.100.114
create user 'gg'@'%' identified by '123'
-- 给gg用户赋查询和修改的权限
grant select,update on my_db1.menu to 'gg'@'localhost';
-- 撤销赋给gg的权限
revoke select,update on my_db1.menu from 'gg'@'localhost';
-- 将全部的权限赋给gg
grant all privileges on *.* to 'gg'@'localhost';
grant all privileges on *.* to 'gg'@'%';
-- 撤销赋给gg的所有权限
revoke all privileges on *.* from 'gg'@'localhost';
revoke all privileges on *.* from 'gg'@'%';
-- 删除仅本机进入数据库的gg用户
drop user 'gg'@'localhost';
-- 删除本机和ip都能进入数据库的gg用户
drop user 'gg'@'%';
注：修改用户权限后可能需要退出数据库再进入数据库后才会生效
18.sql优化
-- 最左匹配
-- 对于联合索引 来说，会按最左匹配来走
-- 但是如果从某个索引列开始，有范围查询，后面的索引 会失效
-- 假设 联合索引 a,b,c 访问 where a=0,c=0 ,C部分就没走
-- 假设 联合索引 a,b,c 访问 where b,c,a 这个会走，myql底层帮我们优化了
-- 索引下推、mysql8(索引跳跃扫描)
use my_db1;-- 切换回我们的数据库
explain select menu_name,price from menu where price >0 and menu_name like 'a%' ;
-- 1.尽量避免全表扫描 可以考虑在where、order by 建索引
-- 2.where num>2*10 where num/10>2 会导致索引失效
-- 3.select * from menu where id=5 or id=10;有or可能 会导致索引失效，可以替换成下面的方案
select * from menu where id=5
union all
select * from menu where id=10
-- 4.不要使用select * from 表
-- 5.尽量少用子查询
19.java连接数据库
- java连接数据库是有一套API的
- mysql-connector-java是Java的标准JDBC（Java数据库连接）驱动程序，是一种面向Java语言的驱动，可以与MySQL数据库连接并操作，需要根据所使用的MySQL版本选择相应的驱动版本，这样才能正常连接到MySQL数据库
- 需要导入mysql提供的jar包，拷过来之后不能直接使用，需要加载到项目中
- 右键jar包–>add as library，会发现jar包左侧，有小箭头，就可以了
19.1JDBC
JDBC全称为Java数据库连接(Java Database Connectivity)，是一套用于执行SQL语句的Java API，应用程序可以通过这套API连接到关系型数据库，并使用SQL语句完成对数据中数据的查询、增加、更新和删除等操作
JDBC在应用程序与数据库之间起到了一个桥梁作用，当应用程序使用JDBC访问特定的数据库时，需要通过不同数据库驱动与不同数据库进行连接，连接后即可对该数据库进行相应的操作
JDBC常用API主要位于Java.sql包中，该包定义了一系列访问数据库的接口和类：
1.Driver 接口：代表驱动程序；
2.DriverManager 类：驱动程序管理员；
3.Connection 接口：代表数据库连接；
4.Statement 、PreparedStatement、CallableStatement 接口：代表数据库操作对象；
5.ResultSet 接口：代表结果集；
6.DatabaseMetadata、ResultSetMetadata接口：代表元数据；
7.Types 类：代表JDBC类型。
19.2查询
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//(可以理解为我们在使用前安了个驱动)类的全限定名，该句的目的，是要将com.mysql.jdbc.Driver这个类加载到内存中
//如果报ClassNotFoundException，一种是我们写错了;一种是真没这个类
Class.forName("com.mysql.jdbc.Driver");//驱动管理器会帮我们处理这些事，但我们需要告诉他我们是哪个驱动，如是mysql还是oracle
//连接数据库 ip、port、用户名、密码
//jdbc:mysql: 是mysql厂商专用表达式
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_db1", "root", "root");
//从conn上获取一个执行器，执行器的主要作用，就是执行sql语句
//它就相当于我们navicat上的那个 执行的小按钮
Statement statement = connection.createStatement();
//准备要执行的sql（注意，这只是个字符串）
String sql = "select * from menu";
//执行器开始执行sql语句 result：结果 Set集合 -->结果集
ResultSet resultSet = statement.executeQuery(sql);
//将结果集中的数据取出
while(resultSet.next()){//resultSet.next()方法会返回一个boolean值，如果还能拿出数据，就返回true
//每次执行resultSet.next()都会获取到一条记录
System.out.println(resultSet.getString("menu_name")+","+resultSet.getBigDecimal("price"));
}
//关闭资源
resultSet.close();
statement.close();
connection.close();
}
运行结果如下
19.3增删改
增删改都使用statement.executeUpdate()，返回值为int类型，没有result结果集
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_db1", "root", "root");
Statement statement = connection.createStatement();
String sql = "insert into menu (menu_name,menu_type_id)values('gg',2)";
int i = statement.executeUpdate(sql);
System.out.println(i);
String sql1 = "insert into menu (menu_name,menu_type_id)values('gg',2)";
int i1 = statement.executeUpdate(sql1);
System.out.println(i1);
}
20.sql注入
模拟登陆，需要输入用户名、密码，此时用sql注入，即使密码不对，也可以登录成功，正确用户名和密码如下图
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_db1", "root", "root");
Statement statement = connection.createStatement();
String username = "gg";
String password = "626' or 1 = '1";
//select * from users where username = 'gg' and password = '815' or 1 = '1'
String sql = "select * from users where username = '"+username+"' and password = '"+password+"'";
System.out.println(sql);
ResultSet resultSet = statement.executeQuery(sql);
//resultSet结果集中埋了一个指针，刚开始谁也没指，每次next后才会指向下一条记录
if (resultSet.next()){
System.out.println("欢迎━(*｀∀´*)ノ亻!");
}else{
System.out.println("账号或密码输入有误");
}
connection.close();
statement.close();
resultSet.close();
}
运行结果如下
使用sql语句select * from users where username = 'gg' and password = '626' or 1 = '1'运行出来的结果如下，所以resultSet.next()的结果总为true，所以可以登录成功
21.阻止sql注入
- 可以将半成品sql传给mysql数据库，如
select * from users where username = ? and password = ? - 此时就相当于传入两个参数，不会改变sql语句的结构
- 使用set这个方法传入参数时会自动给参数加双引号
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_db1", "root", "root");
String sql = "select * from users where username = ? and password = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1,"gg"); //第一个参数的1代表第一个问号
ps.setString(2,"' or '1'='1"); //第一个参数的2代表第二个问号
ResultSet rs = ps.executeQuery();
if (rs.next()){
System.out.println("欢迎━(*｀∀´*)ノ亻!");
}else{
System.out.println("账号或密码输入有误");
}
connection.close();
ps.close();
rs.close();
}
运行结果如下
22.元数据
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_db1", "root", "root");
String sql = "select sid as '学生id',sname from student";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSetMetaData metaData = ps.getMetaData();
System.out.println("获取列名："+metaData.getColumnName(1));
System.out.println("获取列的别名："+metaData.getColumnLabel(1));
System.out.println("获取第一列的类型的名字："+metaData.getColumnTypeName(1));
System.out.println("获取数据库的名字"+metaData.getCatalogName(1));
connection.close();
ps.close();
}
23.封装数据库中的数据
- 数据从数据库中查询出来需要封装起来，方便后续使用
- 通常的方案是每一条记录用一个实例来装，再准备一个ArrayList来存这些实例对象
- 数据库表的int类型字段映射到实体类中要使用Integer类型，因为如果返回字段值为null，int类型会报错，Integer不会报错
public class homework01_取出Student表中的数据 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/my_db1", "root", "root");
String sql = "select * from student";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
List<Student> list = new ArrayList<>();
while (rs.next()) {
Student stu = new Student(
rs.getInt("SId"),
rs.getString("Sname"),
rs.getString("birthday"),
rs.getString("Ssex")
);
list.add(stu);
}
for (Student student : list) {
System.out.println(student);
}
}
}
class Student {
private Integer id;
private String name;
private String birthday;
private String gender;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Student(Integer id, String name, String birthday, String gender) {
this.id = id;
this.name = name;
this.birthday = birthday;
this.gender = gender;
}
public Student() {
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", birthday='" + birthday + '\'' +
", gender='" + gender + '\'' +
'}';
}
}
注：sql语句查出来的表存在结果集中，循环输出时要注意不能输出表(结果集)中没有的列，否则会报错
24.解耦合
24.1自己获取连接
- 工具类
- 获取连接，方便其他需要用到连接的java文件
- 关闭连接
public class DButil_解耦合 {
//之所以把变量定义在外面是因为如果定义在静态代码块内部，那么出了静态代码块后，变量就失效了，其他方法就用不了了，所以需要定义在外面，而之所以定义为静态变量是因为静态代码块只能给静态变量初始化
static String driverName;
static String url;
static String username;
static String password;
/**
* 将获取文件这个IO操作，改成在静态代码块中执行，避免多次执行
* 也说明一件事，如果我们修改了配置文件，需要重新启动一下
* 重启会使static代码块重新执行一次，因为它就执行一次
* 首次加载该类时，会调用下面的静态代码块
* 但如果再次调用，就不会再加载了
*/
static{
//创建一个Properties对象
Properties prop = new Properties();
//将文件中的数据加载到prop中
// prop.load(new FileInputStream("E:\\Java\\IdeaProjects\\JavaSe\\day26\\src\\day26\\aa.properties"));
try {
//使用类加载器来加载资源，相对路径和加载我们平时的类是一个路径，就不容易出问题
//它是相对于src目录下
prop.load(DButil_解耦合.class.getClassLoader().getResourceAsStream("day26/aa.properties"));
} catch (IOException e) {
throw new RuntimeException(e);
}
driverName = prop.getProperty("driverName");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
}
//获取连接
public static Connection getConn() {
Connection connection = null;
try {
//(可以理解为我们在使用前安了个驱动)类的全限定名，该句的目的，是要将com.mysql.jdbc.Driver这个类加载到内存中
//如果报ClassNotFoundException，一种是我们写错了;一种是真没这个类
Class.forName(driverName);//驱动管理器会帮我们处理这些事，但我们需要告诉他我们是哪个驱动，如是mysql还是oracle
//连接数据库 ip、port、用户名、密码
//jdbc:mysql: 是mysql厂商专用表达式
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return connection;
}
//处理PreparedStatement
public static void close(ResultSet resultSet, PreparedStatement statement, Connection connection) {
//关闭资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
//处理Statement
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
//关闭资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
配置文件如下图
24.2连接池帮我们获取连接
- 三种连接池dbcp，c3p0，druid，这里只练习druid这一种
- dbcp连接池需要从配置文件中读取四个参数driver、url、username、pwd并传给连接池
- c3p0默认配置文件名叫c3p0-config.xml，默认位置在src下，c3p0配置文件中有两种配置，区别是密码不同，可以选择其中一个配置传入连接池中
- druid连接池提供一个工厂类，可以接收Properties对象
- 使用连接池需要做的工作
- 1.导包
- 2.修改配置文件
- 3.写工具类
public class DruidUtil连接池 {
static DataSource dataSource;
//加载静态资源
static {
Properties prop = new Properties();
try {
//读取druid.properties文件中的数据
prop.load(DruidUtil连接池.class.getClassLoader().getResourceAsStream("druid/druid(1).properties"));
//Druid看我们自己一句一句的set太麻烦，提供给我们一个工厂类，它说它帮我们set，我们只需要将prop传给它
dataSource = DruidDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
throw new RuntimeException(e);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConn() {
try {
//之前我们自己去连数据库，用Class.forName()和DriverManger.getConn
//现在交给连接池了，既然让连接池帮我们连数据库，就必须把4个参数给它
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void close(ResultSet resultSet, PreparedStatement statement, Connection connection) {
//关闭资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
//关闭资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
配置文件和需要的jar包如下(其中druid(1).properties为配置文件)
25.事务
- 事务：一起成功，一起失败
- 一个事务只有被提交或回滚时才会结束
- 当一个事务没提交也没回滚时，即使表中的数据没改，但在这个事务中是能查看到数据的改变的，因为是在同一个事务中
- set autocommit=0 ==> conn.setAutoCommmit(false);
- commit; ==> conn.commit();
- rollback; ==> conn.rollback();
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DruidUtil连接池.getConn();
conn.setAutoCommit(false); //设置不能自动提交并开启事务
String sql = "update stu set age = 1 where id = 3";
ps = conn.prepareStatement(sql);
ps.executeUpdate();
int a = 2 / 0;
conn.commit(); //手动提交，不过因为前面有异常，这句话执行不到
} catch (Exception e) {
try {
conn.rollback(); //捕获异常后，记得回滚
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
e.printStackTrace();
}
//这里查询表，发现查到的结果是：
//上面没写回滚，查到的就是上面修改的结果，
//如果上面写回滚了，查到的就是最原始的数据
try {
String sql = "select * from stu where id = 3";
ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(
rs.getInt("id")
+ "," +
rs.getString("username")
+ "," +
rs.getInt("age")
);
}
conn.commit(); //设置手动提交
} catch (Exception e) {
e.printStackTrace();
}
DruidUtil连接池.close(null, ps, conn);
}
26.批处理
- 批量处理
- mysql的url需要添加这个值：
&rewriteBatchedStatements=true - addBatch()：将要执行的SQL先保存起来，先不执行，这个方法在设置完所有的占位符之后调用
public static void main(String[] args)throws Exception {
//获取连接
Connection conn = DruidUtil连接池.getConn();
//准备半成品sql
String sql = "insert into cc (num)values(?)";
//预编译执行器
PreparedStatement stat = conn.prepareStatement(sql);
for (int i = 0; i < 100000; i++) {
stat.setInt(1,5); //把占位符填充好
stat.addBatch(); //将待执行的sql添加到一个内存区域
if(i%5000==0){
stat.executeBatch(); //满5000，再执行一次 写入操作
stat.clearBatch(); //清空Batch
}
}
//再执行一次executeBatch
stat.executeBatch(); //如果我们插入的数据，是100008条，不写这句话，就会丢了8条数据
DruidUtil连接池.close(null,stat,conn);
}
27.自动返回主键
public static void main(String[] args) throws SQLException {
//获取连接
Connection conn = DruidUtil连接池.getConn();
//准备半成品sql
String sql = "insert into stu (id,username,age)values(null,?,?)";
//准备预编译处理器，这里添加了一个参数，要求执行后返回主键
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
//将占位符填满
ps.setString(1,"yy");
ps.setInt(2,5);
//执行sql语句
// int i = ps.executeUpdate(); //这里的返回值返回的是成功的条数
//循环添加数据并取出主键的值
for (int j = 0; j < 2; j++) {
int i = ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
rs.next();
int anInt = rs.getInt(1);
System.out.println(anInt);
}
// System.out.println("成功的条数为："+i);
//用执行器获取新添加的记录的主键，但主键会被包在ResultSet这个对象中
// ResultSet rs = ps.getGeneratedKeys();
// while (rs.next()){
// //这里我们之前是用的rs.getInt(字段名)，但此次没字段名，我们通过另一种方式
// //通过第N列来获取数据。此次返回值 就一列，所以写1
// int i1 = rs.getInt(1);
// System.out.println(i1);
// }
//结果集想获取数据，需要先next一下
// resultSet.next();
// //这里我们之前是用的rs.getInt(字段名)，但此次没字段名，我们通过另一种方式
// //通过第N列来获取数据。此次返回值 就一列，所以写1
// int key = resultSet.getInt(1);
// System.out.println(key);
}
5055
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
