Javaweb知识 day04 mysql多表查询&事务

一、多表查询：

1.1 首先准备两张表来用作举例：

# 创建部门表

CREATE TABLE dept(

          id INT PRIMARY KEY AUTO_INCREMENT,

          NAME VARCHAR(20)

);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

# 创建员工表

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 emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);（省略重复sql语句）

1.2 内连接查询：

1.2.1. 隐式内连接：使用where条件消除无用数据

例子：

-- 查询所有员工信息和对应的部门信息

SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 查询员工表的名称，性别。部门表的名称

SELECT

         t1.name, -- 员工表的姓名

         t1.gender,-- 员工表的性别

         t2.name -- 部门表的名称

FROM

          emp t1,

          dept t2

WHERE

          t1.`dept_id` = t2.`id`;

1.2.2. 显式内连接：

语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件

例如：SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;     

1.2.3 注意事项：

如果不加条件筛选，那么多表查询的结果就是笛卡尔积：有两个集合A,B，取这两个集合的所有组成情况。

要完成多表查询，需要消除无用的数据

1.3 外连接查询：

1.3.1 左外连接：

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；

查询的是左表所有数据以及其交集部分。

例子：

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称

SELECT  t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

1.3.2 右外连接：

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；

 查询的是右表所有数据以及其交集部分。

例子：

SELECT  * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

1.4 子查询：

1.4.1 概念：查询中嵌套查询，称嵌套查询为子查询。

1.4.2 举例：查询工资最高的员工信息

传统方式查询：

1.查询最高的工资是多少：SELECT MAX(salary) FROM emp;

2.查询员工信息，并且工资等于最高工资的：

SELECT * FROM emp WHERE emp.`salary` = 9000;

子查询：

SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

1.4.3 子查询不同情况

1. 子查询的结果是单行单列的：

子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

举例：查询员工工资小于平均工资的人

SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

2. 子查询的结果是多行单列的：

子查询可以作为条件，使用运算符in来判断

举例：查询'财务部'和'市场部'所有的员工信息

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

3. 子查询的结果是多行多列的：

子查询可以作为一张虚拟表参与查询

举例：查询员工入职日期是2011-11-11日之后的员工信息和部门信息

SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;


1.5 多表查询练习

1.5.1 准备sql表数据：

#创建部门表

CREATE TABLE dept (

        id INT PRIMARY KEY PRIMARY KEY, -- 部门id

        dname VARCHAR(50), -- 部门名称

        loc VARCHAR(50) -- 部门所在地

);

#给部门表添加4个部门

INSERT INTO dept(id,dname,loc) VALUES

(10,'教研部','北京'),    （删除重复语句，方便阅读）

 #创建职务表

CREATE TABLE job (

          id INT PRIMARY KEY,

          jname VARCHAR(20),    //职务名称

         description VARCHAR(50) //职务描述

);

#给职务表添加4个职务

INSERT INTO job (id, jname, description) VALUES

(1, '董事长', '管理整个公司，接单'),（删除重复语句，方便阅读）

#创建员工表

CREATE TABLE emp (

         id INT PRIMARY KEY, -- 员工id

         ename VARCHAR(50), -- 员工姓名

         job_id INT, -- 职务id

         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),    -- 职务id添加外键链接职务表

         CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)        --部门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),

                      （删除重复语句，方便阅读）


#创建工资等级表

CREATE TABLE salarygrade (

           grade INT PRIMARY KEY,   -- 级别

           losalary INT,  -- 最低工资

           hisalary INT -- 最高工资

);

#给工资等级表添加5个工资等级

INSERT INTO salarygrade(grade,losalary,hisalary) VALUES

                        (1,7000,12000),（删除重复语句）

1.5.2 需求：

1.查询所有员工信息：查询员工编号，员工姓名，工资，职务名称，职务描述

分析：

员工编号，员工姓名，工资，需要查询emp表  职务名称，职务描述 需要查询job表

查询条件 emp.job_id = job.id

sql实现：

SELECT

           t1.`id`, -- 员工编号

           t1.`ename`, -- 员工姓名

           t1.`salary`,-- 工资

           t2.`jname`, -- 职务名称

           t2.`description` -- 职务描述

FROM

           emp t1, job t2

WHERE

           t1.`job_id` = t2.`id`;


2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

分析：

员工编号，员工姓名，工资在emp表，职务名称，职务描述在job表，部门名称，部门位置在 dept表

条件：emp.job_id = job.id and emp.dept_id = dept.id

sql实现：

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`;


3.查询员工姓名，工资，工资等级

分析：

员工姓名，工资在emp表，工资等级在salarygrade表

条件：emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary

sql实现：

SELECT

          t1.ename ,

          t1.`salary`,

          t2.*

FROM emp t1, salarygrade t2

WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;


4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

分析：

员工姓名，工资在emp表，职务名称，职务描述在job表，部门名称，部门位置在dept表，工资等级在salarygrade表

条件： emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary

sql实现：

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`;


5.查询出部门编号、部门名称、部门位置、部门人数

分析：

部门编号、部门名称、部门位置在dept 表，部门人数需要统计

使用分组查询，按照emp.dept_id完成分组，查询count(id)

使用子查询将第2步的查询结果和dept表进行关联查询

sql实现：

SELECT

           t1.`id`,

           t1.`dname`,

           t1.`loc` , 

           t2.total

FROM

           dept t1,

           (SELECT

                   dept_id,COUNT(id) total

             FROM

                   emp

             GROUP BY dept_id) t2

WHERE t1.`id` = t2.dept_id;


6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询

分析：

姓名在emp表，直接上级的姓名也在emp表，emp表的id和mgr是自关联

条件 emp.id = emp.mgr

查询左表的所有数据，和交集数据，使用左外连接查询

sql实现：

SELECT

          t1.ename,

          t1.mgr,

          t2.`id`,

          t2.`ename`

FROM emp t1

LEFT JOIN emp t2

ON t1.`mgr` = t2.`id`;

二、事务

2.1 事务的基本介绍

2.1.1 概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

2.1.2 操作：

1. 开启事务： start transaction;

2. 回滚：rollback;

3. 提交：commit;

2.1.3 例子：

#创建表

CREATE TABLE account (

             id INT PRIMARY KEY AUTO_INCREMENT,

             NAME VARCHAR(10),

             balance DOUBLE

);

#添加数据

INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);

#张三给李四转账 500 元

#0. 开启事务

START TRANSACTION;

#1. 张三账户 -500

UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';

#2. 李四账户 +500

UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';


#发现执行没有问题，提交事务

COMMIT;

#发现出问题了，回滚事务

ROLLBACK;


2.1.4 事务提交的两种方式：

1.自动提交：

mysql就是自动提交的

一条DML(增删改)语句会自动提交一次事务。

2.手动提交：

Oracle 数据库默认是手动提交事务

需要先开启事务，再提交

3.修改事务的默认提交方式：

查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交

修改默认提交方式： set @@autocommit = 0;

2.1.5 事务的四大特征：

        1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。

        2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。

        3. 隔离性：多个事务之间。相互独立。

        4. 一致性：事务操作前后，数据总量不变

2.1.6 事务的隔离级别（了解）

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题：

1. 脏读：一个事务，读取到另一个事务中没有提交的数据

2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。

3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：

1. read uncommitted：读未提交

产生的问题：脏读、不可重复读、幻读

2. read committed：读已提交 （Oracle）

产生的问题：不可重复读、幻读

3. repeatable read：可重复读 （MySQL默认）

产生的问题：幻读

4. serializable：串行化

可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别：select @@tx_isolation;

数据库设置隔离级别：

set global transaction isolation level  级别字符串;

三、DCL操作：

3.1 重温SQL分类：

1. DDL：操作数据库和表

2. DML：增删改表中数据

3. DQL：查询表中数据

4. DCL：管理用户，授权

3.2 管理用户

1. 添加用户：

语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

2. 删除用户：

语法：DROP USER '用户名'@'主机名';

3. 修改用户密码：

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';

UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

3.3 mysql中忘记了root用户的密码？

1. cmd -- > net stop mysql 停止mysql服务

需要管理员运行该cmd

2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables

3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功

4. use mysql;

5. update user set password = password('你的新密码') where user = 'root';

6. 关闭两个窗口

7. 打开任务管理器，手动结束mysqld.exe 的进程

8. 启动mysql服务

9. 使用新密码登录。

3.4 查询用户：

1. 切换到mysql数据库

USE myql;

2. 查询user表

SELECT * FROM USER;

通配符： % 表示可以在任意主机使用用户登录数据库


3.5 权限管理：

1. 查询权限：

SHOW GRANTS FOR '用户名'@'主机名';

SHOW GRANTS FOR 'lisi'@'%';

2. 授予权限：

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

给张三用户授予所有权限，在任意数据库任意表上

GRANT ALL ON *.* TO 'zhangsan'@'localhost';

3. 撤销权限：

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,324评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,303评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,192评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,555评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,569评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,566评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,927评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,583评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,827评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,590评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,669评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,365评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,941评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,928评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,159评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,880评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,399评论 2赞 342

推荐阅读更多精彩内容

  • 2019-11-28 173
    16宿命：用概率思维提高你的胜算 以前的我是风险厌恶者，不喜欢去冒险，但是人生放弃了冒险，也就放弃了无数的可能。 ...
    yichen大刀阅读 6,030评论 0赞 4
  • 2019-11-28三数灯谜分析
    公元：2019年11月28日19时42分农历：二零一九年 十一月 初三日 戌时干支：己亥乙亥己巳甲戌当月节气：立冬...
    石放阅读 6,870评论 0赞 2
  • 断舍离第四天2019.11.28
    今天上午陪老妈看病，下午健身房跑步，晚上想想今天还没有断舍离，马上做，衣架和旁边的的布衣架，一看乱乱，又想想自己是...
    影子3623253阅读 2,903评论 1赞 8