本文主要是阅读《SQL 基础教程》这本书总结而来,有需求的话可以阅读原著。
1. SELECT 语句基础
1.1 列的查询
- 语法结构
- 在语句中可以指定多列,显示会根据指定的列进行显示
select <列名>,...... from <表名>;
- 示例演示
mysql> select shohin_id, shohin_mei, shiire_ranka from Shohin;
+-----------+------------+--------------+
| shohin_id | shohin_mei | shiire_ranka |
+-----------+------------+--------------+
| 0001 | TT | 500 |
| 0002 | Ta | 10 |
| 0003 | xa | NULL |
| 0004 | 打孔器 | 320 |
| 0005 | 叉子 | 320 |
| 0006 | T恤衫 | 500 |
| 0007 | T恤衫 | 990 |
+-----------+------------+--------------+
7 rows in set (0.00 sec)
1.2 查询出表中所有的列
- 语法结构
- 可以指定全部的列进行输出也可以查询表中的所有列
- 使用星号的话,就无法设定列的显示顺序了
select * from <表名>;
- 示例演示
mysql> select * from Shohin;
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_ranka | torokubi |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001 | TT | yifu | 1000 | 500 | 2016-05-20 |
| 0002 | Ta | hhh | 10 | 10 | 2016-05-20 |
| 0003 | xa | hhh | NULL | NULL | 2016-05-21 |
| 0004 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0005 | 叉子 | 餐具 | 500 | 320 | 2009-09-11 |
| 0006 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0007 | T恤衫 | 衣服 | 2000 | 990 | 2009-09-20 |
+-----------+------------+---------------+--------------+--------------+------------+
7 rows in set (0.00 sec)
1.3 为列设定别名
- 语法结构
- 设定中文别名时需要使用双引号括起来
使用 as 关键字进行设定
- 示例演示
# 为列设定别名
mysql> select shohin_id as id,
-> shohin_mei as name
-> from Shohin;
+------+-----------+
| id | name |
+------+-----------+
| 0001 | TT |
| 0002 | Ta |
| 0003 | xa |
| 0004 | 打孔器 |
| 0005 | 叉子 |
| 0006 | T恤衫 |
| 0007 | T恤衫 |
+------+-----------+
7 rows in set (0.01 sec)
# 为列设定中文别名
mysql> select shohin_id as "商品编号",
-> shohin_mei as "商品名称"
-> from Shohin;
+--------------+--------------+
| 商品编号 | 商品名称 |
+--------------+--------------+
| 0001 | TT |
| 0002 | Ta |
| 0003 | xa |
| 0004 | 打孔器 |
| 0005 | 叉子 |
| 0006 | T恤衫 |
| 0007 | T恤衫 |
+--------------+--------------+
7 rows in set (0.00 sec)
1.4 常数的查询
- 语法结构
select 字句中不仅可以书写列名,而且还可以书写常数
- 示例演示
mysql> select '商品' as shangpin,
-> 28 as jianshu,
-> '2016-05-20' as date,
-> shohin_id, shohin_mei
-> from Shohin;
+----------+---------+------------+-----------+------------+
| shangpin | jianshu | date | shohin_id | shohin_mei |
+----------+---------+------------+-----------+------------+
| 商品 | 28 | 2016-05-20 | 0001 | TT |
| 商品 | 28 | 2016-05-20 | 0002 | Ta |
| 商品 | 28 | 2016-05-20 | 0003 | xa |
| 商品 | 28 | 2016-05-20 | 0004 | 打孔器 |
| 商品 | 28 | 2016-05-20 | 0005 | 叉子 |
| 商品 | 28 | 2016-05-20 | 0006 | T恤衫 |
| 商品 | 28 | 2016-05-20 | 0007 | T恤衫 |
+----------+---------+------------+-----------+------------+
7 rows in set (0.00 sec)
1.5 从结果中删除重复行
- 语法结构
- distinct 关键字可以将包含null的数据列出来
- 在多列中使用 distinct 时,只能用在第一个列名之前
使用 distinct 关键字
- 示例演示
mysql> select distinct shohin_mei from Shohin;
+------------+
| shohin_mei |
+------------+
| TT |
| Ta |
| xa |
| 打孔器 |
| 叉子 |
| T恤衫 |
+------------+
6 rows in set (0.00 sec)
mysql> select distinct shiire_ranka from Shohin;
+--------------+
| shiire_ranka |
+--------------+
| 500 |
| 10 |
| NULL |
| 320 |
| 990 |
+--------------+
5 rows in set (0.00 sec)
mysql> select distinct shohin_id, shohin_mei from Shohin;
+-----------+------------+
| shohin_id | shohin_mei |
+-----------+------------+
| 0001 | TT |
| 0002 | Ta |
| 0003 | xa |
| 0004 | 打孔器 |
| 0005 | 叉子 |
| 0006 | T恤衫 |
| 0007 | T恤衫 |
+-----------+------------+
7 rows in set (0.00 sec)
1.6 根据 where 语句来选择记录
- 语法结构
- 通过where字句查询符合指定条件的记录,然后在选取出select语句指定的列
- 这里可以选取作为查询条件的列,也可以不选取
- where 字句要紧跟在from字句之后
select <列名>,.....
from <表名>
where <条件表达式>;
- 示例演示
mysql> select shohin_id,shohin_mei from Shohin where shohin_mei='T恤衫';
+-----------+------------+
| shohin_id | shohin_mei |
+-----------+------------+
| 0006 | T恤衫 |
| 0007 | T恤衫 |
+-----------+------------+
2 rows in set (0.01 sec)
1.7 注释的书写方式
- 语法结构
1行注释 ==> '--'
多行注释 ==> '/*'和'*/'
- 示例演示
mysql> -- 查询全部列
mysql> select * from Shohin;
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_ranka | torokubi |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001 | TT | yifu | 1000 | 500 | 2016-05-20 |
| 0002 | Ta | hhh | 10 | 10 | 2016-05-20 |
| 0003 | xa | hhh | NULL | NULL | 2016-05-21 |
| 0004 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0005 | 叉子 | 餐具 | 500 | 320 | 2009-09-11 |
| 0006 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0007 | T恤衫 | 衣服 | 2000 | 990 | 2009-09-20 |
+-----------+------------+---------------+--------------+--------------+------------+
7 rows in set (0.00 sec)
mysql> /* 这也是一个对于全列的查询,
/*> 但是这个是多行的书写 */
mysql> select * from Shohin;
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_ranka | torokubi |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001 | TT | yifu | 1000 | 500 | 2016-05-20 |
| 0002 | Ta | hhh | 10 | 10 | 2016-05-20 |
| 0003 | xa | hhh | NULL | NULL | 2016-05-21 |
| 0004 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0005 | 叉子 | 餐具 | 500 | 320 | 2009-09-11 |
| 0006 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0007 | T恤衫 | 衣服 | 2000 | 990 | 2009-09-20 |
+-----------+------------+---------------+--------------+--------------+------------+
7 rows in set (0.00 sec)
2. 算术运算符和比较运算符
2.1 算数运算符
语法结构
+
==> 加法-
==> 减法*
==> 乘法/
==> 除法- **
必知
**:所以包含null
的计算,结果肯定是null
示例演示
# 乘法运算
mysql> select shohin_id, shohin_mei, hanbai_tanka * 2 as 'double' from Shohin;
+-----------+------------+--------+
| shohin_id | shohin_mei | double |
+-----------+------------+--------+
| 0001 | TT | 2000 |
| 0002 | Ta | 20 |
| 0003 | xa | NULL |
| 0004 | 打孔器 | 1000 |
| 0005 | 叉子 | 1000 |
| 0006 | T恤衫 | 2000 |
| 0007 | T恤衫 | 4000 |
+-----------+------------+--------+
7 rows in set (0.00 sec)
# from子句真的需要吗?
mysql> select (100 + 200) * 3 as money;
+-------+
| money |
+-------+
| 900 |
+-------+
1 row in set (0.00 sec)
2.2 比较运算符
语法结构
=
==> 等于<>
==> 不等于>=
==> 大于等于<=
==> 小于等于>
==> 大于<
==> 小于
示例演示
# 等于
mysql> select shohin_mei, shohin_bunrui from Shohin where hanbai_tanka = 500;
+------------+---------------+
| shohin_mei | shohin_bunrui |
+------------+---------------+
| 打孔器 | 办公用品 |
| 叉子 | 餐具 |
+------------+---------------+
2 rows in set (0.00 sec)
# 不等于
mysql> select shohin_id, shohin_mei, shohin_bunrui from Shohin where hanbai_tanka <> 500;
+-----------+------------+---------------+
| shohin_id | shohin_mei | shohin_bunrui |
+-----------+------------+---------------+
| 0001 | TT | yifu |
| 0002 | Ta | hhh |
| 0006 | T恤衫 | 衣服 |
| 0007 | T恤衫 | 衣服 |
+-----------+------------+---------------+
4 rows in set (0.00 sec)
# 日期比较
mysql> select shohin_id, shohin_mei, torokubi from Shohin where torokubi < '2016-05-19';
+-----------+------------+------------+
| shohin_id | shohin_mei | torokubi |
+-----------+------------+------------+
| 0004 | 打孔器 | 2009-09-11 |
| 0005 | 叉子 | 2009-09-11 |
| 0006 | T恤衫 | 2009-09-20 |
| 0007 | T恤衫 | 2009-09-20 |
+-----------+------------+------------+
2.3 对字符串使用不等于时的注意事项
语法结构
- 字符串的比较是对,对应字符进行对位比较
- 对位比较是从左往右排序的
- 字符创类型数据原则上按照字典顺序排序,不能与数字的大小顺序混淆
示例演示
mysql> select * from Chars;
+-----+
| chr |
+-----+
| 1 |
| 10 |
| 11 |
| 2 |
| 21 |
| 22 |
| 3 |
+-----+
7 rows in set (0.00 sec)
mysql> select chr from Chars where chr > '2';
+-----+
| chr |
+-----+
| 21 |
| 22 |
| 3 |
+-----+
3 rows in set (0.00 sec)
2.4 不能对 null 使用比较运算符
语法结构
- 希望选取 null 记录时,需要在条件表达式中使用
is null
运算符 - 希望选取不是 null 记录时,需要在条件表达式中使用
is not null
运算符
- 希望选取 null 记录时,需要在条件表达式中使用
示例演示
# is null
mysql> select shohin_mei, shiire_ranka
-> from Shohin
-> where shiire_ranka is null;
+------------+--------------+
| shohin_mei | shiire_ranka |
+------------+--------------+
| xa | NULL |
+------------+--------------+
1 row in set (0.00 sec)
# is not null
mysql> select shohin_mei, shiire_ranka
-> from Shohin
-> where shiire_ranka is not null;
+------------+--------------+
| shohin_mei | shiire_ranka |
+------------+--------------+
| TT | 500 |
| Ta | 10 |
| 打孔器 | 320 |
| 叉子 | 320 |
| T恤衫 | 500 |
| T恤衫 | 990 |
+------------+--------------+
6 rows in set (0.00 sec)
3. 逻辑运算符
3.1 not 运算符
语法结构
- 除了使用
<>
运算符之外,还存在另外一种否定的表达形似,那就是not
not
不能单独使用,必须和其他查询条件组合起来使用
- 除了使用
示例演示
mysql> select shohin_mei, shohin_bunrui
-> from Shohin
-> where not hanbai_tanka >= 1000;
+------------+---------------+
| shohin_mei | shohin_bunrui |
+------------+---------------+
| Ta | hhh |
| 打孔器 | 办公用品 |
| 叉子 | 餐具 |
+------------+---------------+
3 rows in set (0.00 sec)
3.2 and 和 or 运算符
语法结构
and
运算符在两侧的查询条件都成立时整个查询条件才成立or
运算符在其两侧的查询条件有一个成立时真个查询条件成立and
运算优先于or
运算执行- 用于多条件查询使用
示例演示
# or
mysql> select shohin_mei, shiire_ranka
-> from Shohin
-> where shohin_bunrui = "yifu" or hanbai_tanka > 1000;
+------------+--------------+
| shohin_mei | shiire_ranka |
+------------+--------------+
| TT | 500 |
| T恤衫 | 990 |
+------------+--------------+
2 rows in set (0.00 sec)
# and
mysql> select shohin_mei, shiire_ranka
-> from Shohin
-> where shohin_bunrui = "yifu" and hanbai_tanka >= 1000;
+------------+--------------+
| shohin_mei | shiire_ranka |
+------------+--------------+
| TT | 500 |
+------------+--------------+
1 row in set (0.00 sec)
3.3 通过括号进行强化
语法结构
- 使用括号可以方便的将逻辑进行划分开
示例演示
# 当我们使用逻辑运算符的时候,很容易影响到实际的结果
# 以下就是一个例子,原因是`and`运算优先于`or`运算执行
mysql> select shohin_mei, shohin_bunrui
-> from Shohin
-> where shohin_bunrui = "衣服"
-> and hanbai_tanka >= 1000
-> or torokubi = '2009-09-20';
+------------+---------------+
| shohin_mei | shohin_bunrui |
+------------+---------------+
| T恤衫 | 衣服 |
| T恤衫 | 衣服 |
+------------+---------------+
2 rows in set (0.00 sec)
# 所以使用到括号之后就很容易区分了
mysql> select shohin_mei, shohin_bunrui
-> from Shohin
-> where shohin_bunrui = "衣服"
-> and (hanbai_tanka >= 1000
-> or torokubi = '2009-09-20');
+------------+---------------+
| shohin_mei | shohin_bunrui |
+------------+---------------+
| T恤衫 | 衣服 |
| T恤衫 | 衣服 |
+------------+---------------+
2 rows in set (0.00 sec)
3.4 含有 null 时的真值
- 语法结构
- SQL 之外的语言也基本上只使用真和假的二值逻辑,只有在 SQL 中存在三值逻辑
- 三值逻辑包括,真、假和不确定