数据库与Mysql基础知识

关系型数据库，可以理解为数据以表格的形式出现。MySQL 是一个关系型数据库管理系统。

关系型数据库基础

1. ER图 entity relationship diagram ERD
2. 键
   超键 – 在关系中能唯一标识元组的属性集称
   候选键 – 不含有多余属性的超键称为候选键
   主键 – 用户选作元组标识的候选键称为主键
   外键 --为两个表的数据建立连接
   MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。ref :键详情
3. 范式 Normalization 数据库范式
4. 关系代数 课件L9 L10 关系代数

MYSQL基础

• 支持的数据类型： 数值型、日期时间型、字符型
• 增删改查

DDL: Data Definition Language
允许用户定义数据，即创建表、删除表、修改表结构这些操作。通常,DDL由数据库管理员执行
CREATE ALTER DROP RENAME

创建
CREATE DATABASE 数据库名;
CREATE TABLE table_name (column_name column_type);

	CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
	)ENGINE=InnoDB DEFAULT CHARSET=utf8;
条件： PRIMARY KEY , FOREIGN KEY, UNIQUE, CHECK, NOT NULL
DESCRIBE 表名   //展示表内容
删除
drop database <数据库名>;
DROP TABLE table_name ;
truncate table 表名  //清空表
修改表结构
ALTER TABLE //可新增、删除、修改列
重命名
RENAME // 可修改表名，可修改列名

DML: Data Manipulation Language
为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
INSERT UPDATE DELETE TRUNCATE MERGE

插入
INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
删除
delete from 表名  //清空表
DELETE FROM 表名 WHERE 条件 //删除数据行

修改表内容
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

DQL: Data Query Language
允许用户查询数据，这也是通常最频繁的数据库日常操作。

• 单张表查询

SELECT [DISTINCT]*,column_name,column_name
FROM table_name，...   //表格，可进行join等操作
[WHERE Clause]  //限制条件，不可用聚合函数
[GROUP BY <group by definition>   //分组
[HAVING <expression> [{<operator> <expression>}…]] //可用聚合函数
[ORDER BY <order by definition>]   //排序，可用聚合函数
[LIMIT N] //使用 LIMIT 属性来设定返回的记录数
]

执行顺序
当一个语句中同时含有where、group by 、having及聚集函数时，执行顺序如下：

1. from 确定表
2. where子句查找符合条件的数据；
3. group by 子句对数据进行分组；对group by子句形成的组运行聚集函数计算每一组的值；
4. having 子句去掉不符合条件的组。 having子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
5. select选出需要的列
6. order by 将结果排序
7. limit 取多少个数

WHERE 的关键operator :
 ANY    WHERE id < ANY(1001,1002,1004)
 ALL    WHERE id <> ALL(1001,1002,1004)
 IN     WHERE id IN (1,2,4)
           IN 是 = ANY 的别名，二者相同，但 NOT IN 的别名是 <> ALL 
 LIKE   匹配  WHERE lastname LIKE 'P%'
 	    % 代表任何位char
 	    _ 代表一位char
 AND
 OR
 NOT

DISTINCT 无重复         

GROUP BY
“Group By” 就是根据“By”指定的规则对数据进行分组。**作用是分组统计。**
注意：在select指定的字段要求如下
1. 要么在Group By语句的后面，作为分组的依据
2. 要么出现在聚合函数中，用于统计

聚合函数的用法
Max               求最大，可用于任意data type
Min               求最小，可用于任意data type
Sum               求总和，用于数值型
Avg               求平均，用于数值型
Count            计算总行数，可用于任意data type
MEDIAN            求中位数
STDDEV			  求标准差，用于数值型
VARIANCE          求方差，用于数值型

例子：统计各类别分别有多少个，降序排列
select 类别, sum(数量) as 数量之和
from A
group by 类别
order by 类别 desc

HAVING
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

 1. Having只能用于Group By（分组统计语句中） 
 2. WHERE 是用于在初始表中筛选查询，HAVING用于在WHERE和GROUP
    BY 结果分组中查询  
 3. Having 子句中的每一个元素也必须出现在select列表中
 4. Having语句可以使用聚合函数，而where不使用。
 
例子 我们想要查找总访问量大于 200 的网站，并且 alexa 排名小于 200。
SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;
ref: https://www.runoob.com/sql/sql-having.html

ORDER BY  默认 ASC ， 可改为DESC
          可以有好几列。先排第一列，再排后面的。

多张表查询
join讲解

1.INNER JOIN or JOIN  
内连接是最常见的一种连接，只连接匹配的行。
内连接基本与自然连接相同，不同之处在于自然连接的是同名属性列的连接，而内连接则不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件
Select * from table1 inner join table2 on table1.A=table2.E
table1 JOIN table2 USING 列名
JOIN .. ON 条件
 
2.OUTER JOIN
    2.1LEFT OUTER JOIN or LEFT JOIN
    返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。
    2.2RIGHT OUTER JOIN or RIGHT JOIN
    返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。
    2.3FULL OUTER JOIN or FULL JOIN
    会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替
    
3.NATURAL JOIN
自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。
Select * from table1 natural join table2

4.CROSS JOIN   笛卡尔积

5.SELF JOIN  同一张表join

SET 操作
UNION  
 - 用于合并两个或多个 SELECT 语句的结果集 
 - 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型
 - 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

UNION ALL
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

INTERSECT
交集，mysql无此关键词。但可以模拟操作。
比如使用DISTINCT运算符和INNER JOIN子句。

MINUS
差集，mysql无此关键词。但可以模拟操作。
比如用左连接或者not in 来实现。not in 数据大时效率低下。

其他SQL

subqueries   EXISTS 关键词

TOP N 问题

LIMIT
https://blog.csdn.net/weixin_40844116/article/details/93141543
//有待查询

COMMIT
SAVEPOINT 名字
ROLLBACK TO 名字

ref： https://blog.csdn.net/weter_drop/article/details/84729822