数据库进阶：实用命令，约束，链接查询-天涯论坛

b1gc8v 发表于 2024-8-4 12:21:19

数据库进阶：实用命令，约束，链接查询

在这个数字化转型的浪潮中，信息技术正以前所未有的速度重塑着咱们的世界。数据库已作为存储和管理数据不可或缺的工具。把握基本的SQL命令仅仅是起始，想要将数据库运用得游刃有余，得心应手，还必须把握有些进阶技能，本文笔者将带你探索数据库的深层次应用，从实用命令到表约束和繁杂

链接查找，循序渐进提高数据处理的能力。

首要：咱们将介绍一系列高级SQL命令，这些命令能够让你更有效地查找数据库表信息，表结构，引擎等。接着：咱们深入领会数据库约束的重要性，它们是保证数据完整性和准确性的关键。另外：链接查找做为数据库中的一项强大功能，能够让你在多个表之间创立繁杂的关系，实现数据的深度整合，案例供参考，避免陷阱笛卡尔积，加强查找性能。
数据库进阶重点内容：
- 实用命令
：use，set，show，rename，truncate和万能的 help 命令。

- 添加约束：
主键自增，主键约束，外键约束，独一约束，默认约束，check约束。

- 链接查找：
左连接，右连接，内连接，全连接，交叉连接，自连接，以及表连接查找产生的笛卡尔积。

说明：本文部分关键字采用了大写，为了让SQL语句结构更加清晰，更利于区分SQL之间差异。
1. USE<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZV3z2Ou0SpXicSwuEnKOBUAsbQne53LBZZMNCzFOP6Ir4MfQI4wz6cVw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
选取数据库use database_name;
2. SET<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZV3z2Ou0SpXicSwuEnKOBUAsbQne53LBZZMNCzFOP6Ir4MfQI4wz6cVw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
设置字符集set names utf8;
3.  SHOW<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZV3z2Ou0SpXicSwuEnKOBUAsbQne53LBZZMNCzFOP6Ir4MfQI4wz6cVw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
1. 查看数据库show databases | schemas;
2. 查看表show tables;
3. 查看表结构show columns from table_name  | desc table_name;
4. 查看服务器状态show status;
5. 查看MySQL进程show processlist;
6. 表示引擎show engines;<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZLeOXGGSvDSGNksmia6ViaNMOseQ5rMghl2KticHycCb2uw5ugLJJ6NQaA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
7. 列出所有字符集show charset;
8. 显示数据库的编码show variables like character_set_database;
9. 表示校验规则show variables like collation_database;<img src="https://mmbiz.qpic.cn/sz_mmbiz_jpg/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZYTWHUVwGicekPVQkictd6MsdhXJVzAttnVibBd4A9obGuNpq9H8kcnD1g/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
提示：在SQL编写过程中检测关键字和语句无误，显现红色浪花线能够忽略。
10. 表示数据库引擎showvariableslike "have_%";<img src="https://mmbiz.qpic.cn/sz_mmbiz_jpg/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZqRA6axLbVS1GvC4tXWcic6NAhcks8sdqxWffa6CNGeLeeY4z3KyJKKg/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
4. Rename<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZV3z2Ou0SpXicSwuEnKOBUAsbQne53LBZZMNCzFOP6Ir4MfQI4wz6cVw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
1. 修改表名，方式一Rename table oldname to newname;
2. 修改表名，方式二alter table table_name  RENAME  [TO|AS] new_table_name-- 将 employees（雇员表）修改为 user（用户表） --ALTER TABLE employees RENAME TO user;-- 或 --ALTER TABLE employees RENAME AS user;
5. Truncate<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZV3z2Ou0SpXicSwuEnKOBUAsbQne53LBZZMNCzFOP6Ir4MfQI4wz6cVw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
1. 清空表数据truncate table_name;  
提示：truncate table_name 清空表不可和where要求一块运用。和delete区别是，delete删除表中的数据，是从删除所有层面来操作的，操作后自增列还是存在的。而truncate 相当于删除表数据，再重新建一张相同结构的表，操作后得到一张新表，速度快。
6. HELP<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZV3z2Ou0SpXicSwuEnKOBUAsbQne53LBZZMNCzFOP6Ir4MfQI4wz6cVw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"> -- 帮忙查看 show 关联命令 --help show;-- 帮忙查看 create 关联命令 --help create;-- 帮忙查看 alter 关联命令 --help alter;
7. 约束<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZV3z2Ou0SpXicSwuEnKOBUAsbQne53LBZZMNCzFOP6Ir4MfQI4wz6cVw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
约束重点包括6种：主键自增，主键约束，外键约束，独一约束，默认约束，check约束。
1. 主键自动增长alter table table_name column id intprimarykey auto_ increment
提示：自动增长必要和主键一块运用，然则主键不必定是自动增长的。auto_increment：自动增长关键字，primary key：主键关键字。
2. 添加主键约束alter table table_name add constraint PK_ID primary key ( ID )
案例：-- 将用户表的 id 设置为主键 --ALTER TABLE users ADD CONSTRAINT PK_users_id PRIMARY KEY (id);
3. 添加外键约束alter table table_name add constraint FK_ID foreign key (ID) references table_name( id )
案例：-- orders 订单表，customers 客户表，orders 添加一个外键 customer_id 指向客户表中的主键 id --ALTER TABLE ordersADD CONSTRAINT FK_customers_id FOREIGN KEY(customer_id)REFERENCES customers(id);
重视：针对MySQL不支持外键，之前是有有些误解。为何之前会有MySQL不支持外键的误解呢？还一度作为机构的面试题。
不是不支持外键，而是大厂不举荐MySQL运用外键。在阿里手册中说到：不得运用外键与级联，一切外键概念必要在应用层处理。
定义外键约束之后，数据库的每次操作都必须去检测外键约束。针对插进数据来讲，影响了插进速度。针对更新来讲，级联更新是强阻塞，存在数据库更新风暴 Database Update Storm 的危害。外链约束亦不是完全无优点，外链的存在就保准了表关系的完整性。但鱼和熊掌不可兼得。
我是最起始是在运用Microsoft SQL Server数据库，Microsoft SQL Server 在数据库设计时候，表之间的关系是必须知道主外键约束。运用MySQL在设计表结构时候发掘项目组小伙伴都不运用外键。起始还挺意外的，有些不适，后面一看，它不影响我的多表连查，我亦就没管它了。
说起这个不支持外键，还是和搜索引擎有必定的关系。在 MySQL5.5 及之前的版本中，默认的存储引擎是 MyISAM，它不支持外键。从MySQL5.5起始，默认存储引擎变为 InnoDB，它支持外键。从下表能够看出MySQL供给了多种引擎。截止日前跟新到 MySQL Community Server 9.0.1版本。<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZLeOXGGSvDSGNksmia6ViaNMOseQ5rMghl2KticHycCb2uw5ugLJJ6NQaA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
4. 独一约束alter table table_name add constraint QU_id unique (identityID)
案例：ALTER TABLE usersADD CONSTRAINT QU_id UNIQUE (identityID);
5. 默认约束alter table table_name add constraintDF_loginpwddefault (123) for loginpwd
案例：ALTER TABLE usersADD CONSTRAINT DF_loginpwd DEFAULT 123 FOR loginpwd;-- 倘若您运用的数据库系统不支持 FOR 关键字，能够省略它。--ALTER TABLE usersADD CONSTRAINT DF_loginpwd DEFAULT 123;
6. check 约束alter table table_name add constraint CK_Date check ( Date >=1990-1-1)
7. 约束重视事项
首要：数据库中的表，是能够按照每张表的特定需求为表设置合适的存储引擎。然则更改存储引擎可能会影响表的性能和功能，引擎的特性各不相同，在更改引擎前必须认识引擎的有点点，按照项目需求做出选取。主表和从表之间必要是相同的引擎。
二是：一张表中只能有一个主键，能够有多个外键，主外键相关形成的表关系。独一约束能够有多个，且能够为空。
最后：外键列和参照列必要拥有类似的数据类型。数据类型，长度和精度，有没有符号不可混用，字符集，NULL处理：外键列和参照列对 NULL 值的处理必要一致。倘若参照列不准许 NULL 值，外键列亦必要设置为 NOT NULL，存储引擎必要支持外键，一般运用InnoDB。
8. 链接查找<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/fLF2peu3TJqSJ5rH4hy5DB5EYq63GQyZV3z2Ou0SpXicSwuEnKOBUAsbQne53LBZZMNCzFOP6Ir4MfQI4wz6cVw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
1. 笛卡尔积
提示：表联查必定要重视笛卡尔积。在数据库查找中，倘若两个表在无指定连接要求的状况下进行连接操作，就会产生笛卡尔积。
案例：产生笛卡尔积mysql> select * from fruits;+------+--------+| f_id | f_name |+------+--------+|    1 | 苹果   ||    2 | 梨子   |+------+--------+2 rows in set (0.00 sec)mysql> select * from animal;+----+--------+| id | name   |+----+--------+|  1 | 老虎   ||  2 | 狮子   ||  3 | 熊猫   |+----+--------+3 rows in set (0.00 sec)查找结果：产生笛卡尔积，SQL语句查找无指定where条件mysql> select id,name,f_id,f_name from fruits,animal;+----+--------+------+--------+| id | name   | f_id | f_name |+----+--------+------+--------+|  1 | 老虎   |    1 | 苹果   ||  1 | 老虎   |    2 | 梨子   ||  2 | 狮子   |    1 | 苹果   ||  2 | 狮子   |    2 | 梨子   ||  3 | 熊猫   |    1 | 苹果   ||  3 | 熊猫   |    2 | 梨子   |+----+--------+------+--------+6 rows in set (0.01 sec)
两表做全乘：从行看，便是两表每一行，两两组合。从列看，结果集中列，两表列相加。
2. 左连接 LEFT JOIN ON-- LEFT JOIN 或 LEFT OUTER JOIN --SELECT columnsFROM table1LEFT JOIN table2ON table1.common_field = table2.common_field;
3. 右连接 RIGHT JOIN ON-- RIGHT JOIN 或 RIGHT OUTER JOIN --SELECT columnsFROM table1RIGHT JOIN table2ON table1.common_field = table2.common_field;
4. 内连接 INNER JOIN ONSELECT columnsFROM table1INNER JOIN table2ON table1.common_field = table2.common_field;
5. 全连接 FULL JOIN ON-- FULL JOIN 或 FULL OUTER JOIN --SELECT columnsFROM table1FULL JOIN table2ONtable1.common_field= table2.common_field;
6.  交叉连接 CROSS JOINSELECT columnsFROM table1CROSS JOIN table2;
重视：返回两张表的笛卡尔积，不思虑连接要求。两表相乘查找方式，内存消耗大。
7. 自连接 SELF JOIN
自连接（Self Join）是一种特殊的SQL连接操作，它准许一个表与自己进行连接。自连接一般用于查找同一个表中相互相关的数据，例如员工表中员工与其直接上级的关系。SELECT columnsFROM table1 AS t1, table1 AS t2WHERE t1.common_field =t2.another_field;
案例：SELECT    e1.EmployeeID,    e1.Name AS Employee Name,    e2.Name AS Manager NameFROM    Employees e1JOIN    Employees e2 ON e1.ManagerID = e2.EmployeeID;
写在最后：这篇文案笔者总结了数据库进阶的内容，包括有些实用命令，数据库表约束以及表的连接查找。在上一篇文案中总结了数据库增删改查，文案详情：<a style="color: black;">《数据库的基本：增删改查操作指南》</a>⬅ 点看 <img src="https://res.wx.qq.com/t/wx_fed/we-emoji/res/v1.3.10/assets/newemoji/Addoil.png?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"> ~
公众号｜杨小样随笔
关注  点赞  在看
长按关注 更加多精彩

qzmjef 发表于 2024-10-1 10:15:44

太棒了、厉害、为你打call、点赞、非常精彩等。

j8typz 发表于 2024-10-15 18:39:22

感谢你的精彩评论，为我的思绪打开了新的窗口。

4lqedz 发表于 2024-11-8 20:10:01

论坛外链网http://www.fok120.com/

b1gc8v 发表于 2024-11-9 16:33:39

你的见解独到，让我受益匪浅，期待更多交流。

页: [1]

天涯论坛's Archiver

数据库进阶：实用命令，约束，链接查询