SQL advanced query skills

1. Union, except T, Intersect 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, except 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.
Bytes ----------------------------------------------------------------------------------------------------
2. External Connection
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.
SQL: select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B on A. A = B. C
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.
Bytes ----------------------------------------------------------------------------------------------------

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:
Cross-database table copy (absolute path for specific data) (access 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: DisplayArticle, Submitted 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),
Table name B where B. primary key field = A. primary key field order by A. Sorting Field

14. Note: The first 10 records
Select top 10 * Form Table1 where range

15. Description: select all the information of the largest a record in the data with the same B value in each group.
(Usage similar to this can be used for monthly rankings of forums, monthly analysis of popular products, rankings by subject scores, 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

Method for randomly selecting database records (using the randomize function and using SQL statements)
For the data stored in the database, random numbers provide the above results, but they may be too slow.
You cannot ask ASP to "find a random number" and print it out. In fact, a common solution is to create a loop as follows:
Randomize
Rnumber = int (rndx 499) + 1

While not objrec. EOF
If objrec ("ID") = rnumber then
... The execution script...
End if
Objrec. movenext
Wend

This is easy to understand. First, you get a random number ranging from 1 to 500 (assuming 500 is the total number of records in the database ).
Then, you traverse each record to test the id value and check whether it matches the rnumber. If conditions are met, execute the one starting with the then keyword.Code.
If your rnumber is equal to 495, it takes a long time to repeat the database.
Although the number 500 looks a little larger, it is still a small database than a more robust enterprise solution, which usually contains thousands of records in a database.
At this time, it will not die?
With SQL, you can quickly find an accurate record and open a recordset that only contains the record, as shown below:
Randomize
Rnumber = int (rndx 499) + 1

SQL = "select * from MERs where id =" & rnumber

Set objrec = objconn. Execute (SQL)
Response. writernumber & "=" & objrec ("ID") & "& objrec (" c_email ")

You do not need to write rnumber and ID. You only need to check the matching conditions.
As long as you are satisfied with the work of the above code, you can operate "random" records on demand.
Recordset does not contain any other content, so you can quickly find the record you need, which greatly reduces the processing time.
Bytes --------------------------------------------------------------------------------------
Random Number again

Now you are determined to squeeze the last drop of the random function, you may retrieve multiple random records at a time or want to use records within a certain random range.
By extending the standard random example above, you can use SQL to cope with the above two situations.
To retrieve several randomly selected records and place them in the same recordset, you can store three random numbers and query the database to obtain records matching these numbers:
SQL = "select * from MERs where id =" & rnumber & "or ID =" & rnumber2 & "or ID =" & rnumber3

If you want to select 10 records (maybe the list of 10 links for each page load ),
You can use between or a mathematical equation to select the first record and the appropriate number of incremental records.
This operation can be completed in several ways, but the SELECT statement only shows one possibility (the ID here is the automatically generated number ):
SQL = "select * from MERs where ID between" & rnumber & "and" & rnumber & "+ 9"
Note: The above code is not executed to check whether there are 9 concurrent records in the database.

Reads several records randomly,
Tested
Access Syntax: Select top 10 * from table name order by RND (ID)
SQL Server: Select Top N * from table name order by newid ()
Mysqlelect * from table name order by rand () limit n
Access left connection syntax (the left connection is used in recent development, access does not help anything, there is no access SQL instructions on the internet, only test by yourself, now write down for future check)
Syntax elect table1.fd1, Table1, fd2, table2.fd2 from Table1 left join Table2 on table1.fd1, table2.fd1 where...
Use SQL statements to display long strings...
Syntax:
SQL database:
Select case when Len (field)> 10 then left (field, 10) + '...'
Else field end as news_name, news_id from tablename
Access Database: Select IIF (LEN (field)> 2, left (field, 2) + '...', field) from tablename;

Conn. Execute description
Execute method this method is used to execute SQL statements. This method is used in the following two formats based on whether the record set is returned after an SQL statement is executed:

1. When an SQL query statement is executed, the query records are returned.
Usage: Set object variable name = connection object. Execute ("SQL query language ")
After the execute method is called, the record set object is automatically created and the query results are stored in the record object,
The set method is used to assign a record set to a specified object for storage. Later, the object variable represents the record set object.

2. When the SQL operator language is executed, no record set is returned. The usage is as follows:
Connection object. Execute "SQL operational statement" [, recordaffected] [, option]
· Recordaffected is optional. A variable can be placed here. After the SQL statement is executed, the number of valid records is automatically saved to the variable.
By accessing this variable, you can know how many records the SQL statement team has performed.
· Option. The value of this parameter is generally adshorttext, which is used to tell ado that the first character after the execute method should be interpreted as the command text.
You can specify this parameter to make execution more efficient.

· Begintrans, rollbacktrans, and committrans Methods
These three methods are the methods provided by the connection object for transaction processing. Begintrans is used to start a transaction; rollbacktrans is used to roll back the transaction;
Committrans is used to submit all the transaction processing results, that is, to confirm the processing of the transaction.
Transaction processing can regard a group of operations as a whole. Transaction processing is successful only after all statements are successfully executed;
If one of the statements fails to be executed, the entire process will fail and the status will be restored.
Begintrans and committrans are used to mark the start and end of a transaction. The statements between them are used as the statement for transaction processing.
To determine whether the transaction is successfully processed, you can use the error set of the connected object. If the number of members in the error set is not 0, an error occurs and the transaction fails to be processed.
Each error object in the error set represents an error message.

Bytes -------------------------------------------------------------------------------------------------

TransAct --- SQL advanced query (lower)
5: Use the having keyword to filter results
6: Use the compute and compute by clauses
7. Use nested Query
8: Distributed Query

E: Use the having keyword to filter the results.
after the query and statistics of the data results are completed, you can use the having keyword to filter query and calculation results in one step.
for example, you can find the number of students with a degree in the work table.
select degree, count (degree) from work group by degree having degree in (\ 'Junior college \ ', \ 'secondary school \')
Note: 1: having keywords are used together with group.
2: Having does not support alias allocation to columns
For example: Select degree, \ 'number of persons greater than 5 \ '= count (education level) from work group by: Number of students having greater than 5> 5 [incorrect]
changed to: select education, \ 'number of students greater than 5 \ '= count (education degree) from work group by education level having count (education level)> 5

F: Use compute and compute
The compute clause allows you to observe the details of the data in each column obtained by the query and to collect statistics on the summary columns generated by the data in each column.
Select * from work [query details of the data in each column]
Compute max (basic salary), min (basic salary) [statistical results]
In this example, the by keyword is not used. The returned result is that the maximum and minimum values of the basic salary of the last row are added, and the by keyword can also be added.
Example: Select * from work order by education
Compute max (basic salary), min (basic salary) by education
Comparison: Select degree, max (basic salary), min (basic salary) from work group by degree
Note: 1: the compute clause must be used together with the order by clause.
2: The compute clause can return multiple result sets. One is a dataset that reflects data details and can be correctly classified according to the classification requirements. The other is a summary of the results generated based on the classification.
3: The group by clause can only produce one result after each type of data is classified, and the details are unknown.

G: Use nested Query
A query is usually used as a condition for another query.
For example, there are work tables and department tables.
A: retrieve basic information about employees of all departments registered in the department table
Select * from work where Department No. In [not in] (select Department No. From DBO. Department)
B: retrieve the employee information for the highest basic salary of each department in the work table
Select * from work a where basic salary = (select max (basic salary) from work B where a. Department name = B. Department name)
Note: An external query provides an internal query with the department name. The internal query uses the Department name to find the highest basic salary of the Department. The external query then checks whether the highest salary is equal to the basic salary, if yes, it is displayed.
Equivalent to: Select * from work, (select Department name, max (basic salary) as basic salary from work group by department name as t) where work. basic salary = T. basic salary and work. department name = T. department name
C: Use a nested work table and a nested Department table to retrieve the employee data that both names and employee numbers exist in the nested department from the nested work table.
Select * From nested work where employee ID in (select employee ID from nested Department) and name in (Select name from nested Department) [view results, analyze reasons]
Change: Select * From nested work a, nested Department B where a. employee ID = B. employee ID and A. Name = B. Name
Change: Select * From nested work where employee ID = (select employee ID from nested Department) and name = (Select name from nested Department) [OK? Why?]

Use the exists keyword [exist] in nesting
Example: 1: Use a nested work table and a nested Department table to retrieve the employee data that both the name and employee number exist in the nested department from the nested work table.
Select * From nested work a where exists (select * From nested Department B where a. Name = B. Name and A. employee ID = B. employee ID)
2: Search for employees in the work table that do not exist in the Department table
Select * from work where not exists (select * from department where department. Department ID = work. Department ID)
Can it be changed to: Select * from work where exists (select * from department where department. Department No. <> Work. Department No)

Use select in the column list
Example: 1: Search for the department name and basic salary of all departments in work1 and department tables.
Select Department name, (select sum (basic salary) from work1 B where a. Department No. = B. Department No.) from department
2: Search the number of employees in each department
Select department no., Department name, (select count (employee No.) from work1 A where a. Department No. = B. Department No.) as number of people from department B
3: query the name, department, and total sales of each employee in the product table and sales table.
Select name, department, (select sum (sales volume) from item sales a where a. employee number = B. employee number) as total sales from nested department B

H: Distributed Query
Our previous queries are only based on one database on one server. If a query is performed across one server, a query like this is a distributed query, then we can see that the distributed query is the data source from the two servers. to perform distributed queries, you must first create a "linked server" to allow local users to map to the process server.
Creation of "linked server"
A: In "linked server", enter the name of the linked server for convenient access [any].
B:ProgramSelect "Microsoft ole db provider for SQL Server" in "name"
C: Enter the Network Name of the server in "Data Source ".
D: Local login. Enter a local login user in the remote user and remote password to map the local SQL Server login to the user on the linked server.
E: Access Method: Format: name of the linked server. Database Name. DBO. Table Name
Linked servers have two features:
1: The linked server cannot delete any images of the linked source server.
2: You can perform insert, updae, and delete operations on the tables linked to the source server through the linked server.

View
1: What is a view?
2: differences between views and queries
3: advantages of a view
4: how to create and manage views
5: how to modify the data of a basic table through a view
6: how to implement data security through views

A: What is a view:
View: Creates a virtual table from one or more basic tables based on your needs.
1: A view is a virtual table. It stores only the definition of the view but does not store the corresponding data.
2: The view collects data from the base table by definition and presents the data to the user only at the moment of opening.

B: differences between views and queries:
Views and queries are made up of SQL statements, which are the same, but views and queries are essentially different:
Their differences are: 1: The difference in storage: View storage is part of the database design, while query is not.
2: Different update restrictions
Note: because the view is from a table, you can use the view to indirectly update the table. You can also use the update statement to update the table, but the view and query update restrictions are different, below we will know that although tables can be indirectly updated through views, there are many restrictions.
3: sorting result: You can sort a table using an SQL statement, but not a view.
For example, if you want to create a view containing the order by clause, can it be successful?

C: advantages of a view:
why do we need to introduce a view to a table? This is because a view has the following advantages:
1: data can be split to simplify the view
views can be defined through select and where, this allows you to split some data in the base table that you don't care about, so that you can focus on the data columns that you care about. further simplify data browsing.
2: Providing logical independence for data
if you define a view for a base table, even if the content of the basic table changes in the future, the data obtained by the "view definition" is not affected.
3: provides automatic Security Protection
the view can grant or revoke access permissions like basic tables.
4: A view can be used to indirectly update a table. Therefore, a view update is a table update.

D: View creation and management
View creation
1: SQL statement
Format: create view name as select statement
try: Create views for one or more tables separately [Because views can come from multiple tables]
2: enterprise Manager
Note: 1: After creating a view, you can use the view like using a basic table.
2: when creating a view, not all select subqueries are available
For example: compute and compute by, order by [Unless used together with top]
3: But during query, still available for select subqueries disabled during creation
4: when creating a view, you must specify a title for a column without a title. [think: can I create a view without using the SELECT statement?]
Delete view:
1: Use the SQL statement: Drop view name
2: enterprise Manager
Note: Unlike deleting a table, deleting a view only deletes the view definition and does not delete the data in the table. [view relevance]
modify view definition
1: Use Enterprise Manager
2: use SQL statement:
Format: alter view name as new SELECT statement

View information: sp_helptext view name [view statements created by view]

E: how to modify the data of a basic table through a view.
1: Use the insert Statement on The View
Inserting data through a view is the same as inserting data directly into a table, but the view is not a basic table after all. Therefore, there are still some restrictions when inserting data.
1: If the view does not contain columns whose basic table attributes are not null [cannot be blank], the insertion Operation will fail because these columns are null values.
2: if some Columns cannot directly accept columns inserted from the view due to certain rules or constraints, insertion will fail.
3: If the view contains the result of using the statistical function or contains a computed column, the insert operation will fail.
4: The value cannot be inserted in the view where the distinct statement is used.
5: values cannot be inserted in views that use the group by statement.

2: Use update to update data in the view
1: update a view is the same as update a table. However, when multiple basic tables are connected in the view, each update operation can only update one data column from the basic table.
For example, create view del
Select employee ID, name, Department name, owner from work1, Department
Where work1. Department No. = Department. Department No.
If you execute the following statement again:
Update del set employee ID = \ '001 \ ', Department name = \ 'wenda \ 'where employee ID = \ '01 \' [Error]
You can only change it to: Update del set employee ID = \ '001 \ 'where employee ID = \ '01 \'
Update del set Department name = \ 'wenda \ 'where employee ID = \ '01 \'
2: The value cannot be updated in the view that uses the distinct statement.
3: values cannot be updated in views that use the group by statement.

3: delete the data in the view.
Deleting data from a view is ultimately reflected in deleting data from a basic table.
Format: delete view name [Where condition]
Note: When a view consists of more than two base tables, the view data cannot be deleted.
For example, to create a view kk
Create view KK
Select employee ID, name, gender, Department name from work1, department where work1. Department No. = Department. Department No. [try to delete]

View with check Option
If you do not know the definition of a view, data that does not conform to the definition of the view is often input into the view.
For example, create view XM
Select * from work where gender = \ 'male \'
Insert XM values (\ '001 \ ', \ 'female \', 23, \ '2018 \'....)
Although it is unreasonable in the sense, the preceding statement is correct. To prevent this situation, you can use the with check option clause to restrict the inserted or changed data.
For example, create view XM
Select * from work where gender = \ 'male' with check Option

Use the schemabinding view [bind to architecture]
We know that a view depends on a table. If you create a view in a table and delete the view in the future, the view will no longer be available. to prevent users from deleting a table referenced by a view, you can add the schemabinding keyword when creating the view.
For example, create view basic salary with schemabinding
As select name, gender, basic salary from DBO. Work
Note: 1: you cannot use "*" to create views of this type.
2: When creating this type of view, you must add DBO. Table name.
3: If this type of view is defined in a table, you cannot modify the structure of the table. Otherwise, these bindings will be deleted.
4: If you change the name of a column in the table structure, the binding is deleted and the view is unavailable.
5: If you modify the column type or size of the table structure, the binding is deleted but the view is available. In this case, you can delete the table referenced by the view.

Use with encryption to encrypt the view
To protect the original code for creating a view definition, You can encrypt the view.
For example, create view KK with encryption
As select * from work where title = \ 'manager \'
Use sp_helptext to check it. Or use the Enterprise Manager to check it.
Note: If this option is applied, you cannot design the view.

F: Use views to enhance data security
Generally, there are three ways to enhance data security by using views.
A: different users are granted different use rights.
B: Use the select clause to restrict access to columns in some underlying base tables.
C: Use the WHERE clause to restrict access to rows in some underlying base tables.
Grant different permissions to different users