SQL Server Summary

1. Database Management System (DBMS) is a system software that consists of an interconnected data set and a group of program structures that access data.

2. In the database system, data duplication is caused by data redundancy.

3. The databases in SQL Server can be divided into two types by purpose (System database and user database)

4. SQL Server System database:

(1) master database: records all system-level information of the SQL server system. (1. all logon accounts and system configuration settings. 2. location of all other databases and database files. 3. SQL server initialization information .)

(2) tempdb Database: stores all temporary tables and temporary stored procedures, as well as temporary worksheets.

(3) model database: templates of all databases created on the system.

(4) MSDB Database: used for SQL Server Agent to schedule alarms, jobs, and record operations.

5. access restriction: Specify which users can access the database. There are three possible values:

(1) Multiple: the normal data status, allowing multiple user colleagues to access the database.

(2) Single: used to maintain the operation status. Only one user can access the database at a time.

(3) restricted: only the administrator role or a specific member can use the database.

6. When SQL Server is started, database files cannot be copied, pasted, or moved.

7. Data Integrity

(1) Object Integrity: each row of data in the table must reflect different entities and cannot have the same data (index, unique constraint, primary key constraint, or column attribute identification)

(2) domain integrity: the input validity of a given column (restrict data type, check constraints, input format, foreign key constraints, default values, and non-null constraints)

(3) integrity of reference: When you input or delete data rows, reference the complete constraints to save the relationships defined by the table sword (the reference relationships between the primary key and the foreign key are implemented ).

(4) custom Integrity: define specific rules (Database Rules, stored procedures, trigger objects for constraints)

8. T-SQL supports logical operators: And, or, and not.

9. insert data rows Using Insert

(1) Insert a single row of data

Syntax: insert [into] <Table Name> [values] <Value List>

Example: insert into students (name, age, sex) values ('Harry ', 20, 'male ')

(2) Insert multiple rows of data

Syntax: insert <Table Name> [values] Select <Value List> Union select <Value List> example: insert students (name, age, sex) Select 'zhang san', 20, 'male' unionselect 'lily', 18, 'female'

(3) Use the insert SELECT statement to add data from an existing table to a new table.

Syntax: insert [into] <Table 1> <values> select vaules from <Table 2> example: insert into student1 (name, age, gender) Select name, age, sex from student2

10. Update Data

Syntax: Update <Table Name> set <column name = Update value> [where <update condition>] example: Update student set sex = 'male' where studentid = 2

11. delete data

Syntax: delete from <Table Name> [where <deletion condition>] example: delete from student where studentid = 2

12. Use turncate table to delete all row data in the table

Syntax: turncate table <Table Name> example: turncate table student

13. Simple query statement

Example 1: Select * from students Example 2: Select studentid, name, age, sex from students Example 3: Select studentid, name, age, sex from students where studentid = 10 example 4: select name from students where sex is null Example 5: Select name = Name, age = Age, Gender = sex 'hebei xinlong' as school name Example 6: Select top 5 name, age, sex from students where objectid = 3 order by score DESC (or ASC Ascending Order)

14. Use like for fuzzy search

Example: Select * from students where name like 'sheet %'

15. Use between to query within a certain range

Example: selecr * from student where score between 60 and 80

16. Aggregate functions

Sum: Sum AVG: average max: Maximum min: Minimum count: calculate the number of rows in non-empty rows

17. query groups

(1) Use group by for grouping query examples: Select courseid, AVG (score) as average course score from score group by courseid (2) use having clause for filtering examples: Select courseid, AVG (score) as average course score from score group by courseid having

18. Where ---> group by ---> having

19. Multi-table join query

(1) Inner join: inner join (2) Outer Join (1) left Outer Join: left join or left Outer Join (2) Right Outer Join or right Outer Join (3) complete outer join: Full join or full outer join

20. Database Design Steps

(1) Collect information

(2) identify an object

(3) Identify the details (attributes) to be stored for each object)

(4) identify the relationship between entities

21. Database 3 paradigm

(1) 1nf: The Column cannot be further divided

(2) 2nf: Each table describes only one thing.

(3) third paradigm (3nf): No transfer dependency

A T-SQL statement

1. Use T-SQL statements to create and delete a database

Create Database database name on [primary] (<data file parameter> [,...... n] [<file group parameters>]) [Log On] ({<Log File parameters> [,...... n]}) the specific parameters of the file are as follows: Name = logical file name filename = physical file name size = file size maxsize = maximum capacity filegrowth = growth example: -- call the CMD command to create the folder exec sp_configure 'show advanced options', 1 goreconfiguregoexec sp_configure 'xp _ cmdshell', 1 goreconfiguregoexec xp_cmdshell' mkdir E: \ Project 'goif exists (select * From sysdatabases where name = 'myschool ') -- determine if myschool database drop database myschool -- delete database gocreate database myschool -- create database -- master file on (name = 'myschool _ data', filename = 'e: \ project \ myschool_data.mdf ', size = 10, filegrowth = 20%) -- Log File Log On (name = 'myschool _ log', filename = 'e: \ project \ myschool_log.ldf ', size = 3, maxsize = 20, filegrowth = 1) Go

2. Create and Delete tables with T-SQL

Create Table Name (feature of Column 1 data type column, feature of Column 2 data type column ......) example: Use myschoolgoif exists (select * From sysobjects where name = 'subobject') -- determine whether the subject table exists drop table subject gocreate table subject -- create subject table (subjectid int identity) not null, -- identity auto-increment column subjectname nvarchar (50) not null, classhour int not null, gradeid int not null) Go

 

3. Use T-SQL statements to create and delete Constraints

Primary key constraint: The primary key column data must be unique and cannot be empty. For example, a student can be uniquely identified by a student ID.

Non-null constraint (not null): The Column cannot have null values. For example, the Student name cannot be empty.

Unique constraint: the column value must be unique. It can be null, but only one null value can appear.

Check constraint: Specifies the value range and format of a column, for example, age constraints.

Default constraint: the default value of a column. For example, we have many male students and the default gender is male.

Foreign key constraint (foreign key constraint): used to establish a relationship between two tables. You must specify the column that references the primary table.

Example:

(1) primary key constraint (pK _) If exists (select * From sysobjects where name = 'pk _ subjectid ') alter table subjectdrop constraint Partition Table subjectadd constraint pk_subjectid primary key (subjectid) go (2) unique constraint (uq _) If exists (select * From sysobjects where name = 'uq _ subjectname ') alter table subjectdrop constraint Partition Table subjectadd constraint uq_subjectname unique (subjectname) go (3) Check constraints (CK _) If exists (select * From sysobjects where name = 'ck _ classhour ') alter table subjectdrop constraint ck_classhourgoalter table subjectadd constraint ck_classhour check (classhour> 0) Go (4) default constraint (DF _) if exists (select * From sysobjects where name = 'df _ exa') alter table resultdrop constraint df_exagalter table resultadd constraint df_exa default (getdate () for examdatego (5) foreign key constraint (FK _ TABLE 1 _ TABLE 2) If exists (select * From sysobjects where name = 'fk _ subject_grade_gradeid ') alter table subjectdrop constraint fk_subject_grade_gradeidgoalter table subjectadd constraint fk_subject_grade_gradeid foreign key (gradeid) References grade (gradeid) Go

SQL programming

1. Local Variables

Declare @ variable_name datatype example: declare @ name varchar (20) -- declare to store the name variable name, which can store up to 20 characters

2. assign values to variables

Set @ variable_name = value or select @ variable_name = Value

3. Print and select statements

Print local variable or string select local variable as Custom column name example: Print 'server name: '+ @ servernameselect @ servername as 'server name'

 

 

4. Data type conversion (cast () and convert () functions)

Cast (expression as data type) convert (Data Type [length], length [, style]) Example: Cast (@ result as varchar (10) convert (varchar (20 ), @ error)

 

 

5. Begin-end statement

Begin statement or statement block end

 

 

6. If-else statement

If (condition) statement or statement block 1else statement or statement Block 2

 

 

7. While statement

While (condition) Begin statement or statement block [Break | continue] End

 

 

8. Case multi-branch statement

Casewhen condition 1 then result 1 When condition 2 then result 2 [other else results] End

 

 

Advanced Query

1. subquery select ........ from table 1 where column 1> (subquery) Example: Select * from student where age> (select AVG (AGE) from student) 2.In and not in subquery 3. exists and not exists subqueries 4. union Query

 

 

Transactions, views, indexes, and stored procedures

1. Transaction)

A) atomicity: Inseparable.

B) Consistency: when the transaction is completed, the data must be in the consistent state.

C) isolation: all concurrent transactions that modify data are isolated from each other.

D) durability: the transaction processing result is permanent no matter whether the system is faulty or not.

Syntax:

Begin transaction -- indicates the starting point of a transaction.

Commit transaction -- this statement indicates that a transaction is successfully completed.

Rollback transaction -- rolls back the data status to the transaction start point and releases the resources controlled by the transaction.

Example:

Use myschoolgoset nocount no -- do not show affected rows print 'view the balance before the transfer transaction 'select * From bankgo -- start the transaction bengin transactiondeclare @ errornum = 0 update bank set currentmoney = currentMoney-1000 where customername = 'zhang san' set @ errornum = @ errornum + @ errorupdate Bank set currentmoney = currentmoney + 1000 where customername = 'Li si' set @ errornum = @ errornum + @ errorprint 'view the transfer the balance in the transaction 'select * From bankgo -- Based on whether there is an error, are you sure you want to commit or cancel the transaction if @ errornum> 0 beginprint 'failed? roll back the transaction 'rollback transactionendelsebeginprint' transaction successful 'commit transactionendgoprint 'to view the balance 'select * From bankgo

 

 

2. View)

Create view Syntax: Create view view_nameas <SELECT statement> Delete view Syntax: Drop view view_name example: If exists (select 1 from sysobjects where name = 'vw _ cardcostomer ') drop view vw_cardcostomergo -- create view vw_cardcostomerasselect cardid, C. customername customer name, password, moneytype currency, BS. typename savings type, opencarddate account opening date, opencardmoney account opening amount, case when isactive = 'Yes 'then' loss reporting' When isactive = 'no' then' no loss reporting 'end whether loss reporting from bankcard B inner join customer C on C. customerid = B. customerid inner join banksavegettype BS on BS. typeid = B. savetypeid go

3. Stored Procedure (Procedure)

Advantages:

A) Modular Program Design

B) Fast execution Speed and High Efficiency

C) reduce network traffic

D) good security

Category: system stored procedure (SP _) and user-defined Stored Procedure (up _)

Common system stored procedures

A) exec sp_databases -- list database B in the current system) exec sp_renamedb 'mybank', 'bank' -- change database name c) exec sp_tables -- List of objects that can be queried in the current database D) exec sp_colums student -- view the information of columns in the student table E) exec sp_help student -- view all information of student f) exec sp_helpconstraint student -- view the constraint g in the student table) exec sp_helptext 'view _ student_result '-- view the statement text of the view h) exec sp_stored_procedures -- return the list of stored procedures in the current database

Create and delete stored procedures

 

Note: As long as the stored procedure is followed by the output keyword, this parameter is the input parameter. Otherwise, it is regarded as the input parameter. The input parameter can also be set to the default value.

Syntax:

Create proc [edure] stored procedure name [{@ parameter 1 Data Type} [= default value] [Output], ...... .................................. {@ parameter n data type} [= default value] [Output] as SQL statement example: -- if exists (select 1 from sys. objects where name = 'up _ changemoney ') Drop proc up_changemoneygocreate proc up_changemoney @ cardid varchar (32), @ changedate datetime, @ changemoney money, @ changetypename varchar (10 ), @ changenote varchar (100) as begin trandeclare @ Error Sum Int = 0 declare @ changetypeid intset @ changetypeid = (select typeid from changetype where typename = @ changetypename) if (@ changetypename = 'login ') beginprint 'transaction in progress ..... 'Update bankcard set banlance = banlance + @ changemoney where cardid = @ cardidset @ errorsum = @ override + @ errorinsert into changemsg values (@ cardid, @ changedate, @ changemoney, @ override, @ changenote) set @ errorsum = @ errorsum + @ errorendelsebeginupd Ate bankcard set banlance = banlance-@ changemoney where cardid = @ cardidset @ errorsum = @ errorsum + @ errorif (@ errorsum> 0) beginraiserror ('transaction failed! Insufficient balance! ',) Endinsert into changemsg values (@ cardid, @ changedate, @ changemoney, @ override, @ changenote) set @ errorsum = @ errorsum + @ errorendif (@ errorsum = 0) begin print @ changetypename + 'amount successful 'commit tranendelsebegin print' operation failed 'rollback tranendgo