入门到精通"/>

MySQL从入门到精通

一、配置

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,	#线路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：密码

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. 定义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： ? 的值
• 执行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);}
}