Skill-building Courses Intro to SQL Lesson 2 More Functions in SQL
通配符 :LIKE SELECT * FROM Products WHERE PName LIKE %gizmo% PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi 特别提示 : 与 % 能匹配 0 个字符不一样, _ 总是匹配一个字符, 不能多也不能少 PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks
排序 :ORDER BY ( 永远写在最后, 除非有 limit 行数 ) PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $59.99 Gadgets GizmoWorks SingleTouch $59.99 Gadgets GizmoWorks MultiTouch $79.99 Gadgets GizmoWorks SELECT pname, price, manufacturer FROM Product WHERE category= Gadgets AND price > 50 ORDER BY price, pname Ties are broken by the second attribute on the ORDER BY list, etc. 特别提示 :Ordering is ascending( 升序 ), unless you specify the DESC keyword.
作业 : Product Table xirao11@gmail.com SELECT DISTINCT category FROM Product ORDER BY category PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi? SELECT Category FROM Product ORDER BY PName? SELECT DISTINCT category FROM Product ORDER BY PName?
练习 : 数据 : Company (cname, stockprice, country) Product (pname, price, category, manufacturer) 需要解决的问题 : Find all products under $200 manufactured in Japan; return their names and prices. SELECT PName, Price FROM Product, Company WHERE Country= Japan AND Price <200 Company CName StockPrice Country GizmoWorks 25 USA Canon 65 Japan Hitachi 15 Japan Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi 需要用到 Product 和 Company 两个表 PName Price SingleTouch $149.99
练习 : 数据 : Company Stock (cname, price, country) Product (pname, price, category, manufacturer) 需要解决的问题 : Find all products under $200 manufactured in Japan; return their names and prices. SELECT PName, Price FROM Product, Company WHERE Country= Japan AND Price <200 Company Stock CName Price Country GizmoWorks 25 USA Canon 65 Japan Hitachi 15 Japan Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi Which price?
重命名 AS Person(pname, address, worksfor) Company(cname, address) SELECT DISTINCT pname, address FROM Person, Company WHERE worksfor = cname Which address? SELECT DISTINCT Person.pname, Company.address FROM Person, Company WHERE Person.worksfor = Company.cname SELECT DISTINCT x.pname, y.address FROM Person AS x, Company AS y WHERE x.worksfor = y.cname Alias ( 别名 )
计算函数 聚集函数 用在 select 后面
聚集函数 ( 只有 5 个 ):Aggregation 特别提示 : 聚集函数都针对于某一列, 不包含 NULL 值 只有 count(*) 返回所有行数, 不管行中各列有什么值 SELECT 语句可以包含多个聚集函数
例子 : SELECT PName, Price, Quantity, Quantity*Price AS Revenue FROM Product, Sales Alias ( 别名 ) SELECT avg(price) FROM Product WHERE maker= Toyota SELECT avg(price),maker FROM Product WHERE maker= Toyota SELECT plate number, avg(price),maker FROM Product WHERE maker= Toyota SELECT count(*) FROM Product WHERE year > 1995 error
函数 :Calculation+Aggregation SELECT Sum(price * quantity) FROM Purchase SELECT Sum(price * quantity) FROM Purchase WHERE product = bagel Purchase(product, date, price, quantity) Product Date Price Quantity Bagel 10/21 1 20 Banana 10/3 0.5 10 Banana 10/10 1 10 Bagel 10/25 1.50 20 What do they mean?
聚集函数 :Aggregation Purchase Product Date Price Quantity Bagel 10/21 1 20 Banana 10/3 0.5 10 Banana 10/10 1 10 Bagel 10/25 1.50 20 SELECT Sum(price * quantity) FROM Purchase WHERE product = bagel 50 (= 20+30)
数据分组 :Group By 数据 :Purchase(product, date, price, quantity) Product Date Price Quantity Bagel 10/21 1 20 Banana 10/3 0.5 10 Banana 10/10 1 10 Bagel 10/25 1.50 20 需要解决的问题 :Find total sales after 10/1/ per product. 特别提醒 : 通常这样的问法都是强烈暗示需要用 group by;
数据分组 :Group By 特别提醒 : 除了聚集计算语句外,SELECT 语句中的每个列都必须在 GROUP BY 子句中给出 Product Date Price Quantity Bagel 10/21 1 20 Bagel 10/25 1.50 20 Banana 10/3 0.5 10 Banana 10/10 1 10 Product TotalSales Bagel 50 Banana 15 SELECT product, Sum(price*quantity) AS TotalSales FROM Purchase WHERE date > 10/1 GROUP BY product SELECT Sum(price*quantity) AS TotalSales FROM Purchase WHERE date > 10/1 TotalSales 65
Group by Brand Price (1000) Plate Number Col or Color Count Avg Toyota 10 123435636 R Toyota 20 357364585798 R Toyota 30 234534674 W R W 3 4 BMW 90 356785786 W BMW 80 34675786 W Inifinite 70 2345134536 W Jeep 50 34675968 R SELECT Color, Count(Brand) AS Count, Avg (price) AS AvgPrice FROM Car GROUP BY Color
小结 : 固定顺序 SELECT 我们想要最后看到的数据 FROM 数据来自的表 (* 包含要最后看到的数据, 和过滤涉及的数据 ) WHERE GROUP BY ORDER BY LIMIT 对数据进行一些条件过滤对数据进行分组对最后输出数据的排序要求对最后输出数据的行数要求
作业 : 观察 Chinook_Sqlite 中的 InvoiceLine 表格, 用 SQL 解决以下问题 : 1) 计算每一行 invoice line 的 salesvalue ( 提示 :UnitPrice*Quantity =salesvalue), 返回 invoicelineid, trackid, salesvalue 2) 假设刚刚计算出的 salesvalue 已经成为了 InvoiceLine 表中的一列, 计算每一个 track 的总销量 ( 提示 :group by), 返回 trackid, totalsales 3) 假设刚刚返回的 trackid 和 totalsales 储存为了一个新的表格 TrackSales, 请利用这个新表格显示销量最靠前的 5 个 track
作业 : If I got this table for the final result using left join 2 tables, then what are the 2 original tables most likely to be?