Mysql--5--subquery and connections

1. Sub-query

Refers to a SELECT clause that appears within other SQL statements

Form: SELECT * from T1 WHERE col1= (select COL2 from T2);

where select * from T1 is called an external query (note that it is not limited to select statements, including SELECT, INSERT, UPDATE, DELETE)

(SELECT COL2 from T2) is called a subquery:

A subquery is nested inside a query and must always appear in parentheses;

A subquery can contain multiple keywords or conditions, including select GROUP By/order by/limit

An external query can be insert, SELECT, UPDATE, DELETE, SET, do ...

The return value of a subquery can be a scalar, a row, a column, a table (child subquery)

1.1 Using Subqueries For comparison

Comparison operators: <, > = <= >=! =

General form: Select field 1[as b], field 2, .... From the Where Field 3/field 3 expression ' >= operator (subquery expression--select field 3/Field 3 expression from .......);

  
 

   
  
Select Code,Name, Region,Continent,lifeexpectancy  fromCountrywhere lifeexpectancy >= (Selectround(avg(lifeexpectancy),1)  fromCountry)Order by lifeexpectancydesc;
  
 

 
   
  

  
    
   
select goods_id,goods_name,goods_price from tdb_goods where goods_price >=(select round(avg(goods_price),2) from tdb_goods);　＃＃avg 是一个聚合函数，只有一个值
 
   
  

When a subquery returns multiple results, it needs to be decorated with any, SOME, and all: where SOME and any are the same,

 
   
  
 
    
   
select goods_price,goods_name,goods_id from Tdb_goods where Goods_price    > any    (select Goods_price From Tdb_goods where goods_cate= ' Super Ben '); # Any here refers to a price that is greater than any of the qualifying conditions (which is greater than the minimum). If it is all. is greater than the maximum   
 
   
  
 

 
   
  

  
    
   
select goods_price,goods_name from tdb_goods where goods_price !=all(select goods_price from tdb_goods where cate_id=1);

  
    
   
select goods_price,goods_name from tdb_goods where goods_price =all(select goods_price from tdb_goods where cate_id=1);

 
   
  

1.2 Subqueries using ' not ' in

... ' not ' in (subquery)

=any and in equivalence;

！ =all and Notin equivalence

1.3 Insert ... Select

Sub-query used:

There may be a lot of duplicate information in the table, such as the brand in multiple records, the type is the same, because the record may be infinitely large, and the Chinese characters occupy more space. Therefore, the data table is very large query speed is very slow. It is therefore necessary to make a data sheet to store the brand or classification, to correlate and to thin the original data table.

The first step:

Create a branding table:

 
   
  

  
    
   
create table brands(brand_id smallint unsigned key auto_increment,bname varchar(40) not null);
 
   
  

To create a type table:

 
   
  

  
    
   
create table goods_cates(cate_id smallint unsigned key auto_increment,cate_name varchar(40) not null);
 
   
  

Step Two:

Insert the Goods_brand and goods_cate in the tdb_goods into the corresponding data table, respectively:

 
   
  

  
    
   
insert goods_cate(cate_name) select goods_cate from tdb_goods group by goods_cate;
  
    
   
insert brands(bname) select goods_cate from tdb_goods group by goods_cate; 
 
   
  

　

The third step: multi-table Update--with the original Tdb_goods create connection update--refer to the classification table, brand table update the original table, with cate_id and brand_id instead of the corresponding Chinese

Form:UPDATE table_reference SET Col1={expr|default},......, [WHERE ...]

  
 

   
  

  
update tdb_goods inner join brands on goods_brand=brand_name set goods_brand=brand_id;
   
  

  
update tdb_goods inner join tbd_cates on goods_cate=cate_name　set goods_cate=cate_id;
   
  

  
update tdb_goods inner join brands on goods_brand=brand_name set goods_brand=brand_id inner join tbd_cates on goods_cate=cate_name set goods_cate=cate_id;## 试下对不对，明天
  
 

The reference relationship of the Table_reference table, because it is a multi-table, is the connection between multiple tables.

Table_reference can be a table1 [[as] alias 1] or an alias for a table1_subquery [[as] subquery]

Structure of the connection:

Table_reference1 Connection type 　 table_reference2 on connection conditions

Connection type:

Inner Connection---INNER join	-Connect two sheets, intersect
Outer link--Left/right outer connection left/right [OUTER] Join	All + right tables on the left table match the conditions/right side table all + the left table matches the criteria

A concise notation:

Create and write a one-step update (first and second steps):Create TABLE table_name (Field properties) Selsect col from table_name expr;

  
 

   
  

  
 
  create table       tdb_ Cates (cate_id smallint unsigned key auto_increment,  cate_name    varchar (+) NOT null)  select     cate_name    from Tdb_goods the group by cate_name;# #因为id is automatically numbered, so you can use no tube, just select Cate_ The value of the Name field is inserted into the Cate_name field of the Tdb_cates (the name of the field you selected is the best one for the table you want to insert, or the other column is added, such as 1, or the ID field is defined only when you create it/such as 2:) 
   
   
  

  
create table tdb2_brands(brand_id smallint unsigned key auto_increment) select brand_name from tdb_brands group by brand_name;
  
 

 
   
  

  
    
   
create table tdb_brands(brand_id smallint unsigned key auto_increment,bname varchar(40) not null) select brand_name from tdb_goods group by brand_name;（列名不一样）
 
   
  

Column name is not the same

Omit column name when creating, directly with inserted column name

Third Step connection: Of course, because the brand name field of the two tables to be connected is the same, it is necessary to prefix the table name or give the table its alias, if the field is unique and does not cause confusion:

 
   
  

  
    
   
update tdb_goods as tg inner join tdb_cates as tc on tg.cate_name=tc.cate_name set cate_name=cate_id;
 
   
  

One-step update of the branding table:

 
   
  

  
    
   
create table tdb_brands(brand_id smallint unsigned key auto_increment,bname varchar(40) not null) select brand_name from tdb_goods group by brand_name; ##创建品牌表并且插入查询到的品牌名称
  
    
   
update tdb_goods as tg inner join tdb_brands as tb on tg.brand_name=tb.brand_name set tg.brand_name=tb.brand_id;＃＃连接原表和品牌表，并更新原表
 
   
  

Next. Although the Chinese representation of the brand and type field is replaced by the number of its corresponding reference table, the field properties of the Tdb_goods Brand_Name and Cate_name are still character types when they are originally defined, so they are modified to the corresponding int type:

 
   
  

  
    
   
alter table tdb_goods change goods_cate cate_id smallint unsigned not nill,change goods_brand brand_id smallint unsigned not null;＃＃原字段名１　新字段名１及其属性，……
 
   
  

 
   
  

  
    
   
select goods_id,goods_name,goods_price,brand_name,cate_name from tdb_goods inner join goods_cates as gc on gc.cate_id=tdb_goods.cate_id inner join tdb_brands as tb on tb.brand_id=tdb_goods.brand_id\G;
 
   
  

 
   
  

  
    
   
alter table tdb_goods change goods_cate cate_id smallint unsigned not null, change goods_brand brand_id smallint unsigned nut null;
 
   
  

Self-Connection:

1. Infinite pole Classification-----------the same data table is connected to itself and must be aliased.

Many of the database under a lot of very small classification, such as electrical appliances and appliances under the kitchen appliances and furniture, and kitchen appliances are divided into rice cookers, microwave ovens and the like. The bottom may be divided into many classes.

Find out who is the parent of each name in the table. Imagine the right side of the same table to the left of the child table as the parent table

The third column of the parent table is useless.

A. If I want to see who is the parent table entry for the entry in the child table, the entries in the child table should appear:

 
   
  

  
    
   
select s.type_id,s.type_name,p.type_name from tdb_goods_types as p right join tdb_goods_types as s on p.type_id=s.parent_id;##子表s中的条目要全部出现，因此连接要选择ｊｏｉｎ右边s的方位，即右连接；
 
   
  

B. If you want to see what subclasses are under the parent's entry, the entries for the parent class will all appear:

 
   
  

  
    
   
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.type_id;
 
   
  

B.1 If you want to see the number of subclasses under the parent class instead of the child class, then:

 
   
  

  
    
   
select p.type_id,p.tyoe_name,count(s.type_name) child_count　from tdb_goods_types as p left join tdb_goods_types as s on s.parent_id=p.type_name group by p.type_id order by p.type_id;##先做了一个左连接，然后对查询结果按照父类的type_name做了分组，分组的顺序按照p.type_id的顺序，并且计算了父类下面子类的数目，（注意其表达方式）
 
   
  

2. Multiple table deletions: Delete duplicate records in a table, keeping records with smaller IDs.

First, look for duplicate records:

 
   
  

  
    
   
select goods_id,goods_name from tdb_goods group by goods_id;##将所有的记录查看
 
   
  

GROUP BY A can be understood to list content that is not duplicated in the A field;

As you can see, there are 24 records in the original table, but only 22 are queried because there are duplicate trade names (22, 23).

The total number of corresponding subclasses under the query parent class:

 
   
  

  
    
   
select goods_name,count(goods_id) name_count from tdb_goods group by goods_name order by goods_id;
 
   
  

Pick out two or two records individually:

 
   
  

  
    
   
select goods_name,count(goods_id) name_count,goods_id from tdb_goods group by goods_name having name_count >=2 order by goods_id;
 
   
  

Multiple table Delete:delete tbl_name1,[tbl2_name,~~~] from tbl_references [WHERE where_condition]

The following is also a single-table simulation of multi-table deduplication operations, the use of their own connection-

The table above is the table we are going to delete/preserve next.

First, the table from which to delete (reference alias T1), and then on the connection in the table-with a subquery get, (reference alias T2), the condition of the connection is the same name, but the same is not all deleted, but rather delete the larger record ID number (the ID number in the table above is the same name under the small 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_id) >=2) as t2 on t1.goods_name=t2.goods_name where t1.goods_id >t2.goods_id;
   
  

  
＃＃小括号中的子查询语句即为我们上一步得出的有两条重复以上的记录。黑色加粗字体是多表删除的框架，删除的是ｉｄ号较大的重复记录；红色字体的部分－－左连接整体是作为参照表，连接的条件是两张表的ｎａｍｅ相同；绿色底色的部分－－子查询整体是连接的右表，子查询返回的结果有两条重复以上的记录，


  
 

As you can see, only 22 records after deletion, that is, duplicate records of numbers 22 and 23rd are deleted!

CREATE TABLE tdb_goods_types (type_id SMALLINT UNSIGNED PRIMARY KEY auto_increment, type_name VARCHAR) NO T null, parent_id SMALLINT UNSIGNED not null DEFAULT 0);

Mysql--5--subquery and connections