MySQL基础学习（尚硅谷婷姐）自学笔记_001

最新推荐文章于 2022-10-28 21:38:45 发布
仲子_real
最新推荐文章于 2022-10-28 21:38:45 发布
阅读量273
点赞数
分类专栏： SQL学习文章标签： mysql
本文链接：https://blog.csdn.net/weixin_46076729/article/details/114538643
版权
SQL学习专栏收录该内容
5 篇文章 2 订阅
订阅专栏
MySQL基础学习（尚硅谷婷姐）自学笔记_001

/*MySQL服务的登录与退出
方式一：通过dos命令
mysql -h主机名 -P端口号 -u用户名 -p密码
注意：
如果是本机，则-h主机名可以省略
如果端口是3306，则-P端口号可以省略
方式二：通过图形化界面客户端
通过sqlyog，直接输入用户名、密码等链接进去


MySQL的常见命令与语法规范：
（1）常见命令
show databases;            #显示当前连接下所有数据库
show tables;               #显示当前库中的列表
show tables from 库名;     #显示指定表中所有表
show columns from 表名;    #显示指定表中所有列
use 库名;                  #打开/使用指定库

（2）语法规则
不区分大小写
每条命令节为用分号*/




#注释的方法有三种：
#注释的内容（使用井号键#）
-- 注释的内容（使用双杠，但是空格不能省略）
/*
注释的内容
*/



#基础一：基础查询
/*
语法：
select 查询列表 from 表名;

特点：
1、查询的结果集是一个虚拟表
2、select 查询列表 类似于java中的System.out.printin(打印的字段);

select后面的查询列表，可以有多个部分组成，中间用逗号隔开
例如：select 字段1, 字段2, 表达式 from 表名;

System.out.printin()的打印内容只能有一个

3、执行顺序
select first_name from employees;

执行顺序为：
第一步：from语句：先搜索有没有employees表
第二步：select语句：再搜索有没有first_namme表

查询列表可以是：字段、表达式、常量、函数
*/

#一、查询常量
SELECT 100 FROM employees;
SELECT 100;

#二、查询表达式
SELECT 100%3 FROM employees;
SELECT 100%3;

#三、查询单个字段
SELECT `last_name` FROM `employees`;


#四、 查询多个字段
SELECT `first_name`, `last_name`, `salary` FROM `employees`;

#五、查询所有字段
SELECT * FROM `employees`;

#快捷键F12能够帮助我们自动调整格式事项标准格式
SELECT 
  `first_name`,
  `last_name`,
  `hiredate`,
  `job_id`,
  `salary` 
FROM
  `employees` ;


#六、查询函数（低矮哦用函数、获取返回值）
SELECT DATABASE(); #查询当前使用的数据库
SELECT VERSION(); #查询当前数据库的版本
SELECT USER(); #查询当前使用数据库的用户名


#七、起别名
#方式一：使用关键字as
SELECT USER() AS 用户名;
SELECT USER() AS ”用户名“; #最好加上引号，防止因为名字中有空格等其他符号二十的程序报错
SELECT USER() AS ‘用户名’;

SELECT last_name AS '姓 名' FROM employees;

#方式二：；使用空格
SELECT USER() 用户名;
SELECT USER() ”用户名“; #最好加上引号，防止因为名字中有空格等其他符号二十的程序报错
SELECT USER() ‘用户名’;

SELECT last_name AS '姓 名' FROM employees;

#八、+的作呕用以及concat的使用
-- 需求：查询first_name和last_name拼接成全名，最终起别名为”姓 名“
#使用拼接函数concat
SELECT CONCAT(last_name, " ", first_name) AS "姓 名" FROM employees;

/*
mysql中的+加号作用：只有及爱发运算的作用
（1）两个操作数都是数值型
100+1.5

（2）其中一个为字符型数据
将字符型数据强制转换成数值型，若无法完成转换，则将其作为0处理
”张三“ + 1.5 = 1.5

（3）其中一个操作数为null
只要出现null，则表达式为0
null + null = 0
100 + null = 0
*/

#九、distinct的使用
-- 需求：查询员工设计的部门编号有哪些
SELECT DISTINCT department_id FROM employees;

#十、查询表的结构
DESC employees;
SHOW COLUMNS FROM employees;


#############第一次练习题#################

#1. 下面的语句是否可以执行成功
SELECT last_name , job_id , salary AS sal 
FROM employees;

#可以执行

#2. 下面的语句是否可以执行成功
SELECT * FROM employees;

#可以执行

#3. 找出下面语句中的错误
SELECT employee_id , last_name，
salary * 12“ANNUAL SALARY”
FROM employees;

#错误修改：
SELECT employee_id , last_name,
salary * 12 AS "ANNUAL SALARY" 
FROM employees;

#4. 显示表 departments 的结构，并查询其中的全部数据
DESC departments;
SHOW COLUMNS FROM departments;

#5. 显示出表 employees 中的全部 job_id（不能重复）
SELECT DISTINCT job_id FROM employees;

#6. 显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT
SELECT CONCAT(first_name,",",last_name,",",email,',',job_id,',',salary,',',IFNULL(commission_pct,''),',',manager_id,',',department_id,',',hiredate) AS "OUT_PUT"
FROM employees;



#基础二：条件查询
/*
语法：
select 查询列表
from 表名
where 筛选条件

执行顺序：
1、from子句
2、where子句
3、select子句

select last_name, first_name from employees where salary  > 20000;

特点：
1、按关系表达式筛选
关系运算符：> < >= <= = <>
补充：也可以使用!=， 但不建议

2、逻辑运算符：and or not
也可以写：  &&   ||   ！   ，但是不建议

模糊查询
like
in
between and
is null
*/

#一、按关系表达式筛选
#案例一：查询部门编号不是100的员工信息
SELECT * FROM employees WHERE department_id <> 100;

#案例二：查询工资小于15000的员工姓名、工资
SELECT first_name, last_name, salary FROM employees WHERE salary < 15000;

#二、按照逻辑表达式筛选
#案例一：查询部门编号不是50-100之间员工的姓名、部门编号、邮箱
SELECT first_name, last_name, department_id, `email` FROM employees WHERE NOT(`department_id`>= 50 AND `department_id` <= 100);

#案例二：查询奖金率》0.03或者员工编号再60-110之间的员工信息
SELECT * FROM employees WHERE `commission_pct`>0.03 OR (`employee_id` > 60 AND `employee_id` < 110);


#三、模糊查询
#1、like
/*
功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询
常见的通配符有：
_  任意单个字符
%  任意多个字符
*/

#案例一：查询姓名中含有字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%'

#案例二：查询姓名中第二个字符为_的员工信息
#利用转义字符\_代表一个_
SELECT * FROM employees WHERE last_name LIKE '_\_%'; #这是其他语言的做法

#以下十SQL的做法
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'; #将$设为转义字符，其他的符号也可以

#2、in
/*
功能：查询某字段中的值是否属于指定的列表中
a in(常量值1, 常量值2, 常量值3,...)
a not in(常量值1, 常量值2, 常量值3,...)

*/

#案例一：查询部门编号为30/50/90的员工名
SELECT first_name, last_name, department_id FROM employees WHERE department_id IN(30, 50, 90);

#3、between and
/*
功能：判断某个字段的值是否介于xx之间

between and/not between and

*/

#案例一：查询部门编号十30-90之间的部门编号，员工姓名
SELECT department_id, first_name, last_name FROM employees WHERE department_id BETWEEN 30 AND 90;

#案例二：查询年薪不是100000-2000000之间的原本共姓名、工资、年薪
SELECT last_name, first_name, salary, salary*12*(1 + IFNULL(commission_pct, 0)) AS '年 薪'
FROM employees
WHERE salary*12*(1 + IFNULL(`commission_pct`, 0)) NOT BETWEEN 1000000 AND 200000;


#4、is null/is not null
#案例一：查询没有奖金率的员工信息
SELECT * FROM employees WHERE commission_pct IS NULL;


-- =    只能判断普通的内容（数值）
-- is   只能判断NULL值
-- <=>  安全等于，既可以判断普通内容，也可以判断NULL值


################第二次练习################

#1. 查询工资大于 12000 的员工姓名和工资
SELECT last_name, salary FROM employees WHERE salary > 12000;

#2. 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct, 0)) AS '年 薪'
FROM employees
WHERE employee_id = 176;

#3. 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

#4. 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT last_name, depart ment_id FROM employees WHERE departmrnt_id IN(20, 50);

#5. 选择公司中没有管理者的员工姓名及 job_id
SELECT last_name, job_id FROM employees WHERE manager_id IS NULL;

#6. 选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name, salary, commission_pct FROM employees WHERE commission_pct IS NOT NULL;

#7. 选择员工姓名的第三个字母是 a 的员工姓名
SELECT last_name FROM employees WHERE last_name LIKE "__a%";

#8. 选择姓名中有字母 a 和 e 的员工姓名
SELECT last_name FROM employees WHERE last_name LIKE "%a%" AND last_name LIKE "%e%";

#9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT * FROM employees WHERE first_name LIKE '%e';

#10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT first_name, last_name, job_id FROM employees WHERE department_id BETWEEN 80 AND 100;

#11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT last_name, job_id, manager_id FROM employees WHERE manager_id IN(100, 101, 110);



#基础三，排序查询
/*
语法：
select 查询列表
from 表名
(where 筛选条件)
order by 排序列表 


执行顺序
1、from子句
2、where子句
3、select子句
4、order by子句


特点：
1、排序列表可以是单个字段、多个字段、表达式、函数、常数（列数）、以及以上的组合
2、升序通过 asc， 默认行为
   降序通过 desc
   
*/

#一、按单个字符排序
#案例一：将员工编号>120的员工信息按照工资升序的排列进行列出
SELECT * FROM employees WHERE employee_id > 120 ORDER BY salary ASC;


#二、按表达式排序
#案例一：对所有有奖金的员工，按照年薪排序
SELECT *, salary*12*(1+IFNULL(commission_pct, 0)) AS '年 薪'
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct, 0)) DESC;


#三、按别名排序
#案例一：对所有有奖金的员工，按照年薪排序
SELECT *, salary*12*(1+IFNULL(commission_pct, 0)) AS '年 薪'
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY "年 薪" DESC;


#四、按函数的结果排序
#案例一：按照姓名的长度进行排序
SELECT last_name FROM employees ORDER BY LENGTH(last_name);


#五、按照多个字段排序
#案例一：查询员工姓名、工资、部门编号，先按工资升序排序，再按部门编号降序
SELECT last_name, salary, department_id FROM employees ORDER BY salary ASC, department_id DESC;


#六、按照列数排序
#以第二列进行升序排列
SELECT * FROM employees ORDER BY 2;

#第三次练习
#1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct, 0)) AS '年 薪'
FROM employees
ORDER BY "年 薪" DESC, last_name ASC;

#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *
FROM employees
WHERE `email` LIKE "%e%"
ORDER BY LENGTH(`email`) DESC, department_id ASC; 


#基础四、常见函数
/*
函数：类似于java中的方法
为了解决某个问题，将编写的一系列命令集合封装在一起，对外仅仅暴露其方法名，供外部调用

1、自定义方法（函数）
2、调用方法（函数）
	叫什么：函数名
	干什么：函数功能

常见函数：
	字符函数
	数学函数
	日期函数
	流程控制函数
*/


#一、字符函数
#1、concat(拼接字符函数)

SELECT CONCAT('hello', first_name, last_name)  AS 'hello' FROM employees;


#2、char_length（获取字符个数）
SELECT CHAR_LENGTH('heelo, 郭襄');

#4、substring截取字串)
#substr(str, 起始索引, 截取的字符长度); 起始索引从1开始，java从0开始
SELECT SUBSTR('张三丰爱上了郭襄', 1, 3);
SELECT SUBSTR('张三丰爱上了郭襄', 7);


#5、instr(获取字符第一次出现的索引)
SELECT INSTR('孙悟空三打白骨精', '白骨精');

#6、trim(去掉前后空格)
SELECT TRIM('  虚   竹    ') AS a;
SELECT TRIM('X' FROM 'XXXXXXXX虚XXX竹XXXXXXXXXXX') AS a;

#7、lpad/rpad  (左填充/右填充)
SELECT LPAD('木婉清', 10, 'a');
SELECT RPAD('木婉清', 10, 'a');

#8、upper/lower(变大写/变小写)
#案例一：查询员工的姓名，要求格式：姓首字母大写，其他子怒小写，名所有字母大写，姓和名之间用_相连，最后起别名“OUTPUT”

SELECT UPPER(SUBSTR(first_name, 1, 1)), frist_name FROM employees;
SELECT LOWER(SUBSTR(first_name, 2)), first_name FROM employees;
SELECT UPPER(last_name) FROM employees;

SELECT CONCAT(UPPER(SUBSTR(first_name, 1, 1)), LOWER(SUBSTR(first_name, 2)), '_', UPPER(last_name)) AS OUTPUIT FROM employees;

#9、strcmp (比较两个字符的大小)
SELECT STRCMP('aa', 'abc');
SELECT STRCMP('aa', 'aca');

#10、left/right（截取字符串）
#从右截取和从左截取

SELECT LEFT('江户川柯南', 1);
SELECT RIGHT('江户川柯南', 1);

#二、数学函数
#1、ABS（绝对值函数）
SELECT ABS(-2.4);
#2\cell(向上取整)
SELECT cell(1.09);
#3、fllor（向下取整）
SELECT FLOOR(1.09);
#4、round（四舍五入）
SELECT ROUND(1.87234);
SELECT ROUND(1.87213423, 2);  #保留两位小数
#5、truncate（截断）
SELECT TRUNCATE(121.87675, 1);   #截断小数点后面的数
#6、mod(取余)
SELECT MOD(10, 3);
SELECT -10%3;   #跟被除数的符号一样

#三、日期函数
#1、now (当前日期和时间)
SELECT NOW();
#2、curdate(当前日期)
SELECT CURDATE();
#3、curtime(当前时间)
SELECT CURTIME();
#4、datediff
SELECT DATEDIFF('2021-2-25', '1997-8-26');
#5、date_format
SELECT DATE_FORMAT('1998-7-16', '%Y年%m月%d日 %H小时%i分钟%s秒') AS '出生日期';
SELECT DATE_FORMAT(hiredate, '%Y年%m月%d日 %H小时%i分钟%s秒') AS '入职日期' FROM employees;
#6、str_to_date （按照指定格式解析字符串为日期类型）
SELECT * FROM employees
WHERE hiredate < STR_TO_DATE('3/15 1998', '%m/%d %Y');
#四、流程控制函数
#1、if函数
SELECT IF(100 > 9, '好', '坏');

#需求：如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL, 0, salary*12*commission_pct) AS '奖 金' FROM employees;

#2、case函数
/*
第一种情况:
case 表达式
when 值1 then 结果1
when 值2 then 结果2
...
else 结果n
end

案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变
显示部门编号，新工资，旧工资
*/

SELECT department_id, salary AS oldsalary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END  AS newsalary
FROM employees;

/*
第二种情况:雷素与多重if语句，实现区间判断
case
when 条件1 then 结果1
when 条件2 then 结果2
...
else 结果n
end

案例：
如果工资>20000，显示级别A
如果工资>15000，显示级别B
如果工资>10000，显示级别C
否则，结果显示D
*/

SELECT salary, 
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS grade
FROM employees;

###########第四次练习################
#1. 显示系统时间(注：日期+时间)
SELECT NOW();

#2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
SELECT employee_id, first_name, salary, salary*1.2 AS 'new salary' FROM employees;

#3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT LENGTH(last_name), last_name
FROM employees
ORDER BY SUBSTR(last_name, 1, 1) ASC;



#基础五分组函数（与单行函数区分）
/*
说明：
分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚会函数统计函数

分组函数清单：
sum(字段名)   求和函数
avg(字段名)   求平均数
max(字段名)   求最大值
min(字段名)   求最小值
count(字段名) 计算非空字段值的个数

*/

#案例一：查询员工信息表中，所有员工的工资和，工资平均值，最低工资，最高工资，有工资的个数
SELECT SUM(salary), AVG(salary), MAX(salary), MIN(salary), COUNT(salary) FROM employees;

#count的补充介绍：
#1、统计结果集的行数，推荐使用count(*)
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department_id = 30;

SELECT COUNT(1) FROM employees; #count()的括号中可以放置任意的常量值或者常量字段
SELECT COUNT(1) FROM employees WHERE department_id = 30;

SELECT COUNT('奥特曼') FROM employees; #count()的括号中可以放置任意的常量值或者常量字段
SELECT COUNT(12143) FROM employees WHERE department_id = 30;

#2、搭配distinct实现去重的统计
#需求：查询有员工的部门个数
SELECT COUNT(DISTINCT department_id) FROM employees;


#思考：每个部门的总工资与平均工资怎么计算
SELECT department_id, SUM(salary), AVG(salary) FROM employees GROUP BY department_id;


#基础六：分组查询
/*
语法：

select 查询列表
from 表名
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表


执行顺序
(1)from
(2)where
(3)group by
(4)having
(5)select
(6)order by



特点：
1）查询列表往往是  分组函数和杯分组的字段
2）分组查询中的筛选可以分为两类
				筛选的基表	使用的关键词		位置
分组前筛选			原始表		where			group by的前面	
分组后筛选			分组后的结果表	having			group by的后面

where - group by - having

*/

#1）简单的分组
#案例一：查询每个公众的员工平均工资
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id;

#案例二：查询每个领导的手下人数
SELECT COUNT(*), manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;


#2）可以实现分组前的筛选
#案例一：查询邮箱中包含a字符的每个部门的最高工资
SELECT MAX(salary) AS '最高工资', department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#案例二：查询有奖金的每个领导手下员工的平均工资
SELECT AVG(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#3）可以实现分组后的筛选
#案例一：查询那个部门的员工个数>5
SELECT department_id, COUNT(*) AS '员工个数'
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;   ####分组后的筛选####

#案例二：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id, MAX(salary) AS '最高工资'
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

#案例三：领导编号>102每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

#4）可以实现排序
#案例一：每个工种没有奖金的员工的最高工资>6000的工种编号的最高工资，按最高工资升序
SELECT job_id, MAX(salary) AS '最高工资'
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary) > 6000
ORDER BY MAX(salary) ASC;

#5）按多个字段分组
#案例一：查询每个工种每个部门的最低工资，并按最低工资降序
#工种和部门一致才能算时一组

SELECT MIN(salary) AS '最低工资', job_id, department_id
FROM employees
GROUP BY job_id, department_id;




#基础六：连接查询
/*
含义：又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1  有m行，表2有n行，结果有m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
按年代分类：sql92标准（仅仅支持内连接）   sql99标准（推荐）（支持内连接+外连接（左外和右外）+交叉连接）
按功能分类：
内连接（等值连接、非等值连接、自连接）
外连接（左外连接、右外连接、全外连接）
交叉连接
*/

SELECT * FROM boys;
SELECT * FROM beauty;


SELECT NAME,boyName FROM boys,beauty;  #笛卡尔现象


SELECT NAME,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id` = boys.`id`;



#————————————————sql92语法————————————————

#一、内连接
#一）等值连接
/*
语法：
	select 查询列表
	from 表名1，表名2，。。。
	where 等值连接的连接条件

特点：
	1、为了解决夺标中的字段名重名问题，往往为表起别名，提高语义性
	2、表的顺序无要求
	
	
*/

#（1）简单的两个表连接
USE myemployees;
#案例一：查询员工名和部门名
SELECT last_name, department_name
FROM employees, departments
WHERE employees.`department_id` = departments.`department_id`;

#（2）添加筛选条件
#案例一：查询部门编号>100的部门名和所在的城市名
SELECT department_name, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
AND d.`department_id` > 100;
#案例二：查询有奖金的员工名和部门名
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND commission_pct IS NOT NULL;
#查询城市名中第二个字符o的部门名和城市名
SELECT department_name, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
AND l.`city` LIKE '_o%';

#（3）添加分组+筛选
#案例一：查询每个城市的部门个数
SELECT COUNT(*) 部门个数, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY l.`city`;


#二）非等值连接

#案例一：查询员工的供房子和工资级别

#下列代码时进行创建员工等级表格
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  INT,
 highest_sal INT);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

#下面代码时答案
SELECT salary, grade_laval
FROM employees e, job_grades g
WHERE salary ebtween g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level` = 'A';


#案例二：筛选出员工等级为A的员工工资
SELECT salary, grade_laval
FROM employees e, job_grades g
WHERE salary ebtween g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level` = 'A';

#三）自连接（自己连接自己）

#案例一：查询员工名和上级的名

SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;



#################第五次练习###################3

#1. 显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`;

#2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id
SELECT e.job_id, l.location_id
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND d.`department_id` = 90;

#3. 选择所有有奖金的员工的last_name , department_name , location_id , city
SELECT e.last_name, d.department_name, l.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND commission_pct IS NOT NULL;


#4. 选择city在Toronto工作的员工的last_name , job_id , department_id , department_name
SELECT e.last_name, e.job_id, e.department_id, d.department_name, l.`city`
FROM employees e, departments d, locations l
WHERE d.`department_id` = e.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto';

#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.department_name, j.job_title, j.min_salary
FROM employees e, departments d, jobs j
WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY j.job_id;


#6.查询每个国家下的部门个数大于 2 的国家编号
SELECT COUNT(*), l.country_id 
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY country_id
HAVING COUNT(*) > 2;

#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
#    employees    Emp#     manager    Mgr#
#    kochhar      101      king       100

SELECT e.last_name employees, e.employee_id "Emp#", m.last_name manager, m.employee_id "Mgr#"
FROM employees e, employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = 'kochhar';


#——————————————————sql99语法——————————————————

/*
语法：
	select 查询列表
	from 表1 别名 （连接类型）
	join 表2 别名
	on 连接条件
	（where 筛选条件）
	（group by 分组）
	（having 筛选条件）
	（order by 排序列表）


连接类型分类
内连接：inner
外连接：
	左外：left (outer)
	右外：right (outer)
	全外：full (outer)
交叉连接：cross

*/


#一）内连接
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;


分类：
等值连接
非等值连接
自连接

特点：
（1）添加链接、分组筛选
（2）inner可以省略
（3）筛选条件可以放在where后面，连接条件放在on后面，提高分离性，便于阅读
（4）inner join 连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集


*/

#1、等值连接
#案例一：查询员工名、部门名（调换位置）
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`

#案例二：查询名字中包含e的员工名和工种名（筛选）
SELECT last_name, job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id` = j.job_id
WHERE e.`last_name` LIKE '%e%';


#案例三：查询部门个数>3的城市名和部门名（分组+筛选）
SELECT city, COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON l.`location_id` = d.`location_id`
GROUP BY city
HAVING COUNT(*) > 3;


#案例四：查询哪个部门的部门员工个数>3的部门名和员工个数，并按照个数降序排列
SELECT department_name, COUNT(*) 员工个数
FROM departments d
INNER JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY d.`department_id`
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;



#案例五：查询员工名、部门名、工种名，并按照部门名降序
SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN departments d ON d.`department_id` = e.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;


#二）非等值连接

#案例一：查询员工的工资级别

SELECT salary, grade_level
FROM employees e
JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;


#案例二：查询工资级别的个数>20的个数，并且按照工资级别降序
SELECT COUNT(*), grade_level
FROM employees e
JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC;


#三）自连接
#案例一：查询员工的名字、上级的名字

SELECT e.last_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id`;


#二、外连接
/*
应用场景：用于查询一个表中有，另一个表中没有的记录

特点：
1、外连接的查询结果为主表中的所有记录
	如果从表中有与它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示NULL
	外连接查询结果=内连接结果+主表中有二从表中没有的记录
2、左外连接，left join左边的时主表
   右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的结果
4、全外连接=内连接的结果+表1中有但表2中没有的+表2中有但是表1中没有的

*/

#引入：查询男朋友不在表里的女生姓名

SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.id IS NULL;

#案例以：查询那个部门没有员工
#左外连接：

SELECT d.*, e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;


#交叉连接
SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo; # 又出现了笛卡尔现象


#############第六次练习##############

#一、查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充
SELECT b.`id`,b.name, bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` > 3;

#二、查询哪个城市没有部门
SELECT city
FROM departments d
RIGHT OUTER JOIN locations l
ON l.`location_id` = d.`location_id`
WHERE d.`department_id` IS NULL;

#三、查询部门名为 SAL 或 IT 的员工信息

SELECT e.*, department_name
FROM departments d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN('SAL', 'IT');


#基础7：子查询
/*
含义：
出现在其他语句内部的select语句，称为子查询

内部嵌套其他select于江湖的查询，称为外查询或者主查询

分类：
按子查询出现的位置：
	select后面
		仅仅支持表量子查询
	from后面
		支持表子查询
	where后面或者having后面
		支持标量子查询（单行）
		    列子查询（多行）
		    行子查询
	exists后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一行多列）
	行子查询（结果集有一行列）
	表子查询（结果集一般为多行多列）

*/


#一、where或者having后面
/*
特点：
（1）子查询放在小括号内
（2）子查询一般放在条件的右侧
（3）标量子查询一般搭配着单行操作符使用
 > < >= <= <>

列子查询：一般搭配着多行操作符使用
 in any/some all
（4）子查询的执行顺序优先于主查询执行，主查询的条件用到了子查询的结果
*/
#1、标量子查询
#案例一：查询谁的工资比Abel高
SELECT *
FROM employees
WHERE salary > 
(
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

#案例二：返回job_id与141号员工相同，salary比143号员工多少我员工，姓名，job_id和工资
SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
)
AND salary >
(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

#案例三：返回工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(
	SELECT MIN(salary)
	FROM employees
);

#案例四：查询修低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);


#2、列子查询（多行子查询）
#案例一：返回location_id是1400或1700的部门中的所有员工姓名
SELECT department_id
FROM departments
WHERE location_id IN(1400, 1700);

SELECT last_name
FROM employees
WHERE department_id IN
(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400, 1700)
);

#案例二：返回其他工种中比job_id为“IT_PROG”部门任一工资低的员工的员工号、姓名、job_id以及salary

SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ANY
(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';

#3、行子查询


#二、select后面的子查询

#案例：查询每个部门的员工的个数


SELECT d.*, 
(
	SELECT COUNT(*)
	FROM employees e
	GROUP BY department_id
	# where e.department_id = d.`department_id`
) 个数
FROM departments d;


#案例二：查询员工工号=120的部门名
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` = 102;


#三、from后面的子查询
/*
将子查询结果充当一张表，要求必须起别名
*/

#案例一：查询每个部门的平均工资的工资等级

SELECT ag_dep.*, g.`grade_level`
FROM
(
	SELECT AVG(salary) 平均工资, department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.平均工资 BETWEEN g.`lowest_sal` AND g.`highest_sal`;


#四、exists后面的子查询（相关子查询）(查询结果为布尔类型（是否存在）)
/*
语法：exisits(完整的查询语句)
结果：1或者0
*/

#案例一：查询有员工名的部门名
SELECT department_name
FROM departments d
WHERE EXISTS
(
	SELECT last_name
	FROM employees e
	WHERE e.`department_id` = d.`department_id`
);

#用in来进行查询
SELECT department_name
FROM departments d
WHERE department_id IN
(
	SELECT department_id
	FROM employees
);

#案例二：查询没有女朋友的男生信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN
(
	SELECT boyfriend_id
	FROM beauty
);

#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS
(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id` = b.`boyfriend_id`
);

##########第八次练习################

#1. 查询和 Zlotkey 相同部门的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE department_id =
(
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'
);

#2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT last_name, employee_id, salary
FROM employees
WHERE salary >
(
	SELECT AVG(salary)
	FROM employees
);

#3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

SELECT employee_id, last_name, salary, e.department_id
FROM employees e
INNER JOIN 
(
	SELECT AVG(salary) AS ag, department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary > ag_dep.ag;

#4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';

SELECT employee_id, last_name
FROM employees
WHERE department_id IN 
(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
);

#5. 查询在部门的 location_id 为 1700 的部门工作的员工的员工号
SELECT department_id
FROM departments
WHERE location_id = 1700;

SELECT employee_id
FROM employees e
WHERE department_id ANY
(
	SELECT department_id
	FROM departments
	WHERE location_id = 1700
);

#6. 查询管理者是 King 的员工姓名和工资
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing';

SELECT last_name, salary
FROM employees
WHERE manager_id IN
(
	SELECT employee_id
	FROM employees
	WHERE last_name = 'K_ing'
);

#7. 查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名

SELECT MAX(salary)
FROM employees

SELECT CONCAT(first_name, ' ', last_name) '姓 名'
FROM employees
WHERE salary = 
(
	SELECT MAX(salary)
	FROM employees
);

#基础八：分页查询
/*
应用场景：
当要显示的数据，一页显示不全，需要分页显示的时候，调教sql请求

语法：
	select 查询列表
	from 表
	（join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段）
	limit offset, size;

	offset 要显示条目的起始索引（起始索引从0开始）
	size 要显示的条目个数
	
特点：
	（1）limit语句放在查询语句的最后
	（2）公式：要显示的页数：page 煤业的条目数：size
	select 查询列表
	from 表
	limit （page-1）*size, size;
*/

#案例一：千寻前五条员工信息
SELECT * FROM employees LIMIT 0, 5;
SELECT * FROM employees LIMIT 5;

#案例二：查询第十一条到第二十五条员工信息
SELECT * FROM employees LIMIT 10, 15;

#案例三：有奖金的员工信息，并且工资较高的前十名显示出来
SELECT * 
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC
LIMIT 10;



#基础九：联合查询
/*
union 联合  合并：将多条查询语句的结果合并为一个结果

语法：
查询语句1
union
查询语句2
union
......

应用场景：
要查询的结果来自于多个表，并且表没有直接的连接关系，但是查询的信息一致时

特点：
（1）要求多条查询语句的查询列数是一致的
（2）要求多条查询语句的查询的每一列的类型和顺序最好一致
（3）union关键字默认去重，如果使用union all可以包含重复项

*/

#案例一：查询员工姓名含有‘u’或者工资大于7000的员工的姓名和薪资
SELECT last_name, salary FROM employees WHERE last_name LIKE '%u%'
UNION
SELECT last_name, salary FROM employees WHERE salary > 7000;
仲子_real
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL基础学习（尚硅谷婷姐）自学笔记_001

MySQL基础学习（尚硅谷婷姐）自学笔记_001/*MySQL服务的登录与退出方式一：通过dos命令mysql -h主机名 -P端口号 -u用户名 -p密码注意：如果是本机，则-h主机名可以省略如果端口是3306，则-P端口号可以省略方式二：通过图形化界面客户端通过sqlyog，直接输入用户名、密码等链接进去MySQL的常见命令与语法规范：（1）常见命令show databases; #显示当前连接下所有数据库show tables;
复制链接

扫一扫