数据库
1、多表查询
数据表创建
CREATE TABLE DEPT(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
CREATE TABLE EMP(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
GENDER CHAR(1),
SALARY DOUBLE,
JOIN_DATE DATE,
DEPT_ID INT,
FOREIGN KEY (DEPT_ID) REFERENCES DEPT(ID)
);
INSERT INTO DEPT(ID,NAME) VALUES(1,‘研发部’);
INSERT INTO DEPT(ID,NAME) VALUES(2,‘市场部’);
INSERT INTO DEPT(ID,NAME) VALUES(3,‘财务部’);
INSERT INTO EMP(NAME,GENDER,SALARY,JOIN_DATE,DEPT_ID)
VALUES(‘孙悟 空’,‘男’,7200,‘2013-02-24’,1);
INSERT INTO EMP(NAME,GENDER,SALARY,JOIN_DATE,DEPT_ID)
VALUES(‘猪八戒’,‘男’,3600,‘2010-12-02’,2);
INSERT INTO EMP(NAME,GENDER,SALARY,JOIN_DATE,DEPT_ID)
VALUES(‘唐僧’,‘男’,9000,‘2008-08-08’,2);
INSERT INTO EMP(NAME,GENDER,SALARY,JOIN_DATE,DEPT_ID)
VALUES(‘白骨精’,‘女’,5000,‘2015-10-07’,3);
INSERT INTO EMP(NAME,GENDER,SALARY,JOIN_DATE,DEPT_ID)
VALUES(‘蜘蛛精’,‘女’,4500,‘2013-03-14’,1);
数据表展示
DEPT
ID NAME
1 研发部
2 市场部
3 财务部
EMP
ID NAME GENDER SALARY JOIN_DATE DEPT_ID
1 孙悟空 男 7200 2013-02-24 1
2 猪八戒 男 3600 2010-12-02 2
3 唐僧 男 9000 2008-08-08 2
4 白骨精 女 5000 2015-10-07 3
5 蜘蛛精 女 4500 2013-03-14 1
多表查询
SELECT * FROM 表1,表2;
SELECT * FROM EMP,DEPT;
查询结果称为笛卡尔积,即两个集合元素的全部组合情况,两表数据记录的所有组合情况,结果中可能会有无用的数据
多表查询的分类(消除无用数据的方法):
内连接查询
隐式内连接:使用where条件消除无用数据
SELECT * FROM EMP,DEPT WHERE EMP.ID = DEPT.ID;
查询其中某些数据的规范格式 方便加注释
SELECT
t1.NAME,
t1.GENDER,
t2.NAME
FROM
EMP t1,
DEPT t2
WHERE
t1.ID=t2.ID;
显式内连接:
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
SELECT * FROM EMP INNER JOIN DEPT ON EMP.ID = DEPT.ID;
SELECT * FROM EMP JOIN DEPT ON EMP.ID = DEPT.ID;
需要确定:
查询哪些表
条件是什么
查询哪些字段
外连接查询
左外连接:查询左表所有信息及交集部分
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
查询所有员工信息,如果员工有部门则查询部门名称,否则不显示
SELECT t1.*,t2.NAME FROM EMP t1 LEFT JOIN DEPT t2 ON t1.ID=t2.ID;
右外连接:查询右表所有信息及交集部分
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
子查询
查询中嵌套查询,称嵌套查询为子查询
查询工资最高的员工信息 SELECT * FROM EMP WHERE SALARY=(SELECT MAX(SALARY) FROM EMP);
子查询分类:
结果为单行单列
结果可以作为条件,使用运算符判断 > >= = <= <
查询工资小于平均工资的员工
SELECT * FROM EMP WHERE SALARY<(SELECT AVG(SALARY) FROM EMP);
结果为多行单列
结果可以作为条件,使用运算符IN判断
查询财务部所有的员工信息
SELECT *
FROM EMP
WHERE DEPT_ID=
(SELECT ID FROM DEPT WHERE NAME=‘财务部’ OR NAME=‘市场部’);
查询财务部和市场部所有的员工信息
SELECT *
FROM EMP
WHERE DEPT_ID IN
(SELECT ID FROM DEPT WHERE NAME=‘财务部’ OR NAME=‘市场部’);
SELECT *
FROM EMP
WHERE DEPT_ID IN
(SELECT ID FROM DEPT WHERE NAME IN (‘市场部’,‘财务部’));
结果为多行多列
结果可以作为一张虚拟表,与其它表连接进行多表查询
查询入职在2011-11-11之后的员工信息和部门信息 日期可以使用字符串直接比较
子查询
SELECT *
FROM DEPT t1 JOIN (SELECT * FROM EMP WHERE JOIN_DATE > ‘2011-11-11’) t2
ON t1.ID = t2.DEPT_ID;
普通内连接
2、多表查询练习
创建部门表
CREATE TABLE DEPT(
ID INT PRIMARY KEY,
DNAME VARCHAR(50),
LOC VARCHAR(50)
);
INSERT INTO DEPT(ID,DNAME,LOC) VALUES(10,‘教研部’,‘北京’),
(20,‘学工部’,‘上海’),
(30,‘销售部’,‘广州’),
(40,‘财务部’,‘深圳’);
创建职位表
CREATE TABLE JOB(
ID INT PRIMARY KEY,
JNAME VARCHAR(20),
DESCRIPTION VARCHAR(50)
);
INSERT INTO JOB(ID,JNAME,DESCRIPTION) VALUES(1,‘董事长’,‘管理整个公司,接单’),
(2,‘经理’,‘管理部门员工’),
(3,‘销售员’,‘向客人推销产品’),
(4,‘文员’,‘使用办公软件’);
创建员工表
CREATE TABLE EMP(
ID INT PRIMARY KEY,
ENAME VARCHAR(20),
JOB_ID INT,
MGR INT,
JOINDATE DATE,
SALARY DECIMAL(7,2),
BONUS DECIMAL(7,2),
DEPT_ID INT,
CONSTRAINT EMP_JOBID_REF_JOB_ID_FK FOREIGN KEY (JOB_ID)
REFERENCES JOB(ID),
CONSTRAINT EMP_DEPTID_REF_DEPT_ID_FK FOREIGN KEY (DEPT_ID)
REFERENCES DEPT(ID)
);
INSERT INTO EMP(ID,ENAME,JOB_ID,MGR,JOINDATE,SALARY,BONUS,DEPT_ID) VALUES
(1001,‘孙悟空’,4,1004,‘2000-12-17’,‘8000.00’,NULL,20),
(1002,‘卢俊义’,3,1004,‘2001-02-20’,‘10000.00’,‘3000.00’,30),
(1003,‘林冲’,3,1004,‘2001-02-22’,‘12500.00’,‘5000.00’,30),
(1004,‘唐僧’,3,1004,‘2001-04-03’,‘29760.00’,NULL,20),
(1005,‘李逵’,4,1004,‘2001-09-28’,‘12500.00’,‘14000.00’,30),
(1006,‘宋江’,2,1004,‘2001-05-01’,‘25800.00’,NULL,10),
(1007,‘刘备’,2,1004,‘2001-09-01’,‘24580.00’,NULL,10),
(1008,‘猪八戒’,4,1004,‘2007-04-19’,‘50000.00’,NULL,30),
(1009,‘罗贯中’,1,1004,‘2001-11-17’,‘85000.00’,NULL,10),
(1010,‘吴用’,1,1004,‘2001-09-08’,‘15000.00’,‘0.00’,10),
(1011,‘沙僧’,4,1004,‘2000-12-17’,‘11000.00’,NULL,30),
(1012,‘小白龙’,4,1004,‘2007-05-01’,‘9500.00’,NULL,10),
(1013,‘小白龙’,4,1004,‘2001-12-03’,‘30000.00’,NULL,20),
(1014,‘关羽’,4,1004,‘2002-01-23’,‘13000.00’,NULL,10);
创建工资等级表
CREATE TABLE SALARYGRADE(
GRADE INT PRIMARY KEY,
LOSALARY INT,
HISALARY INT
);
INSERT INTO SALARYGRADE(GRADE,LOSALARY,HISALARY) VALUES
(1,7000,12000),
(2,12010,24000),
(3,24010,30000),
(4,30010,50000),
(5,50010,70000);
数据表展示
DEPT部门表
ID DNAME LOC
10 教研部 北京
20 学工部 上海
30 销售部 广州
40 财务部 深圳
JOB职位表
ID JNAME DESCRIPTION
1 董事长 管理整个公司,接单
2 经理 管理部门员工
3 销售员 向客人推销产品
4 文员 使用办公软件
EMP员工表
ID ENAME JOB_ID MGR JOINDATE SALARY BONUS DEPT_ID
1001 孙悟空 4 1004 2000-12-17 8000.00 NULL 20
1002 卢俊义 3 1006 2001-02-20 10000.00 3000.00 30
1003 林冲 3 1006 2001-02-22 12500.00 5000.00 30
1004 唐僧 3 1009 2001-04-03 29760.00 NULL 20
1005 李逵 4 1007 2001-09-28 12500.00 14000.00 30
1006 宋江 2 1009 2001-05-01 25800.00 NULL 10
1007 刘备 2 1009 2001-09-01 24580.00 NULL 10
1008 猪八戒 4 1004 2007-04-19 50000.00 NULL 30
1009 罗贯中 1 NULL 2001-11-17 85000.00 NULL 10
1010 吴用 1 1006 2001-09-08 15000.00 0.00 10
1011 沙僧 4 1004 2000-12-17 11000.00 NULL 30
1012 李逵 4 1006 2001-12-03 9500.00 NULL 10
1013 小白龙 4 1004 2001-12-03 30000.00 NULL 20
1014 关羽 4 1004 2002-01-23 13000.00 NULL 10
SALARYGRADE工资等级表
GRADE LOSALARY HISALARY
1 7000 12000
2 12010 24000
3 24010 30000
4 30010 50000
5 50010 70000
需求:
查询所有员工信息,员工编号 姓名 工资 职位名称 职位描述
SELECT
t1.ID, – 员工编号
t1.ENAME, – 员工姓名
t1.SALARY, – 员工工资
t2.JNAME, – 职位名称
t2.DESCRIPTION – 职位描述
FROM
EMP t1, – 员工表
JOB t2 – 职位表
WHERE
t1.JOB_ID=t2.ID;
查询所有员工信息,员工编号 姓名 工资 职位名称 职位描述 部门名称 部门位置
SELECT
t1.ID, – 员工编号
t1.ENAME, – 员工姓名
t1.SALARY, – 员工工资
t2.JNAME, – 职位名称
t2.DESCRIPTION, – 职位描述
t3.DNAME, – 部门名称
t3.LOC – 部门位置
FROM
EMP t1, – 员工表
JOB t2, – 职位表
DEPT t3 – 部门表
WHERE
t1.JOB_ID=t2.ID AND t1.DEPT_ID=t3.ID;
查询员工姓名 工资 工资等级
SELECT
t1.ENAME, – 员工编号
t1.SALARY, – 工资
t2.GRADE – 工资等级
FROM
EMP t1, – 员工表
SALARYGRADE t2 – 工资等级表
WHERE
t1.SALARY > t2.LOSALARY AND t1.SALARY < t2.HISALARY;
或
SELECT
t1.ENAME, – 员工编号
t1.SALARY, – 工资
t2.GRADE – 工资等级
FROM
EMP t1, – 员工表
SALARYGRADE t2 – 工资等级表
WHERE
t1.SALARY BETWEEN t2.LOSALARY AND t2.HISALARY;
查询员工姓名 工资 职位名称 职位描述 部门名称 部门位置 工资等级
SELECT
t1.ENAME, – 员工姓名
t1.SALARY, – 工资
t2.JNAME, – 职位名称
t2.DESCRIPTION, – 职位描述
t3.DNAME, – 部门名称
t3.LOC, – 部门位置
t4.GRADE – 工资等级
FROM
EMP t1, – 员工表
JOB t2, – 职位表
DEPT t3, – 部门表
SALARYGRADE t4 – 工资等级表
WHERE
t1.JOB_ID=t2.ID AND t1.DEPT_ID=t3.ID AND
t1.SALARY BETWEEN t4.LOSALARY AND t4.HISALARY;
查询部门编号 名称 位置 人数
SELECT
t1.ID, – 部门编号
t1.DNAME, – 部门姓名
t1.LOC, – 部门位置
t2.total – 部门人数
FROM
DEPT t1, – 部门表
(SELECT
COUNT(ID) total, – 部门人数
DEPT_ID – 部门编号
FROM
EMP
GROUP BY DEPT_ID) t2
WHERE
t1.ID=t2.DEPT_ID;
查询所有员工的姓名以及直接上级的姓名 没有领导的员工也需要查询
自关联映射 需要插叙左表的所有数据和两张表的交集数据
SELECT
t1.ENAME, – 员工姓名
t1.MGR, – 上级编号
t2.ID, – 上级编号
t2.ENAME – 上级姓名
FROM
EMP t1
LEFT JOIN
EMP t2
ON
t1.MGR=t2.ID;
3、数据库事务
如果一个包含多个步骤的业务操作被事务管理,那么这些步骤要么同时成功,要么同时失败
使用事务的操作
开启事务 start transaction;
回滚 rollback;
提交 commit;
转账例子
张三给李四转账500
张三账户-500
李四账户+500
创建帐户表,并插入数据
CREATE TABLE ACCOUNT(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
BALANCE DOUBLE
);
INSERT INTO ACCOUNT(NAME,BALANCE) VALUES(‘张三’,1000),(‘李四’,1000);
ACCOUNT账户表
ID NAME BALANCE
1 张三 1000
2 李四 1000
张三账户-500
UPDATE ACCOUNT SET BALANCE=BALANCE-500 WHERE NAME=‘张三’;
李四账户+500
UPDATE ACCOUNT SET BALANCE=BALANCE+500 WHERE NAME=‘李四’;
此时的转账的成功的,但如果在张三-500之后代码出错,发生了异常,那么就会出现张三账户-500而李四账户没有+500的情况
使用事务
开启事务
START TRANSACTION;
张三账户-500
UPDATE ACCOUNT SET BALANCE=BALANCE-500 WHERE NAME=‘张三’;
李四账户+500
UPDATE ACCOUNT SET BALANCE=BALANCE+500 WHERE NAME=‘李四’;
此时若中间出现异常,查看数据,会出现临时数据,用于我们查看是否有问题,但原始数据不会被修改,关闭窗口后再查询还是原始数据
SELECT * FROM ACCOUNT;
若发现临时数据有问题,回滚事务,将数据回滚到开启事务之前的状态
ROLLBACK;
若发现临时数据没有问题,提交事务
COMMIT;
事务的自动提交
MySQL中事务默认自动提交,DML(增删改)语句会自动提交事务,一条DML语句自动提交一次事务
手动提交需要先开启事务,手动开启了事务,但没有提交,事务会自动回滚
修改事务的默认提交方式
查看事务的默认提交方式
SELECT @@autocommit; 1自动提交 0手动提交
修改事务的默认提交方式
SET @@autocommit=0;
修改为手动提交之后再执行DML语句不会自动提交,也就是DML语句不会生效,不会改变原始数据
Oracle是手动提交事务的
4、事务的特征和隔离级别
事务的四大特征
原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
持久性:当事务提交或回滚后,数据库会持久化地保存数据
隔离性:多个事务之间相互独立
一致性:事务操作前后,数据总量不变
事务的隔离级别(了解)
多个事务之间是隔离的,相互独立的,但如果多个事务同时操作一批数据,则会引发一些问题,设置不同的隔离级别可以解决这些问题
引发的问题:
脏读:一个事务,读取到另一个事务中没有提交的数据
不可重复读(虚读):在同一事务中,两次读取到的数据不一样
幻读:一个事务操作(DML)数据库中所有记录,另一个事务增加了一条数据,
则第一个事务查询不到自己的修改
该问题在MySQL数据库中无法演示
隔离级别
read uncommitted 读未提交
问题:脏读 不可重复读(虚读)幻读
read committed 读已提交(Oracle默认)
问题:不可重复读(虚读)
repeatable read 可重复读(MySQL默认)
问题:幻读
serializable 串行化
本质上是一个锁表操作 一个事务在操作一张数据表时,另一个事务无法操作这张表
可以解决上述所有问题
隔离级别从小到大,安全性越来越高,效率越来越低
一般情况下使用默认隔离级别,只有在特殊需求下才会改变级别
数据库查询隔离级别
select @@tx_isolation;
数据库设置隔离级别
set global transaction isolation level 级别字符串;
设置之后要重新打开连接才会生效
不同隔离级别的演示
转账前account表
ID NAME BALANCE
1 张三 1000
2 李四 1000
(1)
打开一个cmd,登录mysql,设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;
再打开一个cmd就可以查询当前的隔离级别已经改变
开启事务(两个cmd都开启事务)
start transaction;
第一个cmd执行转账操作
update account set balance = balance - 500 where name = “张三”;
update account set balance = balance + 500 where name = “李四”;
第二个cmd查询
select * from account;
此时第一个cmd并没有提交事务,但第二个cmd查询到的数据是转账后的数据,即脏数据
此时若第一个cmd回滚事务,那么数据还是会回到转账之前的样子,转账并没有生效
rollback;
然后第二个cmd再查询
select * from account;
查询到的数据是转账前的数据
发生了脏读,同时也发生了不可重复读,因为在同一个事务中,第二个cmd两次查询到的数据是不一样的
(2)
第一个cmd设置隔离级别为read committed
set global transaction isolation level read committed;
将两个账户的余额设置为1000
update account set balance = 1000;数据会改变,因为在没有开启事务的情况下,DML会自动提交事务
关闭第二个cmd,重新打开,此时可以查询当前的隔离级别已经改变
开启事务(两个cmd都开启事务)
start transaction;
第一个cmd执行转账操作
update account set balance = balance - 500 where name = “张三”;
update account set balance = balance + 500 where name = “李四”;
第二个cmd查询
select * from account;
此时第一个cmd并没有提交事务,第二个cmd查询到的还是转账之前的数据
只有当第一个cmd提交事务之后,第二个cmd查询到的才是转账之后的数据
第一个cmd提交事务
commit;
第二个cmd查询数据
select * from account;
此时查询到的数据是转账之后的数据
避免了脏读,但发生了不可重复读,在同一个事务中第二个cmd两次查询到的数据不一样
(3)
第一个cmd设置隔离级别为repeatable read
set global transaction isolation level repeatable read;
将两个账户的余额设置为1000
update account set balance = 1000;
关闭第二个cmd,重新打开,此时可以查询当前的隔离级别已经改变
开启事务(两个cmd都开启事务)
start transaction;
第一个cmd执行转账操作
update account set balance = balance - 500 where name = “张三”;
update account set balance = balance + 500 where name = “李四”;
第二个cmd查询
select * from account;
此时第一个cmd并没有提交事务,第二个cmd查询到的还是转账之前的数据
只有当第一个cmd和第二个cmd都提交了事务,才会查询到转账后的数据
第一个cmd提交事务
commit;
第二个cmd查询
select * from account;
此时第二个cmd还没提交事务,查询到的还是转账前的数据
第二个cmd提交事务
commit;
第二个cmd查询
select * from account;
此时第一个cmd和第二个cmd都提交了事务,查询到的是转账后的数据
(4)
第一个cmd设置隔离级别为serializable
set global transaction isolation level serializable;
关闭第二个cmd,重新打开,此时可以查询当前的隔离级别已经改变
开启事务(两个cmd都开启事务)
start transaction;
第一个cmd执行转账操作
update account set balance = balance - 500 where name = “张三”;
update account set balance = balance + 500 where name = “李四”;
第二个cmd查询
select * from account;
此时查询语句下一行会闪烁光标,查询语句并没有执行,只有当第一个cmd的事务结束之后,第二个cmd才能完成查询的操作
4、DCL
管理用户 授权 安全访问
了解,这些操作主要是DBA(数据库管理员)来写
管理用户
查询用户
切换到mysql数据库 USE mysql;
查询USER表数据 SELECT * FROM USER;
会查询到两个root用户,一个Host为localhost,为可以在本机登录的root用户
一个Host为%(通配符),表示可以在任意主机登录的root用户
创建用户
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
CREATE USER ‘zhangsan’@‘localhost’ IDENTIFIED BY ‘123’;
CREATE USER ‘lisi’@’%’ IDENTIFIED BY ‘123’;
删除用户
DROP USER ‘用户名’@‘主机名’;
DROP USER ‘zhangsan’@‘localhost’;
修改密码
UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;
UPDATE USER SET PASSWORD = PASSWORD(‘abc’) WHERE USER = ‘lisi’;
SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);DCL特有方式
SET PASSWORD FOR ‘lisi’@’%’ = PASSWORD(‘aaa’);
忘记root用户密码
在cmd中停止MySQL服务 net stop mysql 需要管理员权限
使用无验证方式启动MySQL服务 mysqld --skip-grant-tables
打开一个新cmd,直接输入mysql,不需要用户名密码就可以登录
使用mysql数据库
USE mysql;
更改root用户密码
UPDATE USER SET PASSWORD = PASSWORD(‘新密码’)
WHERE USER = ‘root’;
关闭两个cmd
在任务管理器中结束mysqld.exe的进程
在cmd中开启MySQL服务 net start mysql 需要管理员权限
使用新密码登录
管理权限
查看权限
SHOW GRANTS FOR ‘用户名’@‘主机名’;
SHOW GRANTS FOR ‘lisi’@’%’;
USAGE 登录权限
root有所有权限
授予权限
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;
GRANT SELECT,UPDATE,DELETE ON db3.account TO ‘lisi’@’%’;
授权后
lisi可以使用db3数据库USE db3;
查询所有表SHOW TABLES;只能看到account表
授予所有权限在任意数据库任意表上
GRANT ALL ON . TO ‘用户名’@‘主机名’;
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;
REVOKE UPDATE,DELETE ON db3.account FROM ‘lisi’@’%’;