Mysql数据库-子查询和连接

新建一张表：create table tdb_goods(

同时插入数据 -> goods_id smallint(5) unsigned not null 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.000,

     -> is_show tinyint(1) nut null default 1,

          -> is_saleoff tinyint(1) not null default 0

       -> );

1.子查询：出现在其他sql语句内的select子句，嵌套在查询内部，而且必然出现再()内

select * from t1 where col1= (select col2 from t2);

外层查询                      内层查询

子查询可以包含多个关键字。

子查询可以返回标量/一行/一列或子查询。

比较运算符的子查询：=  >  <  >=  <=  <>  !=  <=>

select round(avg(goods_price),2) from tdb_goods; //查询平均价格并保留2位小数

    select goods_id,goods_name,goods_price from tdb_goods where goods_price>=5000.00;

查询goods_price字段值大于5000.00的几个字段。

 

将上述两个语句合并，就是子查询语句：

select goods_id,goods_name,goods_price from tdb_goods where goods_price>=(select round(avg(goods_price),2) from tdb_goods);

 

如果子查询返回多个结果，使用any  some (符合部分)   all(符合全部)

select goods_id,goods_name,goods_price from tdb_goods where goods_price>any (select goods_price from tdb_goods where goods_cate='超极本');

查询价格大于(goods_cate=超极本的价格) 的id，name，price。

--------------------------------------------------

[not] in (子查询语句) 

[not] exists ：如果子查询返回值，是true，否则是false。

2.使用insert...select插入记录：insert [into] tbl_name [(col_name,...)] select...

新建另外一张表： create table if not exists tdb_goods_cates(

     -> cate_id smallint unsigned primary key auto_increment,

    -> cate_name varchar(40) not null

     -> );

 并查看按某字段的分类：select goods_cate from tdb_goods group by goods_cate;

(查看数据表结构：desc tdb_goods_cates;)

 

依据查询条件插入记录： 

insert tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate;

3.多表更新：(接下来，参照分类表来更新商品表)

update table_references set col_name1={expr1|default} [,col_name2={expr2|default}]... [where where_condition]

示例：update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name

    -> set goods_cate=cate_id;

inner join是内连接，将两张表以字段goods_cate和cate_name相等为条件，并设置cate值=id值。

表tdb_goods的所有goods_cate字段的值，将被替换成表tdb_goods_cates的cate_id值。

4.多表更新：一步到位-- 建表+查询+写数据(将查询结果写入新建的数据表中)

create table [if not exists] tbl_name [(creat_definition,...)] select_statement

示例：create table tdb_goods_brands(

     -> brand_id smallint unsigned primary key auto_increment,

       -> brand_name varchar(40) not null)

       -> select brand_name from tdb_goods group by brand_name;

创建新表tdb_goods_brands，并设置有两个字段，并将查询结果直接插入表。

接下来仍是多表更新，主表根据副表的字段相应修改：

update tdb_goods as g inner join tdb_goods_brands as b on g. brand_name=b.brand_name set g.brand_name=b.brand_id;

这里as是给表起一个别名，使系统可以认出代表的是哪张表。

多表更新之后，记得要把主表的字段进行更新：

alter table tdb_goods

    -> change brand_name brand_id smallint unsigned not null;

5.两表连接： 

table_reference {[inner | cross] join | {left |right} [outer] join} table_reference on conditional_expr

内连接：inner join和cross join或join是等价的； //显示左表与右表的交集部分

select goods_id,goods_name,cate_name from tdb_goods inner join tdb_goods_cates on tdb_goods.cate_id=tdb_goods_cates.cate_id;

查询goods_id等3个字段，从两张表(以内连接方式)，查询条件是这两个字段值相等。

左外连接：left [outer] join //显示左表的全部记录及右表符合条件的记录

右外连接：right [outer] join //显示右表的全部记录及左表符合条件的记录

使用on设定连接条件，使用where关键字进行结果集记录的过滤。

6.多表连接：

select goods_id,goods_name,cate_name,brand_name,goods_price 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;

三张表共同inner join。

7.无限级分类表设计：

新建一张表： 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

   ); 

其中type_name存储分类，parent_id显示父类id(type_id).

比如id是2，name是电脑，parent_id是0(本身是父类)；

id是9，name是电脑整机，parent_id是2.(父类是id为2的电脑)

查询的时候，需要与本身做连接，查询所有子类及其父类：

 select s.type_id,s.type_name,p.type_name from tdb_goods_types as s 

left join tdb_goods_types as p on s.parent_id=p.type_id;

左侧p为父表，右侧s为子表。

 

查询所有父类及其子类：

select p.type_id,p.type_name,s.type_name from tdb_goods_types as p 

left join tdb_goods_types as s on s.parent_id=p.parent_id; 

对查询结果按p.type_name进行分组，并按p.type_id进行排序：

select p.type_id,p.type_name,s.type_name from tdb_goods_types as p left join tdb_goods_types as s on s.parent_id=p.parent_id group by p.type_name order by p.type_id; 

8.多表删除：delete tbl_name[.*] [,tbl_name[.*]]... From table_references [where where_condition]

将删除重复的记录，只保留id号较小的记录：

delete t1 from tdb_goods as t1 left join (select goods_id,goods_name from tdb_goods group by goods_name having count(goods_name)>=2) as t2 on t1.goods_name=t2.goods_name where t1.goods_id>t2.goods_id;

()内的查询出的表作为t2。

 

 

 

 

 

 

 

 

一直在http://www.imooc.com/ 学习，感觉不错。