Sql-t-sql statements

sql-t-sql Statements
a basic use
1, greater than, less than query
Use Ltsdatabase
Select student number, student age from Student where student number <2

2, greater than or equal, less than or equal to
Use Ltsdatabase
Select *from website Staff table where staff wages > or staff wages = 5000

3,and:
Use Ltsdatabase
SELECT * from website staff table where age =19 and graduate school = ' Nchu '

4,!
Use Ltsdatabase
SELECT * from website staff table where staff wages!>6000 not greater than

5,not
Use Ltsdatabase
SELECT * from website staff table where not staff salary >6000 not greater than

6, priority order from high to Low:
Brackets
Not positive or negative
* /
Add, Subtract
Comparison operators
and
Or

Use Ltsdatabase
SELECT * from website staff table where Graduate school = ' Nchu ' and age <23

7, continuous use and
Use Ltsdatabase
SELECT * from website staff table where age =20 and staff name = ' Orange 3 '
Both satisfy the return value to True

8, continuous use or
Use Ltsdatabase
SELECT * from website staff table where age =20 or clerk name = ' Orange 3 '
Age can be met, that is, the previous condition satisfies the return value is true, the previous one does not meet after the check a condition

9,in Restriction conditions
Use Ltsdatabase
SELECT * from website staff table where age in (18, 19, 20)
Enquiry Age at 18 19 20

No in limit conditions
Use Ltsdatabase
SELECT * from website staff table where not age in (18, 19, 20)
Query age not at 18 19 20

11, query is Null,null must lowercase
Use Ltsdatabase
SELECT * From site staff table where staff name is null
Use Ltsdatabase

12, query is NOT NULL
SELECT * From site staff table where not clerk name is null

Two advanced query
13,between and
Use Ltsdatabase
SELECT * from website staff table where staff wages between and 7000

14,not between and
Use Ltsdatabase
SELECT * from website staff table where staff wages not between and 7000

15, eliminate duplicate records
Use Ltsdatabase
Select distinct staff salary, graduation college, age from website staff table
Elimination of staff salaries, graduation schools, the same age record

16, projection Query
Use Ltsdatabase
Select staff salary, graduation college, age from website staff table
Results according to the salary of the staff, the graduation school, the age to sort

17, the value of the other field with the same value as the value of a field, nested subqueries
Use Ltsdatabase
SELECT * From site staff table where staff wages in (select staff pay from site staff table where clerk name = ' Orange ')
Query the Employee Payroll field for all field values with the same orange
Note: In can also be swapped for = and other arithmetic symbols, but cannot be changed to = number when the subquery returns more than one value

18, multi-table nested query
Create a table with a primary key that represents a seed and an indicator increment
Use Ltsdatabase
CREATE TABLE website Management Project
(
Item number int identity (1, 1) primary key,
Indicates seed is 1, increment is 1, item number is primary key
Project name varchar (50),
In charge of varchar (50),
Co-operative Unit varchar (50)
)

19, nested query
Use Ltsdatabase
Select *from Site Staff table where staff name in (select distinct responsible from website Business Project)
Remove duplicate data (distinct) from people in the site staff table who are in charge of the site's management project

Use Ltsdatabase
Select *from website staff table where staff name not in (select distinct responsible from website Business Project)
Remove duplicate data from people in the site staff table who are not in charge of the site's Business Project (DISTINCT)

20, query single-level sorting
Use Ltsdatabase
SELECT * from website staff table where staff name in (select owner from website operating Item table 2) Order by staff salary ASC
The results of the query are arranged in ascending order of staff wages, small in front, large in the rear

Use Ltsdatabase
SELECT * from website staff table where staff name in (select owner from website operating Item table 2) Order by staff salary DESC
The results of the query are arranged according to the clerk's salary, big in front, small in the rear

Use Ltsdatabase
SELECT * From site staff table where staff name in (select owner from website operating Item table 2)
The non-sorting criteria are sorted in ascending order

21, Multilevel sorting
Use Ltsdatabase
SELECT * from website staff form ORDER by age ASC, staff salary ASC
When the age is the same, sort by the employee's salary and set the rules for sorting

use of three functions
22, query the number of rows, use the statistics function count
Use Ltsdatabase
Select COUNT (*) as record number of rows from site staff table
The queried column name defaults to a no-list and can be specified with as as the number of record rows
* Represents all fields

23, query the number of each column
Use Ltsdatabase
Select COUNT (age) as record number of rows from site staff table
Replace * with the specified field name you want to query

24, statistical function sum sum
Use Ltsdatabase
Select SUM (Staff salary) as total amount from website staff table

25, averaging Avg
Use Ltsdatabase
Select AVG (Staff salary) as average salary from website staff table

26, Maximum Max
Use Ltsdatabase
Select MAX (Staff salary) as average salary from website staff table

27, Min value min
Use Ltsdatabase
Select min (Staff salary) as average salary from website staff table

28, statistical values as query criteria
Use Ltsdatabase
SELECT * from website staff table where staff salary > (select AVG (Staff salary) from website staff table)

29, combination Query
Use Ltsdatabase
Select employee number, employee name, staff salary + employee bonus as total income from website staff table

30,like Fuzzy Query
Use Ltsdatabase
SELECT * from website staff table where graduate schools like ' c% '
The location of the queried word represents the query where
c% represents a query that starts with a C record
%c% representative queries for records containing C
%c represents querying for records ending in C

31, group Query Rollup query
Use Ltsdatabase
SELECT * from website staff form ORDER BY age compute max (Staff salary), MIN (Employee's salary), SUM (employee's salary), AVG (Employee's salary) by age
Max (employee's salary), MIN (Employee's salary), SUM (employee's salary), AVG (Employee's salary) summary information
Order BY age Summary Condition
By age sort field, which must appear in the summary condition

32, Group query
Use Ltsdatabase
Select Graduate School, MAX (employee's salary) as highest wage, MIN (employee's salary) as minimum wage, AVG (employee's salary) as average salary from website staff Table GROUP by graduated college
In accordance with the Graduate Schools group

33, grouping conditions
Use Ltsdatabase
Select Graduate School, MAX (staff salary) as highest wage, MIN (employee's salary) as minimum wage, AVG (employee's salary) as average salary from website staff Table group by graduate School having SUM (staff salary) > 7500
Having SUM (clerk's salary) > 7500 filter criteria for grouping

34, grouping nested queries
Use Ltsdatabase
SELECT * from website staff table where graduate school in (select graduated from website Staff Table group by graduate School having SUM (staff salary) > 20000)

35, predicate query
The condition after (in, not) conforms to the previous record, querying only the records of the compliant table
(exist, not exist) query all records of a table as long as the following query conditions conform to the previous record
Use Ltsdatabase
SELECT * From site staff table where not EXISTS (SELECT * from Web site staff table where Graduate school = ' Tsinghua ')

Use Ltsdatabase
SELECT * From site staff table where exists (SELECT * from site staff table where Graduate school = ' Tsinghua ')

36, quantifier query any
Use Ltsdatabase
SELECT * From site staff table where staff wages > any (select staff pay from site staff table where clerk name = ' Orange ')
Query any record that is larger than the orange (other fields, there may be multiple values) for any one of the wages, greater than one of which meets the criteria
The value of the specified field of the query must be greater than any of the values in the query's collection

37, quantifier query all
Use Ltsdatabase
SELECT * From site staff table where staff wages > all (select Staff pay from site staff table where Clerk name = ' Xiaoming ')
The value of the specified field of the query must be greater than all the values in the query's collection

38, the query specifies how many records
Use Ltsdatabase
Select top percent * from website staff table
Enquiry 10%

Use Ltsdatabase
Select Top * from website staff table
Query Top 10 Records

39, query saved to other table
Use Ltsdatabase
SELECT * into second site staff table from site staff table where staff wages > 7000

40, and set query
Use Ltsdatabase
SELECT * from Website staff Table Union SELECT * FROM second website staff table

41, intersection query
Use Ltsdatabase
SELECT * from website staff table Interset SELECT * from second website staff table

42, Connection Query
Use Ltsdatabase
SELECT * from website staff table, website management project
Each record in the subsequent table is connected to all records of the previous table, respectively

43, conditional connection
Use Ltsdatabase
SELECT * from website staff table, website Business Project where site staff table. Employee name = person in charge

44, Hyperlink Query
Use Ltsdatabase
SELECT * FROM website Business Project INNER JOIN website staff table on the website Business Project. Person in charge = Website Staff table. Employee Name

45, left Connection
Use Ltsdatabase
SELECT * from website operating items table 2 left JOIN website staff table on the Website Management Project table 2. Person in charge = Website Staff table. Staff name
The table on the left shows all, the right side has no corresponding field value is null

45, right Connection
Use Ltsdatabase
SELECT * from website operating items table 2 Right Join website staff table on website operating Item table 2. person in charge = Website Staff table. Staff name
The table on the right shows all, the left side has no corresponding field value is null

46, fully connected
Use Ltsdatabase
SELECT * from the Site Management Table 2 full join website staff table on the Website Management Project table 2. Person in charge = Website Staff table. Staff name
Both left and right show all, no relative field values are all null

Copyright NOTICE: Welcome to communicate the error of the article, must humbly accept, QQ872785786

Sql-t-sql statements