mysql学习一

sql语句
1、简单单表查询语句
select * from 表名;
select 属性 from 表名;
select 属性 as ‘xxx’ from 表名 设置别名xxx，as可以省略
2、单表的添加语句
insert 表名（属性）values（属性值）;
3、单表的删除语句
delete from 表名 where 条件
如：deletefrom dept where deptno = 50;

4、单表的修改语句
update 表名 set xxx where 条件
如：update dept set dname = ‘shshh’,loc = ‘nisod’ where deptno = 50;

5、条件查询
条件查询需要用到where语句，where必须放到from语句表的后面
支持如下运算符

运算符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and ….	两个值之间,等同于 >= and <=
is null	为null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个or（not in不在这个范围中）
not	not可以取非，主要用在is 或in中
like	like称为模糊查询，支持%或下划线匹配
	%匹配任意个字符
	下划线，一个下划线只匹配一个字符

5.1 <>操作符
查询薪水不等于5000的员工
select empno, ename, sal from emp where sal <> 5000;
select empno, ename, sal from emp where sal != 5000;

5.2 between … and …操作符
查询薪水为1600到3000的员工(第一种方式，采用>=和<=)
select empno, ename, sal from emp where sal >= 1600 and sal <= 3000;
select empno, ename, sal from emp where sal between 1600 and 3000;

5.3 is null
Null为空，但不是空串，为null可以设置这个字段不填值，如果查询为null的字段，采用is null
查询津贴为空的员工
select * from emp where comm is null;

5.4 and
and表示并且的含义，表示所有的条件必须满足
工作岗位为MANAGER,薪水大于2500的员工
select * from emp where job=‘MANAGER’ and sal > 2500;

5.5 or
or，只要满足条件即可,相当于包含
查询出job为manager或者job为salesman的员工
select * from emp where job=‘MANAGER’ or job=‘SALESMAN’;

5.6 表达式的优先级
查询薪水大于1800，并且部门代码为20或30的员工（错误的写法）
select * from emp where sal > 1800 and deptno = 20 or deptno = 30;

查询薪水大于1800，并且部门代码为20或30的（正确的写法）
select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);

5.7 in
in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些
查询出job为manager或者job为salesman的员工
select * from emp where job in (‘manager’,‘salesman’);

5.8 not
查询出薪水不包含1600和薪水不包含3000的员工（第一种写法）
select * from emp where sal <> 1600 and sal <> 3000;

查询出薪水不包含1600和薪水不包含3000的员工（第二种写法）
select * from emp where not (sal = 1600 or sal = 3000);

查询出薪水不包含1600和薪水不包含3000的员工（第三种写法）
select * from emp where sal not in (1600, 3000);

5.9 like
Like可以实现模糊查询，like支持%和下划线匹配
查询姓名以M开头所有的员工
select * from emp where ename like ‘M%’;

查询姓名以N结尾的所有的员工
select * from emp where ename like ‘%N’;

查询姓名中包含O的所有的员工
select * from emp where ename like ‘%O%’;

查询姓名中第二个字符为A的所有员工
select * from emp where ename like ‘_A%’;

Like 中的表达式必须放到单引号中|双引号中

6、数据排序
6.1单一字段排序 order by
排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面
asc ：升序
desc：降序

6.2多个字段排序
按照job和薪水倒序
select * from emp order by job desc, sal desc;

6.3使用字段的位置来排序
按照薪水升序
select * from emp order by 6;

不建议使用此种方式，采用数字含义不明确，程序不健壮

7、分组函数/聚合函数/多行处理函数
count 取得记录数
sum 求和
avg 取平均
max 取最大的数
min 取最小的数

注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。
select count(*) from emp where xxx; 符合条件的所有记录总数。
select count(comm) from emp; comm这个字段中不为空的元素总数。

注意：分组函数不能直接使用在where关键字后面。
mysql> select ename,sal from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function

注意：where 后面不能存在聚合函数

7.1 聚合函数（支持取别名） count
取得所有的员工数
select count(*) from emp;

7.2 sum
Sum可以取得某一个列的和，null会被忽略
取得薪水的合计
select sum(sal) from emp;

7.3 avg
取得某一列的平均值
取得平均薪水
select avg(sal) from emp;

7.4 max
取得某个一列的最大值
取得最高薪水
select max(sal) from emp;

取得最晚入职得员工
select max(str_to_date (hiredate, ‘%Y-%m-%d’)) from emp;

7.5 min
取得某个一列的最小值
取得最低薪水
select min(sal) from emp;

取得最早入职得员工（可以不使用str_to_date转换）
select min(str_to_date(hiredate, ‘%Y-%m-%d’)) from emp;

7.6 组合聚合函数
可以将这些聚合函数都放到select中一起使用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
如下：

mysql> select count(),sum(sal),avg(sal),max(sal),min(sal) from emp;
±---------±---------±------------±---------±---------+
| count() | sum(sal) | avg(sal) | max(sal) | min(sal) |
±---------±---------±------------±---------±---------+
| 14 | 29025.00 | 2073.214286 | 5000.00 | 800.00 |
±---------±---------±------------±---------±---------+

8、分组查询
分组查询主要涉及到两个子句，分别是：group by和having
8.1、group by
取得每个工作岗位的工资合计，要求显示岗位名称和工资合计
select job, sum(sal) from emp group by job;

如果使用了order by，order by必须放到group by后面

按照工作岗位和部门编码分组，取得的工资合计
分组语句
select job,deptno,sum(sal) from emp group by job,deptno;

在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段

8.2 having
如果想对分组数据再进行过滤需要使用having子句
取得每个岗位的平均工资大于2000
select job, avg(sal) from emp group by job having avg(sal) >2000;

分组函数的执行顺序：
根据条件查询数据
分组
采用having过滤，取得正确的数据

8.3、select语句总结
一个完整的select语句格式如下

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..

以上语句的执行顺序

1.执行from找到表
2.首先执行where语句过滤原始数据
3.执行group by进行分组
4.执行having对分组数据进行操作
5.执行select选出数据
6.执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

9、连接查询
9.1、SQL92语法
连接查询：也可以叫跨表查询，需要关联多个表进行查询

显示每个员工信息，并显示所属的部门名称

select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno;

也可以使用别名

select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）

9.2、SQL99语法（join … on…）
（内连接）显示薪水大于2000的员工信息，并显示所属的部门名称
采用SQL92语法：

select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno and e.sal > 2000;
采用SQL99语法：

select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
或
select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno where e.sal>2000;
在实际中一般不加inner关键字

Sql92语法和sql99语法的区别：99语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比sql92更清晰
（外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来
右连接：
select e.ename, e.sal, d.dname from emp e right join dept d on e.deptno=d.deptno;
左连接：
select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno=d.deptno;
以上两个查询效果相同

连接分类：
内连接（自己连接自己）

• 表1 inner join 表2 on 关联条件
• 做连接查询的时候一定要写上关联条件
• inner 可以省略
  缺点：如果有字段是null，不被被筛选出来，会被过滤掉

外连接
*左外连接

• 表1 left outer join 表2 on 关联条件
• 做连接查询的时候一定要写上关联条件
• outer 可以省略*右外连接
• 表1 right outer join 表2 on 关联条件
• 做连接查询的时候一定要写上关联条件
• outer 可以省略
  *左外连接（左连接）和右外连接（右连接）的区别：
  左连接即左边的表为主表，主表查询的字段都要显示出来，即使是对应的字段是null。
  *左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示
  *右连接恰恰相反，以上左连接和右连接也可以加入outer关键字，但一般不建议这种写法，如：

select e.ename, e.sal, d.dname from emp e right outer join dept d on e.deptno=d.deptno;
select e.ename, e.sal, d.dname from dept d left outer join emp e on e.deptno=d.deptno;

左连接能完成的功能右连接一定可以完成

10、子查询
子查询就是嵌套的select语句，可以理解为子查询是一张表

10.1、在where语句中使用子查询，也就是在where语句中加入select语句
查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
实现思路：
1、首先取得管理者的编号，去除重复的
select distinct mgr from emp where mgr is not null;

distinct 去除重复行
2、查询员工编号包含管理者编号的
select empno, ename from emp where empno in(select mgr from emp where mgr is not null);

查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水
实现思路
1、取得平均薪水
select avg(sal) from emp;
2、取得大于平均薪水的员工
select empno, ename, sal from emp where sal > (select avg(sal) from emp);

10.2、在from语句中使用子查询，可以将该子查询看做一张表
查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
首先取得管理者的编号，去除重复的

select distinct mgr from emp where mgr is not null;

将以上查询作为一张表，放到from语句的后面
使用92语法：

 select e.empno, e.ename from emp e, (select distinct mgr from emp where mgr is not null) m where e.empno=m.mgr;

使用99语法：

select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;

查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号
实现思路
1、首先取得各个部门的平均薪水

 select deptno, avg(sal) avg_sal from emp group by deptno;	

2、将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级

select deptno,avg(sal) avg_sal from emp group by deptno;
select * from salgrade;
select a.deptno,a.avg_sal,g.grade from (select deptno,avg(sal) avg_sal from emp group by deptno ) a join salgrade g on a.avg_sal between g.losal and hisal;

10.3、在select语句中使用子查询
查询员工信息，并显示出员工所属的部门名称
第一种做法，将员工表和部门表连接

select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

第二种做法，在select语句中再次嵌套select语句完成部分名称的查询

select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

11、分页 limit
limit 起始索引位置，分页单位（查询个数）
limit关键字永远出现在sql语句的最后面。

例子：
找出员工薪水排在前五的员工
select ename,sal from emp order by sal desc;

select ename,sal from emp order by sal desc limit 0,5;

找出员工薪水排在4-9名的员工
select ename,sal from emp order by sal desc limit 3,6;

用上以上所有条件查询关键字：
select 5
from 1
where 2
group by 3
having 4
order by 6
limit 7
数字是执行顺序