SQL入门经典—CHAPTER7数据库查询

最新推荐文章于 2024-08-12 07:00:00 发布
Mm901
最新推荐文章于 2024-08-12 07:00:00 发布
阅读量148
点赞数
分类专栏： mysql 数据库
本文链接：https://blog.csdn.net/zhjulia123/article/details/81773206
版权
mysql 同时被 2 个专栏收录
8 篇文章 0 订阅
订阅专栏
数据库
5 篇文章 0 订阅
订阅专栏
--7.2.1 SELECT语句
--语法
SELECT [ * | ALL | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE1 [ , TABLE2 ];

--*
mysql> SELECT * FROM PRODUCTS_TBL;
+---------+-------------------------+-------+
| PROD_ID | PROD_DESC               | COST  |
+---------+-------------------------+-------+
|       9 | CANDY CORN              | 1.35  |
|      13 | FALSE PARAFFIN TEETH    | 1.1   |
|      15 | ASSORTED COSTUMES       | 10    |
|      90 | LIGHTED LANTERNS        | 14.5  |
|     222 | PLASTIC PUMPKIN 18 INCH | 7.75  |
|     301 | FIREMAN COSTUME         | 29.99 |
|     302 | POLICEMAN COSTUME       | 29.99 |
|     303 | KIDDIE GRAB BAG         | 4.99  |
|     777 | CANDY CORN              | 2.15  |
|    7725 | LEATHER GLOVES          | 24.99 |
|   11235 | WITCH COSTUME           | 29.99 |
+---------+-------------------------+-------+
11 rows in set

--单列
mysql> SELECT PROD_DESC FROM PRODUCTS_TBL;
+-------------------------+
| PROD_DESC               |
+-------------------------+
| CANDY CORN              |
| FALSE PARAFFIN TEETH    |
| ASSORTED COSTUMES       |
| LIGHTED LANTERNS        |
| PLASTIC PUMPKIN 18 INCH |
| FIREMAN COSTUME         |
| POLICEMAN COSTUME       |
| KIDDIE GRAB BAG         |
| CANDY CORN              |
| LEATHER GLOVES          |
| WITCH COSTUME           |
+-------------------------+
11 rows in set

--ALL是多余的，默认为ALL，可不加
mysql> SELECT ALL PROD_DESC FROM PRODUCTS_TBL;
+-------------------------+
| PROD_DESC               |
+-------------------------+
| CANDY CORN              |
| FALSE PARAFFIN TEETH    |
| ASSORTED COSTUMES       |
| LIGHTED LANTERNS        |
| PLASTIC PUMPKIN 18 INCH |
| FIREMAN COSTUME         |
| POLICEMAN COSTUME       |
| KIDDIE GRAB BAG         |
| CANDY CORN              |
| LEATHER GLOVES          |
| WITCH COSTUME           |
+-------------------------+
11 rows in set

--DISTINCT去除重复记录
mysql> SELECT DISTINCT PROD_DESC FROM PRODUCTS_TBL;
+-------------------------+
| PROD_DESC               |
+-------------------------+
| CANDY CORN              |
| FALSE PARAFFIN TEETH    |
| ASSORTED COSTUMES       |
| LIGHTED LANTERNS        |
| PLASTIC PUMPKIN 18 INCH |
| FIREMAN COSTUME         |
| POLICEMAN COSTUME       |
| KIDDIE GRAB BAG         |
| LEATHER GLOVES          |
| WITCH COSTUME           |
+-------------------------+
10 rows in set

--DISTINCT(COLUMN) 一般加圆括号增加可读性
mysql> SELECT DISTINCT (PROD_DESC) FROM PRODUCTS_TBL;
+-------------------------+
| PROD_DESC               |
+-------------------------+
| CANDY CORN              |
| FALSE PARAFFIN TEETH    |
| ASSORTED COSTUMES       |
| LIGHTED LANTERNS        |
| PLASTIC PUMPKIN 18 INCH |
| FIREMAN COSTUME         |
| POLICEMAN COSTUME       |
| KIDDIE GRAB BAG         |
| LEATHER GLOVES          |
| WITCH COSTUME           |
+-------------------------+
10 rows in set

--7.2.2 FROM 子句
--语法
SELECT TABLE1 [ , TABLE2]

mysql> SELECT * FROM EMPLOYEE_TBL;
+----------+----------------+--------------+-------------+--------+---------+-----------+-----------+
| EMP_ID   | EMP_NAME       | EMP_ST_ADDR  | EMP_CITY    | EMP_ST | EMP_ZIP | EMP_PHONE | EMP_PAGER |
+----------+----------------+--------------+-------------+--------+---------+-----------+-----------+
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |
+----------+----------------+--------------+-------------+--------+---------+-----------+-----------+
1 row in set

mysql> SELECT * FROM EMPLOYEE_TBL, PRODUCTS_TBL;
+----------+----------------+--------------+-------------+--------+---------+-----------+-----------+---------+-------------------------+-------+
| EMP_ID   | EMP_NAME       | EMP_ST_ADDR  | EMP_CITY    | EMP_ST | EMP_ZIP | EMP_PHONE | EMP_PAGER | PROD_ID | PROD_DESC               | COST  |
+----------+----------------+--------------+-------------+--------+---------+-----------+-----------+---------+-------------------------+-------+
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |       9 | CANDY CORN              | 1.35  |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |      13 | FALSE PARAFFIN TEETH    | 1.1   |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |      15 | ASSORTED COSTUMES       | 10    |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |      90 | LIGHTED LANTERNS        | 14.5  |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |     222 | PLASTIC PUMPKIN 18 INCH | 7.75  |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |     301 | FIREMAN COSTUME         | 29.99 |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |     302 | POLICEMAN COSTUME       | 29.99 |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |     303 | KIDDIE GRAB BAG         | 4.99  |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |     777 | CANDY CORN              | 2.15  |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |    7725 | LEATHER GLOVES          | 24.99 |
| 12345678 | SMITH JAY JOHN | 12 BEACON CT | INDIANAPLIS | IN     |   46222 | 317299686 | NULL      |   11235 | WITCH COSTUME           | 29.99 |
+----------+----------------+--------------+-------------+--------+---------+-----------+-----------+---------+-------------------------+-------+
11 rows in set

--7.2.3 WHERE 子句
--语法
SELECT [ ALL | * | DISTINCT COLUMN1， COLUMN2 ] 
FROM TABLE1 [ , TABLE2 ]
WHERE [ CONDITION1 | EXPRESSION1 ]
[ AND|OR CONDITION2 | EXPRESSION2 ]

--7.2.4 ORDER BY 子句
--语法:默认升序
SELECT [ ALL | * | DISTINCT COLUMN1，COLUMN2 ]
FROM TABLE1 [ , TABLE2 ]
WHERE [ CONDITION1 | EXPRESSION1 ]
[ AND|OR CONDITION2 | EXPRESSION2]
ORDER BY COLUMN1| INTEGER [ ASC| DESC ]

--ORDER BY后1、2、3代表PROD_DESC、PROD_ID、COST列
mysql> SELECT * FROM PRODUCTS_TBL;
+---------+-------------------------+-------+
| PROD_ID | PROD_DESC               | COST  |
+---------+-------------------------+-------+
|       9 | CANDY CORN              | 1.35  |
|      13 | FALSE PARAFFIN TEETH    | 1.1   |
|      15 | ASSORTED COSTUMES       | 10    |
|      90 | LIGHTED LANTERNS        | 14.5  |
|     222 | PLASTIC PUMPKIN 18 INCH | 7.75  |
|     301 | FIREMAN COSTUME         | 29.99 |
|     302 | POLICEMAN COSTUME       | 29.99 |
|     303 | KIDDIE GRAB BAG         | 4.99  |
|     777 | CANDY CORN              | 2.15  |
|    7725 | LEATHER GLOVES          | 24.99 |
|   11235 | WITCH COSTUME           | 29.99 |
+---------+-------------------------+-------+
11 rows in set

mysql> SELECT PROD_DESC, PROD_ID, COST
FROM PRODUCTS_TBL
WHERE COST < 20
ORDER BY 1;
+-------------------------+---------+------+
| PROD_DESC               | PROD_ID | COST |
+-------------------------+---------+------+
| ASSORTED COSTUMES       |      15 | 10   |
| CANDY CORN              |       9 | 1.35 |
| CANDY CORN              |     777 | 2.15 |
| FALSE PARAFFIN TEETH    |      13 | 1.1  |
| KIDDIE GRAB BAG         |     303 | 4.99 |
| LIGHTED LANTERNS        |      90 | 14.5 |
| PLASTIC PUMPKIN 18 INCH |     222 | 7.75 |
+-------------------------+---------+------+
7 rows in set

mysql> SELECT PROD_DESC, PROD_ID, COST
FROM PRODUCTS_TBL
WHERE COST < 20
ORDER BY 2;
+-------------------------+---------+------+
| PROD_DESC               | PROD_ID | COST |
+-------------------------+---------+------+
| CANDY CORN              |       9 | 1.35 |
| FALSE PARAFFIN TEETH    |      13 | 1.1  |
| ASSORTED COSTUMES       |      15 | 10   |
| LIGHTED LANTERNS        |      90 | 14.5 |
| PLASTIC PUMPKIN 18 INCH |     222 | 7.75 |
| KIDDIE GRAB BAG         |     303 | 4.99 |
| CANDY CORN              |     777 | 2.15 |
+-------------------------+---------+------+
7 rows in set

--7.2.5 大小写敏感性
--SQL命令和关键字不区分大小写。数据大小写：MySQL和Microsoft SQL Server默认大小写不敏感，Oracle默认大小写敏感。。

--7.3.1COUNT 统计表里的记录数量
--语法
SELECT COUNT(*)
FROM TABLE_NAME;

mysql> SELECT * FROM PRODUCTS_TBL;
+---------+-------------------------+-------+
| PROD_ID | PROD_DESC               | COST  |
+---------+-------------------------+-------+
|       9 | CANDY CORN              | 1.35  |
|      13 | FALSE PARAFFIN TEETH    | 1.1   |
|      15 | ASSORTED COSTUMES       | 10    |
|      90 | LIGHTED LANTERNS        | 14.5  |
|     222 | PLASTIC PUMPKIN 18 INCH | 7.75  |
|     301 | FIREMAN COSTUME         | 29.99 |
|     302 | POLICEMAN COSTUME       | 29.99 |
|     303 | KIDDIE GRAB BAG         | 4.99  |
|     777 | CANDY CORN              | 2.15  |
|    7725 | LEATHER GLOVES          | 24.99 |
|   11235 | WITCH COSTUME           | 29.99 |
+---------+-------------------------+-------+
11 rows in set

mysql> SELECT COUNT(*) FROM PRODUCTS_TBL;
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
1 row in set

--出现值的种类数
mysql> SELECT COUNT(DISTINCT PROD_DESC) FROM PRODUCTS_TBL;
+---------------------------+
| COUNT(DISTINCT PROD_DESC) |
+---------------------------+
|                        10 |
+---------------------------+
1 row in set