解锁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空值
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 'A%';#A开头的
ENAME COMM
---------- ----------
ALLEN 300
ADAMS
SQL> select ename,comm from emp where ename like '%S';#S结尾的
ENAME COMM
---------- ----------
JONES
ADAMS
JAMES
SQL>
SQL> select ename,comm from emp where ename like '_A%';#_代表一个字符
ENAME COMM
---------- ----------
WARD 500
MARTIN 1400
JAMES
SQL> select ename,comm from emp where ename like '__A%';
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=7788SQL> 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>