MySQL单表操作

二、数据的新增、修改、删除

1.回顾ishop的数据表

mysql> use ishop;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_ishop |
+-----------------+
| commodity       |
| commoditytype   |
| customer        |
| order           |
+-----------------+
4 rows in set (0.00 sec)

mysql> desc commoditytype;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ct_id   | int(11)     | NO   | PRI | NULL    |       |
| ct_name | varchar(50) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> desc commodity;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| c_id       | int(11)     | NO   | PRI | NULL    |       |
| c_name     | varchar(50) | NO   |     | NULL    |       |
| c_madein   | varchar(50) | NO   |     | NULL    |       |
| c_type     | int(11)     | NO   | MUL | NULL    |       |
| c_inprice  | int(11)     | NO   |     | NULL    |       |
| c_outprice | int(11)     | YES  |     | NULL    |       |
| c_num      | int(11)     | NO   |     | 100     |       |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.02 sec)

mysql> desc customer;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| cu_id      | int(11)      | NO   | PRI | NULL    |       |
| cu_name    | varchar(50)  | NO   |     | NULL    |       |
| cu_phone   | varchar(50)  | NO   |     | NULL    |       |
| cu_gender  | int(11)      | NO   |     | 1       |       |
| cu_address | varchar(100) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> desc order;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1
mysql> desc `order`;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| o_id   | int(11) | NO   | PRI | NULL    | auto_increment |
| o_cuid | int(11) | NO   | MUL | NULL    |                |
| o_cid  | int(11) | NO   | MUL | NULL    |                |
| o_num  | int(11) | NO   |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
4 rows in set (0.17 sec)

2.新增数据

新增数据的SQL语法

INSERT INTO table_name [(field1,field2,...,fieldn)]
VALUES (value1,value2,...,valuen);

注意！字段和数据表中的字段顺序一一对应，字段和后面的值也是一一对应！

示例：

mysql> insert into commoditytype (ct_id,ct_name) values (1,'玩具');
Query OK, 1 row affected (0.01 sec)

mysql> # 先学习一条最简单的查询语句
mysql> select * from commoditytype;
+-------+---------+
| ct_id | ct_name |
+-------+---------+
|     1 | 玩具    |
+-------+---------+
1 row in set (0.00 sec)

mysql> # 继续新增多条数据
mysql> # 同时新增 2 文具 3 书籍
mysql> insert into commoditytype (ct_id,ct_name)
    -> values (2,'文具'),(3,'书籍');
Query OK, 2 rows affected (0.22 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from commoditytype;
+-------+---------+
| ct_id | ct_name |
+-------+---------+
|     1 | 玩具    |
|     2 | 文具    |
|     3 | 书籍    |
+-------+---------+
3 rows in set (0.00 sec)

3.删除数据

删除数据的SQL语法

DELETE FROM table_name [WHERE CONDITION];

delete语句后跟着where的条件子句

示例：

mysql> delete from commoditytype where ct_id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from commoditytype;
+-------+---------+
| ct_id | ct_name |
+-------+---------+
|     1 | 玩具    |
|     2 | 文具    |
+-------+---------+
2 rows in set (0.00 sec)

mysql> delete from commoditytype;
Query OK, 2 rows affected (0.01 sec)

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

delete语句如果没有加上where子句，整张表的数据会被全部删除，并且不能撤销！

在公司中如果没有添加where，就是真正的“从删库到跑路”

真正的工作中如果执行了这样的操作是需要承担法律责任的！

变卖家产-》牢底坐穿

4.更新/修改数据

更新数据的SQL语法

UPDATE table_name 
SET field1=value1,field2=field2,...,fieldn=valuen 
WHERE CONDITION;

示例：

mysql> # 修改书籍为图书
mysql> update commoditytype set ct_name='图书' where ct_id=3;
Query OK, 1 row affected (0.32 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from commoditytype;
+-------+---------+
| ct_id | ct_name |
+-------+---------+
|     1 | 玩具    |
|     2 | 文具    |
|     3 | 图书    |
+-------+---------+
3 rows in set (0.00 sec)

mysql> # 测试如果update没有where子句
mysql> update commoditytype set ct_name='哈哈';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from commoditytype;
+-------+---------+
| ct_id | ct_name |
+-------+---------+
|     1 | 哈哈    |
|     2 | 哈哈    |
|     3 | 哈哈    |
+-------+---------+
3 rows in set (0.00 sec)

如果update没有where子句，则所有数据被修改为相同的值

效果和delete是一样一样的

变卖家产或牢底坐穿

5.新增商品表的数据

新增3条商品数据

mysql> insert into commodity (c_id,c_name,c_madein,c_type,c_inprice,c_outprice,c_num)
    -> values (1,'变形金刚','中国',1,10,20,21);
Query OK, 1 row affected (0.01 sec)

mysql> select * from commodity;
+------+----------+----------+--------+-----------+------------+-------+
| c_id | c_name   | c_madein | c_type | c_inprice | c_outprice | c_num |
+------+----------+----------+--------+-----------+------------+-------+
|    1 | 变形金刚 | 中国     |      1 |        10 |         20 |    21 |
+------+----------+----------+--------+-----------+------------+-------+
1 row in set (0.00 sec)

mysql> # c_outprice是允许为空的，c_num是有默认值的
mysql> insert into commodity (c_id,c_name,c_madein,c_type,c_inprice,c_outprice,c_num)
    -> values (2,'高达模型','日本',1,120,240,default);
Query OK, 1 row affected (0.15 sec)

mysql> select * from commodity;
+------+----------+----------+--------+-----------+------------+-------+
| c_id | c_name   | c_madein | c_type | c_inprice | c_outprice | c_num |
+------+----------+----------+--------+-----------+------------+-------+
|    1 | 变形金刚 | 中国     |      1 |        10 |         20 |    21 |
|    2 | 高达模型 | 日本     |      1 |       120 |        240 |   100 |
+------+----------+----------+--------+-----------+------------+-------+
2 rows in set (0.00 sec)

mysql> insert into commodity (c_id,c_name,c_madein,c_type,c_inprice,c_outprice,c_num)
    -> values (3,'奥特曼手办','中国',1,1,null,10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from commodity;
+------+------------+----------+--------+-----------+------------+-------+
| c_id | c_name     | c_madein | c_type | c_inprice | c_outprice | c_num |
+------+------------+----------+--------+-----------+------------+-------+
|    1 | 变形金刚   | 中国     |      1 |        10 |         20 |    21 |
|    2 | 高达模型   | 日本     |      1 |       120 |        240 |   100 |
|    3 | 奥特曼手办 | 中国     |      1 |         1 |       NULL |    10 |
+------+------------+----------+--------+-----------+------------+-------+
3 rows in set (0.00 sec)

6.易错讲解

三、单表查询

1.最简单的查询语句

mysql> select * from commoditytype;
+-------+---------+
| ct_id | ct_name |
+-------+---------+
|     1 | 玩具    |
|     2 | 文具    |
|     3 | 书籍    |
+-------+---------+
3 rows in set (0.00 sec)

让我们来重新认识下select关键字

mysql> select * from commoditytype;
+-------+---------+
| ct_id | ct_name |
+-------+---------+
|     1 | 玩具    |
|     2 | 文具    |
|     3 | 书籍    |
+-------+---------+
3 rows in set (0.00 sec)

mysql> # 让我们重新来认识一下select关键字
mysql> # 在MySQL中select关键字的意义，是输出
mysql> select helloworld;
ERROR 1054 (42S22): Unknown column 'helloworld' in 'field list'
mysql> # 未知的字段 ``修饰字段 ''修饰值
mysql> select 'helloworld';
+------------+
| helloworld |
+------------+
| helloworld |
+------------+
1 row in set (0.00 sec)

mysql> # 如何指定字段名称呢？ 使用的是as关键字
mysql> select 'hello world' as `say hi`;
+-------------+
| say hi      |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)

from关键字负责指定数据来源

select * from commoditytype;

from指定数据来源自商品类型表，select控制输出数据，*表示输出所有！

如果我们想输出我们想输出的字段该如何编写SQL语句呢？

select的SQL语法

SELECT field1,field2,...,fieldn from table_name;

示例

mysql> select c_id,c_name from commodity;
+------+------------------------+
| c_id | c_name                 |
+------+------------------------+
|    1 | 变形金刚-擎天柱        |
|    2 | 变形金刚-霸天虎        |
|    3 | 变形金刚-威震天        |
|   57 | 唐诗三百首             |
|   58 | 名家演讲赏析           |
|   59 | 三国演义               |
|   60 | 红楼梦                 |
+------+------------------------+
59 rows in set (0.00 sec)

mysql> select c_type,c_id,c_name,c_type from commodity;
+--------+------+------------------------+--------+
| c_type | c_id | c_name                 | c_type |
+--------+------+------------------------+--------+
|      1 |    1 | 变形金刚-擎天柱        |      1 |
|      1 |    2 | 变形金刚-霸天虎        |      1 |
|      1 |    3 | 变形金刚-威震天        |      1 |
|      1 |    4 | 魔仙玩偶1              |      1 |
|      1 |    5 | 超人玩偶               |      1 |
|      1 |    7 | 小霸王游戏机           |      1 |
|      1 |    8 | X-BOX游戏机            |      1 |
|      3 |   56 | 牛津英语               |      3 |
|      3 |   57 | 唐诗三百首             |      3 |
|      3 |   58 | 名家演讲赏析           |      3 |
|      3 |   59 | 三国演义               |      3 |
|      3 |   60 | 红楼梦                 |      3 |
+--------+------+------------------------+--------+
59 rows in set (0.00 sec)

2.四则运算和去掉重复数据

（1）MySQL支持简单的四则运算

mysql> # MySQL支持简单的四则运算 + - * /
mysql> # 计算每一个商品的单件利润 c_outprice-c_inprice
mysql> select c_name as 商品名称,c_outprice-c_inprice as 单件利润
    -> from commodity;
+------------------------+----------+
| 商品名称               | 单件利润 |
+------------------------+----------+
| 变形金刚-擎天柱        |       30 |
| 变形金刚-霸天虎        |       25 |
| 变形金刚-威震天        |      125 |
| 魔仙玩偶1              |        6 |
| 超人玩偶               |       70 |
| 小霸王游戏机           |       49 |
| X-BOX游戏机            |     1800 |
| 任天堂游戏机           |      300 |
| 乐高玩具-散装          |       20 |
| 乐高玩具-快乐家庭      |     NULL |
| 乐高玩具-蝙蝠侠纪念版  |      300 |
| 夏日有人手办           |       30 |
| EVA模型玩具            |      250 |
| 平板电脑模型           |       10 |
| 手机模型玩具           |       17 |
| 手机模型玩具           |       60 |
| 手机模型玩具           |     NULL |
| 上下五千年             |      200 |
| 电脑报15年合集         |       28 |
| 哈利波特1-3            |     NULL |
| 新华字典               |        9 |
| 英汉字典               |        0 |
| 牛津英语               |       83 |
| 唐诗三百首             |        2 |
| 名家演讲赏析           |       28 |
| 三国演义               |       17 |
| 红楼梦                 |        2 |
+------------------------+----------+
59 rows in set (0.01 sec)

从输出结果我们可以看出，当null数据参与运算的时候，结果也为null。

（2）去掉重复数据

mysql> # 我想知道我在商品表中一共进了多少种商品类型
mysql> select c_type from commodity;
+--------+
| c_type |
+--------+
|      1 |
|      1 |
|      1 |
|      2 |
|      2 |
|      3 |
|      3 |
|      3 |
|      3 |
|      3 |
|      3 |
|      3 |
|      3 |
+--------+
59 rows in set (0.00 sec)

mysql> # 我们需要使用distinct关键字来帮助我们去除重复的数据
mysql> select distinct c_type from commodity;
+--------+
| c_type |
+--------+
|      1 |
|      2 |
|      3 |
+--------+
3 rows in set (0.01 sec)

3.where条件子句的作用

（1）关系运算符和逻辑运算符的条件查询

关系运算符 大于> 小于< 大于等于>= 小于等于<= 等于= 不等于<>

逻辑运算符 与 and 或 or

示例

mysql> # 查询出进价大于20的商品
mysql> select c_name,c_inprice from commodity
    -> where c_inprice>20;
+------------------------+-----------+
| c_name                 | c_inprice |
+------------------------+-----------+
| 变形金刚-威震天        |       120 |
| 超人玩偶               |        29 |
| 小霸王游戏机           |        50 |
| 英汉字典               |        26 |
| 牛津英语               |       217 |
| 名家演讲赏析           |        22 |
+------------------------+-----------+
29 rows in set (0.00 sec)

mysql> # 查询售价小于20的商品信息
mysql> select c_name,c_outprice
    -> from commodity
    -> where c_outprice<20;
+--------------+------------+
| c_name       | c_outprice |
+--------------+------------+
| 魔仙玩偶1    |         12 |
| 平板电脑模型 |         12 |
| 水枪-小      |          4 |
| 红楼梦       |         16 |
+--------------+------------+
16 rows in set (0.00 sec)

mysql> # 查询售价大于100并且是玩具的商品
mysql> # c_outprice>100 and c_type=1
mysql> select c_name,c_outprice,c_type
    -> from commodity
    -> where c_outprice>100 and c_type=1;
+-----------------------+------------+--------+
| c_name                | c_outprice | c_type |
+-----------------------+------------+--------+
| 变形金刚-威震天       |        245 |      1 |
| EVA模型玩具           |        450 |      1 |
+-----------------------+------------+--------+
5 rows in set (0.10 sec)

mysql> # 查询进价小于10 或者 是文具的商品
mysql> # c_inprice<10 or c_type=2;
mysql> select c_name,c_inprice,c_type
    -> from commodity
    -> where c_inprice<10 or c_type=2;
+------------------------+-----------+--------+
| c_name                 | c_inprice | c_type |
+------------------------+-----------+--------+
| 魔仙玩偶1              |         6 |      1 |
| 平板电脑模型           |         2 |      1 |
| 手机模型玩具           |         7 |      1 |
| 儿童彩色铅笔           |        60 |      2 |
| 快乐家族文具礼盒       |       160 |      2 |
| 新华字典               |         8 |      3 |
| 唐诗三百首             |         6 |      3 |
+------------------------+-----------+--------+
25 rows in set (0.00 sec)

（2）BETWEEN AND关键字的条件查询

BETWEEN AND关键字用来查询某一个数值区间

mysql> # 查询进价在10-100之间的商品
mysql> # c_inprice>=10 and c_inprice<=100
mysql> select c_name,c_inprice
    -> from commodity
    -> where c_inprice>=10 and c_inprice<=100;
+------------------------+-----------+
| c_name                 | c_inprice |
+------------------------+-----------+
| 变形金刚-擎天柱        |        20 |
| 变形金刚-霸天虎        |        20 |
| 名家演讲赏析           |        22 |
| 三国演义               |        20 |
| 红楼梦                 |        14 |
+------------------------+-----------+
32 rows in set (0.00 sec)

mysql> # 使用between and来进行相同需求的查询
mysql> select c_name ,c_inprice
    -> from commodity
    -> where c_inprice between 10 and 100;
+------------------------+-----------+
| c_name                 | c_inprice |
+------------------------+-----------+
| 变形金刚-擎天柱        |        20 |
| 变形金刚-霸天虎        |        20 |
| 红楼梦                 |        14 |
+------------------------+-----------+
32 rows in set (0.00 sec)

mysql> # 这个区间外的数值如何查询
mysql> # c_inprice<10 and c_inprice>100 这个是查询不出数据的
mysql> # c_inprice<10 or c_inprice>100
mysql> # not between and
mysql> select c_name,c_inprice
    -> from commodity
    -> where c_inprice not between 10 and 100;
+-----------------------+-----------+
| c_name                | c_inprice |
+-----------------------+-----------+
| 变形金刚-威震天       |       120 |
| 魔仙玩偶1             |         6 |
| 唐诗三百首            |         6 |
+-----------------------+-----------+
27 rows in set (0.00 sec)

between and 和 not between and 能够覆盖所有的数值范围

（3）IS NULL关键字的条件查询

使用 IS NULL 来进行 为空判断，找出哪些数据是空值的。

使用 IS NOT NULL 来进行非空判断，找出哪些数据没有空值。

示例

mysql> select c_name,c_outprice
    -> from commodity
    -> where c_outprice is null;
+-------------------+------------+
| c_name            | c_outprice |
+-------------------+------------+
| 乐高玩具-快乐家庭 |       NULL |
| 手机模型玩具      |       NULL |
| 哈利波特1-3       |       NULL |
+-------------------+------------+
3 rows in set (0.00 sec)

mysql> select c_name,c_outprice
    -> from commodity
    -> where c_outprice is not null;
+------------------------+------------+
| c_name                 | c_outprice |
+------------------------+------------+
| 变形金刚-擎天柱        |         50 |
| 变形金刚-霸天虎        |         45 |
| 三国演义               |         37 |
| 红楼梦                 |         16 |
+------------------------+------------+
56 rows in set (0.00 sec)

（4）IN 关键字的条件查询

IN关键字是用来查询某些特定的数值的

示例

mysql> # 我们需要查询出进价为 10 20 30 40 50 的商品信息
mysql> # c_inprice=10 or c_inprice=20 or c_.....
mysql> # in (10,20,30,40,50)
mysql> select c_name,c_inprice
    -> from commodity
    -> where c_inprice in (10,20,30,40,50);
+-------------------+-----------+
| c_name            | c_inprice |
+-------------------+-----------+
| 变形金刚-擎天柱   |        20 |
| 变形金刚-霸天虎   |        20 |
| 睡前小故事4-7     |        20 |
| 三国演义          |        20 |
+-------------------+-----------+
17 rows in set (0.00 sec)

mysql> # 不是 10 20 30 40 50
mysql> select c_name,c_inprice
    -> from commodity
    -> where c_inprice not in (10,20,30,40,50);
+------------------------+-----------+
| c_name                 | c_inprice |
+------------------------+-----------+
| 变形金刚-威震天        |       120 |
| 魔仙玩偶1              |         6 |
| 名家演讲赏析           |        22 |
| 红楼梦                 |        14 |
+------------------------+-----------+
42 rows in set (0.00 sec)

注意，in关键字中的数值是或关系！not in关键字中的数值是且关系

（5）LIKE关键字的条件查询

LIKE关键字是用于匹配字符条件搜索的，常用于模糊搜索！

MySQL提供的两个条件通配符

_ 匹配任意单个字符

% 配置任意多个字符

举个例子： _a%

符合这个条件的查询结果有：ward / martin / james

利用这些通配符，查询张姓学员的SQL语句

select name from student where name like '张%';

示例

mysql> # 查询商品名称中带有‘玩具’字样的商品信息
mysql> select c_name
    -> from commodity
    -> where c_name like '%玩具%';
+-----------------------+
| c_name                |
+-----------------------+
| 乐高玩具-散装         |
| 乐高玩具-快乐家庭     |
| 乐高玩具-蝙蝠侠纪念版 |
| EVA模型玩具           |
| 手机模型玩具          |
| 手机模型玩具          |
| 手机模型玩具          |
+-----------------------+
7 rows in set (0.00 sec)

mysql> # where c_name like '%玩具'; -> EVA模型玩具
mysql> # 如果不使用通配符，那么like就和=作用一致
mysql> select c_name
    -> from commodity
    -> where c_name like '玩具';
Empty set (0.00 sec)

mysql> # 如果只有通配符，效果等同于查询所有
mysql> select c_name
    -> from commodity
    -> where c_name like '%';
+------------------------+
| c_name                 |
+------------------------+
| 变形金刚-擎天柱        |
| 变形金刚-霸天虎        |
| 红楼梦                 |
+------------------------+
59 rows in set (0.00 sec)

4.排序输出

让结果按照你指定的关键字进行排序输出，使用ORDER BY关键字

注意！asc表示从小到大排序（默认可以省略）,逆序（从大到小）使用desc关键字

示例

mysql> # 让商品按照进价从小到大排序输出
mysql> select c_name,c_inprice
    -> from commodity
    -> order by c_inprice ;
+------------------------+-----------+
| c_name                 | c_inprice |
+------------------------+-----------+
| 中华铅笔HB             |         1 |
| 牛津英语               |       217 |
| 乐高玩具-蝙蝠侠纪念版  |       290 |
| 任天堂游戏机           |       300 |
| X-BOX游戏机            |      1200 |
+------------------------+-----------+
59 rows in set (0.00 sec)

mysql> # 按照售价从大到小排序输出
mysql> # 注意售价是有空值的
mysql> select c_name,c_outprice
    -> from commodity
    -> where c_outprice is not null
    -> order by c_outprice desc;
+------------------------+------------+
| c_name                 | c_outprice |
+------------------------+------------+
| X-BOX游戏机            |       3000 |
| 中华铅笔2H             |          2 |
| 中华铅笔2B             |          2 |
| 中华铅笔HB             |          2 |
+------------------------+------------+
56 rows in set (0.00 sec)

5.限制输出条目数

我们可以使用limit关键字限制查询结果输出的条目数量

语法：

select * from commodity limit 10;  -- 当limit后面只有1个数字的时候，限制输出最前面的10条数据
select * from commodity limit 10,2;  -- 当limit后面有2个数字的时候，限制从第10条数据开始输出2条数据

注意！计数从0开始

输出第1-5条数据，输出第6-10条数据

select * from commodity limit 5;
select * from commodity limit 5,5; 

示例

mysql> select c_id,c_name
    -> from commodity
    -> limit 10;
+------+-------------------+
| c_id | c_name            |
+------+-------------------+
|    1 | 变形金刚-擎天柱   |
|    2 | 变形金刚-霸天虎   |
|    3 | 变形金刚-威震天   |
|    4 | 魔仙玩偶1         |
|    5 | 超人玩偶          |
|    7 | 小霸王游戏机      |
|    8 | X-BOX游戏机       |
|    9 | 任天堂游戏机      |
|   10 | 乐高玩具-散装     |
|   11 | 乐高玩具-快乐家庭 |
+------+-------------------+
10 rows in set (0.00 sec)

mysql> select c_id,c_name
    -> from commodity
    -> limit 5;
+------+-----------------+
| c_id | c_name          |
+------+-----------------+
|    1 | 变形金刚-擎天柱 |
|    2 | 变形金刚-霸天虎 |
|    3 | 变形金刚-威震天 |
|    4 | 魔仙玩偶1       |
|    5 | 超人玩偶        |
+------+-----------------+
5 rows in set (0.00 sec)

mysql> select c_id,c_name
    -> from commodity
    -> limit 5,5;
+------+-------------------+
| c_id | c_name            |
+------+-------------------+
|    7 | 小霸王游戏机      |
|    8 | X-BOX游戏机       |
|    9 | 任天堂游戏机      |
|   10 | 乐高玩具-散装     |
|   11 | 乐高玩具-快乐家庭 |
+------+-------------------+
5 rows in set (0.00 sec)

limit最常见的使用场景分析

• 排行榜功能

mysql> # 按照售价从高到底排行榜第1-10位，11-20位
mysql> select c_name,c_outprice
    -> from commodity
    -> where c_outprice is not null
    -> order by c_outprice desc
    -> limit 10;
+-----------------------+------------+
| c_name                | c_outprice |
+-----------------------+------------+
| X-BOX游戏机           |       3000 |
| 任天堂游戏机          |        600 |
| 变形金刚-威震天       |        245 |
| java思考1             |        170 |
+-----------------------+------------+
10 rows in set (0.00 sec)

mysql> select c_name,c_outprice
    -> from commodity
    -> where c_outprice is not null
    -> order by c_outprice desc
    -> limit 10,10;
+------------------------+------------+
| c_name                 | c_outprice |
+------------------------+------------+
| 疯狂java               |        166 |
| 超人图案书包           |        160 |
| java入门到精通         |         66 |
| 折叠尺                 |         60 |
+------------------------+------------+
10 rows in set (0.00 sec)

• 分页效果

所有网站都有分页效果

示例

mysql> # 查询出所有的文具商品，并按每页10条进行输出
mysql> # page 1 -》 limit 0,10>  limit 10
mysql> # page 2 ->  limit 10,10
mysql> # page 3 ->  limit 20,10
mysql> # ...
mysql> select c_name
    -> from commodity
    -> where c_type=2;
+------------------------+
| c_name                 |
+------------------------+
| 中华铅笔HB             |
| 快乐家族文具礼盒       |
+------------------------+
18 rows in set (0.00 sec)

mysql> # page1
mysql> select c_name
    -> from commodity
    -> where c_type=2
    -> limit 10;
+------------------------+
| c_name                 |
+------------------------+
| 中华铅笔HB             |
| 礼盒丝带-红            |
+------------------------+
10 rows in set (0.00 sec)

mysql> # page2
mysql> select c_name
    -> from commodity
    -> where c_type=2
    -> limit 10,10;
+-------------------+
| c_name            |
+-------------------+
| 礼盒丝带-蓝       |
| 快乐家族文具礼盒  |
+-------------------+
8 rows in set (0.00 sec)

6.聚合函数【重点】

• COUNT( ) 计数

mysql> # count(*) count(field) 计数
mysql> select count(*) from commodity;
+----------+
| count(*) |
+----------+
|       59 |
+----------+
1 row in set (0.01 sec)

mysql> select count(c_id) from commodity;
+-------------+
| count(c_id) |
+-------------+
|          59 |
+-------------+
1 row in set (0.00 sec)

mysql> # 系统函数有一个功能，能够自动去除空值数据
mysql> select count(c_outprice) from commodity;
+-------------------+
| count(c_outprice) |
+-------------------+
|                56 |
+-------------------+
1 row in set (0.00 sec)

• AVG( ) 平均数

mysql> select avg(c_outprice) from commodity;
+-----------------+
| avg(c_outprice) |
+-----------------+
|        151.4643 |
+-----------------+
1 row in set (0.00 sec)

• SUM( ) 求和

mysql> select sum(c_outprice) from commodity;
+-----------------+
| sum(c_outprice) |
+-----------------+
|            8482 |
+-----------------+
1 row in set (0.00 sec)

• MAX( ) 求最大数

mysql> select max(c_outprice) from commodity;
+-----------------+
| max(c_outprice) |
+-----------------+
|            3000 |
+-----------------+
1 row in set (0.00 sec)

• MIN( ) 求最小数

mysql> select min(c_inprice) from commodity;
+----------------+
| min(c_inprice) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

7.分组查询【难点】

分组查询的作用是将数据进行分组。使用GROUP BY关键字实现！

示例

mysql> select * from commodity
    -> group by c_type;
+------+-----------------+----------+--------+-----------+------------+-------+
| c_id | c_name          | c_madein | c_type | c_inprice | c_outprice | c_num |
+------+-----------------+----------+--------+-----------+------------+-------+
|    1 | 变形金刚-擎天柱 | 中国     |      1 |        20 |         50 |    60 |
|   23 | 中华铅笔HB      | 中国     |      2 |         1 |          2 |   100 |
|   41 | java入门到精通  | 中国     |      3 |        30 |         66 |    15 |
+------+-----------------+----------+--------+-----------+------------+-------+
3 rows in set (0.00 sec)

问：这样查询出的结果正好就是每一个种类商品的第一条录入的记录，思考，这样的结果有意义嘛？

答：没有意义。对于分组查询来说，和聚合函数就像俩兄弟，一般都是同时出现的！

示例

mysql> # 计算每种商品的数量 平均进价 最高售价 最低售价 总库存
mysql> select c_type,count(*),avg(c_inprice),max(c_outprice),min(c_outprice),sum(c_num)
    -> from commodity
    -> group by c_type;
+--------+----------+----------------+-----------------+-----------------+------------+
| c_type | count(*) | avg(c_inprice) | max(c_outprice) | min(c_outprice) | sum(c_num) |
+--------+----------+----------------+-----------------+-----------------+------------+
|      1 |       21 |       116.5714 |            3000 |               4 |       1291 |
|      2 |       18 |        33.3333 |             460 |               2 |        814 |
|      3 |       20 |        52.6500 |             400 |               8 |        380 |
+--------+----------+----------------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)

8.HAVING子句

having子句比较特殊，作用和where是一样的，都是条件的删选。

示例

mysql> # 平均进价在你的结果表中 而不是原始数据表中的字段
mysql> # 所以where
mysql> # 是找不到avg(c_inprice)的
mysql> # 针对这样的需求，我们需要在结果中继续筛选条件
mysql> # 提供了having子句
mysql> # having的优先级比where低
mysql> select c_type,avg(c_inprice)
    -> from commodity
    -> group by c_type
    -> having avg(c_inprice)>100;
+--------+----------------+
| c_type | avg(c_inprice) |
+--------+----------------+
|      1 |       116.5714 |
+--------+----------------+
1 row in set (0.00 sec)

四、扩展

数据库的备份和还原

备份数据库

C:\Users\naixi>mysqldump -u root -p ishop > C:\Users\naixi\Desktop\ishop_bk.sql
Enter password: ****

数据库的还原先要有数据库

C:\Users\naixi>mysql -u root -p ishop < C:\Users\naixi\Desktop\ishop_bk.sql
Enter password: ****