Basic review of MySQL and JDBC (ii)

This article will be followed by the previous article about MySQL query function!

The following will be a product case, the table is product. Pid,pname,price,pdate built in.

　　Query operation syntax: SELECT [DISTINCT] *| Column name, column name from table name [where condition];

　　4.1 Simple Query

　　　　1. Check All Products: SELECT * from Product;

2. Check the product name and price: select Pname,price from Product;

3. Check all product information Use table aliases (easy to query when multiple tables): SELECT * from product as p;

4. Query the product name, using column aliases: Select PName as P from product; (as can be omitted)

5. Remove duplicate values (by price): SELECT DISTINCT (prices) from product;

6. Display all commodity prices +10: select pname,price+10 from Product;

　　4.2 Item Query

　　　　　1. Check the product information for XX: SELECT * from product where pname = "XX";

2. Check all product information for commodity prices greater than $60: SELECT * FROM product where price>60;

3. Check the product name contains the "X" word of the product information (fuzzy query): SELECT * from the product where pname like '%X% ';

4. Check all product information in the scope of the Product ID (3,6,9): SELECT * from product where PID in (3,6,9);

5. Check the product name contains the "X" word and the ID 6 of the product information: SELECT * from product where pname like '%X% ' and PID = 6;

6. Query the product information with ID 2 or ID 6: SELECT * FROM product where PID = 2 or PID = 6;

　　4.3 sort

　　　　　　1. Check all items, sort by price (L, s): SELECT * from Product order by value Asc/desc;

2. The query name has an "X" of commodity information and is sorted in descending order of price: SELECT * FROM product where pname like '%X% ' ORDER by value Desc;

　4.4 Aggregation

Commonly used aggregate functions: SUM () sum, avg () average, Max () max () min () minimum, count () technology;

NOTE: Aggregate functions do not count null values!

1. Obtain the sum of the prices of all goods: select SUM (price) from product;

2. Average price for the item: select Avg. from Product;

3. Number of items obtained: SELECT COUNT (*) from product;

　　4.5 Grouping

Preparation: Add classification id:alter Table product add CID varchar (32);

Initialize data: Update product Set cid = ' 1 ';

Update product Set cid = ' 2 ' where PID in (5,6,7);

1. According to the CID field grouping, the number of items to be counted after grouping: select Cid,count (*) from the product group by CID;

2. According to the CID group, the average price of each group of goods, flat and average price is greater than 2000 yuan: select Avg. from product GROUP BY CID have AVG (prices) >2000;

　　4.6 Query Summary

Order: Select (the field that is typically followed by the query)-from (Table of Inquiry)-Where-group by-having (after grouping with conditions can only be used)-order by (must be put last)

　　MySQL's basic summary to this end, the advanced content will be specifically opened after the topic to write! The basic summary of the JDBC Chapter begins here!

　　　　　　　　　　　　　　

Basic review of MySQL and JDBC (ii)