數據庫操作方法基礎。

----------試圖操作(view)-----------------------創建視圖create view titles_viewasselect title,type from titles--調用視圖select * from titles_view--刪除視圖drop view titles_view--修改視圖alter view titles_viewasselect title,type,PRice from titlesgo--------對表（Table）的操作------------------create table teacher(number int primary key,name varchar(20) not null,sex char(2) check(sex='男' or sex='女'),birthday datetime,job_title varchar(20),salary money,memo ntext,nicheng varchar(20) unique,height numeric(7,2))select * from teacherdrop table studentcreate table Student(number int primary key,name varchar(20) not null,sex char(2) check(sex='男' or sex='女'),teachernumber int foreign key references teacher(number))--在 Student 表 添加一個新列alter table Studentadd birthday datetime,salary money--在 Student 表 刪除一個已有的列alter table Studentdrop column salary--在 Sutdent 表 修改一個列的約束alter table Studentalter column name varchar(20)insert Student(number,name,sex,teachernumber)values(0003,'小小黑2','男',1)insert Student(number,name,sex,teachernumber)values(0004,'小小黑4','男',1)--外鍵必須產生于主鍵--在刪除的時候，如果這表上的列在其他表有外鍵的話--(如果插入的數據產生關聯)必須先刪外鍵數據之后，才可以刪除這表的數據------------查詢技術use pubsgo--查詢書名表的所有列select * from titles--查詢書名表的書名編號、書名名稱、單價、類型select * from titlesselect title_id,title,price,type from titles--as 用法 取別名select title_id as '書名編號',title as '書名名稱',price as '單價',type as'類型' from titles--oder by 排序 asc,desc--查詢書名表的所有列 按價格排序(從大到小) ascselect title,price from titles order by priceselect title,price from titles order by price asc--查詢書名表的所有列 按價格排序(從小到大)descselect title,price from titles order by price desc---where 條件--查看書名編號為：BU1111的記錄信息select * from titlesselect * from titles where title_id='BU1111'--查看書的類型是"business"的所有信息select * from titles where type='business'-- in 包含-- not in 不包含-- or 或者-- and 且--查看書的類型是"business,mod_cook"的所有信息select title,type from titles where type='business' ortype='mod_cook'select title,type from titles where typein('business','mod_cook')--查看書的類型不是"business,mod_cook"的所有信息select title,type from titles where type!='business' andtype!='mod_cook'select title,[type] from titles where type notin('busines','mod_cook')--一些函數應用min,max,sum,avg,count,count(*)select * from titles--不算price 等于null----min 最小值select min(price) from titlesselect price from titles where type='business'select min(price) from titles where type='business' -----max 最大值select max(price) from titles----- sum 總和select sum(price) from titles-----avg 平均值select avg(price) from titles-----count(*),count(列明)select count(*) as '總計' from titlesselect count(title_id) '總計' from titles-- like  像select * from titles--查一下 title_id 中有'BU'的所有行數-----'%' 代表所有字符select * from titles where title_id like '%BU%'-----‘_’ 代表一個字符select * from titles where title_id like '__1%'--group by 分組select type,count(*) '記錄總數',min(price) '最小價格',max(price)'最大價格',sum(price) '總價格',avg(price) '平均價格' from titles group bytype--比較運算符=,>,<,>=,<=,!=----！= 不等于select title,price from titlesselect title,price from titles where price>10--any 任何一個,all 都select title,price from titleswhere price >any(select price from titles wheretype='business')select price from titles where type='business'select min(price) from titles where type='business'select title,price from titleswhere price >all(select price from titles wheretype='business')select max(price) from titles--exists 存在use mastergo-------對數據庫（Database）的操作---------------if exists(select * from sys.databaseswhere name='db2')begin drop database db2endgocreate database db2gouse db2 2011/2/17 ylb pm17:20

作者：ylbtech出處：http://ylbtech.cnblogs.com/本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，否則保留追究法律責任的權利。