資料庫查詢基本命令

weixin_34148340發表於2017-03-30
資料庫

oracle 資料庫的scott帳號。

<>作為查詢條件時，可以使用！= 來替換。

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

單表查詢：

SQL> select ename,job,sal from emp;

ENAME JOB SAL

SMITH CLERK 800.00
ALLEN SALESMAN 1600.00
WARD SALESMAN 1250.00
JONES MANAGER 2975.00
MARTIN SALESMAN 1250.00
BLAKE MANAGER 2850.00
CLARK MANAGER 2450.00
SCOTT ANALYST 3000.00
KING PRESIDENT 5000.00
TURNER SALESMAN 1500.00
ADAMS CLERK 1100.00
JAMES CLERK 950.00
FORD ANALYST 3000.00
MILLER CLERK 1300.00
14 rows selected

查詢全部列：

select * from emp;

查詢經過計算的值：

SQL> select ename,job,6000-sal from emp;

ENAME JOB 6000-SAL

SMITH CLERK 5200
ALLEN SALESMAN 4400
WARD SALESMAN 4750
JONES MANAGER 3025
MARTIN SALESMAN 4750
BLAKE MANAGER 3150
CLARK MANAGER 3550
SCOTT ANALYST 3000
KING PRESIDENT 1000
TURNER SALESMAN 4500
ADAMS CLERK 4900
JAMES CLERK 5050
FORD ANALYST 3000
MILLER CLERK 4700
14 rows selected

列出ename,job,sal,並用小寫字母來表示job,ename ：

SQL> select lower(ename),lower(job),sal from emp;

LOWER(ENAME) LOWER(JOB) SAL

smith clerk 800.00
allen salesman 1600.00
ward salesman 1250.00
jones manager 2975.00
martin salesman 1250.00
blake manager 2850.00
clark manager 2450.00
scott analyst 3000.00
king president 5000.00
turner salesman 1500.00
adams clerk 1100.00
james clerk 950.00
ford analyst 3000.00
miller clerk 1300.00
14 rows selected

別名的使用：在select語句的執行順序中，from子句最先被執行，然後就是where子句，最後才是select子句。當在from子句中指定表別名後，表的真實名稱將被替換。同時，其他的子句只能使用表別名來限定列。

SQL> select ename 名字，job 工作，sal from emp;

名字 工作 SAL

SMITH CLERK 800.00
ALLEN SALESMAN 1600.00
WARD SALESMAN 1250.00
JONES MANAGER 2975.00
MARTIN SALESMAN 1250.00
BLAKE MANAGER 2850.00
CLARK MANAGER 2450.00
SCOTT ANALYST 3000.00
KING PRESIDENT 5000.00
TURNER SALESMAN 1500.00
ADAMS CLERK 1100.00
JAMES CLERK 950.00
FORD ANALYST 3000.00
MILLER CLERK 1300.00
14 rows selected

SQL> select ename "名字"，job "工作"，sal from emp;(此處雙引號只能為英文雙引號，漢字雙引號報錯)

查詢的時候，比如job='clerk'這裡是英文單引號
關於別名，比如select ename "名字"，job "工作"，sal from emp； 這裡是英文雙引號;這裡也是英文的，中文無效

在使用列別名時，如果列別名區分大小寫，並且還包含一些特殊字元，那麼必須使用雙引號將列別名引起來。
名字 工作 SAL

SMITH CLERK 800.00
ALLEN SALESMAN 1600.00
WARD SALESMAN 1250.00
JONES MANAGER 2975.00
MARTIN SALESMAN 1250.00
BLAKE MANAGER 2850.00
CLARK MANAGER 2450.00
SCOTT ANALYST 3000.00
KING PRESIDENT 5000.00
TURNER SALESMAN 1500.00
ADAMS CLERK 1100.00
JAMES CLERK 950.00
FORD ANALYST 3000.00
MILLER CLERK 1300.00
14 rows selected

SQL> select ename “名字”，job “工作”，sal from emp;

select ename “名字”，job “工作”，sal from emp

ORA-00911: 無效字元
消除取值重複的行：

SQL> select deptno from emp;

DEPTNO

20
30
30
20
30
30
10
20
10
30
20
30
20
10

14 rows selected

SQL> select distinct deptno from emp;

DEPTNO

30
20
10

此處： select deptno from emp; 等同於 select all deptno from emp;預設沒有指定distinct關鍵詞，則預設值為all，即保留結果表中取值重複的行。
查詢滿足條件的元組： where 語句來實現！

查詢關於工資的比較:

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

ENAME JOB SAL

SMITH CLERK 800.00

SQL> select deptno,ename,job from emp where ename='KING';(oracle這裡區分大小寫)

DEPTNO ENAME JOB

10 KING       PRESIDENT

SQL> select ename,job,sal from emp where sal>=2000;

ENAME JOB SAL

JONES MANAGER 2975.00
BLAKE MANAGER 2850.00
CLARK MANAGER 2450.00
SCOTT ANALYST 3000.00
KING PRESIDENT 5000.00
FORD ANALYST 3000.00
6 rows selected

SQL> select ename,job,sal from emp where sal between 1000 and 2000;(此處not between...and...相同使用方法)

ENAME JOB SAL

ALLEN SALESMAN 1600.00
WARD SALESMAN 1250.00
MARTIN SALESMAN 1250.00
TURNER SALESMAN 1500.00
ADAMS CLERK 1100.00
MILLER CLERK 1300.00
6 rows selected
確定集合，謂詞In 可以用來查詢屬性值屬於指定集合的元組。（IN 的相對是 NOT IN）

SQL> select ename,job,sal from emp where deptno in ('10','20');

ENAME JOB SAL

SMITH CLERK 800.00
JONES MANAGER 2975.00
CLARK MANAGER 2450.00
SCOTT ANALYST 3000.00
KING PRESIDENT 5000.00
ADAMS CLERK 1100.00
FORD ANALYST 3000.00
MILLER CLERK 1300.00
8 rows selected

字元匹配：

SQL> select ename,job, sal from emp where empno like '7369';

ENAME JOB SAL

SMITH CLERK 800.00

SQL> select ename,job, sal from emp where empno='7369';

ENAME JOB SAL

SMITH CLERK 800.00

SQL> select ename,job, sal from emp where empno like '7%9';

ENAME JOB SAL

SMITH CLERK 800.00
ALLEN SALESMAN 1600.00
KING PRESIDENT 5000.00

SQL> select ename,job, sal from emp where empno like '7__9';

ENAME JOB SAL

SMITH CLERK 800.00
ALLEN SALESMAN 1600.00
KING PRESIDENT 5000.00

SQL> select ename,job, sal from emp where empno like '7_9_';

ENAME JOB SAL

ALLEN SALESMAN 1600.00
BLAKE MANAGER 2850.00
(一個漢字要佔兩個字元的位置，所以匹配漢字的時候要注意)
涉及空值的查詢：（此處is 不能用等號（=）代替）

SQL> select ename,job,sal from emp where mgr is null;

ENAME JOB SAL

KING PRESIDENT 5000.00

多重條件查詢： 邏輯運算子and 和or 可用來聯結多個查詢條件，and優先順序比or高，可以通過括號來改變優先順序。

SQL> select ename,job,sal from emp where deptno='20' and sal>2500;

ENAME JOB SAL

JONES MANAGER 2975.00
SCOTT ANALYST 3000.00
FORD ANALYST 3000.00

order by 子句：
使用者可以用order by 子句對查詢結果按照一個或多個屬性列的升序（asc）或降序（desc）排列，預設值為升序。

SQL> select empno,ename,job,sal from emp where deptno='20' order by sal,empno;

EMPNO ENAME JOB SAL

7369 SMITH CLERK 800.00
7876 ADAMS CLERK 1100.00
7566 JONES MANAGER 2975.00
7788 SCOTT ANALYST 3000.00
7902 FORD ANALYST 3000.00

聚集函式：
count([distinct|all]*) 統計元組個數
count([distinct|all]<列名>） 統計一列中值的個數
sum([distinct|all]<列名>) 計算一列值的總合（此列必須是數值型）
avg([distinct|all]<列名>) 計算一列值的平均值（此列必須是數值型）
max([distinct|all]<列名>) 求一列值中的最大值
min([distinct|all]<列名>) 求一列值中的最小值

SQL> select count(*) from emp;

COUNT(*)

    14

SQL> select count(sal) from emp;

COUNT(SAL)

    14

SQL> select avg(sal) from emp where deptno='20';

AVG(SAL)

  2175

SQL> select max(sal) from emp where deptno='20';

MAX(SAL)

  3000

SQL> select ename,job, min(sal) from emp where deptno='20';

select ename,job, min(sal) from emp where deptno='20'

ORA-00937: 不是單組分組函式

SQL> select min(sal) from emp where deptno='20';

MIN(SAL)

   800

SQL> select sum(sal) from emp ;

SUM(SAL)

 29025

Group by句子：

SQL> select avg(sal),max(sal),deptno from emp group by deptno;

AVG(SAL) MAX(SAL) DEPTNO

1566.66666 2850 30
2175 3000 20
2916.66666 5000 10

SQL> select avg(sal),min(sal),job,deptno from emp group by deptno,job;

AVG(SAL) MIN(SAL) JOB DEPTNO

   950        800 CLERK         20
  1400       1250 SALESMAN      30
  2975       2975 MANAGER       20
   950        950 CLERK         30
  5000       5000 PRESIDENT     10
  2850       2850 MANAGER       30
  1300       1300 CLERK         10
  2450       2450 MANAGER       10
  3000       3000 ANALYST       20

9 rows selected

having的用法。就是起到限制，group by 後面的限制。

SQL> select avg(sal),deptno from emp group by deptno having avg(sal)<2000;

AVG(SAL) DEPTNO

1566.66666 30

對資料分組的總結：
1 分組函式只能出現在選擇列表、having、order by子句中
2 如果在select 語句中同時包含有group by，having，order by 那麼他們的順序是group by，having，order by
3 在選擇列中如果有列、表示式、和分組函式，那麼這些列和表示式必須有一個出現在group by 子句中，否則就會出錯
如select deptno，avg(sal)，max（sal） from emp group by deptno having avg(sal)<2000；這裡deptno就一定要出現在group by 中。

在使用group by 子句時，必須滿足下面的條件：
在select 子句的後面只可以有兩類表示式：統計函式和進行分組的列名。
在select子句中的列名必須是進行分組的列，除此之外新增其他的列名都是錯誤的，但是，group by子句後面的列名可以不出現在select子句中。
如果使用了where子句，那麼所有參加分組計算的資料必須首先滿足where子句指定的條件。

下面是衣蛾錯誤的查詢，猶豫在select子句後面出現了salary列，而該列並沒有出現在group by 子句中，所以該語句是一個錯誤的查詢。
select job_id,salary,avg(salary),sumsalary),max(salary),count(*) from employees group by job_id;
這裡是錯誤的！！！！

4566103-0b1c37ded1419cd3.jpeg
857e9db0gd932b3281f93&690.jpeg

如果處理null值
select sal13+nvl(comm,0)13 "年工資"，ename,comm from emp;

4566103-d2777ad69d707982.jpeg
857e9db0gd933621a03b5&690.jpeg
4566103-bad8fd25b24b996d.jpeg
857e9db0gd933486c8d33&690.jpeg
4566103-716b9d02e15c3e04.jpeg
857e9db0gd9335e4db2f4&690.jpeg
4566103-f3b5881598a853df.jpeg
857e9db0gd9335a5ec910&690.jpeg
4566103-c061924f01a89cb7.jpeg
857e9db0gd9332a02ed75&690.jpeg
4566103-b66e7b949af92841.jpeg
857e9db0gd984dcef45fe&690.jpeg
4566103-a282eec9ab5f403f.jpeg
857e9db0gd984c3b95479&690.jpeg
4566103-8e0481e61eba26fe.jpeg
857e9db0gd932f561058e&690.jpeg
4566103-b92388aa5f9b787b.jpeg
857e9db0gd932dcca035b&690.jpeg
4566103-8c29b708bb60dc87.jpeg
857e9db0gd932c7c454ee&690.jpeg
4566103-9307e9ef40c3dae5.jpeg
857e9db0gd932b3281f93&690.jpeg
4566103-b0467e8d91b05580.jpeg
857e9db0g7c26e250fcbe&690.jpeg
4566103-d867145b76468421.jpeg
857e9db0g7c26e2c88cee&690.jpeg

1：子查詢
子查詢的型別：基本有兩種：單行子查詢和多行子查詢
單行子查詢
多行子查詢
另外，子查詢還有3種子型別，這3種子型別返回一行或者多行的查詢結果。
多列子查詢
關聯子查詢
巢狀子查詢

單行子查詢：
select start_date,end_date,job_id from job_history where employee_id=(select employee_id from employees where first_name='jennifer' and last_name='whalen');
多列子查詢是指返回多列資料的子查詢語句。當多列子查詢返回單行資料時，在where自居中可以使用單行比交付：單色如果需要返回多行資料時，那麼where自居中必須使用多行比較符（in、any、all）
1：使用In操作符實現制定匹配查詢
當在多行子查詢中使用In操作符時，會處理匹配與子查詢中任一個值的行。
以下程式碼查詢Jobs表中最高工資分別為10000、20000、30000的職位資訊。
select job_id,max_salary from jobs where max_salary in(10000，20000，30000)；
2：使用any操作符實現任意匹配查詢
any操作符必須與單行操作符結合使用，並且返回行只需匹配與子查詢的任一個結果即可。
首先查詢jobs表中最高工資小於10000的工作職位，然後在外部查詢中查詢最低工資大於這些工資的任意一個工作職位資訊。
select job_id,min-salary,max_salary from jobs where min_salary>any(select max_salary from jobs where max_salary<10000);
3:使用all操作符實現全部匹配查詢
首先查詢jobs表中最高工資小於10000的工作職位，然後在外部查詢中查詢最低工資大於這些工資的任意一個工作職位資訊。
select job_id,min_salary,max_salary from jobs where min_salary>all(select max_salary from jobs where max_salary<10000);

在update語句中使用子查詢
update jobs set min_salary=min_salary+100 where min_salary>all(select max_salary from jobs where max_salary <10000);
在delete 語句中使用子查詢
delete from job_history where employee _id=(select employee_id from employees where first_name='jennifer' and last_name='whalen');

相關文章