数据分析需要用SQL(Structured Query Language)在数据库中查询所需的数据。以下是经常用到的SQL查询语句:
SELECT
选择需要查询的内容
SELECT column_name(s)
FROM table_name
WHERE
给查询结果加上过滤条件
SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY
对查询结果进行排序(ASC:顺序;DESC:倒序)
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
GROUP BY
对查询结果进行分组,通常和合计函数结合使用
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING
对分组结果进行过滤
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
联结查询
将多个表通过主键联结起来,在此基础上进行查询
- INNER JOIN(JOIN):如果表中有至少一个匹配,则返回行
- LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
- RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName
子查询(Subquery)
也可以称作嵌套查询(Nested Query),是一种嵌套在其他 SQL 查询的 WHERE 子句中的查询
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
组合查询(UNION)
执行多个查询,将结果作为单个结果集返回(自动去除重复的行)
SELECT column_name(s) FROM table1 [WHERE condition] UNION SELECT column_name(s) FROM table2 [WHERE condition];
运算符:
运算符 | 描述 |
---|---|
ALL | ALL运算符用于将值与另一个值集中的所有值进行比较。 |
AND | AND运算符允许在SQL语句的WHERE子句中指定多个条件。 |
ANY | ANY运算符用于根据条件将值与列表中的任何适用值进行比较。 |
BETWEEN | BETWEEN运算符用于搜索在给定最小值和最大值内的值。 |
EXISTS | EXISTS运算符用于搜索指定表中是否存在满足特定条件的行。 |
IN | IN运算符用于将值与已指定的文字值列表进行比较。 |
LIKE | LIKE运算符用于使用通配符运算符将值与类似值进行比较。 |
NOT | NOT运算符反转使用它的逻辑运算符的含义。 例如:NOT EXISTS, NOT BETWEEN, NOT IN等等,这是一个否定运算符。 |
OR | OR运算符用于组合SQL语句的WHERE子句中的多个条件。 |
IS NULL | IS NULL运算符用于将值与NULL值进行比较。 |
UNIQUE | UNIQUE运算符搜索指定表的每一行的唯一性(无重复项)。 |
通配符:
通配符 | 描述 |
---|---|
% | 替代一个或多个字符 |
_ | 仅替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist] 或者 [!charlist] | 不在字符列中的任何单一字符 |
NOT NULL
约束强制列不接受 NULL 值
AVG(xx)
返回数值列的平均值。NULL 值不包括在计算中
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SUM(xx)
返回数值列的总和
SELECT SUM(column_name)
FROM table_name
WHERE condition;
COUNT(xx)
返回表中的记录条数
SELECT COUNT(*) FROM table_name;
DISTINCT
返回不重复的记录
SELECT DISTINCT column_name(s)
FROM table_name
LIMIT
限制返回的条数
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
OFFSET
跳过指定的返回条数
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number OFFSET number;
SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT a. select_expr -- 可以用 * 表示所有字段。 select * from tb; -- 可以使用表达式(计算公式、函数调用、字段也是个表达式) select stu, 29+25, now() from tb; -- 可以为每个列使用别名。适用于简化列标识,避免多个列标识符重复。 - 使用 as 关键字,也可省略 as. select stu+10 as add10 from tb; b. FROM 子句 用于标识查询来源。 -- 可以为表起别名。使用as关键字。 SELECT * FROM tb1 AS tt, tb2 AS bb; -- from子句后,可以同时出现多个表。 -- 多个表会横向叠加到一起,而数据会形成一个笛卡尔积。 SELECT * FROM tb1, tb2; -- 向优化符提示如何选择索引 USE INDEX、IGNORE INDEX、FORCE INDEX SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3; c. WHERE 子句 -- 从from获得的数据源中进行筛选。 -- 整型1表示真,0表示假。 -- 表达式由运算符和运算数组成。 -- 运算数:变量(字段)、值、函数返回值 -- 运算符: =, <=>, <>, !=, <=, <, >=, >, !, &&, ||, in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor is/is not 加上ture/false/unknown,检验某个值的真假 <=>与<>功能相同,<=>可用于null比较
d. GROUP BY 子句, 分组子句
GROUP BY 字段/别名 [排序方式] 分组后会进行排序。升序:ASC,降序:DESC 以下[合计函数]需配合 GROUP BY 使用: count 返回不同的非NULL值数目 count(*)、count(字段) sum 求和 max 求最大值 min 求最小值 avg 求平均值 group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。 e. HAVING 子句,条件子句 与 where 功能、用法相同,执行时机不同。 where 在开始时执行检测数据,对原数据进行过滤。 having 对筛选出的结果再次进行过滤。 having 字段必须是查询出来的,where 字段必须是数据表存在的。 where 不可以使用字段的别名,having 可以。因为执行WHERE代码时,可能尚未确定列值。 where 不可以使用合计函数。一般需用合计函数才会用 having SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。 f. ORDER BY 子句,排序子句 order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]... 升序:ASC,降序:DESC 支持多个字段的排序。 g. LIMIT 子句,限制结果数量子句 仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合,按照记录出现的顺序,索引从0开始。 limit 起始位置, 获取条数 省略第一个参数,表示从索引0开始。limit 获取条数 h. DISTINCT, ALL 选项 distinct 去除重复记录 默认为 all, 全部记录
- 子查询需用括号包裹。
-- from型
from后要求是一个表,必须给子查询结果取个别名。
- 简化每个查询内的条件。 - from型需将结果生成一个临时表格,可用以原表的锁定的释放。 - 子查询返回一个表,表型子查询。 select * from (select * from tb where id>0) as subfrom where id>1; -- where型 - 子查询返回一个值,标量子查询。 - 不需要给子查询取别名。 - where子查询内的表,不能直接用以更新。 select * from tb where money = (select max(money) from tb); -- 列子查询 如果子查询结果返回的是一列。 使用 in 或 not in 完成查询 exists 和 not exists 条件 如果子查询返回数据,则返回1或0。常用于判断条件。 select column1 from t1 where exists (select * from t2); -- 行子查询 查询条件是一个行。 select * from t1 where (id, gender) in (select id, gender from t2); 行构造符:(col1, col2, ...) 或 ROW(col1, col2, ...) 行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。 -- 特殊运算符 != all() 相当于 not in = some() 相当于 in。any 是 some 的别名 != some() 不等同于 not in,不等于其中某一个。 all, some 可以配合其他运算符一起使用。
将多个表的字段进行连接,可以指定连接条件。
-- 内连接(inner join)
- 默认就是内连接,可省略inner。 - 只有数据存在时才能发送连接。即连接结果不能出现空行。 on 表示连接条件。其条件表达式与where类似。也可以省略条件(表示条件永远为真) 也可用where表示连接条件。 还有 using, 但需字段名相同。 using(字段名) -- 交叉连接 cross join 即,没有条件的内连接。 select * from tb1 cross join tb2; -- 外连接(outer join) - 如果数据不存在,也会出现在连接结果中。 -- 左外连接 left join 如果数据不存在,左表记录会出现,而右表为null填充 -- 右外连接 right join 如果数据不存在,右表记录会出现,而左表为null填充 -- 自然连接(natural join) 自动判断连接条件完成连接。 相当于省略了using,会自动查找相同字段名。 natural join natural left join natural right join select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;
附:
MySQL 数据类型:(摘自:https://www.w3school.com.cn/sql/sql_datatypes.asp)
在 MySQL 中,有三种主要的类型:文本、数字和日期/时间类型。
数据类型 | 描述 |
CHAR(size) | 保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。 |
VARCHAR(size) | 保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。 注释:如果值的长度大于 255,则被转换为 TEXT 类型。 |
INT(size) | -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。 |
FLOAT(size,d) | 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
DATE() | 日期。格式:YYYY-MM-DD 注释:支持的范围是从 '1000-01-01' 到 '9999-12-31' |
DATETIME() | *日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS 注释:支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' |
YEAR() | 2 位或 4 位格式的年。 注释:4 位格式所允许的值:1901 到 2155。2 位格式所允许的值:70 到 69,表示从 |