[大数据]面试 ---mysql 基础

SQL的分类
DDL 数据库定义语言 drop trance alter create

DML 数据库操作语言 insert delete update

DQL 数据库查询语言 select

TCL 事务控制语言 commit rollback

DCL 数据控制语言 grant revoke

创建数据库
create databse 库名

选择数据库
user 表名

导入数据
source D:\ bjpowernode.sql （位置）

删除数据库
drop database 表名

查看数据库版本
mysql -version

mysql -V

查询当前使用 数据库
select database（）

查询数据库版本
select version（）

退出数据库

可使用\q、QUIT 或 EXIT：

查看和指定现有的数据库
show databases

查看当前库中的表
show 表名

查看其他库中的表
show 表名 from 表名

show tables from ;

查看表的结构
desc 表名
desc

查看表的创建语句
show create table 表名

计算员工的年薪
列出员工的编号，姓名和年薪

SELECT
EMPNO,
ENAME,
SAL * 12
FROM
emp

将查询出来的字段显示为中文

SELECT
EMPNO AS ‘员工编号’,
ENAME AS ‘员工姓名’,
SAL * 12 AS ‘年薪’
FROM
emp

查询薪水为 5000 的员工

SELECT
EMPNO,
ENAME,
SAL
FROM
emp
WHERE
SAL = ‘5000’

查询 job 为 MANAGER 的员工’
SELECT
*
FROM
emp
WHERE
JOB='MANAGER ’

查询薪水不等于 5000 的员工
SELECT * FROM emp WHERE sal !=‘5000’
SELECT * FROM emp WHERE sal <>‘5000’

查询薪水为 1600 到 3000 的员工
select * from emp WHERE sal >=1600 or sal <=3000
select * from emp WHERE sal between 1600 and 3000

查询津贴为空的员工

SELECT * FROM emp WHERE comm IS NULL

工作岗位为 MANAGER,薪水大于 2500 的员工
select * from emp where job=‘MANAGER’ and sal > 2500;

查询出 job 为 manager 或者 job 为 salesman 的员工
select * from emp where job=‘MANAGER’ or job=‘SALESMAN’;

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

查询出薪水包含 1600 和薪水包含 3000 的员工

select * from emp where sal =1600 or  sal = 3000;
select * from emp where sal in(1600, 3000);

查询出薪水不包含 1600 和薪水不包含 3000 的员工
select * from emp where sal <>1600 and sal <>3000;
select * from emp where sal !=1600 and sal != 3000;
select * from emp where not (sal = 1600 or sal = 3000);
select * from emp where sal not in (1600, 3000);

查询出津贴不为 null 的所有员工
select * from emp where comm is not null;

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%’

** 排序数据 **

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

按照薪水由小到大排序(系统默认由小到大)
select * from emp order by sal;

取得 job 为 MANAGER 的员工，按照薪水由小到大排序(系统默认由小到大)
select * from emp where job=‘MANAGER’ order by sal;

按照多个字段排序，如：首先按照 job 排序，再按照 sal 排序
select * from emp order by job,sal;

手动指定排序顺序
asc（升序） desc （降序）


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

lower 转换成小写

查询员工，将员工姓名全部转换成小写
select lower(ENAME) from emp ;

upper 转换成大写
查询 job 为 manager 的员工

manager 在数据库为大写
select * from emp where job=upper(‘manager’);

substr
查询姓名以 M 开头所有的员工
select * from emp where ename like ‘M%’;
select * from emp where substr(ename,1,1)=upper(‘m’);

length 取长度
取得员工姓名长度为 5
select length(ename), ename from emp where length(ename)=5;

trim
trim 会去首尾空格，不会去除中间的空格
取得工作岗位为 manager 的所有员工
select * from emp where job=trim(upper(’ manager '));

str_to_date (必须严格按照标准输出）
查询 1981-02-20 入职的员工
select * from emp where HIREDATE=‘1981-02-20’;

select * from emp where HIREDATE=str_to_date(‘1981-02-20’,’%Y-%m-%d’);
select * from emp where HIREDATE=str_to_date(‘02-20-1981’,’%m-%d-%Y’);

str_to_date 可以将字符串转换成日期，具体格式 str_to_date (字符串，匹配格式)

查询 1981-02-20 以后入职的员工，将入职日期格式化成 yyyy-mm-dd hh:mm:ss
select empno, ename, date_format(hiredate, ‘%Y-%m-%d %H:%i:%s’) as hiredate from emp;

select date_format(now(),’%Y-%m-%d %H %i %s’)

format 设置千分位
查询员工薪水加入千分位
select empno, ename, Format(sal, 0) from emp;

round 四舍五入
select round(123.56);

rand() 生成随机数

select * from emp order by rand() limit 2; order by 必须写上。

ifnull
select ifnull(comm,0) from emp;

如果 comm 为 null 就替换为 0
在 SQL 语句当中若有 NULL 值参与数学运算，计算结果一定是 NULL
为了防止计算结果出现 NULL，建议先使用 ifnull 空值处理函数预先处理。
以下 SQL 是计算年薪的：
select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;

case … when … then ……else …end

如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%
select empno, ename, job, sal, case job when ‘MANAGER’ then sal1.1 when ‘SALESMAN’ then sal1.5 end as newsal from emp;

select empno, ename, job, sal, case job when ‘MANAGER’ then sal1.1 when ‘SALESMAN’ then
sal1.5 else sal=0 end as newsal from emp;

select empno, ename, job, sal, case job when ‘MANAGER’ then sal*1.1 end as newsal from emp;

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

注意：分组函数不能直接使用在 where 关键字后面。

取得所有的员工数
select count(*) from emp;

取得津贴不为 null 员工数
select count(comm) from emp;

采用 count(字段名称)，不会取得为 null 的记录

取得工作岗位的个数
select count(distinct job ) from emp;

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

select sum(comm) from emp;

取得薪水的合计（sal+comm）
select sum(sal+comm) from emp;

从以上结果来看，不正确，原因在于 comm 字段有 null 值，所以无法计算，sum 会忽略掉，正确的做法是将 comm 字
段转换成 0

select sum(sal+IFNULL(comm, 0)) from emp;

avg 取得某一列的平均值

select avg(sal) from emp;

max
取得某个一列的最大值

select max(sal) from emp;

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

select max(hiredate) from emp;

min取得某个一列的最小值

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

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

分组查询主要涉及到两个子句，分别是：group by 和 having

group by
取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

如果使用了 order by，order by 必须放到 group by 后
select job , sum(sal) FROM emp GROUP BY job ORDER BY sal;

如果想对分组数据再进行过滤需要使用 having 子句

取得每个岗位的平均工资大于 2000
SELECT job, avg (sal) FROM emp
group by job HAVING avg (sal) >2000

SELECT job, avg (sal) FROM emp WHERE sal >2000
group by job

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

连接查询

以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）
指定连接条件
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;

取得员工和所属的领导的姓名
select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;
以上称为“自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，

子查询
在 where 语句中使用子查询，也就是在 where 语句中加入 select 语句
distinct 去除重复行

首先取得管理者的编号，去除重复的
select distinct mgr from emp where mgr is not null;

查询员工编号包含管理者编号的
select empno, ename from emp where empno in(select mgr from emp where mgr is not null);

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

在 from 语句中使用子查询，可以将该子查询看做一张表

查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号
实现思路
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;

union
union 可以合并集合（相加）
查询 job 包含 MANAGER 和包含 SALESMAN 的员工
select * FROM emp
WHERE
job=‘MANAGER’
or
job=‘SALESMAN’
select * from emp where job in(‘MANAGER’, ‘SALESMAN’);

elect * from emp where job=‘MANAGER’
union
select * from emp where job=‘SALESMAN’

合并结果集的时候，需要查询字段对应个数相同。在 Oracle 中更严格，不但要求个数相同，而且还要求类型对应相同。

limit 的使用 limit 起始值，查询个数

取得前 5 条数据
select * from emp limit 0,5;

从第二条开始取两条数据
select * from emp limit 1,2;

取得薪水最高的前 5 名
select * from emp order by sal desc limit 0,5;
select * from emp e order by e.sal desc limit 5;
select IFNULL (
(
select
distinct sal from emp order by sal desc limit 1,1
),0
) as ‘Two’
select IFNULL(
(
select
distinct sal from emp order by sal desc limit 100,1
),0
) as ‘Two’

18.1、什么是视图
? 视图是一种根据查询（也就是 SELECT 表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。
? 视图有时也被成为“虚拟表”。
? 视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。
? 相对于从基表中直接获取数据，视图有以下好处：
– 访问数据变得简单
– 可被用来对不同用户显示不同的表的内容
用来协助适配表的结构以适应前端现有的应用程序
视图作用：

• 提高检索效率
• 隐藏表的实现细节【面向视图检索】

18.2、创建视图
CREATE view v_all as ( select * from emp) ;
create view as （sql语句）
如下示例：查询员工的姓名，部门，工资入职信息等信息。
select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno
= e.deptno and e.deptno = 10;
为什么使用视图？因为需求决定以上语句需要在多个地方使用，如果频繁的拷贝以上代码，会给维护带来成本，视图
可以解决这个问题
create view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where
e.deptno
= e.deptno and e.deptno = 10;
create view v_dept_avg_sal_grade as select a.deptno, a.avg_sal, b.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b
where a.avg_sal between b.losal and b.hisal;/注意 mysql 不支持子查询创建视图/
18.3、修改视图
alter view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from e
mp e,dept d where e.deptno = 20;
86 / 103
18.4、删除视图
drop view if exists v_dept_emp;

19.4、导出导入
19.4.1、导出
19.4.1.1、导出整个数据库
在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123
19.4.1.2、导出指定库下的指定表
在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123
19.4.2、导入
登录 MYSQL 数据库管理系统之后执行：source D:\ bjpowernode.sql

19.1、新建用户
CREATE USER username IDENTIFIED BY ‘password’;
说明:username——你将创建的用户名, password——该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密
码登陆服务器.
例如：
create user p361 identified by ‘123’;
–可以登录但是只可以看见一个库 information_schema
19.2、授权
命令详解
mysql> grant all privileges on dbname.tbname to ‘username’@‘login ip’ identified by ‘password’ with grant option;

1. dbname=*表示所有数据库
2. tbname=*表示所有表
3. login ip=%表示任何 ip
4. password 为空，表示不需要密码即可登录
5. with grant option; 表示该用户还可以授权给其他用户
   ? 细粒度授权
   首先以 root 用户进入 mysql，然后键入命令：grantselect,insert,update,delete on . to p361 @localhost Identified by “123”;
   如果希望该用户能够在任何机器上登陆 mysql，则将 localhost 改为 “%”。
   ? 粗粒度授权
   我们测试用户一般使用该命令授权，
   GRANT ALL PRIVILEGES ON . TO ‘p361’@’%’ Identified by “123”;
   注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
   GRANT ALL PRIVILEGES ON . TO ‘p361’@’%’ Identified by “123” WITH GRANT OPTION;
   privileges 包括：
6. alter：修改数据库的表
   87 / 103
7. create：创建新的数据库或表
8. delete：删除表数据
9. drop：删除数据库/表
10. index：创建/删除索引
11. insert：添加表数据
12. select：查询表数据
13. update：更新表数据
14. all：允许任何操作
15. usage：只允许登录
    19.3、回收权限
    命令详解
    revoke privileges on dbname[.tbname] from username;
    revoke all privileges on . from p361;
    use mysql
    select * from user
    进入 mysql 库中
    修改密码;
    update user set password = password(‘qwe’) where user = ‘p646’;
    刷新权限;
    flush privileges

创建表加入约束
? 常见的约束
a) 非空约束，not null
b) 唯一约束，unique
c) 主键约束，primary key
d) 外键约束，foreign key

唯一约束，unique
唯一性约束，它可以使某个字段的值不能重复，如：email 不能重复：

非空约束，not null
非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空

创建表
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default ‘m’,
birthday date,
email varchar(30) unique,
classes_id int(3)
)

drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default ‘m’,
birthday date,
email varchar(30) ,
classes_id int(3) ,
constraint email_unique unique(email)/表级约束/
)

drop table if exists t_student;
create table t_student()
student_id int(10) primary key,/列级约束/
student_name varchar(20) not null,
sex char(2) default ‘m’,
birthday date,
email varchar(30) ,
classes_id int(3)
)

drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default ‘m’,
birthday date,
email varchar(30) ,
classes_id int(3),
CONSTRAINT p_id PRIMARY key (student_id)
)

首先建立班级表 t_classes
drop table if exists t_classes;
create table t_classes(
classes_id int(3),
classes_name varchar(40),
constraint pk_classes_id primary key(classes_id)
)
在 t_student 中加入外键约束
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3),
constraint student_id_pk primary key(student_id),
constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
)

以上成功的插入了学生信息，当时 classes_id 没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空
drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3) not null,
constraint student_id_pk primary key(student_id),
constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
70 / 103
)

取得每个部门最高薪水的人员名称
取得每个部门最高薪水(按照部门编号分组，找出每一组最大值)
select ename, max(sal) as maxsal , deptno from emp e 
group by deptno;

select  e.ename, t.*
from emp e JOIN
(select ename, max(sal) as maxsal , deptno from emp e 
group by deptno) t 
on  t.deptno=e.deptno and e.sal=t.maxsal

哪些人的薪水在部门的平均薪水之上
SELECT deptno, avg(sal) as avgsal FROM emp
GROUP BY deptno


select e.ename,e.sal 
FROM emp e
JOIN
(SELECT deptno, avg(sal)  as avgsal  FROM emp
GROUP BY deptno) t
on t.deptno=e.DEPTNO and e.SAL> avgsal 


取得部门中（所有人的）平均的薪水等级，如下
找出每个人的薪水等级
SELECT e.ename,s.grade
FROM emp e
JOIN salgrade s
on e.sal between s.losal and s.hisal;
基于以上的结果继续按照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 deptno

不准用组函数（Max），取得最高薪水
sal降序，limit 1
SELECT e.*
FROM emp e
ORDER BY sal desc limit 0,1

select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

取得平均薪水最高的部门的部门编号
select  avg(sal) as avgsal , deptno from emp e 
group by deptno limit 1;


SELECT max(t.avgsal)
FROM emp e
JOIN 
(select  avg(sal) as avgsal , deptno from emp e 
group by deptno)  t
on e.deptno=t.deptno 

select 
		deptno,avg(sal) as avgsal 
	from 
		emp 
	group by 
		deptno
	having	avgsal=(
	SELECT max(t.avgsal)
FROM emp e
JOIN 
(select  avg(sal) as avgsal , deptno from emp e 
group by deptno)  t
on e.deptno=t.deptno )
		
取得平均薪水最高的部门的部门名称
SELECT d.DNAME
FROM dept d
JOIN 
	 (select  avg(sal) as avgsal , deptno from emp e 
group by deptno limit 1	) t
ON d.DEPTNO =t.deptno