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

一、数据库的操作

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 表的数据类型

注意：

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

注意：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. 对表的操作：
   
   注意：假如说刚开始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 表的四种连接方式

• 内连接：就是表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的用法

（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 查询数据记录

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

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

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 一对多表及一对一表设计

以一个部门表和成员做例子：

对部门表而言，一共有三个部门，每个部门都有自己的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 多对多表设计

比如说我们现在创建了这么两张表

然后怎么将这两个多对多的两张表给对应起来呢？我们需要创建一个中间表将这两个表关联起来。值得注意的是，中间表应该有如下特点：
（1）中间表表名一般将这两个表名结合起来，方便理解。
（2）中间表有两个外键，这两个外键分别对应着两张表的主键。

六、知识总结