数据分析修炼手册之mysql篇

数据库基本操作

• 创建数据库
  CREATE database crashcourse;
• 创建表
  CREATE table customers;
• 删除表
  DROP table customers;
• 重命名表
  RENAME table customers TO customers2;

• 切换数据库
  use crashcourse;
• 查看数据库中的所有表
  show tables;
• 查看某张表所有的列名
  show columns from customers 或者 describe customers

简单数据查询

• 查询某张表的某一列数据
  SELECT prod_name FROM products;
• 查询某张表的多列数据
  SELECT prod_id,prod_name,prod_price FROM products;
• 查询某张表的所有数据
  SELECT * FROM products;
• DISTINCT关键字 查询出不重复的数据
  SELECT DISTINCT vend_id FROM products;
• limit关键字 限定查询出来的数据的数量
  select vend_id from products limit 5;
select vend_id from products limit 3,4;这个代表查询从第三行之后的4行
• 限定表名或数据库名
  select products.prod_name FROM crashcourse.products

数据排序

• 将查询出来的结果按价格排序，按多列进行排序的时候会先根据第一列排序，在第一类相同时才会根据第二列排序
  SELECT prod_name FROM products ORDER BY prod_price;
• 查询结果按降序排列，默认是升序
  select prod_name,prod_price from products order by prod_price desc;

数据过滤

• 基本数据过滤
  a. 常见的WHERE子句操作符

  操作符	说明
  =	等于
  <>	不等于
  !=	不等于
  <	小于
  <=	小于等于
  >	大于
  >=	大于等于
  BETWEEN	在指定的两个值之间
  is NULL	检查数据是否为空

  b. 对查询到的数据根据条件进行过滤
  SELECT * FROM products WHERE prod_price = 2.5
  c. and和or
  select prod_name,prod_price from products where prod_price <=10 and prod_id =1003;
select prod_name,prod_price from products where prod_price <=10 or prod_id =1002;
  and和or同时出现时，and的优先级高于or
  d. in
  select prod_name,prod_price,vend_id from products where vend_id in (1002,1003);
  e. not
  select prod_name,prod_price,vend_id from products where vend_id not in (1002,1003);

• 使用通配符过滤数据

通配符	说明
%	匹配任意个（包括0个）任意字符
_	匹配单个字符

• 使用正则表达式过滤数据

  通配符	说明
  .	匹配任意一个字符
  |	代表或的意思
  [123]	匹配括号中123其中某一个字符
  [^123 ]	匹配除了括号中的123以外的任意一个字符
  [a-z]	匹配任意一个字母字符
  \\.	如果要查找.这个符号的话需要转义字符
  *	0个或多个匹配
  +	1个或多个匹配（等于{1,}）
  ?	前面的任何字符的0次或1次出现
  {n}	指定数目的匹配
  {n,}	不少于指定数目的匹配
  {n,m}	匹配数目的范围（m不超过255）
  ^	文本的开始
  $	文本的结尾
  [[:<:]]	词的开始
  [[:>:]]	词的结尾

数据处理

• 拼接查询结果 concat
  select concat(vend_name,'(',vend_country,')') from vendors;

• 查询结果去空格 RTrim、LTrim
  select concat(vend_name,'(',RTrim(vend_country),')') from vendors;

• 查询结果起别名 AS
  select concat(vend_name,'(',vend_country,')')as vend_title from vendors;

• 加入数学运算
  select quantity,item_price,quantity * item_price as total_fee from orderitems;

• 对查询结果中的文本进行处理

  函数	作用
  Left()	返回串左边的字符
  Length()	返回串的长度
  Locate()	找出串的一个子串
  Lower()	将串转换为小写
  LTrim()	去掉串左边的空格
  Right()	返回串右边的字符
  RTrim()	去掉串右边的空格
  Soundex()	返回串的SOUNDEX值
  SubString()	返回子串的字符
  Upper()	将串转换为大写

• 对查询结果中的日期和时间进行处理

  函数	作用
  AddDate()	增加一个日期（天、周等）
  AddTime()	增加一个时间（时、分等）
  CurDate()	返回当前日期
  CurTime()	返回当前时间
  Date()	返回日期时间的日期部分
  DateDiff()	计算两个日期之差
  Date_Add()	高度灵活的日期运算函数
  Date_Format()	返回一个格式化的日期或时间串
  Day()	返回一个日期的天数部分
  DayOfWeek()	对于一个日期，返回对应的星期几
  Hour()	返回一个时间的小时部分
  Minute()	返回一个时间的分钟部分
  Month()	返回一个日期的月份部分
  Now()	返回当前日期和时间
  Second()	返回一个时间的秒部分
  Time()	返回一个日期时间的时间部分
  Year()	返回一个日期的年份部分

• 对查询结果中的数值进行处理

  函数	作用
  Abs()	返回一个数的绝对值
  Cos()	返回一个角度的余弦
  Exp()	返回一个数的指数值
  Mod()	返回除操作的余数
  Pi()	返回圆周率
  Rand()	返回一个随机数
  Sin()	返回一个角度的正弦
  Sqrt()	返回一个数的平方根
  Tan()	返回一个角度的正切

数据聚集与分组

• SQL聚集函数

  函数	作用
  AVG()	返回某列的平均值
  COUNT()	返回某列的行数
  MAX()	返回某列的最大值
  MIN()	返回某列的最小值
  SUM()	返回某列值之和

• 分组函数
  a. group by 进行分组
  GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。
  select vend_id,count(*) as num_prods from products group by vend_id with rollup;
  将数据按vend_id分组，rollup的作用是进行分组合计
  b. having 分组过滤
  select vend_id,count(*) as num_prods from products group by vend_id having num_prods > 2;

高级查询

• 嵌套查询
  select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2')

• 多表查询
  select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors where products.vend_id =vendors.vend_id and orderitems.prod_id =products,prod_id and order_num=20005

• 表连接的方式

  连接方式	含义
  内部连接INNER JOIN	选择两个表中关键字段相匹配的记录
  左外部连接LEFT OUTER JOIN	选择第一个表中的所有记录以及第二个表中与关键字段相匹配的记录
  右外部连接 RIGHT OUTER JOIN	选择录第一个表中与关键字段相匹配的记录以及第二个表中的所有记录

• 组合查询 UNION 和UNION all
  UNION 查询出来的数据会进行去重
  UNION all 查询出来的数据会不会进行去重

增删改操作

• 增加数据项
  a. 插入一行数据
  INSERT INTO customers(cust_name,cust_country) VALUES('chen','china');
  b. 将数据用逗号隔开，可以同时插入多行
  INSERT INTO custcustomers(cust_name,cust_country) VALUES('chen','china'),('bin','china');
  c. 插入查询到的数据
  INSERT INTO customers(cust_name,cust_country) SELECT cust_name,cust_country FROM cust_new;
• 更新数据项
  UPDATE customers SET cust_email='123@qq.com' WHERE cust_id=10005
• 删除数据项
  DELETE FROM customers WHERE cust_id =10006
  上面的语句是用来删除一整行的，删除指定列的数据需要使用UPDATE语句
  UPDATE customers SET cust_email=NULL WHERE cust_id=10005

视图与存储过程

• 视图
  a. 视图创建

  CREATE VIEW productCustomers  AS  
   SELECT cust_name,cust_contact,prod_id FROM  
  customers as a,orders as b,orderitems as c  
  WHERE a.cust_id = b.cust_id,b.order_num=c.order_num;
  

  b. 视图使用
  `SELECT * FROM productCustomers;

• 存储过程
  a. 创建存储过程

   CREATE PROCEDURE productpricing()
  BEGIN
     SELECT  AVG(PROD_PRICE) AS priceAvg FROM products;
  END;
  

  b. 使用存储过程
  CALL productpricing()
  c. 删除存储过程
  DROP PROCEDURE productpricing
  d. 查看创建存储过程语句
  SHOW CREATE PROCEDURE productpricing;