I. Create a database, create tables, and add constraints.
1.1 database creation
Copy codeThe Code is as follows:
Use master
Go
If exists (select * from sysdatabases where name = 'mydatabas')-determine whether the database name to be created exists in the master database sys‑bases table
Drop database MyDatabase-if this database name exists in the sysdatabases table, delete it.
Go
Exec xp_mongoshell 'md D:/MyDatabases '-create a folder using the stored procedure to store physical data files (data files, log files), doscommands (mkdir = md)
Go
Create database MyDatabase-create database
On
(
Name = 'mydatabase _ data', -- specifies the logical file name
Filename = 'd:/MyDatabases/MyDatabase_data.mdf ', -- specify the physical file name
Size = 5 mb, -- initial size
Maxsize = 50 mb, -- specifies the maximum physical file capacity, optional
Filegrowth = 20% -- growth volume
)
Log on
(
Name = 'mydatabase _ log', -- specify the logical log File name
Filename = 'd:/MyDatabases/MyDatabase_log.ldf ', -- specify the physical log file name
Size = 5 mb, -- initial size
Maxsize = 50 mb, -- specify the maximum size of the log physical file, optional
Filegrowth = 20% -- growth volume
)
Go
Use MyDatabase
Go
1.2 create a table.
Copy codeThe Code is as follows:
If exists (select * from sysobjects where name = 'mytable ')
Drop table Mytable
Go
Create table Mytable
(
ID int not null identity (1, 1) primary key, -- ID seed 1, ID increment 1, set this column as the primary key
Name nchar (10) not null, -- cannot be blank
Degree numeric ()-ID card, numeric () represents 18 digits, decimal places is 0
)
Go
1.3 add constraints.
Copy codeThe Code is as follows:
Alter table stuInfo add constraint PK_stuNo primary key (stuNo)-primary key
Alter table stuMarks add constraint FK_stuNo foreign key (stuNo) references stuInfo (stuNo)-foreign key
Alter table stuInfo add constraint UQ_stuID unique (stuID)-unique constraint
Alter table stuInfo add constraint df_stuAddress default ('address unknown ') for stuAddres-default constraint
Alter table stuMarks add constraint ck_stuAge check (stuAge between 15 and 40)-check Constraints
Alter table stuMarks drop constraint ck_stuAge-delete Constraints
1.4 account management.
1.4.1 create a Logon account.
Copy codeThe Code is as follows:
Exec sp_grantlogin 'jbtraining/s26301 '-- windows users are jbtraining/s26301, and jbtraining indicates the domain
Exec sp_addlogin 'admin', '000000' -- SQL Logon account, account: 'admin', password: 0533131.
1.4.2 create a database user.
Copy codeThe Code is as follows:
Exec sp_grantdbaccess 'jbtraining/s26301 ', 's26301dbuser' -- s26301dbuser is the database username
Exec sp_grantdbaccess 'admin', 's26301dbuser' -- s26301dbuser indicates the database name.
1.4.3 grant permissions to database users.
Copy codeThe Code is as follows:
/* Assign the select, insert, updata, and delete permissions on the table mytable to s26301dbuser */
Grant select, insert, update, delete on mytable to s26301dbuser
/* Assign table creation permissions to s26301dbuser
Grant create table to s26301dbuser
2. Programming T-SQL
2.1 variables.
The local variable name must be prefixed:
Declare @ name varchar (8) -- Declare a variable.
Declare @ name varchar (8) = value -- Initial value.
Set @ name = value -- value assignment.
Select @ name = value -- assign value.
Global Variables
All global variables in SQL server use two @ signs as the prefix:
Variable |
Description |
@ Error |
Error code for last T-SQL Error |
@ Identity |
ID value of the last insert operation |
@ Language |
Current language name |
@ Max_connections |
Maximum number of simultaneous connections that can be created |
@ Rowcount |
Number of rows affected by the previous SQL statement |
@ Servername |
Name of the local server |
@ Servicename |
Name of the SQL service on the computer |
@ Timeticks |
Number of microseconds per scale on the current computer |
@ Transcount |
Number of transactions opened by the current connection |
@ Version |
SQL server version |
2.2 output statement.
Print local variables or strings to Print data in the form of strings.
Select Local variable AS Custom column name, print data in table form.
2.3 logical control statement.
2.3.1.If-else
If (expression)
Begin
Statement 1
Statement 2
End
Else
Begin
Statement 1
Statement 2
End
2.3.2.case
Case
When condition 1 then result 2
When condition 2 then result 2
End
Iii. Advanced Query
3.1 subquery.
Select... From table 1 where field 1> (subquery)
3.2IN and not in subqueries
Select... From table 1 where field 1 not in (subquery)
Select... From table 2 where field 2 IN (subquery)
3.3Exists and Not Exists subqueries
If exists (subquery)
Statement
If not exists (subquery)
Statement
4. Transactions, indexes, and views.
Transaction 4.1
2. Start transaction: begin transaction
2. commit a transaction: commit transaction
2. rollback transaction: rollback transaction
Transaction category
2. Explicit transaction: Use begin transaction to specify the start of the transaction.
2. Implicit transactions: set the set implicit_transactions on statement to enable the trapped transaction mode. When a transaction is operated by implicit transactions, SQL server starts a new transaction in poker after the transaction is committed or rolled back. The start of a transaction cannot be described. You only need to commit or roll back each transaction.
2. Automatic commit transaction: This is the default mode of SQL server, which treats each individual T-SQL statement as a transaction. If it is successfully executed, it is automatically submitted. If an error occurs, it is automatically rolled back.
Example:
Begin transaction -- start transaction
Copy codeThe Code is as follows:
Declare @ errorsum int
Set @ errorsum = 0
Update ............................
Set @ errorsum = @ errorsum + @ error
Update .............................
Set @ errorsum = @ errorsum + @ error
If @ errorsum <> 0
Begin
Rollback transaction -- roll back the transaction
End
Else
Begin
Commit transaction -- submit a transaction
End
Go
4.2 Index
Indexing is an internal method for SQL server to orchestrate data. It provides SQL server with a way to orchestrate a route for querying data
Index page:
The data page for storing indexes in the database. The index page stores the keyword page of the retrieved data row and the address pointer of the Data row. The index page is similar to the directory page sorted by pinyin or strokes in the Chinese dictionary.
Unique index:
The unique index does not allow two rows to have the same index value.
Primary Key Index:
Defining a primary key for a table in the database relationship diagram will automatically create a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to data.
Clustered index:
In a clustered index, the physical order of each row in the table is the same as that of the key-value Logic (index. A table can contain only one clustered index.
Create an index
Copy codeThe Code is as follows:
If exists (select name from sysindexes where name = 'myindex ')
Drop index table name. myindex
Create nonclustered index myindex
On
Student (id) with fillfactor = 30
Go
Myindex is the index name, with fillfactor = 30, and the specified fill factor is 30%
Use Index
Select * from stumarks (myindex) where writtenExam between 60 and 90
Stumarks indicates the table name, myindex indicates the index name, writtenexam indicates the column name, and between 60 and 90 indicates the value between 60 and 90 in the writtenexam field.
Index creation Conditions
This column is used for frequent searches.
This column is used to sort data.
Disable Indexing
The column contains only a few different values.
It is not cost-effective to create an index for a small table because SQL server takes longer time to search for data in the index than to search for data row by row in the table.
4.3 View
View usage
Filtering rows in a table
Prevent Unauthorized users from accessing sensitive data
Reduce database complexity
Abstract multiple physical data tables into one logical data table
Benefits of using a view
Ü benefits to end users
L The results are easier to understand. When creating a view, you can change the column name to a meaningful name, making it easier for you to understand what the column represents. Modifying the column name in the view does not affect the column name of the base table.
L it is easier to obtain data. Many people do not know much about SQL, so it is difficult for them to create complex queries for multiple tables. Therefore, you can create a view to easily access data in multiple tables.
Ü benefits to developers
L it is easier to restrict data retrieval. Developers sometimes need to hide information in certain rows or columns. By using views, users can flexibly access the data they need and ensure the security of other databases in the same table or other tables. To achieve this goal, you can exclude the user password-preserving column when creating the view.
L easier to maintain applications. Debugging view is easier than debugging query. It is easier to trace errors in each step of a view because all steps are part of the view.
Create View
Copy codeThe Code is as follows:
If exists (select * from sysobjects where name-'myview ')
Drop view myview
Go
Create view myview
As
Select name = stuName, student = sutInfo from stuInfo left join stuMarks
On stuInfo. stuNO = stuMarks. stuNo
Go
Use View
Select * from myview
5. Stored Procedure
5.1 system stored procedures
Common system stored procedures
Sp_datadases: list all databases on the server
Sp_helpdb reports information about a specified database or all databases
Sp_renamedb Change Database Name
Sp_tables: returns the list of objects that can be queried in the current environment.
Sp_columns returns information about a table column.
Sp_help
Sq_helpconstraint
Sq_helpindex
Sq_stored_procedures: list all stored procedures in the current environment
Sp_password: Add or modify the Logon account password
Sp_helptext displays the default value, unencrypted stored procedure, user-defined stored procedure, trigger, or view's actual text
5.2 custom stored procedures
Ø stored procedure without Parameters
Ø stored procedures with input parameters
Ø stored procedures with output parameters
5.2.1 stored procedures without Parameters
Copy codeThe Code is as follows:
Create proc procedureName
As
SQL statement
Go
Call syntax
Exec procedureName
5.2.2 stored procedure with input parameters
Copy codeThe Code is as follows:
Create proc procedureName
@ Number int = default value,
@ N varchar (20)
As
SQL statement
Go
Call Syntax:
Exec procedureName 200, 'lyh'
Exec procedureName @ n = 'lyh'
5.2.3 stored procedure with output parameters
Copy codeThe Code is as follows:
Create proc procedureName
@ Number int output,
@ Name char (20)
As
SQL statement
Set @ number = 1000
Go
Call syntax
Declare @ dd int
Exec procedureName @ dd output, 'lyh'
6. SQL server triggers
What is a trigger:
A trigger is a stored procedure that is automatically executed when a table is inserted, updated, or deleted.
Trigger category
INSERT trigger: triggered when data is inserted into the table, the SQL statement defined by the trigger is automatically executed.
UPDATE trigger: triggered when updating a column or multiple columns in a table, the SQL statement defined by the trigger is automatically executed.
DELETE trigger: triggered when a table record is deleted, the SQL statement defined by the trigger is automatically executed.
Deleted table: used to store copies of rows affected by the DELETE and UPDATE statements. That is, the DELETED or updated record rows are temporarily saved in the Deleted table. When executing the DELETE or UPDATE statement, the row is DELETED from the trigger table and transmitted to the DELETED table. Therefore, we can check whether the DELETED data rows can be DELETED from the DELETED table. If not, you can roll back and undo this operation because the trigger itself is a special transaction unit.
Inserted Table: used to store copies of the rows affected by the INSERT and UPDATE statements. That is, the inserted or updated record rows are temporarily saved in the Inserted Table. When an INSERT or UPDATE statement is executed, the newly added row is added to both the INSERT table and the trigger table. Therefore, we can check whether the INSERTED data meets the business needs from INSERTED. If not, you can report the error message to the user and roll back and cancel the operation.
Define a trigger
Create trigger trigger_name
On tablae_name
[With encryption]
For (insert, update, delete)
As
SQL statement
Go
Trigger_name: name of the trigger. The trigger name must comply with the identifier rules and must be unique in the database ,. You can choose whether to specify the trigger owner name.
Table_name: The table or view on which the trigger is executed
With encryption: encrypts entries in the syscomments table that contain the create trigger statement text. Use with encryption to prevent the trigger from being released as part of the replication.
Create trigger: it must be the first statement in batch processing and can only be applied to one table.
Triggers can only be created in the current database, but triggers can reference external objects of the current database.
