--上课内容：第五单元 Transact-SQL语言(运算符、函数)
-- 5.2 运算符
-- 5.2.1 算术运算符: +加 -减 *乘 /除 %取模
-- 例：2个字符串相加
select '1234'+'5678' as a
-- 例：2个数字相减
select 12-8 as a
-- 例：2个数字相乘
select 12*0.8 as a
--对21与6取模运算(取余数)
select 22%6 as '21与6取模的结果'
-- 5.2.2 赋值运算符：=
--例：查询receive_detail表中goods_id字段中以'12'开头的商品信息
select detail_id,goods_id from receive_detail where substring(goods_id,1,2)='12'
-- 5.2.3 位运算符
-- 5.2.4 比较运算符:=,>,=, --例：查询exam表中所有成绩大于80分的记录
select * from exam where t_grade>80
-- 5.3 函数
-- 5.3.1 字符串函数
RIGHT(char_expr,inter_expr) --返回字符串从右边开始指定个数的字符
select right('sql server',6) as a --取右边6个字符
LEFT(char_expr,inter_expr) --返回字符串从左边开始指定个数的字符
select left('sql server',3) as a --取左边3个字符
LTRIM(char_expr) --删除左边空格
select ltrim(' sql server ') as a
RTRIM(char_expr) --删除右边空格
select rtrim(' sql server ') as a
STR(float_expr,[length,[,decimal]]) --数字数据转换为字符数据
select str(10.3456,7,4) as a --把10.3456这个数字转换为长度为7、小数位为4的字符
select str(10.3456,10,4) as a --如果位数多，前面补空格
select str(10.3456,10,2) as a --如果小数位少，则四舍五入
STUFF(char_expr1,start,length,char_expr2) --删除指定字符便插入另一组字符
select stuff('study vb',7,2,'sql server') as a --从第一个字符串第7个字符去掉2个字符插入第二个字符串
SUBSTRING(char_expr,start,length) --取指定长度的子串
select substring('sql server',5,6) as a --从字符串第5个字符取6个字符
UPPER(char_expr) --转化为大写字符
select upper('sql server') as a --把字符串转换为大写
LOWER(char_expr) --转化为小写字符
select lower('SQL Server') as a --把字符串转换为小写
LEN(char_expr) --返回字符串长度
select len('SQL Server') as a
SPACE(int_expr) --返回由重复的空格组成的字符串
-- 5.3.2 日期函数
GETDATE() --返回但前系统日期和时间
select getdate() as '系统日期' --显示系统日期年月日时分秒毫秒
DATEADD(datepart,number,date) --返回指定日期某部分加上一段时间的新时间
select dateadd(yy,3,getdate()) as '当前日期3年以后的日期'
select dateadd(mm,3,getdate()) as '当前日期3个月以后的日期'
select dateadd(dd,3,getdate()) as '当前日期3天以后的日期'
DATEDIFF(datepart,date1,date2) --返回2个日期某部分相差值
select datediff(yy,'20051231','20070101') as '2个日期相差几年'
select datediff(mm,'20051231','20070101') as '2个日期相差几个月'
select datediff(dd,'20051231','20070101') as '2个日期相差几天'
DATEPART(datepart,date) --返回指定日期的某部分
select datepart(yy,getdate()) as '当前年份'
select datepart(mm,getdate()) as '当前月份'
select datepart(dd,getdate()) as '当前天数'
select datepart(hh,getdate()) as '当前时'
select datepart(mi,getdate()) as '当前分'
select datepart(ss,getdate()) as '当前秒'
select datepart(yy,getdate()) as '当前年份',datepart(mm,getdate()) as '当前月份',datepart(dd,getdate()) as '当前天数',datepart(hh,getdate()) as '当前时',datepart(mi,getdate()) as '当前分'
DATENAME(datepart,date) --返回指定日期的字符串
select datename(yy,getdate()) as '年'
select datename(mm,getdate()) as '月'
select datename(dd,getdate()) as '日'
DAY(date) --返回指定日期日的整数
select day(getdate()) as '日'
MONTH(date) --返回指定日期月的整数
select month(getdate()) as '月'
YEAR(date) --返回指定日期年的整数
select year(getdate()) as '年'
GETDATE() --返回当前日期
select getdate() as '当前日期'
-- 5.3.3 数学函数
ABS（num_expr）： --取绝对值
select abs(-34) as a
SQRT（num_expr）： --取平方根
select sqrt(16)
CEILING（num_expr）： --大于等于给定数字的最小整数
select CEILING(15.2564) as a
FLOOR（num_expr）： --小于等于给定数字的最大整数
select FLOOR(15.2564) as a
ROUND（num_expr，n）： --四舍五入为精度为n的数
select ROUND(15.2564,2) as a
RAND（）： --返回0-1中的随机数
select rand() as a
-- 5.3.4 转换函数
CAST(表达式 AS 数据类型) --结果将字符串转换成数字
select CAST('10.54' as decimal(10,2)) as a
CONVERT(数据类型，表达式[,style]) --结果将字符串转换成数字
select CONVERT(decimal(5,2),'10.54') as a
--[,style] 在转换的是 datetime,smalldatetime,money,smallmoney,real,float时使用
select convert(datetime,getdate(),112)
-- 5.3.5 其它函数
--练习:
-- 1. 显示'sql'和'server' 2个字符串的和
-- 2. 显示'sql'和'server' 2个字符串，并且2个字符串中间插入2个空格
-- 3. 显示10000减101的结果
-- 4. 显示10000乘101的结果
-- 5. 显示10000除101的结果
-- 6. 显示10000与101做取模的结果
-- 7. 创建一个工资表 salary(emp_id char(4),yingfa decimal(8,2),koufa decimal(8,2))
--插入5条数据
insert into salary values('1001',1000,100)
insert into salary values('1002',2000,200)
insert into salary values('1003',10000,1001)
insert into salary values('1004',5000,500)
insert into salary values('1005',1800,180)
--做8-14题
-- 8. 显示每位员工的应发工资、扣发工资、实发工资
-- 9. 显示给每位员工的应发工资涨10%，重新计算实发工资
-- 10. 给1001员工的应发工资涨10%,显示这位员工的应发工资、扣发工资、实发工资
-- 11. 给1001员工的应发工资涨10%,显示每位员工的应发工资、扣发工资、实发工资
-- 12. 给1001员工的扣发工资多扣100元,显示这位员工的应发工资、扣发工资、实发工资
-- 13. 查看所有应发工资小于3000的员工的数量,显示应发工资小于3000的员工的应发工资、扣发工资、实发工资
-- 14. 给所有应发工资小于2000的员工的应发工资涨20%,显示每位员工的应发工资、扣发工资、实发工资
-- 15. 只显示字符串'How are you'中的'you'
-- 16. 只显示字符串'How are you'中的'How'
-- 17. 只显示字符串'How are you'中的'are'
-- 18. 显示字符串'How are you'的长度
-- 19. 显示字符串'How are you'为全大写
-- 20. 显示字符串'How are you'为全小写
-- 21. 显示大于等于16.6547的最小整数
-- 22. 显示小于等于16.6547的最大整数
-- 23. 显示16.6547四舍五入到2位小数
-- 24. 显示系统当前日期
-- 25. 显示系统当前日期的年份
-- 26. 显示系统当前日期的年份
-- 27. 显示系统当前日期的日
-- 28. 显示明天这时的日期
-- 29. 显示明年这时的日期
-- 30. 显示下个月这时的日期
-- 31. 显示上个月这时的日期
-- 32. 显示20000101与20071231相差的年份
-- 33. 用CAST把字符串'15.567'转换成2位小数的数字
-- 34. 用CONVERT把字符串'15.567'转换成2位小数的数字
-- 35. 查询pubs数据库中的authors表中au_lname为5个字母的作家的信息。
-- 36. 查询pubs数据库中的authors表中，使第一个字母大写其它子母小写
-- 37. 查询pubs数据库中的titles表中，在1994年6月以后出版的书
-- 38. 查询pubs数据库中的employees表中，每位员工的工作年限
--练习参考答案：
-- 1. 显示'sql'和'server' 2个字符串的和
select 'sql'+'server' as a
-- 2. 显示'sql'和'server' 2个字符串，并且2个字符串中间插入2个空格
select 'sql'+' '+'server' as a
--或
select 'sql'+space(2)+'server' as a
-- 3. 显示10000减101的结果
select 10000-101 as a
-- 4. 显示10000乘101的结果
select 10000*101 as a
-- 5. 显示10000除101的结果
select 10000/101 as a
-- 6. 显示10000与101做取模的结果
select 10000%101 as a
-- 7. 创建一个工资表 salary(emp_id char(4),yingfa decimal(8,2),koufa decimal(8,2))
--插入5条数据
insert into salary values('1001',1000,100)
insert into salary values('1002',2000,200)
insert into salary values('1003',10000,1001)
insert into salary values('1004',5000,500)
insert into salary values('1005',1800,180)
--做8-14题
-- 8. 显示每位员工的应发工资、扣发工资、实发工资
select yingfa as '应发工资',koufa as '扣发工资',yingfa-koufa as '实发工资' from salary
-- 9. 显示给每位员工的应发工资涨10%，重新计算实发工资
select yingfa*1.1 as '应发工资',koufa as '扣发工资',yingfa-koufa as '实发工资' from salary
-- 10. 给1001员工的应发工资涨10%,显示这位员工的应发工资、扣发工资、实发工资
update salary set yingfa=yingfa*1.1 where emp_id='1001'
select yingfa as '应发工资',koufa as '扣发工资',yingfa-koufa as '实发工资' from salary where emp_id='1001'
-- 11. 给1001员工的应发工资涨10%,显示每位员工的应发工资、扣发工资、实发工资
update salary set yingfa=yingfa*1.1 where emp_id='1001'
select yingfa as '应发工资',koufa as '扣发工资',yingfa-koufa as '实发工资' from salary
-- 12. 给1001员工的扣发工资多扣100元,显示这位员工的应发工资、扣发工资、实发工资
update salary set koufa=koufa+100 where emp_id='1001'
select yingfa as '应发工资',koufa as '扣发工资',yingfa-koufa as '实发工资' from salary where emp_id='1001'
-- 13. 查看所有应发工资小于3000的员工的数量,显示应发工资小于3000的员工的应发工资、扣发工资、实发工资
select count(*) from salary where yingfa select yingfa as '应发工资',koufa as '扣发工资',yingfa-koufa as '实发工资' from salary where yingfa
-- 14. 给所有应发工资小于2000的员工的应发工资涨20%,显示每位员工的应发工资、扣发工资、实发工资
update salary set yingfa=yingfa*1.2 where yingfa select yingfa as '应发工资',koufa as '扣发工资',yingfa-koufa as '实发工资' from salary
-- 15. 只显示字符串'How are you'中的'you'
select right('How are you',3) as a
-- 16. 只显示字符串'How are you'中的'How'
select left('How are you',3) as a
-- 17. 只显示字符串'How are you'中的'are'
select substring('How are you',5,3) as a
-- 18. 显示字符串'How are you'的长度
select len('How are you') as a
-- 19. 显示字符串'How are you'为全大写
select upper('How are you') as a
-- 20. 显示字符串'How are you'为全小写
select lower('How are you') as a
-- 21. 显示大于等于16.6547的最小整数
select CEILING(16.6547) as a
-- 22. 显示小于等于16.6547的最大整数
select FLOOR(16.6547) as a
-- 23. 显示16.6547四舍五入到2位小数
select ROUND(16.6547,2) as a
-- 24. 显示系统当前日期
select getdate() as a
-- 25. 显示系统当前日期的年份
select year(getdate()) as a
-- 26. 显示系统当前日期的年份
select month(getdate()) as a
-- 27. 显示系统当前日期的日
select day(getdate()) as a
-- 28. 显示明天这时的日期
select dateadd(dd,1,getdate()) as a
-- 29. 显示明年这时的日期
select dateadd(yy,1,getdate()) as a
-- 30. 显示下个月这时的日期
select dateadd(mm,1,getdate()) as a
-- 31. 显示上个月这时的日期
select dateadd(mm,-1,getdate()) as a
-- 32. 显示20000101与20071231相差的年份
select datediff(yy,'20000101','20071231') as a
-- 33. 用CAST把字符串'15.567'转换成2位小数的数字
select CAST('15.567' as decimal(10,2)) as a
-- 34. 用CONVERT把字符串'15.567'转换成2位小数的数字
select CONVERT(decimal(10,2),'15.567') as a
-- 35. 查询pubs数据库中的authors表中au_lname为5个字母的作家的信息。
select au_lname from authors where len(au_lname)=5
-- 36. 查询pubs数据库中的authors表中，使第一个字母大写其它子母小写
select upper(substring(au_lname,1,1))+lower(rtrim(substring(au_lname,2,20))) from authors
-- 37. 查询pubs数据库中的titles表中，在1994年6月以后出版的书
select title_id,title,pubdate from titles where year(pubdate)>'1994'and month(pubdate)>'6'
-- 38. 查询pubs数据库中的employees表中，每位员工的工作年限
select emp_id,fname+' '+lname,hire_date,datediff(yy,hire_date,getdate()) as '工作年限' from employee
