oracle常用命令、sql语句

DESC table_name;

--创建表(表名为test)
create tablespace test
--表空间存放路径(注意目录权限)
datafile '/data/oracle/dbf/test.dbf'
--表空间初始大小
size 100m
--开启自动扩容
autoextend on
--下次自动扩容10m
next 10m;

--创建用户名(用户名为testuser)
create user testuser
--设置密码(123456)
identified by 123456
--指定归属的表空间(test)
default tablespace test;

--将testuser用户归属于dba组
grant dba to testuser;

create table 表名  (    
字段 类型(长度) primary key,    
字段 类型(长度)，
...
...    
);

(1)字符型(！必须指定长度！)
CHAR        固定长度的字符类型，最多2000个字节(不足长度的补充空格)
VARCHAR2    可变长度的字符类型，最多4000个字节(不足长度则忽略不补充空格，节省空间)
LANG        大文本类型，最多存储2G

(2)数值型
NUMBER    数值类型，默认18个字节
范例：(总共字节数，小数后字节数)
    NUMBER(5)          最大数值99999
    NUMBER(5,2)        最大值999.99

(3)日期型
DATE        日期时间型，精确到秒
TIMESTAMP    精确到毫秒(毫秒为9位)

(4)二进制型(大数据类型)
CLOB        存储字符，最大可以4G
BLOB        存储图片、音频、视频、exe、编译程序等二进制程序，最大可以4G

--创建表
create table T_OWNERS (
ID     NUMBER primary key,
NAME   VARCHAR2(30),
ADDRESSID     NUMBER,
HOUSENUMBER   VARCHAR2(30),
WATERMETER    VARCHAR2(30),
ADDDATE       DATE,
OWNERTYPEID   NUMBER
);

--去重 distinct
--语法：select distinct <字段名> from <表名>
SQL> select distinct id from dept;



---统计deptno有多少个类型
--先去除，再count
SQL> select count(distinct deptno) from emp;
COUNT(DISTINCTDEPTNO)
---------------------
                    3

--别名 as (也可省略)
--注，别名如果要定义格式要使用双引号
SQL> select deptno a, dname b, loc "c" from dept;
  A B              c
--- -------------- -------------
 10 ACCOUNTING     NEW YORK
 20 RESEARCH       DALLAS
 30 SALES          CHICAGO
 40 OPERATIONS     BOSTON

--select 查询日期大于1981/12/03
--order by排序
-- 排序多个就逗号
-- desc降序
--注！！！日期要转为英文
SQL> select * from emp where  HIREDATE >= '03-December-1981' order by hiredate desc;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30


 --查询81年的
 SQL> select * from emp where  HIREDATE like '%81' order by hiredate desc;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30


 --先按工资降序排序，再姓名排序排序(当出现工资相同的的时候，才会出现姓名排序)
SQL> select * from emp order by sal desc,ename;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

---计算sal/mgr大于20%的员工
SQL> select * from emp where (sal/mgr) >0.2;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 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
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20


 --计算comm占比大于50%
 SQL> select * from emp where comm/(comm + sal ) >0.5;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30



 --计算comm*12，并as别名为cc,打印非空的姓名
SQL> select ename,comm*12 as cc from emp where comm*12 is not null;
ENAME              CC
---------- ----------
ALLEN            3600
WARD             6000
MARTIN          16800
TURNER              0

--like 配合 %或者_ 
--%包含零个或多个字符的任意字符串；_表示一个字符。
select * from table1 where code like '%abc';   查询列值以abc结尾的
Select * from table1 where code like 'abc%';   查询列值以abc开头的
Select * from table1 where code like '%abc%';  查询列值包含abc的;
Select * from table1 where code like 'ab%c';   查询列值以ab开头，c结尾的

Select * from table1 where code like 'A%';    查询列值以大写A开头
Select * from table1 where code like '_A%';   查询列值第二个字符是大A的

--比较
--betweent <范围> and <范围>                  等于：and
--not betweent <范围> and <范围>               等于 不再这个区间的 
--in (值)                                     等于：=和or
--like                                        等于：像，需配合%或者_
--is null                                     等于：空
--is not null                                 等于：非空

--转义ESCAPE
--在字段值中包含于特殊符号或者特殊含义的字符可以使用escape进行转义，使用Escape可以转义任意字符，且放置在语句末尾。
--如下，筛选'SA_'的字符，因为_在like中代表一个字符，为其转为_值，使用\表示转义符，且在末尾escape标识转义符为'\'
--escape '\' 定义转义符为'\''
SQL> select * from test where name like '%SA\_%' escape '\';
        ID NAME                        AGE    AVG
---------- -------------------- ---------- ------
         4 SA_a                          4  14.00
         5 SA_b                          5  15.00
         6 SA_c                          6  16.00

--is null
SQL> select * from test  where avg is null;
        ID NAME                        AGE    AVG
---------- -------------------- ---------- ------
         4 SA_a                          4 
         5 SA_b                          5 
         6 SA_c                          6 


--is not null
SQL> select * from test  where avg is not null;
        ID NAME                        AGE    AVG
---------- -------------------- ---------- ------
         1 a                            11   1.10
         2 b                            22  22.32
         3 c                            33  33.33

--and


--or


--not

SQL> select * from test;
        ID NAME                        AGE    AVG
---------- -------------------- ---------- ------
         1 a                            11   1.10
         2 b                            22  22.32
         3 c                            33  33.33
         4 SA_a                          4 
         5 SA_b                          5 
         6 SA_c                          6 
6 rows selected

--not in (in必须是匹配值)
SQL> select * from test where name not in ('a','b');
        ID NAME                        AGE    AVG
---------- -------------------- ---------- ------
         3 c                            33  33.33
         4 SA_a                          4 
         5 SA_b                          5 
         6 SA_c                          6 


--not like (like 和%一一匹配，所以多个条件多个not like)
SQL> select * from test where name not like '%a%' and name not like '%b%';
        ID NAME                        AGE    AVG
---------- -------------------- ---------- ------
         3 c                            33  33.33
         6 SA_c                          6 

--order by  必须放置末尾
--asc       升序(默认排序方式，可以省略)
--desc      降序
--注！！！order by 排序也可以指定未在select中的字段
--语法: select * from <表> order by <字段名> [asc|desc];
--范例： 以id字段名排序
select name,age from test order by id;

--范例： 投影列排序，如下那么name的投影列顺序是为1，age投影列顺序是为2；则order by 1，则排序的是第一个name
select name，age from test order by 1;

--order by 多列排序
--可以用多列排序查询结果。在 ORDER BY 子句中，多个指定的列名之间用逗号分开。如果想要对某个列倒序排序需则在该列名后面指定 DESC。
--范例：id为升序，age为降序
select name,age from test order by id,age desc;