織夢cms中常用的php查詢語句

—————————– 數據庫的有關SQL語句 ————————-
1、數據庫
創建 create database data_name
on primary
(name= ,filename= ,size= ,maxsize= ,filegrowth=),
filegroup [輔助文件組名]
(name= ,filename= ,size= ,maxsize= ,filegrowth=)
log on
(name= ,filename= ,size= ,maxsize= ,filegrowth=)
修改 alter database 原數據庫名
modify name=新名字
刪除 drop database data_name

2、架構
創建 create schema jiagou
刪除 drop schema jiagou

3、輔助文件
添加 alter database data_name
add file(name=file1,filename=’d:ile1.ndf’,size=10MB,filegrwth=10MB) to filegroup
group1
修改 alter database data_name
modify file(name= ,filename= ,size= ,maxsize= ,filegrowth=)
刪除 alter database data_name
remove file file_name

4、日志文件(修改的時候，邏輯名稱不能修改)
添加 alter database data_name
add log file (name= ,filename= ,size= ,maxsize= ,filegrowth=)
修改 alter database data_name
modify file(name= ,filename= ,size= ,maxsize= )

5、文件組
添加 alter database data_name
add filegroup group_name
修改 alter database data_name
modify filegroup 原文件組名 name=新文件組名
刪除 alter database data_name
remove filegroup 文件組名

——————————— 表的有關SQL語句 ——————————–

1、表
創建：create table table_name
(
id int identity(1001,3) primary key not null,
st_name nvarchar(10) null,
sex nvarchar(4) default(‘男’),
gongzi money,
shijian datetime
)
修改表名：exec sp_rename ‘table’, ‘table33′ (注意：盡量不要改表名，容易引起其它對象的錯誤)
刪除：drop table table_name

2、表的列（字段）的操作
添加列：alter table table_name
add 列名 列的數據類型 null / not null
刪除列：alter table table_name
drop column 列名
修改列的名稱： exec sp_rename ‘表名.字段名’, ‘新的字段名’, ‘column’ (提示：盡量不要改列名，容易引起錯誤)
修改列的數據類型： alter table table_name
alter column 列名 列的數據類型
3、對數據的操作
插入: insert into table_name(字段1,字段2,字段3) values( 值, 值, 值,)
刪除：delete from where stu_name=’王偉’ and id=3
修改：update table_name set 字段名=值 where id=4

———————————— 主鍵、外建 (補充) ————————-
1、創建：

create table class
(
cl_id int primary key,
cl_name nvarchar(10) null
)

create table address
(
add_id int primary key,
add_name nvarchar(10) null
)

create table student
(
stu_id int primary key,
stu_name nvarchar(10) null,
cl_id int foreign key references class(cl_id) null ,
add_id int foreign key references address(add_id) null
)

意義：用于和加強表之間的聯系，當在添加,修改和刪除數據時，保持幾張表中數據的一致性

—————————— SQL查詢語句 ——————————–

1、排序
select top(3) * from student order by cl_id desc

2、分組
select class_id ,sum(score) as chengji from student group by class_id
having sum(score)>=250
order by sum(score) desc
提示：聚合函數不能在where中使用，所以才用到了having

3、清空表
truncate table table_name

4、消除重復列
select distinct 列名 from table_name (提示：只能顯示出此列，其它列顯示不出來)

5、select * from table_name where name in (‘張三’,’李四’,’王五’)
select * from table_name where score in (100,90,87,89,96)

——————————- 表聯接 ———————————

1、內聯接：select * from student as s inner join class
as c on s.cl_id=c.cl_id where …..

2、左外聯接：返回第一個已命名的表中符合條件的所有行
select * from student as s left join class as c on s.cl_id=c.cl_id
where ….

3、右外鏈接：返回第二個已命名的表中符合條件的所有行
select * from student as s right join class as c on s.cl_id=c.cl_id
where …..

4、完全外聯接：返回左表、右表中的所有值
select * from student as s full join class as c on s.cl_id=c.cl_id

5、交叉聯接：將從被聯接的表中返回所有可能的行組合(會生成一個笛卡爾積)
select * from student as s cross join class as c
where ….

6、兩個以上的表的聯接：
select * from student as s join class as c
on s.cl_id=c.cl_id join address as a on s.add_id=a.add_id
where s.name=’張三’

7、union 結合多個表的數據
select stu_id as ‘學生編號’ , stu_name as ‘名字’ from student1
union
select id ,name from student2
union
select s_id , s_name from student3

————————— 子查詢 ———————-

1、把子查詢用作派生表

可用子查詢產生一個派生表，用于代替where、having、from子句中的表，注意要用別名來引用這個派生表

select s.stu_name,s.cl_id from (select * from student where stu_id >2) as s
where s.stu_name=’王二’

2、把子查詢用作表達式

在sql語句中，所有使用表達式的地方，都可以用子查詢代替，此時子查詢必須取值為單個列值的表，于是這個子查詢可以代替where子句中包含in關鍵字的表達式

select * from student where stu_id not in (select id from student where stu_id>2)

3、使用子查詢關聯數據

關聯子查詢可以作動態表達式，因為它隨著外層查詢的每一次變化而變化

例1：
select stu_id,stu_name,cl_id,(select count(stu_id) from student) as 記錄條數 from student

例2：
select * from student as s join class as c on s.cl_id=c.cl_id
where not exists(select * from class where c.cl_id>3)

————————— 變量、條件語句、循環語句 ————————–

1、變量

(1)局部變量—可以select、set進行賦值

例一：declare @i int
select @i=count(*) from student
print @i

例二：declare @sname varchar(4)
set @sname=’張三’
print @sname

(2)全局變量—只能用系統提供的，用戶不能定義，所以只了解一下書上70頁的就可以

2、條件語句

(1) if…else…語句
declare @sex_id int
declare @sex_name nvarchar(4)
select @sex_id=sex from student (where …)
if @sex_id=1
begin
set @sex_name=’男’
end
else
begin
set @sex_name=’女’
end
print @sex_name

(2) case語句
select stu_name as 姓名, (case cl_id
when 1 then ‘一班’
when 2 then ‘二班’
when 3 then ‘三班’
when 4 then ‘四班’
else ‘暫無班級’
end ) as 班級 from student

3、循環語句

while 語句：
declare @i int
declare @name nvarchar(10)
set @i=1
while @i<13
begin
select @name=stu_name from student where stu_id=@i
print @name
set @name=”
set @i=@i+1
end

—————————— 事務 ——————————

1、事務的概念
事務是一種機制、是一種操作序列，它包含了一組數據庫操作命令，這組命令要么全部執行，要么全部不執行。因此事務是一個不可分割的工作邏輯單元。這特別 使用于多用戶同時操作的數據通信系統：訂票、銀行、保險公司以及證券交易系統等。需要使用事務的SQL語句通常是更新和刪除操作等。

2、創建事務
開始事務：begin transaction
提交事務：commit transaction
回滾事務：rollback transaction
@@error全局變量顯示錯誤號

3、例題
begin transaction /* 開始事務 */
declare @error_sum int /* 錯誤數量 */
declare @sum int /* 人數 */
declare @a nvarchar(10) /* 轉錢者 */
declare @b nvarchar(10) /* 得錢者 */
declare @x int /* 轉賬金額 */

set @error_sum=0
set @a=’于聰’
set @b=’許’
set @x=2000

select @sum=count(*)from icbc where users=@a or users=@b
if @sum = 2
begin
set @error_sum=@error_sum+0
end
else
begin
set @error_sum=@error_sum+1
end

update icbc set moneys=moneys-@x where users=@a
set @error_sum=@error_sum + @@error
update icbc set moneys=moneys+@x where users=@b
set @error_sum=@error_sum + @@error

if @error_sum > 0
begin
print ‘操作失敗，執行回滾’
rollback transaction /* 回滾事務 */
end

else
begin
print ‘操作成功’
commit transaction /* 提交事務 */
end

—————————– 視圖 ——————————-

視圖就是查詢語句的別名，而這個別名就稱為視圖
視圖的分類：標準視圖、索引視圖、分區視圖

1、創建視圖

語法：create view 視圖名稱(列的別名)
as （select 查詢語句）

create view v_student(sname,cname)
as (select s.stu_name,c.cl_name from
student as s join class as c on
s.cl_id=c.cl_id)

2、刪除視圖

drop view 視圖名稱

3、修改視圖

語法跟創建視圖是一樣的：alter view 視圖名(別名1，別名2)
as
(select …………)

4、獲取視圖的定義語句

exec sp_helptext 視圖名稱

5、查看視圖的列的信息

exec sp_help 視圖名稱

6、查看視圖的虛擬表

select * from 視圖名稱

7、更改視圖內的數據

update from 視圖名 set ….. where ….

—————————- 存儲過程 ———————————-

1、創建無參數存儲過程

語法： create procedure 過程名
as
sql語句體

例題：create procedure p_student
as
select * from student as s join class as c
on s.cl_id=c.cl_id where s.stu_id>2

2、創建帶輸入參數的存儲過程

語法：create procedure 過程名
@參數1 數據類型(=默認值)，
@參數2 數據類型(=默認值)
as
select 語句體

例題：create procedure p_student
@name nvarchar(10)
as
select * from student as s join class as c
on s.cl_id=c.cl_id where s.stu_name=@name

3、創建帶輸入、輸出參數的存儲過程

語法：create procedure 過程名
@參數1 數據類型 output，
@參數2 數據類型(=默認值)
as
sql 語句體
return

例題：create procedure p_stu_cla
@cname nvarchar(10) output，
@sname nvarchar(10)
as
select @cname=c.cl_name from student as s join class as c
on s.cl_id=c.cl_id where s.stu_name=@sname
return

調用：declare @cname nvarchar(10)
exec p_stu_cla @cname output , ‘王二’
select @cname

4、存儲過程的管理

查看存儲過程的定義
exec sp_helptext 過程名
查看存儲過程的信息
exec sp_help 過程名
刪除存儲過程
drop procedure 過程名
修改存儲過程
alter procedure 過程名
@參數名 數據類型=默認值 output
as
sql 語句
return

————————————- 函數 ————————————–

Sql server2005支持三種用戶定義函數：標量函數、內嵌表值函數、多語句表值函數

1、標量函數

標量函數是根據輸入參數值的不同來獲得不同的函數值，標量函數可以有多個輸入參數，但是只能有一個返回值；標量函數體包括一條或多條sql語句，由begin開始，以end 結束；用returns字句定義函數返回值的數據類型，并返回函數值

語法： create function 函數名(標量參數 標量數據類型)
returns 函數返回值的類型
as
begin
函數體
return 變量/標量表達式
end

例題： create function f_count( @sname nvarchar(10) )
returns nvarchar(10)
as
begin
declare @cname nvarchar(10)
select @cname=cl_name from student as s jion class as c
on s.cl_id=c.cl_id where s.stu_name=@sname
return @cname
end

調用函數： declare @name nvarchar(10)
select @name=架構名.f_count(‘王二’)
print @name

2、內嵌表值函數

內嵌表值型函數以返回的都不是一個標量數據，而是一個表，返回的表值函數還可以提供參數化視圖功能。

語法： create function 架構.函數名(標量參數 數據類型)
returns table
as
return (select語句)

調用函數：select * from 架構.函數名(參數)

——————————– 約束 ————————————-

SQL server2005 中，用于實現數據完整性的機制有這幾種：數據類型、規則和默認值、約束、觸發器、XML架構

約束的種類：主鍵(primary key)約束、外鍵(foreign key)約束、唯一(unique)約束、核對(check)約束、默認(default)約束

1、主鍵約束 primary key

直接創建表時創建約束：

create table student
(
sid int identity not null,
sname nvarchar(10)，
constraint 主鍵名 primary key (sid)
)

在已創建表中添加約束：

alter table 表名
add constraint 主鍵名 primary key (列名)
例如：add constraint pk_id primary key (sid)

刪除主鍵：

alter table 表名
drop constraint 主鍵名

2、外鍵約束 foreign key

直接創建表時創建：

create table student
(
id int identity not null,
sname nvarchar(10),
class_id int ,
constraint 外鍵名 foreign key (class_id) references 其它表(列名)
)

在已創建表中添加：

alter table 表名
add constraint 外鍵名 foreign key (列名) references 其它表(列名)
例如：add constraint fk_cid foreign key (class_id) references class(class_id)

刪除：

alter table 表名
drop constraint 外鍵名
例如：drop constraint fk_cid

3、唯一約束 unique

直接創建表時創建：

create table student
(
id int identity not null,
sname nvarchar(10) ,
class_id int ,
constraint 唯一約束名 unique (sname)
)

在已創建表中添加：

alter table 表名
add constraint 唯一約束名 unique (列名)
例如：add constraint uni_name unique (sname)

刪除：

alter table 表名
drop constraint 唯一約束名
例如：drop constraint uni_name

4、核對約束 check

直接創建表時創建：

create table student
(
id int identity not null,
sname nvarchar(10) ,
class_id int ,
constraint 核對約束名 check (class_id>0 and class_id<4)
)

在已創建表中添加：

alter table 表名
add constraint 核對約束名 check (列名的約束條件)
例如：add constraint che_id unique (class_id>0 and class_id<4)

刪除：

alter table 表名
drop constraint 核對約束名
例如：drop constraint che_id

5、默認約束 default

直接創建表時創建：

create table student
(
id int identity not null,
sname nvarchar(10) ,
class_id int constraint 默認約束名 default(默認值)
)

在已創建表中添加：

alter table 表名
add constraint 默認約束名 default (默認值) for 列名
例如：add constraint df_id default (1002) for class_id

刪除：

alter table 表名
drop constraint 默認約束名
例如：drop constraint df_id

—————————————- 觸發器 ——————————————–

在sql server里面也就是對某個表的一定操作，觸發某種條件，從而執行的一段程序。觸發器是一個特殊的存儲過程。觸發器常用于強制業務規則，它是一種高級約束，通過事件進行觸發而被執行

常見的觸發器有三種：分別應用于insert,update,delete事件

例如兩個表：student學生表（id編號，stuid學號，stu_name學生名字）
library借書表（id編號，stu_id學號，book_name書名）

1、update型觸發器

create trigger tri_student on student
after update
as
if update(stu_id)

begin
update library set stu_id=i.stuid from library l ,deleted d, inserted i
where l.stu_id=d.stuid
end

2、delete型觸發器

create trigger trg_student on student
after delete
as
delete library from library l,deleted d
where l.stu_id=d.stuid

———————————– 級聯更新、刪除 ————————————-

級聯更新、刪除是對主鍵進行的，外鍵卻不能

1、創建級聯更新、刪除

create table class
(
cid int identity not null,
cname nvarchar(10),
constraint pk_cid primary key(cid)
)

create table student
(
sid int identity not null,
sname nvarchar(10),
cid int ,
constraint fk_cid foreign key (cid) references class (cid)
on delete cascad / on update cascade
)

注：只能對主表class表進行更新、刪除時才能實現級聯

———————————- 索引 —————————————

索引是的指表中的數據和其相應存儲位置的列表。它類似于書中目錄，可以快速地查找表中的數據而不必掃描整個數據表。

1、創建聚集索引

create clustered index 索引名
on 表(列名)

2、創建非聚集索引

create nonclustered index 索引名
on 表(列名)

3、創建唯一、非聚集索引

create unique nonclustered index 索引名
on 表(列名)

4、刪除索引

drop index 表名.索引名

注意：刪除索引時要注意，如果索引是在create table語句中創建的，只能用alter table語句刪除。
如果索引是用create index創建的，可用drop index

5、修改索引的名稱

sp_rename ‘表名.舊索引名’，‘新索引名’

注意：盡量不要修改索引的名字，容易破壞腳本和存儲過程