> DQL (Data Query Lanuage) 基本查询
基于查询语句,由select 语句及其子语句组成。
SELECT: 基本语句
FROM: 必备子语句
WHERE: 可选子语句
ORDER BY: 可选子语句
1. 主要(典型)语法syntax:
SELECT [* | ALL | DISTINCT column1, colum2 ]
FROM TABLE1 [, TABLE2]
WHERE [condition1 | expression1 and|or condition 2 | expression2]
ORDER BY colum1 | integer [ASC | DESC]
2. 统计表中的记录数量:
SELECT COUNT(*)
FROM TABLE_NAME;
3. 使用字段别名:
SELECT column_name alias_name
FROM TABLE_NAME;
选项*: 表示输出表中的全部字段
选项ALL: 默认的操作
选项DISTINCT: 用于禁止输出重复的行
选项ORDER BY:默认为升序排列。ASC: 升序,DESC: 降序
注意:
不同的数据库系统,对大小写的敏感性不同。
mysql 不区分大小写,而Oracle 区分大小写。
练习:
For exmaple:
products_tbl,是我之前建好的表。可用用desc查看其字段,用select * from products_tbl 读取所有数据。
实例操作如下:
mysql> desc products_tbl;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| pro_id | varchar(10) | NO | | NULL | |
| pro_desc | varchar(20) | NO | | NULL | |
| cost | decimal(6,2) | NO | | NULL | |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM products_tbl;
+--------+-----------------+-------+
| pro_id | pro_desc | cost |
+--------+-----------------+-------+
| 7725 | Leather gloves | 24.99 |
| 7723 | trousers | 15.99 |
| 303 | kiddie grab bag | 4.99 |
| 7710 | trousers | 14.66 |
| 7710 | trousers | 19.66 |
+--------+-----------------+-------+
5 rows in set (0.00 sec)
mysql> SELECT pro_id
-> FROM products_tbl;
+--------+
| pro_id |
+--------+
| 7725 |
| 7723 |
| 303 |
| 7710 |
| 7710 |
+--------+
5 rows in set (0.00 sec)
mysql> SELECT pro_id, pro_desc
-> FROM products_tbl;
+--------+-----------------+
| pro_id | pro_desc |
+--------+-----------------+
| 7725 | Leather gloves |
| 7723 | trousers |
| 303 | kiddie grab bag |
| 7710 | trousers |
| 7710 | trousers |
+--------+-----------------+
5 rows in set (0.00 sec)
mysql> SELECT *
-> FROM products_tbl
-> WHERE cost < 10 or cost > 20;
+--------+-----------------+-------+
| pro_id | pro_desc | cost |
+--------+-----------------+-------+
| 7725 | Leather gloves | 24.99 |
| 303 | kiddie grab bag | 4.99 |
+--------+-----------------+-------+
2 rows in set (0.00 sec)
mysql> SELECT *
-> FROM products_tbl
-> order by cost ;
+--------+-----------------+-------+
| pro_id | pro_desc | cost |
+--------+-----------------+-------+
| 303 | kiddie grab bag | 4.99 |
| 7710 | trousers | 14.66 |
| 7723 | trousers | 15.99 |
| 7710 | trousers | 19.66 |
| 7725 | Leather gloves | 24.99 |
+--------+-----------------+-------+
5 rows in set (0.00 sec)
mysql> SELECT *
-> FROM products_tbl
-> order by cost ASC;
+--------+-----------------+-------+
| pro_id | pro_desc | cost |
+--------+-----------------+-------+
| 303 | kiddie grab bag | 4.99 |
| 7710 | trousers | 14.66 |
| 7723 | trousers | 15.99 |
| 7710 | trousers | 19.66 |
| 7725 | Leather gloves | 24.99 |
+--------+-----------------+-------+
5 rows in set (0.00 sec)
mysql> SELECT *
-> FROM products_tbl
-> order by cost DESC;
+--------+-----------------+-------+
| pro_id | pro_desc | cost |
+--------+-----------------+-------+
| 7725 | Leather gloves | 24.99 |
| 7710 | trousers | 19.66 |
| 7723 | trousers | 15.99 |
| 7710 | trousers | 14.66 |
| 303 | kiddie grab bag | 4.99 |
+--------+-----------------+-------+
5 rows in set (0.00 sec)
mysql> SELECT count(*)
-> FROM products_tbl;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*)
-> FROM products_tbl
-> where cost < 20;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT pro_id id
-> FROM products_tbl;
+------+
| id |
+------+
| 7725 |
| 7723 |
| 303 |
| 7710 |
| 7710 |
+------+
5 rows in set (0.00 sec)
mysql> SELECT pro_id, pro_id id
-> FROM products_tbl;
+--------+------+
| pro_id | id |
+--------+------+
| 7725 | 7725 |
| 7723 | 7723 |
| 303 | 303 |
| 7710 | 7710 |
| 7710 | 7710 |
+--------+------+
5 rows in set (0.00 sec)
mysql> SELECT PRO_ID, PRO_ID ID, PRO_DESC, PRO_DESC PRODUCT
-> FROM PRODUCTS_TBL;
+--------+------+-----------------+-----------------+
| PRO_ID | ID | PRO_DESC | PRODUCT |
+--------+------+-----------------+-----------------+
| 7725 | 7725 | Leather gloves | Leather gloves |
| 7723 | 7723 | trousers | trousers |
| 303 | 303 | kiddie grab bag | kiddie grab bag |
| 7710 | 7710 | trousers | trousers |
| 7710 | 7710 | trousers | trousers |
+--------+------+-----------------+-----------------+
5 rows in set (0.00 sec)
mysql>