DBMS

DBMS 的英文全称是 DataBase Management System，数据库管理系统，实际上它可以对
多个数据库进行管理，所以你可以理解为 DBMS =多个数据库（DB - DataBase） ＋ 管理程序。

SQL功能

DDL

1. 创建数据库
   CREATE DATABASE **;

2. 删除数据库
   DROP DATABASE **;

3. 创建表
   CREATE TABLE [数据库名称].[表名] (表结构);
CREATE TABLE [table_name](字段名 数据类型，......)

4. 删除表
   DROP TABLE IF EXISTS [table_name];

5. 添加字段
   ALTER TABLE player ADD (age int(11));

6. 修改字段名
   ALTER TABLE player RENAME COLUMN age to player_age

7. 修改字段数据类型
   ALTER TABLE player MODIFY (player_age float(3,1));

8. 删除字段
   ALTER TABLE player DROP COLUMN player_age;

DQL

1. 查询
   SELECT [字段名], [字段名] AS [别名] FROM [表名]

2. 添加常量
   SELECT '[常量]' as platform, [字段名] FROM [表名]

3. 去重
   SELECT DISTINCT [字段名] FROM [表名]

4. 排序 - 升降
   SELECT [字段名], [字段名] FROM [表名] ORDER BY [字段名]
SELECT [字段名], [字段名] FROM [表名] ORDER BY [字段名] DESC

5. 约束
   Mysql
   SELECT [字段名], [字段名] FROM [表名] ORDER BY [字段名] DESC LIMIT [数量]
   Oracle
   SELECT [字段名], [字段名] FROM [表名] WHERE ROWNUM <=5

执行顺序

1. 关键字顺序
   SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

2. 语句执行顺序
   FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

WHERE数据过滤

1. 比较运算符

2. 逻辑运算符

SQL：//例子
SELECT name, role_main, role_assist, hp_max, mp_max, birthdate
FROM heros 
WHERE (role_main IN ('法师', '射手') OR role_assist IN ('法师', '射手')) 
AND DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY (hp_max + mp_max) DESC

3. 通用匹配符%
   SELECT name FROM heros WHERE name LIKE '%太%'

SQL 函数

1. 算术函数

2. 字符串函数

3. 日期函数

例子
SELECT EXTRACT(YEAR FROM '2022-04-13')

4. 转换函数

5. 聚集函数

注意的是 COUNT([字段名])与其他聚集函数会忽略 null 值行，而 COUNT(*) 不会忽略

6. GROUP BY ，HAVING

SELECT COUNT(*) as num, role_main, role_assist FROM heros
WHERE hp_max > 6000 GROUP BY role_main, role_assist 
HAVING num > 5 ORDER BY num DESC

子查询

分类

1. 关联子查询：查询的执行和主查询相关，需耍执行多次；

SELECT player_name, height FROM player 
WHERE height = (SELECT max(height) FROM player)

2. 非关联子查询：子查询的执行与生查询执行无关，只需要执行一次即可；

SELECT player_name, height, team_id FROM player AS a 
WHERE height > (SELECT avg(height) FROM player AS b 
WHERE a.team_id = b.team_id)

关键字

1. EXIST , NO EXIST子查询
   用来判断条件是否满足：
   SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

2. 集合比较子查询

• IN - 判断是否在集合中
  SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
  假如A表比B表大则使用IN子查询效率高，反之EXIST效率高

• ANY - 需要与比较操作符一起使用，与子查询返回的任何值做比较

• ALL - 需要与比较操作符一起使用，与子查询返回的所有值做比较

• SOME - 实际上是ANY的别名，作用相同，一般常使用ANY

92，99连接

1. 交叉连接，笛卡尔积
   连接表所有可能的组合
   92: SELECT * FROM player, team
99: SELECT * FROM player CROSS JOIN team
99: SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3

2. 等值连接
   92 连接
   92: SELECT player_id, a.team_id FROM player as a, team as b WHERE a.team_id = b.team_id
   自然连接
   99: SELECT player_id, team_id FROM player NATURAL JOIN team
   ON 连接
   99：SELECT player_id, player.team_id FROM player JOIN team ON player.team_id = team.team_id
   USING 连接
   99：SELECT player_id, team_id FROM player JOIN team USING(team_id)

3. 非等值连接

92：SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest

99：SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON height BETWEEN h.height_lowest AND h.height_highest

4. 外连接

5. 自连接
   92：SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height
99：SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = '布雷克-格里芬' and a.height < b.height

6. 99 连接的优势

//像for循环嵌套般清晰
SELECT ...
FROM table1
    JOIN table2 ON table1和table2的连接条件
        JOIN table3 ON table2和table3的连接条件

视图

1. 创建

CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

2. 修改

ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

3. 删除

DROP VIEW view_name