php mysql数据库常用sql语句命令集合

   
/*
 ****** author:Vericlongmore ******
 ****** update date:2012-04-05 *****
 ****** spot:beijing **********
*/
-----------------------------  数据库的有关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  ‘表名.旧索引名’，‘新索引名’ 


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