SQL查询的艺术学习笔记--连接符数值运算函数

use seldata
create table bookitem
(
bookname varchar(60) not null,
book_price decimal(5,2)  not null,
quantity int not null
)
insert into bookitem values('image processing',34.95,8)
insert into bookitem values('signal processing',51.75,6)
insert into bookitem values('signal and system',48.50,10)
insert into bookitem values('digital signal processing',45.00,12)
insert into bookitem values('the logic circuit',28.65,10)
insert into bookitem values('sql techiques',65.50,10)
insert into bookitem values('a first course in database system',
52.80,9)
select *   into seldata.dbo.bookitem from seldata2.dbo.bookitem 
--复制表
delete from bookitem where 1=1
insert into seldata2.dbo.bookitem select * from seldata.dbo.bookitem
--复制表内容
select a.bookname,b.book_price,b.quantity from seldata .dbo.bookitem as a,seldata2.dbo.bookitem as b
where  a.bookname=b.bookname
--按别名查询
use seldata
select top 3 * from teacher
select tname+'('+dname+')',sal,age from teacher order by age
--连接字符查询
alter table teacher alter column tname char(10)
--修改表字段长度
alter table teacher alter column dname char(20)
select tno+cno as q,sal,tname,dname,age from teacher order by q
--别名查询并排序
select tname+sal,dname,age from teacher
--字段类型不一致时会出现错误
--下面讲字段不同类型之间的转换 利用CAST在不同数据之间进行转换
select tname+CAST( sal as varchar(5)),age from teacher
--说明：CAST（sal as varchr(5)) 将SAL由整型转化为VARCHAR（5）
--字符长度为5
--在使用连接时，如果相联接的两列中有一列为null测整列字段值也为null
select * from teacher
select tno,cast(cno as varchar(3))+CAST(sal as varchar(4)) as ssal,
tname,dname from teacher
--注意：连接运算符+为字符类型，如`果是数值类型则变为数值运算符\\\
use seldata
--别名（alias)：
--1.给列取别名：select coloumn as coloumn_alias 
--2.给表取别名：select colounm from talble as table_alise
select tname+'('+dname+')' as info, age from teacher as a order by info
use seldata2
select * from  teacher 
select * into  seldata2.dbo.teacher from seldata.dbo.teacher
alter table teacher alter  column info char(30)


/*这条语句没有完成：今后有空需写完整：如何将一张表查询的记录写入到表内新加列中
select * from #lsb
update  teacher as a set a.info=#lsb.info where a.tno=#lsb.tno
select cast(tname as CHAR(8))+'('+cast(dname as CHAR(12))+')' as info,tno into #lsb  from teacher ) 
*/
use seldata
select tname+'('+dname+')' as info,age from teacher
 where  info like '%计算机%'
 --根据执行优先级 where info like '%计算机%' info 未生成，会出错。
 --数值运算符
select * from bookitem
select bookname,quantity,book_price ,quantity*book_price 
as total_price from bookitem order by total_price
--列与列运算后产生新列查询，可以按新列进行排序
select bookname,quantity,book_price*0.8 as off_price
from bookitem where book_price*0.8>30 order by off_price
--查询打折后价格高于30块的书并按打折价排序，
--注意：off_price不能出现在where语句后，仍然以book_price*0.8实现
--CAST使用之数据类型
--CAST语法：cast (value expression as data type)
--CAST可以将任何数值类型转为char或varcher
select * from course 
select cno,cname,ctime,scount,CAST(ctest as CHAR(11)) 
as chardate from course
--case语句的使用
select * from course
select cname,ctime,credit=
case
    when ctime>=40 then 5
    when ctime>=30 then 4
    when ctime>=20 then 3
    else 2
    end
from course order by credit
--函数：字数处理函数 算术运算函数  时间 日期处理函数
select a=CHAR(56)
select b=ASCII(8)
select a=upper('abcdefg')
select b=LOWER('ABCDEFDgh')
print ltrim(   123)
print rtrim(cast(123 as char(10)))
print left('abcdefg',5)
select * from course
select cname,ctime,scount,LEFT(cast(ctest as CHAR(11)),11) as yy from course
select cname,ctime,scount,right(cast(ctest as CHAR(11)),8) as yy from course
print right(123456789,4)
print substring(123456447,4,5)
select UPPER(bookname) as book,quantity,book_price from bookitem
--将书名转化为大写
select LOWER(bookname) as book,quantity,book_price from bookitem
--rtrim去除空格
select RTRIM(tname)+'('+RTRIM(dname)+')' as info,age 
from teacher order by dname
select * from teacher
select upper(LEFT(bookname,6)+'...') as shortname,quantity,book_price 
from bookitem order by bookname
--left用于查询语句
--left用于where语句
select bookname,quantity,book_price from bookitem
where right(bookname,10)='PROCESSING' ORDER BY bookname
--SQLCF对字符数据大小写并不完全区别，所以这里小写也可以等于大写
select bookname,quantity,book_price from bookitem
where UPPER(right(bookname,10)) like '%processing'
--字符串比较 charindex
print charindex('qq','badasdfasqq')
print charindex('123','1245444123')
select upper(bookname) as 书名大写,quantity as 数量,book_price as 单价,CHARINDEX('a',bookname)
as A出现的位置 from bookitem order by bookname
--substring用法
select SUBSTRING('123456789',1,8)
select SUBSTRING('asdefgisdh',3,7)
select UPPER(bookname) as 书名,quantity as 数量,SUBSTRING(cast(book_price as CHAR(5)),1,2) as 单价 from bookitem order by 单价
--从一个字符中指定位置取指定长度字符
--replace 取代指定字符
select * from bookitem
select bookname as 书名,book_price as 单价,quantity as 数量,replace(bookname,'processing','PRO') as 重命书名 
from bookitem order by bookname
select SIGN(-9)
select bookname,book_price,quantity,ROUND(book_price*quantity,0) as totalmoney from bookitem order by quantity
--使用算术运算符
select  SIN(23.45) as  sin正弦,
        atan(1.234) as atan正切,
        rand()      as 随机数,
        pi()        as 圆周率,
        sign(-100)  as 正负值
        
---重点和我自己觉得难点的日期函数
select day(getdate()) as 日期, month(getdate()) as 月份,year(getdate()) as 年份
select cname,scount,MONTH(ctest) as realmonth,DATENAME(month,ctest) as charmonth,
DATEPART(MONTH,ctest) intmonth from course order by scount desc
--注意：在当前会话环境为中文情况下：月份显示为数字，
--      在会话环境为英文时，月份显示为英文。
select * from course
select datename(month,getdate())
select DATEPART(day,getdate())
--查询返回的类型，日期表达式
select cname,scount,ctest from course where month(ctest)=7 order by ctest
select cname,scount,ctest from course where DAY(ctest)=10 order by cname
--另一种字符日期表达式查询
set language 'english'
select cname,scount,ctest from course where DATENAME(month,ctest)='July' order by ctest
select DATENAME(day,ctest) as day_day,cname,scount,ctest from course order by ctest
set language 'english'
--设置会话语言环境为英文
set language '简体中文'
--设置会话语言环境为中文
select convert(varchar(11),GETDATE())
--在不同环境下日期显示会有不同值出现
--dataadd用法
select cname,DATEADD(day,7,ctest) as newtestday from course order by newtestday
select DATEADD(MM,1,getdate())
--表达式dateadd(datepar,number,date)其中datepart为日期中的所有包括表示日期信息：
--年、月、日、小时、 分、 秒、至 纳秒 可以为英文简写
select DATEADD(MONTH,4,getdate())/select DATEADD(day,61,getdate())
select cname,scount,GETDATE() as currentdate,ctest,
DATEDIFF(day,GETDATE(),ctest) as remainday from course order by remainday
select DATEDIFF(DAY,ctest,getdate()) from course
set language '简体中文'
select cname,scount,CONVERT(varchar(11),ctest,106) as test_date from course order by ctest
--注意这里的106为类型6，变为106是+100让年显示为4位数
select cname,scount,CONVERT(varchar(11),ctest,20) as test_date from course order by ctest
--获取当前时间，精确至秒
select CONVERT(varchar,getdate(),8) as nowtime