java查询oracle表字段

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;
    }

收起

模块一 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密码 数据库名 > 文件路径

收起

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

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

JAVA学习第二阶段模块一

模块一 Mysql基础和SQL入门

SQL的概念

SQL通用语法

Mysql自带数据库

创建数据库

创建表

建表语法

常见的数据类型

char和varchar区别

复制表

删除表

修改表

修改表名称

修改表字符集

新增一个字段

修改列的类型

修改列的名称

删除列

DML对表进行增删改

增加

修改

删除

查询

条件查询

DQL

排序

单列排序

组合排序

聚合函数

分组查询

where和having区别

limit关键字

约束

主键约束（primary key）

主键自增

非空约束（not null）

唯一约束（unique key）

主键约束和唯一约束

默认值

Mysql事务

手动开启事务

自动开启事务

事务隔离

Mysql多表

外键

外键约束

级联删除

表与表之间关系

一对多关系

多对多关系

一对一关系

多表查询

笛卡尔积

内连接查询

外连接

内外连接总结

子查询（SubQuery）

数据库设计三范式

数据库反三范式

Mysql索引

主键索引

唯一索引

普通索引

删除索引

索引优缺点总结

Mysql视图

视图的作用

视图与表的区别

Mysql存储过程

存储过程的优缺点

创建、调用存储过程

Mysql触发器

DCL（数据控制语言）

创建用户

用户授权

查询用户权限

查询、删除用户

关于操作数据库时报的java.sql.SQLException: ORA-00904:标识符无效的问题

mybatis在指定库建表_mybatis generator生成代码库与指定的库不一致为其他库的同名表...

mybatis generator生成代码库与指定的库不一致为其他库的同名表