数据库MYSQL（最全的总结）

「爱情、让人受尽委屈。」 2021-09-19 20:26 259阅读 0赞

**目录**

【一】常见的数据库

【二】Mysql——Sql语句的分类

【三】数据库的操作

1.创建数据库

2.查看数据库的定义

3.修改数据库

4.删除数据库

5.切换数据库

6.查看当前正在使用的数据库

【四】表的操作（CRUD——增删改查）

1.创建表

2.列的类型：

3.列的约束

4.查看表

5.删除表

6.插入数据

7.删除数据

8.修改表

1）添加列（add）

2）修改列（modify）

3）修改列名（change）

4）删除列（drop）

5）修改表名（rename）

6）修改表的字符集

9.更新表记录

10.查询数据

【五】多表查询

1.交叉连接查询

2.内连接查询

3.左外连接查询

4.右外连接查询

5.分页查询

6.子查询

【六】索引

【七】建标及建库原则

--------------------

# 【一】常见的数据库 #

1.关系型数据库：

mysql:开源免费的适用于中小型企业的数据库，后被Sun公司收购，再后来Sun公司被Oracle公司收购，Oracle公司开始收费。

mariadb：由MySQL创始人开发，是mysql开源版本的一个分支，基本上所有的命令与mysql相同。

Oracle：适用于大型电商网站，收费软件。

sqlserver：windows里面的，常用于政府网站。

关系型数据库：主要用来描述实体与实体之间的关系。

E-R关系图（Entity-Relationship）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70][]

2.非关系型数据库（key：value）：如redis，mongodb

# 【二】Mysql——Sql语句的分类 #

DDL（数据定义语言）：定义数据库、数据表它们的结构。——create（创建）、drop（删除）、alter（修改）

DML（数据操纵语言）：主要是用来操作数据。——insert（插入）、delete（删除）、update（修改）

DCL（数据控制语言）：定义访问权限、取消访问权限、安全设置。——grant

DQL（数据查询语言）：——select（查询）  from子句   where子句

# 【三】数据库的操作 #

## 1.创建数据库 ##

create database 数据库名；

![20190617191241529.png][]

create database 数据库名  character set 字符集；

create database 数据库名  character set 字符集 collate 校对规则；

[校对规则][Link 1]：在数据库输入show collation,查询所有校对规则

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1][]

## 2.查看数据库的定义 ##

show create database 数据库名；

![20190617191328441.png][]

## 3.修改数据库 ##

ater database 数据库名 character set 字符集；（修改数据库的字符集）

![20190617191515745.png][]

## 4.删除数据库 ##

drop database 数据库名；

没有删数据库之前：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 2][]

删除数据库之后：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 3][]

## 5.切换数据库 ##

use 数据库名；

![20190617191741926.png][]

## 6.查看当前正在使用的数据库 ##

select database()；

![20190617191827625.png][]

# 【四】表的操作（CRUD——增删改查） #

## 1.创建表 ##

create table 表名(

列名1   列的类型  约束，

列名2   列的类型  约束，

........

);

![20190617192004322.png][]

## 2.列的类型： ##

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td>java</td> 
 <td>sql</td> 
 </tr> 
 <tr> 
 <td>int</td> 
 <td>int&nbsp;</td> 
 </tr> 
 <tr> 
 <td>char/String</td> 
 <td> char/varchar&nbsp; &nbsp; &nbsp; char：固定长度 varchar：可变长度 eg：char(3)&nbsp; &nbsp;"1"==="1&nbsp; "&nbsp; &nbsp; &nbsp;varchar(3)&nbsp; "1"==="1" char类型无论字符长度为多少，都按其固定长度计算，如例子中的1，在char类型中存储的为1，null，null；在varchar中存储的是1 </td> 
 </tr> 
 <tr> 
 <td>double</td> 
 <td>double&nbsp;</td> 
 </tr> 
 <tr> 
 <td>float</td> 
 <td>float</td> 
 </tr> 
 <tr> 
 <td>boolean</td> 
 <td>boolean</td> 
 </tr> 
 <tr> 
 <td>date&nbsp;</td> 
 <td> date(yyyy-MM-dd) time(hh:mm:ss) datetime(yyyy-MM-dd&nbsp; hh:mm:ss&nbsp;默认为null) timestamp(yyyy-MM-dd&nbsp; hh:mm:ss&nbsp;默认为当前时间) </td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td> text&nbsp;主要用于存储文本 blob&nbsp;主要用于存储二进制 </td> 
 </tr> 
 </tbody> 
</table>

## 3.列的约束 ##

主键约束：primary key

默认就是不能为空，唯一外键都是指向另外一张表的主键。主键一张表只能有一个

> 主键约束相当于唯一约束和非空约束。每个表中最多允许有一个主键。

外键约束：foreign key

指定该行记录从属于主表中的一条记录。

唯一约束：unique

列名的内容必须是唯一的，不能出现重复情况；唯一约束不可以作为其他表的外键；一张表中可以有多个唯一约束。

> 虽然唯一约束不可以出现重复值，但可以出现多个null值。同一个表内可以建多个唯一约束。

非空约束：not null，指定的列不能为空。

> Sql中的null具有如下特性：
> 
> 1.所有的数据类型的值都可以为null。
> 
> 2.与java类似的是空字符串不等于null，0也不等于null。

## 4.查看表 ##

1）查看所有表：show tables;

![20190617192028484.png][]

2）查看表结构：desc 表名;

![20190617192054524.png][]

3)查看表的定义语句：show create table 表名;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 4][]

## 5.删除表 ##

drop table 表名;

没有删除表之前：

![20190617192157499.png][]

删除表之后：

![20190617192235837.png][]

## 6.插入数据 ##

1）insert into 表名(列名1，列名2...列名n)  values(值1,值2...,值n);

![20190617192530740.png][]

2）简单写法：insert into 表名 values(值1,值2...,值n);

![20190617192611696.png][]

注意:如果只是插入部分列的话，列名不能省略。

3）批量插入：insert into 表名 values(值1,值2...,值n)，(值1,值2...,值n)，...(值1,值2...,值n);

![20190617192807392.png][]

> 批量插入与单条插入的效率问题：
> 
> 1）批量插入
> 
> 如果是一条一条写，要涉及每一条语句去翻译，很费时，所以批量插入效率更高一点。
> 
> 但是，如果批量插入的过程中，有一条语句出现问题，批量插入失败。
> 
> 1）批量插入
> 
> 时间较慢，但是如果语句有错，改起来比较方便。

## 7.删除数据 ##

delete from 表名\[where 条件\];

未删除数据前，xuesheng表中的所有数据：

![20190617193024778.png][]

删除学生年龄为19的学生后：

![20190617193109687.png][]

![201906171931290.png][]

\#\#delete from 表名;如果没有指定条件，会将表中的所有数据一条一条的全部删除。

> 面试问题：请说明delete删除数据和truncate删除数据的区别？
> 
> delete :DML,一条一条的删除表中的数据。
> 
> truncate：DDL,先删除表，再重建表。
> 
> 关于执行效率的问题，具体要看表中的数据：如果数据量比较少，采用delete比较高效；如果数据量比较多，采用truncate比较高效。

![20190617193328676.png][]

## 8.修改表  ##

### 1）添加列（add） ###

alter table 表名 add 列名 列的类型 列的约束;

eg: alter table xuesheng add id int primary key auto\_increment;

### 2)修改列（modify） ###

alter table 表名 modify 列名 列的类型;

eg:

xuesheng表未改动之前：

![20190617193656517.png][]

改动之后：

alter table xuesheng modify sname varchar(5);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 5][]

### 3）修改列名（change） ###

alter table 表名 change 列名(原)  列名(改后)  列的类型;

eg:

![20190617193953528.png][]

### 4）删除列（drop） ###

alter table 表名 drop column 列名;

eg:

![20190617194212648.png][]

### 5)修改表名（rename） ###

rename table 表名(原) to 表名(改后);

eg:

![2019061719425645.png][]

### 6)修改表的字符集 ###

alter table 表名 character set 字符集；

eg：

![20190617194419175.png][]

## 9.更新表记录 ##

update 表名 set 列名1=值1，列名2=值2，...列名n=值n \[where 条件\];

如果参数是字符串、日期要加上单引号。

\#\#update 表名 set 列名1=值1，列名2=值2，...列名n=值n；不加条件将修改表中的所有数据对应的相关列的值。

## 10.查询数据 ##

1）select  \[distinct\] \[\*\] \[列名\] from 表名\[where 条件\]；（\[\]表示可选的条件）

distinct表示去除重复数据；

as别名查询，as可以省略

表别名：select s.sname,s.sage from student s;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 6][]

列别名:select name as stuname,age as stuage from student;

2）运算查询

select \* ,age\*1.5 from student;（显示所有数据，并增加一列，列名为age\*1.5）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 7][]

3）运算查询

仅仅在查询结果上做+ - \* /,where后的条件写法 关系运算符 >  >=  <  <=  !=  <>

<>:为不等于，标准的SQL语句

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 8][]

!=:为不等于，非标准的SQL语句

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 9][]

判断某一列是否为空 is null;is not null

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 10][]

逻辑运算:and  or  not

模糊查询: like

\_代表一个字符；%代表多个字符

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 11][]

![20190617201751674.png][]

in在某一范围中获得值

![20190617201842334.png][]

排序查询 order by +关键字（默认为升序）

asc:ascend，升序

desc：descend，降序

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 12][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 13][]

聚合函数

sum():求和

![20190617202245158.png][]

avg():求平均

![20190617202345562.png][]

count():统计数量

![20190617202416841.png][]

max():求最大值

![20190617202454996.png][]

miin():求最小值

![20190617202511151.png][]

\#\#where条件后不能跟聚合函数

分组 group by

having 关键字 可以接聚合函数  出现在分组之后

where 关键字 不可以接聚合函数  出现在分组之前

执行顺序

from..where...group by...having...select...order by

--------------------

# 【五】多表查询 #

## 1.交叉连接查询 ##

select \* from product,category;

笛卡尔积,查出来的是两张表的乘积

过滤出有意义的数据：select \* from product,category where cid=cno;

## 2.内连接查询 ##

1）隐式内连接

select \* from product p,category c where c.cid=p.cno;

2）显式内连接

select \* from product p  inner join category c on c.cid=p.cno;

> 区别：
> 
> 隐式内连接:在查询出结果的基础上做where条件过滤。
> 
> 显式内连接：带着条件去查询结果，执行效率高。

## 3.左外连接查询 ##

select \* from product p left outer join category c on p.cno=c.cid;

左外连接会将左表中的所有数据都查询出来，如果右表中没有对应的数据用null代替。

## 4.右外连接查询 ##

select \* from product p right outer join category c on p.cno=c.cid;

右外连接会将右表中的所有数据都查询出来，如果左表中没有对应的数据用null代替。

## 5.分页查询 ##

limit x,y

x为起始索引，y为每页显示的条数。

## 6.子查询 ##

[https://blog.csdn.net/The\_Best\_Hacker/article/details/81976139][https_blog.csdn.net_The_Best_Hacker_article_details_81976139]

# 【六】索引 #

索引是存放在模式中的一个数据库对象。创建索引的唯一作用就是加速对表的查询，索引通过使用快速路径访问方法来快速定位数据，从而减少磁盘的I/O。

索引作为数据库的对象，在数据字典中独立存放，但不能独立存在，必须属于某个表。

> 索引的缺点：
> 
> 1.当数据表中的记录被添加，删除、修改时，数据库系统需要维护索引，因此有一定的系统开销。
> 
> 2.存储索引需要一定的磁盘空间。

1.创建索引

1）自动：当在表上定义了主键约束、唯一约束和外键约束，系统会为该数据列自动创建对应的索引。

2）手动：create index 索引名 on 表名（列名）

eg: create index index\_sname on student(sname);

2.删除索引

1）自动：数据表被删除时，该数据表中的索引自动被删除。

2）手动：drop index 索引名 on 表名。

eg:drop index index\_sname on student；

# 【七】建标及建库原则 #

1.多表之间的关系如何维护？外键约束 foreign key

添加一个外键：alter table product add forign key(cno) references  category(cid);

添加一个字段：foreign key(cno) references category(cid);

删除数据的时候先删除外键关联的所有数据，最后删除分类的数据。

2.数据库的建库原则

通常情况下，一个项目/应用建一个数据库。

3.多表之间的建表原则

*  一对多

建表原则：在多的一方创建一个外键指向一的一方的主键。

*  多对多

建表原则：建立一张中间表，将多对多的关系拆成一对多的关系，中间表至少要有两个外键，分别指向原来的两张表。

*  一对一

建表原则：将一对一的情况当做一对多的情况，在任意一张表添加一个外键，并且这个外键要唯一指向另一张表。

直接将两张表合成一张表。

将两张标的主键建立起连接，让两张表的主键相同。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70]: /images/20210811/2cf9fb7ce4f84cfea7c45f6a9c706aef.png
[20190617191241529.png]: /images/20210811/5f265b5b39b34f80b8c0e02b54acafe9.png
[Link 1]: http://www.360doc.com/content/15/0702/16/18139076_482169344.shtml
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 1]: /images/20210811/0fc79d7cdd7d468a94cb41f383905fb7.png
[20190617191328441.png]: /images/20210811/50c138c131fe45babe9cc743d9de5ebc.png
[20190617191515745.png]: /images/20210811/75aa07e65eba459fb6b00e1a56934bfc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 2]: /images/20210811/e0b476ac33c447e08c524308d9d83d9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 3]: /images/20210811/03ae79af7cdc4a789f1c06e3148ea14e.png
[20190617191741926.png]: /images/20210811/b4ddca08005042529ecf80db9c629c89.png
[20190617191827625.png]: /images/20210811/43890b510400485ea5fbc76af73ad87e.png
[20190617192004322.png]: /images/20210811/07b9f04ce90d41479bb3efa052d25ada.png
[20190617192028484.png]: /images/20210811/59a54f4740594cc4ae68b6b0e537dd85.png
[20190617192054524.png]: /images/20210811/012c39aa039e44cfa4234f31dd6a2ca4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 4]: /images/20210811/eb2560b3f0de454fbf7d0a0a14e35b1c.png
[20190617192157499.png]: /images/20210811/f27af355dd794b3b9599424779cd7762.png
[20190617192235837.png]: /images/20210811/f80f4b4bc7974effb5c77908c71cecc1.png
[20190617192530740.png]: /images/20210811/e536068aeded42438d5219149a4d9956.png
[20190617192611696.png]: /images/20210811/2bad7a9d53a4473fbe9ef827e421f6bb.png
[20190617192807392.png]: /images/20210811/e73584153cf84eb7b95778b106a7eded.png
[20190617193024778.png]: /images/20210811/04a632dd50454ffcb9e2f036f0ee0582.png
[20190617193109687.png]: /images/20210811/6f76b62ad84143b3a1e148540dbed1ff.png
[201906171931290.png]: /images/20210811/ac6022e0178b44c0af1e14067d45d670.png
[20190617193328676.png]: /images/20210811/3ea3c05b5a3244a3a7c3daea2c5b320e.png
[20190617193656517.png]: /images/20210811/cc44e4f887884db59ab764cb7441b457.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 5]: /images/20210811/fce91609fcd54fc8850f1e074bf7c5af.png
[20190617193953528.png]: /images/20210811/6a36c871fa324b82b3b2508b0122e566.png
[20190617194212648.png]: /images/20210811/4758fe77b4b74fb5b12b4b0bbce474a2.png
[2019061719425645.png]: /images/20210811/887845bf01c64b5884fd85ea0130bcc1.png
[20190617194419175.png]: /images/20210811/e32e627df205418ebd98509c9496f517.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 6]: /images/20210811/7f03caa7725d466e871c2c18c70deaf6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 7]: /images/20210811/89989d14cbdf4a94962dc9a6366e6f6d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 8]: /images/20210811/dea698693a014f5a8675513fa6e329b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 9]: /images/20210811/dec1ca0813a343069577e495fa03aaae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 10]: /images/20210811/37c9d5432c1446fba680d87443f5bd2f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 11]: /images/20210811/79e49cfdd1df4b2a818baabbb4ddf8b0.png
[20190617201751674.png]: /images/20210811/6f20c8f49eec464583ee57876890e42e.png
[20190617201842334.png]: /images/20210811/78a94e4f9f8442159bd97ec34523dd79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 12]: /images/20210811/8cff60c9fdd540499879a7966e6f6111.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZV9CZXN0X0hhY2tlcg_size_16_color_FFFFFF_t_70 13]: /images/20210811/a169e7ab37214fb98a16630e66c61c87.png
[20190617202245158.png]: /images/20210811/605820cfc29942d8937417d9bfd1ec6a.png
[20190617202345562.png]: /images/20210811/77320e32d88149a0adeb33edc49f253e.png
[20190617202416841.png]: /images/20210811/bbecffe7fb7d4b1a875ee719f780ba6c.png
[20190617202454996.png]: /images/20210811/b2491f6ad07a43288e82884b69999fc4.png
[20190617202511151.png]: /images/20210811/80deea4ed15842669feb41b8fab13036.png
[https_blog.csdn.net_The_Best_Hacker_article_details_81976139]: https://blog.csdn.net/The_Best_Hacker/article/details/81976139