Mysql数据库
数据库的连接
也可以直接用 mysql -u root -pPWD
PWD是密码,和-p之间没有间隔.
mysql -h localhost -P 3306 -u root -p123456
MySQL的三层结构:DBMS、数据库（多个）、表
// 使用Java操作数据库。
public class JavaMysql {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//这里老韩给大家演示一下 java 程序如何操作Mysql
//加载类，得到mysql连接
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/hsp_db02", "root", "hsp");
//创建一个商品hsp_goods表, 选用适当的数据类型
//添加2条数据
//删除表goods
//这里可以编写sql 【create , select , insert , update ,delete ...】
//String sql = "create table hsp_goods ( id int, name varchar(32), price double, introduce text)" ;
//String sql = "insert into hsp_goods values(1, '华为手机', 2000, '这是不错的一款手机')" ;
String sql = "drop table hsp_goods" ;
//得到statement对象，把sql 语法发送给mysql执行
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
//关闭连接
statement.close();
connection.close();
System.out.println("成功~");
}
}
如何打开.sql文件: 使用Navicat: 文件 -> 打开外部文件 ->查询
创建数据库
查看、删除数据库
导入外部的.sql文件的数据到数据库中
mysql -u root -p study < 【.sql文件路径】
# 演示数据库的操作
# 使用指令创建数据库
CREATE DATABASE test
# 创建一个使用utf-8字符集的db2数据库
CREATE DATABASE db2 CHARACTER SET utf8 # 默认校对规则是不区分大小写的.
# 创建一个使用utf-8字符集的db3数据库, 并带校对规则的db3数据库
# COLLATE 就是校对的意思, utf8bin表示校对规则区分大小写
CREATE DATABASE db3 CHARACTER SET utf8 COLLATE utf8_bin #
# 删除数据库指令。
DROP DATABASE test
# 查询的sql语句
# 查询数据库db3下的表list的数据
SELECT * FROM db3.list WHERE NAME = 'tom' # 写了tom和Tom, 但是utf8_bin是区分大小写的,所以只有一个记录
SELECT * FROM db2.list WHERE NAME = 'tom'
# 查看当前数据库服务器中的所有数据库
SHOW DATABASES
# 查看当前创建的某个数据库的定义信息
SHOW CREATE DATABASE db2
# 在创建数据库表时, 为了规避关键字,可以使用反引号解决
CREATE DATABASE `CREATE` # 反引号是0左边的
备份恢复数据库
如果出现拒绝访问 检查一下导出的文件目录是不是一个.sql文件!
# database.sql 备份bzhan 和db2数据库中的数据, 并恢复
# 备份 要在Dos下执行
# 备份后的文件, 就是对应的sql语句
mysqldump -u root -p -B bzhan db2 > E:\\JavaCode\基础\\Chapter23Mysql\\bak.sql
# 删除之前原本的数据库
DROP DATABASE bzhan
DROP DATABASE db2
# 恢复数据库 (在Dos中进入Mysql命令后在执行)
source E:\JavaCode\基础\Chapter23Mysql\backup.sql # 这里的目录不能有中文！！
创建表
列类型
65535-3 /2或3
-3是因为1-3个字节要用于记录大小，除以3是utf8编码3个字节表示一个字符。如果是gbk编码则除以2，因为gbk两个字节表示一个字符。
字符串使用细节（varchar）
char(4) 和 varchar(4) 这个 4 表示的是字符，而不是字节, 不区分字符是汉字还是字母
日期类型的基本使用
# 创建一张表 date, datetime, timestamp
CREATE TABLE t7 (
`birthday` DATE, -- 生日
`job_time` DATETIME, -- 记录年月日
`login_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
INSERT INTO t7(`birthday`, `job_time`) VALUES('2022-11-11', '2022-11-11 10:10:10');
SELECT * FROM t7;
修改表
# 修改表Alter的代码 改变的是表的结构。
# 在表的后面增加列
# 员工表emp上增加一个image列, varchar类型, 要求在resume后面.
ALTER TABLE emp
ADD `image` VARCHAR(32) NOT NULL DEFAULT ''
SELECT * FROM emp
DESC employee -- 显示表结构, 可以查看表的所有列.
# 修改job列,使其长度为60
ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
# 删除列
ALTER TABLE emp DROP sex
# 修改表名：表名改为employee
RENAME TABLE emp TO employee
# 修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8
# 列名name修改为user_name
ALTER TABLE employee CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''
CRUD学习：
# INSERT练习 update
CREATE TABLE `goods` (
`id` INT,
`goods_name` VARCHAR(10),
`price` DOUBLE);
# 添加数据
INSERT INTO `goods` (`id`, `goods_name`, price)
VALUES(10, '华为手机', 2000);
INSERT INTO `goods` (`id`, `goods_name`, price)
VALUES(20, '苹果手机', 3000);
SELECT * FROM goods
insert语句的细节
# 添加多个值
INSERT INTO `goods` (`id`, `goods_name`, price)
VALUES(30, '小米手机', 1999), (40, 'OPPO手机', 4000);
# 当给表中所有字段添加数据时， 可以不写前面的字段名称
INSERT INTO `goods` VALUES(20, '苹果手机', 3000);
update和alter的区别：update是更新表里的数据（修改记录），alter是修改字段（表的结构）
#update的使用
SELECT * FROM employee
INSERT INTO employee VALUES(10, '老妖怪', '2000-11-1','2020-3-1', '捶背', 9999.1654, '干活的');
# UPDATE employee SET user_name = '小妖怪' WHERE user_name ='我想要'
# 不带where会将所有的行的数据都修改。
UPDATE employee SET Salary = 3000
# 将姓名为小妖怪的员工薪水修改为3000
UPDATE employee SET Salary = 3000 WHERE user_name = '小妖怪'
# 将老妖怪的薪水在原有基础上增加1000
UPDATE employee
SET Salary = Salary + 1000 -- 在原有基础上变化的方法
where user_name = '老妖怪'
# 删除表中名称为`老妖怪`的记录
DELETE FROM employee
WHERE user_name = '老妖怪'
# DELETE语句不能删除某一列的值(可使用update设为null或'')
UPDATE employee SET job = '' WHERE user_name = '老妖怪'
select★★★★
select语句基本用法
# SELECT 语句
CREATE TABLE student(
`id` INT NOT NULL DEFAULT 1,
`name` VARCHAR(20) NOT NULL DEFAULT '',
`chinese` FLOAT NOT NULL DEFAULT 0.0,
`english` FLOAT NOT NULL DEFAULT 0.0,
`math` FLOAT NOT NULL DEFAULT 0.0);
INSERT INTO student VALUES(1, '韩顺平', 89,78,90)
INSERT INTO student VALUES(2, '张飞', 67,98,56)
INSERT INTO student VALUES(3, '宋江', 87,78,77)
INSERT INTO student VALUES(4, '关羽', 88,89,90)
INSERT INTO student VALUES(5, '赵云', 82,84,67)
INSERT INTO student VALUES(6, '欧阳锋', 55, 85, 45)
INSERT INTO student VALUES(7, '黄蓉', 75, 65, 30)
# 查询表中所有学生的信息
SELECT * FROM student
# 查询表中所有学生的姓名和对应的英语成绩
SELECT `name`, `english` FROM student;
# 过滤表中重复数据 DISTINCT
-- 要查询的记录每个字段都相同时才会去重
SELECT DISTINCT `english` FROM student;
#当英语和姓名都相同时才会去重
SELECT DISTINCT `english`, `name` FROM student;
# 统计每个学生的总分
SELECT `name` as '名字', (`chinese`+`english`+`math`) AS `total_score` FROM student;
# 在所有学生总分基础上再加10分
SELECT `name` as '名字', (`chinese`+`english`+`math`) AS '总分' FROM student;
order by是用于根据某一列排序的。
# order by
# 根据某一列进行排序(升序or降序)
SELECT * FROM student
ORDER BY math DESC; # DESC表示降序
# 对总分按照从高到低顺序输出[降序] -- 使用别名排序
SELECT `name`, (chinese+math+english) AS total_score FROM student
ORDER BY total_score DESC;
# 对姓李的学生成绩升序排序
SELECT `name`, (chinese+math+english) AS total_score FROM student
WHERE `name` LIKE '赵%' ORDER BY total_score DESC
# 先按照部门号升序, 再]工资降序排序
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;
count
# count的使用
# 统计有多少学生
SELECT COUNT(*) FROM student;
# 统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student
WHERE `math` >= 90
# 统计总分大于250的人
SELECT COUNT(*) FROM student
WHERE chinese+math+english>250
# COUNT(*) 和 COUNT(列) 的区别:
-- COUNT(*) 是返回所有的记录条数
-- COUNT(列) 是返回该列不为null的记录条数.
# 演示sum函数的使用
# 统计一个班数学总成绩
SELECT SUM(math) FROM student;
# 统计一个班语文,数学,英语各科总成绩
SELECT SUM(chinese), SUM(math), sum(english) FROM student;
# 统计一个班语文, 英语, 数学的成绩总和
SELECT SUM(chinese+english+math) from student;
# 统计一个班级语文成绩平均分
SELECT SUM(chinese) / COUNT(*) from student;
avg、max、min的使用
# 演示avg的使用
# 求一个班级数学平均分
SELECT AVG(math) FROM student;
# 求一个班级总分平均分
SELECT AVG(math + english+chinese) FROM student;
# 演示max和min的使用
SELECT MAX(math) from student;
SELECT MAX(math+english+chinese) AS max, MIN(math+english+chinese) as min from student;
Group by & having的学习
# GROUP BY & HAVING 的学习
# GROUP by用于对查询的结果分组统计
# having子句用于限制分组显示结果.
# 显示每个部门的平均工资和最高工资
SELECT AVG(sal), MAX(sal), deptno
FROM emp GROUP BY deptno;
# 显示每个部门的每种岗位的平均工资和最低工资
-- 先按照deptno排序，再根据job排序。但是我不是这样的。
SELECT AVG(sal), MIN(sal), deptno, job
FROM emp GROUP BY deptno, job;
# 显示平均工资低于2000的部门号和他的平均工资
SELECT AVG(sal) as avg_sal, deptno FROM emp
GROUP BY deptno HAVING avg_sal < 2000;
字符串相关函数
# 就介绍一个了。
SELECT UCASE(name) FROM emp;
# 作业：以首字母小写的方式展示所有的姓名
SELECT CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2)) FROM emp;
数学函数
SELECT ABS(-10) FROM DUAL # DUAL是一个特殊的表,它只有一列和一行,用于执行一些无需实际表数据的操作。
# FORMAT(num, 位数) 保留小数位数(四舍五入)
SELECT FORMAT(78.1234567986,3) FROM DUAL;
# RAND([seed]) 返回随机数,其范围是[0, 1]
# 可以设置种子，设置后随机数就是固定不变了
SELECT RAND() FROM DUAL;
时间日期相关函数
# 查询本月份最后一天是哪一天
SELECT LAST_DAY('2023.11.19') # 2023-11-30
# CURRENT_DATE() 当前时间
SELECT CURRENT_DATE() FROM DUAL;
# CURRENT_TIME() 当前时间
SELECT CURRENT_TIME() FROM DUAL;
# CURRENT_TIMESTAMP() 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
# 创建测试表 信息表
CREATE TABLE mes(
id INT,
content VARCHAR(30),
send_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP());
# 插入一条信息
INSERT INTO mes VALUES(1, '吉大新闻', CURRENT_TIMESTAMP())
INSERT INTO mes(id, content) VALUES(2, '山东新闻')
SELECT NOW() FROM DUAL; # NOW() 是当前的时间.
# 显示所有的新闻信息,发布时间只显示日期, 不显示时间.
-- DATE(send_time) 就是只显示日期, TIME(send_time)就是只显示时间.
SELECT id, content, DATE(send_time) FROM mes;
# 查询在10分钟之内发布的新闻
SELECT * FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW() -- 发布时间+10min大于现在.
# 求2011-11-11和1990-1-1相差多少天
-- 大的时间写在前面
SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL;
SELECT DATEDIFF(CURRENT_DATE(),'2001-8-4')
# 如果能活到80岁, 求还能活多少天.
SELECT DATEDIFF(DATE_ADD('2001-8-4',INTERVAL 80 YEAR),CURRENT_DATE)
# YEAR|MONTH|DAY|DATE (datetime)
SELECT YEAR(NOW()) FROM DUAL
SELECT MONTH(NOW()) FROM DUAL
SELECT DAY(NOW()) FROM DUAL
# unix_timestamp(): 返回的是1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP()/(24*3600*365) FROM DUAL;
# FROM_UNIXTIME() 可以把一个unix_timestamp的秒数转为指定格式的日期.
# 意义:在开饭中,可以存放一个整数表示时间, 通过FROM_UNIXTIME()进行转换.
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%y-%m-%d %H:%i:%s') FROM DUAL;
加密和系统函数
流程控制函数
# 演示加密函数和系统函数
# 查询用户
SELECT USER() FROM DUAL;
# 查询当前使用数据库名称
SELECT DATABASE()
# MD5(str) 为字符串计算出一个MD5 32位的字符串, 常用于用户密码加密.
# root密码是hsp -> 加密hd5 -> 在数据库中存放的是加密后的密码(不然数据库被入侵就G了)
SELECT MD5('lgq') FROM DUAL
# 查询长度
SELECT LENGTH(MD5('lgq')) FROM DUAL
# 演示用户表,存放密码是用的md5
CREATE TABLE hsp_user
(id INT,
`name` VARCHAR(32) NOT NULL DEFAULT '',
`pwd` CHAR(32) NOT NULL DEFAULT ''); # VARCHAR是变长的, CHAR是定长的.
INSERT INTO hsp_user
VALUES(100, '韩顺平', MD5('hsp'));
SELECT * FROM hsp_user; # 密码是加密后的
# 如何查询
SELECT * FROM hsp_user
WHERE `name`='韩顺平' AND `pwd`=MD5('hsp');
# PASSWORD(str) -- 加密函数 Mysql数据库用户密码就是PASSWORD加密的.
SELECT PASSWORD('hsp') FROM DUAL; # 被弃用了.
SELECT * FROM mysql.`user`
# 流程控制函数
# IF(expr1,expr2,expr3) 如果expr1为true, 则返回expr2, 否则返回expr3
SELECT IF(FALSE,'北京','上海') FROM DUAL -- 上海
SELECT IF(TRUE,'北京','上海') FROM DUAL -- 北京
# IFNULL(expr1,expr2) 如果expr1不为NULL, 则返回expr1. 否则返回expr2
SELECT IFNULL(NULL,'此项为NULL') FROM DUAL -- 此项为NULL
SELECT IFNULL('元数据','此项为NULL') FROM DUAL -- 元数据
# 查询emp表, 如果comm是null,则显示0.0
-- 方法1
SELECT ename, IFNULL(comm,0.0)
FROM emp
-- 方法2 判断空是用 IS NULL
SELECT ename, IF(comm IS NULL, 0.0, comm)
FROM emp;
# 如果emp表的job是CLERK则显示职员, 如果是MANAGER则显示经理
# 如果是SALESMAN则显示销售人员.其他正常显示.
# 如果job用这种方法前后都要加括号.
SELECT ename, (SELECT CASE job
WHEN 'CLERK' THEN '职员'
WHEN 'MANAGER' THEN '经理'
WHEN 'SALESMAN' THEN '销售人员'
ELSE job END)
FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
查询加强
# 查询加强
-- 在MySQL中, 日期类型可以直接比较
# 查询1992.1.1后入职的员工.
SELECT * FROM emp
WHERE hiredate > '1992.1.1';
# 模糊查询like
-- % 表示零到多个任意字符
-- _ 表示单个任意字符
# 如果显示首字符为S的员工姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%';
# 如何显示第三个字符为大写O 的所有员工的姓名和工资
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%';
# 如何显示没有上级的员工
SELECT ename, sal, mgr FROM emp
WHERE mgr IS NULL; -- 判断是否为NULL 要用ISNULL!!!
# 查询表结构
DESC emp;
# ORDER BY 加强
# 按照工资从低到高升序,显示雇员的信息
SELECT * FROM emp
ORDER BY sal;
# 先按照部门号升序, 再]工资降序排序
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;
分页查询
# 分页查询
# 按雇员的id号降序取出, 每页显示3条记录.分别显示第1,2,3页
# 第 1 页
SELECT * FROM emp
ORDER BY empno
LIMIT 0, 3;
# 第 2 页
SELECT * FROM emp
ORDER BY empno
LIMIT 3, 3;
# 第 3 页
SELECT * FROM emp
ORDER BY empno
LIMIT 6, 3;
# 按照雇员empno降序, 显示第三页:
SELECT * FROM emp
ORDER BY empno DESC
LIMIT 10, 5;
# 增强group by的使用
# 使用分组函数和分组子句group by
# 显示每种岗位的雇员总数, 平均工资
SELECT job, COUNT(*), FORMAT(AVG(sal), 2) FROM emp -- FORMAT(num,位数) 显示num的前几位
GROUP BY job;
# 显示雇员总数,以及获得补助的雇员数
SELECT COUNT(*), COUNT(comm) FROM emp;
# 显示管理者的总人数
SELECT COUNT(DISTINCT(mgr)) AS '管理者总人数' FROM emp;
# 显示雇员工资的最大差额
SELECT MAX(sal) - MIN(sal) FROM emp;
各种语句的顺序
# 各种语句的顺序
# 统计各部门平均工资, 并且是大于1000的, 且按照平均工资从高到低排序, 并取出前2行记录.
# 必须按照下面的顺序才行
SELECT job, AVG(sal) as avg_sal FROM emp
GROUP BY job
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0, 2;
多表查询
多表查询的条件不能小于表个数-1. 否则会出现笛卡尔集
# 多表查询
# 显示雇员名, 雇员工资, 以及所在部门的名字.[笛卡尔集]
-- 雇员名, 雇员工资来自emp表
-- 部门的名字来自dept表
-- 因此是对两个表查询. 多表查询的筛选条件where非常重要.
-- 当我们需要指定某个表的列时,需要说明
# 显示雇员名, 雇员工资和所在部门的条件.
SELECT ename, sal, dname, emp.deptno # deptno要说明是哪个表的, 因为两个表都有.
FROM emp, dept
WHERE emp.deptno = dept.deptno;
# 显示部门号为10的部门名, 员工名和工资
SELECT dept.deptno, ename, sal
FROM emp, dept
WHERE emp.deptno=dept.deptno AND dept.deptno=10
# 显示各个员工的姓名, 工资, 以及工资的级别
SELECT * FROM salgrade;
SELECT ename, sal, grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal; -- sal BETWEEN losal AND hisal 根据sal查询grade
多表查询的自连接
# 多表查询的自连接
# 必须使用表别名.
# 自连接的特点:
# 1. 把同一张表当作两张表使用
# 2. 需要给表取别名.
# 3. 列名不明确, 可以指定列的别名, 列名 AS 列的别名.
# 显示公司员工和他的上级的名字
SELECT worker.ename AS '员工', employee.ename AS '老板'
FROM emp AS worker, emp AS employee # 取别名的AS可以不写
WHERE worker.mgr = employee.empno
子查询
# 子查询的演示
# 如何显示与SMITH同一部门的所有员工
# 单行自查询
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno # 这一个SELECT语句得到的就是SMITH的部门编号.
FROM emp
WHERE ename = 'SMITH'
)
# 多行子查询
# 查询和部门10的工作相同的雇员的名字,岗位,工资和部门号, 但是不包含10号部门自己的雇员。
SELECT ename, job, sal, deptno
FROM emp
WHERE job IN ( # 工作岗位是在()里面的.
SELECT DISTINCT job # 工作岗位要唯一
FROM emp
WHERE deptno=10
) AND deptno <> 10 # <>也是不等于的意思. 等于是一个=
// 使用子查询当作临时表。
# 子查询临时表
SELECT goods_id, cat_id, goods_name, shop_price
FROM ecshop.ecs_goods;
# 查询ecshop中各个类别中, 价格最高的商品
# 先得到每个类别的价格最高的, 把这个当作临时表
SELECT cat_id, MAX(shop_price)
FROM ecshop.ecs_goods
GROUP BY cat_id;
# 把子查询当作一张临时表可以解决狠毒复杂的查询.
SELECT goods_id, ecshop.ecs_goods.cat_id, goods_name, shop_price
FROM (
SELECT cat_id, MAX(shop_price) AS max_price
FROM ecshop.ecs_goods
GROUP BY cat_id
) AS temp, ecshop.ecs_goods
WHERE ecshop.ecs_goods.cat_id=temp.cat_id
AND temp.max_price = ecshop.ecs_goods.shop_price
多列子查询
# 多列子查询
# 查询与WARD的部门和岗位完全相同的所有雇员, 且不包含他本人.
# (字段1, 字段2) = (SELECT 字段1, 字段2 FROM....)
SELECT * FROM emp
WHERE (deptno, job) = (
SELECT deptno, job
FROM emp
WHERE ename='WARD')
AND ename <> 'WARD'
# 查询与宋江的语数英成绩完全相同的学生(这个表是student表)
SELECT * FROM student;
SELECT * FROM student
WHERE (chinese, math, english) = (
SELECT chinese, math, english
FROM student
WHERE name='宋江'
)
子查询练习题
# 子查询练习题
# 查询每个部门工资高于本部门平均工资的人的资料
# 分析: 这里的temp表有部门号和该部门的平均工资, 我们要做的就是在emp表中
# 找部门号能对起来且他的工资大于平均工资的人.
SELECT ename, sal, avg_sal, emp.deptno
FROM (
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) AS temp, emp
WHERE emp.deptno=temp.deptno AND sal > temp.avg_sal
# 查找每个部门工资最高的人的详细资料
SELECT *
FROM (
SELECT deptno, MAX(sal) as max_sal
FROM emp
GROUP BY deptno
) AS temp, emp
WHERE emp.deptno=temp.deptno AND sal = max_sal
# 查询每个部门的信息(包括 部门号, 编号, 地址,) 和人员数量.
SELECT * FROM dept;
SELECT * FROM emp;
# 这个查询是部门号和部门人数.
SELECT COUNT(*), deptno
FROM emp
GROUP BY deptno;
SELECT dname, tmp.deptno, loc, tmp.per_num AS '人数'
FROM(
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) AS tmp, dept
WHERE tmp.deptno=dept.deptno
# 表.* 表示将所有的列都显现出来.
# 在多表查询中, 当多个表的列不重复时,才可以直接这样用.
SELECT dname, loc, tmp.*
FROM(
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) AS tmp, dept
WHERE tmp.deptno=dept.deptno
表复制
# 去重.
# 1. 先创建一张临时表, my_temp, 该表的结构和要去重的表my_tmp结构一样.
# 2. 把my_tmp的记录通过distinct 关键字处理后把记录复制到my_tmp
# 3. 清除掉my_tab02记录.
# 4. 把my_tmp表的记录复制到my_tab02
# 5. drop掉 临时表my_tmp.
CREATE TABLE my_tmp LIKE my_tab02;
INSERT INTO my_tmp
SELECT DISTINCT * FROM my_tab02;
DELETE FROM my_tab02;
# 蠕虫复制
INSERT INTO my_tab02
SELECT * FROM my_tmp;
DROP TABLE my_tmp;
合并查询
外连接
# 使用右连接
# 显示所有人的成绩(没有名字显示null)
SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id=exam.id;
# jack 1 56
# tom 2 76
# null null 8
-- 列出部门名称和这些部门的员工信息(名字和工作)，
-- 会有部门为null的, 也显示出来.
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON emp.deptno=dept.deptno;
mysql 约束
primary key(主键)-基本使用
主键
not null(非空)
`name` VARCHAR(32) NOT NULL,
unique(唯一)
CREATE TABLE t21
(id INT UNIQUE , -- 表示 id 列是不可以重复的.
`name` VARCHAR(32) ,
email VARCHAR(32));
foreign key(外键)
从表作为外键的列在插入数据时，要保证这个数据在主表中出现过。
-- 创建 从表 my_stu
CREATE TABLE my_stu (
id INT PRIMARY KEY , -- 学生编号
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT , -- 学生所在班级的编号
-- 下面指定外键关系
FOREIGN KEY (class_id) REFERENCES my_class(id))
check
CREATE TABLE t23 (
id INT PRIMARY KEY,
`name` VARCHAR(32) ,
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK ( sal > 1000 AND sal < 2000));
比较复杂的作业
商品售货系统表
CREATE TABLE goods(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(32) NOT NULL DEFAULT '',
unitprice DOUBLE(10, 2) NOT NULL DEFAULT 0
CHECK (unitprice BETWEEN 1.0 AND 9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(32) NOT NULL DEFAULT '');
CREATE TABLE customer(
customer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
address VARCHAR(32),
email VARCHAR(32) UNIQUE NOT NULL DEFAULT '',
sex ENUM('男','女') NOT NULL, # 枚举类型.
card_Id CHAR(18) UNIQUE);
CREATE TABLE pruchase(
order_id INT UNSIGNED PRIMARY KEY, -- UNSIGNED表示全为正
customer_id CHAR(8) NOT NULL DEFAULT '' ,
goods_id INT NOT NULL DEFAULT 0 ,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id), -- 外键关系
FOREIGN KEY (goods_id) REFERENCES goods(goods_id) -- 外键关系
);
自增长
-- 演示自增长的使用
CREATE TABLE t24
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32)NOT NULL DEFAULT '',
`name` VARCHAR(32)NOT NULL DEFAULT '');
INSERT INTO t24 (id, email, `name`) VALUES (NULL, '3@qq.com', 'afg');
INSERT INTO t24 (email, `name`) VALUES ('1@qq.com', 'SQL');
INSERT INTO t24 VALUES (NULL, '2@qq.com', 'sq');
SELECT * FROM t24;
DROP TABLE t24;
ALTER TABLE t24 AUTO_INCREMENT = 5; # 使得自增长序号从5开始.
INSERT INTO t24 VALUES (NULL, '5@qq.com', 'safq');
SELECT * FROM t24;
mysql 索引
# 创建索引
-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp 表的 empno 列创建索引
CREATE INDEX empno_index ON emp (empno);
-- 在ename 上创建索引
CREATE INDEX ename_index ON emp (ename);
# 添加索引
# 1.添加唯一索引
CREATE UNIQUE INDEX id_index ON t25(id);
# 2.添加普通索引方式1
CREATE INDEX id_index ON t25(id);
# 3.添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index(id);
# 4.添加主键索引
ALTER TABLE t25 ADD PRIMARY KEY(id);
# 删除索引
DROP INDEX id_index ON t25;
# 删除主键索引
ALTER TABLE t25 DROP PRIMARY KEY;
# 修改索引:
# 先删除,再添加
# 查询索引的 3 种方式
SHOW INDEX FROM t25;
show INDEXES FROM t25;
SHOW KEYS FROM t25;
在那些列上适合使用索引
MySQL事务
事务：执行的语句要么全部成功，要么全部失败
-- 1. 创建一张测试表
CREATE TABLE t27
( id INT, `name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION
-- 3. 设置保存点
SAVEPOINT a
-- 执行 dml 操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;
SAVEPOINT b
-- 执行 dml 操作
INSERT INTO t27 VALUES(200, 'jack');
-- 回退到 b
ROLLBACK TO b
-- 继续回退 a
ROLLBACK TO a
-- 如果这样, 表示直接回退到事务开始的状态. ROLLBACK
COMMIT
MySQL事务隔离级别
对于客户端A和B来说（设A是主体），当A的事务隔离级别是读未提交时，会出现脏读、不可重复读、幻读。
脏读是B写入了但未提交，A可以读到。
不可重复读是B修改了，幻读是B又插入了一个并且都提交了，A按理说应该读到某个时间点，后面的就不读了，但是实际上也读到了这些修改和插入的数据，这就叫不可重复读和幻读。
同理，当A的事务隔离级别是读已提交时，不会出现脏读，即B写入不提交则A看不到。但是B修改或者插入时A那边能看到。
脏数据：修改未提交，不可重复：修改并提交，幻读：查询和统计
# 查询隔离级别
SELECT @@transaction_isolation;
修改存储引擎
ALTER TABLE `表名` ENGINE = InnoDB
视图
让查询者只能看到整个表的一部分，不是全部的。
视图的细节
- 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
- 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
- 视图中可以再使用视图 , 比如从 emp_view01 视图中，选出 empno,和 ename 做出新视图
-- 针对 emp ，dept , 和 salgrade 张三表.创建一个视图 emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和 薪水级别[即使用三张表，构建一个视图]
CREATE VIEW emp_view03
AS
SELECT empno ,dname,grade
FROM emp, dept, salgrade
WHERE emp.deptno=dept.deptno # 找部门号能对应起来的
AND emp.sal BETWEEN salgrade.losal AND salgrade.hisal; # 找这个员工的薪水在等级的最低最高薪水之间的，否则会实现笛卡尔集
SELECT * FROM emp_view03;
MySQL数据库中创建，删除，修改密码
# 创建用户
CREATE USER 'lgq'@'localhost' IDENTIFIED BY '123456';
# 删除用户
DROP USER 'lgq'@'localhost';
# 修改自己的密码 PASSWORD(str) 这个函数已经被弃用。
ALTER USER 'lgq'@'localhost' IDENTIFIED BY 'abc';
root下操作的
# 创建库和表testdb下的news表，要求使用root用户创建
CREATE DATABASE testdb;
CREATE TABLE testdb.news(
id INT,
`name` VARCHAR(32)
)
INSERT INTO testdb.news
VALUES(100, '北京新闻');
SELECT * FROM testdb.news;
# 给lgq分配查看news表和添加news的权限
GRANT SELECT, INSERT
ON testdb.news
TO 'lgq'@'localhost';
GRANT UPDATE
ON testdb.news
TO 'lgq'@'localhost';
# 回收权限
REVOKE SELECT, INSERT, UPDATE
ON testdb.news
FROM 'lgq'@'localhost';
# 修改密码
ALTER USER 'lgq'@'localhost' IDENTIFIED BY '123';
DROP USER 'lgq'@'localhost';
CREATE TABLE DEPARTMENT(
departmendid INT PRIMARY KEY,
deptname VARCHAR(32) UNIQUE NOT NULL)
CREATE TABLE CLASS(
classid INT PRIMARY KEY, # 班号
subject VARCHAR(32), # 专业号
deptname VARCHAR(32), # 系名, 是唯一约束
enrolltime INT, # 入学年份
num INT, # 班级人数
FOREIGN KEY (deptname) REFERENCES DEPARTMENT(deptname)) # 人数
CREATE TABLE STUDENT(
studentid INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
age INT,
classid INT,
FOREIGN KEY (classid) REFERENCES CLASS(classid)
)
INSERT INTO DEPARTMENT VALUES (001,'数学'), (002, '计算机'), (003,'化学'), (004, '中文'), (005, '经济')
INSERT INTO class VALUES(101,'软件','计算机',1995,20);
INSERT INTO class VALUES(102,'微电子','计算机',1996,30);
INSERT INTO class VALUES(111,'无机化学','化学',1995,29);
INSERT INTO class VALUES(112,'高分子化学','化学',1996,25);
INSERT INTO class VALUES(121,'统计数学','数学',1995,20);
INSERT INTO class VALUES(131,'现代语言','中文',1996,20);
INSERT INTO class VALUES(141,'国际贸易','经济',1997,30);
INSERT INTO class VALUES(142,'国际金融','经济',1996,14);
INSERT INTO STUDENT VALUES(8101,'张三',18,101);
INSERT INTO STUDENT VALUES(8102,'钱四',16,121);
INSERT INTO STUDENT VALUES(8103,'王玲',17,131);
INSERT INTO STUDENT VALUES(8105,'李飞',19,102);
INSERT INTO STUDENT VALUES(8109,'赵四',18,141);
INSERT INTO STUDENT VALUES(8110,'李可',20,142);
INSERT INTO STUDENT VALUES(8201,'张飞',18,111);
INSERT INTO STUDENT VALUES(8302,'周瑜',16,112);
INSERT INTO STUDENT VALUES(8203,'王亮',17,111);
INSERT INTO STUDENT VALUES(8305,'董庆',19,102);
INSERT INTO STUDENT VALUES(8409,'赵龙',18,101);
# 查询所有姓李的学生
SELECT * FROM student
WHERE `name` LIKE '李%';
# 查询所有开设超过1个专业的系的名字
SELECT deptname, COUNT(*) AS num
FROM CLASS
GROUP BY deptname
HAVING num > 1
# 查询人数大于等于30的系的编号和名字
# 首先得到人数大于等于30的系的名字和人数
SELECT SUM(num) AS nums
FROM CLASS
GROUP BY deptname
HAVING nums >= 30;
# 通过联合查询tmp表以及DEPARTMENT表获取id,name,人数.
SELECT departmendid, DEPARTMENT.deptname, tmp.nums
FROM DEPARTMENT, (
SELECT deptname, SUM(num) AS nums # 这里要用sum求和才行，才是这个系所有的人数
FROM CLASS
GROUP BY deptname
HAVING nums >= 30
) AS tmp
WHERE tmp.deptname=DEPARTMENT.deptname;
-- (4) 学校又新增加了一个物理系，编号为006
INSERT INTO department VALUES('006','物理系');
-- (5) 学生张三退学，请更新相关的表
UPDATE CLASS
SET num=num-1
WHERE classid = (
SELECT classid
FROM STUDENT
WHERE `name`='张三'
)
SELECT * FROM CLASS;
DELETE FROM STUDENT
WHERE `name` = '张三';
JDBC
使用Java操作数据库
JDBC编写步骤
将mysql-connect-java的jar包添加到项目中是这样添加。
public static void main(String[] args) throws SQLException {
// 1. 注册驱动
Driver driver = new Driver();
// 2. 得到连接
// jdbc:mysql : 规定好表示协议， 通过jdbc方式连接mysql
// localhost：主机, 可以是ip地址
// 3306 表示mysql监听的端口
// db2 表示是连接到mysql dbms 的哪个数据库
// mysql的连接本质上就是前面学的socket连接.
String url = "jdbc:mysql://localhost:3306/db2";
// 将用户名和密码放入到Properties 对象
Properties properties = new Properties();
// user和pwd的值是规定好的,后面的值根据实际情况写
properties.setProperty("user", "root"); // 用户名
properties.setProperty("password", "..."); // 密码
Connection connect = driver.connect(url, properties);
// 3. 执行sql
String sql = "insert into actor values(null, '刘德华', '男', '1970-11-11', '110')";
// statement 用于执行静态sql语句并返回其生成的结果的对象.
Statement statement = connect.createStatement();
int rows = statement.executeUpdate(sql); // 如果是dml语句, 返回的就是影响行数.
System.out.println(rows>0?"成功":"失败");
// 4. 关闭资源连接
statement.close();
connect.close();
}
连接mysql的5种方法，主要记4和5
@Test
public void connect01() throws SQLException {
Driver driver = new Driver(); // 创建Driver对象
String url = "jdbc:mysql://localhost:3306/db2";
// 将用户名和密码放入到Properties 对象
Properties properties = new Properties();
// user和pwd的值是规定好的,后面的值根据实际情况写
properties.setProperty("user", "root"); // 用户名
properties.setProperty("password", "password"); // 密码
Connection connect = driver.connect(url, properties);
System.out.println(connect);
}
@Test
public void connect02() throws ClassNotFoundException, NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException, SQLException {
// 使用反射加载Driver类, 动态加载, 更加灵活, 减少依赖性
Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver = (Driver) aClass.getConstructor().newInstance();
String url = "jdbc:mysql://localhost:3306/db2";
// 将用户名和密码放入到Properties 对象
Properties properties = new Properties();
// user和pwd的值是规定好的,后面的值根据实际情况写
properties.setProperty("user", "root"); // 用户名
properties.setProperty("password", "password"); // 密码
Connection connect = driver.connect(url, properties);
System.out.println(connect);
}
@Test
public void connect03() throws SQLException, ClassNotFoundException, InvocationTargetException, InstantiationException, IllegalAccessException, NoSuchMethodException {
// 使用DriverManager替代driver进行统一管理
// 使用反射加载Driver
Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver = (Driver) aClass.getConstructor().newInstance();
String url = "jdbc:mysql://localhost:3306/db2";
String user = "root";
String password = "password";
DriverManager.registerDriver(driver); // 注册Driver驱动, 这句可以自动做, 不需要写.
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("第三种方式" + connection);
}
@Test
public void connect04() throws SQLException, ClassNotFoundException {
// 这种方式用的最多, 推荐使用.
// 使用Class.forName 自动完成注册驱动, 简化代码
// 使用反射加载Driver类
// 在加载Driver类时, 完成注册.
//Class.forName("com.mysql.cj.jdbc.Driver"); 这一行可以不写, 会自己找驱动.
// 创建url和user, pwd
String url = "jdbc:mysql://localhost:3306/db2";
String user = "root";
String password = "password";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("第四种方式" + connection);
}
@Test
public void connect05() throws IOException, SQLException {
// 方式5: 在方式4的基础上改进, 增加配置文件, 让连接mysql变得更加灵活.
Properties properties = new Properties();
properties.load(new FileInputStream("..\\Chapter23Mysql\\jdbc\\mysql.properties"));
// 获取相关的值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("方式5" + connection);
}
字符串里如何嵌套字符串去表示：
"INSERT INTO news values (1, \"北京新闻\")";
homework
// 创建news表, 使用jdbc添加5条数据.
// 修改id=1的记录,将name改成中国新闻
// 删除id=3的记录.
public static void main(String[] args) throws SQLException, IOException {
Properties properties = new Properties();
properties.load(new FileInputStream("..\\Chapter23Mysql\\jdbc\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
Connection connection = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO news values (1, \"北京新闻\")";
String sql1 = "INSERT INTO news values (2, \"山东新闻\")";
String sql2 = "INSERT INTO news values (3, \"潍坊新闻\")";
String sql3 = "INSERT INTO news values (4, \"烟台新闻\")";
String sql4 = "INSERT INTO news values (5, \"长春新闻\")";
// statement 用于执行静态sql语句并返回其生成的结果的对象.
Statement statement = connection.createStatement();
int rows = statement.executeUpdate(sql);
int rows1 = statement.executeUpdate(sql1);
int rows2 = statement.executeUpdate(sql2);
int rows3 = statement.executeUpdate(sql3);
int rows4 = statement.executeUpdate(sql4);
System.out.println(rows>0?"成功":"失败");
System.out.println(rows1>0?"成功":"失败");
System.out.println(rows2>0?"成功":"失败");
System.out.println(rows3>0?"成功":"失败");
System.out.println(rows4>0?"成功":"失败");
// 修改id=1的记录,将name改成中国新闻
sql = "UPDATE news SET `name`=\"中国新闻\" WHERE id=1";
rows = statement.executeUpdate(sql);
System.out.println(rows>0?"成功":"失败");
// 删除id=3的记录.
sql = "DELETE FROM news WHERE id=3";
rows = statement.executeUpdate(sql);
System.out.println(rows>0?"成功":"失败");
}
ResultSet[结果集]
public static void main(String[] args) throws SQLException {
String user = "root";
String pwd = "password";
String url = "jdbc:mysql://localhost:3306/db2";
Connection connection = DriverManager.getConnection(url, user, pwd);
Statement statement = connection.createStatement();
String sql = "SELECT * FROM news";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
int id = resultSet.getInt(1); // 获取第 1 列的数据
String name = resultSet.getString(2); //获取第 2 列的数据
System.out.println("id=" + id + " name=" + name);
}
statement.close();
connection.close();
resultSet.close();
}
PreparedStatement 预处理
public static void main(String[] args) throws SQLException {
String user = "root";
String pwd = "password";
String url = "jdbc:mysql://localhost:3306/db2";
Scanner scanner = new Scanner(System.in);
String id = scanner.nextLine();
String password=scanner.nextLine();
String sql = "SELECT user, pwd FROM admin WHERE user=? AND pwd=?";
// statement 用于执行静态sql语句并返回其生成的结果的对象.
Connection connection = DriverManager.getConnection(url, user, pwd);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 给 ? 赋值
preparedStatement.setString(1, id);
preparedStatement.setString(2, password);
// 如果执行的是dml(SELECT, insert, delete), 就要用executeUpdate()
// 下面这里是直接执行，而不是再用sql参数。如果用sql参数, 那用的还是之前没有给?赋值的语句
ResultSet resultSet = preparedStatement.executeQuery();
if(resultSet.next()) // 如果有next, 说明OK,查询到了
{
System.out.println("查询成功");
}
else{
System.out.println("查询失败");
}
sql = "UPDATE admin SET user=? WHERE user='tom'";
preparedStatement = connection.prepareStatement(sql);
// 给 ? 分配值
preparedStatement.setString(1,"kkk");
// 执行更新.
int res = preparedStatement.executeUpdate();
if(res>0){
System.out.println("修改成功");
}
else{
System.out.println("修改失败");
}
preparedStatement.close();
connection.close();
resultSet.close();
}
public static void main(String[] args) throws SQLException {
String user = "root";
String pwd = "password";
String url = "jdbc:mysql://localhost:3306/db2";
String sql = "INSERT INTO admin VALUES(?, ?)";
Connection connection = DriverManager.getConnection(url, user, pwd);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"jack");
preparedStatement.setString(2,"123a");
int res = preparedStatement.executeUpdate();
System.out.println(res>0?"成功":"失败");
preparedStatement.setString(1,"rose");
preparedStatement.setString(2,"avb");
res = preparedStatement.executeUpdate();
System.out.println(res>0?"成功":"失败");
preparedStatement.setString(1,"hsp");
preparedStatement.setString(2,"12sa");
res = preparedStatement.executeUpdate();
System.out.println(res>0?"成功":"失败");
preparedStatement.setString(1,"tonny");
preparedStatement.setString(2,"1ag");
res = preparedStatement.executeUpdate();
System.out.println(res>0?"成功":"失败");
// 修改jack的记录, 将name改为king
sql = "UPDATE admin SET user='king' WHERE user=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"jack");
res = preparedStatement.executeUpdate();
System.out.println(res>0?"成功":"失败");
// 删除名为tonny的记录
sql = "DELETE FROM admin WHERE user=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"tonny");
res = preparedStatement.executeUpdate();
System.out.println(res>0?"成功":"失败");
// 显示全部的记录.
sql = "SELECT * FROM admin";
preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2));
}
preparedStatement.close();
connection.close();
resultSet.close();
}
JDBC相关API总结
首先得到连接，通过.getConnection()方法得到连接。
然后得到预处理preparestatement，这个用于执行增删改查。executeUpdate和executeQuery。
然后处理完之后，关闭连接。调用close方法
JDBCUtils的使用
# 首先在数据库中创建表
CREATE TABLE actor (
id INT,
`name` VARCHAR(32),
`sex` VARCHAR(32),
borndate Date,
phone VARCHAR(32)
)
INSERT INTO actor VALUES (1, '刘广琦','男','2000-1-1','110')
INSERT INTO actor VALUES (2, '王雪莹','女','2001-1-1','1100'), (3, '魏宝航','男','2011-1-1','01100'),
(4, '与数位','男','2101-2-1','11000'), (5, '度过红','女','1101-1-1','12500')
SELECT * FROM actor;
mysql.properties:
user=root
password=password
url=jdbc:mysql://localhost:3306/db2?rewriteBatchedStatements=true
driver=com.mysql.jdbc.Driver
JDBCUtils.java
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String user;
private static String password;
private static String url;
private static String driver;
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("..\\JavaCode\\基础\\mysql.properties"));
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
driver = properties.getProperty("driver");
} catch (IOException e) {
//在实际开发中，我们可以这样处理
//1. 将编译异常转成 运行异常
//2. 调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便.
throw new RuntimeException(e);
}
}
// 连接数据库, 返回Connect对象.
public static Connection getConnection() {
try {
Connection connection = DriverManager.getConnection(url,user,password);
return connection;
} catch (SQLException e) {
//1. 将编译异常转成 运行异常
//2. 调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便.
throw new RuntimeException(e);
}
}
/*
1. ResultSet 结果集
2. Statement 或者 PreparedStatement, PreparedStatement是Statement的子集，所以用到向下转型。
3. Connection
4. 如果需要关闭资源，就传入对象，否则传入 null
*/
public static void close(ResultSet resultSet, Statement statement, Connection connection){
try {
if(resultSet != null) {
resultSet.close();
}
if(statement != null) {
statement.close();
}
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
JDBCUtils_Use.java
import org.junit.Test;
import java.sql.*;
public class JDBCUtils_Use {
@Test
public void testSelect(){
Connection connection = null;
String sql = "SELECT * FROM actor WHERE id = ?";
try {
// 得到连接
connection = JDBCUtils.getConnection();
System.out.println(connection.getClass());
// 执行增删改查
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,2);
ResultSet resultSet = preparedStatement.executeQuery(); // 这里不需要加sql
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String sex = resultSet.getString(3);
Date date = resultSet.getDate(4);
String phone = resultSet.getString(5);
System.out.println(id + " " + name + " " + sex + " " + date + " " + " phone " + phone);
}
// 关闭连接
JDBCUtils.close(resultSet,preparedStatement, connection);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Test
public void testDML() { // DML 包括 insert, update, delete
//得到连接
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
// 插入
String sql = "INSERT INTO actor VALUES (8, ?, ?, ?, '110')"; //需要依次插入name, sex, date
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"范永军");
preparedStatement.setString(2,"男");
preparedStatement.setDate(3,Date.valueOf("2001-1-1"));
preparedStatement.executeUpdate();
// 修改
sql = "UPDATE actor SET name = '刘梓硕' WHERE name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "刘广琦");
preparedStatement.executeUpdate();
// 删除(4, '与数位','男','2101-2-1','11000')这个人
sql = "DELETE FROM actor WHERE name='与数位'";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
// 关闭连接
JDBCUtils.close(null, preparedStatement, connection);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
事务处理
CREATE TABLE ACCOUNT (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL DEFAULT '',
balance DOUBLE NOT NULL DEFAULT 0
) CHARACTER SET utf8;
INSERT INTO ACCOUNT VALUES (NULL, '马云', 3000);
INSERT INTO ACCOUNT VALUES (NULL, '马化腾', 10000);
SELECT * FROM ACCOUNT;
使用事务 Transcation_.java
@Test
public void use_Transcation(){
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
String sql = "UPDATE ACCOUNT SET balance = balance - 100 WHERE id=1";
String sql2 = "UPDATE ACCOUNT SET balance = balance + 100 WHERE id=2";
// 设置不自动提交, 等价于开启事务, 显式提交时才会真正提交
connection.setAutoCommit(false);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
// 假设在此处发生异常,则转账不应该发生
//int i = 1/0;
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//该执行的都执行完了, 再提交事务.
connection.commit();
JDBCUtils.close(null, preparedStatement, connection);
} catch (Exception e) {
try {
connection.rollback();
System.out.println("发生了异常, 现在回滚");
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
throw new RuntimeException(e);
}
}
批处理
url更新为 url=jdbc:mysql://localhost:3306/db2?rewriteBatchedStatements=true
// 使用批处理:
public class Batch_ {
// 使用批处理方式对sql进行DML操作
@Test
public void use_Batch(){
Connection connection = null;
String url = "INSERT INTO admin2 VALUES (?, ?)";
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();`
PreparedStatement preparedStatement = connection.prepareStatement(url);
for(int i=0;i<5000;i++){
preparedStatement.setInt(1,i);
preparedStatement.setString(2,"jack"+i);
preparedStatement.addBatch(); // 先添加到批处理中
if((i+1)%1000==0){
preparedStatement.executeBatch(); // 执行处理批处理中的数据
preparedStatement.clearBatch(); // 处理完后清空当前队伍。
}
}
long end = System.currentTimeMillis();
System.out.println("用时" + (end-start)); // 传统用时11065 批处理用时356
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
传统获取 Connection 问题分析
数据库连接池种类分类
C3P0连接池连接
注意!!! c3p0-config.xml文件必须放在src目录下！！
如何创建src目录？
public class C3P0 {
@Test
public void C3P0_1() throws Exception {
// 1. 创建一个数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
// 2. 设置初始化参数值
Properties properties = new Properties();
properties.load(new FileInputStream(".\\jdbc\\mysql.properties")); //实验室：..\JavaCode\基础\mysql.properties 自己： .\jdbc\mysql.properties
// 3. 读取相关的属性值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 4.为数据源comboPooledDataSource设置参数。
// 连接管理是通过数据源comboPooledDataSource来设置的，所以要给数据源设置参数
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
comboPooledDataSource.setJdbcUrl(url);
// 设置初始化参数
comboPooledDataSource.setInitialPoolSize(10);
// 设置最大连接数
comboPooledDataSource.setMaxPoolSize(50);
// 测试连接池效率
long start = System.currentTimeMillis();
for(int i=0;i<5000;i++){
// getConnection方法就是从DataSource接口实现的。
Connection connection = comboPooledDataSource.getConnection();
// 关闭连接
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("使用C3P0的连接池时间为" + (end-start));
}
/*
第二种方式：使用配置文件来完成
1. 将c3p0提供的c3p0.config.xml拷贝到src目录下
2. 该文件指定了连接数据库和连接池的相关参数。
*/
@Test
public void C3P0_2() throws Exception {
// 报错：警告: named-config with name 'abc' does not exist. Using default-config extensions.
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("abc");
System.out.println(comboPooledDataSource.getUser());
System.out.println(comboPooledDataSource.getPassword());
System.out.println(comboPooledDataSource.getDriverClass());
/*
root
liafu1457465154996
com.mysql.cj.jdbc.Driver
*/
// 测试5000次连接mysql
long start = System.currentTimeMillis();
for(int i=0;i<5000;i++){
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("耗时" + (end-start)); //耗时499
}
Druid连接池连接
public class druid {
// 使用阿里的德鲁伊连接池连接数据库mysql
/*
步骤：
1. 加入Druid jar包
2. 加入配置文件 druid.properties, 将该文件拷贝到项目的src目录下
3. 创建Properties对象, 读取配置文件.
4. 创建一个指定参数的数据库连接池 Druid连接池.
*/
@Test
public void testDruid() throws Exception {
Properties properties = new Properties();
properties.load(new FileReader("src\\druid.properties"));
// 4. 创建一个指定参数的数据库连接池 Druid连接池.
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//测试连接效率
long start = System.currentTimeMillis();
for(int i=0;i<5000;i++){
Connection connection = dataSource.getConnection(); //获取与数据库的连接
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("Druid耗时" + (end-start)); //Druid耗时658
}
}
使用Druid创建JDBCUtils
//JDBCUtilsByDruid.java
public class JDBCUtilsByDruid {
private static DataSource ds;
// 在静态代码块中完成ds的初始化
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
Connection connection = ds.getConnection();
return connection;
}
/*
* 关闭连接.再次强调:在数据库连接池技术中,close并不是真正断开连接,
* 而是把使用的Connection对象放回连接池
* 这里的close是druid的处理方式.而在JDBCUtils中的关闭连接,是真正关闭原生的连接.
* */
public static void close(ResultSet resultSet, Statement statement, Connection connection){
try {
if(resultSet!=null)
resultSet.close();
if(connection!=null)
connection.close();
if(statement!=null)
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
Java_ApDBUtils引用
传统的resultSet在关闭connection后就没法用了, 但是有的情况下需要在关闭连接后再用(如果非得使用完再关闭连接,相当于一直在占用连接,导致其他人可能要等待.) 其次,resultSet在得到数据时,也非常麻烦, getString并没有体现我得到的是哪个属性. 所以这里定义一个Actor类,里面保存了一行的数据,然后定义一个ArrayList< Actor > 用于保存所有的行, 即一行对应一个数据.
Apache_DBUtils
举例: 使用DBUtils和Druid对表Actor实现增删改查
java bean有一个约定俗成的习惯，就是要有一个无参构造器。
// Apache-DBUtils & Druid的联动
package jdbc.datasource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class DBUtils_Use {
// 使用Apache-DBUtils 工具类 + druid 完成crud操作
@Test
public void testQueryMany() throws Exception {
// 1. 得到连接 Druid
Connection connection = JDBCUtilsByDruid.getConnection();
// 2. 使用DBUtils类和接口, 先引入DBUtils相关jar,加入到本Project.
// 3. 创建QueryRunner, 用于执行sql增删改查批处理
QueryRunner queryRunner = new QueryRunner();
// 4. 就可以执行相关方法, 返回ArrayList结果集.
String sql = "SELECT * FROM actor WHERE id >= ?";
// query方法就是执行sql语句, 得到结果集ResultSet, 将其封装到ArrayList中.
/*
connection 是指定的连接
sql 是执行的sql语句
new BeanListHandler<>(Actor.class): 将ResultSet 封装为Actor对象, 再封装到ArrayList中.
Actor.class()方法利用反射获取了Actor这个类里的全部信息,包括属性,方法,等.
*/
List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);//1就是指定sql语句中?的值,可以有多个
for(Actor actor:list){
System.out.print(actor);
}
// 在query中会自动关闭ResultSet, PrepareStatement, 所以这里只需要关闭connection
JDBCUtilsByDruid.close(null, null, connection);
}
@Test
// 使用Apache-DBUtils + Druid 返回单行记录(单个对象)
public void testQuerySingle() throws Exception {
Connection connection = JDBCUtilsByDruid.getConnection();
QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT * FROM actor WHERE id = ?";
// 查询单个对象(单行记录) 使用的就是BeanHandler
Actor query = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 1);
System.out.println(query);
}
@Test
// Scalar就是标量的意思
public void testScalar() throws SQLException {
// 1.使用Druid得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 使用Apache-DBUtils 得到查询Runner
QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT `name` FROM actor WHERE id = ?";
Object obj = queryRunner.query(connection, sql, new ScalarHandler<>(), 2);
System.out.println(obj + " " + obj.getClass());
JDBCUtilsByDruid.close(null, null, connection);
}
@Test
// 增删改查的使用
public void testDML() throws Exception {
// 1. 使用Druid得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 创建QueryRunnner
QueryRunner queryRunner = new QueryRunner();
// 执行增删改查
// 改
String sql = "UPDATE actor SET `name`=? WHERE id=?";
int affectedRows = queryRunner.update(connection, sql, "王雪莹2", 2);
System.out.println(affectedRows);
// 删
sql = "DELETE FROM actor WHERE id=?";
affectedRows = queryRunner.update(connection, sql, 8);
System.out.println(affectedRows);
// 增
sql = "INSERT INTO actor VALUES (4, ?, \"男\", \"2001-1-5\", \"1564\")";
affectedRows = queryRunner.update(connection, sql, "刘广琦");
System.out.println(affectedRows);
JDBCUtilsByDruid.close(null, null, connection);
}
}
JavaBean和数据库表的对应关系
JavaBean 通常用于表示数据库表的数据结构
-
属性与列的对应：
JavaBean 的每个属性通常对应于数据库表中的一个列。例如，如果数据库表有 id, name, email 列，那么相应的 JavaBean 将有 id, name, email 属性。 -
数据类型的映射：
JavaBean 属性的数据类型应该与数据库表列的数据类型兼容。例如，数据库中的 INT 类型可以映射到 Java 中的 int 或 Integer，VARCHAR 类型可以映射到 String 等。 -
Getter 和 Setter 方法：
JavaBean 遵循固定的命名规范，为每个属性提供 getter 和 setter 方法。这些方法用于访问和修改属性值，确保了封装性和数据隐藏。 -
无参构造函数：
JavaBean 应该提供一个无参的构造函数。这是许多框架（例如 Hibernate 或 Spring）在反射时创建对象实例所必需的。 -
可序列化：
通常建议 JavaBean 实现 java.io.Serializable 接口，尤其是在分布式应用中，或者当需要在网络间传输对象时。 -
与数据库操作的集成：
在实际应用中，JavaBean 通常与 JDBC 或 ORM 框架（如 Hibernate, JPA）一起使用。这些框架可以将数据库操作（如查询、更新）映射到 JavaBean 的实例上。
DAO
一个数据库表对应一个JavaBean XXX类(就是domain XXX类)，然后对应一个XXXDAO类（完成对数据库表的增删改查）
BasicDAO.java
/*
开发BasicDAO,这个是其他DAO的父类
DAO类就是完成对表的增删改查.
*/
public class BasicDAO<T> {
private QueryRunner queryRunner = new QueryRunner();
// 开发通用DML的方法, 针对任意表实现增删改查
public int update(String sql, Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int affectedRows = queryRunner.update(connection, sql, parameters);
return affectedRows; //返回受影响的行数
} catch (SQLException e) {
throw new RuntimeException(e); // 将编译异常转为运行异常并抛出, 可以选择处理该异常或者继续抛出
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//查询多行
/**
*
* @param sql sql语句
* @param clazz 传入要查询表对应的javabean类的Class对象
* @param parameters 可变参数, 是sql语句中?的值.
* @return
*/
public List<T> queryMuti(String sql, Class<T> clazz, Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
// 查询单行
public T querySingle (String sql, Class<T> clazz, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new BeanHandler<T>(clazz), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
// 查询标量
public Object queryScalar(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
Object o = queryRunner.query(connection, sql, new ScalarHandler<>(), parameters);
return o;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
}
ActorDAO.java 继承自BasicDAO
public class ActorDAO extends BasicDAO{
// 1. 继承BasicDAO后, 就有他所有的方法
// 2. 如果有需要特殊的业务, 就在这里添加特有的方法.
}
测试DAO
使用BasicDAO中的方法
public class TestDAO {
@Test
public void testActorDAO() {
ActorDAO actorDAO = new ActorDAO();
// 1. 查询多行
System.out.println("=========查询多行=========");
List<Actor> list = actorDAO.queryMuti("SELECT * FROM actor WHERE id >= ?", Actor.class, 1);
for(Actor actor:list){
System.out.println(actor);
}
System.out.println("=========查询单行=========");
// 2. 查询一行
Object o = actorDAO.querySingle("SELECT * FROM actor WHERE id=?", Actor.class, 2);
System.out.println(o);
// 3. 查询标量
Object o1 = actorDAO.queryScalar("SELECT `name` FROM actor WHERE id=?", 2);
System.out.println(o1);
// 4. 插入
int affectedRows = actorDAO.update("INSERT INTO actor VALUES(?, ?, ?, ?, ?)", 8, "刘梓硕", "男", "2015-1-1", "897");
System.out.println(affectedRows);
}
}
564
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
