精华内容
下载资源
问答
  • 背景:项目中需要从oracle中读取表字段并以指定间隔符拼接字段,输出到txt。每张表的数据量大概在2000W左右,因为是只需要读一张表指定字段的全部数据,也不需要考虑查询优化,只针对表读取规模进行优化即可。 版本...

    java从oracle中读取大量数据写出到文件

    背景:项目中需要从oracle中读取表字段并以指定间隔符拼接字段,输出到txt。每张表的数据量大概在2000W左右,因为是只需要读一张表指定字段的全部数据,也不需要考虑查询优化,只针对表读取规模进行优化即可。

    版本oracle 11G

    思路一:

    使用oracle语句对数据表进行分页读取,这里应注意到的是rowid效率和rownum效率问题,还有使不使用order by

    
    SELECT t.*
      FROM a t, (SELECT ROWNUM rn, c.*
                      FROM (SELECT   ROWID k
                                FROM a
                            ORDER BY ID) c) b
    WHERE t.ROWID = b.k AND b.rn BETWEEN 10001 AND 20000;
    

    有一篇对比文章,大家可以看一下 http://www.itpub.net/thread-1603830-1-1.html

    这个思路对我的需求来说也不太适合,分页读取确实缩短了查询时间,但是对于上千万的大表来说,越往后分页花的时间越长,整体来说效率提升不多大。

    思路二:

    在写数据时,使用多线程,提升写的效率,测试证明,我的效率瓶颈是在读取数据方面,不是写数据方面。

    思路三:

    使用ResultSet对结果集进行分批读取,开始的时候没想到ResultSet直接支持了分批读取,在前面两个思路上花费了不少时间。

    先讲解下再贴代码
    ResultSet的常见用法:点击跳转查看

    大多数文章知识讲解了常见方法及参数的设定,ResultSet的setFetchSize()setMaxRows() 缺很少有人提及

    简单来说
    setFetchSize()  :是设置ResultSet每次向数据库取的行数 
    
    例如:rs.setFetchSize(100),ResultSet每次向数据库读取100条数据,
    之后下一百条数据的读取是在ResultSet内部完成的,不需要手动去进行调用或定位数据从哪行开始。
    
    setMaxRows() :是设置Resultset最多返回的行数,不需要读取全部数据,只要特定行数的数据,可以选择此方法。
    

    有篇文章里分析的还算可以:
    JDBC读取数据优化setFetchSize
    JDBC读取数据优化-fetch size

    另外如果设置的

    stmt = destCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    

    可能会报这个错
    在这里插入图片描述

    这时需要把 
    ResultSet.TYPE_SCROLL_SENSITIVE
    改为
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    

    下面贴上我的代码

    
    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
    
            String selsql;
            Connection destCon = null;
            Statement stmt = null;
            BufferedWriter output = null;
            long rowCount = 0L;
            int colCounts = 0;
            ResultSet res = null;
    
            long flen = 0L;
            selsql = "select RANDOM_STRING from myTestTable";
            destCon = getConnection();
            int fileCount = 1;
            EtlRuler etlRuler = new EtlRuler();
            etlRuler.setLocal_path("E:\\web_project\\");
            etlRuler.setFile_name("test010.txt");
            String filePath = etlRuler.getLocal_path() + etlRuler.getFile_name().replace("${NUM}", "00" + fileCount);
            etlRuler.getDataPath().add(filePath);
    
    
            File file = new File(etlRuler.getDataPath().get(fileCount - 1));
            if (!file.exists()) {
                file.createNewFile();
            }
            StringBuilder line = new StringBuilder();
            output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, false), StandardCharsets.UTF_8));
    
            stmt = destCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            res = stmt.executeQuery(selsql);
            res.setFetchSize(2000);
    
    
    
            if (res == null || !res.next()) {
                System.out.println("数据文件sql无数据!");
                // throw new Exception("数据文件sql无数据!");
            }
            assert res != null;
            res.previous();
    
            // 获取字段元信息
            ResultSetMetaData rsmd1 = res.getMetaData();
            colCounts = rsmd1.getColumnCount();
            int j = 0;
            String str = "";
            while (res.next()) {
                //System.out.println("开始读取数据" + rowCount++);
                // 打印进度
                rowCount++;
                if (rowCount % 2000 == 0) {
                    Date date = new Date();
                    //20w条数据的时候写入一下,之后清空StringBuilder,重新添加数据
                    System.out.println("执行时间:" + date);
                    System.out.println(rowCount + " ----rows proceed");
    //                output.write(line.toString());
    //                output.flush();
    //                line.delete(0, line.length());
                }
    
                for (int i = 1; i <= colCounts; i++) {
                    //line.append(res.getString(i)).append("\n");
                    str = res.getString(i)+"\n";
                }
                output.write(str);
                output.flush();
                str = "";
    
                //System.out.println("开始写入数据");
    
    
                if (file.length() > (1024 * 500)) {
                    if (etlRuler.getFile_name().contains("${NUM}")) {
                        //output.write(line.toString());
                        //output.flush();
                        //line.delete(0,line.length());
                        fileCount++;
                        System.out.println("创建新文件");
                        String newfilePath = etlRuler.getLocal_path() + etlRuler.getFile_name().replace("${NUM}", "00" + fileCount);
                        etlRuler.getDataPath().add(newfilePath);
                        file = new File(etlRuler.getDataPath().get(fileCount - 1));
                        if (!file.exists()) {
                            file.createNewFile();
                        }
                        output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, false),
                                StandardCharsets.UTF_8));
                    }
                }
            }
    
            //System.out.println("开始写入数据");
            output.write(line.toString());
            output.flush();
            output.close();
            flen = file.length();
            System.out.println("文件大小:" + flen);
        }
    
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = null;
            con = DriverManager.getConnection(
                    "jdbc:oracle:thin:@" + ip + ":" + port + ":" + sid, user,
                    password);  //设置数据库连接字符串
            return con;
        }
    
    展开全文
  • 成员变量 —》表字段 对象 —》对应一条记录 SQL的概念 结构化查询语言,用于存取数据以及查询、更新和管理关系的数据库系统 SQL通用语法 SQL语句可以单行或多行书写,以分号;结尾 Mysql自带数据库 ...

    模块一 Mysql基础和SQL入门

    • 常见数据库:

      • Mysql数据库(开源免费)
      • Oracle数据库(收费,安全性高,占有量排行第一)
      • DB2
      • SQL Server(只能运行在Windows上,使用比较少)
    • Java类对应Mysql一张表

      • 类 —》表
      • 成员变量 —》表字段
      • 对象 —》对应一条记录

    SQL的概念

    • 结构化查询语言,用于存取数据以及查询、更新和管理关系的数据库系统

    SQL通用语法

    • SQL语句可以单行或多行书写,以分号;结尾

    Mysql自带数据库

    • information_schema (信息数据库 保存的是其他数据库的信息)
    • mysql (Mysql核心数据库,保存的是用户和权限信息)
    • performance_schema (保存性能相关数据 监控Mysql的性能)
    • sys (记录了DBA所需要的一些信息,更方便的让DBA快速了解数据库的运行情况)

    创建数据库

    -- 创建数据库
    CREATE DATABASE db1;
    
    -- 修改db1字符集为utf8
    ALTER DATABASE db1 CHARACTER SET utf8;
    
    -- 查询当前数据库的基本信息
    SHOW CREATE DATABASE db1;
    
    -- 删除数据库
    DROP DATABASE db1;
    

    创建表

    建表语法

    CREATE TABLE category(
    	cid INT,  -- 变量名在前,数据类型在后
    	cname VARCHAR(20)
    );
    

    常见的数据类型

    • int(整型)
    • double(浮点型)
    • varchar(字符串类型)
    • date(日期类型,只显示年月日 yyyy-MM-dd)
    • datetime(年月日时分秒 yyyy-MM-dd HH:mm:ss)
    • char(在Mysql中也代表字符串)

    char和varchar区别

    • varchar类型特点:可变长度,存储字符串时,只使用所需的空间
    • char类型特点:固定长度,创建时就指定了长度

    复制表

    -- create table 新表名称 like 旧表名称
    
    create table test1 like test2;
    
    -- 查看表结构
    desc test2;
    
    -- 查看建表语句
    SHOW CREATE TABLE category;
    

    删除表

    • 两种方式
    drop table 表名;  -- 从数据库中永久删除一张表
    
    drop table if exist 表名; -- 判断表是否存在,如果存在就删除,不存在就不删除
    

    修改表

    修改表名称

    rename table 旧表名 to 新表名;
    

    修改表字符集

    -- 修改表的字符集为gbk
    alter table 表名 character set 字符集
    

    新增一个字段

    -- 关键字 add,新增加一列
    alter table 表名 add 字段名 字段类型;
    
    alter table category1 add cdesc varchar(10);
    

    修改列的类型

    -- 关键字 modify
    alter table 表名 modify 字段名称 字段类型;
    
    alter table category1 modify cdesc varchar(50);
    

    修改列的名称

    -- 关键字 change
    alter table 表名 change 旧列名 新列明 新列明类型(长度);
    
    alter table category1 change cdesc adesc int;
    

    删除列

    alter table 表名 drop 列名;
    
    alter table category1 drop adesc;
    

    DML对表进行增删改

    增加

    insert into 表名 (字段名1,字段名2...) values (字段值1,字段值2...);
    
    • 值与字段必须对应,个数、数据类型、长度 都必须一致
    • 在插入varchar char date类型的时候,必须要使用单引号或者双引号进行包裹
    • 如果插入空值 可以忽略不写或者写null

    修改

    • 无条件
    -- 不带条件,更新全部数据
    update 表名 set 列名 = 值;
    
    • 有条件
    -- 条件更新
    update 表名 set 列名 = 值 where 条件表达式(字段名 = 值);
    

    删除

    • 无条件,删除全部数据
    -- 不推荐,对表中的数据进行逐条删除,效率低
    delete from 表名;
    
    -- 删除整张表,然后再创建一个一模一样的新表,效率高推荐使用
    truncate table 表名;
    
    • 有条件
    delete from 表名 where 条件表达式;
    

    查询

    • 查询全部字段
    select * from 表名;
    
    • 查询所有数据 ,只显示id和name
    select id,name from 表名;
    
    • 去重操作
    select distinct dept_name from emp 
    
    • 查询操作不会对数据表中的数据进行修改,只是一种展现方式

    条件查询

    • like ‘%%’
    • % 通配符,表示匹配任意多个字符串
    -- 查询包含‘精’字的所有员工
    select * from emp where ename like '%精%;
    
    • _ 通配符,表示匹配一个字符
    -- 查询第三个字是‘精’字的员工
    select * from emp where ename like '_精';
    
    • 查询是否为null,只能用is null,不能用=null
    -- 查询没有部门信息的所有员工
    select * from emp where dept_name is null;
    -- 查询有部门信息的所有员工
    select * from emp where dept_name is not null;
    

    DQL

    排序

    • order by 语句,对查询的数据进行排序
    • 默认升序排序ASC
    • 降序排序DESC

    单列排序

    -- 对员工表的salary字段从小到大进行排序,默认升序
    select * from emp order by salary;
    -- 对员工表的salary字段从大到小进行排序
    select * from emp order by salary desc;
    

    组合排序

    • 同时对多个字段进行排序
    • 如果第一个字段相同,就对第二个字段进行排序
    -- 在salary排序的基础上再对id进行排序
    select * from emp order by salary desc ,eid desc;
    

    聚合函数

    -- 语法格式
    select 聚合函数 from 表名 (where 条件表达式);
    
    • count(字段)统计记录数
    • count()函数在统计时会忽略空值,不要使用带空值的列查询
    -- 查询emp表中的总记录数
    select count(*) from emp;
    -- 和count(*)相同
    select count(1) from emp;
    -- 查询条数缺失值为null的记录
    select count(dept_name) from emp;
    
    • sum(字段)求和操作
    -- 查询员工总薪水
    select sum(salary) from emp;
    
    • max(字段)求最大值
    -- 查询员工最高薪水
    select max(salary) from emp;
    
    • min(字段)求最小值
    -- 查询员工最小薪水
    select min(salary) from emp;
    
    • avg(字段)求平均值
    -- 查询员工平均薪水
    select avg(salary) from emp;
    

    分组查询

    • 通过group by语句
    -- 语法格式
    select 分组字段/聚合函数 from 表名 group by 分组字段;
    
    -- emp表中按照姓名进行分组
    select * from emp group by sex;
    
    • group by分组的过程
      • 第一步:将字段相同的数据分为一组
      • 第二步:将每组的第一条数据返回
    • 分组的目的就是为了做统计操作,一般分组会和聚合函数一起使用
    • 另外,查询的时候要查询分组字段
    -- 查询emp表中平均工资大于6000的所有部门
    SELECT
    dept_name , AVG(salary) 
    FROM emp WHERE dept_name IS NOT NULL 
    GROUP BY dept_name 
    HAVING AVG(salary) > 6000 ;
    

    where和having区别

    • where在分组前过滤条件,且后面不可以接聚合函数
    • having在分组后过滤条件,后面可以接聚合函数

    limit关键字

    • 通过limit指定要查询的条数/行数
    -- 语法格式
    select 字段名 from 表名 limit offset,length;
    
    • offset:起始行数,默认从0开始计数
    • length:要查询的行数
    -- 查询emp表中的前5条数据
    select * from emp limit 0,5;
    select * from emp limit 5;
    
    -- 查询emp表中从第4条开始,查询6条
    select * from emp limit 3,6;
    
    • limit分页操作,每页显示3条
    select * from emp limit 0,3; -- 第一页
    select * from emp limit 3,3; -- 第二页
    select * from emp limit 6,3; -- 第三页
    
    • 分页公式:起始行数 =(当前页码 - 1)* 每页显示条数

    约束

    • 约束是指对数据进行一定的限制,来保证数据的完整性、有效性、正确性
    • 常见的约束
      • 主键约束:primary key
      • 唯一约束:unique
      • 非空约束:not null
      • 外键约束:foreign key

    主键约束(primary key)

    • 特点:不可重复、唯一、非空
    • 作用:用来表示数据库中的每一条记录
    -- 语法格式(建表用)
    字段名 字段类型 primary key;
    
    • 方式1:
    CREATE TABLE emp2(
    	eid INT PRIMARY KEY,
    	ename VARCHAR(20),
    	sex char(1)
    );
    
    • 方式2:
    CREATE TABLE emp2(
    	eid INT,
    	ename VARCHAR(20),
    	sex char(1),
    	PRIMARY KEY(eid) -- 指定eid为主键
    );
    
    • 方式3:
    CREATE TABLE emp2(
    	eid INT,
    	ename VARCHAR(20),
    	sex char(1)
    );
    ALTER TABLE emp2 ADD PRIMARY KEY(eid); -- 后续补充primary key
    

    主键自增

    • 关键字:auto_increment
    • 要求字段类型必须为整数类型
    CREATE TABLE emp2(
    	eid INT PRIMARY KEY auto_increment,
    	ename VARCHAR(20),
    	sex char(1)
    );
    
    • 修改自增起始值
    CREATE TABLE emp2(
    	eid INT PRIMARY KEY auto_increment,
    	ename VARCHAR(20),
    	sex char(1)
    )auto_increment = 100;  -- 自增从100开始
    
    • delete和truncate对自增的影响
      • delete是对表中的数据进行逐条删除
      • truncate是将整个表删除,再创建一个结构相同的表
      • delete删除后,再次插入数据,数据再次自增,对自增没有影响
      • truncate删除后,自增从1开始

    非空约束(not null)

    • 某一列不允许为空
    CREATE TABLE emp2(
    	eid INT PRIMARY KEY auto_increment,
    	ename VARCHAR(20) NOT NULL, -- 非空约束
    	sex char(1)
    )auto_increment = 100;
    

    唯一约束(unique key)

    • 表中的某一列的值不能重复**(对null值不做校验)**
    CREATE TABLE emp2(
    	eid INT PRIMARY KEY,
    	ename VARCHAR(10) NOT NULL,
    	dept_name VARCHAR(10) UNIQUE KEY -- 唯一约束
    );
    

    主键约束和唯一约束

    • 主键约束:唯一且不能为空
    • 唯一约束:唯一但可以为空
    • 一个表中只能有一个主键,但可以有多个唯一约束

    默认值

    • 用来指定某个字段的默认值
    -- 语法格式
    字段名 字段类型 default 默认值;
    
    CREATE TABLE emp2(
    	eid INT PRIMARY KEY,
    	ename VARCHAR(10) NOT NULL ,
    	dept_name VARCHAR(10) UNIQUE KEY,
    	sex VARCHAR(20) DEFAULT '女'  -- 设置默认值为女
    );
    

    Mysql事务

    • 事务的定义:由一条或多条sql组成,要成功都成功,要失败则都失败。

    手动开启事务

    • 开始事务关键字:START TRANSACTION / BEGIN
    • 提交事务:COMMIT
    • 回滚事务:ROLLBACK
    START TRANSACTION;
    UPDATE account SET money = money - 500 WHERE user_name = 'tom';
    UPDATE account SET money = money + 500 WHERE user_name = 'jack';
    COMMIT;
    

    自动开启事务

    • mysql默认自动开启事务
    • SHOW VARIABLES LIKE ‘autocommit’;

    事务隔离

    • 四大特性:
      • 原子性:每个事务都是一个整体,不可再拆分,要么全部成功,要么全部失败
      • 一致性:事务在执行前数据库的状态与执行后数据库的状态保持一致。如:转账前2个人的 总金额是 2000,转账后 2 个人总金额也是 2000
      • 隔离性:多个事务之间相对独立,互不影响
      • 持久性:一旦事务执行成功,对数据库的修改是持久的。就算关机,数据也是要保存下来的
    • 隔离的四个级别**(隔离级别安全性越高,效率越低)**
      • read uncommitted 读未提交
      • read commited 读已提交(Oracle默认隔离级别)
      • repeatable read 可重复读(Mysql默认隔离级别)
      • serializable 串行化
    • 数据并发访问可能会出现的问题
      • 脏读:一个事务读到了另一个事务未提交的数据
      • 不可重复读:一个事务中两次读取的数据内容不一致, 要求的是在一个事务中多次读取时数据是一致的,这是进行 update 操作时引发的问题
      • 幻读:一个事务中,某一次的 select 操作得到的结果所表征的数据状态, 无法支撑后续的业务操作. 查询得到的数据状态不准确,导致幻读
      • 解决方式:提高隔离级别
    • 查询隔离级别语句
    select @@tx_isolation;
    
    • 修改隔离级别语句(需重新连接才能生效)
    set global transaction isolation level 级别名称;
    read uncommitted 读未提交
    read committed 读已提交
    repeatable read 可重复读
    serializable 串行化
    set global transaction isolation level read uncommitted; -- 设置隔离级别为读未提交
    

    Mysql多表

    • 实际开发中,需要多张表完成业务功能

    外键

    • 两张表表示对应关系的字段叫做外键字段
    • 从表中与主表对应的字段

    外键约束

    • 两张表使用外键关联,一张表插入一条数据在另外一张表内不存在,会造成业务异常

    • 作用:外键约束可以让两张表之间产生一个对应关系,从而保证了主从表引用的完整性

    • 添加外键约束

    -- 语法格式
    CREATE TABLE 表名(
      字段... [CONSTRAINT] [外键约束名] FOREIGN KEY(外键字段名) REFERENCES 主表(主键字段)
    );
    
    CREATE TABLE emp(
      eid INT PRIMARY KEY AUTO_INCREMENT,
      ename VARCHAR(20),
      age INT,
      dept_id INT,
      CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
    );
    
    • 建表之后添加外键约束
    -- 语法格式
    ALTER TABLE 表名 ADD CONSTRAINT 外键约束名 FOREIGN KEY(外键字段名) REFERENCES 主表(主表字段);
    
    ALTER TABLE emp ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id);
    
    • 插入外键约束后,会产生一个强制的外键约束检查,插入外键不存在的数据报错

    • 删除外键约束

    -- 语法格式
    ALTER TABLE 从表 DROP FOREIGN KEY 外键约束名称;
    
    ALTER TABLE emp DROP FOREIGN KEY emp_dept_fk;
    
    • 注意事项
      • 主表的字段与从表的外键字段的类型必须一样
      • 添加数据时应该先添加主表的数据,再添加从表数据
      • 删除数据的时候,要先删除从表中的数据

    级联删除

    • 在删除主表数据的同时,可以删除与之相关的从表中的数据
    on delete cascade;
    
    CREATE TABLE emp(
      eid INT PRIMARY KEY AUTO_INCREMENT,
      ename VARCHAR(20),
      age INT,
      dept_id INT,
      CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
      on delete cascade
    );
    
    • 执行DELETE FROM department WHERE id = 2; 之后会执行DELETE FROM employee WHERE dept_id = 2;

    表与表之间关系

    • 一对多(1:n):班级和学生(多个学生属于一个班级) 部门和员工
    • 多对多(n:n):学生与课程(多个学生学习多个课程)
    • 一对一(1:1):身份证和人(一个人对应一张身份证)

    一对多关系

    • 设计省市表
    #创建省表 (主表,注意: 一定要添加主键约束) 
    CREATE TABLE province( 
        id INT PRIMARY KEY AUTO_INCREMENT, 
        NAME VARCHAR(20), 
        description VARCHAR(20)
    );
    #创建市表 (从表,注意: 外键类型一定要与主表主键一致) 
    CREATE TABLE city( 
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20), 
        description VARCHAR(20), 
        pid INT, -- 添加外键约束 
        CONSTRAINT pro_city_fk FOREIGN KEY (pid) REFERENCES province(id) 
    );
    

    多对多关系

    • 需要创建第三张表,中间表

    • 中间表中至少要有两个字段是两张表的主键字段,作为中间表中的外键

    • 设计演员与角色表

      • 先建表
      #创建演员表 
      CREATE TABLE actor( 
          id INT PRIMARY KEY AUTO_INCREMENT, 
          NAME VARCHAR(20) 
      );
      #创建角色表
      CREATE TABLE role( 
          id INT PRIMARY KEY AUTO_INCREMENT, 
          NAME VARCHAR(20) 
      );
      #创建中间表 
      CREATE TABLE actor_role( 
          id INT PRIMARY KEY AUTO_INCREMENT, -- 中间表自己的主键 
          aid INT,  -- 指向actor 表的外键 
          rid INT -- 指向role 表的外键 
      );
      
      • 添加外键约束
      -- 为中间表的aid字段,添加外键约束 指向演员表的主键 
      ALTER TABLE actor_role ADD FOREIGN KEY(aid) REFERENCES actor(id);
      -- 为中间表的rid字段, 添加外键约束 指向角色表的主键 
      ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(id);
      

    一对一关系

    • 在开发过程中不是很常见

    多表查询

    -- 语法
    select 字段列表 from 表列表;
    

    笛卡尔积

    SELECT 字段名 FROM 表1, 表2;
    
    • 多表查询,交叉连接查询会产生笛卡尔积,得到的结果是不能够使用的

    内连接查询

    • 通过指定的条件去匹配,去匹配两张表中的内容,匹配不上的就不显示
    • 显式内连接
    -- 使用where
    SELECT 
    p.`pname`,
    p.`price`,
    c.`cname`
    FROM products p,category c 
    WHERE p.`category_id` = c.`cid`;
    
    -- 使用join
    select * from 
    product p
    inner join category c on p.catepory_id = c.cid; 
    
    • 隐式内连接
    select 字段... from 左表,右表 where 连接条件;
    

    外连接

    • 左外连接
      • 以左表为基准,匹配右表中的数据,能匹配上就显示
      • 匹配不上左表中的数据正常显示,右表中的数据展示为null
    SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件;
    
    -- 左外连接查询
    SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;
    
    • 右外连接
      • 以右表为基准,匹配左表中的数据,能匹配上就显示
      • 匹配不上右表中的数据正常显示,左表中的数据展示为null
    SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
    
    -- 右外连接查询 
    SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;
    

    内外连接总结

    • 内连接(inner join):只取两个表中的交集部分的数据
    • 左外连接(left join):以左表为基准,匹配右表中的数据,能匹配上就显示,匹配不上左表中的数据正常显示,右表中的数据展示为null
    • 右外连接(right join):以右表为基准,匹配左表中的数据,能匹配上就显示,匹配不上右表中的数据正常显示,左表中的数据展示为null

    子查询(SubQuery)

    • 一条select语句的结果,作为另外一条select语句的一部分
    SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
    
    SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);
    
    • 子查询语句必须要放在小括号中
    • 子查询作为父查询的条件使用
    • 子查询分类
      • where型子查询:将子查询的结果作为父查询的条件
      • from型子查询:将子查询的查询结果作为一张表使用
      • exists型子查询:查询结果是单列多行的情况,可以将子查询的结果作为父查询的in函数中的条件使用
    • 总结
      • 子查询如果查出来是单个字段,则跟在where后面当成一个条件
      • 子查询如果查出来是多个字段,则跟在from后面当做一张表使用
      • 子查询如果查出来是多行单列,则跟在in函数后面,作为条件

    数据库设计三范式

    • 第一范式:
      • 列具有原子性,设计列要做到列不可拆分
    • 第二范式:
      • 一张表只能描述一件事情
    • 第三范式:
      • 消除传递依赖,表中的信息如果能被推导出来,就不要设计一个字段单独来记录
    • 三范式就是空间最省原则

    数据库反三范式

    • 指的是通过增加冗余或者重复数据,来提高数据库的性能
    • 增加冗余字段后无需连表查询,增加效率

    Mysql索引

    • 什么是索引
      • 可以通过对数据表中的字段创建索引来提高查询速度
    • 常见索引
      • 主键索引(primary key)主键是一个唯一性的索引,每个表中只能有一个主键
      • 唯一索引(Unique)索引列的所有数据只能出现一次,必须是唯一的
      • 普通索引(index)最常见的索引,作用就是提高对数据的访问速度
    • 表对应的索引被保存在一个索引文件中,如果对数据进行增删改操作,那么mysql就需要对索引进行更新

    主键索引

    • 创建方式
    CREATE TABLE 表名(
        -- 添加主键 (主键是唯一性索引,不能为null,不能重复,) 
        字段名 类型 PRIMARY KEY, );
    
    ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )
    
    ALTER TABLE demo01 ADD PRIMARY KEY (did);
    

    唯一索引

    • 特点: 索引列的所有值都只能出现一次,必须唯一
    • 唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复
    CREATE TABLE 表名( 
        列名 类型(长度), 
        -- 添加唯一索引 
        UNIQUE [索引名称] (列名) );
    
    create unique index 索引名 on 表名(列名(长度))
    
    ALTER TABLE 表名 ADD UNIQUE ( 列名 )
    

    普通索引

    • 普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE column=)或排序条件(ORDERBY column)中的数据列创建索引。
    create index 索引名 on 表名(列名[长度])
    
    ALTER TABLE 表名 ADD INDEX 索引名 (列名)
    

    删除索引

    ALTER TABLE table_name DROP INDEX index_name;
    
    ALTER TABLE demo01 DROP INDEX dname_indx;
    

    索引优缺点总结

    • 添加索引首先应考虑在 where 及 order by 涉及的列上建立索引
    • 最好在建表的时候添加索引,后期在数据量大的时候添加索引成本很大
    • 优点
      • 大大的提高查询速度
      • 可以显著的减少查询中分组和排序的时间
    • 缺点
      • 创建索引和维护索引需要时间,而且数据量越大时间越长
      • 当对表中的数据进行增加,修改,删除的时候,索引也要同时进行维护,降低了数据的维护速度

    Mysql视图

    • 是一张虚拟的表,类似缓存数据,可以优化多表联查
    • 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句

    视图的作用

    • 权限控制时可以使用
      • 比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用
    • 简化复杂的多表查询
      • 视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)
      • 视图主要就是为了简化多表的查询
    create view 视图名 [column_list] as select语句; 
    view: 表示视图 
    column_list: 可选参数,表示属性清单,指定视图中各个属性的名称,默认情况下,与SELECT语句中查询 的属性相同 
    as : 表示视图要执行的操作 
    select语句: 向视图提供数据内容
    
    • 创建视图
    SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`; 
    
    CREATE VIEW products_category_view AS -- 后面接关联查询语句
    SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;
    
    • 查询视图(和查询表一样)
    SELECT * FROM products_category_view;
    
    • 通过视图进行多表查询
    -- 普通sql查询写起来比较复杂
    SELECT 
    cname AS '分类名称', 
    AVG(p.`price`) AS '平均价格' 
    FROM products p 
    LEFT JOIN category c 
    ON p.`category_id` = c.`cid`
    GROUP BY c.`cname`;
    
    -- 使用视图查询
    SELECT 
    cname AS '分类名称', 
    AVG(price) AS '平均价格' 
    FROM products_category_view 
    GROUP BY cname;
    

    视图与表的区别

    • 视图是建立在表的基础上,表存储数据库中的数据,而视图只是做一个数据的展示
    • 通过视图不能改变表中数据(一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许更新)
    • 删除视图,表不受影响,而删除表,视图不再起作用

    Mysql存储过程

    • 存储过程其实就是一堆 SQL语句的合并。中间加入了一些逻辑控制

    存储过程的优缺点

    • 优点
      • 存储过程一旦调试完成后,就可以稳定运行,(前提是,业务需求要相对稳定,没有变化)
      • 存储过程减少业务系统与数据库的交互,降低耦合,数据库交互更加快捷(应用服务器,与数据库服务器不在同一个地区)
    • 缺点
      • 在互联网行业中,大量使用MySQL,MySQL的存储过程与Oracle的相比较弱,所以较少使用,并且互联网行业需求变化较快也是原因之一
      • 尽量在简单的逻辑中使用,存储过程移植十分困难,数据库集群环境,保证各个库之间存储过程变更一致也十分困难
      • 阿里的代码规范里也提出了禁止使用存储过程,存储过程维护起来的确麻烦

    创建、调用存储过程

    • 方式1
    DELIMITER $$ -- 声明语句结束符,可以自定义 一般使用$$ 
    CREATE PROCEDURE 过程名称() -- 声明存储过程 
    BEGIN -- 开始编写存储过程 
    -- 要执行的操作 
    END $$ -- 存储过程结束
    
    -- 查询商品表全部商品信息
    DELIMITER $$ 
    CREATE PROCEDURE goods_proc() 
    BEGIN
    select * from goods;
    END $$
    
    • 调用
    call goods_proc;
    
    • 方式2(接收参数的存储过程)
    CREATE PROCEDURE 存储过程名称(IN 参数名 参数类型)
    
    DELIMITER $$ 
    CREATE PROCEDURE goods_proc02(IN goods_id INT) 
    BEGIN
    DELETE FROM goods WHERE gid = goods_id ; 
    END $$
    
    • 调用
    -- 删除id为2的商品
    call goods_proc02(2);
    
    • 方式3(变量赋值)
    SET @变量名=值;
    -- OUT 输出参数:表示存储过程向调用者传出值
    OUT 变量名 数据类型;
    
    # 创建存储过程 接收参数插入数据, 并返回受影响的行数 
    DELIMITER $$ 
    CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT, OUT out_num INT) 
    BEGIN
    -- 执行插入操作 
    INSERT INTO orders VALUES(o_oid,o_gid,o_price); 
    -- 设置 num的值为 1 
    SET @out_num = 1; 
    -- 返回 out_num的值 
    SELECT @out_num; 
    END $$
    
    • 调用
    CALL orders_proc(1,2,30,@out_num); -- 返回out_num-->1
    

    Mysql触发器

    • 触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete, update)时就会激活它执行。——百度百科
    delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误 
    CREATE TRIGGER Trigger_Name -- 触发器名,在一个数据库中触发器名是唯一的 before/after(insert/update/delete) -- 触发的时机 和 监视的事件 
    on table_Name -- 触发器所在的表 
    for each row -- 固定写法 叫做行触发器, 每一行受影响,触发事件都执行 
    begin
    -- begin和end之间写触发事件 
    end $ -- 结束标记
    
    • 需求: 在下订单的时候,对应的商品的库存量要相应的减少,卖出商品之后减少库存量
    DELIMITER $
    CREATE TRIGGER t1 -- 创建触发器
    AFTER INSERT ON orders -- 设置触发时间以及监视的表(表:orders,监视时间:insert之后)
    FOR EACH ROW -- 行触发器
    BEGIN
       -- 订单+1 库存-1
       -- 执行修改库存的操作
       UPDATE goods SET num = num - 1 WHERE gid = 4;
    END $
    

    DCL(数据控制语言)

    创建用户

    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'
    
    -- 创建admin1用户,只能在localhost这台mysql服务器上登录,密码是123456
    CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';
    -- 创建admin1用户,可以在任意mysql服务器登录,密码是123456
    CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';
    

    用户授权

    -- ALL代表所有权限 
    GRANT 权限1,权限2,... (ALL) ON 数据库名.表名 TO '用户名'@'主机名';
    
    -- 给admin1分配goods的查询权限
    GRANT SELECT ON db3.goods TO 'admin1'@'localhost';
    
    -- 给admin2用户分配全部权限
    GRANT ALL ON *.* TO 'admin2'@'%';
    

    查询用户权限

    SHOW GRANTS FOR 'admin1'@'localhost'
    

    查询、删除用户

    • 删除用户
    DROP USER 'admin1'@'localhost';
    
    • 查询用户
    SELECT * FROM mysql.`user`
    

    数据库备份

    • sqlyog方式

      • 右键数据库–》备份/导出
    • 命令行方式

      • mysqldump -u用户名 -p密码 数据库名 > 文件路径
    展开全文
  • 现在改为oracle之后,表字段全是大写,经过sql查出的放回Map中的key全是大写的, 这个一个Map要传递到action 甚至页面上, 之前key全是mysql中的数据字段(小写,有驼峰规则),现在全变成大写了。 请问,谁有好的...
  • oracle日期类型字段

    千次阅读 2009-03-11 15:01:00
    我的开发框架是webwork+ibatis+oracle大家有没有碰到这样的问题: 假如我的表字段的类型是date,java po类里此字段对应的类型是String,我们需要从数据库里把此po的信息查询出来,然后保存到另外一个表里,另外一张...

    我的开发框架是webwork+ibatis+oracle

    大家有没有碰到这样的问题:

          假如我的表字段的类型是date,java po类里此字段对应的类型是String,我们需要从数据库里把此po的信息查询出来,

    然后保存到另外一个表里,另外一张表里字段的类型是date,java po类里此字段对应的类型是String。

          如果我们直接从数据库里查询出来,然后不加处理就直接插入,或是更新到另一个表里,那么就会报错,导致insert or

    update 失败.原因date类型的字段从数据库里查出来是 21-1月 -09 12.00.00.000000 这样的格式,然后我们用to_date('','yyyy-mm-dd'),或  to_date('','yyyy-mm-dd hh-mi-ss.sss')都会报错。

          我的解决方法是:(1)查询此po的信息时把它转换成字符串,并规定好格式

                                   (2)插入时用to_date('','yyyy-mm-dd')就行了

     

          代码:

         <select id="getContentInfoCopyByID" resultClass="contentPO" parameterClass="long">
              select  t.content_id,t.content_name,t.indicate,t.content_type,t.item_id,
              t.order_command,t.type_id,t.imguri,t.order_success_message,
             t.content_order_message,t.service_approve_state,t.opinion,
             to_char(t.apply_time,'yyyy-mm-dd') as apply_time,to_char(t.approve_time,'yyyy-mm-dd') as            approve_time,
             t.delete_flag,t.cp_id,t.inner_service_code_id,t.author,t.copyright_deadline
              from t_content_info_copy t where content_id = #content_id#
     </select>

    展开全文
  • 业务场景是这样的,a表有字段id,ids,ID= 11 ids= 1,2等字符串,b表有字段id, name字段,内容如id= 1name =java工程师,ID =2name =需求分析师,id = 3name =ui设计,id = 3name =web前端等内容 ,a表字段ids值为b...

    记录一次特殊的列表查询,以便于以后查阅。

    业务场景是这样的,a表有字段id,ids,ID= 11 ids= 1,2 等字符串,b表 有字段 id,  name 字段,内容如 id= 1 name = java工程师,ID =2 name = 需求分析师,id = 3 name = ui设计,id = 3 name = web前端 等内容 ,a表字段ids 值为b表取值集合,要求 取出a表字段ids 展示中文名称 Java工程师,需求分析师

    数据库 是oracle11g

    思路分析,多于复杂的字段且有规律的字段查询 此刻想到的是REGEXP_LIKE函数,REGEXP_LIKE (a.ids,b.id),取出 符合条件的a表id,b表name,列表  11,java工程师,11 ,需求分析师,相当于一次拆分,然后怎么做就比较明了了,对a表id做分组,wm_concat函数对name做组合,最终得到结果 11   java工程师,需求分析师

     

    完整sql语句如下:select a.*,( select temp.name from 
    (select aa.id,wm_concat(b.name)  name
    from a aa,
    b b 
    where  REGEXP_LIKE (aa.ids,b.id) 
    group by a.id) temp where temp.id = a.id)  name from a a

     

    查询结果 11 1,2 java工程师,需求分析师

    展开全文
  • 开发中我使用的是Oracle...仔细检查了数据库表字段的映射,发现没问题,后来搞了好久才终于搞明白了: Oracle查询字段区分大小写,于是我把SQL语句中要查询的字段都改为大写,并且就算你的Oracle数据库使用了注...
  • oracle或sqlServer下会遇到,查询数据表字段类型为char,如果使用Hibernate原生SQL去执行查询,往往只会返回 一个字符.原因是char在Java中只有两个字节. 解决方法 1.改成  select cast(列 as varchar2(列...
  • oracle或sqlServer下会遇到,查询数据表字段类型为char,如果使用Hibernate原生SQL去执行查询,往往只会返回 一个字符.原因是char在java中只有两个字节. 解决方法 1.改成  select cast(列 as varchar2(列大小)...
  • oracle或sqlServer下会遇到,查询数据表字段类型为char,如果使用Hibernate原生SQL去执行查询,往往只会返回一个字符.原因是char在java中只有两个字节.    解决方法: 1.改成  select cast(列 as varchar2...
  • 1.当表结构或表字段数据改动的话,单表查询修改代码相对简单2. 业务逻辑放到 java 层处理后,接口联调的时候,非常方便;3. 数据库变动的话,改动较少,如mysql 换成 oracle;4. 二级缓存用 nosql 时,缓存命中率更...
  • 书名:《Java开发实战1200例(第I卷)》(清华大学出版社.李钟尉.陈丹丹) 2011年1月出版 PDF格式扫描版,共压缩为5部分,这是第2部分。 内容简介 本系列图书包括第I卷、第II卷共计1200个例子,本文档是第I卷,共计603...
  • 书名:《Java开发实战1200例(第I卷)》(清华大学出版社.李钟尉.陈丹丹) 2011年1月出版 PDF格式扫描版,共压缩为5部分,这是第3部分。 内容简介 本系列图书包括第I卷、第II卷共计1200个例子,本文档是第I卷,共计603...
  • 书名:《Java开发实战1200例(第I卷)》(清华大学出版社.李钟尉,陈丹丹) PDF格式扫描版,全书分为24章,共817页。2011年1月出版。 全书压缩打包成4部分,这是第3部分 注:本系列图书的第I、II卷再版时均相应改名为...
  • 书名:《Java开发实战1200例(第I卷)》(清华大学出版社.李钟尉,陈丹丹) PDF格式扫描版,全书分为24章,共817页。2011年1月出版。 全书压缩打包成4部分,这是第1部分 注:本系列图书的第I、II卷再版时均相应改名为...
  • sql处理char类型

    千次阅读 2018-07-24 17:17:55
    oracle或sqlServer下会遇到,查询数据表字段类型为char,如果使用Hibernate原生SQL去执行查询,往往只会返回 一个字符.原因是char在java中只有两个字节. 解决方法 1.改成 select cast(列 as varchar2(列大小)...
  • 使用mybatis generator在自动生成代码时,数据库使用的是oracle,遇到一个问题就是生成的表对应的java文件和预期的表字段不一致,后面发现生成的是不同库的同名表网上查询解决办法都是mysql的通过在数据库连接配置中...
  • 使用mybatis generator在自动生成代码时,数据库使用的是oracle,遇到一个问题就是生成的表对应的java文件和预期的表字段不一致,后面发现生成的是不同库的同名表 网上查询解决办法都是mysql的 通过在数据库连接配置...
  • 电子商务项目 电商项目简介 电子商务项目taoshop ...本开源电商项目,SpringBoot+Dubbo技术栈实现微服务,实现一款分布式集群的电商系统。(开发中...) ...Oracle笔记之修改表字段类型 Oracle的nvl函数和nvl2函数

空空如也

空空如也

1 2
收藏数 25
精华内容 10
关键字:

java查询oracle表字段

java 订阅