Summary of important knowledge points of database

First of all thanks to the blog Park, the creation of this proprietary knowledge sharing platform for developers, it is important to emphasize that I am not a character, but a first into the garden has not seen the small IT rookie, in the spirit of a study to the park in the heart of the Daniel, joined the circle, take this opportunity to their daily learning to organize real-time, Also hope that the great God to teach you more.

My e-mail is: [email protected]

The following is a summary of recent knowledge of SQL Server database, I hope that in some places to help beginners, for the relevant basic terminology I do not introduce, from the SQL language operation.

1. Summary of SQL statements for getting started with databases

Let's start by understanding the data types in several databases:

NCHAR----------------Storage data type is a wide-byte character array, up to 4000 bytes

nvarchar-------------storage data type is a wide-byte string, up to 4000 bytes

Char------------------storage data type is a multibyte character array, up to 8000 bytes

VARCHAR--------------Storage data type is multibyte string, up to 8000 bytes

As an example:

The "Hello" string is stored with char (10) and varchar (10) respectively, the number of bytes in char (10) is 10, and the number of bytes in varchar (10) is 6.

If you have super-large text storage, choose Text,ntext type at this time

Definition function of 1.1SQL statement

This entry includes definitions and revocation of basic tables, indexes, and views. For tables, we can create, delete, and modify them, and for views, indexes, we can create and delete them.

1.1.1 Creating, modifying, and deleting tables

Create:

CREATE TABLE Table name

{

< column names > < data types > < constraints >

}

Such as:

CREATE TABLE Studentinfo

(

Number int primary key,--primary key, unique identifier, not allowed to be empty

Name varchar unique, a unique constraint that is uniquely identified in addition to the primary key, can be re-nullable

Sex varchar (5) Check (sex= ' male ' or sex= ' female '),--check the constraint to ensure that the value is within a certain range

Age int Default (18)--Default constraint

)

CREATE TABLE Studentcourse

(

Number int foreign key references studentinfo (number),--foreign key

Course varchar (20)

)

Modify:

Such as:

ALTER TABLE table name

{

Add < new column name > < data type > < constraints >

drop< constraint conditions >

ALTER COLUMN < column name > < data type >

}

Delete:

DROP table Name

Such as:

drop table Studentinfo

1.1.2 Creating, deleting views

Create:

Create View name as

(Statement block)

Delete:

Drop View Name

It is important to note that a view is a virtual table that is exported by one or more tables

Based on a table:

Such as:

CREATE VIEW MyView as

(SELECT * FROM student)

SELECT * FROM MyView

Update MyView set ssex= ' female ' where s#= ' 01 '

INSERT into myview values (' A ', ' AA ', ' 1994-07-09 ', ' Male ')

Delete from MyView where s#= ' 09 '

Update myview set ssex= ' man ' where s#= ' 01 '

Based on multiple tables:

Such as:

CREATE View View1 as

Select s.*,sc.score,sc.c# from student s inner join

SC on s.s#=sc.s#

SELECT * FROM View1

Note: It is worth noting that no matter for a table or a multi-table, as long as the aggregate function of the view will not be added or deleted, only the view of the original table can be modified.

1.1.2 Creating, deleting indexes

In general, a very large database chooses to add an index to the database, divided into clustered and nonclustered indexes.

Create:

Create index index name on table name (column name)

Delete:

Drop Index Index name

Query function of 1.2SQL statement

Statement format (including statement order):

Select [All | distinct] Column name

From table name/view name

[Where Condition expression]

[Group BY column name] [Having conditional expression]

[Order BY column name] [ASC | desc]

1.2.1 Single-Table query

(1) Query columns

Select column name (multiple columns with "," separated, all columns with "*") from table name

(2) Query line

A Eliminate duplicate rows

Select distinct column name from table name

B Compare size:=,>,>=,!=,!>,!< etc.

Select sname from student where cname= ' 中文版 '

C Scoping: Between...and...,not Between...and ...

Select sname,sage from student where Sage between and 23

D Determining the collection: In,not in

Select sname from student where sdept on (' is ', ' MA ', ' CS ')

E. Fuzzy query: Like

Select s#,sname from student where sname like ' Liu '

Note: If it is '% Liu ', then the name of the word "Liu" is queried.

F. Null value involved: IsNull

Select *,isnull (Ssex, ' unknown ') from student

Note: Ssex Xiang Jo is empty, it is set to "unknown"

G Multiple criteria query: And,or

Select sname,sage from student where Sage >= and ssex= ' man '

H. Aggregate functions: COUNT,SUM,MAX,MIN,AVG, etc.

Select avg (score) from SC where c#= ' 01 '

I. Query Table top: Top

Select Top 2 * from student

J Conditions:

Select column Name Case when condition

Then statement block

When conditions

Then statement block

Else Statement block

End

From table name

Such as:

According to the date of birth, the current day of the month < month of birth, the age is reduced by one

Select *,datediff (Yy,birth,getdate ()),

Case when month (GETDATE ()) <month (birth)

Then DateDiff (Yy,birth,getdate ())-1

When Day (getdate ()) <day (birth)

Then DateDiff (Yy,birth,getdate ())-1

Else DateDiff (Yy,birth,getdate ())

End

From Teacherinfo

K Type conversions

Select cast ()

Select cast (' +1 ' as int)

Select convert (int, ' +1 ')//convert type conversion style options

1.2.2 Multi-Table query

In the Multi-table query, there are 5 ways to query: Left JOIN, right-hand, Cartesian product, nested

A Take the intersection of two tables, the INNER join

SELECT * FROM student inner join SC on student. s#=sc.s#

B The left table is the main left join

SELECT * FROM student left join SC on student. s#=sc.s#

C The right table is the main and right joins

SELECT * FROM student right join SC on student. s#=sc.s#

D Multiply two table rows, that is, the Cartesian product

SELECT * from STUDENT,SC where student. s#=sc.s#

E. nested queries

For example: Information and course scores for students with "01" courses higher than "02"

SELECT * FROM

(SELECT *, (select Score from SC where c#= ' "and student.s#=sc.s#) SC01,

(Select Score from SC where c#= ' "and student.s#=sc.s#) Sc02 from student) a

where SC01>SC02

Nested queries have subqueries with an in predicate, subqueries for comparison operators, predicate subqueries with any or OR, exists predicate subqueries, and Union for collection queries, which are not examples.

Operation function of 1.3SQL statement

This content mainly includes inserting, modifying and deleting the data.

Insert:

Insert into table name values (value, value)

Such as:

INSERT into Studentinfo values (5, ' ee ', ' Male ', 35)

INSERT into Studentinfo (number, age) VALUES (6,60)

Modify:

Update table Name set column name = value

Such as:

Update studentinfo set age=60 where number=6

Update studentinfo set name= ' GG ', sex= ' female ' where number=6

Delete:

Delete from table name

Delete from studentinfo where number=6;

2. List of database entry specifications

If the table-to-table relationship is N: {, the third table created by the primary key is a combined primary key of two tables, and if the relationship is 1:n, the 1 primary key is placed in N

First paradigm:

Any of the properties of a table cannot be split, which means that several properties are not allowed under a column property in each table

Second paradigm:

Candidate key (non-primary key) partial dependency on combined primary key not allowed

such as: (Student number, course number) [combination of primary key] (grades, students ' names)

Results depend on the combination of the primary key, and the student name depends only on the number, resulting in a partial dependency, not in accordance with the second paradigm

The third paradigm:

Transitive dependencies are not allowed.

As an example:

(student number) (name, age, school, school address)

Student name, age, in which school is dependent on the number of students, and the school address is not dependent on the student's number, no matter what the student number is, the school is there, and the school address depends on the school is which, produced a transmission dependence, so does not conform to the third paradigm.

2. Stored procedures for getting started with the database

Said stored procedures, I think it is possible to understand that in the SQL statement, in order to avoid the same operation more than once, the same operation of the same statement of the process of encapsulation, equivalent to the function of it.

So before you say the stored procedure, let's look at some grammar rules:

Define variables: (local variable with 1 @, global variable with 2 @)

Declare the type of the variable name variable, the type of the @ variable name variable

Such as:

DECLARE @sum int,@i int

Variable assignment:

Set: Only one variable can be assigned; Select: Assign values to multiple variables

Such as:

Select @sum =1,@i=9

Print variables:

Print @ variable Name

Conditions:

If condition

Begin statement block

End

Else

Begin statement block

End

Such as:

declare @number int, @size int

Select @number =100, @size =200

If @number <50

Begin

Print @number

End

Else

Begin

Print @size

End

Loop: (only while in the loop statement, the dead loop is while 1=1)

While condition

Begin statement block

End

Such as:

declare @sum int, @i int

Select @sum =1,@i=9

While @i>0

Begin

Set @sum = (@sum + 1)

Set @[email protected]

End

Print @sum

Let's take a look at the stored procedure.

To create a stored procedure:

Create procedure Stored Procedure name @ variable name variable type, @ variable name variable type

As

Begin

Statement block

End

Such as:

Alter procedure Monkey @sum int, @i int, @value int output

As

Begin

--declare @sum int,@i int

--select @sum =1,@i=9

While @i>0

Begin

Set @sum = (@sum + 1)

Set @[email protected]

End

--print @sum

--return @sum

Set @[email protected]

End

--print's

Execute monkey 1,9//execute a Stored procedure statement

--return's

DECLARE @sum int

Execute @sum =monkey 1,9//Execute Stored Procedure statement

Print @sum

--output's

DECLARE @sum int

Execute monkey 1,9, @sum output//Execute Stored Procedure statement

Print @sum

To delete a stored procedure:

Drop Procedure Stored Procedure name

3. Getting started with databases triggers, cursors, temporary tables, replication tables

3.1 Triggers

The trigger is a special kind of stored procedure, when the specified event occurs, the automatic call, with the vernacular understanding is for example has the student table also has the choice timetable, then learns the number to be a number the classmate deletes, the choice curriculum also has a student's elective course information and so on need to delete together, but the trigger can complete this function.

There are 3 types of triggers: inserting, modifying, and deleting triggers. Two tables are created at the same time when a trigger is created, inserted and deleted tables

Delete:

Create Trigger Trigger Name

On table name

After delete

As

Begin

Statement block

End

Such as:

Create Trigger Mytrigger

On student

After delete

As

Begin

DECLARE @s# varchar (3)

Set @s#= (select s# from deleted)

Delete from SC where s#[email protected]#

End

Delete from student where s#= ' 01 '

Insert:

Create Trigger Trigger Name

On table name

After insert

As

Begin

Statement block

End

Such as:

Create Trigger Myinsert

On student

After insert

As

Begin

DECLARE @s# varchar (3)

Set @s#= (select s# from inserted)

INSERT into SC values (@s#, ' 01 ', 80)

INSERT into SC values (@s#, ' 02 ', 90)

INSERT into SC values (@s#, ' 03 ', 99)

End

INSERT into student values (' 01 ', ' Lei ', ' 1990-01-01 ', ' Male ')

Modify:

Create Trigger Trigger Name

On table name

After update

As

Begin

Statement block

End

Such as:

Create Trigger Myupdate

On student

After update

As

Begin

DECLARE @s#old varchar (3), @s#new varchar (3)

Select @s#old=s# from deleted

Select @s#new=s# from inserted

Update SC set s#[email protected] #new where S#[email protected] #old

End

Update student set s#= ' where s#= ' 11 '

3.2 Cursors

A cursor is a method of processing data, one or more rows of a result set, which can be imagined as a pointer.

Cursor creation takes 5 steps: Defining a cursor, opening a cursor, traversing the data, closing the cursor, freeing up memory.

Such as:

--Define cursors, open cursors, traverse cursors, close cursors, free memory

DECLARE @s# varchar, @sname varchar (50)

--Defining cursors

DECLARE mycursor cursor FOR

Select S#,sname from Student

--Open cursor

Open MyCursor

--Traversal cursors

FETCH NEXT from MyCursor to @s#, @sname

While @ @FETCH_STATUS =0 The return value of the//fetch function with a value of 0

Begin

Print @s#+ '---------' [email protected]

FETCH NEXT from MyCursor to @s#, @sname

End

--Close cursor

Close MyCursor

--Free memory

Deallocate mycursor

3.3 Creating a temporary table

CREATE TABLE #临时表名---------------created

drop table #临时表名---------------Delete

3.4 Copying tables

Tables that do not exist:

Select * into a new non-existent table name from the existing table name

Such as:

Select * to AAA from student

Tables that exist:

Insert into table name (existing table name) content

Such as:

Insert into AAA select * from Student

The above is I think the database should be used to learn some of the knowledge points, only for your reference, if there is said the wrong place, but also hope that many advice.

Summary of important knowledge points of database