03-基础查询和排序-白红宇

03-基础查询和排序

阅读量：2062 次

发布时间：2019-04-29

本文共 7183 字，大约阅读时间需要 23 分钟。

1、SELECT语句：SELECT <列名>， FROM <表名>；

select * from shop.product p

2、WHERE语句：SELECT <列名>，…… FROM <表名> WHERE <条件表达式>；

SELECT product_name,product_type  FROM product WHERE product_type = '衣服'

SELECT product_name  FROM product WHERE product_type = '衣服'

3、相关法则：

星号（*）代表全部列的意思。

SQL中可以随意使用换行符，不影响语句执行（但不可插入空行）。

设定汉语别名时需要使用双引号（"）括起来。

在SELECT语句中使用DISTINCT可以删除重复行。

注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释"-- “和多行注释两种”/* */"

-- 想要查询出全部列时，可以使用代表所有列的星号（*）。SELECT *  FROM shop.product p ;

-- SQL语句可以使用AS关键字为列设定别名（用中文时需要双引号（“”））。SELECT product_id     As id,       product_name   As name,       purchase_price AS "进货单价"  FROM product;

-- 使用DISTINCT删除product_type列中重复的数据SELECT DISTINCT product_type  FROM product;

4、算术运算符

SQL语句中可以使用的四则运算的主要运算符如下：

含义	运算符
加法	+
减法	-
乘法	*
除法	/

5、比较运算符

SQL常见比较运算符如下：

运算符	含义
=	和~相等
<>	和~不相等
>=	大于等于~
>	大于~
<=	小于等于~
<	小于~

6、常用法则

SELECT子句中可以使用常数或者表达式。

使用比较运算符时一定要注意不等号和等号的位置。

字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。

希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

-- SQL语句中也可以使用运算表达式SELECT product_name,sale_price,sale_price * 2 AS "sale_price x2"  FROM product;

-- WHERE子句的条件表达式中也可以使用计算表达式SELECT product_name，sale_price，purchase_price  FROM product WHERE sale_price-purchase_price >= 500；

/* 对字符串使用不等号首先创建chars并插入数据选取出大于‘2’的SELECT语句*/-- DDL：创建表CREATE TABLE chars(chr CHAR(3) NOT null,PRIMARY key (chr));-- 选取出大于'2'的数据的SELECT语句('2'为字符串)SELECT chr  FROM chars WHERE chr > '2'；-- 选取NULL的记录SELECT product_name,purchase_price  FROM product WHERE purchase_price IS NULL;-- 选取不为NULL的记录SELECT product_name,purchase_price  FROM product WHERE purchase_price IS NOT NULL;

7、NOT运算符

想要表示“不是……”时，除了前文的<>运算符外，还存在另外一个表示否定、使用范围更广的运算符：NOT。

NOT不能单独使用，如下例

-- 选取出销售单价大于等于1000日元的记录SELECT product_name,product_type,sale_price  FROM product WHERE sale_price >= 1000;-- 向代码清单2-30的查询条件中添加NOT运算符SELECT product_name,product_type,sale_price  FROM product WHERE NOT sale_price >= 1000;

8、AND运算符和OR运算符

AND 相当于“并且”，类似数学中的取交集；

OR 相当于“或者”，类似数学中的取并集。

9、通过括号优先处理

“商品种类为办公用品”并且“登记日期是 2009 年 9 月 11 日或者 2009 年 9 月 20 日”

-- 通过使用括号让OR运算符先于AND运算符执行SELECT product_name, product_type, regist_date  FROM product WHERE product_type = '办公用品'   AND ( regist_date = '2009-09-11'        OR regist_date = '2009-09-20');

如果不加括号，会先执行AND

SELECT product_name, product_type, regist_date  FROM product WHERE product_type = '办公用品'   AND regist_date = '2009-09-11'    OR regist_date = '2009-09-20';

10、真值表

AND 运算符两侧的真值都为真时返回真，除此之外都返回假。

OR 运算符两侧的真值只要有一个不为假就返回真，只有当其两侧的真值都为假时才返回假。

NOT运算符只是单纯的将真转换为假，将假转换为真。

作业：

（1）编写一条SQL语句，从 product（商品）表中选取出“登记日期（ regist 在2009年4月28日之后”的商品，查询结果要包含 product_name 和 regist_date 两列。

select p.product_name,p.regist_date from shop.product p where p.regist_date > '2009-04-28'

（2）请说出对product 表执行如下3条SELECT语句时的返回结果。

SELECT * FROM product WHERE purchase_price = NULL;

SELECT * FROM product WHERE purchase_price <> NULL;

SELECT * FROM product WHERE product_name > NULL;

（3）SELECT语句能够从product表中取出“销售单价（saleprice）比进货单价（purchase price）高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示。

select p.product_name,p.sale_price ,p.purchase_price from  shop.product p where p.sale_price - p.purchase_price >= 500select p.product_name,p.sale_price ,p.purchase_price from  shop.product p where p.sale_price >= p.purchase_price + 500

（4）请写出一条SELECT语句，从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设定为profit）

select p.product_name ,p.product_type ,p.sale_price * 0.9 - p.purchase_price as profit from shop.product pwhere p.sale_price * 0.9 - p.purchase_price > 100and p.product_type in ('办公用品','厨房用具')

select p.product_name ,p.product_type ,p.sale_price * 0.9 - p.purchase_price as profit from shop.product pwhere p.sale_price * 0.9 - p.purchase_price > 100and (p.product_type = '办公用品' or p.product_type = '厨房用具')

11、对表进行聚合查询

SQL中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数：

COUNT：计算表中的记录数（行数）

-- 计算全部数据的行数（包含NULL）SELECT COUNT(*)  FROM product;-- 计算NULL以外数据的行数SELECT COUNT(purchase_price)  FROM product;

SUM：计算表中数值列中数据的合计值

SELECT SUM(sale_price), SUM(purchase_price)   FROM product;

AVG：计算表中数值列中数据的平均值

SELECT AVG(sale_price), AVG(purchase_price)  FROM product;

MAX：求出表中任意列中数据的最大值

MIN：求出表中任意列中数据的最小值

SELECT MAX(regist_date), MIN(regist_date)  FROM product;

12、group by 语句

之前使用聚合函数都是会整个表的数据进行处理，当你想将进行分组汇总时（即：将现有的数据按照某列来汇总统计），GROUP BY可以帮助你：

SELECT 
   <列名1>
    ,
    <列名2>
     , 
     <列名3>
      , ……  FROM 
      <表名>
        GROUP BY 
       <列名1>
        , 
        <列名2>
         , 
         <列名3>
          , ……;

看一看是否使用GROUP BY语句的差异：

GROUP BY书写位置

GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句书写顺序为：

1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理

在WHERE子句中使用GROUP BY

SELECT purchase_price, COUNT(*)  FROM product WHERE product_type = '衣服' GROUP BY purchase_price;

常见错误

在使用聚合函数及GROUP BY子句时，经常出现的错误有：

在聚合函数的SELECT子句中写了聚合健以外的列使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。

在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。

在WHERE中使用聚合函数原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

13、为聚合结果指定条件

HAVING子句用于对分组进行过滤，可以使用数字、聚合函数和GROUP BY中指定的列名（聚合键）。

ELECT product_type, COUNT(*)  FROM product GROUP BY product_typeHAVING COUNT(*) = 2;

14、对查询结果进行排序

SQL中的执行结果是随机排列的，当需要按照特定顺序排序时，可已使用ORDER BY子句。

SELECT 
   <列名1>
    , 
    <列名2>
     , 
     <列名3>
      , ……  FROM 
      <表名>
        ORDER BY 
       <排序基准列1>
        , 
        <排序基准列2>
         , ……

默认为升序排列，降序排列为DESC

-- 降序排列SELECT product_id, product_name, sale_price, purchase_price  FROM product ORDER BY sale_price DESC;

-- 多个排序键SELECT product_id, product_name, sale_price, purchase_price  FROM product ORDER BY sale_price, product_id;

-- 当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。SELECT product_id, product_name, sale_price, purchase_price  FROM product ORDER BY purchase_price;

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中不能使用别名

作业：

（1）请指出下述SELECT语句中所有的语法错误。

SELECT product_id, SUM（product_name） FROM product GROUP BY product_type WHERE regist_date > '2009-09-01';

where语句在group by 语句后面，语法不正确。

而且select 后是product_id, SUM（product_name）而分组语句后面是product_type，语法也正确。

（2）请编写一条SELECT语句，求出销售单价（sale_price 列）合计值大于进货单价（purchase_price 列）合计值1.5倍的商品种类。执行结果如下所示。

product_type | sum  | sum -------------+------+------衣服         | 5000 | 3300办公用品     |  600 | 320

select p.product_type ,sum(p.sale_price),sum(p.purchase_price) from product p group by p.product_type having sum(p.sale_price) > sum(p.purchase_price) * 1.5

（3）此前我们曾经使用SELECT语句选取出了product（商品）表中的全部记录。当时我们使用了ORDERBY子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考ORDERBY子句的内容。

Ig0bhBIB1Di4BCTV

引用别人答案：

回答：首先可以排除product_id、sale_price、purchase_price这三个没有明显的升序和降序的列。

其次，product_name 和product_type如果是考虑第一个字的首字母，也没有类似升降序的排序。

最后只剩下regist_date这列，可以看出除了第一个记录，其他记录都是按降序排序的。

如果要把NULL排在前面，MySQL并没有像Oracle的ORDER BY xx NULL FIRST这种现成的函数。可以用Mysql的IF和ISNULL函数，对数据进行判断是否为NULL，为NULL则返回0，否返回1。再把判断结果的0和1作为分组。这样按默认的降序排列，分组0就排在分组1前面了。

SELECT * FROM productORDER BY IF(ISNULL(regist_date), 0, 1),         regist_date DESC,         purchase_price asc

转载地址：http://mjmlf.baihongyu.com/

你可能感兴趣的文章

iOS常用宏定义

查看>>

被废弃的dispatch_get_current_queue

查看>>

什么是ActiveRecord

查看>>

有道词典for mac在Mac OS X 10.9不能取词

Windows7中IIS简单安装与配置(详细图解)

查看>>

linux基本命令

查看>>

BlockQueue 生产消费不需要判断阻塞唤醒条件

查看>>

ExecutorService 线程池 newFixedThreadPool newSingleThreadExecutor newCachedThreadPool

"NetworkError: 400 Bad Request - http://172.16.47.117:8088/rhip/**/####t/approval?date=976

第一篇 HelloWorld.java重新学起

web.xml配置监听器，加载数据库信息配置文件ServletContextListener

查看>>