MySQL数据库学习

MySQL 数据库学习

MySQL数据库软件

安装

…

配置

MySQL服务启动

1. 手动。
2. cmd–> services.msc 打开服务的窗口
3. 使用管理员打开cmd

net start mysql //启动mysql的服务
net stop mysql  //关闭mysql服务

MySQL登录

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

MySQL退出

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. 几个概念
   • 数据库：文件夹
   • 表：文件
   • 数据：数据

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 等

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

操作数据库：CRUD

C(Create):创建

创建数据库：

create database 数据库名称;

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

create database if not exists 数据库名称;

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

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

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

create database if not exists db4 character set gbk;

R(Retrieve)：查询

查询所有数据库的名称:

show databases;

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

show create database 数据库名称;

U(Update):修改

修改数据库的字符集：

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

D(Delete):删除

删除数据库：

drop database 数据库名称;

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

drop database if exists 数据库名称;

使用数据库

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

select database();

使用数据库：

use 数据库名称;

操作表

C(Create):创建

语法

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
	);

复制表

create table 表名 like 被复制的表名;

R(Retrieve)：查询

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

show tables;

查询表结构：

desc 表名;

U(Update):修改

修改表名

alter table 表名 rename to 新的表名;

修改表的字符集

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

添加一列

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

修改列名称 类型

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

删除列

alter table 表名 drop 列名;

D(Delete):删除

drop table 表名;
drop table  if exists 表名 ;

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

添加数据

语法

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

注意

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

删除数据

语法

delete from 表名 [where 条件];

注意

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

修改数据

语法

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

注意

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

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

语法

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

基础查询

多个字段的查询

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

• 注意
  • 如果查询所有字段，则可以使用 * 来替代字段列表。

去除重复：distinct

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

计算列

select 字段1, 字段2, 字段1 + 字段2 from 表名;

• 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）null参与的运算，计算结果都为null。

select 字段1, 字段2, 字段1 + ifnull(字段2, 0) from 表名;

• ifnull(表达式1,表达式2)
  • 表达式1：哪个字段需要判断是否为null。
  • 表达式2：如果该字段为null后的替换值。

起别名

select 字段1, 字段2, 字段1 + ifnull(字段2, 0) as 总和 from 表名;
select 字段1, 字段2, 字段1 + ifnull(字段2, 0) 总和 from 表名;

• as（可以省略）

条件查询

语法

where 条件语句

运算符

​

排序查询

语法

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

排序方式

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

注意

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

聚合函数

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

语法

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

函数

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

注意

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

分组查询

语法

group by 分组字段;

注意

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

分页查询

语法

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

公式

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

注意

• limit 是一个 MySQL ”方言“。

约束

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

分类

非空约束

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

创建表时添加非空约束

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

创建表后添加非空约束

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

删除非空约束

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

唯一约束

unique ，某一列的值不能重复

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

创建表时添加唯一约束

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

创建表后添加唯一约束

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

删除唯一约束

alter table 表名 drop index 变量2;

主键约束

1. primary key 非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识

创建表时添加主键约束

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

创建表后添加主键约束

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

删除主键约束

alter table 表名 drop primary key;

自动增长

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

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

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

添加自动增长

alter table 表名 modify 变量1 int auto_incremrnt;

删除自动增长

alter table 表名 modify 变量1 int;

外键约束

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

创建表时添加外键约束

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

创建表后添加外键约束

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

删除外键约束

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

级联操作

分类

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

添加级联操作

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 基础上消除传递依赖）

数据库的备份和还原

语法

备份（命令行）

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

还原（命令行）

登录数据库

创建数据库

使用数据库

执行文件

source 文件路径

多表查询

语法

select
	列名列表
from
	表名列表
where....;

内连接查询

隐式内连接

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

显式内连接

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

注意

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

外连接查询

左外连接

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

• 查询的是左表所有数据以及其交集部分。

右外连接

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

• 查询的是右表所有数据以及其交集部分。

子查询

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

• 子查询的结果是单行单列的
  • 子查询可以作为条件，使用运算符去判断。
• 子查询的结果是多行单列的
  • 子查询可以作为条件，使用运算符in来判断。
• 子查询的结果是多行多列的
  • 子查询可以作为一张虚拟表参与查询。

事务

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

操作

开启事务：

start transaction;

回滚：

rollback;

提交：

commit;

自动提交：

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

手动提交：

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

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

SELECT @@autocommit;

• 1 代表自动提交
• 0 代表手动提交

修改默认提交方式：

set @@autocommit = 0;

四大特征

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

隔离级别

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

问题

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

级别

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

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

数据库查询隔离级别：

select @@tx_isolation;

数据库设置隔离级别：

set global transaction isolation level  级别字符串;

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

DBA: 数据库管理员

用户管理

添加用户

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

删除用户

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

修改用户密码

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';

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

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

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

net stop mysql

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

mysqld --skip-grant-tables

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

输入以下指令修改密码：

use mysql;

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

关闭两个窗口

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

启动 mysql 服务

使用新密码登录。

查询用户

切换到mysql数据库

USE myql;

查询user表

SELECT * FROM USER;

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

权限管理

查询权限

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

授予权限

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

撤销权限

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