【JavaWeb-mySQL】学习笔记

原创 已于 2025-03-25 21:36:40 修改 · 988 阅读 · 21 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #sql #学习 #笔记 #数据库

于 2024-06-01 18:58:45 首次发布

目录

<<回到导览

MySQL

分类全称说明
DDLData Definition Language数据定义语言，用来定义数据库对象（数据库，表，字段）
DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行增删改
DQLData Query Language数据查询语言，用来查询数据库中表的记录
DCLData Control Language数据控制语言，儿用来创建数据库用户、控制数据库的访问权限

1.DDL

1.1.数据库操作
语句说明
show databases;查询所有数据库
select database()；查询当前数据库
use 数据库名；使用数据库
create database [if not exists ] 数据库名；创建数据库
drop database [ if exists ] 数据库名；删除数据库：
1.2.数据类型

  1. 数值类型

    类型大小(byte)描述
    tinyint1小整数值(0,255)
    smallint2大整数值
    mediumint3大整数值
    int4大整数值
    bigint8极大整数值
    float4单精度浮点数值
    double8双精度浮点数值
    decimal小数值（精度更高）

    float(5, 2)：5表示整个数字长度，2表示小数位个数

  2. 字符串类型

    类型大小（byte）描述
    （√）char0-255 bytes定长字符串
    （√）varchar0-65535 bytes变长字符串
    tinyblob0-255 bytes不超过255个字符的二进制数据
    tinytext0-255 bytes短文本字符串
    blob二进制形式的长文本数据
    text长文本数据
    mediumblob/mediumtext中等长度文本数据
    longblob/longtext极大文本数据

    char(10)：最多10个字符，不足10个字符，占用10个字符

    varchar(10)：最多10个字符，不足10个字符，按1实际长度储存

  3. 日期类型

    类型大小（byte）格式描述
    （√）date3YYYY-MM-DD日期值
    time3HH:MM:SS时间值或持续时间值
    year1YYYY年份值
    （√）datetime8YYYY-MM-DD HH:MM:SS混合日期和时间值
    timestamp4YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳
1.3.约束

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

约束描述关键字
非空约束限制该字段值不能为nullnot null
唯一约束保证字段的所有数据都是唯一、不重复的unique
主键约束主键是一行数据的唯一标识，要求非空且唯一primary key
默认约束保存数据时，如果未指定该字段值，则采用默认值default
外健约束让两张表的数据建立连接，保证数据的一致性和完整性foreign key
1.4.表结构操作

DDL的SQL语句只要能看懂就行了，主要掌握图形化界面的使用

  1. 创建表结构

       # 员工表
   create table tb_emp
   (
       id int unsigned auto_increment comment 'ID' primary key,
       username    varchar(20)                 not null comment '用户名',
       password    varchar(20) default '12345' not null comment '密码',
       gender      tinyint unsigned            not null comment '性别，男1，女2',
       image       varchar(100)                null comment '图像',
       job         tinyint unsigned            null comment '身份，1学生，2教师，3,管理人员',
       entrydate   date                        null comment '入职日期',
       create_time datetime                    not null comment '创建日期',
       update_time datetime                    not null comment '更新日期',
       constraint tb_emp_pk_2
           unique (username)
   )comment '员工表';

   # 部门表
   create table tb_dept(
       id int unsigned primary key auto_increment comment 'ID',
       name varchar(10) not null unique comment '部门名称',
       create_time datetime not null comment '创建时间',
       update_time datetime not null comment '修改时间'
   )comment '部门表';

    也可以用图形化工具创建表结构
    在这里插入图片描述

  2. 查询表结构

    语句说明
    show tables;查询当前数据库所有表
    desc 表名；查询表结构
    show create table 表名；查询建表语句

  3. 修改表结构

    语句说明
    alter table 表名 add 字段名 类型（长度） [comment 注释] 约束;添加字段
    alter table 表名 modify 字段名 新数据类型（长度）;修改字段类型
    alter table 表名 change 旧字段名 新字段名 类型 （长度） [comment 注释] 约束;修改字段名和字段类型
    alter table 表名 drop column 字段名;删除字段
    rename table 表名 to 新表名；修改表名
    --修改：为表 tb emp 添加字段 qg varchar (11)
alter table tb_emp add qg varchar(11) comment 'QQ';

--修改：修改 th_emp 字段类型 qg varchar(13)
alter table tb_emp modify qq varchar(13) comment 'QQ';

-- 修改：修改 tb_emp 字段名 qq 为qq_num varchar(13)
alter table tb_emp change qg qq_num varchar(13) comment 'QQ';

--修改：删除 tb_emp 的 qq_num 字段
alter table tb_emp drop column qq_num;

-- 修改：将tb_emp 表名修改为 emp 
rename table tb_emp to emp;

  4. 删除表结构

    语句说明
    删除表：drop table [if exists] 表名删除表

2.DML

DML的SQL语句运用较多，需要熟练掌握、

  1. 添加数据

    语句说明
    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.为tb_emp 表的 username, name, gender 字段插入值
insert into tb_emp (username, name, gender, create_time, update_time) values ('wuji', '张无忌'，1, now(), now());

-- 2.为tb_emp 表的 所有字段插入值
insert into tb_emp values (null, 'zhiruo', '123','周芷若', 2, '1.jpg', 1, '2010-01-01', now(), now());

-- 3.批量为为 tb_emp 表的 username , name, gender 字段插入数据
insert into tb_emp (username, name, gender, create_time, update_time) values('weifuwang','韦一笑',1, now(),now()),('xieshiwang','谢逊'，1, now(), now())；

  2. 修改数据

    语句说明
    update 表名 set 字段名1 = 值1, 字段名2 = 值2, … [where条件];修改数据
    -- 1. 将tb_emp 表的ID为1员工姓名name字段更新为 '张三', 并更新修改时间
update tb_emp set name = '张三', update_time = now() where id = 1;

-- 2.将tb_emp 表的所有员工的入职日期更新为'2010-01-01', 并更新修改时间
update tb_emp set entrydate = '2010-01-01', update_time = now();

  3. 删除数据

    语句说明
    delete from 表名 [where 条件];删除数据
    -- 1.删除 tb_emp 表中 ID为1的员工
delete from tb_emp where id = 1;

-- 2. 删除 tb_emp 表中的所有员工
delete from tb_emp;

    delete语句不能删除某一个字段的值（可以赢update设置为null）

3. DQL

select

  • 字段列表

from

  • 表名列表

where

  • 条件列表

group by

  • 分组字段列表

having

  • 分组后条件列表

order by

  • 排序字段列表

limit

  • 分页参数
3.1.基本查询
语句说明
select 字段1, 字段2, 字段3 from 表名;查询多个字段
select * from 表名;查询所有字段（通配符）
select 字段1 [as 别名1], 字段2 [as 别名2] from 表名;设置别名
select distinct 字段列表 from 表名;去除重复记录
-- 1.查询所有员工的 name, entrydate，并起别名（姓尔、入职日期）
select name as 姓名,entrydate as 入职日期 from tb_emp;

-- 2.查询已有的员工关联了哪几种职位（不要重复）
select distinct job from tb_emp;

*号不直观、效率较低，在实际开发中尽量少用

3.2.条件查询
语句说明
select 字段列表 from 表名where 条件列表;条件查询
比较运算符说明
<> 或 !=不等于
between … and …在某个范围内
in(…)在in之后的列表中的值，多选一
like 占位符模糊匹配（_匹配单个字符，%匹配多个字符）
is null是null
--1.直询 入职时问 在 2000-01-01’（包含） 到'2010-01-01'（包含） 之问 且 性别为女 的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender = 2;

-- 2.查询 职位是 2（讲师），3（学工主管），4（教研主管） 的员工信息
select * from tb_emp where job = 2 or job = 3 or job = 4;
select * from tb_emp where job in (2, 3, 4);

-- 3.查询 姓名 为两个字的员工信息
select * from tb_emp where name like '__'

-- 4.查询 姓‘张’的员工信息
select * from tb_emp where name like '张%'
3.3.分组查询

聚合函数：将一列数据作为一个整体，进行纵向计算

聚合函数说明
count统计数量
max / min最值
avg平均值
sum求和

聚合函数不对null进行运算

-- 1.统计该企业最迟入职的员工
select max(entrydate) from tb_emp;
-- 2.统计该企业员工ID 的平均值
select avg(id) from tb_emp;
-- 3.统计该企业员工的 ID 之和
select sum(id) from tb_emp;

分组查询语法：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后的过滤条件]

-- 1.根据性别分组，统计男性和女性员工的数量
-- 返回的是分组字段 + 聚合函数
select gender, count(*) from tb_emp group by gender;

-- 2.先在询入作时间在‘2015-01-01’（包含） 以前的员工，非对结果根据职位外组，获水员工数最大于等于2的职位
select job, count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;

执行顺序：where > 聚合函数 > having

having是对分组之后的聚合函数进行过滤

####排序查询

select 字段列表 from 表名 where 条件列表 [ group by 分组字段] order by 字段1 排序1, 字段2 排序2…;

排序方式说明
ASC升序（默认）
DESC降序
-- 1.根据入职时问，对员工进行降序排序 - desc.
select * from tb_emp order by entrydate desc;

-- 2.根据 入职时问 对公司的员工进行 升序排序，入职时间相同，再按照 更新时问 进行降序排序
select * from tb_emp order by entrydate, update_time desc;
3.4.分页查询

分页查询：select 字段列表 from 表名 limit 起始索引, 查询记录数；

-- 1.查询 第3页 员工数据，每页展示5条记录
select * from tb_emp limit 10, 5;
3.5.综合练习

  1. 练习1

    1.根据输入的员工姓名、员工性别、入职时间 搜索满足条件的员工信息。
    2.其中员工姓名，支持模期匹配；性别进行精确查询；入职时间进行范围查询。
    3.支持分页查询。
    4.并对查询的结果，根据最后修改时间进行倒序排序。

    select * from tb_emp 
    where username like '张%'
    and gender = 1
    and entrydate between '2000-01-01' and '2025-01-01'
    order by update_date desc
    limit 0,5 ;

  2. 练习2

    员工性别统计

    员工职位统计

    -- 员工性别统计
select if(gender=1, '男性员工', '女性员工') 性别, count(*) from tb_emp group by gender;

-- 员工职位统计
select (case job 
    when 1 then '学生' 
    when 2 then '教师' 
    when 3 then '管理人员' 
    else '未知' end
    ) 职位,
    count(*) from tb_emp group by job ;

4.多表设计

4.1.一对多

示例：部门和员工的关系

要想使两张表关联，而且保证数据的完整性和一致性（如果部门表的部门删除，员工表中所对应的该部门的员工也被删除），就需要用到外键（物理外键）

在这里插入图片描述

实现：

# 员工表
create table tb_emp
(
    -- ...
    dept_id     int unsigned                null comment '部门ID',
    -- 外键
    constraint tb_emp_fk_dpt_id
        foreign key (dept_id) references tb_dept (id)
)comment '员工表';

# 部门表
create table tb_dept(
    id int unsigned primary key auto_increment comment 'ID',
    ...
)comment '部门表';

物理外键：这种用foreign key定义表外键关联的叫物理外键，在实际开发中最后不用，缺点：

  1. 需要检查外键关系，影响增删改的效率
  2. 仅用于单节点数据库，不适用与分布式、集群场景
  3. 容易引发数据库死锁问题，消耗性能

逻辑外键：在业务逻辑层面解决外键关联

4.2.一对一

示例：人和身份证

使用场景：多用于单标拆分，将一张表的基础字段（查询频率高的字段）放在一张表中，其他字段放在另一张表中，提高操作效率

实现：任意一方添加外键，关联另一方主键，而且设置外键为唯一

在这里插入图片描述

4.3.多对多

示例：学生和课程

实现：建立第三张中间表，中间表至少包含两个外键，分别管了两张表的主键

在这里插入图片描述

4.4.综合案例

在这里插入图片描述

5.多表查询

多表查询主要解决了物理外键的弊端，实现逻辑外键

运行下面代码，tb_emp表3条数据，tb_dept表2条数据

查询结果是tb_emp表和tb_dept表的笛卡尔积，一共6条数据

select * from tb_emp,tb_dept;

筛选无用信息，得到3条信息

select * from tb_emp,tb_dept where tb_dept.id = tb_emp.dept_id;
5.1.连接查询
5.1.1.内连接

  • 隐式内连接

  • 显式内连接

    -- 内连接
# 1.查询员工姓名，及所属部门名称（隐式内连接）
select tb_emp.username, tb_dept.name from tb_emp,tb_dept where tb_dept.id = tb_emp.dept_id;
# 通过给表起别名来简化sql语句
select e.username, d.name from tb_emp e,tb_dept d where d.id = e.dept_id;

# 2.查询员工姓名，及所属部门名称（显式内连接）
select e.username, d.name from tb_emp e inner join tb_dept d on d.id = e.dept_id;

    在这里插入图片描述

5.1.2.外连接

  • 左外连接：查询左表所有数据(A)

    -- 左外连接
# 查询员工表所有员工姓名，及所属部门名称
select e.username,d.name  from tb_emp e left outer join tb_dept d on e.dept_id = d.id;

    在这里插入图片描述

  • 右外连接：查询右表所有数据(B)

    -- 右外连接
# 查询部门表所有部门姓名，及所属部门名称
select e.username,d.name  from tb_emp e right join tb_dept d on e.dept_id = d.id;

    在这里插入图片描述

5.2.子查询

子查询又称为嵌套查询

5.2.1.标量子查询

标量子查询：返回的结果为单个值

-- 要求：查询部门名称为学生的员工信息
-- 步骤拆解：
-- A.查询部门表中部门名称为学生的部门id
select id from tb_dept where name = '学生';	-- 返回的是1，为标量子查询
-- B.查询员工表中部门id为1的员工
select * from tb_emp where dept_id = 1;

-- A语句的返回值为1，那么可以赢A语句替换1，这样就可以合并A、B语句
select * from tb_emp where dept_id = (select id from tb_dept where name = '学生');

-- 要求：查询Marry早入职的员工信息
-- 步骤拆解：
-- A.查询Mary入职时间
select entrydate from tb_emp where username = 'Marry'; -- 返回的是2025-03-24，为标量子查询
-- B.查询比Marry早入职的员工信息
select * from tb_emp where entrydate < '2025-03-24';

-- 合并A、B语句
select * from tb_emp where entrydate < (select entrydate from tb_emp where username = 'Marry');
5.2.2.列子查询

列子查询：返回的结果为一列

-- 要求：查询部门为"学生"和"老师"的所有员工信息
-- 步骤拆解：
-- A:查询部门为"学生"和"老师"的部门id
select id from tb_dept where name = '学生' or name = '教师';	-- 返回1和2，为列子查询
-- B:查询部门id为1和2的所有员工信息
select * from tb_emp where dept_id in (1,2);

-- 合并A、B语句
select * from tb_emp where dept_id in (select id from tb_dept where name = '学生' or name = '教师');
5.2.3.行子查询

表子查询：返回的结果为一行（但可以是多列）

-- 查询和Tom入职日期及职位都相同的员工信息
-- A:查询Tom入职日期
select entrydate from tb_emp where username = 'Tom';	-- 返回1和2
-- B:查询Tom职位id
select dept_id from tb_emp where username = 'Tom';		-- 返回2025-03-23，用行查询
-- C:查询和Tom入职日期及职位都相同的员工信息
select * from tb_emp where entrydate = '2025-03-23' and dept_id = 1;

-- 合并A、B、C语句
select * from tb_emp where entrydate = (select entrydate from tb_emp where username = 'Tom') and dept_id = (select dept_id from tb_emp where username = 'Tom');

-- 简化写法
select * from tb_emp where (entrydate, dept_id) = (select entrydate, dept_id from tb_emp where username = 'Tom');
5.2.4.表子查询

表子查询：返回的结果为多行多列，常作为临时表

-- 查询入职时期是'2025-03-24的员工信息及其部门名称
-- A.查询入职时期是'2025-03-24的员工信息
select * from tb_emp where entrydate = '2025-03-24';	-- 返回的是表，用表子查询

select e.*, d.name from (select * from tb_emp where entrydate > '2025-03-23')e, tb_dept d where e.dept_id = d.id;
5.2.5.综合训练

以多表设计章节的分类表、套餐表、菜品表为例进行以下操作

在这里插入图片描述

练习1：查询不超过10元的菜品的名称、价格、及分类名称

select d.name, d.price, c.name 
    from dish d, category c 
    where d.category_id = c.id 
    and d.price <= 10;

练习2：查询价格为10~15，且状态为起售的菜品，展示菜品名称、价格、所属分类名称（即使菜品没有分类，也有将菜品查询处理）

select d.name, d.price, c.name
    from dish d
    right join  category c
    on d.category_id = c.id
    where d.price between 10 and 16
    and d.status = 1;

练习3：查询每个分类下最贵的菜品，展示分类名称、最贵的菜品的价格

select c.name, max(d.price) 
    from dish d,category c 
    where d.category_id = c.id 
    group by c.name;

练习4：查询各个分类下菜品状态为’起售’，且该分类下菜品数量大于等于1的分类名称

select c.name, count(*) 
	from dish d,category c
    where d.category_id = c.id
    and d.status = 1
    group by d.category_id
    having count(*) > 1;

练习5：查询出套餐’请三丝’中包含哪些菜品（展示套餐名称，套餐价格菜品名称，菜品价格，套餐份数）

select s.name, s.price, d.name, d.price, sd.copies
    from setmeal s, setmeul_dish sd, dish d
    where  s.setmeal_id = sd.setmeal_id
    and  sd.dish_id = d.id
    and s.name = '请三丝';

练习6：查询出低于平均价格的菜品信息（展示菜品名称，菜品价格）

select name, price 
	from dish 
	where price < (select avg(price) from dish);
6.事务

场景：删除一个部门，并删除该部门下的所有员工

-- 删除一个部门
delete from tb_dept where id = 1;

-- 并删除该部门下的所有员工
delete from tb_emp where dept_id = 1;

如果删除部门成功，而删除部门下的员工不成功，则会引发数据不一致

事务：一组操作的集合，事务会把所有的操作作为一个整体，要么同时成功，要么同时失败。

事物的四大特性：原子性，一致性，隔离性，持久性

  • 隔离性：保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性：一旦提交和回滚，对数据库的数据的改变就是永久的

默认MySQL的事物是自动提交的，及执行一个语句，提交一次

语句说明
start transaction; / begin开启事务
commit提交事务
rollback回滚事务

改写上面代码

-- 开启事务
start transaction ;

-- 删除一个部门
delete from tb_dept where id = 1;

-- 并删除该部门下的所有员工
delete from tb_emp where dept_id = 1;

-- 提交事务
commit ;

-- 回滚事务
rollback ;
7.索引

索引：帮助数据库高效获取数据的数据结构（空间换时间，默认B+Tree）

优点：

  • 提供查询效率，降低IO成本
  • 通过索引列对数据进行排序，降低数据排序成本

缺点：

  • 占用较大空间（但内存不值钱了）
  • 降低了insert、update、delete效率（这些操作使用较少）

主键字段在建表时会自动添加主键索引

有唯一约束时，会自动添加唯一索引

语句说明
create [unique] index 索引名 on 表名 (字段名)创建索引
show index from 表名;查看索引
drop index 索引名 on 表名删除索引
-- 创建索引
create index idx_emp_name on tb_emp(username);

-- 查看索引
show index from tb_emp;

-- 删除索引
drop index idx_emp_name on tb_emp;
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值