sqlserver 命令一览表

sqlserver 命令一览表   

 

1.创建数据库：

    create database Student

    on

    (

     name=Student_Data,

     filename='f:\data\student_Data.mdf',

     size=10,

     maxsize=20,

     filegrowth=5

    )

    log on

    (

     name=Student_Log,

     filename='f:\data\student_Log.ldf',

     size=10,

     maxsize=20,

     filegrowth=5

    )

2.修改数据库：

   1>添加数据文件：

         alter database student

         add file

         (

         name=Student_Data2,

         filename='f:\data\student_Data2.ndf',

         size=10,

         maxsize=20,

         filegrowth=5

         )

   2>修改数据文件：

         alter database student

         modify file

         (

         name=Student_Data,

         size=15

         )

3.删除数据库:

         drop database student

4.设置数据库选项:

         sp_dboption student,'single user',true

5.修改数据库名:

         sp_renamedb 'student','students'

6.查看服务器上的数据库：

         sp_databases

7.查看数据库上的文件:

         sp_helpdb

         sp_helpdb students

8.压缩数据库:

          sp_dboption student,'single user',true

          go

          DBCC shrinkdatabase(students,50)

9.断开与连接数据库:

      1>断开:    sp_detach_db 'students'

      2>连接:    sp_attach_db 'students','f:\students_data.mdf'

10.备份和恢复数据库

      1>备份:    backup database students to disk='h:\students_back'

      2>恢复:    restore database students from disk='h:\students_back'

二.表

1.创建表：(先建主键表,再建外键表)

               create table xsxxb

            (       

             xh char(10) primary key,

             xm char(8),

             xb char(2),

              csrq datetime,

             dh char(20)

            )

        go

        create table kmxxb

        (

         kmbh char(10),

         kmmc char(20),

          primary key(kmbh)

        )

        go

        create table xscjb

        (

        xh char(10),

          kmbh char(10),

          fs int,

          foreign key(xh)references xsxxb,

        foreign key(kmbh)references kmxxb

        )

2.修改表:

   1>增加字段

         alter table xsxxb

         add bz char(50) null

   2>删除字段

         alter table xsxxb

         drop column bz

3.删除表：(先删外键表,再删主键表)

      drop table xscjb

      drop table xsxxb

      drop table kmxxb

4.复制一个表:

   select * into xsxxb2 from xsxxb

5.创建临时表 #,##)

       create table #xsxxb

            (       

             xh char(10) primary key,

             xm char(8),

             xb char(2),

              csrq datetime,

             dh char(20)

            )

 

       select * from #xsxxb

6.创建用户定义数据类型:

        use students

        go

        sp_addtype sts,'varchar(20)','not null','dbo'

 

        sp_addtype sts,datatime,'null','dbo'

7.删除用户定义数据类型:

        sp_droptype sts

三.操作表中的数据

   1>使用 INSERT 语句向表中插入数据:

            insert into xsxxb values('008','','','')

   2>使用 UPDATE 语句修改表中的数据:

            update xsxxb set xm='不' where xh='001'

   3>使用 DELETE 语句删除表中的数据:

            delete from xsxxb where xh='001'

            delete from xsxxb where xh in('002','004')

            delete from xsxxb

四.系统内置函数的使用

1.聚合函数:

   1>AVG（表达式）     返回表达式中所有值的平均值。仅用于数字列并自动忽略 null 值。

   2>COUNT（表达式）   返回表达式中非 null 值的数量。可用于数字和字符列。

   3>COUNT(*)          返回表中的行数（包括有 null 值的行）。

   4>MAX（表达式）     返回表达式中的最大值，忽略 null 值。可用于数字、字符和日期时间列。

   5>MIN（表达式）     返回表达式中最小值，忽略 null 值。可用于数字、字符和日期时间列。

   6>SUM（表达式）     返回表达式中所有值的总和，忽略 null 值。仅用于数字列。

2.转型函数:

   CONVERT(datatype[(length)], expression [,style])

       select convert(char(20),getdate(),101)

3.日期函数:

   1>GETDATE()         当前的系统日期。

   2>DATEADD(datepart, number, date)   返回带有指定数字 (number) 的日期 (date)，

                                         该数字添加到指定的日期部分 (date part)  。

       select dateadd(yy,2,getdate())  (yy,mm,dd,ww,hh,mi,ss)

   3>DATEDIFF(datepart, date1, date2)   返回两个日期中指定的日期部分之间的不同.

       select datediff(yy,getdate(),'2008/09/09')

   4>DATENAME(datepart, date)          返回日期中日期部分的字符串形式。

   5>DATEPART(datepart, date)           返回日期中指定的日期部分的整数形式。

4.数学函数:

5.字符串函数:

     rtrim()

     ltrim()

     ltrim(rtrim())

     space(2)

     substring(列名,开始位置,长度)

     right(列名,长度)

     left(列名,长度)

     stuff(列名,开始位置,长度,字符串)

五.表的连接

1.内部连接:

        select xsxxb.xh,xm,xscjb.fs from xsxxb inner join xscjb

         on xsxxb.xh=xscjb.xh

2.多表连接:

        select xsxxb.xh,xm,kmmc,fs from xsxxb inner join xscjb

        on xsxxb.xh=xscjb.xh

        join  kmxxb

        on xscjb.kmbh=kmxxb.kmbh

3.左外部连接:

       select xsxxb.xh,xm,fs from xsxxb left outer join xscjb

       on xsxxb.xh=xscjb.xh

4.右外部连接:

       (与左外部连接相反)

5.完全外部连接:

       select xsxxb.xh,xm,fs from xsxxb full join xscjb

       on xsxxb.xh=xscjb.xh

6.交叉连接:

      select xm,fs from xsxxb cross join xscjb

7.自连接:

       select A.xh,A.fs,B.xh from xscjb A join xscjb B

       on A.kmbh=B.kmbh

       where A.xh>B.xh

8.联合运算符(union):

       select xh,xm from xsxxb

       union   

       select xh,xm from xsxxb2

六.数据汇总

1.排序:   (Asc升,desc降)

       select * from xscjb order by fs Asc

2.分组 group by all 包含不符合指定的where条件的组,但将它们设为null)

 

        select xh,sum(fs) as tol from xscjb

        where xh='004'

        group by all xh

3.指定分组后,组的条件(having):

          select xh,sum(fs) as tol from xscjb

          group by xh

          having sum(fs)>80

4.compute汇总:

          select xh,fs from xscjb

          order by xh

          compute sum(fs)

5.compute by汇总:   

          select xh,fs from xscjb

          order by xh

          compute sum(fs) by xh

          compute sum(fs)

6.rollup和cube函数:

   rollup()  对group by子句中的列按不同的等级进行分组.

 

           select xh,sum(fs) as tol from xscjb

            group by xh with rollup

   cube()    是rollup的扩展.

七.数据的查询

1.SELECT 语句的数据检索形式

    1>显示所有列和行:

         SELECT * FROM authors

    2>显示所有行和特定的列:

         SELECT pub_name, city FROM publishers

    3>显示限定范围的行:

         SELECT stor_id, qty, title_id FROM sales

             WHERE qty BETWEEN 20 AND 50

    4>显示与值列表相匹配的行:

         SELECT * FROM publishers

             WHERE state IN ('CA', 'TX', 'NY')

    5>根据未知值显示行:

         SELECT price, title FROM titles

             WHERE price IS NULL

    6>显示/隐藏重复的值:

         SELECT DISTINCT city FROM authors

    7>显示根据多个搜索条件查询的结果:

         SELECT title FROM titles

             WHERE pub_id = 0736 AND type = 'business'

2.SELECT 语句中使用的关键字:

    BETWEEN 测试值是否在两值之间

    EXISTS 测试值是否存在

    IN 测试在一定范围内的值

    LIKE 测试类似这个值的值

    IS NULL 测试是否为 null 值

3.查询通配符的使用:

     (%,_,[],^)

   1> select * from xsxxb where xm like '张%'

   2> select  * from xsxxb where xm like '_花%'

   3> select * from xsxxb where xm like '_[花娇]%'

   4> select * from xsxxb where xm like '_[^花娇]%'

4.简单子查询:

   1>使用返回单个值的子查询:

         select xm,xb,csrq,dh from xsxxb

          where xh=(select xh from xscjb where fs=70)

5.相关子查询:

   1>使用返回多行单列的子查询:

         select xm,xb,csrq,dh from xsxxb

          where xh in(select xh from xscjb where fs>70)

   2>使用exists关键字验证存在性的子查询:

           select xm,xb,csrq,dh from xsxxb

             where exists (select xh from xscjb where kmbh='3' and fs>70

                                          and xh=xsxxb.xh )

   3>使用not exists关键字验证存在性的子查询:

            select xm,xb,csrq,dh from xsxxb

             where not exists (select xh from xscjb where kmbh='3' and fs>70

                                          and xh=xsxxb.xh )

八.流程控制语句

   1>声明和使用变量:

      declare @i int

      set @i=3

      select @i=(select fs from xscjb where xh='001')

   2>begin...end 语句块:

      begin

       print'dfdfdfd'

      end

   3>条件语句:

      if (select fs from xscjb where xh='002') >70

        begin

         print'dfdfedfd'

        end

      else if (select fs from xscjb where xh='002') <60

        begin

         print'888888'

        end

      else

        begin

         print'99999999'

        end

   4>分支语句:

        select gg=case fs   'gg是别名

          when 60 then 'df'

          when 70 then 'xdf'

          when 80 then 'yb'

          when 90 then 'xgf'

         else 'mf'

         end

        from xscjb

   5>循环语句:

      declare @i int

      declare @sum int

          set @i=0

          set @sum=0

       while @i<10

        begin

        set @sum=@sum+@i

        set @i=@i+1

        end

       print @sum

   6>标签:

       select * from xsxxb

       goto A

       select * from kmxxb

       A:

        select * from xscjb

九.视图的使用

1.创建视图:

   1>创建基于表中指定列的视图:

            create view GetFs

            as

            select xh,fs from xscjb

   2>创建基于表中指定行的视图:

            create view GetFs2

            as

            select xh,fs from xscjb where fs=80

   3>创建基于表中汇总信息的视图:

            create view GetFs3

            as

            select xh,sum(fs) as tol from xscjb

            group by xh

   4>创建基于多个表的视图:

            create view GetFs4

            as

            select xsxxb.xh,xm,kmxxb.kmmc,xscjb.fs from xsxxb,kmxxb,xscjb

            where xsxxb.xh=xscjb.xh and xscjb.kmbh=kmxxb.kmbh

   5>创建基于视图的视图:

            create view GetFs5

            as

            select * from GetFs4

             where fs>75

2.更改视图:

           (把create换为alter)

3.删除视图:

   1>删除视图中的数据:

         delete GetFs2

   2>删除视图:

         drop view GetFs2

4.通过视图修改数据:

     create view GetFs6

     as

     select xh,xm from xsxxb

   1>插入数据:

       insert into GetFs6 values('005','黄三')

   2>更新数据:

       update GetFs6 set xh='006' where xh='005'

   3>删除数据

       delete GetFs6 where xh='006'

十.存储过程与触发器

1.创建存储过程与执行存储过程:

   1>创建一个不带参数的存储过程:

        create proc Display_orders

        as

        select * from orders

   2>创建一个带输入参数的存储过程:

        create proc Display_orders

        @cusid char(20)

        as

        select * from orders where customerid=@cusid

   3>创建一个带输入，输出参数的存储过程:

        create proc Display_Name

        @Name char(20) output

        as

        select @Name=(select xm from xsxxb,kmxxb,xscjb where xsxxb.xh=xscjb.xh and

                       fs=(select max(fs) from xscjb where kmbh=(select kmbh from kmxxb

                             where kmmc=@Name))

                      and kmxxb.kmbh=(select kmbh from kmxxb where kmmc=@Name))

                  from xsxxb,kmxxb,xscjb

        print @Name

2.更改存储过程:

       (把create换为alter)

3.删除存储过程:

      drop proc Display_Name

4.创建触发器:

   1>创建INSERT 触发器:

        create trigger checkFs

        on  xscjb

        for insert

        as

         if(select fs from inserted)<50

         begin

         print'bu neng cha!'

         rollback tran

         end

   2>创建UPDATE 触发器:

        create trigger NoUPdateXm

        on xsxxb

        for update

        as

        if update(xm)

         begin

         print'bu neng geng xing xm!'

         rollback tran

         end

   3>创建DELETE 触发器:

        create trigger NoDelete002

        on xsxxb

        for delete

        as

        if (select xh from deleted)='002'

         begin

         print'bu neng shang chu xh wei 002!'

         rollback tran

         end

5.更改触发器:

          (把create换为alter)

6.删除触发器:

        drop trigger NoDelete002

7.禁用和启用触发器:

    1> 禁用:

        alter table xsxxb

        disable trigger NoDelete002

   2> 启用:

        alter table xsxxb

        enable trigger NoDelete002

 

十一.用户自定义函数

1.创建用户自定义函数:

   1>创建数量型用户自定义函数：（返回一个简单的数值,如：int,char等）

      create function NumAdd

      (@num1 int,@num2 int)

      returns int

      as

      begin

      return(@num1+@num2)

      end

   调用：select dbo.NumAdd(4.6)

   2>创建表值型用户自定义函数:（返回一个Table型数据）

      use northwind

      go

      create function DisplayXsxx

      (@xh char(20))

      returns table

      as

      return(select * from xsxxb where xh=@xh)

   调用：select * from DisplayXsxx('002')

2.更改用户自定义函数:

     (把create换为alter)

3.删除用户自定义函数:

      drop function DisplayXsxx

十二.游标

1.创建游标:

declare Fs_level cursor

static

for select xm,sum(fs) from xsxxb,xscjb where xsxxb.xh=xscjb.xh group by xm

declare

     @fs int,

     @Name varchar(20)

2.打开游标:

open Fs_level

3.提取游标:

   fetch next from Fs_level into @Name,@fs

   while(@@Fetch_status=0)

       begin

           if @fs<150

               print'总分太低！'

           else

              print'高分！'

           fetch next from Fs_level into @Name,@fs

       end

4.关闭游标:

close  Fs_level

5.销毁游标:

deallocate Fs_level

十三.数据完整性

1.缺省

  1>创建缺省:

      create default dd

      as 'MN'

  2>绑定缺省:

      sp_bindefault dd,'xsxxb.xh'

  3>取消缺省:

      sp_unbindefault 'xsxxb.xh'

  4>删除缺省：

      drop default dd

2.规则

  1>创建规则:

      create rule rr

      as @scode in('MN','ND','SD')

  2>绑定规则:

      sp_binderule rr, 'xsxxb.xh'

  3>取消规则:

      sp_unbindrule 'xsxxb.xh'

  4>删除规则：

      drop rule rr

3.约束

  1>主键约束:

      primary key

  2>外键约束:

      foreign key(列名) references 表名

  3>唯一约束:

      unique

  4>检查约束:

      check(xb='男' or xb='女')

十四.数据库的安全性

1.帐户

   1>创建一个帐户:

    sp_addlogin 'zj','0822','pubs'

   2>查询帐户的相关信息:

    select convert(varbinary(32),password) from syslogins where name='zj'

   3>更改，删除帐户:

     sp_password '0822','888','zj'

2.数据库用户

   1>添加数据库用户

     use northwind

     go

     sp_grantdbaccess zj

   2>删除数据库用户

     use northwind

     go

     sp_revokedbaccess [zj]

3.角色

   1>浏览服务器角色的内容：

      sp_helpsrvrole

   2>角色分配给帐户：

       sp_addsrvrolemember zj,'sysadmin'

4.权限

   1>授予权限:

       use northwind

       go

       grant insert,update on xsxxb to zj

   2>撤消权限：

       revoke create table,create view from zj

   3>拒绝访问:

       use northwind

       go

       deny select,insert,update ,delete on xsxxb to zj

十五.事务与锁

1.事务

  1>一个标准事务:

    begin tran

      select * from xsxxb

    commit tran

   或

    begin tran insert xscjb

      insert into xscjb values('002','2',70)

    commit tran

  2>返回几个事务在运行:

     begin tran

        select * from xsxxb

        select * from kmxxb

        select @@trancount  --执行第一次时返回值为1，每执行一次事务数量就加1。

     commit tran

        select @@trancount  --返回值为0。

  3>复杂可回滚事务:

      declare @i int

    set @i=0

    print ltrim(str(@i))

  begin tran

      print ltrim(str(@i))

      select @i=(select count(*) from xsxxb)

   if @i>4

       begin

          rollback tran

          return      --停止过程中当前批的执行，并在下一批的开始处恢复执行。

       end

   else

       print ltrim(str(@i))

       select * from xsxxb

       select @@trancount

      begin tran      --嵌套事务

          select * from xscjb

          select @@trancount

      commit tran

  commit tran

  4>嵌套事务:

     declare @i int

    set @i=0

    print ltrim(str(@i))

  begin tran

      print ltrim(str(@i))

      select @i=(select count(*) from xsxxb)

   if @i>4  --改为3试一试

       begin

          rollback tran

          return      --停止过程中当前批的执行，并在下一批的开始处恢复执行。

       end

   else

       print ltrim(str(@i))

       select * from xsxxb

       select @@trancount

      begin tran      --嵌套事务

          select * from xscjb

          select @@trancount

      commit tran

  commit tran

  5>与表相联系的事务:

    declare @i int

       set @i=0

         begin tran

           update xscjb set fs=85 where fs=70

            set @i=2

           if @i>1  --改为3试一试

           begin

             rollback tran

             return

           end

           else

         commit tran

     go

   select * from xscjb

  6>设置保存点:

    declare @i int

       set @i=0

         begin tran

           update xscjb set fs=120 where fs=90

            save tran s1

            set @i=2

           if @i>1  

           begin

             rollback tran s1

             return

           end

           else

         commit tran

     go

   select * from xscjb

  7>含子查询的事务:

    begin tran

          declare @fs int

          update xscjb set fs=95 where fs=90

          select @fs=(select max(fs) from xscjb)

        if @fs<100  --改为90试一试

         begin

           rollback tran

           return

         end

        else

       commit tran

        go

           select * from xscjb

  8>隐式事务:

    [im'plisit]暗示的  

      set implicit_transactions on  --打开

         update xscjb set fs=95 where fs=90

         select @@trancount

         go

          select * from xscjb

2.锁

  事务的隔离级别：

    1>读提交：

      第一个窗口：

       begin tran

           update xscjb set fs=95 where xh='002'

      第二个窗口:

       set transaction isolation level read committed

            go

             select * from xscjb where xh='002'

    2>读未提交:

       第一个窗口：

      begin tran

            update xscjb set fs=80 where xh='002'

       第二个窗口:

      set transaction isolation level read uncommitted

         go

           select * from xscjb

    3>可重复读:

      第一个窗口：

      set transaction isolation level repeatable read

  go

   begin tran

      select * from xscjb

      update xscjb set fs=100 where xh='002'

      select * from xscjb

      rollback

    第二个窗口:

        set transaction isolation level read committed

go

   begin tran

       insert into xscjb values('002','2',120)

       select * from xscjb where fs=120

       rollback

    4>顺序读:

      第一个窗口：

      set transaction isolation level serializable

  go

   begin tran

      select * from xscjb

      update xscjb set fs=100 where xh='002'

      select * from xscjb

 

      第二个窗口:

        set transaction isolation level read committed

go

   begin tran

       insert into xscjb values('002','2',120)

       select * from xscjb where fs=120

 

 

1. STATUS;

2. mysql> SELECT @@global.time_zone, @@session.time_zone;

+--------------------+---------------------+

| @@global.time_zone | @@session.time_zone |

+--------------------+---------------------+

| SYSTEM             | SYSTEM              |

+--------------------+---------------------+

1 row in set (0.00 sec)

3.

mysql> SHOW CREATE TABLE t\G

*************************** 1. row ***************************

Table: t

Create Table: CREATE TABLE `t` (

`i` int(11) default NULL,

`j` int(11) NOT NULL,  `k` int(11) default '-1'

) ENGINE=MyISAM DEFAULT CHARSET=latin1

4. SHOW DATABASES LIKE 'm%';

5.SHOW WARNINGS\G显示错误

复制创建表技巧

6. CREATE TABLE CityCopy1 SELECT * FROM City;复制表

7. CREATE TABLE CityCopy2 SELECT * FROM City where id=5;

按条件复制表:将city 表格的结构复制同时复制id=5的记录

8. CREATE TABLE CityCopy3 SELECT title FROM City where id=5;

   title(是字段)指定city表中的title字段复制创建成CityCopy3表

9.重命名

(1)        ALTER TABLE t1 RENAME TO t2;

  (2)Rename tabae t1 to t2;

(2)        RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2; 批量命名

10.删表

(1)DROP TABLE IF EXISTS t1;或者DROP TABLE t1

  (2)DROP TABLE t1, t2, t3; 批量删表

11.清空表记录

(1)        DELETE FROM t;或者TRUNCATE TABLE t;

(2)        DELETE FROM t WHERE id=5;指定条件删除

删除表

DELETE FROM table_name;

TRUNCATE TABLE table_name;

12.select 另类用法

(1) mysql> SELECT 1 AS One, 4*3 'Four Times Three';

+-----+------------------+

| One | Four Times Three |

+-----+------------------+

|   1 |               12 |

+-----+------------------+

(2)SELECT last_name, first_name FROM t ORDER BY 1, 2;

排序ORDER BY 1, 2升序;2,1降序

(3) mysql> SELECT description, IF('abc' LIKE pattern,'YES','NO') FROM patlist;

  在对pattern字段中别条件

+--------------------------------+-----------------------------------+

| description                    | IF('abc' LIKE pattern,'YES','NO') |

+--------------------------------+-----------------------------------+

| empty string                   | NO                                |

| non-empty string               | YES                               |

| string of exactly 3 characters | YES                               |

+--------------------------------+-----------------------------------+

(4) SELECT ABS(-14.7), ABS(14.7);

(5) select * from tablename order by data desc limit 0,20返回20条数据(同微软数据库中select top 20 * from tablename 一样)

select * from tablename limit 0,20

说明：limit 0,20 （0是从表的第一行开始，是可以指定的，20是查询返回20条记录）

13.数据库加密

SELECT MD5('a');

mysql> SELECT MD5('a');

+----------------------------------+

| MD5('a')                         |

+----------------------------------+

| 0cc175b9c0f1b6a831c399e269772661 |

+----------------------------------+

14.随机数

SELECT RAND();

15. INSERT插入值的技术

(1)        INSERT INTO people (name,age)VALUES('William',25),('Bart',15),('Mary',12);多重插入

(2)INSERT INTO people VALUES(25,'William');不用中间的字段名字

(3) INSERT INTO t (i) VALUES(1),(2),(3),(4),(5);

16.数据替换

(1)        REPLACE INTO people (id,name,age) VALUES(12,'William',25);

(2)        多重替换

REPLACE INTO people (id,name,age)VALUES(12,'William',25),(13,'Bart',15),(14,'Mary',12);

(3)