mysql知识点详细总结

为什么我们需要学习数据库？

 因为数据库是应用系统开发必备技能，mysql是互联网应用使用最广泛的数据库。所以我们需要学习数据库相关知识。总之，不管大家是因为什么原因学习MySQL相关知识，我希望我的分享能够帮助到大家更好的学习mysql相关知识。

一、数据库基本知识

1、数据库相关概念

 数据库(DataBase)：存储数据的仓库，数据是有组织的进行存储。简称：DB。

 数据库管理系统（DataBase Management System）：模拟和管理数据库的大型软件。简称DBMS。

 SQL(Structured Query Language):操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

这三者的关系是：程序员可以通过sql操作数据库管理系统，实现增删改查数据库中的数据。

 关系型数据库（RDBMS）:建立在关系模型基础上，由多张相互连接的二维表组成的数据库。（巧记：通过表存储数据的数据库就是关系型数据库）

 关系型数据库的特点：使用表存储数据且使用sql语句操作，格式统一便于维护；

二、SQL语句相关知识

1、SQL通用语法

 ①SQL语句可以单行或多行书写，以分号结尾；

 ②SQL语句可以使用空格或缩进增强语句的可读性；

  ③MySQL数据库的SQL语句不区分大小写，关键字建议使用大写；

2.注释方法：

 mysql的注释方法用两种：单行注释和多行注释。

  单行注释：--注释内容 或者#注释内容

 多行注释：/注释内容/

3.SQL语句的分类

 ①DDL(Data Defintion Language):数据定义语句，用来定义数据库对象（数据库、表、字段）；

 ②DML（Data Manipulation Language）:数据操作语句，用来对数据库表中的数据进行增删改；

 ③DQL(Data Query Language):数据查询语句。用来查询数据库中表的记录；

 ④DCL(Data Control Language):数据控制语句，用来创建数据库用户、控制数据库的访问权限。

三.DDL——数据定义语句

1.DDL——数据库操作

查询所有数据库： show databases;

使用数据库：use 数据库名；

查询当前数据库：select database();

创建数据库：create database [if not exists] 数据库名 [defaullt charset 字符集] [collate 排序规则] （注意事项：创建语句中所有的中括号内容都可以省略）

删除数据库：drop database [if exists] 数据库名；

2.DDL——数据库表查询操作

查询当前数据库中所有表：show tables;

查询表结构: desc 表名；

查询指定表的建表语句：show create table 表名；

3.DDL——数据库表创建操作

 create table 表名（

 字段1 字段1类型 [comment 注释]，

 字段2 字段2类型，

 字段3 字段3类型

）；（注意事项：中括号中的内容可省略）

4.DDL——数据库表操作中的数据类型

Mysql中的数据类型有很多，主要分为：数值类型、字符串类型、日期类型。

5.DDL——数据库表修改操作

添加字段：alter table 表名 add 字段名 类型（长度）；

修改表中数据类型：alter table 表名 modify 字段名 新数据类型（长度）；

修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型（长度） ；

删除字段：alter table 表名 drop 字段名；

修改表名：alter table 表名 rename to 新表名；

删除表：

第一种方法：drop table 表名；

第二种方法：删除指定表，并重新创建新表：truncate table 表名；（即将表中的数据清空）

四.DML——数据操作语句

DML是数据操作语句，用来对数据库中表的数据记录进行增删改操作。

添加数据（insert）

修改数据（update）

删除数据（delete）

1.DML——添加数据

添加数据分为3类：

 给指定字段添加数据(顺序是固定的)：insert into 表名（字段名1，字段名2...） values(值1，值2,......)

 给全部字段添加数据：insert into 表名 values （值1，值2，...）;

 批量添加数据：insert into 表名（字段名1，字段名2，....）values (值1，值2，...),(值1，值2，...);

 insert into 表明 values(值1，值2，...),(值1，值2，...);

注意事项：

 如果一次性添加多条数据，则每条数据之间用逗号隔开。

2.DML——修改数据

修改数据：update 表名 set 字段名1=值1，字段名2=值2，...[where 条件];

这个语句如果写了where语句，那么就先判断条件是否满足，如果满足where条件，那么就执行update语句。如果没有写where语句，就更新写出的字段名。

注意事项：如果一次性修改多个数据，那么每个数据之间用逗号隔开。

3.DML——删除数据

删除数据：delete from 表名 [where 表名]

注意事项：条件可有可无，如果没有条件，则会删除整张表中的所有数据。delete语句不能删除某一个字段的值。

五、DQL——数据库查询语句

DQL是数据查询语句，用来查询数据库中表的记录。

DQL语法：

 select 字段列表 from 表名列表 （基本查询）

 where 条件列表 （条件查询）

 group by 分组字段列表（聚合函数查询，分组查询）

 having 分组后条件列表

 order by 排序字段列表 （排序查询）

 limit 分页参数（分页查询）

1.基本查询

查询多个字段：select 字段1，字段2，... from 表名；

查询表中的全部信息：select * from 表名；（*）

设置别名：select 字段1 [as 别名1]，字段2[as 别名2]，... from 表名

去除重复记录：select distinct 字段列表 from 表名；

2.条件查询

条件查询语法：select 字段列表 from 表名 where 条件列表；

查询学生表中学号在001到003之间的学生信息：

查询学生表中学生名称，长度为6的学生信息：

查询学生表中学生名称最后两个字母为“hu”的学生信息：

3.聚合函数

聚合函数定义：将一列数据作为一个整体，进行纵向计算。

常见的聚合函数：

count：统计数量；

max：最大值；

min：最小值；

avg：平均值；

sum：求和；

4.分组查询（group by）

语法：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]；

语法中where与having的区别：

 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

 判断条件不同：where不能对聚合函数进行判断，但having可以。

注意事项：

 执行顺序：where>聚合函数>having

 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段往往无意义。

5.排序查询（order by）

语法：select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；

排序方式：

asc ：升序（默认）

desc：降序

注意事项：如果是多个字段排序，当前一个字段值相同时，才会根据第二个字段进行排序。

按照年龄升序，id降序查询表中的数据：

6.分页查询（limit）

语法：select 字段列表 from 表名 limit 起始索引，查询记录数；

六、DCL——数据控制语句

DCL用来管理数据库用户，控制数据库的访问权限。

1.查询用户

在mysql数据库中，所有用户的权限信息都存储在系统数据库mysql中的user表中；

查询用户的语法：use mysql；

select * from user；

2.创建用户

创建用户语法：

create user ‘用户名’ @‘用户名’ identified by ‘密码’ ；

注意事项：在创建用户名的时候，主机名：localhost，只能在本机访问数据库；主机名：%，则在任何主机上都可以访问数据库。

3.修改用户密码

修改用户密码语法：

alter user ‘用户名’@‘主机名’ identified with mysql_native_password by "新密码"；

4.删除用户

删除用户密码：

drop user "用户名"@“主机名”；

5.权限控制

查询用户权限语法：show grants for “用户名”@“主机名”；

给用户授予权限语法：grant 权限列表 on 数据库名 表名 to "用户名"@“主机名”;（数据库名和表名都可以用*进行通配，表示所有。）

给xincun这个用户授予mysql数据库中所有表的所有权限：

为用户撤销权限语法：

revoke 权限列表 on 数据库名 表名 from “用户名”@“主机名”；

七、多表查询

在学习过程中，因为没有合适的数据库表结构，所以就不做演示了。如果后期遇到多表查询，会补齐演示内容。

1.多表关系

项目开发中，数据库表结构设计，会根据业务需求，分析并设计表结构，由于业务之间互相联系。所以每个表之间存在各种联系，大致分为：一对多、多对多、一对一。

多表关系概述：

一对多：

实现：在多的一方建立外键，指向“一”的一方的主键。 情况举例：在实际开发中，一个部门对应多个员工。

多对多：

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。情况举例：在实际开发中，一个学生可以选择多门课程，一门课程可以对应多个学生。

一对一：

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的。 情况举例：一对一关系多用于单表的拆分，将一张表的基础字段放到一张表中，其他字段放到另外一张表中，从而提升操作效率。

基本概念概述： 笛卡尔积：笛卡尔积是指在数学中，两个集合A和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

多表查询的分类：

连接查询：

内连接：相当于查询A、B交集部分数据。

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据。

右外连接：查询右表所有数据，以及两张表交集部分数据。

自连接：当前表语自身的连接查询，自连接必须使用表别名。

子查询

2.连接查询——内连接

内连接查询的是两张表中交集的部分。

内连接查询方法分两种：隐式内连接和显示内连接；

隐式内连接：select 字段列表 from 表1，表2 where 条件...

显示内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件...

3.连接查询——外连接

外连接查询分为两种：左外连接和右外连接查询；

左外连接语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；

左外连接查询的是左表的数据，同时包含左表和右表的交集部分数据。

自己总结左外连接的使用场景：在查询过程中要将左表的数据全部与另一张表的数据对应，即使左表的数据不完整也得将查询结果展示出来。

右外连接语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；

右外连接查询的是右表的数据，同时包含左表和右表的交集部分数据。

自己总结右外连接的使用场景：在查询过程中要将右表的数据全部与另一张表的数据对应，即使右表的数据不完整也得将查询结果展示出来。***

4.连接查询——自连接

自连接指的是一张表自己连接自己，用于查询自己的数据。

自连接语法：select 字段列表 from 表1 as 别名1 join 表1 as 别名2 on 条件...

自连接查询，可以是内连接查询，也可以是外连接查询。

自己总结自连接查询的使用时机：需要查询的数据都在同一张表里面。（技巧:这个时候，将一张表分别对应的别名，看作两张表操作。）

5.连接查询——联合查询（union，union all）

联合查询：就是将多次查询的结果合并起来，形成一个新的查询结果。

联合查询的语法：

select 字段列表 from 表1 ....

union [all]

select 字段列表 from 表2...

这个语法结构会将两条查询语句的结果，整合组成一个新的结果。

注意事项：

使用联合查询的多张表，多张表的列数必须保持一致，字段类型也需要保持一致。并且返回字段个数必须一致。

如果联合查询使用的关键字是union all 那么只是将两条语句查询的结果，直接拼接在一起，不会进行去重操作。

如果联合查询使用的关键字是union，那么在将两条语句查询结果拼接在一起，会进行去重操作。

5.连接查询——子查询

子查询概念：SQL语句中嵌套select语句，视为嵌套查询，又称为子查询。

语法：select * from 表1 where column1=（select column from 表2）；

子查询中的语句可以是insert、update、delete、select中的任何一个。

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）；

列子查询：子查询结果为一列；

行子查询：子查询结果为一行；

表子查询：查询结果为多行多列。

根据子查询位置：分为：where之后、from之后、select之后。

接下来将会详细介绍不同类型的子查询：

标量子查询：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用的操作符：= <> > >= < <=

列子查询：子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：in，not in、any、some、all

行子查询：子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、<> 、in、not in

表子查询：

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：in