|
在这个数字化转型的浪潮中，信息技术正以前所未有的速度重塑着咱们的世界。数据库已作为存储和管理数据不可或缺的工具。把握基本的SQL命令仅仅是起始，想要将数据库运用得游刃有余，得心应手，还必须把握有些进阶技能，本文笔者将带你探索数据库的深层次应用，从实用命令到表约束和繁杂
链接查找，循序渐进提高数据处理的能力。
首要：咱们将介绍一系列高级SQL命令，这些命令能够让你更有效地查找数据库表信息，表结构，引擎等。接着：咱们深入领会数据库约束的重要性，它们是保证数据完整性和准确性的关键。另外：链接查找做为数据库中的一项强大功能，能够让你在多个表之间创立繁杂的关系，实现数据的深度整合，案例供参考，避免陷阱笛卡尔积，加强查找性能。
数据库进阶重点内容：
- 实用命令
：use，set，show，rename，truncate和万能的 help 命令。
- 添加约束：
主键自增，主键约束，外键约束，独一约束，默认约束，check约束。
- 链接查找：
左连接，右连接，内连接，全连接，交叉连接，自连接，以及表连接查找产生的笛卡尔积。
说明：本文部分关键字采用了大写，为了让SQL语句结构更加清晰，更利于区分SQL之间差异。
1. USE 
选取数据库 use database_name;
2. SET
设置字符集 set names utf8;
3. SHOW
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;
7. 列出所有字符集 show charset;
8. 显示数据库的编码 show variables like character_set_database;
9. 表示校验规则 show variables like collation_database;
提示：在SQL编写过程中检测关键字和语句无误，显现红色浪花线能够忽略。
10. 表示数据库引擎 showvariableslike "have_%";
4. Rename
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
1. 清空表数据 truncate table_name;
提示：truncate table_name 清空表不可和where要求一块运用。和delete区别是，delete删除表中的数据，是从删除所有层面来操作的，操作后自增列还是存在的。而truncate 相当于删除表数据，再重新建一张相同结构的表，操作后得到一张新表，速度快。
6. HELP -- 帮忙查看 show 关联命令 --help show;-- 帮忙查看 create 关联命令 --help create;-- 帮忙查看 alter 关联命令 --help alter;
7. 约束
约束重点包括6种：主键自增，主键约束，外键约束，独一约束，默认约束，check约束。
1. 主键自动增长 alter table table_name column id intprimarykey auto_ increment
提示：自动增长必要和主键一块运用，然则主键不必定是自动增长的。auto_increment：自动增长关键字，primary key：主键关键字。
2. 添加主键约束 alter table table_name add constraint  K_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版本。
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. 链接查找
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 ON SELECT 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 JOIN SELECT 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;
写在最后：这篇文案笔者总结了数据库进阶的内容，包括有些实用命令，数据库表约束以及表的连接查找。在上一篇文案中总结了数据库增删改查，文案详情：《数据库的基本：增删改查操作指南》⬅ 点看  ~
公众号｜杨小样随笔
关注 点赞 在看
长按关注 更加多精彩
| 
发表于 2024-8-4 12:21:19