/*--数据库和数据表基本操作*/
/*创建一个数据表*/
/*create table T_Person(Id int not null,Name nvarchar(max),Age int null,NickName nvarchar(max))*/
--创建一个数据表同时设置主键列
--create table T_PersonAutoIncrement1(Id int not null primary key identity,Name nvarchar(max),Age int null,NickName nvarchar(max));
/*往对应的数据库创建一个新数据表*/
/*use Person1;
create table T_Person(Id int not null,Name nvarchar(max),Age int null,NickName nvarchar(max))
*/
/*删除一个数据表*/
/*
drop table T_Person;
*/
/*插入一条数据*/
/*insert into T_Person(Id,Name,Age,NickName) values(1,'狗蛋1',21,'gou');*/
/*添加列*/
/*alter table T_Person add Height int;*/
/*更改列的类型*/
/*alter table T_Person alter column Height bigint;*/
/*删除列*/
/*alter table T_Person drop column Height;*/
/*更新某一行中的一个列(某个表格)//将id=1的那一行里面age值设置为30，这里不用谢update table
T_Person,直接写update T_Person就可以了，跟上面的不同，上面的都有table*/
/*update T_Person set Age=30 where id=1;*/
/**更新一行中的多个列数据(多个表格)**/
/*update T_Person set Age=40 ,NickName='猪八戒' where id=1 */
/**删除某行*，如果有多个Age=20的行就会都删除,删除某一个就要多个判断条件，没有限制就会删除某一行，注意了！！！*/
/*
delete from T_Person where Age =20 and id=3;*/
/***删除所有行，*/
/*delete from T_Person;*/
--或者//单行注释是--
/*delete from T_Person*/
/*读取某列,包括重复的值*/
/*
select Name from T_Person; */
/*读取某列，数据去重输出*/
/*
select distinct Name from T_Person;
*/
/*按照一定的排序输出数据*/
/*
use Person1;
select Age from T_Person order by Age desc;--以年龄的倒序输出，顺序是asc
select Name from T_Person order by Name;--以字母的顺序输出名字
select Name , Age from T_Person order by name ,Age;--先以名字字母顺序排，有相同的项则再按年龄的顺序排，从小到大；
select Name , Age from T_Person order by name asc ,Age asc;--也可以这么写
select Name , Age from T_Person order by name asc ,Age desc;--名称字母顺序，年龄逆序
*/
/*创建新数据库*/
/*
create database my_db;
*/
/*删除数据库*/
/*drop database my_db;*/
/*--约束-*/
/*创建不重复的id列有限制的数据表,id不能重复*/
--create table U_PersonUniqueid(id bigint not null unique,name nvarchar(max) not null,age int);
/**创建数据表的同时命名约束名称**/
--create table U_PersonUnique(id bigint not null,name nvarchar(max) not null,age int,constraint uc_name unique(id));
/*修改名为uc_PersonID的约束unique*/
--alter table U_Person add constraint uc_PersonID unique(id,orderid);
/*删除名称为uc_name(uc其实为unique constraint的缩写) 的unique约束*/
--alter table U_PersonUnique drop constraint uc_name;
--强制约束id和name唯一 //这条语句报错是因为Name设置了max，超过了作为索引的最大值900，将max改为小于等于900的值就可以了；
--create table UC_Person(Id bigint,Name nvarchar(max),Age int,constraint uc_onlyone unique(id,Name));
--create table UC_Person(Id bigint ,Name nvarchar(800),Age int,constraint uc_onlyone unique(id,Name,Age));
--去掉原有约束
--alter table UC_Person drop constraint uc_onlyone;
--新加约束
--alter table UC_Person add constraint uc_onlyone unique(id);
--alter table UC_Person add constraint uc_onlyone1 unique(Name);
--alter table UC_Person add constraint uc_onlyone2 unique(Age);
--这里直接写alter table UC_Person add constraint uc_onlyone unique(id,Name,Age);竟然不行，所以独立写了三个就可以，不知为什么；有待探讨
--insert into UC_Person(id,name,age) values(1,'na',110);
--update T_Person set NickName=N'未成年' where Age<18 and Age>1;
--update T_Person set NickName=N'壮年' where Age>25 and Age<35;
--update T_Person set NickName=N'老少' where Age<18 or Age>40;
--update T_Person set NickName=N'老年' where (Age<18 or Age>40) and Age=50;
/**产生唯一不同的guid,可以用newid()函数，也可以直接对id这个列的默认值设置newid(),这样就不用填写id这一列了,但一般不这么做，因为这个guid后面要用到**/
--这里插入会报错，因为id的类型不能临时更换
--insert into T_Person(Id,Name,Age,NickName) values(NEWID(),'lucy',22,'Wlucy');
/*--删除数据--*/
--起别名
use Person1;
select Age as 年龄,Name as 姓名,NickName as 昵称 from T_Person where Age>20;
--输出内容和运算，加列名称;
select NEWID();
select @@VERSION;
select 1+1;
select 1+1 as 列1;
select 1+1 as 列1,GETDATE() as 日期,NEWID() as 编号;
select 1+1 as 列1,GETDATE()+1000 as 日期,NEWID() as 编号;
/**数据统计查询最大值最小值等等聚合函数**/
select COUNT(*) from T_Person;--总共有多少条数据
select MAX(Age) from T_Person;--年龄最大值
select Min(Age) from T_Person;--最小值
select avg(Age) from T_Person;--平均值
select sum(Age) from T_Person;--和
select COUNT(*) from T_Person where Age>23;--统计符合条件的数据条数
/*****数据的排序***/
select * from T_Person order by Age; --按年龄排序，默认是顺序
select * from T_Person order by Age Asc; --按年龄升序排序
select * from T_Person order by Age Desc;--倒叙输出
select * from T_Person order by Age Asc ,Id Desc;--升序降序混合用
select * from T_Person where Age>23 order by Age Asc ,Id Desc;--加过滤条件
/***模糊匹配**/
select * from T_Person where Name='tom';--精准匹配
select * from T_Person where Name like '_om';-- 单个字符匹配,_代表一个字符，前匹配
select * from T_Person where Name like 'to_';--后匹配
select * from T_Person where Name like '_o_';--三位，前后各一位匹配
select * from T_Person where Name like '%m';--前匹配0-多个字符;
select * from T_Person where Name like 'm%';--后匹配0-多个字符；
select * from T_Person where Name like '%r%';--包含r的匹配
select * from T_Person where Name is null;--不知道的值null//null不是没有值，而是不知道
select * from T_Person where Name is not null;--不为不知道的数据
/****null的特殊性***/
select 'aaa'+'1';--aaa1
select 'aaa'+null;--还是null
select null+1;--还是为null
select null*1;--还是null
select null-null;--还是null
select * from T_Person where Name=null;--等于查不出来
select * from T_Person where Name<>null;--不等于，都查不出来
select * from T_Person where Name!=null;--不等于，都查不出来
select * from T_Person where Name is null;--正确查法
select * from T_Person where Name is not null;--正确查法
/**多个点条件查询or***/
select * from T_Person where Name='tom' or Age=22 or Age=30;--多个不同可以用or
select * from T_Person where Age in (20,25,26,29);--多个相同列的用in
select * from T_Person where Age in (20,25,26,29) and Name is not null;--混用;
select * from T_Person where Age >20 and Age <30;--区间
select * from T_Person where Age >= 20 and Age <= 30;--区间等于
select * from T_Person where Age between 20 and 30;--区间等于另一种写法，包括等于的值
/**********数据的分组************/
select COUNT(*) from T_Person ;--统计人数
select Age,count(*) from T_Person group by Age;--对年龄进行分组;
select Age,Name count(*) from T_Person group by Age;--报错，不能使用不在group by出现的列名，聚合函数除外，看下面例子
select Age,MAX(Id),COUNT(*) from T_Person group by Age;--取某一组中的id的最大值；正确
select Age,AVG(Id),COUNT(*) from T_Person group by Age;--得到某一组中的id的平均值；正确
select Age,AVG(Id) as 平均值,COUNT(*) as 个数 from T_Person group by Age;--得到某一组中的id的平均值，以列名平均值输出；正确，以年龄分组，输出年龄，各组的平均值，各组的个数
/****having是对分组后的数据进行过滤，select中有的列才能用*****/
select Age,COUNT(*) from T_Person where COUNT(*)>5 group by Age;--报错，要用having，不能用where
select Age,COUNT(*) from T_Person group by Age having COUNT(*)>1;--having要放到group by后面;
select Age,COUNT(*) as 年龄组 from T_Person group by Age having COUNT(*)>1;--给列名
select Age,COUNT(*) as 年龄组 from T_Person group by Age having Age>28;--以年龄大于28的进行分组，输出年龄，各组的个数
/**复习查询不重复数据的方法**/
select distinct Age from T_Person;--查询不重复的Name数据;
select distinct * from T_Person;--查询不重复的Name数据;
--2017-04-29
--如何使用远程服务器的数据库，首先左上角的链接点击，填写对应的链接地址链接成功后选中要查询的数据库，然后点击上面的新建查询就可以直接查询了，必要时要写use 来查询
--前几名
use Person1;
select Age from T_Person order by Age desc;
select top 3 Age from T_Person order by Age desc;--只输出年龄列
select top 3 * from T_Person order by Age desc;--输出年龄前3的人的所有列，按倒序输出
select top 5 id from T_Person order by Age desc;--按年龄倒序前5列的id输出
select top 3 * from T_Person where id not in (select top 5 id from T_Person order by Age desc) order by Age desc;--第六条开始取3条
--给表增加列
alter table T_Person add Salary int;
--给空列增加数据
update T_Person set Salary=(id*500)+(Age*100) where Salary is null;
--去掉重复数据
select Age from T_Person order by Age desc;--取到所有人的年龄
select distinct Age from T_Person order by Age desc;--针对正行信息重复的，并不是所有重复的
--不加distinct的话会多出一行工资9400，年龄29的，加了之后就会去掉一条，但工资列相同值或者年龄列相同值的不会去掉
select Age, Salary from T_Person order by Age desc;
select distinct Age, Salary from T_Person order by Age desc;
/********union从不同的表输出相同的列(列数相同)的链接关键词*******/
use Person1;
select Age,Name from T_Person
union
select Age,Name from p1;
--发现丢失了数据，默认会把完全重复的数据合并，如果不想丢失的话加一个all；
select Age,Name from T_Person
union all
select Age,Name from p1 order by Age;--按年龄顺序输出
--联合输出
select 'T年龄最大值',MAX(Age) from T_Person
union all
select 'T年龄最小值',MIN(Age) from T_Person
union all
select 'p年龄最大值',Max(Age) from p1
union all
select 'p年龄最小值',MIN(Age) from p1 ;
--计算列和合计
select Name,Age from T_Person where Age>22
union all
select '年龄合计',SUM(Age) from T_Person where Age>22 order by Age;
/**********************数据库函数*************************/
select ABS(-3);--绝对值
select CEILING(3.33);--往上取整
select CEILING(-3.33);--往上取整，比他大的整数
select FLOOR(3.33);--往下取整
select FLOOR(3.83);--往下取整
select FLOOR(-3.83);--往下取整，比他小的整数
select round(3.33124154,3);--四舍五入精确到第三位小数
select round(3.536515154,3);--四舍五入精确到第三位小数、
select LEN('fsdfsdfsdf');--字符串的长度
select Name,LEN(Name) from T_Person;--名字长度，为null的长度依然为null
select Name,LEN(Name) from T_Person order by LEN(Name);--按名字长度输出，为null排前面；
select LOWER('FDFDF');--转小写
select upper('kjfdskjf');--转大写
select LTRIM(' bb ');--去掉左边空格
select RTRIM(' bb '),LEN(RTRIM(' bb '));--去掉右边空格
select LTRIM(RTRIM(' bb ')),LEN(LTRIM(RTRIM(' bb ')));--两边去掉
--字符串函数
select SUBSTRING('abcdefghijklmn',1,4);--字符串，从(1为最小值)什么位置开始，取多少位，跟js中的substring参数定义不同，js为0开始，取到截至位置
select Name, SUBSTRING(Name,1,2) from T_Person;
--时间函数
select GETDATE();--当前时间
select DATEADD(YYYY,3,GETDATE());--往后加三年 简写yy或者yyyy
select DATEADD(year,-3,GETDATE());--往前减三年
select DATEADD(month,3,GETDATE());--往后加三个月，简写mm/m
select DATEADD(month,-3,GETDATE());--往前减三个月mm/m
select DATEADD(DAY,3,GETDATE());--往后加三天dd/d
select DATEADD(DAY,-3,GETDATE());--往前减三天
select DATEADD(HOUR,25,GETDATE());--往后加25个小时hh
select DATEADD(HOUR,-3,GETDATE());--往前减三个小时
select DATEADD(MINUTE,3,GETDATE());--往后加三分钟mi
select DATEADD(MINUTE,-3,GETDATE());--往后倒三分钟
select DATEADD(SS,3,GETDATE());--往后加3秒s/ss
select DATEADD(SECOND,-3,GETDATE());--往后倒3秒
select DATEADD(MS,3,GETDATE());--往后加3毫秒
select DATEADD(MS,-3,GETDATE());--往后倒3毫秒
--时间差计算
select DATEDIFF(DD,'2017-04-28','2017-05-30');--dd代表天
select DATEDIFF(M,'2017-04-28','2017-05-30');--mm代表月
--以此类推
--复杂点的计算
--算出入职年限的员工的个数，就是进入公司多少年分组
use Person1;
select indate from T_Person;
select DATEDIFF(yy,indate,getdate()) as 年限组,COUNT(*) as 这一年有多少个人 from T_Person group by DATEDIFF(yy,indate,getdate());
--取出日期的特定部分
select DATEPART(YY,GETDATE());--取年分
--年月日时分秒毫秒以此类推
select DATENAME(WEEKDAY,GETDATE());--今天星期几//星期日
select DATENAME(WEEK,GETDATE());--今天是今年第几周---和datepart输出不同的是datename输出的是字符串类型
select DAY(GETDATE());--取天
select MONTH(GETDATE());--取月
select YEAR(GETDATE());--取年
--重新实现上面年限分组的数据
select DATEPART(YY,indate),COUNT(*) as 个数 from T_Person group by DATEPART(YY,indate);
/**************类型转换***************/
select CAST('123456' as int) , CAST('2017-04-30' as datetime);--字符串转换为int和datetime,超出范围会报错
select CONVERT(datetime,'2017-04-20'),CONVERT(varchar(50),123);--第一个参数是将要转换成的类型
