sqlserver学习--SQL语句
|
苏州IT之家群：46213669，技术交流，招聘就业，职业发展，欢迎苏州IT人士加入。 第2章??????tsql tsql语句的基本分类： DML（Data Manipulation Language）：数据操制语句 2.1约束 唯一约束:表中字段内容不可重复。 非空约束：字段必须有内容。 检查约束：字段内容必须在范围内。如1-100。 主键约束：作为表的主键，必须要为非空。 外键约束：与外部表相关联。 默认约束：定义字段的默认内容。 ------------------------------------------------------------------ 2.2数据库语句 ------------------------------------------------------------------ 数据定义语句（DDL） 1、create (1)建数据库 create dababase 数据库名 ? （2）建表： Ctreat table item （Ino? char(6) not null uniqueprimary key,? //商品编码，最长6位，不能为空，且值是唯一的不能重复,，并作为主键。 ?Iname nvarchar(15),not null,? //商品名不能为空。 Iprise money,??? 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 表名称 ------------------------------------------------ 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.8，Sremark=’库存过高’ where Snumber>50;? //更改所有库存数量大于50的商品的价格打八折，并且备注改为库存过高。 5、删除表数据?(delete) Delete From 表名 Where 条件?? //记得加上where条件，不然会删除整个表中的数据。 Delete from shopping Where Cno=’100002’; //删除所有仓库序号为100002的商品数据。 ? 2.3查询数据（select）//查的内容最多，单独列出来。 1、基本查询 （1）select * from shopping //查出shopping表中的所有数据。 （2）select? Sname,Snumber from shopping //找出表中sname和snumber字段。 （3）select sprise*0.8 from shopping //找出所有价格，并乘以0.8。可以用箱术表达式，字符串常量，和函数。 ? 2、条件查询 (1)比较条件查询 （可用>,<,<=等运算符） ?select sprise from shopping where Snumber>50;??????????????? //查询所有库存数大于50的商品的价格。 ? （2）范围条件查询（between and和not between and） select sname,snumber from shopping where Sprise between 5 and 10;//查找价格在5到10元之间的所有商品的名字和数量。 ? 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 NULL，IS NOT NULL） 即判断是否为空值。 （7）order 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()等函数。 ? （9）group 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,的方式显示出来。 ? （10）having 分组后，再对分组出来的结果进行筛选，就用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表的第一行数据（也叫元组）用CNO和warehouse表中的第一行中的cno进行对比，如果相等，就用自已的数据加上匹配的数据组成结果的第一行。接着和表warehouse第二行数据的cno进行对比，一直比到表尾，有多少组成多少新行。 然后shopping表的第二行数据接着用自已的cno和warehouse表中的第一行数据中的cno进行对比，有相等的就组成新行，一直到表尾。以此类推。 还可以写成： Selectshopping.Sno,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表中有一行CNo为100005，则warehouse中没有任何一条与之相匹配的cno数据，那么shopping表中的这一行仍显示在结果中，但因为没有匹配的warehouse表的数据，这行结果中，warehouse表的字段就全为空。 （4）外部连接―右连接(right join) 不管是否匹配条件，右表中的记录总会在结果集中。 不管是否匹配条件，右表中的记录总会在结果中。如果右表中有元组（行）不与左表相匹配，就只显示出右表自已的数据，其他数据为空。 ? ? （5）外部连接―全连接(full join) 左右表的记录都会在结果集中，是左右外连接的集合。 ? 4、嵌套查询（子查询） 即一个查询的结果集供其他查询使用。 （1）In select shopping.sno,shopping.Sname from shopping where snumber in (select Snumber from shopping where Sprise>500) //查询库存数量为价格高于500任何一个商品的库存数的。In，即属行任何一个值就行。等价于下面的=any ? （2）Any select shopping.sno,shopping.Sname from shopping where snumber =any (select Snumber from shopping where Sprise>500) ? //查询库存数量为价格高于500任何一个商品的库存数的。Any即大于等于，小于等于，或等于查询结果中的任何一个值的。 （3）All 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?0或1，或空值。存ture或false，男或女这种。 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:大字符串。可存储2的31次方个字节的字符串。 ? 4、日期类型： ? Datetime: 1753年到9999年 Smalldatetime:1900到2079 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语句 1、BEGIN...END程序块语句 程序块语句用于将多条T-SQL语句封装起来构成一个程序块。SQLServer在处理时，将整个程序块视为一条T-SQL语句执行。 ? begin <T-SQL命令行或程序块> end 经常与while或if...else组合起来使用，可以相互嵌套。有点类似于c#中的大括号。 ? 2、case ?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 ? ? ? 3、If 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 ? ? 4、while?? //和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人士加入。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |