SQL Database Knowledge Point Review

SQL2008

First, the installation precautions:

1. Modify user rights (seven or eight in one installation page) (to net.)
2. Add current system user as Account

Second, primary key, constraint, index

Third, increase the deletion check;

Insert, DELETE, update

select__* or column name _____from table where_______ sort grouping
Check each article, match the conditions of the display

Statement execution procedure:
First find table, filter row end, sort, filter column

First executes the from table, and finally executes the select_______.

Aggregate function: The result set of the final query, for aggregation operations. In the middle of select and from, no more columns can be written after the aggregation function is used. The aggregate function returns a row of columns.
Count (*), Sum (column name), AVG, Max, Min
Select SUM (degree) (Cannot add other columns name, etc.) from student where birthday=1990

Mathematical function: In the middle of select and from or after.
Round,ceiling,floor,abs,sqrt
Select Ceiling (degree) from student where degree>95

datetime function: In the middle of select and from or after
DateAdd

String function: Substr,ltrim,rtrim

Type conversion: Cast,convert
Cast (column as type)
Convert (class, column)

Enquiry
subqueries (nested queries) query a query for a set of column data as a filter for the current query

Two tables teacher Student

Look up all the students a teacher teaches
SELECT * FROM student where tno=001
Check all the students taught by all the teachers in a class.
SELECT * FROM student where Tno in (select Tno from Teacher where cno=001)

Connection query (simultaneous query of multiple tables): Horizontal queries (tables A and B) use where or join to join tables, using where (Cartesian product) to occupy high memory

SELECT * from Student Join Teacher on S.tno=t.tno
(No same name to use *)

Vertical connection:
Select Name,sex from Student
Union
Select Name,sex from Teacher
Note that the column data types of the two tables are consistent, the order is random, and the column name of the query result is the column name of student.

Iv. Stored Procedures

Create proc_______
Parameters
as
Process
Go
Exec parameters

Declare: Defining Variables
If Else Begin End
For

Alter Delete

V. views

Virtual tables for easier and more intuitive display of certain requirements data (based on a compact, easy-to-design database)

creat View
as
Query statements, table joins
Go

VI. triggers

Trigger, an operation is executed, triggering the contents of a trigger, a special stored procedure (not performed by exec, by the operation of the database (pruning) to trigger execution)

are divided into two types:
for After
Instead of replacing the action content, executing the trigger content.

Application:
Cascade Delete: Teacher in student refers to teacher in the teacher table.
Auto Add: Employee table new Employee, automatically add a user name and password to the user table

Vii. Business:

Begin Tran
...
Commit (all steps no problem, unified commit)
RollBack (rollback: Any step with a problem, restore the starting state, as if nothing happened)

Example: Bank transfer

SQL: Stored procedures, transactions, triggers are not commonly used (easy to issue, data migration is inconvenient)

SQL Database Knowledge Point Review