oracle之sql查询
oracle表管理
表名和列的命名规则：
必须以字母开头
长度不能超过30字符
不能使用oracle的保留字
只能使用如下字符：A-Z，a-z，0-9，$,#等
字符型：
char：定长 最大2000字符
例子：char(10) '小韩' 前四个字符放'小韩'，后面添6个空格补全
比如×××定长，查询速度很快
varchar2(20) 变长 最大4000字符，oracle推荐使用的类型
例子：varchar2(10) '小韩' oracle分配四个字符，这样可以省略空间
clob(character large object) 字符型大对象，最大4G
数字类型
number 范围10的-38次方，10的38次方
可以表示整数，也可以表示小数
number(5,2),表示有小数部位，且最大允许5位有效数，2位小数,number(5)表示最大允许5位整数
日期类型
date 包含年月日和时分秒
timestamp精度高
图片类型
blob 二进制数据 可以存放图片，声音视频 4G（如果需要安全很高就存放数据库中）
建表语句：
SQL>create table stu(xh number(4),xm varchar2(20),sex char(3),birthday date,sal number(7,2));
或者：
SQL> create table stu(
2 xh number(4),
3 xm varchar2(20),
4 sex char(3),
5 sal number(7,2)
6 );
Table created.
SQL> desc stu; 查看表结构
Name Null? Type
----------------------------------------- -------- ----------------------------
XH NUMBER(4)
XM VARCHAR2(20)
SEX CHAR(3)
BIRTHDAY DATE
SAL NUMBER(7,2)
删表：
SQL> drop table student; 由于先前已经有张表为student
Table dropped.
向表stu中添加一个新字段
SQL> alter table stu add (classid number(2));
Table altered.
修改表stu中的某一个字段
SQL> alter table stu modify (xm varchar2(30));
Table altered.
删除表stu中某一个字段
SQL> alter table stu drop column sal;
Table altered.
修改表的名字
SQL> rename stu to student;
Table renamed.
向表student中插入数据，先查看表结构
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
XH NUMBER(4)
XM VARCHAR2(30)
SEX CHAR(3)
BIRTHDAY DATE
CLASSID NUMBER(2)
SQL> insert into student values (1,'小黄','F','9-6月-16',1);
insert into student values (1,'小黄','F','9-6月-16',1)
*
ERROR at line 1:
ORA-01843: not a valid month
插入数据一直出错，月份一直有问题，百度了一下，解决方法如下
先查看数据库支持的是什么语言的
SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE'
2 ;
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_LANGUAGE
AMERICAN 由这里可以看出是AMERICAN
SQL> show parameter nls_language;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_language string AMERICAN
由这条语句查看也是AMERICAN，所以猜想不能使用汉字模式，修改如下
SQL> insert into student values (1,'小黄','F','9-July-16',1);
1 row created.
表示插入数据成功，查看数据
SQL> select * from student;
XH XM SEX BIRTHDAY CLASSID
---------- ------------------------------ --- --------- ----------
1 ?????? F 09-JUL-16 1
可以看出插入数据最好匹配数据库原始支持的语言
修改默认日期格式：
alter session set nls_date_format = 'yyyy-mm-dd';--->修改后为2016-06-09
SQL> alter session set nls_date_format='yyyy-mm-dd';
Session altered.
SQL> insert into student values (2,'xiaobai','F','2016-09-06',2);
1 row created.
SQL> select * from student;
XH XM SEX BIRTHDAY CLASSID
---------- ------------------------------ --- ---------- ----------
1 ?????? F 2016-07-09 1
2 xiaobai F 2016-09-06 2
插入数值也可以为空
SQL> insert into student values (3,'xiaohua','F',null,3);
1 row created.
SQL> select * from student;
XH XM SEX BIRTHDAY CLASSID
---------- ------------------------------ --- ---------- ----------
1 ?????? F 2016-07-09 1
2 xiaobai F 2016-09-06 2
3 xiaohua F 3
查出生日为空的同学：
错误操作如下：
SQL> select * from student where birthday='';
no rows selected
正确操作如下：
SQL> select * from student where birthday is null;
XH XM SEX BIRTHDAY CLASSID
---------- ------------------------------ --- ---------- ----------
3 xiaohua F 3
SQL> select * from student where birthday is not null;
XH XM SEX BIRTHDAY CLASSID
---------- ------------------------------ --- ---------- ----------
1 ?????? F 2016-07-09 1
2 xiaobai F 2016-09-06 2
update修改某个字段：
SQL> update student set classid=2 where xm='xiaohua';
1 row updated.
SQL> select * from student;
XH XM SEX BIRTHDAY CLASSID
---------- ------------------------------ --- ---------- ----------
1 ?????? F 2016-07-09 1
2 xiaobai F 2016-09-06 2
3 xiaohua F 2
模拟删除用户能否恢复
SQL> savepoint a; 预先定义一个保存点
Savepoint created.
SQL> delete from student; 模拟删除表
3 rows deleted.
SQL> select * from student; 查看表数据，发现没有数据
no rows selected
SQL> rollback to a; rollback回滚到保存点
SQL> select * from student; 查看数据，发现数据恢复，回滚成功
XH XM SEX BIRTHDAY CLASSID
---------- ------------------------------ --- ---------- ----------
1 ?????? F 2016-07-09 1
2 xiaobai F 2016-09-06 2
3 xiaohua F 2
Rollback complete.
于是结论如下：
delete from table,删除表所有记录，但表结构还在，写日志，可以恢复
drop table student:删除表的结构和数据
truncate table student:删除表中的所有记录，但表结构还在，但不写日志，无法找回删除的记录，速度很快
表空间：就是存放某些表的那些空间（oracle事先就有很多表空间）
表用来存放数据
scott用户下面的表
1、emp表
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
EMPNO：雇员number
ENAME：雇员名字
JOB：工作职位（clerk：员工）
MGR：雇员上级id
HIREDATE：入职时间
SAL:薪水工资
COMM：奖金
DEPTNO：所在的部门，部门编号
2、dept部门表
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
DEPTNO：所在的部门，部门编号
ACCOUNTING：财务部
RESEARCH：研发
SALES：销售
显示一张表有多少行数据？
SQL> select count(*) from emp;
COUNT(*)
----------
14
关键字
1、distinct过滤掉重复的行
SQL> select deptno,job from emp;
DEPTNO JOB
---------- ---------
20 CLERK
30 SALESMAN
30 SALESMAN
20 MANAGER
30 SALESMAN
30 MANAGER
10 MANAGER
20 ANALYST
10 PRESIDENT
30 SALESMAN
20 CLERK
30 CLERK
20 ANALYST
10 CLERK
SQL> select distinct deptno,job from emp;
DEPTNO JOB
---------- ---------
20 CLERK
30 SALESMAN
20 MANAGER
30 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
Oracle一般不区分大小写，但如下情况却区分
SQL> select * from emp where ename='smith';
no rows selected
SQL> select * from emp where ename='SMITH';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 1980-12-17 800
当匹配表中某内容时，需要区分大小写
2、使用算是表达式
SQL> select ename,sal*12 from emp;
ENAME SAL*12
---------- ----------
SMITH 9600
ALLEN 19200
WARD 15000
JONES 35700
MARTIN 15000
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600
当然也可以将列去个另外的别名
SQL> select ename,sal*12 nianxin from emp;
ENAME NIANXIN 此时这里就使用了别名替换了sal*12
---------- ----------
SMITH 9600
ALLEN 19200
如果字段中有一个值为null，那么计算出来的值也是null，该如何准确查询呢？
SQL> select ename,sal*13+comm*13,comm from emp;
ENAME SAL*13+COMM*13 COMM 由于字段comm有些员工并没有，所以有些为空
---------- -------------- ---------
SMITH
ALLEN 24700 300.00
WARD 22750 500.00
JONES
MARTIN 34450 1400.00
BLAKE
CLARK
SCOTT
KING
TURNER 19500 0.00
ADAMS
JAMES
FORD
MILLER
如何处理null值，使用nvl函数
SQL> select ename,sal*13+nvl(comm,0)*13 as 年工资,comm from emp; ----》当comm为空值时，用后面的0替换，当comm有值时，用自己本身的值计算
ENAME SAL*13+NVL(COMM,0)*13 COMM
---------- --------------------- ---------
SMITH 10400
ALLEN 24700 300.00
WARD 22750 500.00
JONES 38675
MARTIN 34450 1400.00
BLAKE 37050
CLARK 31850
SCOTT 39000
KING 65000
TURNER 19500 0.00
ADAMS 14300
JAMES 12350
FORD 39000
MILLER 16900