MySQL基础

文章目录
  1. 1. 数据库的存储引擎MyISAM 和 InnoDB 的区别
  2. 2. 什么是索引,作用是什么?常见索引类型有那些?Mysql 建立索引的原则
  3. 3. MySQL 分库分表怎么设计
  4. 4. 什么是 MySQL 的事务?事务都有那些特性
  5. 5. 什么是NoSQL
  6. 6. 数据库最左原则
  7. 7. 什么是索引?为什么要建立索引
  8. 8. 索引的分类
  9. 9. 数据库索引建立的原则
  10. 10. 什么是 MySQL 死锁?如何有效降低死锁
  11. 11. 什么是 MySQL 的事务?事务都有那些特性
  12. 12. SQL语言的分类
  13. 13. PDO对象中的成员方法:
  14. 14. 什么是锁
  15. 15. 什么叫视图
  16. 16. 什么是游标
  17. 17. JOIN ON语句
  18. 18. 如何通俗地理解三个范式
  19. 19. 什么是存储过程
  20. 20. MySQL数据类型
  21. 21. MySQL中TRUNCATE、DELETE和DROP的区别
  22. 22. PgSQL和MySQL区别
  23. 23. 聚合函数
数据库的存储引擎MyISAM 和 InnoDB 的区别
  • MyISAM 查询效率更高,但是不支持事务
  • InnoDB 插入、更新较高,支持事务处理
  • MyISAM 支持表锁, InnoDb 支持行锁
  • MyISAM 是默认引擎,InnoDB 需要指定
  • InnoDB 不支持 FULLTEXT 类型的索引
什么是索引,作用是什么?常见索引类型有那些?Mysql 建立索引的原则

索引是一种特殊的文件,它们包含着对数据表里所有记录的引用指针,相当于书本的目录。其作用就是加快数据的检索效率。常见索引类型有主键、唯一索引、复合索引、全文索引

索引创建的原则

  • 最左前缀原理
  • 尽量的去扩展索引,而不是重复的新建新索引
MySQL 分库分表怎么设计
  1. 垂直分表
    垂直分表在日常开发和设计中比较常见,通俗的说法叫做“大表拆小表”,某个表中的字段比较多,可以新建立一张“扩展表”,将不经常使用或者长度较大的字段,拆分出去放到“扩展表”中。
  2. 垂直分库
    基本的思路就是按照业务模块来划分出不同的数据库,而不是像早期一样将所有的数据表都放到同一个数据库中。
  3. 水平分表
    水平分表也称为横向分表,比较容易理解,就是将表中不同的数据行按照一定规律分布到不同的数据库表中(这些表保存在同一个数据库中),这样来降低单表数据量,优化查询性能。
  4. 水平分库分表
    水平分库分表与上面讲到的水平分表的思想相同,唯一不同的就是将这些拆分出来的表保存在不同的数据库中。
什么是 MySQL 的事务?事务都有那些特性

事务是一个序列操作,其中的操作要么都执行,要么都不执行,它是一个不可分割的工作单位,ACID 四大特性是事务的基础。

  • A原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。
  • C一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。
  • I隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  • D持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
什么是NoSQL

NoSQL,指的是非关系型的数据库。NoSQL有时也称作Not Only SQL的缩写,是对不同于传统的关系型数据库的数据库管理系统的统称。

NoSQL用于超大规模数据的存储。(例如谷歌或Facebook每天为他们的用户收集万亿比特的数据)。这些类型的数据存储不需要固定的模式,无需多余操作就可以横向扩展。

数据库最左原则

最左优先,以最左边的为起点任何连续的索引都能匹配上。

在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

组合索引就是遵从了最左前缀,利用索引中最左边的列集来匹配行,这样的列集称为最左前缀,例如:这里由id、name和age3个字段构成的索引,索引行中就按id/name/age的顺序存放,索引可以索引下面字段组合(id,name,age)、(id,name)或者(id)。如果要查询的字段不构成索引最左面的前缀,那么就不会是用索引,比如,age或者(name,age)组合就不会使用索引查询

什么是索引?为什么要建立索引

索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

索引的分类
  1. 单列索引 - 一个索引只包含单个列,但一个表中可以有多个单列索引。
  • 普通索引 - MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值。
  • 唯一索引 - 索引列中的值必须是唯一的,但是允许为空值。
  • 主键索引 - 是一种特殊的唯一索引,不允许有空值。
  1. 组合索引 - 在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。
  2. 全文索引 - 只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。
  3. 空间索引 - 空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。
  4. 数据库索引建立的原则
  5. 确定针对该表的操作是大量的查询操作还是大量的增删改操作。
  6. 尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引。
  7. 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间,同时,复合索引也占磁盘空间。
  8. 对于小型的表,建立索引可能会影响性能。
  9. 应该避免对具有较少值的字段进行索引。
  10. 避免选择大型数据类型的列作为索引。
什么是 MySQL 死锁?如何有效降低死锁

死锁:死锁一般是事务相互等待对方资源,最后形成环路,而无法继续运行。
产生死锁的原因:

  1. 系统资源不足;
  2. 进程运行推进的顺序不合适;
  3. 资源分配不当等;

如何有效降低死锁:

  1. 按同一顺序访问资源;
  2. 避免事务中的用户交互;
  3. 保持事务简短并在一个批处理中;
  4. 使用低隔离级别;
  5. 使用绑定连接;
什么是 MySQL 的事务?事务都有那些特性

事务是一个序列操作,其中的操作要么都执行,要么都不执行,它是一个不可分割的工作单位,ACID 四大特性是事务的基础。

  • 原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。
  • 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。
  • 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  • 持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
SQL语言的分类
  • DQL - 数据查询语言,由SELECT子句,FROM子句,WHERE子句组成的查询块。
  • DDL - 数据定义语言,用于定义和管理 SQL 数据库中的所有对象的语言。
  • DML - 数据操纵语言(Data Manipulation Language, DML)是SQL语言中,负责对数据库对象运行数据访问工作的指令集,以INSERT、UPDATE、DELETE三种指令为核心,分别代表插入、更新与删除。
  • DCL - 数据控制语言,用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等。
PDO对象中的成员方法:
  • getAttribute() 获取一个数据库连接对象的属性
  • setAttribute() 为一个数据库连接对象设定属性
  • errorCode() 获取错误码
  • errorInfo() 获取错误信息
  • exec() 处理一条SQL语句,并返回所影响的条数
  • query() 处理一条SQL语句,并返回一个PDOStatement对象
  • quote() 为某个sql中的字符串添加引号
  • lastInsertId() 获取插入到表中的最后一条数据的主键
  • prepare() 负责准备执行的sql语句
  • getAvailableDriver() 获取有效的PDO驱动器名称
  • beginTransaction() 开始一个事务
  • commit() 提交事务
  • rollback() 回滚事务
什么是锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

基本锁类型

  • 锁包括行级锁
  • 表级锁
什么叫视图

视图是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增,改,查,操作,视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比多表查询。

什么是游标

游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。

JOIN ON语句
  • inner join(等值连接) 只返回两个表中联结字段相等的行
  • left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
  • right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

两条SQL
1、select form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select
form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

如何通俗地理解三个范式
  • 第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;
  • 第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;
  • 第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余
什么是存储过程

存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

MySQL数据类型

引用于https://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html

整形
| MySQL数据类型 | 含义(有符号) |
| — | — |
| tinyint(m) | 1个字节 范围(-128~127) |
| smallint(m) | 2个字节 范围(-32768~32767) |
| mediumint(m) | 3个字节 范围(-8388608~8388607) |
| int(m) | 4个字节 范围(-2147483648~2147483647) |
| bigint(m) | 8个字节 范围(+-9.22*10的18次方) |

取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~256)。
int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个m有什么用。

浮点型(float和double)
| MySQL数据类型 | 含义(有符号) |
| — | — |
| float(m,d) | 单精度浮点型 8位精度(4字节) m总个数,d小数位 |
| double(m,d) | 双精度浮点型 16位精度(8字节) m总个数,d小数位 |

字符串(char,varchar,_text)
| MySQL数据类型 | 含义(有符号) |
| — | — |
| char(n) | 固定长度,最多255个字符 |
| varchar(n) | 固定长度,最多65535个字符 |
| tinytext | 可变长度,最多255个字符 |
| text | 可变长度,最多65535个字符 |
| mediumtext | 可变长度,最多2的24次方-1个字符 |
| longtext | 可变长度,最多2的32次方-1个字符 |

⚠️char和varchar

  1. char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。
  2. char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),所以varchar(4),存入3个字符将占用4个字节。
  3. char类型的字符串检索速度要比varchar类型的快。

⚠️varchar和text

  1. varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),text是实际字符数+2个字节。
  2. text类型不能有默认值。
  3. varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下,text的索引似乎不起作用。

二进制数据(_Blob)

  1. _BLOB和_text存储方式不同,_TEXT以文本方式存储,英文存储区分大小写,而_Blob是以二进制方式存储,不分大小写。
  2. _BLOB存储的数据只能整体读出。
  3. _TEXT可以指定字符集,_BLO不用指定字符集。

定点数
浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位。

日期时间类型

MySQL数据类型 含义
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间

⚠️若定义一个字段为timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。

MySQL中TRUNCATE、DELETE和DROP的区别
  • DELETE和TRUNCATE 删除表数据
  • DROP 删除表结构

    TRUNCATE相当于初始化,比如如果有自增长ID,用TRUNCATE删除后,自增长Id会从1开始,DELETE不会。

PgSQL和MySQL区别

原文:https://www.cnblogs.com/doit8791/p/8608597.html

一、 PostgreSQL 的稳定性极强, Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步,然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的,相比之下,PG数据库这方面要好一些。

二、任何系统都有它的性能极限,在高并发读写,负载逼近极限下,PG的性能指标仍可以维持双曲线甚至对数曲线,到顶峰之后不再下降,而 MySQL 明显出现一个波峰后下滑(5.5版本之后,在企业级版本中有个插件可以改善很多,不过需要付费)。

三、PG 多年来在 GIS 领域处于优势地位,因为它有丰富的几何类型,实际上不止几何类型,PG有大量字典、数组、bitmap 等数据类型,相比之下mysql就差很多,instagram就是因为PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial而采用PGSQL的。

四、PG 的“无锁定”特性非常突出,甚至包括 vacuum 这样的整理数据空间的操作,这个和PGSQL的MVCC实现有关系。

五、PG 的可以使用函数和条件索引,这使得PG数据库的调优非常灵活,mysql就没有这个功能,条件索引在web应用中很重要。

六、PG有极其强悍的 SQL 编程能力(9.x 图灵完备,支持递归!),有非常丰富的统计函数和统计语法支持,比如分析函数(ORACLE的叫法,PG里叫window函数),还可以用多种语言来写存储过程,对于R的支持也很好。这一点上MYSQL就差的很远,很多分析功能都不支持,腾讯内部数据存储主要是MYSQL,但是数据分析主要是HADOOP+PGSQL(听李元佳说过,但是没有验证过)。

七、PG 的有多种集群架构可以选择,plproxy 可以支持语句级的镜像或分片,slony 可以进行字段级的同步设置,standby 可以构建WAL文件级或流式的读写分离集群,同步频率和集群策略调整方便,操作非常简单。

八、一般关系型数据库的字符串有限定长度8k左右,无限长 TEXT 类型的功能受限,只能作为外部大数据访问。而 PG 的 TEXT 类型可以直接访问,SQL语法内置正则表达式,可以索引,还可以全文检索,或使用xml xpath。用PG的话,文档数据库都可以省了。

九,对于WEB应用来说,复制的特性很重要,mysql到现在也是异步复制,pgsql可以做到同步,异步,半同步复制。还有mysql的同步是基于binlog复制,类似oracle golden gate,是基于stream的复制,做到同步很困难,这种方式更加适合异地复制,pgsql的复制基于wal,可以做到同步复制。同时,pgsql还提供stream复制。

十,pgsql对于numa架构的支持比mysql强一些,比MYSQL对于读的性能更好一些,pgsql提交可以完全异步,而mysql的内存表不够实用(因为表锁的原因)

聚合函数

求各组平均值
select 类别, avg(数量) AS 平均值 from A group by 类别;

求各组记录数目
select 类别, count(*) AS 记录数 from A group by 类别;

Group By All
select 类别, 摘要, sum(数量) as 数量之和 from A group by all 类别, 摘要;

⚠️ Having与Where的区别

  • where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,where条件中不能包含聚组函数,使用where条件过滤出特定的行。
  • having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件过滤出特定的组,也可以使用多个分组标准进行分组。

select 类别, sum(数量) as 数量之和 from A group by 类别 having sum(数量) > 18

Having和Where的联合使用方法
select 类别, SUM(数量)from A where 数量 group by 类别 having SUM(数量);