子查询
子查询
子查询
使用比较运算符的子查询
=、 >、 <、 >=、 <=、 <>、 !=、 <=>
SELECT AVG(salary) FROM users;#求平均值
SELECT ROUND(AVG(salary),2) FROM users;#四舍五入，保留两位小数
eg
SELECT id, username FROM users WHERE salary >= (
SELECT ROUND(AVG(salary),2) FROM users
);
在这种比较运算符中，子查询的结果不能大于1条，否则不能直接比较，如果想要比较，需要使用ANY、SOME或ALL修饰的比较运算符。
comparison_operator ANY (subquery)
comparison_operator SOME (subquery)
comparison_operator ALL (subquery)
例如：
... > ANY(subquery) 指的是，只要大于子查询中的任意一个值就可以了。
ANY,SOME,ALL
使用[NOT] IN 的子查询
语法结构
comparision_operator [NOT] IN (subquery)
=ANY运算符，与IN等效。
!=ALL或<>ALL与NOT IN等效。
INSERT ... SELECT
将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
eg
#将查询出来的user_title全部插入到titles表中的title_name列
INSERT titles(title_name) SELECT user_title FROM users GROUP BY user_title;
多表更新
UPDATE table_rederences SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}]...[WHERE where_condition]
eg
UPDATE table_1 INNER JOIN table_2 ON table_1.id = table_2.pid SET table_1.name = '职工';
CREATE...SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement
eg
CREATE TABLE test2(
id INT ...
name VARCHAR(50) ...
)
SELECT username FROM users GROUP BY username;
连接
语法结构
table_reference
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON conditional_expr;
连接类型
- INNER JOIN (JOIN,CROSS JOIN和INNER JOIN是等价的)
- LEFT [OUTER] JOIN,左外连接
- RIGHT [OUTER] JOIN,右外连接
连接条件
使用ON关键字来设定连接条件，也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤。
内连接
内连接
SELECT id, name FROM users INNER JOIN test2 ON test2.id = users.id;
左外连接
SELECT id, name FROM users LEFT JOIN test2 ON test2.id = users.id;
左外连接
右外连接
SELECT id, name FROM users RIGHT JOIN test2 ON test2.id = users.id;
右外连接
自身连接
自身连接可用于实现无限极分类表设计。
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
示例数据
data
eg1
SELECT A.type_name AS type_name, B.type_name AS parent_name
FROM tdb_goods_types AS A
INNER JOIN tdb_goods_types AS B
ON
A.parent_id = B.type_id;
查询结果
data2
eg2
SELECT A.type_name AS type_name,COUNT(B.type_name) AS child_counts
FROM tdb_goods_types AS A
INNER JOIN tdb_goods_types AS B
ON A.type_id = B.parent_id
GROUP BY type_name;
data3
