Day02[20200715]_数据库进阶

一、作业讲解

整个的作业总结：

1. 自主学习能力还需提高
2. 上课不认真听讲，作业不仔细的问题比较突出！

1.Typora的使用和设置

2.myschool数据库和数据表的创建

（1）创建数据库

登录MySQL客户端

C:\Users\naixi>mysql -h localhost -P 3306 -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

创建数据库

这个数据需要用到中文，在创建数据库的时候，需要设置默认字符集为utf8

mysql> create database myschool default charset=utf8;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ishop              |
| myschool           |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
8 rows in set (0.00 sec)

mysql> use myschool;
Database changed
mysql>

（2）数据库模型图

关于fk的命名，每个公司都会有自己的完整的命名规范。

参阅MSSQL的命名规范：fk_1 -> fk_student_grand。

我这里就简单的以fk_数字的形式进行命名，是为了授课的方便。

整个数据库中（myschool）中外键的命名是不能重复的！

（3）创建数据表grand

这里比较集中的一个问题

1. create，很多同学竟然写成creat。单词写错不应该。
2. int不允许简写，养成习惯int(11)

mysql> create table grand(
    -> id int(11) primary key,
    -> name varchar(50) not null
    -> );
Query OK, 0 rows affected (1.17 sec)

mysql> show tables;
+--------------------+
| Tables_in_myschool |
+--------------------+
| grand              |
+--------------------+
1 row in set (0.00 sec)

mysql> desc grand;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.13 sec)

（4）创建数据表student

比较集中的问题：

1. 外键约束添加失败

• 字段名写错
• 外键命名规范混乱

2. 错误率太高了

• 需要大家尽快将错误率减少到一个可以接受的范围内

3. 中文的问题

mysql> create table student(
    -> no varchar(50) primary key,
    -> pwd varchar(20) not null default '123456',
    -> name varchar(50) not null,
    -> sex char(2) not null,
    -> grand_id int(11) not null,
    -> phone varchar(255) not null,
    -> address varchar(255) not null default '学生宿舍',
    -> borndate date ,
    -> email varchar(50) default null,
    -> constraint fk_1 foreign key (grand_id) references grand (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> desc student;
+----------+--------------+------+-----+----------+-------+
| Field    | Type         | Null | Key | Default  | Extra |
+----------+--------------+------+-----+----------+-------+
| no       | varchar(50)  | NO   | PRI | NULL     |       |
| pwd      | varchar(20)  | NO   |     | 123456   |       |
| name     | varchar(50)  | NO   |     | NULL     |       |
| sex      | char(2)      | NO   |     | NULL     |       |
| grand_id | int(11)      | NO   | MUL | NULL     |       |
| phone    | varchar(255) | NO   |     | NULL     |       |
| address  | varchar(255) | NO   |     | 学生宿舍 |       |
| borndate | date         | YES  |     | NULL     |       |
| email    | varchar(50)  | YES  |     | NULL     |       |
+----------+--------------+------+-----+----------+-------+
9 rows in set (0.01 sec)

关于符号没有闭合

（5）创建数据表subject

这里遇见的问题并不多，主要仔细即可

mysql> create table subject(
    -> id int(11) primary key,
    -> name varchar(50) not null,
    -> hour int(11) not null,
    -> grand_id int(11) not null,
    -> constraint fk_4 foreign key (grand_id) references grand (id)
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql> desc subject;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(50) | NO   |     | NULL    |       |
| hour     | int(11)     | NO   |     | NULL    |       |
| grand_id | int(11)     | NO   | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

（6）创建数据表result

主要问题是对于类似有多个外键的时候，需要同学们注意外键的字段名称不要搞错。

mysql> create table result(
    -> id int(11) primary key,
    -> student_no varchar(50) not null,
    -> subject_id int(11) not null,
    -> result int(11) ,
    -> examdate datetime not null,
    -> constraint fk_2 foreign key (student_no) references student (no),
    -> constraint fk_3 foreign key (subject_id) references subject (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc result;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   | PRI | NULL    |       |
| student_no | varchar(50) | NO   | MUL | NULL    |       |
| subject_id | int(11)     | NO   | MUL | NULL    |       |
| result     | int(11)     | YES  |     | NULL    |       |
| examdate   | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

作业总结

• 无他，唯手熟尔！

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

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: ****

五、作业

使用md将今天的第三章（单表查询）在自己的数据库中敲一遍。

所有的需求、命令和我一致，也需要相同的代码块、解释等