查询部门名字
mysql> select dname from dept; +------------+ | dname | +------------+ | ACCOUNTING | | RESEARCH | | SALES | | OPERATIONS | +------------+ 4 rows in set (0.29 sec)查询两个字段,或者多个字段使用,隔开
查询部门编号和部门名
mysql> select deptno,dname from dept; +--------+------------+ | deptno | dname | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+ 4 rows in set (0.03 sec)查询所以字段
方式一
select a,b,c,d,e,f... 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.02 sec)给查询的列起别名
mysql> select deptno,dname as deptname from dept; +--------+------------+ | deptno | deptname | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+ 4 rows in set (0.03 sec)
省略as
mysql> select deptno,dname deptname from dept; +--------+------------+ | deptno | deptname | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+ 4 rows in set (0.03 sec)
别名带空格
mysql> select deptno,dname 'dept name' from dept; +--------+------------+ | deptno | dept name | +--------+------------+ | 10 | ACCOUNTING | | 20 | RESEARCH | | 30 | SALES | | 40 | OPERATIONS | +--------+------------+ 4 rows in set (0.03 sec)
注意:在所有的数据库中,字符串同一使用单引号括起来,单引号是标准,双引号在oracle数据库中用不了,但是在mysql中可以使用。
列参合数学运算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 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ 14 rows in set (0.07 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 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ 14 rows in set (0.05 sec)条件查询
查询工资等于800的编号和姓名
mysql> select empno,ename from emp where sal = 800; +-------+-------+ | empno | ename | +-------+-------+ | 7369 | SMITH | +-------+-------+ 1 row in set (0.13 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 | | 7788 | SCOTT | | 7839 | KING | | 7844 | TURNER | | 7876 | ADAMS | | 7900 | JAMES | | 7902 | FORD | | 7934 | MILLER | +-------+--------+ 13 rows in set (0.04 sec) 或者: mysql> select empno,ename from emp where sal <> 800;
查询工资小于2000的编号和姓名
select empno,ename from emp where sal < 2000;
查询工资大于2000的编号和姓名
select empno,ename from emp where sal > 2000;
查询在两个之间的数据
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 | | 7788 | SCOTT | 3000.00 | | 7902 | FORD | 3000.00 | +-------+-------+---------+ 5 rows in set (0.04 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 | | 7788 | SCOTT | 3000.00 | | 7902 | FORD | 3000.00 | +-------+-------+---------+ 5 rows in set (0.04 sec)
注意:使用between and的时候,必须遵循左小右大。
查询哪些员工的津贴/补助为null
该写法错误,不能使用等号
mysql> select empno,ename,sal from emp where comm = null; Empty set
正确写法
mysql> select empno,ename,sal from emp where comm is null; +-------+--------+---------+ | empno | ename | sal | +-------+--------+---------+ | 7369 | SMITH | 800.00 | | 7566 | JONES | 2975.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7839 | KING | 5000.00 | | 7876 | ADAMS | 1100.00 | | 7900 | JAMES | 950.00 | | 7902 | FORD | 3000.00 | | 7934 | MILLER | 1300.00 | +-------+--------+---------+ 10 rows in set (0.04 sec)
查询哪些员工的津贴/补助不为null
mysql> select empno,ename,sal from emp where comm is not null; +-------+--------+---------+ | empno | ename | sal | +-------+--------+---------+ | 7499 | ALLEN | 1600.00 | | 7521 | WARD | 1250.00 | | 7654 | MARTIN | 1250.00 | | 7844 | TURNER | 1500.00 | +-------+--------+---------+ 4 rows in set (0.03 sec)
查询工作岗位是MANAGER并且工作岗位大于2500的员工信息
mysql> select empno,ename,sal,job from emp where job = 'MANAGER' and sal >2500; +-------+-------+---------+---------+ | empno | ename | sal | job | +-------+-------+---------+---------+ | 7566 | JONES | 2975.00 | MANAGER | | 7698 | BLAKE | 2850.00 | MANAGER | +-------+-------+---------+---------+ 2 rows in set (0.03 sec)
查询工作岗位是MANAGER和SALESMAN的员工
mysql> select empno,ename,sal,job from emp where job = 'MANAGER' or job = 'SALESMAN'; +-------+--------+---------+----------+ | empno | ename | sal | job | +-------+--------+---------+----------+ | 7499 | ALLEN | 1600.00 | SALESMAN | | 7521 | WARD | 1250.00 | SALESMAN | | 7566 | JONES | 2975.00 | MANAGER | | 7654 | MARTIN | 1250.00 | SALESMAN | | 7698 | BLAKE | 2850.00 | MANAGER | | 7782 | CLARK | 2450.00 | MANAGER | | 7844 | TURNER | 1500.00 | SALESMAN | +-------+--------+---------+----------+ 7 rows in set (0.03 sec)
查询工资大于2500,并且部门编号为10或20部门的员工
and和or同时出现有优先级问题
and的优先级比or高
会先执行and,然后执行or,想要or先执行需要加小括号。
例
mysql> select * from emp where sal > 2500 and deptno=10 or deptno = 20; +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ 6 rows in set (0.04 sec)
加括号后
mysql> select * from emp where sal > 2500 and (deptno=10 or deptno = 20); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ 4 rows in set (0.04 sec)
使用 in 查询工作岗位是MANAGER和SALESMAN的员工
in 相当于多个or
mysql> select empno,ename,sal,job from emp where job in ('MANAGER','SALESMAN');
+-------+--------+---------+----------+
| empno | ename | sal | job |
+-------+--------+---------+----------+
| 7499 | ALLEN | 1600.00 | SALESMAN |
| 7521 | WARD | 1250.00 | SALESMAN |
| 7566 | JONES | 2975.00 | MANAGER |
| 7654 | MARTIN | 1250.00 | SALESMAN |
| 7698 | BLAKE | 2850.00 | MANAGER |
| 7782 | CLARK | 2450.00 | MANAGER |
| 7844 | TURNER | 1500.00 | SALESMAN |
+-------+--------+---------+----------+
7 rows in set (0.05 sec)
使用 in 查询工作岗位不是MANAGER和SALESMAN的员工
mysql> select empno,ename,sal,job from emp where job not in ('MANAGER','SALESMAN');
+-------+--------+---------+-----------+
| empno | ename | sal | job |
+-------+--------+---------+-----------+
| 7369 | SMITH | 800.00 | CLERK |
| 7788 | SCOTT | 3000.00 | ANALYST |
| 7839 | KING | 5000.00 | PRESIDENT |
| 7876 | ADAMS | 1100.00 | CLERK |
| 7900 | JAMES | 950.00 | CLERK |
| 7902 | FORD | 3000.00 | ANALYST |
| 7934 | MILLER | 1300.00 | CLERK |
+-------+--------+---------+-----------+
7 rows in set (0.03 sec)
模糊查询
like称为模糊查询,支持%或下划线匹配
%匹配任意多个字符
下划线,一个下划线只匹配一个字符
(%是一个特殊的字符,_也是 一个特殊的字符)
查询名字中含有O的
mysql> select ename from emp where ename like '%O%'; +-------+ | ename | +-------+ | JONES | | SCOTT | | FORD | +-------+ 3 rows in set (0.03 sec)
查询名字以T结尾的
mysql> select ename from emp where ename like '%T'; +-------+ | ename | +-------+ | SCOTT | +-------+ 1 row in set (0.03 sec)
查询名字以K开始的
mysql> select ename from emp where ename like 'K%'; +-------+ | ename | +-------+ | KING | +-------+ 1 row in set (0.03 sec)
找出第二个字母是A的
mysql> select ename from emp where ename like '_A%'; +--------+ | ename | +--------+ | WARD | | MARTIN | | JAMES | +--------+ 3 rows in set (0.02 sec)
找出第三个字母是R的
mysql> select ename from emp where ename like '__R%'; +--------+ | ename | +--------+ | WARD | | MARTIN | | TURNER | | FORD | +--------+ 4 rows in set (0.03 sec)
找出名字有下划线的
是转义字符
select ename from emp where ename like '%_%';排序
查询所有员工薪资并升序
order by sal 后面不写默认是升序
指定升序 asc
mysql> select ename,sal from emp order by sal asc; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+ 14 rows in set (0.03 sec)
查询所有员工薪资并降序
指定降序 desc
mysql> select ename,sal from emp order by sal desc; +--------+---------+ | ename | sal | +--------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+ 14 rows in set (0.03 sec)
查询员工名字和薪资,要求按照薪资升序,如果薪资一样的话,再按照名字升序排序
mysql> select ename,sal from emp order by sal asc,ename asc;//sal在前,只有sal相等的时候才会考虑启用ename排序 +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | MARTIN | 1250.00 | | WARD | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | KING | 5000.00 | +--------+---------+ 14 rows in set (0.03 sec)
找出工资在1250到3000之间的员工信息,要求按照薪资的降序排列
mysql> select ename,sal from emp where sal between 1250 and 3000 order by sal desc; +--------+---------+ | ename | sal | +--------+---------+ | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | +--------+---------+ 10 rows in set (0.03 sec)



