SQL Learning Summary (3)--sql single-Table query technology

Basic format:

SELECT [All | distinct] select_list

From Table_list/view_list

[WHERE conditions]

[GROUP BY Group_list]

[Having conditions]

[ORDER by Order_list]

Cases:

Select employee number, name, salary as month salary, (salary *12+5000)/6 as year bonus from staff

Select *from staff where warehouse number in (' Wh1 ', ' wh2 ') and sex! = ' women ' and wages >=1300 and wages <=2100

3.1 several special SQL Enquiry

3.1.1 Fuzzy Query

Wildcard characters	Meaning
%	Contains 0 or more characters
_ (Underline)	Contains a single character
[]	Specify a range (such as [a~z]) for any single character
[^]	Any single character that does not belong to a specified range (such as [^a~z])

--show the number of the wages of the hundred is not between 2~6, the name contains "flat" word, and warehouse number in WH1 and WH3 staff information:

SELECT * from staff where salary like ' _[^2-6]% ' and name like '% flat% ' and warehouse number in (' Wh1 ', ' wh3 ')

3.1.2 null value and restricted range query

SELECT * from staff where wages between and 1800 and sex are NOT null and warehouse number in (' Wh1 ', ' wh2 ')

3.1.3 Show partial record query

Select top [percent] from employee where wage >1500

3.1.4 Save Query

Select employee number, name, salary, salary/30 as day salary, salary quarter salary, salary *12 as year salary into table with calculated field from employee where wage <=1900

3.2 single-table nested queries

SELECT * from warehouse where warehouse number! = ' wh1 ' and area >= (select area from warehouse where warehouse number = ' WH1 ')

3.3 Statistical function Query

Statistical functions	Meaning
COUNT (*)	Count the number of records selected
Count ()	Count the number of values in a specific column
SUM ()	Calculate sum (must be numeric field)
AVG ()	Calculate average (must be a numeric field)
Max ()	Determining the maximum Value
Min ()	Determining the Minimum value

Select Staff. *, (select AVG (Salary) from employee) as average salary, wages-(select AVG (wages) from employee) as to the difference from the average salary from the employee where ABS (wage-(select AVG (payroll) F ROM staff)) >200

3.4 Sort and Compute by Enquiry

Select Top 3 * from Employee ORDER by payroll ASC

SELECT * FROM Employee ORDER BY salary DESC, employee ID ASC

--Show employees ' information and employee information with salary greater than 2000

SELECT * FROM employee where wage >2000

Compute AVG (Salary), Max (Salary), min (Salary), sum (Salary)

--Show employee information and employee summary information in different warehouses

SELECT * FROM Employee ORDER by warehouse number

Compute AVG (Salary), Max (Salary), min (Salary), sum (payroll) by warehouse number

3.5 Group Queries

Grouped query Keywords: GROUP BY, if the condition before grouping is used where, if the condition after grouping is used having.

Show the average wage, wage and maximum wage, minimum wage, wage number information for different warehouses with wages greater than ZG1 workers ' wages:

Select warehouse number, max (wages) as the maximum wage, min (wages) as minimum wage, sum (wages) as the sum of Wages, AVG (wages) as average wage, COUNT (*) as the number of employees from staff where wages > (Select Wages From employee where employee number = ' zg1 ') Group by warehouse number

Displays the average wage, wage and maximum wage, minimum wage, wage number information for different warehouses, but requires that the average salary for the warehouse is greater than 1650:

Select warehouse number, max (wages) as the maximum wage, min (wages) as minimum wage, sum (wages) as the sum of Wages, AVG (wages) as average wage, COUNT (*) as the number of employees from the employee group by warehouse number having AVG (Salary) >1650

Shows the difference between the maximum wage and the minimum wage in the warehouse of the employee the employee information between 700~800:

SELECT * from staff where warehouse number in (

Select warehouse number from employee GROUP by warehouse number

Having Max (salary)-min (salary) between and 800)

Show the maximum order information for each employee's order Amount:

Select a.* from Order a WHERE amount = (

Select Max (amount) from order form where employee number =A. Employee number)

Note: this should be done to find out the maximum value of each employee's order amount, along with the information of the employee's corresponding order.

Many people do this:

SELECT * from order form where amount in (select Max (amount) from order Form group by employee number)

This practice is wrong.

3.6 quantifier Query

Commonly used quantifiers any, all, and some, where any and some are synonyms, as long as the subquery has a true, true, and all of the behavior in the subquery is true.

SELECT * FROM employee where wage >=any (select salary from employee where warehouse number = ' WH2 ')

SELECT * FROM employee where wage >=all (select salary from employee where warehouse number = ' WH1 ')

SQL Learning Summary (3)--sql single-Table query technology