对表做增删改查

-- 创建一张表
create table tab_user(
   id number(11) primary key ,
   username varchar2(10),
   password varchar2(20)
);

-- 修改表添加一列
alter table tab_user add age number(3) ;
alter table tab_user add birthday date ;

-- 修改列的类型长度和约束
alter table tab_user modify birthday varchar(20) ;

-- 修改表删除一列
alter table tab_user drop column age ;

-- 修改表修改列名称
alter table tab_user rename column birthday to birth ;

-- 删除表
delete from tab_user; --- 一条一条删除数据
drop table tab_user ; -- 删除整个表结构
truncate table tab_user ; -- 删除表结构,再从新创建表结构

数据库表数据的增删改查

oracle序列

CREATE SEQUENCE 序列名称;

序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
nextval :取得序列的下一个内容
currval :取得序列的当前内容

表数据增删改

-- 创建一个序列
-- 序列的作用:产生一组连续的值
create sequence seq_user ;

-- 插入数据
insert into tab_user values(seq_user.nextval,'张三','123');
insert into tab_user values(seq_user.nextval,'李四','123');
insert into tab_user values(seq_user.nextval,'王五','123');
commit; -- 注意oracle执行增删改操作需要提交事物

-- 更新数据
update tab_user set password = '456' where username = '张三' ;

开启scott账户

alter user scott account unlock; --解锁scott账户
alter user scott identified by itcast;  -- 为scott账户设置密码 itcast

单行函数

字符函数

函数	说明
ASCII	返回对应字符的十进制值
CHR	给出十进制返回字符
CONCAT	拼接两个字符串
INITCAP	将字符串的第一个字母变为大写
INSTR	找出某个字符串的位置
INSTRB	找出某个字符串的位置和字节数
LENGTH	以字符给出字符串的长度
LENGTHB	以字节给出字符串的长度
LOWER	将字符串转换成小写
LPAD	使用指定的字符在字符的左边填充
LTRIM	在左边裁剪掉指定的字符
RPAD	使用指定的字符在字符的右边填充
RTRIM	在右边裁剪掉指定的字符
REPLACE	执行字符串搜索和替换
SUBSTR	取字符串的子串
SUBSTRB	取字符串的子串（以字节）
SOUNDEX	返回一个同音字符串
TRANSLATE	执行字符串搜索和替换
TRIM	裁剪掉前面或后面的字符串
UPPER	将字符串变为大写

-- 求字符串长度LENGTH
select length('ABCD') from dual;

-- 求字符串的子串SUBSTR
select substr('ABCD',2,2) from dual;

-- 字符串拼接CONCAT
select concat('ABC','D') from dual;

数值函数

函数	说明
ABS(value)	绝对值
CEIL(value)	大于或等于value 的最小整数
COS(value)	余弦
COSH(value)	反余弦
EXP(value)	e 的value 次幂
FLOOR(value)	小于或等于value 的最大整数
LN(value)	value 的自然对数
LOG(value)	value 的以10 为底的对数
MOD(value,divisor)	求模
POWER(value,exponent)	value 的exponent 次幂
ROUND(value,precision)	按precision 精度4 舍5 入
SIGN(value)	value 为正返回1;为负返回-1;为0 返回0.
SIN(value)	余弦
SINH(value)	反余弦
SQRT(value)	value 的平方根
TAN(value)	正切
TANH(value)	反正切
TRUNC(value,按precision)	按照precision 截取value
VSIZE(value)	返回value 在ORACLE 的存储空间大小

---- 四舍五入函数ROUND
select round(100.567) from dual
-- 四舍五入保留二位小数
select round(100.567,2) from dual

----截取函数TRUNC
select trunc(100.567) from dual
-- 截取数字,保留二位小数
select trunc(100.567,2) from dual

----取模MOD
select mod(10,3) from dual

日期函数

函数	描述
ADD_MONTHS	在日期date 上增加count 个月
GREATEST(date1,date2,. . .)	从日期列表中选出最晚的日期
LAST_DAY( date )	返回日期date 所在月的最后一天
LEAST( date1, date2, . . .)	从日期列表中选出最早的日期
MONTHS_BETWEEN(date2, date1)	给出Date2 - date1 的月数(可以是小数)
NEXT_DAY( date,’day’)	给出日期date 之后下一天的日期，这里的day 为星期， 如： MONDAY,Tuesday 等。
NEW_TIME(date,’this’,’other’)	给出在this 时区=Other 时区的日期和时间
ROUND(date,’format’)	未指定format 时，如果日期中的时间在中午之前，则将日期中的时间截断为12 A.M.(午夜，一天的开始),否则进到第二天。时间截断为12 A.M.(午夜，一天的开始),否则进到第二天。
TRUNC(date,’format’)	未指定format 时，将日期截为12 A.M.( 午夜，一天的开始).

----查询出emp表中所有员工入职距离现在几天。
select sysdate-e.hiredate from emp e;

----算出明天此刻
select sysdate+1 from dual;

----查询出emp表中所有员工入职距离现在几月。
select months_between(sysdate,e.hiredate) from emp e;

----查询出emp表中所有员工入职距离现在几年。
select months_between(sysdate,e.hiredate)/12 from emp e;

----查询出emp表中所有员工入职距离现在几周。
select round((sysdate-e.hiredate)/7) from emp e;

转换函数

函数	描述
CHARTOROWID	将字符转换到rowid 类型
CONVERT	转换一个字符节到另外一个字符节
HEXTORAW	转换十六进制到raw 类型
RAWTOHEX	转换raw 到十六进制
ROWIDTOCHAR	转换ROWID 到字符
TO_CHAR	转换日期格式到字符串
TO_DATE	按照指定的格式将字符串转换到日期型
TO_MULTIBYTE	把单字节字符转换到多字节
TO_NUMBER	将数字字串转换到数字
TO_SINGLE_BYTE	转换多字节到单字节

---日期转字符串
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;
---字符串转日期
select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;

空值处理函数

函数	描述
NVL（检测的值，如果为null 的值）	将null转化为指定的值
NVL2（检测的值，如果不为null 的值，如果为null 的值）；	对null值进行处理

----通用函数
---算出emp表中所有员工的年薪
----奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。
select e.sal*12+nvl(e.comm, 0) from emp e;

条件取值函数

函数	描述
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)	根据条件返回相应值

----oracle专用条件表达式
select e.ename,
        decode(e.ename,
          'SMITH',  '曹贼',
          'ALLEN',  '大耳贼',
          'WARD',  '诸葛小儿',
          '无名') "中文名"             
from emp e;

上边的语句也可以用case when then 语句来实现(mysql和oracle通用)

---条件表达式
---给emp表中员工起中文名
select e.ename,
      case e.ename
      when 'SMITH' then '曹贼'
      when 'ALLEN' then '大耳贼'
      when 'WARD' then '诸葛小儿'
      else '无名'
      end
from emp e;
---判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，
-----其余显示低收入
select e.sal,
      case
      when e.sal>3000 then '高收入'
      when e.sal>1500 then '中等收入'
      else '低收入'
      end
from emp e;

多行函数

函数	描述
count(统计值)	计算数量
sum(统计值)	计算总和
max(统计值)	计算最大值
min(统计值)	计算最小值
avg(统计值)	计算平均值

--多行函数【聚合函数】：作用于多行，返回一个值。
select count(1) from emp;---查询总数量
select sum(sal) from emp;---工资总和
select max(sal) from emp;---最大工资
select min(sal) from emp;---最低工资
select avg(sal) from emp;---平均工资

oracle数据库查询语句

分组查询

---分组查询
---查询出每个部门的平均工资
---分组查询中，出现在group by后面的原始列，才能出现在select后面
---没有出现在group by后面的列，想在select后面，必须加上聚合函数。
---聚合函数有一个特性，可以把多行记录变成一个值。
select e.deptno, avg(e.sal)--, e.ename
from emp e
group by e.deptno;


---查询出平均工资高于2000的部门信息
select e.deptno, avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000;


---所有条件都不能使用别名来判断。
--比如下面的条件语句也不能使用别名当条件
select ename, sal s from emp where sal>1500;

---查询出每个部门工资高于800的员工的平均工资
select e.deptno, avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno;


----where是过滤分组前的数据，having是过滤分组后的数据。
---表现形式：where必须在group by之前，having是在group by之后。
---查询出每个部门工资高于800的员工的平均工资
---然后再查询出平均工资高于2000的部门
select e.deptno, avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

试题

1. where和having区别？（面试和笔试会遇到） ```
2. where 不能放在GROUP BY 后面
3. HAVING 是跟GROUP BY 连在一起用的，放在GROUP BY 后面，此时的作用相当于WHERE

4. WHERE 后面的条件中不能有聚集函数，比如SUM(),AVG()等，而HAVING 可以 ```

5. mysql分组和oralce分组的不同（面试和笔试会遇到） ```

6. Oracle里，SELECT子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是聚合函数 MySQL里SELECT后面的字段并没有这样的限制，MySQL里SELECT后面可以是表里的任何字段或这些字段的表达式。

7. 在对分组之后的数据进行筛选的时候MySQL可以为表里的任何字段，而Oracle只能为分组字段 只要有group by，那么Oracle里除where子句的其他任何子句里出现的字段都必须是分组字段（子查询不受这个限制）。 ```

   连接查询

---多表查询中的一些概念
---笛卡尔积
select *
from emp e, dept d;

---等值连接
select *
from emp e, dept d
where e.deptno=d.deptno;

---内连接
select *
from emp e inner join dept d
on e.deptno = d.deptno;

---查询出所有部门，以及部门下的员工信息。【外连接】
select *
from emp e right join dept d
on e.deptno=d.deptno;

---查询所有员工信息，以及员工所属部门
select *
from emp e left join dept d
on e.deptno=d.deptno;

---oracle中专用外连接(了解)
select *
from emp e, dept d
where e.deptno(+) = d.deptno;

-- 自连接查询
---查询出员工姓名，员工领导姓名
---自连接：自连接其实就是站在不同的角度把一张表看成多张表。
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;

------查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select e1.ename, d1.dname, e2.ename, d2.dname
from emp e1, emp e2, dept d1, dept d2
where e1.mgr = e2.empno
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;

子查询

---子查询
---子查询返回一个值
---查询出工资和SCOTT一样的员工信息
select * from emp where sal in
(select sal from emp where ename = 'SCOTT');


---子查询返回一个集合
---查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in
(select sal from emp where deptno = 10);


---子查询返回一张表
---查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
---1，先查询出每个部门最低工资
select deptno, min(sal) msal
from emp
group by deptno;

---2，三表联查，得到最终结果。
select t.deptno, t.msal, e.ename, d.dname
from (select deptno, min(sal) msal
      from emp
      group by deptno) t, emp e, dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno;

分页查询

ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。

----oracle中的分页
---rownum行号：当我们做select操作的时候，
--每查询出一行记录，就会在该行上加上一个行号，
--行号从1开始，依次递增，不能跳着走。

----排序操作会影响rownum的顺序
select rownum, e.* from emp e order by e.sal desc

----如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询。
select rownum, t.* from(
select rownum, e.* from emp e order by e.sal desc) t;


----emp表工资倒叙排列后，每页五条记录，查询第二页。
----rownum行号不能写上大于一个正数。
select * from(
    select rownum rn, tt.* from(
          select * from emp order by sal desc
    ) tt
) where rn>5 and rn  <11