SHOW 命令
SHOW databases; //查看所有数据库
SHOW tables FROM xx_databases; //查看库中所有表
SHOW columns FROM xx_databases.xx_table; //查看表中的所有列信息;**
SELECT 检索数据
select xx_name from xx_table; //检索某一列
select xx_name1, xx_name2 from xx_table; //检索多列
SELECT * //所有列
mysql> SELECT * FROM test.products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01 | 1001 | .5 ton anvil | 5.99 | .5 ton anvil, black, complete with handy hook |
| ANV02 | 1001 | 1 ton anvil | 9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03 | 1001 | 2 ton anvil | 14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR | 1003 | Detonator | 13.00 | Detonator (plunger powered), fuses not included |
| FB | 1003 | Bird seed | 10.00 | Large bag (suitable for road runners) |
| FC | 1003 | Carrots | 2.50 | Carrots (rabbit hunting season only) |
| FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long |
| JP1000 | 1005 | JetPack 1000 | 35.00 | JetPack 1000, intended for single use |
| JP2000 | 1005 | JetPack 2000 | 55.00 | JetPack 2000, multi-use |
| OL1 | 1002 | Oil can | 8.99 | Oil can, red |
| SAFE | 1003 | Safe | 50.00 | Safe with combination lock |
| SLING | 1003 | Sling | 4.49 | Sling, one size fits all |
| TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick |
| TNT2 | 1003 | TNT (5 sticks) | 10.00 | TNT, red, pack of 10 sticks |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.00 sec)
DISTINCT //检索不同行
mysql> SELECT DISTINCT vend_id
FROM test.products
LIMIT 5;
+---------+
| vend_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+---------+
4 rows in set (0.01 sec)
LIMIT // 限制行数
mysql> SELECT prod_name
FROM test.products
LIMIT 5;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Detonator |
| Bird seed |
+--------------+
5 rows in set (0.00 sec)
LIMIT 5, 3; //检索行数区间;
mysql> SELECT prod_name FROM test.products LIMIT 5,3;
+--------------+
| prod_name |
+--------------+
| Carrots |
| Fuses |
| JetPack 1000 |
+--------------+
3 rows in set (0.00 sec)
ORDER BY //排序(默认升序 关键字 ASC 可省略)
mysql> SELECT DISTINCT vend_id, prod_price
FROM test.products
ORDER BY prod_price;
+---------+------------+
| vend_id | prod_price |
+---------+------------+
| 1003 | 2.50 |
| 1002 | 3.42 |
| 1003 | 4.49 |
| 1001 | 5.99 |
| 1002 | 8.99 |
| 1001 | 9.99 |
| 1003 | 10.00 |
| 1003 | 13.00 |
| 1001 | 14.99 |
| 1005 | 35.00 |
| 1003 | 50.00 |
| 1005 | 55.00 |
+---------+------------+
12 rows in set (0.01 sec)
ORDER BY //多列排序
下例中先按 prod_price 列进行排序,如prod_price列中有相同值,再按prod_name列进行排序;
注:也可通过未进行检索列做为 ORDER BY 的参数进行排序;
mysql> SELECT prod_id, prod_price, prod_name
FROM test.products
ORDER BY prod_price, prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| FC | 2.50 | Carrots |
| TNT1 | 2.50 | TNT (1 stick) |
| FU1 | 3.42 | Fuses |
| SLING | 4.49 | Sling |
| ANV01 | 5.99 | .5 ton anvil |
| OL1 | 8.99 | Oil can |
| ANV02 | 9.99 | 1 ton anvil |
| FB | 10.00 | Bird seed |
| TNT2 | 10.00 | TNT (5 sticks) |
| DTNTR | 13.00 | Detonator |
| ANV03 | 14.99 | 2 ton anvil |
| JP1000 | 35.00 | JetPack 1000 |
| SAFE | 50.00 | Safe |
| JP2000 | 55.00 | JetPack 2000 |
+---------+------------+----------------+
14 rows in set (0.00 sec)
DESC //降序
注:多列降序排列,需要对每列指定 DESC关键字
mysql> SELECT prod_id, prod_price, prod_name
FROM test.products
ORDER BY prod_price DESC, prod_name DESC;
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| JP2000 | 55.00 | JetPack 2000 |
| SAFE | 50.00 | Safe |
| JP1000 | 35.00 | JetPack 1000 |
| ANV03 | 14.99 | 2 ton anvil |
| DTNTR | 13.00 | Detonator |
| TNT2 | 10.00 | TNT (5 sticks) |
| FB | 10.00 | Bird seed |
| ANV02 | 9.99 | 1 ton anvil |
| OL1 | 8.99 | Oil can |
| ANV01 | 5.99 | .5 ton anvil |
| SLING | 4.49 | Sling |
| FU1 | 3.42 | Fuses |
| TNT1 | 2.50 | TNT (1 stick) |
| FC | 2.50 | Carrots |
+---------+------------+----------------+
14 rows in set (0.00 sec)
结合使用: 筛选最高价格;
注:子句位置规范: ORDER BY 位置在 FORM 之后, LIMIT 需要在 ORDER BY之后 , 子句位置次序不对会报错;
mysql> SELECT prod_price
FROM test.products
ORDER BY prod_price DESC
LIMIT 1;
+------------+
| prod_price |
+------------+
| 55.00 |
+------------+
1 row in set (0.00 sec)
WHERE
注: <> 与 != 不包含, 两者使用效果相同只是写法不同 , 字符检索时使用 ’ ’ 括起来;
mysql> SELECT prod_name, prod_price
FROM test.products
WHERE prod_price = 2.50;
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| Carrots | 2.50 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
2 rows in set (0.00 sec)
mysql> SELECT prod_name, prod_price
FROM test.products
WHERE prod_price >= 10.00;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| 2 ton anvil | 14.99 |
| Detonator | 13.00 |
| Bird seed | 10.00 |
| JetPack 1000 | 35.00 |
| JetPack 2000 | 55.00 |
| Safe | 50.00 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
7 rows in set (0.00 sec)
mySQL不作大小写检查, 字符检索时使用 ’ ’ 括起来;
mysql> SELECT prod_name, prod_price
FROM test.products
WHERE prod_name = 'fuses';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses | 3.42 |
+-----------+------------+
1 row in set (0.00 sec)
<> 与 != 不包含, 两者使用效果相同只是写法不同 ;
mysql> SELECT vend_id, prod_name
FROM test.products
WHERE vend_id <> 1003;
+---------+--------------+
| vend_id | prod_name |
+---------+--------------+
| 1001 | .5 ton anvil |
| 1001 | 1 ton anvil |
| 1001 | 2 ton anvil |
| 1002 | Fuses |
| 1005 | JetPack 1000 |
| 1005 | JetPack 2000 |
| 1002 | Oil can |
+---------+--------------+
7 rows in set (0.03 sec)
BETWEEN //范围检查
mysql> SELECT prod_name, prod_price
FROM test.products
WHERE prod_price
BETWEEN 5 AND 10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Oil can | 8.99 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
5 rows in set (0.04 sec)
IS NULL //空字段
注:不包含 空字符串’ '和 值 为 0的 情况,
注: 当使用 <> 或 != 进行不匹配检索时, 不能筛选出为 NULL的字段, 需要使用 IS NULL 并行限定筛选条件;
mysql> SELECT cust_id
-> FROM test.customers
-> WHERE cust_email IS NULL;
+---------+
| cust_id |
+---------+
| 10002 |
| 10005 |
+---------+
2 rows in set (0.00 sec)
AND //与逻辑 运算符
mysql> SELECT prod_id, prod_price, vend_id, prod_name
-> FROM test.products
-> WHERE vend_id = 1003 AND prod_price <= 10;
+---------+------------+---------+----------------+
| prod_id | prod_price | vend_id | prod_name |
+---------+------------+---------+----------------+
| FB | 10.00 | 1003 | Bird seed |
| FC | 2.50 | 1003 | Carrots |
| SLING | 4.49 | 1003 | Sling |
| TNT1 | 2.50 | 1003 | TNT (1 stick) |
| TNT2 | 10.00 | 1003 | TNT (5 sticks) |
+---------+------------+---------+----------------+
5 rows in set (0.00 sec)
OR //或逻辑 运算符
mysql> SELECT prod_name, prod_price, vend_id
-> FROM test.products
-> WHERE vend_id = 1002 OR vend_id = 1003;
+----------------+------------+---------+
| prod_name | prod_price | vend_id |
+----------------+------------+---------+
| Detonator | 13.00 | 1003 |
| Bird seed | 10.00 | 1003 |
| Carrots | 2.50 | 1003 |
| Fuses | 3.42 | 1002 |
| Oil can | 8.99 | 1002 |
| Safe | 50.00 | 1003 |
| Sling | 4.49 | 1003 |
| TNT (1 stick) | 2.50 | 1003 |
| TNT (5 sticks) | 10.00 | 1003 |
+----------------+------------+---------+
9 rows in set (0.00 sec)
ADN 与 OR 结合 //注意() 的作用
mysql> SELECT prod_name, prod_price, vend_id
-> FROM test.products
-> WHERE prod_price >= 10 AND (vend_id = 1002 OR vend_id = 1003);
+----------------+------------+---------+
| prod_name | prod_price | vend_id |
+----------------+------------+---------+
| Detonator | 13.00 | 1003 |
| Bird seed | 10.00 | 1003 |
| Safe | 50.00 | 1003 |
| TNT (5 sticks) | 10.00 | 1003 |
+----------------+------------+---------+
4 rows in set (0.00 sec)
IN // 功能与 OR 相同
mysql> SELECT prod_name, prod_price, vend_id
-> FROM test.products
-> WHERE vend_id IN(1002, 1003)
-> ORDER BY prod_name;
+----------------+------------+---------+
| prod_name | prod_price | vend_id |
+----------------+------------+---------+
| Bird seed | 10.00 | 1003 |
| Carrots | 2.50 | 1003 |
| Detonator | 13.00 | 1003 |
| Fuses | 3.42 | 1002 |
| Oil can | 8.99 | 1002 |
| Safe | 50.00 | 1003 |
| Sling | 4.49 | 1003 |
| TNT (1 stick) | 2.50 | 1003 |
| TNT (5 sticks) | 10.00 | 1003 |
+----------------+------------+---------+
9 rows in set (0.02 sec)
NOT // 取反
例: NOT IN() //不包含;
例: NOT BETWEEN() // 不在范围内
mysql> SELECT prod_name, prod_price, vend_id
-> FROM test.products
-> WHERE vend_id NOT IN (1002, 1003);
+--------------+------------+---------+
| prod_name | prod_price | vend_id |
+--------------+------------+---------+
| .5 ton anvil | 5.99 | 1001 |
| 1 ton anvil | 9.99 | 1001 |
| 2 ton anvil | 14.99 | 1001 |
| JetPack 1000 | 35.00 | 1005 |
| JetPack 2000 | 55.00 | 1005 |
+--------------+------------+---------+
5 rows in set (0.00 sec)
LINK
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
- 使用通配符 检索时间长, 使用时,最好放在最后;
- 如果可以通过其它方法达到要求,就不要使用通配符检索;
% //百分号通配符
注: %表示对任意字符匹配
下例中: jet% 表示 jet后面有任意多少字符都可匹配(只要开头是 jet 的字段就可以匹配)
mysql> SELECT prod_id, prod_name
-> FROM test.products
-> WHERE prod_name LIKE'jet%';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)
%an%
注: 不论 an 字符前面与后面有任何字符都可匹配(只要字段中存在 an 就可以匹配)
mysql> SELECT prod_id, prod_name
-> FROM test.products
-> WHERE prod_name LIKE '%an%';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
| OL1 | Oil can |
+---------+--------------+
4 rows in set (0.00 sec)
s%e
注: s与e之间有任意字符均可匹配 , 除了 NULL之外(例如 set 不能被匹配, 因为se之间没有字符 );
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name LIKE's%e';
+-----------+
| prod_name |
+-----------+
| Safe |
+-----------+
1 row in set (0.00 sec)
_ // 下划线通配符
注: 只匹配一个字符
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name LIKE'_ ton anvil';
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)
REGEXP 正则表达式
. 表示任意一个字符;
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP '.000';
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
BINARY //区分大小写(默认不区分大小写)
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP BINARY 'JetPack .000';
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.09 sec)
| //或逻辑匹配, 功能相当于OR
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP '1000|2000'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
[ ]// 匹配几个字符之一;
例 : [123] ,匹配1或2或3,也可以写成 [1|2|3] ,效果相同
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP '[123] ton'
-> ORDER BY prod_name;
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.01 sec)
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP '[1|2|3] ton'
-> ORDER BY prod_name;
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)
[ 0 - 9 ] // 匹配范围, 相当于 [0123456789]
注 : 也可以是 [4 - 7] , [ a - z ], [ G - U ] … …
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP '[1-5] TON'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.00 sec)
\\ : 匹配特殊字符
例 : ’ \\ - ’ 查找 - 字符 ; ’ \\ .’ 查找 . 字符
mysql> SELECT vend_name
-> FROM test.vendors
-> WHERE vend_name REGEXP '\\.'
-> ORDER BY vend_name;
+--------------+
| vend_name |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.02 sec)
匹配多个实例
下例中:
- \\( 匹配 ( ;
- [0-9] 匹配 0-9之间任意数 ;
- s? 使 s 成为可选字符 (匹配 ?号之前的字符s, 0次或1 次 ,0次时, 匹配 stick , 1 次时匹配 sticks);
- \\) 匹配 );
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
-> ORDER BY prod_name;
+----------------+
| prod_name |
+----------------+
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.00 sec)
匹配任意四位连续数字
注 : [:digit:] // 匹配任意迷数字 同[0-9]
注: {4} // 要求前面的字符([:digit:] 任意数字)出现4次;
‘[[:digit:]]{4}’ 等同于 ‘[0-9][0-9][0-9][0-9]’
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP '[[:digit:]]{4}'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.01 sec)
^ $ //定位符
下例中 :
’^ [ 0-9\\.] ’ 只有字段的每一个字符为 0-9的 数字或 . 时才会匹配所以结束为三行;
’[0-9\\.]’ 没有使用定位符,限定字段的开始, 所以只要字段中存在 0-9的数字或 . 就可以被匹配
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP '^[0-9\\.]'
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.00 sec)
mysql> SELECT prod_name
-> FROM test.products
-> WHERE prod_name REGEXP '[0-9\\.]'
-> ORDER BY prod_name;
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| JetPack 1000 |
| JetPack 2000 |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
7 rows in set (0.02 sec)