SQL语句基础

1、char(n)不足长度N的部分用空格填充
2、常用字段类型：bit(可选值0或1)、datetime、int、varchar、nvarchar(支持含中文)
3、SQL语句中字符串用单引号。
4、创建表：Create Table T_Person1(Id int Not NULL primary key,Name nvarchar(50),Age int)  
5、删除表：Drop Table T_Person1
6、修改表：Alter Table T_Person1 Add Sex nchar(10);
7、查询语句：Select 字段 From 表 Where 字段
8、插入语句：Insert Into 表(字段) Values(数据)
9、删除语句：Delete From 表 Where 字段
10、更新语句：Update 表 Set 字段 Where 字段
11、分组查询：Group By 字段 Having 分组参数
12、排序查询：Order By 字段 ASC 或 DESC
13、模糊查询：LIKE(_xxx) LIKE(%x%)
14、处理重复数据：Select  Distinct 字段 From 表
15、联合表语句：Union All
数字函数：
16、求绝对值：ABS()
17、舍入到最大整数：CEILING()
18、舍入到最小整数：FLOOR()
19、四舍五入：ROUND()
字符串函数：
20、计算字符串长度：LEN()
21、转小写和转大写：LOWER() UPPER()
22、去掉字符串左侧空格：LTRIM()
23、去掉字符串右侧空格：RTRIM()
24、截取字符串：SUBSTRING()
日期函数：
25、获取当前日期：GETDATE()
26、计算增加以后的日期：DATEADD(datepart,number,date)
27、计算两个日期之间的差额：DATEDIFF(datepart,startdate,enddate)
28、返回日期的特定部分：DATEPART(datepart,date)
类型转换函数：
29、CAST(expression AS date_type)
30、CONVERT(date_type,expression)
空值处理函数：
31、ISNULL(expression,values)
32、*表连接join：select a.字段,b.字段 from  表1 as a join 表2 as b on a.CustomerId = b.ID
33、row_number() over(order by id  desc) as rumber row_number() 必须跟开窗函数一起使用。
临时表和表变量：
34、Create Table #Person 创建临时表
35、declare @VarT Table(Id int, Name nvarchar(10)) 
    INSERT INTO @VarT VALUES(10,'zhangshan')
    SELECT * FROM @VarT
36、declare @变量名 数据类型
    set @变量名=值
    select @变量名=值 from 表
存储过程：
37、create proc sp_PersonAdd(存储过程名)
    @参数1 数据类型=默认值,
    @参数2 数据类型=默认值 output
    as
    SQL语句
    
EXEC 存储过程名 参数
38、SQL插入语句，返回ID值: select @@identity 或 output inserted.Id
 --SQL插入语句，返回ID值--通过output 函数返回插入后某个字段值
insert into Person2(Name,Age) output inserted.Id values('测试',12)
--SQL插入语句，返回ID值--@@identity 返回自增ID值
insert into Person2(Name,Age) values('测试',13);select @@identity;

39、partition by
select A,B,row_number()over(partition by b order by A)as e from T_D
select * from T_D order by min(A) over(partition by B)
--=============================================================================================

SELECT * FROM T_Person1 
SELECT COUNT(*) FROM T_Person1
SELECT MAX(Age) FROM T_Person1
SELECT MIN(Age) FROM T_Person1
SELECT AVG(Age) FROM T_Person1
SELECT SUM(Age) FROM T_Person1
SELECT TOP 10 Id,Name,Age FROM dbo.T_Person1 
SELECT TOP 3 * FROM T_Person1 ORDER BY Age ASC
SELECT * FROM T_Person1 ORDER BY Age DESC
SELECT * FROM T_Person1 WHERE Sex IS NULL
SELECT * FROM T_Person1 WHERE Age=30 OR Age=26
SELECT * FROM T_Person1 WHERE Age IN(26,30,31)
SELECT Age,COUNT(*) FROM T_Person1 WHERE Sex='女' GROUP BY Age
SELECT Age,COUNT(*) FROM T_Person1 GROUP BY Age HAVING COUNT(*)>1
SELECT TOP 3 Name FROM T_Person1 ORDER BY Age DESC

SELECT TOP 5 * FROM T_Person1 
WHERE Name NOT IN(SELECT TOP 3 Name FROM T_Person1 ORDER BY Age DESC) 
ORDER BY Age DESC

INSERT INTO T_Person1(Name,Age) VALUES('凤姐',30)

INSERT INTO mydb.dbo.T_Person1(Id,Name,Age) VALUES(1,'王宝强',30)
DELETE FROM mydb.dbo.T_Person1 WHERE Name='王宝强'
UPDATE dbo.T_Person1 SET Name='傻根' WHERE Id=1

UPDATE T_Person1 SET Sex=N'女' WHERE Name LIKE('王菲%')
UPDATE T_Person1 SET Sex=N'男' WHERE Age>39 and Age<53

ALTER TABLE T_Person1 ADD Area nvarchar(50)
ALTER TABLE T_Person1 ADD Profession nvarchar(50)
SELECT * FROM T_Person1 
UPDATE T_Person1 SET Area=N'重庆',Profession=N'修脚工' WHERE Id=13
SELECT DISTINCT Profession FROM T_Person1
SELECT DISTINCT Area,Profession FROM T_Person1

Create Table T_Employee(FNumber varchar(20),FName varchar(20),FAge int, FSalary numeric(10,2), primary key(FNumber));
Insert Into T_Employee(FNumber,FName,FAge,FSalary) Values('DEV001','Tom',25,8300);
Insert Into T_Employee(FNumber,FName,FAge,FSalary) Values('DEV002','Jerry',28,2300.80);
Insert Into T_Employee(FNumber,FName,FAge,FSalary) Values('SALES001','John',23,5000);
Insert Into T_Employee(FNumber,FName,FAge,FSalary) Values('SALES002','Kerry',28,6200);
Insert Into T_Employee(FNumber,FName,FAge,FSalary) Values('SALES003','Stone',22,1200);
Insert Into T_Employee(FNumber,FName,FAge,FSalary) Values('HR001','Jane',23,2200.88);
Insert Into T_Employee(FNumber,FName,FAge,FSalary) Values('HR002','Tina',25,5200.36);
Insert Into T_Employee(FNumber,FName,FAge,FSalary) Values('IT001','Smith',28,3900);
Insert Into T_Employee(FNumber,FName,FAge,FSalary) Values('IT002',NULL,27,2800);

SELECT * From T_Employee Order By FAge DESC;

SELECT TOP 3 * FROM T_Employee 
WHERE FNumber NOT IN(SELECT TOP 5 FNumber FROM T_Employee ORDER BY FSalary) 
ORDER BY FSalary;

ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);
ALTER TABLE T_Employee ADD FDepartment VARCHAR(20);
UPDATE T_Employee SET FSubCompany='BeiJing', FDepartment='Development' WHERE FNumber='DEV001';
UPDATE T_Employee SET FSubCompany='ShenZhen', FDepartment='Development' WHERE FNumber='DEV002';
UPDATE T_Employee SET FSubCompany='BeiJing', FDepartment='HumanResource' WHERE FNumber='HR001';
UPDATE T_Employee SET FSubCompany='BeiJing', FDepartment='HumanResource' WHERE FNumber='HR002';
UPDATE T_Employee SET FSubCompany='BeiJing', FDepartment='IntoTech' WHERE FNumber='IT001';
UPDATE T_Employee SET FSubCompany='ShenZhen', FDepartment='IntoTech' WHERE FNumber='IT002';
UPDATE T_Employee SET FSubCompany='BeiJing', FDepartment='Sales' WHERE FNumber='SALES001';
UPDATE T_Employee SET FSubCompany='BeiJing', FDepartment='Sales' WHERE FNumber='SALES002';
UPDATE T_Employee SET FSubCompany='ShenZhen', FDepartment='Sales' WHERE FNumber='SALES003';

SELECT DISTINCT FDepartment FROM T_Employee;

CREATE TABLE T_TempEmployee(FIdCardNumber VARCHAR(20),FName VARCHAR(20),FAge INT, PRIMARY KEY(FIdCardNumber));
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('1234567890121','Sarani',33);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('1234567890122','Tom',26);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('1234567890123','Yalaha',38);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('1234567890124','Tina',26);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('1234567890125','Konkaya',29);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('1234567890126','Fotifa',46);

SELECT FName, FAge,FSalary FROM T_Employee
UNION ALL
SELECT FName, FAge,0 FROM T_TempEmployee

SELECT ISNULL(FName,'佚名') FROM T_Employee;

SELECT FName,
(
CASE FLevel 
when 0 then '临时工'
when 1 then '普通员工'
when 2 then '高级员工'
when 3 then '部门经理'
else '特殊职位'
END
)
FROM T_TempEmployee
SELECT FName,
(
case
when FSalary<2000 then '低收入'
when FSalary>=2000 and FSalary<5000 then '中等收入'
else '高收入'
end
) as '收入水平'
FROM T_Employee

Drop Table T_Employee;

SELECT * FROM
(
SELECT *,row_number() over(order by FSalary) as number FROM T_Employee
) as tbl 
where tbl.number between 5*2+1 and 5*3