sqlserver学习--SQL语句

jxwjq

　　苏州IT之家群：46213669，技术交流，招聘就业，职业发展，欢迎苏州IT人士加入。
　　第2章      tsql
　　tsql语句的基本分类：
　　DML（Data Manipulation Language）：数据操制语句
　　它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言
　　DDL（Data Definition Language）：数据定义语句
　　DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
　　DCL（Data Control Language）：数据控制语句
　　是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL
　　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 ,not null,
　　Inumber int ,not null check (inumber>0and inumber= 0 and age 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)比较条件查询 （可用>,50 and Snumber50 or Snumber50 or Snumber50 or Snumber40     //查询数量大于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,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表中有一行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>　　print @user2
　　6.3语句
　　1、BEGIN...END程序块语句
　　程序块语句用于将多条T-SQL语句封装起来构成一个程序块。SQLServer在处理时，将整个程序块视为一条T-SQL语句执行。
　　begin
　　
　　end
　　经常与while或if...else组合起来使用，可以相互嵌套。有点类似于c#中的大括号。
　　2、case  end    与C#中不一样
　　（1）类似于c#中switch用法
　　Case

　　SELECT>　　CASE Sex
　　WHEN 0 THEN '男'  //不作控制用，只是作为显示为的意思，如性别为0，显示为男。
　　WHEN 1 THEN '女'
　　ELSE '不清楚'
　　END AS性别   //给别名。
　　FROM PERSON
　　（2）类似于c#中if else用法
　　Case
　　When    then  
　　When   then  
　　Else
　　End
　　例：

　　select>　　(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人士加入。