文章目录
MySQL补充与JDBC
1 行转列
1.1 什么是行转列
可是我想要的结果是这样…
通过SQL语句 实现这样的功能,就叫行转列
1.2 准备示例数据
有 id,名字,课程,成绩
create table test_9(
id int,
name varchar(22),
course varchar(22),
score decimal(18,2)
);
insert into test_9 (id,name,course,score)values(1,'小王','java',99);
insert into test_9 (id,name,course,score)values(2,'小张','java',89.2);
insert into test_9 (id,name,course,score)values(3,'小李','java',88);
insert into test_9 (id,name,course,score)values(4,'小王','MySQL',92.2);
insert into test_9 (id,name,course,score)values(5,'小张','MySQL',42.2);
insert into test_9 (id,name,course,score)values(6,'小李','MySQL',59.2);
1.3 场景一(多行转一列)
可以使用下面的SQL语句（group by 与 case when结合使用即可实现）：
select name,max(
case course
when 'java' then score
end) Java, max(
case course
when 'MySQL' then score
end) MySQL
from test_9
group by name;
1.4 场景二(多行转一行一列)
相关函数
concat(值,’拼接符’,值 ) : 拼接,多行数据只会拼接一行
group_concat(值,’拼接符’,值 ) : 拼接,多行压扁到一行
思路分析 :
-- 第一步：拆分问题，先按分组的思路
select name,1 as '各科成绩' from test_9 group by name;
-- 第二步：将课程名与成绩拼接成一列
select name,
concat(course,'=',score) as '各科成绩'
from test_9 group by name;
-- 第三步：利用group_concat函数将多行压扁到一行
select name,
group_concat(course,'=',score) as '各科成绩'
from test_9 group by name;
-- 第四步：修改分隔符（默认是逗号）
select name,
group_concat(course,'=',score separator ' | ') as '各科成绩'
from test_9 group by name;
-- 第五步：按课程名称排序
select name,
group_concat(course,'=',score order by course asc separator ' | ') as '各科成绩'
from test_9 group by name;
2 DQL-连接查询
2.1 笛卡尔积
create table teacher(
id int ,
name varchar(20),
primary key (id)
);
create table student (
id int ,
name varchar(20),
teacher_id int ,
primary key (id),
foreign key (teacher_id) references teacher(id)
);
insert into teacher (id,name) values(1,'张老师');
insert into teacher (id,name) values(2,'王老师');
insert into student (id,name,teacher_id) values(1,'小明',1);
insert into student (id,name) values(2,'小红');
insert into student (id,name,teacher_id) values(3,'小黑',2);
-- 笛卡尔积
select * from teacher,student;
2.2 内连接( inner join)
插入一条示例数据
INSERT INTO teacher (`id`, `name`) VALUES ('3', '孙老师');
1
select *
from teacher tea
inner join student stu on tea.id = stu.teacher_id;
2
select *
from student stu
inner join teacher tea on tea.id = stu.teacher_id;
使用内连接的话,会以左边表为基准(student),生成新视图的时候,先生成左边表中的数据,然后再去匹配右边表中是否有符合条件的,没有的话,就不生成这一行
多表查询是有左右表之分的,一般左表是主表,以左边为主
2.3 左外连接(left join)
1 查询结果,显示小红,但是不显示孙老师
select * from student s
left join teacher t on s.teacher_id = t.id;
2 查询结果显示孙老师,但是不显示小红
select * from teacher t
left join student s on s.teacher_id = t.id;
以左边的表为基准,左表中数据都有,右表中不符合条件的就没有,就在指定列上用null代替
生成视图的时候,也是先生成左表的数据
2.4 右外连接(right join)
1 查询结果,显示孙老师,但是不显示小红
select * from student s
right join teacher t on s.teacher_id = t.id;
2 查询结果显示小红,但是不显示孙老师
select * from teacher t
right join student s on s.teacher_id = t.id;
以右表为基准,右表中数据都有,左表中不符合条件的就没有,就在指定列上用null代替
但是视图生成的时候,还是会先生成左表数据
以上可以看出，student right join teacher 显示的内容是与teacher left join student相同的。而teacher right join student 显示的内容又与student left join student相同。
所以，我们工作中，right join基本不用。用的最多的是inner join 与left join。
2.5 模拟oracle中的全连接(full join)
使用 union 获取并集
select * from student s
left join teacher t on s.teacher_id = t.id
union
select * from student s
right join teacher t on s.teacher_id = t.id;
3 数据库导入和导出
3.1 使用Navicat数据导入和导出
3.1.1 导出
3.1.2 导入
3.2 MySQL语句导入和导出
3.2.1 导出
mysqldump -u用户名 -p密码 需要导出的数据库 > d:\tianliang.sql(导出的文件的保存位置)
-u用户名 -p密码 要备份的数据库, > 是把文件保存到哪里, < 是文件在哪里导进来
3.2.2 导入
还是进入到bin目录下
mysql -u用户名 -p密码 导入到哪个数据库< d:\tianliang.sql(被导入的文件路径)
-u用户名 -p密码 需要导入的数据库 需要导入的sql文件
4 JDBC介绍
JDBC : Java DataBase Connectivity (java数据库链接)
是让java链接数据库的API
API : Application Programming Intergace (应用程序接口)
就是函数库
4.1 使用步骤
4.1.1 导包
创建java项目
创建lib文件夹
把mysql-connector-java-5.1.38-bin.jar复制到lib中
右键 -> Build Path -> Add to Build Path
4.1.2 注册驱动(仅作一次)
Class.*forName*("com.mysql.jdbc.Driver");
4.1.3 建立连接(Connection)
第一个参数是url
jdbc:mysql://IP:端口/数据库/serverTimezone=GMT%2B8
第二个参数是数据库用户名
第三个参数是数据库密码
Connection connection = DriverManager
.getConnection(url, user, password);
4.1.4 创建运行SQL的语句
String sql = "select Address from IP";
4.1.5 获取引擎(Statement)
Statement st=connection .Statement();
4.1.6 运行语句
ResultSet rs = st.executeQuery();
4.1.7 处理结果
while (SystemParas.rs.next()) {
System.out.println(SystemParas.rs.getString("Address"));
}
4.1.8 释放资源
rs.close();
st.close();
connection .close();
5 代码优化
查询语句写错了等,这时候会抛出异常,那么关闭语句就不会执行
所以我们应该使用try…catch…finally来优化一下
以刚才的练习为例,对test_jdbc表的查询进行优化
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
6 DML
Data Manipulation Language : 数据操作语言
涉及的关键字有 : delete,update,insert
和查询的操作几乎一样,就是把第4步和第6步更改一下
String sql = "insert into test_jdbc (id,name,money) values (4,'小小',999.9)";
// sql = "update test_jdbc set money=money+1000 where id=1 ";
// sql = "delete from test_jdbc where id = 1";
// 如果是查询,就返回true,不是就返回false,价值不大,所以用的不多,添加,删除,更新都可以用这个方法
// stmt.execute(sql);
// 返回值是int,返回影响了几条数据(更改了几条/删除了几条/添加了几条),添加,删除,更新都可以用这个方法
int count = stmt.executeUpdate(sql);
7 PreparedStatement
PreparedStatement是预编译的SQL语句对象,sql语句被预编译并保存在对象中, 被封装的sql语句中可以使用动态包含的参数 ? ,在执行的时候,可以为?传递参数
7.1 DQL
//使用PreparedStatement 执行查询
public static void load(int id) {
Connection conn = null;
PreparedStatement prst = null;
ResultSet rs = null;
try {
// 1 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2 创建数据库连接对象
conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");
// 这里我们用? 问号代替值,可以叫占位符,也可以叫通配符
String sql = "select * from test_jdbc where id = ?";
// 3 语句传输对象
prst = conn.prepareStatement(sql);
// 设置第一个?的值
prst.setInt(1, id);
rs = prst.executeQuery();
while (rs.next()) {
System.out.print(rs.getInt("id") + " ");
System.out.print(rs.getString("name") + " ");
System.out.println(rs.getString("money") + " ");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// 关闭资源,从上到下依次关闭,后打开的先关闭
if (rs != null) {
rs.close();
}
if (prst != null) {
prst.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
7.2 DML
使用PreparedStatement 执行增删改,以添加为例
public static void add(int id, String name, double money) {
Connection conn = null;
PreparedStatement prst = null;
try {
// 1 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2 创建数据库连接对象
conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");
// 这里我们用? 问号代替值,可以叫占位符,也可以叫通配符
String sql = "insert into test_jdbc (id,name,money) values (?,?,?)";
// 3 语句传输对象
prst = conn.prepareStatement(sql);
// 设置第一个?的值
prst.setInt(1, id);
prst.setString(2, name);
prst.setDouble(3, money);
// 返回也是影响的条数
int count = prst.executeUpdate();
System.out.println("影响了 "+count+" 条数据");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// 关闭资源,从上到下依次关闭,后打开的先关闭
if (prst != null) {
prst.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
8 封装工具类
主要针对2个方面进行改进封装。
以下的代码我们每个方法中都会重复一次，这是没必要的，因为不同的方法其实只是具体执行的SQL语句的内容不同，对于获取连接与释放资源，对应的逻辑是相同的。我们完全可以把这一段逻辑抽取出来，形成独立的类与方法，再在实际应用时调用对应的类和方法就可以了。
1 创建连接这些
2 关闭资源这些
//创建链接这些可以这样进行优化
public static Connection getConnection() throws ClassNotFoundException,
SQLException {
String username = "root";
String password = "root";
String url = "jdbc:mysql://127.0.0.1:3306/_06_";
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, username,
password);
return connection;
}
//关闭资源这些可以这样进行优化
//因为Connection和Statement/PreparedStatement以及ResultSet都实现了AutoCloseable接口
//所以我们可以直接写AutoCloseable
public static void close(AutoCloseable obj) {
if (obj != null) {
try {
obj.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
9 Batch多语句操作
9.1 Statement实现
Connection conn = null;
Statement stmt = null;
try {
conn = DBUtil.getConnection();
stmt = conn.createStatement();
stmt.addBatch("insert into test_jdbc (id,name,money) values(21,'stmt多条测试1',99.12)");
stmt.addBatch("insert into test_jdbc (id,name,money) values(22,'stmt多条测试2',99.22)");
stmt.addBatch("insert into test_jdbc (id,name,money) values(23,'stmt多条测试3',99.32)");
stmt.addBatch("insert into test_jdbc (id,name,money) values(24,'stmt多条测试4',99.42)");
stmt.executeBatch();
System.out.println("执行成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(stmt);
DBUtil.close(conn);
}
9.2 PreparedStatement实现
Connection conn = null;
PreparedStatement prst = null;
try {
conn = DBUtil.getConnection();
String sql = "insert into test_jdbc (id,name,money) values(?,?,?)";
prst = conn.prepareStatement(sql);
prst.setInt(1, 31);
prst.setString(2, "prst多条测试1");
prst.setDouble(3, 11.1);
prst.addBatch();
prst.setInt(1, 32);
prst.setString(2, "prst多条测试2");
prst.setDouble(3, 21.1);
prst.addBatch();
prst.setInt(1, 33);
prst.setString(2, "prst多条测试3");
prst.setDouble(3, 31.1);
prst.addBatch();
prst.executeBatch();
System.out.println("执行成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(prst);
DBUtil.close(conn);
}
扫一扫
850
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
