# 查询 ## 查询列字段 ``` # SELECT *查询所有 mysql> SELECT product_id ,product_name -> FROM Product -> ; +------------+--------------+ | product_id | product_name | +------------+--------------+ | 0001 | T恤 | | 0002 | 打孔器 | | 0003 | 运动T恤 | | 0004 | 菜刀 | | 0005 | 高压锅 | | 0006 | 叉子 | | 0007 | 擦菜板 | | 0008 | 圆珠笔 | +------------+--------------+ 8 rows in set (0.00 sec) ``` ## AS 设置别名 ```sql mysql> SELECT product_id AS '编号',product_name AS '产品名' FROM Product; +--------+------------+ | 编号 | 产品名 | +--------+------------+ | 0001 | T恤 | | 0002 | 打孔器 | | 0003 | 运动T恤 | | 0004 | 菜刀 | | 0005 | 高压锅 | | 0006 | 叉子 | | 0007 | 擦菜板 | | 0008 | 圆珠笔 | +--------+------------+ 8 rows in set (0.00 sec) ``` ## 设置常数 把'商品'，38这些常量指定一个列字段，同时查询其它表自由字段，组合为一个新的数据返回 ```sql mysql> mysql> SELECT '商品' AS string , 38 AS number,'2009-02-24' AS date,product_name FROM Product; +--------+--------+------------+--------------+ | string | number | date | product_name | +--------+--------+------------+--------------+ | 商品 | 38 | 2009-02-24 | T恤 | | 商品 | 38 | 2009-02-24 | 打孔器 | | 商品 | 38 | 2009-02-24 | 运动T恤 | | 商品 | 38 | 2009-02-24 | 菜刀 | | 商品 | 38 | 2009-02-24 | 高压锅 | | 商品 | 38 | 2009-02-24 | 叉子 | | 商品 | 38 | 2009-02-24 | 擦菜板 | | 商品 | 38 | 2009-02-24 | 圆珠笔 | +--------+--------+------------+--------------+ 8 rows in set (0.00 sec) ``` ## 去除重复元素 ``` mysql> SELECT product_type FROM Product; +--------------+ | product_type | +--------------+ | 衣服 | | 办公用品 | | 衣服 | | 厨房用具 | | 厨房用具 | | 厨房用具 | | 厨房用具 | | 办公用品 | +--------------+ 8 rows in set (0.00 sec) mysql> SELECT DISTINCT(product_type) FROM Product; +--------------+ | product_type | +--------------+ | 衣服 | | 办公用品 | | 厨房用具 | +--------------+ 3 rows in set (0.00 sec) #PS:也可以多个字段组合的去重， DISTINCT(c1,c2,..) ``` ## WHERE过滤条件 ``` #PS :日期也行 mysql> SELECT * FROM Product WHERE regist_date>'2009-05-01'; +------------+--------------+--------------+------------+----------------+-------------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | +------------+--------------+--------------+------------+----------------+-------------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | | 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 | | 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | +------------+--------------+--------------+------------+----------------+-------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM Product WHERE product_type = '衣服'; +------------+--------------+--------------+------------+----------------+-------------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | +------------+--------------+--------------+------------+----------------+-------------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | | 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL | +------------+--------------+--------------+------------+----------------+-------------+ 2 rows in set (0.00 sec) ``` ## 注释 ``` --- 单行注释 /* */ 间可跨行注释 mysql> SELECT * FROM Product WHERE product_type = '衣服' -> /* /*> ddddd /*> dd /*> */ -> ; +------------+--------------+--------------+------------+----------------+-------------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | +------------+--------------+--------------+------------+----------------+-------------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | | 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL | +------------+--------------+--------------+------------+----------------+-------------+ 2 rows in set (0.00 sec) ``` ## 比较运算 ``` #PS:与NULL相关的计算结果是NULL #不等于用 <> #字符串比较时：按照字典顺序比较的 mysql> SELECT product_name,sale_price,sale_price*2 AS double_sale_price -> FROM Product; +--------------+------------+-------------------+ | product_name | sale_price | double_sale_price | +--------------+------------+-------------------+ | T恤 | 1000 | 2000 | | 打孔器 | 500 | 1000 | | 运动T恤 | 4000 | 8000 | | 菜刀 | 3000 | 6000 | | 高压锅 | 6800 | 13600 | | 叉子 | 500 | 1000 | | 擦菜板 | 880 | 1760 | | 圆珠笔 | 100 | 200 | +--------------+------------+-------------------+ 8 rows in set (0.00 sec) #比较符号对NULL字段不起作用，匹配NULL用：WHERE 列名 IS NULL; mysql> SELECT product_name,purchase_price -> FROM Product -> WHERE purchase_price IS NULL; +--------------+----------------+ | product_name | purchase_price | +--------------+----------------+ | 叉子 | NULL | | 圆珠笔 | NULL | +--------------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT product_name,purchase_price FROM Product WHERE purchase_price IS NOT NULL; +--------------+----------------+ | product_name | purchase_price | +--------------+----------------+ | T恤 | 500 | | 打孔器 | 320 | | 运动T恤 | 2800 | | 菜刀 | 2800 | | 高压锅 | 5000 | | 擦菜板 | 790 | +--------------+----------------+ 6 rows in set (0.00 sec) ``` ``` #PS 如下结果都是空 SELECT * FROM Product WHERE purchase_price = NULL; SELECT * FROM Product WHERE purchase_price <> NULL; SELECT * FROM Product WHERE purchase_price > NULL; SELECT * FROM Product WHERE purchase_price < NULL; ``` ## 逻辑运算 在WHERE中使用AND、OR，AND优先级大于OR，一起用的时候记得套括号 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://im-qianuxn.gitbook.io/pytorch/ji-suan-ji/shu-ju-ku/mysql/select.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.