当前位置：首页 > article >正文

数据库和MySQL

article 2024/12/29 12:29:21

ER图

实体（矩形）：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。

属性（椭圆）：实体拥有的属性。

联系（菱形）：实体与实体之间的关系。

函数依赖

函数依赖：若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y。

部分函数依赖：如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0→Y，则称 Y 对 X 部分函数依赖。

完全函数依赖：若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。

传递函数依赖：在关系模式 R(U)中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，（X∪Y）∩Z=空集合，则称 Z 传递函数依赖(transitive functional dependency) 于 X。

数据库范式

第一范式：属性不可再分。

第二范式：在第一范式的基础之上，消除了非主属性对于码的部分函数依赖。

第三范式：在第二范式的基础之上，消除了非主属性对于码的传递函数依赖。

drop、delete、truncate区别

drop：将表删除掉。

truncate：清空表中的数据。

delete：删除某一（多）行数据。

SQL和NoSQL的区别

SQL语句分类

数据定义语言（DDL）：定义数据库对象。create、alter、drop。

数据查询语言（DQL）：查询数据库。select。

数据操纵语言（DML）：访问数据，以读写数据库为主。insert、update、delete。

数据控制语言（DCL）：控制用户的访问权限。grant、revoke。

事务控制语言（TCL）：管理数据库中的事务。commit、rollback。

DDL语句

数据库：

# 创建数据库
CREATE DATABASE test;

# 删除数据库
DROP DATABASE test;

# 选择数据库
USE test;

数据表：

# 普通创建
CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
  password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'
) COMMENT='用户表';

# 根据已有的表创建新表
CREATE TABLE vip_user AS SELECT * FROM user;

# 删除表
DROP TABLE user;

# 修改数据表
-- 添加列
ALTER TABLE user ADD age int(3);
-- 删除列
ALTER TABLE user DROP COLUMN age;
-- 修改列
ALTER TABLE `user` MODIFY COLUMN age tinyint;
-- 添加主键
ALTER TABLE user ADD PRIMARY KEY (id);
-- 删除主键
ALTER TABLE user DROP PRIMARY KEY;

视图：

视图是基于 SQL 语句的结果集的可视化的表。视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。

视图的作用：简化复杂的 SQL 操作，比如复杂的联结；只使用实际表的一部分数据；通过只给用户访问视图的权限，保证数据的安全性；更改数据格式和表示。

# 创建视图
CREATE VIEW top_10_user_view AS SELECT id, username FROM user WHERE id < 10;

# 删除视图
DROP VIEW top_10_user_view;

索引：

是一种用于快速查询和检索数据的数据结构，本质上可以看成是一种排序好的数据结构。

# 创建索引
CREATE INDEX user_index ON user (id);

# 添加索引
ALTER table user ADD INDEX user_index(id);

# 创建唯一索引
CREATE UNIQUE INDEX user_index ON user (id);

# 删除索引
ALTER TABLE user DROP INDEX user_index;

约束：

用于规定表中的数据规则。约束可以在创建表时规定（create table），也可以在表创建之后规定（alter table）。

约束类型：not null（某列不能存储 NULL 值）、unique（某列的每行必须有唯一的值）、primary key（前面两者的结合，确保某列有唯一标识）、foreign key（保证一个表中的数据匹配另一个表中的值的参照完整性）、check（保证列中的值符合指定的条件）、default（规定没有给列赋值时的默认值）。

# 创建表时使用约束条件
CREATE TABLE Users (
  Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT '用户名',
  Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '邮箱地址',
  Enabled TINYINT(4) DEFAULT NULL COMMENT '是否有效',
  PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

DQL语句

查询

DML语句

插入数据：

insert into 语句用于向表中插入新记录。

# 插入一行
INSERT INTO user VALUES (10, 'root', 'root', 'xxxx@163.com');

# 插入多行
INSERT INTO user VALUES (10, 'root', 'root', 'xxxx@163.com'), (12, 'user1', 'user1', 'xxxx@163.com'), (18, 'user2', 'user2', 'xxxx@163.com');

# 插入行的一部分
INSERT INTO user(username, password, email) VALUES ('admin', 'admin', 'xxxx@163.com');

# 插入查询出来的数据
INSERT INTO user(username) SELECT name FROM account;

更新数据：

update 语句用于更新表中的记录。

# 更新表中的记录
UPDATE user SET username='robot', password='robot' WHERE username = 'root';

删除数据：

delete 语句用于删除表中的记录；

truncate table 可以清空表，属于DDL语法。

# 删除表中指定数据
DELETE FROM user WHERE username = 'robot';

# 清空表中的数据
TRUNCATE TABLE user;

查询数据：

select 语句用于从数据库中查询数据；

distinct 用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同；

limit 限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

# 查询单列
SELECT prod_name FROM products;

# 查询多列
SELECT prod_id, prod_name, prod_price FROM products;

# 查询所有列
SELECT * FROM products;

# 查询不同的值
SELECT DISTINCT vend_id FROM products;

# 限制查询结果（第一个参数为起始行，第二个参数为返回的总行数）
-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3;

排序：

order by 用于对结果集按照一个列或者多个列进行排序，默认升序；对多列排序的时候，先排序的列放前面，后排序的列放后面。并且，不同的列可以有不同的排序规则。

# 排序
SELECT * FROM products ORDER BY prod_price DESC, prod_name ASC;

分组：

group by 将记录分组到汇总行中；为每个组返回一个记录；通常还涉及聚合 count、max、sum、avg 等；可以按一列或多列进行分组；按分组字段进行排序后，可以以汇总字段来进行排序；

having 用于对汇总的 group by 结果进行过滤；一般都是和 group by 连用； where 和 having 可以在相同的查询中；

where 过滤指定的行，后面不能加聚合函数（分组函数），在 group by 之前； having 过滤分组，一般和 group by 连用，不能单独使用，在 group by 之后。

# 分组
SELECT cust_name, COUNT(cust_address) AS addr_num FROM Customers GROUP BY cust_name;

# 分组后排序
SELECT cust_name, COUNT(cust_address) AS addr_num FROM Customers GROUP BY cust_name ORDER BY cust_name DESC;

# 使用where和having过滤数据
SELECT cust_name, COUNT(*) AS NumberOfOrders FROM Customers WHERE cust_email IS NOT NULL GROUP BY cust_name HAVING COUNT(*) > 1;

子查询：

将一个查询结果作为另一个SQL语句的数据来源或判断条件。通常用在 where 和 from 后面。

# 用于 where 的子查询，子查询需要放在括号内，operator 表示用于 where 子句的运算符
select column_name [, column_name ] from table1 [, table2 ] where  column_name operator (select column_name [, column_name ] from table1 [, table2 ] [where])

# 用于 from 的子查询，返回的结果相当于一张临时表，所以需要使用 AS 关键字为该临时表起一个名字
select column_name [, column_name ] from (select column_name [, column_name ] from table1 [, table2 ] [where]) as temp_table_name where  condition

# 子查询的子查询
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders                   WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'RGAN01'));

where：

用于过滤记录，即缩小访问数据的范围；其后跟一个返回 true 或 false 的条件；可以和 select、update、delete 一起使用；可以在 where 子句中使用操作符；

in 在 where 子句中使用，在指定的几个特定值中任选一个值；

between 在 where 子句中使用，选取介于某个范围内的值；

and、or、not 是用于对过滤条件的逻辑处理指令；and 的优先级高于 or，为了明确处理顺序，可以使用括号；

like 在 where 子句中使用，确定字符串是否匹配模式；只有字段是文本值时才使用 like；支持两个通配符选项 %（任何字符出现任意次数）和 _（任何字符出现一次）；

# select 中的 where 子句
SELECT * FROM Customers WHERE cust_name = 'Kids Place';

# update 中的 where 子句
UPDATE Customers SET cust_name = 'Jack Jones' WHERE cust_name = 'Kids Place';

# delete 中的 where 子句
DELETE FROM Customers WHERE cust_name = 'Kids Place';

# in 的示例
SELECT * FROM products WHERE vend_id IN ('DLL01', 'BRS01');

# between 的示例
SELECT * FROM products WHERE prod_price BETWEEN 3 AND 5;

# and 的示例
SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id = 'DLL01' AND prod_price <= 4;

# or 的示例
SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

# not 的示例
SELECT * FROM products WHERE prod_price NOT BETWEEN 3 AND 5;

# % 的示例
SELECT prod_id, prod_name, prod_price FROM products WHERE prod_name LIKE '%bean bag%';

# _ 的示例
SELECT prod_id, prod_name, prod_price FROM products WHERE prod_name LIKE '__ inch teddy bear';

连接：

用于将两个或者多个表联合起来进行查询。连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。

on 和 where 的区别：在连接表时，SQL会根据连接条件生成一个新的临时表；on 是链接条件，决定临时表的生成；where 是在临时表生成以后，再对临时表中的数据进行过滤，生成最终的结果集，此时已经没有 join on 了。 join左侧可以加上一些修饰性的关键词，从而形成不同类型的连接：

# 使用 join 连接两个表
select table1.column1, table2.column2... from table1 join table2 on table1.common_column1 = table2.common_column2;

# 如果两张表的关联字段名相同，也可以使用 using 代替 on
-- join....on
select c.cust_name, o.order_num from Customers c inner join Orders o on c.cust_id = o.cust_id order by c.cust_name;
-- join....using()
select c.cust_name, o.order_num from Customers c inner join Orders o using(cust_id) order by c.cust_name;

# 隐式内连接
select c.cust_name, o.order_num from Customers c, Orders o where c.cust_id = o.cust_id order by c.cust_name;

# 显式内连接
select c.cust_name, o.order_num from Customers c inner join Orders o using(cust_id) order by c.cust_name;

组合：

union 将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 union 中参与查询的提取行。

union 中所有查询的列数和列顺序必须相同；每个查询中涉及表的列的数据类型必须相同或兼容；通常返回的列名取自第一个查询。

union 默认选取不同的值，如果允许重复的值，则使用 union all。

# 使用 union all
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

join 中连接表的列可能不同，在 union 中所有查询的列数和列的顺序必须相同；join 将查询之后的列放在一起（水平放置），构成一个笛卡尔积；union 将查询后的行放在一起（垂直放置）。

DCL语句

权限控制：

grant：授予用户账户权限

在 grant 关键字后指定一个或多个权限。如果授予用户多个权限，则每个权限由逗号分隔。

on privilege 确定权限应用级别。MySQL 支持 global（*.*）、database（database.*）、table（database.table）和列级别。如果使用列权限级别，则必须在每个权限之后指定一个或逗号分隔列的列表。

user 是要授予权限的用户。如果用户已存在，则 grant 将修改其权限；否则 grant 将创建一个新用户。可选子句 identified by 允许为用户设置新的密码。

require tsl_option 指定用户是否必须通过 SSL、Xo59 等安全连接连接到数据库服务器。

可选 with grant option 允许授予其他用户或从其他用户中删除您拥有的权限。此外，可以使用 with 子句分配 MySQL 数据库服务器的资源，如设置用户每小时可以使用的连接数或语句数。

revoke：撤销用户的权限

在 revoke 后指定要从用户撤消的权限列表，使用逗号分隔权限。

指定在 on 子句中撤销特权的特权级别。

指定要撤销 from 子句中的权限的用户账号。

grant 和 revoke 可以在几个层次上控制访问权限

整个服务器：使用 grant all 和 revoke all ；

整个数据库：使用 on database.* ；

特定的表：使用 on database.table ；

特定的列；

特定的存储过程。

# grant 授予权限
GRANT privilege,[privilege],.. ON privilege_level TO user [IDENTIFIED BY password] [REQUIRE tsl_option] [WITH [GRANT_OPTION | resource_option]];

# revoke 撤销权限
REVOKE   privilege_type [(column_list)] [, priv_type [(column_list)]]... ON [object_type] privilege_level FROM user [, user]...

# 创建账户
CREATE USER myuser IDENTIFIED BY 'mypassword';

# 修改账户名
UPDATE user SET user='newuser' WHERE user='myuser'; 
FLUSH PRIVILEGES;

# 删除账户
DROP USER myuser;

# 查看权限
SHOW GRANTS FOR myuser;

# 授予权限
GRANT SELECT, INSERT ON *.* TO myuser;

# 删除权限
REVOKE SELECT, INSERT ON *.* FROM myuser;

#更改密码
SET PASSWORD FOR myuser = 'mypass';

TCL语句

事务处理：

不能回退 select、create、drop 语句。

事务提交：

MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 start transaction 时，会关闭隐式提交；当 commit 或 rollback 执行后，事务会自动关闭，重新恢复为隐式提交。

通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交； autocommit 标记是针对每个连接而不是针对服务器的。

指令：start transaction（标记事务的起始点）、savepoint（创建保留点）、rollback to（回滚到指定的保留点，若没有保留点则回退到 start transaction 处）、commit（提交事务）。

-- 开始事务
START TRANSACTION;
-- 插入操作 A
INSERT INTO user VALUES (1, 'root1', 'root1', 'xxxx@163.com');
-- 创建保留点 updateA
SAVEPOINT updateA;
-- 插入操作 B
INSERT INTO user VALUES (2, 'root2', 'root2', 'xxxx@163.com');
-- 回滚到保留点 updateA
ROLLBACK TO updateA;
-- 提交事务，只有操作 A 生效
COMMIT;

MySQL

默认端口号

MySQL 的默认端口号是 3306 。

MySQL 常用字段

char 和 varchar 的区别

char ：定长字符串，在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格。

varchar：变长字符串，在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

decimal 和 float/double 的区别

decimal 用于存储具有精度要求的小数，可以存储精确的小数值；float/double 只能存储近似的小数值。

NULL 和 ' ' 的区别

NULL ：代表一个不确定的值（除了distinct、group by、order by 其它判等 NULL=NULL 都为 false）；要占用空间；会影响聚合函数的结果（count * 会统计 NULL ；count 列名会忽略 NULL 值；其它函数会忽略 NULL 值）；查询 NULL 值时，要用 is NULL 或 is not NULL ，不能用比较运算符。

' ' ：长度为 0；不占空间；可以用比较运算符。

boolean 类型的表示

用 tinyint（1）表示布尔值，可以存储 0 或者 1 。

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

# 开启一个事务
START TRANSACTION;

# 多条 SQL 语句
SQL1,SQL2...

## 提交事务
COMMIT;

事务的特性（ACID）：

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

一致性（Consistency）：执行事务前后，数据保持一致；

隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来的问题：

脏读：一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。

丢失修改：在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

不可重复读：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读：幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

并发事务的控制方式

MySQL 中并发事务的控制方式有两种：锁和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC）可以看作是乐观控制的模式。

锁控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

共享锁（S 锁、读锁）：事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

排他锁（X 锁、写锁、独占锁）：事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

undo log : undo log 用于记录某行数据的多个版本的数据。

read view 和 隐藏字段 : 用来判断当前版本数据的可见性。