SQL Server表与列别名,计算列等等的说明和例子.doc

语句所用表说明第一章：1.select 语句中使用列别名 在select 语句中使用列别名的例子中利用的表是学生表(studenttable)，学生表中有字段studentid（学生学号） 从1开始自动增长1，主键，不可以为空。studentname（学生姓名）不可以为空。studentsex（学生性别）唯一约束，只能为男或者女，不可以为空。studentage（学生年龄）唯一约束，年龄范围在10-35之间，不可以为空。在select语句中使用列别名有四种使用方法：（1）用as关键字例：select studentid as 学号 from studenttale where studentname = 值（2）用双引号例：select studentid “学号” from studenttable where studentname = 值（3）用单引号例：select studentid 学号 from studenttable where studentname =值（4）不带引号例：select studentid 学号 from studenttable where studentname =值2.select 语句中使用计算列在select语句中使用计算列利用的表是商品表（commoditytable）,商品表中有字段Commodityid（商品编号）从1开始自动增长1，主键，不可以为空。Commodityname（商品名称）不可为空。Commoditystock（商品进货数量）不可为空。Commoditysell（商品售出数量）不可为空。Commodityunit（商品进价）不可为空。Commoditybid（商品单价）不可为空。（1）计算库存数量例：select （commoditystock-commoditysell）as 库存数量 from commoditytable（2）计算已卖商品利润例：select （commoditybid-commodityunit）*commoditysell 已卖商品获得利润 from commdoitytable（3）计算库存利润例：select （commoditystock-commoditysell）* （commoditybid-commodityunit） as 库存利润From commdoitytable3.使用表别名查询数据 表别名和列别名几乎相同，在此例中我们利用学生表（studenttable）进行说明（1）利用as关键字例：select student.* from studenttable as student（2）利用双引号例：select student.*from studenttable “student”（3）不带引号例：select student.* from studenttable student三种方法中均可以给where条件例：select student.* from studenttable student where student.Studentid = 值注意：列别名可以用单引号（），而表别名不可以用单引号。4在where子句使用计算列 Where子句中使用计算列和在select语句中使用计算列几乎相同，所以在此例中我们利用商品表（commoditytable）进行说明。（1）计算库存数量为5的商品信息例：select commodity.* from commoditytable as commodity where (moditysell) = 5（2）计算已卖商品利润大于35的商品信息例：select commodityid as 编号,commodityname 商品名称,commoditystock 进货总量,commoditysell 已售数量,commodityunit 进价,commoditybid 单价from commoditytable where (commoditybid-commodityunit) 35（3）计算库存利润小于200的商品信息例：select commodity.* from commoditytable as commodity Where (moditystock moditysell)*(moditybid-commodity.unit) 200注意：select语句中使用计算列是可以直接给出计算结果的，而在where子句中只能做对比的，5利用beginend批处理执行多条sql语句Beginend是可以一次性处理多条sql语句的批处理，我们这里利用学生表(studenttable)举例说明（1）输出年龄小于23的学生的姓名（studentname），性别（studentsex）例：declare studentid int,studentname varchar(15),studentsex char(5) ,studentage int Select studentname=student.studentname,studentsex=student.studentsex ,studentage=student.studentage from studenttable student Where studentage 23If studentage = 21BeginPrint 姓名：+studentnamePrint性别：+studentsexEndElsePrint 不存在的记录在if语句中如果没有beginend语句的话执行的时候会报关键字else附近有语法错误，还有就是在本例中为什么我没有输出学号和年龄，因为这两字段都为int类型，如果我按print 学号：+studentid这样输出的话他会说“在将varchar 值学号： 转换成数据类型int 时失败”如果直接print studentid的话则可以6利用top关键字查询指定行数据利用top关键字可以查询指定的行数据，这里我们利用学生表(studenttable)具体说明语法:select top n percent select_list from tablenamen：为整数percent：为可选参数，代表返回所有记录的百分之n(1)查询学生表(studenttable)中的前两条记录例：select top 2 * from studenttable(2)查询学生表(studenttable)中的%5的数据例：select top 5 percent * from studenttable7去除结果集中的重复行 利用distinct关键字可以去除结果集中的重复行，这里我们利用订单表（ordertable）举例说明。语法：select distinct|all select_list from tablenameAll是语句的默认行为，如果不写distinct和all的话默认为all。例：(1)查询表中所有记录去掉order_id重复的记录Select distinct order_id,user_name,num_tot,ord_status,ord_date from ordertable(2)查询表中记录用order_id去除重复记录的总数Select count(*) from (select distinct order_id from ordertable) as ot8.计算数据在结果集中的行号如果一个表中没有主键也没有自动编号，那么要统计数据的行号就是很难的， 这里我们使用订单表（ordertable）表举例说明 利用ROW_NUMBER()函数可以获得结果集中的行号。语法：ROW_NUMBER() OVER(排序字段)例：(1)查询出ordertable表中的所有字段并且按ord_date升序排列Select row_number over(order by ord_date asc) as 行号, ord_id,user_name,num_tot,ord_status,ord_date from ordertable注意：使用row_number()函数必须要有order by子句，否则没有办法计算行号9随机查询n行记录这里我们利用学生表（studenttable）举例说明，利用newid（）函数可以随机查询n行记录和随机排序(1)随机排序例：select * from studenttable order by newid（）(2)随机查询n行记录例：select top n from studenttable order by newid()注意：neweid()函数必须和order by子句一起使用，如果没有order by子句则执行错误10格式化结果集数据本节我们利用convert（）函数，cast（）函数substring（）函数实现格式化结果集数据A. convert()转化函数语法：convert（data_typelength,expression style）(1) 把字符串转换为日期Declare tem varchar(10)Set tem = 20100806Select convert(datetime,tem)(2) 把日期转化为字符串Declare tem datetimeSet tem =2010-08-06Select convert(varchar,tem)在表中，左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100，可获得包括世纪数位的四位年份 (yyyy)。 不带世纪数位 (yy) 带世纪数位 (yyyy) 标准 输入/输出* - 0 或 100 (*) 默认值 mon dd yyyy hh:miAM（或 PM） 1 101 美国 mm/dd/yyyy 2 102 ANSI yy.mm.dd 3 103 英国/法国 dd/mm/yy 4 104 德国 dd.mm.yy 5 105 意大利 dd-mm-yy 6 106 - dd mon yy 7 107 - mon dd, yy 8 108 - hh:mm:ss - 9 或 109 (*) 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM） 10 110 美国 mm-dd-yy 11 111 日本 yy/mm/dd 12 112 ISO yymmdd - 13 或 113 (*) 欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h) 14 114 - hh:mi:ss:mmm(24h) - 20 或 120 (*) ODBC 规范 yyyy-mm-dd hh:mm:ss.fff - 21 或 121 (*) ODBC 规范（带毫秒） yyyy-mm-dd hh:mm:ss.fff - 126(*) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm（不含空格） - 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM - 131* 科威特 dd/mm/yy hh:mi:ss:mmmAMB. cast()转换函数 语法:cast(expression as data_type)(1) 把时间类型转换为字符串例：declare tmp datetimeSet tmp = 2010-08-06Select cast(tmp as varchar)(2) 把字符串类型转换为时间类型 例： declare tmp varchar(16) Set tmp = 20100806 Select cast(tmp as datetime)Csubstring()字符串函数语法:substring(string,start,length)(1) 剪取字符串的前六个字符例：Declare tmp varcharSet tmp = woaiguopanSelect substring(tmp,1,6)11.利用查询结果集生成表或者临时表语法:se