3 基础select篇
3.1 基本SELECT语句
3.1.1 基本查询
- SELECT 字段名,列名 FROM 数据库表
- 查询指定表中指定列的数据
- 当省略数据库表时，默认为伪表（DAUL）
- 例子
- 伪表查询
- 字段查询
SELECT 1 + 3, 2+4;
SELECT employee_id, first_name FROM employees;
/* 结果如下 */
3.1.2 列的别名
- SELECT 列名 as “别名” FROM tables
- 别名紧跟在列名后面
- as 可以省略
- 别名需要用双引号，也可以省略
- 例子：
# 别名
SELECT employee_id AS "ID", first_name "名字", last_name 姓名 FROM employees;
3.1.3 去重(distinct)
- SELECT DISTINCT 列名，字段 FROM tables
- 相同值的行被清除
- 查询输入多个列时，各个列名同时相同时，会被清除
- 例子：
- 查询部门
# 去重前
SELECT department_id
FROM employees;
# 去重后
SELECT DISTINCT department_id
FROM employees;
3.1.4 空值参与运算
-
空值是指 null 它不等同于 0，‘null’, ‘’
空值不代表空字符串，空字符串的长度为0，而一个空值的长度为空，且空值是占空间的。
-
默认情况下，空值会参与四则运算，而且结果也为null
-
例子
- 查询员工每年的工资，（工资 = (月工资 + 月奖金) * 12)
SELECT employee_id AS "ID",
salary "月工资",
salary * (1 + commission_pct) * 12 "年工资",
commission_pct "绩效"
FROM employees;
# 如图由于绩效为空，因此得到的年工资也为空
# 在当前情况下，由于可以把空绩效等于0，因此可以用 ifnull 来解决
SELECT employee_id AS "ID",
salary "月工资",
salary * (1 + IFNULL(commission_pct) * 12 "年工资",
commission_pct "绩效"
FROM employees;
3.1.5 着重号
- ``
- 当表名，列名，字段名等与关键字重复了，用注重号进行区分
- 例子
# 在 from表中，查找select z
SELECT `select` from `from`
3.1.6 筛选 (where)
- SELECT 列名，字段 FROM tables where 逻辑
- 当逻辑判断为1时，返回该列，反之不返回
3.2 运算符
3.2.1 算术运算符
加减乘除和取余%
-
数值类类型进行运算时
- 结果为精度较高的一位，即int和float进行运算，结果位float
-
当有字符串类型进行运算时
- 对于纯数字的字符串则直接转为数值类型
- 对于数字开头的字符串则截取开头的数字部分转为数值类型
- 对于截取不到开头为数字的字符串则转换为数值0
- 在SQL两个字符串相加不是合并的意思，若想合并需用CONCAT()
-
当有null时
- 与null类型进行运算，结果为null
-
0作为除数时
- 结果为null
select 1 +0.1, 1 + '0.1', 1+ 'G', 2/ 3, 4/4, 4 * 'g', 4 / 'g', 'g' +'g', 3 + null;
# 结果
+--------+-----------+--------+--------+--------+---------+---------+----------+----------+
| 1 +0.1 | 1 + '0.1' | 1+ 'G' | 2/ 3 | 4/4 | 4 * 'g' | 4 / 'g' | 'g' +'g' | 3 + null |
+--------+-----------+--------+--------+--------+---------+---------+----------+----------+
| 1.1 | 1.1 | 1 | 0.6667 | 1.0000 | 0 | NULL | 0 | NULL |
+--------+-----------+--------+--------+--------+---------+---------+----------+----------+
1 row in set, 6 warnings (0.00 sec)
3.2.2 比较运算符
01) 符号
- =：等于号，用于比较，在SQL中，赋值符号为 “:=”
- <=>：安全等于号，与=相似，主要用于比较null
- <>(!=)：不等于号，两种方式
- <、>、>=、<=：其他比较符号
02) 规则
- 比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，为假则返回0，其他情况则返回NULL。
- 当有字符串类型时：
- 若两边都为字符串类型，会先将字符串转化为字节码再进行比较
- 当有一方为字符串类型，一方为数值类型时，会尝试将字符串类型转化为数值类型，若无法转化，则默认为0.
- 当有null进行比较时
- 除安全等于号外(<=>)，其他符号不能判断null，因此当有null时，结果为null。
- <=>：等两边都为null时，返回1，当存在有一方为null时，返回为0
- 例子
# 存在字符串
mysql> select '1' > 'k', 1 >'k';
+-----------+--------+
| '1' > 'k' | 1 >'k' |
+-----------+--------+
| 0 | 1 |
+-----------+--------+
# 存在null
mysql> select 1=null, 1 = 'null', 'null'='null', null=null;
+--------+------------+---------------+-----------+
| 1=null | 1 = 'null' | 'null'='null' | null=null |
+--------+------------+---------------+-----------+
| NULL | 0 | 1 | NULL |
+--------+------------+---------------+-----------+
# 安全等于号
mysql> select 1<=>null, 1 <=> 'null', 'null'<=>'null', null<=>null;
+----------+--------------+-----------------+-------------+
| 1<=>null | 1 <=> 'null' | 'null'<=>'null' | null<=>null |
+----------+--------------+-----------------+-------------+
| 0 | 0 | 1 | 1 |
+----------+--------------+-----------------+-------------+
3.2.3 逻辑运算符
- 用于判断左右表达式的真假，返回结果有：0（错误）1（正确）null（无法判断）
01) 符号
- NOT 或 ！：逻辑非
- AND 或 &&：逻辑与
- OR 或 ||：逻辑或
- XOR：逻辑异或，当两边表达式结果不相同时，返回1，即结果一个为0，一个为1时返回1，反之返回0
02) 规则
- 空的字符串表示为0，'0’也表示0，其他非空字符串为1
- null表示无法判断，不为0也不为1，看下面例子
- 例子
# 空的字符串表示为0，'0'也表示0，其他非空字符串为1
mysql> select !'', !'0', !'1';
+-----+------+------+
| !'' | !'0' | !'1' |
+-----+------+------+
| 1 | 1 | 0 |
+-----+------+------+
# || 是逻辑或，
# 当俩个同时为0是，结果为0； 0 || null 中，null非0非1，所有结果为null
# 当存在有一个为1是，结果为1’ 1 || null，有一个为1，结果为1
mysql> select 0 || null, 1 || null;
+-----------+-----------+
| 0 || null | 1 || null |
+-----------+-----------+
| NULL | 1 |
+-----------+-----------+
mysql> select 0 && null, null && 1; # 同理
+-----------+-----------+
| 0 && null | null && 1 |
+-----------+-----------+
| 0 | NULL |
+-----------+-----------+
# XOR 为判断两边不同，但是null非0非1，所有无法判断
mysql> select null XOR null；
+---------------+
| null XOR null |
+---------------+
| NULL |
+---------------+
3.2.4 非符号运算（函数）
01) null相关
- 值 IS NULL 或 ISNULL(值)：判断一个值是否为null，1（是）0（非）
- 值 IS NOT NULL：判断一个值是否不为null。注意：空字符串没空，‘’ is not null.
- IFBULL(值， v)：判断一个值是否为null，若为null则返回v，反之返回改值
mysql> select '' is null, '' is not null, ifnull('', 0), ifnull(null, 1);
+------------+----------------+---------------+-----------------+
| '' is null | '' is not null | ifnull('', 0) | ifnull(null, 1) |
+------------+----------------+---------------+-----------------+
| 0 | 1 | | 1 |
+------------+----------------+---------------+-----------------+
02) 比较运算符
- LEAST(v1, v2, …, vn)：从值v1~n从选出最小的值
- GREATEST(v1, …, vn)：选出最大的值.
- v1 BETWEEN v2 AND v3：判断v1是否在v2v3之间
- 注意
- 不要产生字符串类型与数值类型之间的比较
- 有null是结果为null
mysql> select least(1, 2, 3), greatest(1,2,3), 2 between 1 and 3, 2 between null and 3;
+----------------+-----------------+-------------------+----------------------+
| least(1, 2, 3) | greatest(1,2,3) | 2 between 1 and 3 | 2 between null and 3 |
+----------------+-----------------+-------------------+----------------------+
| 1 | 3 | 1 | NULL |
+----------------+-----------------+-------------------+----------------------+
03) in 与 not in
- v1 [NOT] IN (v2, v3, …, vn)：判断v1是否属于v2~n
- null表示无法判断，当结果无法判断时，返回null。
- 当null为v1时，结果必为null
# 1 肯定在 (0, 1, null)中，所以结果1，
# 1 不一定在（0， null)中所有结果为null，
# 1 一定不再 (0)，所以结果为0
mysql> select 1 in (0, 1, null), 1 in (0, null), 1 in (0);
+-------------------+----------------+----------+
| 1 in (0, 1, null) | 1 in (0, null) | 1 in (0) |
+-------------------+----------------+----------+
| 1 | NULL | 0 |
+-------------------+----------------+----------+
04) like、\ 与 escape
- like用于匹配字符串，通常时模糊匹配，满足条件返回1，反之返回0，若出现null，则结果为null
- like只有两个匹配符号，要使用正则匹配需利用regexp
- “%”：匹配0个或多个字符。
“_”：只能匹配一个字符。
- \：转化字符，指定后续字符为普通字符
- escape ‘字符’：和转义字符一样，指定后续字符为普通字符
- 例子：
- 查找以IT开头的，其中 %为正则字符，匹配一个或多个，转化为普通字符时，就无记录，
# 查找以IT开头的
mysql> select job_id from jobs where job_id like 'IT%';
+---------+
| job_id |
+---------+
| IT_PROG |
+---------+
1 row in set (0.00 sec)
mysql> select job_id from jobs where job_id like 'IT\%'; # 查找以IT开头的
Empty set (0.00 sec)
mysql> select job_id from jobs where job_id like 'IT%' escape '%'; # 查找以IT开头的
Empty set (0.00 sec)
05) regexp
- v1 regexp format：用于匹配字符串，通常也是正则匹配，若符合条件返回1
mysql> select 'abc' regexp '^a', 'abc' regexp 'c$';
+-------------------+-------------------+
| 'abc' regexp '^a' | 'abc' regexp 'c$' |
+-------------------+-------------------+
| 1 | 1 |
+-------------------+-------------------+
1 row in set (0.00 sec)
06) 正则符号
“%”：匹配0个或多个字符。
“_”：只能匹配一个字符。
3.3 排序与分页
3.3.1 排序
- order by 列名[ASC, DESC], 列名[ASC, DESC]:
- 对查询的语句根据列名进行排序，ASC(ascend升序)，DESC(descend降序)。
- 可同时根据多个列名进行排序，当第一个列出现相同值后，对第二列进行排序。且每一个列可指定升序还是降序
- order by 需放在select语句结尾，因为是得先查询到，才能进行排序。
# 查询各个部门的工资，部门id升序，工资降序
select department_id, salary
from employees
order by department_id ASc, salary Desc;
+---------------+----------+
| department_id | salary |
+---------------+----------+
| NULL | 7000.00 |
| 10 | 4400.00 |
| 20 | 13000.00 |
| 20 | 6000.00 |
| 30 | 11000.00 |
| 30 | 3100.00 |
| 30 | 2900.00 |
| 30 | 2800.00 |
| 30 | 2600.00 |
| 30 | 2500.00 |
3.3.2 分页
- limit index, n: 所谓分页，就是在查询结果中查看指定的行数，而不再是全部查看
- index, n 表示从第index开始，联系的n行
- 第一行的index = 0
- limit语言需放在最后面
- 分页查询的方式不再查询整张表，一旦指定了查找记录的位置，找到该位置后就不再进行查找，可以提高查询的效率。
- 注意：在不同的软件中，分页查询的方式不同，如在SQL Server中就是用"Top n"来表示
- 例子：
# 查询第3到5行
mysql> select last_name from employees limit 2, 3;
+-----------+
| last_name |
+-----------+
| De Haan |
| Hunold |
| Ernst |
+-----------+
3 rows in set (0.00 sec)
3.4 多表查询
- 当我们需要的数据分布在多个表中时，就需要多表查询
- select查询只能在一个表中进行查询，而因为数据在不同表中，所以需要将相关表进行连接产生临时表，然后再进行查询
- 多表连接将产生临时表储存在内存中，查询完毕后将释放。
3.4.1 连接方式
- 交叉连接（笛卡尔积）： join, 或省略
- 笛卡尔积：返回两个集合中的元素两两组合的所有可能组合，因此若 len(A)=a,len(B)=b，则结果返回个数为 a*b。
- 交叉连接：交叉连接不考虑两个表之家的关系，信息及其冗杂。
- 产生方式：不指定连接条件，则必为交叉连接
- 自连接：
- 自己与自己连接，即将自己虚拟成两个不同的表进行连接
- 实现方式：给予同一个表不同的别名
- 内连接： inner join
- 返回满足条件的行，不满足条件的行不会出现结果集中
- 外连接：
- 内连接中，不满足条件的行不会出现在结果集中，但是有时候，我们需要知道不满足条件的行，因此就需要外连接
- 左(右)外连接：在左外连接中，左表为主表，右表为副表，结果集中主表的记录将会显示出来，不满足条件列将会用null代替。
- 外连接：在外连接中，结果集中包含不满足条件的行，无论是左表还是右表，相应的，不满足条件的列将会用null代替。
3.4.2 语法
-
基本语法：
select 列名，列名 from 表1 [连接方式] 表2 on 筛选条件 where 其他筛选条件 # 交叉连接 from t1 join t2 from t1 cross join t2 from t1, t2 # 内连接 from t1 inner join t2 # 外连接 from t1 full\ left\ right ojoin t2
注意事项：
-
通过给予表别名的方式提高查询效率
select * from 表1 as t1 join 表2 as t2 -
当相连接的表中存在相同列名时，需在列名前添加所属表
# 以表employees的自连接为例 select e1.employee_id, e2.manager_id from employees as e1 join employees as e2 -
可进行多重连接
select * from t1 left join t2 on ... right join t3 on ... full join t4 on .. where ...
3.4.3 where 与 on
功能：
-
on 是创建临时表的筛选条件
-
where 是select的筛选条件，也就是对临时表进行筛选
从功能上可以看出，where 需在on后面，因为得先有表才能进行筛选
都是筛选，在表的连接中有什么区别呢
- 在内连接中，on和where没有区别，因为不满足条件的结果都会舍弃
- 在外连接中，
不满足条件的行也会被留下来，也就是说on的筛选程度不够强，因此需要有where进行补充。
注意
- 若需要用到连接，需考虑on的筛选程度，并决定是否应用where
- 外连接中，即使
不满足条件的列也会被保存！！
# 需求为：保留行value=100的记录
select * from A
left join B
on A.value = "100" # 错误，因为value!=100的行也会被保留
select * from A
left join B
where A.value="100” # z
3.4.4 union 连接
- 作用：将多个两个select语句的查询结果合并到一起，合并时，两个表对应的列数和数据类型必须相同，并且相互对应。
- 关键字：
- union：对合并的结果去重
- union all：合并的结果不去重
- 语法：
select ...
union
select ...
union all
select ...
下篇传送门
http://t.csdn.cn/3JzGF
75
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
