Shulou(Shulou.com)05/31 Report--

This article mainly introduces "summary of basic operation statements of SQL Server database". In daily operation, I believe that many people have doubts about the summary of basic operation statements of SQL Server database. The editor has consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "summary of basic operation statements of SQL Server database". Next, please follow the editor to study!

The copy code is as follows:-- basic operation of sql

-- create a database

Create database Studets

-- create tables

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 the table

-- insert column

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 is not null and unique, ssex check constraints, sage default 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)),)

-- remove the constraint alter table student drop constraint ssex of the table

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

-- add primary key constraints 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 associated table already exists. You can add primary key and foreign key constraints by doing the following

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 creating the sc, modify the primary foreign key 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.

Divided into clustered indexes (clustered physical order) and non-clustered indexes (nonclustered logical order, multiple)

The copy code is as follows:

-- when not null constrains the field. A system-built constraint key value is created, and this non-null judgment is achieved through an index query, and the index 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

-- DBCC SHOWCONTIG that displays the data of the table and the fragment information of the index

-- insert data select * from student

Alter table student alter column sno char (10)

Insert into student values ('10021' Zhang San', 'male', 20 'Department of Planning')

Insert into student values ('10022' imperial 'dynasty', 'female', 18 'software')

Insert into student values ('10023' Zhu Yuanzhang, 'male', 20 'management')

Insert into student values ('10024' Liu Che', 'male', 18 'military')

Insert into student values ('10025' Liu 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 insert into course values ('001' database', '005')

Insert into course values (002mathematics, 2)

Insert into course values ('003' information system', '001')

Insert into course values ('004' operating system', '006')

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

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

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

Select * from sc insert into sc values ('10021 million, 002 million, 100)

Insert into sc values ('10021th, 01th).

Insert into sc values ('10021century, 006century, 100th)

Insert into sc values ('10021, 007, and 68)

Insert into sc values ('10022 million, 002, 100)

Insert into sc values ('10023cm, 005pm, 30th)

Insert into sc values ('10024th and 002th)

Insert into sc values ('10024,006pm 56)

Select * from student-query operation

-- query select * from student select * from course select * from sc

-- remove duplicate lines select distinct sno from sc

-- format the query

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

Select sname,' date of birth', 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 ccredit100-

-join query,

-- equivalent connection

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

-- self-connection

Select distinct A. Magna B. * from student Amari sc B where A.sno=B.sno

Select B.sname as' the same department 'from student Amenon student B where A. snameboy' Bai Juyi 'and A.sdept=B.sdept

-- external connection

Select A. Magnum B.* from student A left join sc B on A.sno=B.sno

Select A. Magnum B.* from student A right join sc B on A.sno=B.sno

Select A. Magnum 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.Magnum B. * from student Amari sc B where A.sno=B.sno and B.grade > 99 and B.cnockery 002'

Select distinct A.Pendagogical B.C.C.from student Amagorsc Btraining course C where A.sno=B.sno and B.cno=C.cno and B.grade > 99 and B.CNOPERATION 002'

-- string concatenation 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 (select AVG (sage) from student)

-- query whether it exists or not

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

-- View creation

Create view VIEW_STUGrade (student number, name, course, grade)

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 (student number, name, course, grade)

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

-- View view is not affected after update failure

-- View update

Update VIEW_STUGrade set name = 'Wang Chao' where student number = '10022' select * from student where sno='10022'

/ * 1, updatable view: a, 2 derived from a single base table, non-updatable view a B view fields derived from more than two basic tables come from expressions or functions c table d grouping clauses of nested queries use distinct * /

-- Delete view drop view VIEW_STUGrade

Advanced sql programming

-- data type 1 5char 2 minint 3 minint tinyint (0MMMI 255) 4 minminint bigint fixed length @ b

The minimum value of print'is:'+ @ a

Else

The maximum value of print'is:'+ @ b

-- waitfor is executed at regular intervals

Waitfor delay '00VOG 04' print' delay for 4 seconds

Waitfor time'17 45 waitfor time 50 'print' wait for this moment to be executed.

-- create function

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

RETURNS TABLE

AS RETURN (

Select datediff (dd,@date1,@date2) daily range, datediff (mm,@date1,@date2) monthly range, datediff (yy,@date1,@date2) annual range

)

-- create stored procedures

-- 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 (10), @ sex char (2)

As

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

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

-- 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 (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='10029', @ sname='tom', @ sex='', @ sage=100, @ sdept='sc' select * from student

-- query operation

-- query

Select * from student select * from course select * from sc

-- remove duplicate lines select distinct sno from sc

-- format the query

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

Select sname,' date of birth', 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 ccredit100

-join query,-- equivalent join

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

-- self-connection

Select distinct A. from student where A.sno=B.sno select B.sname as B. * from student A where A.sno=B.sno select B.sname as' the same department'B where A. snamekeeper 'Bai Juyi' and A.sdept=B.sdept

-- external connection

Select A. Magnum B.* from student A left join sc B on A.sno=B.sno select A. Magna B.* from student A right join sc B on A.sno=B.sno

Select A. Magnum B.* from student A FULL join sc B on A.sno=B.sno

-compound conditional connection

Select distinct A.Magnum B. * from student Amari sc B where A.sno=B.sno and B.grade > 99 and B.cnockery 002'

Select distinct A.Pendagogical B.C.C.from student Amagorsc Btraining course C where A.sno=B.sno and B.cno=C.cno and B.grade > 99 and B.CNOPERATION 002'

-- string concatenation 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 (select AVG (sage) from student)

-- query whether it exists or not

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

-- View creation

Create view VIEW_STUGrade (student number, name, course, grade)

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 (student number, name, course, grade) 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

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

Update VIEW_STUGrade set name = 'Wang Chao' where student number = '10022' select * from student where sno='10022'

/ * 1, updatable view: a, 2 derived from a single base table, non-updatable view a B view fields derived from more than two basic tables come from expressions or functions c table d grouping clauses of nested queries use distinct * /

-- Delete view drop view VIEW_STUGrade

-- trigger

Use Studets

GO create trigger insert_Tri

ON student after

Insert as print 'there is new data inserted!'

GO create trigger update_Tri

On student after

Update as print 'data update!'

GO create trigger delete_Tri

On student after

Delete as print 'data deletion!'

-- modify the trigger

GO alter trigger delete_Tri

On student after delete

As

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

Print 'this message must not be deleted!'

Rollback transaction

-- execute stored procedures to view trigger usage

Exec sel @ sno='10021'

Exec inser @ sno='10029', @ sname=' Wang Shuai', @ sex=' Man', @ sage=25, @ sdept=' International Trade'

Exec updat @ sno='10029', @ sex=''

Exec dele @ sno='10029'

-- View, modify, delete triggers

/ * sp_*+ trigger name

Sp_helptext: 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: specify information * / sp_help delete_Tri

Sp_helptext delete_Tri

Sp_depends delete_Tri

Sp_helptrigger student

-- delete trigger

Drop trigger delete_Tri

At this point, the study of "summary of basic operation statements of SQL Server database" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.