c mysql数据库数据排序_MySQL|基础查询与排序

执行顺序

本文使用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;

查询结果

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

mysql> select * from product

-> order by regist_data DESC;

regist_data降序排列，但是在原本输入数据时把叉子的日期写错了，和答案有所不同，但是原理不变