SQL Server Database Introduction Learning summary _mssql

A picture of "ten" words: SQL Server Database Summary

A rough summary.

After a period of study, also has some knowledge of the database.
The database is basically made up of tables, relationships, and operations; for beginners to learn first:

1. How the database stores the data
Tables, Constraints, triggers
2. How the database operates data
Insert,update,delete T-SQL function stored procedure triggers
3. How the database displays the data
Select

SQL Server Database Learning summary

1.SQL Foundation
SQL Server2000 installation, configuration, server startup, stop, Enterprise Manager, Query Analyzer
First generation database-net database and hierarchical database, second generation database-relational database
Database (DB), Database management System (DBMS), database system (DBS)
SQL Server 2000 offers different versions: Enterprise, standard, personal, development

Data type in SQL Server: integer: Int,smallint,tinyint,bigint floating-point number: real,float,decimal; binary: binary,varbinary; logic: bit; character: Char, Nchar,varchar,nvarchar text and graphics: text,ntext,image; date and time: datetime,smalldatetime; Currency: Money,smallmoney

Creation and deletion of databases, creation, modification, and deletion of database tables

Data integrity: Entity integrity: Primary Key,unique key,unique index,identity Column; Domain integrity: default,check,foreign key,data Type,rule ; referential integrity: Foreign key,check,triggers,procedure; user-defined integrity: rule,triggers,procedure; All column-level and table-level constraints in Create table

There are 5 kinds of constraints in SQL Server: PRIMARY KEY constraint (Primary key Constraint), DEFAULT constraint (defaults Constraint), check constraint (check Constraint), uniqueness constraint (unique Constraint), FOREIGN KEY constraint (Foreign key Constraint).

Relationship Diagram

The steps of database design: Requirement Analysis, conceptual structure design, logical structure design, database physics design, database implementation, database operation and maintenance

Two relationships between entities: one-to-one (1:1), One-to-many (1:n), Many-to-many (M:N)

Entity Relationship Model--e-R diagram

Database normalization: Simplify the structure of the database to the simplest form; remove redundant columns from the table; Identify all data that depends on other databases.

Database three normal paradigm: the first normal form is the column without repetition; the second paradigm is that non-primary attributes are dependent on the primary key; The third paradigm is that attributes are not dependent on other non-primary attributes

2.SQL Statement
The SQL full name is "Structured Query Language (structured query Language)"

4 Parts of sql:
Data Definition Language DDL (Language) is used to define the structure of the data: Create, Alter, drop.
The Data Control Language (DCL) is used to control the access permission, access permissions, and so on of database components: Grant, revoke.
Data manipulation language DML (manipulation Language) a command used to manipulate data in a database: INSERT, UPDATE, delete.
Data Query Language DQL the command used to query data in a database: SELECT. Language

SQL-Operator: arithmetic, bitwise, comparison, logical, wildcard, string, assignment operator

3. Query
Simple query, using the TOP clause
Query results sorted by
A query where with conditions, using arithmetic expressions, using logical expressions, using the between keyword, using the IN keyword,
Fuzzy query like
Use aggregate functions in queries: SUM (x), AVG (x), Min (x), Max (x), count (x), COUNT (*)
Querying group by,having clauses with groups
Distinct keyword
Column Alias
Select Top 6 * from Sales ORDER BY qty Desc
Select Au_id,au_fname,au_lname from authors where state in (' KS ', ' Ca ', ' mi ')
Select Au_fname,au_lname,phone from authors where au_id like ' 72[234]-% '
Select Type,sum (Price), avg ("Price"), COUNT (*) from the titles group by type having type in (' Business ', ' psycheology ')


Simple subqueries: Nested subqueries, correlated subqueries, and the ORDER BY clause cannot be used in a SELECT statement of a subquery, and the Roder by clause can only sort the final query results.
Nested subqueries: Executes the procedure, executes the subquery, the subquery obtains the result not to be displayed, but passes to the outer query, acts as the outer query's condition, then executes the outer query, and displays the result.
The execution of a nested subquery does not depend on the outer query, and the subquery executes only once.
Subqueries with comparison operators, subqueries with in and not in, subqueries with any or all
Related subqueries: Subqueries are executed once for each row of the outer query, and the outer query passes the values of the columns referenced by the subquery to the subquery.
The execution of the correlated subquery relies on the outer query, and the subquery needs to be executed repeatedly.
Dependent subqueries with exists and not exists.
Multiple table join query: INNER JOIN (inner join), outer join (left, right, full) outer join), self-join (self join), and Cross join (cross join)
Create a new table on a query: the SELECT INTO statement first creates a new table, and then populates the new table with the results of the query.
Table Aliases
Select Coursename from Course where CourseID to (select distinct CourseID from grade where grade>10)
Select Studname from student where Sudbirthday > No (select Studbirthday from student where class = ' information System ') and CLASS&L T;> ' Information system '
Select Studname from student where exists (SELECT * from grade where Studid = Student.studid and CourseID = ' 01 ')
Select stud1.* from student as STUD1 join student as STUD2 on stud2.studname = ' mm ' and stud1.studsex = Stud2.studsex
SELECT * into girls from student where studsex= ' m '

4. Views, indexes, and transactions
A view is a virtual table or query table that is exported by one or more data tables (basic tables) and is an important mechanism for a relational database system to provide users with a variety of perspectives for observing data in a database.
The benefits of a view: the ability to simplify the user's actions; Views provide security for confidential data.
When you create a view, the name of the view exists in the sysobjects table. Information about the columns defined in the view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table. In addition, the text of the CREATE VIEW statement is added to the syscomments table.
When inserting data into a table through a view, this is not allowed if the INSERT statement list contains columns that are not selected in the view and columns that are not allowed to be null.
Creating view: Create View View_employee as select Emp_id,fname,lname from employee
Working with Views: select * FROM View_employee
Modify view: Alter View View_employee AS select emp_id,fname,job_id from employee where job_id>10
Delete view: Drop Veiw View_employee
View structure: EXEC sp_help view_employee
View definition information: Exec sp_helptext ' View_employee '

An index provides a way to quickly access a table's data rows based on one or more columns of values. The index provides the logical order of the tables.
The clustered index sorts and stores the data rows in the table based on the key values of the data rows. When a data table establishes a clustered index with a column keyword, the data rows in the table are stored in the sort order of the columns (clustered index keys). There can be only one clustered index per table.
A nonclustered index has a completely separate structure from the data row, and a table can establish multiple nonclustered indexes.
Create a clustered index: Create clustered index studid_ind on stud (Studid)
Create a nonclustered index: create unique index studfullname_ind on stud (fname desc,lname)
Deleting indexes: Drop INDEX Stud.studid_ind
To view the index on the stud table: EXEC sp_helpindex Stud

A transaction is a mechanism, a sequence of operations, that contains a set of database operation commands, and All Commands act as a whole to submit or revoke an action request to the system.
Attributes of the transaction: atomicity (atomicity), consistency (consistenty), isolation (isolation), permanent (durability).
Transaction classification: Displays transactions, hidden transactions, autocommit transactions.

Creation, use, modification, and deletion of views, indexes, and transactions

5.transact-sql Programming
Global variables: defined and maintained by the system with the name beginning with the @@ 字符
Local variables: defined and assigned by the user with the name beginning at the @ character
Output statement: Print
Logic control statement: begin...end; break, case; continue; goto; If...else; return; While
Common functions: Rowset functions, aggregate functions, scalar functions
Convert function: Convert (dt,e,s), cast ()
Math function: Absolute ABS (n), take the whole ceiling (n) up, take the whole floor (n) down, assign the Power (n,y), round Round (n,length), find the symbol sign (n), square root sqrt (n)
Date and Time functions: DateAdd (Datepart,num,date), DateDiff (Datepart,date1,date2), Datename (datepart,date), DATEPART (datepart, Date), GETDATE (), Year (date), month (date), Day (date)
String functions: Lower (e), Upper (E), left (E,i), right (E,i), replace (S1,S2,S3) Repeat the specified number of times in the 2,replicate (e,i) of 3, Stuff (S1,start, LENGTH,S2) with 2 replaces the specified position in 1, substring (expression,start,length)
Meta-data functions: db_id (' database_name '), db_name (datebase_id), object_id (' Obj_name '), object_name (obj_id), col_length (' table ') , ' column '), Col_name (table_id,col_id)
Aggregate functions: AVG (expr), count (expr), COUNT (*), Max (expr), min (expr), sum (expr)
Select au_lname,au_fname,contory =
Case State
When ' ut ' Then ' Utah '
When ' CA ' Then ' California '
Else ' world '
End,city from authors, DESC

while (select Avg. (price) from titles) <30
Begin
Update titles Set price = Price * 2
if (select Max (price) from titles) >50 break
else continue
End
print ' Price is too high '

Begin
Insert into jobs values (' a ', 80,234)
If @ @error <>0 print ' Data insert failed '
Else Goto M
End
M:print ' Data insert Success '

6. Cursors
A cursor is a mechanism that extracts one record at a time from a result set that contains more than one data record. The batch operation becomes a row operation, and a row is made to the result set.
Declare AUTHOR_CSR cursor READ_ONLY for--defines a read-only cursor
Select Au_fname,au_lname from authors where State = ' CA ' ORDER by au_fname,au_lname
DECLARE @lname varchar, @fname varchar (20)--Define variable
Open AUTHOR_CSR--opening cursors
FETCH NEXT from AUTHOR_CSR into @lname, @fname--Perform a data read operation
While @ @fetch_status = 0--circular cursor reads data
Begin
print ' Author name: ' + @lname + ' + @fname
FETCH NEXT from AUTHOR_CSR into @lname, @fname
End
Close AUTHOR_CSR--closing cursors
Deallocate AUTHOR_CSR--Releasing cursors

7. Stored Procedures
A stored procedure (stored procedure), similar to a function in C, is a set of SQL statements that are compiled and stored in a database in order to complete a specific function. The user executes the parameter by specifying a name cookie for the stored procedure.
Common system stored procedures: SP_DATABASE,SP_HELPDB,SP_RENAMEDB,SP_TABLES,SP_COLUMN,SP_HELP,SP_HELPCONSTRAINT,SP_HELPINDEX,SP_ Stored_procedure,sp_password
To create a stored procedure:
CREATE PROCEDURE Book_num (@book_name varchar, @starttime datetime, @endtime datetime, @total int output)
As
Select @total =count (jy.askbookid) from Book,jyls JY where bookname like @book_name and BOOK.ISBN=JY.ISBN and jy.starttime& gt;= @starttime and endtime<= @endtime
Using Stored procedures:
Declare @book_name char (), @total int
Set @book_name = ' object-oriented analysis and Design '
exec book_num @book_name, ' 2007-01-01 ', ' 2007-11-01 ', @total output
Select @book_name as BookName, @total as num

8. Triggers
A trigger is a special type of stored procedure that is executed primarily by triggering in practice.
The main function of a trigger is the ability to achieve complex referential integrity and data consistency that cannot be guaranteed by primary and foreign keys. Other features: Enhanced constraints, tracking changes, cascading runs, stored procedure calls.
SQL Server 2000 supports two types of triggers:
After triggers: A trigger is required to be executed only after an action is performed and can only be defined on the table.
Instead OF triggers: represents an action that does not perform its defined actions, but only executes the trigger itself. Can be defined either on a table or on a view, but only one instead of trigger can be defined for the same operation.

working principle:
When the insert trigger is triggered, the new data row is inserted into the trigger table and the inserted table. Triggers check the inserted table to determine whether or how to execute a trigger action.
When an UPDATE statement is executed on a table that has a trigger defined, the original row is moved into the deleted table, and the update row is moved into the inserted table. Triggers check deleted tables and inserted tables and updated tables to determine whether multiple rows have been updated and how trigger actions are executed.
When the deleted trigger is triggered, rows deleted from the affected table are placed in a special deleted table.

Create trigger Update_smoke_t_sale on Smoke_t_sale for update
As
declare @newsalenum int, @smokeproductname varchar (40)
Select @newsalenum = Salenum from inserted
Select @smokeproductname =smokeproductname from inserted
If Update (Salenum)--to determine whether to update
Begin
Update Smoke_t_sale Set saletotalprice= @newsalenum * Saleprice where smokeproductname= @smokeproductname
Insert into Smoke_log (logcontent) VALUES (' Update successful ')
End
Else
print ' not updated '

9. Advanced Database Management
SQL Server security architecture, 4 levels: Client operating system security, SQL Server login security, database security, use of data Objects security
SQL Server Authentication mode: Windows Authentication mode and mixed mode (Windows authentication and SQL Server Authentication)
Login account: User login (Connect) account and password of SQL Server server.
Role management: A server role (the group responsible for managing and maintaining SQL Server); database roles (a collection of user accounts and groups with the same access to a database)
Database User: For each login account that requires access to the database, the access account for the database must be established in the database to be accessed and associated with the login account link to access the database.
Rights Management: refers to whether the user can access the database resources of the appropriate operation. Permissions include: statement permissions, object permissions, and implied permissions.

Grant permissions:
Grant statement [...] to security account [...]
Grant permission [...] on table or view [(column [,...])]| On stored procedures |on user-defined functions to security account [, ...]
Deny permission:
Deny statement [...] to security account [...]
Deny permission [...] on table or view [(column [,...])]| On stored procedures |on user-defined functions to security account [, ...]
Revoke permissions:
Revoke statement [...] from security account [...]
Revoke permissions [...] on table or view [(column [,...])]| On stored procedures |on user-defined functions from security account [, ...]

Backup and restore:
Database backup device, you first create a backup device before you make a database backup. Includes: disk, tape, and named pipes
SQL Server Backup strategy: Backs up only databases, backs up databases and transaction logs, and differential backups.
Backup database medicaldb to disk= ' MEDICAL_BK1 ' with name= ' medicaldb backup ' description= ' medicaldb fullbackup ' init
Restore Database Medicaldb from MEDICAL_BK1

Import Export:
The DTS (Data Transformation Service) is the data transfer services provided by SQL Server. Enables users to extract, transform, and merge data from disparate data sources into single or multiple purposes.

Detach and attach: Create a removable database.