欢迎访问织梦学习网,加群!!!!!!!!!!!!!!!!
当前位置:主页 > 教程帮助 >

织梦cms中常用的php查询语句

2014-03-27 dedecms教程帮助 织梦,cms,中常,用的,php,查询,语句,织梦,cms,

dedestudy.cn/' target='_blank'>织梦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:\file1.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 ‘表名.旧索引名’,‘新索引名’

注意:尽量不要修改索引的名字,容易破坏脚本和存储过程