数据库(MySQL黑马)

基础篇

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通用语法及分类

1. SQL语句可以单行或多行书写，以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 注释：

• 单行注释：--注释内容 或 # 注释内容（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.分类：

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

主要归类为列级约束和表级约束

1. 列级约束：NOT NULL | DEFAULT | PRIMARY KEY | UNIQUE | CHECK
2. 表级约束：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

 

 注：事务隔离级别越高，数据越安全，但是性能越低 。

小结