MySQL入门学习

数据库的好处

    1.持久存到本地  
    2.可以实现结构化查询，方便管理

数据库相关概念

    1.DB：数据库，保存一组有组织的数据的融通器  
    2.DBMS:数据库管理系统，又称数据库软件(产品),用于管理DB中的数据    
    3.SQL结构化查询语言，用于和DBMS通信的语言

数据库存储数据的特点

    1、将数据放到表中，表再放到库中
	2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
	3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
	4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
	5、表中的数据是按行存储的，每一行类似于java中的“对象”。

MySQL服务的启动和停止

    方式一：计算机——右击管理——服务
	方式二：通过管理员身份运行
	net start 服务名（启动服务）
	net stop 服务名（停止服务）

MySQL服务的登录和退出

    方式一：通过mysql自带的客户端
	只限于root用户

	方式二：通过windows自带的客户端
	登录：
	mysql 【-h主机名 -P端口号 】-u用户名 -p密码

	退出：
	exit或ctrl+C

MySQL的常见命令

    1.查看当前所有的数据库
	show databases;
	
    2.打开指定的库
	use 库名
	
    3.查看当前库的所有表
	show tables;
	
    4.查看其它库的所有表
	show tables from 库名;
	5.创建表
	create table 表名(

		列名 列类型,
		列名 列类型，
		。。。
	);
    6.查看表结构
	desc 表名;


    7.查看服务器的版本
	方式一：登录到mysql服务端
	select version();
	方式二：没有登录到mysql服务端
	mysql --version
	或
	mysql --V

MySQL的语法规范

    1.不区分大小写,但建议关键字大写，表名、列名小写
	2.每条命令最好用分号结尾
	3.每条命令根据需要，可以进行缩进 或换行
	4.注释
		单行注释：#注释文字
		单行注释：-- 注释文字
		多行注释：

SQL的语言分类

`DQL（Data Query Language）：数据查询语言
		select 
	DML(Data Manipulate Language):数据操作语言
		insert 、update、delete
	DDL（Data Define Languge）：数据定义语言
		create、drop、alter
	TCL（Transaction Control Language）：事务控制语言
		commit、rollback

SQL的常见命令

    show databases； 查看所有的数据库
	use 库名； 打开指定 的库
	show tables ; 显示库中的所有表
	show tables from 库名;显示指定库中的所有表
	create table 表名(
		字段名 字段类型,	
		字段名 字段类型
	); 创建表

	desc 表名; 查看指定表的结构
	select * from 表名;显示表中的所有数据

DQL语言的学习

进阶1：基础查询

语法：
	SELECT 要查询的东西
	【FROM 表名】;

	类似于Java中 :System.out.println(要打印的东西);
	特点：
	①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
	② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

进阶2：条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据
	语法：
	select 
		要查询的字段|表达式|常量值|函数
	from 
		表
	where 
		条件 ;

	分类：
	一、条件表达式
		示例：salary>10000
		条件运算符：
		> < >= <= = != <>
	
	二、逻辑表达式
	示例：salary>10000 && salary<20000
	
	逻辑运算符：

		and（&&）:两个条件如果同时成立，结果为true，否则为false
		or(||)：两个条件只要有一个成立，结果为true，否则为false
		not(!)：如果条件成立，则not后为false，否则为true

	三、模糊查询
	示例：last_name like "a%"

进阶3：排序查询

语法：
	select
		要查询的东西
	from
		表
	where 
		条件
	
	order by 排序的字段|表达式|函数|别名 【asc|desc】

进阶4：常见函数

一、单行函数
	1、字符函数
		concat拼接
		substr截取子串
		upper转换成大写
		lower转换成小写
		trim去前后指定的空格和字符
		ltrim去左边空格
		rtrim去右边空格
		replace替换
		lpad左填充
		rpad右填充
		instr返回子串第一次出现的索引
		length 获取字节个数
		
	2、数学函数
		round 四舍五入
		rand 随机数
		floor向下取整
		ceil向上取整
		mod取余
		truncate截断
	3、日期函数
		now当前系统日期+时间
		curdate当前系统日期
		curtime当前系统时间
		str_to_date 将字符转换成日期
		date_format将日期转换成字符
	4、流程控制函数
		if 处理双分支
		case语句 处理多分支
			情况1：处理等值判断
			情况2：处理条件判断
		
	5、其他函数
		version版本
		database当前库
		user当前连接用户



	


二、分组函数


		sum 求和
		max 最大值
		min 最小值
		avg 平均值
		count 计数
	
		特点：
		1、以上五个分组函数都忽略null值，除了count(*)
		2、sum和avg一般用于处理数值型
			max、min、count可以处理任何数据类型
	    3、都可以搭配distinct使用，用于统计去重后的结果
		4、count的参数可以支持：
			字段、*、常量值，一般放1
	
		   建议使用 count(*)

代码笔记

进阶一：基础查询

-- 进阶一：基础查询


-- 1.查询表中的单个字段
select email from employees;

-- 2.查询表中的多个字段
select last_name,first_name from employees;

-- 3.查询表中的所有字段
select * from employees;
 
-- 起别名：①便于理解 ②如果查询的字段有重名的情况，起别名可以解决
	-- 方式一：使用AS
	select email AS 邮箱 from employees;

	-- 方式二：使用空格
	select email 邮箱 from employees;
	
-- 去重
-- 案例：查询员工表中涉及到的所有部门编号
select DISTINCT department_id from employees;


-- 加号的作用  : 只有一个功能  运算符
-- 如果两个操作数都为数值，则做加法运算
select 110 + 110;  
-- 只要一方为字符型，试图将字符数值转换成数值型，如果转换成功，则继续加法运算，如果转换失败，则将字符数值转换成0
select "123" + 123; 
select "Tom" + 123; 
-- 只要其中一方为null ，则结果肯定为null
select null + 10;

-- mysql 中的拼接使用函数 concat
select CONCAT(last_name,first_name) AS 姓名  from employees;

进阶二：条件查询

-- 进阶二：条件查询



-- 按条件表达式查询
 
-- 案例1： 查询工资大于12000的员工信息
select * from employees where salary >12000

-- 案例2：查询部门编号不等于90号的员工名和部门编号
select last_name,department_id from employees where department_id !=90;
select last_name,department_id from employees where department_id <>90;


-- 按逻辑表达式筛选

-- 案例1：查询工资在10000到20000之间的员工名，工资以及奖金
select last_name,salary,commission_pct from employees where salary >=10000  and salary <= 20000;
select last_name,salary,commission_pct from employees where not(salary < 10000 or salary >20000);

-- 案例2：查询部门编号不是在90到110之间或者工资高于15000的员工信息
select * from employees where department_id < 90 or department_id > 110 or salary >15000;
select * from employees where not(department_id >=90 and department_id <=110 and salary <=15000);
select * from employees where  not(department_id >=90 and department_id <=110 ) or salary >15000;


-- 模糊查询
-- 案例1：查询员工名包含字符a的员工信息
select * from employees where last_name like "%a%";

-- 案例2：查询员工名中年第三个字符为n，第五个字符为l的员工名和工资
select last_name,salary from employees where last_name like "__n_l%";

-- 案例3：查询员工名中第二个字符为_的员工名
select * from employees where last_name like "_\_%";  -- 转义字符
select * from employees where last_name like "_@_%" escape "@"; -- 转义关键字  可以是任意字符

-- between and 使用
-- 案例1：插叙员工编号在100 到120之间的员工信息
select * from employees where department_id >=100 and department_id <= 120;
select * from employees where department_id between 100 and 120;

-- in 的使用
-- 案例1：查询员工的工种编号是 IT_PRCG,AD_VP,AD_PRES中的一个员工名和工种编号
select last_name,job_id from employees where job_id ="IT_PRCG" or job_id ="AD_VP" or job_id ="AD_PRES";
select last_name,job_id from employees where job_id in("IT_PRCG","AD_VP","AD_PRES");

-- is null的使用
-- 案例1:查询没有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct is null;

-- 案例2；查询有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct is not null;

进阶三：排序

-- 进阶三：排序----------------------------------------


-- 案例1； 查询员工信息，要求工资从高到低排序
select * from employees order by salary desc;

-- 案例2：查询部门编号>=90的员工信息，按入职时间的先后顺序排序【添加筛选条件】
select * from employees where department_id >=90  order by hiredate;

-- 案例3：按年薪的高低显示员工的信息【按表达式排序】
select * from employees order by salary * 12 * (IFNULL(commission_pct,0)+1);

-- 案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
select *,salary * 12 * (IFNULL(commission_pct,0)+1) as  年薪 from employees order by 年薪;

-- 案例5：按姓名的长度显示员工的姓和工资【按函数排序】
select length(last_name),first_name,salary from employees order by length(last_name);

-- 案例6：查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
select * from employees order by salary,department_id desc;

进阶四：常见函数

-- 进阶四：常见函数----------------------------------------



-- 单行函数
-- 1.length  获取字节长度
select length("张三123");

-- 2.concat  字符串拼接
select CONCAT(last_name,"_",first_name) 姓名 from employees;

-- 3.upper、lower 大小写转换
select UPPER("tom");
select LOWER("TOM");
-- 实例：将姓变大写，名变小写，然后拼接
select concat(upper(last_name),"_",lower(first_name)) from employees

-- 4.substr[或substring] 字符串截取
-- 截取从指定索引开始处后面所有字符
select substr("张三爱上了李四",3) out_pu; 

-- 截取从指定所有开始指定字符长度的字符
select substr("张三爱上了李四",1,4);

-- 案例：姓名中首字母大写，其它字符小写
select concat(CONCAT(upper(substr(last_name,1,1)),lower(substr(last_name,2))),"_"   ,CONCAT(UPPER(substr(first_name,1,1)),LOWER(substr(first_name,2)))) 姓名 from employees;


-- 5.instr 返回子串第一次出现的索引，如果找不到返回0
 select instr("张三爱上李四","李四");
 

-- 6.trim 去除两边空格
select trim("  aa   张三  aa  ");
select trim("a" from "aaaaaa张aaaaa三aaaaa");

-- 7.lpad 用指定的字符实现左填充指定长度
select lpad("张三",4,"*");
 
 -- 8.rpad 用指定的字符实现右填充指定长度
select rpad("张三",4,"*");
 
--  9.replace 替换
select replace("张三张三爱上了李四","张三","小红");
 
 
-- 数学函数
-- 1.round 四舍五入
select round(1.61);
select round(1.41);

-- 2.ceil 向上取整，返回>=该参数的最小整数
select ceil(1.01);
select ceil(1.0);

-- 3.floor 向下取整，返回<=该参数的最大整数
select floor(1.1);
select floor(2.0);

--  4.truncate 截断
select truncate(1.222,2);

-- 5.mod  取余
select mod(10,3);


-- 日期函数
-- 1.now 返回当前系统日期+时间
select now();
 
--  2.curdate 返回当前系统日期，不包含时间
select curdate();

-- 3.curtime 返回当前时间，不包含日期
select curtime();

-- 4.可以获取指定的不分:年、月、日、小时、分钟、秒
select year(now())  年; 
select month(now()) 月;
select date(now()) 日;
select hour(now())  小时;
select minute(now()) 分钟;
select second(now()) 秒;


-- 5.str_to_date 将字符通过指定的格式转换成日期
select STR_TO_DATE("1998-3-2","%Y-%c-%d") oup_put;


-- 6.date_fomart 将指定日期转换成字符串
select date_format(now(),"%y年%m月%d日");


-- 其它函数
select version();
select database();
select user();




-- 流程控制函数
-- 1.if函数
select if(10>5,"大","小");

-- 案例:
select last_name,commission_pct,if(commission_pct is null ,"没奖金","有奖金") 备注 from employees;


-- 2.case函数的使用一


-- 案例：查询员工的工资要求 
-- 部门号= 30，显示的工资为1.1倍
-- 部门号= 40，显示的工资为1.2倍
-- 部门号= 50，显示的工资为1.3倍
-- 其它部门，显示的工资为元工资

select salary 原工资,department_id, 
case department_id
when 30 then salary * 1.1
when 40 then salary * 1.2
when 50 then salary * 1.3
else salary  
end 新工资
from employees;



-- 2.case函数的使用二

-- 案例：查询员工的工资情况
-- 如果工资>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 工资等级
from employees;



-- 分组函数
-- 分类： sun求和、  avg平均值、   max最大值、  min 最小值 count计算个数
-- 1.简单实用
select sum(salary) from employees;
select avg(salary) from employees;
select max(salary) from employees;
select min(salary) from employees;
select count(salary) from employees;

进阶五：分组查询

-- 进阶五：分组查询----------------------------------------


-- 简单的分组查询
-- 案例1：查询每个工种的最高工资
select MAX(salary),job_id from employees
group by job_id;

-- 案例2：查询每个位置上的部门个数
select count(*), location_id from departments
group by location_id;

-- 案例3：查询邮箱中包含a字符的，每个部门的平均工资
select  avg(salary),department_id from employees
where email like "%a%"
group by department_id;


-- 案例4：查询有奖金的每个领导手下员工的最高工资
select max(salary),manager_id from employees
where commission_pct is not null
group by manager_id;

-- 案例5：哪个部门的员工个数>2
select count(*),department_id from employees
group by department_id
having count(*)>2;

-- 案例6：查询每个工种有奖金的员工最高工资>12000的工种编号和最高工资
select max(salary),job_id from  employees
where commission_pct is not null
group by job_id
having max(salary) >12000;

-- 案例7：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
 select manager_id ,min(salary) from employees
 where manager_id >102
 group by manager_id
 having min(salary) > 5000;

进阶六：连接查询

-- 进阶六：连接查询----------------------------------------
 
 
--  一、sql92标准--------------
--  1、等值连接

 
--  案例1：查询员工名和对应的部门名
 
 select last_name,department_name from employees ,departments 
 where employees.employee_id = departments.department_id;
 
 
--  为表起别名： ①提高了语句的简介度 ②区分多个重名字段
-- 		注意：如果为表起了别名，则查询的字段就补鞥呢使用原来的表名去限定
--  案例2：查询员工名、工种号、工种名
select last_name,e.job_id,job_title from employees e,jobs j
where e.job_id = j.job_id;

 
--  案例3：查询有奖金的员工名，部门名【加筛选】
select last_name ,e.commission_pct,d.department_name from  employees e,departments d
where e.department_id = e.department_id  
and e.commission_pct is not null;

-- 案例4：查询城市名中第二个字符为o的部门名称和城市名【加筛选】

select d.department_name,l.city from departments d, locations l
where d.location_id = l.location_id
and l.city like "_o%";

-- 案例5：查询每个城市的部门个数【加分组】
select count(*),city from departments d,locations l
where d.location_id = l.location_id
GROUP BY city;

-- 案例6：查询有奖金的每个部门的部门名和部门的领导编号和该部门的低工资
select d.department_name,d.manager_id,min(salary) from employees e,departments d
where e.department_id = d.department_id
and commission_pct is not null
GROUP BY d.department_name,d.manager_id;
 
-- 案例7：查询每个工种的工种名和员工的个数，并按员工个数降序
select count(*),j.job_title from employees e,jobs j
where e.job_id = j.job_id
group by j.job_title
order by count(*) desc;


-- 三表联查
-- 案例：查询员工名、部门名和所在的城市
select e.last_name,d.department_name,l.city from employees e,departments d,locations l
where e.employee_id = d.department_id and d.location_id =l.location_id;



-- 二、非等值连接
-- 案例1：查询员工的工资和工资级别
select salary,grade_level from employees e,job_grades j
where salary between j.lowest_sal and j.highest_sal;


-- 三、自连接
-- 案例：查询员工名和上级的名称																																								-- as 员工表			-- as 领导表
select e.last_name as 员工名,e.manager_id as 上级编号,d.employee_id as 上级员工编号,d.last_name  as 上级名称 from employees e ,employees d 
where e.manager_id = d.employee_id;



-- 二、sql99语法


-- 等值连接
-- 案例1；查询员工名、部门名（调换位置）
select last_name,department_name from employees e
inner join departments d
on e.department_id = d.department_id;

-- 案例2：查询名字包含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：查询部门个数>3的城市名和部门个数(分组+筛选)
select count(*),city from departments d
inner join locations l
on d.location_id = l.location_id
group by city
having count(*) >3;

-- 案例4：查询那个部门的部门员工个数>3的部门名和员工个数，并按个数降序(排序)
select count(*) ,department_name from employees e
inner join departments d
on e.department_id = d.department_id
GROUP BY d.department_name
having count(*) >3
order by count(*) desc;

-- 案例5：查询员工名、部门名、工种名，并按部门名降序

select last_name,department_name,job_title from employees e
inner join departments d on e.department_id = d.department_id
inner join jobs j on e.job_id = j.job_id
order by d.department_name desc;


-- 二、非等值连接
-- 案例1：查询员工的工资级别
select salary,grade_level from employees e
inner join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;

-- 案例2：查询工资级别的个数>20的个数，并按工资级别降序
select count(*),salary,grade_level from  employees e
inner join job_grades j
on e.salary between j.lowest_sal and j.highest_sal
group by grade_level
having count(*) >20
order by grade_level desc;


-- 三、自连接
-- 案例：查询员工的名字、上级的名字
select e.last_name 员工名字,e.manager_id as 上级编号,m.employee_id as 上级员工编号,m.last_name as 上级名字 from employees e
inner join employees m
on e.manager_id =m.employee_id;



-- 四、外连接




-- 案例：查询哪个部门没有员工

-- 左外
select d.department_name,e.employee_id from departments d
left outer join employees e
on d.department_id =e.department_id
where e.employee_id is null;

-- 右外
select d.department_name,e.employee_id from  employees e
right outer join departments d
on e.department_id =d.department_id
where e.employee_id is null;

进阶7：子查询

-- 进阶7：子查询------------------------------------------



-- 一、where或having后面



-- 案例1：谁的工资比Abel高？

-- ①查询Abel的工资
select salary from employees
where last_name ="Abel";

-- ②查询员工的工资，满足salary①结果
select * from employees
where salary >(select salary from employees
where last_name ="Abel");

-- 案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

-- ①查询job_id与141号员工相同
select job_id from employees where employee_id=141;

-- ②查询salary比143号员工多的员工
select salary from employees where employee_id =143;

-- ③查询员工的 姓名，job_id 和工资 要求job_id = ① 并且salary>②
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 min(salary) from employees;

-- ②查询工资=①的员工的last_name,job_id和salary
select last_name,job_id,salary from employees
where salary = (
select min(salary) from employees);



-- 案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

-- ①查询50号部门最低工资
select min(salary) from employees 
where department_id = 50;

-- ②查询每个部门的最低工资
select min(salary),department_id from employees
group by department_id

-- ③筛选② 最低salary >①50号部门最低salary
select min(salary),department_id from employees
group by department_id
having min(salary) >(select min(salary) from employees 
where department_id = 50);

-- 列子查询(多行子查询)
-- 案例1；返回location_id是1400或1700的部门中的所有员工姓名

-- ①查询location_id是1400或1700的部门编号
select distinct 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));


-- 案列2：返回其它工种中比job_id为‘IT_PROG’部门任意工资低的员工的；工号、姓名、job_id、salary

-- ①查询job_id为‘IT_PROG’部门任意工资
select distinct salary from employees
where job_id ="IT_PROG";

-- ②查询员工的；工号、姓名、job_id、salary, salary<①的任意一个
select last_name,job_id,salary from employees
where salary < any(select distinct salary from employees
where job_id ="IT_PROG")   and  job_id <> "IT_PROG";

-- 案例3：返回其它工种中比job_id为‘IT_PROG’部门所有工资低的员工的；工号、姓名、job_id、salary

select last_name,job_id,salary from employees
where salary < all(select distinct salary from employees
where job_id ="IT_PROG")   and  job_id <> "IT_PROG";


-- 二、select后面
-- 案例1；查询每个部门的员工个数
select d.* ,(
select count(*) from employees e
where e.department_id = d.department_id)
from departments d;


-- 案例2；查询员工号=102的部门
select (select department_name from departments d
inner join employees e
on d.department_id =e.department_id
where e.employee_id =102) as 部门名;


-- 三、from后面

-- 案例：查询每个部门的平均工资的工资等级
select em.*,j.grade_level 
from (
			select avg(salary) as ag,department_id 
			from employees
				group by department_id) as em
inner join  job_grades j
on em.ag between j.lowest_sal and j.highest_sal;


-- 四、exists后面(相关子查询)


select exists(select employee_id from employees);


-- 子查询经典测试题
-- 查询工资最低的员工信息：last_name,salary
-- ①查询最低工资
select min(salary) from employees;
-- ②查询员工的信息：last_name,salary，并且salary=①
select last_name ,salary from employees
where salary =(select min(salary) from employees);


-- 查询平均工资最低的部门信息

#方式一
-- ①查询各部门的平均工资
 select avg(salary),department_id from employees
 group by department_id;

-- ②查询①结果上的最低平均工资
select min(ag) from 
( select avg(salary) as ag,department_id from employees 
 group by department_id) as emps;
 
--  ③查询各部门的平均工资，并且salary =②  得到department_id
 select avg(salary),department_id from employees
 group by department_id
 having avg(salary) = (select min(ag) from 
( select avg(salary) as ag,department_id from employees 
 group by department_id) as emps);
 
--  ④根据③得到的部门最低平均工资和id  关联表查询部门信息
 select * from departments
 where department_id = ( select department_id from employees
 group by department_id
 having avg(salary) = (select min(ag) from 
( select avg(salary) as ag,department_id from employees 
 group by department_id) as emps));
 
 
 
 #方式二
 -- ①查询各部门的平均工资并对salary进行升序,然后用limit查询第一条
 select avg(salary),department_id from employees
 group by department_id
 order by avg(salary)
 limit 1;
 
--  ②根据①查询部门信息
 select * from departments
 where department_id =(select department_id from employees
 group by department_id
 order by avg(salary)
 limit 1);

-- 查询平均工资最低的部门信息和该部门的平均工资
 -- ①查询各部门的平均工资并对salary进行升序,然后用limit查询第一条
 select avg(salary),department_id from employees
 group by department_id
 order by avg(salary)
 limit 1;
 
 
 --  ②根据①查询部门信息
 select * from departments
 where department_id =(select department_id from employees
 group by department_id
 order by avg(salary)
 limit 1);
 
--  ③在②的基础上把①当做一个字段查询
 select d.*,( select avg(salary)from employees
 group by department_id
 order by avg(salary)
 limit 1) as 平均工资  from departments d
 where department_id =(select department_id from employees
 group by department_id
 order by avg(salary)
 limit 1);

-- 查询平均工资最高的job信息
-- ①查询最高的平均工资
 select avg(salary),job_id from employees
 group by job_id
 order by avg(salary) desc
 limit 1;
 
--  ②查询job信息  job_id = ①的job_id
select * from jobs j
where j.job_id =( select job_id from employees
 group by job_id
 order by avg(salary) desc
 limit 1);
-- 查询平均工资高于公司平均的部门有哪些
-- ①查询部门的平均工资
 select avg(salary),department_id from employees
 group by department_id;
 
--  ②查询公司的平均工资
select avg(salary) from employees;

-- ③筛选①的结果集，满足平局工资>②
 select avg(salary),department_id from employees
 group by department_id
 having avg(salary) > (select avg(salary) from employees);

-- 查询出公司所有manager的详细信息
-- ①查询所有的manager_id
select distinct manager_id  from employees;

-- ②查询详细信息，满足manager_id=①
select * from employees
where manager_id =any(select distinct manager_id  from employees);

-- 各个部门中 最高工资中最低的那个部门的最低工资是多少 
-- 方式一
select MAX(salary),department_id from employees e
group by department_id
order by  MAX(salary) 
limit 1;

-- 查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary
-- 方式一；
select  avg(salary),e.* from  employees e
group by department_id
order by avg(salary) desc
limit 1;

进阶8：分页查询

-- 进阶8:分页查询----------------------------------


-- 案例1：查询前五条员工信息
select * from employees
limit 0,5;

select * from employees
limit 5;


-- 案例2:查询第11条---第25条
select * from employees
limit 10,15;


-- 案例3：有奖金的员工信息，并且工资较高的前10名显示出来
select * from employees
where commission_pct is not null
limit 10;

进阶9：联合查询

-- 进阶9：联合查询--------------------------------------



-- 引入案例：查询部门编号>90或邮箱包含a的员工信息
select * from employees
where department_id >90 or email like "%a%";


select * from employees where department_id >90
union
select * from employees where  email like "%a%";

进阶10：DML语言

-- 一、插入语句


-- 1.插入的值类型要与列的类型一致或兼容
desc beauty;
select * from beauty;
insert into beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id)
values(13,"张三","男","1992-12-12","11111111",null,1);

-- 2.不可以为null的列必须插入值，可以为null的列如何插入值？
desc beauty;
-- 方式一；
insert into beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id)
values(15,"王五","男","1992-12-12","11111111",null,1);


-- 方式二：语序为空的字段可以不加
select * from beauty;
insert into beauty(id,`name`,sex,borndate,phone,boyfriend_id)
values(14,"李四","女","1997-12-12","11111111",2);

-- 3.列的顺序是否可以调换? 可以
insert into beauty(`name`,sex,id,phone) values("赵六","男",16,"120");

-- 4.列数和个数必须一致
insert into beauty(`name`,sex,id,phone) values("李七","男",17);
-- 5.可以省略列名，默认所有列，而且列的顺序和表的顺序一致
insert into beauty values(18,"张三","男","1992-12-12","11111111",null,1);


-- 方式二；

insert into beauty
set id =19,`name` ="小红",phone="119";

-- 两种方式比较：

-- 1.方式一支持插入多行，方式二不支持
insert into beauty 
values(21,"张三","男","1992-12-12","11111111",null,1),
(22,"张三","男","1992-12-12","11111111",null,1),
(23,"张三","男","1992-12-12","11111111",null,1);


-- 2.方式一支持子查询，方式二不支持
inser into beauty(id,`name`,phone)
select id,boyName,"110" from boys
where id <3;  -- 执行不成功原有 两张表有id重复


-- 二、修改语句


-- 案例1：修改beauty表中姓张的电话为"123";
select * from beauty;
update beauty
set phone="119"
where  `name` like "张%";
-- 案例2：修改boys表中的id号为2的名称为张飞，魅力值10;
update boys
set boyName ="张飞",userCP=10
where id =2;


-- 修改多表记录
-- 案例1：修改张无忌的女朋友的手机号为114
update boys bo
inner join beauty be
on bo.id=be.boyfriend_id
set phone="114"
where bo.boyName="张无忌";

-- 案例2：修改没有男朋友的女神的男朋友编号都为2
select * from beauty;

update beauty be
left join boys bo
on be.boyfriend_id =bo.id
set be.boyfriend_id=2
where be.id is null;



-- 三、删除语句


-- 单表的删除
-- 案例：删除手机号以9结尾的女神信息
delete from beauty
where phone like "%9";



-- 多表的删除
-- 案例：删除张无忌的女朋友的信息
delete be from beauty be
inner join boys bo
on be.boyfriend_id =bo.id




-- delete 和 truncate比较

进阶11：DDL语言

-- DDL  


-- 一、库的管理
-- 1.库的创建

-- 案例：创建库books;
create database if not exists books ;

-- 库的修改
alter database books character set gbk;


-- 库的删除
drop database if exists books;


-- 
-- 二、表的管理
-- 1、表的创建★



-- 案例：创建book表
create table book(
				id int,  #编号
				bName varchar(20), #书名
				price double,  #价格
				anthorId int,  #作者id
				publiishDate datetime #出版日期
);
desc bool;

-- 案例：创建表author
create table author(
				it int,
				au_name varchar(20),
				nation varchar(20)
);



-- 2.表的修改


-- ①修改列名
alter table book change column publiishDate pubDate  datetime;

-- ②修改列的类型或约束
alter table book modify column pubDate  double;

-- ③添加新列
alter table book add column annual double;

-- ④删除列
alter table book drop column annual;

-- ⑤修改表名
alter table author rename to book_another;


-- 3.表的删除
drop table book_another;
show tables;



-- 4.表的复制

-- 1.只复制表的结构
create table copy1 like employees;

-- 2.复制表的结构和数据
create table copy2
select * from employees;

select * from copy2;


-- 3.只复制部分数据
create table copy3
select * from employees
 limit 3;
 
 
 

常见的数据类型

-- 常见的数据类型


-- 一、整型

创建约束

-- 常见约束

TCL（事务控制语言）

-- TCL



drop table if exists account;

create table account(
				id int primary key auto_increment,
				username varchar(20),
				balance double
);

insert into account(username,balance)
values("张三",1000),("李四",1000);


-- 简单演示事务的使用步骤
-- 开启事务
set autocommit=0;
start transaction; -- 可选

-- 编写一组事务的语句
update account set balance="100" where username="张三";
update account set balance="1100" where username="李四";

-- 结束事务
rollback; -- 回滚

commit; -- 提交


select * from account;



	
	

视图

	
	
-- 视图

-- 一、创建视图



-- 1.查询姓名中包含a字符的员工姓名、部门名和工种信息
-- ①创建
create view myv1
as
select last_name,department_name,job_title from employees e
inner join departments d on e.department_id=d.department_id
inner join jobs j on e.job_id=j.job_id;

-- ②使用
select * from myv1 where last_name like "%a%";



-- 2.查询各部门的平均工资级别
-- ①创建视图查看每个部门的平均工资
create view myv2
as
select avg(salary) ag ,department_id from employees
group by department_id;

-- ②使用
select myv2.ag,g.grade_level from myv2
inner join job_grades g
on ag between g.lowest_sal and g.highest_sal;



-- 二、视图的修改
-- 方式一


create or replace view myv3
as
select avg(salary),job_id from employees
group by job_id;



-- 方式二；

alter view myv3
as
select * from employees;

select * from myv3;


-- 三、删除视图

drop view myv1,myv2;


-- 四、查看视图
desc myv3;
show create view myv3;

存储过程

-- 存储过程



-- 1.空参列表
-- 案例:插入到 admin 表中五条记录
DELIMITER $
create procedure myp1()
begin 
insert into jobs values("1111","11111",1111,1111),
("2222","2222",222,112211),
("3333","333",333,333);
end $


-- 调用
call myp1() $





-- 2.创建带in模式参数的存储过程


create procedure myp2(in beautyName  varchar(20))
begin 
				select bo.* from boys bo
				right join beauty b on bo.id=b.boyfriend_id
				where b.`name`=beautyName;
end $


-- 调用
call myp2("柳岩")

函数

-- 函数





-- 1、无参有返回
-- 案例：返回公司的员工个数
CREATE FUNCTION myf1() returns int
BEGIN
    declare c int default 0;  -- 定义局部变量
		select count(*) into c  -- 赋值
		from employees;
		return c;

END

-- 调用函数
select myf1()



-- 2.有参返回
-- 案例：根据员工名，返回他的工资

create function myf2(emoName varchar(20)) returns double 
begin
			set @sal=0; -- 定义用户变量
			select salary into  @sal  -- 赋值
			from employees
			where last_name =emoName;
			
			return @sal;
			
end


-- 调用函数
select myf2("kochhar")