A. Build a library, build a table, and add constraints.
1.1 Building the Library
Copy Code code as follows:
Use master
Go
if exists (SELECT * from sysdatabases where name= ' MyDatabase ')-Determine if there is a database name that will be created in the master database sysdatagbases table
Drop database mydatabase-If the name exists in the sysdatabases table, delete it
Go
EXEC xp_cmdshell ' MD d:/mydatabases '-Create a folder using stored procedures to store data physical files (data files, log files), DOS commands (MKDIR=MD)
Go
Create database mydatabase-Creating databases
On
(
Name= ' Mydatabase_data ',--specify a logical filename
Filename= ' d:/mydatabases/mydatabase_data.mdf ',--specify physical file name
SIZE=5MB,--Initial Size
MAXSIZE=50MB,--Specify the maximum capacity of the physical file, optional
filegrowth=20%--Growth rate
)
Log on
(
Name= ' Mydatabase_log ',--specify the logical log file name
Filename= ' d:/mydatabases/mydatabase_log.ldf ',--specify log physical file name
SIZE=5MB,--Initial Size
MAXSIZE=50MB,--Specify the maximum capacity of the log physical file, optional
filegrowth=20%--Growth rate
)
Go
Use MyDatabase
Go
1.2 Build the table.
Copy Code code 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,--identify seed 1, identify increment 1, set this column as primary key
Name NCHAR (a) NOT null,--cannot be empty
degree numeric (18,0)-Identity card, numeric (18,0) represents a 18-digit number with a scale of 0
)
Go
1.3 Plus constraint.
Copy Code code 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-defaults constraint
ALTER TABLE Stumarks add constraint ck_stuage check (stuage between and 40)-CHECK constraints
ALTER TABLE Stumarks drop constraint ck_stuage-Delete constraint
1.4 Account management.
1.4.1 Create a login account.
Copy Code code as follows:
exec sp_grantlogin ' jbtraining/s26301 '--windows user as jbtraining/s26301,jbtraining representation domain
exec sp_addlogin ' admin ', ' 0533131 '--sql login account, account: ' admin ', Password: 0533131.
1.4.2 Create a database user.
Copy Code code as follows:
exec sp_grantdbaccess ' jbtraining/s26301 ', ' S26301dbuser '--s26301dbuser for database username
exec sp_grantdbaccess ' admin ', ' s26301dbuser '--s26301dbuser for database name
1.4.3 is licensed to the database user.
Copy Code code as follows:
/* Assign Select,insert,updata,delete permissions to table MyTable for S26301dbuser. *
Grant Select,insert,update,delete on MyTable to S26301dbuser
/* Assign permissions to create tables for s26301dbuser users
Grant CREATE TABLE to S26301dbuser
two. T-SQL Programming
2.1 Variables.
The name of a local variable must be prefixed with a mark @:
Declare @name varchar (8)--Declares a variable.
Declare @name varchar (8) =value--initial value.
Set @name =value--Assignment.
Select @name =value--Assignment.
Global variables
All global variables in SQL Server are prefixed with two @ flags:
Variable |
Meaning |
@ @error |
The error number of the last T-SQL error |
@ @identity |
The identity value that was last inserted |
@ @language |
Language name currently in use |
@ @max_connections |
You can create the maximum number of simultaneous connections |
@ @rowcount |
Number of rows affected by the previous SQL statement |
@ @servername |
The name of the local server |
@ @servicename |
The name of the SQL service on this computer |
@ @timeticks |
The number of microseconds per tick on the current computer |
@ @transcount |
The number of transactions opened by the current connection |
@ @version |
Version information for SQL Server |
2.2 Output statement.
Print local variable or string that prints the data as a string.
Select local variable as custom column name to print data in tabular form.
2.3 Logical control statements.
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 one then result two
When condition two then result two
End
three. Advanced Query
3.1 Subqueries.
Select ... from table 1 where field 1> (subquery)
3.2IN and not in subquery
Select ... from table 1 where field one not in (subquery)
Select ... from table 2 where field two in (subquery)
3.3Exists and NOT EXISTS subqueries
IF exists (subquery)
Statement
If NOT EXISTS (subquery)
Statement
Four. transactions, indexes, and views.
4.1 Business
² Start transaction: BEGIN TRANSACTION
² Commit a transaction: Commit Transaction
² ROLLBACK TRANSACTION: ROLLBACK TRANSACTION
Transaction classification
² Explicit Transaction: Explicitly specify the start of a transaction with the BEGIN transaction.
² Implicit transaction: Sets the trap transaction mode to open by setting the SET IMPLICIT_TRANSACTIONS on statement. When you operate with an implicit transaction, SQL Server starts a new transaction at poker after committing or rolling back the transaction. Cannot describe the start of a transaction, just commit or roll back each transaction.
² autocommit Transaction: This is the default mode for SQL Server, which treats each individual T-SQL statement as a transaction. If executed successfully, it is submitted automatically. If the error occurs, it is automatically rolled back.
Example:
Begin transaction--Start a transaction
Copy Code code as follows:
Declare @errorsum int
Set @errorsum =0
Update .......... ...........
Set @errorsum = @errorsum +@ @error
Update ......... .............
Set @errorsum = @errorsum +@ @error
If @errorsum <>0
Begin
Rollback transaction--ROLLBACK TRANSACTION
End
Else
Begin
Commit transaction--Commit a transaction
End
Go
4.2 Index
An index is an internal method that SQL Server formats data. It provides a way for SQL Server to orchestrate the routing of Query data
Index page:
The data page in the database where the index is stored. The index page holds the keyword page that retrieves the data row and the address pointer of the data row. An index page is similar to a catalog page in a Chinese dictionary sorted by phonetic or stroke.
Unique index:
A unique index does not allow two rows to have the same index value.
Primary KEY index:
Defining a primary key for a table in a database diagram automatically creates 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 you use a primary key index in a query, it also allows you to access data quickly.
Clustered index:
In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index.
Create an index
Copy Code code 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, specifying a fill factor of 30%
Using indexes
Select * from Stumarks (myindex) where Writtenexam between 90
Stumarks is the table name, Myindex is the index name, Writtenexam is the column name, between and 90 specifies the value between 60 and 90 of the query out of the Writtenexam field
Criteria for indexing
Ø This column is used for frequent searches
Ø This column is used to sort the data
Prohibit the use of indexes
Ø The column contains only a few different values
Ø the data in the table contains only a few rows, and it may not be cost-effective to create indexes for small tables, because SQL Server spends more time searching for data in the index than it does on a table-by-row search
4.3 view
The usefulness of the view
Ø filter the rows in the table
Ø prevent unauthorized users from accessing sensitive data
Ø reduce the complexity of the database
Ø to abstract multiple physical data tables into one logical datasheet
Benefits of using views
• Benefits to end users
L The results are much easier to understand. When you create a view, you can change the column name to a meaningful name, making it easier for the user to understand what the column represents. Modifying a column name in a view does not affect the column name of the base table
• Easier access to data. Many people don't know much about SQL, so it's difficult for them to create complex queries for multiple tables. You can then create a view to make it easier for users to access data from multiple tables.
U Benefits for Developers
L Restricting data retrieval is easier. Developers sometimes need to hide the information in some rows or columns. By using views, users have the flexibility to access the data they need while ensuring the security of other databases in the same table or other tables. To do this, you can exclude columns that will be password-guaranteed when you create a view.
• More convenient maintenance of applications. Debugging a view is easier than debugging a query. It is easier to track errors in the steps of a procedure in a view because all of the steps are part of the view.
Create a View
Copy Code code 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
Working with views
Select * from MyView
Five. Stored Procedures
5. 1 System stored Procedures
Common system stored procedures
Sp_datadases lists all databases on the server
sp_helpdb report information about a specified database or all databases
Sp_renamedb change the name of the database
Sp_tables returns a list of objects that can be queried in the current environment
Sp_columns returns information for a table column
sp_help View all information for a table
Sq_helpconstraint view constraints on a table
Sq_helpindex View the index of a table
Sq_stored_procedures lists all stored procedures in the current environment
Sp_password Add or modify the password for the login account
SP_HELPTEXT Displays the default values, unencrypted stored procedures, user-defined stored procedures, triggers, or the actual text of a view
5. 2 Custom Stored Procedures
Ø no parameter stored procedures
Ø stored procedures with input parameters
Ø stored procedures with output parameters
5. 2. 1 stored procedures with no parameters
Copy Code code as follows:
Create proc ProcedureName
As
SQL statement
Go
Calling syntax
Exec procedurename
5. 2. 2 stored procedures with input parameters
Copy Code code as follows:
Create proc ProcedureName
@number int = default value,
@n varchar (20)
As
SQL statement
Go
Call Syntax:
Exec procedurename, ' lyh '
Exec procedurename @n= ' lyh '
5. 2. 3 stored procedures with output parameters
Copy Code code as follows:
Create proc ProcedureName
@number int OUTPUT,
@name Char (20)
As
SQL statement
Set @number =1000
Go
Calling syntax
Declare @dd int
Exec procedurename @dd output, ' lyh '
six. SQL Server triggers
What is a trigger:
Triggers are stored procedures that are automatically executed when an INSERT, update, or delete operation is made to a table.
Categories of triggers
Insert trigger: Fires when data is inserted into a table, automatically executing the SQL statement defined by the trigger.
Update triggers: Automatically executes the SQL statement defined by the trigger when a column or columns in the table are updated.
Delete trigger: Fires when a record in a table is deleted, and the SQL statement defined by the trigger is automatically executed.
Deleted table: Used to store a copy of the row affected by the Delete and UPDATE statements, that is, to temporarily save the row of records before it is deleted or updated in the deleted table. In the execution of the delete or UPDATE statement, the row is removed from the trigger table and transferred to the deleted table. This allows us to check whether deleted rows can be deleted from the deleted table. If not, you can rollback the undo operation because the trigger itself is a special transaction unit.
Inserted table: Used to store a copy of the rows affected by the INSERT and UPDATE statements, that is, the row of records that was inserted or updated is temporarily saved in the inserted table. When you execute an INSERT or UPDATE statement, the new row is added to both the Insert table and the trigger table. This allows us to check whether the insert data meets the business requirements from the inserted. If not, you can report an error message to the user and roll back the undo operation.
Defining triggers
Create Trigger Trigger_name
On Tablae_name
[WITH Encryption]
for (Insert,update,delete)
As
SQL statement
Go
Trigger_name: Is the name of the trigger. Trigger names must conform to the rules for identifiers and must be unique in the database. You can choose whether to specify the trigger owner name.
TABLE_NAME: is the table or view on which the trigger is executed
With encryption: Encrypts an entry in the syscomments table containing the text of the CREATE TRIGGER statement. Using the WITH encryption prevents the trigger from being published as part of SQL Server replication.
Create Trigger: Must be the first statement in the batch and can only be applied to one table.
Triggers can only be created in the current database, but triggers may reference external objects of the current database.
