一文学懂数据库（持续更新）

柔情只为你懂 2022-12-09 05:13 175阅读 0赞

### 文章目录 ###

*   *  一、数据库的操作
     *  二、表的基本操作
     *   *  2.1 表的数据类型
         *  2.2 表的基本操作
     *  三、数据库的多表查询操作
     *   *  3.1 表的四种连接方式
         *  3.2 表的关联子查询
         *  3.3 in、any和some的用法
         *  3.4 as的使用
     *  四、数据记录的基本操作
     *   *  4.1 增加数据记录
         *  4.2 修改数据记录
         *  4.3 删除数据记录
         *  4.4 查询数据记录
     *  五、数据库备份以及多表设计
     *   *  5.1 数据库的备份及恢复
         *  5.2 表与表之间的关系
         *  5.3 一对多表及一对一表设计
         *  5.4 多对多表设计
     *  六、知识总结

## 一、数据库的操作 ##

1.  查看所有的数据库：show databases;
2.  查看数据库的编码表：show create database 表名;
3.  创建数据库：create database 表名;
4.  以特定的编码表创建数据库：create database 表名 character set 编码格式;
5.  删除数据库：drop database 表名;
6.  修改数据库表的编码：alter database 表名 character set 编码格式;
7.  使用数据库(切换使用的数据库)：use 表名;
8.  查看正在使用的是哪个数据库：select database();

## 二、表的基本操作 ##

### 2.1 表的数据类型 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 1]  
注意：

1.  bit字段在显示的时候，默认的是按 ASCII码对应的值显示。
2.  在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的，可以通过UNSIGNED来说明某个字段是无符号的。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 2]  
注意：data占用3字节；datatime占用8字节；timestamp(从1970年开始)占用4字节

### 2.2 表的基本操作 ###

1.  创建名为stu的表：create table stu;(里边说明表每个字段的类型);
2.  展示这个数据库的表：show tables;
3.  查看表stu的创建信息：show create table stu;
4.  添加约束：  
    主键约束且自增 id int primary key auto\_increment;  
    唯一约束：name varchar(20) unique;  
    非空约束：age int not null;
5.  对表的操作：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 3]  
    注意：假如说刚开始name字段设置的长度为20，后期想把它的长度改为10的时候，这个时候如果name字段中含有的字符串的长度有大于10的话就无法修改成功。
6.  删除表/清空表  
    主要是delete、drop、truncate的区别  
    （1）功能上：delete和truncate仅仅是删除表的数据(delete 是删除一条数据，truncate是删除表的所有数据)，drop是表的结构和数据一起删除了。  
    （2）安全性：delete操作完不提交事务的话还可以回滚，但是truncate和drop是操作完立即生效的，无法恢复。  
    （3）效率上：drop>truncate>delete。

## 三、数据库的多表查询操作 ##

### 3.1 表的四种连接方式 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 4]

*  内连接：就是表A和表B相交的部分，内连接又分为隐式内连接和显示内连接，两者没有什么区别，只是在写法上有所不同。  
    隐式内连接：select \* from +表A+where+表B+A与B相交的部分;  
    显式内连接：select \* from +表A+inner join+表B+on+A与B相交的部分;
 *  外连接：外连接分为左外连接，右外连接，全外连接  
    左外连接：显示左表的全部记录以及右边符合连接条件的记录：select \* from 表Ａ＋left outer join+表B+on+A与B相交的部分;  
    右外连接：显示右表的全部记录以及左边符合连接条件的记录：select \* from 表A+ right outer join+表B+on+A与B相交的部分;  
    全外连接：显示左表和右表的全部记录但去除两表相交的部分：select \* from 表A+ full outer join+表B+on+A与B相交的部分;  
    注意点：全外连接的这个sql语句在MySQL数据库中不支持，Oracle数据库才支持，在MySQL 中应该是这样的：select \* from 表Ａ＋left outer join+表B+on+A与B相交的部分 **union** select \* from 表A+ right outer join+表B+on+A与B相交的部分;  
    也就是全外连接=左外连接+右外连接所以多了一个相交的部分，那么就要去重。  
    这里的union是去掉重复的数据的意思，而union all是不去掉重复数据的意思。

### 3.2 表的关联子查询 ###

*  假设现在有一个表是成员信息表，里边的字段有成员的ID，年龄，现在有一个需求是查询年龄最小的部门成员信息，有哪些方法呢？  
    （1）常规方法：先通过聚合函数查询出最小的年龄，再根据年龄查询最小年龄的成员信息；这样做的不好处是需要访问数据库两次，并且第二次查询要建立在第一次查询的结果之上。  
    SQL为：select min(age) from A; select \* from A where age = xxx;  
    （2）通过子查询：也就是将常规方法中的两步结合成一步了，语法：A查询语句作为B查询语句的条件，那么A查询称之为子查询，B查询称之为主查询。子查询都要写在 () 里，且执行要先于主查询。  
    SQL为：select \* from A where age=(select min(age) from A);  
    （3）all的用法：SQL为：select \* from A where age<=all(select age from A);  
    翻译过来就是：年龄小于或等于从A表中查到的所有年龄，也就是最小年龄了。

### 3.3 in、any和some的用法 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 5]  
（1）常规方法：第一步先从中间表查询出money大于10000的coder\_id，第二步根据查询到的code\_id查询对应的code信息。  
SQL为：select coder\_id from code\_project where money>10000; select \* from coder where id=xxx;  
（2）运用子查询：

*  in的使用：in就是表示包含子查询查出的数据，当子查询的结果只有一个时，可以用=，也可以用in；但是当子查询的结果有多个时，不可以用=，只能用in。  
    SQL为：select \* from coder where id in(select coder\_id from code\_project where money>10000);
 *  any和some的使用：虽然子查询的结果有多个，但是我们仍然想用=，这个时候我们可以用any或者是some；any表示任意的意思，也就是=子查询查出的任意一个数据；some也就是=子查询查出的一些数据，作用和any是一样的。  
    SQL为：select \* from coder where id=any(select coder\_id from code\_project where money>10000);

### 3.4 as的使用 ###

接上一个图，现在有一个新的需求是：查询money大于10000的程序员信息和对应的money，该怎么做呢？  
思路分析：

*  我们从中间表coder\_project中可以找到满足条件的coder\_id和money，结果是一张新的表，我们取名为temp表，然后根据code\_id（临时表）找出所有对应的程序员信息（coder表）和money（临时表）。  
    但是呢这张临时表本身是不存在的，我们没法直接在第二张表中用，temp表其实就是中间表coder\_project中的一部分满足条件的数据。  
    有这张临时表之后：  
    （1）我们需要对应程序员的所有信息：也就是coder.\*  
    （2）我们需要temp表中的money  
    （3）从temp表、coder表中查询  
    （4）coder表中的id和temp表中的coder\_id相同，也就是内连接相等的部分。  
    SQL为：select coder\_id,money from coder\_project where money>10000;  
    select coder.\*,money from temp,coder where coder\_id=temp.coder\_id;
 *  应用as定义临时表：  
    select coder.\*,money from(select coder\_id,money from coder\_project where money>10000) as temp.coder where coder.id=temp.coder\_id;

## 四、数据记录的基本操作 ##

### 4.1 增加数据记录 ###

*  增加数据全写的情况  
    给表添加一行数据，每一列都要赋值，格式为 insert into+表名+所有列名+values+所有值，比如：insert into stu(id,name,gender,address)values(1,‘小高’,‘男’,‘中国’);  
    **但是呢，当有一列的数据可以为空或者由默认值（主键设置自增长时也算有默认值），可以省略不赋值，所以上述的SQL可以写为 insert into stu(name,gender,address)values(‘高层’,‘男’,‘美国’);**
 *  增加指定列的数据  
    也就是上边加粗的字体，格式为：insert into+表名+指定列+values+对应值;
 *  省略所有列（简写）  
    将所有列名都省略，但是每一列都要赋值，格式为：insert into+表名+values+所有值，比如： insert into stu values(1,‘小高’,‘男’,‘中国’);
 *  注意：  
    （1）SQL的字符串用的是单引号  
    （2）列约束是unique、唯一的时，添加数据不允许重复  
    （3）列约束是not null、不允许重复的，同时又没有默认值的话，那么不能不添加数据。

### 4.2 修改数据记录 ###

*  修改整列的数据  
    比如update stu set address=‘中国’; 是说把地址这一列的所有数据都修改为中国，格式为：update+表名+set+列名+值
 *  修改指定行的列数据  
    比如update stu set gender=‘女’ where name=‘张三’; 是说将gender这一列中的张三的性别改为女，格式如下：update+表名+set+列名+值+where+指定条件
 *  使用运算修改指定行的数据  
    比如update stu set age=age+2 where name=‘张三’; 是说将张三的年龄加两岁，不过值得注意的是sql里没有+=这样的运算符，!= 和< >都表示不等于。

### 4.3 删除数据记录 ###

*  删除指定的数据  
    比如delete from stu where name=‘张三’;指的是删除名字为张三的整行数据，格式为：delete from+表名+where+指定条件
 *  删除表的所有数据  
    （1）可以用delete from +表名; 这个删除数据是一行一行的删除，数据有可能会被恢复。  
    （2）可以用truncate table+表名; 这个是先删除表，再创建表，相当于只是清空了表的数据，但格式还是在的。  
    （3）可以用drop，但是这个是删除表的结构与数据，是无法恢复的。

### 4.4 查询数据记录 ###

此处内容较多，还请转战此文章：[传送门][Link 1]

## 五、数据库备份以及多表设计 ##

### 5.1 数据库的备份及恢复 ###

*  备份：mysqldump -u root -p stu > C:/student.sql 这个是将stu数据库备份到C盘的目录下，取名为student.sql  
    语法为：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径
 *  恢复的两个方法：  
    （1）mysqldump -u root -p xiaogao < C:/student.sql 这个是将C盘下的student.sql文件恢复到数据库中，取名为xiaogao  
    语法为：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径  
    （2）source C:/student.sql 语法为：source+磁盘SQL文件路径

### 5.2 表与表之间的关系 ###

*  一对多：一个部门有多个成员，一个成员只属于一个部门，所以是1对多。
 *  多对多：一个程序员会开发多个项目，一个项目会被多个程序员开发，所以是多对多。
 *  一对一：一个丈夫只能有一个妻子，一个妻子也只能有一个丈夫。

### 5.3 一对多表及一对一表设计 ###

以一个部门表和成员做例子：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 6]  
对部门表而言，一共有三个部门，每个部门都有自己的ID，现在我们想要将这两张表联系起来，首先最能想到的是在部门表加入成员表的ID，但是这样的话一行需要添加多个数据，显然是不行的。  
对成员表来说，现在我们想要将这两张表联系起来，我们可以在每个成员的后边加一个属性，也就是自己所属的部门ID，这样就方便判断。但是现在的问题是如果只这样处理的话，实际上两个表是没有关联起来的。可以这么理解：假如将部门表的某个部门删除了，但是在成员表中这个部门仍然存在，这个时候我们就要引入外键来将这两个表真正的关联起来。

*  添加外键约束  
    外键就是指关系表中的主键，它可以保持数据的一致性和完整性，使两张表形成关联。  
    （1）在建表时就添加外键约束  
    比如在成员表添加每个成员的部门ID时，SQL语句为：在建成员表时添加语句foreign key (dept\_id) references department(id)，语法为：**foreign key(中间表的字段) references 主表(被引用的字段)**，这就将两张表给关联起来了。其中，这里的部门表（1对多中的1）叫主表，这里的成员表（1对多中的多叫做从表）。  
    （2）在建表后添加外键约束  
    比如在成员表添加每个成员的部门ID时，SQL语句为：alter table member(成员表名) add foreign key (dept\_id) references department(部门表名) (id);  
    （3）注意点：**对这两种添加外键约束而言，必须保证从表中和其相关的数据不存在**，也就是说我们想删除部门表的某个部门，得保证成员表中没有该部门的成员。

一对一表设计和一对多表设计是相似的，也就是任意一张表将另一张表的ID作为外键就可以了。

### 5.4 多对多表设计 ###

比如说我们现在创建了这么两张表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 7]  
然后怎么将这两个多对多的两张表给对应起来呢？我们需要创建一个中间表将这两个表关联起来。值得注意的是，中间表应该有如下特点：  
（1）中间表表名一般将这两个表名结合起来，方便理解。  
（2）中间表有两个外键，这两个外键分别对应着两张表的主键。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 8]

## 六、知识总结 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 9]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 10]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/0af73b75650a4f5a89e4b0e223ddccb5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/b9868148e78f434bb4d5b43341684ab6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/a10fdcbd810a45d787d42c317bbcf822.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/f762c1a88304408cbe70ca86f160d0fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/8a469304c6d74cfc941a652ff4a428ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/b9387ad5a998469f97a6e42c4068d65b.png
[Link 1]: https://blog.csdn.net/qq_44840148/article/details/109534347
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221123/ea75b05db3dd4960ac32dd58fc9dfaf5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221123/375d33a67c434473a5a8e7047ddab334.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221123/2153da7242004e06aced771c4f542718.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221123/945fad19a561401f9fc175a34a327ab6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0ODQwMTQ4_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20221123/d5789791f9864a88b000f3c00dc543c4.png