mysql的学习

red_redemption

于 2024-10-04 17:43:07 发布

阅读量421

点赞数 18

文章标签： mysql 学习数据库

本文链接：https://blog.csdn.net/red_redemption/article/details/142280281

版权

垂直连接union

join是水平连接，UNION 是垂直连接，它是把两张表的结果集按列堆叠，行数增加了，但列数保持不变。

`UNION` 垂直连接的作用

在 MySQL 中，UNION 可以将两个具有相同列数且列数据类型兼容的查询结果合并在一起。它将一个结果集的行放在另一个结果集的行下面。也就是说，行的合并，是典型的垂直连接。

`UNION` 的例子

假设你有两个表 table1 和 table2，它们的结构相同：

+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Bob   |
+----+-------+






+----+-------+
| id | name  |
+----+-------+
| 3  | Carol |
| 4  | Dave  |
+----+-------+

如果你想把这两个表的行合并到一起，你可以使用 UNION：

SELECT id, name FROM table1
UNION
SELECT id, name FROM table2;

+----+-------+
| id | name  |
+----+-------+
| 1  | Alice |
| 2  | Bob   |
| 3  | Carol |
| 4  | Dave  |
+----+-------+

这里，UNION 操作把 table1 和 table2 的数据垂直地连接在了一起。结果集中，table1 的数据出现在前面，table2 的数据紧接在后面。列的数量和顺序保持不变，行数增加了。

`UNION` 与 `UNION ALL` 的区别

UNION：默认会去除重复的行，因此如果 table1 和 table2 中有相同的行，UNION 只会保留一份。
UNION ALL：不会去除重复的行，直接把所有行合并。

SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

垂直连接的使用场景

跨表查询不同来源的同类型数据：比如你有不同时间段的数据存储在不同的表中，可以用 UNION 将它们整合为一个结果集。
数据归档：把历史表的数据与当前表的数据合并，便于一起分析。

表的自然连接和自连接

自连接

自连接的应用场景

自连接不是新语法，是内外自然连接的一种多次使用自己一张表里数据去比对的情况

比如同一张表里有各个工作人员的编号，每个工作人员有自己上级的编号

那就可以多次使用自己的表，取别名直接用三次查自己老板的老板，用两次自己的表查自己的老板，用一次表就很自然的是只查自己的信息

自然连接

表的内外连接

可以多张表同时外连接第一个表全是left就会看最左的那个为主表

如果多个表外连接，要是掺了一个内连接，那对应补上的行依然会消失

外连接会拼在一起，left为主表，然后右边的表如果不存在对应的on就全部显示未null

ifnull（列名，'为null的显示内容'）

这样select的列名会变，可取新列名

如果是部门为主表，那没有部门就不会显示，这里的d为left

外键约束补充

department_ref INT,

FOREIGN KEY (department_ref) REFERENCES departments(dept_id)

这两条语句是分开的，第一条只是增加一个int类型的列，名字为department_ref,

而下一条则是单独的约束添加操作，选择的目标为外键，对应的外界目标为另一张表中的（dept_id）这列

reference后面的叫主表只是在约束关系上是主

在外键的定义和设置，是在从表里的

所以要删外键约束要删从表（这里是employee）里

外键约束有一些具体的要求和约定，这些要求涉及到从表和主表之间的列关系。

1. 数据类型匹配

相同的数据类型：外键列和被引用的主键列必须具有相同的数据类型。例如，如果主表的主键是 INT 类型，从表的外键也应该是 INT 类型。

2. 数据完整性

引用存在性：从表中的外键值必须在主表的主键列中存在。这意味着你不能在从表中插入一个没有在主表中对应值的外键。

3. 唯一性和非空性

主表的主键列：主表的主键列必须是唯一的，并且不能为 NULL。
外键列：虽然外键列可以包含 NULL 值（如果没有设置 NOT NULL），但如果包含值，则这些值必须引用主表中存在的主键。

4. 级联操作

外键约束允许你定义级联操作，例如 ON DELETE CASCADE 或 ON UPDATE CASCADE。这意味着如果主表中的某个记录被删除或更新，相关的从表记录也会自动被删除或更新。

可是为什么呢？如果数据不匹配呢？如果设置约束后，从表的外键和主表的主键值不同呢？为什么主表的对应键要唯一且非空，而外键列什么要求都没有？

主表的主键的唯一性和非空性

唯一性：主键必须是唯一的，以确保每一行数据可以通过主键唯一标识。这使得数据管理和查询更加高效。
非空性：主键不能为 NULL，因为没有有效的标识符就无法唯一地识别一行数据。

2. 外键的要求

引用存在性：从表的外键列的值必须在主表的主键列中存在。（主表可以有更多数据，而从表只引用部分数据）这保证了从表中的数据有一个有效的、对应的主表数据。
可以为 NULL：外键列可以包含 NULL 值。这允许某些记录在没有有效引用时仍然存在，这在实际应用中是常见的，比如某个员工可能没有分配部门。

3. 数据不匹配的情况

如果从表中的外键值不匹配主表的主键值，数据库会拒绝插入或更新操作，这样可以避免数据不一致。例如：

尝试插入 employees 表中的 department_id 为 999，但 departments 表中没有对应的 department_id，这会导致错误。
之所以是设置foreign key的表是从表，是因为数据的需求原因，主表里面可以有各种各样的全部部门，而外键设定者-从表只是想让自己的这个列变得可以引用别的表里的对应列里的部分，得到这一部分里面的值相同时其他的各种信息

4. 总结

主表的主键需要唯一且非空，以便唯一识别每条记录，而外键可以包含 NULL，以灵活处理某些数据不完整的情况。外键约束的设置确保了从表的数据始终能够与主表的数据保持一致，避免了数据孤立的情况。

约束起名

外键可以在约束前面加上constraint，为该外键约束起名

其他类型的约束（如主键约束、唯一约束和检查约束）也可以使用 CONSTRAINT 关键字来起名字。

在列定义中直接给约束起名字是不可以的。

对于外键约束，只能单独命名。这是因为外键约束涉及到对其他表的引用，因此更常见的是在定义时通过 CONSTRAINT 进行命名。

外键约束不能列定义

约束的添加和删除都是按约束添加的主表，为所有者，这里就是score为student的外键约束，要删去这种主从的约束，就要选择主表去删，即束缚student的条件表（主表），去删关系

选择表add添加外键约束

主、从表

虽然主表是从表的一部分，但在约束的概念上，主表和从表的角色是明确的。

从表里写foreign key（本表里的列）references 主表名(主表里的列)

CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) -- 给外键约束起名字

AUTO_INCREMENT

information_schema

（要先有外键，内键才能用，所以这里3 4反过来）

可以查询别的公开的库的信息

information_schema是一个独立的系统数据库，它存储了有关数据库系统中所有数据库和表的信息。

为什么可以在`school`库中执行

跨数据库访问：在许多数据库管理系统中，information_schema是一个全局可访问的视图，不依赖于特定的数据库。当你在任何数据库（如school）中执行查询时，你可以使用information_schema中的表来获取当前数据库的元数据。
当前上下文：即使你在school数据库中执行查询，information_schema.table_constraints会返回关于当前数据库（school）中的表的约束信息。这样，你可以查询任何数据库的元数据

所以，information_schema提供了一个统一的方式来访问数据库结构信息，无论你当前连接的是哪个数据库。

语句复习

特定数据库：当我们提到“特定数据库”时，指的是你正在使用的数据库。例如，如果你在使用一个叫test05的数据库，那么test05就是你当前连接的特定数据库。

当前连接的数据库：在执行SQL查询时，你需要先选择或连接到一个数据库。这个数据库是你正在操作的地方，所有的查询和修改都会发生在这个数据库中。

information_schema的查询：当你查询information_schema.table_constraints时，你得到的结果是与当前连接的数据库相关的约束信息。比如，如果你连接到test05数据库，查询结果就会列出test05中所有表的约束信息。

所以，当我们提到“特定”时，就是指你当前正在

select *，得到表中所有，information_schema.table_constraints是表吗，系统自带的表，然后里面存了所有的约束信息，而后面的table_schema='test05' and table_name='score1'; 则是在这个约束总表里添加where约束，得到库名为test05里的名为score1的表的所有约束是吗

没错，你理解得很准确！

**information_schema.table_constraints**确实是一个系统自带的表，存储了所有数据库中表的约束信息。
使用SELECT *会获取该表中的所有列信息。
后面的条件table_schema='test05' AND table_name='score1'是用来筛选出库名为test05、表名为score1的记录。

这样，你最终能得到关于score1表的所有约束信息。

为什么偏要用table_schema和table_name当这个约束库的列名？

table_schema和table_name是information_schema.table_constraints表中定义的列名，分别表示数据库（模式）的名称和表的名称。

table_schema：用于表示包含表的数据库名。在一个数据库管理系统中，可以有多个数据库，因此需要通过这个列来区分不同的数据库。

table_name：表示具体的表名。在一个数据库中，可以有多个表，因此需要通过这个列来指定具体的表。

意思是table_schema是数据库别名？等同database？

可以这样理解，但有些细微的差别。

table_schema：在许多数据库系统中，table_schema通常指代数据库中的模式（schema）。在一个数据库中，可以有多个模式，每个模式可以包含多个表。table_schema提供了一个组织结构。

database_name：直接表示一个数据库的名称。在某些数据库系统中，数据库和模式是相同的概念，但在其他系统中（如PostgreSQL），模式是数据库内的一种逻辑分组。

所以，虽然在某些情况下table_schema和database_name可以互换，但它们的含义和用法可能会因数据库系统而异。总的来说，table_schema是更通用的术语，强调了数据的组织结构。

约束还有名字？

是的，约束在数据库中通常都有一个名称，称为约束名（constraint name）。约束名用于唯一标识一个约束，以便于管理和引用。

常见的约束包括：

主键约束（Primary Key）：确保某列的值唯一且不为空。
外键约束（Foreign Key）：用于确保数据的完整性，强制要求某列的值在另一表中存在。
唯一约束（Unique）：确保某列的值在整个表中是唯一的。
检查约束（Check）：用于限制列中的值必须满足的条件。

在information_schema.table_constraints中，你可以看到每个约束的名称、类型等信息，这样可以帮助你更好地理解和管理数据库结构。

？听不懂，约束是给到谁的，看的是谁的约束，我可以给哪些约束，我要怎么给约束，我怎么查我做了哪些约束，约束不遵守是不是只报错而已，不同的约束会不会出现互斥

好的，让我更简单地解释一下约束。

约束是什么？
约束是用于限制表中数据的一种规则，确保数据的完整性和一致性。

约束给谁？
约束是给表的，限制表中列的数据。例如，主键约束确保某一列的值是唯一的。

常见的约束类型：

主键约束：确保列的值唯一且不能为空。
外键约束：确保一列的值必须在另一张表中存在。
唯一约束：确保某列的值在整个表中是唯一的。
检查约束：限制列中值必须满足的条件。

如何给约束？
可以在创建表时或者修改表时使用SQL语句添加约束，例如：

CREATE TABLE score1 ( id INT PRIMARY KEY, score INT CHECK (score >= 0) );

如何查看已添加的约束？
可以查询information_schema.table_constraints来查看当前表的所有约束：

SELECT * FROM information_schema.table_constraints WHERE table_name='score1';

约束不遵守怎么办？
如果插入的数据违反约束，数据库会报错，拒绝执行该操作。

约束互斥吗？
不同的约束可以同时存在，但某些情况下（如外键约束与主键约束），可能会有逻辑上的依赖关系。

数据库中的约束

在数据库中，约束（Constraints）是用于限制数据输入的规则，确保数据的完整性和一致性。常见的约束类型包括：

1. 主键约束（PRIMARY KEY）

因此，当你在表中定义一个列为PRIMARY KEY时，数据库会自动将该列设置为NOT NULL和UNIQUE。

定义：唯一标识表中的每一行，主键的值不能重复且不能为 NULL。
示例：在创建表时，可以指定某列为主键：

CREATE TABLE Users ( UserID INT PRIMARY KEY, UserName VARCHAR(100) );

主键（PRIMARY KEY）等同于NOT NULL和UNIQUE的组合。
NOT NULL：主键列不能包含空值。这意味着每个记录都必须有一个有效的主键值。
UNIQUE：主键列的值必须是唯一的，这确保了没有两行会有相同的主键值。

2. 外键约束（FOREIGN KEY）

定义：确保一列的值必须在另一张表中存在。
示例：

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, UserID INT, FOREIGN KEY (UserID) REFERENCES Users(UserID) );

3. 唯一约束（UNIQUE）

定义：确保列中的所有值都是唯一的，允许 NULL 值（通常一个 NULL）。
示例：

CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductCode VARCHAR(50) UNIQUE );

4. 非空约束（NOT NULL）

定义：确保列中的值不能为空。
示例：

CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, Email VARCHAR(100) NOT NULL );

5. 检查约束（CHECK）

定义：用于限制列中的值，以满足特定条件。
示例：

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Age INT CHECK (Age >= 18) );

6. 默认约束（DEFAULT）

定义：为列设置默认值，如果插入数据时未提供该列的值，则使用默认值。
示例：

CREATE TABLE Items ( ItemID INT PRIMARY KEY, Quantity INT DEFAULT 0 );

全程练习

insert into books(id,name,author,price,pubdate,note,num) value(对应类型存对应值，，，...)

更新值

update books set price =price+6 where note ='joke';

update 该表的名称 set 列名=值要改的(全部的行都改),note=要把note列全部改成什么 where 用列名值再筛选条件（于是就只改这些选中的列名的值条件成立的所有行）

删除库存为0的行

delete from 当前的books表名 where 添加的约束num=0的所有行

统计书名中所有书名有a的书、、、用like和%模糊查询字符串

select *（所有，现在只是Select，不去修改了）from books where name like‘%a%’

1.在 MySQL 中创建表时，列名不需要加引号，但使用反引号（``）可以避免与保留字冲突。更新列值时，字符串变量确实需要用单引号（‘’）或双引号（“”）包围，以确保正确赋值。

2.列名写什么就是什么，MySQL 会直接按照你输入的列名来处理。如果你用双引号（“”）包围列名，MySQL 也会将其视为列名。需要注意的是，双引号在某些 SQL 模式中是允许的

3.在 MySQL 中，字符串值可以用单引号（'）或双引号（"）包围，但使用单引号是更常见的做法。两者在大多数情况下是可以互换的，但在某些 SQL 模式下，双引号的使用可能会引起一些解析问题。因此，建议使用单引号来确保兼容性。
在 MySQL 中，字符串类型主要有以下几种：

CHAR：固定长度的字符串，最多可达 255 个字符。
VARCHAR：可变长度的字符串，最多可达 65,535 个字符。
TEXT：长文本字符串，最大可达 65,535 个字符。
MEDIUMTEXT：中等长度文本，最大可达 16,777,215 个字符。
LONGTEXT：非常长的文本，最大可达 4,294,967,295 个字符。
BINARY 和 VARBINARY：用于存储二进制数据，功能与 CHAR 和 VARCHAR 类似，但不进行字符集转换。

聚合函数count(1)或者（*）

在 MySQL 中，常用的聚合函数包括：

COUNT()：计算行数。
SUM()：计算数值列的总和。
AVG()：计算数值列的平均值。
MAX()：返回列中的最大值。
MIN()：返回列中的最小值。
GROUP_CONCAT()：将组内的值连接成一个字符串。

having 后面可以使用select里的别名nm，

select note sum(num) as nm from books group by note having num>30(分成组之后的总体组的结果判断)

limit 截去多少条，之后再拿多少条

不写默认截去0条，拿1条--->limit 1

关键字的顺序

select 列名 from 表 where 条件 and| or 条件 group by 分组使用的字段 having 分组后的条件

这是书写的顺序，执行顺序会有一些的差异

from 数据源 where 筛选前的前置条件 group by 分组 having 分组后的数据过滤 select 列的信息显示

查表，改表名

desc 表名;

char(1)可以直接省略成char ，1代表一个字符，字符大小和字符集设定有关，汉字也是一个字符

alter table table_name rename new_name;

show tables;//显示当前库中所有的表

命名推荐

标准字段名格式：大小写字母、数字、下划线 _。开头建议使用字母，不建议使用数字开头。

避免使用 SQL 关键字：避免使用 SELECT, TABLE, ORDER 等保留字作为字段名，即使包裹了反引号，仍可能导致混淆。

一致性：尽量保持字段名的一致性，遵循一种命名规范（如全小写或驼峰命名法），避免多个风格混杂。

用汉字当字段名：MySQL 允许你使用汉字作为字段名，只要你使用了适当的字符集（如 utf8 或 utf8mb4）。尽管技术上允许，实际开发中一般不推荐使用汉字或非 ASCII 字符作为字段名，主要原因是代码维护和跨平台兼容性问题。

数字开头：尽管从技术上来说，MySQL 允许你使用数字开头的字段名，但通常的最佳实践是避免这种情况，建议以字母开头，并使用大小写字母、数字和下划线组合。在一些数据库系统中，数字开头的字段名可能会带来兼容性问题，尤其是在涉及跨平台的数据库迁移时。一些数据库可能严格限制字段名的格式，要求字段名必须以字母开头。

-- 不推荐
CREATE TABLE users (
1name VARCHAR(50),
2age INT
);

-- 推荐
CREATE TABLE users (
user_id INT PRIMARY KEY,
first_name VARCHAR(50),
age INT
);

反引号``

在 MySQL 中，使用反引号（``）允许你在列名或表名中包含特殊字符，包括空格、符号和汉字，但这并不意味着推荐这么做。

CREATE TABLE `my@table` (
`column#1` INT,
`name&age` VARCHAR(50)
);

CREATE TABLE `用户信息` (
`姓名` VARCHAR(50),
`年龄` INT
);

兼容性问题：虽然 MySQL 支持用反引号包裹字段名、表名并使用特殊符号或汉字，但其他数据库（如 SQL Server、Oracle）可能不支持这种写法。如果你将来需要迁移数据库，这会导致兼容性问题。

关键词冲突

定义列字段的时候，有时刚好和已有关键词相同了，用反引号``(1左边键，在英文模式下按)

比如code

用了反引号，字段名依然是code，可以避免关键字的重叠，语句更整洁

（至于为什么不用''，非要用``，因为用了''，会变成字符串）

反引号还允许你在列名或表名中使用空格或其他特殊字符

SELECT `first name` FROM `my table`;

单引号用于表示字符串常量

INSERT INTO employees (first_name) VALUES ('John');

双引号（"）的作用：

双引号在一些 SQL 标准中可以用来引用标识符（类似于反引号），但是不同的数据库系统处理双引号的方式不同。在一些数据库中，双引号用于引用标识符，但在其他数据库中，它可能被用于字符串或会有不同的处理方式。
例如在 SQL Server 中，双引号也可以引用标识符，而在 MySQL 中，它更常用于其他用途。为了一致性，MySQL 强烈推荐使用反引号来引用标识符。
不同的sql软件定义不同，有的的确也可以像反引号一样，但更常见的还是在某些数据库中表示字符串或对象。