Real-Life SQL statement collection (constantly updated-)

Statement
Real-Life SQL statement collection (constantly updated-)

Foreword: Here will I encounter the programming practice of the value of the SQL statement down the road, on the one hand to facilitate their own search, on the other hand, also tamping the memory of the impending forgotten. Throughout the process I will keep on updating until I can no longer add, and at the same time, only record the most practical, not to follow the college pie.

　

One, the common SQL statement highlights

1, query:

1.1, Simple query: SELECT * FROM table where



1.2, connection query:

What is a connection query? Gu name interpretation, is a query involving more than one table query. is to talk about the connection, nonsense, to know the connection is still the main characteristics of the relational database.

Connection queries are divided into three types: outer joins (OUTER join), Inner joins (INNER joins), and Cross joins (CROSS join).

1.2.1, an inner join (INNER join) uses the comparison operator to compare operations between tables for some (some) column data and lists the rows of data in those tables that match the join condition. According to the comparison method used, the inner connection is divided into equivalent connection, natural connection and unequal connection three kinds.

1.2.2, the outer joins are divided into three kinds of left outer joins (the left-hand OUTER join or the RIGHT join), the right-hand outer join (the right-click OUTER join or the OK join), and the full OUTER join or the fully join. Unlike an inner connection, the outer join lists not only the rows that match the join condition, but all the rows of data that match the search criteria in the left table (when left outer), right (when the right outer join), or two tables (when all outer joins).

1.2.3, a cross join (CROSS JOIN) does not have a WHERE clause that returns the Cartesian product of all the rows of data in the join table, with the number of rows in the result set equal to the number of rows of data in the first table that match the query criteria multiplied by the number of rows of data in the second table that The ON (join_condition) clause in a JOIN operation indicates the join condition, which consists of columns and comparison operators, logical operators, and so on in the connected table.

1.2.4, no connection can be directly connected to the text, ntext, and image data type columns, but it is possible to indirectly connect the three types of columns. For example:

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, use the WHERE clause to set the query condition

The WHERE clause sets the query condition to filter out unwanted rows of data. For example, the following statement queries for data older than 20:

SELECT *

From Usertable

WHERE age>20

The WHERE clause can include various conditional operators:

Comparison operators (size comparison):>, >=, =, <, <=, <>,!>,!<

Range operator (whether the expression value is in the specified range): BETWEEN ... And ...

Not BETWEEN ... And ...

List operator (determines whether an expression is a specified item in a list): In (item 1, item 2 ...)

Not in (item 1, item 2 ...)

Pattern-matching character (determines whether the value matches the specified character wildcard format): like, not like

Null-valued identifier (to determine whether an expression is empty): Is null, not is NULL

Logical operators (logical joins for multiple conditions): not, and, or

1. Range operator Example: Age BETWEEN and 30 are equivalent to age>=10 and age<=30

2, List operator Example: Country in (' Germany ', ' the ', ')

3, pattern matching example: Often used in fuzzy lookup, it determines whether the column value and the specified string format match. Can be used for types of queries such as char, varchar, text, ntext, datetime, and smalldatetime.

You can use the following wildcard characters:

Percent%: can match any type and length of the character, if it is in Chinese, please use two percent sign that%.

Underline _: Matches a single arbitrary character, which is often used to limit the length of the character of an expression.

square brackets []: Specifies a character, string, or range, requiring that the matched object be any of them.

[^]: The value is also [] the same, but it requires that the matched object be any character other than the specified character.

For example:

Limit ends with Publishing, using like '%publishing '

Limit begins with a: like ' [a]% '

Limit to start with a: like ' [^a]% '

Null-value Identifier Example: WHERE the IS NULL

2, UPDATE: Update table

　

3, insert:

3.1, General insertion:

INSERT into Publishers
(pub_id, pub_name, city, state)
VALUES
(' 9001 ', ' Acme Publishing ', ' New York ', ' NY ')

3.2, inserting multiple lines

You can add multiple rows of data to a table by using the INSERT statement. These multiline data are selected from another table that already has the data. In this example, add data about publishers in California and Texas to the Pubhold table. This 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 Joint deletion:

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 uses the rule

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

* + 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

* + 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 invocation of stored procedures

1, the call to the stored procedure

Execute procedurename @param = ' value '

2, a typical stored procedure with parameters

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 section information

Delete from Uu_minclassroomsect

where min_classroom_id = @miniClassroom_id

--Delete Small classroom user purchase record

Delete from Uu_userbuyminclassroom

where min_classroom_id = @miniClassroom_id

--Deletion of the subject information of the corresponding small classroom grade

Delete from Uu_minclassroomtogradeclass

where min_classroom_id = @miniClassroom_id

--Delete small class speeches

Delete from Uu_minclassroomdiscuss

where min_classroom_id = @miniClassroom_id

--Delete Course Discussion

DELETE from Uu_coursediscuss

WHERE course_id in

(SELECT course_id

From Uu_courseinfo

WHERE min_classroom_id = @miniClassroom_id)

--Deleting a user's course collection

DELETE from Uu_usercellectioncourse

WHERE course_id in

(SELECT course_id

From Uu_courseinfo

WHERE min_classroom_id = @miniClassroom_id)

--delete the user purchase information of small class course

DELETE from Uu_userbuycourse

WHERE course_id in

(SELECT course_id

From Uu_courseinfo

WHERE min_classroom_id = @miniClassroom_id)

Commit TRANSACTION

Return (1)

Go

Four, triggers