SELECT FAge, COUNT(*) as '数目' FROM T_Employee
GROUP BY FAge;
--SELECT FAge, COUNT(*) FROM T_Employee
--GROUP BY FAge;fault
SELECT FAge, MAX(FSalary) as salary,COUNT(*) as number FROM T_Employee
GROUP BY FAge;
--没有出现在GROUP BY子句中的列是不能放到
--SELECT语句后的列名列表中的(聚合函数除外)
SELECT FAge, COUNT(*) as '数目' FROM T_Employee
GROUP BY FAge
HAVING COUNT(*) > 1;--HAVING是对分组
--后的信息的过滤,能用的列和SELECT中
--能用的列是一样的
--限制结果集的范围->分页
SELECT * FROM T_Employee
ORDER BY FSalary DESC
SELECT TOP 3 * FROM T_Employee--前三行
ORDER BY FSalary DESC
--检索按照工资从高到低排序从第六个人开始的三个人信息
SELECT TOP 3 * FROM T_Employee
WHERE FNumber NOT IN
(
SELECT TOP 5 FNumber FROM T_Employee
ORDER BY FSalary DESC
)
ORDER BY FSalary DESC
--消除重复的行
SELECT DISTINCT FAge FROM T_Employee
--SELECT FAge FROM T_Employee
--UNION
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','Fortifa',46);
SELECT FName, FAge, 0 FROM T_TempEmployee
UNION--上下两个字段数目相同,其类型相容
SELECT FName, FAge, FSalary FROM T_Employee
--如果不同可以补全字段数目
SELECT FName, 0 FROM T_TempEmployee
UNION
SELECT FName, FSalary FROM T_Employee
SELECT FName FROM T_TempEmployee
UNION--删除重复行
SELECT FName FROM T_Employee
SELECT FName FROM T_TempEmployee
UNION ALL--保留重复数据,用UNION是记得带上ALL
SELECT FName FROM T_Employee
SELECT '正式员工最高年龄', MAX(FAge) FROM T_Employee
UNION ALL
SELECT '正式员工最低年龄', MAX(FAge) FROM T_Employee
UNION ALL
SELECT '正式员工最高年龄', MAX(FAge) FROM T_TempEmployee
UNION ALL
SELECT '正式员工最低年龄', MAX(FAge) FROM T_TempEmployee
--查询每位正式员工的信息,包括工号,工资,并且在最后一行加上所有员工工资额合计
SELECT FNumber, FSalary FROM T_Employee
UNION ALL
SELECT '工资合计',SUM(FSalary) FROM T_Employee
--数字函数
--ABS()
--CEILING()舍入为最大整数
--e.g.3.33->4 ; 2.89->3 ; -3.61->-3
--FLOOR()舍入到最小整数
--e.g.3.33->3 ; 2.89->2 ; -3.61->-4
--ROUND()四舍五入
--LEN()计算字符串长度
--LOWER(), UPPER()
--LTRIM()
--RTRIM()
--SUBSTRING(string, start_position, length)
--GETDATE()取得当前时间
--DATEADD(datepart,number,date)date为待计算的日期
--number为增量,datepart为计量单位
--e.g.DATEADD(DAY,3,date)
--DATEPART: year, quarter, month, dayofyear
SELECT DATEADD(YEAR,3,GETDATE())
--DATEDIFF(datepart, startdate, enddate)
--DATEPART(datepart,date)返回一个日期的特定部分
SELECT FName, DATEPART(YEAR, FIndate), DATEDIFF(YEAR, FIndate ,GETDATE()) as '年数'
FROM T_Employee
-按照年限分组,再计算人数
SELECT DATEDIFF(YEAR, FIndate ,GETDATE()) as '入职年数', COUNT(*) as '个数'
FROM T_Employee
GROUP BY DATEDIFF(YEAR, FIndate ,GETDATE())
--CAST(expression AS data_type)
--CONVERT(data_type, expression)
SELECT CAST('123' AS int), CAST('2011-11-28' AS DATETIME),
CONVERT(DATETIME, '2009-01-09'), CONVERT(varchar(50),123)
--ISNULL()
SELECT ISNULL(FName, '佚名') as 姓名 FROM T_Employee
--CASE
--1相当于switch case;
--CASE expression
--WHEN value1 THEN returnValue1
--WHEN value2 THEN returnValue2
--WHEN value3 THEN returnValue3
--ELSE defaultReturnValue
--END
--2范围判断
SELECT FName,
(
CASE FLevel
WHEN 1 THEN '普通客户'
WHEN 2 THEN '会员'
WHEN 3 THEN 'VIP'
ELSE 'unknow'
END
) as 客户类型
FROM tbl_customer
SELECT FName,
(
CASE
WHEN FSalary < 2000 THEN 'low income'
WHEN FSalary >= 2000 AND FSalary < 5000 THEN 'middle income'
ELSE 'high income'
END
) AS income_type
FROM T_Employee
--表中有ABC三列,用SQL语句实现:
--当A列大于B列时选择A列否则选择B列;
--当B列大于C列时选择B列否则选择C列
--SELECT
--(
-- CASE
-- WHEN a > b THEN a
-- ELSE b
-- WHEN b > c THEN b
-- ELSE c
-- END
--)
--FROM T
*int和uniqueidentifier (GUID) 做主键*SQL中表示字符串用‘’单引号*标识字段(自动增长字段),一般把主键设置为标识字段
*GUID产生方法SQL newid()
VS2010 Guid.NewGuid()
*UPDATE tbl_person4 SET nickname=N‘青年人’ WHERE age > 20
*UPDATE tbl_person4 SET nickname=N‘青年人’ WHERE age = 20
*UPDATE tbl_person4 SET nickname=N‘青年人’ WHERE age = 20 or age = 30
INSERT INTO tbl_person4(id,name,age)VALUES(NEWID(), 'yan',26)
UPDATE tbl_person4 SET age=22, name='ye'
CREATE TABLE tbl_person5(id uniqueidentifier, name nvarchar(50), age int)
DROP TABLE tbl_person5
DELETE FROM tbl_person1 WHERE Age=23
DROP TABLE tbl_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',20,3900);
INSERT INTO T_Employee(FNumber, FAge, FSalary)VALUES('IT002',27,2600);
SELECT FName as 姓名, FAge as 年龄, FSalary as 月薪 FROM T_Employee WHERE FSalary > 5000
SELECT @@version查询SQL SERVER版本
SELECT getdate()查询时间
SELECT newid()产生GUID
聚合函数
SELECT COUNT(*) from T_Employee
SELECT MAX(FSalary) from T_Employee
SELECT MIN(FSalary) FROM T_Employee
SELECT AVG(FSalary) FROM T_Employee
SELECT SUM(FAge) FROM T_Employee数据排序
SELECT * FROM T_Employee ORDER BY FAge
SELECT * FROM T_Employee ORDER BY FAge ASC
SELECT * FROM T_Employee ORDER BY FAge DESC
SELECT * FROM T_Employee ORDER BY FAge DESC, FSalary ASC;
SELECT * FROM T_EmployeeWHERE FAge > 23
ORDER BY FAge ASC, FSalary DESC通配符
SELECT * FROM T_EmployeeWHERE FName LIKE '_erry'
SELECT *FROM T_EmployeeWHERE FName LIKE '%n'
SELECT *FROM T_EmployeeWHERE FName LIKE '%t%'空值处理
SELECT * FROM T_EmployeeWHERE FName is NULL
SQL中NULL表示”不知道具体的值为多少“(空集)->无法比较
SELECT NULL + 1 -> NULL;SELECT '' + '123' -> '123';
SELECT NULL + '123' -> NULL;多值匹配
SELECT * FROM T_EmployeeWHERE FAge = 23 or FAge = 28 or FAge = 25;
SELECT * FROM T_EmployeeWHERE FAge in (23,25,28);
SELECT * FROM T_EmployeeWHERE FAge > 20 and FAge < 30
SELECT * FROM T_EmployeeWHERE FAge between 20 and 30
Group by按照年龄相同的分组,计算每组的个数
SELECT FAge, COUNT(*) FROM T_EmployeeGROUP BY FAge;Having子句聚合函数不能出现在WHERE子句
--------------------------------------------
SELECT FAge, COUNT(*) as '数目' FROM T_Employee
GROUP BY FAge;
--SELECT FAge, COUNT(*) FROM T_Employee
--GROUP BY FAge;fault
SELECT FAge, MAX(FSalary) as salary,COUNT(*) as number FROM T_Employee
GROUP BY FAge;
--没有出现在GROUP BY子句中的列是不能放到
--SELECT语句后的列名列表中的(聚合函数除外)
SELECT FAge, COUNT(*) as '数目' FROM T_Employee
GROUP BY FAge
HAVING COUNT(*) > 1;--HAVING是对分组
--后的信息的过滤,能用的列和SELECT中
--能用的列是一样的
--限制结果集的范围->分页
SELECT * FROM T_Employee
ORDER BY FSalary DESC
SELECT TOP 3 * FROM T_Employee--前三行
ORDER BY FSalary DESC
--检索按照工资从高到低排序从第六个人开始的三个人信息
SELECT TOP 3 * FROM T_Employee
WHERE FNumber NOT IN
(
SELECT TOP 5 FNumber FROM T_Employee
ORDER BY FSalary DESC
)
ORDER BY FSalary DESC
--消除重复的行
SELECT DISTINCT FAge FROM T_Employee
--SELECT FAge FROM T_Employee
--UNION
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','Fortifa',46);
SELECT FName, FAge, 0 FROM T_TempEmployee
UNION--上下两个字段数目相同,其类型相容
SELECT FName, FAge, FSalary FROM T_Employee
--如果不同可以补全字段数目
SELECT FName, 0 FROM T_TempEmployee
UNION
SELECT FName, FSalary FROM T_Employee
SELECT FName FROM T_TempEmployee
UNION--删除重复行
SELECT FName FROM T_Employee
SELECT FName FROM T_TempEmployee
UNION ALL--保留重复数据,用UNION是记得带上ALL
SELECT FName FROM T_Employee
SELECT '正式员工最高年龄', MAX(FAge) FROM T_Employee
UNION ALL
SELECT '正式员工最低年龄', MAX(FAge) FROM T_Employee
UNION ALL
SELECT '正式员工最高年龄', MAX(FAge) FROM T_TempEmployee
UNION ALL
SELECT '正式员工最低年龄', MAX(FAge) FROM T_TempEmployee
--查询每位正式员工的信息,包括工号,工资,并且在最后一行加上所有员工工资额合计
SELECT FNumber, FSalary FROM T_Employee
UNION ALL
SELECT '工资合计',SUM(FSalary) FROM T_Employee
--数字函数
--ABS()
--CEILING()舍入为最大整数
--e.g.3.33->4 ; 2.89->3 ; -3.61->-3
--FLOOR()舍入到最小整数
--e.g.3.33->3 ; 2.89->2 ; -3.61->-4
--ROUND()四舍五入
--LEN()计算字符串长度
--LOWER(), UPPER()
--LTRIM()
--RTRIM()
--SUBSTRING(string, start_position, length)
--GETDATE()取得当前时间
--DATEADD(datepart,number,date)date为待计算的日期
--number为增量,datepart为计量单位
--e.g.DATEADD(DAY,3,date)
--DATEPART: year, quarter, month, dayofyear
SELECT DATEADD(YEAR,3,GETDATE())
--DATEDIFF(datepart, startdate, enddate)
--DATEPART(datepart,date)返回一个日期的特定部分
SELECT FName, DATEPART(YEAR, FIndate), DATEDIFF(YEAR, FIndate ,GETDATE()) as '年数'
FROM T_Employee
-按照年限分组,再计算人数
SELECT DATEDIFF(YEAR, FIndate ,GETDATE()) as '入职年数', COUNT(*) as '个数'
FROM T_Employee
GROUP BY DATEDIFF(YEAR, FIndate ,GETDATE())
--CAST(expression AS data_type)
--CONVERT(data_type, expression)
SELECT CAST('123' AS int), CAST('2011-11-28' AS DATETIME),
CONVERT(DATETIME, '2009-01-09'), CONVERT(varchar(50),123)
--ISNULL()
SELECT ISNULL(FName, '佚名') as 姓名 FROM T_Employee
--CASE
--1相当于switch case;
--CASE expression
--WHEN value1 THEN returnValue1
--WHEN value2 THEN returnValue2
--WHEN value3 THEN returnValue3
--ELSE defaultReturnValue
--END
--2范围判断
SELECT FName,
(
CASE FLevel
WHEN 1 THEN '普通客户'
WHEN 2 THEN '会员'
WHEN 3 THEN 'VIP'
ELSE 'unknow'
END
) as 客户类型
FROM tbl_customer
SELECT FName,
(
CASE
WHEN FSalary < 2000 THEN 'low income'
WHEN FSalary >= 2000 AND FSalary < 5000 THEN 'middle income'
ELSE 'high income'
END
) AS income_type
FROM T_Employee
--表中有ABC三列,用SQL语句实现:
--当A列大于B列时选择A列否则选择B列;
--当B列大于C列时选择B列否则选择C列
--SELECT
--(
-- CASE
-- WHEN a > b THEN a
-- ELSE b
-- WHEN b > c THEN b
-- ELSE c
-- END
--)
--FROM T
----------------------
--Rk1 10
--Rk2 20
--Rk3 -30
--Rk4 -10
SELECT FNumber AS 单号,
(
CASE
WHEN FAmount > 0 THEN FAmount
ELSE 0
END
) AS '收入',
(
CASE
WHEN FAmount < 0 THEN ABS(FAmount)
ELSE 0
END
) AS '支出'
FROM tbl_test
-------------------
CREATE TABLE [tbl_scores]
(
[Date] [datetime] NULL,
[Name] [NVARCHAR](50) COLLATE Chinese_PRC_CI_AS NULL,
[Score] [NVARCHAR](50) COLLATE Chinese_PRC_CI_AS NULL
);
INSERT [tbl_scores] ([Date],[Name],[Score]) VALUES (2008-8-8, N'拜仁',N'胜');
INSERT [tbl_scores] ([Date],[Name],[Score]) VALUES (2008-8-9, N'奇才',N'胜');
INSERT [tbl_scores] ([Date],[Name],[Score]) VALUES (2008-8-9, N'湖人',N'胜');
INSERT [tbl_scores] ([Date],[Name],[Score]) VALUES (2008-8-10, N'拜仁',N'负');
INSERT [tbl_scores] ([Date],[Name],[Score]) VALUES (2008-8-8, N'拜仁',N'负');
INSERT [tbl_scores] ([Date],[Name],[Score]) VALUES (2008-8-12, N'奇才',N'胜');
SELECT Name,
(
CASE Score
WHEN N'胜' THEN 1
ELSE 0
END
) AS 胜,
(
CASE Score
WHEN N'负' THEN 1
ELSE 0
END
)AS 负
FROM tbl_scores
---------------------------
SELECT Name,
SUM
(
CASE Score
WHEN N'胜' THEN 1
ELSE 0
END
) AS 胜,
SUM
(
CASE Score
WHEN N'负' THEN 1
ELSE 0
END
)AS 负
FROM tbl_scores
GROUP BY Name
-----------------------
SELECT TOP 5 *,DATEDIFF(HOUR,StartDateTime,EndDateTime)AS 通话时长
FROM tbl_CallRecord
ORDER BY DATEDIFF(HOUR,StartDateTime,EndDateTime) DESC
SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))
FROM tbl_CallRecord
WHERE TelNum LIKE '0%'
SELECT CallerNumber, TelNum, DATEDIFF(MONTH, StartDateTime, GETDATE())
FROM tbl_CallRecord
SELECT * FROM tbl_CallRecord
WHERE DATEDIFF(MONTH,StartDateTime,GETDATE())=2
SELECT TOP 3 CallerNumber
FROM tbl_CallRecord
GROUP BY CallerNumber
ORDER BY SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) DESC
SELECT TOP 3 CallerNumber
FROM tbl_CallRecord
WHERE DATEDIFF(MONTH,StartDateTime,GETDATE())=2
GROUP BY CallerNumber
ORDER BY SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) DESC
SELECT TOP 3 CallerNumber,COUNT(*)
FROM tbl_CallRecord
WHERE DATEDIFF(MONTH,StartDateTime,GETDATE())=2
GROUP BY CallerNumber
ORDER BY COUNT(*) DESC
SELECT CallerNumber, TelNum, DATEDIFF(SECOND,StartDateTime,EndDateTime)
FROM tbl_CallRecord
UNION ALL
SELECT DISTINCT N'汇总',
(
SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))
FROM tbl_CallRecord
WHERE TelNum LIKE '0%'
) AS N'市内号码总时长',
(
SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))
FROM tbl_CallRecord
WHERE TelNum NOT LIKE '0%'
) AS N'长途号码总时长'
FROM tbl_CallRecord
SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))
FROM tbl_CallRecord
WHERE TelNum LIKE '0%'
SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))
FROM tbl_CallRecord
WHERE TelNum NOT LIKE '0%'