select查询汇总(合集)
读文前提
已安装数据库
"数据导入" ]#mysql < tarena.sql
基础查询
查询表结构
desc tarena.user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL |
....
查询表内容
"查询tarena库中user表中所有列"
select * from tarena.user;
"查看tarena库中user表中的name列"
select name from tarena.user;
数值比较查询
use tarena;
查看第3行的行号、用户名、uid、gid 四个表头的值
mysql>select id,name,uid,gid from user where id=3;
查看前2行的行号用户名、uid、gid 四个表头的值
mysql> select id,name,uid,gid from user where id<3;
查看前3行的行号、用户名、uid、gid 四个表头的值
mysql> select id,name,uid,gid from user where id<=3;
范围比较查询
范围比较常用符号:
- in 在…里
- not in 不在…里
- between 1 and 2 在1和2之间
"uid号表头的值 是 (1 , 3 , 5 , 7) 中的任意一个即可"
mysql> select id,uid,name from user where uid in (1,3,5,7);
"shell 表头的的值 不是 "/bin/bash"或"/sbin/nologin" 即可"
mysql> select uid,name,shell from user where shell not in ('/bin/bash','/sbin/nologin');
"id表头的值 在 10 到 20 之间即可 包括 10 和 20 本身"
mysql> select id,uid,name from user where id between 10 and 20;
模糊查询
基本根式:select 表头 from 库.表 where 字段名 like “表达式”;
常用通配符:
- _ 代表一个字符
- %代表0个或多个字符
"找名字必须是3个字符的 (没有空格挨着敲)"
mysql> select name from user where name like "___";
"找名字必须是4个字符的(没有空格挨着敲)"
mysql> select name from user where name like "____";
"找名字以字母a开头的(没有空格挨着敲)"
mysql> select name from user where name like "a%";
"查找名字至少是4个字符的表达式"
mysql> select name from user where name like "__%__";
正则匹配查询
基本格式:select 表头名 from 库.表 where 表头名 regexp “正则表达式”;
常用字符:
^ 匹配行首
$ 匹配行尾
[] 匹配范围内任意一个
* 前边的表达式出现零次或多次
| 或者
. 任意一个字符
"添加测试数据->添加有数字的名字"
insert into tarena.user(name)values("yaya9");
insert into tarena.user(name)values("6yaya");
insert into tarena.user(name)values("ya7ya");
insert into tarena.user(name)values("yay8a");
"查看名字里有数字的"
mysql> select name from user where name regexp "[0-9]";
"查看名字以数字开头"
mysql> select name from user where name regexp "^[0-9]";
"查看名字以数字结尾"
mysql> select name from user where name regexp "[0-9]$";
'查看名字以r开头'
mysql> select name from user where name regexp "^r";
'查看名字以t结尾'
mysql> select name from user where name regexp "t$";
'查看名字以r开头或t结尾'
mysql> select name from user where name regexp "^r|t$";
'名字r开头t结尾'
mysql> select name from user where name regexp "^r.*t$";
逻辑匹配查询
逻辑与 and (&&) 多个判断条件必须同时成立
逻辑或 or (||) 多个判断条件其中某个条件成立即可
逻辑非 not (!) 取反
优先级 (),括号中数据先进行计算
查看解释器不是/bin/bash的
mysql>select uid,naem from user where shell != "/bin/bash";
使用not取反
mysql>select uid,name from user where not shell = "/bin/bash";
id值不在 10 到 20 之间
mysql>select uid,name from user where not id between 10 and 20;
查找name=‘root’,uid=‘0’的行
mysql>select uid,name from user where name="root" and uid="0";
查找name=‘root’或者name=‘bin’或者uid=‘0’的行
mysql>select uid,name from user where name="root" or name="bin" or uid="0";
"不加() 的查询"
mysql>select uid,name from user where name="root" or name="bin" and uid="1";
+------+------+
| name | uid |
+------+------+
| root | 0 |
| bin | 1 |
+------+------+
"加()比较"
mysql>select uid,name from user where (name="root" or name="bin") and uid="1";
+------+------+
| name | uid |
+------+------+
| bin | 1 |
+------+------+
表头别名
定义别名使用“as”或空格
默认查询出的数据表头为select查询时的表头
mysql>select name as 账号,uid 属组ID from user where id<=5;
+--------+----------+
| 账号 | 属组ID |
+--------+----------+
| root | 0 |
| bin | 1 |
| daemon | 2 |
| adm | 3 |
| lp | 4 |
+--------+----------+
5 rows in set (0.00 sec)
去重合并
针对单列进行去重合并数据:
拼接 concat()
去重 distinct
mysql> select concat(name, "-" , uid) from user where id=1;
+-------------------------+
| concat(name, "-" , uid) |
+-------------------------+
| root-0 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select distinct shell from user;
+----------------+
| shell |
+----------------+
| /bin/bash |
| /sbin/nologin |
| /bin/sync |
| /sbin/shutdown |
| /sbin/halt |
| /bin/false |
| NULL |
+----------------+
7 rows in set
字符比较
= 相等比较
!= 不相等比较
is null 为空
is not null 非空
[注]:null与空不同,请自行百度查询(mysql中null与字符空的区别)
"添加测试数据---> 添加新行 仅给行中的id 表头和name表头赋值"
mysql> insert into tarena.user(id,name) values(32,""); //零个字符
mysql> insert into tarena.user(id,name) values(33,"null");//普通字母
mysql> insert into tarena.user(id,name) values(34,NULL); //表示空
mysql> insert into tarena.user(id,name) values(35,null); //表示空
mysql> select id,name from user where name is null;
+----+------+
| id | name |
+----+------+
| 34 | NULL |
| 35 | NULL |
+----+------+
2 rows in set (0.00 sec)
mysql> select id,name from user where name is not null and id >31;
+----+------+
| id | name |
+----+------+
| 32 | |
| 33 | null |
+----+------+
2 rows in set (0.00 sec)
常用函数
字符函数
"
length(str) 返回字符串长度,以字节为单位
一个中文字符=3字节
"
mysql> select name,length(name) from employees where employee_
+--------+--------------+
| name | length(name) |
+--------+--------------+
| 郭岩 | 6 |
+--------+--------------+
1 row in set (0.00 sec)
"char_length(str)返回字符串的长度,以字符为单位"
mysql> select name,char_length(name) from employees where employee_id=2;
+--------+-------------------+
| name | char_length(name) |
+--------+-------------------+
| 郭岩 | 2 |
+--------+-------------------+
1 row in set (0.00 sec)
"
upeer(str)和ucase(str)都用于将字段中字母转为大写
lower(str)和lcase(str)与之相反
"
mysql> select name,upper(name) from user where shell="/bin/bash";
+------+-------------+
| name | upper(name) |
+------+-------------+
| root | ROOT |
| plj | PLJ |
+------+-------------+
2 rows in set (0.00 sec)
"
substr(s,start,end)从s的start的位置取出到end位置的字符串
默认从1开始
"
mysql> select name,substr(name,1,2) from user where shell="/bin/bash" limit 1;
+------+------------------+
| name | substr(name,1,2) |
+------+------------------+
| root | ro |
+------+------------------+
1 row in set (0.00 sec)
"instr(str,str1)返回str1参数在str中的位置"
mysql> select name,instr(name,"t") from user limit 1;
+------+-----------------+
| name | instr(name,"t") |
+------+-----------------+
| root | 4 |
+------+-----------------+
1 row in set (0.00 sec)
"trim(s)返回字符串s删除了两边空格之后的字符串"
mysql> select name,trim(name) from user limit 1;
+------+------------+
| name | trim(name) |
+------+------------+
| root | root |
+------+------------+
1 row in set (0.00 sec)
数字函数
"abs(s)返回s的绝对值"
mysql> select abs(-12);
+----------+
| abs(-12) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
"PI()返回圆周率,默认显示6位小数"
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
"mod(x,y)返回x被y除后的余数"
mysql> select mod(6,2);
+----------+
| mod(6,2) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
日期函数
日期函数还有许多,以下只是举例
"curtime()当前系统时间"
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 21:55:44 |
+-----------+
1 row in set (0.00 sec)
"curdate()当前系统日期"
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2024-01-27 |
+------------+
1 row in set (0.00 sec)
"now()获取日期及时间"
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-01-27 21:57:15 |
+---------------------+
1 row in set (0.00 sec)
"year(now())只获取年"
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2024 |
+-------------+
1 row in set (0.00 sec)
"获取employees表中入职年份的月与出生年份的月相同的员工信息"
mysql> select name,hire_date,birth_date from employees where month(birth_date)=month(hire_date) limit 1;
+-----------+------------+------------+
| name | hire_date | birth_date |
+-----------+------------+------------+
| 李玉英 | 2012-01-19 | 1974-01-25 |
+-----------+------------+------------+
1 row in set (0.01 sec)
聚集函数
"
avg(字段名)计算平均值
计算2号员工2018年的的平均工资
"
mysql> select avg(basic) as total from salary where employee_id=2 and year(date)=2018;
+------------+
| total |
+------------+
| 19760.9167 |
+------------+
1 row in set (0.01 sec)
"
sum(字段名)计算字段的总和
计算2018年2号员工的总工资
"
mysql> select sum(basic) as total from salary where employee_id=2 and year(date)=2018;
+--------+
| total |
+--------+
| 237131 |
+--------+
1 row in set (0.00 sec)
"
min(字段名)返回最大值
max(字段名)返回最小值
返回2号员工2018年最高、最低的工资数额
"
mysql> select max(basic),min(basic) from salary where employee_id=2 and year(date)=2018;
+------------+------------+
| max(basic) | min(basic) |
+------------+------------+
| 20662 | 19679 |
+------------+------------+
1 row in set (0.00 sec)
"
count(字段名)统计字段的个数
返回user表中解释器为/bin/bash的用户个数
"
mysql> select count(name) from user where shell="/bin/bash";
+-------------+
| count(name) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
判断函数
"
if(条件,v1,v2)如果条件为true返回v1,否则返回v2
查询user表中的uid大于1000输出手动创建,小于1000输出系统创建
"
+-----------------+-------+--------------------------------------------+
| name | uid | if(uid>1000,"手动创建","系统创建") |
+-----------------+-------+--------------------------------------------+
| root | 0 | 系统创建 |
| bin | 1 | 系统创建
"
ifnull(v1,v2)如果v1不为空,则返回v1,否则返回v2
判断user表中的家目录是否为空,为空返回NO,不为空则返回原有值
"
mysql> select name,ifnull(homedir,"NO") from user;
+-----------------+----------------------+
| name | ifnull(homedir,"NO") |
+-----------------+----------------------+
| root | /root |
| bob | NO |
| b | NO |
| c | NO |
分组查询
对已经查询出来的结果做处理
"格式" select 字段名 from 表名 where 筛选条件 分组|排序|过滤|分页;
"
group by 字段名 根据字段名进行分组
统计各个解释的人数
"
mysql> select shell,count(name) from user group by shell;
+----------------+-------------+
| shell | count(name) |
+----------------+-------------+
| /bin/bash | 2 |
| /sbin/nologin | 20 |
| /bin/sync | 1 |
| /sbin/shutdown | 1 |
| /sbin/halt | 1 |
| /bin/false | 1 |
| NULL | 8 |
+----------------+-------------+
7 rows in set (0.00 sec)
排序
"
order by 字段名 desc/asc 按照字段名进行排序,默认为升序(asc),降序为desc
找出uid在100-50且不为空,将uid按照降序进行排序
"
mysql> select name,uid from user where uid between 10 and 50 order by uid desc;
+----------+------+
| name | uid |
+----------+------+
| apache | 48 |
| rpc | 32 |
| rpcuser | 29 |
| mysql | 27 |
| ftp | 14 |
| games | 12 |
| operator | 11 |
+----------+------+
7 rows in set (0.00 sec)
过滤
having 针对已经查询出来的数据做筛选条件
"
查找部门总人数少于10人的部门名称及人数
"
mysql> select dept_id,count(name) as 部门人数 from employees group by dept_id having 部门人数 < 10;
+---------+--------------+
| dept_id | 部门人数 |
+---------+--------------+
| NULL | 2 |
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 6 | 9 |
| 8 | 3 |
+---------+--------------+
6 rows in set (0.00 sec)
分页
"
limit 数字 显示查询结果的前多少行
Limit 数字1,数字2 显示指定范围内的查询记录;数字1表示从起始行(从0开始),数字2表示总显示的行数
"
mysql> select * from user limit 1;
+----+------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-----------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
+----+------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)
# 查询前三行
mysql> select * from user limit 0,3;
+----+--------+----------+------+------+---------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+--------+----------+------+------+---------+---------+---------------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
+----+--------+----------+------+------+---------+---------+---------------+
3 rows in set (0.00 sec)
进阶查询
内连接查询
等值连接
使用相等判断做连接条件,表中必须存在相同的数据表头
[注]:一定添加上连接条件,避免出现笛卡尔积
"
inner join 需要拼接的表名 on 连接条件
查询员工对应的部门
"
mysql> select name,dept_name from employees inner join departments on employees.dept_id = departments.dept_id limit 0,3;
+-----------+-----------+
| name | dept_name |
+-----------+-----------+
| 梁伟 | 人事部 |
| 郭岩 | 人事部 |
| 李玉英 | 人事部 |
+-----------+-----------+
3 rows in set (0.00 sec)
# 查询2018年总工资大于300000的人姓名,并输出前三名
mysql> select name,sum(basic+bonus) as 总工资 from employees inner join
salary on employees.employee_id = salary.employee_id
where
year(date)="2018"
group by name
having 总工资 > 300000
order by 总工资
limit 0,3;
+-----------+-----------+
| name | 总工资 |
+-----------+-----------+
| 王淑珍 | 304131 |
| 张冬梅 | 304190 |
| 刘桂兰 | 305131 |
+-----------+-----------+
3 rows in set (0.01 sec)
非等值连接
不使用向登记判断做连接条件,适合表里没有存储相同数据的表头时使用
"
以2018年12月份的员工工资按照wage_grade的等级表进行划分,输出前三条信息
"
mysql> select basic,grade from salary
inner join wage_grade
on salary.basic between wage_grade.low and wage_grade.high
where year(date)=2018 and month(date)=12
limit 1,3;
+-------+-------+
| basic | grade |
+-------+-------+
| 20662 | E |
| 9724 | B |
| 17016 | D |
+-------+-------+
3 rows in set (0.01 sec)
外连接查询
"创建测试数据"
insert into departments(dept_name) values("小卖部"),("海关部"),("公关部");
左连接
- 左表表头记录全显示
- 右表只显示与条件匹配的记录,右表比左表少的记录使用NULL匹配
- left join 左边称为左表
"
left join 表名 on 连接条件
输出没有员工的部门名
"
mysql> select dept_name,name from departments left join employees on departments.dept_id = employees.dept_id where name is null;
+-----------+------+
| dept_name | name |
+-----------+------+
| 小卖部 | NULL |
| 行政部 | NULL |
| 公关部 | NULL |
右连接
- 右表表头记录全显示
- 左表只显示与条件匹配的记录,左表比右表少的记录使用null匹配
- right join 右边称为右表
"
right join 表名 连接条件
输出没有部门的员工名字
"
mysql> select name,dept_name from departments right join employees on departments.dept_id = employees.dept_id where dept_name is null;
+------+-----------+
| name | dept_name |
+------+-----------+
| bob | NULL |
| yyh | NULL |
+------+-----------+
2 rows in set (0.00 sec)
全外连接
- 联合查询,用来合并查询结果
- 可以合并同一张的表的查询记录(不同表的查询记录也可合并)
- 查询时,多个select语句查询表头个数必须一样
- union关键字默认去重,可以使用union all 包含重复项
"语法格式"
(select语句) union (select语句); 去重
(select语句) union all (select语句);不去重
"查询工资表中2018年最高与最低"
mysql> (select max(basic) from salary where year(date)=2018)
union (select min(basic) from salary where year(date)=2018);
+------------+
| max(basic) |
+------------+
| 25524 |
| 5787 |
+------------+
2 rows in set (0.00 sec)
嵌套查询
where之后
"格式"
select 字段名 from 库 where 筛选条件 (in、=、not in) (select 字段名 from 库 where 筛选条件)
"
查询出所有运维部的员工
"
mysql> select name,dept_id from employees where dept_id = (select dept_id from departments where dept_name = "运维部");
+-----------+---------+
| name | dept_id |
+-----------+---------+
| 廖娜 | 3 |
| 窦红梅 | 3 |
| 聂想 | 3 |
| 陈阳 | 3 |
| 戴璐 | 3 |
| 陈斌 | 3 |
+-----------+---------+
6 rows in set (0.00 sec)
having之后
"格式"
select * from 库 where 筛选条件 having 字段 (>,<,=,!=) (select * from 库 where 筛选条件)
"查询部门总人数比开发部总人数少的部门名称和人数"
mysql> select dept_name,count(name) from employees
inner join departments
on departments.dept_id=employees.dept_id
group by employees.dept_id
having count(name) < (select count(name) from employees
where dept_id = (select dept_id from departments where dept_name="开发部"));
+-----------+-------------+
| dept_name | count(name) |
+-----------+-------------+
| 人事部 | 8 |
| 财务部 | 5 |
| 运维部 | 6 |
| 测试部 | 12 |
| 市场部 | 9 |
| 销售部 | 35 |
| 法务部 | 3 |
+-----------+-------------+
7 rows in set (0.00 sec)
from之后
- 把查询结果当做新表使用
- [注]:新表一定需要as进行别名,不然查不出数据
"格式"
select 表头名 from (select 查询命令) where 筛选条件
"查询3号部门 、部门名称 及其部门内 员工的编号、名字 和 email"
mysql> select dept_name,employee_id,name,email from (select dept_name,e.* from departments as d inner join employees as e on d.dept_id = e.dept_id) as newtab where dept_id=3 limit 0,2;
+-----------+-------------+-----------+--------------------+
| dept_name | employee_id | name | email |
+-----------+-------------+-----------+--------------------+
| 运维部 | 14 | 廖娜 | liaona@tarena.com |
| 运维部 | 15 | 窦红梅 | douhongmei@tedu.cn |
+-----------+-------------+-----------+--------------------+
2 rows in set (0.00 sec)
select之后
将查询结果作为表头
"查询各个部门的总人数"
mysql> select d.*,(select count(name) from employees as e where e.dept_id=d.dept_id) from departments as d limit 0,3;
+---------+-----------+---------------------------------------------------------------------------------+
| dept_id | dept_name | (select count(name) from employees as e where e.dept_id=d.dept_id) |
+---------+-----------+---------------------------------------------------------------------------------+
| 1 | 人事部 | 8 |
| 2 | 财务部 | 5 |
| 3 | 运维部 | 6 |
+---------+-----------+---------------------------------------------------------------------------------+
3 rows in set (0.00 sec)