"Go" SQL Summary (i) Basic query

Original address: http://www.cnblogs.com/yank/p/3672478.html

SQL queries are simple things, but often make mistakes because of simple things. We forget the basic syntax of SQL queries when we encounter some more complex queries.

This article hopes that through the simple summary, the commonly used query method summarizes, hoped can clear in the heart.

Scenario: Student information System, including student info, teacher information, professional information, and course selection information.

--Student Information table if OBJECT_ID (n ' Students ', n ' U ') is not NULL    DROP table Students; Gocreate TABLE Students (    ID int primary key NOT NULL,    Name nvarchar (+), age    int., city    nvarchar (50),    Majorid int)--Professional information table if OBJECT_ID (n ' majors ', n ' U ') is not NULL    DROP table majors; Gocreate TABLE majors (    ID int primary key NOT NULL,    Name nvarchar (50))--Curriculum if OBJECT_ID (n ' Courses ', n ' U ') is not NULL    DROP TABLE Courses; Gocreate TABLE Courses (    ID int primary key NOT NULL,    Name nvarchar (a) NOT null) IF object_id (n ' SC ', n ' U ') is NO T NULL    DROP TABLE SC; go--Selected Timetable Create TABLE SC (    studentid int not NULL,    CourseID int not null,    score int    )

1. Basic Inquiry

Query the values of some columns from the table, which is the most basic query statement.

SELECT column name 1, column name 2 from table name

2. Where (condition)

Function: Query data according to certain conditions

Grammar:

SELECT column name 1, column name 2 from table name WHERE column Name 1 operator  value

Operator:

operator	Description
=	Equals
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
Between	Within a range
Like	Search for a pattern

Comparison operators are relatively simple and do not repeat them. About between and like, specifically take out the key

3, between

Between two values, for example, I'm checking student information from a student aged 18-20

SELECT Id,name,age from Students WHERE age between and 20

4. Like

Function: Fuzzy query. The LIKE keyword is used with a wildcard character

The main wildcard characters:

Wildcard characters	Describe
%	Override one or more characters
_	Replaces only one character
[Charlist]	Any single character of the word columns
[^charlist] Or [!charlist]	Any single character that is not in the word columns

Instance:

1) Query the student information for surname Zhang

SELECT id,name from Students WHERE Name like ' sheet% '

2) query name the last student who is "born"

SELECT id,name from Students WHERE Name like '% born '

3) Check the student information in the name containing "students"

SELECT id,name from Students WHERE Name like '% born% '

4) query name is two characters, and student information

SELECT id,name from Students WHERE Name like ' Zhang _ '

5) Check the student information of Zhang Li and surname

This can be using the OR keyword, but using wildcards is easier and more efficient

SELECT id,name from Students WHERE Name like ' [Zhang Li]% '

6) Check the student information of the surname not Zhang, Li

This can also be implemented using not-like, preferably in the following way.

SELECT id,name from Students WHERE Name like ' [^ Zhang Li]% '

Or:

SELECT id,name from Students WHERE Name like ' [! Zhang Li]% '

5, and

and combines two or more conditions in the where child statement. expression and meaning, multiple conditions are established.

1) Check the student information of the age greater than 18 and surname Zhang

SELECT id,name from Students WHERE age>18 and Name like ' sheet% '

6, or

Or you can combine two or more conditions in a where sub-statement. or relationship, which represents multiple conditions, only one can be met.

1) Check the student information of Zhang Li and surname

SELECT id,name from Students WHERE name like ' Zhang% ' OR Name like ' li% '

7, in

The in operator allows us to specify multiple values in the WHERE clause. Indicates: in which values.

1) Inquiry age is 18, 19, 20 student information

SELECT Id,name from Students WHERE age in (18,19,20)

8, not negative

Not for the negation of the condition, take the non.

1) Inquiry into the learning information of non-Zhang surname

SELECT id,name from Students WHERE Name is not a like ' sheet% '

9. Order BY (sort)

Function: Sort the result set after the query is required

Identity	Meaning	Description
Asc	Ascending	Default
DESC	Reverse

Instance:

1) Check the Student information sheet for number, name, age, and in ascending order

SELECT id,name,age from Students ORDER by age

or specify ASC

SELECT id,name,age from Students ORDER by age ASC

2) Check student information and arrange it in reverse chronological order

SELECT id,name,age from Students ORDER by age DESC

In addition to making a sort of column, you can also specify a multi-column sort, and each sort field can make a collation

Description: Priority first column sort, if the first column is the same, then follow the second column collation, and so on.

3) Check the information of students, in accordance with the reverse of the overall grade, the number in ascending order

SELECT Id,name,score from Students ORDER by score Desc,id ASC

This query meaning: first by score in reverse order, if there are more than one record score the same, and then by the ID in ascending order.

Query results, Examples:

Id	Name	Score
2	Guang Kun	98
3	Old Seven	98
1	Zhao Four	79

10, as (Alias)

You can specify aliases for column names and table names (alias)

Role: We can specify the column of the query, or the table specifies the required name, such as the name of the table is too long, with its abbreviation, in the query is often used in the connection table.

1) Change the result column to the desired name

SELECT ID as studentid,name as Studentname from Students

2) Use the alias of the table name to identify the source of the column

SELECT S.id,s.name,m.name as Majorname from Students as S left JOIN majors as MON S.majorid = m.id

3) in the aggregate function, give the total result a name

SELECT COUNT (ID) as Studentcount from Students

11, Distinct

Meaning: Different

Function: Ignores duplicate values when querying.

Grammar:

SELECT DISTINCT column name from table name

Instance:

1) Check the name of the student's city and eliminate duplication

SELECT DISTINCT City from Student

2) Distribution of query results

SELECT DISTINCT (Score), Count (ID) from Student GROUP by score

Students may be able to repeat their grades in order to get a score and the number of students who have achieved this result. The group by usage is described in more detail later.

12, Max/min

The Max function returns the maximum value in a column. NULL values are not included in the calculation.

The Min function returns the minimum value in a column. NULL values are not included in the calculation.

MIN and MAX can also be used in text columns to get the highest or lowest values in alphabetical order.

1) Check the highest score in the student

SELECT MAX (score) from Students

2) Check the minimum age of Students

SELECT MIN from Students

13. SUM

Query the aggregate value of a column.

1) The total scores of the students with ID 1001 are queried

SC is the student's score table, field: Studentid,courseid,score.

14. AVG

Avg function returns the average of a numeric column

1) Check the average age of students

SELECT AVG (age) as Ageaverage from Students

2) The average score for course ID C001

SELECT AVG (Score) from SC WHERE courseid= ' C001 '

15. COUNT

The count () function returns the number of rows that match the specified criteria.

1) Query The total number of students

SELECT COUNT (ID) from Students

2) Check the total age distribution of students

SELECT COUNT (DISTINCT age) from Students

3) Query the total number of males

SELECT COUNT (ID) from Students WHERE sex= ' man '

4) Find out how many men and women are born.

SELECT Sex,count (ID) from Students GROUP by Sex

16. GROUP by

The GROUP BY statement is used to combine aggregate functions to group result sets based on one or more columns.

1) Query the distribution of male and female students, the above has been given the answer.

SELECT Sex,count (ID) from Students GROUP by Sex

2) Query the city distribution of students

SELECT City,count (ID) from Students GROUP by city

3) Student's average score, query results include: Student ID, average score

SELECT Studentid,avg (Score) from SC GROUP by StudentID

4) Delete duplicate records in student information

Grouping by columns, if all columns are the same, is defined as duplicates, the group by all fields are required. Otherwise, it can be processed by the specified field.

DELETE from Students WHERE ID not in (the SELECT MAX (ID) from Students GROUP by Id,name,age,sex,city,majorid)

17. Having

The addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with the aggregate function.

Grammar:

SELECT column_name, Aggregate_function (column_name) from Table_namewhere column_name operator Valuegroup by Column_ Namehaving aggregate_function (column_name) operator value

1) The average result of the query is equal to 60 of the student ID and the average score

SELECT Studentid,avg (Score) from SC GROUP by StudentID have AVG (score) >=60

2) or with a having SQL statement, you can have a common where condition

The average score for a query is greater than or equal to 60, and the student ID equals 1 of the student's ID and average score.

SELECT Studentid,avg (Score) from SC WHERE studentid= ' 1 ' GROUP by StudentID have AVG (score) >=60

3) Check the student ID of 600 + (including 600)

SELECT StudentID from SC GROUP by StudentID have SUM (score) >=600

18. TOP

The TOP clause is used to specify the number of records to return. It is useful for big data and is often used when paging.

1) Check the information of the oldest three students

SELECT TOP 3 id,name from Students ORDER by age DESC

2) or the last question, if there is the same age how to deal with it?

Select Id,name,age from Students WHERE-age-In (select TOP 3 age from Students)

19. Case statement

Evaluates a list of conditions and returns one of several possible result expressions.
The case expression has two formats:

• A case simple expression that determines the result by comparing an expression to a simple set of expressions.
• A case search expression that determines the result by calculating a set of Boolean expressions.

Simple expression Syntax:

Case input_expression while      when_expression then result_expression [... n]      

Search-style syntax:

case where     boolean_expression then result_expression [... n]      [ELSE else_result_expression] END

1) Query The learning information, if the sex is 0 is shown as a male, if 1 is shown as a female, other display for the other.

SELECT ID, Name, Case Sex when ' 0 ' then ' Male ' when ' 1 ' then ' women ' else ' other ' END as Sexfrom Students

2) Inquiry student information, according to age statistics whether adult, greater than or equal to 18 for adulthood, less than 18 for minors

SELECT ID, Name, case if age>=18 then ' adult ' ELSE ' underage ' END as whether adult from Students

3) Statistics on the number of adult underage students

Request Results

Adult	Minor
23	6

SQL statements

SELECT sum (case if age>=18 then  1 Else 0 end) as ' adult ', SUM (case if age<18 then  1 ELSE 0 end) as ' underage ' from Students

4) row and column conversions. Statistics on the number of minors and adults in male and female students

The results are as follows:

Gender	Minor	Adult
Man	3	13
Woman	2	18

SQL statements:

SELECT case when sex=0 then ' male ' ELSE ' END as ' gender ', sum (case if age<18 then 1 ELSE 0 END) as ' underage ', sum (case when Ag E>=18 then 1 ELSE 0 END) as ' adult ' from Studentsgroup by Sex

"Go" SQL Summary (i) Basic query