mysql基础

Mysql

一、SQL的基本概念和通用语法

1.1 什么是SQL？

structured query language：结构化查询语言

就是定义操作所有关系型数据库的规则；每一种数据库的操作方式存在不一样的地方，我们就叫做方言；

1.2 SQL的通用语法

可以单行或者多行书写，以分号结尾；
不区分大小写，建议关键字大写；

1.3 SQL的分类

DDL：数据定义语言

用来定义数据库对象：数据库，表，列等。关键字：create，drop，alter等；

DML：数据库操作语言

用来对数据库中表的数据进行增删改。关键字：insert，delete，update等；

DQL：数据查询语言

用来查询数据库中表的记录数据。关键字：select，where等；

DCL：数据控制语言(了解)

用来定义数据库的访问权限和安全级别，以及创建用户。关键字：GRANT，REVOKE等；

二、操作数据库、表(DDL)

2.1 操作数据库

2.1.1 C：Create

创建一个db2的数据库，并且先判断是否存在，且指定字符集为GBK；

create database if not exists db2 character set gbk;

2.1.2 R：Retrieve

查询所有数据库：show databases;
查询某个数据库的创建语句：show create database xxxxx；

2.1.3 U：Update

修改数据库的字符集：alter database 数据库名称 character set 指定的字符集；

2.1.4 D：Delete

删除数据库：drop database 数据库名称；

2.1.5 使用数据库

查询当前正在使用的数据库名称：select database();
使用数据库：use 数据库名称；

2.2 操作表

2.2.1 C：Create

创建表

create table 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    .
    .
    .
    列名n 数据类型n
);

数据类型
- int 整数类型：name int;
- double 小数类型：score double(4,2) 表示一共四位，小数点后面保留两位；
- date 日期：birthday date 只包含年月日，格式为 yyyy-MM-dd；
- datetime 日期：insert_time datetime 包含年月日时分秒，格式为yyyy-MM-dd HH:mm:ss;
- timestamp 时间戳类型：如果不给它赋值或者赋值为 null 的话，则默认使用系统的时间自动赋值；
- varchar 字符串类型：name varchar(20) 最多包含 20 个字符的字符串；

2.2.2 R：Retrieve

查询一个数据库中所有的表：show tables;;
查询表结构：desc 表名;;

2.2.3 U：Update

修改表名

alter table 表名 rename to 新表名；

修改表的字符集

alter table 表名 character set 字符集名称；

添加一列

alter table 表名 add 列名 数据类型；

修改列名，类型

alter table 表名 change 旧列名 新列名 新列的数据类型；

alter table 表名 modify 列名 新的数据类型；

删除列

alter table 表名 drop 列名；

2.2.4 D：Delete

删除表

drop table 表名；
drop table if exists 表名；

三、操作数据库表中的数据(DML)

3.1 添加数据

格式：

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

注意事项：
- 列名和值要一一对应；
- 如果表名后面不指定列名的话，默认给所有的列添加值；
- 除了数字，其他的都要用引号包起来；

3.2 删除数据

格式：

delete from 表名 [where 条件];

注意事项：
- 如果不写条件，就删除表中的所有数据；
- 如果要删除所有的数据有两种方式：
  - 第一种 delete from 表名 ，不推荐，有多少条数据就执行第三次 delete 命令；
  - 第二种 truncate table 表名 ，推荐，原理是把这个表删除，然后再创建一个和原来一模一样的；

3.3 修改数据

格式：

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

注意事项：
- 如果不加条件，就会修改所有的数据；

四、数据库的查询操作(DQL)

select * from 表名;

4.1 查询的语法

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组列表
having
    分组之后的条件
order by
    排序
limit
    分页限定

4.2 基础查询

去除重复的结果集；

select distinct addr from stu;
# 关键字distinct；

计算列与列

select math,english,math+ IFNULL(english,0) from stu;
# 也可以直接通过运算符计算列与列；
# 如果有值为NULL，可以使用IFNULL函数来判断；            
# IFNULL(english,0);意思就是如果英语成绩为空，就算成0分；

起别名

select math,english,math+ IFNULL(english,0) as 总分 from stu;

4.3 条件查询

where子句后面接条件；
运算符：
- 基本的都有，还有一个特殊的 <> 就是相当于 !=；
- between...and...；
- in(集合)；
- like：模糊查询；
  - 占位符：
    - _ : 单个任意字符；
    - % : 多个任意字符；
- is null / is not null；
- and 或者 && ；
- or 或者 ||；
- not 或者 !；

4.4 排序查询

格式

select * from 表名 order by 参考项 asc/desc;

参数
- ASC：表示升序；
- DESC：表示降序；

4.5 聚合查询

定义：将一列数据当成一个整体，进行纵向的计算；
种类：
- count：SELECT COUNT(id) FROM mysheet01;
- max：SELECT MAX(score) FROM mysheet01;
- min：SELECT MIN(score) FROM mysheet01;
- sum：SELECT SUM(score) FROM mysheet01;
- avg：SELECT AVG(score) FROM mysheet01;
注意：聚合函数会排除 null 的值；
- 选择不包含null的进行计算；
- 用IFNULL函数；

4.6 分组查询

格式

select 参与分组的列 from 表名 order by 分组字段；

注意事项：
- 分组查询的字段只能是分组字段或者是聚合函数；
  - select sex,avg(score) from mysheet01 order by sex; 表示对性别分组，并且计算各组的平均；
- where和having的区别；
  - 位置不同，where在分组之前，having在之后；
  - where之后不可以跟聚合函数；having可以；

4.7 分页查询

语法：limit 开始的索引,每一页的条数；
公式：开始的索引 = （页码 - 1）* 每页的记录条数；
是方言；

4.8 约束

概念：对表中的数据进行限定，保证数据的正确性、有效性、完整性。
分类：
- 主键约束：primary key
  - 注意
    - 非空且唯一；
    - 一张表只能有一个字段作为主键；
  - 在创建表时添加主键
  - 在表创建之后添加主键
  - 删除主键
```
alter table student drop primary key;
```

- 自动增长

```mysql
id int primary key auto_increment;
```

非空约束：not null

在创建表的时候添加约束；

create tabel student(
    id int,
    name varchar(20) not null
)

- 创建表之后添加；

```mysql
alter table student name varchar(20) not null;
```

唯一约束：unique

创建表时添加

create tabel student(
    id int,
    phone_number varchar(20) unique
)

- 删除唯一约束

```mysql
alter table student drop index phone_number;
```

- 在创建表之后添加唯一约束

```mysql
alter table student phone_number varchar(20) unique;
```

外键约束：foreign key

让表与表产生关系，从而保证数据的正确性；

在创建表时添加外键约束

create table 表名(
    ...
    外键列，
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
)

//外键名称是可以自定义的；

- 删除外键

```mysql
alter table 表名 drop foreign key 外键名称；
```

- 创建表之后添加外键

```mysql 
alter table 表名 add constraint 外键名称 foreign key (外键列) references 主表(外键列);
```

级联操作
- 级联更新：on update cascade
- 级联删除：on delete cascade

五、数据库的设计

5.1 多表之间的关系

5.1.1 一对一的关系

案例：人和身份证的关系；
实现方式：从任意一方添加唯一外键指向另一方的主键

5.1.2 一对多的关系

案例：员工和部门的关系；
实现方式：从多的一方添加外键指向一的一方的主键；

5.1.3 多对多的关系

案例：学生和课程的关系；
实现方式：借助第三方中间表；

5.2 数据库设计的范式

设计数据库时，要遵循的的一些规范。要遵循后面的范式要求，就必须先满足前面的范式要求；
范式呈递次规范，越高的范式数据库的冗余越小；

现在有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯·科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)；

5.2.1 第一范式

每一列都是不可分割的原子数据项；

5.2.2 第二范式

在第一范式的基础上，非码属性必须完全依赖于候选码(在1NF的基础上消除非主属性对主码的部分函数依赖)；

函数依赖：A ---> B ，如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于属性A；
完全函数依赖：A ---> B ，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性；
部分函数依赖：A ---> B ，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的部分属性；
传递函数依赖：A ---> B ，B ---> C ，如果通过A属性(属性组)的值，可以确定唯一B属性(属性组)的值，通过B属性(属性组)的值，可以确定唯一的C属性的值，则称C传递函数依赖A；
码：如果在一张表中，一个属性或者属性组，被其他所有属性完全依赖，则称这个属性(属性组)为该表的码；
- 主属性：码属性组中的所有属性；
- 非主属性：其他；

5.2.3 第三范式

在第二范式的基础上，任何非主属性不依赖于其他非主属性(在2NF的基础上消除传递依赖)；

六、多表查询

6.1 内连接查询

6.1.1 隐式内连接

select 
    t1.name,t1.gender,t2.name
from 
    emp t1,
    dept t2
where
    t1.dept_id = t2.id;

6.1.2 显式内连接

select 字段列表 from 表1 [inner] join 表2 on 条件；

6.1.3 注意事项：

从哪些表中查；
条件是什么；
查询什么字段；
查询的是两表的交集部分；

6.2 外连接查询

6.2.1 左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件；

查询的是左表所有的数据以及两表的交集部分；

6.2.1 右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件；

查询的是右表的所有数据以及两表的交集部分；

6.3 子查询

6.3.1 概念

查询中嵌套查询，称嵌套的查询为子查询；
格式示例：

select * from emp where salary = (select max(salary) from emp);

6.3.2 子查询的三种情况

子查询的结果是单行单列的；

子查询可以作为条件，使用运算符去判断。> < = 。。。

子查询的结果是多行单列的；

子查询可以作为条件，使用运算符 in 来判断；

子查询的结果是多行多列的；

子查询可以作为一张虚拟表参与查询；

七、事务

7.1 事务的基本介绍

7.1.1 概念：

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败

7.1.2 操作：

① 开启事务：start transaction

② 回滚：rollback

③ 提交：commit

7.1.3 mysql数据库中事务默认提交

事务提交的两种方式：
- 自动提交，mysql 就是自动提交的，一条 DML 语句会自动提交一次事务；
- 手动提交：需要先开启事务，再提交；

修改事务的提交方式：

查看事务的默认提交方式：

select @@autocommit;
# 1 代表自动提交；
# 0 代表手动提交；

- 修改默认提交方式：

```mysql
set @@autocommit = 0 / 1;
```

7.2 事务的四大特征

原子性：是不可分割的最小单位，要么同时成功，要么同时失败；
1. 持久性：当事务提交或者回滚后，数据库会持久化的保存数据；
2. 隔离性：多个事务之间相互独立；
3. 一致性：事务操作前后，事务总量不变；

7.3 事务的隔离级别(了解)

7.3.1 概念

事务之间是相互独立的，隔离的。但是多个事务同时操作同一批数据，则会引发一些问题，设置不同的隔离级别可以解决这个问题；

7.3.2 存在的问题

脏读：一个事务读取到另外一个事务中没有提交的数据；
不可重复读(虚读)：在一个事务中，两次读到的数据不一样；
幻读：一个事务操作(DML)了数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改；

7.3.3 隔离级别

级别：
1. read uncommitted：读未提交；产生的问题：三个都有；
2. read committed：读已提交；产生的问题：不可重复读，幻读；（Oracle默认)
3. repeatable read：可重复读；产生的问题：幻读；（mysql默认)
4. serializable：串行化；解决了所有问题；
注意：

隔离级别从小到大，安全级别越高，但是效率越来越低；

数据库查询隔离级别：

select @@tx_isolation;

数据库设置隔离级别：

set global transaction isolation level 级别字符串;

八、用户管理和权限管理(DCL)

8.1 管理用户

8.1.1 添加用户

create user '用户名'@'主机名' identified by '密码';

8.1.2 删除用户

drop user '用户名'@'主机名';

8.1.3 修改用户密码

第一种

update user set password = password(新密码) where user = '用户名'；

第二种

set password for '用户名'@'主机名' = password(新密码);

忘记了root密码怎么办？
- 通过管理员权限打开 cmd 关闭mysql服务：net stop mysql；
- 使用无验证方式启动mysql：mysqld --skip-grant-tables；
- 打开新的cmd窗口，直接输入mysql敲回车；
- 修改密码；
- 关闭 mysqld 进程;

8.1.4 查询用户

切换到mysql数据库；
查询此库中的user表；

8.2 权限管理

8.2.1 查询权限

show grants for '用户名'@'主机名';

8.2.2 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

8.2.3 撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';