苏州IT之家群:46213669,技术交流,招聘就业,职业发展,欢迎苏州IT人士加入。


第2章      tsql

tsql语句的基本分类:

DML(Data Manipulation Language):数据操制语句
       它们是SELECTUPDATEINSERTDELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
  DDLData Definition Language):数据定义语句
       DDLDML要多,主要的命令有CREATEALTERDROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
  DCLData Control Language):数据控制语句
       是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_ownerdb_securityadmin等人员才有权力执行DCL




2.1约束

一约束:表中字段内容不可重复。

非空约束:字段必须有内容。

检查约束:字段内容必须在范围内。如1-100

主键约束:作为表的主键,必须要为非空。

外键约束:与外部表相关联。

默认约束:定义字段的默认内容。

------------------------------------------------------------------

2.2数据库语句



------------------------------------------------------------------

数据定义语句(DDL)


1create

(1)建数据库

create dababase 数据库名

 

2)建表:

Ctreat table item

Ino  char(6) not null uniqueprimary key,  //商品编码,最长6位,不能为空,且值是唯一的不能重复,,并作为主键。

 Iname nvarchar(15), not null,  //商品名不能为空。

Iprise money ,not null,   

Inumber int ,not null check (inumber>0and inumber<1000) ,//数量是大于0小于1000的检查约束)

Cno int not null foreign key referenceschangwu(Cno),    //外键约束。

Iremark text default(有货)

--Primary key (In0) //也可以在最后写出约束。

--Constraint PK_item_sno primary key(sno)  //也可以用这种方法在后面创建约束。

格式:constraint 约束名约束类型(字段名)   

             

2、改表结构,重命名表,重命名字段,约束增删(alter

(1)添加、删除约束

Alter table 表名

Add 字段名 字段类型 约束  //增加字段。

Drop column 字段名         //删除字段。

Drop 约束名     //删除表的约束。

Add constraint 约束名  约束类型  //增加约束。

例:

 

--添加主键约束  //要添加主键约束,必须是先增加非空约束。

alter table dbo.tblCreateConstraint2

add constraint PK_tblCreateConstraint2_id primarykey(id)

 

--唯一约束

alter table dbo.tblCreateConstraint2

add constraint UQ_tblCreateConstraint2_name unique(name)

 

--检查约束

alter table dbo.tblCreateConstraint2

add constraint CK_tblCreateConstraint2_age

check(age >= 0 and age <=100)

 

--默认约束

alter table dbo.tblCreateConstraint2

add constraint DF_tblCreateConstraint2_joinDate

default(getDate()) for joinDate   

 

--外键约束:

alter table shopping

add constraint FG_shopping_Cno foreignkey(cno) references warehouse(Cno);--//外键约束外面要定义主键表的字段名。--

 

 

(2)修改表字段类型

Alter table 表名

Alter column 字段名 字段类型 约束

 

:

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20)NOT NULL

3)增加和删除字段

Alter table shopping

Alter address nvarchar(30)    //给表shopping增加一个叫address的字段。

 

Alter table shopping

Drop column address         //删除一个叫address的字段。

4)重命名表

exec sp_rename '[原表名]','[新表名]'

5)重命名字段

exec sp_rename '[表名].[列名]','[表名].[新列名]'

6)只删除表中的数据,不删除表

truncate table 表名称


------------------------------------------------
数据操控语句(DML)

3、给表添加数据(insert

Insert int 表名

(字段1,字段2,。。。字段n  //可省略

Values

(1,值2,。。。,值n)  //字符型要用单引号引起来。

例:

insert into  shopping

values (100004,'蛋糕',100,1,100002,'有货') //shopping表中加一行数据。

4、修改表数据(update)

Update 表名

Set 列名=<表达式>,列名=<表达式>

Where<条件>

例:

update shopping

set Sprise=Sprise*0.8Sremark=’库存过高

where Snumber>50;  //更改所有库存数量大于50的商品的价格打八折,并且备注改为库存过高。

5、删除表数据 (delete)

Delete

From 表名

Where 条件   //记得加上where条件,不然会删除整个表中的数据。

Delete from shopping

Where Cno=’100002’; //删除所有仓库序号为100002的商品数据。

 

2.3查询数据(select//查的内容最多,单独列出来。

1、基本查询

1select * from shopping //查出shopping表中的所有数据。

2select  Sname,Snumber from shopping //找出表中snamesnumber字段。

3select sprise*0.8 from shopping //找出所有价格,并乘以0.8。可以用箱术表达式,字符串常量,和函数。

 

2、条件查询

(1)比较条件查询 (可用>,<,<=等运算符)

 select sprise

from shopping

where Snumber>50;                //查询所有库存数大于50的商品的价格。

 

2)范围条件查询between andnot between and

select sname,snumber

from shopping

where Sprise between 5 and 10;//查找价格在510元之间的所有商品的名字和数量。

 

Between and,指定一个范围。Between是低值,and是高值。

Not between and是指不在这个范围内的。

3)确定集合查询 in, not in

select sname,snumber

from shopping

where Sremark in('紧俏','缺货')//查询属于紧俏和缺货的商品的名字和数量。

 

Not in刚好相反。

 

4)字符匹配查询 like not like (这是模糊查询,很有用)

select sname,snumber

from shopping

where Sname like '%_'  //找到商品名中倒数第二个字带有包字的。

 

%代表任意长度。

_代表一个字符。

 

5)多重条件查询 and or

select sname

from shopping

where Sprise>50 and Snumber<50  //查询价格大于50,并且数量小于50的商品名。

 

select sname

from shopping

where Sprise>50 or Snumber<50 //查询价格大于50,或者数量小于50的商品。

 

上例in语句,也可以用or多重条件查询实现。

 

6)空值查询IS NULLIS NOT NULL

即判断是否为空值。

7order by 对查询结果排序

select sname,snumber,sprise

from shopping

where Sprise>50 or Snumber<50

order by snumber,sprise      //查询出价格大于50,或者数量小于50的商品,并用数量和价格排序。数量为主排序。

 

8)集函数查询

select COUNT(sname)

from shopping

where Sprise>50 or Snumber<50   //统计价格大于50,数量小于50的商品名的个数。

 

select MAX(snumber)

from shopping

where Snumber>40     //查询数量大于40的所有商品的最大数。即商品数最多的数字。

还有如:sum(),avg(),min()等函数。

 

9group by 对查询结果分组

select CNO        //可以有多个字段。需要分组的所有列必须放在groupby后面的。如果没有放在group by后面的字段,在select后面也是不允许有的,聚合函数除外。

 from shopping

where Snumber>40

group by cno

分组,即是根据条件查询出数据,并根据group by关键字后的字段,把相同的分为一组,分组就表示把相同的合并为一条,即表示只有一行,同一分组只用一行来显示。分组一般和聚合函数一起使用,分组分出组来,然后用聚合函数进行统计。

上面的例子的意思是,查出所有商品中数量大于40的商品的仓库号,并根据仓库号分组,一个仓库号只分为一组,即只有一行,如果多个商品都放在同一个仓库,也只列出一个仓库号来。

 

select CNO, count(sno)

 from shopping

where Snumber>40

group by cno   //查询所有库存数大于40的商品的仓库号,并根据仓库号分组,并计算每个仓库号中有多少种商品。

 

即先查库存数大于40的商品,并显示出仓库号。如果不分组的情况下,100002号仓库因为存有三种商品,会显示出三条100002仓库记录。因为根据仓库号分组,所以相同的三条100002仓库号,分组后成为一条记录。而且我们统计每个分组中的商品序号的数量,因为有三种商品,所以结果就是100002   3,的方式显示出来。

 

10having

分组后,再对分组出来的结果进行筛选,就用having

select CNO ,count(*) as counts  //这里as是别名,把统计函数在显示时用别名显示。

 from shopping

where Snumber>40

group by Cno

having COUNT(*)>1

//查询大于40的库存商品,并按仓库号分组,并统计出每个分组的商品数,并只显示分组中商品数大于1的仓库号。

 

 

 

3、联合查询   //多表查询

1)自然连接查询(内连接)  join

Selectshopping.Sno,shopping.Sprise,shopping.Snumber,warehouse.Cname //表名可以不加,但最好是加上。

from shopping join warehouse     //inner join等同于join

on shopping.cno=warehouse.cno   //相关联的的字段

where shopping.Snumber>40

order by shopping.sno             //排序

 

查询shopping表和warehouse表中仓库名相等的数据,然后显示出来,并按照shopping表中的sno排序。 

过程:shopping表的第一行数据(也叫元组)用CNOwarehouse表中的第一行中的cno进行对比,如果相等,就用自已的数据加上匹配的数据组成结果的第一行。接着和表warehouse第二行数据的cno进行对比,一直比到表尾,有多少组成多少新行。

然后shopping表的第二行数据接着用自已的cnowarehouse表中的第一行数据中的cno进行对比,有相等的就组成新行,一直到表尾。以此类推。

还可以写成:

Selectshopping.Sno,shopping.Sprise,shopping.Snumber,warehouse.Cname

From shopping,warehouse

Where shopping.cno=warehouse.cnoand shopping.Snumber>40

Order by shopping.sno

 

//和上面的效果一样。

 

(2)交叉连接,也叫卡氏积(crossjoin

//交叉连接即是不需要判断两个表是否有相等的字段,直接全部连接。即一个表的所有元组都要与另一个表的元组进行连接。如果两个表,一个表有三行,一个表有四行,那么新的表将有十二行。

 

select shopping.*,warehouse.*//这里可以把字段一个一个打出来,用星号代表表的所有字段。

from shopping,warehouse

3)外部连接

//外部连接的作用,主要解决空值匹配的问题。内连接必须有相匹配的字段,而外连接可以不需要。外部连接不需要两个表具有匹配记录。可以指定某个表的记录总是放到结果集中。根据哪个表的记录总是放到结果集中,分为左连接,右连接和全连接。

 

3)外部连接---左连接(left join

不管是否匹配条件,左表中的记录总会在结果中。如果左表中有元组(行)不与右表相匹配,就只显示出左表自已的数据,其他数据为空。

 

select shopping.Sno ,shopping.Sprise,shopping.Snumber,shopping.Sname,warehouse.Cno,warehouse.Cname,warehouse.Ctype

from shopping

left join  warehouse

on shopping.cno=warehouse.cno

 

//如果shopping表中有一行CNo100005,则warehouse中没有任何一条与之相匹配的cno数据,那么shopping表中的这一行仍显示在结果中,但因为没有匹配的warehouse表的数据,这行结果中,warehouse表的字段就全为空。

4)外部连接右连接(right join)

不管是否匹配条件,右表中的记录总会在结果集中。

不管是否匹配条件,右表中的记录总会在结果中。如果右表中有元组(行)不与左表相匹配,就只显示出右表自已的数据,其他数据为空。

 

 

5)外部连接全连接(full join)

左右表的记录都会在结果集中,是左右外连接的集合。

 

4、嵌套查询(子查询)

即一个查询的结果集供其他查询使用。

1In

select shopping.sno,shopping.Sname

from shopping

where snumber in

(select Snumber

from shopping

where Sprise>500)

//查询库存数量为价格高于500任何一个商品的库存数的。In,即属行任何一个值就行。等价于下面的=any

 

2Any

select shopping.sno,shopping.Sname

from shopping

where snumber =any

(select Snumber

from shopping

where Sprise>500)

 

//查询库存数量为价格高于500任何一个商品的库存数的。Any即大于等于,小于等于,或等于查询结果中的任何一个值的。

3All

select shopping.sno,shopping.Sname

from shopping

where snumber >all

(select Snumber

from shopping

where Sprise>500)

//比库存数超过500的商品的值都要大的商品。 All,即要大于等于,小于等于,或不等于所查询结果中的任何值。

 

(4) exists 只要查询结果为非空,刚外层的where子句返回真值,否则返回假值。是一个布尔类型。

select shopping.sno,shopping.Sname

from shopping

where exist

(select Snumber

from shopping

where Sprise>500)

//只要有大于500价格的商品,就返回ture.


--------------------------------------------------------------

数据操控语句



 

3章索引

 

4章视图

 

5函数

一、聚合函数

MAX() 求最大

MIN()  求最小

AVG()  求平均

SUM()  求和

COUNT() 求总数

 

二、字符串函数

 

 

6章编程

 

6.1数据类型(不同数据库的数据类型不一样)

1、整型

Bit 01,或空值。存turefalse,男或女这种。

Int 

Smallint

bigint

2、浮点型

Decimal(精度,宽度)   decimal (2,6)   精度,小数点后的位数,精度是整个数字的位数,包括小数点前的。 如:4321.23,精度为2,宽度为6. 

Money:货币类型。

Float:近似数型。比real范围小。

Real:浮点型。

 

3、字符类型

Char(m):固定长度,m是字符串最大的长度,如果不够最大长度,会以空格填充。只能存储ascii码字符串。

Varchar(m):可变长度。M为最大的长度,但如果不够最大长度时,不会以空格填充。

Nchar(m):固定长度,但存储的是unicode字符集。这是国际字符集。

Nvarchar(m):可度长度,但存储的是unicode字符集,也可以存储ascii码的字符串。

Text:大字符串。可存储231次方个字节的字符串。

 

4、日期类型:

 

Datetime: 1753年到9999

Smalldatetime:19002079

Timestamp:时间戳,日期加时间。。

 

5、二进制类型

Image:并非只能存二进制图片。还可以存储任何二进制数据。

 

 6.2  变量

1、全局变量

全局变量为sql自已提供的,只是调用即可。不需要你再定义。用@@作前缀

 

2、局部变量

declare @a int  //定义变量  

set @a=5        //赋值

print @a        //显示。

 

 

 

 

declare @user1 nvarchar(50)

select @user1='张三'     //用select赋值

print @user1

 

 

declare @user2 nvarchar(50)

select @user2 = Name from ST_User where ID=1

print @user2

 

6.3语句

1BEGIN...END程序块语句

程序块语句用于将多条T-SQL语句封装起来构成一个程序块。SQLServer在处理时,将整个程序块视为一条T-SQL语句执行。

 

begin

<T-SQL命令行或程序块>

end

经常与whileif...else组合起来使用,可以相互嵌套。有点类似于c#中的大括号。

 

2case  end    C#中不一样

1)类似于c#switch用法

Case     

SELECT Id,Name,

    CASE Sex

    WHEN 0 THEN '男'  //不作控制用,只是作为显示为的意思,如性别为0,显示为男。

      WHEN 1 THEN '女'

      ELSE '不清楚'

      END AS性别   //给别名。

FROM PERSON

 

2)类似于c#if else用法

Case

When <判断表达式>   then  <T-SQL命令行或程序块>

When <判断表达式>  then  <T-SQL命令行或程序块>

Else <T-SQL命令行或程序块>

End

 

例:

select Id,

    (case

            when chinese >= 80 then '优秀'   //作为显示为的意思。

            when chinese >= 60 then '及格'

            else  '不及格'

    end) as语文,

    (case

            when math >= 80 then '优秀'

            when math >= 60 then '及格'

            else '不及格'

    end) as数学,

       (case

            when english >= 80 then '优秀'

            when english >= 60 then '及格'

            else '不及格'

    end) as英语

from fenshu

 

 

 

3If else   //C#用法一至。

DECLARE @i int

SET @i = 10;

IF(@i < 5)

   PRINT '小于5';

ELSE IF(@i < 8)

   BEGIN

       PRINT '小于8'

   END

ELSE

   BEGIN

       PRINT '前面都不满足!'

   END

 

 

4while   //c#基本一至。

DECLARE @i int;

SET @i = 0;

WHILE(@i < 10)

BEGIN

    SET @i = @i + 1;

    IF(@i % 2 = 0)

    BEGIN

        PRINT('跳过2的倍数' + CAST(@i AS varchar));

       CONTINUE;

    END

    ELSE IF (@i = 7)

    BEGIN

        PRINT('到' + CAST(@i AS varchar) + '就跳出循环');

       BREAK;

    END

    PRINT @i;

END

 

6.4存储过程

1)带Output参数

Create procedure usp_name    //过程名

@ gongzi  int               //定义变量,可以给变量赋初值。

@jiangjin  int

@all  int output            //定义输出变量,即相当于c#中的Out参数,把值传递出去。

 

Bengin

Set @all=@gongzi+@jiangji

End

调用存储过程:

delacre @allmoney int    //定义一个变量,以接收Output变量的值。

execute usp_name@gongzi=5000,@jiangjin=3000,@allmoney=@all output  //调用存储过程,给变量赋值。

print @allmoney

2)带return

创建Return返回值存储过程

CREATE PROCEDURE PR_Sum2

    @a int,

    @b int

AS

BEGIN

    Return @a+@b

END

 

执行存储过程获取Return型返回值

declare @mysum2 int   //定义一个参数接收返回值。

execute @mysum2= PR_Sum2 1,2   //给参数赋值可以简写

print @mysum2

 

 

6.5事务

如果所有语句没有错误全执行了,就提交数据,否则就回滚。如果销售了多少商品,就应该从库存中减去多少商品。  但如果因为约束条件,或其他原因,销售表中增加商品的语句执行了,而库存表中减少商品的语句没有执行,那就会产生错误数据。所以这两条语句要么都执行了且无错误,就提交,如果其中任何一条语句有误,就回滚。

第一种方法:

--第一种方法:

 

  begin transaction           --开始事务             

 

 declare @myerro int;

 set @myerro=0;

 

 update commadity                    --当我不应用事务时,库存因为减后成为负数,此语句会报错,执行不成功.

 set cnumber=cnumber-151

 where cno=100001;

set @myerro+=@@ERROR;             --@@error是一个全局变量,一旦有错就会记录,有错就返回值.如果每错一次,myerro就加.

 

-----------------------

update sale                         --而销售会增加,但此时库存没有减掉.所以要么这两条都执行,要么都不执行.

set cnumber=cnumber+151

where cno=100001

set @myerro+=@@ERROR;    --每条语句后面都加一条记录错误。

 

if(@myerro=0)   --v如果等于,说明没有出过错.

  begin

     commit     --就提交.

  end

else            --否则就回滚.

begin

  rollback

 end

 

 

 

第二种方法:

begin try 

update commadity                    --当我不应用事务时,库存因为减后成为负数,此语句会报错,执行不成功.

 set cnumber=cnumber-151

 where cno=100001;

 

-----------------------

update sale                         --而销售会增加,但此时库存没有减掉.所以要么这两条都执行,要么都不执行.

set cnumber=cnumber+151

where cno=100001;

 

commit                                    --如果执行到这里还没有报错就提交数据.

end try

 

begin catch

rollback                                 --如果出错会到catch中来,就回滚.

end catch

 

6.6触发器




苏州IT之家群:46213669,技术交流,招聘就业,职业发展,欢迎苏州IT人士加入。