一、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> 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) |
