三、基本查询语句
1. 数据查询语句的基础语法
1.1 基础语法
在MySQL中 select 表示查询，select可以单独成句，是MySQL的方言
比如查询系统时间： select now();
now() 表示当前系统时间 MySQL特有的一个方法
在Oracle中系统时间： select sysdate from dual; 是Oracle的方言
dual表时Oracle特有的一张表，主要目的就是为了补充SQL语句中的from**
在Oracle的语法中，select之后必须要有from
MySQL中有DUAL关键字，所以SELECT NOW()也可以写作SELECT NOW() FROM DUAL;
但MySQL中的DUAL不是表 执行SELECT * FROM DUAL;会报错，而Oracle中DUAL是一张实际存在的表，执行SELECT * FROM DUAL;会查询一个叫做DUMMY的结果
标准语法：select...from...
语法格式：
- 从某张表中查询特定列的数据
select 列1,列2,....列n from 表
- 从某张表中查询其全部列的数据
select * from 表
开发中建议以查询特定列的方式进行查询，前者的执行效率更快
1.2 表达式和别名
image
示例1：查询员工编号，员工姓名，年薪 （sal表示的是月薪）
select empno, ename, sal*12
from emp;
image
注意年薪的表头显示的是“sal*12”
可以使用as或者空格，为查询结果的列起别名
select empno, ename, sal*12 income
from emp;
或
select empno, ename, sal*12 as income
from emp;
image
1.3 数值运算中的null问题
当数据运算中出现null值时，结果一定是null
MySQL解决问题是用ifnull()函数
Oracle解决问题是用nvl()函数
ifnull(expr1,expr2)
nvl(expr1,expr2)
当expr1的结果是null时，使用expr2的结果
当expr1的结果不是null时，使用expr1的结果
示例2： 查询员工姓名和员工的月收入（月薪+奖金）
select ename, sal+comm
from emp;
此时原始数据中如果comm中有null值，查询结果不正确
image
select ename, sal, comm, sal+ifnull(comm,0)
from emp;
image
1.4 重复数据的处理
使用distinct去掉查询结果中的重复记录
基础语法
select distinct 列1, 列2, ... 列n from 表;
查询列1，列2直至列n的数据，并去掉其中重复的组合
2. 数据的条件筛选
2.1 语法格式：
select ... from...where 查询条件
示例3：查询所有在部门编号是1工作的员工记录
SELECT * FROM emp WHERE deptno = 1;
2.2 运算符
-
数值比较运算：
deptno = 1 ( 在1号部门)
deptno <> 1 或 deptno != 1 (不在1号部门)
sal > 2000 (月薪高于2000)
sal >= 2000 (月薪大于等于2000)
sal < 2000 (月薪小于2000)
sal <= 2000 (月薪不高于2000 月薪小于等于2000) -
文本比较运算
ename = '赵四' (名字是赵四)
ename <> '赵四' 或 ename != '赵四' (名字不是赵四)在windows中，比较英文文本时，MySQL会忽略大小写
select * from emp where ename = 'tom'
select * from emp where ename = 'TOM'
的查询结果是一样的，数据中Tom，tom，TOM，tOm...等都会被查询出
如果需要严格控制大小写字母敏感，查询语句需要在列前加binary
select * from emp where binary ename = 'TOM'
这样只能查询出TOM
- 时间比较运算
hiredate = now() (入职日期是今天)
hiredate <> now() (入职日期不是今天)
hiredate < now() (入职日期在今天之前)
hiredate >= now() (入职日期在今天之后,含今天)
2.3 逻辑关系
2.3.1 AND 逻辑与
X AND Y 必须同时满足条件X和条件Y
示例4： 查询在1号部门工作，且月薪不低于2500的员工信息
SELECT * FROM emp WHERE deptno = 1 AND sal >= 2500;
示例5： 查询在1号部门工作，且月薪不低于2500的保洁信息
SELECT * FROM emp WHERE deptno = 1 AND sal >= 2500 AND job = '保洁';
2.3.2 OR 逻辑或
X OR Y 满足条件X或条件Y其一即可
示例6：查询在1号部门或2号部门工作的员工信息
SELECT * FROM emp WHERE deptno = 1 OR deptno = 2
2.3.3 NOT 逻辑非
NOT(X) 不是条件X
主要使用在特殊的比较运算符中
2.3.4 AND和OR的优先级比较
AND语句的优先级高于OR
SELECT * FROM emp
WHERE deptno = 3 OR sal < 2500 AND deptno = 1
- 先执行sal < 2500 AND deptno = 1 ---- X
- 再执行deptno = 3 OR X
如果使用括号控制优先级
SELECT * FROM emp
WHERE (deptno = 3 OR sal < 2500) AND deptno = 1
- 先执行deptno = 3 OR sal < 2500 ---- X
- 再执行 X AND deptno = 1
SQL注入问题：利用了AND和OR优先级破坏系统中的SQL语句逻辑
image
正常访问逻辑，用户输入admin和123 正确账号和密码可以登录
SELECT * FROM userinfo WHERE lname = 'admin' AND lpass = '123';
SQL注入通过输入正确的账号，和一个带有or逻辑的SQL片段将原来逻辑破坏掉，进行非法登录
输入的是 账号：admin 密码：' OR 1=1 AND lname='admin
这个SQL语句就变成了
SELECT * FROM userinfo WHERE lname = 'admin' AND lpass = '' OR 1=1 AND lname='admin';
lname = 'admin' AND lpass = '' -- X 假 查不出结果
1=1 AND lname='admin' -- Y 真 查出admin的数据
X OR Y -- 真 验证通过 访问了admin的账号
3. 特殊的比较运算符
3.1 between...and...
X between A and B 表示X在[A,B]之间
相当于 X >= A and X <= B
一般情况下： B应该大于等于A
如果B小于A语法也没错误，逻辑上冲突，不会由查询结果
示例1：查询月薪在[2000,2500]区间的员工信息
select *
from emp
where sal between 2000 and 2500;
示例2：查询在20号部门入职日期在1994年的员工信息
select *
from emp
where hiredate between '1994-01-01' and '1994-12-31' and deptno = 20;
3.2 like (非常重要)
用于查询数据时进行模糊匹配(模糊查询)，只适用于文本查询
语法规则
- 相当于ename = 'A'
ename like 'A'
- ename中含有A字母。 %表示此处可能有[0,n]个字符
ename like '%A%'
- ename中倒数第二个字符是A字母。 _表示此处有1个字符
ename like '%A_'
- ename中含有两个O字母
ename like '%O%O%'
-ename中含有字符% 使用\做为转义字符
将'%'看做一个%字符
MySQL中
select *
from emp
where ename like '%\%%';
Oracle中
select *
from emp
where ename like '%\%%' escape '\';
3.3 in
相当于or
语法规则
- deptno = 10
deptno in (10)
- deptno是10或者20
deptno in (10,20)
相当于
(deptno = 10 or deptno = 20)
示例3：查询在10号部门或20部门工作，且职位是CLERK的员工信息
select *
from emp
where deptno in(10,20) and job = 'CLERK';
示例4：查询员工SMITH和AMY的员工信息
select *
from emp
where ename in('SMITH','AMY');
示例5：查询在'1992-01-15'或'1994-12-22'或'1994-07-05'入职的员工信息
select *
from emp
where hiredate in('1992-01-15','1994-12-22','1994-07-05');
如果in中的数据有null值，null值不会被查询出
3.4 is null
专门针对null值进行筛选
示例6： 查询所有奖金是null的员工信息
select *
from emp
where comm is null
不能使用comm = null查询，这样查询没有结果
3.5 not
not表示特殊比较运算符逻辑取反
示例7：查询月薪不在[2000,2500]区间的员工信息
select *
from emp
where sal not between 2000 and 2500;
示例8：查询员工名字中不包含字母A的员工信息
select *
from emp
where ename not like '%A%';
示例9：查询不在10号或20号部门工作的，职位是CLERK的员工信息
select *
from emp
where deptno not in(10,20) and job = 'CLERK';
示例10：查询奖金不是null的员工信息
select *
from emp
where comm is not null;
4. 排序
排序是对查询结果进行排序显示
4.1 排序规则
排序规则
- 升序（由小到大）
数值 0，1，2，3....
文本 A, B, C .... 汉字遵循字典顺序
时间 1972-01-01 ... 1978-05-22 - 降序（由大到小）
数值 3，2，1，0 ...
文本 Z，Y，X .... 汉字遵循字典顺序的反向
时间 1978-05-22 ... 1972-01-01
4.2 语法规则
select...from...where...order by...
示例11：查询所有的员工信息，按照月薪升序排序。
asc表示升序，排序默认就是升序，asc可以省略。
select * from emp order by sal;
select * from emp order by sal asc;
示例12：查询所有的员工信息，按照月薪降序排序。
desc表示降序
select * from emp order by sal desc;
4.3 多个列进行排序
order by 列1 排序方式, 列2 排序方式,.... 列n 排序方式;
在列1的排序基础上，进行列2排序，.... 在之前所有排序基础上进行列n的排序
示例13 ： 查询所有员工信息，按照部门编号升序排序，按照月薪降序排序
先按照deptno进行升序排序，在deptno排序的基础上，再进行sal的降序排序
select *
from emp
order by deptno asc, sal desc;
4.4 表达式或列别名
示例14：查询20号部门员工姓名，月薪，奖金，月收入，按照月收入的降序排序
select ename, sal, comm, sal+IFNULL(comm,0)
from emp
where deptno = 20
order by sal+IFNULL(comm,0) desc;
可以使用列别名
select ename, sal, comm, sal+IFNULL(comm,0) as income
from emp
where deptno = 20
order by income desc;
4.5 列的序号
按照查询结果的第3列进行升序排序，在排序基础上以第2列进行降序排序
实际：deptno升序后，sal降序
select ename,sal,deptno
from emp
order by 3 asc, 2 desc;
5. 查询语句的编写顺序和执行顺序
编写顺序： select...from...where...order by...
执行顺序： from...where...select...order by...
