数据库(MySQL黑马)
基础篇
MySQL概述
数据库概述
数据库相关概念
主流的关系型数据库管理系统
MySQL数据库的安装与启动
下载：MySQL :: MySQL Community Downloads
安装步骤
客户端连接
方式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-表操作-创建
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图形化界面工具
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.id
and d.name = '研发部';
-- 7、查询"研发部"员工的平均工资
-- 表：emp , dep
-- 连接条件 ： emp.dep_id = dep.id
select avg(e.salary)
from emp e,
dept d
where e.dept_id = d.id
and 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
注：事务隔离级别越高，数据越安全，但是性能越低 。
小结
原文地址：https://blog.csdn.net/zhang_longwei22/article/details/143904755
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！