javaWeb-day03-MySQL(三). JDBC(一)

最新推荐文章于 2021-08-20 17:31:16 发布

重庆森林呼呼呼

最新推荐文章于 2021-08-20 17:31:16 发布

阅读量262

点赞数

本文链接：https://blog.csdn.net/qq_34681728/article/details/105884425

版权

子查询

准备数据：

-- 删除db40库(如果存在)
drop database if exists db40;
-- 重新创建db40库
create database db40 charset utf8;
-- 选择db40库
use db40;
-- 创建部门表
create table dept(    -- 创建部门表
 id int primary key,   -- 部门编号
 name varchar(50),   -- 部门名称
 loc varchar(50)    -- 部门位置
);
-- 创建员工表
create table emp(    -- 创建员工表
 id int primary key,   -- 员工编号
 name varchar(50),   -- 员工姓名
 job varchar(50),   -- 职位
 topid int,     -- 直属上级
 hdate date,     -- 受雇日期
 sal int,     -- 薪资
 bonus int,     -- 奖金
 dept_id int,    -- 所在部门编号
 foreign key(dept_id) references dept(id)
);
-- 往部门表中插入记录
insert into dept values ('10', '培优部', '北京');
insert into dept values ('20', '就业部', '上海');
insert into dept values ('30', '大数据部', '广州');
insert into dept values ('40', '销售部', '深圳');
-- 往员工表中插入记录
insert into emp values ('1001', '王克晶', '办事员', '1007', '1980-12-17', '800', 500, '20');
insert into emp values ('1003', '齐雷', '分析员', '1011', '1981-02-20', '1900', '300', '10');
insert into emp values ('1005', '王海涛', '推销员', '1011', '1981-02-22', '2450', '600', '10');
insert into emp values ('1007', '刘苍松', '经理', '1017', '1981-04-02', '3675', 700, '20');
insert into emp values ('1009', '张慎政', '推销员', '1011', '1981-09-28', '1250', '1400', '10');
insert into emp values ('1011', '陈子枢', '经理', '1017', '1981-05-01', '3450', 400, '10');
insert into emp values ('1013', '张久军', '办事员', '1011', '1981-06-09', '1250', 800, '10');
insert into emp values ('1015', '程祖红', '分析员', '1007', '1987-04-19', '3000', 1000, '20');
insert into emp values ('1017', '韩少云', '董事长', null, '1981-11-17', '5000', null, null);
insert into emp values ('1019', '刘沛霞', '推销员', '1011', '1981-09-08', '1500', 500, '10');
insert into emp values ('1021', '范传奇', '办事员', '1007', '1987-05-23', '1100', 1000, '20');
insert into emp values ('1023', '赵栋', '经理', '1017', '1981-12-03', '950', null, '30');
insert into emp values ('1025', '朴乾', '分析员', '1023', '1981-12-03', '3000', 600, '30');
insert into emp values ('1027', '叶尚青', '办事员', '1023', '1982-01-23', '1300', 400, '30');
-- ------------------- 执行完毕 -----------------------

45.列出薪资比’王海涛’的薪资高的所有员工，显示姓名、薪资

--求出'王海涛'的薪资
select sal from emp where name='王海涛'; --2450
--列出比'王海涛'薪资还高的员工
select name,sal from emp 
where sal >(select sal from emp where name='王海涛');

46.列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位。

--求出'刘沛霞'的职位
select name,job from emp where name ='刘沛霞';
--求出和'刘沛霞'从事相同职位的员工
select name,job from emp
where job=(select job from emp where name='刘沛霞');

47.列出薪资比’大数据部’部门(已知部门编号为30)所有员工薪资都高的员工信息，显示员工姓名、薪资和部门名称
如果不考虑没有部门的员工

-- 1、连接查询员工和部门
select e.name,e.sal,d.name from  dept d，emp e
where e.dept_id=d.id;
-- 2、求出大数据部门的最高薪资(查询员工表中部门编号为30的最高薪资)
select max(sal) from emp where dept_id=30;
-- 3、求出比大数据部门最高薪资还高的员工信息
select e.name,e.sal,d.name from dept d , emp e
where e.dept_id=d.id 
and sal>(select max(sal) from emp where dept_id=30);

如果加上没有部门的员工

-- 1、用左外连接查询所有员工和对应的部门
select e.name,e.sal,d.name from dept d right join emp e
on e.dept_id=d.id;
-- 2、求出大数据部门的最高薪资
select max(sal) from emp where e.dept_id=30;
-- 3、求出比大数据部门最高薪资还高的员工信息
select e.name,e.sal,d.name from dept d right join emp e
on e.dept_id=d.id
where sal >(select max(sal) from emp where dept_id=30);

多表查询

48.列出在’培优部’任职的员工，假定不知道’培优部’的部门编号，显示部门名称，员工名称。

-- 关联查询两张表
select d.name,e.name from dept d,emp e
where e.dept_id=d.id;
-- 求出在培优部的员工
select d.name,e.name from dept d,emp e
where e.dept_id=d.id and d.name='培优部';

49.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名

/* emp e1 员工表，emp e2 上级表
* 查询的表：emp e1,emp e2 
* 显示的列：e1.name,e2.id,e2.name
* 连接条件： e1.topid=e2.id
*/
select e1.name,e1.topid,e2.name 
from emp e1 ,emp e2 
where e1.topid=e2.id;

50.列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资

--列出每个职位的最低薪资
select job,min(sal) from emp group by job;
--求出最低薪资大于1500的薪资
select job,min(sal) from emp group by job
having min(sal) >1500;

补充内容：where和having子句的区别:
(1)相同点: where和having都可以对记录进行筛选过滤。
(2)区别：where是在分组之前,对记录进行筛选过滤,并且where子句中不能使用多行函数以及列别名(但是可以使用表别名)
(3)区别：having是在分组之后,对记录进行筛选过滤,并且having子句中可以使用多行函数以及列别名、表别名。
51.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资

--根据部门对员工进行分组，统计每个组(部门)的人数和平均工资
select dept_id,count(*),avg(sal) from emp group by dept_id;

52.查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数

--连接查询部门表和员工表
select d.id,d.name,d.loc from dept d,emp e 
where d.id=e.dept_id;
--按照部门分组
select d.id,d.name,d.loc,count(*) from dept d,emp e 
where d.id=e.dept_id
group by d.id
having count(*)>=1;

53.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称

select e1.id,e1.name,d.name
from emp e1,emp e2,dept d
where e1.topid=e2.id and e1.dept_id=d.id
      and e1.hdate<e2.hdate;

where中不能使用列别名

SQL语句的书写顺序:

select * | 列名 -- 确定要查询的列有哪些
from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤，剔除不符合条件的记录
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount -- 指定返回第几页记录以及每页显示多少条

SQL语句的执行顺序:

from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤，剔除不符合条件的记录
select * | 列名 列别名 -- 确定要查询的列有哪些，
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount

关于where中不能使用列别名但是可以使用表别名?
是因为，表别名是声明在from中，from先于where执行，先声明再使用没有问题，但是列别名是声明在select中，where先于select执行，如果先使用列别名，再声明，这样执行会报错!!

数据库备份与恢复

备份数据库

在cmd窗口中(未登录的状态下)，可以通过如下命令对指定的数据库进行备份

mysqldump -u用户名 -p 数据库的名字 > 备份文件的位置

示例1: 对db40库中的数据(表，表记录)进行备份，备份到 d:/db40.sql文件中

mysqldump -uroot -p db40 > d:/db40.sql

键入密码，如果没有提示，即表示备份成功！
也可以一次性备份所有库，例如：对 MySQL 服务器中所有的数据库进行备份，备份到 d:/all.sql文件中

mysqldump -uroot -p --all-database > d:/all.sql

键入密码，如果没有提示错误(警告信息不是错误，可以忽略)，即表示备份成功

恢复数据库

1、恢复数据库方式一
在cmd窗口中(未登录的状态下)，可以通过如下命令对指定的数据库进行恢复：

mysql -u用户名 -p 数据库的名字 < 备份文件的位置

示例：将d:/db40.sql文件中的数据恢复到db60库中
在cmd窗口中（已登录的状态下），先创建db60库：

create database db60 charset utf8;

在cmd窗口中（未登录的状态下）

mysql -uroot -p db60 < d:/db40.sql

2、恢复数据库方式二：
在cmd窗口中(已登录的状态下)，可以通过source执行指定位置的SQL文件：

source sql文件的位置

示例：将d:/db40.sql文件中的数据恢复到db80库中
在cmd窗口中（已登录的状态下），先创建db80库，进入db80库：

create database db80 charset utf8;
use db80;

再通过source执行指定位置下的sql文件：

source d:/db40.sql

JDBC

JDBC(Java Database Connectivity) Java数据库连接
其实就是利用Java语言/程序连接并访问数据库的一门技术

如何通过JDBC程序访问数据库?

创建一个 jt_db 数据库，在库中创建一个account表，并插入三条记录，然后利用Java程序查询出account表中所有的记录，并将查询的结果打印在控制台上
(1)准备数据, 创建jt_db库, 创建account表

drop database if exists jt_db;
create database jt_db charset utf8;
use jt_db;
create table account(
    id int primary key auto_increment,
    name varchar(50),
    money double
);
insert into account values(null, 'tom', 1000);
insert into account values(null, 'andy', 1000);
insert into account values(null, 'tony', 1000);

(2)创建JAVA工程
(3)导入jar包——MySQL 驱动包：
在这里插入图片描述
代码实现：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//查询jt_db库中account表中所有记录
public class Test1Jdbc01 {
    public static void main(String[] args) throws Exception {
        //1.注册数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库连接
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8",
                "root","root");
        //3.获取传输器
        Statement statement = connection.createStatement();
        //4.基于传输器发送SQL语句到服务器执行，返回执行结果
        String sql = "select * from account";
        ResultSet resultSet = statement.executeQuery(sql);
        //5.处理结果（打印到控制台）
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            double money = resultSet.getDouble("money");
            System.out.println(id+":"+name+":"+money);
        }
        //6.释放资源
        connection.close();
        statement.close();
        resultSet.close();
    }
}

异常处理后版本：

import java.sql.*;
//查询jt_db库中account表中所有记录
public class Test1Jdbc02 {
    public static void main(String[] args)  {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
       try{
           //1.注册数据库驱动
           Class.forName("com.mysql.jdbc.Driver");
           //2.获取数据库连接
           connection = DriverManager.getConnection(
                   "jdbc:mysql:///jt_db?characterEncoding=utf-8",
                   "root","root");
           //3.获取传输器
           statement = connection.createStatement();
           //4.基于传输器发送SQL语句到服务器执行，返回执行结果
           String sql = "select * from account";
           resultSet = statement.executeQuery(sql);
           //5.处理结果（打印到控制台）
           while (resultSet.next()){
               int id = resultSet.getInt("id");
               String name = resultSet.getString("name");
               double money = resultSet.getDouble("money");
               System.out.println(id+":"+name+":"+money);
           }
       }catch (Exception e){
           System.out.println("查询失败");
           e.printStackTrace();
       }finally {
           //6.释放资源
            if(resultSet!=null){
                try {
                    resultSet.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
           if(statement!=null){
               try {
                   statement.close();
               }catch (SQLException e){
                   e.printStackTrace();
               }
           }
           if(connection!=null){
               try {
                   connection.close();
               }catch (SQLException e){
                   e.printStackTrace();
               }
           }
       }
    }
}

重庆森林呼呼呼

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
javaWeb-day03-MySQL(三). JDBC(一)

子查询准备数据：-- 删除db40库(如果存在)drop database if exists db40;-- 重新创建db40库create database db40 charset utf8;-- 选择db40库use db40;-- 创建部门表create table dept( -- 创建部门表 id int primary key, -- 部门编号 na...
复制链接

扫一扫