MySQL数据库笔记（持续更新）

MySQL数据库笔记（持续更新）

一、什么是数据库

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种

用户或应用共享的数据集合。

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软

件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。

用户通过数据库管理系统访问数据库中的数据。

数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

数据库：存储、维护和管理数据的集合。

二、三大范式

第一范式：无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：属性完全依赖于主键 [ 消除部分子函数依赖]。如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。

第三范式:属性不依赖于其它非主属性 [ 消除传递依赖 ]。设R是一个满足第一范式条件的关系模式，X 是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF. 满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

三、MySQL的卸载与安装

使用版本:MYSQL8.0.22

1.安装

步骤1:访问地址:https://dev.mysql.com/downloads/mysql/

步骤2:下载压缩包

for window

没有账户的点击左下方:No thanks。

下载后解压，放在非C盘下，文件夹改名mysql
将解压文件夹下的bin路径添加到变量值中，前后以 ; 开头结尾

步骤3:

在mysql文件夹下找到my.ini或my-default.ini，如果没有.ini结尾的文件,直接创建该文件。新增内容为如下，注意basedir和datadir是我自己的路径位置，自定义。记得新增一个文件Data文件夹

# 设置mysql数据库的数据的存放目录
datadir=D:\Program Files\mysql\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

步骤4:安装mysql
在mysql的安装目录中，打开bin文件夹，运行cmd.执行初始化数据库的指令：

mysqld --initialize --console

安装成功

root用户的初始化密码:

要是你不小心关掉cmd，或者没记住，那也没事，删掉初始化的 datadir 目录，再执行一遍初始化命令，又会重新生成的。或者再教一个更加简单的方法，下面会讲

再说说安装失败的情况下，可能是缺少一些文件

例如我报的错误

解决方案：

安装微软常用库

链接：https://pan.baidu.com/s/1e4RIrbGWUSdSrIe2ApWptw
提取码：5hvn

安装后运行就能成功了

步骤5:安装服务
在MySQL安装目录的 bin 目录下执行命令：
mysqld --install [服务名] 这里的服务名默认是mysql，可以自定义

如果提示上述错误，需要关闭cmd,重新打开，使用管理员身份执行

安装完成之后
通过命令net start mysql8启动MySQL的服务了。通过命令net stop mysql8停止服务。

注意:安装时，卸载其他版本的mysql数据库

步骤6:链接数据库

第一种方式：记得住初始密码的情况下)

修改账户密码:

alter user 'root'@'localhost' identified with mysql_native_password BY '新密码';

示例：

alter user 'root'@'localhost' identified with mysql_native_password BY '123456';

修改密码，注意命令尾的分号一定要有，这是mysql的语法

退出数据库:

第二种方式：记不得初始密码的情况下（例如我这种）

以下命令行代码均在管理员模式下操作
命令行exe文件目录：C:\Windows\System32\cmd.exe -> 右键 -> 以"管理员模式运行"

第一步：关闭Mysql服务

net stop mysql

第二步：跳过Mysql密码验证

关闭Mysql服务之后，继续在mysql所在的bin目录下进行操作

mysqld --console --skip-grant-tables --shared-memory 

在输入这行代码之后，我们就已经成功跳过Mysql的密码登录了

第三步：无密码方式进入Mysql

在上述步骤之后，再打开一个管理员模式运行的cmd.exe
进入mysql下的bin目录后，直接登录mysql
不需要通过net start mysql打开mysql服务
在命令行中输入以下代码

mysql -u root -p

此时会显示让你输入密码，直接回车，就可以成功连接Mysql

第四步：将登陆密码设置为空

输入代码，将密码设置为空（此时还不能直接修改密码，必须先设置为空，否则会报错）
分别输入

use mysql; (使用mysql数据表)
update user set authentication_string='' where user='root';（将密码置为空）
quit; (然后退出Mysql)

第五步：更改自己的登陆密码

1.关闭第一个cmd窗口(一定要关闭！)

2.在第二个窗口中依次输入代码

net stop mysql(关闭mysql服务,虽然会显示没有开启服务，但是以防万一)
net start mysql(再打开mysql服务)
(这里不要嫌麻烦，如果上一个mysql服务没关闭,我们依旧是无密码登陆)
cd 进入你mysql所在的bin目录
mysql -u root -p
(此处会显示输入密码，直接回车就好了，第四步我们已经将他置为空了)
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';（更改密码）

最后一步：验证密码是否修改成功

2.卸载

步骤1:使用管理员身份运行cmd,关闭mysql服务

步骤2:删除mysql服务

命令:sc delete mysql8 或者 mysqld remove mysql8（mysql8是我自己的服务名）

步骤3:刪除mysqlDB目录文件（ 安裝mysql时my.ini指定的目录

验证密码是否修改成功

四、SQL语言

1.概述

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。各数据库厂商都支持ISO的SQL标准，普通话各数据库厂商在标准的基础上做了自己的扩展（方言）。

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并删除条目等操作。Create, Read, Update, and Delete 通常称为CRUD操作。

2.SQL语句分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。

注意：sql语句以;结尾 mysql中的关键字不区分大小写

3.DDL操作数据库

3.1 创建

CREATE DATABASE语句用于创建新的数据库：

编码方式:gb2312,utf-8,gbk,iso-8859-1等

 //create database 数据库名
 CREATE DATABASE mydb1; 
 //create database 数据库名 character set 编码方式
 CREATE DATABASE mydb2 character SET GBK;  
  //create database 数据库名 set 编码方式 collate 排序规则
 CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

3.2 查看数据库

查看当前数据库服务器中的所有数据库

show databases;

查看前面创建的mydb2数据库的定义信息

//show create database 数据库名; 
Show CREATE DATABASE mydb2;

3.3 修改数据库

alter  database  数据库名  character set 编码方式

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

ALTER DATABASE mydb2 character SET utf8;

3.4 删除数据库

//drop database 数据库名
DROP DATABASE mydb3;

5 其他语句

查看当前使用的数据库

Select database();

切换数据库: use 数据库名

USE mydb2;DDL操作表

4. DDL操作表

CREATE TABLE语句用于创建新表。语法：

CREATE TABLE 表名( 
列名1 数据类型 [约束],  
列名2 数据类型 [约束], 
列名n 数据类型 [约束]
）;

说明:表名，列名是自定义，多列之间使用逗号间隔，最后一列的逗号不能写
[约束] 表示可有可无
示例：

CREATE TABLE Employees(
 	id INT ,
 	age INT ,
 	first VARCHAR(255),  last VARCHAR(255)
 	);

常用数据类型：

• int：整型
• double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；默认支持四舍五入
• char：固定长度字符串类型； char(10) 'aaa ’ 占10位
• varchar：可变长度字符串类型； varchar(10) ‘aaa’ 占3位
• text：字符串类型，比如小说信息；
• blob：字节类型，保存文件信息(视频，音频，图片)；
• date：日期类型，格式为：yyyy-MM-dd；
• time：时间类型，格式为：hh:mm:ss
• timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
• datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

4.1 其他表操作

drop table 表名;

DROP TABLE table_name;

当前数据库中的所有表

SHOW TABLES;

查看表的字段信息

desc 表名；

DESC employee;

增加列:在上面员工表的基本上增加一个image列。

alter table 表名 add 新列名 新的数据类型

ALTER TABLE employee ADD image blob;

修改job列，使其长度为60。

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

ALTER TABLE employee MODIFY job varchar(60); ALTER TABLE employee change job job varchar(60);

列名name修改为username

ALTER TABLE user CHANGE name username varchar(100);

删除image列,一次只能删一列。

alter table 表名 drop 列名

ALTER TABLE employee DROP image;

修改表名,表名改为user。

alter table 旧表名 rename 新表名;

alter table user rename users;

查看表格的创建细节

show create table 表名;

SHOW CREATE TABLE user;

修改表的字符集为gbk

alter table 表名 character set 编码方式

ALTER TABLE user CHARACTER SET gbk;

五、DML对表中数据进行操作

添加表中数据，按指定列

insert into 表名（列名）values（数据值）；

添加表中数据，省略列名，注意要全部数据都要添加，而且要按表中顺序

insert into 表名values（数据值1，数据值2.....）；

添加多行数据

inster into 表名（列名） values （数据1），（数据2）.....;

查看表中数据

select*from 表名；

更新（修改)表中数据

update 表名 set 值名=值.....（where 条件）;

删除数据

方式一：

delete from 表名 （where 条件）；

方式二：

truncate table 表名；

两种方式的区别：

• delete 删除表的数据，表的结构还在，删除的数据可以找回。
• truncate 删除是直接将表drop，再创建跟表一样的新表，删除的数据不能找回，执行速度比delete快。

六、DCL权限语句

1.创建用户

指定IP

create user 用户名@客户端IP identified by '密码'；

任意IP

create user 用户名 @‘%’ indentified by '密码'；

2.用户授权

给予用户部分权限

grant 权限1，权限2....on 数据库名.*to 用户名@IP

给予用户全部权限

grant all on * .* to 用户名@IP

3.用户权限查询

show grants for 用户名@IP

4.撤销用户权限

revoke 权限1，权限2...... on 数据库名.* from 用户名@IP

5.删除用户

drop user 用户名@IP

七、DQL查询语句

1.简单查询

查询所有列

select*from 表名；

查询部分列

select 列1,列2..... from 表名；

2.条件查询（where）

列名 in（列值1，列值2，.........）;

3.模糊查询

where 列名 like '表达式'

通配符：_(下划线)：任意一个字符

​ %：任意0-n个字符，如’张%’

4.字段控制查询

4.1 去除重复记录 （distinct）

select distinct 列名 from 表名；

4.2相加

如果两列或两列以上都是数值类型，可以做运算

select *，列1+列2+.... from 表名；

但有时候一些记录是为null的，任何数字与null相加都是null，此时可以运用把null转换为0的函数IFNULL（列名，0）

select*，列1+IFNULL(列名2,0) from 表名；

如果列名过长，可以选择起别名 as可以省略，如：

select*，列1+IFNULL(列名2,0) as 别名 from 表名；

5.排序（order by）

asc表示升序 desc表示降序 默认为升序

order by 列名 asc/desc；//单列
order by 列名1 asc/desc.列名2 asc/desc，列名n asc/desc；//多列

6.聚合函数

聚合函数是用来做纵向运算的函数：

• COUNT(列名)：统计指定列不为NULL的记录行数；
• MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
• MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
• SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
• AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

语法：

select count（列名）（as 别名） from 表名；

7.分组查询（group by）

注意：如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名

select （聚合函数）（分组的别名） from 表名 group by 列名；

8.having子句

注：having与where的区别:

​ 1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤

​ 2.having后面可以使用分组函数(统计函数)where后面不可以使用分组函数。

9.分页查询（limit）

select*from 表名 limit 起始，显示条数；
或
select*from 表名 limit 显示条数；//默认从0开始

查询语句书写顺序：select – from- where- groupby- having- order by-limit

查询语句执行顺序：from - where -group by -having - select - order by-limit