-
java从oracle中读取大量数据写出到文件
2020-09-15 16:42:06背景:项目中需要从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; }
-
JAVA学习第二阶段模块一
2020-11-14 13:28:48成员变量 —》表字段 对象 —》对应一条记录 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密码 数据库名 > 文件路径
-
-
mysql 数据迁移至oracle, 程序查询数据遭遇oracle字段大写问题
2013-03-15 15:56:46现在改为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> -
记录一次oracle 多值逗号隔开字段显示中文名称的解决方案
2019-05-17 14:29:38业务场景是这样的,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工程师,需求分析师
-
关于操作数据库时报的java.sql.SQLException: ORA-00904:标识符无效 的问题
2019-10-30 12:00:36开发中我使用的是Oracle...仔细检查了数据库表字段的映射,发现没问题,后来搞了好久才终于搞明白了: Oracle查询字段区分大小写,于是我把SQL语句中要查询的字段都改为大写,并且就算你的Oracle数据库使用了注... -
hibernate查询数据表char类型字段只返回一个字符
2017-05-22 11:32:40在oracle或sqlServer下会遇到,查询数据表字段类型为char,如果使用Hibernate原生SQL去执行查询,往往只会返回 一个字符.原因是char在Java中只有两个字节. 解决方法 1.改成 select cast(列 as varchar2(列... -
hibernate查询数据库char类型字段时只返回一个字符
2015-09-18 23:55:35在oracle或sqlServer下会遇到,查询数据表字段类型为char,如果使用Hibernate原生SQL去执行查询,往往只会返回 一个字符.原因是char在java中只有两个字节. 解决方法 1.改成 select cast(列 as varchar2(列大小)... -
Hibernate查询数据表char类型字段只返回一个字符
2012-11-27 16:26:38在oracle或sqlServer下会遇到,查询数据表字段类型为char,如果使用Hibernate原生SQL去执行查询,往往只会返回一个字符.原因是char在java中只有两个字节. 解决方法: 1.改成 select cast(列 as varchar2... -
mybatis xml 单表查询的好处(持续更新中)
2016-01-20 10:59:011.当表结构或表字段数据改动的话,单表查询修改代码相对简单2. 业务逻辑放到 java 层处理后,接口联调的时候,非常方便;3. 数据库变动的话,改动较少,如mysql 换成 oracle;4. 二级缓存用 nosql 时,缓存命中率更... -
《Java开发实战1200例(第I卷)》(李钟尉.陈丹丹).part2 高清完整PDF版
2016-06-13 15:53:27书名:《Java开发实战1200例(第I卷)》(清华大学出版社.李钟尉.陈丹丹) 2011年1月出版 PDF格式扫描版,共压缩为5部分,这是第2部分。 内容简介 本系列图书包括第I卷、第II卷共计1200个例子,本文档是第I卷,共计603... -
《Java开发实战1200例(第I卷)》(李钟尉.陈丹丹).part3 高清完整PDF版
2016-06-13 16:11:24书名:《Java开发实战1200例(第I卷)》(清华大学出版社.李钟尉.陈丹丹) 2011年1月出版 PDF格式扫描版,共压缩为5部分,这是第3部分。 内容简介 本系列图书包括第I卷、第II卷共计1200个例子,本文档是第I卷,共计603... -
Java开发实战1200例(第1卷).(清华出版.李钟尉.陈丹丹).part3
2016-06-12 11:39:31书名:《Java开发实战1200例(第I卷)》(清华大学出版社.李钟尉,陈丹丹) PDF格式扫描版,全书分为24章,共817页。2011年1月出版。 全书压缩打包成4部分,这是第3部分 注:本系列图书的第I、II卷再版时均相应改名为... -
Java开发实战1200例(第1卷).(清华出版.李钟尉.陈丹丹).part1
2016-06-12 11:34:39书名:《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在指定库建表_mybatis generator生成代码库 与指定的库不一致 为其他库的同名表...
2020-12-30 08:48:22使用mybatis generator在自动生成代码时,数据库使用的是oracle,遇到一个问题就是生成的表对应的java文件和预期的表字段不一致,后面发现生成的是不同库的同名表网上查询解决办法都是mysql的通过在数据库连接配置中... -
mybatis generator生成代码库 与指定的库不一致 为其他库的同名表
2020-07-29 18:21:48使用mybatis generator在自动生成代码时,数据库使用的是oracle,遇到一个问题就是生成的表对应的java文件和预期的表字段不一致,后面发现生成的是不同库的同名表 网上查询解决办法都是mysql的 通过在数据库连接配置... -
电子商务项目 电商项目简介 电子商务项目taoshop ...本开源电商项目,SpringBoot+Dubbo技术栈实现微服务,实现一款分布式集群的电商系统。(开发中...) ...Oracle笔记之修改表字段类型 Oracle的nvl函数和nvl2函数