mysql-条件查询

最新推荐文章于 2024-04-30 05:14:28 发布
奋进的白羊
最新推荐文章于 2024-04-30 05:14:28 发布
阅读量1.2k
点赞数
分类专栏： mysql 文章标签：数据库
本文链接：https://blog.csdn.net/wswxy52188/article/details/116352945
版权
mysql 专栏收录该内容
5 篇文章 0 订阅
订阅专栏
一.条件查询
1.判断表字段是否为空（不能用等号，要用is null或 is not null）
mysql> select username,pwd from b_user where username is null;
Empty set (0.00 sec)

mysql> select username,pwd from b_user where username is not null;
+----------+--------+
| username | pwd    |
+----------+--------+
| 张三     | qwe123 |
| rose     | 123    |
| 李四     | 1234   |
| 小芳     | qwer   |
| 123      | 123    |
| 12       | 12     |
| 1234     | 1234   |
+----------+--------+
		7 rows in set (0.00 sec)

2.and（并且） 和 or（或者）
	and优先级比or高
	会先把用户名是李四密码是123的查询出来，再把密码是1234查询出来.
	结果：因为没有用户名是李四密码是123的用户，所以最后只走了密码是1234的，将其查询了出来
	mysql> select username,pwd from b_user where username='李四' and pwd='123' or pwd='1234';
+----------+------+
| username | pwd  |
+----------+------+
| 李四     | 1234 |
| 1234     | 1234 |
+----------+------+
		2 rows in set (0.00 sec)
	提升优先级：()可以提升优先级
	如下：将用户名是李四，密码是对应其用户名为李四的密码1234的用户查了出来
mysql> select username,pwd from b_user where username='李四' and (pwd='123' or pwd='1234');
+----------+------+
| username | pwd  |
+----------+------+
| 李四     | 1234 |
+----------+------+
		1 row in set (0.00 sec
	单个字段间查询用or
	mysql> select username,pwd from b_user where username='张三' or username='rose';
+----------+--------+
| username | pwd    |
+----------+--------+
| 张三     | qwe123 |
| rose     | 123    |
+----------+--------+
		2 rows in set (0.00 sec)

mysql> select username,pwd from b_user where username='张三' and username='rose';
		Empty set (0.00 sec)
	多个字段间查询有and（当查询多个字段时，若其中字段与值不是一一对应时可能查出来的值和你想要的值不相同）
	mysql> select username,pwd from b_user where username='张三' and pwd='qwe123';
+----------+--------+
| username | pwd    |
+----------+--------+
| 张三     | qwe123 |
+----------+--------+
		1 row in set (0.00 sec)
mysql> select username,pwd from b_user where username='张三' or pwd='qwe123';
+----------+--------+
| username | pwd    |
+----------+--------+
| 张三     | qwe123 |
+----------+--------+
		1 row in set (0.00 sec)
mysql> select username,pwd from b_user where username='张三' or pwd='123';
+----------+--------+
| username | pwd    |
+----------+--------+
| 张三     | qwe123 |
| rose     | 123    |
| 123      | 123    |
+----------+--------+
		3 rows in set (0.00 sec)
3.in 等同or
mysql> select username  from b_user where username='张三' or username='rose' or username='李四';
+----------+
| username |
+----------+
| 张三     |
| rose     |
| 李四     |
+----------+
		3 rows in set (0.00 sec)

mysql> select username  from b_user where username in ('张三','rose','李四');
+----------+
| username |
+----------+
| 张三     |
| rose     |
| 李四     |
+----------+
		3 rows in set (0.00 sec)
	not in
	mysql> select username  from b_user where username not in ('张三','rose','李四');
+----------+
| username |
+----------+
| 小芳     |
| 123      |
| 12       |
| 1234     |
+----------+
		4 rows in set (0.00 sec)
4.like（模糊查询）
%表示多个字符。
_表示单个字符。
\转义字符：想要在查询时，查询出带有特殊意义的字符时可以使用，比如\%就代表一个字符%了。
mysql> select username from b_user
    -> ;
+----------+
| username |
+----------+
| 张三     |
| rose     |
| 李四     |
| 小芳     |
| 123      |
| 12       |
| 1234     |
+----------+
		7 rows in set (0.00 sec)

mysql> select username u from b_user where username like '%1';
Empty set (0.00 sec)

mysql> select username u from b_user where username like '1%';
+------+
| u    |
+------+
| 123  |
| 12   |
| 1234 |
+------+
		3 rows in set (0.00 sec)

mysql> select username u from b_user where username like '_2';
+------+
| u    |
+------+
| 12   |
+------+
		1 row in set (0.00 sec)

mysql> select username u from b_user where username like '2__';
Empty set (0.00 sec)

mysql> select username u from b_user where username like '_2__';
+------+
| u    |
+------+
| 1234 |
+------+
		1 row in set (0.00 sec)

mysql> select username u from b_user where username like '%2__';
+------+
| u    |
+------+
| 1234 |
+------+
		1 row in set (0.00 sec)

mysql> select username u from b_user where username like '_2%';
+------+
| u    |
+------+
| 123  |
| 12   |
| 1234 |
+------+
		3 rows in set (0.00 sec)
		
5.排序（order：排序，by：通过什么来排序）
#默认为升序排列
select id,author from b_chart order by id; 
#asc：升序关键字
select id,author from b_chart order by id asc;
#desc：降序关键字
select id,author from b_chart order by id desc;
#当排序条件大于一个时，可在字符间加一个逗号。但越靠前越主导排序。
select id,author from b_chart order by id asc, author desc;

mysql> select id,author from b_chart;
+----+------------------------------+
| id | author                       |
+----+------------------------------+
| 41 | 孟庆江主编 步印童            |
| 42 | （美）科恩 著                |
| 43 | [英]亚历克斯·弗里斯          |
| 44 | [英]亚历克斯·弗里斯          |
| 45 | [英]亚历克斯·弗里斯          |
| 46 | （英）珍妮特·温特            |
| 47 | [英]亚历克斯·弗里斯          |
| 48 | （法）安托万·德·圣           |
+----+------------------------------+
		8 rows in set (0.00 sec)

mysql> select id,author from b_chart order by id;
+----+------------------------------+
| id | author                       |
+----+------------------------------+
| 41 | 孟庆江主编 步印童            |
| 42 | （美）科恩 著                |
| 43 | [英]亚历克斯·弗里斯          |
| 44 | [英]亚历克斯·弗里斯          |
| 45 | [英]亚历克斯·弗里斯          |
| 46 | （英）珍妮特·温特            |
| 47 | [英]亚历克斯·弗里斯          |
| 48 | （法）安托万·德·圣           |
+----+------------------------------+
		8 rows in set (0.00 sec)

mysql> select id,author from b_chart order by id asc;
+----+------------------------------+
| id | author                       |
+----+------------------------------+
| 41 | 孟庆江主编 步印童            |
| 42 | （美）科恩 著                |
| 43 | [英]亚历克斯·弗里斯          |
| 44 | [英]亚历克斯·弗里斯          |
| 45 | [英]亚历克斯·弗里斯          |
| 46 | （英）珍妮特·温特            |
| 47 | [英]亚历克斯·弗里斯          |
| 48 | （法）安托万·德·圣           |
+----+------------------------------+
		8 rows in set (0.00 sec)

mysql> select id,author from b_chart order by id desc;
+----+------------------------------+
| id | author                       |
+----+------------------------------+
| 48 | （法）安托万·德·圣           |
| 47 | [英]亚历克斯·弗里斯          |
| 46 | （英）珍妮特·温特            |
| 45 | [英]亚历克斯·弗里斯          |
| 44 | [英]亚历克斯·弗里斯          |
| 43 | [英]亚历克斯·弗里斯          |
| 42 | （美）科恩 著                |
| 41 | 孟庆江主编 步印童            |
+----+------------------------------+
		8 rows in set (0.00 sec)
mysql> select id,author from b_chart order by id asc, author desc;
+----+------------------------------+
| id | author                       |
+----+------------------------------+
| 41 | 孟庆江主编 步印童            |
| 42 | （美）科恩 著                |
| 43 | [英]亚历克斯·弗里斯          |
| 44 | [英]亚历克斯·弗里斯          |
| 45 | [英]亚历克斯·弗里斯          |
| 46 | （英）珍妮特·温特            |
| 47 | [英]亚历克斯·弗里斯          |
| 48 | （法）安托万·德·圣           |
+----+------------------------------+
		8 rows in set (0.00 sec)
6.函数
mysql> select *from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+
		14 rows in set (0.01 sec)
#单行处理函数：输入一行，输出一行。
ifnull()：空处理函数
ifnull(可能为null的数据，想被当成是什么数据处理)
eg：ifnull(comm,0)
#求年薪
#当数值相加时有null参与运算时，结果为null
mysql> select ename,(sal+comm)*12 from emp;
+--------+---------------+
| ename  | (sal+comm)*12 |
+--------+---------------+
| SMITH  |          NULL |
| ALLEN  |         22800 |
| WARD   |         21000 |
| JONES  |          NULL |
| MARTIN |         31800 |
| BLAKE  |          NULL |
| CLARK  |          NULL |
| SCOTT  |          NULL |
| KING   |          NULL |
| TURNER |         18000 |
| ADAMS  |          NULL |
| JAMES  |          NULL |
| FORD   |          NULL |
| MILLER |          NULL |
+--------+---------------+
		14 rows in set (0.00 sec)
#ifnull这个函数可以将null转换成另一个你想要的数据类型
mysql> select ename,(sal+ifnull(comm,0))*12 from emp;
+--------+-------------------------+
| ename  | (sal+ifnull(comm,0))*12 |
+--------+-------------------------+
| SMITH  |                    9600 |
| ALLEN  |                   22800 |
| WARD   |                   21000 |
| JONES  |                   35700 |
| MARTIN |                   31800 |
| BLAKE  |                   34200 |
| CLARK  |                   29400 |
| SCOTT  |                   36000 |
| KING   |                   60000 |
| TURNER |                   18000 |
| ADAMS  |                   13200 |
| JAMES  |                   11400 |
| FORD   |                   36000 |
| MILLER |                   15600 |
+--------+-------------------------+
		14 rows in set (0.00 sec)
#多行处理函数：输入多行，输出一行。
分组函数：属于多行处理函数。
分组函数自动忽略null
count():计数。
#count(*)和count(字段)的区别
#count(*)与字段无关，直接查出这一张表中有多少条数据。
select count(*) from emp;
#count(字段)能查出数据中这个字段非空的个数
select count(comm) from emp;
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
		1 row in set (0.00 sec)

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
		1 row in set (0.00 sec)
sum():求和。
#将sal这个字段的数相加
avg():平均值。
#将sal这个字段的数求平均值
max():最大值。
#将sal这个字段的数的最大值
min():最小值。
#将sal这个字段的数的最小值
mysql> select sum(sal),avg(sal),max(sal),min(sal) from emp;
+----------+-------------------+----------+----------+
| sum(sal) | avg(sal)          | max(sal) | min(sal) |
+----------+-------------------+----------+----------+
|    29025 | 2073.214285714286 |     5000 |      800 |
+----------+-------------------+----------+----------+
		1 row in set (0.00 sec)

7.group by和having
group by：按照某个字段或者某些字段进行分组
having：对分组的数据再次进行过滤
#group by操作
mysql> select *from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+
	14 rows in set (0.00 sec)
#通过job进行分组，将最大sal和job打印出来
mysql> select max(sal),job from emp group by job;
+----------+-----------+
| max(sal) | job       |
+----------+-----------+
|     3000 | ANALYST   |
|     1300 | CLERK     |
|     2975 | MANAGER   |
|     5000 | PRESIDENT |
|     1600 | SALESMAN  |
+----------+-----------+
	5 rows in set (0.00 sec)
#多个字段进行分组写法
mysql> select max(sal),job,deptno from emp group by job,deptno;
+----------+-----------+--------+
| max(sal) | job       | deptno |
+----------+-----------+--------+
|     3000 | ANALYST   |     20 |
|     1300 | CLERK     |     10 |
|     1100 | CLERK     |     20 |
|      950 | CLERK     |     30 |
|     2450 | MANAGER   |     10 |
|     2975 | MANAGER   |     20 |
|     2850 | MANAGER   |     30 |
|     5000 | PRESIDENT |     10 |
|     1600 | SALESMAN  |     30 |
+----------+-----------+--------+
	9 rows in set (0.00 sec)
#分组后加一个排序
mysql> select max(sal),job,deptno from emp group by job,deptno order by max(sal);
+----------+-----------+--------+
| max(sal) | job       | deptno |
+----------+-----------+--------+
|      950 | CLERK     |     30 |
|     1100 | CLERK     |     20 |
|     1300 | CLERK     |     10 |
|     1600 | SALESMAN  |     30 |
|     2450 | MANAGER   |     10 |
|     2850 | MANAGER   |     30 |
|     2975 | MANAGER   |     20 |
|     3000 | ANALYST   |     20 |
|     5000 | PRESIDENT |     10 |
+----------+-----------+--------+
	9 rows in set (0.00 sec)
#group by和having连用（能用where过滤的先用where过滤，不要用having，原因：效率低）
mysql> select max(sal),deptno from emp group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|     5000 |     10 |
|     3000 |     20 |
|     2850 |     30 |
+----------+--------+
	3 rows in set (0.00 sec)

mysql> select max(sal),deptno from emp group by deptno having max(sal)>=3000;#效率低（原因：第一次查询时查询的数据中有部分被having过滤掉了，相当于查多了）
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|     5000 |     10 |
|     3000 |     20 |
+----------+--------+
	2 rows in set (0.00 sec)

mysql> select max(sal),deptno from emp where sal>=3000 group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|     5000 |     10 |
|     3000 |     20 |
+----------+--------+
	2 rows in set (0.00 sec)
#deptno分组，将avg(sal) deptno查询出来
mysql> select avg(sal) deptno from emp group by deptno;
+--------------------+
| deptno             |
+--------------------+
| 2916.6666666666665 |
|               2175 |
| 1566.6666666666667 |
+--------------------+
	3 rows in set, 1 warning (0.00 sec)

mysql> select avg(sal) deptno from emp group by deptno having avg(sal)>2000 ;#（不能用where过滤了，原因：where字句后不能有分组函数，原因：看mysql数据优先级）
+--------------------+
| deptno             |
+--------------------+
| 2916.6666666666665 |
|               2175 |
+--------------------+
	2 rows in set, 1 warning (0.00 sec)
8 distinct（去重：去出重复记录）
注意：1.distinct要写在所有字段最前方。（原因：要查询多个字段时，写在字段前会造成语法错误）
	 2.distinct后面有多个字段代表的是后面字段联合查询后在去重
mysql> select job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| SALESMAN  |     30 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| SALESMAN  |     30 |
| CLERK     |     20 |
| CLERK     |     30 |
| ANALYST   |     20 |
| CLERK     |     10 |
+-----------+--------+
	14 rows in set (0.00 sec)

mysql> select distinct job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+
	9 rows in set (0.00 sec)
	
	mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
	3 rows in set (0.00 sec)

#错误原因：job共有14条，distinct deptno只有3条，无法显示
mysql> select job,distinct deptno from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct deptno from emp' at line 1

#distinct和分组函数一起用
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
	1 row in set (0.00 sec)
奋进的白羊
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql-条件查询

一.条件查询1.判断表字段是否为空（不能用等号，要用is null或 is not null）mysql> select username,pwd from b_user where username is null;Empty set (0.00 sec)mysql> select username,pwd from b_user where username is not null;+----------+--------+| username | pwd |+----
复制链接

扫一扫