掌握数据产品经理必备技能:MySQL入门指南

发表时间: 2018-07-26 12:24

作为一枚数据产品经理,需要掌握基本的SQL查询语句技能,之后才能进一步了解与搭建数据仓库、元数据、指标字典体系。本文首先介绍MySQL基本知识。

一、了解数据库

  1. 模式:关于数据库和表的布局及特性的信息;
  2. 行是表中的记录,列是字段;
  3. 主键列中的值不能修改,且不能重用;
  4. 数据库中包含表及其它相关sql结构。

二、检索数据

2.1 select语句

检索所有列:select * from products

2.2 排序检索数据

order by:select prod_name from products order by prod_name。

注意:order by的子句必须是select语句的最后一个子句。

按多个列排序:select prod_id,prod_name,prod_price from products order by prod_price,prod_name。那么输出结果先按prod_price排序,然后再将排序后的结果按prod_name排序。

按列位置排序:select prod_id,prod_name,prod_price from products order by 2,3。语句与上一句结果相同。

降序排序(系统默认升序):order by prod_price DSC,prod_name。则结果先按prod_price降序排序,再按price_name升序排序。

三、过滤数据

(1)使用where子句(在from之后)

  • 范围值检查:between and:where prod_price between 5 and 10。
  • 空值检查:where prod_price is NULL。语句返回没有价格的所有产品(空字段,不是价格为0)

(2)使用组合where子句

  • AND操作符:where vend AND prod_price<=4 #检索由供应商DLL01制造且产品价格低于4美元的所有产品注意AND用在where子句中的关键字,用来指示检索满足所给定条件的行。
  • OR操作符:where OR OR用在where子句中的关键字,用来表示检索匹配任一给定条件的行。
  • AND,OR相结合(AND优先级比OR高):所以注意使用圆括号,where (vend or vend) and prod_price>=10。
  • IN操作符:where vend_id in(‘DLL01′,’BRS01’),其功能与where or相当。
  • NOT:否定跟在它之后的任何条件(mysql不支持此操作符), where NOT vend #检索除DLL01之外所有供应商的产品(此句等价于where vend_id <> ‘DLL01’) #NOT一定要紧跟在where后。

(3)用通配符进行过滤(通配符:用来匹配值的一部分的特殊字符,用于“字符串”),使用LIKE+通配符

  • 百分号(%):where prod_name like ‘Fish%’ #检索任意以Fish起头的词(匹配单个或多个字符)。
  • 下划线(_ ) :where prod_name like ‘Fish__’ #检索出的字符必须为Fish+两个字符 (与%能匹配0个字符不一样,_必须匹配一个字符,不能多也不能少)。
  • 方括号([]):where contact like [JM]% #[JM]匹配任何以J,M开头的人名,但它也只能匹配单个字符,为此在后加%,则可匹配多个字符。(若改为[^JM],则为检索不以J,M起头的任意联系人名,等价于:where NOT contact like [JM]%)。

四、创建计算字段

(1)拼接字段(将值联结到一起构成单个值)

  • concat函数(mysql用):select concat(vend_id,’ (‘,vend_country,’)’) #select语句联结以上4个元素。
  • ‘+’号(Access,SQL Server,Sybase使用):select vend_id + ‘ (‘ + vend_country + ‘)’。
  • ‘||’号(DB2,Oracle使用):select vend_id || ‘ (‘ || vend_country || ‘)’。
  • TRIM函数:RTRIM去掉值右边所有空格,LTRIM去掉值左边所有空格,TRIM去掉值两边所有空格。
  • AS关键字:给联结字段取别名。
  • 完整联结字段语句:select RTRIM(vend_id) + ‘ (‘ + TRIRM(vend_country) + ‘)’ AS vendor_title FROM Vendors ORDER BY vend_name。

(2)执行算数计算

五、使用视图

视图是虚拟的表,视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的,在更改这些表中的数据时,视图将返回改变过后的数据。

(1)视图的规则和限制

  • 视图可以嵌套;
  • 禁止在视图查询时使用ORDER BY语句。

(2)创建视图(creat view)

  • 利用视图简化复杂的联结;
  • 用视图重新格式化检索出的数据;
  • 用视图过滤不想要的数据(where子句);
  • 使用视图与计算字段。

六、使用存储过程

为以后的使用而存储一条或多条SQL语句的集合。

七、管理事务处理

(1)什么是事务处理

事务管理用来管理必须成批执行的SQL操作,以保证数据库不包含不完整的操作结果。利用事务处理,可以保证一组数据不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示)。

如果没有错误发生,则整组语句写到数据库表,如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。

(2)几个术语

  • 事务(transction):一组SQL语句;
  • 回退(rollback):指撤销指定SQL语句的过程;
  • 提交(commit):指将未存储的SQL语句结果写入数据库表;
  • 保留点(savepoint):指事务处理中设置的临时占位符,你可以对它发布回退(与回退整个事务处理不同)。

(3)可以回退哪些语句

可管理INSERT,UPDATE,DELETE语句,不能回退SELECT语句也不能回退CREATE,DROP操作。事务处理中可以使用这些语句,但回退时它们不被撤销。

(4)控制事务处理

创建占位符:

  1. mysql:savepoint delete1;
  2. sql server:save trasaction delete1; #delete1为此占位符的名字。

rollback:#撤销整个事务处理

  1. rollback to delete1;
  2. rollback transaction delete1;#回退到保留点delete1;

八、使用游标(cursor)

游标不是一条select语句,而是被该语句检索出来的结果集,在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或更改。

  • 声明:declare
  • 打开:open cursor
  • 检索:fetch… into…
  • 关闭:close

九、了解高级SQL特性

约束:管理如何插入或处理数据库数据的规则

主键:一种特殊约束

外键:可帮助防止意外删除,唯一约束:与主键类似但区别于主键

本文由 @两人一杯奶茶 原创发布于人人都是产品经理。未经许可,禁止转载

题图来自Unsplash,基于CC0协议