SQL Server 2012 Database notes

Mu class net

• Home
• Actual combat
• Path
• Ape asked
• Notes

PythonNotebooks \ SQL Server 2012 database notes SQL Server 2012 database notes2016-10-25 16:29:33 123 views 0 Reviews

Chapter One initial knowledge of SQL Server2012

1.2. As the latest version of SQL Server, SQL Server 2012 has the following exciting new features.
1, AlwaysOn.
2, Columnstore index.
3. DBA to customize server permissions.
4. Windows Server Core Support.
5, Sequence Objects.
6, PowerView.
7, enhanced audit function.
8. Enhanced PowerShell support.
9, distributed playback (distributed Replay).
10. SQL Azure Enhancements.

1.3 Composition of SQL Server 2012
1.3.1, SQL Server database engine
1.3.2, Analytical Services (analysis Service)
1.3.3, integration Service (Integration Services)
1.3.4, reporting Service (Reporting services)

Chapter II Operation of the database

2.1 Composition of the database
2.1.1, data files
2.1.2, log files

2.2 System Database
2.2.1, master database
2.2.2, model database
2.2.3, msdb database
2.2.4, tempdb database

2.3 Creating a Database
2.3.1, creating a database using Object Explorer
2.3.2, creating a database using Transact-SQL

SQL命令创建CREATE DATABASE [sample_db] ON PRIMARY(NAME = ‘sample_db‘,FILENAME = ‘C:\SS2012Data\sample.mdf‘,SIZE = 5120KB,MAXSIZE = 30MB,FILEGROWT = 5%) LOG ON(NAME = ‘sample_log‘,FILENAME = ‘C:\SQL Server 2012\sample_log.ldf‘,SIZE = 1024KB,MAXSIZE = 8192KB,FILEGROWTH = 10%)GO

2.4 Managing Databases
2.4.1, modifying the database
2.4.2, viewing database information
2.4.3, database renaming
2.4.4, deleting a database

Chapter III

3.1. SQL Server database objects
Database objects are part of a database, and data tables, views, indexes, stored procedures, and triggers are all database objects.

3.2. Create a data table
3.2.1, data type
3.2.2, creating a table using Object Explorer
3.2.3, creating tables using Transact-SQL

3.3. Management Data Sheet
3.3.1, modifying table fields
3.3.2, modifying table constraints
3.3.3, view the information in the table
3.3.4, deleting tables

Fourth. The foundation of Transact-SQL language

4.1. Overview of Transact-SQL
4.1.1, what is Transact-SQL
4.1.2, T-SQL syntax conventions

4.2. How to name identifiers
1. Classification of identifiers
2. Rule identifiers
3. Defining identifiers
4. Rules for identifiers
5. Object naming rules
6. Naming rules for instances

4.3. Constants
4.3.1, numeric constants
4.3.2, String constants
4.3.3, date, and time constants
4.3.4, Symbolic constants

4.4. Variables
4.4.1, global variables
View the version and name of the server
Select @ @version as ' SQL Server version ', @ @servername as ' server name '

4.4.2、局部变量Declear  @MyCounter int;4.4.3、批和脚本

4.5. Operators and expressions
4.5.1, arithmetic operators
4.5.2, Comparison operators
4.5.3, logical operators
4.5.4, join operators
4.5.5, bitwise operators
4.5.6, Operator Precedence
4.5.7, what is an expression
Classification of 4.5.8 and Transact-SQL expressions

4.6, Transact-SQL weapon------wildcard characters
Wildcard characters
Description
Example
Example of matching values
%
Matches any length of character, even including 0 characters
' f%n ' match character preceded by any character F
Fn,fan,faan,abcn

Match any single character
' B' matches a value with a length of two characters beginning with b
Ba,by,bx,bp
[Character Set]
Match any one of the characters in the character set
' [XZ] ' matches x or Z
Dizzy,zebra,x-ray,extra
[^] or [!]
Match any characters that are no longer in parentheses
' [^ABC] ' matches any string that does not contain a, B, or C
Desk,fox,f8ke

4.7. Annotations in the Transact-SQL language

1. Single-line Comment
The single-line comment begins with two minus "--" and is scoped from the beginning of the comment symbol to the end of the line
Example:--create Table Temp
--(ID int promary key,hobby varchar () null)
2, multi-line Comment
Multi-line Annotations Act on a block of code that uses a slash star (/*/), and when used with this annotation, the compiler ignores everything from the beginning (/) until it encounters (/).
For example:
/CREATE TABLE Temp
--(ID int promary key,hobby varchar () NULL) */

Eighth. Rules, defaults, and completeness constraints

8.1. Rules and Default overview
A rule is a constraint on a column of a stored data table or a value in a user-defined data type, which is independent of the table or user-defined data type that it acts on, that is, any action on a table or user-defined data type has no effect on the rules it sets.
8.2, the basic operation of the Rules
8.2.1, creating rules
8.2.2, binding custom rules to columns (defining rules)

8.2.3, Validation rule action (binding rules)

向数据表中添加一条字段score的值为101数据，![图片描述][3]8.2.4、取消规则绑定 （取消规则绑定）![图片描述][4]8.2.5、删除规则 （删除规则）  正在使用的规则是删除不了的![图片描述][5]

8.3, the default basic operation
8.3.1, create default
8.3.2, bind custom defaults to columns
8.3.3, verifying the default effect
8.3.4, canceling default bindings
8.3.5, deleting the default
8.4. Integrity constraints

Nineth. Create and use index
9.1, meaning and characteristics of the index
there are now 20,000 records in the database, and now you want to execute this query: SELECT * FROM table where num = 10000 If there is no index, you must traverse the entire table. Until this line of Num equals 10000 is found, and if an index is created on the NUM column, SQL Server does not need any scanning, and you can find the location of the line by looking at 10000 directly in the index. It can be seen that the establishment of indexes accelerates the query speed of the database.
9.2, Index classification
Different index types are available in different databases, and there are two types of indexes in SQL Server: Clustered and nonclustered indexes. The difference between clustered and nonclustered indexes is in how physical data is stored.
9.3, the design principles of the index
(1) Not as many indexes as possible, if a table has a large number of indexes, not only the disk space will increase, but also affect the performance of the INSERT, delete, update and other statements. The index is also adjusted and updated as the data in the table changes.
(2) Avoid too many indexes on frequently updated tables, and as few columns as possible in the index. For fields that are frequently used for queries, you should create an index, but avoid adding unnecessary fields.
(3) a table with a small amount of data is best not to use an index, and because there is less data, the query may take less time than traversing the index, and the index may not have an optimization effect. The
(4) Indexes the columns that are often used in the conditional expression for a number of different values, and does not index on columns with fewer values. For example, in the "Gender" field of the student table there are only two different values for "male" and "female", so there is no need to index. If you build an index, it will not improve the query efficiency, but can seriously reduce the update speed.
(5) A unique index is developed when uniqueness is a characteristic of the data itself. Using a unique index ensures the data integrity of the defined columns. Improve query speed.
(6) Indexes on columns that are frequently sorted or grouped (that is, group by or order by operations), and if there are multiple columns to be sorted, you can create a composite index on those columns.
9.4, CREATE INDEX
9.5, manage and maintain indexes
9.5.1, display index information
9.5.2, rename index
9.5.3, delete index

Chapter 11th stored procedures and custom functions
11.1. Overview of stored Procedures
A stored procedure is a stored procedure created by the SQL SERVER2012 system that is designed to easily query information from a system table, or to complete administrative tasks or other system administration tasks related to updating a database table. A Transact-SQL statement is a programming interface between a database and an application that is not a 2012. In many cases, some code will be written repeatedly by the developer, if each time to write code to absorb the common that function, not only cumbersome, error-prone, and because SQL Server 2012-by-one execution of the statement will reduce the operational efficiency of the system.
11.2. Classification of stored procedures
11.2.1, System stored procedures
11.2.2, custom stored procedures
11.2.3, extended stored procedures
11.3. Create a stored procedure
11.4. Managing Stored Procedures
11.5. Extended stored Procedures
11.6. Custom Functions

Related tags: sql Server

2 Recommendations CollectionRelated reading

• The data structure and algorithm principle behind MySQL index
• At present, the application of several mainstream database software features, the scope of applicability of what?
• What do you know about the Java connection database? Essay
• Views, indexes, stored procedures, transactions, functions
• MySQL Data sheet has foreign key, should pay attention to a few points (currently learn the experience gained)

the sea breeze under the sunAll reviews 0 article

No Comments

Simpledaisy

Java Development Engineer Life needs Passion 5 Notes 0 Recommended author's top notes

• AngularJS Video Learning note 265 views 2 recommended 1 reviews
• C # LINQ (Language Integrated query) 108 views 1 Recommended 1 reviews

• Website Home
• Enterprise cooperation
• Careers
• Contact Us
• Cooperation Zone
• About Us
• Instructor Recruitment
• Problems
• View Feedback
• Friendship Link

Copyright? imooc.com All Rights Reserved | Beijing ICP 13,046,642th No.-2

SQL Server 2012 Database notes