Web前端之路Web 前端开发

7.MySQL子查询与连接

2017-05-07  本文已影响91人  你好星期四

子查询

子查询 子查询

使用比较运算符的子查询

=、 >、 <、 >=、 <=、 <>、 !=、 <=>

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)

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;

连接类型

连接条件

使用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
上一篇 下一篇

猜你喜欢

热点阅读