sql server复习 Select 语句。

 

1，create数据库 Test

2，create数据表  MyStudents（包含的字段有 FId，FName，FAge，FGender，FMath，FEnglish）

3，向数据表MyStudent中插入40条数据（以我们班学生为列）
create table  MyStudents
(
FId int identity(1,1)primary key,
FName nvarchar not null,
FAge int,
FGender nchar(2),
FMath float,
FEnglish float,
)

insert into MyStudents(Fname,FAge,FGender,FMath,FEnglish)
select '刘晓飞',21,'女',56.5,89 union
select '杨巧巧',21,'女',89,98 union
select '段琳琳',21,'女',54,83 union
select '张少丹',22,'女',89,38 union
select '耿宇丹',21,'女',96,89 union
select '王静静',21,'女',69,83 union
select '齐一韩',20,'女',73.5,34 union
select '张兰',21,'女',93,565  union
select '苏少琪',21,'女',57.5,48 union
select '吴志远',21,'男',86,98 union
select '尤越',21,'男',92.5,83 union
select '李硕',21,'男',83,78 union
select '姚志超',21,'男',82,84 union
select '王磊',21,'男',69.5,83 union
select '王江朴',20,'男',82,94 union
select '祁宏霞',22,'女',99,34 union
select '刘祎',21,'女',89,90 union
select '张曦',20,'女',100,85 union
select '齐志超',20,'男',80,85 union
select '邢晓康',20,'男',78.5,80 union
select '张亚飞',20,'男',80,88 union
select '张东',20,'男',99,100 union
select '康凯',20,'男',85.5,85 union
select '商柄琪',21,'男',93,90 union
select '郑寒松',20,'男',84,85 union
select '肖伟哲',20,'男',90.5,85 union
select '王锐',20,'男',83,85.5 union
select '李琛',20,'男',88,100 union
select '刘洪洋',20,'男',70,70 union
select '付志伟',20,'男',99,75 union
select '李昂',20,'男',80.5,85 union
select '安鑫',20,'男',58,85 union
select '胡冰冰',20,'男',70,85 union
select '丁森',20,'男',80,87 union
select '王琪敏',20,'男',89,75 union
select '滕翔',20,'男',87.5,80 union
select '牛光远',20,'男',87,85 union
select '巩腾辉',20,'男',94.5,85 union
select '李宁',20,'男',63,70.5 union
select '杨世天',20,'男',80,85

 
 

--获取MyStudents中年龄最大的前5个，和前20%
select top 5 * from Mystudents order by FAge desc
select top 20 percent * from Mystudents order by FAge desc
--获取当前时间
select GETDATE() as 当前时间
select convert(char(8),getdate(),108)
--去掉重复distinct
select distinct * from Mystudents
--查询英语成绩最大，最小，总分数和平均值
select max(FEnglish) as 英语成绩最大,MIN (FEnglish) as 英语成绩最小,SUM(FEnglish) as 总分数,AVG (FEnglish ) as 平均值 from Mystudents
--查询班级总人数
select COUNT(*) as 总人数 from Mystudents
---6 查询数学成绩为null的人数（在数据库中进行设置n各人的数学成绩为null看效果）
select Count(FMath) as 数学成绩 from Mystudents where FMath is null

 

 

1手动添加[主键约束]PK_Employees_EmpId

alter table Employee add constraint PK_Employee_EmpId primary key(EmpId)
2手动为EmpName增加非空约束

alter table Employee alter column EmpName varchar(50) not null;

3手动为EmpName增加唯一键约束

alter table Employee add constraint UQ_Employee_EmpName unique(EmpName)
4删除唯一键约束

alter table Employees drop constraint UQ_Employees_EmpName

5为性别增加默认约束,使默认值为"男"

alter table Employees add constraint DF_Employees_EmpGender
default('男') for EmpGender

6为年龄增加检测约束  0-120含0和120

alter table Employees add constraint CK_Employees_EmpAge
check(EmpAge>=0 and EmpAge<=120)

7.为性别增加检查约束 非 男 即 女

alter table Employees add constraint CK_Employees_EmpGender
check(EmpGender='男' or EmpGender='女')

8--为员工表增加外键约束----首先，设置部门表中的DepId为主键,并且外键不能为空

alter table Department add constraint Pk_Deparment_DepId primary key(DepId)

alter table Employees add constraint FK_Employees_EmpDepId
foreign key(EmpDepId) references Department(DepId) on delete cascade

9一条语句删除多个约束

alter table Employees drop constraint FK_Employees_EmpDepId,
CK_Employees_EmpAge,
UQ_Employees_EmpName
10一条语句为表增加多个约束

alter table Employees add constraint
UQ_Employees_EmpName
unique(EmpName),
constraint
CK_Employee_EmpAge
check(EmpAge>=0and EmpAge<=150)

 

--男学生出生日期最大和最小值
select MAX(FDate) as 出生日期最大,MIN (FDate) as 出生日期最小 from Mystudents where FGender='男'

--tSex  控制只能是男 女，默认男
alter table Teacher add constraint CK_Teacher_tSex check(tSex='男' or tSex='女' ),constraint DF_Teacher_tSex default('男')for tSex;
alter table Teacher  add constraint CK_Teacher_tAge check(tAge>=20 and tAge<40 ),constraint DF_Teacher_tAge default(30)for tAge;
--Score表中
--studentId 是外键    先要把Student表中的sId设置为主键
alter table Student add constraint Pk_Student_sId primary key(sId)
alter table Score add constraint FK_Score_studentId
foreign key(studentId) references Student(sId) on delete cascade

 

1. -- 查询 MyStudents 数学成绩没有及格的学生
select FId as 学号,FMath as 数学成绩 from MyStudents
where FMath <60

2. -- 查询年龄大于 20 的男学生
select * from MyStudents where FGender='男' and FAge>20

3. -- 查询年龄大于 27and 小于 33 的男学生
--方法一
select *from MyStudents
 where  FAge>=27 and Fage<=30 and FGender='男'
---方法二
---between 的用法
 select *from MyStudents
 where  FAge between 27 and 30 and FGender='男'

4. -- 查询部门 1,4,5 中的员工
--方法一
    select EmpName as 姓名, EmpDpmId AS 部门 from Employee
    where EmpDpmId=1 or EmpDpmId=4 or EmpDpmId=5
--  方法二 
--in 的用法
    select  EmpName as 姓名, EmpDpmId AS 部门 from Employee
    where EmpDpmId in(1,4,5)

- 模糊查询 ---( 针对字符串 )

5. -- 查询 mystudents 中所有姓张的同学 ,% 代表 0 个或多个任意字符
 select * from MyStudents
  where FName like'张%'

6. -- 查询所有数学成绩为 null 的同学 , 并且将 null 替换为 ' 缺考 '
select FName,FAge,数学成绩=ISNULL( cast(FMath as varchar(50)),'缺考')from MyStudents
where FMath is null 

7. -- 按英语成绩排序
 select * from MyStudents
 order by FEnglish desc

8. -- 查询学生表中的信息，显示 姓名，性别，英语成绩，数学成绩和 平均分 并按照平均分 降序排列

    select 
    姓名=Fname,
    FGender as 性别,
    FEnglish as 英语成绩,
    数学成绩=FMath,
    平均分=(FMath+FEnglish)/2
    from MyStudents
    order by (FMath+FEnglish)/2 desc
   
9. -- 统计学生表 mystudent 中男同学和女同学的人数
        select
 性别=FGender,
 COUNT (*)as 人数
 from MyStudents
 group by FGender

10. -- 统计员工表中每个部门中男性员工的个数超过 3 人的人数
   select
   部门号=EmpDpmId,
   部门男性人数=COUNT (*)
   from Employee
   where EmpGender ='男'
   group by EmpDpmId
   having count(*)>3

11. 查询以下数据表 MyOrder ：

（1） 统计一个热销商品排名表，即按照每种商品的销售数量统计。
  select
  商品名称,
  总销售数量=SUM (销售数量 )
  from MyOrders
  group by 商品名称
  order by 总销售数量 desc

（2） 统计销售总价格超过 3000 元的商品名称和销售总价，并按照销售总价降序排列。
  select
  商品名称,
  销售总价=SUM (销售价格*销售数量)
  from MyOrders
  group by 商品名称
  Having SUM (销售价格*销售数量)>3000
  order by  销售总价 desc

 （3） 统计各个客户对“可口可乐”的喜爱程度（即统计每个购买人对“可口可乐”的购买数量）
select
  购买人,
  购买数量=COUNT (*),
  购买名称='可口可乐'
  from MyOrders
  group by 购买人
  order by 购买数量 desc
 
--类型转换
1 把日期转换成 月日年 的字符串的格式输出
select convert (varchar(50),getdate())
2 查询出每个销售员的销售总金额，以及总的销售金额（底

部汇总）

select
销售员,
销售金额=SUM (销售数量*销售价格)
from MyOrders
group by 销售员 
union all
select
'总的销售金额',
SUM (销售数量* 销售价格)
from MyOrders

3 要求在一个表格中查询出学生的英语最高成绩、最低成绩

、平均成绩

（1）查询结果为1行

select
英语成绩最高=MAX(FEnglish),
英语成绩最低=MIN(FEnglish),--列名
平均成绩=AVG(FEnglish)
from Mystudents

（2）查询结果为3行

select'最高英语成绩', MAX(FEnglish)from Mystudents  union all
select '最低英语成绩',MIN(FEnglish)from Mystudents union all
select '平均英语成绩', AVG(FEnglish)from Mystudents

4 复制表(只要表结构,不要数据)
  

 （1）方法1

select *
into New2Teacher
from TblTeacher
where 1<>1

（2）方法2
use TestSchool
select top 0 *
into New3Teacher
from TblTeacher

5 把一个表的数据copy到另一个 已经用上面的方式生成的

表中。
set identity_insert New3Teacher on
insert into New3Teacher values('玉良',1,22,2000,'2010-10-10')
select* from TblTeacher
set identity_insert New3Teacher off

6 创建一张表，记录电话呼叫员的工作流水，记录呼叫员编

号、对方号码、通话开始时间、通话结束时间。建表、插数

据等最后都自己写SQL语句。
   要求：
  CREATE TABLE CallRecords
(
  Id int NOT NULL identity(1,1) primary key,
     CallerNumber nvarchar(50),
  TelNum varchar(50),
  StartDateTime datetime not null,
  EndDateTime datetime not null
)
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
insert  into CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) values ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));

--（1）输出所有数据中通话时间最长的5条记录。orderby datediff
select
top 5 *
from CallRecords
order by datediff(second,StartDateTime,EndDateTime) desc
--（2）输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
select
sum(datediff(second,StartDateTime,EndDateTime))
from CallRecords
where TelNum like '0%'
--（3）输出本月通话总时长最多的前三个呼叫员的编号。
select
top 3 CallerNumber,
sum(datediff(ss,StartDateTime,EndDateTime))
from CallRecords
where datediff(month,StartDateTime,'2010-07-1') = 0
group by CallerNumber
order by sum(datediff(ss,StartDateTime,EndDateTime)) desc
----------------方法二

select top 3 CallerNumber ,SUM (DATEDIFF (second,StartDateTime,EndDateTime )) from CallRecords
where DATEPART (MONTH ,StartDateTime)=DATEPART (MONTH ,GETDATE() )
group by callerNumber
order by SUM (DATEDIFF (second,StartDateTime,EndDateTime )) desc;

--(4)输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)按照月份分组。

select
top 3 CallerNumber,
count(*) 
from CallRecords
where datediff(month,StartDateTime ,'2010-07-1') = 0
group by CallerNumber
order by count(*) desc
---------------方法二
select
top 3 CallerNumber,
count(*) from
CallRecords
where DATEPART(month,StartDateTime)=DATEPART(month,getdate())
group by CallerNumber
order by count(*) desc

 

--1计算100天后病毒发作的日期。
  print convert(varchar(50),dateadd(day,100,getdate()),111)
--2凡是入职一年以上的员工，工资增加500￥
select *from Employee 
update Employee set EmpSalary=EmpSalary+1000
where DATEADD(year,1,EmpInTime)<GETDATE()
--3计算1975年10月5日到现在现在相差多少年/月/日/小时?
print datediff(year,'1975-10-5',getdate());
print datediff(month,'1975-10-5',getdate());
print datediff(day,'1975-10-5',getdate());
print datediff(hour,'1975-10-5',getdate())
--4统计2008年入职的员工
select * from CallRecords
where year(EmpInTime)='2008'
--5输出所有数据中通话时间最长的5条记录。
select top 5 *
from CallRecords
order by DATEDIFF (SECOND ,StartDateTime,EndDateTime )desc;
--6将结果集加入一列“通话时长（秒）”
select top 5 *,
通话时长=DATEDIFF (SECOND ,StartDateTime,EndDateTime )
from CallRecords
order by DATEDIFF (SECOND ,StartDateTime,EndDateTime ) desc,CallerNumber  desc;

--三、Case函数用法
--1.1
--use School
-- SELECT * FROM Score
-- SELECT * FROM Student
----90分以上优秀
----80分以上良好
----70分以上中等
----60分以上及格
----60分以下不及格

use Schools
select tSId ,tEnglish,
评级=
 case
 when tEnglish >=90 then '优秀'
 when tEnglish>=80 then '良好'
 when tenglish>=70 then '中等'
 when tenglish>=60 then '合格'
 else
 '不及格'
 end
 from TblScore
 --2、要求，查询结果集中有A B C三列,用SQL语句实现：当A列大于B列时，在C中显示A列的值否则显示B列中的值。
 create table TestCase
   (
   A int,
   B int
   )
   select * from TestCase
   insert into TestCase values(10,20)
   insert into TestCase values(100,32)
  
   select  A,B,
   C=
   case
   when A>B then A
   else B
   end
   from TestCase
  
   --3.在订单表中，统计每个销售员的总销售金额，列出销售员名、总销售金额、称号（>6000金牌，>5500\
   --银牌,>4500铜牌，否则普通）
  select *from MyOrders
   select
   salesAssistant AS 销售员,
   SUM(saveNumber*savePrice) as 销售总金额,
   称号=
   case
       when  SUM(saveNumber*savePrice) >6000 then '金牌'
       when  SUM(saveNumber*savePrice) >5500 then '银牌'
       when  SUM(saveNumber*savePrice) >4500 then '铜牌'
   else '普通'
   FROM MyOrders
   GROUP BY salesAssistant
  4............
  select number,
   ( 
     case
     when amount >0 then amount
     else 0
     end
   )as 收入,
   (
     case
     when amount >0 then amount
     else abs(amount)
     end
   )as 支出
  
   from XF