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  ‘表名.舊索引名’,‘新索引名’

   註意:盡量不要修改索引的名字,容易破壞腳本和存儲過程 

摘自  Vericlongmore的專欄
 

發佈留言