Oracle数据库是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。下面是Oracle数据库建表、修改表、查询语句。
–创建表
–语法：create table table_name(
— column_name datatype,
— …
— );
create table emp(
empno number(10) primary key ,–主键
ename varchar2(20) not null unique,–不能为空,唯一
job varchar2(10) default ‘匿名’,–默认值，用单引号
mgr number(10),
hiredate date,–默认格式DD-MM-YY
sal number(10,2),
comn number(10,2),
deptno number(10)
);
create table dept(
deptno number(10) primary key,
daname varchar(20) not null unique,
loc varchar2(20)
);
commit;–提交
rollback;–回滚
–查询表
select *from emp;
–查询具体的列
select ename , job , mgr from emp;
–查看表的结构
desc emp;
–复制表
create table emp_copy as select*from emp;
–已经存在一张表的结构，却复制其他表的数据
insert into emp_copy select *from emp;
–增加表字段
alter table emp_copy add (stjobdate date);
–修改字段
alter table emp_copy modify(stjobdate varchar2(20));
–删除字段
alter table emp_copy drop(stjobdate);
–修改列名
alter table emp_copy rename column ename to empname;
alter table emp rename column comn to comm;
–插入记录
–语法：insert into table_name(column1,column2,…) values(value1,value2,…);
insert into emp values(8001,’jodon’,’selesman’,7698,to_date(‘2016-12-13′,’yyyy-mm-dd’),1250,1400,30);
insert into dept values(40,’OPERATIONS’,’BOSTON’);
insert into dept(deptno,daname) values(50,’AILEN’);
–修改记录
–语法：update table_name set column1=value1,column2=value2,… where 条件;
update emp set empno=1000,ename=’Obama’ where empno=8001;
–可以按指定条件删除表中指定的记录,可以表的结构仍存在，优点支持事务,删除后仍能恢复
–语法:delete table_name where 条件；
delete from emp where empno=8000;
–剪切表:删除所有表中的记录，性能比delete高，同时维护索引，删除后无法恢复
–语法truncate table_name;
truncate table emp_copy;
–删除表：删除表结构和数据
drop table emp_copy;
–恢复删除表
flashback table emp_copy to before drop;
–列的别名：可用as作为标识符，也可以直接空格加双引号
select empno as “员工编号”,ename “员工姓名” from emp;
–where条件
select empno,job from emp where ename=’SMITH’;
select ename,sal from emp where job=’CLERK’;
–运算符：
–1、算术运算符：+ – * / 注意：表达式中只要有一个为空，整个表达式结果返回空：null + 1000 =null
–2、比较运算符：> >= < <= = 不等于：!= <>
–3、逻辑运算符：not and or [not]in [not]like between…and
–4、连接运算符：||
select 4+3 from dual;
select ename “姓名”,sal “薪水” from emp where sal<>800;
select ename “姓名”,sal “薪水” from emp where empno in(7369,7566,7839);–in()查询与括号指定的记录
select ename “姓名”,sal “薪水” from emp where sal not between 800 and 1500;
select ename “姓名”,sal “薪水” from emp where ename not like ‘SMITH’;
select ‘abc’||’def’ from dual;–连接：abcdef
select empno||’:’||ename “编号与姓名” from emp;
–运算符优先级：
–1、算术运算符
–2、连接运算符
–3、比较运算符
–4、is[not] null,[not] like,[not]in
–5、[not]between…and
–6、not and or
select ename “员工姓名”,sal “员工薪水” from emp where (sal>800) or (not (sal<=1500));
–获取唯一不重复的记录：distinct
select distinct job from emp;
–null值判断:is null 或者 is not null; 错误：column=null;
select *from emp where mgr is not null;
–排序:order by column1,column2,…;必须放在select语句的最后！两种：(默认)升序:asc 降序:desc
select ename,sal from emp order by sal desc,ename;
select ename “姓名”,sal “工资” from emp order by “工资” desc,”姓名”;–用别名排序
select ename,sal from emp order by 2 desc,1;–用列的顺序排序
–模糊查询[not]like
–通配符：%：任意0-n个任意字符 _:任意一个字符
select *from emp where ename like ‘%S%’;–含有S字符
select *from emp where ename like ‘S%’;–S字符在前
select *from emp where ename not like ‘%S’;–S字符后
select *from emp where ename like ‘_L%’;–第二个字符为L
–字符串函数
–大小写转换
select lower(ename) from emp;–小写
select upper(ename) from emp;–大写
select lower(‘AAA’) from dual;–aaa
select initcap(‘AAA’) from dual;–首字母大写：Aaa
select concat(‘aaa’,’bbb’) from dual;–连接字符：aaabbb
select lpad(‘abcde’,10，’*’) from dual;–左边填充：*****abcde
select rpad(‘abcde’,10,’*’) from dual;–右边填充：abcde*****
select trim(‘=’ from ‘====abc==defg===’) from dual;–两边压缩：abc==defg
select ltrim(‘====abc==defg===’,’=’) from dual;–左边压缩：abc==defg===
select rtrim(‘====abc==defg===’,’=’) from dual;–右边压缩：====abc==defg
select length(ename)from emp;–获取字符串的长度
select substr(ename,2,3) “姓名” from emp;–从指定的列第2个字符开始截取3个字符
select instr(‘abcdef’,’cd’)from dual;–获取子字符串所在的位置
select chr(65)from dual;–获取字符
select ascii(‘a’)from dual;–获取奥斯卡码（ASCII）
select replace(‘abcfdefdhifjk’,’f’,’*’) from dual;–将f替换成*：abc*de*dhi*jk
–数值函数
–1、abs绝对值
–2、ceil 只产生大于或等于指定值的最小整数
–3、floor 只产生小于或等于指定值的最大整数
–4、mod 求余
–5、power 求指数
–6、sqrt 求平方根
–7、round 截断保留，四舍五入
–8、trunc 截断保留，不四舍五入
select abs(-3)from dual;
select ceil(10.5)from dual;
select floor(10.5)from dual;
select mod(5,2)from dual;–余1
select power(2，3)from dual;–
select sqrt(4)from dual;
select round(5.55,1)from dual;–小数点后面1位被截断，并四舍五入：5.6
select round(5.5,0)from dual;–小数点截断，并四舍五入，保留整数：6
select trunc(15.5，-1)from dual;–小数点前面1位被截断，并不四舍五入：10
–日期函数
–1、sysdate
–2、last_day()本月最后一天
–3、add_months（d,n）
–4、months_between(f,s)
–5、current_timestamp
–6、extract 找出日期或间隔值得字段值
–日期格式：
YY,YYYY
Q 季度
MM 月份数
WW 当年第几周
W 本月第几周
DDD 当年第几天
DD 本月第几天
D 本星期第几天
DY 本星期第几天缩写
–时间格式：
24小时制：HH24:MI:SS
12小时制：HH12:MI:SS
select sysdate from dual;–系统时间
select current_timestamp from dual;–系统时间戳
select last_day(sysdate) from dual;
select add_months(sysdate,3) from dual;
select months_between(sysdate,hiredate),sysdate,hiredate from emp;
select extract(month from sysdate) “This Month” from dual;–找到当前系统时间的月份
–nvl函数：判定空值
–1、nvl格式：nvl(expr1,expr2)：如果expr1为空，则返回expr2
–2、nvl2格式：nvl2(expr1,expr2,expr3)：如果expr1不为空，则返回expr2；如果expr1为空，则返回expr3
–3、nullif函数：nvullif(expr1,expr2):如果expr1,expr2相等则返回空（null），否则返回第一个值expr1
–4、coalesce函数：coalesce(expr1,expr2,expr3,…exprn):返回expr1,expr2,expr3,…exprn中第一个部位null的值
select sal,comm,sal+nvl(comm,0) “工资总和” from emp;
select sal,comm,sal+nvl2(comm,10000,0) “工资总和” from emp;
select sal,comm,sal+nullif(sal,comm) “工资总和” from emp;
select coalesce(null,null,11,22,null,33)from dual;
–转换函数
–1、数值转为字符串
–2、字符串转为数值
–3、日期到字符串
–4、字符串到日期
select ‘aaa’||123 from dual;
select to_char(123) from dual;
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)from dual;
select to_number(‘1234’) from dual;
select to_date(‘2016-12-12′,’yyyy-mm-dd’)from dual;–格式要匹配
–多行函数
–1、count()计数
–2、max()最大值
–3、min()最小值
–4、sum()求和
–5、avg()求平均值
select count(*) 总人数 from emp;–不忽略null
select count(comm) 总人数 from emp;–count(列名)会忽略null
select count(*) “总人数”,max(sal) “最高薪水”, min(sal) “最低薪水” ,sum(sal) “薪水总和”,trunc(avg(sal),2) “平均薪水” from emp;
–分组查询 group by
–select 之后的非参数列必须出现在group by 列的之后
select deptno “部门编号”,count(*) “部门总人数”,max(sal) “部门最高薪水”, min(sal) “部门最低薪水” ,sum(sal) “部门薪水总和”,trunc(avg(sal),2) “部门平均薪水”
from emp
group by deptno;
–连接查询
–笛卡尔积:行乘列加
select *from emp,dept;
–等值查询
–内连接：where , a inner join b on 条件;内连接可以省略inner
select * from emp,dept where emp.deptno = dept.deptno;
select * from emp inner join dept on emp.deptno = dept.deptno;
–给表取别名:在表后面空格+别名
select * from emp e,dept d where e.deptno = d.deptno;
–全外连接outer join on
select dname，ename from dept d full outer join emp e on e.deptno = d.deptno;
–左外连接left outer join on 右连接right outer join on 全外连接：full outer join on
select dname，ename from dept d left outer join emp e on e.deptno = d.deptno;–左连接
select ename,dname from dept d left join emp e on e.deptno = d.deptno;–右连接
–n张表连接，n-1个条件
select *from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
–自连接：自己连接自己
select e1.ename “员工姓名”,e2.ename “直接领导”from emp e1 left join emp e2
on e1.mgr=e2.empno;
–子查询
select * from emp where deptno=(select deptno from emp where ename=’SMITH’);
select * from emp where sal>(select sal from emp where ename=’SMITH’);
–all any exists存在
select ename from emp where exists(select *from emp where sal>2000);