Article From:https://www.cnblogs.com/kingle-study/p/9967350.html

SQLStatement execution order: from-where-group by-have-select-order by

where Multiple conditions can be filtered, such as where A and B

1.sort
order by Column name ASC | desc
asc:Ascending order is by default ascending order, so it can be omitted.
desc:Descending order
Ever end of SQL statement

2.distinct To remove duplication, you can only specify one field, specifying that multiple fields are distinct invalid

Invalid: SELECT DISTINCT price, pname FROM product ORDER BY price

Effective: SELECT DISTINCT price FROM product ORDER BY price

3.alter table Add constraint foreign key name from table (custom) foreign key from table (column name)
references Main table (column name);

4.If the type of time is date, use symbols to compare sizes
SELECT empno,ename,hiredate FROM emp WHERE hiredate > ‘1981-02-01’
AND hiredate < ‘1987-05-01’
l  Simple query

    Removing duplicate data

    Select distinct  *|Column Name, Column Name From Table Where Conditions

    Alias (as can be omitted)

    Select *  from  product as  p

    Select   pname        as  pn  from product

    The result of the query is expression (operation query): display the price of all commodities + 10 yuan

    select  pname,price+10  from  product;
l  Conditional query

Comparison operator
    

>  <  <=   >=   =  <>
    

Greater than, less than, greater than (less than) equal to, or not equal to

BETWEEN  …AND…
    

Values displayed in an interval (with head and tail)

IN(set)
    

Values displayed in the in list, example: in (100,200)

LIKE ‘Zhang Pattern’
    

Fuzzy query, in the Like statement,

%Represents zero or more arbitrary characters,

_Represents a character.

For example: first_name like’_a%’;

IS NULL
    

Judge whether it is empty

Logical Operator
    

and
    

Multiple conditions hold at the same time

or
    

Any of several conditions holds

not
    

Where not (salary & gt; 100);

#Search for all commodities between 200 and 1000

SELECT * FROM product WHERE price >= 200 AND price <=1000;

SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

 

#Inquire about all goods whose prices are 200 or 800

SELECT * FROM product WHERE price = 200 OR price = 800;

SELECT * FROM product WHERE price IN (200,800);

 

#Query all goods with the word’Ba’

SELECT * FROM product WHERE pname LIKE ‘%Ba% ‘;

 

#Inquire about all goods starting with’fragrance’

SELECT * FROM product WHERE pname LIKE ‘Fragrance%;

 

#Look up all the goods with the second word’Want’

SELECT * FROM product WHERE pname LIKE ‘_Think%;

 

#Goods that are not classified

SELECT * FROM product WHERE category_id IS NULL

 

#Search for Classified Goods

SELECT * FROM product WHERE category_id IS NOT NULL
l  Sort query

#With the order by statement, you can sort the query results.

    SELECT * FROM Table name ORDER BY sort field ASC | DESC;

#On the basis of price order (descending order), sort by category (descending order)

    SELECT * FROM product ORDER BY price DESC,category_id DESC;

#Display the price of the item (to repeat) and sort (to descend)

    SELECT DISTINCT price FROM product ORDER BY price DESC;
l  Aggregate query

Previous queries we made are horizontal queries, they are based on the conditions of line by line judgment, and the use of aggregation function queries are vertical queries, it is a column of values calculated, and then returned a single value; in addition, aggregation function will ignore empty values.

Today we will learn the following five aggregation functions:

    count:Statistically specify the number of rows that are not NULL.

    sum:Calculate the numerical sum of the specified column. If the specified column type is not a numerical type, the result is 0.

    max:Calculates the maximum value of the specified column. If the specified column is a string type, string sorting is used.

    min:Calculates the minimum value of the specified column. If the specified column is a string type, string sorting is used.

    avg:Calculate the average value of the specified column. If the specified column type is not a numerical type, the result is 0.

 

l  Query the sum of all commodity prices categorized as’c001′

   SELECT SUM(price) FROM product WHERE category_id = ‘c001’;

l  The query is categorized as’c002′ average price of all goods

  SELECT AVG(price) FROM product WHERE category_id = ‘c002’;

l   Search for maximum and minimum prices of goods

  SELECT MAX(price),MIN(price) FROM product;
l  Group query

Group query refers to the use of group by sentence to group query information.

Format:

    SELECT Field 1, field 2… FROM table name GROUPBY grouping field HAVING grouping condition;

    The having clause in grouping operation is used to filter the data after grouping, and its function is similar to where condition.

        

havingDistinction from where:

     havingData is filtered after grouping.

     whereFiltering data before grouping

          havingGrouping functions (statistical functions) can be used later.

          whereGrouping functions may not be used later.

1 Statistics of the number of classified commodities

    SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;

2 Statistics of the number of classified goods, and only show the number of more than 1 information

    SELECT category_id ,COUNT(*) FROM product GROUP BY category_idHAVING COUNT(*) > 1;
l  Foreign key constraint

l  Declare foreign key constraints

Syntax: alter table adds [constraint] [foreign key name] foreign key (field name of foreign key from table) references main table (primary key of main table);

[The foreign key name] is used to delete foreign key constraints. It is generally recommended that “_fk” end

    altertable Drop ForeignKey foreign key name from table

l  The purpose of using foreign keys is:

    Ensuring data integrity

 
l  multi-table query

SQLStatement execution order: from-where-group by-have-select-order by

1.        Cross-join queries (not used at all – get the product of two tables) [Understanding]

          Grammar: select * from A, B;

2.        Inner join query (keyword inner join – inner can be omitted)

           Implicit internal connection: select* from A, where condition;

           Display the inner connection: select * from A inner join B on condition;

3.        External join queries (the keyword outer join – out can be omitted)

          Left outer join

              select * from A left outer join B on Conditions;

          Right outer join

              select * from A right outer join B on Conditions;

The difference between the three connections:

For example:

CREATE TABLE category (

  cidVARCHAR(32) PRIMARY KEY ,

  cnameVARCHAR(50)

);

CREATE TABLE products(

  pidVARCHAR(32) PRIMARY KEY ,

  pnameVARCHAR(50),

  price INT,

  flagVARCHAR(2),              #Whether the upper shelf is marked as: 1 for upper shelf, 0 for lower shelf

  category_idVARCHAR(32),

  CONSTRAINTproducts_fk FOREIGN KEY (category_id) REFERENCES category (cid)

);

 

#classification

INSERT INTO category(cid,cname) VALUES(‘c001’,’Household appliances);

INSERT INTO category(cid,cname) VALUES(‘c002’,’Clothing)

INSERT INTO category(cid,cname) VALUES(‘c003’,’Cosmetics);

#commodity

INSERT INTO products(pid,pname,price,flag,category_id) VALUES(‘p001′,’Lenovo’, 5000,’1′,’c001′;

INSERT INTO products(pid,pname,price,flag,category_id) VALUES(‘p002′,’Haier’, 3000,’1′,’c001′;

INSERT INTO products(pid,pname,price,flag,category_id) VALUES(‘p003′,’Raytheon’, 5000,’1′,’c001′;

 

INSERT INTO products (pid,pname,price,flag,category_id) VALUES(‘p004′,’JACK JONES’,800,’1′,’c002′);

INSERT INTO products (pid,pname,price,flag,category_id) VALUES(‘p005′,’Jarvis’, 200,’1′,’c002′;

INSERT INTO products (pid,pname,price,flag,category_id) VALUES(‘p006′,’Playboy’, 440,’1′,’c002′;

INSERT INTO products (pid, pname,price,flag,category_id)VALUES(‘p007′,’Jinba’, 2000,’1′,’c002′;

 

INSERT INTO products (pid,pname,price,flag,category_id) VALUES(‘p008′,’Chanel’, 800,’1′,’c003′;

INSERT INTO products (pid,pname,price,flag,category_id) VALUES(‘p009′,’Suitable herbs’, 200,’1′,’c003′;

 

#1.Query which categories of goods are already on the shelves

#Implicit internal connection

SELECT DISTINCT c.cname FROM category c , productsp

    WHEREc.cid = p.category_id AND p.flag = ‘1’;

 

#Internal connection

SELECT DISTINCT c.cname FROM category c

    INNER JOINproducts p ON c.cid = p.category_id

    WHEREp.flag = ‘1’;

 

#2.Query the Number of All Classified Goods

#Left outer join

INSERT INTO category(cid,cname) VALUES(‘c004’,’Luxury goods);

SELECT cname,COUNT(category_id) FROM category c

     LEFT OUTER JOIN products p

        ONc.cid = p.category_id

    GROUP BYcname;

 
l  Subquery

Subquery: The result of one select statement is part of another select grammar (query conditions, query results, tables, etc.).

select ….Query field… from… table…. Where… query condition

 

#3 Subquery for details of “cosmetics” classified goods on the shelf

#Implicit internal connection

SELECT p.* FROM products p , category c

    WHEREp.category_id=c.cid AND c.cname = ‘Cosmetics’;

 

#Subquery

##As a query condition

SELECT * FROM products p

    WHEREp.category_id =

    (

        SELECTc.cid FROM category c

            WHEREc.cname=’Cosmetics’

    );

    ##As another table

    SELECT * FROM products p ,

            (SELECT * FROM category WHERE cname=’Cosmetics’)c

        WHERE p.category_id = c.cid;    

 

#Enquire about the details of cosmetics and household appliances on the shelves

SELECT * FROM products p

    WHEREp.category_id in

    (

        SELECTc.cid FROM category c

            WHEREc.cname=’Cosmetics’or c.name=’household appliances’
)

Link of this Article: Mysql DQL Language Operation

Leave a Reply

Your email address will not be published. Required fields are marked *