首页 > 数据库 > Oracle >

【2017/4/23】oracle之sql(一)

2017-04-24

解锁scott用户设置密码tiger SQL> show user USER is "SYS " SQL> SQL> alter user scott identified by tiger; 用户设置密码tiger User altered SQL> alter user scott account un

解锁scott用户设置密码tiger

SQL> show user

USER is "SYS"

SQL>

SQL> alter user scott identified by tiger; #用户设置密码tiger

User altered.

SQL> alter user scott account unlock; #解锁用户scott

User altered.

SQL> conn scott/tiger #登录scott用户

Connected.

SQL> show user

USER is "SCOTT"

SQL>

SQL>

sql常识

SQL> select * from tab; #查看用户下所有的表

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

T1 TABLE

SQL> desc dept; #描述dept表的所有列信息

Name Null? Type

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

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> select dname,loc from dept; #查询指定的列用逗号分开

DNAME LOC

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

ACCOUNTING NEW YORK

RESEARCH DALLAS

SALES CHICAGO

OPERATIONS BOSTON

SQL>

1.书写关键字,不区分大小写,除非“”

2.可以分行书写语句,关键词一个字母都不能少,也不可分行

3.排版结尾加上;

在sqlplus中默认:

1.字符和日期靠左对齐

SQL> select sysdate from dual;

SYSDATE

---------

10-APR-17

2.数字靠右对齐

3.全部大写

NULL空值

Null Value空值不代表没有,只是不明是多少,算术运算为空

SQL> select ename,sal,comm,sal+comm from emp; #空值算术运算为空

ENAME SAL COMM SAL+COMM

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

SMITH 800

ALLEN 1600 300 1900

WARD 1250 500 1750

JONES 2975

MARTIN 1250 1400 2650

BLAKE 2850

CLARK 2450

SCOTT 3000

KING 5000

TURNER 1500 0 1500

ADAMS 1100

ENAME SAL COMM SAL+COMM

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

JAMES 950

FORD 3000

MILLER 1300

14 rows selected.

SQL> select ename,sal,comm,sal+nvl(comm,0) from emp; #将空值=0,进行算术运算

ENAME SAL COMM SAL+NVL(COMM,0)

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

SMITH 800 800

ALLEN 1600 300 1900

WARD 1250 500 1750

JONES 2975 2975

MARTIN 1250 1400 2650

BLAKE 2850 2850

CLARK 2450 2450

SCOTT 3000 3000

KING 5000 5000

TURNER 1500 0 1500

ADAMS 1100 1100

ENAME SAL COMM SAL+NVL(COMM,0)

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

JAMES 950 950

FORD 3000 3000

MILLER 1300 1300

14 rows selected.

SQL>

别名

SQL> select deptno,dname from dept;

DEPTNO DNAME

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

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

SQL> select deptno "deptno",dname "dname" from dept;

deptno dname #别名后,heading是小写

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

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

SQL>

连接符||和字面字符‘’

连接符最后的结果是字符串

SQL> select 'name:'||empno from emp;

'NAME:'||EMPNO

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

name:7369

name:7499

name:7521

name:7566

name:7654

name:7698

name:7782

name:7788

name:7839

name:7844

name:7876

'NAME:'||EMPNO

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

name:7900

name:7902

name:7934

14 rows selected.

SQL> select ename,'******'sal from emp; #将sal列显示为****

ENAME SAL

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

SMITH ******

ALLEN ******

WARD ******

JONES ******

MARTIN ******

BLAKE ******

CLARK ******

SCOTT ******

KING ******

TURNER ******

ADAMS ******

ENAME SAL

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

JAMES ******

FORD ******

MILLER ******

14 rows selected.

SQL>

SQL> select ename||' is work ' || job from emp;

ENAME||'ISWORK'||JOB

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

SMITH is work CLERK

ALLEN is work SALESMAN

WARD is work SALESMAN

JONES is work MANAGER

MARTIN is work SALESMAN

BLAKE is work MANAGER

CLARK is work MANAGER

SCOTT is work ANALYST

KING is work PRESIDENT

TURNER is work SALESMAN

ADAMS is work CLERK

ENAME||'ISWORK'||JOB

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

JAMES is work CLERK

FORD is work ANALYST

MILLER is work CLERK

14 rows selected.

SQL>

转义字符

SQL> select ename||'"s work '||job from emp;

ENAME||'"SWORK'||JOB

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

SMITH"s work CLERK

ALLEN"s work SALESMAN

WARD"s work SALESMAN

JONES"s work MANAGER

MARTIN"s work SALESMAN

BLAKE"s work MANAGER

CLARK"s work MANAGER

SCOTT"s work ANALYST

KING"s work PRESIDENT

TURNER"s work SALESMAN

ADAMS"s work CLERK

ENAME||'"SWORK'||JOB

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

JAMES"s work CLERK

FORD"s work ANALYST

MILLER"s work CLERK

14 rows selected.

SQL> select ename||q'['s work ]'||job from emp;

ENAME||Q'['SWORK]'||JOB

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

SMITH's work CLERK

ALLEN's work SALESMAN

WARD's work SALESMAN

JONES's work MANAGER

MARTIN's work SALESMAN

BLAKE's work MANAGER

CLARK's work MANAGER

SCOTT's work ANALYST

KING's work PRESIDENT

TURNER's work SALESMAN

ADAMS's work CLERK

ENAME||Q'['SWORK]'||JOB

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

JAMES's work CLERK

FORD's work ANALYST

MILLER's work CLERK

14 rows selected.

SQL>

SQL> select ename||q'('s work )'||job from emp;

SQL> select ename||q'{'s work }'||job from emp;

SQL> select ename||q'a's work a'||job from emp;

只要是{}[]()这种成对出现的都可以。

去除重复行

SQL> select deptno from emp;

DEPTNO

----------

20

30

30

20

30

30

10

20

10

30

20

DEPTNO

----------

30

20

10

14 rows selected.

SQL> select distinct deptno from emp;

DEPTNO

----------

30

20

10

SQL>

SQL> select distinct(deptno) from emp;

DEPTNO

----------

30

20

10

SQL> select unique deptno from emp;

DEPTNO

----------

30

20

10

SQL> select unique(deptno) from emp;

DEPTNO

----------

30

20

10

SQL>

where子句

SQL> select ename,sal from emp where ename='scott';

no rows selected

SQL> select ename,sal from emp where ename='SCOTT'; #'字符'

ENAME SAL

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

SCOTT 3000

SQL>

SQL> select ename from emp where hiredate='19-APR-87';#‘日期’默认DD-MON-RR

ENAME

----------

SCOTT

SQL>

SQL> select ename,sal from emp where sal=3000;#数字不需要‘’

ENAME SAL

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

SCOTT 3000

FORD 3000

SQL>

SQL> select ename,sal from emp where sal<>3000;#不等于

ENAME SAL

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

SMITH 800

ALLEN 1600

WARD 1250

JONES 2975

MARTIN 1250

BLAKE 2850

CLARK 2450

KING 5000

TURNER 1500

ADAMS 1100

JAMES 950

ENAME SAL

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

MILLER 1300

12 rows selected.

SQL> select ename,sal from emp where sal^=3000;

SQL> select ename,sal from emp where sal!=3000;

SQL> select ename,sal from emp where sal between 2000 and 3000;#[2000,3000]

ENAME SAL

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

JONES 2975

BLAKE 2850

CLARK 2450

SCOTT 3000

FORD 3000

SQL> select ename,sal from emp where sal>2000 and sal<3000;#(2000,3000)

ENAME SAL

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

JONES 2975

BLAKE 2850

CLARK 2450

SQL>

SQL> select ename,sal from emp where sal not between 2000 and 3000;#取反

ENAME SAL

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

SMITH 800

ALLEN 1600

WARD 1250

MARTIN 1250

KING 5000

TURNER 1500

ADAMS 1100

JAMES 950

MILLER 1300

9 rows selected.

SQL>

SQL> select ename,sal from emp where sal in (2000,3000);#找到就显示

ENAME SAL

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

SCOTT 3000

FORD 3000

SQL>

SQL> select ename,comm from emp where comm is null;

ENAME COMM

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

SMITH

JONES

BLAKE

CLARK

SCOTT

KING

ADAMS

JAMES

FORD

MILLER

10 rows selected.

SQL> select ename,comm from emp where comm is not null;#包括数字0

ENAME COMM

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

ALLEN 300

WARD 500

MARTIN 1400

TURNER 0

SQL>

SQL> select ename,comm from emp where comm>0;

ENAME COMM

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

ALLEN 300

WARD 500

MARTIN 1400

SQL>

SQL> select ename,comm from emp where ename like &#39;A%&#39;;#A开头的

ENAME COMM

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

ALLEN 300

ADAMS

SQL> select ename,comm from emp where ename like &#39;%S&#39;;#S结尾的

ENAME COMM

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

JONES

ADAMS

JAMES

SQL>

SQL> select ename,comm from emp where ename like &#39;_A%&#39;;#_代表一个字符

ENAME COMM

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

WARD 500

MARTIN 1400

JAMES

SQL> select ename,comm from emp where ename like &#39;__A%&#39;;

ENAME COMM

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

BLAKE

CLARK

ADAMS

SQL>

not and or优先级

SQL> select ename,empno,sal from emp where empno=7566 or comm>0 and sal<2000; #先进行and后or

ENAME EMPNO SAL

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

ALLEN 7499 1600

WARD 7521 1250

JONES 7566 2975

MARTIN 7654 1250

SQL>

order by排序,默认升序

默认NULL是最大值

SQL> select ename,sal,comm from emp order by sal desc;#desc是降序

ENAME SAL COMM

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

KING 5000

FORD 3000

SCOTT 3000

JONES 2975

BLAKE 2850

CLARK 2450

ALLEN 1600 300

TURNER 1500 0

MILLER 1300

WARD 1250 500

MARTIN 1250 1400

ENAME SAL COMM

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

ADAMS 1100

JAMES 950

SMITH 800

14 rows selected.

SQL> select ename,sal,comm from emp order by comm desc;

ENAME SAL COMM

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

SMITH 800

CLARK 2450

FORD 3000

JAMES 950

ADAMS 1100

JONES 2975

BLAKE 2850

MILLER 1300

SCOTT 3000

KING 5000

MARTIN 1250 1400

ENAME SAL COMM

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

WARD 1250 500

ALLEN 1600 300

TURNER 1500 0

14 rows selected.

SQL>

可以使用别名简化

SQL> select ename,sal,comm,sal+nvl(comm,0) from emp order by sal+nvl(comm,0) desc;

ENAME SAL COMM SAL+NVL(COMM,0)

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

KING 5000 5000

FORD 3000 3000

SCOTT 3000 3000

JONES 2975 2975

BLAKE 2850 2850

MARTIN 1250 1400 2650

CLARK 2450 2450

ALLEN 1600 300 1900

WARD 1250 500 1750

TURNER 1500 0 1500

MILLER 1300 1300

ENAME SAL COMM SAL+NVL(COMM,0)

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

ADAMS 1100 1100

JAMES 950 950

SMITH 800 800

14 rows selected.

SQL> select ename,sal,comm,sal+nvl(comm,0) ssr from emp order by ssr desc;

ENAME SAL COMM SSR

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

KING 5000 5000

FORD 3000 3000

SCOTT 3000 3000

JONES 2975 2975

BLAKE 2850 2850

MARTIN 1250 1400 2650

CLARK 2450 2450

ALLEN 1600 300 1900

WARD 1250 500 1750

TURNER 1500 0 1500

MILLER 1300 1300

ENAME SAL COMM SSR

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

ADAMS 1100 1100

JAMES 950 950

SMITH 800 800

14 rows selected.

SQL> select ename,sal,comm,sal+nvl(comm,0) from emp order by 4 desc;

ENAME SAL COMM SAL+NVL(COMM,0)

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

KING 5000 5000

FORD 3000 3000

SCOTT 3000 3000

JONES 2975 2975

BLAKE 2850 2850

MARTIN 1250 1400 2650

CLARK 2450 2450

ALLEN 1600 300 1900

WARD 1250 500 1750

TURNER 1500 0 1500

MILLER 1300 1300

ENAME SAL COMM SAL+NVL(COMM,0)

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

ADAMS 1100 1100

JAMES 950 950

SMITH 800 800

14 rows selected.

SQL>

SQL> select ename,deptno,sal from emp order by deptno,sal desc;#先升序,后降序

ENAME DEPTNO SAL

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

KING 10 5000

CLARK 10 2450

MILLER 10 1300

SCOTT 20 3000

FORD 20 3000

JONES 20 2975

ADAMS 20 1100

SMITH 20 800

BLAKE 30 2850

ALLEN 30 1600

TURNER 30 1500

ENAME DEPTNO SAL

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

MARTIN 30 1250

WARD 30 1250

JAMES 30 950

14 rows selected.

SQL>

替代变量

SQL> define ssr=7788

SQL> select ename,sal from emp where empno=&ssr;

old 1: select ename,sal from emp where empno=&ssr

new 1: select ename,sal from emp where empno=7788

ENAME SAL

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

SCOTT 3000

SQL> undefine ssr=7788

SQL> select ename,sal from emp where empno=&ssr;

Enter value for ssr: 7788

old 1: select ename,sal from emp where empno=&ssr

new 1: select ename,sal from emp where empno=7788

ENAME SAL

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

SCOTT 3000

SQL>

相关文章
最新文章
热点推荐