快速学习MySQL基础知识

这篇文章主要梳理了 SQL 的基础用法,会涉及到以下方面内容:

  • SQL大小写的规范
  • 数据库的类型以及适用场景
  • SELECT 的执行过程
  • WHERE 使用规范
  • MySQL 中常见函数
  • 子查询分类
  • 如何选择合适的 EXISTS 和 IN 子查询

了解 SQL

SQL 是我们用来最长和数据打交道的方式之一,如果按照功能划分可分为如下 4 个部分:

  • DDL,数据定义语言。定义数据库对象,数据表,数据列。也就是,对数据库和表结构进行增删改操作。
  • DML,数据操作语言。对数据表的增删改。
  • DCL,数据控制语言。定义访问权限和安全级别。
  • DQL,数据查询语言。用来查询数据。

平时在编写 SQL 时,可能发现许多 SQL 大小写不统一,虽然不会影响 SQL 的执行结果,但保持统一的书写规范,是提高效率的关键,通常遵循如下的原则:

  • 表名,表别名,字段名,字段别名等用小写。
  • SQL 保留字,函数名,绑定变量等用大写。
  • 数据表,字段名采用下划线命名。

目前排名较前的 DBMS:

  • 关系型数据库:建立在关系模型上的数据库,在建表时,通常先设计 ER 图表示之间的关系。
  • 键值型数据库:以 key-value 的形式存储数据,优点是查找速度快,缺点是无法向关系型数据库一样使用如 WHERE 等的过滤条件。常见场景是作为内容缓存。
  • 文档型数据库,在保存时以文档作为处理信息的基本单位。
  • 搜索引擎:针对全文检索而设计。核心原理是 “倒排索引”。
  • 列式数据库:相对于如 MySQL 等行式存储的数据库,是以列将数据存在数据库中,由于列具有相同的数据类型,所以可以更好的压缩,从而减低系统的 I/O,适用于分布式文件系统,但功能相对有限。
  • 图形数据库,利用图的数据结构存储实体之间的关系。比如社交网络中人与人的关系,数据模型为节点和边来实现。

认识 SELECT

SELECT 一般是在学习 SQL 接触的第一个关键字,基础的内容就是不提了,这里整理常用的规范:

起别名

SELECT name AS n FROM student

查询常数, 增加一列固定的常数列:

SELECT '学生信息' as student_info, name FROM student

去重重复行

SELECT DISTINCT age FROM student

需要注意的是 DISTINCT 是对后面的所有列进行去重, 下面这种情况就会对 age 和 name 的组合进行去重。

SELECT DISTINCT age,name FROM student

排序数据,ASC 代表升序,DESC 代表降序

如先按照 name 排序,name 相等的情况下按照 age 排序。

SELECT DISTINCT age FROM student ORDERY BY name,age DESC

限制返回的数量

SELECT DISTINCT age FROM student ORDERY BY name DESC LIMIT 5

SELECT 的执行顺序

了解了 SELECT 的执行顺序,才能更好地写出更有效率的 SQL。

对于 SELECT 顺序有两个原则:

  • 关键字的顺序不能颠倒:
  • SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

  • SELECT 会按照如下顺序执行:
  • FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

SELECT DISTINCT student_id, name, count(*) as num #顺序5
FROM student JOIN class ON student.class_id = class.class_id #顺序1
WHERE age > 18 #顺序2
GROUP BY student.class_id #顺序3
HAVING num > 2 #顺序4
ORDER BY num DESC #顺序6
LIMIT 2 #顺序7

在逐一分析下这个过程前,我们需要知道在上面的每一个步骤中都会产生一个虚拟表,然后将这个虚拟表作为下一个步骤中作为输入,但这一过程对我们来说是不可见的:

  1. 从 FROM 语句开始,对 student 和 class 表进行 CROSS JOIN 笛卡尔积运算,得到虚拟表 vt 1-1;
  2. 通过 ON 筛选,在 vt1-1 的基础上进行过滤然后得到表 vt 1-2;
  3. 添加外部行。如使用左连接,右连接和全连接时,就会涉及到外部行,会在 vt1-2 的基础上增加外部行,得到 vt1-3。
  4. 如果超过两张表,就会重复上面的步骤。
  5. 在拿到最终的 vt1 的表数据后,会执行 WHERE 后面的过滤阶段,得到表 vt2.
  6. 接着到 GROUP 阶段,进行分组得到 vt3.
  7. 接着到 HAVING 阶段,对分组的数据进行过滤,得到 vt4.
  8. 后面进入 SELECT 阶段,提取需要的字段,得到 vt5-1,接着通过 DISTINCT 阶段,过滤到重复的行,得到 vt5-2.
  9. 然后对指定的字段进行排序,进入 ORDER BY 阶段,得到 vt6.
  10. 最后在 LIMIT 阶段,取出指定的行,对应 vt7,也就是最后的结果。

如果涉及到函数的计算比如 sum() 等,会在 GROUP BY分组后,HAVING 分组前,进行聚集函数的计算。

涉及到表达式计算,如 age * 10 等,会在 HAVING 阶段后,SELECT 阶段前进行计算。

  • 通过这里,就可以总结出提高 SQL 效率的第一个方法:

使用 SELECT 时指定明确的列来代替 SELECT * . 从而减少网络的传输量。

使用 WHERE 进行过滤

使用 WHERE 筛选时,常有通过比较运算符,逻辑运算符,通配符三种方式。

对于比较运算符,常用的运算符如下表。

对于逻辑运算符来说,可以将多个比较运行符连接起来,进行多条件的筛选,常用的运算符如下:

需要注意的是,当 AND 和 OR 同时出现时,AND 的优先级更高会先被执行。当如果存在 () 的话,则括号的优先级最高。

使用通配符过滤:

like:(%)代表零个或多个字符,(_)只代表一个字符

函数

和编程语言中的定义的函数一样,SQL 同样定义了一些函数方便使用,比如求和,平均值,长度等。

常见的函数主要分为如下四类,分类的原则是根据定义列时的数据类型:

  • 算术函数:

  • 字符串函数

需要注意的是,在使用字符串比较日期时,要使用 DATE 函数比较。

  • 日期函数

  • 转换函数:

CAST 函数在转换数据类型时,不会四舍五入,如果原数值是小数,在转换到整数时会报错。

在转换时可以使用 DECIMAL(a,b) 函数来规定小数的精度,比如 DECIMAL(8,2) 表示精度为 8 位 - 小数加整数最多 8 位。小数后面最多为 2 位。

然后通过 SELECT CAST(123.123 AS DECIMAL(8,2)) 来转换。

聚集函数

通常情况下,我们会使用聚集函数来汇总表的数据,输入为一组数据,输出为单个值。

常用的聚集函数有 5 个:

其中 COUNT 函数需要额外注意,具体的内容可以参考这篇。

如何进行分组

在统计结果时,往往需要对数据按照一定条件进行分组,对应就是 GROUP BY 语句。

比如统计每个班级的学生人数:

SELECT class_id, COUNT(*) as student_count FROM student \
GROUP BY class_id; 

GROUP BY 后也可接多个列名,进行分组,比如按照班级和性别分组:

SELECT class_id, sex, COUNT(*) as student_count FROM \
student GROUP BY class_id, sex; 

HAVING 过滤和 WHERE 的区别

和 WHERE 一样,可以对分组后的数据进行筛选。区别在于 WHERE 适用于数据行,HAVING 用于分组。

而且 WHERE 支持的操作,HAVING 也同样支持。

比如可以筛选大于2人的班级:

SELECT class_id, COUNT(*) as student_count FROM student \
GROUP BY class_id \
HAVING student_count > 20; 

子查询

在一些更为复杂的情况中,往往会进行嵌套的查询,比如在获取结果后,该结果作为输入,去获取另外一组结果。

在 SQL 中,查询可以分为关联子查询和非关联子查询。

假设有如下的表结构:

-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL DEFAULT '',
 `age` int(3) NOT NULL,
 `sex` varchar(10) NOT NULL DEFAULT '',
 `class_id` int(11) NOT NULL COMMENT '班级ID',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '胡一', 13, '男', '1');
INSERT INTO `student` VALUES ('3', '王阿', 11, '女', '1');
INSERT INTO `student` VALUES ('5', '王琦', 12, '男', '1');
INSERT INTO `student` VALUES ('7', '刘伟', 11, '女', '1');
INSERT INTO `student` VALUES ('7', '王意识', 11, '女', '2');

-- ----------------------------
DROP TABLE IF EXISTS `student_activities`;
CREATE TABLE `student_activities` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL DEFAULT '',
 `stu_id` int(11) NOT NULL COMMENT '班级ID',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `student_activities` VALUES ('1', '博物馆', 1);
INSERT INTO `student_activities` VALUES ('3, '春游', 3);

非关联子查询

子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件接着执行。

这里想要查询和胡一相同班级的同学名称:

SELECT name FROM student WHERE class_id = \
(SELECT class_id FROM student WHERE name='胡一')

这里先查到胡一的班级,只有一次查询,再根据该班级查找学生就是非关联子查询。

关联子查询

如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部

再举个例子, 比如查询比每个班级中比平均年龄大的学生姓名信息:

SELECT name FROM student as s1 WHERE age >
	(SELECT AVG(age) FROM student as s2 where s1.class_id = s2.class_id) 

这里根据每名同学的班级信息,查找出对应班级的平均年龄,然后做判断。子查询每次执行时,都需要根据外部的查询然后进行计算。这样的子查询就是关联子查询。

EXISTS 子查询

在关联子查询中,常会和 EXISTS 一起使用。用来判断条件是否满足,满足的话为 True,不满足为 False。

比如查询参加过学校活动的学生名称:

SELECT NAME FROM student as s where \
	EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)

同样 NOT EXISTS 就是不存在的意思,满足为 FALSE , 不满足为 True.

比如查询没有参加过学校活动的学生名称:

SELECT NAME FROM student as s where \
	NOT EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)

集合比较子查询

可以在子查询中,使用集合操作符,来比较结果。

还是上面查询参加学校活动的学生名字的子查询, 同样可以使用 IN:

SELECT name FROM student WHERE id IN (SELECT stu_id FROM student_activities)

EXISTS 和 IN 的区别

既然 EXISTS 和 IN 都能实现相同的功能,那么他们之间的区别是什么?

现在假设我们有表 A 和 表 B,其中 A,B 都有字段 cc,并对 cc 建立了 b+ 索引,其中 A 表 n 条记录,B 表 m 条索引。

将其模式抽象为:

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

对于 EXISTS 来说,会先对外表进行逐条循环,每次拿到外表的结果后,带入子查询的内表中,去判断该值是否存在。

伪代码类似于下面:

for i in A
    for j in B
        if j.cc == i.cc:
         return result

首先先看外表 A,每一条都需要遍历到,所以需要 n 次。内表 B,在查询时由于使用索引进而查询效率变成 log(m) B+ 的树高,而不是 m。

进而总效率:n * log(m)

所以对于 A 表的数量明显小于 B 时,推荐使用 EXISTS 查询。

再看 IN ,会先对内表 B 进行查询,然后用外表 A 进行判断,伪代码如下:

for i in B
    for j in A
        if j.cc == i.cc:
         return result

由于需要首先将内表所有数据查出,所以需要的次数就是 m. 再看外表 A ,由于使用了 cc 索引,可将 n 简化至 log(n), 也就是 m * log(n).

所以对于 A 表的数据明显大于 B 表时,推荐使用 IN 查询。

总结一下对于 IN 和 EXISTS时,采用小表驱动大表的原则。

这里再扩展下 NOT EXISTS 和 NOT IN 的区别:

SELECT * FROM A WHERE cc NOT IN (SELECT cc FROM B) 

SELECT * FROM A WHERE NOT EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

对于 NOT EXITS 来说,和 EXISTS 一样,对于内表可以使用 cc 的索引。适用于 A 表小于 B 表的情况。

但对于 NOT IN 来说,和 IN 就有区别了,由于 cc 设置了索引 cc IN (1, 2, 3) 可以转换成 WHERE cc=1 OR cc=2 OR cc=3 , 是可以正常走 cc 索引的。但对于 NOT IN 也就是转化为 cc!=1 OR cc!=2 OR cc!=3 这时由于是不等号查询,是无法走索引的,进而全表扫描。

也就是说,在设置索引的情况下 NOT EXISTS 比 NOT IN 的效率高。

但对于没有索引的情况,IN 和 OR 是不同的:

一、操作不同
1、in:in是把父查询表和子查询表作hash连接。
2、or:or是对父查询表作loop循环,每次loop循环再对子查询表进行查询。

二、适用场景不同
1、in:in适合用于子查询表数据比父查询表数据多的情况。
2、or:or适合用于子查询表数据比父查询表数据少的情况。

三、效率不同
1、in:在没有索引的情况下,随着in后面的数据量越多,in的执行效率不会有太大的下降。
2、or:在没有索引的情况下,随着or后面的数据量越多,or的执行效率会有明显的下降。

以上就是快速学习MySQL基础知识的详细内容,更多关于MySQL基础知识的资料请关注我们其它相关文章!

(0)

相关推荐

  • 学习mysql之后的一点总结(基础)

    1.想要在命令提示符下操作mysql服务器,添加系统变量.(计算机-系统属性--环境变量--path) 2.查询数据表中的数据: select selection_list select * /columns from table_list from table1/table2 where primary_constraint group by grouping_columns order by sorting_colomns desc降序 select * from table order b

  • Mysql基础入门 轻松学习Mysql命令

    一.MySQL的相关概念介绍 MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格: 表头(header): 每一列的名称; 列(row): 具有相同数据类型的数据的集合; 行(col): 每一行用来描述某个人/物的具体信息; 值(value): 行的具体信息, 每个值必须与该列的数据类型相同; 键(key

  • MySQL事务的基础学习以及心得分享

    事务是逻辑上的一组操作,组成这组操作的各个单元,要不全都成功要不全都失败,这个特性就是事务,下面就是关于MySQL事务学习中的心得分享: 事务的特性 1.原子性(Atomicity):原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生. 2.一致性(Consistency):在一个事务中,事务前后数据的完整性必须保持一致,可以想象银行转账.火车购票. 3.隔离性(Isolation):多个事务,事务的隔离性是指多个用户并发访问数据库时, 一个用户的事务不能被其它用户的事

  • MySQL中触发器的基础学习教程

    0.触发器的基本概念 触发器是一种特殊的存储过程,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力. 数据库触发器有以下的作用: (1).安全性.可以基于数据库的值使用户具有操作数据库的某种权利. # 可以基于时间限制用户的操作,例如不允许下班后和节假日修改数据库数据. # 可以基于数据库中的数据限制用户的操作,例如不允许股票的价格的升幅一次超过10%. (2).审计.可以跟踪用户对数据库的操作. # 审计用户操作数据库的语句. # 把用户对数

  • MySQL中表子查询与关联子查询的基础学习教程

    MySQL 表子查询 表子查询是指子查询返回的结果集是 N 行 N 列的一个表数据. MySQL 表子查询实例 下面是用于例子的两张原始数据表: article 表: blog 表: SQL 如下: SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog) 查询返回结果如下所示: 该 SQL 的意义在于查找 article 表中指定的字段同时也存在于 blog 表中的所有的行(注

  • mysql学习笔记之基础知识

    查看数据库 show databases; 创建数据库 create DATABASE 数据库名称 create DATABASE databasetest; 选择数据库 use 数据库名称 use databasetest; ------------ Database changed;切换成功 查看当前数据库名称 SELECT DATABASE(); 删除数据库 drop DATABASE 数据库名称 drop DATABASE databasetest; 数据库状态 status; ----

  • 快速学习MySQL基础知识

    这篇文章主要梳理了 SQL 的基础用法,会涉及到以下方面内容: SQL大小写的规范 数据库的类型以及适用场景 SELECT 的执行过程 WHERE 使用规范 MySQL 中常见函数 子查询分类 如何选择合适的 EXISTS 和 IN 子查询 了解 SQL SQL 是我们用来最长和数据打交道的方式之一,如果按照功能划分可分为如下 4 个部分: DDL,数据定义语言.定义数据库对象,数据表,数据列.也就是,对数据库和表结构进行增删改操作. DML,数据操作语言.对数据表的增删改. DCL,数据控制语

  • Spring5学习之基础知识总结

    1.概述 1.Spring 是轻量级的开源的 JavaEE 框架 2. Spring 可以解决企业应用开发的复杂性 3.Spring 有两个核心部分:IOC 和 Aop IOC:控制反转,把创建对象过程交给 Spring 进行管理Aop:面向切面,不修改源代码进行功能增强 4.Spring 特点 方便解耦,简化开发Aop 编程支持方便程序测试方便和其他框架进行整合方便进行事务操作降低 API 开发难度 2.入门Demo 1.jar包引入 <project xmlns="http://mav

  • 一文读懂navicat for mysql基础知识

    一.数据库的操作 1.新建数据库 2.打开数据库 右键或者双击就可以了. 3.删除数据库 右键–>删除数据库 4.修改数据库 右键–>数据库属性 二.数据类型 1.常用的数据类型 整数:int 小数:decimal 字符串:varchar 日期时间:datatime 2.约束条件 主键:物理上储存的顺序(主键唯一.不能为空,所以允许空值的勾得去掉,不然不能新建或保存,还可以选择下面注释中得自动递增节省工作量) 非空:此字段不允许填空值 唯一:此字段不允许重复 默认值:当不填写时会使用默认值,如

  • 学习nginx基础知识

    目录 一.什么是nginx? 二.nginx能干什么? 2.1 什么是正向代理? 2.2 什么是反向代理? 2.3负载均衡 2.4 动静分离 三.nginx基本操作 3.1 nginx配置文件 nginx/conf/nginx.conf 3.2 防火墙设定 四.反向代理实现案例 4.1.Nginx反向代理的实现案例1 1.hosts 文件域名配置 2.nginx里进行请求转发的配置(反向代理配置) nginx.conf 4.2Nginx反向代理的实现案例2 五.负载均衡实现案例 5.1nginx

  • mysql基础知识扫盲

    本篇主要介绍关于mysql的一些非常基础的知识,为后面的sql优化做准备. 一:连接mysql 关于mysql的下载和安装我在这里就不说了,第一步我们要连接我们的mysql服务器,打开cmd命令切换到你安装MySQL Server 的bin目录下,然后输入mysql -h localhost -u root -p 其中-h 表示你的主机地址(本机就是localhost,记住不要带端口号) -u 就是连接数据库名称 -p就是连接密码.出现以下图就表示连接成功了 二:常用的sql语句 2.1:创建数

  • 快速学习MySQL索引的入门超级教程

    所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找.而用的最多,并且是mysql默认的就是二叉树算法 BTREE,通过BTREE算法建立索引的字段,比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果,具体的实现方式后续本博客会出一个算法专题里面会有具体的分析讨论; Explain优化查询检测 EXPLAIN可以帮助开发人员分析SQL问题,explain显示了mysql如何使用索引来处理select语

  • R语言学习Rcpp基础知识全面整理

    目录 1. 相关配置和说明 2. 常用数据类型 3. 常用数据类型的建立 4. 常用数据类型元素访问 5. 成员函数 6. 语法糖 6.1 算术和逻辑运算符 6.2. 常用函数 7. STL 7.1. 迭代器 7.2. 算法 7.3. 数据结构 7.3.1. Vectors 7.3.2. Sets 7.3.3. Maps 8. 与R环境的互动 9. 用Rcpp创建R包 10. 输入和输出示例 如何传递数组 通过.attr("dim")设置维数 函数返回一维STL vector 函数返回

  • 深入mysql基础知识的详解

    1.每个客户端连接都会从服务器进程中分到一个属于它的线程.而该连接的相应查询都都会通过该线程处理. 2.服务器会缓存线程.因此并不会为每个新连接创建或者销毁线程. 3.当发起对MySQL服务器的连接时,服务器会对 username,host,password进行验证.而一旦连接上,服务器就会检测其权限. 4.MySQL查询缓存只会保存 SELECT 语句和相应的结果.在解析查询之前会询问查询缓存,如果查询缓存中能找到相应的结果就直接返回结果. 5.MySQL的 data 文件夹下会根据每个数据库

  • 关于mysql基础知识的介绍

    一.启动与退出1.进入MySQL:启动MySQL Command Line Client(MySQL的DOS界面),直接输入安装时的密码即可.此时的提示符是:mysql>或打开终端,输入SQL语句:mysql –uroot –p123 2.退出MySQL:quit或exit 二.库操作1.创建数据库命令:create database <数据库名>例如:建立一个名为xhkdb的数据库mysql> create database xhkdb; 2.显示所有的数据库命令:show da

  • python 专题九 Mysql数据库编程基础知识

    在Python网络爬虫中,通常是通过TXT纯文本方式存储,其实也是可以存储在数据库中的:同时在WAMP(Windows.Apache.MySQL.PHP或Python)开发网站中,也可以通过Python构建网页的,所以这篇文章主要讲述Python调用MySQL数据库相关编程知识.从以下几个方面进行讲解: 1.配置MySLQ 2.SQL语句基础知识 3.Python操作MySQL基础知识 4.Python调用MySQL示例 一. 配置MySQL 首先下载mysql-5.0.96-winx64,安装

随机推荐