6-14代码

大数据量的处理？??

老马的视频，有一个处理！

--插入 几条老师信息 和成绩、用SQL插入几条员工信息（注意：bit类型，在写代码中用1或0来表示，不要用’false’,会进行类型转换的。）

select * from TblTeacher

insert into TblTeacher
values('杨中科','男',18,1000000,'2000-10-10')

--练习1：给studentId是1的英语成绩加10分
select * from TblStudent

select * from TblScore
update TblScore set tEnglish=tEnglish+10 where tsid=1


--练习2：考试题偏难，所有人的成绩加5分
--为所有人的成绩+5分
update TblScore set tEnglish=tEnglish+5,tMath=tMath+5

--如果该生的成绩+5分后，大于100，则不给该生加分
update TblScore set tEnglish=tEnglish+5
where tEnglish+5<=100

update TblScore set tMath=tMath+5
where tMath+5<=100


---
update TblScore set tEnglish=100 where tEnglish+5>=100

update TblScore set tMath=100 where tMath+5>=100



update TblScore
set tEnglish=
(
case when tEnglish+5<=100 then tEnglish+5
when tEnglish+5>100 then 100
end
),
tMath=
(
case when tMath+5<=100 then tMath+5
when tMath+5>100 then 100
end
)


--练习3：所有女学生的年龄减1岁

select * from TblStudent
update TblStudent set tsage=tsage-1 where tsgender='女'


--删除工资大于2000的老师
select * from TblTeacher
delete from TblTeacher where ttSalary>2000
--delete [from] TblTeacher where ttSalary>2000

--============将老师表清空========
--删除所有老师
truncate table TblTeacher
--删除数据时候  把自增长列的值还原成种子

insert into TblTeacher
values('小菜鸟','男',20,1200,'2000-10-10')


--===============================================
create table Employees
(
     EmpId int identity(1,1),
     EmpName varchar(50),
     EmpGender char(2),
     EmpAge int,
     EmpEmail varchar(100),
     EmpAddress varchar(500)
)

go
create table Department
(
     DepId int identity(1,1),
     DepName varchar(50)
)

drop table Employees
drop table Department



--================先通过设计器手动添加，然后通过代码来添加====

--============手动增加约束==========
--手动删除一列(删除EmpAddress列)
alter table Employees drop column EmpAddress
go

--手动增加一列(增加一列EmpAddr varchar(1000))
alter table Employees add EmpAddr varchar(1000)

--手动修改一下EmpEmail的数据类型(varchar(200))
alter table Employees alter column EmpAddr varchar(200)
go

--为EmpId增加一个主键约束
alter table Employees
add constraint PK_Employees_EmpId primary key(EmpId)

--非空约束,为EmpName增加一个非空约束，修改列为not null
--增加一个非空约束其实就是修改列
alter table Employees
alter column EmpName varchar(50) not null
go

--为EmpName增加一个唯一约束
alter table Employees add constraint
UQ_Employees_EmpName unique(EmpName)
Go


--为性别增加一个默认约束，默认为'男'
alter table Employees add constraint
DF_Employees_EmpGender default('男') for EmpGender

go



--为年龄增加一个检查约束：年龄必须在0-120岁之间，含0岁与120岁。
alter table Employees add constraint
CK_Emplpoyees_EmpAge check(empage>=0 and empage<=120)
go





--增加外键约束，表Employee中有一列EmpDeptId引用TblDepartment表中的DeptId

alter table Employees add  DeptId int not null
alter table Department add constraint
PK_Department_DeptId primary key(DepId)


alter table Employees add constraint
FK_Employees_Department foreign key(DeptId)
references Department(DepId) on delete cascade


--先删除原来的外键
alter table Employees drop constraint FK_Employees_Department



--删除某个名字的约束

--一条语句删除多个约束，约束名用 逗号 隔开
alter table Employees drop constraint
FK_Employees_Department,
CK_Emplpoyees_EmpAge,
UQ_Employees_EmpName

--用一条语句为表增加多个约束。
alter table Employees add
constraint UQ_Employees_EmpName unique(EmpName),
constraint CK_Emplpoyees_EmpAge check(EmpAge>=0 and EmpAge<=120)

create table t1
(
     autoId int,
     uname varchar(50)
    
)

alter table t1 alter column autoId int identity(1,1)



--==================================================================================================


select * from mystudent
select
     Fid as 学号,
     fname as 姓名,
     fage 年龄,
     性别=fgender,
     数学成绩=fmath,
     英语成绩=fenglish
from MyStudent

select
     学生总人数=100,
     学生总分数=7000
    
    
select
     Fid as 学号,
     fname as 姓名,
     fage 年龄,
     性别=fgender,
     数学成绩=fmath,
     英语成绩=fenglish,
     是否团员='是'
from MyStudent

select '哈哈','吼吼！','嘎嘎','(*^__^*) 嘻嘻……'

--获得系统时间
select getdate()

select * from MyStudent order by fage asc
select * from MyStudent order by fage desc



select top 500 * from MyStudent order by fage desc

--top 数字后加percent表示百分比，如果百分比最后计算出来的条数中有小数，则直接进位。
select top 30 percent * from MyStudent order by fage desc

insert into MyStudent values('杨',18,'男',99,29)

--===================distinct去除重复数据=======

create table Test2
(
     autoId int identity(1,1),
     uname varchar(50),
     uage int,
     ugender char(2)
)

insert into Test2
select '杨',18,'男' union all
select '苏',18,'男' union all
select '蒋',18,'男' union all
select '杨',20,'男' union all
select '杨',18,'男' union all
select '杨',19,'男'

insert into Test2
select '杨',18,'女' union all
select '苏‘,18,'女' union all
select '蒋',18,'女' union all
select '杨',20,'女'


select * from Test2

select distinct * from Test2

select distinct uname,uage from Test2

select uname,uage from Test2


select * from MyStudent

--查询数学成绩中，最高分是多少分
select max(fMath) as 数学成绩最高分 from MyStudent

select min(fMath) as 数学成绩最低分 from MyStudent

--求总分，求和
select sum(fmath) as 总分 from MyStudent

--求平均分
select avg(fmath) as 平均分 from MyStudent


--求班级中的总的记录条数（总人数）
select count(*) as 班级总人数 from MyStudent

select * from MyStudent

--错误,因为聚合函数是把多条汇总成了一条，而fname则是查询出了多条，无法一一匹配，所以不对。
select fname,max(fMath) as 数学成绩最高分 from MyStudent


--select fname from Mystudent
--select max(fMath) from Mystudent

select
     最高分=(select max(fMath) as 数学成绩最高分 from MyStudent),
     最低分=(select min(fMath) as 数学成绩最低分 from MyStudent),
     总分=(select sum(fmath) as 总分 from MyStudent)
    
    
create table Test3
(
     autoId int identity(1,1) primary key,
     uname varchar(50),
     uscore int
)

insert into Test3
select '杨中科',100 union all
select '杨中科1',null union all
select '杨中科2',null union all
select '杨中科3',100 union all
select '杨中科4',80 union all
select '杨中科5',90

select * from Test3

select max(uscore) from Test3
select min(uscore) from Test3
select sum(uscore) from Test3

--计算平均分的时候对空值不处理
select avg(uscore) from Test3
--count计算的时候也不考虑空值
select count(uscore) from Test3

--结果是：6
select count(*) from Test3


--------------带条件的查询------------------
select
       --列名
from  --数据源（表名、子查询结果、视图、.....）
where --条件（筛选一些行......）


--
--查询数学没有及格的学生的学号
select * from MyStudent

select
          fid as 学号,
          fmath as 分数
from MyStudent
where fMath<90


--查询年龄在20-30岁之间的男学生
select
          *
from MyStudent
where fage>=20 and fage<=30 and fgender='男'

select
*
from MyStudent
where fage between 18 and 24 and fgender='女'
--推荐使用between ... and ...
--between 18 and 24 and也相当于是>=18 and <=24

select * from myStudent

use itcastcn

select * from TblStudent

select * from TblClass

--查询班级id为1，2，3的所有学生
select * from TblStudent
where tsclassid=1 or tsclassid=2 or tsclassid=3

--在1,2,3中选择任意一个班级都返回true
--可以用in()语法替换多个or
select * from TblStudent
where tsclassid in (1,2,3)


--------------------------模糊查询=================================
select * from MyStudent

--查询出所有姓'赵'的同学
--通配符%表示：任意多个任意字符
select * from Mystudent
where fname like '赵%'


insert into MyStudent
values('赵敏',24,'女',100,80)

--查询出姓名中只要包含一个'敏'字即可。

select
*
from MyStudent
where fname like '%敏%'

insert into MyStudent
values('毛阿敏',50,'女',89,99)

insert into MyStudent
values('毛敏阿',50,'女',89,99)

insert into MyStudent
values('敏阿毛',50,'女',89,99)




--查询出所有姓'赵'的同学，并且姓名总字符个数，必须是3个。
--通配符 _ ：表示任意的单个字符。
select * from Mystudent
where fname like '赵__'

select * from Mystudent
where fname like '赵%' and len(fname)=3

--通配符[],
--请查询出姓名中包含'磊'或'伟'的人的姓名
select * from MyStudent
where fname like '%[磊伟]%'

--表示x与y之间只要不是'磊'或'伟'的任意单个字符都可以。
--[]通配符，表示中括号中的任意个字符，只选一个匹配。
--[^]表示除了中括号中的任意单个字符。
select * from MyStudent
where fname like 'x[^磊伟]y'


--查询出姓名中包含百分号的
--用[]括起来，就表示转义了。
select * from MyStudent
where fname like '%[%]%'

select * from MyStudent
where fname like '%[_]%'

select * from MyStudent
where fname like '%[[]%'  --奚皓[轩



--查询出所有不姓赵的同学
select * from Mystudent
where fname not like '赵%'


--通过sql语句将表中的数据修改为null值。
use test

select * from mystudent
update MyStudent set fMath=null
where fname like '赵%'

update MyStudent set fMath=null
where fage>30

--1.请查询出学生表中所有数学成绩为null的人的信息
--null在数据库中表示unkonw(不知道)，判断一个值是否为null,也就不能用=或者<>来判断
select * from MyStudent
where fMath=null

--null与null比较结果还是null(null就表示不知道，"不知道"在where中就认为是false,所以不返回任何数据)

select * from MyStudent
where fMath<>null

--查询所有fmath为null的值。
select * from MyStudent where fmath is null

--查询所有fmath为非null的值。
select * from MyStudent where fmath is not null


--true
--false
--unknow
select * from mystudent

update MyStudent set fage=null where fid=1

update MyStudent set fage=fage+1 where fid=2

--null值与任何数据运算后得到的还是null值。
--就像不知道与1相加结果还是不知道。
update MyStudent set fage=fage+1 where fid=1

select fage from Mystudent where fid=1

----select 0/0
--select 0/null

--ISNULL

select * from MyStudent

select
     fname,
     fage,
     --注意：同一列上的数据，数据类型必须一致，如果不一致就会报错。所以要求自己定义查询的时候，注意同一列数据类型一致。
     --这里的'缺考',只存在与查询出的结果集中，表中的数据没有变化
     数学成绩=isnull(cast(fmath as varchar(50)),'缺考')
from MyStudent


select * from MyStudent
--select * from (select * from MyStudent where fage>20) as tbl

--select * from (select top  * from MyStudent where fage>20 order by fage desc) as tbl

--先按英语成绩排序，如果英语成绩相同的话，再按数学成绩排序
select * from MyStudent
order by FEnglish desc,FMath desc,fage asc

--默认不写排序方式，则认为是asc
select * from MyStudent order by fage --asc


--请查询学生表中的信息，显示：
--姓名、性别、英语成绩、数学成绩、平均分
select
     fname 姓名,
     fgender 性别,
     fmath 数学成绩,
     fenglish 英语成绩,
     平均分=(fmath+fenglish)/2.0
from MyStudent
order by (fmath+fenglish)/2.0 desc --order by后面可以跟一个表达式

select
     fname 姓名,
     fgender 性别,
     fmath 数学成绩,
     fenglish 英语成绩,
     平均分=(fmath+fenglish)/2.0
from MyStudent
order by 平均分 desc --order by后面可以跟一个表达式
--order by 永远是在语句的最后才执行。




select
     fage,
     fname 姓名,
     fgender 性别,
     fmath 数学成绩,
     fenglish 英语成绩,
     平均分=(fmath+fenglish)/2.0
from MyStudent
order by (fmath+fenglish)/2.0/fage desc --order by后面可以跟一个表达式


select * from MyStudent
---请统计出MyStudent表中，男同学的个数与女同学的个数
select
     fgender as 性别, --当fgender出现在了group语句中的时候，就可以与聚合函数一起使用。
     count(*) as 人数  --这时，count(*)统计的是每一组的记录条数，而不是总的条数
                    --先执行group by 语句分组，分完组以后再count(*)统计每一组中的信息
from MyStudent
group by fgender --分组以后，分出来几个组，那么count(*)就统计几次。


--
use itcastcn

select * from TblStudent

--统计班级中每个班级的班级Id以及该班级的班级人数

--所以要按班级来分组
select
     tsclassid as 班级Id,
     count(*) as 班级人数
from TblStudent
group by TSClassId


--=======================================
--请从学生表中查询出每个班的班级Id和班级中男同学的人数：
select * from TblStudent

select
     --tsname as 姓名,
     tsclassid as 班级Id,
     count(*) as 班级人数
from TblStudent
where tsgender='男'
group by TSClassId
--一般分组语句group by 都要与聚合函数配合使用，如果没有聚合函数，分组的意义不大。

--当在select查询语句中出现聚合函数时，这时不能在select查询中再出现其他列，除非：该列也在group子句中出现或者也在聚合函数中出现。





select * from TblStudent

--1.统计每个班的男同学的人数
select
     tsclassId as 班级Id,
     count(*) as 班级人数
from TblStudent
where tsgender='男'
group by tsclassId
having count(*) >2

--=============================================
select
     tsclassId as 班级Id,
     count(*) as 班级人数
from TblStudent
where tsgender='男'
group by tsclassId
having 班级人数 >2 --这里不能使用别名"班级人数"，因为当程序执行到having语句的时候，select语句还没有被执行，所以这里还没有创建别名"班级人数"呢。

--=================================================
select
     tsclassId as 班级Id,
     count(*) as 班级人数
from TblStudent
where tsgender='男'
group by tsclassId
having tsclassId in(1,2,4) and ... or ...
--having tsname='貂蝉' --having语句后能跟什么列，主要看：分组后得到的结果集中包含什么列。
--having tsclassId>2



use test

select * from MyStudent

select
     --distinct / top 之类的关键字(这些都是一些现实的选项)
     fgender as 性别,  --5>选择列
     count(*) as 人数
from MyStudent --1>先从MyStudent表中拿到数据(全部数据的一个结果集)
where fage>30  --2>从MyStudent的数据中筛选出所有年龄大于30岁的人的信息（新结果集,都是年龄大于30的）
group by fgender --3>按照性别分组，分完组以后又得到一个新结果集(分组后的结果)
having count(*)>355 --4>基于分组以后的结果集，然后在筛选，筛选出那些组中记录大于500的组。
order by  人数 desc--6>最后把显示出来的结果排序


select * from MyStudent
select top 5 * from MyStudent order by fenglish desc


--========================================
select * from myorders

--1.热销售商品排名表，【即按照每种商品的总销售数量排序】。
select * from myorders

select
          商品名称,
          sum(销售数量) as 共计销售数量
from MyOrders
group by 商品名称
order by 共计销售数量 desc
--2.请统计销售总价超过3000元的商品名称和销售总价,并按销售总价降序排序。
select * from myorders

select
     商品名称,
     sum(销售数量*销售价格) as 销售总价
from MyOrders
group by 商品名称
having sum(销售数量*销售价格)>3000
order by 销售总价 desc
--3.统计各个客户对"可口可乐"的喜爱度(既统计每个购买人对“可口可乐”的购买量)
select * from myorders
select
          购买人,
          sum(销售数量) as 销售数量,
          商品名称='可口可乐'
from MyOrders
where 商品名称='可口可乐'
group by 购买人
order by 销售数量 desc

--========================类型转换============================、
--select 100+'hello'
--select 'hello'+100

select 100+'100'

select cast(100 as varchar(10))+'hello'
select convert(varchar(10),100)+'hello'

select convert(varchar(50),getdate())

select convert(varchar(50),getdate())
select convert(varchar(50),getdate(),101)
select convert(varchar(50),getdate(),100)
select convert(varchar(50),getdate(),111)
select convert(varchar(10),getdate(),126)


create table TestConvert
(
     autoId int identity(1,1) primary key,
     uname varchar(50),
     myid varchar(50)
)

select * from testconvert
insert into testconvert
select '杨','21' union all
select '杨','22' union all
select '杨','23' union all
select '杨','34' union all
select '杨','35' union all
select '杨','36' union all
select '杨','47' union all
select '杨','48' union all
select '杨','49' union all
select '杨','50' union all
select '杨','51' union all
select '杨','52'
select * from TestConvert order by cast(myid as int) desc

------------------------联合---------------------------------------

select * from mystudent

select * from itcastcn..tblstudent

--union联合的作用就是将多个结果集并到了一起
select fname,fage from mystudent where fid>999
union all
select tsname,tsage from itcastcn..tblstudent where tsid>12

--===================================================
--当联合的时候注意：
--1.多个结果集中的列的数据类型必须一一对应
--2.列的个数必须一样
--联合的时候如果只写union，则会去除重复数据，如果写union all则不会去除重复数据。由于一般情况下我们都没有重复数据，所以也不需要去除，所以我们一般建议使用union all。
select fname,fage from mystudent where fid>999
union all
select tsage,tsname from itcastcn..tblstudent where tsid>12

select * from MyOrders
--请查询出每个销售员的销售总金额以及总的销售金额
--要求：总的销售金额显示在表的底部
select
     销售员,
     销售金额=sum(销售数量*销售价格)
from MyOrders
group by 销售员
union all
select '总销售额：',sum(销售价格*销售数量)
from MyOrders

--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
select * from MyStudent
select
     max(Fmath) as 最高分,
     min(fmath) as 最低分,
     avg(fmath) as 平均分
from MyStudent

select 统计='最高分：',max(Fmath) as 实际得分 from MyStudent
union all
select '最低分：',min(Fmath) from MyStudent
union all
select '平均分：',avg(Fmath) from MyStudent


select * from MyStudent
select * into NewStudents from MyStudent where 1<>1
select * from NewStudents

--拷贝表结构，但是没有数据
select top 0 * into NewStudents1 from MyStudent

select * from NewStudents1

--================字符串函数=================
select len('哈哈hello') --返回字符的个数
select datalength('哈哈hello') --返回是字节个数

select lower('AaBb')

select UPPeR('AaBb')

select '==========='+rtrim(ltrim('     aaa       '))+'==============='

--从左边数，截取10个字符
select left('hello welcome to China.',10)

--从右边数，截取10个字符
select right('hello welcome to China.',10)

--从索引为2的开始，截取5个字符。
--注意：1.索引从1开始数。2.含当前索引。
select substring('abcdefghijklmn',2,5)

select replace('fjdsalfafdjaslkfjdsakjflksafjdsfjdslkfjdsljf','f','★')
select replace(username,'赵','李')