MySQL数据库学习笔记（2）

子查询与连接

子查询是指在另一个查询语句中的SELECT子句。如：

  SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

其中，SELECT * FROM t1 …称为Outer Query[外查询](或者Outer Statement), SELECT column1 FROM t2 称为Sub Query[子查询]。

所以，子查询嵌套在外查询内部。而事实上也可能在子查询内部再嵌套子查询。 子查询必须出现在圆括号之间。
子查询的外层查询可以是：SELECT, INSERT, UPDATE,SET，DO。

1、由比较运算符所引发的子查询（>,<,=,>=,<=,<>）

示例数据表

  CREATE TABLE IF NOT EXISTS tdb_goods(
    goods_id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_name  VARCHAR(150) NOT NULL,
    goods_cate  VARCHAR(40)  NOT NULL,
    brand_name  VARCHAR(40)  NOT NULL,
    goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
    is_show     BOOLEAN NOT NULL DEFAULT 1,
    is_saleoff  BOOLEAN NOT NULL DEFAULT 0
  );

选择商品价格的平均值

SELECT AVG(goods_price) FROM tdb_goods;
//保留两位小数
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;

选择价格大于及平均值的商品

SELECT * FROM tdb_goods WHERE goods_price > 5636.36;

将上述操作合并，使用子查询实现

SELECT * FROM tdb_goods WHERE goods_price >(SELECT AVG(goods_price) FROM tdb_goods);

ANY,SOME,ALL关键字

注意，ANY关键字和SOME关键字作用相同。

SELECT * FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = "台式机");

2、由[NOT] IN 引发的子查询

IN 与 =ANY 等价；NOT IN 与 ！= ALL 等价。

 SELECT * FROM tdb_goods WHERE goods_price !=ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = "台式机");
 //等价于
 SELECT * FROM tdb_goods WHERE goods_price NOT IN(SELECT goods_price FROM tdb_goods WHERE goods_cate = "台式机");

3、使用INSERT…SELECT语句

将查询记录插入到另外一个数据表中。

//新建数据表
 CREATE TABLE IF NOT EXISTS tdb_goods_cates (
    cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    cate_name VARCHAR(40)
    );
//将查询记录插入到表中
 INSERT INTO tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

4、多表更新

如何将tdb_goods表中的商品类型名称全部更新为tdb_goods_cates中的商品类型编号呢？这就需要用到多表更新的操作。

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name
    -> SET goods_cate = cate_id;

5、CREATE…SELECT 操作

创建数据表并且同时写入记录。可以不用分两步操作。
如下，将tdb_goods表格中的商品品牌单独拿出来存入新表。

 CREATE TABLE IF NOT EXISTS tdb_goods_brands(
    brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    brand_name VARCHAR(40)
    )
    SELECT brand_name FROM tdb_goods GROUP BY brand_name;

参照品牌表，更新商品表：

UPDATE tdb_goods INNER JOIN tdb_goods_brands ON tdb_goods.brand_name = tdb_goods_brands.brand_name
    SET tdb_goods.brand_name = brand_id;

修改tdb_goods表中的字段名和数据类型：

 ALTER TABLE tdb_goods
    CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
    CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

6、连接操作

连接可分为内连接和外连接，其中外连接又分为左外连接和右外链接。连接操作的语法结构为：

table_name
{[INNER |CROSS]JOIN | {LEFT|RIGHT}[OUTER]JOIN}
table_name
ON conditional_expr

MySQL中，INNER JOIN,CROSS JOIN, JOIN三者等价，都代表内连接。一般使用INNER JOIN.
LEFT [OUT] JOIN 左外连接
RHGHT [OUT] JION 右外连接
内连接仅显示左右表都符合条件的记录。

SELECT goods_id,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

左外链接显示左表的全部记录及右表中符合条件的记录。如果右表中没有符合条件的记录，将显示为NULL。
右外连接显示右边中的全部。

多表连接

查询所有商品的详细信息

SELECT goods_id, goods_name,cate_name,brand_name FROM tdb_goods AS g INNER JOIN tdb_goods_cates as c ON g.cate_id = c.cate_id INNER JOIN tdb_goods_brands as b ON g.brand_id = b.brand_id;