淮北市建设局网站,微营销,五金机械东莞网站建设,网站响应式好吗SQL(Structured Query Language 结构化查询语言 )是一种特殊目的的编程语言#xff0c;是一种数据库查询和程序设计语言#xff0c;用于存取数据以及查询、更新和管理关系数据库系统。SQL 是一种 ANSI(American National Standards Institute 美国国家标准化组织)标准的计算机… SQL(Structured Query Language 结构化查询语言 )是一种特殊目的的编程语言是一种数据库查询和程序设计语言用于存取数据以及查询、更新和管理关系数据库系统。SQL 是一种 ANSI(American National Standards Institute 美国国家标准化组织)标准的计算机语言,是一种第四代语言(4GL)。 SQL包含6个部分1、数据查询语言(DQL:Data Query Language) 2、数据操作语言(DMLData Manipulation Language)3、事务控制语言(TCL) 4、数据控制语言(DCL)5、数据定义语言(DDL)6、指针控制语言(CCL) 在DDL或者DML中不同的操作都有对应的语句如CREATE、ALTER、DROP、INSERT、DELETE而在DQL中只有一条语句即SELECT语句SELECT也算是DQL的标志。在计算机或者非计算机岗位中SQL语言中的DQL被广泛应用。 SQL语言是诸多计算机语言中相对来说比较容易学的语言简单高效易上手很适合青少年及对计算机编程语言感兴趣的人学习。在SQL语言的应用过程中对于数据库的设计、操作、查询等掌握可以帮助参与者提高计算机编程能力和设计能力。可以想象当你拥有一些重要的数据时如果学会了数据统计和数据处理将是一件非常有益的事情或者去处理一些常规的数据有可能得到一些意外的惊喜。数据是一种特殊的资源如何利用这种资源非常值得去深思。 在21世纪的今天计算机行业蓬勃发展掌握一门计算机语言显得非常的必要。而SQL非常值得爱好者拥有首先就是它的实用性很强当学会SQL之后就会将数据库的数据牢牢的攥在手里任你想怎么查询就怎么查询而SQl与其他编程语言结合使用的时候SQL也是非常重要的组成部分。当SQL熟练时已经可以掌控数据至于数据结构的整理和数据渲染可以由其他编程语言来完成。 本人曾经利用SQL统计人口首先整理了自建国以来至今(1949~2019)每年的出生人口(网上查的数据)然后做了一些简单的统计其中包括总人口、劳动力人口、适龄结婚人口、各个年代总人口、各个年代平均每年出生人口、各个年代与上个年代每年出生人口差、未来几十年的劳动力人口变化、未来几十年适龄结婚人口变化以及各个指标的同比和环比增幅最终会预判出未来很多行业的整体趋势和社会劳动力的整体趋势。以此举例如果有了更好的数据资源利用数据库以及SQL将其整合与分析将会受益匪浅。 SQL分为6部分本文着重MySQL数据库DQL的场景应用实例可以为初学者提供适合场景设计的查询语句通过分析和学习积累更多的查询语句经验来应对以后的实际应用场景。DQL语句编写简单易懂难点在于怎样通过分析与设计将需求转化为结构化查询语句实现数据查询。 在MySQL数据库SQL查询语句实际应用之前需要了解四点1、SELECT语句结构 2、SELECT语句执行顺序 3、索引 4、视图。 1、SELECT语句结构熟练的掌握SELECT语句结构是应用的前提所有的查询语句必须按照固定结构搭配才能正常执行如果颠倒语句结构会出现语句报错以下语句来自mysql官网“MySQL 8.0参考手册”https://dev.mysql.com/doc/refman/8.0/en/select.html SQL函数和运算符参考https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html 表达式评估中的类型转换: https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr] ... [into_option] [FROM table_references [PARTITION partition_list]] [WHERE where_condition] [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]] [HAVING where_condition] [WINDOW window_name AS (window_spec) [, window_name AS (window_spec)] ...] [ORDER BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [LIMIT {[offset,] row_count | row_count OFFSET offset}] 2、SELECT语句执行顺序SELECT语句在运行时有执行顺序需要知道语句执行顺序在有些场景下才会获取正确的预期结果。 参考地址https://www.cnblogs.com/rollenholt/p/3776923.html 执行顺序如下 1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1 2. ON: 对虚表VT1进行ON筛选只有那些符合的行才会被记录在虚表VT2中。 3. JOIN如果指定了OUTER JOIN(比如left join、 right join)那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中产生虚拟表VT3, rug from子句中包含两个以上的表的话那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤一直到处理完所有的表为止。 4. WHERE对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。 5. GROUP BY: 根据group by子句中的列对VT4中的记录进行分组操作产生VT5. 6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作产生表VT6. 7. HAVING对虚拟表VT6应用having过滤只有符合的记录才会被 插入到虚拟表VT7中。 8. SELECT执行select操作选择指定的列插入到虚拟表VT8中。 9. DISTINCT对VT8中的记录进行去重。产生虚拟表VT9. 10. ORDER BY: 将虚拟表VT9中的记录按照进行排序操作产生虚拟表VT10. 11. LIMIT取出指定行的记录产生虚拟表VT11, 并将结果返回。 3、索引索引的建立对于MySQL数据库的DQL语句的高效运行是很重要的索引可以大大提高数据的检索速度。 4、视图在 SQL 中视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句。在有些业务当中表结构与数据相对比较隐私或者复杂对业务了解不够细致的人员在获取某些业务信息时会比较麻烦这时就可以创建一个视图。视图封装了业务数据的获取过程直接将数据结果集展示出来有利于其他非业务人员快速方便的查询业务信息。 在了解查询语句语法结构及规则的同时需要掌握更多的语句结构样例更加深刻的理解语句结构的变化和语句结构所表达的查询内容。积累更多的语法结构模型才会有更加灵活的语句组织方式和更加深刻的认识。工欲善其事必先利其器。 接下来列举应用场景分析具体的应用实例。如下是不同的语句结构实现不同场景的需求 场景1: 测试select查询语句 分析组织含“select”关键字的语句查看是否运行正常。 语句SELECT 1; 或者 SELECT 1 FROM DUAL; 场景2测试select语句嵌套结构 分析使用select查询语句的嵌套查看是否运行正常。 语句SELECT (SELECT (SELECT 2*3)); 场景3获取mysql数据库用户信息 分析查询mysql数据库获取用户信息。 语句SELECT * FROM mysql.user LIMIT 0, 1000; 场景4获取员工及部门信息 分析查询员工表附带员工部门信息。 语句SELECT se.name, sd.name FROM sys_employees se LEFT JOIN sys_dept sd ON se.dept_id sd.id 场景5获取有部门并且可用的用户及部门信息 分析查询用户表与部门表的交集数据过滤可用的用户信息。 语句 SELECT se.name, sd.name FROM sys_employees se INNER JOIN sys_dept sd ON se.dept_id sd.id AND se.is_used 1 易混淆的语句在inner joinonand语句结构中join两边的表在取得交集之后还可以继续筛选条件最终得到筛选后的信息。然而在left joinonand语句结构中左表没有进行任何筛选所有数据的关联结果以左表为主所有的onand都不会过滤左表任何数据但是右表数据会条件筛选而受到影响。如果需要用left join完成语句查询需要添加 where sd.name IS NOT NULL and se.is_used 1 如图 场景6获取有效员工当前部门和上一级部门的信息 分析查询当前有效员工关联用户部门和上一级部门主信息为员工信息联表部门信息。基于对场景5的分析可知left join的onand作用除了关联外键信息的其他筛选条件需要放在where后。 语句 SELECT se.name AS NAME, sd.name AS deptName, sdp.name AS deptParentName FROM sys_employees se LEFT JOIN sys_dept sd ON se.dept_id sd.id LEFT JOIN sys_dept sdp ON sd.pid sdp.id WHERE se.is_used 1 场景7获取某用户最近7天每天的消费次数统计用户最近一周消费习惯和活跃度。 分析查询某用户最近7天消费记录获取每条数据的日期 %Y-%m-%d格式信息然后对日期格式信息进行分组聚合。 语句 SELECT dateData.create_date, COUNT(dateData.create_date) FROM (SELECT DATE_FORMAT(scr.create_time, %Y-%m-%d) AS create_date FROM sys_user_consumption_records scr WHERE DATE_FORMAT(scr.create_time, %Y-%m-%d) BETWEEN DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 6 DAY), %Y-%m-%d ) AND DATE_FORMAT(NOW(), %Y-%m-%d) AND user_id 1) dateData GROUP BY dateData.create_date; 场景8获取某用户最近100次消费的平均金额用户消费能力评级。 分析获取用户最近100次消费记录通过消费记录进行聚合获取平均消费金额 语句 SELECT AVG(priceData.price) FROM (SELECT price FROM sys_user_consumption_records WHERE user_id 1 ORDER BY create_time DESC LIMIT 0, 100) priceData 易混淆的语句在进行数据区间的局部信息计算时需要进行筛选(where)、排序(order by)、截取(limit)操作在这些操作之后所获得的数据列表才是需要进行计算的有效数据。然而SQL查询语句规定了关键字的执行顺序”SELECT”关键字优先于”DISTINCT”、”ORDER BY”、“LIMIT”,因此在语句执行的时候SELECT聚合函数执行完成之后”ORDER BY”、“LIMIT”才进行筛选。因此写语句的初学者很容易忽视SQL的执行顺序从而导致错误的语句和错误的结果。如图 场景9获取每一位用户最近一次消费记录设置用户活跃等级。 分析以用户ID分组获取每位用户最大时间的数据联表查询用户消费记录数据。 语句 SELECT sucri.* FROM sys_user_consumption_records sucri INNER JOIN (SELECT sucr.user_id, MAX(sucr.create_time) AS create_time FROM sys_user_consumption_records sucr GROUP BY sucr.user_id) userNewRecord ON sucri.user_id userNewRecord.user_id AND sucri.create_time userNewRecord.create_time 场景10获取每位用户的最高消费记录信息 分析以用户ID分组获取每位用户数据最高消费的记录(最高消费记录可能有多条取最新一条)联表查询用户消费记录数据。 语句 SELECT sucri.* FROM sys_user_consumption_records sucri INNER JOIN (SELECT sucr.user_id AS uuserId, (SELECT sucrchild.id FROM sys_user_consumption_records sucrchild WHERE sucrchild.user_id uuserId ORDER BY sucrchild.price DESC, sucrchild.create_time DESC LIMIT 0, 1) AS sucrchildId FROM sys_user_consumption_records sucr GROUP BY sucr.user_id) userNewRecord ON sucri.id userNewRecord.sucrchildId 易混淆的语句在以user_id分组的select中获取最高消费记录子句需要引用”uuserId”值进行当前用户数据的筛选。在这个子句当中所有的left join或者inner join的onand条件筛选中都不能出现”uuserId”值筛选条件否则会报错。”uuserId”值只能出现在子句的whereand筛选条件中 以上是列举的10个应用场景举例了不同场景需求的实现方式。还有更多的语句查询细节需要在应用过程中仔细学习其含义并且熟悉应用场景。不同场景应用到的语句各不相同 比如“IFNULL(table.num,table.num,0)”将为null的值默认转为0比如“order by serial 0”将字符串的数值转为数字进行排序等等。不同的场景和需求需要不同的结构语句和函数根据实际情况设计出合理的语句来实现业务。在开发中不仅要解决实际问题更加需要不断的积累不同的结构语句模型来给新的需求提供借鉴的思路。
