山东省建设管理局网站,嵌入式软件开发流程图,营销策划公司经营范围包括哪些,seo建站优化推广-天池龙珠计划SQL训练营
Task02#xff1a;SQL基础查询与排序
SQL训练营页面地址#xff1a;https://tianchi.aliyun.com/specials/promotion/aicampsql
一、SELECT语句基础
1.1 从表中选取数据
SELECT语句 从表中选取数据时需要使用SELECT语句#xff0c;也就是只从表…-天池龙珠计划SQL训练营
Task02SQL基础查询与排序
SQL训练营页面地址https://tianchi.aliyun.com/specials/promotion/aicampsql
一、SELECT语句基础
1.1 从表中选取数据
SELECT语句 从表中选取数据时需要使用SELECT语句也就是只从表中选出SELECT必要数据的意思。通过SELECT语句查询并选取出必要数据的过程称为匹配查询或查询query。 基本SELECT语句包含了SELECT和FROM两个子句clause。示例如下
SELECT 列名,FROM 表名;其中SELECT子句中列举了希望从表中查询出的列的名称而FROM子句则指定了选取出数据的表的名称。
1.2从表中选取符合条件的数据
WHERE语句 当不需要取出全部数据而是选取出满足“商品种类为衣服”“销售单价在1000日元以上”等某些条件的数据时使用WHERE语句。 SELECT 语句通过WHERE子句来指定查询数据的条件。在WHERE 子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件。执行含有这些条件的SELECT语句就可以查询出只符合该条件的记录了。
SELECT 列名, ……FROM 表名WHERE 条件表达式;比较下面两者输出结果的不同
-- 用来选取product type列为衣服’的记录的SELECT语句
SELECT product_name, product_typeFROM productWHERE product_type 衣服;
-- 也可以选取出不是查询条件的列条件列与输出列不同
SELECT product_nameFROM productWHERE product_type 衣服;1.3 相关法则
星号*代表全部列的意思。SQL中可以随意使用换行符不影响语句执行但不可插入空行。设定汉语别名时需要使用双引号括起来。在SELECT语句中使用DISTINCT可以删除重复行。注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释-- “和多行注释两种”/* */。
-- 想要查询出全部列时可以使用代表所有列的星号*。
SELECT *FROM 表名;
-- SQL语句可以使用AS关键字为列设定别名用中文时需要双引号“”。
SELECT product_id AS id,product_name AS name,purchase_price AS 进货单价FROM product;
-- 使用DISTINCT删除product_type列中重复的数据
SELECT DISTINCT product_typeFROM product;二、算术运算符和比较运算符
2.1 算术运算符
SQL语句中可以使用的四则运算的主要运算符如下
含义运算符加法减法-乘法*除法/
2.2 比较运算符
-- 选取出sale_price列为500的记录
SELECT product_namep, roduct_typeFROM productWHERE sale_price 500;SQL常见比较运算符如下
运算符含义和~相等和~不相等大于等于~大于~小于等于~小于~
2.3 常用法则
SELECT子句中可以使用常数或者表达式。使用比较运算符时一定要注意不等号和等号的位置。字符串类型的数据原则上按照字典顺序进行排序不能与数字的大小顺序混淆。希望选取NULL记录时需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时需要在条件表达式中使用IS NOT NULL运算符。
相关代码如下
-- SQL语句中也可以使用运算表达式
SELECT product_name, sale_price, sale_price * 2 AS sale_price x2FROM product;
-- WHERE子句的条件表达式中也可以使用计算表达式
SELECT product_name, sale_price, purchase_priceFROM productWHERE sale_price-purchase_price 500;
/* 对字符串使用不等号
首先创建chars并插入数据
选取出大于‘2’的SELECT语句*/
-- DDL创建表
CREATE TABLE chars
chr CHAR3NOT NULL
PRIMARY KEYchr;
-- 选取出大于2的数据的SELECT语句(2为字符串)
SELECT chrFROM charsWHERE chr 2;
-- 选取NULL的记录
SELECT product_namepurchase_priceFROM productWHERE purchase_price IS NULL;
-- 选取不为NULL的记录
SELECT product_namepurchase_priceFROM productWHERE purchase_price IS NOT NULL;三、逻辑运算符
3.1 NOT运算符
想要表示“不是……”时除了前文的运算符外还存在另外一个表示否定、使用范围更广的运算符NOT。 NOT不能单独使用如下例
-- 选取出销售单价大于等于1000日元的记录
SELECT product_name, product_type, sale_priceFROM productWHERE sale_price 1000;-- 向代码清单2-30的查询条件中添加NOT运算符
SELECT product_name, product_type, sale_priceFROM productWHERE NOT sale_price 1000;3.2 AND运算符和OR运算符
当希望同时使用多个查询条件时可以使用AND或者OR运算符。 AND 相当于“并且”类似数学中的取交集 OR 相当于“或者”类似数学中的取并集。 如下图所示 AND运算符工作效果图 OR运算符工作效果图
3.3 通过括号优先处理
如果要查找这样一个商品该怎么处理 “商品种类为办公用品”并且“登记日期是 2009 年 9 月 11 日或者 2009 年 9 月 20 日” 理想结果为“打孔器”但当你输入以下信息时会得到错误结果
-- 将查询条件原封不动地写入条件表达式会得到错误结果
SELECT product_name, product_type, regist_dateFROM productWHERE product_type 办公用品AND regist_date 2009-09-11OR regist_date 2009-09-20;错误的原因是是 AND 运算符优先于 OR 运算符想要优先执行OR运算可以使用括号
-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name, product_type, regist_dateFROM productWHERE product_type 办公用品AND ( regist_date 2009-09-11OR regist_date 2009-09-20);3.4 真值表
复杂运算时该怎样理解 当碰到条件较复杂的语句时理解语句含义并不容易这时可以采用真值表来梳理逻辑关系。 什么是真值 本节介绍的三个运算符 NOT、AND 和 OR 称为逻辑运算符。这里所说的逻辑就是对真值进行操作的意思。真值就是值为真TRUE或假 FALSE其中之一的值。 例如对于 sale_price 3000 这个查询条件来说由于 product_name 列为 ‘运动 T 恤’ 的记录的 sale_price 列的值是 2800因此会返回假FALSE而 product_name 列为 ‘高压锅’ 的记录的sale_price 列的值是 5000所以返回真TRUE。 AND 运算符**两侧的真值都为真时返回真除此之外都返回假。 OR 运算符两侧的真值只要有一个不为假就返回真只有当其两侧的真值都为假时才返回假。 NOT运算符**只是单纯的将真转换为假将假转换为真。 真值表 查询条件为P ANDQ OR R的真值表
3.5 含有NULL时的真值
NULL的真值结果既不为真也不为假因为并不知道这样一个值。 那该如何表示呢 这时真值是除真假之外的第三种值——不确定UNKNOWN。一般的逻辑运算并不存在这第三种值。SQL 之外的语言也基本上只使用真和假这两种真值。与通常的逻辑运算被称为二值逻辑相对只有 SQL 中的逻辑运算被称为三值逻辑。 三值逻辑下的AND和OR真值表为
练习题-第一部分
练习题1
编写一条SQL语句从product商品表中选取出“登记日期regist在2009年4月28日之后”的商品查询结果要包含product_name和regist_date两列。
SELECT product_name,regist_date
FROM product
WHERE regist_date2009-04-28练习题2
请说出对product 表执行如下3条SELECT语句时的返回结果。 ①
SELECT *FROM productWHERE purchase_price NULL;②
SELECT *FROM productWHERE purchase_price NULL;③
SELECT *FROM productWHERE product_name NULL;以上三条语句有问题
练习题3
代码清单2-222-2节中的SELECT语句能够从product表中取出“销售单价saleprice比进货单价purchase price高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示。
product_name | sale_price | purchase_price
-------------------------------------
T恤衫 | 1000 | 500
运动T恤 | 4000 | 2800
高压锅 | 6800 | 5000SELECT product_name,sale_price,purchase_price
FROM product
WHERE sale_pricepurchase_price500;练习题4
请写出一条SELECT语句从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润别名设定为profit。 提示销售单价打九折可以通过saleprice列的值乘以0.9获得利润可以通过该值减去purchase_price列的值获得。
SELECT product_name,product_type,(sale_price*0.9-purchase_price) AS profit
FROM product
WHERE sale_price*0.9-purchase_price100AND (product_type办公用品 OR product_type厨房用品);四、对表进行聚合查询
4.1 聚合函数
SQL中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数
COUNT计算表中的记录数行数SUM计算表中数值列中数据的合计值AVG计算表中数值列中数据的平均值MAX求出表中任意列中数据的最大值MIN求出表中任意列中数据的最小值
请沿用第一章的数据使用以下操作熟练函数
-- 计算全部数据的行数包含NULL
SELECT COUNT(*)FROM product;
-- 计算NULL以外数据的行数
SELECT COUNT(purchase_price)FROM product;
-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)FROM product;4.2 使用聚合函数删除重复值
-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)FROM product;-- 是否使用DISTINCT时的动作差异SUM函数
SELECT SUM(sale_price), SUM(DISTINCT sale_price)FROM product;4.3 常用法则
COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数而COUNT(列名)会得到NULL之外的数据行数。聚合函数会将NULL排除在外。但COUNT(*)例外并不会排除NULL。MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。想要计算值的种类时可以在COUNT函数的参数中使用DISTINCT。在聚合函数的参数中使用DISTINCT可以删除重复数据。
五、对表进行分组
5.1 GROUP BY语句
之前使用聚合函数都是会整个表的数据进行处理当你想将进行分组汇总时即将现有的数据按照某列来汇总统计GROUP BY可以帮助你
SELECT 列名1,列名2, 列名3, ……FROM 表名GROUP BY 列名1, 列名2, 列名3, ……;看一看是否使用GROUP BY语句的差异
-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)FROM productGROUP BY product_type;-- 不含GROUP BY
SELECT product_type, COUNT(*)FROM product按照商品种类对表进行切分 这样GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列。
5.2 聚合键中包含NULL时
将进货单价purchase_price作为聚合键举例
SELECT purchase_price, COUNT(*)FROM productGROUP BY purchase_price;此时会将NULL作为一组特殊数据进行处理
5.3 GROUP BY书写位置
GROUP BY的子句书写顺序有严格要求不按要求会导致SQL无法正常执行目前出现过的子句书写****顺序为 1**.**SELECT → 2. FROM → 3. WHERE → 4. GROUP BY 其中前三项用于筛选数据GROUP BY对筛选出的数据进行处理
5.4 在WHERE子句中使用GROUP BY
SELECT purchase_price, COUNT(*)FROM productWHERE product_type 衣服GROUP BY purchase_price;5.5 常见错误
在使用聚合函数及GROUP BY子句时经常出现的错误有
在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时SELECT子句中如果出现列名只能是GROUP BY子句中指定的列名也就是聚合键。在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定而WHERE还处于确定结果集的过程中所以相互矛盾会引发错误。 如果想指定条件可以在SELECTHAVING下面马上会讲以及ORDER BY子句中使用聚合函数。
六、为聚合结果指定条件
6.1 用HAVING得到特定分组
将表使用GROUP BY分组后怎样才能只取出其中两组 这里WHERE不可行因为WHERE子句只能指定记录行的条件而不能用来指定组的条件例如“数据行数为 2 行”或者“平均值为 500”等。 可以在GROUP BY后使用HAVING子句。 HAVING的用法类似WHERE
6.2 HAVING特点
HAVING子句用于对分组进行过滤可以使用数字、聚合函数和GROUP BY中指定的列名聚合键。
-- 数字
SELECT product_type, COUNT(*)FROM productGROUP BY product_type
HAVING COUNT(*) 2;
-- 错误形式因为product_name不包含在GROUP BY聚合键中
SELECT product_type, COUNT(*)FROM productGROUP BY product_type
HAVING product_name 圆珠笔;七、对查询结果进行排序
7.1 ORDER BY
SQL中的执行结果是随机排列的当需要按照特定顺序排序时可已使用ORDER BY子句。
SELECT 列名1, 列名2, 列名3, ……FROM 表名ORDER BY 排序基准列1, 排序基准列2, ……默认为升序排列降序排列为DESC
-- 降序排列
SELECT product_id, product_name, sale_price, purchase_priceFROM productORDER BY sale_price DESC;
-- 多个排序键
SELECT product_id, product_name, sale_price, purchase_priceFROM productORDER BY sale_price, product_id;
-- 当用于排序的列名中含有NULL时NULL会在开头或末尾进行汇总。
SELECT product_id, product_name, sale_price, purchase_priceFROM productORDER BY purchase_price;7.2 ORDER BY中列名可使用别名
前文讲GROUP BY中提到GROUP BY 子句中不能使用SELECT 子句中定义的别名但是在 ORDER BY 子句中却可以使用别名。为什么在GROUP BY中不可以而在ORDER BY中可以呢 这是因为SQL在使用 HAVING 子句时 SELECT 语句的执行****顺序为 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 其中SELECT的执行顺序在 GROUP BY 子句之后ORDER BY 子句之前。也就是说当在ORDER BY中使用别名时已经知道了SELECT设置的别名存在但是在GROUP BY中使用别名时还不知道别名的存在所以在ORDER BY中可以使用别名但是在GROUP BY中不能使用别名****。
练习题-第二部分
练习题5
请指出下述SELECT语句中所有的语法错误。
SELECT product_id, SUMproduct_name
--本SELECT语句中存在错误。FROM product GROUP BY product_type WHERE regist_date 2009-09-01;问题1.product_name不可进行sum 2.where应该在from后group by之前 ** 3.group by 与select字段不同**
练习题6
请编写一条SELECT语句求出销售单价sale_price列合计值大于进货单价purchase_price列合计值1.5倍的商品种类。执行结果如下所示。
product_type | sum | sum
-------------------------
衣服 | 5000 | 3300
办公用品 | 600 | 320SELECT product_type,SUM(sale_price),SUM(purchase_price) FROM product
GROUP BY product_type
HAVING SUM(sale_price)SUM(purchase_price)*1.5;练习题7
此前我们曾经使用SELECT语句选取出了product商品表中的全部记录。当时我们使用了ORDERBY子句来指定排列顺序但现在已经无法记起当时如何指定的了。请根据下列执行结果思考ORDERBY子句的内容。
SELECT *
FROM product
ORDER BY regist_date DESC,sale_price;
