Beginning SQL Server 2008 R2 summary
The reference room for learning is the Chinese version of SQL Server 2008. From entry to mastery, the whole learning idea is also to learn according to the idea of the text. But not C#, and JAVA. The latter two examples did not do it.
1, SQL Server quick start
1. Basic concepts
(1) Watch
A table is a database object that contains all the data in a database. It is composed of rows and columns and is used to organize and store data.
(2) Field
Each column in the table is called a field, which has its own attributes, such as field type, field size, etc. Among them, the segment type is the most important attribute of the field, which determines which data the field can store.
The SQL specification supports five basic field types: character type, text type, numeric type, logical type and date / time type.
(3) Index
An index is a separate, physical database structure. It depends on the table. Indexing in the database enables the database program to find the required data without scanning the whole table.
(4) View
View is a table exported from one or more tables (also known as virtual table). It is a way for users to view the data in the data table. The table includes several defined data columns and rows, and its structure and data are based on the query of the table.
(5) Stored procedure
Stored Procedure is a set of SQL statements (including operations such as query, insert, delete and update) to complete specific functions. After compilation, it is stored in the database on the SQL Server side in the form of name, which is executed by the user by specifying the name of the Stored Procedure. When the Stored Procedure is called for execution, these operations will also be executed at the same time
2. Database files and basic databases
In a SQL Server 2008 database, the following three types of files can be used to store information.
(1) Master data file
The master data file contains the startup information of the database and points to other files in the database. User data and objects can be stored in this file or in a secondary data file. Each database can only have one master data file, and the default file extension is mdf
(2) Auxiliary data file
Auxiliary data files are optional, user-defined and store user data. By placing each file on a different disk drive, auxiliary files can be used to spread data across multiple disks. In addition, if the database exceeds the maximum limit of a single Windows file, you can use auxiliary data files so that the database can continue to grow. The default file extension for auxiliary data files is ndf
(3) Transaction log file
The transaction log file holds the log information used to recover the database. Each database must have at least one log file, and its default file extension is. ldf
Basic database
2, Database creation
1. Create a database.
Open SQL Server Management Studio (database manager) - right click database - new database - enter database name - OK - creation succeeded.
2. Table creation
Open the database - open the database you want to create (usually the database you created yourself, do not touch the four basic databases) - right click the table - create a new table - enter the column name and data type you want - right click the header after entering - enter the table name and click OK - create successfully.
3. Delete libraries and tables
Right click the library or table to delete - click delete - click OK.
You can also delete with a statement, but there will be no confirmation message for this statement deletion. That is, the legendary database deletion and running (no, most companies backup data regularly. Unless the whole database is deleted, I advise you to run faster.)
4. Other database operations. (not very useful, except for backing up and restoring the database)
5. Data type
3, Transact SQL statement
1. SELECT get simple data
1.1 SELECT syntax
USE Library management system \\Library name SELECT series,title \\Search column names FROM Book inventory information \\Table name of the search column WHERE series='Economics' \\Conditional search statement
Original table information
WHERE statement
1.2 optional statements
USE The educational administration management system management system SELECT * FROM Transcript ORDER BY achievement DESC
2. Keywords
2.1DISTINCT
2.2 TOP keyword
Returns the number of rows of the result, from top to bottom.
USE Library management system SELECT TOP 10 series,title FROM Book inventory information
3. Insert data.
USE Library management system
INSERT INTO Book inventory information VALUES('A','Sociology','Convince others with eloquence',10,5)
Edit the inserted data information according to the column name order of the table you want to insert.
USE The educational administration management system management system UPDATE Teacher information form SET working years=working years+1
4, Advanced operations
After declaring the cursor, you can operate on the cursor. It mainly opens the cursor, retrieves the cursor specific row, closes the cursor and releases the cursor. DECLARE cursor_name CURSOR //Set up a tour label FOR SELECT * FROM Table name //Table name OPEN Tour label //Open tag name FETCH NEXT FROM Tea_cursor //Retrieve cursor specific rows WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Tea_cursor END CLOSE Tea_cursor //Close cursor DEALLOCATE Tea_cursor //Release cursor ```      1,Create stored procedure   2,Using stored procedure parameters                                  There is a problem here. When I use the manager to create a backup device, the creation is normal. When I open the backup device of the server object, it prompts me that I can't open the system file. But I use it T-SQL It is normal to create a statement and then open it. I see that others are normal, only mine SQL There's a problem. I don't know if it's a problem with the old version of the database.       Here's the last one SQL Server 2008 From introduction to proficient Chinese version PDF Information. Link: https://pan.baidu.com/s/1Lu76FsWLevQipuucimeDSA Extraction code: u6pw