[SQL]SQL語(yǔ)句分析集錦
(本文瀏覽次數: 24)
注:紅色標識為常用語(yǔ)句(唉,寫(xiě)了一天了,那個(gè)累呀!)
1.創(chuàng )建數據庫
create database bvtc_db --數據庫名
on primary --在主文件組中指定文件.默認為第一個(gè)文件
(
name=‘bvtc_db_data‘, --指定主數據文件名稱(chēng)(邏輯名)
filename=‘F:\bvtc_db_data.mdf‘, --指定主文件的操作系統文件名稱(chēng)和路徑.必須為安裝SQL服務(wù)器的計算機上的文件夾.(物理文件名)
size=5MB, --初始容量大小
maxsize=20MB, --最大容量
filegrowth=20% --文件增長(cháng)量(默認時(shí)為10%,不能超過(guò)maxsize)
)
log on --指定建立數據庫的事務(wù)日志文件,文件擴展名為:ldf
(name=‘bvtc_db_log‘, -- 指定事務(wù)日志文件邏輯名稱(chēng)
filename=‘F:\bvtc_db_log.ldf‘,--指定物理文件名
size=10MB, --初始容量大小
filegrowth=2MB --文件增長(cháng)量
)
collate Chinese_PRC_CI_AS --指定默認排序方法
GO
2.查看數據庫信息
除了直接查看數據庫屬性外,我們以用系統存儲過(guò)程式sp_helpdb:
exec sp_helpdb ‘bvtc_db‘ --exec為執行語(yǔ)句,bvtc_db為數據庫名
3.管理數據庫
(1)打開(kāi)或切換數據庫: use bvtc_db
(2)修改數據庫容量：可以直接在數據庫屬性里改，也可以用如下語(yǔ)句：
use bvtc_db
go
alter database bvtc_db
modify file
(
name=bvtc_db_data,--同樣也可以是事務(wù)日志文件(bvtc_db_log)
size=20MB
)
(3)縮減數據庫容量:
方法1:右擊數據庫名-所有任務(wù)-收縮數據庫
方法2:
dbcc shrinkdatabase(database_name[,new_size,[,‘masteroverride‘]])
實(shí)例:
use bvtc_db
go
exec sp_dboption ‘bvtc_db‘,‘single user‘,TRUE --在縮減數據庫前要將其設為單用戶(hù)模式.
go
dbcc shrinkdatabase(‘bvtc_db‘) --此處將數據庫縮減到最小容量.
go
exec sp_dboption ‘bvtc_db‘,‘single user‘,FALSE
go
(4).設定修改數據庫選項:
方法1:數據庫屬性,選項
方法2:
(1)查看數據庫選項:exec sp_dboption ‘database_name‘
(2)修改數據庫選項:exec sp_dboption [database_name,option_name,{true|false}]
實(shí)例:更改數據庫bvtc_db為只讀狀態(tài):
exec sp_dboption ‘bvtc_db‘,‘read only‘,true
go
(5)更改數據庫名稱(chēng): exec sp_renamedb oldname,newname --權限僅限于sa
實(shí)例:將數據庫bvtc_db改名為bvtc1_db
exec sp_dboption ‘bvtc_db‘,‘read only‘,false
go
exec sp_renamedb ‘bvtc_db‘,‘bvtc_db1‘,
go
(6)刪除數據庫:
drop database database_name1[,database_name2...]
或:exec sp_dbremove database_name --這是利用系統存儲過(guò)程.
實(shí)例:將bvtc_db數據庫刪除:
Use master
drop database bvtc_db
go
4.數據庫表的操作
(1)創(chuàng )建表: 在數據庫student創(chuàng )建"學(xué)生表"
use student
go
create table 學(xué)生
(學(xué)號 char(12) constraint pk_xh primary key, --設定學(xué)號為該表的主鍵.
姓名 char(8) not null,--不充許為空.
性別 char(2),
出生日期 datetime,
入學(xué)時(shí)間 datetime,
班級代碼 char(9) constraint fk_bjdm references 班級(班級代碼), --將班級代碼設為此表的外鍵.
專(zhuān)業(yè)代碼 char(2),
系部代碼 char(2)
)
on stugroup1 --將學(xué)生表創(chuàng )建在stugroup1文件組上.
go
(2)修改表:使用alter table語(yǔ)句可以對表的結構和約束進(jìn)行修改.
實(shí)例:<1>在"學(xué)生"表中增加"家庭住址"一列,數據類(lèi)型為varchar(30),不允許為空.
use student
go
alter table 學(xué)生
add 家庭住址 varchar(30) not NULL --add為增加列或約束的子句.
go
<2>在"學(xué)生"表中修改"家庭住址"一列為varchar(50),允許為空.
use student
go
alter table 學(xué)生
alter column 家庭住址 varchar(50) NULL --alter column為修改表列屬性的子句.
go
<3>刪除字段
use student
go
alter table 學(xué)生
drop column 家庭住址
go
(3)刪除表:
use student
go
drop table 學(xué)生
go
(4)數據的添加與表的查看.
<1>向表中添加數據: insert [into] tablename(字段1,字段2,...) values(value1,value2,...)
use student
go
insert 學(xué)生 -- 表名
(學(xué)號,姓名,家庭住址) --所要插入的列名
values(‘001‘,‘種子張‘,‘我家‘) --所插入的值
如果是按順序逐個(gè)列插入數據,則可以省略列名.
還可以將select查詢(xún)結果插入到表中: insert [into] tablename1 (字段1,字段2,...) select 字段1,字段2... from tablename2
例:insert into 系部1
(系部代碼,系部名稱(chēng),系部主任)
select 系部代碼,系部名稱(chēng),系部主任
from 系部
go
(5)數據的修改:update
update table_name set Column_Name=** where ID=***
實(shí)例:根據"教學(xué)計劃"表中的課程號,專(zhuān)業(yè)代碼和專(zhuān)業(yè)學(xué)級修改教師任課表中的"起始周","結束周"列的值.
use student
go
update 教師任課
set 教師任課.起始周=教學(xué)計劃.起始周,教師任課.結束周=教學(xué)計劃.結束周
from 教學(xué)計劃
where 教學(xué)計劃.課程號=教師任課.課程號
and 教學(xué)計劃.專(zhuān)業(yè)代碼=教師任課.專(zhuān)業(yè)代碼
and 教學(xué)計劃.專(zhuān)業(yè)學(xué)級=教師任課.專(zhuān)業(yè)學(xué)級
(6)數據的刪除:
<1>delete語(yǔ)句,Delete table_name 刪除"表格記錄"會(huì )把操作記錄在日志中，可以通過(guò)事務(wù)回滾來(lái)恢復刪除的數據
delete from table_name where 條件
實(shí)例:刪除"課程注冊"表中姓名為"種子張"的課程號為"001"的選課信息.
use student
go
delete 課程注冊
from 學(xué)生
where 學(xué)生.姓名=‘王麗‘ and 課程注冊.專(zhuān)業(yè)代碼=‘001‘
go
<2>truncate table tablename清空表格,刪除"表格記錄"不可恢復.
5.簡(jiǎn)單查詢(xún).
(1)select語(yǔ)句的主要格式.
select select_list
[into new_table_name] --指定使用結果集來(lái)創(chuàng )建一個(gè)新的數據表,表名為:new_table_name
from table_list
[where search_conditions]
[Group by group_by_list] --將結果集按照group_by_list列分組.
[having search_conditions]
[order by order_list[asc|desc] -- asc為升序(默認),desc為倒序.
(2)實(shí)例:
<1>查詢(xún)"學(xué)生"表中全體學(xué)生的記錄 *
use student
go
select * from 學(xué)生
go
<2>為結果集指定別名 as
use student
go
select 姓名,year(getdate()-year(出生日期) as 年齡 from 學(xué)生 --注意是用as ,年齡即為別名.
go
<3>選擇表中不重復的記錄 distinct
use student
go
select distinct 姓名
from 學(xué)生表
go
<4>限制返回行數 top
限制返回行數
選取第一條記錄:select top 1* from table_name
選取前N條記錄: select top N* from table_name
選取最后一條記錄:select top 1* from table_name order by column_name desc
選取最后N條記錄:select top N* from table_name order by column_name desc
選取任意一條記錄(隨機):select top 1* from table_name order by newid()
(3)查詢(xún)滿(mǎn)足條件的元組(常與where聯(lián)用)
<1>查詢(xún)條件: 比 較: =,<,>,>=,!=,<>,!>,!<;not+上述運算符
確定范圍: between and,not between and 判斷是否在范圍內
確定集合: in,not in判斷是否為列表中的值
字符匹配: like,not like 判斷是否與指定的字符通配格式相符
空 值: is null,not is null 判斷是否為空
多重條件: and,or,not 用于多重判斷
實(shí)例:1.查詢(xún)學(xué)生表中出生日期在1980-01-01和1982-01-01之間的學(xué)生姓名
select 姓名 from 學(xué)生 where 出生日期 between ‘1980-01-01‘ and ‘1982-01-01‘
<2>.字符匹配.
[not] like ‘<匹配串>‘ [escape‘<換碼字符>‘]
其中,匹配串可以是一個(gè)完整的字符串,也可以是與通配符組成的字符串.
通配符有:%,_,[ ],[^]四種.%表示任意個(gè)字符 如a%b表示以a開(kāi)頭,b結尾的任意個(gè)字符.,_表示任意一個(gè)字符,
[]表示方括號里的任意一個(gè)字符 如a[bcde]表示以a頭,bcde中任一個(gè)結尾的字符串.[^]表示不取方括號里的值.
實(shí)例:查詢(xún)"學(xué)生"表中姓王的同學(xué)的所有信息.
select * from 學(xué)生 where 姓名 like ‘王%‘
如果要查詢(xún)的字符本身就含有%,_等就要使用escape‘<換碼字符>‘短語(yǔ)對通配答進(jìn)行轉義了.
例:要查詢(xún)delphi_2.9
where 課程名 like ‘delphi/_2.9‘ escape‘/‘表示匹配字符串中緊跟在/之后的字符"_"不再是通配符.
<3>涉及空值的查詢(xún). not is null
實(shí)例:查詢(xún)"班級"表中備注為空的班級信息.
select * from 班級 where 備注 is null --is不能用"="代替.
<4>對查詢(xún)結果進(jìn)行排序 asc升序(默認),desc降序.
實(shí)例:查詢(xún)選修了課程號為001的課程的學(xué)生學(xué)號.按成績(jì)降序排列.
select 學(xué)號,成績(jì) from 課程注冊 where 課程號=‘001‘ order by 學(xué)號 asc,成績(jì) desc --有多個(gè)排序,先排前面的,相同則排后面的.
(4)對數據進(jìn)行統計.
集合函數:count([distinct|all]*)統計記錄個(gè)數.
count([distinct|all]<列名>)統計一列中值的個(gè)數.
sum([distinct|all]<列名> 計算一列值的總和(必須為數據類(lèi)型).
avg,max,min分別求一列值中的平均數,最大值,最小值.
實(shí)例:<1>返回學(xué)生表中學(xué)生總人數.
select count(*) as 學(xué)生人數 from 學(xué)生
<2>返回一列的平均數
select avg(成績(jì)) as 平均分 from 課程注冊
<3>對結果進(jìn)行分組.
group by 列名 [having 篩選條件表達式]
<4>使用compute子句
格式如下：compute 集合函數 [by 列名] --分類(lèi)匯總.它顯示詳細記錄，group by不顯示。
其中，集合函數：sum(),avg(),count()等.
select * from 課程注冊 order by 學(xué)號 compute sum(成績(jì)）by 學(xué)號
(5)用查詢(xún)結果生成新表 select into
select * into 課程注冊副表 from 課程注冊
創(chuàng )建空副表： select * into 學(xué)生副表 from 學(xué)生表 where 1=2
(6)合并結果集 union
將多個(gè)查詢(xún)結果集合并為一個(gè)結果集
select 語(yǔ)句
{union select 語(yǔ)句},[,...n]
各個(gè)結果集列數必須相同，對應數據類(lèi)型也要相同.最后結果集返回第一個(gè)select語(yǔ)句.
實(shí)例：查看“課程注冊”表中選修了001課程或者選修了002課程的學(xué)生，也就是選擇兩者的并并集.
select * from 課程注冊 where 課程號=‘001‘
union
select * form 課程注冊 where 課程號=‘002‘
(7)連接查詢(xún) cross join ?。ú⒓?br> select 列名列表 from table_name1 cross join table_name2
實(shí)例：select * from 產(chǎn)品 cross join 產(chǎn)品銷(xiāo)售
(8)等值與非等值連接查詢(xún)
連接條件：[表名1].<列名><比較運算符>[表名２].<列名> 其中比較運算符有=,>=,<=,!=,>,<.
格式：select 列名列表 from table_name1 [inner] table_name2 on table_name1.列名=table_name2.列名 --inner指定為內連接，可以省.
(on 主鍵=外鍵）
實(shí)例：select * from 產(chǎn)品 inner join 產(chǎn)品銷(xiāo)售 on 產(chǎn)品.產(chǎn)品編號=產(chǎn)品銷(xiāo)售.產(chǎn)品編號 --inner join為交集，不同于cross join
自然連接：在等值連接中，把目標列中重復的屬性列去掉則為自然連接.
(9)自身連接查詢(xún)
實(shí)例：查詢(xún)選修了兩門(mén)或兩門(mén)以上課程的學(xué)生學(xué)號和課程號
select a.學(xué)號，a.課程號 from 課程注冊 as a join 課程注冊 as b
on a.學(xué)號=b.學(xué)號 and a.課程號=b.課程號
(10)外連接查詢(xún)（分為左外連接，右外連接和全外連接）
<1>.左外連接(table_name1在左邊）
Select * from table_name1 left outer join table_name2 on table_name1.column_name=table_name2.column_name
<2>.右外連接(把left 改為right就行了)
<3>.全外連接(觸類(lèi)旁通,left 改為 full)將左表所有記錄分別與右表的每一條記錄進(jìn)行連接組合，結果集中不滿(mǎn)足連接條件的以null顯示出來(lái).
(11)復合連接條件查詢(xún).
實(shí)例：查詢(xún)成績(jì)在75分以上的學(xué)生的學(xué)號，姓名，選修課的課程號，課程名，專(zhuān)業(yè)代碼，成績(jì)，任課老師的教師編號，姓名.
select b.課程號，c.教師編號，c.姓名,a.學(xué)號,b.專(zhuān)業(yè)代碼,b.專(zhuān)業(yè)學(xué)級,b.學(xué)期，b.成績(jì)
from 學(xué)生 as a join 課程注冊 as b on a.學(xué)號=b.學(xué)號 and b.成績(jì)>75 join 教師 as c
(12)子查詢(xún) （查詢(xún)塊的嵌套）
select * from 產(chǎn)品
where 產(chǎn)品編號 in (select 產(chǎn)品編號 from 產(chǎn)品銷(xiāo)售） --()內為子查詢(xún)，其中in可以改為比較運算符.
(13)帶有any或all運算符的子查詢(xún).
實(shí)例：查詢(xún)比"1"班中所有學(xué)生年齡都要小的其它班上的學(xué)生學(xué)號與姓名
select 學(xué)號，姓名
from 學(xué)生
where 出生日期>all
(select 出生日期 from 學(xué)生 where 班級代碼=‘1‘)
and 班級代碼<>‘1‘
(14)帶有exists,not exists運算符的子查詢(xún).不返回任何數據,只產(chǎn)生邏輯true,false.
實(shí)例：查詢(xún)所有選修了‘1‘課程的學(xué)生姓名
select 學(xué)號，姓名
from 學(xué)生
where exists
(select * from 課程注冊
where 學(xué)號=學(xué)生.學(xué)號 and 課程號=‘1‘
)
好，寫(xiě)完收工，這只是第一部分，未完待寫(xiě).
聯(lián)系客服
