[黑马程序员] 数据库基础篇

一条闲鱼_mytube

已于 2023-06-25 14:24:21 修改

阅读量186

点赞数

分类专栏： mysql 文章标签：数据库 sql

于 2023-06-12 18:45:16 首次发布

本文链接：https://blog.csdn.net/weixin_38805083/article/details/131174916

版权

mysql 专栏收录该内容

49 篇文章 1 订阅

订阅专栏

SQL

基础

SQL： Structured Query Language
分类
- DDL (data definition language):数据定义语言，用来定义数据库对象(数据库，表，
  字段)
- DML (Data Manipulation Language): 数据操作语言，用来对数据库表中的数据进行增删改
- DQL (Data Query Language): 数据查询语言，用来查询数据库中表的记录
- DCL (Data Control Language): 数据控制语言，用来创建数据库用户、控制数据库的访问权限

数据库操作

创建数据库

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序 规则 ] ;

删除 drop dbname;
切换数据库 user dbname;

表操作

show tables
desc tableName; 查看表结构
show create table 表明查看创建表语句
类型：主要字符串，数值类型，日期

表结构修改

添加字段 ALTER TABLE 表名 ADD 字段名类型 (长度) [ COMMENT 注释 ] [ 约束 ];
修改字段数据类型： ALTER TABLE 表名 MODIFY 字段名新数据类型 (长度);
修改字段名和字段类型： ALTER TABLE 表名 CHANGE 旧字段名新字段名类型 (长度) [ COMMENT 注释 ] [ 约束 ];
删除字段： ALTER TABLE 表名 DROP 字段名;
修改表名： ALTER TABLE 表名 RENAME TO 新表名;

表操作-删除

删除表：DROP TABLE [ IF EXISTS ] 表名;IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不
加该参数项，删除一张不存在的表，执行将会报错)。
删除指定表, 并重新创建表： TRUNCATE TABLE 表名;在删除表的时候，表中的全部数据也都会被删除

DML

添加数据（INSERT）：
- 给指定字段添加数据：INSERT INTO 表名 (字段名1, 字段名2, …) VALUES (值1, 值2, …);
- 给全部字段添加数据：INSERT INTO 表名 VALUES (值1, 值2, …);
- 批量添加数据
  - INSERT INTO 表名 (字段名1, 字段名2, …) VALUES (值1, 值2, …), (值1, 值2, …), (值 1, 值2, …) ;
  - INSERT INTO 表名 VALUES (值1, 值2, …), (值1, 值2, …), (值1, 值2, …) ;
  - 注意：
    - 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
    - 字符串和日期型数据应该包含在引号中。
    - 插入的数据大小，应该在字段的规定范围内。
修改数据（UPDATE）
- UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , … [ WHERE 条件 ] ;
删除数据（DELETE）
- DELETE FROM 表名 [ WHERE 条件 ] ;
- 注意事项
  - DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数
    据。
  - DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。
  - 当进行删除全部数据操作时，datagrip会提示我们，询问是否确认删除，我们直接点击
    Execute即可。

DQL

基本语法

SELECT字段列表 FROM表名列表 WHERE条件列表 GROUP BY 分组字段列表 HAVING分组后条件列表 ORDER BY 排序字段列表 LIMIT分页参数

基础查询
- 别名查询：
  - SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] … FROM 表名;
  - SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] … FROM 表名;
- 去除重复记录 SELECT DISTINCT 字段列表 FROM 表名;
条件查询
- SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
  - IS NULL 是NULL
聚合函数
- 常见：count max min avg sum
- 语法：SELECT 聚合函数(字段列表) FROM 表名 ;
分组查询
- 语法：SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
- where与having区别
  - 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  - 判断条件不同：where不能对聚合函数进行判断，而having可以。
  - 注意事项：
    - 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
    - 执行顺序: where > 聚合函数 > having
    - 支持多字段分组, 具体语法为 : group by columnA,columnB
  - 例子：select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;
排序查询
- 语法： SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
- 排序方式 ASC : 升序，DESC: 降序
  - 如果是升序, 可以不指定排序方式ASC
  - 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;
分页查询
- SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
- 注意事项
  - 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数
  - 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
  - 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
执行顺序
- 图解
- DQL语句的执行顺序为： from … where … group by …
  having … select … order by … limit …

DCL

2.7.1 管理用户
- 查询用户:select * from mysql.user;
  - Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问
  - User代表的是访问该数据库的用户名
  - 在MySQL中需要通过Host和User来唯一标识一个用户
- 创建用户:CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
  - 创建用户heima, 可以在任意主机访问该数据库
  - create user ‘heima’@‘%’ identified by ‘123456’;
- 修改用户密码: ALTER USER ‘用户名’@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’ ;
  - alter user ‘heima’@‘%’ identified with mysql_native_password by ‘1234’;
- 删除用户: DROP USER ‘用户名’@‘主机名’ ;
  - drop user ‘itcast’@‘localhost’;
- 注意事项
  - 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户
  - 主机名可以使用 % 通配。
  - 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库管理员）使用
2.7.2 权限控制
- 列表
  - 官方文档
- 注意事项：
  - 多个权限之间，使用逗号分隔
  - 授权时，数据库名和表名可以使用 * 进行通配，代表所有。
- 查询 ‘heima’@‘%’ 用户的权限 show grants for ‘heima’@‘%’;
- 授予 ‘heima’@‘%’ 用户itcast数据库所有表的所有操作权限：grant all on itcast.* to ‘heima’@‘%’;
- 撤销 ‘heima’@‘%’ 用户的itcast数据库的所有权限：revoke all on itcast.* from ‘heima’@‘%’;

函数

字符串函数

函数列表
-
A. concat : 字符串拼接：select concat(‘Hello’ , ’ MySQL’);
B. lower : 全部转小写：select lower(‘Hello’);
C. upper : 全部转大写： select upper(‘Hello’);
D. lpad : 左填充： select lpad(‘01’, 5, ‘-’);
E. rpad : 右填充： select rpad(‘01’, 5, ‘-’);
F. trim : 去除空格：select trim(’ Hello MySQL ');
G. substring : 截取子字符串：select substring(‘Hello MySQL’,1,5);

数值函数

ceil：向上取整 select ceil(1.1);
floor：向下取整 select floor(1.9);
mod：取模 select mod(7,4);
rand：获取随机数 select rand();
round：四舍五入 select round(2.344,2);

日期函数

curdate：当前日期 select curdate();
curtime：当前时间 select curtime();
now：当前日期和时间 select now();

YEAR , MONTH , DAY：当前年、月、日

select YEAR(now()); 
select MONTH(now()); 
select DAY(now());

date_add：增加指定的时间间隔 select date_add(now(), INTERVAL 70 YEAR );
datediff：获取两个日期相差的天数: select datediff(‘2021-10-01’, ‘2021-12-01’);

流程控制

select if(false, ‘Ok’, ‘Error’);
select ifnull(‘Ok’,‘Default’);

case when then else end

select name, ( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址' from emp;

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据；保证数据库中数据的正确、有效性和完整性

check 约束

CREATE TABLE tb_user( id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识', 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 '性别' );

外键约束

添加外键的方法

创建表添加

CREATE TABLE 表名( 字段名 数据类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) );

alter 创建

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为
语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

1). CASCADE

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

2). SET NULL

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;

多表查询

多表关系

一对多
多对多
一对一

笛卡尔积

笛卡尔乘积是指在数学中，两个集合A集合和 B集合的所有组合情况
如何消除笛卡尔积：多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据
- select * from emp , dept where emp.dept_id = dept.id;
- 加上链接条件

内连接交集

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

外连接公共部分和左边为准或者右边为准

左外连接：左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接：右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

自连接

自连接查询:自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

SELECT 字段列表 FROM 表A ... UNION [ ALL ] SELECT 字段列表 FROM 表B ....;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重
union all查询出来的结果，仅仅进行简单的合并，并未去重
uninon 查询的结果做了去重处理
如果多条查询语句查询出来的结果，字段数量不一致，会报错

子查询

语法
- 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

分类1:根据子查询结果不同
- A. 标量子查询（子查询结果为单个值）
  - 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
  - 常用的操作符：= <> > >= < <=
- B. 列子查询(子查询结果为一列)
  - 子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
  - 常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
- C. 行子查询(子查询结果为一行):
  - select salary, managerid from emp where name = ‘张无忌’;
  - 子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
  - 常用的操作符：= 、<> 、IN 、NOT IN
- D. 表子查询(子查询结果为多行多列)
  - 子查询返回的结果是多行多列，这种子查询称为表子查询
  - 常用的操作符：IN
  - 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
    - select * from emp where (job,salary) in ( select job, salary from emp where name = ‘鹿杖客’ or name = ‘宋远桥’ );
  - 查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息
    - select e., d. from (select * from emp where entrydate > ‘2006-01-01’) e left join dept d on e.dept_id = d.id ;
分类2:根据子查询位置，分为
- A. WHERE之后
- B. FROM之后
- C. SELECT之后

事务

定义：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系
统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

控制事务一

查看/设置事务提交方式

SELECT @@autocommit ; 
SET @@autocommit = 0 ;

提交事务

COMMIT;

回滚事务

ROLLBACK;

控制事务二

START TRANSACTION 或 BEGIN ;
COMMIT;
ROLLBACK;

事务四大特性

ACID

并发事务问题

赃读：一个事务读到另外一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”

事务隔离级别

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Reference

https://www.bilibili.com/video/BV1Kr4y1i7ru/?p=195&spm_id_from=333.880.my_history.page.click