MySQL数据库常用命令（一）

一、单表查询

1.1、进入数据库： mysql -uroot -p

1.2、退出数据库： quit;

1.3、查看表结构：desc 表名;

1.4、创建数据库：create database 数据库名;

1.5、删除数据库：drop database 数据库名;

1.6、查看当前数据库中所有的表：show tables;

1.7、将.sql文件导入数据库：source .sql文件的绝对路径；

1.8、查看当前数据库版本：select version();

1.9、查看当前所用的数据库：select database();

1.10、切换数据库：use 数据库名;

1.11、查看表名：select create table 表名；

1.12、查询语句：

select  
	字段名1，字段名2，字段名3，... 
from
	表名
where
	条件语句
order by 
	字段名 asc/desc  （asc升序， desc降序）

1.13、分组函数：

分组函数自动忽略NULL

count, sum, max, min, avg
count : 计数
sum : 求和
max : 最大值
min : 最小值
avg : 平均值

用法：

select
	count(字段名)/sum(字段名)/max(字段名)/min(字段名)/avg(字段名)  (这里的 / 是指任选一个指令运行)
from
	表名

1.14、ifnull()函数：

• 在数据库中，如果有数据为NULL，则他在参加任何数学表达式运算时，最终的值都会为null。所以在使用的时候需要用ifnull()函数。
• 语法：ifnull(可能为NULL的字段名, 被当作的数据)
• 作用：将可能为NULL的数据替换成给定的数据

例如，comm字段中可能为NULL，在参与计算时，可以改成：ifnull(comm, 0)

计算员工年薪：

select 
	ename, (sal + ifnull(comm, 0))*12 
from
	emp; 

1.15、group by：按照某个字段或者某些字段进行分组

select
	表达式（可以为 sum(), avg(), max(), min(), count()等）
from
	表名
group by
	字段名；

//用例：查找各岗位的最高工资，并且按照工资降序排序

select job, max(sal) from emp group by job order by sal desc;

1.16、distinct（去除重复的记录）： distinct 字段名

select distinct job from emp;

1.17、执行顺序：

select		5
	...
from		1
	...
where		2
	...
group by	3
	...
having		4
	...
order by	6
	...

二、连接查询

2.1、提高查找效率以及增强可读性的小tip：起别名

select
	别名1.字段名1，别名2.字段名1...
from
	表名1 别名1
inner join  (表示内连接)
	表名2 别名2
on
	连接条件
where
	过滤条件；

2.2、内连接的等值连接

例如：显示各个员工的部门名称

select
	e.ename, d.dname
from
	emp e
inner join     (inner可省略)
	dept d
on 
	e.deptno = d.deptno;

2.3、内连接的非等值连接

例如：找出每个员工的工资等级，要求显示员工名、工资、工资等级

select 
	e.ename, e.sal, s.grade 
from 
	emp e
inner join     (inner可省略)
	salgrade s
on 
	e.sal between s.losal and s.hisal;

2.4 、内连接的自连接

例如：找出每个员工的上级领导，要求显示员工名和对应的领导名（领导跟员工放在一张表上的）

select
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
inner join   (inner可省略)
	emp b
on
	a.mgr = b.empno;

2.5、外连接的左连接

例如：找出每个员工的上级领导，要求显示员工名和对应的领导名，就算有员工没有领导，也应该有NULL与之对应，而不是直接丢弃。

select
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
left outer join   (outer可省略)
	emp b
on
	a.mgr = b.empno;

在上面的例子中，a是主表，b是副表，会对a表进行完全匹配

2.6、外连接的右连接

案例1：找出每个员工的上级领导，要求显示员工名和对应的领导名，就算有员工没有领导，也应该有NULL与之对应，而不是直接丢弃。

select
	a.ename as '员工名', b.ename as '领导名'
from
	emp b
right outer join   (outer可省略)
	emp a
on
	a.mgr = b.empno;

案例2：找出哪个部门没有员工

select
	d.*
from 
	emp e
right join
	dept d
on 
	e.deptno = d.deptno
where
	e.ename is null;

2.7、三张表的连接查询

案例：找出每一个员工的部门名称以及工资等级

select 
	别名1.字段名1, 别名2.字段名, 别名3.字段名, ...
from
	表名1 别名1
join
	表名2 别名2
on
	连接条件
join
	表名3 别名3
on
	连接条件
where
	过滤条件;
...

以上表示：表1和表2先进行表连接，连接之后表1继续和表3进行连接。

案例1：找出每一个员工的部门名称、工资等级：（通过内连接）

select
	e.ename, d.dname, s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

案例2：找出每一个员工的部门名称、工资等级、上级领导：（通过外连接）

select 
	a.ename as '员工', d.dname, s.grade, b.ename as '领导'
from
	emp a
join
	dept d
on
	a.deptno = d.deptno
join
	salgrade s
on
	a.sal between s.losal and s.hisal
left join
	emp b
on 
	a.mgr = b.empno;

三、子查询

在select语句中嵌套使用子查询

出现的位置

select
	...(select)...
from
	...(select)...
where
	...(select)...;

3.1、where后面中使用子查询

案例：找出高于平均薪资的员工信息

select
	*
from
	emp
where
	 sal > (select avg(sal) from emp);

3.2、from后面嵌套子查询

案例1：找出每个部门平均薪水的薪资等级

• 1、找出每个部门的平均薪水

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

• 2、将上面的查询结果看出是一张新表t，让t表和salgrade表连接。条件是：t.avg(sal) between s.losal and s.hisal

最终结果：

select
	t.*, s.grade
from
	(select deptno, avg(sal) as avgsal from emp group by deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

案例2：找出每个部门平均的薪资等级

• 1、找出每个员工的薪水等级

select e.ename, e.sal, e.depton, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

• 2、基于以上结果，继续按照deptno分组，求grade平均值

select 
	e.deptno, avg(s.grade)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
	e.deptno;

3.3、select后面嵌套子查询

案例：找出每个员工所在的部门名称，要求显示员工名和部门名

解法1：

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

解法2：

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

四、union（可以将查询结果相加）

案例：找出工作岗位为MANAGER和SALESMAN的员工

解法1：

select e.ename, e.job from emp where e.job in ('manager', 'salesman');

解法2：

select e.ename, e.job from emp e where e.job = 'manager' or e.job = 'salesman';

解法3：(使用union)

select e.ename, e.job from emp e where e.job = 'manager' 
union
select e.ename, e.job from emp e where e.job = 'salesman';

五、limit（重点用于分页查询）

• limit是MySQL特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，rownum）
• limit取结果集中的部分数据
• 语法： limit startIndex, length startIndex：起始位置（第一个数据为0） length：取数据个数
• limit是最后执行的语句

案例1：取出工资前五名的员工（降序取前5个）

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

案例2：找出工资排名在第4到第9的员工

select ename, sal from emp order by sal desc limit 3, 6;