MySQL|基础查询与排序

学习链接http://datawhale.club/t/topic/476

执行顺序

本文使用cmd命令行执行SQL语句

C:\Users\Administrator>mysql -u root -p

进入Mysql数据库.png

1基础操作

mysql> show databases;

查看已创建的数据库.png

1.1使用数据库并创建数据表格

### 1.1 使用数据库并创建数据表格
mysql> use datawhale;
Database changed
mysql> create table product(
    -> product_id char(4) not null,
    -> product_name varchar(100) not null,
    -> product_type varchar(32) not null,
    -> sale_price integer,
    -> purchase_price integer,
    -> regist_data date,
    -> primary key (product_id));
mysql> desc product;

查看创建好的表格.png

1.2 查看建表语句

方便直接移植原数据表格

# 查看建表语句
mysql> show create table product;
| product | CREATE TABLE `product` (
  `product_id` char(4) NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `product_type` varchar(32) NOT NULL,
  `sale_price` int(11) DEFAULT NULL,
  `purchase_price` int(11) DEFAULT NULL,
  `regist_data` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1.3 修改表格内容-添加和删除列（关键字）

# 修改表格内容-添加列
mysql> alter table product add column product_name_pinyin varchar(100);
mysql> desc product;

添加了新的列

# 删除列
mysql> alter table product drop column product_name_pinyin;
mysql> desc product;

删除列后的数据表格

1.4给数据表填充数据

需要注意数据类型和关键字相对应

# 给数据表填充数据
mysql> insert into product(product_id,product_name,product_type,sale_price,purchase_price,regist_data)
    -> values('0005','高压锅','厨房用具',6800,5000,'2009-01-15');
# 查看数据内容
mysql> select * from product;

添加了一行数据

# 清空表格内容
mysql> truncate table product;
Query OK, 0 rows affected (0.16 sec)

mysql> select * from product;
Empty set (0.00 sec)

# 向表格中插入数据
mysql> insert into product values('0001','T恤衫','衣服',1000,500,'2009-09-20');

mysql> insert into product values('0002','打孔器','办公用品',500,320,'2009-09-11');

mysql> insert into product values('0003','运动T恤','衣服',4000,2800,NULL);

mysql> insert into product values('0004','菜刀','厨房用具',3000,2800,'2009-09-20');

mysql> insert into product values('0005','高压锅','厨房用具',6800,5000,'2009-01-15');

mysql> insert into product values('0006','叉子','厨房用具',500,NULL,'2008-09-20');

mysql> insert into product values('0007','擦菜板','厨房用具',880,790,'2008-04-28');
mysql> insert into product values('0008','圆珠笔','办公用品',100,NULL,'2009-11-11');
# 显示表格详情
mysql> select * from product;

数据表填充数据

2 基础查询操作

2.1 where语句的使用

mysql> select product_name,product_type
    -> from product
    -> where product_type='衣服';

按条件进行查询

# 查询并对关键字重命名
mysql> select product_id as id,
    -> product_name as name,
    -> purchase_price as '进货单价'
    -> from product;

查询结果

# 在where中添加查询条件
mysql> select product_name,sale_price,purchase_price
    -> from product
    -> where sale_price-purchase_price >= 500;

在where中可进行运算

# 对空值的查询操作
mysql> select product_name,purchase_price
    -> from product
    -> where purchase_price is null;

使用is逻辑判断语句

mysql> select product_name, purchase_price
    -> from product
    -> where purchase_price is not null;

not的合理使用


mysql> select product_name,product_type,regist_data
    -> from product
    -> where product_type = '办公用品'
    -> AND (regist_data = '2009-09-11' or regist_data='2009-09-20');

or的使用

1.5 练习题

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

mysql> select product_name,regist_data
    -> from product
    -> where regist_data > '2009-04-28';

字符串可直接比较大小

2 请说出对product 表执行如下3条SELECT语句时的返回结果。
①
SELECT *
FROM product
WHERE purchase_price = NULL;

Empty set (0.00 sec)
由于ourchase_price是数值型数据，因此正确的查询代码如下：

mysql> select * from product where purchase_price is null;

价格是否为空的查询

②
SELECT *
FROM product
WHERE purchase_price <> NULL;

Empty set (0.00 sec)

③
SELECT *
FROM product
WHERE product_name > NULL;

Empty set (0.00 sec)

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

product_name | sale_price | purchase_price
-------------+------------+------------
T恤衫 | 1000 | 500
运动T恤 | 4000 | 2800
高压锅 | 6800 | 5000

mysql> select product_name, sale_price, purchase_price
    -> from product
    -> where sale_price-purchase_price > 400;

mysql> select product_name,sale_price,purchase_price
    -> from product
    -> where sale_price-purchase_price > 300;

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

mysql> select product_name, product_type, (sale_price*0.9-purchase_price) as profit
    -> from product
    -> where sale_price*0.9-purchase_price > 100;

运算法则的应用

2 对表进行聚合查询

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

COUNT：计算表中的记录数（行数）
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值

2.1 count()函数

# 聚合查询
mysql> select count(*)
    -> from product;

count聚合函数

mysql> select count(purchase_price)
    -> from product;

可过滤缺失值

2.2 sum()函数

mysql> select sum(sale_price),sum(purchase_price)
    -> from product;

求出某一列的和

2.3 avg()函数

mysql> select avg(sale_price), avg(purchase_price)
    -> from product;

求出某一列的均值

2.4 max()和min()函数

mysql> select max(regist_data),min(regist_data)
    -> from product;

最大值和最小值求解

mysql> select count(distinct product_type)
    -> from product;

去重统计

mysql> select sum(sale_price),sum(distinct sale_price)
    -> from product;

求和去重统计

2.5 对表进行分组

2.5.1 group by 语句

常见错误
在使用聚合函数及GROUP BY子句时，经常出现的错误有：
在聚合函数的SELECT子句中写了聚合健以外的列使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
在WHERE中使用聚合函数原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

mysql> select product_type,count(*)
    -> from product
    -> group by product_type;

分组函数-group by

mysql> select purchase_price,count(*)
    -> from product
    -> group by purchase_price;

缺失值也可以统计出来

mysql> select purchase_price,count(*)
    -> from product
    -> where product_type = '衣服'
    -> group by purchase_price;

where和group by同时使用

2.6 为聚和结果指定条件

2.6.1 用HAVING得到特定分组

mysql> select product_type,count(*)
    -> from product
    -> group by product_type
    -> having count(*)=2;

having函数

2.7 对查询结果进行排序: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中不能使用别名。

mysql> select product_id, product_name,sale_price,purchase_price
    -> from product
    -> order by sale_price DESC;

order by 并使用desc降序排列

mysql> SELECT product_id, product_name,sale_price,purchase_price
    -> from product
    -> order by sale_price,product_id;

两项排序，谁前谁先排序

mysql> select product_id,product_name,sale_price,purchase_price
    -> from product
    -> order by purchase_price;

含有空值，排序位置不确定，可前可后

2.8 练习

1 请指出下述SELECT语句中所有的语法错误。
SELECT product id，SUM（product name）
--本SELECT语句中存在错误。
FROM product
GROUP BY product_type
WHERE regist_date > '2009-09-01'；

错误1：列名错误，product_id,product_name
错误2：sum只能用来计算数值型数据，product_name属于字符串型
错误3：必须先用where语句，后使用group by语句
错误4：SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）

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

image.png

mysql>  select product_type,sum(sale_price) as sum,sum(purchase_price) as sum
    -> from product
    -> group by product_type
    -> having sum(sale_price)/sum(purchase_price) >= 1.5;