SQL学习之连接符，数值运算，函数

create table bookitem

(

bookname varchar(80) 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('Singal And System',48.5,10)

insert into bookitem values('Digital Signal Processing',45.0,12)

insert into bookitem values('The Logic Circuit',28.65,10)

insert into bookitem values('SQL Techniques',65.50,10)

insert into bookitem values('A Firest Course in Database Systems',52.80,9)

 

 

-------Teacher

create table teacher

(

TNO int not null,

TNAME char(10) not null,

CNO int not null,

SAL int,

DNAME char(10) not null,

TSEX char(2) not null,

AGE int not null

)

 

insert into teacher values(1,'王军',4,800,'数学','男',32)

insert into teacher values(2,'李彤',5,1200,'生物','女',54)

insert into teacher values(3,'王永军',1,900,'计算机','男',40)

insert into teacher values(4,'刘小静',2,1200,'计算机','女',39)

insert into teacher values(5,'高伟',8,2100,'电子工程','男',39)

insert into teacher values(6,'李伟',7,1200,'机械工程','男',29)

insert into teacher values(3,'王永军',1,900,'计算机','男',40)

 

 

---Course

create table course

(

CNO int not null,

CNAME char(30) not null,

CTIME int not null,

SCOUNT int not null,

CTEST smalldatetime not null

)

 

insert into course values(4,'应用数学基础',48,120,2006-7-10)

insert into course values(5,'生物工程概论',32,80,2006-7-8)

insert into course values(1,'计算机软件基础',32,70,2006-7-8)

insert into course values(2,'计算机硬件基础',28,90,2006-7-10)

insert into course values(8,'模拟电路设计',48,120,2006-7-10)

insert into course values(3,'生物化学',32,40,2006-7-2)

insert into course values(9,'数据库设计',16,80,2006-7-1)

insert into course values(6,'设计理论',28,45,2006-6-30)

 

 

create table student

(

SNO char(4) not null,

sname char(10) not null,

dname char(10) not null,

sex char(2),

CNO int,

mark decimal(3,1),

type char(4)

)

insert into student values(9701,'刘建国','管理工程','男',4,82.5,'必修')

insert into student values(9701,'刘建国','管理工程','男',10,70,'必修')

insert into student values(9701,'刘建国','管理工程','男',1,78.5,'必修')

 

 

---第七章：连接符，数值运算，函数

 

-----------————————

---使用"+" 连接符

--------------------

----查Teacher要求：姓名，所在系合为一列，系名放在括号里。如：王明（机械工程系）

select tname + '( ' + dname + ')',age from teacher order by age

select  dname+ sal from teacher --不同类型连接报错

select dname+'('+cast(sal as varchar(5)) +')' from teache---通过cast 解决不同类型连接报错的问题

 

--使用别名操作连接后的数据

select tname + '( ' + dname + ')' as info ,age  from teacher order by info

 

--一个错误的查询：

select tname + '( ' + dname + ')' as info ,age  from teacher where info like '%计算机%'

--报错：Invalid column name 'info'.

--原因：跟SQL语句的执行顺序有关系，SQL语句的执行顺序from->where->group->having ->select->order, where 在select之前执行，所以不认识info,

--而order by在select之后，所以order by info没错。

 

 

----------------

---使用case表达式

-----------------

--任务：查询课程表，要求：根据课时算学分，高于的个学分，~40之间的个学分，~30之间的个学分，少于的两个学分

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

 

 

-------------------

---使用字符处理函数

-------------------

--字符转换(UPPER,LOWER)

select UPPER(bookname) as BOOK,quantity,book_price from bookitem

select Lower(bookname) from bookitem

---去空格(LTRIM,RTRIM)

select RTRIM(tname) + '( ' + RTRIM(dname) + ')',age from teacher order by age

 

---取字符串（LEFT，RIGHT，SUBSTRING)

select LEFT(bookname,6)+'...' as shortname,quantity,book_price from bookitem

 

--charindex(字符第一次出现的位置)

select bookname,quantity,book_price,charindex('i',bookname) as FirstIPosition from bookitem

 

--Replace

select bookname,replace(bookname,'Processing','Pro.') from bookitem

 

 

-------------------

--使用日期时间函数

-------------------

--任务：查询Course表中Cname,SCOUNT,月份，要求考试学分分别采用时间月份，字符型月份，整形月份种形式

--datename(<datepart>,<data>)以字符串形式返回日期指定部分

--datepart(<datepart>,<data>)以整形返回日期部分

select * from course

select CNAME,SCOUNT,MONTH(CTEST) as realmonth,DATENAME(month,CTEST) as charmonth,

datepart(month,ctest) as intmonth from course

 

--任务：查考试日期距当前日期还有多长时间(datediff)

select datediff(day,getdate(),ctest) as remainday from course

 

 

-------------

--第十章：子查询

-------------

--任务：查找平均年龄高于平均年龄的教工信息

select * from teacher

--错误的做法：

select avg(age) as average from teacher where average>40

--正确的做法:

select tno,tname,age from teacher

where age >(select avg(age) from teacher)

 

--任务：查找教师的工号，姓名，所在系，课程号，年龄，要求教师所在系的平均年龄大于所有老师的平年龄

--比较两边都采用聚合分析的字查询

select tno,tname,dname,age from teacher as t

 where (select avg(age) from teacher where dname=t.dname)>(select avg(age) from teacher)

 

--任务：查找教师的工号，姓名，所在系，课程号，以及在student表中修了这门课的学生的人数

select tno,tname,dname,cno, (select count(*) from student where CNO=Teacher.CNO) as S_NUM from teacher