-------插入数据------------------------------------------------------------------------------
INSERT INTO T_Person(FName,FAge,FRemark) values('Tom',18,'USA')
INSERT INTO T_Debt (FNumber,FAmount, FPerson) VALUES ('2',300, 'Jim')
---------删除数据----------------------------------------------------------------------------
DELETE FROM T_Person; --删除一个表中的数据
DELETE FROM T_Person WHERE FAge > 20 or FRemark = 'Mars' --带条件的删除
DROP TABLE T_Debt; --完整删除表
DROP TABLE T_Person; --完整删除表
-----------------------查询:索引------------------------------------------------------------
--查询
SELECT * FROM T_Employee --查询T_Employee表中所有数据
SELECT FNumber FROM T_Employee --检索出需要的一列数据
SELECT FName,FAge FROM T_Employee --检索出多列数据
SELECT FName AS Name,FAge AS Age FROM T_Employee --查询出来后,指定列别名
SELECT FName FROM T_Employee WHERE FSalary<5000 --带条件的查询
SELECT * FROM T_Employee WHERE FSalary<5000 OR FAge>25
--聚合函数
SELECT MAX(FSalary) FROM T_Employee WHERE FAge>25 --查询年龄大于 25 岁的员工的最高工资
SELECT MAX(FSalary) as MAX_SALARY FROM T_Employee WHERE FAge>25 --聚合函数的计算结果指定一个别名
SELECT AVG(FAge) FROM T_Employee WHERE FSalary>3800 --统计一下工资大于 3800 元的员工的平均年龄
SELECT SUM(FSalary) FROM T_Employee --统计一下公司每个月应支出工资总额
SELECT MIN(FSalary),MAX(FSalary) FROM T_Employee --统计公司的最低工资和最高工资
SELECT COUNT(*),COUNT(FNumber) FROM T_Employee --统计记录数量
SELECT COUNT(*),COUNT(FNumber),COUNT(FName) FROM T_Employee-- 为null时,不作查询
--排序
SELECT * FROM T_Employee ORDER BY FAge ASC --以FAge升序排列(默认)
SELECT * FROM T_Employee ORDER BY FAge DESC --以FAge降序排列
SELECT * FROM T_Employee ORDER BY FAge DESC,FSalary DESC --以多个字段来排序
SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC --ORDER BY 与WHERE 一起使用
--通配符过滤 LIKE
SELECT * FROM T_Employee WHERE FName LIKE '_erry' --单字符匹配
SELECT * FROM T_Employee WHERE FName LIKE '__n_'
SELECT * FROM T_Employee WHERE FName LIKE 'T%' --多字符匹配
SELECT * FROM T_Employee WHERE FName LIKE '%n%'
SELECT * FROM T_Employee WHERE FName LIKE '[SJ]%' --集合匹配
SELECT * FROM T_Employee WHERE FName LIKE '[^SJ]%' --^SJ,不能存在SJ开头
SELECT * FROM T_Employee WHERE FName LIKE 'S%' OR FName LIKE 'J%' --
SELECT * FROM T_Employee WHERE NOT(FName LIKE 'S%') AND NOT(FName LIKE 'J%')
--空值检测
SELECT * FROM T_Employee WHERE FNAME IS NULL
--反义运算符
SELECT * FROM T_Employee WHERE FAge!=22 AND FSALARY!<2000
SELECT * FROM T_Employee WHERE NOT(FAge=22) AND NOT(FSALARY<2000)
--多值检测
SELECT FAge,FNumber,FName FROM T_Employee WHERE FAge=23 OR FAge=25 OR FAge=28
SELECT FAge,FNumber,FName FROM T_Employee WHERE FAge IN (23,25,28)
--范围值检测
SELECT * FROM T_Employee WHERE FAGE>=23 AND FAGE <=27
SELECT * FROM T_Employee WHERE FAGE BETWEEN 23 AND 27
--总结:
---where 1=1影响索引
---不等于:<>
------------数据分组GROUP BY--------------------------------------------------------------------------------------------
--GROUP BY
SELECT FAge FROM T_Employee GROUP BY FAge
SELECT FAge,AVG(FSalary) FROM T_Employee GROUP BY FAge --以年龄分组后,统计他们平均工资
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge --年龄有几组,每组有多少人
--HAVING 语句
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*)>1
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*) =1 OR COUNT(*)=3
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*) IN (1,3)
--限制结果集行数
select top 5 * 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
--ROW_NUMBER()行号
SELECT ROW_NUMBER() OVER(ORDER BY FSalary),FNumber,FName,FSalary,FAge FROM T_Employee
SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC),FNumber,FName,FSalary,FAge FROM T_Employee
WHERE (ROW_NUMBER() OVER(ORDER BY FSalary DESC))>=3 AND (ROW_NUMBER() OVER(ORDER BY FSalary DESC))<=5