数据库笔记02

Oracle常用的类型：

​ number(总个数, 小数位数)、char(指定大小)、varchar2(指定大小)、date

存储大容量可以用：

​ long：存储2G，每个表只能有一个long类型，不能作为主键、不能建立索引、不能出现在查询语句中，约束较多，不建议使用。

​ clob：可以存储文章等大容量的内容，推荐使用

操作表结构

-- 将tb2表名修改为tb3
rename tb2 to tb3;

-- 修改表字段（列），可以修改其类型、大小、默认值
alter table tb1 modify (id char(3));

-- 添加表字段
alter table tb1 add (
	age number(2)
);

-- 删除表字段
alter table tb1 drop (sex);

-- 查看表结构
desc tb1;

-- 将tb2的数据插入到tb1
-- 注：两个表的结构一致，不一致时需要查询tb2的指定字段（tb1字段对应）
insert into tb1 select * from tb2;

-- 将结构和数据完全复制并创建新表
create table tb1 as select * from tb2;

-- 设置age字段不可用
alter table tb1 set unused column age;
或者 alter table tb1 set unused(age);

alter table tb1 drop unused columns;

查询

-- oracle 的表不能用as进行别名，直接空格一个别名即可
select empno as id, ename as name, deptno
from scott.emp e					-- 此处别名不能用as
where e.deptno = 20;

-- 拼接字符串
select concat('hello ', ename) as hello from scott.emp;
select concat(concat(ename, '：'), sal) as sal from scott.emp
select ename || ' is ' || job as job  from scott.emp

-- dual 表方便作函数测试，是一个共享表
select concat('didadida', ' 666') from dual;
-- “HH”格式（1-12）的小时不能超过12，超过12需要用“HH24”格式（0-23）
select to_date('2019-12-13 12:30:25', 'YYYY-MM-DD HH:MI:SS') as data from dual;

select to_date('2019-12-13 22:12:36', 'YYYY-MM-DD HH24:MI:SS') as data from dual;
select to_char(to_date('2019-12-13 12:30:25', 'YYYY-MM-DD HH:MI:SS')) as time from dual;

-- 去重distinct，只能放在结果字段最前面，后面有多少字段，以“并”的关系去重
select distinct deptno from scott.emp;
select distinct deptno, ename from scott.emp;

函数

-- 获取长度length()
select ename, length(ename) from scott.emp;

-- 拼接字符串concat()、||
select concat('hello ', ename) as hello from scott.emp;
select concat(concat(ename, '：'), sal) as sal from scott.emp
select ename || ' is ' || job as job  from scott.emp

-- 转换大小写
select * from scott.emp where lower(ename) = 'smith';
select * from scott.emp where upper(ename) = 'JONES';
-- 每个单词首字符大写
select initcap('i love you') as cap from dual;												-- I Love You

-- 左右去除指定字符串
select trim('美' from '美好生活之美') as t_ltrim from dual;						-- 好生活之
select ltrim('美好生活之美', '美') as t_ltrim from dual;							-- 好生活之美
select rtrim('美好生活之美', '美') as t_rtrim from dual;							-- 美好生活之

-- 左右打补丁
select lpad(sal, 4, '~') from scott.emp;
select rpad(sal, 4, '#') from scott.emp;

-- 剪切字符串substr(源, 开始位置, 截取长度)。
-- 位置是从1开始而不是0，负数就是倒数第几个。长度超过按实际大小截取
select substr('i love you without any words', 1, 10) from dual;				-- i love you
select substr('i love you without any words', -9, 10) from dual;			-- any words

-- 索引
select instr('you always in my heart', 'heart') from dual;

-- 四舍五入，round(源, 保留位数)，
-- 保留位数为负数时，小数点左边数起，为正小数点右边数起，默认为0，取整数
select round(1234.5678, 2) from dual;
select round(1234.5678, -2) from dual;
select round(1234.5678) from dual;

-- 同上，但是不会四舍五入
select trunc(1234.5678, 1) from dual;
select trunc(1236.5678, -1) from dual;

-- 取模
select mod(1234, 100) from dual;

-- 向上向下取整
-- 向上取整：有小数，整数个位进一
-- 向下取整：保留整数
select ceil(123.45) from dual;
select floor(123.45) from dual;

-- 系统时间
select sysdate from dual;
select to_char(sysdate, 'YYYY-MM-DD DAY HH24:MI:SS') as time from dual;
select to_char(systimestamp, 'YYYY-MM-DD DAY HH24:MI:SS:FF3') as time from dual;

-- rr格式，当年份大于等于50，使用rr格式，时间就是上世纪的时间
-- yy格式：都是本世纪的时间
select to_date('12-10月-49', 'dd-mon-rr') as time from dual;					-- 2049-10-12 00:00:00
select to_date('12-10月-50', 'dd-mon-rr') as time from dual;					-- 1950-10-12 00:00:00
select to_date('12-10月-50', 'dd-mon-yy') as time from dual;					-- 2050-10-12 00:00:00

-- months_between月份差值
select e.ename, round(months_between(sysdate, e.hiredate) / 12, 2) || '年' as old from scott.emp e

-- 增加月份
select e.ename, add_months(e.hiredate, 30 * 12) as "30周年"  from scott.emp e;

-- 获取月份最后一天
select to_char(last_day(sysdate), 'yyyy-mm-dd') from dual;

-- next_day第二个参数是指定下个星期几，1-7对应星期天-星期六
select next_day(sysdate, 2) from dual;

-- 比较日期大小并返回，
select least(sysdate, to_date('20-10月-16', 'dd-mon-yy')) as min_date from dual;
select greatest(sysdate, to_date('20-10月-16', 'dd-mon-yy')) as min_date from dual;
-- greatest、least也可以比较大小
select greatest(12,10,15,8) from  dual;

-- 提取指定日期数据。
-- YEAR：年、MONTH：月、DAY：日
select extract(YEAR from sysdate) as year from dual;
select extract(MONTH from sysdate) as month from dual;
select extract(DAY from sysdate) as day from dual;

-- 日期可以直接相减
select e.ename as "姓名", ceil((sysdate - e.hiredate)) as "天数" from scott.emp e