什么是扎实的基本功？MySQL 基础知识看看你了解多少

2024-06-23 11:20
文章标签 mysql 了解 基础知识 扎实 database 基本功 看看

本文主要是介绍什么是扎实的基本功？MySQL 基础知识看看你了解多少，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

本文首发于公众平台：腐烂的橘子

当前很多同学沉迷于“碎片化学习”，问题在于获取到的都是零碎的知识，没有体系化的知识框架，这对于练就扎实的基本功是极其不利的。

怎么办？这时要懂得中庸之道“慢即是快”的道理，系统学一遍，查漏补缺，不要觉得有些你知道就学不下去了，要耐得住性子，系统学习。

下面就来检验下这些知识点你是否都掌握了。

关于 join 的那些事

Inner join

冷知识：join = inner join

select order_id, orders.customer_id
from order 
join customers
on orders.customer_id = customers.customer_id

多库连接

可以通过 join 关联其他库表，这是基本用法。

use sql_inventory;select *
from sql_store.order_items oi
join sql_inventory.products p
on oi.product_id = p.product_id

自连接

join 除了可以关联其他表，也可以关联自己。

use sql_hr;select a.account_id, a.account_name,b.account_id, b.account_name
from t_account_object a
join t_account_object bon a.parent_parent_id = b.account_id

连接超过两张表

不多解释，不断使用 join 就可以了。

use sql_invoicing;select *
from payments p
join clients c
on p.client_id = c.client_id
join payment_methods pm
on p.payment_

outer join

这几个等价的关系你了解吗？

  • inner join 也可写作 join
  • left outer join 也可写作 left join
  • right outer join 也可写作 right outer join

using 怎么用

如果关联的字段名相同，using 可以代替 on。

selecto.order_id,c.first_name
from orders o 
join customers cusing (customer_id)
join shippers shusing (shipper_id)

等价于：

selecto.order_id,c.first_name
from orders o 
join customers con c.customer_id = o.customer_id
join shippers shon sh.shipper_id = o.shipper_id

如果需要多个字段，用逗号分隔:

select *
from order_items oi
join order_item_notes oinusing (order_id, product_id)

自然连接

引擎自己选择连接的列，不推荐使用，因为没有显示指明是怎么关联的。

select *
from order o
natual join customers c

交叉连接

返回两张表所有数据行的笛卡尔积，这个一般也不推荐，因为笛卡尔积是指数级别的。

select *
from customers c
cross join products p
order by c.first_name

等价于

select *
from customers c, orders o
order by c.first_name

union

合并两段查询结果。注意两段的列数要相同，下面写法会报错：

select first_name, last_name
from customers
union
select name
from shippers

因为第一段返回一列，第二段返回两列，以下是正确的：

select first_name
from customers
union
select name
from shippers

这里的列名是 first_name，不是 name，以第一段查询的列名为准

varchar 和 char 的区别

  • varchar(50),保存 5 个字符，则没有其余空间
  • char(50),保存 5 个字符，还有 45 个字符空闲

表的创建、更新、删除、查询

insert 一张表

如果插入其中几个字段，需要写字段，否则可以不写：

insert into orders(customer_id, order_date, status) 
values (1, '2019-01-02', 1);

insert into order_items
values(LAST_INSERT_ID(), 1, 1, 2.95),(LAST_INSERT_ID(), 1, 2, 3.95)

第一个 SQL 加了字段名，第二个没有。

创建表复制

复制整张表：

create table orders_archived as
select * from orders

其中 select * from orders 称为子查询，可以替换成其他复杂的查询语句。

复制其中某几行，还是用 insert into：

insert into orders_archived
select *
from orders
where order_date < '2024-01-01'

更新单行

可以设置单行为 null

update invoices
set payment_total = invoice_total * 0.5, payment_date = null
where invoice_id = 1

更新多行

设置 where 选中多行即可。

update 使用子查询

update invoices
set
payment_total = invoice_total * 1.2,
payment_date = due_date
where client_id in (select client_id from clientswhere state in ('CA', 'NY'))

你一定要知道的聚合函数

数值聚合函数

  • MAX()：求最大值
  • MIN()：求最小值
  • AVG()：求平均值
  • SUM()：求和
  • COUNT()：求数量

having 和 where 区别

  • where 在分组前筛选
  • having 在分组后筛选

with rollup

select state,city,sum(invoice_total) as total_sales
from invoices i
join clients c using (client_id)
group by state, city with rollup

生成总计，不能再用 order by。

ALL 关键字

select *
from invoices
where invoice_total > ALL (select invoice_totalfrom invoiceswhere client_id = 3
)

all 和 max 可以替换，比如可以写成这样：

select *
from invoices
where invoice_total > (select max(invoice_total)from invoiceswhere client_id = 3
)

ANY 关键字

先看一段 SQL：

select * 
from clients
where cliend_id in (select client_idfrom invoicesgroup by client_idhavind count(*) >= 2
)

用 any 可以这样写：

select * 
from clients
where cliend_id = any (select client_idfrom invoicesgroup by client_idhavind count(*) >= 2
)

所以 in 等价于 = any

AVG

select *
from invoices
where invoice_total > (select AVG(invoice_total)from invoiceswhere client_id = i.client_id
)

exists

select *
from clients c
where exists (select client_idfrom invoiceswhere client_id = c.client_id
)

select 子句中的子查询

selectinvoice_id, invoice_total,(select avg(invoice_total)from invoice) as invoice_average,invoice_total - (select invoice_average) as difference
from invoices

数值函数

  • round(5.7345, 2)，四舍五入 5.73
  • truncate(5.7345, 2), 截断,5.73
  • ceiling(5.7), 6
  • floor(5.2), 5
  • abs(-5.2), 5.2
  • rand(), 0-1 之间的随机值，如 0.6633075453930605

字符串函数

  • length('sky'),3,字符串长度
  • upper('sky'), SKY
  • lower('SKy'), sky
  • ltrim(' sky '),sky
  • rtrim('sky '), sky
  • trim(' sky '), sky
  • left('Kindergarten', 4), Kind
  • right('Kinderarara', 6), rarara
  • substring('Kindergarten', 3, 5), nderg
  • locate('n', 'Kindergarten'), 3
  • locate('garten', 'Kindergarten'), 7
  • replace('Kindergarten', 'garten', 'garden'), Kindergarden
  • concat('first', 'last'), firstlast

日期函数

  • now(), 2024-06-23 00:34:04
  • curdate(), 2024-06-23
  • curtime(), 00:34:30
  • year(now()), 2024
  • month(now()), 6
  • day(now()), 23
  • hour(now()), 0
  • minute(now()), 35
  • second(now()), 32
  • dayname(now()), Sunday，字符串格式的星期数
  • monthname(now()), June, 字符串格式的月份
  • extract(year from now()), 2024，标准SQL，推荐使用
  • date_format(now(), ‘%y’), 24，两位数年份
  • date_format(now(), ‘%Y’), 2024，四位数年份
  • date_format(now(), ‘%m’), 06，两位数月份
  • date_format(now(), ‘%M’), June，月份名称
  • time_format(now(), ‘%H:%i %p’), 00:36 AM

计算日期和时间

  • date_add(now(), interval 1 day)，返回明天的同一时间
  • date_add(now(), interval 1 year)，返回明年的同一时间
  • date_sub(now(), interval -1 year)，返回明年的同一时间
  • datediff(‘2024-06-02 09:00’, ‘2024-06-07 17:00’), 5
  • datediff(‘2024-06-07 17:00’, ‘2024-06-02 09:00’), -5
  • time_to_sec(‘09:00’), 32400, 表示从零点流逝的秒数
  • time_to_sec(‘09:00’) - time_to_sec(‘09:02’), -120

ifnull 和 coalesce

  • ifnull(shipper_id, 'Not assigned')：如果 shipper_id 为空，返回 Not assigned。即返回非空值
  • coalesce(shipper_id, comments, 'Not assigned')：如果 shipper_id 为空，返回 comments，如果 comments，如果 为空，返回 Not assigned。即返回第一个非空值

if

语法：if(expression, first, second)

select 
product_id, name, 
count(*) as orders,
if (count(*) > 1, 'Many times', 'Once')
from products
join order_items using (product_id)

case

select 
order_id,
casewhen year(order_date) = year(now()) then 'Active' when year(order_date) = year(now()) - 1 then 'Last Year' when year(order_date) = year(now()) - 1 then 'Archived' 
else 'Future'
end as category

MySQL 视图

创建视图

假设我们要多次使用一个查询结果：

selectc.client_id, c.name, sum(invoice_total) as total_sales
from clients c
join invoices i using (client_id)
group by client_id, name

可以创建一个视图：

create view sales_by_client as 
selectc.client_id, c.name, sum(invoice_total) as total_sales
from clients c
join invoices i using (client_id)
group by client_id, name

更改或删除视图

如果想修改视图，有两种办法：

  • 先删除视图，再创建视图
  • 更改视图（更方便）

先删除视图:

drop view sales_by_client

再创建视图:

create view sales_by_client as 
selectc.client_id, c.name, sum(invoice_total) as total_sales
from clients c
join invoices i using (client_id)
group by client_id, name

或直接使用创建或替换视图，更推荐：

create or replace view sales_by_client as
selectc.client_id, c.name, sum(invoice_total) as total_sales
from clients c
join invoices i using (client_id)
group by client_id, name

可以把这段sql保存在.sql文件中，这样可以随时创建视图了。

with option check 子句

如果你先创建了视图，但你可能会使用 update 更新视图里的数据，但是视图展示的还是老数据，会造成数据不一致。为了解决这个问题，你可以在视图里添加 with option check 子句，当 update 会更新掉视图的数据时报错，从而解决数据不一致的问题。

create or replace view sales_by_client as
selectc.client_id, c.name, sum(invoice_total) as total_sales
from clients c
join invoices i using (client_id)
group by client_id, name
with check option

视图的优点是提供了一种抽象，减少了表结构变化带来的影响。

这篇关于什么是扎实的基本功？MySQL 基础知识看看你了解多少的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1087017。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
关于数据埋点，你需要了解这些基本知识

关于数据埋点，你需要了解这些基本知识

产品汪每天都在和数据打交道，你知道数据来自哪里吗？ 移动app端内的用户行为数据大多来自埋点，了解一些埋点知识，能和数据分析师、技术侃大山，参与到前期的数据采集，更重要是让最终的埋点数据能为我所用，否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式，可以区分为： 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理：自动程度高的，能解决通用统计，便于统一化管理，但个性化定
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
linux-基础知识3

linux-基础知识3

打包和压缩 zip 安装zip软件包 yum -y install zip unzip 压缩打包命令: zip -q -r -d -u 压缩包文件名 目录和文件名列表 -q:不显示命令执行过程-r:递归处理，打包各级子目录和文件-u:把文件增加/替换到压缩包中-d:从压缩包中删除指定的文件 解压:unzip 压缩包名 打包文件 把压缩包从服务器下载到本地 把压缩包上传到服务器(zip
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
计组基础知识

计组基础知识

操作系统的特征 并发共享虚拟异步 操作系统的功能 1、资源分配，资源回收硬件资源 CPU、内存、硬盘、I/O设备。2、为应⽤程序提供服务操作系统将硬件资源的操作封装起来，提供相对统⼀的接⼝（系统调⽤）供开发者调⽤。3、管理应⽤程序即控制进程的⽣命周期：进程开始时的环境配置和资源分配、进程结束后的资源回收、进程调度等。4、操作系统内核的功能（1）进程调度能⼒： 管理进程、线
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2