目录
前言
本文是数据库的笔记。
一、DML语法详解
-- SQL语法注释：不会被数据库识别，用于解释说明的代码！
-- 快捷键：ctrl + ?
-- 注意：Navicat这个软件没有自动保存的功能！
-- 切记：所有代码敲完之后一定要按保存！(ctrl + s)
-- 添加语法：insert
-- (1). 指定列添加：添加一条数据只包含指定列的信息！
-- 语法结构：insert into 表名(列名, 列名, 列名) values(数据, 数据, 数据);
-- 注意1：所有符号必须是英文的！！
-- 注意2：所有涉及到字符串或者日期的数据一定要通过单引号声明！
-- 注意3：SQL语法中不区分数据的大小写insert等价于INSERT等价于InSerT
-- 案例：向student表中添加一个姓名为王小丽、年龄为19岁的学生信息
insert into student(name, age) values('王小丽', 19);
insert into student(age, name) values(21, '王小丽2');
-- (2). 全列添加：添加一条数据必须包含所有列的信息！
-- 语法结构：insert into 表名 values(数据, 数据, 数据);
-- 注意：一旦表名后面没有声明列名，那就意味着所有的列的数据都要声明！
insert into student values(1188, '宇文小芳', 28, 97, '2000-09-09 08:09:18');
-- 问题：编号和入学时间是否需要自己声明？不需要！！
-- 大招：如果全列添加时，某列数据不想填充，可以使用default当做占位符！
insert into student values(default, '宇文大纲', 28, 97, default);
-- (3). 批量添加：通过一个SQL语法可以实现同时添加多条数据！
-- 语法结构：insert into 表名 values(数据, 数据, 数据), (数据, 数据, 数据), (数据, 数据, 数据), (数据, 数据, 数据);
-- 删除语法：delete
-- (1). 全删除：清空表中所有的数据！
-- 语法结构：delete from 表名;
-- (2). 条件删除：删除符合条件的数据信息！
-- 语法结构：delete from 表名 where 判断条件;
-- 判断条件：就是通过= != < > <= >=这几个符号声明的判断！
-- 关联符号：如果遇到多个条件，用and(并且)或者or(或者)进行关联！
-- 案例：删除student表中学号为1114的学生信息！
delete from student where id = 1114;
-- 案例：删除student表中所有年龄超过30岁的学生信息！
delete from student where age > 30;
-- 案例：删除student表中所有年龄在25 ~ 30岁之间的学生信息
delete from student where age >= 25 and age <= 30;
delete from student where age between 25 and 30;
-- 修改语法：update
-- (1). 全列修改：将表中某一列中所有的数据进行统一修改！
-- 语法结构：update 表名 set 列名 = 新数据;
-- 注意：set后面的等号表示重新赋值的含义！where后面的等号表示是否相等的含义
-- 案例：将student表中所有学生的入学时间统一改为2022-11-01
update student set joindate = '2022-11-01';
-- 案例：将student表中所有学生的年龄 + 1处理
update student set age = age + 1;
-- (2). 条件单列修改：将符合条件的某些数据进行修改！
-- 语法结构：update 表名 set 列名 = 新数据 where 判断条件;
-- 案例：将student表中学号为1115的学生年龄改为17岁
update student set age = 17 where id = 1115;
-- 案例：将student表中年龄超过80岁的学生成绩+10分
update student set score = score + 10 where age > 80;
-- (3). 条件多列修改：将满足条件的数据的多列同时进行修改！
-- 语法结构：update 表名 set 列名 = 新数据, 列名 = 新数据 where 判断条件;
二、DQL语法详解
-- MySQL查询简介：查询就是从数据库检索数据的行为！
-- 1. 无条件查询
-- 2. 有条件查询
-- 3. 排序查询
-- 4. 模糊查询
-- 5. 函数查询
-- 6. 分组查询(☆☆)
-- 7. 子查询(☆☆☆☆☆)
-- 8. 多表查询(☆☆☆)
-- 9. 分页查询
-- 10. 高级查询
-- 1. 无条件查询：全查询！检索表中某一列或者多列所有数据
-- 语法结构：select 列名, 列名 from 表名;
-- 案例：查询所有学生的学号和姓名
select id, name from student;
-- (1). 我们可以在查询基础上对列中数据进行四则运算！
-- 案例：查询所有学生的姓名和虚岁
select name, age + 1 from student;
-- (2). 我们可以在不改变列中数据的情况下为列名声明别名
-- 语法结构：select 列名 as 别名, 列名 as 别名 from 表名;
-- 注意：别名不是数据，无需通过引号声明！
select name as 姓名, age + 1 as 虚岁 from student;
-- 扩展：声明别名时，as关键字可以省略！
-- (3). 我们还可以对列中数据进行拼接(合并、追加)操作！
-- 语法结果：select concat(数据, 数据, 数据) from 表名;
-- 案例：查询所有学生的学号和姓名(每个姓名后面追加是个好学生)
select id, name + '是个好学生' from student;
-- MySQL中加号只能表示求和！所有文字在求和眼中相当于数字0
select id, concat(name, '是个好学生') from student;
-- MySQL中：1 + 1 = 2 concat(1, 1) = 11
-- MySQL中：'1' + '1' = 2 '你好' + '2' = 2
-- 2. 有条件查询：在全查询的基础上通过where声明筛选条件！
-- 语法结构：select 列名, 列名 from 表名 where 判断条件;
-- 案例：查询student表中所有成年学生的学号、姓名、年龄
select id, name, age from student where age >= 18;
-- (1). between ... and ...
-- 语法结构：where 列名 between a and b
-- 语法结构：where 列名 not between a and b
-- 案例：查询成绩在80 ~ 100分之间的学生姓名、成绩！
select name, score from student where score >= 80 and score <= 100;
select name, score from student where score between 80 and 100;
-- 案例：查询年龄不在20 ~ 30岁之间的学生姓名和年龄
select name, age from student where age not between 20 and 30;
-- (2). in(数据, 数据)
-- 语法结构：where 列名 in(数据, 数据, 数据)
-- 语法结构：where 列名 not in(数据, 数据, 数据)
-- 案例：查询年龄是19或者21或者23岁的学生学号，姓名，年龄
select id, name, age from student
where age = 19 or age = 21 or age = 23;
select id, name, age from student where age in(19, 21, 23);
-- (3). 查询时对于空的判断！
-- MySQL中空有两种表现形式：''(空白字符串)、null(空)
-- 1>. ''的判断：
-- 语法结构：where 列名 = ''
-- 语法结构：where 列名 != ''
-- 2>. null的判断：☆☆☆☆☆☆
-- 语法结构：where 列名 is null
-- 语法结构：where 列名 is not null
-- 案例：查询student表中没有姓名的学生学号和年龄
select id, age from student where name = '' or name is null;
-- 3. 排序查询：将查询结果进行排序处理！
-- 语法结构：order by 列名 asc / desc
-- 升序排序：asc --> ascending
-- 降序排序：desc --> descending
-- 案例：查询所有成年学生的学号，姓名，成绩！(按照成绩降序排列)
select id, name, score from student
where age >= 18 order by score desc;
-- 扩展：如果asc或者desc不写！默认按照升序排列！
-- 4. 模糊查询：关键字查询！(根据某个关键字进行检索！)
-- 语法结构：where 列名 like '匹配规则'
-- 语法结构：where 列名 not like '匹配规则'
-- 匹配规则：在模糊查询中可以通过%和_声明！
-- %：匹配任意长度的任意字符
-- _：匹配一个长度的任意字符
-- 案例：查询student表中姓名含有小字的学生学号、姓名
select id, name from student where name like '%小%';
-- 案例：查询student表中姓名第一个字是小的学生学号、姓名
select id, name from student where name like '小%';
-- 案例：查询student表中姓名第二个字是小的学生学号、姓名
select id, name from student where name like '_小%';
-- 案例：查询student表中2022年入学的所有学生学号，姓名，入学时间
select id, name, joindate from student
where joindate between '2022-01-01' and '2022-12-31';
select id, name, joindate from student
where joindate like '2022%';
-- 案例：查询10月份入学的所有学生学号，姓名，入学时间！
select id, name, joindate from student
where joindate like '_____10%';
三、MySQL函数详解
-- 什么是MySQL的函数？
-- 函数本质上就是用来处理数据的公式！
-- MySQL中根据函数处理数据的特点不同，将函数分为两种
-- 1. 单行函数：每输入一条数据，就产生一条结果！
-- 2. 聚合函数：无论输入多少条数据，只产生一条结果！
-- 单行函数简介：根据单行函数处理数据的种类不同！
-- 1. 字符型单行函数：适用于处理字符类型的数据！
-- 特点：MySQL对数据类型不敏感！数字也可以当做字符处理！
-- 函数的括号内就表示声明输入到函数内部需要处理的数据！
-- (1). length(数据)：求数据的字节长度！
select length('我爱你');
-- 就length这一个函数是看字节长度！剩下所有涉及到长度！都是普通长度
-- 案例：查询每个学生学号、姓名、姓名长度！
select id, name, length(name) from student;
-- (2). concat(数据, 数据, 数据)：将括号内的数据进行拼接操作
-- (3). substring(数据, 数字)：将指定数据从指定位置截取到最后！
-- 扩展：substring可以简写为substr
select substr('我爱你中国', 4);
-- (4). substring(数据, 数字, 数字)：将指定数据从指定位置开始截取指定长度
select substr('我爱你中华人民共和国', 2, 4); -- 爱你中华
select substr('我爱你中华人民共和国', 4, 5); -- 中华人民共
-- 案例：查询所有people的姓名和出生年月日
select name, substr(id, 7, 8) from people;
-- 案例：查询姓名第二个字是小的所有学生学号、姓名
select id, name from student where substr(name, 2, 1) = '小';
-- 案例：查询10月份入学的所有学生姓名、入学时间
select name, joindate from student
where substr(joindate, 6, 2) = '10';
-- (5). replace(数据, 数据, 数据)：将数据中的指定内容进行替换！
select replace('少小离家老大回，安能辨我是雄雌', '老大', '老六');
-- 案例：查询people表中所有人的姓名和手机号(脱敏)
select name, replace(tel, substr(tel, 4, 4), '****') from people;
-- replace会将所有出现的指定数据全部进行替换！
-- (6). instr(数据, 数据)：返回第二个数据在第一个数据中首次出现的位置
-- 扩展：如果没出现则返回0
select instr('唧唧复唧唧，木兰当户织', '唧唧木兰');
-- 案例：查询student表中姓名含有小字的学生学号和姓名
select id, name from student where instr(name, '小') != 0;
-- (7). upper(数据) / lower(数据)：将数据全部变成大写或者小写！
-- 2. 数字型单行函数：只针对于数字格式的数据生效！例如：1、'1'
-- (1). round(数字)：将数字进行四舍五入操作！
select round(123.567);
-- (2). round(数字, 位置)：将数字以小数点位置为基准进行四舍五入！
select round(123.567, -2);
-- (3). mod(数字, 数字)：求余数！
select mod(12, 5); -- 2
select mod(10, 3); -- 1
-- (4). ceil(数字)：向上取整！
select ceil(1.8); -- 2
select ceil(12.1); -- 13
select ceil(2.9); -- 3
select ceil(4.0); -- 4
-- (5). floor(数字)：向下取整！
-- 3. 日期型单行函数：适用于日期类型或者具有日期格式的数据！
-- 例如：'2000-09-09'、'2000/09/09'、'2000/9/9'、20000909
-- 除了：20001301
-- (1). year(日期)：提取日期中的年份
-- (2). month(日期)：提取日期中的月份
-- (3). day(日期)：提取日期中的天数
-- 案例：查询10月份入学的所有学生姓名、入学时间
select name, joindate from student
where month(joindate) = '10';
-- 4. 功能型单行函数(☆☆☆☆☆)
-- (1). ifnull(数据, 结果)：如果第一个数据为null，则当做第二个结果处理！
-- MySQL规则：null数据和任何其他左右数据进行任何运算和处理结果永远是null
-- 案例：查询emp表中所有员工的姓名和月薪！
select name, salary + ifnull(bonus, 0) from emp;
-- (2). if(条件, 结果1, 结果2)：如果条件成立则当做结果1处理否则当做结果2处理
-- 案例：查询student表中所有学生的姓名和成绩(只想看及格、不及格！)
select name, if(score >= 60, '及格', '不及格') from student;
-- 问题：if函数能否代替ifnull？能！
select name, salary + if(bonus is null, 0, bonus) from emp;
-- (3). case ... when ... then ... else ... end语法：
/*
case
when 条件1 then 条件1成立结果
when 条件2 then 条件2成立结果
when 条件3 then 条件3成立结果
else 以上条件都不成立的结果
end
*/
-- 案例：查询student表中所有学生的姓名和成绩！
-- 成绩：0 ~ 60(不及格)、60 ~ 80(及格)、80 ~ 90(良好)、90 ~ 100(优秀)、成绩错误
select name,
case
when score between 90 and 100 then '优秀'
when score >= 80 and score < 90 then '良好'
when score >= 60 and score < 80 then '及格'
when score >= 0 and score < 60 then '不及格'
else '成绩错误'
end as 成绩
from student;
-- 聚合函数：聚合函数处理数据时会自动忽略null数据！
-- (1). max(数据)：求最大值
-- (2). min(数据)：求最小值
-- (3). sum(数据)：求和
-- (4). count(数据)：求数量
-- (5). avg(数据)：求平均数(等价于sum(数据) / count(数据))
select sum(score) from student;
-- 提醒：使用count函数求数量的时候，这一列最好要求不能为null！
select count(id) from student;
-- 查询所有学生的平均成绩
select avg(ifnull(score, 0)) from student;
-- 聚合函数由于不识别null，所以有时需要配合ifnull使用
四、函数任务讲解
-- 查询worker表中所有工人的姓名，要求：姓名中首字母要大写处理并展示！(提示：需要用到多个函数配合)
-- 先截取，再大写，然后替换！(错误写法)
select replace(name, substr(name, 1, 1), upper(substr(name, 1, 1))) from worker;
-- 截取首位变大写！截取剩下的几位，最终拼接！(正确写法)
select
concat(upper(substr(name, 1, 1)), substr(name, 2))
from worker;
-- 但是Oracle中有一个函数captial()可以自动将数据首字母大写处理！
-- 但是MySQL不支持！！！
-- 5.查询emp表中所有员工的姓名和职务，如果职务为'CEO'则显示'首席执行官'，如果职务是'Project Manager'则显示'项目经理'，如果职务是'PG'则显示'程序员'，否则显示'员工'，最后给查询的字段起一个别名叫'job'。
select name,
case
when job = 'CEO' then '首席执行官'
when job = 'Project Manager' then '项目经理'
when job = 'PG' then '程序员'
else '员工'
end
from emp;
五、分组查询详解
-- 什么是分组查询？
-- 分组查询本质上就是将表中所有数据按照某个规则划分成若干个小组，并对每个小组进行单独查询的行为！
-- 特点：分成几个小组，最终就会查询到几个结果！
-- 根据分组的规则不同，分组查询共有三种方式进行分组！
-- 1. group by 列名：将某列中重复的数据换分成一个个小组！
-- 2. group by 列名, 列名：将多列数据完全重复的数据划分成一个小组！
-- 3. group by 条件, 条件：将符合条件的指定数据划分成一个个小组！
-- 案例：查询emp表中每个部门的部门人数、部门编号！
select count(no), deptno from emp group by deptno;
-- 案例：查询emp表中每个部门工资超过1000的人数和部门编号
select deptno, count(no) from emp
where salary > 1000
group by deptno;
-- 问题：先筛选后分组、先分组后筛选
-- 答案：先筛选后分组！(where一定要声明在group by之前)
-- 案例：查询emp表中每个部门有奖金的员工并且工资大于1000的这部分人的平均工资和所在部门编号！
select avg(salary), deptno from emp
where bonus > 0 and salary > 1000
group by deptno;
-- 案例：查询nation表中男女的人数各是多少？
select count(id), sex from nation group by sex;
-- 案例：查询nation表中每个国家的人数各是多少？
select count(id), country from nation group by country;
-- 案例：查询nation表中每个国家男女人数各是多少？
select count(id), sex, country from nation group by country, sex;
select count(id), sex, country from nation group by sex, country;
-- 案例：查询student表中成年和未成年的学生人数各是多少
select
case
when ifnull(age, 0) >= 18 then '成年'
when ifnull(age, 0) < 18 then '未成年'
end,
count(id) from student
group by ifnull(age, 0) >= 18, ifnull(age, 0) < 18;
-- 如果有null数据发生，可以配合ifnull进行处理！
select
case
when age >= 18 then '成年'
when age < 18 then '未成年'
end,
count(id) from student
where age is not null
group by age >= 18, age < 18;
-- 如果统计的时候就是不想要null数据，可以声明筛选条件！
-- 什么是having关键字？having关键字如何使用？
-- having只会在分组查询中使用！
-- having本意和where一样，都是用于声明筛选条件！
-- having和where最大的区别在于，where发生在分组前，对表中所有数据进行统一筛选！having发生在分组后，对小组进行筛选！
-- having可以和where共存！也可以任意存在其中一个！
-- having和where的区别？
-- 1. having声明在分组后，where声明在分组前
-- 2. having是对聚合函数列进行筛选！where是对非聚合函数列进行筛选！
-- 3. having和where不冲突可以任意声明，也可以同时存在！
-- 案例：查询emp表中每个部门的部门人数超过2人的部门编号和部门人数
select deptno, count(no) from emp
group by deptno
having count(no) > 2;
-- 案例：查询emp表中每个部门有奖金的人数超过2人的部门编号和有奖金的人数
select deptno, count(no) from emp
where bonus > 0
group by deptno
having count(no) > 2;
六、子查询详解
-- 什么是子查询？
-- 子查询又称为嵌套查询！
-- 将一个查询语句的结果当做另一个查询语句的条件或者表！
-- 1. 当做条件或者表的查询称为子句！最终查询的语句称为主句！
-- 2. 一个子查询中最多有一个主句！可以有多个子句！
-- 3. 子句中的任何行为不会对主句造成影响！但是主句中的某些行为可能会影响到子句！
-- 案例：查询年龄比小美大的学生的学号，姓名，年龄
-- 当条件中出现未知数据时，需要通过查询语句进行查询！
-- 子句：查询小美的年龄 -> 18岁
select age from student where name = '小美';
-- 主句：查询年龄大于18的学生信息
select id, name, age from student
where age > (select age from student where name = '小美');
-- 案例：查询emp表中工资最高的员工的工号、姓名、工资！
-- 错误写法：
select no, name, salary from emp
where salary = max(salary);
-- 正确写法：
-- 子句：查询最高工资！
select max(salary) from emp;
-- 主句：
select no, name, salary from emp
where salary = (select max(salary) from emp);
-- 案例：查询20号部门工资最低的员工工号，姓名，工资，部门编号
-- 子句：查询20号部门的最低工资
select min(salary) from emp where deptno = 20;
-- 主句：
select no, name, salary, deptno from emp
where salary = (select min(salary) from emp where deptno = 20)
and deptno = 20;
-- 切记：主句不要漏条件！！！！！！！
-- 案例：查询emp表中入职时间最晚的员工中，工资最高的员工的姓名，工资，入职时间
-- 子句：查询最晚入职时间！ --> 20150111
select max(joindate) from emp;
-- 子句：查询最晚入职的员工中的最高工资！ --> 500
select max(salary) from emp
where joindate = (select max(joindate) from emp);
-- 主句：
select name, salary, joindate from emp
where salary = (select max(salary) from emp
where joindate = (select max(joindate) from emp))
and joindate = (select max(joindate) from emp);
-- 根据子句返回的结果数量不同，子查询共分为四种查询形式！
-- 1. 单行单列子查询：子句查询结果返回一行一列的数据！
-- 特点：子句只返回一个结果！可以直接当做主句的条件进行判断！
-- 案例：查询student表中10月份入学的学生中，最小年龄对应的学生姓名，年龄，入学时间
-- 子句：查询10月份入学的最小年龄！
select min(age) from student where month(joindate) = '10';
-- 主句：
select name, age, joindate from student
where age = (select min(age) from student where month(joindate) = '10')
and month(joindate) = '10';
-- 2. 多行单列子查询：子句查询结果返回多行一列的数据！
-- 语法结构：where 列名 > any(多行一列结果)
-- 语法结构：where 列名 > all(多行一列结果)
-- 案例：查询emp表中和lily或者Jeary在一个部门的员工的姓名，职位，部门编号
-- 子句：查询lily和james的部门编号
select deptno from emp where name in('lily', 'Jeary');
-- 主句：
select name, job, deptno from emp
where deptno = any(select deptno from emp where name in('lily', 'Jeary'));
-- 偷鸡做法：
select deptno from emp where name = 'lily';
select deptno from emp where name = 'Jeary';
-- 主句：
select name, job, deptno from emp
where deptno = (select deptno from emp where name = 'lily')
or deptno = (select deptno from emp where name = 'Jeary');
-- 3. 单行多列子查询：子句查询结果返回一行多列的数据！
-- 语法结构：where (列名, 列名) = (单行多列结果)
-- 案例：查询入职时间和工资都和Jeary一样的员工姓名，工资，入职时间！
-- 子句：查询Jeary的入职时间和工资！
select joindate, salary from emp where name = 'Jeary';
-- 主句：
select name, salary, joindate from emp
where (joindate, salary) = (select joindate, salary from emp where name = 'Jeary') and name != 'Jeary';
-- 偷鸡语法：
select joindate from emp where name = 'Jeary';
select salary from emp where name = 'Jeary';
-- 主句：
select name, salary, joindate from emp
where joindate = (select joindate from emp where name = 'Jeary')
and salary = (select salary from emp where name = 'Jeary')
and name != 'Jeary';
-- 4. 表子查询：和查询结果行列数量无关！表子查询就是将查询结果当做表重新进行进一步查询的行为！
-- 切记：当做表的子查询数据一定要通过as声明一个表名！才能进行表子查询
-- 案例：查询emp表中每个部门的平均工资中的最低平均工资！
select min(avg(salary)) from emp group by deptno;
-- 坏消息：MySQL不支持聚合函数的嵌套使用！
-- 好消息：Oracle支持聚合函数的嵌套使用！
-- 将子句当做表处理！(子句查询结果中的复杂列名最好声明别名！)
select avg(salary) as a from emp group by deptno;
-- 主句：
select min(a) from (select avg(salary) as a from emp group by deptno) as aa;
七、分组查询、子查询任务讲解
-- 7.查询有班级并且年龄大于18的这部分学生中，每个班级的学生人数和对应班级号
select count(no), class from student
where class is not null and age > 18
group by class;
-- 8.查询工资低于1000的人数，工资在1000~2000的人数，工资高于2000的人数
-- 分析：这是一道分组查询的题！
select
case
when salary < 1000 then '低于1000的人数'
when salary between 1000 and 2000 then '在1000~2000的人数'
when salary > 2000 then '高于2000的人数'
end,
count(no) from emp
group by salary < 1000, salary between 1000 and 2000, salary > 2000;
-- 7.收到简历在20份以上的招聘途径分别是哪些
select source from job
group by source
having count(id) > 20;
-- 9.显示投递市场专员职位的求职者中，男女求职者的最大年龄分别是多少岁
select sex, max(age) from job
where position = '市场专员'
group by sex;
-- 4、在基本工资高于2000元的员工中，查询每个部门的员工平均工资，并显示出部门平均工资高于3000元的记录，按照平均工资由低到高显示
select com_id, avg(emp_wage) from employee
where emp_wage > 2000
group by com_id
having avg(emp_wage) > 3000
order by avg(emp_wage) asc;
-- 7、查询每一年入职的员工有多少人，显示效果如下图所示（最左侧序号不用展示）
select concat(year(emp_date), '年') 入职年份, count(emp_id) 人数 from employee
group by year(emp_date);
-- 7. 查询年龄比1班学生年龄大的学生中，最高成绩的学生的姓名，班级和分数
-- 子句：查询1班最大年龄
select max(age) from student where class = 1;
-- 子句：查询年龄大于1班的学生中的最高成绩！
select max(score) from student
where age > (select max(age) from student where class = 1);
-- 主句：
select name, class, score from student
where score = (select max(score) from student
where age > (select max(age) from student where class = 1))
and age > (select max(age) from student where class = 1);
-- 1. 查询部门平均工资最低的部门，该部门的部门编号
-- 子句：查询每个部门的平均工资！
select avg(salary) as a from emp group by deptno;
-- 表子查询：
select min(a) from (select avg(salary) as a from emp group by deptno) as aa;
-- 主句：
select deptno from emp
group by deptno
having avg(salary) = (select min(a) from (select avg(salary) as a from emp group by deptno) as aa);
八、多表查询详解
-- 什么是多表查询？
-- 多表查询就是从多张表中获取数据的检索行为！
-- 核心原理：将多表数据合并后进行统一查询！
-- 语法结构：select 列名, 列名 from 表A join 表B on 关联条件;
-- 注意1：多表合并后可能会出现重复的列名！需要通过表名.列名区分
-- 注意2：多表查询一定要通过on声明关联条件！
-- 案例：查询所有员工的姓名，工资，部门编号，部门名称
-- 分析：emp和dept是两张有数据关联的表！可以做多表查询！
select name, salary, emp.deptno, deptname
from emp join dept;
-- 笛卡尔积错误：由于缺失关联条件或者关联条件错误导致数据有问题的现象
-- 正确写法
select name, salary, emp.deptno, deptname
from emp join dept
on emp.deptno = dept.deptno;
-- 案例：查询大连区工作的所有员工的工号，姓名，工资！
-- 分析：算不算多表查询？算！
select no, name, salary from emp join dept
on emp.deptno = dept.deptno
where description = '大连区';
-- 偷鸡写法：查询的列只包含在哪张表中？emp表！
-- 子句：查询dept表中大连区部门的部门编号！
select deptno from dept where description = '大连区';
-- 主句：
select no, name, salary from emp
where deptno = (select deptno from dept where description = '大连区');
-- 当查询所有列都是一个表的数据，只不过条件是另一张表！可以用子查询代替！
-- 多表查询的分类
-- 根据多表匹配的规则不同，多表查询共分为两大类别 + 特殊类别！
-- 1. 内连接：inner join(简写为join)
-- 特点：只能查询到两表符合关联条件的数据
select name, salary, emp.deptno, deptname
from emp join dept
on emp.deptno = dept.deptno;
-- 2. 外连接：outer join
-- (1). 左外连接：left outer join(简写为left join)
-- 特点：查询到两表符合关联条件的数据 + 左表额外的数据
select name, salary, emp.deptno, deptname
from emp left join dept
on emp.deptno = dept.deptno;
-- (2). 右外连接：right outer join(简写为right join)
-- 特点：查询到两表符合关联条件的数据 + 右表额外的数据
select name, salary, emp.deptno, deptname
from emp right join dept
on emp.deptno = dept.deptno;
-- (3). 满外连接：full outer join(简写为full join)
select name, salary, emp.deptno, deptname
from emp full join dept
on emp.deptno = dept.deptno;
-- MySQL不支持满外连的写法！Oracle支持！
-- 3. 自连接：特殊连接方式！
-- 特点：只用一张表来实现多表查询的方式！本质上就是自己关联自己！
-- 解决方案：通过给一张表声明不同的别名进行区分！
-- 案例：查询emp表中所有员工和领导的姓名！
select worker.name as 员工姓名, leader.name as 领导姓名
from emp as worker -- 员工表
join emp as leader -- 领导表
on worker.manageno = leader.no;
-- 问题：内连接和外连接到底用谁？理论上用内连接就可以！但是要看需求！
/*
user表：用户表
id name
101 小明
102 小芳
103 小强
order表：订单表
id name user_id
1001 ipad 101
1002 iphone 101
1003 airpod 101
1001 watch 102
.........
查询所有的用户以及对应的下单信息！(用户表为主表！)
问题：内连接还是外连接？左外连接
select * from user left join order
on user.id = order.user_id;
如果遇到两张以上的表应该如何进行关联？
select 列名, 列名 from 表A
join 表B
on 表B和表A的关联条件
join 表C
on 表C和表B的关联条件 / 表C和表A的关联条件
join 表D
on 表D和表C的关联条件 / 表D和表B的关联条件 / 表D和表A的关联条件
......
where 筛选条件
group by 列名
having 筛选条件
order by ...
*/
九、分页查询详解
-- limit关键字：用来进行分页查询！
-- 1. limit a：截取查询结果的前a条数据！
select * from job limit 5;
-- 案例：查询emp表中工资排名前5的员工姓名，工资！
select name, salary from emp
order by salary desc limit 5;
-- 偷鸡：查询emp表中工资最高的员工工号，姓名，工资！(不建议)
select no, name, salary from emp
order by salary desc limit 1;
-- 2. limit a, b：截取从第a + 1条开始的b条数据！
select * from job limit 2, 4; -- 第3、4、5、6条！
select * from job limit 4, 6; -- 第5、6、7、8、9、10条！
-- 案例：查询emp表中工资排名第3 ~ 第7的员工姓名，工资！
select name, salary from emp
order by salary desc
limit 2, 5;
-- 真正做分页查询！job为例！(400多条数据！)
-- 规定：每页显示10条数据！
-- 第1页：第1条 ~ 第10条 limit 0, 10
-- 第2页：第11条 ~ 第20条 limit 10, 10
-- 第3页：第21条 ~ 第30条 limit 20, 10
-- 第n页： limit 10(n-1), 10
-- 规定：每页显示m条数据！
-- 第n页：limit m(n-1), m。
十、高级查询简介
-- 1. distinct关键字：去重！
-- 语法结构：select distinct 列名, 列名 from 表名;
select distinct salary from emp;
-- 原理：对查询结果中所有列进行统一去重！
select distinct salary, bonus from emp;
-- 2. union / union all关键字：合并多个查询语句的查询结果！
-- union：自带去重效果！
select no, name from emp where no in(0003, 0004, 0005)
union
select no, name from emp where no in(0001, 0002, 0003);
-- 要求：要求多个查询语句查询的列数必须相同！
-- 现阶段：新人(SQL语句能运行就够了！)
-- 后期：效率！(优化！)(不同写法的效率是不同的！)
十一、综合练习任务讲解
-- 11.查询公司中领导是Jim的员工姓名、工资和职务
-- 子查询：
-- 子句：查询Jim的工号！
select no from emp where name = 'Jim';
-- 主句：查询manageno是jim工号的员工
select name, salary, job from emp
where manageno = (select no from emp where name = 'Jim');
-- 自连接：
select worker.name, worker.salary, worker.job
from emp as worker -- 员工
join emp as leader -- 领导
on worker.manageno = leader.no
where leader.name = 'Jim';
-- 22. 查询"财务部"工资最少的人的姓名和职务
-- 子句：查询财务部最低工资！
select min(salary) from emp join dept
on emp.deptno = dept.deptno
where deptname = '财务部';
-- 主句：查询工资是1200的员工信息并且还得是财务部！
select name, job from emp
join dept on emp.deptno = dept.deptno
where salary = (select min(salary) from emp join dept
on emp.deptno = dept.deptno
where deptname = '财务部')
and deptname = '财务部';
-- 29.查询出部门平均工资高于公司平均工资的部门,其部门编号和部门名称。
-- 子句：查询公司平均工资！
select avg(salary) from emp;
-- 主句：
select emp.deptno, deptname from emp join dept
on emp.deptno = dept.deptno
group by emp.deptno
having avg(salary) > (select avg(salary) from emp);
十二、事务的操作详解
-- MySQL会在运行DML语法之前自动开启事务！
insert into test values(1001, '小明');
-- 运行完SQL语法后，MySQL自动提交事务！
set autocommit = 0; -- 开启事务的手动操作！
-- MySQL会在运行DML语法之前自动开启事务！
insert into test values(1002, '小芳');
insert into test values(1003, '小刚');
commit; -- 提交事务(存档)(让当前事务运行成功并结束)
insert into test values(1004, '小刚1');
insert into test values(1005, '小刚2');
commit;
insert into test values(1006, '小刚3');
insert into test values(1007, '小刚4');
insert into test values(1008, '小刚5');
rollback;
insert into test values(1009, '小刚6');
insert into test values(1010, '小刚7');
commit;
insert into test values(1011, '小刚8');
rollback;
-- 问题1：上面的代码一共包含了几个事务？四个事务
-- 问题2：数据库一共新增了几条数据？4条
总结
以上就是今天要讲的内容，本文介绍了数据库的总结。
4018
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
