1. Remove duplicate StatementsThe DISTINCT keyword can remove duplicate rows from the results of an SQL statement. If the DISTINCT keyword is not specified , all rows are returned, including duplicate rows. When you use the DISTINCT keyword to remove duplicate records, you place the DISTINCT keyword before the first field name. The syntax format for distinct is as follows: select distinct ISBN, Barcode, title, author, publishing house from tb_xsb 2. Multi-conditional ordering of data select ISBN, title, author, quantity sold, date FROM tb_xsbGROUP BY ISBN, title, author, number of sales, Date order by ISBN asc, date desc 3. Sort statistical Results select top 5 ISBN, title, author, publisher, SUM (sales quantity) AS Total sales Quantity from tb_xsbgroup BY ISBN, title, author, publishing house order by 5 desc 4. Use the aggregate function min to find the commodity with the lowest selling price select * from tb_sell01 where pin Price in (select min (pin price) from tb_sell01) 5. To apply a subquery in an UPDATE statement update Worker's salary table set basic salary = (select basic salary from prescribed salary table where Base salary =1800) where Employee name = ' Liu Fang ' 5. Use in to override the where expression in the SQL statement to limit the scope of the query statement. The In keyword is primarily used to select rows that match any one of the values in the list. The In keyword is in the Format (column label value 1, List value 2,...), the items in the list must be separated by commas, and in parentheses, the greatest benefit of this is that the query statement can be abbreviated. The meaning of not in is exactly the result of querying the in Xiangfan code to return all records that are no longer in the list range. select * from tb_sell01 where pin price in (select pin Price FROM tb_sell01 where Sales Price>1800)
Various SQL statements
