Preface: Here, the valuable SQL statement encountered in our programming has been recorded, on the one hand, it is convenient for yourself, on the one hand, it is also consolidated to remember the upcoming memories. I will continue to update throughout the process until I can't add it. At the same time, I only record the most practical 咚咚, I don't follow the set of college.
First, common SQL sentences to gather
1, query:
1.1, simple query: SELECT * from Table Where
1.2, join query:
What is a connection query? Assembling the name, it is related to the query of multiple tables when queries. It is to say that the connection, nonsense, know the main features of the connection or the relational database.
Connection query is divided into three: Outer Join, Inner Join, Cross Join.
1.2.1 Inner Join Using the Comparison Operator to perform a comparison operation of the data (some) column data, and list the data lines matching the connection conditions in these tables. According to the comparative method used, the inner connection is divided into equivalent connection, natural connection, and not equal connection.
1.2.2, the outer connection is divided into three left-way connectivity (Right Outer Join or Right Join) and all-end connections (Full Outer Join or Full Join). Unlike internal connections, the external connection does not only list rows matching the connection condition, but is listed on the left table (left external connection), the right table (right-external connection) or two tables (all external connection All data lines in line with search criteria are in line.
1.2.3, Cross Join No WHERE clause, it returns the Cartesian of all data lines in the connection table, and the number of data lines in the result collection is equal to the number of data lines that meet the query conditions in the first table. The number of data lines that meet the query conditions in the second table. The on (join_condition) clause in the connection operation indicates the connection condition, which is constructed by the columns and comparison operators, logical operators, and the like in the connected table.
1.2.4, no matter which connection does not directly connect the text, ntext, and the image data type column, but can be indirectly connected to these three columns. E.g:
Select p1.pub_id, p2.pub_id, p1.pr_info from pub_info as p1 inner join pub_info as p2 on datalength (p1.pr_info) = DATALENGTH (P2.PR_INFO)
1.2.5, set the query condition using the WHERE clause
The WHERE clause sets the query condition and filter out the unwanted data line. For example, the following statement queries the age of greater than 20:
SELECT *
From utertable
WHERE AGE> 20
The WHERE clause can include various conditional operators:
Compare operators (size comparison):>,> =, =, <, <=, <>,!>,! <
Range operators (whether the expression value is in the specified range): Between ... and ...
NOT BETWEEN ... AND ...
List operator (judging if the expression is specified in the list): in (item 1, Item 2 ...)
Not in (item 1, Item 2 ...)
Mode match (determined whether the value is constructed with the specified character wildfire format): Like, Not Like
Null value determine (judging if the expression is empty): Is Null, Not is Null
Logical operator (for multi-condition logical connections): NOT, AND, OR
1. Range Operator Example: AGE BETWEEN 10 and 30 equivalent to agn> = 10 and age <= 30
2, list operator: Country in ('Germany', 'CHINA') 3, Mode Matching Regulation: Commonly used in blurring, it is determined whether the column value matches the specified string format. Can be used for type inquiry such as char, varchar, text, ntext, datetime, and smalldatetime.
You can use the following wild character:
Percentage%: Characters that can match any type and length, if it is Chinese, please use two percent sign to %%.
Underline _: Match a single arbitrary character, it is often used to limit the character length of the expression.
Square brackets []: Specify a character, string, or range that requires the matching object to be any of them.
[^]: The value is also [], but it requires the matching object to be any of the characters other than the specified character.
E.g:
Limited to the end of Publishing, use Like '% Publishing'
Restrictions on A: Like '[A]%'
Limits outside the beginning: Like '[^ a]%'
Null value judgment: WHERE AGE IS NULL
2, update: Update Table
3, insert:
3.1, generally insertion:
INSERT INTO PUBLISHERS (Pub_ID, Pub_Name, City, State) Values ('9001', 'ACME Publishing', 'New York', 'NY')
3.2, insert multi-line
Use the INSERT statement to add multiple lines of data to the table. These multi-line data are selected from another table that has already stored these data. In this example, add data about publishers in California and Texas to the Pubhold table. These data can be obtained from the Publishers table.
INSERT Pubhpold Select * from Publishers Where State = 'CA' or State = 'TX'
4, delete:
4.1, DELETE statement combines:
Delete from uu_suitetominclassroomsect
Where min_classroom_sect_id in
(SELECT MIN_CLASSROOM_SECT_ID
From uu_minclassroomsect
WHERE min_classroom_id = '112')
Second, the view is used
1, a typical view
Create View View_uugrouptaxis
AS
SELECT UU_Groupinfo.group_id, uu_groupinfo.group_name,
UU_GROUPTYPE.MAIN_TYPE, UU_GROUPTYPE.GROUP_TYPE_NAME,
UU_Groupinfo.group_ICon_URL, ISNULL
((Select Count (*)
From uu_groupuser
WHERE uU_groupinfo.group_id = uu_groupuser.group_id), 0)
* 50 isnull (uu_groupinfo.fundcount, 0) isnull
((Select Count (*)
From dv_topic
WHERE DV_TOPIC.BOARDID = uu_groupinfo.subforum_id), 0) * 5 ISNULL
((SELECT Count (*) from uu_groupphotos
Where uu_groupphotos.group_id = uu_groupinfo.group_id), 0)
* 10 isnull (uu_groupinfo.topic_account, 0)
* 2 Isnull (uu_groupinfo.hit_account, 0) as group_activedegree,
ISNULL
((Select Count (*)
From uu_groupuser
WHERE uU_groupinfo.group_id = uu_groupuser.group_id), 0)
As group_membernum, isnull (uu_groupinfo.fundcount, 0) As FundCount,
(Select Count (*)
From dv_topic
WHERE DV_TOPIC.BOARDID = uu_groupinfo.subforum_id) As Group_ArticleNum,
(Select Count (*)
From uu_groupphotos
Where uu_groupphotos.group_id = uu_groupinfo.group_id) As Group_Photonum,
UU_GroupInfo.topic_account, uu_groupinfo.hit_account,
(Select User_Name
From uu_registeruser
Where uu_registeruser.user_id = uu_groupinfo.creator_id)
As group_creatorname, uu_groupinfo.create_time
From uu_groupinfo inner Join
UU_GROUPTYPE ON
UU_GROUPINFO.GROUP_TYPE_ID = uu_grouptype.group_type_id
Third, the creation and call of the stored procedure
1, the call to the stored procedure
Execute procedurename @ param = 'value'
2, a typical parameter stored procedure
Create Procedure P_DelminiClassProc
@miniclassroom_id Int
AS
Declare @billtag varchar (4)
Set nocount on
IF @miniclassroom_id is null
Begin
Return (-1)
end
Else
Begin Transaction
- Delete Package Information
Delete from uu_suitetominclassroomsect
Where min_classroom_sect_id in
(SELECT MIN_CLASSROOM_SECT_ID
From uu_minclassroomsect
WHERE min_classroom_id = @ miniclassroom_id)
- Delete small class segment information
DELETE from uu_minclassroomsect
WHERE min_classroom_id = @miniclassroom_id
- Delete small classroom users purchase record
Delete from uu_userbuyminclassroom
WHERE min_classroom_id = @miniclassroom_id
- Delete the corresponding small class grade discipline information
Delete from uu_minclassroomtogradeclass
WHERE min_classroom_id = @miniclassroom_id
- Delete small class speech
Delete from uu_minclassroomdiscuss
WHERE min_classroom_id = @miniclassroom_id
- Delete course discussion
Delete from uu_coursediscuss
Where courte_id in
(Select Course_ID
From uu_courseinfo
WHERE min_classroom_id = @miniclassroom_id)
- Delete User Course Collection
Delete from uu_usercellectioncourse
Where courte_id in
(Select Course_ID
From uu_courseinfo
WHERE min_classroom_id = @miniclassroom_id)
- Delete user purchase information for small class lessons
Delete from uu_userbuycourse
Where courte_id in
(Select Course_ID
