MySQL study NOTE _ 6_ SQL language design and writing (below) _ MySQL

MySQL study NOTE _ 6_ SQL language design and writing (below) bitsCN. comSQL language design and writing (below) -- explain the SELECT query

Summary:

SELECT [ALL | DISTINCT] # distinct is DISTINCT, clear, and different.

{* | Table. * | [table.] field1 [asalias1] [, [table.] field2 [as alias2] [...]} # alias, alias

FROM table name

[WHERE...]

[GROUPBY...]

[HAVING...]

[ORDERBY...]

[LIMITcount]

The SELECT query language is used to check the data and return the results according to the user's ideas!

1. the fields to be queried must be listed.

E.g. selectname, price from products;

Selectprice, name from products;

Select * from products;

Selectproducts. * from products; # The table name is not required for a single table.

2. you can create an alias for each field [used later (keyword, multi-table query)] [table alias (multi-table query )]

E.g. selectname as bookname, price as bookprice from products; # use aliases; do not add as; note that single quotation marks are required when there are spaces in aliases;

3. use distinct to cancel duplicate data with the entire record. only one column is returned instead of the other.

E.g. selectdistinct price 'Book price' from products;

4. columns using expressions in SQL statements (arithmetic operators, conditional operators, and logical operators can be used ...)

E.g. select1 + 2*3;

Select8 % 5

Updateproducts set num = num + 1 where id = 22;

Selectname, price, price * 0.7 as 'discount price' from products where id <= 15;

5. WHERE can be found in SELECT/UPDATE/DELETE

A) available logical operator numbers (combining multiple conditions)

&/AND |/OR! /NOT

B) comparison operator numbers that can be used

= # Determine whether or not they are equal, which is the same as = in the program

<=> # Determine whether to be equal, consistent with =, but can be used for comparison with NULL

! =/<> # No.

<

<=

>

> =

C) operators not in the program

ISNULL # equal to '<=> Null'

ISNOT NULL

BETWEENAND

E.g. select * from products where id between 10 and 20;

Same as "select * from products where id >=10 & id <= 20 ;"

NOTBETWEEN AND

IN

E.g. select * from products where id in (5, 10, 15, 20 );

Updateproducts set num = 77 where id in (5, 10, 15, 20 );

Deletefrom products where id in (5, 10 );

D) Fuzzy search

LIKE _ (any one character) and % (0 or multiple arbitrary characters) two wildcard numbers

E.g. select * from products where name like '______'; # search for data with any name of 6 characters

Select * from products where name like '% java %'; # The query name contains java data.

NOTLIKE

E.g. select * from products where name not like '% java %'; # query data whose names do not contain the word "java.

REGEXP/RLIKE [regular expression] # RegExp regular expression

E.g. select * from products where name regexp '^ Java'; # search for all data starting with java

Select * from products where name regexp's $ '; # search for all data ending with s

6. multi-table queries (connection queries), which are commonly used # ambiguous

E.g. selectcats. name, products. name from cats, products;

Selectc. name cname, c. desn cdesn, p. name pname, p. price, p. desn pdesn, p. numfrom carts c, products as p; # match the records in Table A with those in Table B in sequence to obtain the result of A * B [Cartesian product], which is meaningless.

Selectc. name cname, c. desn cdesn, p. name pname, p. price, p. desn pdesn, p. numfrom carts c, products as p where c. id = p. cid;

Selectc. name cname, c. desn cdesn, p. name pname, p. price, p. desn pdesn, p. numfrom carts c, products as p where c. id = p. cid and c. id = 3;

Selecta. id aid, a. name aname, B. id bid, B. name bname from cats a, catsb; # divide a single table into multiple tables for query

Selecta. id aid, a. name aname, B. id bid, B. name bname from cats a, cats B wherea. pid = B. id;

7. nested query subquery

E.g. select * from products where cid in (select id from carts where name regexp '^ J ');

Select * from products where cid in (select id from carts where name like 'J % '); # Same role

8. orderby field [asc forward] desc reverse

E.g. select * from order by name;

Select * from order by price; # Sort by non-decreasing price

Select * from order by price desc; # Non-incremental sorting

Select * from where cid> 5 order by price desc; # used with where

9. limitcount [display limit]

E.g. select * from limit 7;

Select * from order by id desc limit 7;

Select * from where id <10 order by id desc limit 7;

Select * from where id> 14 order by id asc limit 0, 1; # limit0, 1 indicates to get from 0th, take 1

10. groupby field [group]

Common functions:

Count () # total number of fields

Sum ()

Avg () # average value

Max ()

Min ()

E.g. selectcount (*), sum (price), avg (price), max (price), min (price) from products;

Selectcid, count (price), sum (price), avg (price), max (price), min (price) fromproducts group by cid;

Selectcid, count (price), sum (price), avg (price), max (price), min (price) fromproducts group by cid having avg (price)> 50; # add the having condition, which is similar to where

# Having must be used in combination with gropby.

BitsCN.com