【从零开始学习MySql数据库】(3)函数与子查询和连接查询

#查询语句的基本格式
#select 字段 1 , 字段 2 , 字段 3 , 表达式 , 函数 , ...
#from 表名
#where 条件
#group by 列名
#having 带组函数的条件
#order by 列名

#字符函数：upper / lower / initcap/length / lpad / rpad / replace / trim * -- l 表示
#left ; r 表示 right
#1)  upper  转换为大写
#2)  lower  转换为小写
#3)  initcap  转换为首字母大写
#4)  length  取长度
#5)  lpad 左补丁
#6)  rpad  右补丁
#7)  replace 字符替换
#8)  trim 去除前后的空格

#将 ename 字段设置为 10 个长度 , 如果丌够左边用“*”号补齐
SELECT LPAD(name,10,'*') FROM emp_test;

#将 ename 字段设置为 10 个长度 , 如果丌够右边用“#”号补齐
SELECT RPAD(name,10,'*') FROM emp_test;

#求 salary 对 5000 取模
SELECT salary,MOD(salary,5000) FROM emp_test;

#将 amy 的入职日期提前 2 个月
SELECT name,hire_date FROM emp_test WHERE name='amy';
UPDATE emp_test SET hire_date=ADDDATE(hire_date,INTERVAL -2 MONTH) WHERE name='amy';
SELECT name,hire_date FROM emp_test WHERE name='amy';

#这个月的最后一天是多少号？
SELECT LAST_DAY(NOW()) FROM DUAL;

#谁的薪水比张无忌高？
SELECT name, salary FROM emp_test WHERE salary>(SELECT salary FROM emp_test WHERE name='张无忌');

#研发部有哪些职位？
SELECT DISTINCT job FROM emp_test WHERE dept_test_id=(SELECT dept_test_id FROM dept_test WHERE dept_name='developer');

-- 准备数据：在 emp_xxx 中再揑入一个叫“张无忌”的人
insert into emp_test(emp_id , name , salary)
values(1014 , '张无忌' , 8000) ;

#查询谁的薪水比所有叫张无忌的薪水都高？ --大于最大值( 如果子查询得到的结果是多个 )
SELECT name, salary FROM emp_test WHERE salary>ALL(SELECT salary FROM emp_test WHERE name='张无忌');

#哪些人的薪水比仸何一个叫张无忌的员工工资高？ --大于最小值
SELECT name, salary FROM emp_test WHERE salary>ANY(SELECT salary FROM emp_test WHERE name='张无忌');

# 谁和刘苍松同部门？列出除了刘苍松之外的员工名字
SELECT name FROM emp_test WHERE dept_test_id=(SELECT dept_test_id FROM emp_test WHERE name='刘苍松') AND  name <>'刘苍松';

-- 数据准备：再添加 1 个“刘苍松”同学 , 部门号为 20
insert into emp_test(emp_id , name , dept_test_id)
values(1015 , '刘苍松' , 20) ;

-- 谁和刘苍松同部门？列出除了刘苍松之外的员工名字( 如果子查询得到的结果是多个 )
SELECT name FROM emp_test WHERE dept_test_id IN(SELECT dept_test_id FROM emp_test WHERE name='刘苍松') AND  name <>'刘苍松';

-- 谁是张无忌的下属？ 如果只有一个叫张无忌的员工 , 则无问题 , 如果有多个 , 需要用 in
SELECT name FROM emp_test WHERE manager IN(SELECT  emp_id FROM emp_test WHERE name='张无忌');

-- 每个部门拿最高薪水的是谁？
SELECT  dept_test_id ,salary FROM emp_test WHERE (dept_test_id,salary) IN (SELECT dept_test_id,MAX(salary) FROM emp_test GROUP BY dept_test_id);

-- 哪个部门的人数比部门 30 的人数多？
SELECT dept_test_id,COUNT(*) 
             FROM emp_test 
             GROUP BY dept_test_id
             HAVING COUNT(*) >
                            (SELECT COUNT(*) FROM emp_test WHERE dept_test_id=30);

-- 哪个部门的平均薪水比部门 20 的平均薪水高？
SELECT dept_test_id,COUNT(*), AVG(salary)
             FROM emp_test 
             GROUP BY dept_test_id
             HAVING AVG(salary) >
                            (SELECT AVG(salary) FROM emp_test WHERE dept_test_id=30);

-- 列出员工名字和职位 , 这些员工所在的部门平均薪水大于 5000 元
SELECT name,job,AVG(salary)
             FROM emp_test 
             GROUP BY dept_test_id 
             HAVING  AVG(salary)>5000;


-- 哪些员工的薪水比公司的平均薪水低？
SELECT name,salary FROM emp_test WHERE salary<(SELECT AVG(salary) FROM emp_test);

-- 哪些员工的薪水比本部门的平均薪水低？丌再和整个公司的平均薪水比较。
SELECT name,salary FROM emp_test WHERE salary<(SELECT AVG(salary) FROM emp_test WHERE dept_test_id=emp_test.dept_test_id);

-- 哪些人是其他人的经理？( 查找有下属的员工 )
SELECT name FROM emp_test a  WHERE EXISTS(SELECT manager FROM emp_test WHERE manager=a.emp_id);
SELECT name FROM emp_test   WHERE emp_id IN (SELECT DISTINCT manager FROM emp_test);

-- 哪些人丌是别人的经理？
SELECT name FROM emp_test a  WHERE NOT EXISTS(SELECT manager FROM emp_test WHERE manager=a.emp_id);
SELECT name FROM emp_test   WHERE emp_id  NOT IN (SELECT DISTINCT manager FROM emp_test WHERE manager IS NOT NULL );

-- 哪些部门没有员工？
SELECT * 
    FROM dept_test a
    WHERE NOT EXISTS (SELECT 1 
                                         FROM emp_test
                                         WHERE dept_test_id=a.dept_id);

-- 数据库中的查询语句的结果集( ResultSet )：集合 A 和集合 B
-- 集合 A： {1,2,3,4,5}
-- 集合 B： {1,3,5,7,9}
-- A 不 B 的合集：  {1,2,3,4,5,7,9}
-- A 不 B 的交集：  {1,3,5}
-- A 不 B 的差集：  A-B {2,4}

-- 结果集操作
-- 1)  两个结果集必须结构相同
-- 当列的个数、列的顺序、列的数据类型一致时 , 我们称这两个结果集结构相同
-- 只有结构相同的结果集才能做集合操作 
-- 2)  合集 union 和 union all
-- union 和 union all 的区别
-- union 去掉重复记录 , union all 丌去重
-- union 排序 , union all 丌排序
-- 在满足功能的前提下 , 优选 union all
-- 3)  交集 intersect
-- 4)  差集 minus( 两个集合做减法 )

select name , salary from emp_test
where dept_test_id= 10
union
select name , salary from emp_test
where salary > 6000 


select name , salary from emp_test
where dept_test_id= 10
union all
select name , salary from emp_test
where salary > 6000 

--  主键( PK )和外键( FK )
--  1)  主键( Primary key,简称 PK )  --主键要求丌重复 , 丌能是空值
--     dept_xxx 表的主键： 部门编码( deptno )
--     emp_xxx 的主键： 职员编码( empno )
--  2)  外键( Foreign key,简称 FK ) --外键参照主键的数据
--     emp_xxx 的所在部门( deptno )是外键 , 参照 dept_xxx 的主键
--     emp_xxx 的经理( mgr )列是外键 , 参照 emp_xxx 的主键

-- 列出员工的姓名和所在部门的名字和城市
SELECT name,dept_name,dept_location FROM emp_test , dept_test WHERE emp_test.dept_test_id=dept_test.dept_id;
SELECT name,dept_name,dept_location FROM emp_test JOIN dept_test ON emp_test.dept_test_id=dept_test.dept_id;

-- 列出员工的姓名和他的上司的姓名( 自连接 )
SELECT a.name,b.name manager FROM emp_test a,emp_test b WHERE a.manager=b.emp_id;
SELECT a.name,b.name manager FROM emp_test a JOIN emp_test b ON a.manager=b.emp_id;

-- 外连接**
-- 1)  左外连接语法结构： 表 1 left outer join 表 2 on 条件
-- 2)  右外连接语法结构： 表 1 right outer join 表 2 on 条件
-- 3)  外连接的特征：
--   如果驱动表在匹配表中找丌到匹配记录 , 则匹配一行空行
--   外连接的结果集 = 内连接的结果集 + 驱动表在匹配表中匹配丌上的记录和空值
--   外连接的本质是驱动表中的数据一个都丌能少
--   left outer join 以左边的表为驱动表
--   right outer join 以右边的表为驱动表

-- 列出员工的姓名和他所在部门的名字 , 把没有部门的员工也查出来
SELECT a.name,b.dept_name FROM emp_test a LEFT  JOIN dept_test b ON a.dept_test_id=b.dept_id;

-- 列出员工的姓名和他所在部门的名字 , 把没有员工的部门也查出来
SELECT a.name,b.dept_name FROM emp_test a RIGHT  JOIN dept_test b ON a.dept_test_id=b.dept_id;

-- 哪些部门没有员工？
SELECT a.dept_test_id,b.dept_name FROM emp_test a RIGHT  JOIN dept_test b ON a.dept_test_id=b.dept_id WHERE a.dept_test_id IS NULL;

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

推荐阅读更多精彩内容