MySQL 15: subquery (1)

Data preparation (1) Create Database CREATEDATABASEIFNOTEXISTSt2CHARACTERSETgbk; (2) create a data table CREATETABLEIFNOTEXISTStdb_goods (outputs, goods_nameVARCHAR (150) NOTNULL, goods_cateVARCHAR (40)

1. Data preparation (1) create database if not exists t2 character set gbk; (2) create table if not exists tdb_goods (goods_id smallint unsigned primary KEYAUTO_INCREMENT, goods_name VARCHAR (150) not null, goods_cate VARCHAR (40)

Data preparation (1) create database if not exists t2 character set gbk;
(2) create a data table

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

);

(3) view the data table structure

Show columns from tdb_goods;

(4) Write records

Set the Chinese character code format displayed on 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 notebook ', 'notebooks', 'asus ', '123', DEFAULT, DEFAULT );

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

Inch laptop ', 'laptop', 'lenovo ', '123', DEFAULT, DEFAULT );

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

Inch game book ', 'game book', 'raytheon ', '123', DEFAULT, DEFAULT );

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

Inch notebook ', 'notebooks', 'asus ', '123', DEFAULT, DEFAULT );

INSERTtdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('x240 (20ALA0

EYCD) 12.5 inch Ultrabook ', 'superscript', 'lenovo', '123', DEFAULT, DEFAULT );

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

Inch ultraboat ', 'superscript', 'lenovo', '123', DEFAULT, DEFAULT );

INSERTtdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('svp13226sc

B 13.3 inch touch Ultrabook ', 'superscript', 'sony', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('ipad mini

MD531CH/A 7.9 inch tablet ', 'tablet', 'apple', '123', 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 (16G WiFi) ', 'tablet', 'apple', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('ipad mini

ME279CH/A is equipped with Retina Display 7.9 inch tablet (16G WiFi) ', 'tablet', 'apple', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('ideacentre

C340 20 inch integrated computer ', 'desktop', 'lenovo ', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('voastro 3800-

R1206 desktops, desktops, Dell, 2899, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('imac

ME086CH/A 21.5 inch integrated computer ', 'desktop', 'apple', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('at7-7414LP

Desktop Computer (i5-3450 quad-core 4G 500G dual display DVD mouse Linux) ', 'desktop', 'acer ', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('z220sff

F4F06PA workstation ', 'server/workstation', 'hp ', '000000', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('poweredge

T110 II server ', 'server/workws', 'Dell', '123', 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/workws', 'apple', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('HMZ-T3W

Headset display device ', 'laptop accessories', 'sony ', '000000', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('business shoulder and shoulder

Package ', 'laptop accessories', 'sony ', '99', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('x3250 M4 Machine

Server Type 2583i14 ', server/workstation', IBM, 6888, DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('xuanlong Elite edition

Notebook radiator ', 'notebook accessories', 'kyushu Fengshen ', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('business shoulder and shoulder

Package ', 'laptop accessories', 'sony ', '99', DEFAULT, DEFAULT );

SELECT * FROM tdb_goods \ G;

Subquery (1) subquery related concepts 1 subquery

Subquery indicates the SELECT clause in other SQL statements (that is, in another query statement.

Example:

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

SELECT * FROM t1 is called Outer Query (external Query or Outer Statement), and SELECT column1 FROM t2 is called

It is a Sub Query (subquery ).

2 subqueries

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

A subquery can contain multiple keywords or conditions, such as DISTINCT, group by, order by, LIMIT, and related functions.

The outer query of the subquery can be SELECT, INSERT, UPDATE, SET, or DO.

Therefore, subqueries are nested inside external queries. In fact, it is possible that the subquery is nested inside the subquery, And the subquery must appear in the circle

Parentheses.

3 subquery Return Value

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

(2) Category 3 subqueries 1 subqueries caused by comparison Operators

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

Comparsion_operator subquery.

Example:

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

Set names gbk;

1) query the average prices of all commodities:

Select avg (goods_price) FROM tdb_goods;

2) query the average prices of all commodities and keep the result in two decimal places.

Select round (AVG (goods_price), 2) FROM tdb_goods;

3) query all commodities whose prices are larger than the average prices

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= 5324.32;

4) query all commodities whose prices are larger than the average prices through subqueries

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price> = (SELECT

ROUND (AVG (goods_price), 2) FROM tdb_goods );

From this we can see that the query results of 3) and 4) are consistent, which indicates that the subquery takes effect.

5) query the price of a product whose type is Ultrabook

SELECT goods_price FROM tdb_goods WHERE goods_cate = 'superscript ';

6) query items whose types are super-sized items

SELECT * FROM tdb_goods WHERE goods_cate = 'superscript' \ G;

2 subqueries using ANY, SOME, or ALL-modified comparison Operators

When a subquery returns multiple results, you can use the comparison operators modified by ANY, SOME, or ALL. Among them, ANY and SOME are equivalent.

Yes, only one of them is required, and ALL indicates that ALL returned results are met. The syntax structure is as follows:

Operand comparsion_operator ANY (subquery );

Operand comparsion_operator SOME (subquery );

Operand comparsion_operator ALL (subquery );

Different results of comparison operators using the keyword ANY, SOME, or ALL:

1) query all commodities whose prices are 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 = 'superscript ');

2) query all commodities whose prices are 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 = 'superscript ');

3) query all commodities whose prices are equal to the super price (any one)

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price = ANY (SELECT

Goods_price FROM tdb_goods WHERE goods_cate = 'superscript ');