sql笔记(查询练习与外键约束)

最新推荐文章于 2024-04-10 02:15:11 发布

急着吃饭的李先生

最新推荐文章于 2024-04-10 02:15:11 发布

阅读量241

点赞数

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/weixin_43117447/article/details/103291411

版权

数据库专栏收录该内容

7 篇文章

订阅专栏

这篇博客介绍了在SQL中创建数据表、插入数据及进行查询演练的过程，重点在于强化SQL语句的理解和使用，包括查询特定商品信息、计算平均价格、优化表结构等操作，涉及到表的连接更新和范式遵循。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 创建数据表

创建"京东"数据库

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

使用"京东"数据库

mysql> use jing_dong;
Database changed

创建一个商品goods数据表

mysql> create table good(
    -> id int unsigned primary key auto_increment,
    -> name varchar(150) not null,
    -> cate_name varchar(40) not null,-- 分类名称
    -> brand_name varchar(40) not null,-- 品牌名称
    -> price decimal(10,3) not null default 0,
    -> is_show bit not null default 1,-- 是否展示
    -> is_saleoff bit not null default 0 -- 是否卖光
    -> );
Query OK, 0 rows affected (0.02 sec)

2 插入数据

修改表名

mysql> alter table good rename to goods;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+---------------------+
| Tables_in_jing_dong |
+---------------------+
| goods               |
+---------------------+
1 row in set (0.01 sec)

mysql> insert into goods values(0,"r510vc 15.6英寸笔记本","笔记本","华硕",3399,default,default);
insert into goods values(0,"y400n 14.0英寸笔记本电脑","笔记本","联想",4999,default,default);
insert into goods values(0,"g150th 15.6英寸游戏本","游戏本","雷神",8499,default,default);
insert into goods values(0,"x550cc 15.6英寸笔记本","笔记本","华硕",2799,default,default);
insert into goods values(0,"x240 超极本","超级本","联想",4880,default,default);
insert into goods values(0,"u330p 13.3英寸超级本","超级本","联想",4299,default,default);
insert into goods values(0,"svp13226scb 触控超极本","超级本","索尼",7999,default,default);
insert into goods values(0,"ipad mini 7.9英寸平板电脑","平板电脑","苹果",1998,default,default);
insert into goods values(0,"ipad air 9.7英寸平板电脑","平板电脑","苹果",3388,default,default);
insert into goods values(0,"ipad mini 配备 retina 显示屏","平板电脑","苹果",2788,default,default);
insert into goods values(0,"ideacentre c340 20英寸一体电脑","台式机","联想",3499,default,default);
insert into goods values(0,"vostro 3800-r1206 台式电脑","台式机","戴尔",2899,default,default);
insert into goods values(0,"imac me086ch/a 21.5英寸一体电脑","台式机","苹果",9188,default,default);
insert into goods values(0,"at7-7414lp 台式电脑 linux","台式机","宏基",3699,default,default);
insert into goods values(0,"z220sff f4f06pa工作站","服务器/工作站","惠普",4288,default,default);
insert into goods values(0,"poweredge ii服务器","服务器/工作站","戴尔",5388,default,default);
insert into goods values(0,"mac pro专业级台式电脑","服务器/工作站","苹果",28888,default,default);
insert into goods values(0,"hmz-t3w 头戴显示设备","笔记本配件","索尼",6999,default,default);
insert into goods values(0," 商务双肩背包","笔记本配件","索尼",99,default,default);
insert into goods values(0,"x3250 m4机架式服务器","服务器/工作站","ibm",6888,default,default);
insert into goods values(0,"商务双肩背包","笔记本配件","索尼",99,default,default);

mysql> select * from goods;
+----+---------------------------------+---------------+------------+-----------+---------+------------+
| id | name                            | cate_name     | brand_name | price     | is_show | is_saleoff |
+----+---------------------------------+---------------+------------+-----------+---------+------------+
|  1 | r510vc 15.6英寸笔记本           | 笔记本        | 华硕       |  3399.000 |        |            |
|  2 | y400n 14.0英寸笔记本电脑        | 笔记本        | 联想       |  4999.000 |        |            |
|  3 | g150th 15.6英寸游戏本           | 游戏本        | 雷神       |  8499.000 |        |            |
|  4 | x550cc 15.6英寸笔记本           | 笔记本        | 华硕       |  2799.000 |        |            |
|  5 | x240 超极本                     | 超级本        | 联想       |  4880.000 |        |            |
|  6 | u330p 13.3英寸超级本            | 超级本        | 联想       |  4299.000 |        |            |
|  7 | svp13226scb 触控超极本          | 超级本        | 索尼       |  7999.000 |        |            |
|  8 | ipad mini 7.9英寸平板电脑       | 平板电脑      | 苹果       |  1998.000 |        |            |
|  9 | ipad air 9.7英寸平板电脑        | 平板电脑      | 苹果       |  3388.000 |        |            |
| 10 | ipad mini 配备 retina 显示屏    | 平板电脑      | 苹果       |  2788.000 |        |            |
| 11 | ideacentre c340 20英寸一体电脑  | 台式机        | 联想       |  3499.000 |        |            |
| 12 | vostro 3800-r1206 台式电脑      | 台式机        | 戴尔       |  2899.000 |        |            |
| 13 | imac me086ch/a 21.5英寸一体电脑 | 台式机        | 苹果       |  9188.000 |        |            |
| 14 | at7-7414lp 台式电脑 linux       | 台式机        | 宏基       |  3699.000 |        |            |
| 15 | z220sff f4f06pa工作站           | 服务器/工作站 | 惠普       |  4288.000 |        |            |
| 16 | poweredge ii服务器              | 服务器/工作站 | 戴尔       |  5388.000 |        |            |
| 17 | mac pro专业级台式电脑           | 服务器/工作站 | 苹果       | 28888.000 |        |            |
| 18 | hmz-t3w 头戴显示设备            | 笔记本配件    | 索尼       |  6999.000 |        |            |
| 19 | 商务双肩背包                    | 笔记本配件    | 索尼       |    99.000 |        |            |
| 20 | x3250 m4机架式服务器            | 服务器/工作站 | ibm        |  6888.000 |        |            |
| 21 | 商务双肩背包                    | 笔记本配件    | 索尼       |    99.000 |        |            |
+----+---------------------------------+---------------+------------+-----------+---------+------------+
21 rows in set (0.01 sec)

3 查询演练

3.1 sql语句的强化

查询类型cate_name为"超极本"的商品名称、价格

mysql> select name,price from goods where cate_name = "超级本";
+------------------------+----------+
| name                   | price    |
+------------------------+----------+
| x240 超极本            | 4880.000 |
| u330p 13.3英寸超级本   | 4299.000 |
| svp13226scb 触控超极本 | 7999.000 |
+------------------------+----------+
3 rows in set (0.00 sec)

显示商品的种类

mysql> select cate_name from goods group by cate_name;
+---------------+
| cate_name     |
+---------------+
| 台式机        |
| 平板电脑      |
| 服务器/工作站 |
| 游戏本        |
| 笔记本        |
| 笔记本配件    |
| 超级本        |
+---------------+
7 rows in set (0.01 sec)

求所有电脑产品的平均价格，并且保留两位小数

mysql> select round(avg(price),2) as avg_price from goods;
+-----------+
| avg_price |
+-----------+
|   5570.57 |
+-----------+
1 row in set (0.01 sec)

显示每种商品的平均价格

mysql> select cate_name,round(avg(price),2) from goods group by cate_name;
+---------------+---------------------+
| cate_name     | round(avg(price),2) |
+---------------+---------------------+
| 台式机        |             4821.25 |
| 平板电脑      |             2724.67 |
| 服务器/工作站 |            11363.00 |
| 游戏本        |             8499.00 |
| 笔记本        |             3732.33 |
| 笔记本配件    |             2399.00 |
| 超级本        |             5726.00 |
+---------------+---------------------+
7 rows in set (0.00 sec)

查询每种类型的商品中最贵、便宜、平均价、数量

mysql> select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;
+---------------+------------+------------+---------------+----------+
| cate_name     | max(price) | min(price) | avg(price)    | count(*) |
+---------------+------------+------------+---------------+----------+
| 台式机        |   9188.000 |   2899.000 |  4821.2500000 |        4 |
| 平板电脑      |   3388.000 |   1998.000 |  2724.6666667 |        3 |
| 服务器/工作站 |  28888.000 |   4288.000 | 11363.0000000 |        4 |
| 游戏本        |   8499.000 |   8499.000 |  8499.0000000 |        1 |
| 笔记本        |   4999.000 |   2799.000 |  3732.3333333 |        3 |
| 笔记本配件    |   6999.000 |     99.000 |  2399.0000000 |        3 |
| 超级本        |   7999.000 |   4299.000 |  5726.0000000 |        3 |
+---------------+------------+------------+---------------+----------+
7 rows in set (0.01 sec)

查询所有价格大于平均价格的商品，并且按价格降序排序

mysql> select * from goods where price > (select avg(price) from goods) order by price desc;
+----+---------------------------------+---------------+------------+-----------+---------+------------+
| id | name                            | cate_name     | brand_name | price     | is_show | is_saleoff |
+----+---------------------------------+---------------+------------+-----------+---------+------------+
| 17 | mac pro专业级台式电脑           | 服务器/工作站 | 苹果       | 28888.000 |        |            |
| 13 | imac me086ch/a 21.5英寸一体电脑 | 台式机        | 苹果       |  9188.000 |        |            |
|  3 | g150th 15.6英寸游戏本           | 游戏本        | 雷神       |  8499.000 |        |            |
|  7 | svp13226scb 触控超极本          | 超级本        | 索尼       |  7999.000 |        |            |
| 18 | hmz-t3w 头戴显示设备            | 笔记本配件    | 索尼       |  6999.000 |        |            |
| 20 | x3250 m4机架式服务器            | 服务器/工作站 | ibm        |  6888.000 |        |            |
+----+---------------------------------+---------------+------------+-----------+---------+------------+
6 rows in set (0.01 sec)

查询每种类型中最贵的电脑信息

mysql> select goods.* from goods inner join (select cate_name,max(price) as max_price from goods group by cate_name) as tmp on goods.cate_name=tmp.cate_name and goods.price=tmp.max_price;
+----+---------------------------------+---------------+------------+-----------+---------+------------+
| id | name                            | cate_name     | brand_name | price     | is_show | is_saleoff |
+----+---------------------------------+---------------+------------+-----------+---------+------------+
|  2 | y400n 14.0英寸笔记本电脑        | 笔记本        | 联想       |  4999.000 |        |            |
|  3 | g150th 15.6英寸游戏本           | 游戏本        | 雷神       |  8499.000 |        |            |
|  7 | svp13226scb 触控超极本          | 超级本        | 索尼       |  7999.000 |        |            |
|  9 | ipad air 9.7英寸平板电脑        | 平板电脑      | 苹果       |  3388.000 |        |            |
| 13 | imac me086ch/a 21.5英寸一体电脑 | 台式机        | 苹果       |  9188.000 |        |            |
| 17 | mac pro专业级台式电脑           | 服务器/工作站 | 苹果       | 28888.000 |        |            |
| 18 | hmz-t3w 头戴显示设备            | 笔记本配件    | 索尼       |  6999.000 |        |            |
+----+---------------------------------+---------------+------------+-----------+---------+------------+
7 rows in set (0.01 sec)

4 表的优化

上表显然不遵守第三范式，所以需要进行优化

4.1 创建"商品分类"表

mysql> create table if not exists goods_cates(
    -> id int unsigned primary key auto_increment,
    -> name varchar(40) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

查询有哪些分类

mysql> select cate_name from goods group by cate_name;
+---------------+
| cate_name     |
+---------------+
| 台式机        |
| 平板电脑      |
| 服务器/工作站 |
| 游戏本        |
| 笔记本        |
| 笔记本配件    |
| 超级本        |
+---------------+
7 rows in set (0.00 sec)

将查询的结果插入到某一个表中

mysql> insert into goods_cates(name) select cate_name from goods group by cate_name;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from goods_cates;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 台式机        |
|  2 | 平板电脑      |
|  3 | 服务器/工作站 |
|  4 | 游戏本        |
|  5 | 笔记本        |
|  6 | 笔记本配件    |
|  7 | 超级本        |
+----+---------------+
7 rows in set (0.00 sec)