在这个数字化转型的浪潮中,信息技术正以前所未有的速度重塑着咱们的世界。数据库已作为存储和管理数据不可或缺的工具。把握基本的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;
写在最后:这篇文案笔者总结了数据库进阶的内容,包括有些实用命令,数据库表约束以及表的连接查找。在上一篇文案中总结了数据库增删改查,文案详情:《数据库的基本:增删改查操作指南》⬅ 点看 ~
公众号|杨小样随笔
关注 点赞 在看
长按关注 更加多精彩
|