SQL基本语句

• 学习SQL语句时候练习记录，仅供参考

--建表、插入
create table Person2(Id int not null,Name nvarchar(50) not null,Age int not null);
insert into Person2( Id,Name,Age) values(1,'liu',20);
insert into Person2(Name,Age) values('bobo',10);
insert into Person2 values('hehe',20);
select NEWID();
--插入
create table Person3(ID int not null,Name nvarchar(20) null,Age int not null);
insert into Person3(ID,Name,Age) values(1,'hhh',20);
insert into Person3(ID,Name,Age) values(2,'hhh',20);
insert into Person3(ID,Name,Age) values(2,'hhh',40);
insert into Person3(ID,Name,Age) values(2,'hhh',30);
insert into Person3(ID,Name,Age) values(2,N'刘春波',30);
insert into Person3(ID,Name,Age) values(2,'刘春波',30);
--更新
update Person3 set Age=30,Name='liheli' where Age>30 or Age<10;
update Person3 set Age=30,Name='liheli' where (Age>30 and Age=20) or Age<10;
update Person3 set Age=30,Name='liheli' where Age<>30;
update Person3 set Name='liheli' where Age=40;
update Person3 set Age=Age+1;
--删除 delete删除数据 drop删除表
delete from Person3 where Age>20;
drop table Person3;
修改表,增加列
alter table Person3 add company nvarchar(20);
alter table Person3 add department nvarchar(20);
--检索
select * from Person3;
select Age,Name from Person3;
select Age as 年龄 from Person3 where Age=30;
select NEWID();
select GETDATE();
select @@VERSION;
select * from Person3;
select 1+1 as 列1,GETDATE() as 日期,NEWID() as 编号;
--统计
--select count(*) from Person3;
select count(*) from Person3;
select max(Age) from Person3;
select min(Age) from Person3;
select avg(Age) from Person3;
select count(Age) as 计数 from Person3 where Age=30;
--数据排序 order by  ASC升序，DESC降序
select * from Person3 order by Age DESC;
select * from Person3 order by Age ASC;
select * from Person3 order by Age desc,Name asc; --先按年龄，年龄相同则按名字
select * from Person3 where Age>30 order by Age desc;
--通配符查询 
-- _单字符匹配,%多字符匹配
select * from Person3 where Name like'_h'
select * from Person3 where Name like'_h_'
select * from Person3 where Name like'__h'
select * from Person3 where Name like'_hh'
select * from Person3 where Name like'%h'
select * from Person3 where Name like'%春%'
select * from Person3 where Name like'%'
--sql 中 null 意思为没有，null+1=null
select null+1;
select ''+'123';
select 'abc'+'123';
select null+'123';
--这两行是查不出来的
select * from Person3 where Name<>null;
select * from Person3 where Name=null;
--sql中要这样查
select * from Person3 where Name is not null;
select * from Person3 where Name is  null;
select * from Person3 where Age=21 or Age=20;
select * from Person3 where Age in (20,21);
select * from Person3 where Age between 20 and 30;
--数据分组 group by
--按字段分组
select Age as 年龄,COUNT(*) as 计数 from Person3 group by Age;
select Name as 姓名,count(*) as 总数 from Person3 group by Name;
select Age as 年龄,AVG(Age) as 平均年龄 from Person3 group by Age;
--Having语句 
--having是对分组后的信息过滤，where是对分组前的信息过滤
--所以having不能简单地代替where
select Age as 年龄,count(*) as 计数 from Person3  where Age>20 group by Age;
select Age as 年龄,count(*) as 计数 from Person3 group by Age having count(Age)>1;
--限制结果集的行数 
select top(2) * from Person3 where Age>10 order by Age desc;
select top 2  * from Person3 where Age>10 order by Age desc;
--下面注意，用in的时候，必须限制后面的语句输出为一列
select top 2 * from Person3 where Name not in (select top  1 Name from Person3 order by Age desc) order by Age desc;
--去掉重复数据
select * from Person3;
update Person3 set company='beijing' where Name='liu';
update Person3 set company='wuhan' where Name='chun';
update Person3 set company='zhengzhou' where Name='bo';
insert into Person3 values('bo',21,'beijing');
select Name,Age from Person3;
--distinct消除完全重复的行
select distinct Name,Age from Person3
--UNION联合结果集
--union去除了完全重复的数据，只保留一条
--如果不想合并，用union all
--注意union两边不能有分号
--上下列数要相同(不同用0补足)，数据类型要相同
select Name,Age from Person3 where Age>20
union --（union all)--不去除重复结果
select Name,0 from Person3 where Age<20;
--select Select出来的结果
select Name from (select Name from Person3 where Age>15)tempTable;
--报表数据
select * from Person3;
select '最大年龄',max(Age) from Person3
union all
select '最小年龄',min(Age) from Person3
union all 
select '平均年龄',avg(Age) from Person3
union all
select '总人数',count(*) from Person3
union all
select '年龄总和',sum(Age) from Person3
--函数
--数字函数
select abs(-20),abs(10);
select ceiling(2.3),ceiling(2.6);
select floor(2.3),floor(2.6);
select round(2.34,1);--四舍五入,第二位为保留几位小数
--字符串含书
select len('len');
select Name, LEN(Name) as '字符串长度' from Person3;
select lower('LEN');
select upper('len');
select rtrim(' len  ');
select ltrim(' len  ');
select len(rtrim(ltrim(' len  ')));
select substring('lenght',2,2);--从第二位开始，长度为2的子字符串
--日期函数
select getdate();--当前日期
--第一个是单位（可以是，HH，DAY，MOUTH，YEAR等），第二个是数量，第三个是要计算的日期
select dateadd(DAY,-10,getdate());
--差额计算，第一个是单位，第二个是开始日期，第三个是结束日期
select datediff(DAY,getdate(),dateadd(day,-10,getdate()));
alter table Person3 add Indate date null ;
select * from Person3;
update Person3 set Indate=dateadd(YEAR,-10,getdate()) where  Age=21;
update Person3 set Indate=dateadd(YEAR,-20,getdate()) where  Age=12;
update Person3 set Indate=dateadd(YEAR,-12,getdate()) where  Age=20;
select Name as 姓名,Indate as 入职日期,datediff(YEAR,Indate,getdate()) as 入职时间 from Person3;
select datediff(YEAR,Indate,getdate()) as 入职时间,count(*) as 计数 from Person3 group by datediff(YEAR,Indate,getdate());
--去日期的特定部分
select datepart(YEAR,getdate()),datepart(MONTH,getdate());
select datepart(year,Indate) as 入职年份,count(*) as 计数 from Person3 group by datepart(year,Indate);
--类型转换
--字符串转整形，字符串转日期
select cast('123' as int),cast('2008-8-8' as datetime);
select convert(datetime,'2008-8-8'),convert(varchar(50),123);
--空值处理函数
select * from Person3;
select company from Person3;
select isnull(company,'佚名') from Person3;
--CASE判断
create table TempTable(ID int not null,Name nvarchar(20) null,VIP int null);
insert into TempTable(ID,Name,VIP) values (1,'liu',1) ,(2,'chun',2), (3,'bo',3);
select * from TempTable;
select Name,
(
case VIP
when 1 then '普通客户'
when 2 then '会员'
when 3 then 'VIP'
else '未知客户类型' end
)
 as 客户类型 from TempTable;

select * from Person3;
select Name,
(
case 
when Age<20 then '少年'
when Age>20 and Age<30 then '青年'
end
) from Person3
--建立子查询
select * from (select * from Person3)t;
select * from (select * from Person3) as t;