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. 原子性：是不可分割的最小单位，要么同时成功，要么同时失败；

   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 '用户名'@'主机名';