1, Functions of SQL language

SQL function	predicate
Data definition (DDL)	CREATE,DROP,ALTER
Data query (DQL)	SELECT
Data manipulation (DML)	INSERT,UPDATE,DELETE
Data control (DCL)	GRANT,REVOKE,DENY

2, Data types supported by SQL

• SQL data types are composed of 13 basic data types, including shaping, single precision, double precision, variable length characters, fixed length characters, long type, date, etc.

1. Numerical type

data type	describe	storage
tinyint	All numbers from 0 to 255 are allowed	1 byte
smallint	All numbers from - 32768 to 32767 are allowed	2 bytes
int	All numbers from - 2147483648 to 2147483647 are allowed	4 bytes
bigint	All numbers between - 9223372036854775808 and 9223372036854775807 are allowed	8 bytes
decimal (p,s) or numeric (p,s)	A number with fixed precision and scale. It is allowed to be a number from - 10 ^ 38 + 1 to the 38th power of 10 - 1. The maximum number of digits that can be stored in the p parameter knowledge (on the left and right sides of the decimal point, p must be a value between 1 and 38, and the default is 18; the S parameter indicates the maximum number of digits stored on the right side of the decimal point, and s must be a value between 0 and p, and the default is 0.)	5-17 bytes
smallmoney	Monetary data between -214748.3648 and 214748.3647	4 bytes
money	Monetary data between -922337203685477.5808 and 922337203685477.5807.	8 bytes
float(n)	Floating precision digital data from -1.79E + 308 to 1.79E + 308; Parameter n: whether this field holds 4 bytes or 8 bytes, float (24) holds 4 bytes and float (53) holds 8 bytes. The default value of n is 53	4 or 8 bytes
real	Floating precision digital data from 3.40E + 38 to 3.40E + 38.	4 bytes

2. Date time type

data type	describe	storage
datetime	From 1753.1.1 to 9999.12.31, the accuracy is 3.33ms	8 bytes
datetime2	From 1753.1.1 to 9999.12.31, the accuracy is 100ns	6-8 bytes
samlldatetime	From 1900.1.1 to 2079.6.6, the accuracy is 1min	4 bytes
date	Store dates only, from 0001.1.1 to 9999.12.31	3 bytes
time	Storage time only, accuracy of 100ns	3 ~ 5 bytes
datetimeoffset	Same as datetime2, plus time zone	8 ~ 10 bytes
timestamp	It stores a unique number. Whenever a row is created or modified, the number will be updated. Timestamp is based on the internal clock and does not correspond to the real time. Each table can only have one timestamp variable.

3. String type

data type	describe	storage
char(n)	Fixed length string, up to 8000 characters	n bytes
varchar(n)	Variable length string, up to 8000 characters	Determined by actual length
varchar(max)	Variable length string, up to 1073741824 characters	Determined by actual length
text	Variable length string, although more than 2GB of character data	Determined by actual length
nchar(n)	Fixed length Unicode data, up to 4000 characters	n bytes
nvarchar(n)	Variable length Unicode data, up to 4000 characters	Determined by actual length
nvarchar(max)	Variable length Unicode data, up to 536870912 characters	Determined by actual length
ntext	Variable length Unicode data, up to 2GB character data	Determined by actual length

4. Binary string

data type	describe	storage
bit	0, 1, or NULL allowed
binary(n)	Fixed length binary data, up to 8000B	n B
varbinary(n)	Variable length binary data, up to 8000B	Determined by actual length
varbinary(max)	Variable length binary data, up to 2GB	Determined by actual length
image	Variable length binary data, up to 2GB	Determined by actual length

3, Database definition

1. Database creation

Syntax format:

CREATE DATABASE NAME
 	[
    [PRIMARY]<Document description>[,.....n]
    [,FILEGROUP Filegroup name[,.....n]]
	]
	[
	[LOG ON
   <Document description>[,.....n]]
	]

• Database name: the name of the database created in SQL Server
• ON: indicates the clear definition of primary data file, secondary data file and file group.
• PRIMARY: this parameter is used to specify that subsequent PRIMARY data files (the default first file) and secondary data files (multiple) are created in the PRIMARY file group.
• FILEGROUP: this parameter is used to indicate the secondary FILEGROUP to be created, and then specify the secondary data files (multiple) to be created in it.
• LOG ON: indicates the explicit definition of the transaction log file. If there is no such definition, the system will automatically create a transaction log file with an initial size of about 30% of all data files.

Case: ① create Library database "Library". ② The logical name of the main data file is "Library"; ③ the physical file name is "D: \ Library management data \ Library.mdf"; ④ the logical name of the transaction log file of the database is "Library_log"; ⑤ the physical file name is "E: \ Library management log \ Library_log.ldf"; ⑥ the initial size is 1MB, the maximum capacity is 600MB, and the growth rate is 10%.

CREATE DATABASE Library
	 ON PRIMARY
	 		(
	 		NAME = Library,
	 		FILENAME = 'D:\Library management data\Library.mdf',
	 		SIZE = 5,
	 		MAXSIZE = 20GB,
	 		FILEGROWTH = 10%
			)
	 LOG ON
	 		(
			NAME = Library_log,
			FILENAME = 'E:\Library management log\Library_log.ldf',
			SIZE = 1,
			MAXSIZE = 600,
			FILEGROWTH = 10%
			)

2. Database modification

Syntax format:

ALTER DATABASE 	Database name	
	{MODIFY NAME = New data file name
	|ADD FILE <Document description>[,....n]
	|ADD LOG FILE <Document description>[,....n]
	|REMOVE FILE Logical name
	|MODIFY FILE <Document description>
	|ADD FILEGROUOP Filegroup name
	|REMOVE FILEGROUP Filegroup name
	|MODIFY FILEGROUP Filegroup name...
	}

<Document description>
(
	NAME = Logical name
	[,NEWNAME = New logical name]
	[,FILENAME = 'Physical file name']
	[,SIZE = initial size[KB|MB|GB|TB]]
	[,MAXSZIE = {Maximum size[KB|MB|GB|TB]|UNLIMITED}]
	[,FILEGROWTH = Automatic growth[KB|MB|GB|TB|%]]
)

Case: ① modify the database "Library" in the Library. ② Add the file group Library_Group. ③ Add the secondary data file "Library_data2" to the "Library_Group" filegroup ④ add the transaction log file "Library_log2". ⑤ Modify the maximum size of the secondary data file "Library_data2" to 20GB, and the automatic growth is 10%. ⑥ Delete the secondary data file "Library_data2" and the transaction log file "Library_log2". ⑦ Delete the secondary filegroup Library_Group.

//Add file group Library_Group
ALTER DATABASE Library
 ADD FILEGROUP Library_Group

//Add the secondary data file Library_data2 to the Library_Group file group
ALTER DATABASE Library
 ADD FILE
		(NAME = Library_data2,FILENAME = 'D:\Library management data\Library_data2.ndf')
		TO FILEGROUP Library_GROUP

//Add transaction log file "Library_log2"
ALTER DATABASE Library
 ADD LOG FILE
 	(NAME = Library_log2,FILENAME='E:\Library management data\Library_log2.ldf',SIZE=5MB,MAXSIZE=300MB,FILEGROWTH=5MB)

//Modify the maximum size of the secondary data file "Library_data2" to 20GB, and the automatic growth is 10%.
ALTER DATABASE Library
 MODIFY FILE
 	(NAME = Library_data2,MAXSIZE = 20GB,FILEGROWTH = 10%)

//Delete the secondary data file "Library_data2" and the transaction log file "Library_log2".
ALTER DATABASE Library
 REMOVE FILE Library_data2
GO
ALTER DATABASE Library
 REMOVE FILE Libiary_log2

//Delete the secondary filegroup Library_Group.
ALTER DATABASE Library
 REMOVE FILEGROUP Library_Group

3. Deletion of database

Syntax format:

DROP DATABASE <Database name>[,...n]

Instance: first create a database "DB1" and then delete it

//establish
CREATE DATABASE DB1

//delete
DROP DATABASE DB1

4, Create and manage tables using SQL

Case: creation and management of library management table

Four relationship models:

ReaderType(TtpeID,Typename,LimitNum,LimitDays)
PK:TypeID
Reader(RID,Rname,TypeID,Lendnum)
PK:RID FK:TypeID
Book(BID,Bname,Authoe,Publisher,Price,LentOut)
PK:BID
Borrow(RID<BID<LendDate,ReturnDate,SReturnDate)
PK:RID+BID+LendDate FK:RID<BID

1. Create table

Function: create a new table in the database and define the name, data type and integrity constraints of each column for the table.

CREATE TABLE Table name
 (
 {<Column definition>|
 [<Table level integrity constraints>]}[,...n]
 [,...n]
 )

Description of column definition syntax:

Column name data type		--Column names and their data types
[NULL|NOT NULL]		--Non null value constraint, default to NULL
[[CONSTRAINT Constraint name]DEFAULT Constant expression]		--Default value definition, for which you can define a constraint name
|[IDENTITY(Identify seed, identify increment)]		--Identity column definition
[<Column level integrity constraints>]


//Column level integrity constraints
[CONSTRAINT Constraint name]		--Optional, which indicates the beginning of the following constraint definition and gives the constraint a name
PRIMARY KEY		--Primary key constraint
|UNIQUE		--Unique key constraint
|[FOREIGN KEY]REFERENCES Primary key table(Primary key)		--Foreign key constraint
|CHECK(Logical expression)		--Check constraints

Table level integrity constraint syntax definition:

[CONSTRAINT Constraint name]		--Indicates that the following is the beginning of the table constraint definition, and a name can be given to the constraint
PRIMARY KEY(Listing[,...n])		--Primary key constraint (combination of one or more columns)
|UNIQUE(Listing[,...n])		--Unique key constraint (combination of one or more columns)
|FOREIGN KEY(Listing[,...n])REFERENCES Primary key table(Listing[,...n])		--Foreign key constraints (one or more column combinations)
|CHECK(Logical expression) --Check constraints

Create table:

• Create reader type table "ReaderType"
• Create Reader table "Reader"
• Create Book table "Book"

//1. Create the reader type table "ReaderType"
USE Library
GO
CREATE TABLE ReaderType
 (
 TypeID int NOT NULL PRIMARY KEY,	--Reader type number, primary key
 Typename char(8) NULL,		--Reader type name
 LimitNum int NULL,		--Limited borrowing quantity
 LimitDays int NULL		--Borrowing limit days
 )

//2. Create the Reader table "Reader"
CREATE TABLE Reader
 (
 RID char(10) NOT NULL PRIMARY KEY,		--Reader number, primary key
 Rname char(8) NULL,		--Reader name
 TypeID int NULL,		--Reader type
 Lendnum int NULL,		--Borrowed quantity
 
 FOREIGN KEY(TypeID)REFERENCES ReaderType(TypeID) ON DELETE NO ACTION		--Foreign keys, do not delete cascades
 )

//3. Create a Book table "Book"
CREATE TABLE Book
 (
 BID char(13) PRIMARY KEY,
 Bname varchar(42) NULL,
 Author varchar(20) NULL,
 Publisher varchar(28) NULL,
 Price decimal(7,2) NULL CHECK(Price>0),
 LendOut bit	--s Lending
 )

//4. Create a book borrowing form "Borrow"
CREATE TABLE Borrow
 (
 RID char(10) NOT NULL FOREIGN KEY REFERENCES Reader(RID)		--Foreign key constraint
 ON UPDATE CASCADE ON DELETE CASCADE,

 BID char(13) NOT NULL FOREIGN KEY REFERENCES Book(BID)
 ON UPDATE CASCADE,

 LendDate date NOT NULL DEFAULT(GETDATE()),		--Borrowing date, default value definition
 ReturnDate date NULL,		--Return date
 SRturnDate date NULL,		--Due date

 PRIMARY KEY(RID,BID,LendDate)		--definition RID+BID+LenDate Is a primary key constraint
 )

2. Modification table

• Modify column properties
• Add columns or constraints
• Delete column or constraint

Syntax structure:

ALTER TABLE indicate
 (
 ALTER COLUMNS Listing		--Specify a column to modify
 data type [NULL|NOT NULL]		--Specify the modified data type and non null value constraints
 |ADD
  {<Column definition>|<Table level integrity constraints>}[,...n]		Specifies to add one or more column definitions or table level constraints
 |DROP
  [CONSTRAINT]Constraint name[,...n]		--Specifies to delete one or more constraints
  |COLUMN Listing[,...n]		--Specifies to delete one or more columns
 )

1. Modify data type (and vacancy constraint)
Case: change the type of "Publisher" in the "Book" reader table from "varchar(28)" to "varchar(30)".

USE Library
GO
ALTER TABLE Book
ALTER COLUMN Publisher varchar(30) NOT NULL		--Modify column definition

2. Add columns or constraints
Case: adding mail address columns and check constraints to the table "Reader"

ALTER TABLE Reader
ADD Email varchar(20) NULL CONSTRAINT Reader_Email CHECK(Email LIKE '%@%')

Case: add a check constraint (BID LIKE '% /%') for the primary key Book number "BID" of table "Book".

ALTER TABLE Book 
ADD CONSTRAINT CK_Book_BID CHECK(BID LIKE '%/%')

3. Delete columns or constraints
Case: delete the constraint and column of "Email" column in "Reader".

ALTER TABLE Reader
DROP CONSTRAINT Reader_Email,
COLUMN Email

3. Delete table

Syntax format:

DROP TABLE Table name

Case: create Table1 first and then delete it

USE Library
GO
DROP TABLE Table1

5, Use SQL operation table

1. Insert data row

• INSERT ... VALUES
• INSERT ... SELECT

1.INSERT ... VALUES
Syntax format:

INSERT [INTO] Table name|View name[(List name)]
VALUES(Constant table)

Insert all values in a column
Case: inserting data rows for table "ReaderType"

USE library
GO
INSERT INTO ReaderType VALUES(1,'teacher',6,90)
INSERT INTO ReaderType VALUES(2,'staff member',4,60)
INSERT INTO ReaderType VALUES(3,'student',3,30)

Inserts the value of a row or part of a column
Case: insert some information of the Reader "Zhang Zijian" into the table "Reader", and add other data rows with the same statement.

USE library
GO
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2000186010,'Zhang Zijian',1)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2000186011,'Zhao Liangyu',1)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2003216008,'Zhang Ying',2)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2004060003,'Li Yaxi',1)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2004216010,'Ren Cancan',1)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2014216117,'Meng Xia',3)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2015216008,'Yang Shuhua',3)
INSERT INTO Reader(RID,Rname,TypeID) VALUES(2015216009,'Cheng Peng',3)

Insert data row of table "Book"

USE library
GO
INSERT INTO Book VALUES('F270.7/34','ERP Start with internal integration','Chen Qishen','Electronic Industry Press',45.00,'False')
INSERT INTO Book VALUES('F270.7/455','SAP Basic tutorial','Huang Jia','People's Posts and Telecommunications Publishing House',55.00,'True')
INSERT INTO Book VALUES('F270.7/56','ERP Integrated application of system','Kingdee software','tsinghua university press ',35.00,'False')
INSERT INTO Book VALUES('F275.3/65','SAP Complete collection of financial management','Wang Wen','tsinghua university press ',29.00,'True')
INSERT INTO Book VALUES('TP311.138/125','Database application technology','Hui Zhou','People's Posts and Telecommunications Publishing House',46.00,'True')
INSERT INTO Book VALUES('TP311.138/136','SQL Server 2008 Basic tutorial','Robin Dewson','People's Posts and Telecommunications Publishing House',55.00,'True')
INSERT INTO Book VALUES('TP311.138/230','SQL Server 2005 Basic tutorial','Robin Dewson','People's Posts and Telecommunications Publishing House',89.00,'False')
INSERT INTO Book VALUES('TP311.138/235','SQL Server 2008 From introduction to mastery','Mike Hotek','tsinghua university press ',46.00,'True')
INSERT INTO Book VALUES('TP311.138/78','Introduction to Database System ','SA Shixuan','Higher Education Press',25.00,'False')
INSERT INTO Book VALUES('TP312/429','C#Introduction classic ',' Karli Watson ',' Tsinghua University Press', 98.00,'False')

Insert data row of table "Borrow"

USE library
GO
USE library
GO
INSERT INTO Borrow VALUES(2000186010,'F270.7/455','2017-05-30','2017-07-14',NULL)
INSERT INTO Borrow VALUES(2000186011,'TP311.138/125','2017-11-26',NULL,NULL)
INSERT INTO Borrow VALUES(2004216010,'TP311.138/235','2017-11-15',NULL,NULL)
INSERT INTO Borrow VALUES(2014216117,'TP312/429','2017-04-09','2017-05-02',NULL)
INSERT INTO Borrow VALUES(2015216008,'F275.3/65','2017-10-28',NULL,NULL)
INSERT INTO Borrow VALUES(2015216009,'F270.7/455','2017-11-17',NULL,NULL)
INSERT INTO Borrow VALUES(2015216009,'TP311.138/136','2017-10-30',NULL,NULL)

2. INSERT ... SELECT
Syntax structure:

INSERT Table name SELECT Subquery

• The SELECT query clause is used to specify the source table and its data, generate the result set through the SELECT query, and INSERT it into the specified table after INSERT.
• This statement can insert multiple rows of data from other data sources into an existing table. You must ensure that the data type of the column in the table that accepts the new value is consistent with the data type of the corresponding column in the source table.

Case: insert the books of the people's Posts and Telecommunications Publishing House in the Book department into the new BookPostTel table

//1. Create BookPostTel table
USE library
GO
CREATE TABLE BookPostTel
(
BID char(13) PRIMARY KEY NOT NULL,
Bname varchar(42) NULL,
Author varchar(20) NULL,
Publisher varchar(30) NULL,
Price decimal(7,2) NULL CHECK (Price>0)
)

//2. Insert the books of the people's Posts and Telecommunications Publishing House in the Book department into the new BookPostTel table
USE library
GO
INSERT BookPostTel
SELECT BID,Bname,Author,Publisher,Price FROM Book
WHERE Publisher='People's Posts and Telecommunications Publishing House'

2. Update data row

UPDATE ... SET

Syntax structure:

UPDATE Table name
SET <Listing=expression>[,...n]
[WHERE Logical expression]

Function:

• For the table specified by UPDATE, when the conditions after the WHERE clause are met (the logical expression is true), the SET clause assigns the value of the expression after the "=" sign to the specified column name.

Case: increase the original borrowing limit of 3 volumes of "Reader Type" middle school students by 2 volumes, and increase the original borrowing limit of 30 days by 5 days.

USE Library
GO
UPDATE ReaderType
SET LimitNum=LimitNum+2,LimitDays=LimitDays+5
WHERE Typename='student'

Case: increase the borrowing limit of all data rows in the Reader Type table by 10 days.

UPDATE ReaderType
SET LimitDays = LimitDays + 10

3. Delete data line

Case: delete the book information of author Cheng Gang in the "BookPostTel" table.

USE Library
DELETE BookPostTel WHERE Author='Cheng Gang'

Case: delete all book information in the table "BookPostTel".

USE Library
DELETE BookPostTel

Creating indexes using SQL

CREATE INDEX ON

Syntax structure:

CREATE [UNIQUE][CLUSTERED|NONCLUSTERED]
INDEX Index name
ON <indicate|View name> (Listing[ASC|DESC][,...n])

Function:

• Creates a unique, clustered, or nonclustered index for the specified table or view based on the specified column (index key), ascending (ASC), and descending (DESC).

Case: in order to improve the speed of searching books by Book name, create a non unique and non clustered index "Bookindex" for the Book table "Book" in the database "Library", with the index key "Bname" and arranged in ascending order.

USE Library
GO
CREATE NONCLUSTERED
INDEX Bookindex
ON Book(Bname ASC)

Delete indexes using SQL

DROP INDEX
Syntax structure:

DROP INDEX indicate.Index name

Case: delete the index "Bookindex" of the table "Book".

USE Library
GO
DROP INDEX Book.Bookindex

6, SELECT query statement

INSERT INTO Student VALUES(2014216001,'Zhao Chenggang','male','1995-05-05','Computer application technology',405.0)
INSERT INTO Student VALUES(2014216002,'Li Jing','female','1995-01-06','Computer application technology',395.5)
INSERT INTO Student VALUES(2014216003,'Guo Hongliang','male','1995-04-12','Computer application technology',353.0)
INSERT INTO Student VALUES(2014216004,'LV Shanshan','female','1996-10-11','Computer information management',353.0)
INSERT INTO Student VALUES(2014216005,'Gao Quanying','female','1996-07-05','Computer information management',387.5)
INSERT INTO Student VALUES(2014216006,'Hao Sha','female','1994-08-03','Computer information management',372.0)
INSERT INTO Student VALUES(2014216007,'Zhang Feng','male','1995-09-03','Software technology',389.0)
INSERT INTO Student VALUES(2014216111,'Wu qiujuan','female','1995-08-05','Software technology',408.0)
INSERT INTO Student VALUES(2014216112,'Mu Jinhua','male','1995-10-06','Software technology',365.0)
INSERT INTO Student VALUES(2014216115,'Xin Xin Zhang','female','1995-04-12','Computer network technology',315.5)
INSERT INTO Student VALUES(2014216117,'Meng Xia','female','1996-01-11','Computer network technology',334.0)
INSERT INTO Student VALUES(2015216008,'Shu Hua Yang','female','1996-07-05','Computer application technology',329.0)
INSERT INTO Student VALUES(2015216009,'Cheng Peng','male','1996-08-03','Computer application technology',342.6)
INSERT INTO Student VALUES(2015216030,'Li Yan','male','1997-09-03','Computer information management',316.0)
INSERT INTO Student VALUES(2015216031,'Zhou Mei','female','1997-06-03','Computer information management',312.0)
INSERT INTO Student VALUES(2015216032,'Guan Xifen','female','1997-10-11','Computer information management',326.0)
INSERT INTO Student VALUES(2015216056,'Ming Ming Liu','female','1997-10-09','Software technology',357.0)
INSERT INTO Student VALUES(2015216057,'Sun Zhengxian','male','1996-05-16','Software technology',362.5)
INSERT INTO Student VALUES(2015216058,'Wang Ting','female','1997-04-13','Software technology',356.0)
INSERT INTO Student VALUES(2015216088,'LV wenkun','male','1996-09-03','Computer network technology',335.0)
INSERT INTO Student VALUES(2015216089,'Jiang Lili','female','1997-10-18','Computer network technology',368.0)

INSERT INTO Course VALUES(16020010,'Microcomputer assembly and maintenance',2.0)
INSERT INTO Course VALUES(16020011,'Installation and use of operating system',2.0)
INSERT INTO Course VALUES(16020012,'Software document editing and production',3.5)
INSERT INTO Course VALUES(16020013,'Process oriented programming',10.0)
INSERT INTO Course VALUES(16020014,'Database development and maintenance',6.5)
INSERT INTO Course VALUES(16020015,'Object oriented programming',7.5)
INSERT INTO Course VALUES(16020016,'Digital media acquisition and processing',4.0)
INSERT INTO Course VALUES(16020017,'Static web design and production',3.0)
INSERT INTO Course VALUES(16020018,'Web Standard design',4.0)
INSERT INTO Course VALUES(16020019,'Web Application design',7.0)
INSERT INTO Course VALUES(16020020,'Computer networking and management',3.5)
INSERT INTO Course VALUES(16020021,'Software testing and Implementation',2.5)

INSERT INTO SC VALUES(2014216001,16020010,96.0)
INSERT INTO SC VALUES(2014216001,16020011,80.0)
INSERT INTO SC VALUES(2014216002,16020010,67.0)
INSERT INTO SC VALUES(2014216003,16020012,78.0)
INSERT INTO SC VALUES(2014216003,16020013,87.0)
INSERT INTO SC VALUES(2014216003,16020014,85.0)
INSERT INTO SC VALUES(2014216111,16020014,89.0)
INSERT INTO SC VALUES(2014216111,16020015,90.0)
INSERT INTO SC VALUES(2015216089,16020010,58.0)

Statement structure:

SELECT expression[,...n]		--Projection (calculation Statistics)
[INTO New table name]		--preservation
FROM indicate[,...n]		--connect
[WHERE Logical expression]		--choice
[GROUP BY expression][,...n]		--Grouping statistics
[HAVING Logical expression]		--Restricted grouping statistics
[ORDER BY expression][,...n]		--sort

Function Description:

• Query one or more tables (base table or view) according to certain conditions and requirements to generate a new table (i.e. query result). The new table can be displayed, re applied as a table (sub query) or saved under a name.
• The order of clauses in the SELECT statement is very important. Optional clauses can be omitted, but these clauses must appear in the specified order when used.
• The expression in the SELECT query statement consists of column names, constants, variables, functions, and operators. In many cases, the expression is only the column name of the table.
• As an embedded language, SELECT query statement can be embedded in various high-level languages to access the database.
• The SELECT query statement is a self-contained language, which can be edited, compiled, executed and saved using the query editor of SSMS.

1.SELECT... Syntax

SELECT [ALL|DISTINCT] [TOP n] expression[,...n]

Function Description:

• The SELECT clause is mainly a comma separated expression list used to project query results.
• The expression in the SELECT clause is mainly the list of tables to be projected, and can also be composed of other constants, variables, functions and operators.
• In addition, some parameters can be selected according to needs. Their applications will be explained through examples below.

1.1 list name projection some columns

//grammar
 Listing [,...n]
//explain:
	When the expression is column name, projection query is performed on the table according to the column name

Case: query the Student number, name, gender and major of boys from the Student version of EDUC.

USE EDUC
GO
SELECT SID,Sname,Sex,Specialty FROM Student WHERE Sex='male'

1.2TOP keyword limits the number of rows returned

//grammar
TOP n [PERCENT]
//explain:
	If no keyword is specified PERCENT，Returns the top of the query result set n Row data. If a keyword is specified PERCENT，n Is the percentage of rows in the result set returned by the query

Case 1: query the data of the first three rows from the curriculum "Course" of the educational administration database "EDUC".
Case 2: query the top 20% of the data rows from the Student table "Student" in the educational administration database "EDUC".

//Case 1
SELECT TOP 3 CID,Cname,Credit FROM Course
//Case 2
SELECT TOP 20 PERCENT SID,Sname,Sex,Birthdate FROM Student

1.3 de duplicate rows

//grammar
ALL|DISTINCT
//Function Description:
	appoint ALL(default)Keyword will retain all data rows in the query result set. After projection operation on the table, duplicate data rows may appear in the query result set. Use DISTINCT Keyword eliminates duplicate rows in the query result set.

Case: query the name of each major in the school from the Student table of EDUC in the educational administration database.

SELECT DISTINCT Specialty FROM Student

1.4 the wildcard "*" projects all columns

//grammar
*
//explain:
	To project all the columns in a table without changing their order, instead of listing all the column names in the table, use“*"replace.

Case: query the data of the first three rows from the Student table "Student" in the educational administration database "EDUC".

SELECT TOP 3 * FROM Student

1.5 calculating column values using expressions

//grammar
 expression[,...n]
//explain:
	stay SELECT Clause can be used in expressions(+),Minus(-),Ride(*),Except(/),Take mold(%)And character connection(+)And other operators and various functions to obtain the column value of the query result through the calculation of the expression. It is worth noting that the calculation of the column in the table only affects the query result and does not change the data in the table.

Case: query the Course hours corresponding to credits from the Course "Course" table of the educational administration database "EDUC" (assuming 1 credit per 18 class hours), and display the first 5 lines.

//The expression contains an operator
SELECT TOP 5 CID,Cname,Credit,Credit*18 FROM Course
//Expression with function
SELECT TOP 5 CID,Cname,Credit,STR(Credit*18,3,0)+'Class hours' FROM Course

1.6 custom column headings

//grammar
1.'Specified column header'=expression
2.expression AS 'Specified column header'

explain:

• After customizing the column title, the specified column title will be displayed in the title position of the query result instead of the column name defined in the table. If the specified column title is not a rule identifier (such AS containing spaces, etc.) The string can be enclosed in single quotation marks, otherwise it can not be used. The keyword AS can also be omitted. By default, the column title displayed in the data query result is the column name used when creating the table.
• For columns calculated through expressions (such as the above examples), the system does not specify column titles, but identifies them with "no column list". In this case, you can re specify column titles for query results.
• After customizing the column title, the specified column title will be displayed in the title position of the query result instead of the column name defined in the table. If the specified column title is not a rule identifier (such AS containing spaces, etc.) The string can be enclosed in single quotation marks, otherwise it can not be used. The keyword AS can also be omitted. By default, the column title displayed in the data query result is the column name used when creating the table.

Case: in the previous example, the column header is displayed in Chinese

SELECT TOP 5 CID AS 'Course number',Cname AS 'Course name',Credit credit,Class hours=STR(Credit*18,3,0) 
FROM Course

1.7 aggregate function

//grammar
 Function name([ALL|DISTINCT] expression|*)

explain:

• Unlike other functions, the parameters of the aggregate function are generally column names or expressions containing column names. The main function is to perform vertical statistics and calculation on the value of the specified column name expression in the table, so it is also called column function. In the parameters of the aggregate function, the ALL keyword identifies the function to perform statistics and calculation on the ownership of the specified column, and the DISTINCT keyword indicates that the function only refers to The specified column is one (excluding duplicate values) for statistics and calculation, and ALL is the default setting.

Common functions	explain
COUNT	Count the number of items selected in the column or the number of rows output by the query
SUM	Calculates the sum of the specified numeric list expression
AVG	Computes the average value of the specified numeric column name expression
MAX	Finds the maximum value of the specified numeric, character, or date column name expression
MIN	Finds the minimum value of the specified numeric, character, or date column name expression

Case: count the number of boys from the Student table.

//Case: count the number of boys from the Student table.
SELECT COUNT(*) AS Number of people
FROM Student
WHERE Sex='male'
//Case: count the number of majors from the Student table. Note that the DISTINCT keyword eliminates duplicate rows, that is, each major is recorded only once.
SELECT COUNT(DISTINCT Specialty) AS Number of specialties 
FROM Student
//Case: count the total number of students, the highest score, the lowest score, the total score (no practical significance, just an example) and the average score of "AScores" from the Student table "Student".
SELECT COUNT(*)  AS Total number,MAX(AScores) AS Highest score,MIN(AScores) AS Lowest score,SUM(AScores) AS Total score,STR(AVG(AScores),5,1) AS average
FROM Student

2.FROM... Syntax

2.1 specify base table

//grammar
FROM Table name[,...n]

explain:

• Specify the base table or view (virtual table) to query. If more than one base table or view is specified, calculate the Cartesian product between them and implement the join query in combination with the equivalent conditions of the WHERE clause.

Case: query the student number, name, selected course name and score information from the educational administration database "EDUC".

SELECT Student.SID,Sname,Cname,Scores		--Project columns from multiple tables
FROM Student,SC,Course		--Cartesian product of three tables
WHERE Student.SID =SC.SID
AND SC.CID=Course.CID		--Equivalent connection condition

2.2 specify a temporary alias for the base table

//grammar
 indicate[AS] alias

explain:

• The AS keyword can not be selected. The alias can simplify the description. In addition, it can realize self connection.

//Case: the same as the above example, specify aliases x, y and z for the base tables "Student", "Course" and "SC" to simplify the table name
SELECT x.SID,Sname,Cname,Scores		--Project the columns of each table
FROM Student AS x,SC AS y,Course AS Z		--Specify aliases for the three tables
WHERE x.SID=y.SID AND y.CID=z.CID		--Equivalent conditional connection
//Case: find out the student number of students who have selected at least two courses from the course selection table "SC".
SELECT x.SID,x.CID,y.CID
FROM SC x ,SC y			--Specify an alias for the table to realize self connection
WHERE x.SID=y.SID AND x.CID<>y.CID
//For the above example, projecting the student number in the SELECT clause and adding the DISTINCT parameter will eliminate duplicate rows in the query
SELECT DISTINCT x.SID AS Student number
FROM SC x,SC y
WHERE x.SID=y.SID AND x.CID<>y.CID

3.WHERE logical expression

//grammar
WHERE Logical expression

explain:

• The WHERE clause is used to SELECT operations, and logical expressions are used to describe query conditions. When the data of the data row meets the query conditions (the logical expression is true), the data is provided like the SELECT query result set. Otherwise, the data will not be used.
• The WHERE clause is also used in DELETE and UPDATE statements to select rows in the table to be deleted and modified.

Logical expression	It is composed of column name, constant, variable] function, subquery, comparison operator or logical operation, and its value is TRUE (1, 'TRUE') or FALSE (0, 'FALSE')
Comparison operator	=(equal to), < > (not equal to), > (greater than), < (less than), > = (greater than or equal to), < = (less than or equal to)
Logical operator	NOT, AND, OR

3.1 comparison operators

//Case: find out the information of Student Zhao Chenggang from the Student table "Student".
SELECT * FROM Student
WHERE Sname='Zhao Chenggang'
//Case: query the information of students majoring in software technology from the Student table "Student".
SELECT * FROM Student
WHERE Specialty='Software technology'
//Case: find out the information of students over 22 years old in 2019 from the Student table "Student", and the date of the association system is 2019.
SELECT *,DATEPART(year,GETDATE())-DATEPART(year,Birthdate) Age
FROM Student
WHERE DATEPART(year,GETDATE())-DATEPART(year,Birthdate)>=22

3.2 logical operators

3.2.1 logical operators (NOT,AND,OR)

//Case: find out the information of girls aged 22 or over in 2019 from the Student table "Student", and the system date is the same as above.
SELECT *,DATEPART(year,GETDATE())-DATEPART(year,Birthdate) Age
FROM Student
WHERE DATEPART(year,GETDATE())-DATEPART(year,Birthdate)>=22
AND Sex='female'
//Case: find out the information of boys under the age of 22 by 2019 from the Student table "Student".
SELECT * DATEPART(year,GETDATE())-DATEPART(year,Birthdate) Age
FROM Student
WHERE NOT(DATEPART(year,GETDATE())-DATEPART(year,Birthdate)>=22)
AND NOT(Sex='female')
//Query the Student information with Student numbers 2014216007 and 2015216089 from the Student table "Student"
SELECT * FROM Student
WHERE SID = '2014216007' OR SID = '2015216089'

3.2.2 logical operators (range operators)

//grammar
 expression[NOT] BETWEEN Start value AND End value

Description: refers to whether the value of the expression is between the start value and the end value. The expression can be the column name in the table.

• Column name BETWEEN start value AND end value - > equivalent to (column name > = start value AND column name < = end value)
• Column name NOT BETWEEN start value AND end value - > equivalent to (column name > = start value OR column name < = end value)

//Case: query the information of students whose admission scores are between 350 and 360 from the Student table.
SELECT * FROM Student
WHERE AScores BETWEEN 350 AND 360

3.2.3 logical operators (pattern matching operators)

//grammar
 expression[NOT] LIKE character string

explain:

• When the value of the expression (NOT, NOT) is similar to the given character string (including wildcards), the value of the logical expression is true.
• The wildcard "" represents one arbitrary character and "%" represents any number of characters. The pattern matching operator LIKE can realize the fuzzy query of the table.

//Case: find out the Student information with surname "Li" from the Student table "Student".
SELECT * FROM Student
WHERE Sname LIKE 'Lee%'		--Pattern matching fuzzy query
//Case: find out the courses related to programming from the curriculum "Course".
SELECT * FROM Course
WHERE Cname LIKE '%Programming%'

3.2.4 logical operators (list operators)

)

//grammar
 expression [NOT] IN (list|Subquery)
The list is: expression[,...n]

explain:

• If the value of the expression (NOT, NOT) is equal to the value of any expression in the list, the value of the logical expression is true.
• The application of subquery will be further introduced in subsequent courses.

//Case: query the Student information with Student numbers 2014216007 and 2015216089 from the Student table "Student".
SELECT * FROM Student
WHERE SID IN('2014216007','2015216089')

3.2.5 predicate operator (null operator)

//grammar
 expression IS [NOT] NULL

explain:

• In the database table, many columns can have no input values except that the columns that must have sentence values are not allowed to be empty. At this time, the value of this column is empty (NULL).
• When the value (NOT, NOT) of the expression is empty (NULL), the value of the logical expression is true.
• The expression can be the column name in the table, which is used to determine whether the specified column name value is NULL.

//Case: query the information of students whose test Scores are not registered from the educational administration database "EDUC". First change the column "Scores" value of a row in the table "SC" to the NULL value "NULL (in uppercase)". Be careful not to change it to 0.0, which is also a score.
SELECT Student.SID,Sname,Cname,Scores
FROM Student,SC,Course
WHERE Student.SID=SC.SID AND SC.CID=Course.CID AND Scores IS NULL		--Null value judgment

4.GROUP BY list expression

//grammar
GROUP BY Tabular expression[,...n]

explain:

• Group statistics can be realized in combination with list expressions (expressions containing column names) or aggregate functions.
• In practical application, it is often necessary to group statistics and summary according to the value of a column.
• If it is necessary to count the total number of students in each major from the student table, group them with "GROUP BY major name" during statistics, and then calculate the value of the aggregation function.
• If it is necessary to calculate the total score of each student from the course selection table, use "GROUP BY student number" for grouping during statistics, and then calculate the value of the aggregation function.

Note: the list expression projected in the SELECT clause must be included in the aggregate function or appear in the list expression after the corresponding GROUP BY.

//Case: find out the average admission score of students of various majors from the Student table.
SELECT Specialty AS major,'average'=str(AVG(AScores),5,1)		--Seek a professional admission average score
FROM Student GROUP BY Specialty			--Group by discipline name

//Case: query the total number of students in each major from the Student table, and the query result is required to display two columns: major name and number of students.
SELECT 'major'=Specialty,'Number of people'=COUNT(*)		--Count the number of students in each major
FROM Student
GROUP BY Specialty		--Group by discipline name

//Case: count the total score of each student from the course selection table "SC", and the query result is required to display the student number, name and total score of the student.
SELECT SC.SID,Student.Sname,'Total score'=SUM(Scores)		--Find the sum of each student's grades
FROM SC,Student 
WHERE SC.SID=Student.SID
GROUP BY SC.SID,Student.Sname		--According to student number SID grouping

5.HAVING logical expression

//grammar
HAVING Logical expression

explain:

• Filter (select) the statistical results in cooperation with the GROUP BY parameter
• For the results of group statistics using the GROUP BY clause above, you can also filter according to the conditions specified by the logical expression in the HAVING clause.
• The logical expression of the HAVING clause usually contains aggregate functions. It is worth noting that aggregate functions cannot be placed in the logical expression of the WHERE clause.

//Case:
From the course selection table SC"And student form Student"Query the student number, name and total score of students with a total score of more than 150 points.
SELECT SC.SID,Student.Sname,'Total score'=SUM(Scores)
FROM SC,Student
WHERE SC.SID=Student.SID
GROUP BY SC.SID,Student.Sname
HAVING SUM(Scores)>150
//WHERE sum (sores) > 150 is a complete error because the aggregate function SUM(Scores) cannot be placed in the logical expression of the WHERE clause.

6.ORDER BY column name

//grammar
ORDER BY {expression[ASC/DESC]}[,...n]

explain:

• Usually, the expression is the column name of the table
• Sort the query results in ascending (ASC: default) or descending (DESC) order by one or more columns (up to 8060 bytes).
• If the ORDER BY clause is followed by an expression table, the system will determine the priority of sorting according to the order of the expressions in each column, and then sort.
• ORDER BY cannot be used on columns whose data types are varhcar(max),nvarchar(max),varbinary(max) or xml, and can only be used in external queries.
• If SELECT DISTINCT is specified, the column name in the ORDER BY clause must appear in the list of the SELECT clause.

//Case: count the total score of each Student from the course selection table "SC" and Student table "Student", and sort the results in descending order according to the total score.
SELECT SC.SID,Student.Sname,'Total score'=SUM(Scores)
FROM SC,Student
WHERE SC.SID=Student.SID
GROUP BY SC.SID,Student.Sname
ORDER BY SUM(SCORES) DESC		--Sort by total score in descending order
//Case: query the number of courses selected by each student from the educational administration database "EDUC" and sort them in ascending order according to the number of courses selected.
SELECT Student.SID AS Student number,Student.Sname AS full name,COUNT(*) AS Number of courses selected
FROM Student,sc
WHERE Student.SID=SC.SID
GROUP BY Student.SID,Student.Sname
ORDER BY COUNT(*) 		--Sort by the number of courses selected in ascending order
//Case: find out the information of students majoring in software technology from the Student table "Student", and arrange it in ascending order by gender and descending order by admission score.
SELECT *
FROM Student
WHERE Specialty='Software technology'
ORDER BY Sex ASC,Ascores DESC

7.INTO new table name

//grammar
INTO New table name

explain:

• The INTO clause specifies that the result set is used to create a new table
• Query results often need to be saved for use. You can use the INTO clause to store the query results in a new basic table. This method is often used to create a copy of the table.

//Case: save the contents of student number and course number as a new student_course from the course selection table "SC".
SELECT SID,CID 
INTO student_course
FROM SC

7, SELECT query (ANSI and subquery)

SELECT ... FROM ... JOIN ... ON
Note: in SQLSERVER, two syntax forms can be used

• FROM clause, the join condition is written in the logical expression of the WHERE clause, so as to realize the join of the table (early).
• In ANSI connection syntax, the JOIN... ON keyword is used in the FROM clause, and the connection conditions are written after ON, so as to realize the connection of tables.
• SQL SERVER2012 recommends ANSI connection.

//grammar
FROM Table name 1[line type] JOIN Table name 2 ON Connection conditions

explain:

• Realize the pairwise connection between tables. After connecting tables 1 and 2, you can continue to connect with tables 3,... And n, and you can connect up to 256 tables.
• The connection condition is placed after the ON keyword.
• In particular, this statement can also connect views, which is described in the next chapter.

line type	significance
INNER JOIN	Inner connection
LEFT [OUTER] JOIN	Left outer connection
RIGHT [OUTER] JOIN	Right outer connection
CROSS JOIN	Cross connect

//grammar
FROM Table name 1 INNER JOIN Table name 2 ON Join expression

explain:

• Select the data row that meets the connection conditions from the Cartesian product of two or more tables. If the data row cannot meet the connection conditions, it is discarded. Inner join eliminates the data row that does not match in another table.

1. Equivalent connection

//Case:
"From the library database" LIbrary"Query the detailed information of each reader (reader and reader type) in, and duplicate columns are allowed.
USE Library
GO
SELECT Reader.*,ReaderType.*
FROM reader INNER JOIN ReaderType ON
Reader.TypeID=ReaderType.TypeID			--Inner connection

2. Natural connection

//Case: query the detailed information of each reader (information of readers and saving books), and duplicate columns are not allowed.
SELECT Reader.*,Borrow.LendDate,Borrow.ReturnDate,Book.BID,Book.Bname,Book.Author,Book.Price
FROM Reader INNER JOIN Borrow ON
Reader.RID=Borrow.RID		--Table 1 internal connection table 2
INNER JOIN Book ON Borrow.BID=Book.BID		--Reconnection Table 3

3. External connection

• The outer join returns all rows in at least one table or view specified in the FROM clause, as long as they meet any WHERE selection (excluding the join condition after ON) or HAVING qualification.
• External connection is divided into left external connection, right external connection and all external connection.
• The left outer connection does not restrict the left table in the connection; The right outer connection does not restrict the right table in the connection; Total outer join does not restrict the two tables, and all rows in the two tables will be included in the result set.

3.1 left outer connection

//Syntax:
FROM Table name 1 LEFT[OUTER] JOIN Table name 2 ON Join expression

explain:

• The connection result retains the rows in Table 1 that do not form a connection, and the corresponding columns in Table 2 are NULL values.

//Case: query the borrowing situation of readers from "Reader" and "Borrow", including readers who have not borrowed books.
SELECT Reader.*,Borrow.RID,BID
FROM Reader LEFT OUTER JOIN BorroW
ON Reader.RID=Borrow.RID
//Note: the connection result retains the third and fourth rows in the table "Reader" that do not meet the equivalence conditions, and the corresponding columns in the table "mirror" are NULL values, indicating that readers Zhang Ying and Li Yaxi have not borrowed books.

3.2 right outer connection

//Syntax:
FROM Table name 1 RIGHT[OUTER] JOIN Table name 2 ON Join expression

explain:

• The connection result retains the rows in Table 2 that do not form a connection, and the corresponding columns in Table 1 are NULL values.

//Case: find out the borrowing of books from table "Borrow" and table "Book", including the borrowing of books not borrowed.
SELECT Borrow.RID,Borrow.BID,Bname,Author
FROM Borrow RIGHT OUTER JOIN Book
ON Borrow.BID=Book.BID
//Note: the connection results are involved in rows 1, 4, 8 and 10 in the table "Book" that do not meet the equivalent conditions, and the corresponding columns in the table "mirror" are NULL, indicating that the four books "ERP starts from internal integration", "integrated application of ERP system", "SQL Server 2008 database design and implementation" and "Introduction to database system" have not been based, and the SAP foundation tutorial It was lent twice.

3.3 all external connections

//grammar
FROM Table name 1 FULL[OUTER] JOIN Table name 2 ON Join expression

explain:

• The connection result retains the tuple of Table 1 that does not form a connection, and the corresponding column of Table 2 is NULL; the connection result also retains the tuple of Table 2 that does not form a connection, and the corresponding column of Table 1 is NULL.

//Case: external connection between borrowing and readers
SELECT Reader.*,Borrow.RID,Borrow.BID
FROM Borrow FULL OUTER JOIN Reader
ON Borrow.RID=Reader.RID

4. Self connection

//grammar
FROM Table 1 alias 1 JOIN Table name 1 alias 2 ON Join expression

explain:

• A table can realize its own connection operation through self connection. Self connection can be regarded as condensation between two copies of a table. In self connection, two different aliases must be specified for the table to make it logically become two tables.

//Case: query the borrowing information of readers who borrowed more than two books from the Library management database
//Step 1: self connect table "mirror" and table "mirror"
SELECT x.RID,x.BID,x.ReturnDate,y.RID,y.BID
FROM Borrow x JOIN Borrow y ON x.RID=y.RID		--Reader's century good equivalent condition self connection
WHERE x.ReturnDate IS NULL AND y.ReturnDate IS NULL		--If the repayment period is empty, it has not been returned

explain:

• After careful observation, it can be found that the reader number "RID" is the same and the single book number "BID" is different in line 5 and line 6 of the query results, indicating that the reader has borrowed at least two books. Add the query condition "x.BID < > y.BID" in the WHERE clause of the above query to query the reader number of more than two books. Here "x.ReturnDate IS NULL "Describes the conditions under which the books borrowed by readers have not been returned.

//Step 2: add selection conditions and projection operations to the self connection of table "mirror" and table "mirror".
SELECT x.RID,x.BID,x.ReturnDate
FROM Borrow x JOIN Borrow y ON x.RID=y.RID		--Reader's century good equivalent condition self connection
WHERE x.ReturnDate IS NULL AND y.ReturnDate IS NULL		--If the repayment period is empty, it has not been returned
AND x.BID<>y.BID		--Different book numbers, that is, two different books

explain:

• After careful observation, it can be found that the reader number "RID" in row 5 and row 6 of the query result is the same, but the book number "BID" is different, indicating that the reader has borrowed at least two books. Add the query condition "x.BID < > y.BID" in the WHERE clause of the above query to query the reader number of more than two books. Here "x.ReturnDate IS NULL "Describes the conditions under which the books borrowed by readers have not been returned.

5. Cross connection

//grammar
FROM Table name 1 CROSS JOIN Table name 2

explain:

• The Cartesian product calculation of the two tables is equivalent to the FROM table name 1, and the logical expression of WHERE connection condition is not added after the table name 2.
• Generally meaningless.

6. Multi meter connection

//grammar
FROM Table name 1 JOIN Table name 2 ON Join expression JOIN Table name 3 ON Join expression

explain:

• Up to 256 tables can be connected, usually 8 ~ 10.

//Case: connect the Reader table "Reader", borrowing table "Borrow" and Book table "Book" equally.
SELECT t1.RID,Rname,BnamE,LendDate
FROM Reader t1 JOIN Borrow t2 ON t1.RID=t2.RID JOIN BooK t3 ON t2.BID=t3.BID