基础篇
MySQL概述
数据库概述
数据库相关概念
主流的关系型数据库管理系统
MySQL数据库的安装与启动
下载：MySQL :: MySQL Community Downloads
安装步骤
MySQL―8.0.40超详细保姆级安装教程_mysql8.0.40安装教程-CSDN博客文章浏览阅读1k次。_mysql8.0.40安装教程https://blog.csdn.net/2403_88677213/article/details/143582509
客户端连接
方式1：MySQL提供的客户端命令行工具
方式2：系统自带的命令行工具执行指令
mysql [-h 127.0.0.1] [-p 3306] -u root -p
(ps:要想在任意的目录下执行mysql指令，必须要先配置PATH环境变量）
数据模型
-
关系型数据库(RDBMS)
概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
特点：
1.使用表存储数据，格式统一，便于维护
2.使用SQL语言操作，标准统一，使用方便
SQL
SQL通用语法及分类
- SQL语句可以单行或多行书写，以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
- 注释：
- 单行注释：--注释内容 或 # 注释内容（MySQL持有）
- 多行注释：/*注释内容*/
SQL分类
DDL
DDL-数据库操作
-
查询
SHOW DATABASES；//查询所有数据库
SELECT DATABASE();//查询当前数据库
-
创建
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [ COLLATE 排序规则]；
-
删除
DROP DATABASE [ IF EXISTS ]数据库名；
-
使用
USE 数据库名；
DDL-表操作-创建&查询
DDL-表操作-查询
- 查询当前数据库所有表
SHOW TABLES;
- 查询表结构
DESC表名；
- 查询指定表的建表语句
SHOW CREATE TABLE 表名；
DDL-表操作-创建
建表规范 ：数据库MySQL-表【DDL/DML】_数据库ddl建表规范-CSDN博客文章浏览阅读239次。数据库MySQL-表【DDL/DML】7、表的创建（建表）7.1、建表的语法格式：(建表属于DDL语句，DDL包括：create drop alter)create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。字段名：见名知意。表名和字段名都属于标识符。7.2、关于mysql中的_数据库ddl建表规范https://blog.csdn.net/qq_43030934/article/details/123178158
DDL-数据类型及案例
-
数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| 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的值 | 小数值 |
-
字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| 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 | 极大文本数据 |
TEXT：描述文本数据 BLOB：描述二进制数据
char类型： 定义形式为 char[10] varchar类型：定义形式为varchar[10]
注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
| 比较项目 | CHAR | VARCHAR |
|---|---|---|
| 空间使用方式 | 定义多大就占用多大空间，不足则在右侧填充空格至定义长度 | 根据输入字符串的实际大小来占用空间，上限为所定义的大小 |
| 示例说明 | 定义CHAR(8)，存储"abc"时，实际存储为"abc "（后面 5 个空格填充） | 定义VARCHAR(8)，存储"abc"时，只占用存储"abc"实际所需空间 |
| 效率层面 | 性能相对较好，因为数据长度固定，在存储和检索时处理相对简单直接 | 性能相对较差，由于其可变长度的特性，存储和检索时可能需要更多的处理来确定实际长度等操作 |
| 适用场景 | 适合存储长度固定的数据，如身份证号（固定 18 位）、邮编（固定几位数字等）、固定格式的代码等 | 适合存储长度不确定且变化较大的数据，如用户评论、文章内容、产品描述等 |
-
日期时间类型：
| 类型 | 大小 ( 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:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值，时间戳 |
案例
DDL-表操作-修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束]；
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)；
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束]；
删除字段
ALTER TABLE 表名 DROP 字段名；
修改表名
ALTER TABLE 表名 RENAME TO 新表名；
DDL-表操作-删除
删除表
DROP TABLE 表名; -- 直接删除表，不检查是否存在
或
DROP TABLE [IF EXISTS] 表名; -- 会检查是否存在，如果存在则删除
删除指定表，并重新创建该表
TRUNCATE TABLE 表名；
PS：在删除表的同时表中的数据也会删除。
DDL总结
MySQL图形化界面工具
DataGrip下载安装及使用教程(详细版)-CSDN博客文章浏览阅读7.2w次，点赞44次，收藏237次。本文提供了DataGrip的详细安装教程和应用指南。从官网下载最新版本的IDE，选择安装路径并进行安装。激活后，启动DataGrip，新建项目并选择数据库类型。在连接界面配置服务器信息，下载驱动，测试连接成功后即可开始使用，查看和操作数据库。https://blog.csdn.net/m0_70536638/article/details/128860243
DML
DML（Data Manipulation Language）语句: 数据操纵语言，主要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。
- INSERT 添加数据
- UPDATE 修改数据
-
DELETE 删除数据
DML-插入
-
给指定字段添加数据
INSERT INTO 表名 (字段名1，字段名2，...) VALUES(值1，值2，...)；
-
给全部字段添加数据
INSERT INTO 表名 VALUES(值1，值2，...)；
-
批量添加数据
INSERT INTO 表名 (字段名1，字段名2，...) VALUES(值1，值2，...)，(值1，值2，...)；
INSERT INTO 表名 VALUES(值1，值2，...)，(值1，值2，...)，(值1，值2，...)；
小Tips：
select * from 表名；/*在命令行输入完成对表的查看*/
DML-修改数据
UPDATE 表名 SET 字段名1 = 值1，字段名2 = 值2，... [WHERE 条件]；
注意：修改语句中的条件可以有，也可以没有，如果没有条件时，则会修改整张表的所有数据。
DML-删除数据
DELETE FROM 表名 [WHERE 条件]
注意：
DELETE语句的条件可以有，也可以没有，如果没有条件时，则会修改整张表的所有数据。
DELETE语句不能删除某一个字段的值（可以使用UPDATE）。
DML语句小结
DQL
DQL（Data Query Language）语句：数据查询语言，主要是对数据进行查询操作。常用关键字有 SELECT、FROM、WHERE 等。
查询关键字：SELECT （频次最多）
DQL-语法
DQL-基本查询
1.查询多个字段
SELECT 字段1,字段2,字段3,... FROM 表名；
SELECT * FROM 表名；
2.设置别名
SELECT 字段1 [ AS 别名1 ]，字段2 [ AS 别名2 ] ... FROM 表名；
3.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名；
DQL-条件查询
1.语法
SELECT 字段列表 FROM 表名 WHERE 条件列表；
2.条件
DQL-聚合函数
1.介绍
将一列数据作为一个整体，进行纵向计算。
2.常见聚合函数
3.语法
SELECT 聚合函数（字段列表）FROM 表名;
DQL-分组查询
1.语法
SELECT 字段列表 FROM [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ]；
2.where与having区别
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
注意
执行顺序：where>聚合函数>having。
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
DQL-排序查询
1.语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
2.排序方式
- ASC：升序（默认值）
- DESC：降序
注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
DQL-分页查询
1.语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数；
注意
- 起始索引从0开始，起始索引 =（查询页码 -1）* 每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
- 如果查询的是第一页数据，起始索引可以省略，直接写为 limit 10。
DQL-执行顺序
DQL-小结
DCL
DCL（Data Control Language）语句： 数据控制语言，主要是用来设置/更改数据库用户权限。常用关键字有 GRANT、REVOKE 等。
DCL-用户管理
1.查询用户
USE mysql；
SELECT * FROM user；
2.创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'；
3.修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'；
4.删除用户
DROP USER '用户名'@'主机名'；
注意：
- 主机名可以使用%通配
- 这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用。
练习：
--创建用户itcast 只能够在当前主机localhost访问，密码123456;
create user 'itcast'@'localhost' identified by '123456';--创建用户 heima ，可以在任意主机访问该数据库，密码123456；;
create user 'heima'@'%' identified by '123456';--修改用户heima 的访问密码 1234;
alter user 'heima'@'%' identified with mysql_native_password BY '1234';--删除itcast@localhost用户
drop user 'itcast'@'localhost';DCL-权限控制
常用：
1.查询权限
SHOW GRANTS FOR '用户名'@'主机名'；
2.授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'；
3.撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'；
注意：
- 多个权限之间，使用逗号分隔
- 授权时，数据库名和表名可以使用*进行通配，代表所有。
DCL-小结
函数
是指一段可以直接被另一段程序调用的程序或代码。
字符串函数
运用函数是尽量不要出现 函数 (s1,s2,s3...) 函数中间尽量不要有空格
SELECT 函数(参数)
数值函数
日期函数
流程（控制）函数
约束
概述
1.概述：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
2.目的：保证数据库中数据的正确，有效性和完整性。
3.分类：
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
主要归类为列级约束和表级约束
列级约束：NOT NULL|DEFAULT|PRIMARY KEY|UNIQUE|CHECK表级约束：PRIMARY KEY|UNIQUE|CHECK|FOREIGN KEY
约束演示
--------------约束演示-------------------
create table user
(id int primary key auto_increment comment '主键',name varchar(10) not null unique comment '姓名',age int check (age > 0 && age <= 120 ) comment '年龄',status char(1) default '1' comment '状态',gender char(1) comment '性别'
)comment '用户表';---------插入数据
insert into user(name,age,status,gender) values ('Tom1',10,'1','男'),('Tom2','9',1,'女');
insert into user(name,age,status,gender) values ('Tom3',10,'1','男');insert into user(name,age,status,gender) values (null,10,'1','男');#报错：[23000][1048] Column 'name' cannot be null
insert into user(name,age,status,gender) values ('Tom3',10,'1','男');#报错：[23000][1062] Duplicate entry 'Tom3' for key 'user. name'insert into user(name,age,status,gender) values ('Tom4',80,'1','男');
insert into user(name,age,status,gender) values ('Tom5',-1,'1','男');#报错：[HY000][3819] Check constraint 'user_chk_1' is violated.
insert into user(name,age,status,gender) values ('Tom6',121,'1','男');#报错：[HY000][3819] Check constraint 'user_chk_1' is violated.insert into user(name,age,gender) values ('Tom7',120,'男');#给予默认值
外键约束
-
概念
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
-
语法
添加外键
删除外键
外键删除/更新行为
## 语法（cascade可替换）
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id) on update cascade on delete cascade ;
小结
多表查询
多表关系
- 概述
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多（多对一）
- 多对多
- 一对一
一对多（多对一）
多对多
一对一
多表查询概述
概述：指从多张表中查询数据
笛卡尔积：笛卡尔乘积是指在数学中，两个集合 A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
真正需要的
多表查询的分类：
连接查询
内连接
外连接
自连接
联合查询
查询语句A（得表A）
union（all）
查询语句B（得表B）
子查询
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=
列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN、NOT IN、ANY、SOME、ALL
行子查询
子查询返回的结果是一行（可以是多行）,这种子查询称为行子查询。
常用的操作符： =、<>、IN、NOT IN
表子查询
子查询返回的结果是多行多行,这种子查询称为表子查询。
常用的操作符：IN
案例演示
要求
准备数据
-- 准备数据
create table dept(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '部门名称'
)comment '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办');alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);create table emp(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '姓名',age int comment '年龄',job varchar(20) comment '职位',salary int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导ID',dept_id int comment '部门ID'
)comment '员工表';INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);
create table salgrade(grade int,losal int,hisal int
)comment "薪资等级表"insert into salgrade value(1,0,3000);
insert into salgrade value(2,3001,5000);
insert into salgrade value(31,5001,8000);
insert into salgrade value(4,8001,10000);
insert into salgrade value(5,10001,15000);
insert into salgrade value(6,15001,20000);
insert into salgrade value(7,20001,25000);
insert into salgrade value(8,25001,30000);-- 案例
-- 1、查询员工姓名、年龄、部门信息（隐式内连接）
-- 表：emp , dept
-- 连接条件 ： emp.dep_id = dep.id
select e.name,e.age, e.job,d.name from emp e,dept d where e.dept_id = d.id;-- 2、查询年龄大于20的员工姓名、年龄、部门信息 （显示内连接）
-- 表：emp , dept
-- 连接条件 ： emp.dep_id = dep.id
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id =d.id where e.age > 20;-- 3、查询拥有员工的部门ID、部门名称 (部门表和员工表交集的部分)
-- 表：emp , dept
-- 连接条件 ： emp.dep_id = dep.id
-- distinct 去重
select distinct d.id, d.name from emp e,dept d where e.dept_id = d.id;-- 4、查询所有年龄大于25的员工及其归属的部门名称，如果没有分配部门，也要展示出来
-- 表：emp , dept
-- 连接条件 ： emp.dep_id = dep.id
-- 外连接
select e.*,d.name from emp e left join dept d on d.id = e.dept_id where e.age > 25;-- 5、查询所有员工工资等级
-- 表：emp , salgrade
-- 连接条件 ： emp.salary >= salgrade.losal && emp.salary <= salgrade.hisal
select e.*, s.grade, s.losal,s.hisal from emp e,salgrade s where e.salary >= s.losal && e.salary <= s.hisal;
select * from emp e ,salgrade s where e.salary between s.losal and s.hisal;-- 6、查询"研发部"所有员工信息及工资等级
-- 表：emp , salgrade， dep
-- 连接条件 ：e.salary between s.losal and s.hisal; emp.dep_id = dep.id
-- 查询条件：dept.dept = '研发部'
select e.*, s.grade
from emp e,dept d,salgrade s
where (e.salary between s.losal and s.hisal)and e.dept_id = d.idand d.name = '研发部';-- 7、查询"研发部"员工的平均工资
-- 表：emp , dep
-- 连接条件 ： emp.dep_id = dep.id
select avg(e.salary)
from emp e,dept d
where e.dept_id = d.idand d.name= '研发部';-- 8、查询工资比小昭高的员工信息
-- 表：emp
select *
from emp e
where salary > (select e.salary from emp e where e.name = '小昭');-- 9、查询比平均工资高的员工信息
-- 表：emp
select *
from emp e
where salary > (select avg(salary) from emp);-- 10、查询低于本部门平均工资的员工
-- 表：emp , dept
-- 连接条件 ： emp.dep_id = dep.id
select *
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);-- 11、查询所有部门的信息，并统计部门的员工人数
select d.id,d.name ,(select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;小结
事务
事务简介
一组操作的集合，它是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
操作演示
-- 数据准备
create table account(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',monney int comment '余额'
) comment '账户表';insert into account (id, name, monney)
values (null,'张三',2000),(null,'李四',2000);-- 恢复数据
update account set monney = 2000 where name = '张三' or name = '李四';-- 张三给李四转账1000
select * from account where name = '张三';
update account set monney = monney - 1000 where name = '张三';
程序抛出异常...
update account set monney = monney + 1000 where name = '李四';
select @@autocommit;
set @@autocommit = 0;-- 张三给李四转账1000
select * from account where name = '张三';
update account set monney = monney - 1000 where name = '张三';
update account set monney = monney + 1000 where name = '李四';-- 提交事务
commit ;-- 回滚事务；
rollback ; 
-- 张三给李四转账1000
start transaction ;
select * from account where name = '张三';
update account set monney = monney - 1000 where name = '张三';
update account set monney = monney + 1000 where name = '李四';
commit ;
rollback ;事务四大特性
并发事务问题
事务隔离级别
MySQL默认为 Repeatable Read（默认） Orcle默认为Read committed
注：事务隔离级别越高，数据越安全，但是性能越低 。
小结
