简答查询
1.查询一个字段:
注意:
- select和from都是关键字。
- 字段名和表名都是标识符
强调:
- 对于SQL语句来说,是通用的
- 所有的SQL语句以“:”结束
- 另外SQL语句不区分大小写,都行
查询部门,名字:
mysql> select dname from dept;
+------------+
| dname |
+------------+
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)
2.查询两个字段或者多个字段怎么办?
使用逗号隔开“,”
查询部门编号和部门名?
mysql> select deptno,dname from dept;
+--------+------------+
| deptno | dname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
3.查询所有字段怎么办?
第一种方法:可以把每个字段都写上
select a,b,c,d…from tablename;
第二种方法:可以使用*
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
这种方式的缺点:
- 效率低
- 可读性差
在实际开发中不建议,可以自己玩没问题
你可以在DOS命令窗口中想快速的看一看全表数据可以采用这种方式
4.给查询的列起别名
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select deptno,dname as deptname from dept;
+--------+------------+
| deptno | deptname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
使用as关键字起别名
- 注意:只是将显示的查询结果列名显示为deptname,原来列名还是dname
- 记住:select语句是永远都不会进行修改操作的。(因为只负责查询)
as关键字可以省略吗? 可以的
mysql> select deptno,dname deptname from dept;
+--------+------------+
| deptno | deptname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
假设起别名的时候,别名里面有空格,怎么办?
mysql> select deptno,dname dept name from dept;
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 'name from dept' at line 1
DBMS看到这样的语句,进行SQL语句的编译,不符合语法,编译报错
怎么解决
mysql> select deptno,dname 'dept name' from dept;
+--------+------------+
| deptno | dept name |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.01 sec)
注意:在所有的数据库当中,字符串统一使用单引号括起来,单引号是标准,**双引号在Oracle数据库中用不了,但是在MySQL中可以使用。
5.计算员工 的年薪?sal*12
mysql> select ename,sal*12 from emp;
//结论:字段可以使用数学表达式
+--------+----------+
| ename | sal*12 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
12 rows in set (0.00 sec)
mysql> select ename,sal*12 as yearal from emp;
+--------+----------+
| ename | yearal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
12 rows in set (0.00 sec)
mysql> select ename,sal*12 as '年薪' from emp;
+--------+----------+
| ename | 年薪 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
12 rows in set (0.00 sec)
14.条件查询
14.1 什么是条件查询?
不是将表中所有数据都查出来,是查询出来符合条件的
语法格式:
select
字段1,字段2,字段3…
from
表名
where
条件;
14.2都有哪些条件?
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E52WddrG-1637543546159)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20211115211454923.png)]
查询薪资等于800的员工姓名和编号?
mysql> select empno,ename from emp where sal = 800;
+-------+-------+
| empno | ename |
+-------+-------+
| 7369 | SMITH |
+-------+-------+
1 row in set (0.00 sec)
查询薪资不等于800的员工姓名和编号?
mysql> select empno,ename from emp where sal != 800;
+-------+--------+
| empno | ename |
+-------+--------+
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7839 | KING |
| 7844 | TURNER |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+-------+--------+
11 rows in set (0.00 sec)
mysql> select empno,ename from emp where sal <> 800;
+-------+--------+
| empno | ename |
+-------+--------+
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7839 | KING |
| 7844 | TURNER |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+-------+--------+
11 rows in set (0.00 sec)
查询薪资小于2000的员工姓名和编号?
mysql> select empno,ename,sal from emp where sal < 2000;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7844 | TURNER | 1500.00 |
| 7900 | JAMES | 950.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
7 rows in set (0.00 sec)
查询薪资小于等于3000的员工姓名和编号?
mysql> select empno,ename,sal from emp where sal <= 3000;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7844 | TURNER | 1500.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
11 rows in set (0.00 sec)
查询SMITH的编号和薪资?
mysql> select empno,sal from emp where ename = 'SMITH';
+-------+--------+
| empno | sal |
+-------+--------+
| 7369 | 800.00 |
+-------+--------+
1 row in set (0.00 sec)
查询薪金在2450到3000之间的员工信息?包括2450和3000
mysql> select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
4 rows in set (0.00 sec)
mysql> select empno,ename,sal from emp where sal between 2450 and 3000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
4 rows in set (0.00 sec)
使用between and的时候必须注意左小右大
查询哪些员工的津贴/补助为null?
mysql> select empno,ename,sal,comm from emp where comm = null;
Empty set (0.00 sec)
mysql> select empno,ename,sal,comm from emp where comm is null;
+-------+--------+---------+------+
| empno | ename | sal | comm |
+-------+--------+---------+------+
| 7369 | SMITH | 800.00 | NULL |
| 7566 | JONES | 2975.00 | NULL |
| 7698 | BLAKE | 2850.00 | NULL |
| 7782 | CLARK | 2450.00 | NULL |
| 7839 | KING | 5000.00 | NULL |
| 7900 | JAMES | 950.00 | NULL |
| 7902 | FORD | 3000.00 | NULL |
| 7934 | MILLER | 1300.00 | NULL |
+-------+--------+---------+------+
8 rows in set (0.00 sec)
注意:在数据库当中null不能使用等号进行衡量。需要使用is null
因为数据库当中null代表什么都没有,它不是一个值,所以不能使用等号衡量。
查询哪些员工的津贴/补助不为null?
mysql> select empno,ename,sal,comm from emp where comm is not null;
+-------+--------+---------+---------+
| empno | ename | sal | comm |
+-------+--------+---------+---------+
| 7499 | ALLEN | 1600.00 | 300.00 |
| 7521 | WARD | 1250.00 | 500.00 |
| 7654 | MARTIN | 1250.00 | 1400.00 |
| 7844 | TURNER | 1500.00 | 0.00 |
+-------+--------+---------+---------+
4 rows in set (0.00 sec)
找出工作岗位是MANAGER且工资大于2500的员工信息?
mysql> select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500;
+-------+-------+---------+---------+
| empno | ename | job | sal |
+-------+-------+---------+---------+
| 7566 | JONES | MANAGER | 2975.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
2 rows in set (0.00 sec)
查询工作工作岗位是MANAGER和SALESMAN的员工?
mysql> select empno, ename,job from emp where job = 'MANAGER';
mysql> select empno, ename,job from emp where job = 'SALESMAN';
mysql> select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
+-------+--------+----------+
| empno | ename | job |
+-------+--------+----------+
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7566 | JONES | MANAGER |
| 7654 | MARTIN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7844 | TURNER | SALESMAN |
+-------+--------+----------+
7 rows in set (0.00 sec)
and 和 or同时出现的话,有优先级问题吗?
查询工资大于2500,并且部门编号为10或20部门的员工?
mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-02 00:00:00 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
12 rows in set (0.01 sec)
mysql> select * from emp where sal > 2500 and deptno = 10 or deptno =20;
分析以上语句问题:
- and优先级比or高
- 以上语句会先执行and,然后执行or
- 以上这个语句表示什么含义?
- 找出工资大于2500并且部门编号为10的员工,或者20部门所有员工找出来。
mysql> select * from emp where sal > 2500 and deptno = 10 or deptno =20;
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
4 rows in set (0.00 sec)
ysql> select * from emp where sal > 2500 and (deptno = 10 or deptno =20);
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
3 rows in set (0.00 sec)
and和or同时出现,and优先级较高。如果想让or先执行,需要加小括号
以后在开发中,如果不确定优先级,就加小括号就行了。
查询工作岗位是MANAGER和SALESMAN的员工
mysql> select empno,job from emp where job = 'MANAGER' or job = 'SALESMAN';
+-------+----------+
| empno | job |
+-------+----------+
| 7499 | SALESMAN |
| 7521 | SALESMAN |
| 7566 | MANAGER |
| 7654 | SALESMAN |
| 7698 | MANAGER |
| 7782 | MANAGER |
| 7844 | SALESMAN |
+-------+----------+
7 rows in set (0.00 sec)
mysql> select empno,job from emp where job in('MANAGER','SALESMAN');
+-------+----------+
| empno | job |
+-------+----------+
| 7499 | SALESMAN |
| 7521 | SALESMAN |
| 7566 | MANAGER |
| 7654 | SALESMAN |
| 7698 | MANAGER |
| 7782 | MANAGER |
| 7844 | SALESMAN |
+-------+----------+
7 rows in set (0.00 sec)
注意:in不是一个区间,in后面跟的是具体的值
查询薪资是在800和5000的员工信息?
mysql> select ename,sal from emp where sal = 800 or sal = 5000;
+-------+---------+
| ename | sal |
+-------+---------+
| SMITH | 800.00 |
| KING | 5000.00 |
+-------+---------+
2 rows in set (0.00 sec)
mysql> select ename,sal from emp where sal in(800,5000);
+-------+---------+
| ename | sal |
+-------+---------+
| SMITH | 800.00 |
| KING | 5000.00 |
+-------+---------+
2 rows in set (0.00 sec)
not in表示不在这几个值当中的数据
mysql> select ename,sal from emp where sal not in(800,5000);
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| TURNER | 1500.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
10 rows in set (0.00 sec)
模糊查询
like称为模糊查询,支持%或下划线匹配
%匹配任意个字符(%是一个特殊的符号)
下划线,一个下划线只匹配一个字符 (下划线是一个特殊的符号)
找出名字中含有O的?
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| FORD |
+-------+
2 rows in set (0.00 sec)
找出名字以D结尾的?
mysql> select ename from emp where ename like '%D';
+-------+
| ename |
+-------+
| WARD |
| FORD |
+-------+
2 rows in set (0.00 sec)
找出名字以K开始的?
mysql> select ename from emp where ename like 'K%';
+-------+
| ename |
+-------+
| KING |
+-------+
1 row in set (0.00 sec)
找出第二个字母是A的?
mysql> select ename from emp where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec)
找出第三个字母是R的?
mysql> select ename from emp where ename like '__R%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| TURNER |
| FORD |
+--------+
4 rows in set (0.00 sec)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GVREBHUe-1637543546165)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20211118212820312.png)]
ORD |
±------+
2 rows in set (0.00 sec)
找出名字以K开始的?
~~~ mysql
mysql> select ename from emp where ename like 'K%';
+-------+
| ename |
+-------+
| KING |
+-------+
1 row in set (0.00 sec)
找出第二个字母是A的?
mysql> select ename from emp where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec)
找出第三个字母是R的?
mysql> select ename from emp where ename like '__R%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| TURNER |
| FORD |
+--------+
4 rows in set (0.00 sec)
[外链图片转存中…(img-GVREBHUe-1637543546165)]