// 简单查询 多条查询用逗号隔开
select 字段列表 from 表名;
// 条件查询 表名字emp
mysql> select empno,ename,sal from emp where sal = 5000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7839 | KING | 5000.00 |
+-------+-------+---------+
// 找出工资 >= 3000,并且<= 5000的
mysql> select ename,sal from emp where sal >= 3000 and sal <= 5000;
+-------+---------+
| ename | sal |
+-------+---------+
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
// 使用 or (或者) 查询
mysql> select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
mysql> select ename,job from emp where job in('SALESMAN','MANAGER');
// 上面两个查询出来的结果一样
+--------+-----------+
| ename | job |
+--------+-----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+-----------+
// 查询工资大于1800 部门在20或者30 的员工
mysql> select empno,ename,sal ,deptno from emp where sal > 1800 and (deptno = 20 or deptno = 30);
+-------+-------+------+--------+
| empno | ename | sal | deptno |
+-------+-------+------+--------+
| 7566 | JONES | 2975 | 20 |
| 7698 | BLAKE | 2850 | 30 |
| 7788 | SCOTT | 3000 | 20 |
| 7902 | FORD | 3000 | 20 |
+-------+-------+------+--------+
4 rows in set (0.00 sec)
and 比 or 的优先级高要加()
// 模糊查询
// 查询包含O字母的名字
mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
// 第一个字母是S
mysql> select ename from emp where ename like 'S%';
+--------+
| ename |
+--------+
| SIMITH |
| SCOTT |
+--------+
// 最后一个字母是S的
mysql> select ename from emp where ename like '%S';
+-------+
| ename |
+-------+
| JONES |
| JAMES |
+-------+
// 找出第二个字母是A的
mysql> select ename from emp where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
// 找出倒数第二个字母是E的
mysql> select ename from emp where ename like '%E_';
+--------+
| ename |
+--------+
| ALLEN |
| JONES |
| TURNER |
| JAMES |
| MILLER |
+--------+
// 排序查询 按照工资升序
mysql> select ename,sal from emp order by sal;
+---------+------+
| ename | sal |
+---------+------+
| SIMITH | 800 |
| JAMES | 950 |
| ADAMS | 1100 |
| WARD | 1250 |
| MARTIN | 1250 |
| MILLER | 1300 |
| TURNER | 1500 |
| ALLEN | 1600 |
| CLARK | 2450 |
| BLAKE | 2850 |
| JONES | 2975 |
| SCOTT | 3000 |
| FORD | 3000 |
| KING | 5000 |
+---------+------+
// 按照工资降序
mysql> select ename,sal from emp order by sal desc;
+---------+------+
| ename | sal |
+---------+------+
| KING | 5000 |
| SCOTT | 3000 |
| FORD | 3000 |
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| ALLEN | 1600 |
| TURNER | 1500 |
| MILLER | 1300 |
| WARD | 1250 |
| MARTIN | 1250 |
| ADAMS | 1100 |
| JAMES | 950 |
| SIMITH | 800 |
+---------+------+
查询用到的运算符
//日期查询
// 日期类型 %Y-%m-%d
// 此条语句自动把字符串转换成日期类型了
mysql> select ename,hiredate from emp where hiredate = '1981-09-28';
+--------+------------+
| ename | hiredate |
+--------+------------+
| MARTIN | 1981-09-28 |
+--------+------------+
// str_to_date 的使用
//str_to_date 一般在插入数据中使用
mysql> select ename,hiredate from emp where hiredate = str_to_date('12-17-1980','%m-%d-%Y');
+--------+------------+
| ename | hiredate |
+--------+------------+
| SIMITH | 1980-12-17 |
+--------+------------+
// date_format 把date 类型转换成字符串
// date_format 主要使用在查询操作中
mysql> select ename,date_format (hiredate,'%Y年%m月%d日') as hiredate from emp;
+---------+-------------------+
| ename | hiredate |
+---------+-------------------+
| SIMITH | 1980年12月17日 |
| ALLEN | 1981年02月20日 |
| WARD | 1981年02月22日 |
| JONES | 1981年04月02日 |
| MARTIN | 1981年09月28日 |
| BLAKE | 1981年05月01日 |
| CLARK | 1981年06月09日 |
| SCOTT | 1987年04月19日 |
| KING | 1981年11月17日 |
| TURNER | 1981年09月08日 |
| ADAMS | 1987年05月23日 |
| JAMES | 1981年12月03日 |
| FORD | 1981年12月03日 |
| MILLER | 1982年01月23日 |
+---------+-------------------+
// 求和查询 sum
//null 不参加运算
mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
| 2200 |
+-----------+
// 记录查询
// 查询 此字段一共有多少条数据
mysql> select count(deptno) from emp;
+---------------+
| count(deptno) |
+---------------+
| 14 |
+---------------+
// 求平均值
mysql> select avg(sal) from emp;
+-----------+
| avg(sal) |
+-----------+
| 2073.2143 |
+-----------+
// 求最大值
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000 |
+----------+
// 求最小值
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800 |
+----------+
// distinct 把查询结果中重复的去掉
// distinct 前面不能放字段
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
// 统计有多少个工作岗位
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
group by 分组查询
// 表示通过哪个或者哪些字段进行分组
//计算每个部门的平均薪水,(按照部门编号分组,对每一组薪水求平均值)
mysql> select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-----------+
| deptno | avgsal |
+--------+-----------+
| 20 | 2175.0000 |
| 30 | 1566.6667 |
| 10 | 2916.6667 |
+--------+-----------+
// 找出每个工作岗位的最高薪水 MANAGER 除外
mysql> select job,max(sal) from emp where job != 'MANAGER' group by job;
+-----------+----------+
| job | max(sal) |
+-----------+----------+
| CLERK | 1300 |
| SALESMAN | 1600 |
| ANALYST | 3000 |
| PRESIDENT | 5000 |
+-----------+----------+
// where 后面不能写分组函数
// having 和 where 都是完成数据的过滤
// where 在 group by 前面过滤
// having 在 group by 的后面过滤
// 找出每个岗位的平均薪水，要求显示平均薪水大于1500的
mysql> select job , avg(sal) from emp group by job having avg(sal) > 1500;
+-----------+-----------+
| job | avg(sal) |
+-----------+-----------+
| MANAGER | 2758.3333 |
| ANALYST | 3000.0000 |
| PRESIDENT | 5000.0000 |
+-----------+-----------+
3 rows in set (0.00 sec)
SQL语句的执行顺序
1. from
2. where
3. group by
4. having
5. select
6. order by