MySQL Learning 15: subqueries (i)

A data preparation (1) Create a database CREATE DATABASE IF not EXISTS T2 CHARACTER SET GBK;
(2) Creating a data table

CREATE TABLE IF not EXISTS tdb_goods (

goods_id SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Goods_name VARCHAR () not NULL,

Goods_cate VARCHAR (+) not NULL,

Brand_Name VARCHAR (+) not NULL,

Goods_price DECIMAL (15,3) UNSIGNED not NULL DEFAULT 0,

Is_show BOOLEAN not NULL for DEFAULT 1,

Is_saleoff BOOLEAN not NULL DEFAULT 0

);

(3) View data table structure

SHOW COLUMNS from Tdb_goods;

(4) write a record

Set the format for the Chinese characters displayed by the MySQL database client:

SET NAMES GBK;

Insert Record:

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' R510VC 15.6

Inch notebooks ', ' notebooks ', ' asus ', ' 3399 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' y400n 14.0

Laptop ' , ' notebook ', ' United think ', ' 4899 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' g150th 15.6

inch game Ben ', ' Game Ben ', ' Ray God ', ' 8499 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' x550cc 15.6

Inch notebooks ', ' notebooks ', ' asus ', ' 2799 ', default,default);

Inserttdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' X240 (20ALA0

EYCD) 12.5 inch ultra-polar ', ' Super Ben ', ' Lenovo ', ' 4999 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' u330p 13.3

"Ultra-polar", ' Super Ben ', ' Lenovo ', ' 4299 ', default,default);

Inserttdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' SVP13226SC

B 13.3-inch touch ultra-polar ', ' Super Ben ', ' Sony ', ' 7999 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' IPad mini

md531ch/a 7.9 "Tablet PC ', ' tablet ', ' Apple ', ' 1998 ', Default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' IPad Air

md788ch/a 9.7 inch Tablet PC (16G WiFi version) ', ' tablet ', ' Apple ', ' 3388 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' IPad mini

me279ch/a with Retina display 7.9 inch tablet (16G WiFi version) ', ' tablet ', ' apple ', ' 2788 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Ideacentre

C340 20 inch PC ', ' Desktop ', ' Lenovo ', ' 3499 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Vostro 3800-

R1206 desktop computers ', ' desktops ', ' dell ', ' 2899 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' IMac

me086ch/a 21.5 inch PC ', ' Desktop ', ' apple ', ' 9188 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' AT7-7414LP

Desktop PC (i5-3450 four-core 4G 500G 2G single-Display DVD-mouse Linux) ', ' desktops ', ' Acer ', ' 3699 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Z220SFF

F4F06PA workstations ', ' Servers/workstations ', ' hp ', ' 4288 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' PowerEdge

T110 II server ', ' Server/workstation ', ' dell ', ' 5388 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Mac Pro

md878ch/a Professional desktop computer ', ' Server/workstation ', ' Apple fruit ', ' 28888 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' hmz-t3w

Head-mounted display device ', ' notebook accessory ', ' Sony ', ' 6999 ', Default,default ';

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Business double shoulder

Package ', ' Notebook accessories ', ' Sony ', ' Default,default ', and more;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' X3250 M4 machine

Rack Server 2583i14 ', ' Servers/workstations ', ' IBM ', ' 6888 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Xuan Dragon Elite ' edition

Notebook radiator ', ' notebook accessories ', ' Kyushu winds ', ' 134 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Business double shoulder

Package ', ' Notebook accessories ', ' Sony ', ' Default,default ', and more;

SELECT * from Tdb_goods\g;

Two sub-query (1) Sub-query related concepts 1 Sub-query

A subquery (subquery) refers to a SELECT clause that appears within another SQL statement (that is, another query statement).

Example:

SELECT * from t1 WHERE column1 = (select Column1 from T2);

Wherein, select * from T1, called outer query (external query or outer Statement), select Column1 from T2, said

Query for Sub Query (subquery).

2 Sub-query composition

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

A subquery can contain multiple keywords or conditions, such as distinct, GROUP by, ORDER by, limit, and related functions.

The outer query of a subquery can be: SELECT, INSERT, UPDATE, set, or do.

So, we say that the subquery is nested inside the outside of the query. In fact, it is possible to embed subqueries inside subqueries, and subqueries must appear in the circle

Between parentheses.

3 Sub-query return value

A subquery can return a scalar, a row, a column, or a subquery.

(2) three types of sub-queries 1 subquery raised by the comparison operator

Comparison operators include: =, >, <, >=, <=, <>,! =, <=>. The syntax structure of the subquery raised by using the comparison operator is; operand

Comparsion_operator subquery.

Example:

First, set the encoding format for the MySQL database client data display:

SET NAMES GBK;

1) Check the average price of all goods:

SELECT AVG (goods_price) from Tdb_goods;

2) The average value of all commodity prices is queried, and the result retains two decimal places

SELECT ROUND (AVG (Goods_price), 2) from Tdb_goods;

3) Check the prices of all goods more than the average price of goods

SELECT goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price >= 5324.32;

4) Search by sub-query for all goods with a price greater than the average price

Select Goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price >= (select

ROUND (AVG (Goods_price), 2) from Tdb_goods);

This shows that 3 and 4) query results are consistent, indicating that the subquery is working.

5) Check the product type is the product price of ultra-polar

SELECT goods_price from tdb_goods WHERE goods_cate = ' super Ben ';

6) Check the product type is super-Ben's products

SELECT * from tdb_goods WHERE goods_cate = ' super Ben ' \g;

2 subqueries with comparison operators with any, some, or all modifiers

When a subquery returns multiple results, you can use the any, some, or all modifier comparison operators, where any and some are equivalent, and

It is only required to conform to one of them, and all indicates that all return results are met. Where the syntax structure is:

Operand comparsion_operator any (subquery);

Operand comparsion_operator SOME (subquery);

Operand comparsion_operator All (subquery);

Comparison operators use different results for any, some, or all keywords:

1) Check the prices of all products not less than the super price (minimum price)

Select Goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price >= any (select

Goods_price from tdb_goods WHERE goods_cate = ' super Ben ');

2) Check the prices of all products not less than the super price (maximum price)

Select Goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price >= All (select

Goods_price from tdb_goods WHERE goods_cate = ' super Ben ');

3) Check the prices of all products equal to the super price (any one) of the goods

Select Goods_id,goods_name,goods_price from tdb_goods WHERE goods_price = any (select

Goods_price from tdb_goods WHERE goods_cate = ' super Ben ');

 

MySQL Learning 15: subqueries (i)