MySQL 数据库学习

MySQL数据库软件

安装

…

配置

MySQL服务启动

  1. 手动。
  2. cmd–> services.msc 打开服务的窗口
  3. 使用管理员打开cmd
1
2
net start mysql //启动mysql的服务
net stop mysql  //关闭mysql服务

MySQL登录

1
2
3
mysql -uroot -p密码
mysql -hip -uroot -p连接目标的密码
mysql --host=ip --user=root --password=连接目标的密码

MySQL退出

1
2
exit
quit

MySQL目录结构

  1. MySQL安装目录：basedir=“D:/MySQL/MySQL Server 5.7/”

    • 配置文件 my.ini

  2. MySQL数据目录：datadir=“D:/MySQL/MySQL Server 5.7/Data”

  3. 几个概念

    • 数据库：文件夹

    • 表：文件

    • 数据：数据

MySQL数据库、表、数据的关系

SQL

什么是SQL？

  • Structured Query Language：结构化查询语言

  • 其实就是定义了操作所有关系型数据库的规则。

  • 每一种数据库操作的方式存在不一样的地方，称为“方言”。

SQL通用语法

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

  • 可使用空格和缩进来增强语句的可读性。

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

  • 3 种注释

    • 单行注释: – 注释内容(两个横杆后需要加空格！) 或 # 注释内容(mysql 特有)
    • 多行注释: /* 注释 */

SQL分类

  • DDL(Data Definition Language)数据定义语言
    用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

  • DML(Data Manipulation Language)数据操作语言
    用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

  • DQL(Data Query Language)数据查询语言
    用来查询数据库中表的记录(数据)。关键字：select, where 等

  • DCL(Data Control Language)数据控制语言(了解)
    用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

SQL分类

DDL: 操作数据库、表(数据定义语言 )

操作数据库：CRUD

C(Create):创建

  • 创建数据库：

    1
    create database 数据库名称;

  • 创建数据库，判断不存在，再创建：

    1
    create database if not exists 数据库名称;

  • 创建数据库，并指定字符集：

    1
    create database 数据库名称 character set 字符集名;

  • 练习： 创建db4数据库，判断是否存在，并制定字符集为gbk：

    1
    create database if not exists db4 character set gbk;

R(Retrieve)：查询

  • 查询所有数据库的名称:

    1
    show databases;

  • 查询某个数据库的字符集或查询某个数据库的创建语句：

    1
    show create database 数据库名称;

U(Update):修改

  • 修改数据库的字符集：

    1
    alter database 数据库名称 character set 字符集名称;

D(Delete):删除

  • 删除数据库：

    1
    drop database 数据库名称;

  • 判断数据库存在，存在再删除：

    1
    drop database if exists 数据库名称;

使用数据库

  • 查询当前正在使用的数据库名称：

    1
    select database();

  • 使用数据库：

    1
    use 数据库名称;

操作表

C(Create):创建

语法
1
2
3
4
5
6
create table 表名(
		列名1 数据类型1,
		列名2 数据类型2,
		....
		列名n 数据类型n
	);
注意
  • 最后一列，不需要加逗号（,）。
数据库类型

  • int：整数类型

    • age int,

  • double:小数类型

    • score double(5,2),

  • date:日期，只包含年月日，yyyy-MM-dd

  • datetime:日期，包含年月日时分秒，yyyy-MM-dd HH:mm:ss

  • timestamp:时间错类型，包含年月日时分秒，yyyy-MM-dd HH:mm:ss

    • 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值

  • varchar：字符串

    • name varchar(20):姓名最大20个字符
    • zhangsan 8个字符 张三 2个字符
创建表
	create table student(
		id int,
		name varchar(32),
		age int,
		score double(4,1),
		birthday date,
		insert_time timestamp
	);
复制表
1
create table 表名 like 被复制的表名;

R(Retrieve)：查询

  • 查询某个数据库中所有的表名称：

    1
    show tables;

  • 查询表结构：

    1
    desc 表名;

U(Update):修改

  • 修改表名

    1
    alter table 表名 rename to 新的表名;

  • 修改表的字符集

    1
    alter table 表名 character set 字符集名称;

  • 添加一列

    1
    alter table 表名 add 列名 数据类型;

  • 修改列名称 类型

    1
    2
    alter table 表名 change 列名 新列别 新数据类型;
    alter table 表名 modify 列名 新数据类型;

  • 删除列

    1
    alter table 表名 drop 列名;

D(Delete):删除

1
2
drop table 表名;
drop table  if exists 表名 ;

DML: 增删改表中数据(数据操作语言)

添加数据

语法

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

注意

  • 列名和值要一一对应。
  • 如果表名后，不定义列名，则默认给所有列添加值。
    • insert into 表名 values(值1,值2,…值n);
  • 除了数字类型，其他类型需要使用引号(单双都可以)引起来。

删除数据

语法

1
delete from 表名 [where 条件];

注意

  • 如果不加条件，则删除表中所有记录。
  • 如果要删除所有记录
    • delete from 表名;
      • – 不推荐使用。（有多少条记录就会执行多少次删除操作。）
    • TRUNCATE TABLE 表名;
      • – 效率更高，推荐使用。（先删除表，然后再创建一张一样的表。）

修改数据

语法

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

注意

  • 如果不加任何条件，则会将表中所有记录全部修改。

DQL: 查询表中的记录(数据查询语言)

语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页限定

基础查询

多个字段的查询

1
select 字段1, 字段2, ... from 表名;
  • 注意
    • 如果查询所有字段，则可以使用 * 来替代字段列表。

去除重复：distinct

1
select distinct 字段1, 字段2, ... from 表名;

计算列

1
select 字段1, 字段2, 字段1 + 字段2 from 表名;
  • 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）null参与的运算，计算结果都为null。
1
select 字段1, 字段2, 字段1 + ifnull(字段2, 0) from 表名;
  • ifnull(表达式1,表达式2)
    • 表达式1：哪个字段需要判断是否为null。
    • 表达式2：如果该字段为null后的替换值。

起别名

1
2
select 字段1, 字段2, 字段1 + ifnull(字段2, 0) as 总和 from 表名;
select 字段1, 字段2, 字段1 + ifnull(字段2, 0) 总和 from 表名;
  • as（可以省略）

条件查询

语法

1
where 条件语句

运算符

比较运算符说明
> 、<、<=、>=、=、<><> 在 SQL 中表示不等于，在 mysql 中也可以使用 !=
没有 ==
between…and…在一个范围之内，如：between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾
in（集合）集合表示多个值，使用逗号分隔
like ‘毛%’模糊查询
占位符 _ 表示单个任意字符
占位符 % 表示多个任意字符
is [not] null查询某一列[不]为NULL 的值，注：NULL值不能用 = 判断
逻辑运算符说明
and 或 &&与，SQL 中建议使用前者，后者并不通用。
or 或 ||或
not 或 !非

​

排序查询

语法

1
order by 排序字段1 排序方式1, 排序字段2 排序方式2...

排序方式

  • ASC：升序，默认。
  • DESC：降序。

注意

  • 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

聚合函数

将一列数据作为一个整体，进行纵向的计算。

语法

1
select 函数名(列名) from 表名;

函数

  • count：计算个数
    • 一般选择非空的列：主键
    • count(*)
  • max：计算最大值
  • min：计算最小值
  • sum：计算和
  • avg：计算平均值

注意

  • 聚合函数的计算，排除null值。
  • 解决方案：
    1. 选择不包含非空的列进行计算
    2. IFNULL函数

分组查询

语法

1
group by 分组字段;

注意

  • 分组之后查询的字段：分组字段、聚合函数
  • where 和 having 的区别？
    • where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来。
    • where 后不可以跟聚合函数，having可以进行聚合函数的判断。

分页查询

语法

1
limit 开始的索引,每页查询的条数;

公式

  • 开始的索引 = （当前的页码 - 1） * 每页显示的条数

注意

  • limit 是一个 MySQL ”方言“。

约束

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类

约束名约束关键字
主键primary key
唯一unique
非空not null
外键foreign key
检查check 注：MySQL 不支持

非空约束

not null ，某一列的值不能为null

  • 创建表时添加非空约束

    1
    2
    3
    4
    create table 表名(
    	变量1 int,
    	变量2 varchar(32) not null -- 给变量2添加非空约束
    );

  • 创建表后添加非空约束

    1
    alter table 表名 modify 变量2 varchar(32) not null;

  • 删除非空约束

    1
    alter table 表名 modify 变量2 varchar(32);

唯一约束

unique ，某一列的值不能重复

  • 唯一约束可以有 NULL 值，但是只能有一条记录为 NULL.

  • 创建表时添加唯一约束

    1
    2
    3
    4
    create table 表名(
    	变量1 int,
    	变量2 varchar(32) unique   -- 给变量2添加唯一约束
    )

  • 创建表后添加唯一约束

    1
    alter table 表名 modify 变量2 varchar(32) unique;

  • 删除唯一约束

    1
    alter table 表名 drop index 变量2;

主键约束

  1. primary key 非空且唯一

  2. 一张表只能有一个字段为主键

  3. 主键就是表中记录的唯一标识

  • 创建表时添加主键约束

    1
    2
    3
    4
    create table 表名(
    	变量1 int primary key,  -- 给变量1添加主键约束
    	变量2 varchar(32)
    );

  • 创建表后添加主键约束

    1
    alter table 表名 modify 变量1 int primary key;

  • 删除主键约束

    1
    alter table 表名 drop primary key;

  • 自动增长

    如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长，一般配合主键使用。

    • 创建表时添加主键约束，并且完成主键自动增长

      1
      2
      3
      4
      create table 表名(
      	变量1 int primary key auto_increment,
      	变量2 varchar(32)
      );

    • 添加自动增长

      1
      alter table 表名 modify 变量1 int auto_incremrnt;

    • 删除自动增长

      1
      alter table 表名 modify 变量1 int;

外键约束

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

  • 创建表时添加外键约束

    1
    2
    3
    4
    5
    create table 表名(
    	...
    	外键列,
    	constraint 外键名称 foreign key (外键列名称) references 主键名称(主表列名称)
    );

  • 创建表后添加外键约束

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

  • 删除外键约束

    1
    alter table 表名 drop foreign key 外键名称;

  • 级联操作

    • 分类

      • 级联更新：on update cascade
      • 级联删除：on delete cascade

    • 添加级联操作

      1
      alter table 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) on update cascade on delete cascade;

数据库的设计

多表关系

一对一

  • 举例：人和身份证

  • 分析：一个人只有一个身份证，一个身份证只能对应一个人。

  • 实现：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。（那其实一张表能解决。）

一对多（多对一）

  • 举例：部门和员工

  • 分析：一个部门有多个员工，一个员工只能对应一个部门。

  • 实现：在多的一方建立外键，指向一的一方的主键。

多对多

  • 举例：学生和课程

  • 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择。

  • 实现：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

数据库范式

设计数据库时，需要遵循的一些规范。要遵循后面的范式要求，必须先遵循前面的所有范式要求

第一范式（1NF）

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

第二范式（2NF）

  • 在 1NF 的基础上，非码属性必须完全依赖于码（在 1NF 基础上消除非主属性对主码的部分函数依赖）

  • 几个概念

    1. 函数依赖：A–>B ，如果通过A属性（属性组）的值，可以确定唯一B属性的值，则称B依赖于A。

      • 例如：学号–>姓名。 （学号，课程名称） --> 分数

    2. 完全函数依赖：A–>B ， 如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。

      • 例如：（学号，课程名称） --> 分数

    3. 部分函数依赖：A–>B ， 如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可。

      • 例如：（学号，课程名称） – > 姓名

    4. 传递函数依赖：A–>B ，B -->C ，如果通过A属性（属性组）的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A。

      • 例如：学号–>系名，系名–>系主任

    5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所依赖，则称这个属性（属性组）为该表的码。

      • 例如：该表中码为：（学号，课程名称）
      • 主属性：码属性组中的所有属性
      • 非主属性：除了码属性组的属性

第三范式（3NF）

  • 在 2NF 基础上，任何非主属性不依赖于其它非主属性（在 2NF 基础上消除传递依赖）

数据库的备份和还原

语法

  • 备份（命令行）

    1
    mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

  • 还原（命令行）

    • 登录数据库

    • 创建数据库

    • 使用数据库

    • 执行文件

      1
      source 文件路径

多表查询

语法

1
2
3
4
5
select
	列名列表
from
	表名列表
where....;

内连接查询

  • 隐式内连接

    • 使用where条件消除无用数据

  • 显式内连接

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

  • 注意

    • 从哪些表中查询数据
    • 条件是什么
    • 查询哪些字段

外连接查询

  • 左外连接

    1
    select 字段列表 from 表1 left [outer] join 表2 on 条件;
    • 查询的是左表所有数据以及其交集部分。

  • 右外连接

    1
    select 字段列表 from 表1 right [outer] join 表2 on 条件;
    • 查询的是右表所有数据以及其交集部分。

子查询

查询中嵌套查询，称嵌套查询为子查询。

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

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

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

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

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

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

事务

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

操作

  1. 开启事务：

    1
    start transaction;

  2. 回滚：

    1
    rollback;

  3. 提交：

    1
    commit;

    • 自动提交：

      • MySQL数据库中事务默认自动提交
      • 一条DML（增删改）语句会自动提交一次事务。

    • 手动提交：

      • Oracle 数据库默认手动提交事务
      • 需要先开启事务，再提交

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

      1
      SELECT @@autocommit;
      • 1 代表自动提交
      • 0 代表手动提交

    • 修改默认提交方式：

      1
      set @@autocommit = 0;

四大特征

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

隔离级别

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

问题

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

级别

  1. read uncommitted：读未提交
    • 产生的问题：脏读、不可重复读、幻读
  2. read committed：读已提交 （Oracle默认）
    • 产生的问题：不可重复读、幻读
  3. repeatable read：可重复读 （MySQL默认）
    • 产生的问题：幻读
  4. serializable：串行化
    • 可以解决所有的问题

  • 注意：隔离级别从小到大安全性越来越高，但是效率越来越低

  • 数据库查询隔离级别：

    1
    select @@tx_isolation;

  • 数据库设置隔离级别：

    1
    set global transaction isolation level  级别字符串;

DCL: 管理用户，授权(数据控制语言)

DBA: 数据库管理员

用户管理

  • 添加用户

    1
    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

  • 删除用户

    1
    DROP USER '用户名'@'主机名';

  • 修改用户密码

    1
    UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
    1
    SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

    • mysql 中忘记了 root 用户的密码？

      1. 管理员运行 cmd 停止 mysql 服务：

        1
        net stop mysql

      2. 使用无验证方式启动 mysql 服务：

        1
        mysqld --skip-grant-tables

      3. 管理员运行新的 cmd ，直接输入 mysql 命令就可以登录成功。

      4. 输入以下指令修改密码：

        1
        use mysql;
        1
        update user set password = password('你的新密码') where user = 'root';

      5. 关闭两个窗口

      6. 打开任务管理器，手动结束 mysqld.exe 的进程

      7. 启动 mysql 服务

      8. 使用新密码登录。

  • 查询用户

    • 切换到mysql数据库

      1
      USE myql;

    • 查询user表

      1
      SELECT * FROM USER;

    • 通配符：% 表示可以在任意主机使用用户登录数据库

权限管理

  • 查询权限

    1
    SHOW GRANTS FOR '用户名'@'主机名';

  • 授予权限

    1
    grant 权限列表(all) on 数据库名(*).表名(* ) to '用户名'@'主机名';

  • 撤销权限

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