MySQL基础笔记

2023-12-13 22:07:39

1. SQL

1.1 SQL-DDL语句

在这里插入图片描述
数据定义语言

1.1.1 数据库操作

# 连接 mysql
mysql -u username -p

# 查看数据库
show dtatbases;

# 创建数据库
create database if not exists db_name;

# 选择数据库
use db_name;

# 查看当前所属数据库
select database();

# 删除数据库
drop databse if exists db_name;

1.1.2 表操作

# 查看当前数据库所有的表
show tables;

# 创建表
create table tb_name (字段 字段类型, 字段 字段类型);

# 查看当前表的字段
desc tb_name

# 查看此表的建表语句
show create table tb_name;

# 添加字段/修改字段类型/修改字段名称及类型/删除字段/修改表名
alter table tb_name add/modify/change/drop/rename to ...;

# 删除表
drop table tb_name;

1.2 MySQL-DML语句

  • 数据操作语言
# 添加数据
insert into tb_name (column1, column2,...) values (value1, value2,...); # 指定字段
insert into tb_name values (value1, value2,...); # 所有字段

# 修改数据
update tb_name set column1 = value1, column2 = value2, ... where condition;

# 删除参数
delete from tb_name where condition;

1.3 MySQL-DQL语句

– 数据查询语言
在这里插入图片描述

1.3.1 基本查询

# 查询多个字段
select 字段1,字段2,... frmo tb_name;
select * from tb_name;

# 设置别名
select 字段1 as zd1, 字段2 as zd2 from tb_name; # as可以省略

# 去除重复记录
select distinct 字段列表 from tb_name;

1.3.2 条件查询

  • 语法
select 字段列表 from tb_name where 条件列表;
  • 条件部分
    在这里插入图片描述在这里插入图片描述

1.3.3 聚合函数

将一列数据作为一个整体, 进行纵向计算

  • 语法
select 聚合函数(字段列表) from tb_name;
  • 函数

null值不参与聚合函数计算

1.3.4 分组查询

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

where与having的区别:

  1. 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组
    之后对结果进行过滤
  2. 判断条件不同:where不能对聚合函数进行判断,而having可以
# 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select address, count(*) add_num from emp 
where age < 45 group by address 
having add_num >= 3

注意事项:
? 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
? 执行顺序: where > 聚合函数 > having 。
? 支持多字段分组, 具体语法为 : group by columnA,columnB

1.3.5 排序查询

  • 语法
# 多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
select 字段列表 from 表名 order by 字段1 排序方式1 , 字段2 排序方式2 ;
  • 排序方式
    • asc 升序(默认值)
    • desc 降序

1.3.6 分页查询

  • 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

注意事项:
? 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数
? 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
? 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10

1.4 MySQL-DCL语句

数据控制语言: 管理数据库用户、控制数据库的访问权限

1.4.1 管理用户

# 查询用户
use mysql;
select *from user;

# 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

# 修改密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

# 删除用户
DROP USER '用户名'@'主机名';

注意事项:
? 在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
? 主机名可以使用 % 通配。
? 这类SQL开发人员操作的比较少,主要是DBA使用

1.4.2 权限控制

在这里插入图片描述

# 查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;

# 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

# 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

2. 函数

一段可以直接被另一段程序调用的程序和代码

2.1 字符串函数

在这里插入图片描述

  • 示例
# 由于业务需求变更,企业员工的工号,统一为5位数
# 目前不足5位数的全部在前面补0
update emp set workno = lpad(workno, 5, '0');

2.2 数值函数

在这里插入图片描述

2.3 日期函数

在这里插入图片描述

  • 示例
# 查询所有员工的入职天数,并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as 'entrydays' 
from emp 
order by entrydays desc;

2.4 流程函数

在这里插入图片描述

# case when then else end
# 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select
	name,
	(case workaddress when '北京' then '一线城市' 
	when '上海' then '一线城市' 
	else'二线城市' end) 
	as '工作地址'
from emp;

# 查询成绩返回优秀 及格 不及格
select
id,
name,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end )
'数学',
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'
end ) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'
end ) '语文'
from score;

3. 约束

3.1 概述

  1. 概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
  2. 目的:保证数据库中数据的正确、有效性和完整性。
  3. 分类:
    在这里插入图片描述

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

3.2 约束演示

在这里插入图片描述

# 对应的sql语句
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 '性别'
);

3.3 外键约束

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

如图,只存在逻辑外键,删除部门后,对应部门的员工还在。
在这里插入图片描述

  • 语法
# 1. 创建表时直接添加
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

# 2. 表结构创建好 额外增加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名); # 关联的主表


# 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
# 新增外键 尝试删除部门时 如有员工 则不能删除(默认行为 no action)
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);

# 删除外键
alter table emp drop foreign key fk_emp_dept_id

  • 删除/更新行为

在这里插入图片描述

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

4. 多表查询

4.1 多表关系

  • 一对多
    • 案例: 部门 与 员工的关系
    • 关系: 一个部门对应多个员工,一个员工对应一个部门
    • 实现: 在多的一方建立外键,指向一的一方的主键

  • 多对多
    • 案例: 学生 与 课程的关系
    • 关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
    • 实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

  • 一对一
    • 案例: 用户 与 用户详情的关系
    • 关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
    • 实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)

4.2 多表查询

  • 概述: 从多张表中查询数据
  • 笛卡尔积: 笛卡尔乘积是指在数学中,两个集合A集合 和 B集合的所有组合情况

多表查询就是在剔除无效的笛卡尔积

  • 分类:
    • 连接查询
      • 内连接:相当于查询A、B交集部分数据
      • 外连接
        • 左外连接:查询左表所有数据,以及两张表交集部分数据
        • 右外连接:查询右表所有数据,以及两张表交集部分数据
      • 自连接:当前表与自身的连接查询,自连接必须使用表别名
    • 子查询

4.3 内连接

查询A、B交集部分数据

内连接的语法分为两种: 隐式内连接、显式内连接

# 隐式内连接
SELECT 字段列表 FROM1,2 WHERE 条件 ... ;

# 显式内连接
SELECT 字段列表 FROM1 [ INNER ] JOIN2 ON 连接条件 ... ;
# 查询每一个员工的姓名 , 及关联的部门的名称 
# 隐式内连接实现
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
select e.name,d.name from emp e , dept d where e.dept_id = d.id; # 别名简化

# 显式内连接实现
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
select e.name, d.name from emp e join dept d on e.dept_id = d.id; # inner省略 别名简化

4.4 外连接

  • 左外连接
SELECT 字段列表 FROM1 LEFT [ OUTER ] JOIN2 ON 条件 ...;

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

  • 右外连接
SELECT 字段列表 FROM1 RIGHT [ OUTER ] JOIN2 ON 条件 ...;

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

注意事项:左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连接

4.5 自连接

4.5.1 自连接查询

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

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

注意事项:在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底是哪一张表的字段。

# 查询员工 及其 所属领导的名字(内连接)
select a.name , b.name from emp a , emp b where a.managerid = b.id;

# 查询所有员工emp及其领导的名字emp, 如果员工没有领导, 也需要查询出来(外连接)
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =
b.id;

4.5.2 联合查询

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

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
  • 对于联合查询返回的多张表的列数必须保持一致,字段类型也需要保持一致
  • union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重

4.6 子查询

4.6.1 概述

SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询

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

根据子查询结果不同, 分为:

  • 标量 子查询(子查询结果为单个值)
  • 列 子查询(子查询结果为一列)
  • 行 子查询(子查询结果为一行)
  • 表 子查询(子查询结果为多行多列)

根据子查询位置不同, 分为:

  • where之后
  • from之后
  • select之后

4.6.2 标量子查询

子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询

常用的操作符:= <> > >= < <=

# 查询 "销售部" 的所有员工信息
select id from dept where name = '销售部'; # 返回结果是单个值

select * from emp where dept_id = (select id from dept where name = '销售部');

4.6.3 列子查询

子查询返回的结果是一列(可以是多行),这种子查询称为列子查询

常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL

# 查询 "销售部" 和 "市场部" 的所有员工信息
select id from dept where name = '销售部' or name = '市场部';

select * from emp where dept_id in 
(select id from dept where name = '销售部' or name = '市场部');

4.6.4 行子查询

子查询返回的结果是一行(可以是多列),这种子查询称为行子查询

常用的操作符:= 、<> 、IN 、NOT IN

# 查询与 "张无忌" 的薪资及直属领导相同的员工信息
select salary, managerid from emp where name = '张无忌';

select * from emp where (salary, managerid) = (select salary, managerid from emp
where name = '张无忌');

4.6.5 表子查询

子查询返回的结果是多行多列,这种子查询称为表子查询

常用的操作符:IN

# 查询与 "鹿杖客", "宋远桥"的职位和薪资相同的员工信息
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

select * from emp where (job,salary) in 
( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

5. 事务

5.1 事务简介

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

注意: 默认MySQL的事务是自动提交的,也就是说,当执行完一条DML语句时,MySQL会立即隐式的提交事务

5.2 事务操作

5.2.1 控制事务一

# 查看/设置事务提交方式
SELECT @@autocommit;

SET @@autocommit = 0;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;

注意:上述的这种方式,我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交

5.2.2 控制事务二

# 开启事务
START TRANSACTIONBEGIN;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;

5.3 事务特性

是事务的四大特性,简称=ACID

  • 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
  • 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
  • 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。

5.4 并发事务问题

(1) 赃读:一个事务读到另外一个事务还没有提交的数据
在这里插入图片描述
比如B读取到了A未提交的数据

(2) 不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
在这里插入图片描述

(3) 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了 “幻影”

5.5 事务隔离级别

为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。主要有以下几种:读–未提交、读–提交、可重复读、串行化
在这里插入图片描述

# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

# 设置事务隔离级别
# 会话/全局级别
SET [ SESSION | GLOBAL ] 
TRANSACTION ISOLATION LEVEL 
{ 
READ UNCOMMITTED | 
READ COMMITTED | 
REPEATABLE READ |
SERIALIZABLE 
} # 四选一

注意:事务隔离级别越高,数据越安全,但是性能越低

文章来源:https://blog.csdn.net/weixin_44465396/article/details/134948029
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。