05-select查询汇总(合集)

最新推荐文章于 2024-07-03 16:21:42 发布

去喝可乐哇

最新推荐文章于 2024-07-03 16:21:42 发布

阅读量995

点赞数 23

分类专栏： MySQL学习文章标签： mysql

本文链接：https://blog.csdn.net/a1357000424/article/details/135884691

版权

MySQL学习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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)