--在SQL 查詢中：from 後最多可以跟多少張表或視圖：256
--在SQL 語句中出現 Order by,查詢時，先排序，後取
--在SQL 中，一個欄位的最大容量是8000，而對於nvarchar(4000),由於nvarchar 是Unicode碼。
--在SQL 語句中出現 Order by,查詢時，先排序，後取
--在SQL 中，一個欄位的最大容量是8000，而對於nvarchar(4000),由於nvarchar 是Unicode碼。
--創建資料庫]
--創建資料庫的文件的存放地址
--1.打開資料庫外圍配置（開始菜單——>所有程序——>Microsoft SQL Server 2005——>配置工具——>SQL Server 外圍應用配置器——>功能的外圍應用配置器——>xp_cmdshell(啟用即可)）
--2.語法編寫(調用存儲過程)創建路徑
exec xp_cmdshell'mkdir F:\pro',no_output
go
exec xP_cmdshell'dir F:\pro'--在E盤中查看pro的文件夾
go
--use master
--select * from sysdatabases
--查找創建資料庫是否存在於sysdatabases中
if exists(select * from sysdatabases where name='test')
--刪除資料庫
drop database test
--創建資料庫
create database test
on
(
name='test_data',
filename='F:\pro\test_data.mdf',
size=3,
filegrowth=5,
maxsize=100
)
log on
(
name='test_log',
filename='F:\pro\test_log.ldf',
size=3,
filegrowth=5,
maxsize=50
)
go
--使用當前指定的資料庫
use test
--創建表
--班級表
create table class
(
--創建主鍵創建與自增
classid int identity(1,1) primary key,
classname varchar(20) not null,
classstartime datetime default(getdate())
)
--創建學員表
create table stu
(
stuid int identity(1,1) primary key,
stuname varchar(20) not null,
stusex char(2) default('男'),
stuage int
)
--數據查詢
select * from class
select * from stu
--去重查詢
select distinct classname from class
--數據插入方法一
insert class values('T108',default)
insert class values('Tc',default)
--數據插入方法二
insert class(classname,classstartime)
select 'T109',getdate()
--數據插入方法三
insert class(classname,classstartime)
select 'T110',getdate() union
select 'T111',getdate() union
select 'T112',getdate() union
select 'T113',getdate()
--數據插入方法四(表與表的插入)
select class.classname,class.classtartime
into 插入表
from class
--數據修改
update class set classname='T107' where classname='T113'
--數據批量修改
update class set classname='T000' where classname in ('T110','T111','T112')
--數據刪除
delete from class where classid=16
--數據批量刪除
delete from class where classname in ('T110','T111','T112','T113')
--數據清空
delete from class
truncate table class
1、TRUNCATE在各種表上無論是大的還是小的都非常快。如果有ROLLBACK命令DELETE將被撤銷，而TRUNCATE則不會被撤銷。
2、TRUNCATE是一個DDL語言，向其他所有的DDL語言一樣，他將被隱式提交，不能對TRUNCATE使用ROLLBACK命令。
3、TRUNCATE將重新設置高水平線和所有的索引。在對整個表和索引進行完全瀏覽時，經過TRUNCATE操作後的表比DELETE操作後的表要快得多。
4、TRUNCATE不能觸發任何DELETE觸發器。
5、不能授予任何人清空他人的表的許可權。
6、當表被清空後表和表的索引講重新設置成初始大小，而delete則不能。
7、不能清空父表。
--‘%’模糊查詢
select * from class where classname like '%T%'
select * from class where classname like '%T'
select * from class where classname like 'T%'
--bettween and范圍之間的查詢包括1與18
select * from class where classid between 1 and 18
--查詢數據記錄的前幾條
select top(5) * from class
--隨機取出數據
select top(5) * from class order by newid()
select * from class order by newid()--隨機排序
-----------------------------------------------按姓氏筆畫排序-----------------------
insert stu(stuname,stusex,stuage)
select '張三','男',18 union
select '李四','男',28 union
select '王五','男',48 union
select '趙六','女',23 union
select '郭小四','女',12 union
select '劉奇','男',26 union
select '諸葛格','男',54 union
select '王雙','男',11
select * from stu order by stuname collate Chinese_prc_stroke_ci_as
--隨機選擇記錄
select newid()
--按數據的百分比查詢
select percent(10) from class
--查詢數據記錄數
select count(*) from class
--去重查找數據記錄數量
select count(distinct classname) from class
--'_'匹配任意一個字元
select * from class where classname like 'T_'--('T___')
select * from class where classname like '_113'
--[A-Z]查詢A-Z中任意一個相符合的記錄
select * from class where classname like '[A-Z]'
select * from class where classname in ('T','T108')
select * from class where classname like '[T,1]'
--與上相反
select * from class where classname like 'T10[^A-Z]'
--order by排序ASC升序，DESC降序
select * from class order by classstartime Asc
select * from class order by classstartime DEsc
--命名列的別名as或=
select classid as 編號,classname as 班級名稱,classstartime as 創班時間 from class
select 編號=classid,班級名稱=classname,創班時間=classstartime from class
--合並列datatiem轉換為varchar類型
select '班級信息'=classname+'.'+convert(varchar(20),classstartime) from class
select classname+'.'+convert(varchar(20),classstartime) as '班級信息' from class
--查詢空行 is null(空) 與 is not null(非空)
select * from class where classname is not null
select * from class where classname is null
--創建資料庫的文件的存放地址
--1.打開資料庫外圍配置（開始菜單——>所有程序——>Microsoft SQL Server 2005——>配置工具——>SQL Server 外圍應用配置器——>功能的外圍應用配置器——>xp_cmdshell(啟用即可)）
--2.語法編寫(調用存儲過程)創建路徑
exec xp_cmdshell'mkdir F:\pro',no_output
go
exec xP_cmdshell'dir F:\pro'--在E盤中查看pro的文件夾
go
--use master
--select * from sysdatabases
--查找創建資料庫是否存在於sysdatabases中
if exists(select * from sysdatabases where name='test')
--刪除資料庫
drop database test
--創建資料庫
create database test
on
(
name='test_data',
filename='F:\pro\test_data.mdf',
size=3,
filegrowth=5,
maxsize=100
)
log on
(
name='test_log',
filename='F:\pro\test_log.ldf',
size=3,
filegrowth=5,
maxsize=50
)
go
--使用當前指定的資料庫
use test
--創建表
--班級表
create table class
(
--創建主鍵創建與自增
classid int identity(1,1) primary key,
classname varchar(20) not null,
classstartime datetime default(getdate())
)
--創建學員表
create table stu
(
stuid int identity(1,1) primary key,
stuname varchar(20) not null,
stusex char(2) default('男'),
stuage int
)
--數據查詢
select * from class
select * from stu
--去重查詢
select distinct classname from class
--數據插入方法一
insert class values('T108',default)
insert class values('Tc',default)
--數據插入方法二
insert class(classname,classstartime)
select 'T109',getdate()
--數據插入方法三
insert class(classname,classstartime)
select 'T110',getdate() union
select 'T111',getdate() union
select 'T112',getdate() union
select 'T113',getdate()
--數據插入方法四(表與表的插入)
select class.classname,class.classtartime
into 插入表
from class
--數據修改
update class set classname='T107' where classname='T113'
--數據批量修改
update class set classname='T000' where classname in ('T110','T111','T112')
--數據刪除
delete from class where classid=16
--數據批量刪除
delete from class where classname in ('T110','T111','T112','T113')
--數據清空
delete from class
truncate table class
1、TRUNCATE在各種表上無論是大的還是小的都非常快。如果有ROLLBACK命令DELETE將被撤銷，而TRUNCATE則不會被撤銷。
2、TRUNCATE是一個DDL語言，向其他所有的DDL語言一樣，他將被隱式提交，不能對TRUNCATE使用ROLLBACK命令。
3、TRUNCATE將重新設置高水平線和所有的索引。在對整個表和索引進行完全瀏覽時，經過TRUNCATE操作後的表比DELETE操作後的表要快得多。
4、TRUNCATE不能觸發任何DELETE觸發器。
5、不能授予任何人清空他人的表的許可權。
6、當表被清空後表和表的索引講重新設置成初始大小，而delete則不能。
7、不能清空父表。
--‘%’模糊查詢
select * from class where classname like '%T%'
select * from class where classname like '%T'
select * from class where classname like 'T%'
--bettween and范圍之間的查詢包括1與18
select * from class where classid between 1 and 18
--查詢數據記錄的前幾條
select top(5) * from class
--隨機取出數據
select top(5) * from class order by newid()
select * from class order by newid()--隨機排序
-----------------------------------------------按姓氏筆畫排序-----------------------
insert stu(stuname,stusex,stuage)
select '張三','男',18 union
select '李四','男',28 union
select '王五','男',48 union
select '趙六','女',23 union
select '郭小四','女',12 union
select '劉奇','男',26 union
select '諸葛格','男',54 union
select '王雙','男',11
select * from stu order by stuname collate Chinese_prc_stroke_ci_as
--隨機選擇記錄
select newid()
--按數據的百分比查詢
select percent(10) from class
--查詢數據記錄數
select count(*) from class
--去重查找數據記錄數量
select count(distinct classname) from class
--'_'匹配任意一個字元
select * from class where classname like 'T_'--('T___')
select * from class where classname like '_113'
--[A-Z]查詢A-Z中任意一個相符合的記錄
select * from class where classname like '[A-Z]'
select * from class where classname in ('T','T108')
select * from class where classname like '[T,1]'
--與上相反
select * from class where classname like 'T10[^A-Z]'
--order by排序ASC升序，DESC降序
select * from class order by classstartime Asc
select * from class order by classstartime DEsc
--命名列的別名as或=
select classid as 編號,classname as 班級名稱,classstartime as 創班時間 from class
select 編號=classid,班級名稱=classname,創班時間=classstartime from class
--合並列datatiem轉換為varchar類型
select '班級信息'=classname+'.'+convert(varchar(20),classstartime) from class
select classname+'.'+convert(varchar(20),classstartime) as '班級信息' from class
--查詢空行 is null(空) 與 is not null(非空)
select * from class where classname is not null
select * from class where classname is null
--查詢使用函數
-----------------------------字元串操作函數-----------------------
-----------------------------查詢位置------------------------------
-- charindex用來尋找一個指定的字元串在另一個字元串中的起始位置charindex('匹配字元','被查欄位')PATINDEX函數支持使用通配符來進行搜索，然而CHARINDEX不支持通佩符
--聲明局部變數
declare @classname varchar(20)
--局部變數賦值
select @classname=classname from class where classid=17
--charindex方法查詢位置
select charindex('T',@classname)
--查詢局部變數
select @classname
--patindex方法查詢
select patindex('%TS%',@classname)
select patindex('[A-Z]q%',@classname)
select patindex('[^1-9]q%',@classname)
-----------------------------查詢長度------------------------------
--len與datalength
--len要計算的字元串返回指定字元串表達式的字元數，其中不包含尾隨空格,如果 expression 的數據類型為 varchar(max)、nvarchar(max) 或 varbinary(max)，則為 bigint；否則為 int。不可以對ntext欄位進行計算
select len('ACCP
select len('ACCPAS
select len('
--datalength任何數據類型的表達式返回用於表示任何表達式的位元組數。任何數據類型的表達式,返回用於表示任何表達式的位元組數。如果 expression 的數據類型為 varchar(max)、nvarchar(max) 或 varbinary(max) 數據類型，則返回 bigint；否則返回 int。對於 varchar、varbinary、text、image、nvarchar 和 ntext 數據類型，DATALENGTH 尤其有用，因為這些數據類型可以存儲長度可變的數據。NULL 的 DATALENGTH 的結果是 NULL。
select datalength('ACCPSS
select datalength('
select datalength('AC
-----------------------------大小寫轉換------------------------------
--Lower轉換成小寫
select lower('ASCccp')
--upper轉換成大寫
select upper('Asccp')
-----------------------------去空格----------------------------------
--ltrim去除左邊的空格
select datalength(ltrim('
select datalength(ltrim('sssss
select datalength(ltrim('ss
--rtrim去除右邊的空格
select datalength(rtrim('
select datalength(rtrim('sssss
select datalength(rtrim('ss
-----------------------------返回查詢指定的字元-----------------------
--right從字元串右邊返回指定數目的字元right('查詢的字元',查詢的字元數)
select right('ABCDEFGHIJKLMopqrst',3)--rst
--left從字元串左邊返回指定數目的字元left('查詢的字元',查詢的字元數)
select left('ABCDEFGHIJKLMopqrst',3)--ABC
-----------------------------替換字元-----------------------
--replace
select replace('AVCDDEEADFDFEHJ','D','k')
-----------------------------刪除並插入字元插入-----------------------
--stuff('被操作字元',刪除的開始位置,刪除的數量,'刪除出插入的字元串')
select stuff('ACCP.NETjava',2,3,'123')
----------------------------獲或ASCII碼---------------------------------
--ascII('單字元')
select ascII('A')
select ascII('a')
----------------------------將數字轉換成ASCII碼--------------------------
--char(數字)范圍0-255
select CHAR(65)
----------------------------將數值型轉換成字元類型--------------------------
select type('aa')
ISNUMERIC
-----------------------------字元串操作函數-----------------------
-----------------------------查詢位置------------------------------
-- charindex用來尋找一個指定的字元串在另一個字元串中的起始位置charindex('匹配字元','被查欄位')PATINDEX函數支持使用通配符來進行搜索，然而CHARINDEX不支持通佩符
--聲明局部變數
declare @classname varchar(20)
--局部變數賦值
select @classname=classname from class where classid=17
--charindex方法查詢位置
select charindex('T',@classname)
--查詢局部變數
select @classname
--patindex方法查詢
select patindex('%TS%',@classname)
select patindex('[A-Z]q%',@classname)
select patindex('[^1-9]q%',@classname)
-----------------------------查詢長度------------------------------
--len與datalength
--len要計算的字元串返回指定字元串表達式的字元數，其中不包含尾隨空格,如果 expression 的數據類型為 varchar(max)、nvarchar(max) 或 varbinary(max)，則為 bigint；否則為 int。不可以對ntext欄位進行計算
select len('ACCP
select len('ACCPAS
select len('
--datalength任何數據類型的表達式返回用於表示任何表達式的位元組數。任何數據類型的表達式,返回用於表示任何表達式的位元組數。如果 expression 的數據類型為 varchar(max)、nvarchar(max) 或 varbinary(max) 數據類型，則返回 bigint；否則返回 int。對於 varchar、varbinary、text、image、nvarchar 和 ntext 數據類型，DATALENGTH 尤其有用，因為這些數據類型可以存儲長度可變的數據。NULL 的 DATALENGTH 的結果是 NULL。
select datalength('ACCPSS
select datalength('
select datalength('AC
-----------------------------大小寫轉換------------------------------
--Lower轉換成小寫
select lower('ASCccp')
--upper轉換成大寫
select upper('Asccp')
-----------------------------去空格----------------------------------
--ltrim去除左邊的空格
select datalength(ltrim('
select datalength(ltrim('sssss
select datalength(ltrim('ss
--rtrim去除右邊的空格
select datalength(rtrim('
select datalength(rtrim('sssss
select datalength(rtrim('ss
-----------------------------返回查詢指定的字元-----------------------
--right從字元串右邊返回指定數目的字元right('查詢的字元',查詢的字元數)
select right('ABCDEFGHIJKLMopqrst',3)--rst
--left從字元串左邊返回指定數目的字元left('查詢的字元',查詢的字元數)
select left('ABCDEFGHIJKLMopqrst',3)--ABC
-----------------------------替換字元-----------------------
--replace
select replace('AVCDDEEADFDFEHJ','D','k')
-----------------------------刪除並插入字元插入-----------------------
--stuff('被操作字元',刪除的開始位置,刪除的數量,'刪除出插入的字元串')
select stuff('ACCP.NETjava',2,3,'123')
----------------------------獲或ASCII碼---------------------------------
--ascII('單字元')
select ascII('A')
select ascII('a')
----------------------------將數字轉換成ASCII碼--------------------------
--char(數字)范圍0-255
select CHAR(65)
----------------------------將數值型轉換成字元類型--------------------------
select type('aa')
ISNUMERIC
----------------------------判斷是否為有效的數字類型---------------------------
----如果輸入表達式的計算值為有效的整數、浮點數、money 或 decimal 類型時，ISNUMERIC 返回 1；否則返回 0。返回值為 1 時，指示可將 expression 至少轉換為上述數值類型中的一種。
select isnumeric('123')--1
select isnumeric('123f')--0
----------------------------將數據類型轉換成字元類型----------------------------
select str('12300')
----------------------------字元串截取----------------------------
--substring('被操作字元串',從左邊的開始截取的位置,截取長度)
select substring('abcdefgh',1,2)--ab
----------------------------返回特定的字元串----------------------
--quotename('被操作的字元')默認用[]框定quotename('被操作的字元'，'運算符')
select quotename('sadfsdf')--[asdfsdf]
select quotename('sadfsdf','<>')--<asdfsdf>
select quotename('sadfsdf','{}')--{asdfsdf}
select quotename('sadfsdf','()')--(asdfsdf)
select quotename('sadfsdf','.,')--null
----------------------------返回重復多次的字元----------------------
--replicate('被操作字元串',重復次數)
select replicate('aacdffe',2)--aacdffeaacdffe
----------------------------將字元串的順序顛倒----------------------
select reverse('AABBCC')--CCBBAA
----------------------------返回一個指定長度空白的字元串------------------
--space(空個數)
select 'A'+space(3)+'c'--A c
declare @lens varchar(50)
set @lens='A'+space(3)+'c'
select datalength(@lens)--長度為5
----------------------------數據類型轉換函數------------------------------
--cast('被轉換的字元串' as 數據類型)
select cast('123' as float)
select cast('123' as int)
--convert(數據類型,'被轉換的字元串')
select convert(float,'123')
----如果輸入表達式的計算值為有效的整數、浮點數、money 或 decimal 類型時，ISNUMERIC 返回 1；否則返回 0。返回值為 1 時，指示可將 expression 至少轉換為上述數值類型中的一種。
select isnumeric('123')--1
select isnumeric('123f')--0
----------------------------將數據類型轉換成字元類型----------------------------
select str('12300')
----------------------------字元串截取----------------------------
--substring('被操作字元串',從左邊的開始截取的位置,截取長度)
select substring('abcdefgh',1,2)--ab
----------------------------返回特定的字元串----------------------
--quotename('被操作的字元')默認用[]框定quotename('被操作的字元'，'運算符')
select quotename('sadfsdf')--[asdfsdf]
select quotename('sadfsdf','<>')--<asdfsdf>
select quotename('sadfsdf','{}')--{asdfsdf}
select quotename('sadfsdf','()')--(asdfsdf)
select quotename('sadfsdf','.,')--null
----------------------------返回重復多次的字元----------------------
--replicate('被操作字元串',重復次數)
select replicate('aacdffe',2)--aacdffeaacdffe
----------------------------將字元串的順序顛倒----------------------
select reverse('AABBCC')--CCBBAA
----------------------------返回一個指定長度空白的字元串------------------
--space(空個數)
select 'A'+space(3)+'c'--A c
declare @lens varchar(50)
set @lens='A'+space(3)+'c'
select datalength(@lens)--長度為5
----------------------------數據類型轉換函數------------------------------
--cast('被轉換的字元串' as 數據類型)
select cast('123' as float)
select cast('123' as int)
--convert(數據類型,'被轉換的字元串')
select convert(float,'123')
-----------------------------日期操作函數-----------------------
--year
-- yy, yyyy
--
--quarter 季節1——3月為1，4——6月為2，7——9月為3，10——12月為4
-- qq, q
--
--month 月份
-- mm, m
--
--dayofyear 年中的第幾天
-- dy, y
--
--day 日
-- dd, d
--
--week 年中的第幾個星期
-- wk, ww
--
--weekday 星期
-- dw
--
--hour 小時
-- hh
--
--minute 分
-- mi, n
--
--second 秒
-- ss, s
--
--millisecond 毫秒
-- ms
--
------------------------------------getdate()獲取當前系統時間-------------------------
select getdate()
print getutcdate()--獲取utc時間
--------------------------------------日期累加函數----------------------------------
--dateadd(選擇增加部分yy[mm][dd],增加的數值,操作的日期必須是時間類型)將指定的數值添加到指定的部分yy(year)-年mm(month)-月dd(day)-日
select dateadd(mm,4,getdate())--2011-01-15 10:12:00.403
select dateadd(dd,4,cast('01/01/2010' as datetime))--2010-01-05 00:00:00.000
select dateadd(yy,4,convert(datetime,'01/01/2010'))--2014-01-01 00:00:00.000
--------------------------------------日期對比區別函數-------------------------------
--datediff(選擇計算部分yy[mm][dd],'日期1','日期2')後者減掉前者
print datediff(yy,'01/01/2009',getdate())--1
print datediff(yy,'01/01/2009','01/02/2020')--11
--------------------------------------查看日期的某部分-------------------------------
--datename(查看部分,'日期')查看日期的某部分
print getdate()
print datename(y,getdate())--258
print datename(weekday,getdate())--當前的星期
print datename(mm,getdate())--09
--datepart以整數形式返回
print getdate()
print datepart(yyyy,getdate())
print datepart(mm,getdate())--9
print datepart(weekday,getdate())--4
-------------------------------------查詢部分-----------------------------------
select day('01/02/2010')--查看日02
select month('01/02/2010')--查看月01
select year('01/02/2010')--查看年2010
select getdate()
print getutcdate()--獲取utc時間
--------------------------------------日期累加函數----------------------------------
--dateadd(選擇增加部分yy[mm][dd],增加的數值,操作的日期必須是時間類型)將指定的數值添加到指定的部分yy(year)-年mm(month)-月dd(day)-日
select dateadd(mm,4,getdate())--2011-01-15 10:12:00.403
select dateadd(dd,4,cast('01/01/2010' as datetime))--2010-01-05 00:00:00.000
select dateadd(yy,4,convert(datetime,'01/01/2010'))--2014-01-01 00:00:00.000
--------------------------------------日期對比區別函數-------------------------------
--datediff(選擇計算部分yy[mm][dd],'日期1','日期2')後者減掉前者
print datediff(yy,'01/01/2009',getdate())--1
print datediff(yy,'01/01/2009','01/02/2020')--11
--------------------------------------查看日期的某部分-------------------------------
--datename(查看部分,'日期')查看日期的某部分
print getdate()
print datename(y,getdate())--258
print datename(weekday,getdate())--當前的星期
print datename(mm,getdate())--09
--datepart以整數形式返回
print getdate()
print datepart(yyyy,getdate())
print datepart(mm,getdate())--9
print datepart(weekday,getdate())--4
-------------------------------------查詢部分-----------------------------------
select day('01/02/2010')--查看日02
select month('01/02/2010')--查看月01
select year('01/02/2010')--查看年2010
------------------------------------數學函數------------------------------------
create table stuscore
(
id int identity(1,1) primary key,
stid int not null,
pid int not null,
score float not null
)
insert stuscore values(1,1,23)
insert stuscore values(9,1,-10)
insert stuscore(stid,pid,score)
select 2,1,100 union
select 3,1,100 union
select 4,1,100 union
select 5,1,100 union
select 6,1,100 union
select 7,1,100 union
select 8,1,100 union
select 1,2,100 union
select 2,2,100 union
select 3,2,100 union
select 4,2,100 union
select 5,2,100 union
select 6,2,100 union
select 7,2,100 union
select 8,2,100 union
select 1,3,100 union
select 2,3,100 union
select 3,3,100 union
select 4,3,100 union
select 5,3,100 union
select 6,3,100 union
select 7,3,100 union
select 8,3,100
select * from stuscore
-------------------------------------總和數------------------------
select sum(score) from stuscore--1370
--分組之後的結果
select sum(score) from stuscore group by pid
-------------------------------------平均數------------------------
select avg(score) from stuscore--57.0833333333333
--分組之後的結果
select avg(score) from stuscore group by pid
-------------------------------------最大值------------------------
select max(score) from stuscore--100
--分組之後的結果
select max(score) from stuscore group by pid
-------------------------------------最小值------------------------
select min(score) from stuscore--10
--分組之後的結果
select min(score) from stuscore group by pid
-------------------------------------絕對值------------------------
select abs(score) from stuscore --編號25為-10被更改為10
-------------------------------------求個數------------------------
select count(score) from stuscore--25條記錄數
-------------------------------------求標准偏差------------------------
select stdev(score) from stuscore
-------------------------------------所有填入的標准偏差------------------------
select stdevp(score) from stuscore
-------------------------------------所有值的統計方差------------------------
select var(score) from stuscore
-------------------------------------隨機數------------------------------------
--rand()產生一個隨機數
select rand()
select rand((datepart(mm,getdate())*100000)+(datepart(ss,getdate())*1000)+datepart(ms,getdate()))
-------------------------------------正弦、余弦、正切、余切-------------------------
print sin(1)
print asin()
print cos(1)
print acos()
print tan(1)
print atan()
print cot()
-------------------------------------圓周率--------------------------------------
print pi()
-------------------------------------求平方根--------------------------------------
print sqrt(25)--5
-------------------------------------求對數--------------------------------------
print log(25)
-------------------------------------返回以10為底的對數---------------------------
print log10(100)--2
--------------------------------------取整--------------------------------------
--ROUND(操作數,小數點後第幾位,是否進行四舍五入)默認0進行四舍五入,其它進行數據截取掉
print ROUND(120.505,0,0)--121.000
print ROUND(120.505,1,0)--120.500
print ROUND(120.505,0,1)--120.00
--------------------------------------求次方值-------------------------------------
--power(數值,次方數)
print power(100,2)--10000
---------------------------------------判斷數值正負0-------------------------------------
-- numeric_expr的值為正數、0或負數時分別返回1、0、-1數值
select sign(100)--1
select sign(-100)---1
select sign(0)--0
---------------------------------------數值取整--------------------------------------
select ceiling(12.33)--無論小數點後是什么都在整數後加1(13)
select ceiling(12.83)--無論小數點後是什么都在整數後加1(13)
select floor(12.33)--無論小數點後是什么都取整數(12)
select floor(12.83)--無論小數點後是什么都取整數(12)
---------------------------------------求角度-------------------------------------------
--當給出弧度為單位的角度時，返回相應的以度數為單位的角度。
print degrees(pi())--半圓
print degrees(2*pi())--圓
---------------------------------------求弧度-------------------------------------------
print radians(180)--即pi()=3.1415926
print radians(90)--1
-----------------------------------------返回所給的float表達式的指數值。-----------------------
--數字的指數是常量 e 使用該數字進行冪運算。例如，EXP(1.0) = e^1.0 = 2.71828182845905，而 EXP(10) = e^10 = 22026.4657948067。數字的自然對數的指數是數字本身：EXP (LOG (n)) = n。數字的指數的自然對數是數字本身：LOG (EXP (n)) = n。
select exp(1)
-----------------------------------------浮點數的平方-----------------------------------
--求圓的面積列子2*pi()*r*r
select pi()*square(2)*2
---------------------------------------------資料庫與表操作----------------------------------------------------
-----------------約束類型
--主鍵約束
primary [key]
--唯一約束(允許為空，但只能一次)
unique
--檢查約束
check
--默認約束
default
--外鍵約束
foreign
--刪除資料庫
drop database test
--刪除表
drop table class
---------------------------------------------------表中添加約束
alter table 表名
add constraint 約束名 約束類型 具體約束說明
--添加主鍵
alter table stu
add constraint pk primary key(stuid)
--添加唯一
alter table stu
add constraint uq unique(stuid)
--添加默認
alter table stu
add constraint df default('sdfasdf') for stuname
--檢查約束
alter table stu
add constraint ck check(stu between 10 and 100)
--添加外鍵
alter table stu
add constraint fk foreign key(stuid) references stuscore(stuid)
stu為從表stuscore為主表(引用表為主表)
---------------------------------------------------表中刪除約束
alter table stu
drop constraint 約束名
--刪除主鍵
alter table stu
drop constraint fk
-------------------------------------------變數
--局部變數
declare @變數名 類型
--
declare @name varchar(20)
--局部變數賦值
set @name='sadf'
select @name='werre'
select @name
--全局變數
@@error --最後一個T-SQL錯誤的錯誤號
@@identity --最後一次插入的標識值
@@language --當前使用語言
@@max_connections --可以創建的同時連接的最大數目
@@rowcount --受上一條T-SQL語句影響的行數
@@servername --本地伺服器的名稱
@@timeticks --當前計算機上的每刻度的微妙數
@@version --SQL Server的版本信息
--邏輯控制語句
--if-else
if(條件)
begin
語句
end
else
語句
--while
while(條件)
begin
語句
[break]
end
--case
case
when 條件 then 結果
when 條件 then 結果
[else]
end
--子查詢
select * from 表名 where 欄位>[in](select.....)
--in 和 not in
select * from 表名 where 欄位 in[not in] (select.....)
--exists和not exists
if exists(select * from sysdatabases where name='test')
begin
print 'sadf'
end
else
begin
print '124'
end
----------------------------事務
--
--begin distributed transaction
--原型：
-- Begin destributed tran [ saction] [transaction_name! @tran_name_variable]
--例如：
-- Use northwind
-- Begin destributed transaction
-- Update empoyees //對表empolees 進行更新
-- Set firstname=’mcdonald’ where homephone = ‘(206) 555-9875’
-- Commit transaction
-- Go
--
--.3
--原型：
-- Rollback [tran [saction] [transaction_name] @tran_name_variable | savepoint_name |
--@savepoint_variable]
--
-- Transadtion 給begin transaction上的事務指派的名稱。
-- @tran_name_bariable
-- Savepoint_name
-- @savepoint_variable 是用戶定義的、含有有效保存點名稱的變數的名稱。
-- 例如：
-- Begin transaction royaltychange
-- Update titleauthor
--
--
--
-- And titleauthor.title_id=titles.title_id
-- And title=’The Gourmet Microwave’
-- Update titleauthor
--
--
--
--
--
-- Save transaction percentchanged
-- Update titles
--
--
-- Select (price * royalty * ytd_sales)* royaltyper
--
--
--
-- Rollback transaction percentchanged
-- Commit transaction // 事務結束
--1.4
--1.5
--1.6
--分組之後的結果
select max(score) from stuscore group by pid
-------------------------------------最小值------------------------
select min(score) from stuscore--10
--分組之後的結果
select min(score) from stuscore group by pid
-------------------------------------絕對值------------------------
select abs(score) from stuscore --編號25為-10被更改為10
-------------------------------------求個數------------------------
select count(score) from stuscore--25條記錄數
-------------------------------------求標准偏差------------------------
select stdev(score) from stuscore
-------------------------------------所有填入的標准偏差------------------------
select stdevp(score) from stuscore
-------------------------------------所有值的統計方差------------------------
select var(score) from stuscore
-------------------------------------隨機數------------------------------------
--rand()產生一個隨機數
select rand()
select rand((datepart(mm,getdate())*100000)+(datepart(ss,getdate())*1000)+datepart(ms,getdate()))
-------------------------------------正弦、余弦、正切、余切-------------------------
print sin(1)
print asin()
print cos(1)
print acos()
print tan(1)
print atan()
print cot()
-------------------------------------圓周率--------------------------------------
print pi()
-------------------------------------求平方根--------------------------------------
print sqrt(25)--5
-------------------------------------求對數--------------------------------------
print log(25)
-------------------------------------返回以10為底的對數---------------------------
print log10(100)--2
--------------------------------------取整--------------------------------------
--ROUND(操作數,小數點後第幾位,是否進行四舍五入)默認0進行四舍五入,其它進行數據截取掉
print ROUND(120.505,0,0)--121.000
print ROUND(120.505,1,0)--120.500
print ROUND(120.505,0,1)--120.00
--------------------------------------求次方值-------------------------------------
--power(數值,次方數)
print power(100,2)--10000
---------------------------------------判斷數值正負0-------------------------------------
-- numeric_expr的值為正數、0或負數時分別返回1、0、-1數值
select sign(100)--1
select sign(-100)---1
select sign(0)--0
---------------------------------------數值取整--------------------------------------
select ceiling(12.33)--無論小數點後是什么都在整數後加1(13)
select ceiling(12.83)--無論小數點後是什么都在整數後加1(13)
select floor(12.33)--無論小數點後是什么都取整數(12)
select floor(12.83)--無論小數點後是什么都取整數(12)
---------------------------------------求角度-------------------------------------------
--當給出弧度為單位的角度時，返回相應的以度數為單位的角度。
print degrees(pi())--半圓
print degrees(2*pi())--圓
---------------------------------------求弧度-------------------------------------------
print radians(180)--即pi()=3.1415926
print radians(90)--1
-----------------------------------------返回所給的float表達式的指數值。-----------------------
--數字的指數是常量 e 使用該數字進行冪運算。例如，EXP(1.0) = e^1.0 = 2.71828182845905，而 EXP(10) = e^10 = 22026.4657948067。數字的自然對數的指數是數字本身：EXP (LOG (n)) = n。數字的指數的自然對數是數字本身：LOG (EXP (n)) = n。
select exp(1)
-----------------------------------------浮點數的平方-----------------------------------
--求圓的面積列子2*pi()*r*r
select pi()*square(2)*2
---------------------------------------------資料庫與表操作----------------------------------------------------
-----------------約束類型
--主鍵約束
primary [key]
--唯一約束(允許為空，但只能一次)
unique
--檢查約束
check
--默認約束
default
--外鍵約束
foreign
--刪除資料庫
drop database test
--刪除表
drop table class
---------------------------------------------------表中添加約束
alter table 表名
add constraint 約束名 約束類型 具體約束說明
--添加主鍵
alter table stu
add constraint pk primary key(stuid)
--添加唯一
alter table stu
add constraint uq unique(stuid)
--添加默認
alter table stu
add constraint df default('sdfasdf') for stuname
--檢查約束
alter table stu
add constraint ck check(stu between 10 and 100)
--添加外鍵
alter table stu
add constraint fk foreign key(stuid) references stuscore(stuid)
stu為從表stuscore為主表(引用表為主表)
---------------------------------------------------表中刪除約束
alter table stu
drop constraint 約束名
--刪除主鍵
alter table stu
drop constraint fk
-------------------------------------------變數
--局部變數
declare @變數名 類型
--
declare @name varchar(20)
--局部變數賦值
set @name='sadf'
select @name='werre'
select @name
--全局變數
@@error --最後一個T-SQL錯誤的錯誤號
@@identity --最後一次插入的標識值
@@language --當前使用語言
@@max_connections --可以創建的同時連接的最大數目
@@rowcount --受上一條T-SQL語句影響的行數
@@servername --本地伺服器的名稱
@@timeticks --當前計算機上的每刻度的微妙數
@@version --SQL Server的版本信息
--邏輯控制語句
--if-else
if(條件)
begin
語句
end
else
語句
--while
while(條件)
begin
語句
[break]
end
--case
case
when 條件 then 結果
when 條件 then 結果
[else]
end
--子查詢
select * from 表名 where 欄位>[in](select.....)
--in 和 not in
select * from 表名 where 欄位 in[not in] (select.....)
--exists和not exists
if exists(select * from sysdatabases where name='test')
begin
print 'sadf'
end
else
begin
print '124'
end
----------------------------事務
--
--begin distributed transaction
--原型：
-- Begin destributed tran [ saction] [transaction_name! @tran_name_variable]
--例如：
-- Use northwind
-- Begin destributed transaction
-- Update empoyees //對表empolees 進行更新
-- Set firstname=’mcdonald’ where homephone = ‘(206) 555-9875’
-- Commit transaction
-- Go
--
--.3
--原型：
-- Rollback [tran [saction] [transaction_name] @tran_name_variable | savepoint_name |
--@savepoint_variable]
--
-- Transadtion 給begin transaction上的事務指派的名稱。
-- @tran_name_bariable
-- Savepoint_name
-- @savepoint_variable 是用戶定義的、含有有效保存點名稱的變數的名稱。
-- 例如：
-- Begin transaction royaltychange
-- Update titleauthor
--
--
--
-- And titleauthor.title_id=titles.title_id
-- And title=’The Gourmet Microwave’
-- Update titleauthor
--
--
--
--
--
-- Save transaction percentchanged
-- Update titles
--
--
-- Select (price * royalty * ytd_sales)* royaltyper
--
--
--
-- Rollback transaction percentchanged
-- Commit transaction // 事務結束
--1.4
--1.5
--1.6
begin transaction--開始事務
commit transaction--提交事務
save transaction --存儲當前記錄點
rollback transaction--回滾事務
--
use test
begin transaction
declare @error int
set @error=0 --計錯誤數
--事件
delete stu where stuid='2'
update stu set stuage='21' where stuid='2'
--insert stu values('sdfsa','男',25)
--update stu set stuage='asdf' where stuid='1'
set @error=@error+@@error--累計錯誤
if (@error<>0) --判斷是否有錯誤
begin
select * from stu
print '已經發生錯誤!!!!!!!!!'
rollback transaction
end
else
begin
select * from stu
print '沒有發生錯誤O(∩_∩)O哈哈~'
commit transaction
end
commit transaction--提交事務
save transaction --存儲當前記錄點
rollback transaction--回滾事務
--
use test
begin transaction
declare @error int
set @error=0 --計錯誤數
--事件
delete stu where stuid='2'
update stu set stuage='21' where stuid='2'
--insert stu values('sdfsa','男',25)
--update stu set stuage='asdf' where stuid='1'
set @error=@error+@@error--累計錯誤
if (@error<>0) --判斷是否有錯誤
begin
select * from stu
print '已經發生錯誤!!!!!!!!!'
rollback transaction
end
else
begin
select * from stu
print '沒有發生錯誤O(∩_∩)O哈哈~'
commit transaction
end
--例子
begin transaction
declare @error int
--計算錯誤
set @error=0
--添加數據
insert users values('wangwu','wangwu')
set @error=@error+@@error
if(@error<>0)
begin
print '已經被回滾'
rollback transaction
end
else
begin
print '事務點已經保存'
save transaction saveTran
end
update users set username=null,userpassword='wangwu' whereuserid=@@identity
set @error=@error+@@error
if(@error<>0)
begin
--回到保存點
print '已經回到事務保存點'
rollback transaction saveTran
update users set username='zhaoliu',userpassword='zhaoliu' whereuserid=@@identity
set @error=@@error+@error
end
if(@error<>0)
begin
print '數據已經提交'
commit transaction
end
else
begin
print '數據已經提交'
commit transaction
end
------------------------索引
create [unique] [clustered] [nonclustered] index '索引名稱' on 表名(列名)[with fillfactor=x]
--fillfactor范圍0-100
create nonclustered index index_stuage on stu(stuage) with fillfactor=30
select * from stu where stuage between 10 and 30
select * from stu with(index=index_stuage) where stuage between 10 and 30
select * from sysindexes
use test
if exists(select name from sysindexes where name='index_stuage')
begin
print '存在'
end
else
print '不存在'
--游標
--游標的使用：
--2.1
--原型：
--
--Only | update }[of
--
--
--dynamic] [read_only | scroll_locks | optimistic] for [select _statement
--[ for {read only | update } [of column_list]]]
-- 參數說明：
-- INSENSITIVE
--臨時拷貝中獲取。原有基表中數據發生了改變，對於游標而言是
--不可見的。這種不敏感的游標不允許數據更改。
-- SCROLL
-- SELECT_SATAEMENT
-- READ ONLY
-- UPDATE
-- OF COLUMN_LIST
--鍵字），所有的列都可進行修改。
-- LOCAL
--激活的任何時候都是可用的。只有池連接結束時，才不再可用。
-- GLOBAL 使用游標對於整個連接全局可見。
-- FORWARD_ONLY
-- STATIC
-- KEYSET
-- DYNAMIC
-- SCROLL_LOCK
-- OPTIMISTIC
--2.2
--2.3
--原型：
--
--
-- Use northwind
-- Go
-- Declare employee_cursor cursor for //定義游標
-- Select lastname,firstname
-- From northwind .dbo.employees
--Where firstname like ‘m%’
--Open employee_cursor // 打開游標
--Fetch next from employee_cursor //利用游標提取數據
--While @@fetch_status=0 //當利用FETCH提取數據成功時，運用循環提取下一
--條數據
--Begin //循環體開始處
--
--End
--close employee_cursor
--Deallocate employee_cursor // 釋放游標
--2.4
--2.5
--
-- Fetch [next | prior | first | last | absolute {n | @nvar} | relative {n | @nvar}] from [global]
--
--
-- NEXT
-- PRIOR
-- FIRST
-- LAST 結果集的最後一行。
-- ABSOLUTE
-- RELATIVE
--數則要取出的行在當前行前n行的位置上，如果該值為負數，則返回
--當前行的後n行。
-- INTO @cursor_variable_name
--類型也應該與被選擇列的數據類型相同。直到下一
--次使用FETCH語句之前，變數中的值都會一直保
--持。
--
-- 利用@@FETCH_STATUS返回FETCH狀態。
-- 0：FETCH 成功。
-- 1：FETCH 失敗或超出設置范圍。
-- 2：提取的數據行丟失。
--游標的使用：
--2.1
--原型：
--
--Only | update }[of
--
--
--dynamic] [read_only | scroll_locks | optimistic] for [select _statement
--[ for {read only | update } [of column_list]]]
-- 參數說明：
-- INSENSITIVE
--臨時拷貝中獲取。原有基表中數據發生了改變，對於游標而言是
--不可見的。這種不敏感的游標不允許數據更改。
-- SCROLL
-- SELECT_SATAEMENT
-- READ ONLY
-- UPDATE
-- OF COLUMN_LIST
--鍵字），所有的列都可進行修改。
-- LOCAL
--激活的任何時候都是可用的。只有池連接結束時，才不再可用。
-- GLOBAL 使用游標對於整個連接全局可見。
-- FORWARD_ONLY
-- STATIC
-- KEYSET
-- DYNAMIC
-- SCROLL_LOCK
-- OPTIMISTIC
--2.2
--2.3
--原型：
--
--
-- Use northwind
-- Go
-- Declare employee_cursor cursor for //定義游標
-- Select lastname,firstname
-- From northwind .dbo.employees
--Where firstname like ‘m%’
--Open employee_cursor // 打開游標
--Fetch next from employee_cursor //利用游標提取數據
--While @@fetch_status=0 //當利用FETCH提取數據成功時，運用循環提取下一
--條數據
--Begin //循環體開始處
--
--End
--close employee_cursor
--Deallocate employee_cursor // 釋放游標
--2.4
--2.5
--
-- Fetch [next | prior | first | last | absolute {n | @nvar} | relative {n | @nvar}] from [global]
--
--
-- NEXT
-- PRIOR
-- FIRST
-- LAST 結果集的最後一行。
-- ABSOLUTE
-- RELATIVE
--數則要取出的行在當前行前n行的位置上，如果該值為負數，則返回
--當前行的後n行。
-- INTO @cursor_variable_name
--類型也應該與被選擇列的數據類型相同。直到下一
--次使用FETCH語句之前，變數中的值都會一直保
--持。
--
-- 利用@@FETCH_STATUS返回FETCH狀態。
-- 0：FETCH 成功。
-- 1：FETCH 失敗或超出設置范圍。
-- 2：提取的數據行丟失。
--例子
declare CursorUsers cursor for
select username from users --where username like 'z%'
declare CursorUsers cursor for
select username from users --where username like 'z%'
open CursorUsers--打開游標
fetch next from CursorUsers--讀取下一行數據
while @@fetch_status=0
begin
fetch next from CursorUsers
end
fetch next from CursorUsers--讀取下一行數據
while @@fetch_status=0
begin
fetch next from CursorUsers
end
close CursorUsers--關閉游標
deallocate CursorUsers--刪除游標
--刪除索引
drop index 表名.索引名
drop index stu.index_stuage
---------------------------視圖
create view 視圖名稱
as
SQL語句
-------------------------
create view view_test
as
select * from stu
create view 視圖名稱
as
SQL語句
-------------------------
create view view_test
as
select * from stu
select * from view_test
--視圖存在於sysobjects
if exists(select name from sysobjects where name='view_test')
begin
print '存在'
end
else
begin
print '不存在'
end
--刪除視圖
drop view 視圖名
drop view view_test
--------------------------------存儲過程
--3.1
--原型：
-- Create proc [edure] procedure_name [; number]
-- [{@parameter data_type} [varying] [ = default] [output]] [,…n]
-- [ with {recomple | enplication | recompile , encryption }] [for replication]
-- As sql_statement […n]
--
-- Procedure_name
-- 過程，在procedure_nameu前加一個編號符#；創建全局臨時過
-- 程，在procedure_nameu前加兩個編號符##。完整的名稱不能超
-- 過128個字元。
-- Number
-- @parameter
-- Data_type
--程的參數。Cursor數據類型只能用於output參數。
--存儲過程的最大小是128MB,最多可以嵌套32級，最多可以有2100個參數
if exists(select name from sysobjects where name='view_test')
begin
print '存在'
end
else
begin
print '不存在'
end
--刪除視圖
drop view 視圖名
drop view view_test
--------------------------------存儲過程
--3.1
--原型：
-- Create proc [edure] procedure_name [; number]
-- [{@parameter data_type} [varying] [ = default] [output]] [,…n]
-- [ with {recomple | enplication | recompile , encryption }] [for replication]
-- As sql_statement […n]
--
-- Procedure_name
-- 過程，在procedure_nameu前加一個編號符#；創建全局臨時過
-- 程，在procedure_nameu前加兩個編號符##。完整的名稱不能超
-- 過128個字元。
-- Number
-- @parameter
-- Data_type
--程的參數。Cursor數據類型只能用於output參數。
--存儲過程的最大小是128MB,最多可以嵌套32級，最多可以有2100個參數
--執行速度快、允許程序模塊化、提高性能的安全化（過濾非法字元）、減少網路流通量
--缺點是程序不易調試
--存儲過程的最大小是128MB,最多可以嵌套32級，最多可以有2100個參數
--常用的系統存儲過程
sp_databases 列出系統所有的資料庫
sp_help 查看某個表的所有信息 sp_help 'stu'
sp_helpdb 報告有關資料庫或所有的資料庫的信息(sp_helpdb 'test')
sp_rename 更改資料庫中表的名稱 exec sp_rename 'stus','stu'(前者原始表名，後者新表名)
sp_renamedb 更改資料庫的名稱
sp_tables 返回當前環境中可查詢對象的列表
sp_columns 返回某個表列的信息 exec sp_columns 'stu'
sp_helpconstraint 查看某個表中的約束 exec sp_helpconstraint 'stu'
sp_helpindex 查看某個表內的索引 exec sp_helpindex 'stu'
sp_stored_procedures 查看當前環境下的所有存儲過程 sp_stored_procedures 'stu'
sp_password 修改當前用的登錄密碼 sp_password 'sa123','123'(前者原始密碼，後者新密碼)
sp_helptext 顯示默認值、未加密的存儲過程、用戶定義的存儲過程、觸發器的或視圖的實際文本 exec sp_helptext 'view_test'
xp_cmdshell dos命令 [no_output] xp_cmdshell 'mkdir D:\pro'
sp_detach_db 資料庫分離 sp_detach_db '資料庫名稱' exec sp_detach_db test
sp_attach_db 資料庫附加 sp_attach_db '資料庫名稱','主文件地址','日志文件地址' exec sp_attach_db 'test','F:\pro\test_data.mdf','F:\pro\test_log.ldf'
----自定義存儲過程
create proc[procedure] 存儲名稱
[
{@參數1 參數類型}[=默認值1][output]
{@參數2 參數類型}[=默認值2][output]
]
as
SQL語句
--
if exists(select name from sysobjects where name='pro_stu')
begin
print '存儲過程已經存在'
end
else
begin
print '存儲過程不存在'
end
--缺點是程序不易調試
--存儲過程的最大小是128MB,最多可以嵌套32級，最多可以有2100個參數
--常用的系統存儲過程
sp_databases 列出系統所有的資料庫
sp_help 查看某個表的所有信息 sp_help 'stu'
sp_helpdb 報告有關資料庫或所有的資料庫的信息(sp_helpdb 'test')
sp_rename 更改資料庫中表的名稱 exec sp_rename 'stus','stu'(前者原始表名，後者新表名)
sp_renamedb 更改資料庫的名稱
sp_tables 返回當前環境中可查詢對象的列表
sp_columns 返回某個表列的信息 exec sp_columns 'stu'
sp_helpconstraint 查看某個表中的約束 exec sp_helpconstraint 'stu'
sp_helpindex 查看某個表內的索引 exec sp_helpindex 'stu'
sp_stored_procedures 查看當前環境下的所有存儲過程 sp_stored_procedures 'stu'
sp_password 修改當前用的登錄密碼 sp_password 'sa123','123'(前者原始密碼，後者新密碼)
sp_helptext 顯示默認值、未加密的存儲過程、用戶定義的存儲過程、觸發器的或視圖的實際文本 exec sp_helptext 'view_test'
xp_cmdshell dos命令 [no_output] xp_cmdshell 'mkdir D:\pro'
sp_detach_db 資料庫分離 sp_detach_db '資料庫名稱' exec sp_detach_db test
sp_attach_db 資料庫附加 sp_attach_db '資料庫名稱','主文件地址','日志文件地址' exec sp_attach_db 'test','F:\pro\test_data.mdf','F:\pro\test_log.ldf'
----自定義存儲過程
create proc[procedure] 存儲名稱
[
{@參數1 參數類型}[=默認值1][output]
{@參數2 參數類型}[=默認值2][output]
]
as
SQL語句
--
if exists(select name from sysobjects where name='pro_stu')
begin
print '存儲過程已經存在'
end
else
begin
print '存儲過程不存在'
end
create procedure pro_stu
as
select * from stu
--調用
exec pro_stu
--刪除存儲過程
drop procedure pro_stu
drop procedure stuid
--有參存儲過程
create procedure stuid
@id int=5,
@name varchar(20)='王雙'
as
select * from stu wherestuid=@idorstuname=@name
exec stuid
exec stuid @id=5,@name='王雙'
exec stuid @id=6
exec stuid @name='張三'
exec stuid 8,'王雙'
exec stuid null,'趙六'
exec stuid 3,null
--查看存儲過程
sp_helptext 'stuid'
as
select * from stu
--調用
exec pro_stu
--刪除存儲過程
drop procedure pro_stu
drop procedure stuid
--有參存儲過程
create procedure stuid
@id int=5,
@name varchar(20)='王雙'
as
select * from stu wherestuid=@idorstuname=@name
exec stuid
exec stuid @id=5,@name='王雙'
exec stuid @id=6
exec stuid @name='張三'
exec stuid 8,'王雙'
exec stuid null,'趙六'
exec stuid 3,null
--查看存儲過程
sp_helptext 'stuid'
--創建有輸出參數的存儲過程（有返回值的存儲過程）
create procedure pro_avgstuage
@resu int output
as
select @resu=avg(stuage) from stu
declare @stuavg int
exec pro_avgstuage @stuavg output
print @stuavg
exec pro_avgstuage @stuavg output
print @stuavg
drop procedure pro_avgstuage
--處理錯誤信息
--raiserror({msg_id(在sysmessages系統表中指定的用戶定義錯誤信息)|msg_str(用戶定義的特定信息，最長255個字元)}{,serverity(與特定信息關聯，表示用戶定義的嚴重性級別，用戶可使用的級別為0~18。19~25級是為sysadmin固定角色的成員預留的，並且需要指定with log選項。20~25級別錯誤被認為是致命錯誤),state表示錯誤的狀態，是1~127的值}[with option[,..n]]指定是否將錯誤記錄到伺服器錯誤日志中。)
--raiserror({msg_id(在sysmessages系統表中指定的用戶定義錯誤信息)|msg_str(用戶定義的特定信息，最長255個字元)}{,serverity(與特定信息關聯，表示用戶定義的嚴重性級別，用戶可使用的級別為0~18。19~25級是為sysadmin固定角色的成員預留的，並且需要指定with log選項。20~25級別錯誤被認為是致命錯誤),state表示錯誤的狀態，是1~127的值}[with option[,..n]]指定是否將錯誤記錄到伺服器錯誤日志中。)
select * from stu
if exists(select name from sysobjects where name='pro_stuid')
begin
drop procedure pro_stuid
end
go
create procedure pro_stuid
@stuid int=3
as
if(@stuid<3)
begin
raiserror('記錄id不能小於3，統計中斷退出',16,1)
return--返回程序，退出存儲過程
end
select * from stu wherestuid=@stuid
exec pro_stuid @stuid=5
if exists(select name from sysobjects where name='pro_stuid')
begin
drop procedure pro_stuid
end
go
create procedure pro_stuid
@stuid int=3
as
if(@stuid<3)
begin
raiserror('記錄id不能小於3，統計中斷退出',16,1)
return--返回程序，退出存儲過程
end
select * from stu wherestuid=@stuid
exec pro_stuid @stuid=5
--創建觸發器
if exists(select name from sysobjects where name='tr_stu')
begin
print '已經存在！'
end
else
begin
print '不存在！'
end
--drop trigger trstu刪除觸發器
create Trigger tr_stu
on stu
for insert
as
begin transaction
print'asasdfsda'
rollback transaction
go
insert stu values('sd','男',32)
--創建商品表
create table shopstore
(
shopid int primary key identity,--商品編號
sid int foreign key references shops(id),--廠家編號
shopname varchar(20) not null,--商品名稱
shopcount int default(0),--商品數量
shopprice float,--商品單價
shoptotal float--商品總金額
)
drop table shopstore
------------------------------------------------------------------引用表位從表，被引用者為主表
--創建廠家表
create table shops
(
id int primary key,
sname varchar(20) not null,
)
insert shops values(1,'明基股份有限公司')
insert shops values(2,'宏基基股份有限公司')
insert shops values(3,'偉創力股份有限公司')
insert shops values(4,'宏泰股份有限公司')
--創建觸發器（insert）
--實現目標是插入的總金額=商品單價*商品數量
if exists(select name from sysobjects where name='tr_shoptatal')
drop trigger tr_shoptotal
go
create trigger tr_shoptotal
on shopstore
for insert
--開始事務
as
begin transaction
--當插入新數據時，將新的數據shoptatal修改
update shopstore set shoptotal=shopprice*shopcount where shopname in(select shopname from inserted)
commit transaction
go
--sp_help shopstore
insert shopstore values(1,'明基顯示器',12,1000.0,300000.0)
select * from shopstore
--創建觸發器（update）
--改變廠家的編號的時候，改變相應的產品的廠家編號
if exists(select name from sysobjects where name='tr_changeid')
drop trigger tr_changeid
go
create trigger tr_changeid
on shops
for update
as
--sp_help shopstore
--系統中沒有update,系統中只有inserted 與 deleted
--存放新紀錄時，inserted虛擬表中臨時存放新紀錄,deleted中不存放新紀錄【觸發器存放新記錄時：將新記錄存放在inserted虛擬表中】
--修改紀錄時，inserted虛擬表中存放更新的紀錄,deleted中存放舊紀錄【觸發器修改時：首先將舊記錄存放在deleted中，插入inserted表中更新記錄】
--刪除紀錄時，inserted虛擬表中不存放紀錄,deleted中存放已經刪除的紀錄【觸發器刪除時：在已經在delected表中存放已經刪除的記錄】
update shopstore set sid=i.id from shopstore s,Inserted i,deleted d where s.sid=d.id
alter table shops
drop constraint PK
select * from shops
update shops set id=1 where sname='明基股份有限公司'
select * from shopstore
select * from shops
--修改觸發器
alter 觸發器名稱
--刪除觸發器、
drop trigger 觸發器名稱
--注意事項
1.一個觸發器只能作用於一個表或一個view視圖
2.觸發器的三種觸發insert、update、delete，同時執行可以，也可執行其一
3.update對列有影響，某列的值發生改變則觸發事件 if update(列名)，delete則是對行影響發生改變觸發事件
4.觸發器兩臨時表inserted 與deleted在觸發器觸發時系統自動生成兩臨時表,結構與觸發器操作相同的表，只是存放是數據有差異
if exists(select name from sysobjects where name='tr_stu')
begin
print '已經存在！'
end
else
begin
print '不存在！'
end
--drop trigger trstu刪除觸發器
create Trigger tr_stu
on stu
for insert
as
begin transaction
print'asasdfsda'
rollback transaction
go
insert stu values('sd','男',32)
--創建商品表
create table shopstore
(
shopid int primary key identity,--商品編號
sid int foreign key references shops(id),--廠家編號
shopname varchar(20) not null,--商品名稱
shopcount int default(0),--商品數量
shopprice float,--商品單價
shoptotal float--商品總金額
)
drop table shopstore
------------------------------------------------------------------引用表位從表，被引用者為主表
--創建廠家表
create table shops
(
id int primary key,
sname varchar(20) not null,
)
insert shops values(1,'明基股份有限公司')
insert shops values(2,'宏基基股份有限公司')
insert shops values(3,'偉創力股份有限公司')
insert shops values(4,'宏泰股份有限公司')
--創建觸發器（insert）
--實現目標是插入的總金額=商品單價*商品數量
if exists(select name from sysobjects where name='tr_shoptatal')
drop trigger tr_shoptotal
go
create trigger tr_shoptotal
on shopstore
for insert
--開始事務
as
begin transaction
--當插入新數據時，將新的數據shoptatal修改
update shopstore set shoptotal=shopprice*shopcount where shopname in(select shopname from inserted)
commit transaction
go
--sp_help shopstore
insert shopstore values(1,'明基顯示器',12,1000.0,300000.0)
select * from shopstore
--創建觸發器（update）
--改變廠家的編號的時候，改變相應的產品的廠家編號
if exists(select name from sysobjects where name='tr_changeid')
drop trigger tr_changeid
go
create trigger tr_changeid
on shops
for update
as
--sp_help shopstore
--系統中沒有update,系統中只有inserted 與 deleted
--存放新紀錄時，inserted虛擬表中臨時存放新紀錄,deleted中不存放新紀錄【觸發器存放新記錄時：將新記錄存放在inserted虛擬表中】
--修改紀錄時，inserted虛擬表中存放更新的紀錄,deleted中存放舊紀錄【觸發器修改時：首先將舊記錄存放在deleted中，插入inserted表中更新記錄】
--刪除紀錄時，inserted虛擬表中不存放紀錄,deleted中存放已經刪除的紀錄【觸發器刪除時：在已經在delected表中存放已經刪除的記錄】
update shopstore set sid=i.id from shopstore s,Inserted i,deleted d where s.sid=d.id
alter table shops
drop constraint PK
select * from shops
update shops set id=1 where sname='明基股份有限公司'
select * from shopstore
select * from shops
--修改觸發器
alter 觸發器名稱
--刪除觸發器、
drop trigger 觸發器名稱
--注意事項
1.一個觸發器只能作用於一個表或一個view視圖
2.觸發器的三種觸發insert、update、delete，同時執行可以，也可執行其一
3.update對列有影響，某列的值發生改變則觸發事件 if update(列名)，delete則是對行影響發生改變觸發事件
4.觸發器兩臨時表inserted 與deleted在觸發器觸發時系統自動生成兩臨時表,結構與觸發器操作相同的表，只是存放是數據有差異
--資料庫備份
backup database test to disk='F:\pro\test.back'
--資料庫還原
use MASTER
restore database test from disk='F:\pro\test.back' with replace
--資料庫附加
exec sp_attach_db 'test','F:\pro\test_data.mdf','F:\pro\test_log.ldf'
--分離資料庫
exec sp_detach_db test
--收縮資料庫
print '收縮前大小：'exec sp_helpdb test
dbcc shrinkdatabase('test')
print '收縮後大小：'exec sp_helpdb test
--資料庫導出
exec master..xp_cmdshell 'bcp db_ADO.dbo.test.out.F:\pro\testout.txt -c-q-s mrsy -u sa -p 123'
--資料庫導入
exec master..xp_cmdshell 'bcp db_ADO.dbo.test.in.F:\pro\testout.txt'-c-q-s mrsy -u sa -p 123
----語 句 功 能
----數據操作
--SELECT --從資料庫表中檢索數據行和列
--INSERT --向資料庫表添加新數據行
--DELETE --從資料庫表中刪除數據行
--UPDATE --更新資料庫表中的數據
----數據定義
--CREATE TABLE --創建一個資料庫表
--DROP TABLE --從資料庫中刪除表
--ALTER TABLE --修改資料庫表結構
--CREATE VIEW --創建一個視圖
--DROP VIEW --從資料庫中刪除視圖
--CREATE INDEX --為資料庫表創建一個索引
--DROP INDEX --從資料庫中刪除索引
--CREATE PROCEDURE --創建一個存儲過程
--DROP PROCEDURE --從資料庫中刪除存儲過程
--
--CREATE TRIGGER --創建一個觸發器
--DROP TRIGGER --從資料庫中刪除觸發器
--CREATE SCHEMA --向資料庫添加一個新模式
--DROP SCHEMA --從資料庫中刪除一個模式
--CREATE DOMAIN --創建一個數據值域
--ALTER DOMAIN --改變域定義
--DROP DOMAIN --從資料庫中刪除一個域
----數據控制
--GRANT --授予用戶訪問許可權
--DENY --拒絕用戶訪問
--REVOKE --解除用戶訪問許可權
----事務控制
--COMMIT --結束當前事務
--ROLLBACK --中止當前事務
--SET TRANSACTION --定義當前事務數據訪問特征
----程序化SQL
--DECLARE --為查詢設定游標
--EXPLAN --為查詢描述數據訪問計劃
--OPEN --檢索查詢結果打開一個游標
--FETCH --檢索一行查詢結果
--CLOSE --關閉游標
--PREPARE --為動態執行准備SQL 語句
--EXECUTE --動態地執行SQL 語句
--DESCRIBE --描述准備好的查詢
-----局部變數
--declare @id char(10)
----set @id = ´10010001´
--select @id = ´10010001´
-----全局變數
-----必須以@@開頭
----IF ELSE
--declare @x int @y int @z int
--select @x = 1 @y = 2 @z=3
--if @x > @y
--print ´x > y´ --列印字元串´x > y´
--else if @y > @z
--print ´y > z´
--else print ´z > y´
----CASE
--use pangu
--update employee
--set e_wage =
--case
--when job_level = ’1’ then e_wage*1.08
--when job_level = ’2’ then e_wage*1.07
--when job_level = ’3’ then e_wage*1.06
--else e_wage*1.05
--end
----WHILE CONTINUE BREAK
--declare @x int @y int @c int
--select @x = 1 @y=1
--while @x < 3
--begin
--print @x --列印變數x 的值
--while @y < 3
--begin
--select @c =100*@x+ @y
--print @c --列印變數c 的值
--select @y = @y + 1
--end
--select @x = @x + 1
--select @y = 1
--end
----WAITFOR
----例 等待1 小時2 分零3 秒後才執行SELECT 語句
--waitfor delay ’01:02:03’
--select * from employee
----例 等到晚上11 點零8 分後才執行SELECT 語句
--waitfor time ’23:08:00’
--select * from employee
--***SELECT***
--select *(列名) from table_name(表名) where column_name operator value
--ex宿主)
--select * from stock_information where stockid = str(nid)
--stockname = ´str_name´
--stockname like ´% find this %´
--stockname like ´[a-zA-Z]%´ --------- ([]指定值的范圍)
--stockname like ´[^F-M]%´ --------- (^排除指定范圍)
----------- 只能在使用like關鍵字的where子句中使用通配符)
--or stockpath = ´stock_path´
--or stocknumber < 1000
--and stockindex = 24
--not stocksex = ´man´
--stocknumber between 20 and 100
--stocknumber in(10,20,30)
--order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
--order by 1,2 --------- by列號
--stockname = (select stockname from stock_information where stockid = 4)
----------- 子查詢
----------- 除非能確保內層select只返回一個行的值，
----------- 否則應在外層where子句中用一個in限定符
--select distinct column_name form table_name --------- distinct指定檢索獨有的列值，不重復
--select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
--select stockname , "stocknumber" = count(*) from table_name group by stockname
----------- group by 將表按行分組,指定列中有相同的值
--having count(*) = 2 --------- having選定指定的組
--select *
--from table1, table2
--where table1.id *= table2.id -------- 左外部連接，table1中有的而table2中沒有得以null表示
--table1.id =* table2.id -------- 右外部連接
--select stockname from table1
--union [all] ----- union合並查詢結果集，all-保留重復行
--select stockname from table2
--***insert***
--insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
--value (select Stockname , Stocknumber from Stock_table2)---value為select語句
--***update***
--update table_name set Stockname = "xxx" [where Stockid = 3]
--Stockname = default
--Stockname = null
--Stocknumber = Stockname + 4
--***delete***
--delete from table_name where Stockid = 3
--truncate table_name ----------- 刪除表中所有行，仍保持表的完整性
--drop table table_name --------------- 完全刪除表
--***alter table*** --- 修改資料庫表結構
--alter table database.owner.table_name add column_name char(2) null .....
--sp_help table_name ---- 顯示表已有特征
--create table table_name (name char(20), age smallint, lname varchar(30))
--insert into table_name select ......... ----- 實現刪除列的方法（創建新表）
--alter table table_name drop constraint Stockname_default ---- 刪除Stockname的default約束
--***function()***
------統計函數----
--AVG --求平均值
--COUNT --統計數目
--MAX --求最大值
--MIN --求最小值
--SUM --求和
----AVG
--use pangu
--select avg(e_wage) as dept_avgWage
--from employee
--group by dept_id
----MAX
----求工資最高的員工姓名
--use pangu
--select e_name
--from employee
--where e_wage =
--(select max(e_wage)
--from employee)
----STDEV()
----STDEV()函數返回表達式中所有數據的標准差
----STDEVP()
----STDEVP()函數返回總體標准差
----VAR()
----VAR()函數返回表達式中所有值的統計變異數
----VARP()
----VARP()函數返回總體變異數
------算術函數----
--
--SIN(float_expression) --返回以弧度表示的角的正弦
--COS(float_expression) --返回以弧度表示的角的余弦
--TAN(float_expression) --返回以弧度表示的角的正切
--COT(float_expression) --返回以弧度表示的角的余切
--
--ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
--ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
--ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
--ATAN2(float_expression1,float_expression2)
----返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
--DEGREES(numeric_expression)
----把弧度轉換為角度返回與表達式相同的數據類型可為
----INTEGER/MONEY/REAL/FLOAT 類型
--RADIANS(numeric_expression) --把角度轉換為弧度返回與表達式相同的數據類型可為
----INTEGER/MONEY/REAL/FLOAT 類型
--EXP(float_expression) --返回表達式的指數值
--LOG(float_expression) --返回表達式的自然對數值
--LOG10(float_expression)--返回表達式的以10 為底的對數值
--SQRT(float_expression) --返回表達式的平方根
--
--CEILING(numeric_expression) --返回>=表達式的最小整數返回的數據類型與表達式相同可為
----INTEGER/MONEY/REAL/FLOAT 類型
--FLOOR(numeric_expression) --返回<=表達式的最小整數返回的數據類型與表達式相同可為
----INTEGER/MONEY/REAL/FLOAT 類型
--ROUND(numeric_expression) --返回以integer_expression 為精度的四舍五入值返回的數據
----類型與表達式相同可為INTEGER/MONEY/REAL/FLOAT 類型
--ABS(numeric_expression) --返回表達式的絕對值返回的數據類型與表達式相同可為
----INTEGER/MONEY/REAL/FLOAT 類型
--SIGN(numeric_expression) --測試參數的正負號返回0 零值1 正數或-1 負數返回的數據類型
----與表達式相同可為INTEGER/MONEY/REAL/FLOAT 類型
--PI() --返回值為π 即3.1415926535897936
--RAND([integer_expression]) --用任選的[integer_expression]做種子值得出0-1 間的隨機浮點數
------字元串函數----
--ASCII() --函數返回字元表達式最左端字元的ASCII 碼值
--CHAR() --函數用於將ASCII 碼轉換為字元
----如果沒有輸入0 ~ 255 之間的ASCII 碼值CHAR 函數會返回一個NULL 值
--LOWER() --函數把字元串全部轉換為小寫
--UPPER() --函數把字元串全部轉換為大寫
--STR() --函數把數值型數據轉換為字元型數據
--LTRIM() --函數把字元串頭部的空格去掉
--RTRIM() --函數把字元串尾部的空格去掉
--LEFT(),RIGHT(),SUBSTRING() --函數返回部分字元串
--CHARINDEX(),PATINDEX() --函數返回字元串中某個指定的子串出現的開始位置
--SOUNDEX() --函數返回一個四位字元碼
----SOUNDEX函數可用來查找聲音相似的字元串但SOUNDEX函數對數字和漢字均只返回0 值
--DIFFERENCE() --函數返回由SOUNDEX 函數返回的兩個字元表達式的值的差異
----0 兩個SOUNDEX 函數返回值的第一個字元不同
----1 兩個SOUNDEX 函數返回值的第一個字元相同
----2 兩個SOUNDEX 函數返回值的第一二個字元相同
----3 兩個SOUNDEX 函數返回值的第一二三個字元相同
----4 兩個SOUNDEX 函數返回值完全相同
--QUOTENAME() --函數返回被特定字元括起來的字元串
--
--REPLICATE() --函數返回一個重復character_expression 指定次數的字元串
--
--REVERSE() --函數將指定的字元串的字元排列順序顛倒
--REPLACE() --函數返回被替換了指定子串的字元串
--
--SPACE() --函數返回一個有指定長度的空白字元串
--STUFF() --函數用另一子串替換字元串指定位置長度的子串
------數據類型轉換函數----
--CAST() 函數語法如下
--CAST() (<expression> AS <data_ type>[ length ])
--CONVERT() 函數語法如下
--CONVERT() (<data_ type>[ length ], <expression> [, style])
--select cast(100+99 as char) convert(varchar(12), getdate())
--運行結果如下
-------------------------------- ------------
--199 Jan 15 2000
------日期函數----
--DAY() --函數返回date_expression 中的日期值
--MONTH() --函數返回date_expression 中的月份值
--YEAR() --函數返回date_expression 中的年份值
--DATEADD(<datepart> ,<number> ,<date> )
----函數返回指定日期date 加上指定的額外日期間隔number 產生的新日期
--DATEDIFF(<datepart> ,<number> ,<date> )
----函數返回兩個指定日期在datepart 方面的不同之處
--DATENAME(<datepart> , <date> ) --函數以字元串的形式返回日期的指定部分
--DATEPART(<datepart> , <date> ) --函數以整數值的形式返回日期的指定部分
--GETDATE() --函數以DATETIME 的預設格式返回系統當前的日期和時間
------系統函數----
--APP_NAME() --函數返回當前執行的應用程序的名稱
--COALESCE() --函數返回眾多表達式中第一個非NULL 表達式的值
--COL_LENGTH(<´table_name´>, <´column_name´> ) --函數返回表中指定欄位的長度值
--COL_NAME(<table_id>, <column_id> ) --函數返回表中指定欄位的名稱即列名
--DATALENGTH() --函數返回數據表達式的數據的實際長度
--DB_ID([´database_name´]) --函數返回資料庫的編號
--DB_NAME(database_id) --函數返回資料庫的名稱
--HOST_ID() --函數返回伺服器端計算機的名稱
--HOST_NAME() --函數返回伺服器端計算機的名稱
--IDENTITY(<data_type>[, seed increment]) [AS column_name])
----IDENTITY() 函數只在SELECT INTO 語句中使用用於插入一個identity column列到新表中
--
--ISDATE() --函數判斷所給定的表達式是否為合理日期
--ISNULL(<check_expression>, <replacement_value> ) --函數將表達式中的NULL 值用指定值替換
--ISNUMERIC() --函數判斷所給定的表達式是否為合理的數值
--NEWID() --函數返回一個UNIQUEIDENTIFIER 類型的數值
--NULLIF(<expression1>, <expression2> )
----NULLIF 函數在expression1 與expression2 相等時返回NULL 值若不相等時則返回expression1 的值
select * from sys.database_files--獲取當前資料庫的大小？
select * from sys.tables --獲取單個表和整個資料庫的占用的容量
exec sp_spaceused--存儲過程來查資料庫的占用容量和某個表的占用容量
sp_helpdb 資料庫名--獲取資料庫相關信息
sp_helpdb 資料庫名--獲取資料庫相關信息
--將存儲過程中的數據插入到臨時表
create table 臨時表名稱(相關的列名)
insert into 臨時表名稱 存儲過程
--create table #table(mm varchar(100),msusername varchar(100),userpassword varchar(30))
--insert into #table exec sp_spaceused
--select * from #table
--drop table #table
create table 臨時表名稱(相關的列名)
insert into 臨時表名稱 存儲過程
--create table #table(mm varchar(100),msusername varchar(100),userpassword varchar(30))
--insert into #table exec sp_spaceused
--select * from #table
--drop table #table
6、說明：增加一個列
Alter table tabname add column col type
--列增加後將不能刪除
Alter table tabname add column col type
--列增加後將不能刪除
7、說明：添加主鍵： Alter table tabname add primary key(col)
說明：刪除主鍵： Alter table tabname drop primary key(col)
說明：刪除主鍵： Alter table tabname drop primary key(col)
8、說明：創建索引：create [unique] index idxname on tabname(col….)
刪除索引：drop index idxname
注：索引是不可更改的，想更改必須刪除重新建。
刪除索引：drop index idxname
注：索引是不可更改的，想更改必須刪除重新建。
A： UNION 運算符
UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）並消去表中
任何重復行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 UNION AL
L），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自
TABLE2。
B： EXCEPT 運算符
EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重
復行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消
除重復行
C： INTERSECT 運算符
INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行並消除所有重復
行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，
不消除重復行。
注：使用運算詞的幾個查詢結果行必須是一致的
UNION 運算符通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）並消去表中
任何重復行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 UNION AL
L），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自
TABLE2。
B： EXCEPT 運算符
EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重
復行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消
除重復行
C： INTERSECT 運算符
INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行並消除所有重復
行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，
不消除重復行。
注：使用運算詞的幾個查詢結果行必須是一致的
12、說明：使用外連接
A、left （outer） join：
左外連接（左連接）：結果集幾包括連接表的匹配行，也包括左連接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.
a = b.c
B：right （outer） join:
右外連接(右連接)：結果集既包括連接表的匹配連接行，也包括右連接表的所有
行。
C：full/cross （outer） join：
全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。
A、left （outer） join：
左外連接（左連接）：結果集幾包括連接表的匹配行，也包括左連接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.
a = b.c
B：right （outer） join:
右外連接(右連接)：結果集既包括連接表的匹配連接行，也包括右連接表的所有
行。
C：full/cross （outer） join：
全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。
12、分組:Group by:
一張表，一旦分組 完成後，查詢後只能得到組相關的信息。
組相關的信息：（統計信息） count,sum,max,min,avg 分組的標准)
在SQLServer 中分組時：不能以text,ntext,image 類型的欄位作為分組依
據
在selecte 統計函數中的欄位，不能和普通的欄位放在一起；
一張表，一旦分組 完成後，查詢後只能得到組相關的信息。
組相關的信息：（統計信息） count,sum,max,min,avg 分組的標准)
在SQLServer 中分組時：不能以text,ntext,image 類型的欄位作為分組依
據
在selecte 統計函數中的欄位，不能和普通的欄位放在一起；
14.如何修改資料庫的名稱:
sp_renamedb 'old_name', 'new_name'
sp_renamedb 'old_name', 'new_name'
1、說明：復制表(只復制結構,源表名：a 新表名：b) (Access 可用)
法一：select * into b from a where 1<>1（僅用於SQlServer）
法二：select top 0 * into b from a
2、說明：拷貝表(拷貝數據,源表名：a 目標表名：b) (Access 可用)
--insert into b(a, b, c) select d,e,f from b;
--select top 0 * into Nusers from users
--insert into Nusers(username,userpassword) select username,userpassword from users
--select * from Nusers
法一：select * into b from a where 1<>1（僅用於SQlServer）
法二：select top 0 * into b from a
2、說明：拷貝表(拷貝數據,源表名：a 目標表名：b) (Access 可用)
--insert into b(a, b, c) select d,e,f from b;
--select top 0 * into Nusers from users
--insert into Nusers(username,userpassword) select username,userpassword from users
--select * from Nusers
3、說明：跨資料庫之間表的拷貝(具體數據使用絕對路徑) (Access 可用)
insert into b(a, b, c) select d,e,f from b in ‘具體資料庫’ where 條
件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
insert into b(a, b, c) select d,e,f from b in ‘具體資料庫’ where 條
件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、說明：子查詢(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c
from a where a IN (1,2,3)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c
from a where a IN (1,2,3)
8、說明：between 的用法,between 限制查詢數據范圍時包括了邊界值,not bet
ween 不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 數值1 and 數值2
ween 不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 數值1 and 數值2
10、說明：兩張關聯表，刪除主表中已經在副表中沒有的信息
delete from table1 where not exists ( select * from table2 where tabl
e1.field1=table2.field1 )
delete from table1 where not exists ( select * from table2 where tabl
e1.field1=table2.field1 )
12、說明：日程安排提前五分鍾提醒
SQL: select * from 日程安排 where datediff('minute',f 開始時間,getdat
e())>5
SQL: select * from 日程安排 where datediff('minute',f 開始時間,getdat
e())>5
13、說明：一條sql 語句搞定資料庫分頁
select top 10 b.* from (select top 20 主鍵欄位,排序欄位 from 表名 order by 排序
欄位 desc) a,表名 b where b.主鍵欄位 = a.主鍵欄位 order by a.排序欄位
select top 10 b.* from (select top 20 主鍵欄位,排序欄位 from 表名 order by 排序
欄位 desc) a,表名 b where b.主鍵欄位 = a.主鍵欄位 order by a.排序欄位
具體實現：
關於資料庫分頁：
declare @start int,@end int
@sql nvarchar(600)
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not
in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql
關於資料庫分頁：
declare @start int,@end int
@sql nvarchar(600)
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not
in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql
--select top(5)* from users where in(select top(5) from users)
--not in中就是外面語句的前身查詢的數據
select top (4) * from users where userid not in (select top (4*4) userid from users)
--select * from users
--not in中就是外面語句的前身查詢的數據
select top (4) * from users where userid not in (select top (4*4) userid from users)
--select * from users
16、說明：包括所有在 TableA 中但不在 TableB 和TableC 中的行並消除所有
重復行而派生出一個結果表
(select a from tableA ) except (select a from tableB) except (select
a from tableC)
重復行而派生出一個結果表
(select a from tableA ) except (select a from tableB) except (select
a from tableC)
19、說明：刪除重復記錄
1),delete from tablename where id not in (select max(id) from tablena
me group by col1,col2,...)
2),select distinct * into temp from tablename
delete from tablename
insert into tablename select * from temp
評價： 這種操作牽連大量的數據的移動，這種做法不適合大容量但數據操作
3),例如：在一個外部表中導入數據，由於某些原因第一次只導入了一部分，但
很難判斷具體位置，這樣只有在下一次全部導入，這樣也就產生好多重復的字
段，怎樣刪除重復欄位
1),delete from tablename where id not in (select max(id) from tablena
me group by col1,col2,...)
2),select distinct * into temp from tablename
delete from tablename
insert into tablename select * from temp
評價： 這種操作牽連大量的數據的移動，這種做法不適合大容量但數據操作
3),例如：在一個外部表中導入數據，由於某些原因第一次只導入了一部分，但
很難判斷具體位置，這樣只有在下一次全部導入，這樣也就產生好多重復的字
段，怎樣刪除重復欄位
alter table tablename
--添加一個自增列
add column_b int identity(1,1)
delete from tablename where column_b not in(
select max(column_b) from tablename group by column1,column2,...)
alter table tablename drop column column_b
--添加一個自增列
add column_b int identity(1,1)
delete from tablename where column_b not in(
select max(column_b) from tablename group by column1,column2,...)
alter table tablename drop column column_b
20、說明：列出資料庫里所有的表名
select name from sysobjects where type='U'-- U 代表用戶
select name from sysobjects where type='U'-- U 代表用戶
21、說明：列出表里的所有的列名
select name from syscolumns where id=object_id('TableName')
select name from syscolumns where id=object_id('TableName')
“where 1=1” 是表示選擇全部 “where 1=2”全部不選，
4、轉移資料庫給新用戶以已存在用戶許可權
exec sp_change_users_login 'update_one','newname','oldname'
go
exec sp_change_users_login 'update_one','newname','oldname'
go
5、檢查備份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
6、修復資料庫
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
8、說明：更改某個表
exec sp_changeobjectowner 'tablename','dbo'
exec sp_changeobjectowner 'tablename','dbo'
2.資料庫加密:
select encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同 encr
ypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同
select encrypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同 encr
ypt('原始密碼')
select pwdencrypt('原始密碼')
select pwdcompare('原始密碼','加密後密碼') = 1--相同；否則不相同
4.查看硬碟分區:
EXEC master..xp_fixeddrives
EXEC master..xp_fixeddrives
5.比較A,B 表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
6.殺掉所有的事件探察器進程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM m
aster.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM m
aster.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
9：獲取當前資料庫中的所有用戶表
select Name from sysobjects where xtype='u' and status>=0
select Name from sysobjects where xtype='u' and status>=0
11：查看與某一個表相關的視圖、存儲過程、函數
select a.* from sysobjects a, syscomments b where a.id = b.id and b.t
ext like '%表名%'
select a.* from sysobjects a, syscomments b where a.id = b.id and b.t
ext like '%表名%'
12：查看當前資料庫中所有存儲過程
select name as 存儲過程名稱 from sysobjects where xtype='P'
select name as 存儲過程名稱 from sysobjects where xtype='P'
13：查詢用戶創建的所有資料庫
select * from master..sysdatabases D where sid not in(select sid from
master..syslogins where name='sa')
或者
13
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
select * from master..sysdatabases D where sid not in(select sid from
master..syslogins where name='sa')
或者
13
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14：查詢某一個表的欄位和數據類型
select column_name,data_type from information_schema.columns
where table_name = '表名'
select column_name,data_type from information_schema.columns
where table_name = '表名'
-----------------------案例
例如1：
一張表有一萬多條記錄，表的第一個欄位 RecID 是自增長欄位， 寫一個SQL 語句，
找出表的第31 到第40 個記錄。
select top 10 recid from A where recid not in(select top 30 recid from A)
分析：如果這樣寫會產生某些問題，如果recid 在表中存在邏輯索引。
select top 10 recid from A where……是從索引中查找，而後面的select top 30 recid
from A 則在數據表中查找，這樣由於索引中的順序有可能和數據表中的不一致，這樣就導
致查詢到的不是本來的欲得到的數據。
解決方案
1， 用order by, select top 30 recid from A order by ricid 如果該欄位不是自
增長，就會出現問題
2， 在那個子查詢中也加條件：select top 30 recid from A where recid>-1
例2：查詢表中的最後以條記錄，並不知道這個表共有多少數據,以及表結構。
set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from
T)'
print @s exec sp_executesql @s
例如1：
一張表有一萬多條記錄，表的第一個欄位 RecID 是自增長欄位， 寫一個SQL 語句，
找出表的第31 到第40 個記錄。
select top 10 recid from A where recid not in(select top 30 recid from A)
分析：如果這樣寫會產生某些問題，如果recid 在表中存在邏輯索引。
select top 10 recid from A where……是從索引中查找，而後面的select top 30 recid
from A 則在數據表中查找，這樣由於索引中的順序有可能和數據表中的不一致，這樣就導
致查詢到的不是本來的欲得到的數據。
解決方案
1， 用order by, select top 30 recid from A order by ricid 如果該欄位不是自
增長，就會出現問題
2， 在那個子查詢中也加條件：select top 30 recid from A where recid>-1
例2：查詢表中的最後以條記錄，並不知道這個表共有多少數據,以及表結構。
set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from
T)'
print @s exec sp_executesql @s
沒有留言:
張貼留言