MySQL Learning note _6_sql language design and writing (next)

 SQLthe design and writing of language (the following)--selectQuery Explaining

Profile:

Select[all | DISTINCT] #distinct Obvious, clear, differentiated

{*|table.*|[ Table.] Field1[asalias1][,[table.] Field2[as alias2]][...]} #alias alias, pseudonym

from Table name

[WHERE ...]

[GROUPBY ...]

[Having ...]

[By ...]

[Limitcount]

Use SELECT Query Language, the purpose is to be able to find the data according to the user's ideas, return the results!

1 , field to list the fields to query

e.g. Selectname,price from the products;

Selectprice,name from Products;

Select* from Products;

selectproducts.* from the products; A single table actually doesn't need to use a table name

2 , individual names can be used for each field "(keyword, multi-table query)" "Table can also alias (multi-table query) "

e.g. Selectname as bookname,price as Bookprice from products;# use an alias, or do not add as Note that there are spaces in the alias, you need to add single quotation marks;

3 , using distinct functions with the entire record, cancels duplicate data, returns only one, not a single column

e.g. selectdistinct "price ' from the products;

4 , in SQL a column using an expression in a statement (you can use arithmetic operators, conditional operators, logical operators ... )

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 in Select/update/delete in

a ) The logical operation symbols that can be used (combining multiple conditions)

&&/and | | /or!/not

b) comparison operation symbols available for use

=# determine if they are equal, as in the program == Same Effect

<=># Judging whether it is equal, with = consistent, but can be used with NULL Compare

!=/<> # Not Equal

<

<=

>

>=

C ) operators that are not in the program

ISNULL # with the ' <=>null ' Equal

ISNOT NULL

Betweenand

e.g. select* from products where ID between and 20;

with the "select* from products where ID >= && ID <=;" Same Effect

Notbetween and

Inch

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

updateproducts Set num = n where ID in (5,10,15,20);

Deletefrom products where ID in (5,10);

D ) Fuzzy query

Like _ (any one character) and % ( 0 One or more of the characters) two wildcard symbols

e.g. select* from products where name is like ' ___ '; find any name as 6 Characters of data

Select* from the products where name is like '%java% '; the query name contains a Java of Data

Notlike

e.g. select* from the products where name is not a like '%java% '; the query name does not contain Java the data for the typeface.

Regexp/rlike "Regular expression" #RegExp Regular Expressions

e.g. select* from products where name regexp ' ^java '; Find all the Java data at the beginning

select* from Products where name RegExp ' s $ '; Find all the s End of data

6 , multi-table query (connection query), more 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;# will be A the records in the table and B the records in the table match sequentially to get a*b "Cartesian product" of the result 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.I. d=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 querying

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

7 , nested query sub-query

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 Effect

8 , by Field [ASC Positive sequence ]desc Reverse

e.g. select* from order by name;

select* from order by Price; Sort by price non-descending

select* from ORDER BY price desc; # non-ascending sort

select* from the WHERE CID > 5 order BY price desc; with the where combined Use

9 , Limitcount "Limit display count"

e.g. select* from limit 7;

Select* from ORDER BY id DESC limit 7;

Select* from where ID < ten ORDER BY id DESC limit 7;

select* from where ID > + ORDER BY ID ASC limit 0,1; #limit0, 1 indicates from the first 0 start fetching, take 1 a

Ten , GroupBy field "Grouping"

Common functions:

count () # total number of a field

SUM ()

avg () # Average

Max ()

Min ()

e.g. SelectCount (*), SUM (price), AVG (Price), Max (Price), min (price) from products;

Selectcid,count (Price), sum (price), Avg., Max (Price), min (price) fromproducts GROUP by CID;

Selectcid,count (Price), sum (price), avg., Max (Price), min (price) Fromproducts GROUP BY CID have AVG (price) > 50; # plus having conditions, similar to where

#having must be associated with Gropby combined to use

MySQL Learning note _6_sql language design and writing (next)