Advanced Transact-SQL query (lower)

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: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: 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 view definition Code To 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