数据库基础
- 数据库：保存有组织的数据的容器（一个文件或一组文件）；
- 表：某种特定类型数据的结构化清单；-模式：关于数据库和表的布局及特定信息；
- 列（column）和数据类型（data type）：列是表中一个字段；数据类型：限制存储在列中的数据种类
- 行（row）：一行就是一条数据库记录（reocrd）；
- 主键（primary key）：一列或一组列，唯一区分表中每个行；
任意两行不具有相同的主键值，每行必有一个主键值（主键列不允许为NULL） - SQL（Structured Query Language）:一种专门用来与数据库通信的语言
DBMS 和 MySQL简介
DBMS
-
数据库定义功能（DDL:CREATE\ALTER\DROP）
-
数据库操作功能（DML:select\delete\update\insert）
-
数据库保护功能：保护数据完整性和安全性
-
数据库维护功能：数据库中数据的表结构和数据维护功能
-
数据库经历的三阶段
网状模型、层次模型、关系模型
关系型数据库
基本信息
- 信息：数据加工处理后得到的有用信息
- 实体（Entity）-联系（Relationship）-模型==ER图
-
实体：客观事物在信息世界中称为实体属性：描述实体或者联系的性质或特征的数据项联系：反应事物内部或事物之间的关联集合【一对一，一对多，多对多】
-
关系：一个关系就是一个二维表，通常将一个没有重复行没有重复列的二维表看成一个关系，每个关系都有一个关系名元组：二维表的每一行在关系中成为称为元组，也就是一条记录属性：二维表的每一列在关系中称为属性域：属性的取值范围关键字（主键）：关系中唯一能区分、确定不同元组的属性或属性组合，称为关系的一个关键字关系模式：对关系（表）的描述； 关系名（属性名1，属性名2,…,属性名n）- 关系模式特点：关系必须规范化，属性不可再分；同意关系中不允许出现相同属性名；元组的顺序任意；属性的顺序任意。
- 关系运算：
选择（行）、投影（列）和连接（笛卡尔积组成新的关系）3种
约束和范式
- 完整性约束：
实体完整性（主键不为空）、参照完整性（引用完整性，外键存在）、域完整性（用户定义完整性，限制某个列的取值范围） 范式：关系模式要满足的条件称为规范化形式，简称范式-
- 目的：消除存储异常，减少数据冗余、保证数据的完整性和存储效率，一般规范为3NF即可
第一范式 1NF：所有属性为简单属性，每个属性不可再分【无重复的列】第二范式 2NF：R满足1NF，且每一个非主键字段完全依赖主键，则R满足第二范式第三范式 3NF：R满足2NF，且非主键字段之间不存在依赖关系（在员工表中，出现部门号之后就不能出现部门名）- 一个基本的关系型数据库满足1NF，一个完整的关系型数据库满足3NF
- E-R图：实体联系图，提供表示实体类型、属性和联系的方法，用来描述现实世界的概念模型；
-
矩形实体型；椭圆实体属性；菱形实体间的关系；直线连接写m、n、1表示多对多还是一对一还是多对一
MySQL
- 一种数据库管理系统DBMS（开源免费使用、性能执行快、可信赖、简单安装和使用）
- 常用命令
mysql -u username -p -h myserver -P 3306p密码 P端口号 默认3306
quit 或 exit退出命令行实用程序
SHOW dbname登录账户后展示已创建的数据库
USE dbname登录账户后打开某个数据库
SHOW tables进入某个数据库后查看已创建的表
SQL
SELECT
相关操作符
-
DISTINCT：Distinct应用于所有列而不是其前置的列
SELECT DISTINCT id, price：除非id和price都不同，否则所有列将被检出 -
LIMIT：限制检索的条数
LIMIT 3,4 == LIMIT 4 OFFSET 3：限制4行，从第三行开始（有第0行） -
WHERE : 过滤数据，放在ORDER BY 前
BEYWEEN x1 AND x2 和 >=x1 <=x2：BETWEEN 包含指定的开始值和结束值；
<、> 、=：注意是=不是==；<> 和 !=：不相等；IS NULL：空值检查；IN (x1,x2,x3)在这些值中
AND 和 OR：并列多个条件，AND 优先级比 OR 高 （乘法比加法高）
NOT：支持 NOT IN、BTEWEEN、EXISTS -
- LIKE 操作符 + 通配符 ： 通过配置是否区分大小写
%：任意字符出现任意次数；jet%：表示jet开头的数据；
_：匹配任意单个字符；
注意：1）其他操作符能达到的目的，不用通配符；2）使用通配符时，不要置于搜索模式开始处；放到开始处是最慢的
- LIKE 操作符 + 通配符 ： 通过配置是否区分大小写
-
REGEXP 操作符+ 正则表达式
.：匹配单个字符
BINARY：加该操作符，则不区分大小写REGEXP BINARY 'jet'
|相当于or，连接多个正则表达式；REGEXP '1001|1002'
\\匹配特殊字符；\\.匹配 .
[]^-相当于or、否定、指定范围；
-REGEXP '[123]001'匹配1001；2001；3001；[^123]匹配除123以外的所有字符；[1-3] [a-z]分别匹配1-3，a-- 匹配字符类
[:alnum:]匹配字母和数字，相当于[a-zA-Z0-9]…还有很多种 P5 - 重复元字符：跟在要匹配的字符后面
元字符 说明 * 0个或多个匹配 + 1个或多个匹配，相当于{1，} ？ 0个或1一个匹配，相当于{0，1} {n} 指定数目的匹配 {n,} 不少于n个的匹配 {n,m} 匹配数目的范围 -
[[:digits:]]{4}匹配任意4个数字 -
stricks?strick或者stricks -
定位元字符符
元字符 说明 ^ 文本开始 $ 文本结束 [[:<:]] 词的开始 [[:>:] 词的结尾 ^[0-9a-zA-Z]数字和字母开头的字符串；
- 匹配字符类
-
LIKE VS REGEXP
1）LIKE 匹配整个串 ；REGEXP匹配子串
2）WHERE name LIKE '1001'：不会返回数据，也不会返回name=1001的数据，因为没有使用通配符
WHERE name REGEXP '1001'：返回name=1001的数据，因为没有符号的正则表达式也有效 -
ORDER BY：默认ASC升序，DESC 降序
ORDER BY id, price：先id升序排列；id相同，按price升序排列 -
聚集函数，
AVG() MAX() MIN() COUNT() SUM()用在SELECT语句；默认为ALL，若不计算相同值，用DISTINCT；
SELECT COUNT(*) FROM t1：不忽略为NULL的值，COUNT(c1)：会忽略c1为NULL的列
SELECT AVG(DISTINCT price) FROM t1计算不同price的平均值 -
GROUP BY数据分组- GROUP BY子句中的列必须是能索引的列，不能使用聚集函数
- SELECT 子句中的列必须都出现在GROUP BY子句中，SELECT子句中包含聚集函数的列除外
- 如果分组具有NULL值，NULL作为一个分组返回，多个NULL作为一个分组
HAVING过滤 GROUP BY分组
-
SELECT 子句顺序
SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT -
常用函数
-
- 拼接字段和算数运算符
| 操作符 | 说明 |示例|
|–|–|–|
| Concat | 拼接字段 | SELECT Concat(vend_name,‘(’,vend_country,‘)’ as vend_title)
| + - * /|计算字段，加减乘除
- 拼接字段和算数运算符
-
文本处理函数
-
|日期和时间处理函数
-
数值处理函数
多表查询=子查询+内外连接
子查询
- 单行单列（一个值）
<、>、>=、<=、!=、= - 多行单列（一串数据结构相同的值）
（IN,ALL,ANY），其中ANY ALL 需结合 <、>、>=、<=、!=、=、<> -
>ANY<ANY=ANY||| 错误用法：<>ANY
>ALL<ALL<>ALL||| 错误用法：=ALL- 也可以用
(dept_no id) = (SELECT dept_no,id WHERE...) - 多行多列（表）
内外连接
join(联合连接) 和union(组合查询)-
join是两张表做交连后里面条件相同的部分记录产生一个记录集，
union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集 。union的两张表需要列数和字段完全匹配 与Intersect相同union自动去重union all不去重
- 内连接和外连接
-
- 内连接
[inner] joininner 可忽略
- 内连接
-
- 外连接 = 左右连接+全连接
left/right [outer] joinouter可忽略 +left join... union right join...全连接
- 外连接 = 左右连接+全连接
in VS exists
- exits的使用：exits 只关心 是否返回行，不关心具体的返回数据，返回有数据即为True
例子：没有下过订单的客户 (where 条件后为True则被筛选出来）SELECT * FROM customer c WHERE not exists (SELECT customer_id FROM order o WHERE c.customer_id=o.customer_id); in 和 exits-
- in后面放数据量小的表，exits后面放数据量大的表
- 使用 in ，两表执行顺序是先查 B 表，再查 A 表
select * from A where id in (select id from B) - 使用exits，使用 exists，两表执行顺序是先查 A 表，再查 B 表
select * from A where exists (select 1 from B where B.id = A.id)
窗口函数
窗口函数是一种分析型的 OLAP（Online Anallytical Processing，联机分析处理）函数，意思是对数据库数据进行实时分析处理。
-
执行顺序：在执行完select之后，在所得结果集之上进行partition
-
<函数名称> OVER ([PARTITION BY <列名1>, <列名2>, ...] ORDER BY <排序列> [ASC|DESC] ROWS <行范围指定>) -
- 函数
- 聚合函数
row_num()、rank()、dense_rank()1234\1223\1223first_value、last_value取分组内，排序后，截止到当前行第一个值\最后一个值
- PARTITION BY colx (分组语句，类似group by)
- ORDER BY coly
- ROWS 行选择语句
rows unbounded preceding当前行至第一行rows x following/preceding当前行到当前行往后/前x行rows between x preceding and y following从前x到后y行rows between unbounded preceding and unbounded following从第一行到最后一行
- 函数
INSERT
- 插入完整数据，忽略列名
INSERT INTO table1 VALUES(NULL,'Pop E.','90046','USA',NULL,NULL); - 插入部分列数据，写上对应列名
INSERT INTO table1(name,addr,zip,country) VALUES('Pop E.','90046','USA'); - 插入多个行
INSERT INTO table1(name,addr,zip,country) VALUES('Pop E.','90046','USA'), ('Martian M.','11213','USA');
UPDATE、DELETE
- 更新
UPDATE table1 SET name = 'new name', zip = 'new zip' WHERE id =10005; - 删除
DELETE FROM table1 WHERE id =10005;
表操作
- 创建表
-
NOT NULL,AUTO_INCREMENT:只允许一个自增列；DEFUALT,PRIMARY KEY(),ENGINE =
--注释 /* 多行注释 */ CREATE TABLE table1 ( id int NOT NULL AUTO_INCREMENT, name char(50) NOT NULL DEFAULT 'JACK', countrt char(50) NULL, PRIMARY KEY(id) )ENGINE = InnoDB; - 更新表
ALTER TABLE table1 ADD c2 int, --新增 ADD CONSTRAINT xxx, Drop c3, -- 删除列 CHANGE c_old c_new int. -- 修改列名 MODIFY c4， int -- 修改数据类型 FOREIGN KEY (id) REFERENCES table2 (id) - 重命名表
RENAME TABLE new_table1 TO table1, new_table2 TO table2, new_table3 TO table3; - 删除表
DROP TABLE tables
视图
为什么使用视图
- 虚拟的表，包含的不是数据，而是根据需要检索数据的查询
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的组成部分而不是整个表
- 保护数据，给予用户表的特定访问权限而不是整个表的访问权限
- 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据
视图的规则和限制
- 唯一命名
- 可创建的视图数目没有限制
- 创建视图需要有相应的访问权限
- 视图可以嵌套（可以从其他视图查询数据得到视图）和联结（可以编写联结视图与表的SELECT语句）
- ORDER BY可以用在视图中，但如果从该视图中查询的SELECT语句含有ORDER BY，则会覆盖视图的ORDER BY 语句？
- 视图不能索引，也不能有关联的触发器和默认值
视图的使用
- 创建视图
CREATE VIEW view_name SELECT id FROM table1 - 删除视图
DROP VIEW view_name - 更新视图 可以先drop再创建，也可使用下列语句
CREATE OR REPLACE VIEW view - 查看创建视图的语句
SHOW CREATE VIEW view_name
存储过程
-
创建存储过程
CREATE PROCEDURE procedure_name( In id INT, //输入参数 OUT p1 DECIMAL(8,2), //输出变量，也可不输出 OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2), ) BEGIN SELECT MIN(price) INTO p1 FROM table1 SELECT MAX(price) INTO ph FROM table1 SELECT AVG(price) INTO pa FROM table1 END -
调用：执行存储过程
CALL PROCEDURE procedure_name(1000, //传入输入参数 @price1, //传入参数接受结果 @price2, @price3); SELECT @price1; -
删除存储过程
DROP PROCEDURE procedure_name -
更新存储过程
CREATE OR REPLACE VIEW view -
查看创建存储过程的语句
SHOW CREATE PROCEDURE procedure_name
游标
- 游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而实被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
- 使用游标
-
- MySQL5中只能用于存储过程中（或函数）中，其他的DBMS不一定；
- 声明游标 DECLARE
- 声明后，必须打开游标使用
- 对于含有数据的游标，根据需要取出（检索）游标
- 结束游标使用后，必须关闭游标
CREATE PROCEDURE procedurename()
BEGIN
DECLARE o INT; // 声明局部变量
DECLARE cursorname CURSOR // 声明游标
FOR
SELECT num FROM orders;
OPEN cursorname;
REPEAT // 循环将查询到的num赋值给局部变量o
FETCH num TO o;
UNTIL done END REPEAT;
CLOSE cursorname;
END
触发器
触发器使用注意
- 唯一的触发器名
- 触发器关联表
- 触发器响应的活动（DELETE、UPDATE、INSERT）
- 触发器何时执行(AFTER、BEFORE)
INSERT 触发器
NEW虚拟表，访问插入的行- BEFORRE 触发器中，NEW的值可进行更改
- 对于AUTO_INCREMENT列，插入前包含0，插入后自动包含自动生成的值
CREATE TRIGGER trigger_name AFTER INSERT ON table1
FOR EACH ROW SELECT NEW.ordernum;
DELETE 触发器
OLD虚拟表，访问被删除的行- OLD表中的值只读，不可更新
CREATE TRIGGER trigger_name BEFORE DELETE ON table1
FOR EACH ROW
BEGIN
INSERT INTO tabel2(name,id)
VALUES (OLD.name,OLD.id);
END;
UPDATE触发器
NEW虚拟表，访问更新后的行，BEFORE触发器中可更改需要UPDATE的行的数据OLD虚拟表，访问被更新的行，只读
CREATE TRIGGER trigger_name BEFORE UPDATE ON table1
FOR EACH ROW SET NEW.country = upper(NEW.country);
174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
