入门到精通"/>
MySQL从入门到精通
一、配置
MySQL服务启动
- 手动
- cmd --> services.msc 打开服务的窗口
- 使用管理员打开cmd
- net start mysql:启动mysql的服务
- net stop mysql:关闭mysql服务
MySQL登陆
- mysql -u用户名 -p密码
- mysql -hip -u连接目标的用户 -p对应的密码
MySQL退出
- exit
- quit
二、SQL
1.什么是SQL?
Structured Query Language:结构化查询语言
其实就是定义了所有关系型数据可的规则。每种数据库操作的方式存在不一样的地方,称为“方言”
2.SQL通用语法
- SQL 语句可以单行或多行书写,以分号结尾
- 可使用空格和缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写,关键字建议用大写
3.SQL分类
-
DDL(Data Definition Language)数据定义语言
用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等
-
DML(Data Manipulation Language)数据库操作语言
用来对数据库中表的数据进行增删改查。关键字:insert,delete,uodate等
-
DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据)。关键字:select,where等
-
DCL(Data Control Language)数据控制语言(了解)
用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT,REVOKE等
(一)、DDL:操作数据库、表
操作数据库
- C(Create):创建
- 创建数据库:create database 数据库名称;
- 创建数据库,判断是否存在:create database if not exists 数据库名称;
- 创建数据库,并制定字符集:create database character set 字符集;
- 创建db4数据库,判断是否存在,并制定字符集为gbk:
- create database if not exists character set gbk;
- R(Retrieve):查询
- 查询所有数据库的名称:show databases;
- 查询某个数据库的字符集:查询某个数据库的创建语句
- show create database 数据库名称;
- U(Updete):修改
- 修改数据库的字符集
- alter database 数据库名称 character set 字符集;
- 修改数据库的字符集
- D(Delete):删除
- 删除数据库:drop database 数据库名称;
- 判断数据库是否存在,存在再删除:drop database if exists 数据库名称;
- 使用数据库
- 查询当前正在使用的数据库
- select database();
- 使用数据库
- use 数据库名称;
- 查询当前正在使用的数据库
操作表
-
C(Create):创建
-
语法:
-
create table 表名(列名1 数据类型1,列名2 数据类型2,...列名n 数据类型n );
-
-
数据类型:
- int:整数类型
- age int,
- double:小数类型
- score double(5,2)
- date:日期,只包含年月日,yyyy-MM-dd
- datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss
- timestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
- 如果没有赋值,默认使用当前系统时间
- varchar:字符串
- name varchar(20):姓名最大20个字符
- int:整数类型
-
创建表
-
create table student(id int,name varchar(32),age int,score double(4,1),birthday date,insert_time timestamp );
-
-
复制表
- create table 表名 like 被复制的表名;
-
-
R(Retrieve):查询
- 查询某个数据库中所有表的名称:show tables;
- 查询表结构:desc 表名;
-
U(Updete):修改
- 修改表名
- alter table 表名 rename to 新表名;
- 修改标的字符集
- alter table 表名 character set 字符集名称;
- 添加列
- alter table 表名 add 列名 数据类型;
- 修改列名称 类型
- alter table 表名 change 列名 新列名 新数据类型;
- alter table 表名 madify 列名 新数据类型;
- 删除列
- alter table 表名 drop 列名;
- 修改表名
-
D(Delete):删除
- drop table 表名;
- drop table if exists 表名;
(二)、DML:增删改表中数据
1. 添加数据
语法:
- insert into 表名(列名1,列名2, …列名n) values (值1,值2,…值n);
注意:
- 列名和值要一一对应
- 如果表明后,不定义列名,则么人给所有列添加值
- insert into 表名 values (值1,值2,…值n);
- 除了数字类型,其他类型需要使用引号(单双都可以)引起来
2. 删除数据
语法:
- delete from 表名 [where 条件];
注意:
- 如果不加条件,则删除表中所有记录
- 如果要删除所有记录
- delete from 表名; ---- 不推荐使用,有多少条记录就会执行多少次删除操作
- truncate table 表名; ---- 推荐使用,效率更高,先删除表,然后再创建一张一模一样的表
3. 修改数据
语法:
- update 表名 set 列名1=值1, 列名2=值2, … [where 条件];
注意:
- 如果不加任何条件,则会将表中所有记录全部修改
(三)、DQL:查询表中的记录
语法:
select字段列表
from表名列表
where条件列表
group by分组字段
having分组之后的条件
order by排序
limit分页限定
1. 基础查询
- 多个字段的查询
- select 字段名1, 字段名2… from 表名;
- 注意:
- 如果查询所有字段,则可以使用*来代替字段列表。
- 去除重复:
- distinct
- 计算列:
- 一般可以使用自足运算计算一些列的值。(一般只会进行数值型的计算)
- ifnull(表达式1, 表达式2):null参与的运算,计算结果都为null
- 表达式1:那个字段需要判断是否为null
- 如果该字段为null后替换的值
- 起别名:
- as:as也可以省略
2. 条件查询
-
where字句后面跟条件
-
运算符
>、<、<=、>=、=、<>
between ... and ...
在一个范围之间in(集合)
集合表示多个值,用逗号分隔like'张%'
模糊查询- 占位符:
- _ :单个任意字符
- %:任意个任意字符
- 占位符:
is null
查询某一列为null的值,不能写=null- and 或 &&
- or 或 ||
- not 或 !
3. 排序查询
语法: order by 子句
- order by 排序字段1 排列方式1, 排序字段2 排序方式2…
排序方式:
- ASC:升序,默认的。
- DESC:降序
注意:
- 如果有多个排序条件,则当前面的条件值一样时,蔡虎判断第二条件。
4. 聚合函数
将一列数据作为整体,进行纵向的计算
- count:计算个数
- 一般选择非空的列:主键
- max:计算最大值
- min:计算最小值
- sum:计算和
- avg:计算平均值
SELECT COUNT(IFNULL(`stu_name`,0)) 人数 FROM student;
SELECT MAX(stu_age) 最大年龄 FROM student;
SELECT MIN(stu_age) 最小年龄 FROM student;
SELECT SUM(stu_age) 年龄总和 FROM student;
SELECT AVG(stu_age) 平均年龄 FROM student;
注意: 聚合函数的计算,会排除null值
- 解决方案:
- 选择不包含空的列进行计算
- IFNULL函数
5. 分组查询
语法: group by 分组字段;
注意:
- 分组之后查询的字段:分组字段、聚合函数
- where和haring的区别
- where在分组之间进行限定,如果不满足条件,则不参与分组;having在分组之后进行限定,如果不满足结果,则不被查询出来
- where后不可以跟聚合函数,having可以进行聚合函数的判断
#按照班级分组,分别查询平均年龄,每个班的人数
SELECT class_id, AVG(stu_age), COUNT(stu_id) FROM student GROUP BY class_id;#按照班级分组,分别查询平均年龄,每个班的人数,小于12岁的不参与分组
SELECT class_id, AVG(stu_age), COUNT(stu_id) FROM student WHERE stu_age>=12 GROUP BY class_id;#按照班级分组,分别查询平均年龄,每个班的人数,小于12岁的不参与分组,分组之后人数要大于两个人
SELECT class_id, AVG(stu_age), COUNT(stu_id) FROM student WHERE stu_age>=12 GROUP BY class_id HAVING COUNT(stu_id)>2;
6. 分页查询
语法: limit 开始的索引,每页查询的条数;
公式: 开始的索引 = (当前的页码-1)* 每页显示的条数
#每页显示三条
SELECT * FROM student LIMIT 0,3; #第一页
SELECT * FROM student LIMIT 3,3; #第二页
SELECT * FROM student LIMIT 6,3; #第三页
limit 是一个MySQL“方言”
三、约束
概念: 对表中的数据进行限定,保证数据的正确性、有效性和完整性
分类:
- 主键约束:primary key
- 非空约束:not null
- 唯一约束:unique
- 外键约束:foreign key
主键约束:primary key
注意:
# 含义:非空主键
# 一张表只能有一个字段为主键
# 主键就是表中记录的唯一标识
-
在创建表时添加约束
-
CERATE TABLE stu(id INT PRIMARY KEY,`name` VARCHAR(20) );
-
-
创建完表后,添加主键
-
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-
-
删除主键
-
ALTER TABLE stu DROP PRIMARY KEY;
-
自动增长:
-
概念:如果某一类是数值类型的,使用auto_increment 可以来完成值的自动增长
-
在创建表时,添加主键约束,并完成自动增长
-
CERATE TABLE stu(id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20) );
-
-
创建表完成后,添加自动增长
-
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
-
-
删除自动增长
-
ALTER TABLE stu MODIFY id INT;
-
非空约束:not null
-
在创建表时添加约束
-
#创建表时添加非空约束 CREATE TABLE stu(id INT,`name` VARCHAR(32) NOT NULL );
-
-
创建表完后,添加非空约束
-
#创建表完成后,添加非空约束 ALTER TABLE stu MODIFY `name` VARCHAR(32) NOT NULL;
-
-
删除name的非空约束
-
#删除name的非空约束 ALTER TABLE stu MODIFY `name` VARCHAR(32);
-
唯一约束:unique
某一列不能重复
注意: 唯一约束可以由null值,但是只能有一条记录为null
-
在创建表时添加约束
-
#创建表时添加唯一约束 CREATE TABLE stu1(id INT,phone_number VARCHAR(20) UNIQUE );
-
-
在表创建完后,添加唯一约束
-
#在表创建完后,添加唯一约束 ALTER TABLE stu1 MODIFY phone_number VARCHAR(20) UNIQUE;
-
-
删除唯一约束
-
#删除唯一约束 ALTER TABLE stu1 DROP INDEX phone_number;
-
外键约束:foreign key
-
在创建表时,可以添加外键
-
CREATE TABLE 表名(...外键列CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) );
-
-
创建表之后,添加外键
-
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-
-
删除外键
-
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称);
-
级联操作:
-
添加级联操作
-
ALTER TABLE 表名 ADD CONSTRAINT 外键名称FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
-
-
分类:
- 级联更新:ON UPDATE CASCADE
- 级联删除:ON DELETE CASCADE
四、多表之间的关系
(一)、关系分类
一对一:
- 如:人和身份证
- 分析:一个人只有一个身份证,一个身份证只能对应一个身份证
一对多:
- 如:部门和员工
- 分析:一个部门有多个员工,一个员工只能对应一个部门
多对多:
- 如:学生和课程
- 分析:一个学生可以选择很多课程,一个课程也可以被很多学生选择
(二)、实现关系
一对多(多对一):
- 如:部门和员工
- 实现方式:在多的一方建立外键,指向一的一方的主键
多对多:
- 如:学生和课程
- 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段。这两个字段作为第三张表的外键,分别指向两张表的主键
一对一:
- 如:学生和身份证
- 实现方式:可以在任意一方添加唯一外键指向另一方的主键
(三)、案例
# 创建旅游路线分类表,tab_category
# cid 旅游路线分类主键,自动增长
# cname 旅游路线分类名称非空,唯一,字符串 100
CREATE TABLE tab_category (cid INT PRIMARY KEY AUTO_INCREMENT,`cname` VARCHAR(100) NOT NULL UNIQUE
);# 创建旅游线路表 tab_route
/*
rid 旅游线路主键,自动增长
rname 旅游线路名称非空,唯一,字符串 100
price 价格
rdate 上架时间,日期类型
cid 外键,所属分类
*/
CREATE TABLE tab_route(rid INT PRIMARY KEY AUTO_INCREMENT,`rname` VARCHAR(100) NOT NULL UNIQUE,price DOUBLE,rdate DATE,cid INT,FOREIGN KEY (cid) REFERENCES tab_category(cid)
);# 创建用户表 tab_user
/*
uid 用户主键
username 用户名 长度100,唯一,非空
password 密码 长度30,非空
name 真实姓名 长度100
birthday 生日
sex 性别 定长字符串1
telephone 手机号 字符串11
email 邮箱 字符串长度100
*/
CREATE TABLE tab_user(uid INT PRIMARY KEY AUTO_INCREMENT,`username` VARCHAR(100) UNIQUE NOT NULL,`password` VARCHAR(30) NOT NULL,`name` VARCHAR(100),`birhtday` DATE,`sex` CHAR(1) DEFAULT'男',`telephone` VARCHAR(11),`email` VARCHAR(100)
);# 创建收藏表 tab_favorite
/*
rid 路由路线id,外键
date 收藏时间
uid 用户id,外键
rid和uid不能重复,设置符合主键,同一个人用户不能收藏同一个人线路两次
*/
CREATE TABLE tab_favotite(rid INT, #线路iddate DATETIME,uid INT, #用户id# 创建复合主键PRIMARY KEY(rid,uid),FOREIGN KEY (rid) REFERENCES tab_route(rid),FOREIGN KEY (uid) REFERENCES tab_user(uid)
);
五、范式
概念: 设计数据库时,需要遵循的一些规范。要遵循后面的范式要求,必须先遵循前边的范式要求
(一)、几个概念:
- 函数依赖: A --> B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称为B依赖于A
- 例如:学号 --> 姓名 (学号,课程名称)–> 分数
- 完全函数依赖: A --> B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中的所有属性值
- 例如:(学号,课程名称)–> 分数
- 部分函数依赖: A --> B,如果A是一个属性组,则B属性的值的确定只需要依赖与A属性组中的某一些值即可
- 例如:(学号,课程名称)–>姓名
- 传递函数依赖: A–> B, B --> C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,通过B属性(属性组)的值可以确定唯一C属性的值,则称为C传递依赖与A
- 例如:学号 --> 系名,系名 -->系主任
- 码: 如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则成这个属性(属性组)为该表的码
- 例如:(学号,课程名称)
- 主属性:码属性组中的所有属性
- 非主属性:除了码属性组的属性
(二)、分类:
-
第一范式(1NF): 没一列都是不可分割的原子数据项
-
第二范式(2NF): 在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖 )
-
第三范式(3NF): 在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖 )
-
巴斯-科德范式(BCNF):
六、数据库的备份和还原
(一)、命令行
- 语法:
- 备份: mysqldump -u用户名 -p密码 数据库名 > 保存的路径
- 还原:
- 登录数据库——mysql -u用户名 -p密码
- 创建数据库——creat database 数据库名;
- 使用数据库——use 数据库名;
- 执行文件 source 文件路径——source d://a.sql
(二)、图形化界面
- 很简单
- 备份:右键需要备份的数据库 --> 转储SQL文件 --> 结构和数据
- 还原:右键 --> 运行SQL文件 --> 找到需要还原的sql文件
七、多表查询
(一)、什么是多表查询
查询语法:
select列名列表
from表名列表
where....
案例:
-
# 创建部门表 create table dept(id int primary key auto_increment,name varchar(20) );insert into dept (name) values ('开发部'),('市场部'),('财务部');# 创建员工表 create table emp (id int primary key auto_increment,name varchar(10),gender char(1), -- 性别salary double, -- 工资join_date date, -- 入职日期dept_id int,foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键) );insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1); insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2); insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2); insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3); insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);
-
直接
SELECT * FROM emp,dept;
的话就是将两个表的数据进行笛卡尔积 计算,会存在很多无用的数据
(二)、多表查询的分类
1. 内连接查询
隐式内连接: 使用where条件消除无用的数据
-
语法: SELECT 字段名 FROM 左表, 右表 WHERE 条件;
-
# 隐式内连接 SELECT emp.`name`,gender,dept.`name` 部门 FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
显示内连接: 使用INNER JOIN语句,可以省略INNER
-
语法: SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件;
-
# 显式内连接 SELECT emp.`name`,gender,dept.`name` 部门 FROM emp JOIN dept ON emp.dept_id=dept.id;
内连接总结:
- 确定查询哪些表
- 确定表连接的条件
- 确定查询的条件
- 确定查询的字段
2. 外链接查询
左外连接:
-
语法: SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
-
查询的是左表所有数据以及其交集部分。
-
用左边表的记录去匹配右边表的记录,如果符合条件的则显示;否则,显示 NULL
-
可以理解为:在内连接的基础上保证左表的数据全部显示**(左表是部门,右表员工)**
-
-- 在部门表中增加一个销售部 insert into dept (name) values ('销售部'); select * from dept; -- 使用内连接查询 select * from dept d inner join emp e on d.`id` = e.`dept_id`; -- 使用左外连接查询 select * from dept d left join emp e on d.`id` = e.`dept_id`;
右外链接:
-
语法: SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
-
查询的是右表所有数据以及其交集部分。
-
-- 在员工表中增加一个员工 insert into emp values (null, '沙僧','男',6666,'2013-12-05',null); select * from emp; -- 使用内连接查询 select * from dept inner join emp on dept.`id` = emp.`dept_id`; -- 使用右外连接查询 select * from dept right join emp on dept.`id` = emp.`dept_id`;
3. 子查询
-
概念: 查询中嵌套查询,称嵌套查询为子查询
-
事例: 查询最高员工的工资是多少
-
SELECT * FROM emp WHERE (SELECT MAX(salary) FROM emp)=salary;
-
子查询的结果是单行单列的:
-
子查询可以作为条件,使用运算符去判断。运算符:
> >= < <= =
-
-- 查询员工工资小于平均工资的人的信息 SELECT * FROM emp WHERE (SELECT AVG(salary) FROM emp)>salary;
子查询的结果是多行单列的:
-
子查询可作为条件,结果集类似于一个数组,父查询使用 IN 运算符
-
-- 查询财务部和市场部所有的员工信息 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE dept.`name` IN ('财务部','市场部'));
子查询的结果是多行多列的:
-
子查询结果只要是多列,肯定在 FROM 后面作为表,子查询作为一张虚拟的表
-
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 SELECT * FROM (SELECT * FROM emp WHERE join_date>'2011-11-11') t1,dept t2 WHERE t1.dept_id=t2.id;
(三)、练习
-- 部门表
CREATE TABLE dept (id INT PRIMARY KEY PRIMARY KEY, -- 部门iddname VARCHAR(50), -- 部门名称loc VARCHAR(50) -- 部门所在地
);-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');-- 职务表,职务名称,职务描述
CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20),description VARCHAR(50)
);-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司,接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');-- 员工表
CREATE TABLE emp (id INT PRIMARY KEY, -- 员工idename VARCHAR(50), -- 员工姓名job_id INT, -- 职务idmgr INT , -- 上级领导joindate DATE, -- 入职日期salary DECIMAL(7,2), -- 工资bonus DECIMAL(7,2), -- 奖金dept_id INT, -- 所在部门编号CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);-- 工资等级表
CREATE TABLE salarygrade (grade INT PRIMARY KEY, -- 级别losalary INT, -- 最低工资hisalary INT -- 最高工资
);-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);-- 需求:-- 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
SELECT emp.id,emp.ename,emp.salary,job.jname,job.description
FROM emp,job
WHERE
emp.job_id=job.id;-- 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
SELECT t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc
FROM emp t1,job t2, dept t3
WHERE
t1.job_id=t2.id AND t1.dept_id=t3.id;-- 3.查询员工姓名,工资,工资等级
SELECTt1.ename,t1.salary,t2.grade
FROMemp t1,salarygrade t2
WHEREt1.salary>t2.losalary AND t1.salary<t2.hisalary;-- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
SELECT t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc,t4.grade
FROM emp t1,job t2, dept t3,salarygrade t4
WHERE (t1.job_id=t2.id AND t1.dept_id=t3.id) AND t1.salary>t4.losalary AND t1.salary<t4.hisalary;-- 5.查询出部门编号、部门名称、部门位置、部门人数/*1.部门编号、部门名称、部门位置dept表。部门人数emp表2.使用分组查询。按照emp.dept_id完成分组,查询count (id)3.使用子查询将第2步的查询结果和dept表进行关联查询*/
SELECT t1.id,t1.dname,t1.loc,t2.renshu
FROMdept t1,(SELECTdept_id,COUNT(id) renshuFROMempGROUP BYdept_id) t2
WHEREt1.id=t2.dept_id;-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
/*1.姓名emp,直接上级的姓名empemp表的id和mgr是自关联2.条件emp .id =emp.mgr3.查询左表的所有数据,和交集数据使用左外连接查询
*/
SELECTt1.ename,t1.mgr,t2.id,t2.ename
FROMemp t1
LEFT JOINemp t2
ONt1.mgr=t2.id;SELECT * FROM emp;
八、事务
(一)、事务的基本介绍
概念:
- 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同事失败。
- 在实际的开发过程中,一个业务操作如:转账,往往是要多次访问数据库才能完成的。转 账是一个用户扣钱,另一个用户加钱。如果其中有一条 SQL 语句出现异常,这条 SQL 就可能执行失败。
- 事务执行是一个整体,所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常,则所有的 SQL 语句都要回滚,整个业务执行失败。
操作:
- 开启事务:start transaction;
- 回滚:rollback;
- 提交:commit;
回滚点:
- 在某些成功的操作完成之后,后续的操作有可能成功有可能失败,但是不管成功还是失败,前面操作都已经成 功,可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置,而不是返回所有操作,这个点称 之为回滚点。
- 设置回滚点:savepoint 名字;
- 回到回滚点:rollback to 名字;
MySQL数据库中事务提交的两种方式:
-
自动提交:
- MySQL 默认每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执行完毕 自动提交事务,MySQL 默认开始自动提交事务
-
手动提交:
- Oracle 数据库默认是手动提交事务
- 需要先开启事务,再提交
- 执行成功的情况: 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
- 执行失败的情况: 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
-
修改事务的默认提交方式
- 查看事务的默认提交方式:select @@autocommit; 1代表自动提交,0代表手动提交
- 修改默认提交方式:set @@autocommit=0;
(二)、事务的四大特征
原子性: 是不可分割的最小操作单位,要么同时成功,要么同时失败。
持久性: 当事务提交或回滚后,数据会持久化的保存数据。
隔离性: 多个事务之间。相互独立。
一致性: 事务操作前后,数据总量不变。
(三)、事务的隔离级别(了解)
概念: 多个事务之间隔离的,互相独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
存在的问题:
- 脏读:一个事务读取到另一个事物中没有提交的数据
- 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
- 幻读:一个事务操作(DML)数据表中所有的记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
隔离级别:
- read uncommitted:读未提交
- 会产生的问题:脏读、不可重复读、幻读
- read committed:读已提交 (Oracle默认)
- 会产生的问题:不可重复读、幻读
- repeatable read:可重复读 (MySQL默认)
- 会产生的问题:幻读
- serializable:串行化
- 可以解决所有问题
- 使用 serializable 隔离级别,一个事务没有执行完,其他事务的 SQL 执行不了,可以挡住幻读
注意: 隔离级别从小到大安全性越来越高,但是效率越来越低
数据库查询隔离级别:
- select @@tx_isolation;
- 在MySQL 8中已经修改为----SELECT @@transaction_isolation;
数据库设置隔离级别:
- set global transaction isolation level 级别字符串;
- 修改事务隔离级别的操作可以修改为:SET transaction_isolation = ‘READ-UNCOMMITTED’;
九、DCL学习
回顾SQL分类:
- DDL:操作数据库和表
- DML:增删改表中数据
- DQL:查询表中数据
- DCL:管理用户,授权
DEA: 数据库管理员
DCL:管理用户,授权
1. 管理用户
-
添加用户:
-
# 用户名:登陆的用户名;主机名:可以访问的主机 CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-
-
删除用户:
-
DROP USER '用户名'@'主机名';
-
-
修改用户密码:
-
# mysql8.0以上 alter user '用户名'@'主机名' identified by '新密码';
-
# mysql5.0 UPDATE USET SET PASSWORD=PASSWORD('新密码') WHERE user='用户名';
-
mysql中忘记了root用户密码?
- cmd --> net stop mysql 停止MySQL服务
- 使用无验证方式启动mysql服务:mysqld – skip-grant-tables
- 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功
- use mysql;
- update user set password = password(‘你的新密码’); 根据版本略有不同
- 关闭两个窗口
- 打开任务管理器,手动结束mysqld.exe进程
- 启动mysql服务
- 使用新密码登陆
-
-
查询用户:
-
# 切换到mysql数据库 USE mysql; # 查询user表 SELECT * FROM USER;
-
2. 管理权限
-
查询权限:
-
SHOW GRANTS FOR '用户名'@'主机名';
-
-
授予权限:
-
GRANT 权限列表 ON 数据库名.表名 to '用户名'@'主机名';
-
-
撤销权限:
-
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
-
十、JDBC
- 概念: Java DataBase Connectivity (Java数据库连接),Java语言操作数据库
- JDBC本质: 其实是官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口。各个数据库厂商趋势线这套接口,提供数据库驱动jar包。我们可以使用这套接口(JDBC编程,)真正执行的代码是驱动jar包中的实现类
快速入门:
- 步骤:
-
/* JDBC快速入门*/ public class JdbcDemo1 {public static void main(String[] args) throws Exception {//1. 导入jar包//2. 驱动注册Class.forName("com.mysql.cj.jdbc.Driver");//3. 获取数据库连接对象Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "123456");//4. 定义sql语句String sql = "update account set balance = 500 where id=1";//5. 获取执行sql的对象,StatementStatement stmt = conn.createStatement();//6. 执行sqlint count = stmt.executeUpdate(sql);//7. 处理结果System.out.println(count);//8. 释放资源stmt.close();conn.close();} }
-
(一)、详解各个对象:
DriverManager:驱动管理对象
-
功能:
-
注册驱动:告诉程序员该使用哪一数据库驱动jar
-
static void registerDriver(Driver driver):注册与给定的驱动程序 DriverManager
-
写代码使用
Class.forName("com.mysql.cj.jdbc.Driver");
-
注意: mysql5 之后的驱动jar包可以省略注册驱动的步骤
-
-
获取数据库连接
- 方法:
static Connection getConnection(String url, String user, String password)
- 参数:
- url:指定连接的路径
- 语法:
jdbc:mysql://ip地址(域名):端口号/数据库名称
- 例子:
jdbc:mysql://localhost:3306/db3
- 扩展:如果连接的是本机的一个mysql服务器,并且mysql服务默认端口是3306,则url可以简写为:
jdbc:mysql:///数据库名称
- 语法:
- user:用户名
- password:密码
- url:指定连接的路径
- 方法:
-
Connection:数据库连接对象
- 功能:
- 获取执行sql对象:
- Statement createStatement()
- PreparedStetement prepareStatement(String sql)
- 管理事务:
- 开启事务:setAutoCommit(boolean autoCommit):调用该方法设置参数为false,即为开启事务
- 提交事务:commit()
- 回滚事务:rollback()
- 获取执行sql对象:
Statement:执行sql的对象
-
执行sql:
- boolean execute(String sql):可以执行任意的sql
- int executeUpdate(String sql):执行DML(insert、update、delete)语句、DDL(create、alter、drop)语句
- 返回值:影响的行数,可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功,反之则失败。
- ResultSet executeQuery(String sql):执行DQL(select)语句
-
练习:
-
account表,添加一条记录
-
account表,修改记录
-
account表,删除一条记录
-
/*account表,添加一条记录,insert语句练习修改和删除的步骤也是一样,直接改sql语句就可以*/ public class JdbcDemo2 {public static void main(String[] args) {Statement stmt = null;Connection conne = null;try {//1. 注册驱动Class.forName("com.mysql.cj.jdbc.Driver"); //可以不写//2. 定义sqlString sql = "insert into account values(null,'王五',3000)";//3. 获取Connection对象conne = DriverManager.getConnection("jdbc:mysql:///db3", "root", "123456");//4. 获取执行sql的对象 Statementstmt = conne.createStatement();//5. 执行sqlint count = stmt.executeUpdate(sql); //影响的行数//6. 处理结果System.out.println(count);if (count > 0) {System.out.println("添加成功");} else {System.out.println("添加失败");}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException throwables) {throwables.printStackTrace();} finally {//7. 释放资源//避免空指针异常if (stmt != null) {try {stmt.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (conne != null) {try {conne.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}} }
-
ResultSet:结果集对象,封装查询结果
-
next():游标向下移动一行,判断当前行是最后一行末尾(是否有数据),如果是返回false,如果不是返回true
-
getXxx(参数):获取数据
- Xxx:代表数据类型
- 参数:可以是int或者String
- int:代表列的编号,从1开始
- String:代表列的名称
-
使用步骤:
- 游标向下移动一行
- 判断是否有数据
- 获取数据
//6. 处理结果
//6.1 让游标向下移动一行
while (rs.next()){//6.2 获取数据int id = rs.getInt(1);String name = rs.getString(2);double balance = rs.getDouble(3);System.out.println(id+"-"+name+"-"+balance);
}
练习: 定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回。
- 定义Emp类(自己定义一个Emp类,代码中没展示)
- 定义方法 public List< Emp > findAll(){}
- 实现方法 select * from emp;
/*** 定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回。*/
public class JdbcDemo5 {public static void main(String[] args) {List<Emp> emps = findAll();System.out.println(emps);}/*** 查询所有emp对象** @return*/public static List<Emp> findAll() {Emp emp = null;List<Emp> emps = new ArrayList<>();Connection conne = null;Statement stmt = null;ResultSet rs = null;try {//1. 注册驱动Class.forName("com.mysql.cj.jdbc.Driver");//2. 获取连接conne = DriverManager.getConnection("jdbc:mysql:///db2", "root", "123456");//3. 定义sqlString sql = "select * from emp";//4. 获取执行sql的对象stmt = conne.createStatement();//5.执行sqlrs = stmt.executeQuery(sql);//6. 处理结果,封装对象,装载集合while (rs.next()) {//封装对象emp = new Emp();emp.setId(rs.getInt(1));emp.setEname(rs.getString(2));emp.setJob_id(rs.getInt(3));emp.setMgr(rs.getInt(4));emp.setJoindate(rs.getDate(5));emp.setSalary(rs.getDouble(6));emp.setBouns(rs.getDouble(7));emp.setDept_id(rs.getInt(8));//装载集合emps.add(emp);}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException throwables) {throwables.printStackTrace();} finally { //释放资源if (rs != null) {try {rs.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (stmt != null) {try {stmt.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (conne != null) {try {conne.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}return emps;}
}
PreparedStatemt:执行sql的对象
SQL注入问题: 在拼接sql时,有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题
解决SQL注入问题: 使用PreparedStatemt对象解决
预编译SQL: 参数试用 ? 作为占位符
步骤:
- 导入驱动jar包
- 注册驱动
- 获取数据库连接对象 Connection
- 定义sql
- 注意: sql的参数使用 ? 作为占位符。如 select * from user where username=?and password=?;
- 获取执行sql语句的对象 PreparedStatement Connection.preparedStatement(String sql);
- 给 ? 赋值
- 方法: setXxx(参数1,参数2)
- 参数1: ? 的位置编号 从1开始
- 参数2: ? 的值
- 方法: setXxx(参数1,参数2)
- 执行sql
- 处理结果
- 释放
//1. 注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 定义sql
String sql = "select * from user where username = ? and password = ?";
//3. 获取Connection对象
conne = DriverManager.getConnection("jdbc:mysql:///db3", "root", "123456");
//4. 获取执行sql的对象
PrepareStatement pstmt = conne.prepareStatement(sql);
//5. 给 ? 赋值
pstmt.setString(1,username);
pstmt.setString(2,password);
//6. 执行sql
rs = stmt.executeQuery();
//7. 判断
return rs.next(); //如果有下一行,则返回true
//8. 释放
...
(二)、抽取JDBC工具类
目的: 简化书写
分析:
- 注册驱动(也抽取出来)
- 抽取一个方法获取连接对象
- 需求: 不想传递参数(麻烦),还得保证工具类的通用性
- 解决: 通过配置文件
- jdbc.properties
- 抽取一个方法释放资源
jdbc.properties
url=jdbc:mysql:///db2
user=root
password=123456
driver=com.mysql.cj.jdbc.Driver
JDBCUtils.java
/*** JDBC工具类*/
public class JDBCUtils {private static String url;private static String user;private static String password;private static String driver;/*** 文件的数据,只需要读取一次即可拿到这些值。使用静态代码块*/static {//读取资源文件,获取值。try {//1. 创建Properties集合Properties properties = new Properties();//获取src路径下文件的方式 ----> ClassLoader 类加载器ClassLoader classLoader = JDBCUtils.class.getClassLoader();URL res = classLoader.getResource("jdbc.properties");String path = res.getPath();System.out.println(path);//2. 加载文件properties.load(new FileReader(path));//3. 获取数据,赋值url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");driver = properties.getProperty("driver");//4. 注册驱动Class.forName(driver);} catch (IOException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}}/*** 获取连接** @return 连接对象*/public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password);}/*** 释放资源** @param stmt* @param conn*/public static void close(Statement stmt, Connection conn) {if (stmt != null) {try {stmt.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (conn != null) {try {conn.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}/*** 释放资源** @param rs* @param stmt* @param conne*/public static void close(ResultSet rs, Statement stmt, Connection conne) {if (rs != null) {try {rs.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (stmt != null) {try {stmt.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (conne != null) {try {conne.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}
}
JdbcDemo5.java
/*** 定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回。*/
public class JdbcDemo5 {public static void main(String[] args) {List<Emp> emps = findAll();System.out.println(emps);}/*** 查询所有emp对象** @return*/public static List<Emp> findAll() {Emp emp = null;List<Emp> emps = new ArrayList<>();Connection conne = null;Statement stmt = null;ResultSet rs = null;try {//1. 注册驱动,2. 获取连接conne = JDBCUtils.getConnection();//3. 定义sqlString sql = "select * from emp";//4. 获取执行sql的对象stmt = conne.createStatement();//5.执行sqlrs = stmt.executeQuery(sql);//6. 处理结果,封装对象,装载集合while (rs.next()) {//封装对象emp = new Emp();emp.setId(rs.getInt(1));emp.setEname(rs.getString(2));emp.setJob_id(rs.getInt(3));emp.setMgr(rs.getInt(4));emp.setJoindate(rs.getDate(5));emp.setSalary(rs.getDouble(6));emp.setBouns(rs.getDouble(7));emp.setDept_id(rs.getInt(8));//装载集合emps.add(emp);}} catch (SQLException throwables) {throwables.printStackTrace();} finally { //释放资源JDBCUtils.close(rs,stmt,conne);}return emps;}
}
(三)、JDBC管理事务
事务: 一个包含多个步骤的业务操作。如果这个业务操作被事务管理,则这多个不收要么同时成功,要么同是失败。
操作:
- 开启事务
- 提交事务
- 回滚事务
使用Connection对象来管理事务:
- 开启事务: setAutoCommit(boolean autoCommit):调用该方法设置参数为false,即开启事务
- 提交事务: commit()
- 回滚事务: rollback()
提前定义一个JDBCUtils工具类
/*** 事务操作*/
public class JdbcDemo6 {public static void main(String[] args) {Connection conn = null;PreparedStatement pstmt1 = null;PreparedStatement pstmt2 = null;try {//1. 获取连接conn = JDBCUtils.getConnection();//开启事务conn.setAutoCommit(false);//2. 定义sql 张三减500 李四加500String sql1 = "update account set balance=balance- ? where id= ?";String sql2 = "update account set balance=balance+ ? where id= ?";//3. 获取执行sql对象pstmt1 = conn.prepareStatement(sql1);pstmt2 = conn.prepareStatement(sql2);//4. 设置参数pstmt1.setDouble(1, 500);pstmt1.setDouble(2, 1);pstmt2.setDouble(1, 500);pstmt2.setDouble(2, 2);//5. 执行sqlpstmt1.executeUpdate();pstmt2.executeUpdate();//提交事务conn.commit();} catch (Exception throwables) {//事务回滚try {if (conn!=null){conn.rollback();}} catch (SQLException e) {e.printStackTrace();}throwables.printStackTrace();}finally {JDBCUtils.close(pstmt1,conn);JDBCUtils.close(pstmt2,null);}}
}
十一、数据库连接池
概念: 其实就是一个容器(集合),存放数据库连接的容器。
- 当系统初始化好后,容器被创建,容器中会申请一些连接对象,当用户来访问数据时,从容器中获取连接对象,用户访问完之后,会将连接对象归还给容器。
好处:
- 节约资源
- 用户访问高效
实现:
- 标准接口:DataSource javax.sql包下的
- 方法:
- 获取连接:getConnection()
- 归还连接:Connection.close(),如果连接对象Connection是从连接池中获取的,那么调用Connection.close()方法,则不会关闭连接了。而是归还连接
- 方法:
- 一般我们不去实现它,有数据库厂商来实现
- C3P0:数据库连接池技术
- Druid:数据库连接池技术,由阿里巴巴提供的
C3P0:数据库连接池技术
步骤:
- 导入jar包(两个)
- c3p0-0.9.5.2.jar
- mchange-commons-java-0.2.12.jar
- 不要忘记导入数据库的驱动jar包
- 定义配置文件:
- 名称:c3p0.properties 或者 c3p0-config.xml
- 路径:直接将文件放在src目录下即可
- 创建核心对象:数据库连接池对象 ComboPooledDataSource
- 获取连接:getConnection
配置文件:c3p0-config.xml
<c3p0-config><!-- 使用默认的配置读取连接池对象 --><default-config><!-- 连接参数 --><property name="driverClass">com.mysql.cj.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/db2</property><property name="user">root</property><property name="password">123456</property><!-- 连接池参数 --><!--初始化申请的连接数量--><property name="initialPoolSize">5</property><!--最大的连接数量--><property name="maxPoolSize">10</property><!--超时时间--><property name="checkoutTimeout">3000</property></default-config><named-config name="otherc3p0"> <!-- 连接参数 --><property name="driverClass">com.mysql.cj.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/db2</property><property name="user">root</property><property name="password">123456</property><!-- 连接池参数 --><property name="initialPoolSize">5</property><property name="maxPoolSize">8</property><property name="checkoutTimeout">1000</property></named-config>
</c3p0-config>
C3P0Demo1.java
public class C3P0Demo1 {public static void main(String[] args) throws SQLException {//1. 获取DataSource,使用默认配置
// DataSource ds = new ComboPooledDataSource();//1.1 获取DataSource,使用指定名称的配置DataSource ds = new ComboPooledDataSource("otherc3p0");//2. 获取连接对象Connection conn = ds.getConnection();//3. 打印System.out.println(conn);}
}
Dtuid:数据库连接池技术(阿里巴巴)
步骤:
- 导入jar包 druid-1.0.9.jar
- 定义配置文件:
- 是properties形式
- 可以叫任意名称,可以放在任意的目录下
- 加载配置文件 Propeoties
- 获取数据库连接对象:通过工厂类来获取,DruidDataSourceFactory
- 获取连接:getConnection
配置文件:druid.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db3
username=root
password=123456
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 虽大等待时间
maxWait=3000
DruidDemo1.java
/*** Druid演示*/
public class DruidDemo1 {public static void main(String[] args) throws Exception {//1. 导入jar包//2. 定义配置文件//3. 加载配置文件Properties pro = new Properties();InputStream inputStream = DruidDemo1.class.getClassLoader().getResourceAsStream("druid.properties");pro.load(inputStream);//4. 获取连接池对象DataSource ds = DruidDataSourceFactory.createDataSource(pro);//5. 获取连接Connection conn = ds.getConnection();System.out.println(conn);}
}
定义工具类:
- 定义工具类 JDBCUils
- 提供静态代码块加载配置文件,初始化连接池对象
- 提供方法
- 获取连接方法:通过数据库连接池获取连接
- 释放资源
- 获取连接池的方法
/*** Druid的工具类*/
public class JDBCUtils {//1. 定义成员变量 DataSourceprivate static DataSource ds;static {try {//1. 加载配置文件Properties pro = new Properties();pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));//2. 获取DataSourceds = DruidDataSourceFactory.createDataSource(pro);} catch (IOException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}}/*** 获取连接* @return* @throws SQLException*/public static Connection getConnection() throws SQLException {return ds.getConnection();}/*** 释放资源* @param stms* @param conn*/public static void close(Statement stms, Connection conn){
// if (stms!=null){
// try {
// stms.close();
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
// }
// if (conn!=null){
// try {
// conn.close();
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
// }close(null,stms,conn);}/*** 释放资源* @param rs* @param stms* @param conn*/public static void close(ResultSet rs, Statement stms, Connection conn){if (rs!=null){try {rs.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (stms!=null){try {stms.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (conn!=null){try {conn.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}/*** 获取连接池方法* @return*/public static DataSource getDataSource(){return ds;}
}
DruidDemo2.java
/*** 使用工具类*/
public class DruidDemo2 {public static void main(String[] args) {/*** 完成添加操作:给account表添加一条记录*/Connection conn = null;PreparedStatement pstmt = null;try {//1. 获取连接conn = JDBCUtils.getConnection();//2. 定义sqlString sql = "insert into account values(null,?,?)";//3. 获取pstmt对象pstmt = conn.prepareStatement(sql);//4.给 ? 赋值pstmt.setString(1,"赵六");pstmt.setDouble(2,3000);//5. 执行sqlpstmt.executeUpdate();} catch (SQLException throwables) {throwables.printStackTrace();}finally {JDBCUtils.close(pstmt,conn);}}
}
十二、Spring JDBC
Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
步骤:
- 导入jar包
- 创建JdbcTemplate对象。依赖于数据源DataSource
- JdbcTemplate template = new JdbcTemplate(ds);
- 调用JdbcTemplate的方法来完成CRUD的操作
- update():执行DML语句。增、删、该语句
- queryForMap():查询结果将结果集封装为map集合,将列名作为key,将值作为value 将这条记录封装为map
- 注意: 这个方法查询的结果集长度只能是1
- queryForList():查询结果将结果集封装为list集合
- 注意: 将每一条记录封装为一个Map集合,在将Map集合装载到List集合中
- query():查询结果,将结果封装为JavaBean对象
- 一般我们使用BeanPropertyRowMapper实现类,可以完成数据到JavaBean的自动封装
- new BeanPropertyRowMapper<类型> (类型.class)
- queryForObject:查询结果,将结果封装为对象
- 一般用于聚合函数的查询
/*** JdbcTemplate入门*/
public class JdbcTemplateDemo1 {public static void main(String[] args) {//1. 导入jar包//2. 创建JDBCTemplate对象JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());//3. 调用方法String sql = "update account set balance = 5000 where id = ?";int count = template.update(sql, 3);System.out.println(count);}
}
练习
需求:
- 修改1号数据的salary 为 10000
- 添加一条记录
- 删除刚才添加的记录
- 查询id为1的记录,将其封装为map集合
- 查询所有记录,将其封装为List集合
- 查询所有记录,将其封装为Emp对象的List集合
- 查询总记录数
注意:自己提前写好JDBCUtils工具类和数据库文件
public class JdbcTemplateDemo2 {//1. 获取JdbcTemplate对象private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());/*** 修改1001号数据的salary 为 10000*/@Testpublic void test1() {//2. SQL语句String sql = "update emp set salary=10000 where id=?";//3. 执行sqltemplate.update(sql, 1001);}/*** 添加一条记录*/@Testpublic void test2() {//2. 定义sqlString sql = "insert into emp(id,ename,dept_id) value(?,?,?)";//3. 执行sqltemplate.update(sql, 1015, "郭靖", 10);}/*** 删除刚才添加的记录*/@Testpublic void test3() {String sql = "delete from emp where id=?";template.update(sql, 1015);}/*** 查询id为1的记录,将其封装为map集合* 注意:这个方法查询的结果集长度只能是1*/@Testpublic void test4() {String sql = "select * from emp where id=?";Map<String, Object> map = template.queryForMap(sql, 1001);System.out.println(map);}/*** 查询所有记录,将其封装为List集合*/@Testpublic void test5() {String sql = "select * from emp";List<Map<String, Object>> list = template.queryForList(sql);for (Map<String, Object> m :list) {System.out.println(m);}}/*** 查询所有记录,将其封装为Emp对象的List集合*/@Testpublic void test6(){String sql = "select * from emp";List<Emp> list = template.query(sql, new RowMapper<Emp>() {@Overridepublic Emp mapRow(ResultSet rs, int i) throws SQLException {Emp emp = new Emp();emp.setId(rs.getInt("id"));emp.setEname(rs.getString("ename"));emp.setJob_id(rs.getInt("job_id"));emp.setMgr(rs.getInt("mgr"));emp.setJoindate(rs.getDate("joindate"));emp.setSalary(rs.getDouble("salary"));emp.setBonus(rs.getDouble("bonus"));emp.setDept_id(rs.getInt("dept_id"));return emp;}});for (Emp emp :list) {System.out.println(emp);}}/*** 查询所有记录,将其封装为Emp对象的List集合*/@Testpublic void test6_2(){String sql = "select * from emp";List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));for (Emp emp :list) {System.out.println(emp);}}/*** 查询总记录数*/@Testpublic void test7(){String sql = "select count(id) from emp";Long total = template.queryForObject(sql, Long.class);System.out.println(total);}
}
更多推荐
MySQL从入门到精通
发布评论