MySQL数据库-黑马程序员

前言·

评价：有时候PPT上的定义不够清晰；感觉不如菜鸟教程或w3school；仿佛为了通俗，但失去了准确性

操作教学挺简单，inspire兴趣

前面几集感觉口音确实有趣，大圣哈哈

内容目录

基础：Mysql，SQL，函数，约束，多表查询，事务
进阶：存储引擎，索引，SQL优化，视图/存储过程/触发器，锁，InnoDB核心，Mysql管理
运维：日志，主从复制，分库分表，读写分离

概述及安装·

数据库-DataBase DB：存储数据的仓库，数据是有组织地存储

数据库管理系统-DataBase Management System DBMS：操纵和管理数据库的大型软件

SQL-Structured Query Language SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

主流的关系型数据库管理系统：

ORACLE,MySQL（开源-社区版），SQL Server（微软），…,SQLite等等

MySQL :: Download MySQL Installer 安装地址

博客教程：MySQL数据库下载及安装教程（最最新版）-CSDN博客

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -h localhost -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

添加环境变量

数据模型：用来描述数据、组织数据和对数据进行操作，是对现实世界数据特征的描述。

【视频里一带而过】

什么是数据模型？数据模型简介 - 知乎 (zhihu.com)

关系型数据库是指采用了关系模型来组织数据的数据库。简单来说，关系模式就是二维表格模型。

两张表相互关联

SQL语法定义，操作，查询，控制·

可单行或多行书写，分号结尾；可空格/缩进提高可读性；不区分大小写，关键字建议大写

注释：-- # 多行注释：/**/

DDL Data Denfinition Language数据定义语言：用来定义数据库对象（数据库，表，字段）

DML Data Manipulation Language数据操作语言：对数据库中的数据进行增删改

DQL Data Query Language数据查询语言：用来查询数据库中表的记录

DCL Data Control Language数据控制语言：用来创建数据库用户、控制数据库的访问权限

DDL 数据定义语言·

查询所有数据库：SHOW DATABASES;

查询当前数据库：SELECT DATABASE();(例如use 数据库后查询)

创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT 字符集CHARSET UTF8MB4] [COLLATE 排序规则];（CREATE DATABASE 顺序必须在最前面）

删除数据库：DROP DATABASE [IF EXISTS] 数据库名;

使用数据库：USE 数据库名;

“Query OK, 1 row affected (0.12 sec);”提示中，“Query OK”表示上面的命令执行成功，“1 row affected”表示操作只影响了数据库中一行的记录

表操作创建：

id	name	age	gender
1	郭靖	28	男
2	杨过	18	男
3	张无忌	8	男

create table user(
	id int comment '编号',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender varchar(1) comment '性别'
)comment '用户表';

查询当前数据库所有表：show tables;

+---------------+
| Tables_in_han |
+---------------+
| user          |
+---------------+

查询表结构：desc 表名;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

查询指定表的建表语句：show create table 表名;

| user  | CREATE TABLE `user` (
  `id` int DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |

MySQL数据类型：

数值类型：

TINYINT UNSIGNED
INT
FLOAT
DOUBLE
DECIMAL 依赖M（精度）和D（标度）的值

字符串类型：

CHAR：定长字符串
VARCHAR：变长字符串（不确定长度的使用varchar能节省空间）
blob二进制相关不常用

日期时间类型：

DATE：YYYY-MM-DD日期值
TIME：HH:MM:SS时间值
DATETIME：YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP：YYYY-MM-DD HH:MM:SS混合日期和时间值时间戳

mysql中的timestamp类型到了2038年怎么办？ - 知乎 (zhihu.com)

2000年问题 - 维基百科，自由的百科全书 (wikipedia.org)

create table emp(
	id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '卡号',
    entrytime date comment '入职时间'
)comment '员工表';

想了想这种实操型的教学视频，有机会还是把代码再敲一遍好

添加字段（第一行的属性名）：alter table 表名 add 字段名类型（长度） [comment 注释] [约束]

例如：为emp添加字段“昵称”为nickname，类型varchar(20) ：alter table emp add nickname varchar(20) comment '昵称';

alter翻译：（通常指轻微地）改动，修改 ;alert翻译：警报

修改数据类型：alter table 表名 Modify 字段名新数据类型（长度）

修改字段名和字段类型：alter table 表名 change 旧字段名新字段名新类型（长度） [comment 注释] [约束]

案例：nickname字段修改为username，类型为varchar(30)：alter table emp change nickname username varchar(30) comment '用户名';

删除字段：alter table 表名 drop 字段名;

修改表名：alter table 表名 rename to 新表名;

删除表：drop table [if exists] 表名;

删除表(所有数据)，并重建新空表：truncate table 表名;

MySQL图形化界面：

Sqlyog、Navicat、DataGrip（原来是要用JB公司的DataGrip，怪不得，目录里没有Navicat）

Download DataGrip: Cross-Platform IDE for Databases & SQL (jetbrains.com)

安装好+配置MySQL就可以手动图形化界面操作数据库了||以后有需要再安装Navicat

DML 数据操作语言·

添加数据（insert）

给指定字段添加数据：insert into 表名(字段名，字段名2) value(value1，value2);
给全部字段添加数据：insert into 表名 value(value1，value2,...);
如果批量添加，那么value(value1，value2,…)，value(value1，value2,…)；每组值之间逗号分隔

*字段顺序与值对应；字符串和日期需包含引号；插入数据大小在字段范围内

table右键：选项query console

例子：
insert into employee(id, workno, name, gender, age, idcard, entrytime) values (1,'321','章','男',23,123456789012345678,'2003-11-1');
select * from employee;
insert into employee values (1,'111','K','男',23,123456789012345678,'2003-11-1');#table后括号直接去掉；为所有赋值
insert into employee values (3,'1431','赵敏','女',23,123456389012345678,'2003-11-1'),(7,'111','周芷若','女',23,123456789012345678,'2003-11-1');#批量插入直接加逗号

修改数据（update）

update 表名 set 字段名1=值1,字段名2=值2,... [where 条件];没有条件则修改整个表，此时会警告

How to enable unsafe query note back in DataGrip? If I click execute and suppress once

execute and suppress 执行并且抑制（以后不提醒不安全行为）

删除数据（delete）

delete from 表名 [where 条件];

没有条件，则删除整张表的所有数据；delete不能删除某个字段的值（要用update）

例子：
update employee set idcard=123456789012345612;
update employee set name='小昭',gender='女' where id=1;
delete from employee where gender='女';
delete from employee;

DQL 数据查询语言·

查询的频次远高于增删（通常，想到了数据结构）

商场就是数据库的东西，前端渲染美化了一下，然后让用户筛选，怪不得很多JAVA项目最后是什么XX商城/瑞吉外卖，适合把之前学过的知识融会贯通

感觉MySQL学的很清晰

基本查询

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

select * from 表名;

设置别名类似第一行属性/注释

select 字段1 [as 别名1],字段2 [as 别名2],... from 表名; 例如：别名 ‘性别’

去除重复记录

select distinct 字段列表 from 表名;

Ctrl+Enter enter键确认执行SQL

select name,workno,age from employee;#查询并只返回XX字段的内容
select * from employee;#查询返回所有，有时可能不规范
select gender as '性别' from employee;#as可去掉； ''类似第一行属性/注释
select distinct gender from employee;#只查询第一个不重复出现的

条件查询

select 字段列表 from 表名 where 条件列表;

<=小于等于

<>或!=代表不等于

BETWEEN … AND … 在xx范围之内，闭区间

IN(…) SQL IN 操作符 (w3school.com.cn) SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

LIKE 占位符模糊匹配（_匹配单个字符，“%” 符号用于在模式的前后定义通配符（默认字母））

百分号（%）	代表零个、一个或者多个任意的字符。
下划线（_）	代表单个字符或者数字。

IS NULL 是NULL

AND && 且

OR ||或

NOT !非

例子
select * from employee where idcard is null;#查询出idcard值为NULL的员工
select * from employee where age between 4 and 23;#闭区间 必须前小后大
select * from employee where name like '__';#名字两个字符的员工 占位符，还以为用函数呢
select * from employee where idcard like '%8%';# %8%必须中间8；%X就是最后一位是X

聚合函数

将一列数据纵向计算

count 统计数量； min max ；avg平均值；sum求和

语法：select 聚合函数（字段列表） from 表名; NULL不参与聚合函数运算

练习
select count(*) from employee#统计员工数量 类似于（不全为NULL）有几行；和sum区分
select avg(age) from employee#统计员工数量
min,max,sum同理

分组查询

语法：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where和having区别：

执行时机：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果过滤
判断条件不同：where不能对聚合函数判断，而having可以

执行顺序：where->聚合函数->having

分组之后，查询的字段一般为分组字段和聚合函数，查询其他的字段无意义（例如男 3；女 7；写姓名没有意义，毕竟每一行都不一样）

练习
select gender,count(*) from employee group by gender;#根据性别分组，统计男性员工和女性员工的数量
错误的code：select age,avg(age) from employee group by gender;#根据性别分组，统计男性员工和女性员工的平均年龄
[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'han.employee.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
RIGHT:select age,avg(age) from employee group by gender;#根据性别分组，统计男性员工和女性员工的平均年龄
select workaddress,count(*) address_count from emp<45 group by workaddress having address_count >= 3 #查询年龄小于45岁的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址 address_count是对count(*)起的一个别名，如果不想的话，后面直接改成count(*)>=3即可
这样一下就理解了having的作用

SQL 别名 | 菜鸟教程 (runoob.com)

mysql别名引号与引用问题_mysql数据库引用别名,where t.version =g_version 需要加引号吗?-CSDN博客 sql中别名as，不写，以及使用双引号总结_sql别名不用as规范吗-CSDN博客

别名问题再说前面不能用中文的记得加引号

排序查询

语法：select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2,..;

排序方式：ASC升序（默认值）；DESC降序

多字段排序，当第一个字段值相同时，进行第二个第二个字段排序

1
2

select name,age from employee order by age asc;#根据年龄员工升序排序 name,age可换成*
select * from employee order by age asc,entrydate desc;#根据年龄员工升序排序,相同则入职时间降序

分页查询

语法：select 字段列表 from 表名 limit 起始索引,查询记录数;

注：

起始索引从0开始，起始索引 = （查询页码-1）*每页显示记录数
分页查询在不同的数据库（MySQL、ORECAL）关键字limit不同
查询的是第一页数据，起始索引可省略；limit 10 （每页/查询记录数为10，第一页）

练习
select * from employee limit 2;#查询第1页，每页展示10条记录
#发现4个数据，查询记录数为3，那么0,3 1，3第一页和第二页显示的是前三个和后三个，会补全查询数；唉以为是查询页码直接-1呢
select * from employee limit 3,3;#查询第1页，每页展示10条记录 （查询页码-1）*每页显示记录数

DQL查询练习
select * from employee where between 20 and 23 && gender=='女';#查询年龄为20，21，22，23的女性员工信息
select * from employee where age between 20 and 23 && gender='女';#正确的 也可 age in(20,21,22,23)
问题：1. between 没写字段 2. gender= 写成了两个=
select * from employee where age between 20 and 40 && gender='男' && name='___';#姓名三个字，男，20-40岁 名字三个字 三个_
select gender count(*) from employee where age<60 group by gender;#小于60岁的男性员工和女性员工数 count(*)或count(gender)都可以，gender和count()之间逗号连接(都是一类数据)
select name,age from employee where age<=35 order by age asc,entrydate desc;#查询年龄<=35的员工姓名和年龄；年龄升序，入职时间降序
select * from employee where age between 20 and 40 && gender='男' limit 0,5 order by age asc,entrydate desc;#男，20-40，前5个员工，年龄升序，入职时间降序 会报错
select * from employee where age between 20 and 40 && gender='男' order by age asc,entrydate desc limit 0,5 ;#默认先排序后分页查询，挺好避免歧义；另外先查询后排序的需求也少，可通过其他方式实现吧

DQL执行顺序：from > where > group by > having >select > order by > limit

编写顺序：select > from > where > group by > having > order by > limit

通过别名的声明顺序验证了执行顺序

select e.name ename,e.age eage from employee e where e.age > 15 order by eage asc;

注：having是分组（group by）后过滤，如获取员工数量（count）大于等于3的工作地址 SQL HAVING 子句 | 菜鸟教程 (runoob.com)

DCL 数据控制语言·

用来管理数据库用户（谁能访问）、控制数据库的访问权限（访问什么怎么访问）

管理用户

查询用户

use mysql; select * from user; 哈哈，直接空格两条sql语句能一块执行

创建用户

create user '用户名'@'主机名' identified by '密码' 创建后没有权限

修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'

[MySQL] MySQL身份验证插件 - piaohua’s blog

删除用户

drop user '用户名'@'主机名'

这类SQL开发人员操作较少，主要是DBA（Database Administrator 数据库管理员）使用

权限控制

用户创建后需要分配权限，常见权限如下：

ALL 所有权限；select 查询数据，insert 插入数据，update 修改数据，delete 删除数据，alter 修改表，drop 删除数据库/表/视图 create 创建数据库/表

查询某个用户拥有的权限：show grants for '用户名'@'主机名';

授予权限：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

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

多个权限之间逗号分隔；数据库名和表名用*通配，表示所有

例子
管理用户
create user 'itcast'@'localhost' identified by '123456'#进入mysql，注册用户itcast 只能当前主机访问，密码
create user 'heima'@'%' identified by '123456'#注册用户hiema 任意主机访问，密码
alter user 'itcast'@'localhost' identified with mysql_native_password by '12345';#修改密码
drop user 'heima'@'%';#删除用户

权限控制
show grants for 'itcast'@'localhost';#返回结果：GRANT USAGE ON *.* TO `itcast`@`localhost`
grant all on han.* to 'itcast'@'localhost';# 授予itcast关于han数据库的所有权限 | GRANT ALL PRIVILEGES ON `han`.* TO `itcast`@`localhost`
revoke all on han.* from 'itcast'@'localhost';# 撤销itcast关于han数据库的所有权限
GRANT SELECT ON `han`.* TO `itcast`@`localhost`#只授予查询权限

函数·

函数：可被另一段代码调用的程序或代码

字符串函数

相对常用的字符串函数

concat(s1,s2,…,sn)：字符串拼接
lower(str)/upper(str)：字符串全部转为小写/大写
lpad(str,n,pad)/rpad(str,n,pad)：用pad左/右填充，达到n个字符串长度
trim(str)：去掉首尾空格
substring(str,start,len)：截断start位置起的len长度字符串

数值函数

ceil(x) 向上取整 | floor(x) 向下取整
mod(x,y) 返回X/Y的模负数究竟是如何取模的？ - 知乎 (zhihu.com)
rand()返回0~1之间的随机数
round(x,y)求参数X四舍五入的值，保留y位小数

日期函数

curdate()：当前日期
curtime()：当前时间：时分秒
now()：日期和时间
year(x) | month(x) | day(x)：x的年月日
date_add(date,inteval expr type)：一个日期/时间值加上一个时间间隔expr后的时间值| interval 间隔
datediff(date1,date2)：起始时间date1和结束时间2之间天数前减后

流程函数

if(value,t,f)：如果value为true，则返回t，否则返回f
ifnull(value1,value2)：如果value1不为null，返回value1，否则value2
case 字段名 when [val1] then [res1]…else [default] end：如果val1为true，返回res1… 否则返回default默认值 | 如果字段的val等于valX，进入X执行
case when [val1[expr]] then [res1] … else [default] end：如果expr的值等于 val1，返回res1… 否则返回default默认值 | 如果满足valX[EXPR] 表达式成立就进入X执行

练习
字符串
select concat('hello',2);
select lower('Hello'); select upper('Hello');
select lpad('01',5,'-'); select rpad('01',5,'-');
select trim('  hello mysql ');
select substring('hello world',1,5);#从1开始截取5个字符 hello
update employee set workno = lpad(workno,5,'0');#将工号改为前导0，总长为5；例如：1改为00001
select name,workno from employee;

数值
select ceil(1.1); select floor(1.9); select mod(-3,4); select rand();
select round(2.344,2); select round(2.345,2);
select lpad(floor(rand()*1000000),6,'0');#生成六位数的验证码 lpad是补齐到六位，因此不需要判断生成的第一位是否是0，是0则成为5位自动补齐

日期
select curdate(); select curtime(); select now();#2023-12-20 20:19:00
select year(now()); select month(now()); select day(now());
select date_add(now(),interval 70 day);
select datediff(now(),'2023-12-19');
select name,datediff(now(),entrytime) as '入职时间' from employee order by 入职时间;#查询所有员工入职时间，入职天数倒序

流程
select if(false,'OK','error'); select if(0,'OK','false'); select if(-1,'OK','false');0代表false
select ifnull( null,'de'); select ifnull('','de')#只有null，且必须声明
alter table employee add workaddress varchar(20) comment '工作地址';#添加字段
select name,
       case workaddress when 'beijing' then '一线' when 'shanghai' then '一线' else '其他' end as '工作地址'#case判断，类似分组；有剩余/其他功能 
from employee;
建表
create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int ,
    english int,
    chinese int
) comment '成绩表';
insert into score values (1,'tom',67,88,95),(2,'han',23,66,90),(3,'jack',32,65,89);# 可以只写表名 注意不是score(*)
select
    name,
    case when math>=85 then 'good' when math >=60 then 'pass' else 'failed' end as 'math',
    case when english>=85 then 'good' when english >=60 then 'pass' else 'failed' end as 'english',
    case when chinese>=85 then 'good' when chinese>=60 then 'pass' else 'failed' end as 'chinese'
from score;

太棒了，学完了，不难理解，敲起来费时间

约束·

约束：作用于表中字段上的规则，用于限制存储在表中的数据

目的：保证数据库的数据正确、有效、完整

非空约束 not null：限制该字段的数据不能为NULL
唯一约束 unique：保证该字段的数据都是唯一、不重复的
主键约束 primary key：主键是一行数据的唯一标识，要求非空且唯一主键(primary key)和唯一键(unique) 知识点总结-CSDN博客通常为id
默认约束 default：保存数据时，如果未指定该字段的值，则采用默认值
检查约束 check：保证字段值满足某一个条件
外键约束 foreign key：用来让两张表的数据之间建立连接，保证数据的一致性和完整性

注：约束作用于表中的字段上，可以在创建表/修改表的时候添加约束。

演示:

数据插入失败引发的主键auto_increment问题-腾讯云开发者社区-腾讯云 (tencent.com)

mysql insert操作失败后id 在auto_increment下仍会自增的解决办法 - 少喝 - 博客园 (cnblogs.com)

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

具有外键的称为父表，外键关联的称为子表；员工表有dept_id，和部门表的id对应

create table yueshu_user(
    id int primary key auto_increment comment '主键',#主键，自动增长
    name varchar(10) not null unique comment '姓名',#不为空，唯一
    age int check ( age>0&&age<=120 ) comment '年龄',#check
    status char(1) default '1' comment '状态',#没有指定值，则默认为1
    gender char(1) comment '性别'#无
) comment '约束用户表';
insert into yueshu_user values (19,'tom6',19,'1','男'),(2,'tom2',19,'0','男');#可以自行设置id大小，之后就会以最大值id增长
insert into yueshu_user(name,age,gender) values ('tom7',13,'女');

MySQL数据库-黑马程序员

前言·

概述及安装·

SQL语法定义，操作，查询，控制·

DDL 数据定义语言·

DML 数据操作语言·

DQL 数据查询语言·

DCL 数据控制语言·

函数·

约束·

多表查询·

事务·

前言·

概述及安装·

SQL语法 定义，操作，查询，控制·

DDL 数据定义语言·

DML 数据操作语言·

DQL 数据查询语言·

DCL 数据控制语言·

函数·

约束·

多表查询·

事务·

SQL语法定义，操作，查询，控制·