查詢是資料庫工作者最常用的一種操作。
Select語句的總結構：

Select [欄位] [限制條件]

from [資料表] /子查詢[巢狀查詢]

where [篩選條件] /子查詢[巢狀查詢]

group by[分組欄位]

Having [輔助篩選條件] /子查詢[巢狀查詢]

order by[排序欄位];
上面說到的子查詢或者巢狀查詢，可以安放在對應的字句位置裡面，起到補充查詢功能作用。

1、全欄位查詢：

SQL> select * from students; ------全欄位查詢時候，可以用星號※代替，也可以把全部欄位列出來

STUDENT_ID NAME DOB SEX

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

45211 Mar 21-JUN-02 man

45212 Maro 21-MAR-02 man

45221 July 29-JUN-02 woman

45222 Marry 12-JUN-03 woman

45223 Mare 21-JUN-02 woman

45214 Jone 10-JUL-02 man

45215 Kaha 31-AUG-04 man

45216 Kaka

8 rows selected.

2、部分欄位查詢：

SQL> select empno,ename,job

2 from emp

3 where job = 'CLERK';

EMPNO ENAME JOB

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

7369 SMITH CLERK

7876 ADAMS CLERK

7900 JAMES CLERK

7934 MILLER CLERK
3、查出學生表中不是男生的所有資訊： not in

SQL> select * from students

2 where sex not in 'man';

STUDENT_ID NAME DOB SEX

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

45221 July 29-JUN-02 woman

45222 Marry 12-JUN-03 woman

45223 Mare 21-JUN-02 woman

4、檢視工資在2600,8000和9000員工的資訊：in

SQL> select employee_id,last_name,salary,hire_date

2 from employees

3 where salary in (2600,8000,9000);

EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE

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

198 OConnell 2600 21-JUN-07

199 Grant 2600 13-JAN-08

103 Hunold 9000 03-JAN-06

109 Faviet 9000 16-AUG-02

118 Himuro 2600 15-NOV-06

120 Weiss 8000 18-JUL-04

143 Matos 2600 15-MAR-06

152 Hall 9000 20-AUG-05

153 Olsen 8000 30-MAR-06

158 McEwen 9000 01-AUG-04

159 Smith 8000 10-MAR-05

11 rows selected.

5、檢視名字以大寫D開頭的員工資訊：like '%'

SQL> select employee_id,last_name,salary

2 from employees

3 where last_name like 'D%';

EMPLOYEE_ID LAST_NAME SALARY

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

142 Davies 3100

102 De Haan 17000

186 Dellinger 3400

189 Dilly 3600

160 Doran 7500

SQL> select employee_id,last_name,salary

2 from employees

3 where last_name like 'D_l%';

6、檢視名字以大寫D並且第三字母為l員工的資訊：like '_%'

EMPLOYEE_ID LAST_NAME SALARY

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

186 Dellinger 3400

189 Dilly 3600

7、檢視名字以D開頭並且工資大於7000員工的資訊：And

SQL> select employee_id,last_name,salary

2 from employees

3 where last_name like 'D%'

4 and salary >7000;

EMPLOYEE_ID LAST_NAME SALARY

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

102 De Haan 17000

160 Doran 7500

8、檢視名字以L開頭或者工資大於10000員工的資訊：Or

SQL> select employee_id,last_name,salary

2 from employees

3 where last_name like 'L%'

4 or salary >10000;

EMPLOYEE_ID LAST_NAME SALARY

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

201 Hartstein 13000

205 Higgins 12008

100 King 24000

101 Kochhar 17000

102 De Haan 17000

107 Lorentz 4200

108 Greenberg 12008

114 Raphaely 11000

127 Landry 2400

137 Ladwig 3600

145 Russell 14000

... ...

20 rows selected.

9、檢視部門領導為空的員工資訊：Null

SQL> select last_name,department_id,manager_id

2 from employees

3 where manager_id is null;

LAST_NAME DEPARTMENT_ID MANAGER_ID

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

King 90

10、檢視學生表中名字不帶Mar學生的資訊：Not like

SQL> select * from students

2 where name not like 'Mar%';

STUDENT_ID NAME DOB SEX

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

45221 July 29-JUN-02 woman

45214 Jone 10-JUL-02 man

45215 Kaha 31-AUG-04 man

45216 Kaka

11、檢視工資在12000跟14000之間的員工資訊：Between and

SQL> select employee_id,last_name,salary

2 from employees

3 where salary between 12000 and 14000;

EMPLOYEE_ID LAST_NAME SALARY

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

201 Hartstein 13000

205 Higgins 12008

108 Greenberg 12008

145 Russell 14000

146 Partners 13500

147 Errazuriz 12000

6 rows selected.

select查詢之一：普通查詢

相關文章