一、select命令格式
查看表里的行：select 表头名列表 from 库名.表名；
查看表里与条件匹配的行：select 表头名列表 from 库名.表名 where 查询条件 ；
二、where的基础条件
| 分类 | 命令/符号/... | 说明 |
|---|---|---|
| 数值比较 | = | 相等 |
| != | 不相等 | |
| > | 大于 | |
| >= | 大于等于 | |
| < | 小于 | |
| <= | 小于等于 | |
| 字符比较 | = | 相等比较 |
| != | 不相等比较 | |
| 空与非空 | is null | 判断是否为空 (NULL,null)表示空 |
| is not null | 判断是否不为空 ("","null","NULL")表示非空 |
|
| 范围匹配 | in ("T1",T2) | 在…里 |
| not in ("T1",T2) | 不在…里 | |
| between 数字1 and 数字2 | 在…之间 | |
| 模糊匹配 | like “表达式” | 常用通配符：_表示 1个字符%表示零个或多个字符 |
| 正则匹配 | regexp “正则表达式” | 通过正则来匹配查询内容 常用正则： ^ $ [] * | .
|
| 逻辑匹配 | and 或者 && | 逻辑与：多个判断条件必须同时成立 |
| or 或者 || | 逻辑或：多个判断条件其中某个条件成立即可 | |
| not 或者 ! | 逻辑非：取反 |
- 注意：
逻辑与and的优先级要高于逻辑或 or，如果在筛选条件里既有and又有or先判断and再判断or，也可以使用（）提高优先级判断
三、select...where匹配示例：
- 字符比较-查找例子
mysql> select name from tarena.user where name="apache" ;
+--------+
| name |
+--------+
| apache |
+--------+
1 row in set (0.00 sec)
- 范围匹配-查找例子
mysql> select name , uid from tarena.user where uid in (1 , 3 , 5 , 7);
+------+------+
| name | uid |
+------+------+
| bin | 1 |
| adm | 3 |
| sync | 5 |
| halt | 7 |
+------+------+
- 模糊匹配-查找例子
# 找名字必须是3个字符的 （没有空格挨着敲）
mysql> select name from tarena.user where name like "___";
+------+
| name |
+------+
| bin |
| adm |
| ftp |
+------+
6 rows in set (0.00 sec)
- 正则匹配-查找例子
# 查询名字了有数字的
mysql> select name from tarena.user where name regexp "[0-9]";
+-------+
| name |
+-------+
| yaya9 |
| 6yaya |
| ya7ya |
| yay8a |
+-------+
4 rows in set (0.00 sec)
- 逻辑匹配-查找例子
- not between ... and ...
mysql> select id, name,uid from tarena.user where id not between 10 and 20 ;
+----+-----------------+------+
| id | name | uid |
+----+-----------------+------+
| 28 | postfix | 89 |
| 29 | chrony | 998 |
| 30 | rpc | 32 |
+----+-----------------+------+
11 rows in set (0.00 sec)
四、select 命令其他用法演示
| 命令/符号/... | 说明 |
|---|---|
| as 或 空格 | 定义别名使用 |
| concat() | 数据拼接 |
| distinct 字段名列表 | 去重显示 |
- 数据拼接：select concat("字段1","连接符","字段2")...
mysql> select concat(uid,"@",gid) from tarena.user where shell is not null;
+---------------------+
| concat(uid,"@",gid) |
+---------------------+
| 2@2 |
| 3@4 |
| 4@7 |
+---------------------+
3 rows in set (0.00 sec)
- 去重显示：select distinct 字段名列表...
mysql> select distinct shell from tarena.user;
+----------------+
| shell |
+----------------+
| /bin/bash |
| /sbin/nologin |
| /bin/false |
| NULL |
+----------------+
4 rows in set (0.00 sec)
- 输出数字
mysql> select 123 ;
+-----+
| 123 |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)
- 输出字符
mysql> select "abc" ;
+-----+
| abc |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)
- 输出时定义列名
mysql> select "abc" as 字母;
+--------+
| 字母 |
+--------+
| abc |
+--------+
1 row in set (0.00 sec)
- 查看mysql变量
# 查看所有变量
mysql> show variables;
# 查看指定变量的值
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.17 |
+-----------+
1 row in set (0.00 sec)
- 输出计算结果
mysql> select 3+5;
+-----+
| 3+5 |
+-----+
| 8 |
+-----+
1 row in set (0.00 sec)
- 输出命令的结果
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-07-01 18:22:26 |
+---------------------+
1 row in set (0.00 sec)
