合肥专业做网站的公司哪家好,如何制作网站效果图,百度推广对网站的好处,怎么注册网站域名本文主要梳理了 SQL 的基础用法#xff0c;会涉及到以下方面内容#xff1a;SQL大小写的规范数据库的类型以及适用场景SELECT 的执行过程WHERE 使用规范MySQL 中常见函数子查询分类如何选择合适的 EXISTS 和 IN子查询了解 SQLSQL 是我们用来和数据打交道的方式之一#xff0…本文主要梳理了 SQL 的基础用法会涉及到以下方面内容SQL大小写的规范数据库的类型以及适用场景SELECT 的执行过程WHERE 使用规范MySQL 中常见函数子查询分类如何选择合适的 EXISTS 和 IN子查询了解 SQLSQL 是我们用来和数据打交道的方式之一如果按照功能划分可分为如下 4 个部分DDL数据定义语言。定义数据库对象数据表数据列。也就是对数据库和表结构进行增删改操作。DML数据操作语言。对数据表的增删改。DCL数据控制语言。定义访问权限和安全级别。DQL数据查询语言。用来查询数据。平时在编写 SQL 时可能发现许多 SQL 大小写不统一虽然不会影响 SQL 的执行结果但保持统一的书写规范是提高效率的关键通常遵循如下的原则表名表别名字段名字段别名等用小写。SQL 保留字函数名绑定变量等用大写。数据表字段名采用下划线命名。目前排名较前的 DBMS关系型数据库建立在关系模型上的数据库在建表时通常先设计 ER 图表示之间的关系。键值型数据库以 key-value 的形式存储数据优点是查找速度快缺点是无法像关系型数据库一样使用如 WHERE 等的过滤条件。常见场景是作为内容缓存。文档型数据库在保存时以文档作为处理信息的基本单位。搜索引擎针对全文检索而设计。核心原理是 “倒排索引”。列式数据库相对于如 MySQL 等行式存储的数据库是以列将数据存在数据库中由于列具有相同的数据类型所以可以更好的压缩从而减低系统的 I/O适用于分布式文件系统但功能相对有限。图形数据库利用图的数据结构存储实体之间的关系。比如社交网络中人与人的关系数据模型为节点和边来实现。认识SELECTSELECT一般是在学习SQL接触的第一个关键字基础的内容就不提了这里整理常用的规范起别名SELECTnameAS n FROM student查询常数, 增加一列固定的常数列SELECT学生信息as student_info, nameFROM student去重重复行SELECTDISTINCT age FROM student需要注意的是DISTINCT是对后面的所有列进行去重下面这种情况就会对age和name的组合进行去重。SELECTDISTINCT age,nameFROM student排序数据ASC代表升序DESC代表降序如先按照name排序name相等的情况下按照age排序。SELECTDISTINCT age FROM student ORDERY BYname,age DESC限制返回的数量SELECTDISTINCT age FROM student ORDERY BYnameDESCLIMIT5SELECT 的执行顺序了解了SELECT的执行顺序才能更好地写出更有效率的 SQL。对于SELECT顺序有两个原则关键字的顺序不能颠倒SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...SELECT会按照如下顺序执行FROM WHERE GROUP BY HAVING SELECT的字段 DISTINCT ORDER BY LIMITSELECTDISTINCT student_id, name, count(*) asnum#顺序5FROM student JOIN class ON student.class_id class.class_id #顺序1WHERE age 18 #顺序2GROUP BY student.class_id #顺序3HAVING num 2 #顺序4ORDER BY num DESC #顺序6LIMIT 2 #顺序7在逐一分析下这个过程前我们需要知道在上面的每一个步骤中都会产生一个虚拟表然后将这个虚拟表作为下一个步骤中作为输入但这一过程对我们来说是不可见的从FROM语句开始对student和class表进行CROSS JOIN 笛卡尔积运算得到虚拟表 vt 1-1通过 ON 筛选在 vt1-1 的基础上进行过滤然后得到表 vt 1-2添加外部行。如使用左连接右连接和全连接时就会涉及到外部行会在 vt1-2 的基础上增加外部行得到 vt1-3。如果超过两张表就会重复上面的步骤。在拿到最终的 vt1 的表数据后会执行 WHERE 后面的过滤阶段得到表 vt2.接着到 GROUP 阶段进行分组得到 vt3.接着到 HAVING 阶段对分组的数据进行过滤得到 vt4.后面进入 SELECT 阶段提取需要的字段得到 vt5-1接着通过 DISTINCT 阶段过滤到重复的行得到 vt5-2.然后对指定的字段进行排序进入 ORDER BY 阶段得到 vt6.最后在 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 个如何进行分组在统计结果时往往需要对数据按照一定条件进行分组对应就是GROUP BY语句。比如统计每个班级的学生人数SELECT class_id, COUNT(*) as student_count FROM student \GROUPBY class_id;GROUP BY 后也可接多个列名进行分组比如按照班级和性别分组SELECT class_id, sex, COUNT(*) as student_count FROM \student GROUPBY class_id, sex;HAVING 过滤和 WHERE 的区别和 WHERE 一样可以对分组后的数据进行筛选。区别在于 WHERE 适用于数据行HAVING 用于分组。而且 WHERE 支持的操作HAVING 也同样支持。比如可以筛选大于2人的班级SELECT class_id, COUNT(*) as student_count FROM student \ GROUPBY class_id \ HAVING student_count 20;子查询在一些更为复杂的情况中往往会进行嵌套的查询比如在获取结果后该结果作为输入去获取另外一组结果。在 SQL 中查询可以分为关联子查询和非关联子查询。假设有如下的表结构-- ----------------------------DROP TABLE IF EXISTS student;CREATE TABLE student (idint(11) NOT NULL AUTO_INCREMENT,name varchar(20) NOT NULL DEFAULT ,ageint(3) NOT NULL,sex varchar(10) NOT NULL DEFAULT ,class_idint(11) NOT NULL COMMENT 班级ID, PRIMARY KEY (id)) ENGINEInnoDB AUTO_INCREMENT9 DEFAULT CHARSETutf8;-- ------------------------------ 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 (idint(11) NOT NULL AUTO_INCREMENT,name varchar(20) NOT NULL DEFAULT ,stu_idint(11) NOT NULL COMMENT 班级ID, PRIMARY KEY (id)) ENGINEInnoDB AUTO_INCREMENT9 DEFAULT CHARSETutf8;INSERT INTO student_activities VALUES (1, 博物馆, 1);INSERT INTO student_activities VALUES (3, 春游, 3);非关联子查询子查询从数据表中查询了数据结果如果这个数据结果只执行一次然后这个数据结果作为主查询的条件接着执行。这里想要查询和胡一相同班级的同学名称SELECTnameFROM student WHERE class_id \(SELECT class_id FROM student WHEREname胡一)这里先查到胡一的班级只有一次查询再根据该班级查找学生就是非关联子查询。关联子查询如果子查询需要执行多次即采用循环的方式先从外部查询开始每次都传入子查询进行查询然后再将结果反馈给外部再举个例子, 比如查询比每个班级中比平均年龄大的学生姓名信息SELECTnameFROM student as s1 WHERE age (SELECTAVG(age) FROM student as s2 where s1.class_id s2.class_id)这里根据每名同学的班级信息查找出对应班级的平均年龄然后做判断。子查询每次执行时都需要根据外部的查询然后进行计算。这样的子查询就是关联子查询。EXISTS子查询在关联子查询中常会和EXISTS 一起使用。用来判断条件是否满足满足的话为True不满足为 False。比如查询参加过学校活动的学生名称SELECTNAMEFROM student as s where \EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_ids.id)同样NOT EXISTS就是不存在的意思满足为FALSE , 不满足为True.比如查询没有参加过学校活动的学生名称SELECTNAMEFROM student as s where \NOTEXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_ids.id)集合比较子查询可以在子查询中使用集合操作符来比较结果。还是上面查询参加学校活动的学生名字的子查询, 同样可以使用 INSELECTnameFROM student WHEREidIN (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.ccA.cc)对于 EXISTS 来说会先对外表进行逐条循环每次拿到外表的结果后带入子查询的内表中去判断该值是否存在。伪代码类似于下面for i in Afor j in Bif 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 Bfor j in Aif 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 NOTIN (SELECT cc FROM B) SELECT * FROM A WHERENOT EXIST (SELECT cc FROM B WHERE B.ccA.cc)对于 NOT EXITS 来说和 EXISTS 一样对于内表可以使用 cc 的索引。适用于 A 表小于 B 表的情况。但对于 NOT IN 来说和 IN 就有区别了由于 cc 设置了索引 cc IN (1, 2, 3) 可以转换成 WHERE cc1 OR cc2 OR cc3 , 是可以正常走 cc 索引的。但对于 NOT IN 也就是转化为 cc!1 OR cc!2 OR cc!3 这时由于是不等号查询是无法走索引的进而全表扫描。也就是说在设置索引的情况下 NOT EXISTS 比 NOT IN 的效率高。但对于没有索引的情况IN 和 OR 是不同的一、操作不同1、inin是把父查询表和子查询表作hash连接。2、oror是对父查询表作loop循环每次loop循环再对子查询表进行查询。二、适用场景不同1、inin适合用于子查询表数据比父查询表数据多的情况。2、oror适合用于子查询表数据比父查询表数据少的情况。三、效率不同1、in在没有索引的情况下随着in后面的数据量越多in的执行效率不会有太大的下降。2、or在没有索引的情况下随着or后面的数据量越多or的执行效率会有明显的下降。总结这篇文章中主要归纳了一些 SQL 的基础知识在使用 SELECT 查询时通过显式指定列名来减少 IO 的传输从而提高效率。并且需要注意 SELECT 的查询过程会从 FROM 后开始到 LIMIT 结束理解了整体的流程可以让我们更好的组织 SQL.之后详细介绍了 WHERE 进行过滤的操作符和常用的函数这里要注意在比较时间时要使用 DATE 函数以及如何对数据进行分组和过滤。最后着重介绍了子查询IN 和 EXISTS 的适用场景。
