共計 10248 個字符，預計需要花費 26 分鐘才能閱讀完成。
這篇文章主要介紹“SQL Server 數據庫的基本操作語句總結”，在日常操作中，相信很多人在 SQL Server 數據庫的基本操作語句總結問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”SQL Server 數據庫的基本操作語句總結”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！
復制代碼 代碼如下:
–sql 基本操作
– 創建數據庫
create database Studets
– 創建表
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 )
– 查看表信息
select * from student select sno as 學號 from student select * from course select * from sc
– 修改表
– 插入列
alter table student add scome datetime
– 修改列的字段類型 alter table student alter column scome char(50)
– 刪除 – 刪除列
alter table student drop column scome
– 刪除表 drop table student drop table course drop table sc
– 完整性約束實現
–sno 非空唯一，ssex 檢查約束，sage 默認大小
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( 男 , 女)), )
– 刪除表的約束 alter table student drop constraint ssex
– 添加字段約束 alter table student add constraint ssex check(sex in( 男 , 女))
– 添加主鍵約束 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 )
– 關聯表主鍵已經存在，可以如下操作添加主鍵和外鍵約束
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]
– 創建 sc 后，通過如下修改主外鍵
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)
– 創建索引。
分為聚簇索引（clustered 物理順序）和非聚簇索引（nonclustered 邏輯順序，可多個）
復制代碼 代碼如下:
–not null 約束字段時候。會創建一個系統內置的約束鍵值，并且這種非空判斷，通過索引查詢實現 – 的，索引默認創建一個系統索引
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
– 插入數據 select * from student
alter table student alter column sno char(10)
insert into student values(10021 , 張三 , 男 ,20, 計科系)
insert into student values(10022 , 王朝 , 女 ,18, 軟件)
insert into student values(10023 , 朱元璋 , 男 ,20, 管理)
insert into student values(10024 , 劉徹 , 男 ,18, 軍事)
insert into student values(10025 , 劉表 , 男 ,20, 商學系)
insert into student values(10026 , 白居易 , 男 ,19, 文法)
insert into student values(10027 , 李清照 , 女 ,24, 文法)
select * from course insert into course values(001 , 數據庫 , 005 ,4)
insert into course values(002 , 高等數學 , ,2)
insert into course values(003 , 信息系統 , 001 ,4)
insert into course values(004 , 操作系統 , 006 ,2)
insert into course values(005 , 數據結構 , 007 ,3)
insert into course values(006 , 數據處理 , ,2)
insert into course values(007 , C 語言 , 006 ,5)
select * from sc insert 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 – 查詢操作
– 查詢 select * from student select * from course select * from sc
– 去掉重復行 select distinct sno from sc
– 格式化查詢
select sname as 姓名 ,2013-sage as 出生日期 from student
select sname, 出生日期 ,2013-sage from student
select 姓名 =sname, 出生日期 =2013-sage from student
– 條件查詢
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)
– 匹配查詢
select * from student where sname like 劉__
select * from student where sname like _表__
select * from student where sname like % 表 %
– 算術元算查詢
select grade*(1+0.2) as 總成績,grade/(10) as 績點 from sc
– 分組函數查詢
select COUNT(*) as 總人數 from student
select COUNT(distinct sno) as 選修的總人數 from sc
select AVG(grade) as 平均成績 from sc where sno= 10021
select MAX(grade) as MAX 成績 from sc where sno= 10021
select MIN(grade) as MIN 成績 from sc where sno= 10021
select SUM(grade) as 總成績 from sc where sno= 10021
select SUM(grade)/COUNT(grade) as 平均成績 from sc where sno= 10021
select SUM(grade) as 總成績 from sc group by sno having sum(grade) 100 –
- 連接查詢、
– 等值連接
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
– 自身連接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno
select B.sname as 同一個系 from student A,student B where A.sname= 白居易 and A.sdept=B.sdept
– 外連接
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
– 復合條件連接
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 and B.cno= 002
– 字符串連接查詢
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
– 子查詢
select * from student where sage (select AVG(sage) from student)
– 是否存在的查詢
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 創建用戶 sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
– 權限分配和收回
grant select on student to bnc
select * from student
revoke select on student from bnc
– 視圖的創建
create view VIEW_STUGrade(學號, 姓名, 課程, 成績)
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= 軟件
– 查看視圖
select * from VIEW_STUGrade
– 視圖修改
alter view VIEW_STUGrade(學號, 姓名, 課程, 成績)
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= 軟件
with check option
– 更新失敗后不影響視圖查看
– 視圖更新
update VIEW_STUGrade set 姓名 = 王超 where 學號 = 10022 select * from student where sno= 10022
/* 1, 可更新視圖：a, 單個基本表導出的 2，不可更新視圖 a 兩個以上基本表導出的 b 視圖字段來自表達式或者函數 c 嵌套查詢的表 d 分組子句使用 distinct */
– 刪除視圖 drop view VIEW_STUGrade
– 高級 sql 編程
– 數據類型 1,int 2,smallint 3,tinyint (0–255) 4,bigint 5char 固定長度 800. 如：學號，姓名 6，varchar 可變長度小于 800 7,text 2GB 8，nvarchar1–4000 */
– 運算符和通配符
select GETDATE()-1 昨天,GETDATE() 今天,GETDATE()+1 明天
select 59 12
select 59|12
select 59^12
– 模糊查詢
select * from student where sname like % 劉 %
select * from student where sno like 1002[5-9]
– 控制流程語句
declare @name char(10) set @name= 司馬相如
print @name
– 輸出一個表達式，不能進行查詢 select @name
– 輸出多個表達式
declare @a nvarchar(50),@b nvarchar(50)
set @a=33 set @b=34 — 簡寫 select@a=33，@b=34
if @a @b
print 最小值是：+@a
else
print 最大值是：+@b
–waitfor 間隔一段時間執行
waitfor delay 00:00:04 print 推遲 4 秒執行
waitfor time 17:45:50 print 等待這一時刻執行
– 創建函數
CREATE FUNCTION GetTime (@date1 datetime, @date2 datetime)
RETURNS TABLE
AS RETURN (
select datediff(dd,@date1,@date2) 日差,datediff(mm,@date1,@date2) 月差, datediff(yy,@date1,@date2) 年差
)
– 創建存儲過程，
– 查看
GO create proc [dbo].[sel] (
@sno char(10)
)
as
select * from student where sno=@sno
exec sel @sno= 10021
– 查看
GO create proc sel2
as
select * from student
exec sel2
– 修改
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= 女
– 刪除
GO create proc dele @sno char(10)
as
delete student where sno=@sno
select * from student
exec dele @sno= 10029
– 插入
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
– 查詢操作
– 查詢
select * from student select * from course select * from sc
– 去掉重復行 select distinct sno from sc
– 格式化查詢
select sname as 姓名 ,2013-sage as 出生日期 from student
select sname, 出生日期 ,2013-sage from student
select 姓名 =sname, 出生日期 =2013-sage from student
– 條件查詢
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)
– 匹配查詢
select * from student where sname like 劉__
select * from student where sname like _表__
select * from student where sname like % 表 %
– 算術元算查詢
select grade*(1+0.2) as 總成績,grade/(10) as 績點 from sc
– 分組函數查詢
select COUNT(*) as 總人數 from student
select COUNT(distinct sno) as 選修的總人數 from sc select AVG(grade) as 平均成績 from sc where sno= 10021
select MAX(grade) as MAX 成績 from sc where sno= 10021
select MIN(grade) as MIN 成績 from sc where sno= 10021
select SUM(grade) as 總成績 from sc where sno= 10021
select SUM(grade)/COUNT(grade) as 平均成績 from sc where sno= 10021
select SUM(grade) as 總成績 from sc group by sno having sum(grade) 100
– 連接查詢、– 等值連接
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
– 自身連接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno select B.sname as 同一個系 from student A,student B where A.sname= 白居易 and A.sdept=B.sdept
– 外連接
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
- 復合條件連接
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 and B.cno= 002
– 字符串連接查詢
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
– 子查詢
select * from student where sage (select AVG(sage) from student)
– 是否存在的查詢
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 創建用戶
sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
– 權限分配和收回
grant select on student to bnc
select * from student
revoke select on student from bnc
– 視圖的創建
create view VIEW_STUGrade(學號, 姓名, 課程, 成績)
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= 軟件
– 查看視圖
select * from VIEW_STUGrade
– 視圖修改
alter view VIEW_STUGrade(學號, 姓名, 課程, 成績) 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= 軟件
with check option
– 更新失敗后不影響視圖查看 – 視圖更新
update VIEW_STUGrade set 姓名 = 王超 where 學號 = 10022 select * from student where sno= 10022
/* 1, 可更新視圖：a, 單個基本表導出的 2，不可更新視圖 a 兩個以上基本表導出的 b 視圖字段來自表達式或者函數 c 嵌套查詢的表 d 分組子句使用 distinct */
– 刪除視圖 drop view VIEW_STUGrade
– 觸發器
use Studets
GO create trigger insert_Tri
ON student after
insert as print 有新數據插入！
GO create trigger update_Tri
on student after
update as print 有數據更新！
GO create trigger delete_Tri
on student after
delete as print 有數據刪除！
– 修改觸發器
GO alter trigger delete_Tri
on student after delete
as
if 王帥 in (select sname from deleted)
print 該信息不許刪除！
rollback transaction
– 執行存儲過程查看觸發器使用情況
exec sel @sno= 10021
exec inser @sno= 10029 , @sname= 王帥 , @sex= 男 , @sage=25, @sdept= 國貿
exec updat @sno= 10029 , @sex= 女
exec dele @sno= 10029
– 查看, 修改, 刪除觸發器
/* sp_*+ 觸發器名稱
sp_helptext：觸發器正文信息 sp_help: 查看一般信息，觸發器名稱，屬性，創建時間，類型 sp_depends：引用或指定表的所有觸發器 sp_helptrigger：指定信息 */ sp_help delete_Tri
sp_helptext delete_Tri
sp_depends delete_Tri
sp_helptrigger student
– 刪除觸發器
drop trigger delete_Tri
到此，關于“SQL Server 數據庫的基本操作語句總結”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！
