Summary of basic SQL Server database operation statements

Copy codeThe Code is as follows:
-- Basic SQL operations

-- Create a database

Create database Studets

-- Create a table

Create table student (sno char (5), sname char (20), ssex char (2), sage smallint, sdept char (15 ))

Create table course (cno char (3), cname char (30), 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 student ID from student select * from course select * from SC

-- Modify a table

-- Insert column

Alter table student add scome datetime

-- Alter table student alter column scome char (50)

-- Delete a column

Alter table student drop column scome

-- Drop table student drop table course drop table SC

-- Integrity Constraint implementation

-- Sno is not null and unique; ssex check constraint; default sage size

Create table student (sno char (5) not null unique, sname char (20), sex char (2), sage smallint default 20, sdept char (15 ), constraint sex check (sex in ('male', 'female ')),)

-- Alter table student drop constraint ssex

-- Add field constraint alter table student add constraint ssex check (sex in ('male', 'female '))

-- Add the primary key constraint alter table student add constraint PK_SNO primary key (sno) create table course (cno char (3) not null unique, cname char (30), cpno char (3 ), ccredit smallint)

-- The primary key of the joined table already exists. You can add the primary key and foreign key constraints as follows:

Alter table course add constraint PK_CNO primary key (cno), constraint FK_CPNO foreign key (cpno) REFERENCES SC (cno)

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 SC is created, modify the primary and Foreign keys as follows:

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 indexes (physical order of clustered) and non-clustered indexes (logical order of nonclustered, which can be multiple)

Copy codeThe Code is as follows:

-- Not null constraints. A built-in constraint key value is created, and this non-null judgment is achieved through index query. By default, a system index is created for the index.

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

-- Display the table data and index fragmentation information DBCC SHOWCONTIG

-- Insert data select * from student

Alter table student alter column sno char (10)

Insert into student values ('20140901', 'zhang san', 'male', 20, 'Family Planning ')

Insert into student values ('20140901', 'dynasty ', 'female', 18, 'soft ')

Insert into student values ('20170101', 'zhu Yuanzhang ', 'male', 20, 'manage ')

Insert into student values ('20140901', 'Liu cher', 'male', 18, 'military ')

Insert into student values ('20140901', 'Liu table', 'male', 20, 'department of commerce ')

Insert into student values ('20170101', 'bai juyi', 'male', 19, 'grammar ')

Insert into student values ('20170101', 'Li Qingzhao ', 'female', 24, 'grammar ')

Select * from course insert into course values ('001 ', 'database', '005', 4)

Insert into course values ('002 ', 'advanced 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 struct', '007', 3)

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

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

Select * from SC insert into SC values ('20170901', '002', 10021)

Insert into SC values ('20170101', '001', 88)

Insert into SC values ('20170101', '006 ', 10021)

Insert into SC values ('20170101', '007 ', 68)

Insert into SC values ('20170101', '002', 10022)

Insert into SC values ('20170101', '005 ', 30)

Insert into SC values ('20170101', '002', 10024)

Insert into SC values ('20140901', '006 ', 56)

Select * from student -- Query operation

-- Query select * from student select * from course select * from SC

-- Remove duplicate rows select distinct sno from SC

-- Format Query

Select sname as 'name', 2013-sage as 'birthdate 'from student

Select sname, 'birthdate ', 2013-sage from student

Select name = sname, birthdate = 2013-sage from student

-- Condition 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 not in (2)

-- Match Query

Select * from student where sname like 'Liu __'

Select * from student where sname like '_ TABLE __'

Select * from student where sname like '% table %'

-- Arithmetic meta-calculation Query

Select grade * (1 + 0.2) as total score, grade/(10) as score from SC

-- Query grouping Functions

Select COUNT (*) as total COUNT from student

Select COUNT (distinct sno) as 'Total number of electives 'from SC

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

Select MAX (grade) as 'max score 'from SC where sno = '2013'

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

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

Select SUM (grade)/COUNT (grade) as 'average score 'from SC where sno = '123'

Select SUM (grade) as 'total score 'from SC group by sno having sum (grade)> 100-

-Connection query,

-- Equijoin

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 cole' from student A, student B where A. sname = 'white-plain yi' 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

-- Composite condition join

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. *, B. *, C. * from student A, SC B, course C where. sno = B. sno and B. cno = C. cno and B. grade> 99 and B. cno = '002'

-- 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 not in (select sno from SC where grade <60) group by sname

-- Subquery

Select * from student where sage> (select AVG (sage) from student)

-- Check whether a query exists

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 creates user sys. sp_addlogin bnc, bnc, Studets sp_adduser bnc, bnc

-- Permission allocation and Revocation

Grant select on student to bnc

Select * from student

Revoke select on student from bnc

-- Create a view

Create view VIEW_STUGrade (student ID, 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 = 'soft'

-- View

Select * from VIEW_STUGrade

-- View Modification

Alter view VIEW_STUGrade (student ID, 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 = 'soft'

With check option

-- View viewing is not affected after an update failure.

-- View update

Update VIEW_STUGrade set name = 'wang Chao 'where student ID = '000000' select * from student where sno = '000000'

/* 1, updatable view: a, 2 exported from a single basic table, view B exported from more than two basic tables of view a cannot be updated. The field of view B is derived from the expression or the grouping clause of Table d in Function c nested query. 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. example: Student ID, name 6, varchar variable length less than 800 7, text 2 GB 8, nvarchar1 -- 4000 */

-- Operator and wildcard

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 '2017 [5-9]'

-- Control Flow Statements

Declare @ name char (10) set @ name = 'sima Xiangru'

Print @ name

-- Output an expression, which cannot be queried. select @ name

-- Output Multiple Expressions

Declare @ a nvarchar (50), @ B nvarchar (50)

Set @ a = 33 set @ B = 34 --- shorthand for select @ a = 33, @ B = 34

If @ a> @ B

Print 'minimum value: '+ @

Else

Print 'maximum value: '+ @ B

-- Waitfor execution Interval

Waitfor delay '00: 00: 04 'print 'execution delayed by 4 seconds'

Waitfor time '17: 45: 50' print 'Wait for this time'

-- 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) year difference

)

-- Create a stored procedure,

-- View

GO create proc [dbo]. [sel] (

@ Sno char (10)

)

As

Select * from student where sno = @ sno

Exec sel @ sno = '000000'

-- View

GO create proc sel2

As

Select * from student

Exec sel2

-- Modify

GO create proc updat @ sno char (10), @ sex char (2)

As

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

Select * from student exec updat @ sno = '000000', @ sex = 'femal'

-- Delete

GO create proc dele @ sno char (10)

As

Delete student where sno = @ sno

Select * from student

Exec dele @ sno = '000000'

-- Insert

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

As

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

Exec inser @ sno = '000000', @ sname = 'Tom ', @ sex = 'male', @ sage = 10029, @ sdept =' SC 'select * from student

-- Query operation

-- Query

Select * from student select * from course select * from SC

-- Remove duplicate rows select distinct sno from SC

-- Format Query

Select sname as 'name', 2013-sage as 'birthdate 'from student

Select sname, 'birthdate ', 2013-sage from student

Select name = sname, birthdate = 2013-sage from student

-- Condition 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 not in (2)

-- Match Query

Select * from student where sname like 'Liu __'

Select * from student where sname like '_ TABLE __'

Select * from student where sname like '% table %'

-- Arithmetic meta-calculation Query

Select grade * (1 + 0.2) as total score, grade/(10) as score from SC

-- Query grouping Functions

Select COUNT (*) as total COUNT from student

Select COUNT (distinct sno) as 'Total number of electives 'from SC select AVG (grade) as 'average score' from SC where sno = '123'

Select MAX (grade) as 'max score 'from SC where sno = '2013'

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

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

Select SUM (grade)/COUNT (grade) as 'average score 'from SC where sno = '123'

Select SUM (grade) as 'total score 'from SC group by sno having sum (grade)> 100

-- Connection query and -- equivalent connection

Select distinct student. *, SC. * from student, SC where student. sno = SC. sno

-- Self-connection

Select distinct. *, B. * from student A, SC B where. sno = B. sno select B. sname as 'same col' from student A, student B where. sname = 'whitelease 'and. sdept = B. sdept

-- External Connection

Select. *, B. * from student A left join SC B on. sno = B. sno select. *, B. * from student A right join SC B on. sno = B. sno

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

-Composite condition join

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

Select distinct. *, B. *, C. * from student A, SC B, course C where. sno = B. sno and B. cno = C. cno and B. grade> 99 and B. cno = '002'

-- 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 not in (select sno from SC where grade <60) group by sname

-- Subquery

Select * from student where sage> (select AVG (sage) from student)

-- Check whether a query exists

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 creates a user

Sys. sp_addlogin bnc, bnc, Studets sp_adduser bnc, bnc

-- Permission allocation and Revocation

Grant select on student to bnc

Select * from student

Revoke select on student from bnc

-- Create a view

Create view VIEW_STUGrade (student ID, 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 = 'soft'

-- View

Select * from VIEW_STUGrade

-- View Modification

Alter view VIEW_STUGrade (student ID, 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 = 'soft'

With check option

-- View viewing is not affected after an update failure -- view update

Update VIEW_STUGrade set name = 'wang Chao 'where student ID = '000000' select * from student where sno = '000000'

/* 1, updatable view: a, 2 exported from a single basic table, view B exported from more than two basic tables of view a cannot be updated. The field of view B is derived from the expression or the grouping clause of Table d in Function c nested query. Use distinct */

-- Delete view drop view VIEW_STUGrade

-- Trigger

Use Studets

GO create trigger insert_Tri

ON student after

Insert as print 'New data is inserted! '

 

GO create trigger update_Tri

On student after

Update as print 'data is updated! '

 

GO create trigger delete_Tri

On student after

Delete as print 'data deleted! '

 

-- Modify a trigger

GO alter trigger delete_Tri

On student after delete

As

If 'wang Shuai 'in (select sname from deleted)

Print 'This information cannot be deleted! '

Rollback transaction

-- Execute the stored procedure to view the trigger usage

Exec sel @ sno = '000000'

Exec inser @ sno = '000000', @ sname = 'wang Shuai ', @ sex = 'male', @ sage = 25, @ sdept = 'guomao'

Exec updat @ sno = '000000', @ sex = 'female'

Exec dele @ sno = '000000'

-- View, modify, and delete a trigger

/* Sp _ * + trigger name

Sp_helptext: the trigger body information sp_help: View general information, trigger name, attribute, creation time, type sp_depends: All triggers that reference or specify a table sp_helptrigger: specified information */sp_help delete_Tri

Sp_helptext delete_Tri

Sp_depends delete_Tri

Sp_helptrigger student

-- Delete a trigger

Drop trigger delete_Tri