Sql_DDL&DML

//------------------DDL----------------

--创建create

　　create database mDB

　　create table mTbl

--删除drop

　　drop database mDB

　　drop table mTbl　　　　　　--(推荐逻辑删除)

--增加约束

　　alter table mTbl

　　add constraint Ck_name check(len(Name)>2)

//-----------------DML-----------------

--插入值

　　insert into mTbl(Name,age) values('Joe',20)　　　　　　--标示自动设置

　　set identity_insert mTbl on

　　insert into mTbl(id,Name,age) values(100,'Cheng',20)　　--标示手动设置

　　set identity_insert mTbl off

--修改

　　update mTbl set Name='Joo',age=21 where(id=100)

--删除

　　delete from mTbl where()

　　truncate table mTbl  　　　　　　--清空表 执行效率比delete高 并且会将标示种子恢复

--基本格式
select * from TblClass
    
--对于列限制

        --取指定列
        select cid,cname from Tblclass
        select cname from Tblclass

        --起别名 as
        select cid as 编号, cname 名称, 描述=cdescription from Tblclass

--对于行限制
        insert into tblclass values('myClass001','jjoe')
        insert into tblclass values('myClass002','jjoe')
        insert into tblclass values('myClass003','jjoe')
        insert into tblclass values('myClass004','jooe')
    --top : 表示取前n行 与 前 n%
    select top 2 * from tblclass
    select top 50 percent * from tblclass
    
select * from tblclass
update tblclass set cdescription ='bi...sowshoddllll' where cid%2=0

    --distinct : 消除重复数据  不查主键列的时候 可以消除重复 比如男女
    select cdescription from tblclass
    select distinct cdescription from tblclass
    select distinct cdescription,cname from tblclass        --两列都重复才消除




--where子句 也是对行进行筛选    做条件用的  设置大小范围

    select * from tblclass 
    where cid>=3        --比较运算符  >  <    >=    <=  != (能比较的类型)

    --查询班级编号在2-4之间的信息：
    select * from tblclass
    where cid>2 and cid<5        --逻辑运算符 and or not 

    --班级编号在1到4之间 并且 要求描述的字符串个数大于5
    select * from tblclass
    where cid>1 and cid <4 and len(cdescription)>5            --len()  用于访问字符个数
    
    --班级编号在1到4之间 或者 要求描述的字符串个数大于5
    select * from tblclass
    where (cid>1 and cid<4) or len(cdescription)>5                --运算符not的优先级最高

    --取范围,表示在一个连续的范围内 between ... and ... [5,8]  闭区间
    select * from tblclass
    where cid between 2 and 4    
    select * from tblclass 
    where (cid between 2 and 4)  and len(cdescription)>5
        
    --in关键字 表示一个不连续的范围
        --查询编号为1,3,5的班级
            select * from tblclass
            where cid=1 or cid=3 or cid=5
        select * from tblclass
        where cid in(1,3,5)

    select * from tblclass

    --模糊查询： like  _(任意一个字符)  %(任意多个字符)  [](连续区间) [^](表示非)
    select * from tblclass
    where cdescription like '%good%'    

        --查询描述包含dll的信息
        select * from tblclass
        where cdescription like '%dll%'
        --查询描述中以b开头并且是4个字符
        select * from tblclass
        where cdescription like 'j___'

        update tblclass set cdescription='Join us !' where cname='myclass002'
        --查询描述中包含！的班级名称            转义[!] [%]
        select cname from tblclass
        where cdescription like '%[!]%'
        
        select  * from tblclass
        insert into tblclass(cname,cdescription) values('myclass005','my description is : good!')
        
        
set identity_insert tblclass on
insert into tblclass(cid,cname) values(100,'myclass100')
set identity_insert tblclass off



insert into tblclass values('myclass102',null)
--空值判断  is [not] null 
        select * from tblclass 
        where cdescription is null

--函数判断 isnull() 判断值是否为空 如果为空 不显示null而给一个默认值
select cid,cname,isnull(cdescription,'暂时没描述') from tblclass


--==============================================
--order by 子句排序子句
select * from tblclass
--order by cid asc  --按cid升序排序 asc
--order by cid desc --按cid降序排序    desc


--可以按照多列排序 order by cid desc,cname asc
order by cid desc,cname asc

--==============================================
--分组子句group by ... having ...
--聚合函数
--聚合： 把多行合并成一行
use itcastcn
select  * from tblscore
--找英语的最高分
select max(tenglish) from tblscore
--找数学的最低分
select min(tmath) from tblscore
--找英语的平均分
select avg(tenglish) from tblscore
--求数学成绩的总和
select sum(tmath) from tblscore
--求参加考试的人数
select count(*) from tblstudent
select * from tblscore

//2015-12-07　　　　分组   聚合 常用函数  格式化 字符串函数 日期函数  联合查询

--分组  group by  统计各班人数  出现在分组选项的列 可以出现在查询结果中 其他的列不可以
--和聚合函数一起出现在结果中
select tsclassid,count(*) as 人数 from tblstudent
group by tsclassid


--做选择 having  和where的区别： having是完成统计之后  在统计结果之中进行筛选 where 在之前
    --查找出班级人数大于5的班级信息
select tsclassid,count(*) as 人数 from tblstudent
group by tsclassid having count(*)>5

--select ... from  ... where ... group by ... having...

-->>完整的select语句  select distinct top n[percent] * 
                    --from 表 where (条件) group by ... having ... order by ...


select  * from tblstudent

select tsclassid,count(*) as renshu 
from tblstudent
where tsGender='男'
group by tsclassid having tsclassid=5


select distinct top 1 tsage,count(*) as 总人数
from tblstudent 
where tsgender='女'
group by tsage having tsage>15
order by tsage asc

--练习：查询所有女生信息
select * from tblstudent where tsgender = '女'
--练习： 查询班级为3的男生信息
select * from tblstudent where tsgender='男' and tsclassid=3
--练习：查询姓张的男学生
select * from tblstudent where tsname like '张%' and tsgender='男'
--练习：找出各个城市的人数
select tsaddress,count(*) as 人数
from tblstudent
group by tsaddress 
order by 人数 desc
--练习：找每个班中人数最多的城市名称






--20151207
--常用函数:
    select * from tblclass
    --cast(值 as 类型)
    update tblclass set cdescription = cdescription +cast(cid as char(2))
    --convert(目标类型,值[,format])    
    select convert(float,'12.3434')

    --格式化
    select convert(float,'12.3434',0)
    select convert(float,'123.23232123123',6)
    select convert(decimal(10,2),'123.23123123')
    select getdate()
    select convert(datetime,'2015-12-07',103)
    
    select convert(varchar(10),getdate())
    --2015-12-07 16:12:38.730


--字符串函数
select upper('asdf')
select * from tblclass
select len(cdescription) from tblclass

select ltrim('   asd')
select rtrim('asd    ')

--字符串截取 left right substring
--下标从1开始


select substring('asdfasllsl',3,5)--从1开始数 第三个 5个字符


--日期函数
--增加日期
select dateadd(year,5,getdate())
select dateadd(month,5,getdate())
--两个时间做差
select datediff(day,getdate(),'2015-12-20')

select * from tblemployee
select datepart(dayofyear,empjoindate) from tblemployee

select year(empjoindate) from tblemployee
select datepart(hour,getdate())


select getdate()




--联合查询  union
select cid,cname from tblclass
union
select * from tbldepartment

use itcastcn

select 'Max：',max(tenglish) from tblscore
union
select 'Min：',min(tenglish) from tblscore
union
select 'Avg：',avg(tenglish) from tblscore


--合并成一行
select max(tenglish),min(tenglish),avg(tenglish) from tblscore


--练习：
select * from tblteacher
select ttname,ttsalary from tblteacher
union all
select '平均工资：',avg(ttsalary) from tblteacher
union    all
select '最高工资:',max(ttsalary) from tblteacher

//2015-12-08  连接查询  判断

--连接查询   join on
select * from tblclass
select * from tblstudent
--查询显示学生姓名以及对应班级名称     多表查询 

select tsname as '姓名',cname as '班级名'
from tblclass
inner join
tblstudent on  tblstudent.tsclassid=tblclass.cid    

--查询学生姓名以及英语数学分数

select tblstudent.tsname,tblscore.tenglish,tblscore.tmath 
from tblstudent
join 
tblscore on tblstudent.tsid=tblscore.tsid

--查询所有学生的姓名、年龄、所在班级

select tsname,tsage,cname 
from tblstudent
join
tblclass on tblstudent.tsclassid=tblclass.cid
order by tsage asc

--查询所有年龄超过20 的学生姓名、年龄、班级
select tsname,tsage,cname 
from tblstudent
join
tblclass on tblstudent.tsclassid=tblclass.cid
where tblstudent.tsage>20
order by tsage desc

--查询学生姓名、年龄、班级及成绩
select tblstudent.tsname,tblstudent.tsage,tblclass.cname,tblscore.tenglish,tblscore.tmath 
from tblstudent
join 
tblclass on tblstudent.tsclassid=tblclass.cid
join 
tblscore on tblstudent.tsid=tblscore.tsid


----查询所有没有参加考试（在成绩表中不存在的学生）学生的姓名
--select tsclassid,max(tsaddress) from tblstudent
--group by tsclassid


--练习：
--1.查询所有英语及格的学生姓名、年龄以及成绩
select tsname,tsage,tenglish 
from tblstudent
join
tblscore on tblstudent.tsid=tblscore.tsid
where tblscore.tenglish>60
order by tenglish asc


--2.查询所有没有参加考试的学生的姓名
--学生编号不在tblscore表中出现not in()
select tsname from tblstudent
where tsid not in (select tsid from tblscore)


--3.分组使用多个属性
--查询每个班级的各个地区有多少人   先对班级分组  在对地区分组
select tsclassid,tsaddress,count(*)
from tblstudent
group by tsclassid,tsaddress
order by tsclassid

--查询每个班级男女人数
select tsclassid,tsgender,count(*) as 人数
from tblstudent
group by tsclassid ,tsgender    
order by tsclassid

--查询每个班级每个年龄人数    显示的只能是聚合的列的信息
select tsclassid,tsage,count(*) as 人数
from tblstudent
group by tsclassid,tsage
order by tsage


--3.计算各员工的工龄
select datediff(day,'2014-11-12',getdate())


select * 
from tblemployee
where datediff(day,empjoindate,getdate())+1>365


-------------------------------外连接---------------------------
select * from  TblDepartment
select * from Tblemployee

select * from tbldepartment left join tblemployee on empdeptid=deptid
select * from tblemployee left join tbldepartment on empdeptid=deptid

select * from tblemployee right join tbldepartment on empdeptid=deptid
select * from tbldepartment right join tblemployee on empdeptid=deptid


select * from tblemployee full join tbldepartment on empdeptid=deptid


--低于60分的提示不及格
select * from tblscore
select * from tblstudent


--判断不想等的情况
select tsname,
isnull(
case 
when tenglish<60
then '不及格'
else cast(tenglish as varchar(10))
end ,'quekao'
)
from tblstudent 
left join tblscore on tblscore.tsid=tblstudent.tsid



--判断相等的情况
select * from tblstudent

use mytestdatabase
create table TblStudent
(
    Autoid  int identity(1,1) ,
    StId int not null,
    StName nvarchar(100),
    StGender int 
)

select * from TblStudent

insert into TblStudent(stid,stname,stgender) values('00003','LJ',0)

update tblstudent set stgender=1 where stid='00002'


--将性别0 1 显示为男女
select *,
case stgender when 0 then N'男' when 1 then N'女' end as 性别
from tblstudent


--显示成绩为 优良中差
use ItCastCn
select * from tblScore

select *,
case 
when tenglish<=100 and tenglish >=90 then '优' 
when tenglish<90 and tenglish>=70 then '良'
when tenglish<70 and tenglish>=60 then '中'
when tenglish<60 then '差'
end 
from tblscore

-- 财务流水表
    --TblMoneyFlow
create Table TblMoneyFlow
(
        fid int identity(1,1) primary key not null,
        ftitle nvarchar(10),
        fmoney money
)

select * from tblmoneyflow

insert into TblMoneyFlow values('发工资',5000)
insert into TblMoneyFlow values('奖金',1000)
insert into TblMoneyFlow values('兼职',400)
insert into TblMoneyFlow values('请客',200)
insert into TblMoneyFlow values('大保健',500)


select * ,case when fmoney>0 then fmoney end as '收入',
case when fmoney<0 then abs(fmoney) end as '支出'
from tblmoneyflow