Mysql

hgh2022

已于 2022-04-27 10:17:55 修改

阅读量123

点赞数

分类专栏：笔记文章标签： mysql 数据库 database

于 2021-12-21 20:12:34 首次发布

本文链接：https://blog.csdn.net/weixin_49354234/article/details/121971240

版权

笔记专栏收录该内容

9 篇文章 1 订阅

订阅专栏

文章目录

前言
开发技巧
SQL优化
相关问题

前言

基于菜鸟教程，复习mysql基本操作命令。
通过MySQL开发技巧，学习开发中mysql的使用技巧。
基于开源项目JavaGuide，学习mysql相关问题

5. 参考 # 基本操作 ## 创建数据库 ![创建数据库](https://img-blog.csdnimg.cn/8fd71c13529d4e52927504c2c96f65ff.png) ## 删除数据库

选择数据库

创建表

在这里插入图片描述

删除表

插入数据

创建表的时候没有设置主键自增，这里通过alter table 修改主键为自增主键

在这里插入图片描述

查询数据

在这里插入图片描述

WHERE子句

在这里插入图片描述

LIKE子句

先添加几条干扰数据

在这里插入图片描述
查询模糊查找username含有tutu的记录

修改数据

在这里插入图片描述

删除数据

删除所有username中有huhu的记录

在这里插入图片描述

UNION操作符

UNION用于联合两张表的数据，因此创建一个工资表SALARY

在这里插入图片描述

少添加一个用户字段，通过alter table命令add一个新的字段

在这里插入图片描述

获取两表的所有id，union会去重，union all显示所有

在这里插入图片描述

CASE

简单CASE

CASE sex 
         WHEN '1' THEN '男' 
         WHEN '2' THEN '女' 
ELSE '其他' END

搜索CASE

--Case搜索函数 
CASE WHEN sex = '1' THEN '男' 
         WHEN sex = '2' THEN '女' 
ELSE '其他' END

排序

在这里插入图片描述

分组

新建一个登陆记录表，忽略插入数据的sql

在这里插入图片描述

连接

内连接：取公共的记录

在这里插入图片描述

补充新数据：分别在USER和SALARY添加对方没有对方对应的记录。

在这里插入图片描述

left join 显示左表所有数据

在这里插入图片描述

right join 显示右表所有数据

在这里插入图片描述

NULL值处理

将上图中的NULL显示为NO DATA

在这里插入图片描述

显示字段为NULL或不为NULL的记录

在这里插入图片描述

修改表

修改SALARY表中的salary_m为salary_mon

在这里插入图片描述

修改SALARY表中的salary_mon小数点后保留三位

在这里插入图片描述

事务

在这里插入图片描述

取消事务

在这里插入图片描述

开发技巧

连接

Inner Join 使用场景

查找有共同部分的记录

Left Join使用场景

1. 查找左表的所有记录
2. 不包含右边表的所有记录

Right Join使用场景

1. 查找右表的所有记录
2. 不包含左边表的所有记录

在MySql中实现Full Join，以及Full Join使用场景

实现：使用左右连接，通过union all联合
场景：
	1. 查询左右表的所有数据
	2. 查询左右表不包括公共部分的数据

什么是Cross Join？使用场景？

笛卡尔乘积
使用场景：某些情况下可以用来简化数据的查询。

如何更新where中包括自身的表

mysql：在from中的表的记录不能被修改

在这里插入图片描述

使用join优化子查询

使用join查询比使用子查询速度更快

使用join优化聚合查询

通过join将表联合在做查询记录
计划查询LOGINLOG中登录次数最多的那天的记录，所以不用使用的join

在这里插入图片描述

通过join优化子查询

在这里插入图片描述

行转列查询

判断是行列转行还是列行转换，看的是属性和属性值是行还是列

行转列

username signin  转为：username
										signin

使用cross join进行交叉连接

每一个子select 只有一个结果，所以交叉连接已有一个结果：

select *
from
(select sum(l1.signin) as "huhu" from USER u1, LOGINLOG l1 where u1.id = l1.id and u1.username = "huhu") a
cross join
(select sum(l2.signin) as "huihui" from USER u2, LOGINLOG l2 where u2.id = l2.id and u2.username = "huhui") b 
cross join
(select sum(l3.signin) as "tutu111" from USER u3, LOGINLOG l3 where u3.id = l3.id and u3.username = "tutu111") c 
cross join
(select sum(l4.signin) as "tutu999" from USER u4, LOGINLOG l4 where u4.id = l4.id and u4.username = "tutu999") d;

使用CASE、WHEN、THEN、ELSE、END

select sum(case when username="huhu" then signin end) as "tutu",
sum(case when username = "huihui" then signin end) as "huihui", 
sum(case when username = "tutu999" then signin end) as "tutu999",
sum(case  when username = "tutu111" then signin end) as "tutu111" 
from USER, LOGINLOG where USER.id = LOGINLOG.id;

场景：单个列的单个单元数据为：1111,2222,3333

使用序列化表

SUBSTRING(str, index)：从str的index字符开始截取
SUBSTRING_INDEX(str, "x", n)：截取从开始到底第n个x的位置，不包括第n个x

select username, REPLACE(SUBSTRING(SUBSTRING_INDEX(phone,",",a.id),CHAR_LENGTH(SUBSTRING_INDEX(phone,",",a.id - 1))+1),",","") as phone 
from tb_squence a cross join (select username, CONCAT(phone,",") as phone, LENGTH(phone) - LENGTH(REPLACE(phone,",",""))+1 size from USER) b on a.id <= b.size;

列转行

创建新表：

create table USER_EQUIPMENT (username varchar(20), corselet varchar(20), shoe varchar(20), trousers varchar(20));

使用union

场景：多列转为多行
使用union all，每个列转为行之后union all

select username,'corselet' as corselet, corselet from USER_EQUIPMENT
union all
select username,'shoe' as shoe, shoe from USER_EQUIPMENT
union all
select username,'trousers' as trousers, trousers from USER_EQUIPMENT

使用序列化表

coalesce：选取不为空的值为结果

SELECT
	username,
	case 
		when id = 1 then "corselet"
		when id = 2 then "shoe"
		when id = 3 then "trousers"
		end as type,
	coalesce(
	case when id = 1 then corselet end,
	case when id = 2 then shoe end,
	case when id = 3 then trousers end
	)
FROM
	USER_EQUIPMENT
CROSS JOIN tb_squence
WHERE
	id <= 3;