select查询之一：普通查询
查询是数据库工作者最常用的一种操作。
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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31392094/viewspace-2125906/ ，如需转载，请注明出处，否则将追究法律责任。
