10. MySQL管理
10.1 创建用户
-- Mysql用户的管理 (此时在mysql数据库下)
-- 为什么需要用户?原因:当我们做项目开发时,可以根据不同的开发人员,赋给他们相应的mysql操作权限
-- 所以,mysql数据库管理人员(root),根据需要创建不同的用户,赋给相应的权限,供开发人员使用
-- 1.创建用户,同时指定密码
-- (1):'hsp_edu'@'localhost':表示用户的完整信息,'hsp_edu'是用户名,'localhost'是该用户的登录IP
-- (2):'123456':是登录密码。但是注意,'123456'在mysql的user表中存储的是 password('123456')加密后的密码
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456';
-- 查看新用户hsp_edu,这里只显示几个主要字段
SELECT `host`, `user`, authentication_string
FROM mysql.user;
-- 2.登录演示。创建了用户后,可以在SQLyog或者DOS下登录。如下图:
-- 3.删除用户
DROP USER 'hsp_edu'@'localhost';
-- 4.修改用户密码
-- root用户(权限最高的用户)修改 'hsp_edu'@'localhost'的密码,是可以的
SET PASSWORD FOR 'hsp_edu'@'localhost' = PASSWORD('123456'); #ok,用户hsp_edu下次要用123456登录
- SQLyog下登录hsp_edu(窗口最上面可以查看当前用户,DOS下同理):
- DOS下登录hsp_edu(注意:显示当前用户可操作数据库:show databases。选中某一个数据库:use 数据库名。显示当前可操作的表:show tables):
-- 用户hsp_edu下
-- 演示修改自己的密码
SET PASSWORD = PASSWORD('abcdefg'); #ok,下次用户hsp_edu登录就要用新密码了
-- 修改其他人的密码,需要权限(只有root用户才可以)
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
10.2 管理权限
root 用户可以给其他用户授权,也可以收回其权限。
- 给用户授权:
- 回收权限:
- 权限生效指令(mysql5.7以后的版本基本上赋了权限马上就生效了):
10.3 权限案例
-- 演示用户权限的管理(root用户下)
-- 1.创建用户 hsp 密码 123,要求从本地登录
CREATE USER 'hsp'@'localhost' IDENTIFIED BY '123';
-- 2.使用 root 用户创建数据库 testdb,表news
CREATE DATABASE testdb; #这里执行了之后要切换到testdb数据库
CREATE TABLE news(
id INT,
content VARCHAR(32)
);
-- 添加一条记录
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;
-- 3.给用户hsp 分配 查看news表 和 添加news 的权限
GRANT SELECT, INSERT
ON testdb.news
TO 'hsp'@'localhost'; #分配了权限之后,用户hsp的就可以查看到testdb这个数据库,并能select和insert
-- 增加一个update权限
GRANT UPDATE
ON testdb.news
TO 'hsp'@'localhost'; #hsp用户可以update了
-- 4.修改hsp用户的密码为 abc
SET PASSWORD FOR 'hsp'@'localhost' = PASSWORD('abc');
-- 5.演示回收权限。回收hsp用户在testdb.news的所有权限
REVOKE SELECT, UPDATE, INSERT ON testdb.news FROM 'hsp'@'localhost'; #执行后用户hsp就看不到testdb数据库了
#或者用下面
REVOKE ALL ON testdb.news FROM 'hsp'@'localhost';
-- 6.删除用户hsp
DROP USER 'hsp'@'localhost';
-- 这里在默认情况下,用户hsp只能看到一个默认的系统数据库(hsp用户下)
-- root授权后测试 select 和 insert
-- 测试select
SELECT * FROM news; #ok
-- 测试insert
INSERT INTO news VALUES(200, '上海新闻'); #ok
-- 看看能否修改和delete
UPDATE news SET content = '成都新闻'
WHERE id = 100; #不ok
#当root用户给hsp用户分配了update权限后,上面语句可以执行
-- 如果root撤回了所有权限,那么用户hsp就看不到testdb数据库了
10.4 权限细节
11.MySQL章节的作业
正确答案:注意第 (1) 题的 c 选项双引号包起来的 “Annual Salary” 也可以作为别名,加不加AS都可以。第(2)题注意判断null和非null要用 is null 和 is not null,不能用等于符号=,也不能用不等号<> 或者 != 判断。DBC
-- MySQL 作业
-- 2.写出查看DEPT表和EMP表的结构的sql语句
DESC dept;
DESC emp;
-- 3.使用简单查询语句完成:
-- (1)显示所有部门名称。
SELECT dname
FROM dept;
-- (2)显示所有雇员名及其全年收入13月(工资+补助),并指定列别名"年收入"
SELECT ename, (sal + IF(comm, comm, 0))*13 AS "年收入"
FROM emp;
#IF(comm, comm, 0)也可以用IFNULL(comm, 0)替换
#注意:一个数跟NULL相加,结果为NULL。可以测试:SELECT 700+NULL FROM DUAL; 结果为null
-- 4.限制查询数据。
-- (1)显示工资超过2850的雇员姓名和工资。
SELECT ename, sal
FROM emp
WHERE sal > 2850;
-- (2)显示工资不在1500到2850之间的所有雇员名及工资。
SELECT ename, sal
FROM emp
WHERE sal < 1500 OR sal > 2850; #也可以写成:WHERE NOT (sal>=1500 AND sal<=2850);
-- (3)显示编号为7566的雇员姓名及所在部门名称。
SELECT ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND empno = 7566;
-- (4)显示部门10和30中工资超过1500的雇员名及工资。
SELECT ename, sal
FROM emp
WHERE deptno IN (10, 30) AND sal > 1500;
-- (5)显示无管理者的雇员名及岗位。
SELECT ename, job
FROM emp
WHERE mgr IS NULL;
-- 5.排序数据。
-- (1)显示在1991年2月1日到1991年5月1日之间雇用的雇员名,岗位及雇佣日期,并以雇佣日期进行排序。
SELECT ename, job, hiredate
FROM emp
WHERE hiredate BETWEEN '1991-2-1' AND '1991-5-1' #注意 between and 是闭集
#也可以:WHERE hiredate >= '1991-2-1' AND hiredate <= '1991-5-1'
ORDER BY hiredate;
-- (2)显示获得补助的所有雇员名,工资及补助,并以工资降序排序
SELECT ename, sal, comm
FROM emp
WHERE comm IS NOT NULL
ORDER BY sal DESC;
-- MySQL 作业
-- 6.根据:emp员工表写出正确SQL
-- 1.选择部门30中的所有员工.
SELECT *
FROM emp
WHERE deptno = 30;
-- 2.列出所有办事员(CLERK)的姓名,编号和部门编号.
SELECT ename, empno, deptno, job
FROM emp
WHERE job = 'CLERK';
-- 3.找出佣金高于薪金的员工.
SELECT *
FROM emp
WHERE sal < comm; #最好加一个判断 IFNULL(comm, 0) > sal;
-- 4.找出佣金高于薪金60%的员工.
SELECT *
FROM emp
WHERE IFNULL(comm, 0) >= sal*0.6;
-- 5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
SELECT *
FROM emp
WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK');
-- 6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
SELECT *
FROM emp
WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK')
OR (job != 'MANAGER' AND job != 'CLERK' AND sal >= 2000);
-- 7.找出收取佣金的员工的不同工作.
SELECT job
FROM emp
WHERE comm IS NOT NULL
GROUP BY job;
#上面是我自己的方法,老师讲的是用去重DISTINCT如下:
SELECT DISTINCT job
FROM emp
WHERE comm IS NOT NULL;
-- 8.找出不收取佣金或收取的佣金低于100的员工.
SELECT *
FROM emp
WHERE comm IS NULL OR comm < 100; #注意设计到值可能为null的最好加一个判断条件,如上面3备注
-- 9.找出各月倒数第3天受雇的所有员工.
#方法1:
SELECT *
FROM emp
WHERE SUBSTRING(hiredate, 6, 5) IN
('01-29', '02-26', '03-29', '04-28', '05-29', '06-28', '07-29', '08-29' ,'09-28'
,'10-29' ,'11-28', '12-29');
#方法2:上面是我的方法,下面是别人的方法
SELECT *
FROM emp
WHERE SUBSTRING(DATE_ADD(hiredate, INTERVAL 3 DAY), 9, 2) = '01';
#方法3:
#老韩提示:last_day(日期),可以返回该日期所在月份的最后一天
SELECT LAST_DAY('2011-11-11'); #2011-11-30
SELECT *
FROM emp
WHERE DATE_ADD(hiredate, INTERVAL 2 DAY) = LAST_DAY(hiredate);
-- 10.找出早于12年前受雇的员工.
SELECT *
FROM emp
WHERE DATE_ADD(hiredate, INTERVAL 12 YEAR) < NOW();
-- 11.以首字母小写的方式显示所有员工的姓名.
SELECT CONCAT(LCASE(LEFT(ename, 1)), RIGHT(ename, LENGTH(ename)-1)) AS 'name'
FROM emp;
-- 12.显示正好为5个字符的员工的姓名.
SELECT ename
FROM emp
WHERE LENGTH(ename) = 5;
-- MySQL作业
-- 6.根据:emp员工表写出正确SQL
-- 13.显示不带有"R"的员工的姓名.
SELECT ename
FROM emp
WHERE INSTR(ename, 'R') = 0;
#或者
SELECT ename
FROM emp
WHERE ename NOT LIKE '%R%'; #这里如果两边上是R也不会显示
-- 14.显示所有员工姓名的前三个字符.
SELECT LEFT(ename, 3)
FROM emp;
-- 15.显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE(ename, 'A', 'a')
FROM emp;
-- 16.显示满10年服务年限的员工的姓名和受雇日期.
SELECT ename, hiredate
FROM emp
WHERE DATE_ADD(hiredate, INTERVAL 10 YEAR) < NOW();
-- 17.显示员工的详细资料,按姓名排序.
SELECT *
FROM emp
ORDER BY ename;
-- 18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
SELECT ename, hiredate
FROM emp
ORDER BY hiredate;
-- 19.显示所有员工的姓名、工作和薪金,按工作降序排序,若工作相同则按薪金排序(薪金默认排序).
SELECT ename, job, sal
FROM emp
ORDER BY job DESC, sal;
-- 20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
SELECT ename, CONCAT(YEAR(hiredate), '-', MONTH(hiredate))
FROM emp
ORDER BY MONTH(hiredate), YEAR(hiredate); #先按月排,再按年排
-- 21.显示在一个月为30天的情况所有员工的日薪金,忽略余数(向下取整)。
SELECT ename, FLOOR((sal + IFNULL(comm, 0))/30) AS "日薪金"
FROM emp;
-- 22.找出在(任何年份的)2月受聘的所有员工。
SELECT *
FROM emp
WHERE MONTH(hiredate) = '02';
-- 23.对于每个员工,显示其加入公司的天数.
SELECT ename, DATEDIFF(NOW(), hiredate)
FROM emp;
-- 24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT ename
FROM emp
WHERE INSTR(ename, 'A') != 0;
#或者
SELECT ename
FROM emp
WHERE ename LIKE '%A%';
-- 25.以年月日的方式显示所有员工的服务年限.(大概)
SELECT ename, FLOOR(DATEDIFF(NOW(), hiredate)/365) AS "年",
DATEDIFF(NOW(), hiredate) MOD 12 AS "月", DATEDIFF(NOW(), hiredate) MOD 365 MOD 30 AS "日"
FROM emp
#老韩的方法:(1)先求出一共工作了多少天:select ename, datediff(now(), hiredate) from emp;
#(2)工作年为:FLOOR( DATEDIFF(NOW(), hiredate)/365 )
#(3)工作月为:FLOOR( DATEDIFF(NOW(), hiredate)%365/31 )
#(4)工作天为:FLOOR( DATEDIFF(NOW(), hiredate)%31 )
SELECT ename, FLOOR( DATEDIFF(NOW(), hiredate)/365 ) AS "年",
FLOOR( DATEDIFF(NOW(), hiredate)%365/31 ) AS "月",
FLOOR( DATEDIFF(NOW(), hiredate)%31 ) AS "天"
FROM emp;
-- MySQL作业
-- 7.根据:emp员工表,dept部门表,工资=薪金sal+佣金comm,写出正确SQL
SELECT * FROM emp;
-- (1).列出至少有一个员工的部门
SELECT deptno, COUNT(deptno)
FROM emp
GROUP BY deptno;
#老韩的方法:先查出各个部门有多少人,再使用having子句过滤
SELECT deptno, COUNT(*) AS c
FROM emp
GROUP BY deptno
HAVING c >= 1;
-- (2).列出薪金比“SMITH”多的所有员工。
#我用的多表查询
SELECT sal
FROM emp
WHERE ename = "SMITH";
SELECT ename, emp.sal
FROM emp, (
SELECT sal
FROM emp
WHERE ename = "SMITH"
) temp
WHERE emp.sal > temp.sal;
#老韩用的子查询
SELECT ename, sal
FROM emp
WHERE sal > (
SELECT sal
FROM emp
WHERE ename = "SMITH"
)
-- (3).列出受雇日期晚于其直接上级的所有员工。
#我用的多表查询(有点多余)
SELECT empno, ename, hiredate
FROM emp
SELECT emp.ename AS '员工', emp.hiredate AS '员工入职日期', temp.ename AS '上级', temp.hiredate AS '上级入职时间'
FROM emp, (
SELECT empno, ename, hiredate
FROM emp
) temp
WHERE emp.mgr = temp.empno AND emp.hiredate > temp.hiredate;
#老韩用的自连接(实际上也是多表查询)
#1.先把emp当做两张表
#2.连接条件:1.worker.mgr = leader.empno 2.worker.hiredate > leader.hiredate
SELECT worker.ename AS '员工', worker.hiredate AS '员工入职日期', leader.ename AS '上级', leader.hiredate AS '上级入职时间'
FROM emp worker, emp leader
WHERE worker.mgr = leader.empno AND worker.hiredate > leader.hiredate;
-- (4).列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
#分析:这里因为需要显示所有部门,因此考虑使用外连接
SELECT dname, emp.*
FROM dept LEFT JOIN emp
ON emp.deptno = dept.deptno
ORDER BY dname;
-- (5).列出所有“CLERK” (办事员)的姓名及其部门名称。
SELECT ename, job, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND job = "CLERK";
-- (6).列出最低薪金大于1500的各种工作。
SELECT job, MIN(sal) AS min_sal
FROM emp
GROUP BY job
HAVING min_sal > 1500; #注意group by 的条件关键字是HAVING,不是WHERE
-- (7).列出在部门“SALES”(销售部)工作的员工的姓名。
SELECT ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND dname = 'SALES';
-- (8).列出薪金高于公司平均薪金的所有员工。
#先拿到公司的平均薪水,再使用子查询
SELECT AVG(sal) FROM emp
SELECT ename, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);
-- MySQL作业
-- (9).列出与“SCOTT”从事相同工作的所有员工。
#先查出scott的工作,再用子查询
SELECT *
FROM emp
WHERE job = (
SELECT job
FROM emp
WHERE ename = "SCOTT"
);
-- (10).列出薪金高于所在部门30工作的所有员工的薪金的员工姓名和薪金。
#子查询 + all
SELECT ename, sal
FROM emp
WHERE sal > ALL(
SELECT sal
FROM emp
WHERE deptno = 30
);
-- (11).列出在每个部门(即按照部门分组)工作的员工数量、平均工资和平均服务期限。
SELECT deptno, COUNT(*), AVG(sal) AS '平均工资', AVG(DATEDIFF(NOW(), hiredate)) AS '平均服务天数'
FROM emp
GROUP BY deptno;
-- (12).列出所有员工的姓名、部门名称和工资。
SELECT ename, dname, sal
FROM emp, dept
WHERE emp.deptno = dept.deptno;
-- (13).列出所有部门的详细信息和部门人数。
#先求得各个部门的人数.(有各个部门,那么就是GROUP BY)
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno;
#用外连接(因为有一个部门没有人)
SELECT dept.*, c AS "部门人数"
FROM dept LEFT JOIN (
SELECT deptno, COUNT(*) AS c
FROM emp
GROUP BY deptno
)temp
ON dept.deptno = temp.deptno;
-- (14).列出各种工作(group by)的最低工资。
SELECT job, MIN(sal)
FROM emp
GROUP BY job;
-- (15).列出MANAGER(经理)的最低薪金。
SELECT MIN(sal), job
FROM emp
WHERE job = 'MANAGER';
-- (16).列出所有员工的年工资,按年薪从低到高排序。
SELECT ename, (sal+IFNULL(comm, 0))*12 AS year_sal
FROM emp
ORDER BY year_sal;