顶级架构师学习——第六阶段：数据库（mysql）只是常规操作

1、老规矩，先简单介绍一下MySQL数据库（当然，还是百科~）

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。

Oracle数据库更加重量级，但我们的MySQL与之相比并不差很多，最最最重要的是——MySQL是免费哒~

2、简单操作

为了安装方便，我直接使用了集成的xmapp，可以在这下载https://www.apachefriends.org/index.html，虽然是用于php编写的，但是可以把apache、mysql、tomcat这些东西一块给装了，而且更改配置也很方便。

数据库操作

1.创建一个库

create database 库名;

create database 库名 character set 编码;

2.删除一个库

drop database 库名;

3.使用库

use 库名;

4.查看当前正在操作的库

select database();

5.查看所有库

show databases;

数据库表操作

1.创建一张表

create table 表名(

    字段名 类型(长度) [约束],

    字段名 类型(长度) [约束],

    字段名 类型(长度) [约束]

);

2.查看数据库表

show tables;

3.查看表结构

desc 表名;

4.删除表

drop table 表名;

5.添加一列

alter table 表名 add 字段名 类型(长度) [约束];

6.修改列的类型、长度、约束等

alter table 表名 modify 要修改的字段名 类型(长度) [约束];

7.修改列名

alter table 表名 change 旧列名 新列名 类型(长度) [约束];

8.删除列

alter table 表名 drop 列名;

9.修改表名

rename table 表名 to 新表名;

10.修改表的字符集

alter table 表名 character set 编码;

11.查看表当前编码格式

show create table 表名;

修改数据库表记录

1.插入

insert into 表名(列名1,列名2,列名3……) values(值1,值2,值3……);

insert into 表名 values(值1,值2,值3……);

2.修改

update 表名 set 字段名=值, 字段名=值, 字段名=值……;  它会将该列的所有记录都更改

update 表名 set字段名=值, 字段名=值, 字段名=值…… where 条件;  仅修改选中的列

3.删除

delete from 表名 where 条件;

delete from 表名;

truncate table 表名;

ps:delete删除的时候是一条一条的删除记录，它配合事务，使用rollback可以将删除的数据找回。truncate删除，它是将整个表摧毁，然后再创建一张一模一样的表，它删除的数据无法找回。

4.查询

select * from 表名;  查询所有

select 属性, 属性... from product;  查询所选列

select * from 表名 as 别名;  使用别名输出表的所有内容，其中as可以省略，同样，所选列也可以使用别名

select distinct(属性) from 表名;  去除重复值

select * from 表名 where 属性=值;  按条件查询，可以使用and、or以及not查询多个条件

select * from 表名 order by asc(desc);  按升序(降序)排列查询结果

5.聚合函数

select sum(属性) from 表名;  查询表中所选属性的总和，sum也可以更改为avg、count等，分别表示平均值、个数

6.分组

select * from 表名 group by 属性;  根据属性进行分组展示

小结：

select  一般在的后面的内容都是要查询的字段

from  要查询到表

where

group by

having  分组后带有条件只能使用having

order by 它必须放到最后面

3、高级操作

外键

在说明外键之前，先简单说一下表与表之间的关系。

1.一对多关系

常见实例：客户和订单、分类和商品、部门和员工等

建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键

2.多对多关系

常见实例：学生和课程、商品和订单、人和角色

建表原则：需要建立第三张表作为中间表，中间表至少有两个字段，分别作为外键指向各自多方的主键，即将多对多拆分为两个一对多

3.一对一关系（了解，在生产中不常见）

建表原则（择其一）：

 - 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一以unique修饰

 - 外键是主键：主表的主键和从表的主键直接形成主外键关系

外键的使用sql代码如下：

alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键) 

[外键名称] 会在删除外键约束时使用，一般使用：字段名_fk

使用外键是为了保证数据的完整性！！！

当两张表没有建立任何关系的时候，那么可以随意删除其中任何一张表中的任何记录，但是一旦把两张表建立了关系(主外键约束)之后，那么不能删除主表中的数据(这些数据内容在从表中有关联关系的数据)，只想执行删除(更新操作)，那么就会出现下图中的错误。

要想删除主表中与从表有关联关系的数据，可以这么做：

1. 解除主从表的约束关系
2. 先删除从表中与主表有关系的数据，再删除主表中的数据。（需要先把p003和P004的数据删除了，再删除分类表中c002的数据。）

另外，从表也不能添加主表中不存在的数据！

 

子查询

带有ANY或ALL谓词的子查询

子查询返回单值时可以用比较运算符，而使用ANY或ALL谓词时则必须同时使用比较运算符，其语义为：

>ANY   大于子查询结果的某个值
>ALL   大于子查询结果中的所有值
<ANY   小于子查询结果中的某个值
<ALL   小于子查询结果中的所有值
>=ANY   大于等于子查询结果中的某个值
>=ALL   大于等于子查询结果中的所有值
<=ANY   小于等于子查询结果中的某个值
<=ALL   小于等于子查询结果中的所有值
=ANY   等于子查询结果中的某个值
=ALL   等于子查询结果中的所有值(通常没有实际意义)
!=(或<>)ANY  不等于子查询结果中的某个值
!=(或<>)ALL  不等于子查询结果中的任何一个值

例：查询其他系中比信息系某一学生年龄小的学生姓名和年龄

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
   FROM Student
   WHERE Sdept='IS')
  AND Sdept<>'IS'
结果如下：
Sname  Sage
-------------------------------------
王敏  18

DBMS执行此查询时，首先处理子查询，找出IS系中所有学生的年龄，构成一个集合（19，18）。然后处理父查询，找所有不是IS系且年龄小于19或18的学生。
本查询可以用集函数来实现，首先用子查询找出IS系中最大年龄（19），然后在父查询中查所有非IS系且年龄小于19岁的学生姓名及年龄，SQL语句如下：

SELECT Sname,Sage
FROM Student
WHERE Sage<
 (SELECT MAX(Sage)
  FROM Student
  WHERE Sdept='IS')
 AND Sdept<>'IS'

例二：查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage<ALL
 (
  SELECT Sage
  FROM Student
  WHERE Sdept='IS'
 )
 AND Sdept<>'IS'

查询结果为空表，本查询同样也可以用集函数来实现，SQL语句如下：

SELECT Sname,Sage
FROM Student
WHERE Sage<
 (SELECT MIN(Sage)
  FROM Student
  WHERE Sdept='IS'
 )
 AND Sdept<>'IS'

事实上，用集函数来实现子查询通常比直接用ANY或ALL查询效率要高，ANY与ALL与

集函数的对应关系如下所示

	=	<>或!=	<	<=	>	>=
ANY	IN	-	<MAX	<=MAX	>MIN	>=MIN
ALL	--	NOT IN	<MIN	<=MIN	>MAX	>=MAX

分页查询

分页查询使用的是limit关键字进行查询。它后面有两个参数。第一个参数是起始的位置（取值为：（需要查看第几页-1）乘以第二个参数），第二个参数是每页需要显示的条目数。

举例：商品表中有10条记录，现在需要进行分页显示，每页显示3条数据。现在需要查看第二页的数据。那么应该使用的sql语句是：

select * from product limit 3,3;

执行分页查询语句后，显示的结果如下：

内连接与外连接

1.概述

内联接（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。

外联接。外联接可以是左向外联接、右向外联接或完整外部联接。 
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定： 
1）LEFT JOIN或LEFT OUTER JOIN 
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN 
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

2.内连接（INNER JOIN）

内连接（INNER JOIN）：有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。（所谓的链接表就是数据库在做查询形成的中间表）。
例如：下面的语句3和语句4的结果是相同的。

语句1：隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C,ORDERS O
WHERE C.ID=O.CUSTOMER_ID;

语句2：显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;

注意：内连接查询结果与表的顺序无关 
（当然顺序可能会发生变化，但是对应关系绝对不会错乱！！！）

内连接有几种，下面一一进行展示：

⑴交叉连接（cross join）

当然，他还有其他的名字，比如：笛卡尔积，交叉积，还有最奇怪的名字“没有连接”（no join） 

使用下列命令同时查询玩具表的toy列和男孩表的boy列，得到的结果就是交叉连接

SELECT t.toy, b.boy FROM toys AS t CROSS JOIN boys AS b;

其中，CROSS JOIN可以省略，简写为

SELECT t.toy,b.boy FROM toys AS t, boys AS b;

交叉连接回把第一张表的每个值与第二张表的每个值进行匹配，结果如下 

交叉连接是内连接的一种，你又可以把内连接看作是通过查询限制条件后去除某些结果数据行之后的交叉连接。

⑵相等连接

我们假设每个男孩子都又一个玩具，表之间是一对一的关系，toy_id是外键，数据库表如下图 

我们想找到每个男孩儿拥有什么玩具，只需要将boys表中的toy_id和toys中的主键进行比对，就会得到结果

SELECT boys.boy, toys.toy FROM boys INNER JOIN toys ON boys.toy_id=toys.toy_id;

⑶不等连接

我们继续沿用1.2中的表结构，如果我们想找到每个男孩儿没有的玩具，这时候我们可以使用不等连接（说白了就是=换成<>,其他没有什么区别）

 SELECT boys.boy, toys.toy FROM boys INNER JOIN toys ON boys.toy_id<>toys.toy_id ORDER BY boys.boy;

⑷自然连接

继续沿用1.2的表结构。。。。。 
注意：自然连接只有在连接的列在两张表中的名称都相同时才会有用 
其实，自然连接就是自动识别相同列的相等连接

SELECT boys.boy, toys.toy FROM boys NATURAL JOIN toys ORDER BY boys.boy;

得到的结果和1.2中的结果完全一样（顺序可能不同）

 3.外连接（OUTER JOIN）：

外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。
三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。
说明：左表就是在“（LEFT OUTER JOIN）”关键字左边的表。右表当然就是右边的了。在三种类型的外连接中，OUTER 关键字是可省略的。

下面举例说明：

语句3：左外连接（LEFT OUTER JOIN）
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

语句4：右外连接（RIGHT OUTER JOIN）
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
注意：WHERE条件放在ON后面查询的结果是不一样的。例如：

语句5：WHERE条件独立。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
WHERE O.ORDER_NUMBER<>'MIKE_ORDER001';

语句6：将语句5中的WHERE条件放到ON后面。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID AND O.ORDER_NUMBER<>'MIKE_ORDER001';

从语句5和语句6查询的结果来看，显然是不相同的，语句6显示的结果是难以理解的。因此，推荐在写连接查询的时候，ON后面只跟连接条件，而对中间表限制的条件都写到WHERE子句中。

语句7：全外连接（FULL OUTER JOIN）。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
注意：MySQL是不支持全外的连接的，这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。

语句8：左外和右外的合集，实际上查询结果和语句7是相同的。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
UNION
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

下面通过图示来更清楚的展示一下：

⑴左外连接

LEFT OUTER JOIN（左外连接）接收左表的所有行，并用这些行与右表进行匹配 
当左表与右表具有一对多的关系时，左外连接特别有用。我们仍然使用之前的表结构

现在我们利用左外连接找出每个男孩拥有的玩具

SELECT b.boy, t.toy FROM boys b LEFT OUTER JOIN toys t ON b.toy_id=t.toy_id;

我们的查询结果和使用内连接时一样，难道说内连接和外连接没区别吗？怎么可能！！！接下来我们改变一下左表boys的表结构 

我们向boys中新添加了一个Andy，把他的toy_id设置为6，注意，6在toys表中没有对应的玩具，接下来再次运行上述程序

我们发现居然出现了一个NULL，NULL的出现是要告诉我们右表toys中没有与左表boys中的Andy相匹配的行，也就是说：

外连接一定会提供数据行，无论还行能否在另一个表中找出相匹配的行

 接着做个实验，我们调换左表和右表的顺序

SELECT b.boy, t.toy FROM toys t LEFT OUTER JOIN boys b ON b.toy_id=t.toy_id;

因此我们可以得出结论：出现NULL的列总是右表中的列

⑵右外连接

与左外连接完全相同，只不过是用右表来评价左表 
此外：RIGHT OUTER JOIN左侧的表为右表！！！！！ 

在实际使用中我们只要掌握一种方式，另一种做了解即可。

另外要说的两件事情！！！

①对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：
       1.（+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。
       2. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符
       3.（+）操作符只适用于列，而不能用在表达式上。
       4.（+）操作符不能与or和in操作符一起使用。
       5.（+）操作符只能用于实现左外连接和右外连接

左连接

用（+）来实现， 这个+号可以这样来理解：+ 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在右表，左表就是全部显示，故是左连接。

SQL> Select * from dave a,bl b where a.id=b.id(+);    -- 注意： 用（+） 就要用关键字where

右连接

用（+）来实现， 这个+号可以这样来理解：+ 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在左表，右表就是全部显示，故是右连接。

SQL> Select * from dave a,bl b where a.id(+)=b.id;

②on与where的区别

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 

在使用left jion时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2 id size 
1 10 
2 20 
3 30 

表2：tab2 size name 
10 AAA 
20 BBB 
20 CCC

两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.sizeand tab2.name=’AAA’)

第一条SQL的过程：

1、中间表

on条件: 
tab1.size = tab2.size  tab1.id tab1.size tab2.size tab2.name 
1 10 10 AAA 
2 20 20 BBB 
2 20 20 CCC 
3 30 (null) (null) 

2、再对中间表过滤

where 条件：
tab2.name=’AAA’ 
 tab1.id tab1.size tab2.size tab2.name 
1 10 10 AAA 

第二条SQL的过程：

1、中间表
on条件: 
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录) tab1.id tab1.size tab2.size tab2.name 
1 10 10 AAA 
2 20 (null) (null) 
3 30 (null) (null) 

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

外链接匹配，内连接过滤，外链接即匹配又过滤用on和where搭配

内连接与外连接部分的内容参考了以下博客，在此表示感谢！

https://blog.youkuaiyun.com/qq877507054/article/details/52328017

https://blog.youkuaiyun.com/chanmufeng/article/details/78234654

 

最后，奉上一份修改MySQL的root用户密码的秘籍一份，关注发送：mysqlroot  获取~~ 

有任何需要请在后台回复，我们会及时查看的~