一 查询表记录
mysql> select * from db3.user;
mysql> select name,shell from db3.user;
mysql> select name,shell from db3.user where id <=2;
*表示所有字段
查看当前库表记录时库名可以省略
字段列表决定显示列个数
条件决定显示行个数
二 基本匹配条件
适用于select, update, delete
1. 数值的比较，字段必须是数值类型
= 相等
> 大于
>= 大于或等于
< 小于
<= 小于等于
!= 不相等 常用于取反
mysql> select name from db3.user where uid<=10;
2. 字符的比较,当字段的值是字符时需要用""引起来
= 相等
!= 不相等
is null 空
is not null 非空
#条件内容是字符时,需要加引号
mysql> select name from db3.user where shell = "/bin/bash";
mysql> select name from db3.user where shell != "/bin/bash";
#条件内容是变量时,不用再加引号
mysql> select name,uid,gid from db3.user where uid=gid;
mysql> select name,comment from db3.user where name=comment;
mysql> select name,comment from db3.user where name!=comment;
mysql> select id name from db3.user where name is not null;
mysql> select id name from db3.user where name is null;
3.逻辑匹配
多个判断条件时使用逻辑匹配
or或|| 逻辑或 （多个条件只要1个条件成立即可）
and或&& 逻辑与 （多个条件必须同时成立）
!或not 逻辑非 （取反）
() 提高优先级
mysql> select name,uid from db3.user where name="root" and uid=3;
mysql> select name,uid from db3.user where name="root" or uid=3;
4.范围内匹配/去重显示
in (值列表) 在...里
not in (值列表) 不在...里
between 数字 and 数字 在...之间
distinct 字段名 去重显示
mysql> select name from db3.user where name in ("adm","sync","mysql","bin");
mysql> select name from db3.user where uid in (12,70,23);
mysql> select name,shell from db3.user where shell not in ("/bin/bash","/sbin/nologin");
mysql> select * from db3.user where uid between 10 and 20;
#去重显示,相同的内容只显示一次
mysql> select distinct shell from db3.user;
+----------------+
| shell |
+----------------+
| /bin/bash |
| /sbin/nologin |
| /bin/sync |
| /sbin/shutdown |
| /sbin/halt |
| /bin/false |
+----------------+
三 高级匹配条件
1.模糊查询 like
用法：
where 字段名 like '表达式';
_ 表示1个字符
% 表示0个或多个字符
#查询名字是三个字符的记录三个下划线
mysql> select name from db3.user where name like '___';
+------+
| name |
+------+
| bin |
| adm |
| ftp |
| tss |
| bob |
+------+
#查询名字里包含有a的记录
mysql> select name from db3.user where name like '%a%';
+----------+
| name |
+----------+
| daemon |
| adm |
| halt |
| mail |
| operator |
| games |
+----------+
#查询名字中包括4个及以上字符的记录
mysql> select name from db3.user where name like '__%__';
2. 正则表达式
用法:
where 字段名 regexp '正则表达式'
正则符号: ^ $ . [ ] * |
. 表示1个任意字符
* 表示前面重复0次,或者任意次
^ 开始
$ 结尾
[] 范围
| 或
#查询以a开头的
mysql> select name from db3.user where name regexp '^a';
#查询以t结尾的
mysql> select name from db3.user where name regexp 't$';
#查询以a开头,中间任意,结尾以t的
mysql> select name from db3.user where name regexp '^a.*t$';
#查询名字以a开头,或者以t结尾的
mysql> select name from db3.user where name regexp '^a|t$';
+------+
| name |
+------+
| root |
| adm |
| halt |
+------+
#查询名字中含有字母的
mysql> select name from db3.user where name regexp '[0-9]';
+-------+
| name |
+-------+
| yaya9 |
| y8aya |
| ya6ya |
+-------+
3.四则运算
字段必须是数值类型
+ 加
- 减
* 乘
/ 除
% 求模
() 提高优先级
mysql> alter table db3.user add system int default 80,add mysql int default 80;
mysql> alter table db3.user add age tinyint default 19 after name;
mysql> select * from db3.user where name="root";
mysql> select name,age from db3.user where name="root";
+------+------+
| name | age |
+------+------+
| root | 19 |
+------+------+
1 row in set (0.00 sec)
mysql> select name,age,2019 - age from db3.user where name="root";
+------+------+------------+
| name | age | 2019 - age |
+------+------+------------+
| root | 19 | 2000 |
+------+------+------------+
1 row in set (0.00 sec)
mysql>
#计算uid是偶数的记录
mysql> select name,uid from db3.user where uid%2 = 0 ;
#计算uid是奇数的记录
mysql> select name,uid from db3.user where uid%2 != 0 ;
#UID小于等于10的,让uid自加1
mysql> update db3.user set uid=uid+1 where uid <=10;
#查询name是root的 system,mysql成绩
mysql> select name,system,mysql from db3.user where name="root";
+------+--------+-------+
| name | system | mysql |
+------+--------+-------+
| root | 80 | 80 |
+------+--------+-------+
1 row in set (0.00 sec)
#对root的 system,mysql成绩,求总分数,和平均分
mysql> select name,system,mysql,system+mysql sum, (system+mysql)/2 ave from
db3.user where name="root";
+------+--------+-------+------+---------+
| name | system | mysql | sum | ave |
+------+--------+-------+------+---------+
| root | 80 | 80 | 160 | 80.0000 |
+------+--------+-------+------+---------+
1 row in set (0.00 sec)
mysql>
四 操作查询结果
把数据查询出来后,再做操作。就是在查询结果中再查询一遍
1 聚焦函数:mysql内置对数据进行统计的指令
avg(字段名)
sum(字段名)
min(字段名)
max(字段名)
count(字段名)
#统计shell不是/bin/bash的用户的 system的平均成绩
mysql> select avg(system) from db3.user where shell != "/bin/bash";
+-------------+
| avg(system) |
+-------------+
| 80.0000 |
+-------------+
1 row in set (0.00 sec)
#统计uid的平台数
mysql> select avg(uid) from db3.user;
+----------+
| avg(uid) |
+----------+
| 212.8636 |
+----------+
1 row in set (0.00 sec)
mysql> select max(uid) from db3.user;
mysql> select sum(uid) from db3.user;
#统计db3.user表中的行数
mysql> select count(*) from db3.user;
#查询shell不是/bin/bash,/sbin/nologin的用户的name
mysql> select name from db3.user where shell
not in ("/bin/bash","/sbin/nologin");
+----------+
| name |
+----------+
| sync |
| shutdown |
| halt |
| mysql |
+----------+
#约计以上查询结果的行数
mysql> select count(name) from db3.user where
shell not in ("/bin/bash","/sbin/nologin");
2 查询结果排序
用法:
-SQL查询 order by 字段名 [ asc|desc ];
-asc 升序 默认升序
-desc 降序
#不做排序查询时,查询出来的结果,就是当时数据存入时的顺序.
mysql> select name,uid from db3.user where uid>=10 and uid <=1000
#默认是升序排列
mysql> select name,uid from db3.user where uid>=10 and uid <=1000 order by uid;
+-----------------+------+
| name | uid |
+-----------------+------+
| operator | 11 |
| games | 12 |
| ftp | 14 |
| mysql | 27 |
| tss | 59 |
| tcpdump | 72 |
| sshd | 74 |
| dbus | 81 |
| nobody | 99 |
| systemd-network | 192 |
| chrony | 998 |
| polkitd | 999 |
+-----------------+------+
12 rows in set (0.00 sec)
#降序排序
mysql> select name,uid from db3.user where uid>=10 and uid <=1000 order by uid desc;
3 查询结果分组
用法:
-SQL查询 group by 字段名;
#对uid <=500的查询结果 分组显示，使用相同shell的被分
#为一组
mysql> select shell from db3.user where uid <=500 group by shell;
+----------------+
| shell |
+----------------+
| /bin/bash |
| /bin/false |
| /bin/sync |
| /sbin/halt |
| /sbin/nologin |
| /sbin/shutdown |
+----------------+
6 rows in set (0.00 sec)
注意:
select distinct shell from db3.user where uid <=500;
这样查询结果与上面用group by的效果是一样的.不一样的是distinct查询效率较低,它是要比对表中所有
行的记录之后做出结果。group by 是先筛选出结果后,从结果中再分组,这个比对次数少,效率较高.
4.限制查询结果显示行数
用法:
-SQL查询 limit 数字; 显示查询结果前多少条记录
-SQL查询 limit 数字1,数字2; 显示指定范围内的查询记录
-数字1 起始行 (0表示第1行)
-数字2 总行数
#在查询结果中只显示前3行.
mysql> select id,name,homedir,shell from db3.user where uid<= 15 limit 3;
#从查询结果中筛选,从第4行开始显示,显示3行
mysql> select id,name,homedir,shell from db3.user where uid<= 15 limit 3,3;
+----+------+----------------+---------------+
| id | name | homedir | shell |
+----+------+----------------+---------------+
| 4 | adm | /var/adm | /sbin/nologin |
| 5 | lp | /var/spool/lpd | /sbin/nologin |
| 6 | sync | /sbin | /bin/sync |
+----+------+----------------+---------------+
3 rows in set (0.00 sec)
5 查询结果过滤
having用法：
-SQL查询 having 条件表达式;就是在查询结果中再查询一次
#在查询结果中过滤name是mysql的用户
mysql> select name from db3.user where shell !="/bin/bash" having name="mysql";
