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(*)

----------

关键字

1、distinct过滤掉重复的行

SQL> select deptno,job from emp;

DEPTNO JOB

---------- ---------

20 CLERK

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

新闻名称：oracle之sql查询
浏览地址：http://scyanting.com/article/joseds.html

oracle之sql查询

其他资讯