本文章针对的是win10/win11+MySQL8.0.25

MySQL基础命令

服务的开启

可以通过计算机管理中服务查看运行状态

net stop MySQL80
//关闭MySQL服务器后面可以跟相应的服务
net start MySQL80
//启动MySQL服务器后面可以跟相应的服务

Mysql 登录和退出

1	mysql-uroot -p

1	mysql -hip -uroot -p连接目标密码

1	mysql --host=ip --user=root --password=连接目标的密码

退出
1. exit
2. quit

Mysql 8.0密码修改

mysql -uroot -p
use mysql;
update user set authentication_string='' where user='root';
alter user 'root'@'localhost' identified by 'password';-- password(密码)
flush privileges;

Mysql 8.0目录结构

安装目录

因为8.0以上版本的特殊原因，mysql的my.ini文件位置在C:\ProgramData\MySQL\MySQL Server 8.0的位置上

SQL

SQL定义

Structured Query Language: 结构化查询语言

其实就是定义了操作所有关系数据库的规则，每一种数据库操作的方式存在不一样的地方。

SQL通用语法

SQL语句可以单行或多行书写，以分句结尾。
可使用空格和缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
3种注释
- 单行注释：– 注释或 # 注释（mysql 特有）（–必须加空格，#可以不加空格）
- 多行注释：/* 注释 */

SQL分类

数据查询语言（所有带有select的语句都是查询语句）

DQL：

基本内容

语法

select

字段列表

from

表名列表

where

条件列表

group by

分组之后的条件

order by

排序

limit

分页限定

查询方式

基础查询

多个字段的查询

-- 查询 姓名 和 年龄
SELECT 
	name, -- 姓名
	age -- 年龄
FROM 
	student; -- 表格

去除重复

1 2	-- 去除重复的结果集 SELECT DISTINCT address FROM student;

计算列
- 一般可以使用四则运算计算一些列的值
- ifnull(a,b) 表示a字段的值如果是null将自动转化成b来计算
1
SELECT name 名字, math+IFNULL(english,0) FROM student; -- 将其中有null的值换成0

起别名

1	SELECT name 名字, math+IFNULL(english,0) AS "总分" FROM student; -- 其中as可写可不写，总分的引号可写可不写

条件查询

where子句后跟条件

运算符

> 、 < 、 <= 、 >= 、 = 、<>
between ... and ...
in
like; --  _:单个任意字符  %:多个任意字符
is null
and /  &&
or  /  ||
not /  !

示例

-- 查询年龄大于等于20 小于等于30
select * from student where age >= 20 and age <=30;
select * from student where age between 20 and 30;

-- 查询年龄为20、 25 、18岁的信息
select * from student where age = 20 or age = 18 or age = 25;
select * from student where age in(20,18,25);

-- 查询英语成绩为null
select * from student where english = NULL;-- 不正确，因为null值不能用=来判断
select * from student where english is null;

like 查询方式

1
2
3

select * from student where name like '马%';
select * from student where name like '_云';

排序查询

语法(order by)

select * from stu order by math asc;
/*
对stu数据库中的数据根据math字段 从低到高升序排序
*/

select * from stu order by math asc, english asc;
/*
先按照数学，后按照英语排序
*/

排序方式
- ASC：升序，默认使用
- DESC：降序
- 如果有多个排序条件，当前面条件一致时，才继续用后面的排序

聚合排序

count：计算个数

一般选择非空的列：主键

select count(name) from student;
/*
从student的数据表中，计算有name字段的数据条数(排除NULL的值)
如果要查找所有的数据，也可以打下面的命令
select count(*) from student;
*/

max：计算最大值
1
select MAX(math) from student;
min：计算最小值
1
select MIN(math) from student;

sum：求和

select SUM(math) from student;
/*
这里在计算的时候会排除null的值
*/

avg：计算平均值

select AVG(math) from student;
/*
这里在计算的时候会排除null的值
*/

分组查询

语法

-- 按照性别分组。分别查询男、女同学的平均分
select sex, AVG(math) from student group by sex;

-- 按照性别分组。分别查询男、女同学的平均分，人数
select sex, AVG(math), COUNT(id) from student group by sex;

-- 按照性别分组。分别查询男、女同学的平均分，人数。要求：分数低于70分的人不参与分组
select sex,AVG(math), COUNT(id) from student where math > 70 group by sex;

-- 按照性别分组。分别查询男、女同学的平均分，人数。要求：分数低于70分的人不参与分组，分组之后人数必须大于2个人
select sex,AVG(math), COUNT(id) from student where math > 70 group by sex having COUNT(id) >= 2;

注意：
- 选择不包括非空的列进行计算
- where 和 having 的区别？
  1. where 在分组之前进项限定，如果不满足条件，则不参与分组。having 在分组之后进行限定，如果不满足结果，则不会被查询出来
  2. where 后不可以跟聚合函数，having可以进行聚合函数的判断

分页查询

语法

1
2
3

-- 每页显示3条记录
select * from student limit 0,3; -- 第一页
select * from student limit 3,3; -- 第二页

公式

开始的索引 = （当前页码-1）* 每页显示的条数
limit 是一个MySQL的方言

1	select * from table1;-- 从table1中找到所有数据

进阶查询

多表查询

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性
分类
1. 主键约束：primary key (非空且唯一)
2. 非空约束：not null
3. 唯一约束：unique （mysql中唯一约束限定列的值可以有多个null）
4. 外键约束：foreign key

用法

主键约束(primary key) 一张表只能有一个字段为主键即唯一标识

-- 创建表的时候添加主键元素
create table student(
	id int primary key,-- 给id添加主键约束
	name varchar(20)
);
-- 删除主键约束
alter table student drop primary key;-- 不需要告诉是哪个字段，因为主键只有一个
-- 建表后添加主键约束的操作
alter table student modify id int primary key;

-- 创建表时，添加主键约束，并且完成主键自增长
create table student(
	id int primary key AUTO_INCREMENT,-- auto_increment表示自增长（会读取最大的key值从而实现自增）
	name varchar(20)
);
 -- 使用时：
 insert into student value(NULL,'ccc');-- 这里的NULL对应的虽然是主键，但是因为auto_increment所以不会报错
 -- 删除自增长的
 alter table student modify id int;
 -- 添加自动增长
 alter table student modify id int AUTO_INCREMENT;

非空约束

-- 创建表时添加约束
create table student(
	id int,
	name varchar(20) not null-- 具体的约束直接写在后面即可
);
-- 创建表后添加约束
alter table student modify name varchar(20) not null;

-- 将name的非空约束删除
alter table student modify name varchar(20);-- 所以其实只需要重新附上类型即可

唯一约束（mysql中唯一约束限定列的值可以有多个null）

-- 创建表
create table student(
	id int,
	photo_number varchar(20) unique-- 唯一约束的添加
);
-- 删除唯一约束的唯一办法
alter table student drop index name;

-- 已经创建表后添加唯一约束的方式
alter table student modify name varchar(20) unique;

外键约束

创建表时可以添加外键

create table employee(
    dep_id int;-- 外键对应主表的主键
    constraint emp_dept_fk foreign key (dep_id) references department(id)
    -- 注意一定要先创建了department表不然无法连接
);

删除外键

1	alter table employee drop foreign key emp_dept_fk;

创建表之后添加外键

1	alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id);

级联操作

-- 级联更新
alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on update cascade;
-- 级联更新操作就是在原先添加外键的基础上加上 on update cascade 操作

-- 级联删除
alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on delete cascade;
-- 级联删除操作就是在原先的添加外键的基础上加上 on delete cascade 操作

DML：

数据操作语言（凡是对表当中的数据进行增删改的都是DML）

查看数据

语法

1	select * from table1;-- 从table1中找到所有数据

添加数据

语法

insert into table1(name,age) value(John,56); 
insert into stu value(John,56,NULL);
/*这是一种简化版本，但是必须按顺序给出所有的字段*/
insert into stu value(John,17,"1987-11-11");
/*注意这里的日期格式的方式*/

删除数据

语法

delete from table1 where id=1;
/*(不推荐使用)注意，其中如果没有where的条件判断，有可能删除所有的数据！！！*/
truncate table stu;
/*(推荐使用)删除表，然后再创建一个一模一样的空表*/

修改数据

语法

updata stu set age = 117 where id = 56;
/*
在stu数据表中，对id为56的数据 修改其age的值为117
updata 表名 set 列名1 = 值1, 列名2 = 值2,... where 条件
如果不加where的操作，将会对表所有的数据进行修改
*/

DDL：

对数据库的操作

create 新建

创建数据库(不判断是否已经创建)

1	create database db2;-- (db2表示要建的表的名字)

创建数据库(已经创建的数据库，也不会报错)
1
create database if not exists db2;
创建db4数据库，判断是否存在，并制定字符集为gbk
1
create database if not exists db4 character set gbk;

rerieve 查询

查询所有数据库名称
1
show databases;
查询某个数据库的字符集：查询某个数据库的创建语句
1
show create database 数据库名称;

drop 删除

删除数据库

1	drop database db2;-- db2(数据库名称)

判断数据库存在，存在再删除
1
drop databases if exists db2;

alter 修改

修改数据库的字符集

1	alter database db3 character set utf8;-- db3(数据库名称),utf8(字符集名称)

使用数据库

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

对数据库的表进行操作

insert 增

语法

create table table1(
    列名1 数据类型1,
    列名2 数据类型2,
    列名3 数据类型3,
    列名4 数据类型4,
    列名5 数据类型5
);-- talbe1(表名)并且最后一列不需要加逗号

数据类型详情（点击跳转）

创建表

create table student(
    id int,
    name varchar(32),
    age int,
    score double(4,1), -- 这里的4表示有效数字位数 1表示保留到小数后1位
    birthday date,
    timeshow timestamp
);

赋值表：

1	create table stu like student;-- 这里的student就是上面创建的表

delete 删

直接删除

1	drop table student;-- 这里的student就是表名

判断后删除

1	drop table if exists student;-- 这里的student就是表名

update改

修改表名

1	alter table table1 rename to table2;-- 这里是将表table1改名为table2

修改表的字符集

1	alter table table1 character set utf8;-- 这里的utf8表示字符集名称

添加一列

1	alter table table1 add name varchar(2); -- 这里的表示添加叫name的一列

修改列名称类型

1 2	alter table stu change name1 name2 varchar(10);-- 既改名字又改类型 alter table stu modify name1 varchar(10);-- 修改表的数据类型

删除列

1	alter table stu drop namee;-- 将stu表中的namee列删除

retrieve查

查询某个数据库中所有的表名称
1
show tables;
查询表结构
1
desc table1;-- table1(表名)

主要针对表中的数据进行更改

TCL:

事务控制语言

跳转到的地方

commit; 事务提交

rollback;事务回滚

DCL：

是数据控制语言。

grant；授权

revoke；撤销权限

数据库的设计

多表之间的关系

实现关系

-- 创建旅游线路分类表tab_category
-- cid旅游线路分类主键，自动增长
-- cname旅游线路分类名称非空，唯一，字符串100
create table tab_category (
cid int primary key auto_increment,
cname varchar(100) not null unique
);



-- 创建旅游线路表tab_route
/*
rid旅游线路主键，自动增长
rname旅游线路名称非空，唯一，字符串100
price价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
create table tab_route(
rid int primary key auto_increment,
rname varchar(100) not null unique,
price double,
rdate date,
cid int,
foreign key (cid) references tab_category(cid)
);

-- 创建旅游使用表tab_user
create table tab_user (
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1) default '男',
telephone varchar(11),
email varchar(100)
);



/*
创建收藏表tab_favorite
rid 旅游线路id，外键
date 收藏时间
uid用户id，外键
rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int,
date datetime,
uid int,
-- 创建复合主键
primary key(rid,uid),
foreign key (rid) references tab_route(rid),
foreign key(uid) references tab_user(uid)
);

数据库设计的范式

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

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式
- 第一范式（1NF）
- 第二范式（2NF）
- 第三范式（3NF）
- 巴斯-科德范式（BCNF）
- 第四范式（4NF）
- 第五范式（5NF，完美范式）
分类
- 第一范式：每一列都是不可分割的原子数据项（即不可拆分）
  
  问题：
  - 存在非常严重的数据冗余（重复）：姓名、系名、系主任
  - 数据添加存在问题，存在数据不合法的问题
  - 删除数据存在问题，存在一起消除数据的问题
- 第二范式：在第一范式基础上，非码属性必须完全依赖于候选码（在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. 码：如果一个属性或属性组被其他所有属性完全依赖，则称这个属性或者属性组为该表的码
- 第三范式：在2NF的基础上任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

数据库的备份和还原

命令行：

语法：
- 备份：mysqldump -u用户名 -p密码数据库名> 保存的路径\保存的名字（a.sql）

客户端图形化工具

数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT/INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

注意，这里用TIMESTAMP的时候，如果没有赋值，那么默认使用当前的系统时间来自动赋值

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串 varchar(20)表示20个字
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

MySQL代码的登录登出

进入对应的文件夹内，按住shift同时右击打开windowsPowerShell输入cmd
在命令行中输入mysql -u+账户 -p+密码（如果不想显示出来可以先不填密码，后续会让你填的）
成功进入后左下方会显示mysql>的语言表示登录成功！
退出时直接输入exit回车即可

MySQL中数据库操作

show databases;                        //查看已有的数据库
create database + 新数据库名字;        //创建数据库
use + 数据库名;                        //表示使用数据库
show tables;                          //在上一行的基础上个表示展示这个数据库中的所有的表
source + 路径;                        //表示将sql文件中的数据导入数据库中