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

—————————– 數(shù)據(jù)庫的有關(guān)SQL語句 ————————-
1、數(shù)據(jù)庫
創(chuàng)建 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 原數(shù)據(jù)庫名
modify name=新名字
刪除 drop database data_name

2、架構(gòu)
創(chuàng)建 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 文件組名

——————————— 表的有關(guān)SQL語句 ——————————–

1、表
創(chuàng)建：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 列名 列的數(shù)據(jù)類型 null / not null
刪除列：alter table table_name
drop column 列名
修改列的名稱： exec sp_rename ‘表名.字段名’, ‘新的字段名’, ‘column’ (提示：盡量不要改列名，容易引起錯誤)
修改列的數(shù)據(jù)類型： alter table table_name
alter column 列名 列的數(shù)據(jù)類型
3、對數(shù)據(jù)的操作
插入: insert into table_name(字段1,字段2,字段3) values( 值, 值, 值,)
刪除：delete from where stu_name=’王偉’ and id=3
修改：update table_name set 字段名=值 where id=4

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

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
)

意義：用于和加強表之間的聯(lián)系，當(dāng)在添加,修改和刪除數(shù)據(jù)時，保持幾張表中數(shù)據(jù)的一致性

—————————— 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
提示：聚合函數(shù)不能在where中使用，所以才用到了having

3、清空表
truncate table table_name

4、消除重復(fù)列
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)

——————————- 表聯(lián)接 ———————————

1、內(nèi)聯(lián)接：select * from student as s inner join class
as c on s.cl_id=c.cl_id where …..

2、左外聯(lián)接：返回第一個已命名的表中符合條件的所有行
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、完全外聯(lián)接：返回左表、右表中的所有值
select * from student as s full join class as c on s.cl_id=c.cl_id

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

6、兩個以上的表的聯(lián)接：
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 結(jié)合多個表的數(shù)據(jù)
select stu_id as ‘學(xué)生編號’ , stu_name as ‘名字’ from student1
union
select id ,name from student2
union
select s_id , s_name from student3

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

1、把子查詢用作派生表

可用子查詢產(chǎn)生一個派生表，用于代替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、把子查詢用作表達(dá)式

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

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

3、使用子查詢關(guān)聯(lián)數(shù)據(jù)

關(guān)聯(lián)子查詢可以作動態(tài)表達(dá)式，因為它隨著外層查詢的每一次變化而變化

例1：
select stu_id,stu_name,cl_id,(select count(stu_id) from student) as 記錄條數(shù) 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)

————————— 變量、條件語句、循環(huán)語句 ————————–

1、變量

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

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

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

(2)全局變量—只能用系統(tǒng)提供的，用戶不能定義，所以只了解一下書上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、循環(huán)語句

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

—————————— 事務(wù) ——————————

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

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

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

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 ‘操作失敗，執(zhí)行回滾’
rollback transaction /* 回滾事務(wù) */
end

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

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

視圖就是查詢語句的別名，而這個別名就稱為視圖
視圖的分類：標(biāo)準(zhǔn)視圖、索引視圖、分區(qū)視圖

1、創(chuàng)建視圖

語法：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、修改視圖

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

4、獲取視圖的定義語句

exec sp_helptext 視圖名稱

5、查看視圖的列的信息

exec sp_help 視圖名稱

6、查看視圖的虛擬表

select * from 視圖名稱

7、更改視圖內(nèi)的數(shù)據(jù)

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

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

1、創(chuàng)建無參數(shù)存儲過程

語法： 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、創(chuàng)建帶輸入?yún)?shù)的存儲過程

語法：create procedure 過程名
@參數(shù)1 數(shù)據(jù)類型(=默認(rèn)值)，
@參數(shù)2 數(shù)據(jù)類型(=默認(rèn)值)
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、創(chuàng)建帶輸入、輸出參數(shù)的存儲過程

語法：create procedure 過程名
@參數(shù)1 數(shù)據(jù)類型 output，
@參數(shù)2 數(shù)據(jù)類型(=默認(rèn)值)
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

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

4、存儲過程的管理

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

————————————- 函數(shù) ————————————–

Sql server2005支持三種用戶定義函數(shù)：標(biāo)量函數(shù)、內(nèi)嵌表值函數(shù)、多語句表值函數(shù)

1、標(biāo)量函數(shù)

標(biāo)量函數(shù)是根據(jù)輸入?yún)?shù)值的不同來獲得不同的函數(shù)值，標(biāo)量函數(shù)可以有多個輸入?yún)?shù)，但是只能有一個返回值；標(biāo)量函數(shù)體包括一條或多條sql語句，由begin開始，以end 結(jié)束；用returns字句定義函數(shù)返回值的數(shù)據(jù)類型，并返回函數(shù)值

語法： create function 函數(shù)名(標(biāo)量參數(shù) 標(biāo)量數(shù)據(jù)類型)
returns 函數(shù)返回值的類型
as
begin
函數(shù)體
return 變量/標(biāo)量表達(dá)式
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

調(diào)用函數(shù)： declare @name nvarchar(10)
select @name=架構(gòu)名.f_count(‘王二’)
print @name

2、內(nèi)嵌表值函數(shù)

內(nèi)嵌表值型函數(shù)以返回的都不是一個標(biāo)量數(shù)據(jù)，而是一個表，返回的表值函數(shù)還可以提供參數(shù)化視圖功能。

語法： create function 架構(gòu).函數(shù)名(標(biāo)量參數(shù) 數(shù)據(jù)類型)
returns table
as
return (select語句)

調(diào)用函數(shù)：select * from 架構(gòu).函數(shù)名(參數(shù))

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

SQL server2005 中，用于實現(xiàn)數(shù)據(jù)完整性的機制有這幾種：數(shù)據(jù)類型、規(guī)則和默認(rèn)值、約束、觸發(fā)器、XML架構(gòu)

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

1、主鍵約束 primary key

直接創(chuàng)建表時創(chuàng)建約束：

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

在已創(chuàng)建表中添加約束：

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

刪除主鍵：

alter table 表名
drop constraint 主鍵名

2、外鍵約束 foreign key

直接創(chuàng)建表時創(chuàng)建：

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

在已創(chuàng)建表中添加：

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

直接創(chuàng)建表時創(chuàng)建：

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

在已創(chuàng)建表中添加：

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

刪除：

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

4、核對約束 check

直接創(chuàng)建表時創(chuàng)建：

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

在已創(chuàng)建表中添加：

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、默認(rèn)約束 default

直接創(chuàng)建表時創(chuàng)建：

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

在已創(chuàng)建表中添加：

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

刪除：

alter table 表名
drop constraint 默認(rèn)約束名
例如：drop constraint df_id

—————————————- 觸發(fā)器 ——————————————–

在sql server里面也就是對某個表的一定操作，觸發(fā)某種條件，從而執(zhí)行的一段程序。觸發(fā)器是一個特殊的存儲過程。觸發(fā)器常用于強制業(yè)務(wù)規(guī)則，它是一種高級約束，通過事件進(jìn)行觸發(fā)而被執(zhí)行

常見的觸發(fā)器有三種：分別應(yīng)用于insert,update,delete事件

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

1、update型觸發(fā)器

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型觸發(fā)器

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

———————————– 級聯(lián)更新、刪除 ————————————-

級聯(lián)更新、刪除是對主鍵進(jìn)行的，外鍵卻不能

1、創(chuàng)建級聯(lián)更新、刪除

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表進(jìn)行更新、刪除時才能實現(xiàn)級聯(lián)

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

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

1、創(chuàng)建聚集索引

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

2、創(chuàng)建非聚集索引

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

3、創(chuàng)建唯一、非聚集索引

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

4、刪除索引

drop index 表名.索引名

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

5、修改索引的名稱

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

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