SQL statement Learning Guide

 

To make it easier for everyone to understand the SQL statements I have mentioned, this article assumes that a student achievement management database has been established. The full text is described in the case of student achievement management.

1. display the column name in the query results:

A. Use the as Keyword: Select name as 'name' from students order by age

B. Direct representation: Select name 'name' from students order by age

2. Exact search:

A. Use in to specify the range: Select * from students where native in ('hunan ', 'sichuan ')

B. Between... And: Select * from students where age between 20 and 30

C. "=": Select * from students where name = 'hill'

D. like: Select * from students where name like 'Li % '(Note that there is "%" in the query condition, it indicates that it is partially matched and there is information in it successively, that is, search for matching items starting with "Li. Therefore, to query all objects with "Li", run the following command: '% Li %'. If the second word is Li, it should be '_ Li %', '_ li', or '_ Li _'.)

E. [] match check character: Select * from courses where CNO like '[AC] %' (The Relationship Between or and "in (...) "similar, and" [] "can represent a range, such as: Select * from courses where CNO like '[A-C] % ')

3. Processing Time type variables

A. smalldatetime: Process strings directly, for example:

Select * from students where birth> = '2017-1-1 1' and birth <= '2017-12-31'

4. Set Functions

A. Count () summation, for example: Select count (*) from students (total number of students)

B. AVG (column) calculates the mean, for example, select AVG (Mark) from grades where CNO = 'b2'

C. Max (column) and min (column), Max and min

5. Group

Used for statistics, such as the total number of group queries:

Select gender, count (SNO)

From students

Group by gender

(Check the number of male and female students)

Note: from which point of view the group is, from which column "group"

For multiple groups, you only need to list the grouping rules. For example, to query the number of male and female students in each major, the grouping rules are: grade, MnO, and Gender. Therefore, "group by grade, MnO, gender"

Select grade, MnO, gender, count (*)

From students

Group by grade, MnO, Gender

Normally, group is also used with having. For example, if a student who fails to take more than one course is queried, the student IDs are classified by student ID (SNO) as follows:

Select SnO, count (*) from grades

Where Mark <60

Group by SnO

Having count (*)> 1

6. Union

Merge query results, such:

Select * from students

Where name like 'sheet %'

Union [all]

Select * from students

Where name like 'Li %'

7. Multi-Table query

A. Internal Connection

Select G. Sno, S. Name, C. coursename

From grades g join students s on G. Sno = S. SnO

Join Courses c on G. CNO = C. CNO

(Note that aliases can be referenced)

B. External Connection

B1. left join

Select courses. CNO, max (coursename), count (SNO)

From courses left join grades on courses. CNO = grades. CNO

Group by courses. CNO

Left join feature: displays all items in all left tables, even if some of the items are not completely filled in.

The left Outer Join returns the rows that exist in the left table but do not exist in the right table, plus the rows of the inner join.

B2. right join

Similar to left join

B3. full connection

Select SnO, name, Major

From students full join majors on students. MnO = majors. MnO

Show all content in both tables

C. Self-connection

Select c1.cno, c1.coursename, c1.pno, c2.coursename

From courses C1, courses C2 where c1.pno = c2.cno

Use aliases to solve the problem.

D. Cross join

Select lastname + firstname from lastname cross join firstanme

Equivalent to playing the flute Product

8. nested Query

A. Use the keyword in, for example, to query the hometown of Li Shan:

Select * from students

Where native in (Select native from students where name = 'hill ')

B. Use the keyword exist. For example, the following two sentences are equivalent:

Select * from students

Where SnO IN (select SnO from grades where CNO = 'b2 ')

Select * from students where exists

(Select * from grades where

Grades. Sno = students. SnO and CNO = 'b2 ')

9. sorting order

A. There are two methods for sorting order: ASC Ascending Order and desc descending order.

B. sorting order can be arranged according to a certain item in the query condition, and this can be represented by numbers, such:

Select SnO, count (*), AVG (Mark) from grades

Group by SnO

Having AVG (Mark)> 85

Order by 3

10. Others

A. names with spaces should be enclosed.

B. You can use null to determine specific queries without data in a column, such as select SnO and courseno from grades where Mark is null.

C. Differentiate the differences between any and all used in nested queries. Any is equivalent to the logical operation "|", while all is equivalent to the logical operation "&".

D. Be careful when querying negative meanings:

For example, students who have not taken the 'b2' course:

Select students .*

From students, grades

Where students. Sno = grades. SnO

And grades. CNO <> 'b2'

The preceding query method is incorrect. For the correct method, see the following:

Select * from students

Where not exists (select * from grades

Where grades. Sno = students. SnO and CNO = 'b2 ')

11. Solution to multiple difficult nested queries:

For example, students who take all courses:

Select *

From students

Where not exists (select *

From courses

Where not exists

(Select *

From grades

Where SnO = students. SnO

And CNO = courses. CNO ))

 

 

On the basis of my predecessors, I have re-edited and sorted out the excellent SQL statements, and strive to be short and easy to learn. I hope you can learn SQL statements better. If you have any questions, read related books. The following statements are MSSQL statements and cannot be used in access.

Note: collect from the network, in order to facilitate the query, I will send this article in my website http://www.ekuaiji.com/articleDetail.asp? Id = 529, updated at any time.

Thank you for posting!

 

SQL classification:

DDL-Data Definition Language (create, alter, drop, declare)

DML-data manipulation language (select, delete, update, insert)

DCL-Data Control Language (Grant, revoke, commit, rollback)

 

First, we will briefly introduce the basic statements:

1. Description: Create a database

Create Database database-name

2. Description: delete a database.

Drop database dbname

3. Description: Back up SQL Server

--- Create a device for the backup data

Use master

Exec sp_addumpdevice 'disk', 'testback', 'c:/mssql7backup/mynwind_1.dat'

--- Start backup

Backup database pubs to testback

4. Description: Create a new table.

Create Table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)

Create a new table based on an existing table:

A: Create Table tab_new like tab_old (use the old table to create a new table)

B: Create Table tab_new as select col1, col2... From tab_old definition only

5. Description: Delete the new table drop table tabname

6. Description: Add a column.

Alter table tabname add column Col type

Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.

7. Description: Add a primary key: alter table tabname add primary key (COL)

Delete a primary key: alter table tabname drop primary key (COL)

8. Description: Create an index: Create [unique] index idxname on tabname (COL ....)

Delete index: drop index idxname

Note: The index cannot be changed. To change the index, you must delete it and recreate it.

9. Description: Create view viewname as select statement

Delete view: Drop view viewname

10. Description: several simple basic SQL statements

Select: Select * From Table1 where range

Insert: insert into Table1 (field1, field2) values (value1, value2)

Delete: delete from Table1 where range

Update: Update Table1 set field1 = value1 where range

Search: Select * From Table1 where field1 like '% value1 %' --- the like syntax is very subtle, query information!

Sort: Select * From Table1 order by field1, field2 [DESC]

Total: Select count (*) as totalcount from Table1

Sum: Select sum (field1) as sumvalue from Table1

Average: Select AVG (field1) as avgvalue from Table1

MAX: Select max (field1) as maxvalue from Table1

Min: select Min (field1) as minvalue from Table1

11. Description: several advanced query Operators

A: Union operator

The Union operator combines two other result tables (such as Table1 and table2) and removes any duplicate rows from the table to generate a result table. When all is used together with Union (that is, Union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either Table1 or table2.

B: Random t operator

The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When all is used with distinct T (distinct t all), duplicate rows are not eliminated.

C: intersect Operator

The Intersect operator derives a result table by only including the rows in Table1 and Table2 and eliminating all repeated rows. When all is used with intersect (intersect all), duplicate rows are not eliminated.

Note: The query results of several computation words must be consistent.

12. Note: use external connections

A. left Outer Join:

Left Outer Join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected table.

B: Right outer join:

Right Outer Join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table.

C: Full outer join:

Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.

 

 

Next, let's look at some good SQL statements.

1. Description: copy a table (only copy structure, source table name: a new table name: B) (access available)

Method 1: Select * into B from a where 1 <> 1

Method 2: Select top 0 * into B from

 

2. Description: copy a table (copy data, source table name: A target table name: B) (access available)

Insert into B (a, B, c) Select D, E, F from B;

 

3. Description: Table Copying across databases (absolute path for specific data) (access is available)

Insert into B (a, B, c) Select D, E, F from B in 'specific database' where Condition

Example:... from B in '"& server. mappath (". ") &"/data. mdb "&" 'where ..

 

4. Description: subquery (table name 1: Table A name 2: B)

Select a, B, c from a where a in (select D from B) or: select a, B, c from a where a in (1, 2, 3)

 

5. Description: displays the article, Submitter, and last reply time.

Select a. Title, A. username, B. adddate from Table A, (select max (adddate) adddate from table where table. Title = A. Title) B

 

6. Description: External join query (table name 1: Table A name 2: B)

Select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B on A. A = B. C

 

7. Description: Online View query (table name 1:)

Select * from (select a, B, c from a) t where T. A> 1;

 

8. Description: between usage. When between restricts the Data Query range, it includes the boundary value. Not between does not include

Select * From Table1 where time between time1 and time2

Select a, B, c, from Table1 where a not between value 1 and value 2

 

9. Description: How to Use in

Select * From Table1 where a [not] In ('value 1', 'value 2', 'value 4', 'value 6 ')

 

10. Description: two associated tables are used to delete information that is not in the secondary table.

Delete from Table1 where not exists (select * From Table2 where table1.field1 = table2.field1)

 

11. Notes: four table join query problems:

Select * from a left inner join B on. A = B. B right inner join C on. A = C. C inner join D on. A = D. d Where .....

 

12. Note: Five minutes ahead of schedule reminder

SQL: Select * from Schedule where datediff ('minute ', F Start Time, getdate ()> 5

 

13. Note: One SQL statement is used to handle database paging.

Select top 10 B. * From (select top 20 primary key field, sorting field from table name order by sorting field DESC) A, table name B where B. primary key field =. primary key field order by. sorting Field

 

14. Note: The first 10 records

Select top 10 * Form Table1 where range

 

15. Note: select all the information of the largest record of a corresponding to the data with the same B value in each group (similar usage can be used for the monthly ranking of the forum and Analysis of popular products each month, rank by subject score, etc .)

Select a, B, c from tablename Ta where a = (select max (a) from tablename TB where TB. B = TA. B)

 

16. Description: includes all rows in tablea but not in tableb and tablec and removes all repeated rows to derive a result table.

(Select a from tablea) Before t (select a from tableb) Before t (select a from tablec)

 

17. Description: 10 data records are randomly taken out.

Select top 10 * From tablename order by newid ()

 

18. Description: randomly selected records

Select newid ()

 

19. Note: delete duplicate records

Delete from tablename where id not in (select max (ID) from tablename group by col1, col2 ,...)

 

20. Description: Lists All table names in the database.

Select name from sysobjects where type = 'U'

 

21. Note: List all

Select name from syscolumns where id = object_id ('tablename ')

 

22. Description: lists the fields of type, vender, and PCs, which are arranged by the Type field. case can be easily selected, similar to case in select.

Select Type, sum (Case vender when 'a then PCs else 0 end), sum (Case vender when 'C' then PCs else 0 end ), sum (Case vender when 'B' then PCs else 0 end) from tablename group by type

Display result:

Type vender PCs

Computer A 1

Computer A 1

Cd B 2

Cd a 2

Mobile phone B 3

Mobile phone C 3

 

23. Description: Initialize table 1.

Truncate table Table1

 

24. Description: select a record from 10 to 15.

Select top 5 * from (select top 15 * from Table order by id asc) Table _ alias order by ID DESC

 

Reference from: