MySQL

---------------创建表------------------------------------------------------------------------

CREATE TABLE T_Person (FName VARCHAR(20),FAge INT,FRemark VARCHAR(20),PRIMARY
KEY (FName)); ---创建一个T_Person表，FName为主键

-------创建一个T_Debt表，FNumber为主键，把FPerson与表T_Person中的FName建立关联--
CREATE TABLE T_Debt (FNumber VARCHAR(20),FAmount NUMERIC(10,2) NOT NULL,
FPerson VARCHAR(20),PRIMARY KEY (FNumber),
FOREIGN KEY (FPerson) REFERENCES T_Person(FName)) ;

--总结
--设置主键：PRIMARY KEY (FName)
--手动设置关联时：在非主键的表中把主键表设置为其它表的主键


-------插入数据------------------------------------------------------------------------------
INSERT INTO T_Person(FName,FAge,FRemark) values('Tom',18,'USA')
INSERT INTO T_Person(FName,FAge,FRemark) values('Tom',18,'USA'),('Tom',18,'USA');
INSERT INTO T_Debt (FNumber,FAmount, FPerson) VALUES ('2',300, 'Jim')

--总结
---1.主键的值不能重复
---2.该字段为可空时，可以省略不插入数据
---3.如果表中一个字段与外表有关联时，插入时该字段的值必须出现在关联表的主键中

--------更新数据-----------------------------------------------------------------------------
UPDATE T_Person SET FRemark = 'SuperMan' --更新单行

UPDATE T_Person  SET FRemark = 'Sonic', FAge=25 --更新多行

UPDATE T_Person  SET FAge = 12 WHERE FNAME='Tom' ---带条件的更新

--总结:
---1.不能把非空字段更新为null
---2.主键不能更新为已存在的主键
---3.外键不能更新为主键中不存在的数据

---------删除数据----------------------------------------------------------------------------
DELETE FROM T_Person;  --删除一个表中的数据
DELETE FROM T_Person WHERE FAge > 20 or FRemark = 'Mars' --带条件的删除

DROP TABLE T_Debt; --完整删除表
DROP TABLE T_Person; --完整删除表

--总结:
---1.DELETE为删除表的数据，而DROP为删除整个表，包括表结构
---2.有外键关联的表，必须先删除外键表数据



---------------------------------------------------------------------------------------------
---上面：增删改
---------------------------------------------------------------------------------------------



-----------------------查询：索引------------------------------------------------------------
--查询
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

--总结：
---1.分组语句必须和聚合函数一起使用
---2.GROUP BY子句必须放到WHERE语句的之后
---3.HAVING语句中不能包含未分组的列名