我的Java学习笔记（五）—— 数据库的操作
数据库语言分类
- DDL：Data Defined Language 数据定义语言——不可逆
创建表、删除表、修改表结构 - DML：Data Management Language 数据操纵语言——可逆
新增/插入数据、删除数据、修改数据、(查询数据) - DCL：Data Control Language 数据控制语言
用户的创建、删除、修改，权限的设置 - TCL：Transaction Control Language 事物控制语言
操作事务：提交、回滚、记录事务点 - DQL：Data Query Language 数据查询语言
查询数据
1.DDL
数据类型： int long double char String Date
Oracle数据库类型 ：
- number(5) —— 99999 - int 括号内为位数
- number(11) —— 99999999999 - long
- number(7,2) —— 99999.99 - double 括号内为(位数，小数位数)
- char(2) - xx —— 固定长度的String(固定字符串)
- varchar(20) - xx —— 最大20位的String(可变字符串)
- varchar2(20) —— 最大20位的String(可变字符串)
- Date —— 年月日
举例
-- sql语句的大小写不敏感，操作不可逆
-- DDL：create/drop/alter/rename/truncate 不可逆
-- 创建表：表名、字段、类型
create table emp_dt(
id number(11),
name varchar(20),-- 字节数
age number(2),
gendar char(1),
salary number(7,2)
);
-- 表刚创建时，数据是空
-- 插入一条数据，一行数据
insert into emp_dt values(1,'lucy',18,'f',5000); -- 插入所有的字段，值得顺序，和字段定义顺序一致
insert into emp_dt values(2,'tom',null,'f',null);-- 插入所有的字段，有的字段值为null
insert into emp_dt(id,name,gendar) values(3,'jack','m');-- 指定字段插入，值得顺序和指定顺序一致,没有插入的字段是null
-- 查询表结构
desc emp_dt;
-- 重命名表
rename emp_dt to emp_dtmax;
-- 修改表结构
-- 增加字段(新增的字段内容为空，其他原有数据仍在)
alter table emp_dt add(hiredate date);
-- 删除原表——同时把表数据一起删除，操作不可逆，新建新表
drop table emp_dt;
-- 删除字段，删除字段中所有的数据
alter table emp_dt drop(gendar);
-- 修改字段
alter table emp_dt modify(age number(3));
-- 删除表数据，保留表数据
truncate table emp_dt;
2.DML
事务管理：
- 什么是事务：业务层面上不可切割的数据库操作，一个事务
- 事务的特点：隔离性、原子性、持久性、一致性
数据类型
- Date：年月日
- timestamp：年月日时分秒
- to_char(date, ‘YYYY-MM-DD hh24:mi:ss’)
- to_date(‘2019-12-31 12:31:46’, ‘YYYY-MM-DD hh24:mi:ss’)
- dual:不是一张真正的表，orcal提供的一个万能的临时用表
举例
--DML:insert into/delete from/update/select
--DQL:select
drop table emp_dt;-- 删除原表
desc emp_dt;-- 查询表结构
create table emp_dt(
id number(5) not null, -- not null 表示字段不能为空
name varchar2(20) default '佚名' not null, -- default 如果字段没有值，填写默认的内容
age number(2),
gendar varchar2(2),
salary number(11,2)
);
-- insert into 新增数据
insert into emp_dt(id,salary) values(20001,5000);
insert into emp_dt values(20002,'tom',28,'男',8000);
insert into emp_dt values(20003,'lucy',19,'女',3000);
insert into emp_dt values(20004,'rose',30,'女',10000);
commit;-- 提交
-- update 修改数据
update emp_dt set gendar = null; -- 影响全部记录
-- 条件的拼接可以用or/and
update emp_dt set name = '小明' where name = '佚名' or id = 20002; -- 修改一个人的名字为小明，使用条件where语句
update emp_dt set age = 18 where age = 28;
-- delete 删除数据
delete from emp_ws;-- 删除全部
delete from emp_dt where name = 'lucy';
--select 查询数据
select * from emp_dt;-- 查询全部
select * from emp_ws;
-- TCL:Transaction Control Language —— 事务控制语言 DML
rollback;
-- 插入时间
alter table emp_dt add(hiredate date);
insert into emp_dt values(20005,'小红',15,'女',5000,'15-4月-19');
-- 时间转换格式 '2019-04-12' -> 转换成date
insert into emp_dt values(20006,'小李子',50,'男',6000,to_date('2019-04-12','yyyy-mm-dd'));
-- date 转换成 '2019-04-12'
to_char(sysdate,'YYYY-mm-dd');
-- 查看当前系统时间 dual:不是一张真正的表，orcal提供的一个万能的临时用表
select to_char(sysdate, 'YYYY-mm-dd') from dual;
-- hiredate 字段类型转换 timestamp:年月日时分秒
alter table emp_dt modify(hiredate timestamp);
--select 查询数据
select * from emp_dt;-- 查询全部
insert into emp_dt values(20007,'小胡子',23,'男',10000,to_date('1996-12-03 09:30:30','yyyy-mm-dd hh24:mi:ss'));
insert into emp_dt values(20007,'小胡子',23,'男',10000,sysdate);
-- 简单的基本查询
select * from emp_dt;-- 查询全部
select id,name from emp_dt;-- 查询指定字段
select * from emp_dt where id > 2 and name = 'tom';-- 条件查询
3.函数
举例
to_date('2019-04-16 9:36:10','yyyy-mm-dd hh24:mi:ss')
to_char(sysdate,'yyyy-mm-dd')
-- 字符串拼接 concat
-- "CONCAT"(ch1, ch2)ch1和ch2的拼接
SELECT concat('hi', 'hello') from dual;
-- 函数可以嵌套 as给字段起别名 —— as可以省略
SELECT concat(concat('hi', 'hello'),'ha') as finalStr from dual;
-- 等同于"CONCAT"(ch1, ch2)
SELECT 'hi' || 'hello' || 'ha' FROM dual
-- 去掉左右两边空格
SELECT trim(' hi ') finalStr from dual;
SELECT trim('e' from 'ehi hello hahae') finalStr from dual;
select ltrim(' hi ') finalStr from dual;-- 去掉左边空格
select ltrim('ehi hello hahae','e') finalStr from dual;-- 去掉左边的e
select rtrim('ehi hello hahae','e') finalStr from dual;-- 去掉右边的e
-- 数据库中是不区分大小写，默认存入数据库变大写，查询时大小写有区分
select * from EMP_DT where name = 'lucy';
select name from EMP_DT where upper(name) = 'LUCY';
select name from EMP_DT where lower(name) = 'lucy';
-- 给定时间所在月的最后一天
SELECT last_dat(sysdate) from dual;
-- 时间可以直接计算
select 12*60 from dual;
-- 从给定时间月份推算
select "ADD_MONTHS"(SYSDATE, 1) from dual;
-- 推算下一个周的周几
select "NEXT_DAY"(sysdate, 3) from dual;
-- 计算两个时间中间的差值
select "ROUND"(sysdate - to_date('2019-4-10','yyyy-MM-dd')) from dual;
-- 时间提取
select "EXTRACT"(month from sysdate) from dual;
-- 数学函数：向上取整，四舍五入，向下取整
select ceil(4.2) from dual;
select round(4.2) from dual;
select floor(4.8) from dual;
-- 四舍五入保留位数
select round(46.789,2) from dual;
select round(46.789,-1) from dual;
--取整
select trunc(46.789,1) from dual;
select trunc(46.789,-1) from dual;
-- 保存当前操作事务点
savepoint a;
-- 回滚当前事务所有操作
rollback;
-- 回滚到a保存点
rollback to a;
commit;-- 提交当前事务所有的操作
4.条件查询和联合查询
-- select 字段、函数、表达式
-- from 表
-- where 条件 > < >= <= = <> [is null] [is not null] in any all [between and] or
-- like[%_]
select * from emp where ename like 'Sa_%' escape 'a';
-- group by 按字段分组 - select 中限制：分组字段、组函数
-- having 组函数的条件，非常消耗内存
-- order by 排序规则 asc desc
-- 顺序 from where [having] select order by
-- 联合查询
-- 每个员工所在的部门名称是什么
select * from emp; -- 15
select * from dept; -- 4
-- 笛卡尔积 - 没有意义
select * from emp, dept; -- 15*4
-- 连接查询 - 外链接
-- left outer join 左外链接 ，outer可以省略
-- 左外链接：左边表中所有的数据
-- 右边表中能匹配上的数据
-- 能显示出来所有数据的表：驱动表
select ename, dname, e.deptno
from emp e left outer join dept d
on e.deptno = d.deptno;
-- 右外链接：左边表中能匹配上的数据
-- 右边表中所有的数据
select ename, dname, e.deptno
from emp e right join dept d
on e.deptno = d.deptno;
select ename, dname, e.deptno
from dept d right join emp e
on e.deptno = d.deptno;
-- 全外链接，将两张表中所有的数据都显示
select ename, dname, d.deptno
from dept d full join emp e
on e.deptno = d.deptno;
-- 查询每个员工的直接上级名字 -- inner join 内链接
select e1.ename, e1.empno, e1.mgr, e2.ename
from emp e1 inner join emp e2
on e1.mgr = e2.empno
5.查询的一些基础知识
查询的分类：
- 单表查询 条件查询、函数、分组查询、模糊查询、排序
- 多表查询 左外连接 右外连接 内连接 等值连接 全外连接
- 子查询 select、from、where、having
- 分页查询 rownum：只能从1开始
约束：
- 约束：主键约束、非空约束、唯一约束、外键约束、检查约束 check(‘f’,‘m’)
- check 约束用法
- 主键约束：非空 + 唯一 primary key, 一个表中只能出现一次
- 标示数据的唯一性，代理主键90%、自然主键
- 唯一约束：unique
外键：
- 外键是什么？一个表引用另一个表的字段
- 在emp表中有个外键-deptno, 引用的是dept表的deptno字段, 外键引用的字段必须唯一
- 父表：dept/course 子表：emp/student
- 在查询中外键效率比较低，现在使用比较少
序列和索引：
- 序列 sequence 提供一个算法,帮助我们生成连续并且唯一的数值列表
- 序列的标号是不可逆的，调用nextVal值，标号就向后一位
- 其他数据库中生成主键算法，自增长 auto_increament
- 索引：提高查询效率 - 原理(二叉树)
原则：
- 小表坚决不加索引
- 经常出现在select或者where中的字段可以添加索引
- 经常出现在update中的字段，坚决不要加索引
- 索引不是越多越好
- 建议添加索引时，组合字段尽量越少越好
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
