MySQL基础知识总结

前言

我们每天使用互联网产品会产生大量的数据，例如使用微信产生的很多聊天记录，在微信里发的朋友圈图片，使用百度搜索的各种内容，使用淘宝进行购物的记录等，这些数据都存储在数据库中，所以，学好数据库可以让程序员在编程世界中如虎添翼。

为什么要学习数据库

在需要保存用户的数据的情况下，随着用户数量的增加，数据量越来越大，使得一种专门管理数据的软件应运而生，编写应用程序的时候，数据读写的功能就大大地被简化了。对于程序员来说，懂得一种数据库管理系统无疑是必须的。

MySQL

MySQL是关系型数据库管理系统之一，属于Oracle旗下管理系统，作为一种开源的轻量级数据库，在开源数据库中比较流行，由于其小巧安装方便快捷的特性，经常用于互联网公司，同时维护也比较方便，追求稳定的性能和易用性。

SQL、DB、DBMS

• SQL：Structured Query Language（结构化查询语言）一门标准通用的高级语言，标准的SQL语言适合于所有的数据库产品
• DB：DataBase（数据库）在硬盘上以文件的形式存在
• DBMS：DataBase Management System（数据库管理系统）口头上说的数据库其实表示的是数据库管理系统，常见的有：MySQL、Oracle、DB2、SqlServer…

DBMS负责执行SQL语句，通过执行SQL语句来操作DB当中的数据

DQL、DML、DDL、TCL、DCL

• DQL：Data Query Language（数据查询语言），查询语句，凡是select语句都是DQL
• DML：Data Manipulation Language（数据操作语言），insert、delete、update，对表当中的数据进行增删改
• DDL：Data Definition Language（数据定义语言），create、drop、alter，对表结构的增删改
• TCL：Transactional Control Language（事务控制语言），commit提交事务、rollback回滚事务
• DCL：Data Control Language（数据控制语言），grant授权、revoke撤销权限…

常用的命令

1.创建数据库

create database 数据库名;
1

2.使用数据库

use 数据库名;
1

3.查看有哪些数据库

show databases;
1

4.查看当前数据库有哪些表

show tables;
1

5.查看其他数据库中的表

show tables from 数据库名;
1

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

select database();
1

7.查看数据库版本

select version();
1

8.终止一条语句

想要终止一条正在编写的语句，输入\c

9.退出MySQL

\q、QUIT或EXIT

10.查看表的结构

desc 表名;
1

11.查看创建表的SQL语句

show create table 表名;
1

增删改查

• 增

insert into 表名(字段名1,字段名2,字段名3 ...) values(值1,值2,值3 ...);
1

• 删

delete from 表名 where 条件;
1

• 改

update 表名 set 字段名1 = 值1,字段名2 = 值2 ... where 条件;
1

• 查

select 字段名1,字段名2 ... from 表名 where 条件;
1

条件查询

任何一条sql语句都是以";"结尾
sql语句不区分大小写

select 字段名1,字段名2 ... from 表名 where 条件;
1

执行顺序：from>where>select

排序

MySQL中默认的排序是升序，可以使用 order by 进行控制升序或降序

select 字段名1,字段名2 ... from 表名 [where 条件] order by 字段名;//升序
select 字段名1,字段名2 ... from 表名 [where 条件] order by 字段名 asc;//升序
select 字段名1,字段名2 ... from 表名 [where 条件] order by 字段名 desc;//降序
1
2
3

执行顺序：form>where>select>order by

分组函数

group by：按照某个字段或者某些字段进行分组
having：对分组之后的数据进行在次过滤
having 是group by 的搭档，没有group by ，having是不能出现的。
MySQL中有5个分组函数count()计数、sum()求和、avg()平均值、max()最大值、min()最小值
分组函数一般都是和group by 联合使用。

select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select count(*) from emp;
1
2
3
4
5

SQL当中有一个语法规则，分组函数不可直接使用在where子句当中
因为group by是在where执行之后才会执行的（分组函数必须在分完组之后才能使用，where的时候，group by还没有执行，意味着还没有分组，不能使用分组函数）

count(*)和count(具体的某个字段)区别
count(*)：统计总记录条数
count(具体的某个字段)：统计具体的某个字段中不为NULL的数据的总数量

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

执行顺序：from>where>group by>having>select>order by

结果集去重

distinct 关键字只能出现在所有字段的最前面，表示所有字段联合去重，并不会修改表中的数据

select distinct 字段名1,字段名2 ...from 表名 [where 条件]
1

连接查询

大多数情况下，都不是从单表中查询数据，而是多张表联合查询取出最终的结果
根据表的连接方式来划分：
内连接

等值连接
非等值连接
自连接

外连接

左外连接
右外连接

全连接(不常用)

笛卡尔积现象
当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。
可以加条件进行过滤，避免笛卡尔积现象，但是不会减少匹配次数，显示的是有效记录

1.内连接之等值连接

特点：连接条件中的关系是等值关系

select ... from A表 [inner]join B表 on 连接条件
1

2.内连接之非等值连接

特点：连接条件中的关系是非等值关系

select ... from A表 [inner]join B表 on 连接条件
1

3.内连接之自连接

特点：一张表看做两张表，自己连接自己

select ... from 表名 as A [inner]join 表名 as B on 连接条件
1

4.外连接和内连接区别

• 内连接：假设A表和B表进行连接，使用内连接，凡是A表和B表能够匹配上的记录都能查询出来，AB两表没有主副之分，两张表是平等的
• 外连接：假设A表和B表进行连接，使用外连接，AB两表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

5.外连接之左连接

特点：左边的这张表为主表

select ... from A表 left [outer] join B表 on 连接条件
1

6.外连接之右连接

特点：右边的这张表为主表

select ... from A表 right [outer] join B表 on 连接条件
1

union

union可以将不相干的两张表连接在一起，但是要显示的列数要一样

select ... from ... where ...
union
select ... from ... where ...
1
2
3

limit

• limit是MySQL中特有的，其他的数据库中没有
• limit用于取结果集中的部分数据
• limit startIndex, length，startIndex表示起始位置，从0开始，length表示要取的数据的长度
• select ... from ... [order by ...] limit 0 ,5;和select ... from ... [order by ...] limit 5;等价
• select ... from ... where ...group by...having ...order by ... limit...执行顺序：from>where>group by>having>select>order by>limit

创建表

create table 表名(
	字段名1 数据类型,
	字段名2 数据类型,
	字段名3 数据类型,
	...
)
1
2
3
4
5
6

MySQL中的字段的数据类型

约束

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

• 非空约束（not null）：约束的字段不能为NULL
• 唯一约束（unique）：约束的字段不能重复
• 主键约束（primary key）：约束的字段既不能为NULL，也不能重复
• 外键约束（foreign key）：使得表与表之间的数据建立关联

drop table if exists t_user;//当表t_user存在的时候删除t_user表
create table t_user(
	id int primary key,
	username varchar(255)
)
insert into t_user(id,username) value (1,'zhangsan');
insert into t_user(id,username) value (2,'wangwu');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | wangwu   |
+----+----------+
//创建一个t_user表格,其中id叫做主键字段,id字段中的每一个值都是主键值.主键值表示的这行记录在这张表中的唯一标识.

//一张表的主键约束只能有1个
drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255)
	primary key(id)
)
insert into t_user(id,username) value (1,'zhangsan');
insert into t_user(id,username) value (2,'wangwu');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | wangwu   |
+----+----------+
//这种写法等同于上面的写法

//MySQL中提供主键值自增
drop table if exists t_user;
create table t_user(
	id int primary key auto_increment,//从1开始自增
	username varchar(255)
	
)
insert into t_user(username) value ('zhangsan');
insert into t_user(username) value ('wangwu');
insert into t_user(username) value ('lisi');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | wangwu   |
|  3 | lisi     |
+----+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

事务

一个事务是一个完整的业务逻辑单元，不可再分。
事务的存在是为了保证数据的完整性、安全性。MySQL中的事务默认的是自动提交的
比如转账功能，从A账户向B账户转账1000元，需要同时让A账户-1000元，B账户+1000元

事务的特性

事务包括四大特性：ACID
A：原子性（atomicity）事务是最小的工作单元，不可再分
C：一致性（consistency）事务必须保证多条DML语句同时成功或失败
I：隔离性（isolation）事务A和事务B之间具有隔离性
D：持久性（durability）数据最终必须持久化到硬盘文件中，事务才算成功的结束

事务的隔离性

• 第一级别：读未提交（read uncommitted）存在脏读现象
• 第二级别：读已提交（read uncommitted ）解决了脏读现象，但是不可重复读取某个数据（数据一直在变化）
• 第三级别：可重复读（repeatable read）解决了不可重复读现象，但是读到的数据是幻象（MySQL数据库默认的隔离级别）
• 第四级别：序列化读（serializable）解决了所有问题，但是效率低，需要事务排队

数据库设计三范式

数据库设计三范式是设计表的依据，根据这个三范式设计的表不会出现数据冗余

• 第一范式：确保每列保持原子性不可再分
• 第二范式：确保表中的每列都和主键相关（非主键属性完全依赖于主键属性）
• 第三范式：确保每列都和主键直接相关，不存在简介相关（非主键属性和非主键属性之间不能存在依赖关系，非主键属性必须直接依赖于主键属性，不能间接依赖于主键属性）