《数据库系统实训》实验报告——单表查询

末蓝、 2022-11-15 14:19 257阅读 0赞

# **检索数据** #

**题号： 1.1**

**SQL****语句描述：**select prod\_id from products;

**查询功能描述：**查询所有的产品ID；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70][]

# **排序数据** #

**题号：** **2.1**

**SQL****语句描述：**select distinct prod\_id  from products order by prod\_id;

**查询功能描述：**查询所有的产品ID并且排序；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 1][]

# **过滤数据** #

**题号： ****3.1**

**SQL****语句描述：**select prod\_price, prod\_name from products where prod\_price=2.50;

**查询功能描述：**查询价格等于2.50的产品ID、产品价格、产品名称；

**执行结果截图：**![20210401233056626.png][]

# **数据过滤** #

**题号：** **4.1**

**SQL****语句描述：**select prod\_id, prod\_price, prod\_name from products where vend\_id=1003 and prod\_price<=10;

**查询功能描述：**查询ID为1003并且价格小于等于10的产品ID、产品价格、产品名称；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 2][]

# **通配符** #

**题号：** **5.1**

**SQL****语句描述：**select prod\_id, prod\_price, prod\_name from products where prod\_name  LIKE 's%e';

**查询功能描述：**查询产品名称以s开头的产品ID、产品价格、产品名称；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 3][]

# **正则表达式** #

**题号：** **6.1**

**SQL****语句描述：**select prod\_id, prod\_name from products where prod\_name REGEXP '1000' ORDER BY prod\_name;

**查询功能描述：**查询包含1000的产品ID、产品名称并且按产品名称排序；

**执行结果截图：**![20210401232953997.png][]

# **创建计算字段** #

**题号：** **7.1**

**SQL****语句描述：**select concat(vend\_name,' (',vend\_country,')') as 'sss' from vendors order by vend\_name;

**查询功能描述：**合并商家名称和商家国家并且按商家名称排序；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 4][]

# **数据处理函数** #

**题号：** **8.1**

**SQL****语句描述：**select vend\_name,upper(vend\_name) as vend\_name\_upcase from vendors order by vend\_name;

**查询功能描述：**查询所有的商家名称和商家大写名称并且按商家名称排序；

**执行结果截图：**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 5][]

# **聚集函数** #

**题号：** **9.1**

**SQL****语句描述：**select avg(prod\_price) as avg\_price from products where vend\_id=1003;

**查询功能描述：**查询商家ID为1003的商品平均价格；

**执行结果截图：**![20210401232925520.png][]

# **分组数据** #

**题号：** **10.1**

**SQL****语句描述：**select count(\*) as num\_prods from products where vend\_id=1003;

**查询功能描述：**查询商家ID为1003的商品数量；

**执行结果截图：**![20210401232915271.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70]: /images/20221022/ac048cfeb8d247ffbb36a4bdb28cfa8f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 1]: /images/20221022/814beebde2c34079b292269c7d10eea0.png
[20210401233056626.png]: /images/20221022/b1a7101c419e4018a1e1c17b0c2724ee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 2]: /images/20221022/e61db983382b497a8cfd5f1b82531e90.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 3]: /images/20221022/12868ef9dc5742b88e120f251af10ca9.png
[20210401232953997.png]: /images/20221022/ae552a715f904e949e44d3cf003c386b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 4]: /images/20221022/b203086ae8f842b5899056a1a2a74fde.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzI3Mjc4MQ_size_16_color_FFFFFF_t_70 5]: /images/20221022/cda1db85f5b54a669b85c95114917b72.png
[20210401232925520.png]: /images/20221022/7b53d51a4a5d4ad7b032886a0222ce68.png
[20210401232915271.png]: /images/20221022/8473fbb3727f4a95a599b3c5bd23f63a.png