SQL Server Database Basic Operational Statement summary _mssql

Copy Code code as follows:

--sql Basic Operations

--Create a database

Create DATABASE Studets

--Create a table

CREATE TABLE Student (Sno char (5), sname char (), Ssex char (2), Sage smallint, sdept char (15))

CREATE TABLE Course (CNO char (3), CNAME char (), Cpno char (3), Ccredit smallint)

CREATE TABLE SC (Sno char (5), CNO char (3), Grade int)

--View table information

SELECT * FROM student Select Sno as learning number from student SELECT * FROM Course SELECT * FROM SC

--Modifying tables

--Inserting columns

ALTER TABLE student Add Scome datetime

--Modify the field type of the column ALTER TABLE student ALTER COLUMN Scome CHAR (50)

--Delete--delete column

ALTER TABLE student Drop column Scome

--Delete Table drop table student drop table course DROP table SC

--Integrity Constraint implementation

--sno a non-empty unique, ssex check constraint, sage default size

CREATE TABLE Student (Sno char (5) NOT null unique, sname char (), Sex char (2), sage smallint default, sdept char (15), Constraint sex check (Sex in (' Male ', ' female '))

--delete table constraint ALTER TABLE student drop constraint Ssex

--add field constraint ALTER TABLE student add constraint ssex check (sex in (' Male ', ' female '))

--Add a PRIMARY KEY constraint ALTER TABLE student add constraint Pk_sno primary KEY (SNO) CREATE TABLE course (CNO char (3) NOT null unique, CNA Me char (M), Cpno char (3), Ccredit smallint)

--The associated table primary key already exists, you can add a primary key and a FOREIGN key constraint as follows

ALTER TABLE course add constraint Pk_cno primary KEY (CNO) Constraint Fk_cpno key (foreign) Cpno SC (REFERENCES)

CREATE TABLE SC

(

Sno Char (5) Foreign key references student (SNO),

CNO Char (3) Foreign key references course (CNO),

grade int,

Constraint PK_SC primary KEY (SNO,CNO)

)

ALTER TABLE [dbo]. [SC] DROP CONSTRAINT [fk__sc__sno__0f975522]

ALTER TABLE [dbo]. [SC] DROP CONSTRAINT [PK_SC]

ALTER TABLE [dbo]. [SC] DROP CONSTRAINT [PK_SC]

--After you create the SC, modify the primary foreign key by following

ALTER TABLE SC add constraint PK_SC primary key (SNO,CNO),

Constraint Fk_sno foreign KEY (SNO) references student (SNO),

Constraint Fk_cno foreign KEY (CNO) References course (CNO)

--Create an index.

Clustered index (clustered physical order) and nonclustered index (nonclustered logical order, can be multiple)

Copy Code code as follows:

--not null constraint field. Creates a system-built-in constraint key value, and this non-null judgment, implemented through an index query, creates a system index by default

Create UNIQUE index Stusno

On student (SNO)

Create UNIQUE index Coucno

On course (CNO)

Create UNIQUE index Scno

On SC (SNO asc,cno desc)

Drop Index SCNO on SC

--Fragment information showing the data and index of the table DBCC Showcontig

--Insert Data select * FROM student

ALTER TABLE student ALTER COLUMN Sno CHAR (10)

INSERT into student values (' 10021 ', ' John ', ' Male ', 20, ' Department of Science ')

INSERT into student values (' 10022 ', ' Dynasty ', ' female ', 18, ' software ')

INSERT into student values (' 10023 ', ' Zhu Yuanzhang ', ' Men ', 20, ' management ')

INSERT into student values (' 10024 ', ' Liu Che ', ' men ', 18, ' military ')

INSERT into student values (' 10025 ', ' Biao ', ' Male ', 20, ' Department of Business ')

INSERT into student values (' 10026 ', ' Bai Juyi ', ' Male ', 19, ' Grammar ')

INSERT into student values (' 10027 ', ' Li Qingzhao ', ' female ', 24, ' Grammar ')

SELECT * FROM course inserts into course values (' 001 ', ' database ', ' 005 ', 4)

Insert into course values (' 002 ', ' higher mathematics ', ', ', 2)

Insert into course values (' 003 ', ' Information system ', ' 001 ', 4)

Insert into course values (' 004 ', ' OS ', ' 006 ', 2)

Insert into course values (' 005 ', ' data structure ', ' 007 ', 3)

Insert into course values (' 006 ', ' data processing ', ', 2)

Insert into course values (' 007 ', ' C language ', ' 006 ', 5)

SELECT * FROM SC inserts into SC values (' 10021 ', ' 002 ', 100)

INSERT into SC values (' 10021 ', ' 001 ', 88)

INSERT into SC values (' 10021 ', ' 006 ', 100)

INSERT into SC values (' 10021 ', ' 007 ', 68)

INSERT into SC values (' 10022 ', ' 002 ', 100)

INSERT into SC values (' 10023 ', ' 005 ', 30)

INSERT into SC values (' 10024 ', ' 002 ', 100)

INSERT into SC values (' 10024 ', ' 006 ', 56)

SELECT * FROM student--query operations

--Query SELECT * FROM Student SELECT * To course SELECT * FROM SC

--Remove duplicate rows SELECT DISTINCT Sno from SC

--Format Query

Select Sname as ' name ', 2013-sage as ' date of birth ' from student

Select Sname, ' Birth date ', 2013-sage from student

Select name =sname, date of birth =2013-sage from student

--Conditional query

SELECT * FROM course where ccredit>3

SELECT * FROM course where ccredit between 2 and 5

SELECT * FROM course where ccredit> 2 and ccredit<5

SELECT * FROM course where ccredit in (2)

SELECT * FROM course where ccredit isn't in (2)

--Matching query

SELECT * FROM student where sname like ' Liu __ '

SELECT * FROM student where sname like ' _ Table __ '

SELECT * FROM student where sname like '% table% '

--Arithmetic element calculation query

Select grade* (1+0.2) as Total score, grade/(a) as Performance point from SC

--Group Function query

Select COUNT (*) as total number from student

Select COUNT (Distinct sno) as ' total number of electives ' from SC

Select AVG (grade) as ' average score ' from SC where sno= ' 10021 '

Select MAX (grade) as ' Max Score ' from SC where sno= ' 10021 '

Select min (grade) as ' min score ' from SC where sno= ' 10021 '

Select SUM (grade) as ' total score ' from SC where sno= ' 10021 '

Select SUM (Grade)/count (grade) as ' average score ' from SC where sno= ' 10021 '

Select SUM (grade) as ' total score ' from SC Group by Sno has SUM (grade) >100-

-Connection queries,

--Equivalent connection

Select distinct student.*,sc.* from STUDENT,SC where Student.sno=sc.sno

--Self connection

Select distinct a.*,b.* from student A,sc B where A.sno=b.sno

Select B.sname as ' the same system ' from student a,student B where a.sname= ' Bai Juyi ' and a.sdept=b.sdept

--External connection

Select a.*,b.* from student A left join SC B on A.sno=b.sno

Select a.*,b.* from student A Right join SC B on A.sno=b.sno

Select a.*,b.* from student A full join SC B on A.sno=b.sno

--Compound Conditional connection

SELECT * FROM SC SELECT * FROM course

Select distinct a.*,b.* from student A,sc B where A.sno=b.sno and b.grade>99 and b.cno= ' 002 '

Select distinct a.*,b.*,c.* from student a,sc B,course C where A.sno=b.sno and B.cno=c.cno and b.grade>99 ' 0 02 '

--String Connection query

Select Sname+sno from Student

Select distinct sname from student, SC where student.sno=sc.sno

Select sname from student, SC where Student.sno=sc.sno and Student.sno isn't in (select Sno from SC where grade<60) grou P by sname

--Subquery

SELECT * FROM student where sage> (select AVG. Sage) from student)

--Queries that exist

SELECT * FROM student where exists (SELECT * from SC where sno=student.sno)

SELECT * from student where NOT EXISTS (SELECT * from SC where sno=student.sno)

--sql Create user Sys.sp_addlogin bnc,bnc,studets sp_adduser BNC,BNC

--rights assignment and recovery

Grant select on student to BNC

SELECT * FROM Student

Revoke select on student from BNC

--Create a view

Create View View_stugrade (school number, name, course, results)

As

Select Student.sno,student.sname,course.cname,sc.grade from STUDENT,COURSE,SC

where Student.sno=sc.sno and Course.cno=sc.cno and student.sdept= ' software '

--View View

SELECT * FROM View_stugrade

--View Modification

Alter VIEW View_stugrade (school number, name, course, results)

As

Select Student.sno,student.sname,course.cname,sc.grade from STUDENT,COURSE,SC

where Student.sno=sc.sno and Course.cno=sc.cno and student.sdept= ' software '

With CHECK option

--Do not affect view view after failed update

--View Update

Update View_stugrade Set name = ' Wang ' where school number = ' 10022 ' select * from student where sno= ' 10022 '

/* 1, Updatable view: A, 2 of a single basic table export, non-updatable view a two or more basic table export B view fields from expressions or function c nested query table D Group clauses use distinct/

--Delete View Drop view View_stugrade

--Advanced SQL Programming

--Data type 1,int 2,smallint 3,tinyint (0--255) 4,bigint 5char fixed length <800. such as: School number, name 6,varchar variable length less than 7,text 2GB 8,nvarchar1--4000 *

--operators and wildcard characters

Select GETDATE ()-1 yesterday, GETDATE () today, GETDATE () +1 tomorrow

Select 59&12

Select 59|12

Select 59^12

--Fuzzy query

SELECT * FROM student where sname like '% Liu '

SELECT * FROM student where Sno like ' 1002[5-9] '

--Control process statements

DECLARE @name char (a) Set @name = ' Division Sima '

Print @name

--Output An expression that cannot be queried for select @name

--Output multiple expressions

declare @a nvarchar (m), @b nvarchar (50)

Set @a=33 set @b=34---shorthand select@a=33,@b=34

If @a>@b

The print ' Minimum value is: ' +@a

Else

print ' Maximum is: ' +@b

--waitfor interval for some time to execute

WAITFOR DELAY ' 00:00:04 ' print ' delayed 4 seconds of execution '

waitfor time ' 17:45:50 ' print ' waits for this moment to execute '

--Create a function

CREATE FUNCTION GetTime (@date1 datetime, @date2 datetime)

RETURNS TABLE

As Return (

Select DateDiff (DD, @date1, @date2) Day difference, DateDiff (mm, @date1, @date2) month difference, DateDiff (yy, @date1, @date2) years of difference

)

--Create a stored procedure,

--View

Go CREATE proc [dbo]. [SEL] (

@sno Char (10)

)

As

SELECT * FROM student where sno= @sno

EXEC sel @sno = ' 10021 '

--View

Go CREATE proc Sel2

As

SELECT * FROM Student

EXEC Sel2

--Modify

Go create proc updat @sno char (@sex char (2)

As

Update student set sex= @sex where sno= @sno

SELECT * FROM student exec updat @sno = ' 10021 ', @sex = ' female '

--Delete

Go create proc Dele @sno char (10)

As

Delete student where sno= @sno

SELECT * FROM Student

exec dele @sno = ' 10029 '

--Insert

Go create proc inser @sno char (@sname char (), @sex char (2), @sage smallint, @sdept char (15)

As

INSERT into student values (@sno, @sname, @sex, @sage, @sdept)

exec inser @sno = ' 10029 ', @sname = ' Tom ', @sex = ' male ', @sage =100, @sdept = ' SC ' select * FROM student

--Query operations

--Query

SELECT * FROM Student SELECT * To course SELECT * FROM SC

--Remove duplicate rows SELECT DISTINCT Sno from SC

--Format Query

Select Sname as ' name ', 2013-sage as ' date of birth ' from student

Select Sname, ' Birth date ', 2013-sage from student

Select name =sname, date of birth =2013-sage from student

--Conditional query

SELECT * FROM course where ccredit>3

SELECT * FROM course where ccredit between 2 and 5

SELECT * FROM course where ccredit> 2 and ccredit<5

SELECT * FROM course where ccredit in (2)

SELECT * FROM course where ccredit isn't in (2)

--Matching query

SELECT * FROM student where sname like ' Liu __ '

SELECT * FROM student where sname like ' _ Table __ '

SELECT * FROM student where sname like '% table% '

--Arithmetic element calculation query

Select grade* (1+0.2) as Total score, grade/(a) as Performance point from SC

--Group Function query

Select COUNT (*) as total number from student

Select COUNT (Distinct sno) as ' total number of electives ' from SC Select AVG (grade) as ' average score ' from SC where sno= ' 10021 '

Select MAX (grade) as ' Max Score ' from SC where sno= ' 10021 '

Select min (grade) as ' min score ' from SC where sno= ' 10021 '

Select SUM (grade) as ' total score ' from SC where sno= ' 10021 '

Select SUM (Grade)/count (grade) as ' average score ' from SC where sno= ' 10021 '

Select sum (grade) as ' total score ' from SC Group by Sno has SUM (grade) >100

--Connection query,--equivalent connection

Select distinct student.*,sc.* from STUDENT,SC where Student.sno=sc.sno

--Self connection

Select distinct a.*,b.* from student A,SC b where a.sno=b.sno select B.sname as ' same system ' from student A,student b where a.sn Ame= ' Bai Juyi ' and a.sdept=b.sdept

--External connection

Select a.*,b.* from student a LEFT join SC b in A.sno=b.sno select a.*,b.* from student a RIGHT join SC B on A.sno=b.sno

Select a.*,b.* from student A full join SC B on A.sno=b.sno

-Composite Condition Connection

Select distinct a.*,b.* from student A,sc B where A.sno=b.sno and b.grade>99 and b.cno= ' 002 '

Select distinct a.*,b.*,c.* from student a,sc B,course C where A.sno=b.sno and B.cno=c.cno and b.grade>99 ' 0 02 '

--String Connection query

Select Sname+sno from Student

Select distinct sname from student, SC where student.sno=sc.sno

Select sname from student, SC where Student.sno=sc.sno and Student.sno isn't in (select Sno from SC where grade<60) grou P by sname

--Subquery

SELECT * FROM student where sage> (select AVG. Sage) from student)

--Queries that exist

SELECT * FROM student where exists (SELECT * from SC where sno=student.sno)

SELECT * from student where NOT EXISTS (SELECT * from SC where sno=student.sno)

--sql Create a user

Sys.sp_addlogin bnc,bnc,studets sp_adduser BNC,BNC

--rights assignment and recovery

Grant select on student to BNC

SELECT * FROM Student

Revoke select on student from BNC

--Create a view

Create View View_stugrade (school number, name, course, results)

As

Select Student.sno,student.sname,course.cname,sc.grade from STUDENT,COURSE,SC

where Student.sno=sc.sno and Course.cno=sc.cno and student.sdept= ' software '

--View View

SELECT * FROM View_stugrade

--View Modification

Alter VIEW View_stugrade (school number, name, course, score) as select Student.sno,student.sname,course.cname,sc.grade from Student,course, Sc

where Student.sno=sc.sno and Course.cno=sc.cno and student.sdept= ' software '

With CHECK option

--The view update is not affected after the update fails

Update View_stugrade Set name = ' Wang ' where school number = ' 10022 ' select * from student where sno= ' 10022 '

/* 1, Updatable view: A, 2 of a single basic table export, non-updatable view a two or more basic table export B view fields from expressions or function c nested query table D Group clauses use distinct/

--Delete View Drop view View_stugrade

--Trigger

Use Studets

GO CREATE TRIGGER Insert_tri

On student

Insert as print ' has new data inserts! '

GO CREATE TRIGGER Update_tri

On student

Update as print ' has data updates! '

GO CREATE TRIGGER Delete_tri

On student

Delete as print ' has data deletion! '

--Modifying triggers

Go ALTER TRIGGER Delete_tri

On student after delete

As

If ' Shuai ' in (select sname from deleted)

print ' This information is not allowed to be deleted! '

ROLLBACK TRANSACTION

--Execute a stored procedure to view the use of triggers

EXEC sel @sno = ' 10021 '

exec inser @sno = ' 10029 ', @sname = ' Shuai ', @sex = ' male ', @sage =25, @sdept = ' Guomao '

exec updat @sno = ' 10029 ', @sex = ' female '

exec dele @sno = ' 10029 '

--View, modify, delete triggers

/* sp_*+ Trigger Name

Sp_helptext: Trigger Body information sp_help: View general information, trigger name, property, creation time, type sp_depends: All triggers for a reference or specified table sp_helptrigger: Specify information/sp_help delete_t Ri

Sp_helptext Delete_tri

Sp_depends Delete_tri

Sp_helptrigger Student

--Deleting triggers

Drop Trigger Delete_tri