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: displays the article, Submitter, and last reply time.
Select a. Title, A. username, B. adddate from Table A, (select max (adddate) adddate from table where table. Title = A. Title) B
6. Description: External join query (table name 1: Table A name 2: B)
Select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B on A. A = B. C
7. Description: Online View query (table name 1:)
Select * from (select a, B, c from a) t where T. A> 1;
8. Description: between usage. When between restricts the Data Query range, it includes the boundary value. Not between does not include
Select * From Table1 where time between time1 and time2
Select a, B, c, from Table1 where a not between value 1 and value 2
9. Description: How to Use in
Select * From Table1 where a [not] In ('value 1', 'value 2', 'value 4', 'value 6 ')
10. Description: two associated tables are used to delete information that is not in the secondary table.
Delete from Table1 where not exists (select * From Table2 where table1.field1 = table2.field1)
11. Notes: four table join query problems:
Select * from a left inner join B on. A = B. B right inner join C on. A = C. C inner join D on. A = D. d Where .....
12. Note: Five minutes ahead of schedule reminder
SQL: Select * from Schedule where datediff ('minute ', F Start Time, getdate ()> 5
13. Note: One SQL statement is used to handle database paging.
Select top 10 B. * from (select top 20 primary key field, sorting field from table name order by sorting field DESC),
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, the code starting with the then keyword is executed.
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 querying and counting the data results, you can use the having keyword to filter the query and calculation results in one step.
For example, the number of students with a degree in the work table
Select education, count (education) from work group by education having education in (\ 'Junior college \ ', \ 'secondary school \')
Note: 1: Having keywords are used together with group.
2: Having does not support column alias allocation.
For example: Select degree, \ 'number of students greater than 5 \ '= count (degree) from work group by number of students having greater than 5> 5 [Error]
Changed to: select education, \ 'number of students greater than 5 \ '= count (education level) 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: In "provider name", select "Microsoft ole db provider for SQL Server"
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 the View:
Why should we introduce a view to a table? This is because the view has the following advantages:
1: Split data to simplify Viewpoint
You can use select and where to define views. This allows you to split some data in the data base table that you do not care about, so that you can focus on the data columns you are concerned about. This further simplifies data browsing.
2: Provide 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
A view can grant or revoke access permission like a basic table.
4: A view can be used to update a table indirectly. Therefore, a view update is a table update.
D: create and manage views
View Creation
1: use SQL statements
Format: Create view name as select statement
Try: Create views for one or more tables separately [Because views can come from multiple tables]
2: Use the Enterprise Manager
Note: 1: After creating a view, you can use the view just 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: However, the Select subqueries that are disabled during creation can still be used in queries.
4: when creating a view, you must specify a title for the column without a title. [think: Can you create a view without using the SELECT statement?]
Delete View:
1: use SQL statement: Drop view name
2: Use the 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 the Enterprise Manager
2: 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
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
