MySQL从入门到精通

精选 专栏收录该内容
10 篇文章 0 订阅

一、配置

MySQL服务启动

  1. 手动
  2. cmd --> services.msc 打开服务的窗口
  3. 使用管理员打开cmd
    • net start mysql:启动mysql的服务
    • net stop mysql:关闭mysql服务

MySQL登陆

  1. mysql -u用户名 -p密码
  2. mysql -hip -u连接目标的用户 -p对应的密码

MySQL退出

  1. exit
  2. quit

二、SQL

1.什么是SQL?

​ Structured Query Language:结构化查询语言

​ 其实就是定义了所有关系型数据可的规则。每种数据库操作的方式存在不一样的地方,称为“方言”

2.SQL通用语法

  • SQL 语句可以单行或多行书写,以分号结尾
  • 可使用空格和缩进来增强语句的可读性
  • MySQL数据库的SQL语句不区分大小写,关键字建议用大写

3.SQL分类

  1. DDL(Data Definition Language)数据定义语言

    用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等

  2. DML(Data Manipulation Language)数据库操作语言

    用来对数据库中表的数据进行增删改查。关键字:insert,delete,uodate等

  3. DQL(Data Query Language)数据查询语言

    用来查询数据库中表的记录(数据)。关键字:select,where等

  4. 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个字符
    • 创建表

      • 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,	#线路id
	date 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, -- 部门id
  dname 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, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr 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.查询员工姓名,工资,工资等级
SELECT
	t1.ename,
	t1.salary,
	t2.grade
FROM
	emp t1,
	salarygrade t2
WHERE
	t1.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
FROM
	dept t1,
	(SELECT
		dept_id,COUNT(id) renshu
	FROM
		emp
	GROUP BY
		dept_id) t2
WHERE
	t1.id=t2.dept_id;
 
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
/*
	1.姓名emp,直接上级的姓名emp
		emp表的id和mgr是自关联
	2.条件emp .id =emp.mgr
	3.查询左表的所有数据,和交集数据
		使用左外连接查询
*/
SELECT
	t1.ename,
	t1.mgr,
	t2.id,
	t2.ename
FROM
	emp t1
LEFT JOIN
	emp t2
ON
	t1.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的对象,Statement
              Statement stmt = conn.createStatement();
              //6. 执行sql
              int 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:密码

Connection:数据库连接对象

  • 功能:
    • 获取执行sql对象:
      • Statement createStatement()
      • PreparedStetement prepareStatement(String sql)
    • 管理事务:
      • 开启事务:setAutoCommit(boolean autoCommit):调用该方法设置参数为false,即为开启事务
      • 提交事务:commit()
      • 回滚事务:rollback()

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. 定义sql
                  String sql = "insert into account values(null,'王五',3000)";
                  //3. 获取Connection对象
                  conne = DriverManager.getConnection("jdbc:mysql:///db3", "root", "123456");
                  //4. 获取执行sql的对象 Statement
                  stmt = conne.createStatement();
                  //5. 执行sql
                  int 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. 定义sql
            String sql = "select * from emp";
            //4. 获取执行sql的对象
            stmt = conne.createStatement();
            //5.执行sql
            rs = 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: ? 的值
  • 执行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. 定义sql
            String sql = "select * from emp";
            //4. 获取执行sql的对象
            stmt = conne.createStatement();
            //5.执行sql
            rs = 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  李四加500
            String 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. 执行sql
            pstmt1.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. 定义成员变量 DataSource
    private static DataSource ds;

    static {

        try {
            //1. 加载配置文件
            Properties pro = new Properties();
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            //2. 获取DataSource
            ds = 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. 定义sql
            String sql = "insert into account values(null,?,?)";
            //3. 获取pstmt对象
            pstmt = conn.prepareStatement(sql);
            //4.给 ? 赋值
            pstmt.setString(1,"赵六");
            pstmt.setDouble(2,3000);
            //5. 执行sql
            pstmt.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
     */
    @Test
    public void test1() {

        //2. SQL语句
        String sql = "update emp set salary=10000 where id=?";
        //3. 执行sql
        template.update(sql, 1001);
    }

    /**
     * 添加一条记录
     */
    @Test
    public void test2() {
        //2. 定义sql
        String sql = "insert into emp(id,ename,dept_id) value(?,?,?)";
        //3. 执行sql
        template.update(sql, 1015, "郭靖", 10);
    }

    /**
     * 删除刚才添加的记录
     */
    @Test
    public void test3() {
        String sql = "delete from emp where id=?";
        template.update(sql, 1015);
    }

    /**
     * 查询id为1的记录,将其封装为map集合
     * 注意:这个方法查询的结果集长度只能是1
     */
    @Test
    public void test4() {
        String sql = "select * from emp where id=?";
        Map<String, Object> map = template.queryForMap(sql, 1001);
        System.out.println(map);
    }

    /**
     * 查询所有记录,将其封装为List集合
     */
    @Test
    public 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集合
     */
    @Test
    public void test6(){
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new RowMapper<Emp>() {
            @Override
            public 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集合
     */
    @Test
    public 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);
        }
    }

    /**
     * 查询总记录数
     */
    @Test
    public void test7(){
        String sql = "select count(id) from emp";
        Long total = template.queryForObject(sql, Long.class);
        System.out.println(total);
    }
}
  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值