Power node mysql basic video data sharing

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently a product of Oracle. MySQL is one of the most popular relational database management systems. In terms of WEB applications, MySQL is the best RDBMS (Relational Database Management System) application software.

Video playback address: http://www.php.cn/course/273.html

When studying this tutorial You need to focus on learning the knowledge points of data table query:

Five sub-sentences of mysql query

where (conditional query), having (filtering), group by (grouping), order by (sorting) , limit (limit the number of results)

1. Where commonly used operators:

        Comparison operators

                                ,   ,     ,  = (),>= ,

in(v1,v2..vn)

between v1 and v2 Between v1 and v2 (including v1, v2)

          Logical operators

            and ( ! ) Logical not

              or ( || )                                        --                                                                                                                 

#Where Price & GT; = 3000 and Price & LT; = 5000 or Price & GT; = 500 AND Price & LT; = 1000

# Take 500-1000 or 3000-5000

#Ser Price Not Between 3000 and 5000

## 不 不 ”

#                                                                                                                                                                                                                                    where goods_name like 'Nokia N__'

# 2, Group by group

Generally, it needs to be used together with the statistical function (aggregation function). ,goods_name,cat_id,max(shop_price) from goods group by cat_id;

                                                                                                                                                                                                             The good_name in the result taken here is wrong! Because shop_price uses the max function, it takes the largest value, and group by is used in the statement, so goods_name does not use an aggregate function. It is just the first product under cat_id and will not change because shop_price changes.

                                                                                                                                                                                                                          but but          Here will be taken out the maximum The value of the price is only the value

                                                                                                                                            ’ it t       ’ ’ ’ ’ ’ it 1 dle it t t t t t t t #Detect Product number with the highest price (2)min: Find the minimum value

(3) SUM: Find the total number and

##Seek the sum of the commodity inventory

Select Sum (GOODS_NUMBER) from goods;

## (4) AVG: Find average value

#Find the average price of the product in each column

SELECT CAT_ID, AVG (GOODS_PRICE) from Goods Group by Cat_id;

#                                                                                                                                                                               It is understood as a variable as a variable.

Query of the payment of the backlog under each column

SELECT CAT_ID, SUM (GOODS_PRICE*Gods_number) from goods group by cat_id;

# #You can use as to give an alias to the calculation result

## You can also use an alias

3. Similarities and differences between having and where

Having is similar to where, you can filter data, and the expression after where How to write, how to write after having after having

WHERE, in order to play a role in the list, query data

# Having play a role in the columns in the query result, screen data

# Check how much the price of our goods is lower than the market price, and output the goods that are more than 200 yuan lower than the market price.

You cannot use where because s is the query result, and where can only filter the field names in the table.

200;

##                                                                                                   #                                                                                                                                               #Query the column with a backlog of goods exceeding 20,000 yuan and the backlog of goods in that column

                 

The average score of students with two or more subjects in the two or more subjects

:

#first calculate the average score of all students

Select name,avg(score) as pj from stu group by name;

                                                                                                                                                                         

SELECT NAME, SCORE & LT; 60 from Stu;

here score & lt; 60 is a judgment statement, so the result is true or false. Students with two and more than two doors

# SELECT NAME, SUM (Score & LT; 60) AS GK FROM Stu Group by name having gk & gt; 1;

##comprehensive results

SELECT NAME, SUM (score & lt; 60) as gk, avg (score) as pj from stu group by name having gk & gt; 1;

# 4, order by

## ( 1) order by price //Default ascending order

                                                                                  using order with price                                              in ascending order

                             .  .                             using order with price ’                             out out.

# (4) Order by Rand () // Randomly arranged, the efficiency is not high

#is arranged according to the sequence of the column number, the price of the product under each column is arranged

Select * from goods where cat_id!=2 order by cat_id,price desc; Writing is equivalent to limit 0,N

N Goods_price from Goods Order by Good_price Desc Limit 3,3;

qia query the most expensive product under each column

idea:

# #First of the price of the product under each column

SELECT CAT_ID, Goods_id, Goods_name, Shop_prom Goods Order by Cat_id, Shop_price Desc;

The product in the first row is the most expensive product

                    #Understand the above query result as a temporary table [existing in memory] [Subquery]

                                                                                                                   Out of the most expensive products in each column

SELECT * From (Select Goods_ids, Goods_name, CAT_ID, Shop_prom Goods Order by Cat_id, Shop_Price Desc) as t group by cat_id;

#Use here here Group by cat_id is because the first product in each column in the temporary table is the most expensive product, and no aggregate function is used before group by, so the first row of data in each group is taken by default, here grouped by cat_id

                                                                                                                                                             (column), understood as a variable, can perform operations (arithmetic operations and logical operations)

          3. The retrieved results can be understood as a temporary table

The above is the detailed content of Power node mysql basic video data sharing. For more information, please follow other related articles on the PHP Chinese website!