数据库MySQL

最新推荐文章于 2024-08-15 11:17:44 发布
最新推荐文章于 2024-08-15 11:17:44 发布
阅读量390 收藏 1
点赞数 2
分类专栏： 笔记 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fenghenzr/article/details/104877506
版权

数据库MySQL

MySQL基础

整体架构

在这里插入图片描述

存储引擎

基础

  • 查看存储引擎语句：show variables like ‘%storage_engine’;
  • 目前Mysql默认InnoDB
  • 存储引擎是针对表的

MyISAM 与 InnoDB

  • 常见对比
    在这里插入图片描述
  • 表文件结构
    在这里插入图片描述

InnoDB

  • 聚集：表数据文件本身就是按照B+Tree组织的一个索引结构文件。
  • 聚集索引叶子节点包含了完整的数据记录。
  • 必须有主键：需要将主键作为聚集索引，组织起表数据，如果未设置主键，选择第一个不包含有NULL值的列作为主键索引，如果没有，InnoDB会创建隐藏列，维护内置6字节长的ROWID作为隐含的聚集索引。
  • 推荐自增主键：那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。不会插入因为插入到之前的索引节点之间而导致索引结构变化较多。
  • 非主键索引结构叶子节点存储的是主键值：一致性、节省空间。

索引

概述

  • 索引本质是帮助MYSQL高效获取数据的数据结构：排好序的快速查找数据结构。
  • 索引影响查找排序

优势

  • 提高数据检索效率，降低数据库IO成本。
  • 通过索引对数据进行排序，降低数据排序成本，降低CPU消耗。

劣势

  • 索引的保存占用空间。
  • 对表进行INSERT、UPDATE、DELETE等更新表的操作，不仅保存数据，还需要更新索引文件，如果设置不当会频繁更新索引结构，增加消耗。

分类

  • 单值索引：一个索引只包含单个列，一个表可以包含多个单值索引。
  • 唯一索引：索引列的值必须唯一，但允许有空值。
  • 复合索引：即一个索引包含多个列。

语法

  • 创建
CREATE [UNIQUE] INDEX index_name ON tb_name(column_name(length));
ALTER TABLE tb_name ADD [UNIQUE] INDEX[index_name] ON (column_name(length));

ALTER TABLE tb_name ADD PRIMARY KEY(column_list); --添加一个主键，索引值唯一，不为空
ALTER TABLE tb_name ADD UNIQUE index_name(column_list);-- 索引值唯一（NULL除外）
ALTER TABLE tb_name ADD index_name(column_list);--普通索引
ALTER TABLE tb_name ADD FULLTEXT index_name(column_list);--全文索引
  • 查看
SHOW INDEX FROM tb_name;
  • 删除
DROP INDEX[index_name] ON tb_name;

数据结构

B+树
  • B树的变形
  • 非叶子节点不存储data,只存储索引（冗余），可以放更多的索引。
  • 叶子节点包含所有的索引字段。
  • 叶子节点间使用指针链接，提高区间访问性能。
    在这里插入图片描述
B树与B+树区别
不使用Hash的原因
  • 哈希碰撞
  • 不支持范围查找，例如where age>10。

SQL语句

参考：《MySql必知必会》

常见

--基本查询
SELECT fid FROM article;
SELECT * FROM article;
SELECT bname FROM article;

-- distinct 应用于多行，去重
SELECT DISTINCT bname FROM article; 

-- 从第二行开始的下一个两行，不包括第二行，常用于分页
SELECT * FROM article LIMIT 2;
SELECT * FROM article LIMIT 2,2; 

--排序
SELECT fid FROM article ORDER BY fid;
SELECT bname,fid FROM article ORDER BY bname ,fid DESC;

-- order by ,limit 使用可以返回最小最大值
SELECT fid FROM article ORDER BY fid LIMIT 1; 

-- = <> != < > <= >= between，"java"也可以
SELECT * FROM article WHERE fid=40; 
SELECT * FROM article WHERE bname='java'; 

-- 注意 is
SELECT * FROM USER WHERE age IS NULL; 
SELECT * FROM USER WHERE sex='男' AND age<=23;

-- or 与 and 混用， and 优先级高
SELECT * FROM USER WHERE age=23 OR sex='男'; 

-- in
SELECT * FROM article WHERE bname IN('java','架构');
SELECT * FROM article WHERE bname NOT IN('JAVA'); -- 忽略大小写

--模糊查询
SELECT * FROM article WHERE fcontent LIKE '%写%';
SELECT * FROM USER WHERE NAME LIKE '_亮'; -- _只能匹配一个字符

/*支持运算
+ 加
- 减
* 乘
/ 除
*/
SELECT  fid,userid,fid*userid AS ex FROM article; -- 如果fid,userid 换成数目与单价，ex则为某物品总额，这里仅仅说明方法

正则表达式

-- 正则表达式
SELECT * FROM article WHERE bname REGEXP '.ava'; -- .匹配任意字符 \\-表示查找-， \\.表示查找.
SELECT * FROM USER WHERE userid REGEXP '26|29'; -- 匹配26或29
SELECT * FROM USER WHERE userid REGEXP '2[69]'; -- 匹配26或29
SELECT * FROM USER WHERE userid REGEXP '2[6-9]'; -- 匹配26-29
/*
[:alnum:] 任意字母和数字（同[a-zA-Z0-9]）
[:alpha:] 任意字符（同[a-zA-Z]）
[:cntrl:] ASCII控制字符（ ASCII 0到31和127）
[:digit:] 任意数字（同[0-9]）
[:xdigit:] 任意十六进制数字（同[a-fA-F0-9]）
[:graph:] 与[:print:]相同，但不包括空格
[:lower:] 任意小写字母（同[a-z]）
[:upper:] 任意大写字母（同[A-Z]）
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）
[:blank:] 空格和制表（同[\\t]）

{n} 指定数目的匹配    [[:digit:]]{4} 等于[0-9][0-9][0-9][0-9] 匹配四位整数
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围（ m不超过255）
* 0个或多个匹配
+ 1个或多个匹配（等于{1,}）
? 0个或1个匹配（等于{0,1}） 放在字符后
^ 文本的开始     ^[0-9\\.] 文首以数字及.开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾 
*/

字符串函数

/*字符串函数*/
/*
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写
*/
SELECT bname,UPPER(bname) FROM article; -- 小写转大写
SELECT SUBSTR("我爱中国",3,4) res; --res:中国
SELECT CONCAT(UPPER(SUBSTR("zhangsan",1,5)),"_",LOWER(SUBSTR("zhangsan",6))); -- res:ZHANG_san
SELECT INSTR("我爱中国","中国") res; -- res:3
SELECT LPAD("我爱中国",10,"*") res; -- res:******我爱中国  RPAD同理
SELECT REPLACE("我爱中国","我","你") as res; -- res:你爱中国

-- 拼接两列 SSM心得（java）
SELECT CONCAT (titles,'(',bname,')') FROM article ORDER BY bname; 
SELECT CONCAT (titles,'(',bname,')') AS listterm FROM article ORDER BY bname; -- 列名命名是listterm

日期函数

/*
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
*/
SELECT titles,TIME FROM article WHERE DATE(TIME)='2019-05-08';
SELECT titles,TIME FROM article WHERE TIME(TIME)='19:22:49';
SELECT titles,TIME FROM article WHERE DATE(TIME) BETWEEN '2019-05-08' AND '2019-05-15';
SELECT titles,TIME FROM article WHERE YEAR(TIME)=2019;
SELECT NOW() res;--res:2021-09-26 09:49:46
SELECT CURRENT_DATE() res; --res:2021-09-26
SELECT CURRENT_TIME() res; --res:09:50:33
SELECT YEAR(NOW()) res; --res:2021
SELECT YEAR("1998-07-01") res; --res:1998
SELECT MONTH(NOW()) res; --res:9
SELECT MONTHNAME(NOW()) res; --res:September
SELECT STR_TO_DATE("9-26-2021","%m-%d-%Y") res; --res:2021-09-26
SELECT DATE_FORMAT("2021/9/26","%Y年%m月%d日") res; --res:2021年09月26日

数学函数

/*
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切
ROUND() 四舍五入
*/
SELECT fid,COS(fid) FROM article; -- 仅仅验证，没有什么含义
SELECT ROUND(-1.5) as res; --res:-2
SELECT ROUND(1.5) as res; -- res:2
SELECT ROUND(1.523,2) as res;--res：1.52
SELECT CEIL(-1.5) as res;--res:-1 向上取整
SELECT FLOOR(-1.5) as res;--res:-2 向下取整
SELECT TRUNCATE(-1.5555,2) as res;--res：-1.55 截断

聚集函数


/*聚集函数
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
*/
SELECT AVG(age) AS avgage FROM USER; -- AVG()函数忽略列值为NULL的行。
SELECT COUNT(*) AS usernum FROM USER;
SELECT COUNT(age) AS haveageuser FROM USER;
SELECT MAX(age) AS maxage FROM USER;
SELECT SUM(age) AS sumage FROM USER;
SELECT AVG(DISTINCT age) AS avgage FROM USER; -- 忽略相同年龄的

/*
group by 不一定有序输出
GROUP BY子句可以包含任意数目的列。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。
除聚集计算语句外， SELECT语句中的每个列都必须在GROUP BY子句中给出。
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前
*/
SELECT bname,COUNT(*) AS fnum FROM article GROUP BY bname; -- 每个板块不同的文章数目
SELECT bname,userid,COUNT(*) AS fnum FROM article GROUP BY bname,userid; -- 不同用户在不同板块发表文章数

/*
WHERE过滤行，而HAVING过滤分组。
HAVING支持所有WHERE操作符。
WHERE在数据分组前进行过滤， HAVING在数据分组后进行过滤。这是一个重要的区别， WHERE排除的行不包括在分组中。
这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。
*/
SELECT userid,COUNT(*) AS fnum FROM article GROUP BY userid HAVING fnum>=2; -- 统计发表文章大于等于两篇的用户,也可以不用别名，直接having count(*)>=2
SELECT userid,COUNT(*) AS fnum FROM article WHERE bname='java' GROUP BY userid HAVING fnum>1; -- 统计在java模块发表文章超过一篇的用户

/*select 语句顺序 
子句 说明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否
*/
SELECT NAME FROM USER WHERE userid IN(SELECT userid FROM article WHERE bname='java'); -- 查找在java模块发表过文章的用户
SELECT NAME,age,(SELECT COUNT(*) FROM article WHERE user.userid=article.userid) AS fnum FROM USER ORDER BY age; -- 统计发表数目的同时按年龄排序输出姓名，年龄，文章数

联合查询

SELECT NAME,age,sex,titles FROM USER,article WHERE user.userid=article.userid;
SELECT NAME,age,sex,titles FROM USER INNER JOIN article ON user.userid=article.userid; -- 内部联结
SELECT NAME,age,sex,titles FROM USER AS u INNER JOIN article AS a ON u.userid=a.userid; -- 别名
SELECT NAME,age,sex,titles FROM USER AS u INNER JOIN article AS a ON u.userid=a.userid AND a.bname='java';
SELECT NAME,age,sex,titles FROM USER AS u,article AS a WHERE u.userid=a.userid AND a.bname='java';  -- 与上一句作用相同
--多表内连接查询；
SELECT aid,atitle,acontent,aphoto,uname,mname FROM (article INNER JOIN USER ON article.uid=user.uid) INNER JOIN module ON article.mid=module.mid --三表查询

/*外部联结，许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。
对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；
列出所有产品以及订购数量，包括没有人订购的产品；
计算平均销售规模，包括那些至今尚未下订单的客户
列出所有的文章及发表者信息，包括没有发表的
*/
/*在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（ RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）*/
-- 右连接包含右表的所有，左表没有的用NUll填充，内连接是只查询两表都有的
SELECT NAME,age,sex,titles FROM USER AS u LEFT JOIN article AS a ON u.userid=a.userid; -- 统计发表的文章的用户，同时也包含没有发表的用户


SELECT u.name,u.age,u.userid,COUNT(*) AS fnum FROM USER AS u INNER JOIN article AS a ON u.userid=a.userid GROUP BY a.userid;
SELECT fid,titles FROM article WHERE bname='架构' UNION SELECT fid,titles FROM article WHERE userid=28; -- 联合查询，union默认取消重复行，可以用 union all全部匹配
--数据库IF  如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。
IF(expr1,expr2,expr3)

事务

--事务
/*
事务处理是一种
机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完
整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们
或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发
生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤
销）以恢复数据库到某个已知且安全的状态。
*/
-- 步骤1：开启
SAVEPOINT delete1;  -- 保留点，在一次提交或回滚后释放
SET autocommit=0; --必须设置自动提交功能为禁用
START TRANSACTION; -- 事务开始	
-- 步骤2：编写事务语句
DELETE FROM USER WHERE userid=28; --事务语句1
DELETE FROM article WHERE userid=28; --事务语句2
-- 步骤3：结束事务，提交or回滚
COMMIT;	-- 提交事务
ROLLBACK TO delete1; -- 回滚到保留点
  • sql语句编写顺序：
    sql语句编写顺序
  • sql语句解析顺序：
    在这里插入图片描述

MySQL优化

风痕funhence
关注
专栏目录
腾讯云数据库mysql产品认证答案
11-14
腾讯云数据库mysql产品认证答案 作为一名IT行业大师，我将详细解释腾讯云数据库mysql产品认证答案，涵盖标题、描述、标签和部分内容中提到的知识点。 腾讯云数据库mysql产品认证答案 腾讯云数据库mysql产品认证...
数据库查询语句
weixin_47447421的博客
04-28 1万+
1.查询全表数据 写法1: SELECT * FROM 表名; 写法2: select 字段名称1, 字段名称2, ... 字段名称n from 表名; 写法3:给表起别名,同时给字段起别名,as可以省略 select 关键字段1 AS'别名1', 关键字段2 AS'别名2', ... 关键字段n AS'别名n' from 表名 AS'别名'; 查询指定字段 select 关键字段1,
Oracle分页查询Rownum
Lyouths的移动笔记本
04-23 219
之前一直用MySQL，分页查询用limit关键字很容易就查到想要的数据了。 但是limit是MySQL是的关键字，oracle并没有。所以我们用rownum进行分页查询: select * from (select A.*,ROWNUM RN FROM (SELECT * FROM TableName) A) where RN between 11 AND 15; 注：TableName替换成想要...
数据库常用的SQL查询语句(非常详细)，看完这一篇就足够了
weixin_53249168的博客
08-15 1291
当我们进⾏语句查询的时候，总会遇到我们需要的条件需要通过另⼀个查询语句查询出来后才能进⾏，就是说A 查询语句需要依赖B 查询语句的查询结果，B 查询就是⼦查询，A 查询语句就是主查询，⼀个SQL语句可以包含多个⼦查询。如果查询数据的来源来自多张表，则必须对这些表进行连接查询，连接是把不同表的记录连到一起的最普遍的方法，通过连接查询可将多个表作为一个表进行处理，连接查询分为内连接和外连接。左外连接：是表1和表2的交集再并上表1的其他数据。右外连接：是表1和表2的交集再并上表2的其他数据。
数据库查询语句-详细篇
Aftery的博客
12-27 1万+
数据库的基本连接，读取表的数据信息
数据库的查询语句
weixin_68393415的博客
06-08 2万+
一 . 基本查询 二 . 条件查询 三 . 模糊查询 四 . 范围查询 五 . 为空查询 六 . 排序 七 . 聚合函数 八 . 分组 九 . 分页查询
数据库基础之查询语句
clyu的博客
04-04 1万+
前言 数据库基本查询语句语法如下： SELECT 列名1...列名n FROM 表名 #选定查找列来自的什么表 [ WHERE 查询条件 ] #限制查询条件，如果没有就查询所有的行 [ GROUP BY 列名1，...列名n ] #对查询进行分组 [ HAVING 条件 ] #过滤分组条件 [ ORDER BY 列名1，...列名n [ ASC | DESC ] ] #限制查
数据库MySQL入门.pdf
01-15
MySQL是属于Oracle Corporation（甲骨文公司）推出的关系型数据库管理系统， MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。 MySQL最流行的关系型数据库管理系统，在 WEB ...
万年历数据库 mysql
03-15
1970-2100年 的农历数据，节气，财神方位，宜，忌，星座，天干地址等等，大数据库，sqlite 版。
数据库Mysql和redis
09-02
数据库Mysql和redis
周公解梦完整数据库mysql
05-22
周公解梦完整版本，mysql数据库信息，大概9000多条数据，带分类，名称，详细解析等。 周公解梦数据库.sql脚本文件，直接可以导入mysql，其他的数据库或许需要细微的修改。
数据库mysql-mysql的卸载
10-18
mysql的验证和卸载方式
身份证归属地数据库 mysql语句
最新发布
09-05
身份证归属地数据库 mysql语句
学习MySQL数据库 MySQL数据类型
08-12
MySQL数据类型
全国地区地址数据库 MySQL 版
07-02
全国地区地址数据库 MySQL 版
MySQL 查询语句大全
热门推荐
qq_57570052的博客
08-08 8万+
超全MySQL查询语句，MySQL查询语句有这一篇就够了
SQL优化-查询语句特征（3）索引
vransy的博客
11-02 137
索引 索引存在于对应的索引文件中 索引的本质： 就是一组排好顺序的数据 作用 1.在索引数据中进行排序时，可避免再次排序 2.在索引数据中对数据行进行定位 时，避免全表扫描 索引文件的位置 1.如果当前表是依赖于innodb存储引擎，索引存在FRM中 2.如果当前表依赖于MYISAM存储引擎，索引存在MYI中 索引基本操作 1.查看表文件中已经存在的索引 SHOW INDEX FROM 表中索引 2.创建索引 CREATE INDEX 索引名 ON 表名（字段） 3.删除索引 drop index 索引名
"深入探讨数据库MySQL开发技术与SQL语言操作
数据库MySQL开发技术是数据库管理系统中非常重要的一部分，掌握MySQL开发技术对于数据库开发人员来说是至关重要的。MySQL是一种开放源代码的关系型数据库管理系统，广泛应用于Web应用程序的开发中。在MySQL开发技术...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值