MySQL数据库加强

关于 MySQL 的内容有很多，上一篇文章主要介绍了一些基础语法和最基础的 SQL 语句，这篇文章还是想写一些基础的内容和概念，主要是关于多表之间的，两张表之间的联系如何在数据库里面表示，当然也还会介绍一些比如修改数据库密码这样的基础操作。

1.修改密码

平时使用数据库的时候，图形化工具可以帮我们做很多事情，比如检查所有数据的时候，在工具里面就不用写 select * 这样的 SQL 语句了，但是有些操作图形化界面工具好像还是没有那么强大的，比如修改数据库密码，这也说明了掌握一些常用命令行的重要性，下面就是梳理过的如何修改 MySQL 数据库密码的步骤。

1.首先需要关闭 MySQL 服务

这里主要说的是在 Windows 系统上进行操作的，在 Windows 中关闭 MySQL 数据库服务有两种方式，第一种是用鼠标右键点击桌面的 "计算机" 图标，会看到一个 "系统服务" 的选项，点击这个选项，就可以看到当前电脑中运行的各种服务，这时候就简单了，在这些服务中选中 MySQL 的服务，双击之后就可以选择停止当前服务了，之后如果想启动 MySQL 服务也是在这里进行操作。

其实要想进入到 "系统服务" 的界面，还有另一种方式，那就是点击左下角的 "开始" 图标，然后在出现的输入框之中输入 services.msc 这条命令，就可以进入到 "系统服务" 的界面了。

关闭 MySQL 服务还有另一种方式，也就是命令行方式，只要输入下面这行代码，界面就会提示服务正在关闭，并且关闭成功。

net stop mysql

2.第二步便是要在命令行窗口中跳过权限开启服务

具体的做法就是打开一个命令行窗口，然后在窗口中输入如下命令：

mysqld --skip-grant-tables

需要注意的是，上面一句命令的最后是没有逗号的，skip 的前面有两个小横杠 "-"

3.登录 MySQL 数据库，并修改密码

这里要做的就是重新设置 MySQL 服务器的密码，首先重新打开一个命令行窗口，然后使用下面的命令进行登录：

mysql -u root -p

由于第 2 步里面已经设置跳过权限检查，因此这里是不用输入密码的，直接回车就可以登录进去，这样就可以进入到数据库当中了，因为关于服务器端的密码是放在名叫 mysql 数据库的 user 表中，因此我们需要先进入相关的数据库，然后再使用 SQL 语句进行重置密码的操作。

use mysql;

这句命令便是进入到名叫 mysql 的数据库当中。

update user set password = password('123') where user = 'root';

这句 SQL 便是进行密码重置的操作，将用户名是 root 的用户密码修改为 123。很明显可以看出上面语句中的 123 和 root 是可以根据我们的需要进行修改的，其中还有需要注意的地方就是 password('123') ,这里的 password 是一个函数名，因为数据库里面的密码都是加密存储的。

4.最后一步便是重启服务

上面的一步修改好密码之后，现在重启 MySQL 服务就可以使用新密码来登录使用数据库了，不过在重启服务器之前有一步需要注意，那就是要先关掉 mysqld.exe 进程，不然 MySQL 服务会启动不成功，关闭的方法最直接就是打开 "任务管理器"，然后找到该进程，并结束该进程。

最后一步就是重启 MySQL 服务，和在第一步中关闭服务一样，重启服务也有两种方式，第一种就是使用鼠标右键点击 "计算机" 图标，在其中选择 "系统服务" 的选项，然后在服务列表中找到 MySQL 的服务，双击选择启动该服务就好了。

第二种方式的话就是使用命令行方式了，首先打开命令行窗口，然后输入下面的命令：

net start mysql

这样也是能够开启 MySQL 服务的。

掌握如何修改数据库密码蛮重要的，因为这样的操作很难在可视化工具里面完成，掌握了之后还是可以节省不少时间的，不过也不需要刻意去记忆，要修改的时候到网上查就好了。

2.备份和还原

数据库的备份和还原相比上面的修改密码就要简单一点了，之所以可以这样说，是因为使用图形化界面工具可以快速地实现数据库的备份和还原，而不像要完成数据库密码的修改，还必须要使用命令行，因为在图形化工具里面的操作很简单，这里就不再说明了，主要介绍一下命令行方式是如何实现的吧。

备份数据使用的命令行：

mysqldump -u root -p p2p > d:/p2p.sql

上面的 p2p 就是要备份的数据库的名称了，大于号之后的便是要将数据备份到哪里的路径以及文件名了，其中文件名的后缀 sql 是固定的。

还原数据库有两种方式，不过操作起来都差不多。

第一种：

1.创建一个数据库
    create database mydb;
2.退出数据库
    \q 回车
3.使用命令还原数据库
    mysql -u root -p mydb < d:/p2p.sql

第二种：

1.创建一个数据库
    create database mydb2;
2.进入数据库
    use mydb2;
3.使用命令还原数据库
    source d:/p2p.sql

上面这就是使用命令行方式完成数据库备份和还原操作的步骤，其实只要知道了解一下就好了，因为使用数据库的图形化工具可以非常轻松地完成数据备份和还原。

3.多表之间的外键

外键是存在于多表之间的一种约束关系，说到约束关系，首先会想到单表之中的约束，单表之中的约束有主键约束(primary key)、唯一约束(unique)以及非空约束(not null)，单表中的约束主要是为了保证数据的完整性。

同样的，在多表之间也存在约束，那就是外键约束了，通过外键约束同样也可以保证数据的完整性和一致性。其实外键的含义就是，在当前表中的某个字段与另一个表之中的主键字段存在关系，那么就会为这个字段添加外键指向另一个表中的主键。

使用 SQL 创建外键约束：

1.创建表时增加外键
create table 表名(
    字段名    数据类型        约束条件，
    ...
    foreign key (当前表中的某字段) references dept(dept表中的主键字段)
);

2.后面修改表结构增加外键约束
alter table employee add foreign key (当前表中的某字段) references dept(dept表中的主键字段);

4.多表之间的关系

多表之间的关系一共可以分为三种，分别是一对多关系、多对多关系以及一对一关系。那在数据库之中应该如何表示它们之间的关系呢？

4.1 一对多关系

如何在数据库之中表示一对多的关系，上面就说到了两表之间的关系可以使用外键来表示，那应该如何体现这种一对多关系呢？解决的方法就是在多的一方创建一个外键，并用这个外键指向一的一方的主键。生活中常见的例子就是学生和班级的关系，一个学生只能属于一个班级，而一个班级可以对应多个学生，那为了表示学生和班级之间这种对应关系，就可以在学生的记录中添加一个班级字段用来存储该学生所属班级的信息，并将该字段作为一个外键指向班级表的主键，这样就可以了。

4.2 多对多关系

两表之间还有一种关系就是多对多的关系，那如何在数据库中表示这种关系呢？解决的办法就是创建一张中间表，并在这张中间表中建立对于原来两张表的外键引用，当然这张中间表中仍然可以带有自己的属性字段。生活中常见的例子就是在购物的过程中顾客与商品之间的关系了，一个顾客可以购买多种商品，一种商品同样也可以被多个顾客购买，这个时候就需要建立第三张表中间表了，这张表中同时会有对于顾客和商品的外键引用，这样就能在得到顾客信息的同时，也可以获得所购买商品的的信息了。

4.3 一对一关系

如果两张表之间是一对一关系，其实最直接的方式就是完全可以只使用一张表，然后将两张表之中不重复的字段都放在一张表中。

不过既然说了是两张表，那还是考虑如何建立这两张表之间的联系吧，有一个办法就是将其中一张表看做是多的一方，这样的话关系就变为一对多的关系，那只要在多的一方建立一个字段，并将该字段设置为对于另一张表主键的外键引用就好了，同时还要将这个外键设置为唯一的。生活中的例子就是每一个学生都对应着一个居住地址，其实完全可以在学生的记录中直接创建一个字段记录学生的居住地址，这样就是一张表了，还有就是在学生的这张表中创建一个外键，引用地址那张表的主键，并设置该外键是唯一的。

5.多表查询

有时候我们根据实际功能的需要会同时查询多个表，也就是多表查询，多表查询可以分为很多种，多表查询可以分为连接查询和子查询，而连接查询又可以分为交叉连接、内连接和外连接查询。

多表查询
    --连接查询
        --交叉连接
        --内连接
        --外连接
    --子查询

5.1 交叉连接

交叉连接查询到的是两个表中记录的笛卡尔积，也就是说如果第一张表中有 2 条记录，而第二张表中有 3 条记录，那么交叉查询得到的结果应该是 6 条记录，运算的过程就是使用第一张表的第 1 条记录去与第二张表的每条记录相连接，然后再用第一张表的第 2 条记录去与第二张表的每条记录相连接，如果还有多的记录的话也会以此类推进行连接。

交叉连接使用的关键字是 cross join,使用的语法如下：

1.select * from 表1 cross join 表2;
2.select * from 表1,表2;

5.2 内连接

内连接查询的作用就相当于是查询两个表共有的部分，通过 SQL 中给定的查询条件，将两个表中符合连接条件的记录连接起来，这样就可以得到返回结果了。内连接使用的关键字是 inner join ... on，其中的 inner 是可以省略的，因此下面的写法两种都是正确的。

1.显式内连接
    select * from 表1 inner join 表2 on 关联条件;
2.隐式内连接
    select * from 表1,表2 where 关联条件;

5.3 外连接

外连接查询分为左外连接查询和右外连接查询，说明一个另一个的含义也就清晰了，比如左外连接查询，得到的查询结果就是左边那张表里面所有的记录以及根据连接条件满足条件的右边表中的记录，当右边表中的记录满足条件时，左右的两张表中满足条件的记录就会连接，而右边表中不满足条件的记录则不会显示，左边表中的记录则会全部显示。右外连接和左外连接遵循相同的连接原理。

外连接查询使用的关键字是 left/right outer join ... on,其中的 outer 是可以省略的，语句如下：

1.左外连接
    语法：select * from 表1 left outer join 表2 on 连接条件;
2.右外连接
    语法：select * from 表1 right outer join 表2 on 连接条件;

外连接需要注意的一点就是查看关键字是 left 还是 right,如果是 left，那在查询的时候就应该以左边的那张表为主，如果是 right 的话，那就是应该以右边的那张表为主。

5.4 子查询

子查询的含义就是一个查询语句的条件必须依赖另一个查询语句的结果，也就是相当于 SQL 语句的嵌套查询，比如要查询班级中有学生是 90 后的班级信息，那我们在查询的时候就应该先查询是 90 后的学生信息，并选择出这些学生所属的班级编号，然后再查询班级表，找出班级表中班级编号在前面那些学生所属班级的班级信息，这样就能得到预想的效果了。

查询学生生日在91年之后的班级的信息。
语法：select * from classes where cid in (SELECT cno FROM student WHERE birthday > '1991-01-01');

上面的语句中使用了关键字 in，表示的作用就是在给定的结果内查询，其实还有和它位置一样但是表示不同含义的关键字，比如 exists，它所表示的作用就是只有当 exists 后面的查询结果存在的时候才会执行前面的 SQL 语句，还有一个 any ，表示的就是只要满足 any 后面查询结果中的一个就行了，不过也可以有下面这样的变化：

SELECT * FROM classes WHERE cid > ANY (SELECT cno FROM student);

大于 any 表示的就是只要大于后面查询结果中最小的就可以了，当然也会有小于 any，表示的就是只要小于后面查询结果中最大的就行了。

SELECT * FROM classes WHERE cid > (SELECT min(cno) FROM student );
SELECT * FROM classes WHERE cid  <  (SELECT max(cno) FROM student );

其实上面的这两句也可以表示一样的意义。

子查询中还有一个关键字，那就是 all 了，表示的就是要满足后面所有的查询结果才行。

SELECT * FROM classes WHERE cid > ALL (SELECT cno FROM student);

5.5 内连接和外连接的区别

其实关于内连接和外连接的区别，使用数学中的集合也许可以很清楚地表示，使用内外连接的时候就可以将连接的两张表看作是两个集合，内连接查询的结果就相当于是两个集合的交集，而外连接查询因为会分以哪张表为主，如果以左边的那张表为主的话，这就是左外连接了，得到的结果就是上面得到的交集还要加上左边那和右边没有交叉的部分，至于右外连接也是和左外连接相类似的了。

需要注意的一点就是上面提高的交集，其实是左右两张表满足连接条件的记录连接得到的结果。