mysql学习（基本的增删改查）

最新推荐文章于 2023-07-01 18:02:47 发布

程序员小赵OvO

最新推荐文章于 2023-07-01 18:02:47 发布

阅读量303

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/h13245/article/details/104789978

版权

MYSQL学习

数据库的好处
1.持久化数据到本地
2.可以实现结构化查询，方便管理

数据库的概念
DB
数据库（database）：存储数据的“仓库”。他保存了一系列有组织的数据
DBMS
数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器
常见的数据库管理系统：MYSQL，Oracle，DB2，SqlServer
SQL
结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言

SQL的优点：
1.不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL
2.简单易学
3.虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

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

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

MySQL服务的登陆和退出
方式一：通过mysql自带的客户端
只限于root用户
方式二：通过windows自带的客户端
登陆：mysql 【-h主机名 -p端口号】 -u用户名 -p密码
退出：exit

MySQL的常见命令
1.查看当前所有的数据库：show databases
2.打开指定的库：use 库名
3.查看当前的库的所有表：show tables from 表名
4.查看其他库的所有表：show tablesfrom 库名
5.创建表 creat table 表名（
列名列类型，
列名列类型
）
6.查看表结构：desc 表名
7.查看服务器的版本：
方式一：登陆到mysql服务器：select version()
方式二：没有登录到mysql服务端：mysql --version或者mysql --V

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

进阶1：基础查询

语法：
select查询列表from表名
特点：
1.查询列表可以是：表中的字段，常量值，表达式，函数
2.查询的结果是一个虚拟的表格

查询表中的单个字段

use day14;
#1.查询表中的单个字段
SELECT
last_name
FROM
employees;
#2.查询表中的多个字段
SELECT
last_name,
salary,
email
FROM
employees;
#3.查询表中的所有字段
#方式一：
SELECT
employees.employee_id,
employees.first_name,
employees.last_name,
employees.email,
employees.phone_number,
employees.job_id,
employees.salary,
employees.commission_pct,
employees.manager_id,
employees.department_id,
employees.hiredate
FROM
employees;
#方式二：
SELECT
*
FROM
employees;
#4.查询常量值
SELECT
100;
SELECT
‘john’;
#5.查询表达式
SELECT
100 % 98;
#6.查询函数
SELECT VERSION();
#7.起别名
#方式一：
SELECT 9999 AS number;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二：
SELECT 8888 number;
SELECT last_name 姓,first_name 名 FROM employees;
#案例：查询salary，显示结果为out put
SELECT salary AS ‘out put’ FROM employees;
#8.去重
#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id from employees;
#9.+号的作用
java中的+号：
1运算符：两个操作数都为数值型
2连接符：只要有一个操作数为字符串
mysql中的+号：
仅仅只有一个功能：运算符
/*select 100+90;
两个操作数都为数值型，则做加法运算
select ‘123’+90;
其中一方为字符型，试图将字符串数值转换成数值型。
如果转换成功，则继续做加法运算，
如果转换失败，则将字符型数值转换为0
select null+10;只要其中一方为null，则结果肯定为null
*/
#案例：查询员工名和姓连接成一个字段，并现实为姓名
SELECT CONCAT(last_name,first_name)AS 姓名 FROM employees;

#小练习
– 1.显示表departments的结构，并查询其中的全部数据
DESC departments;
SELECT * FROM departments;
– 2.显示出表employees中的全部job_id(不能重复)
SELECT DISTINCT job_id FROM employees;
– 3，显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
SELECT CONCAT(employees.employee_id,IFNULL(employees.commission_pct,0),employees.last_name) AS OUT_PUT FROM employees;

#进阶2：条件查询
语法：select 查询列表 from 表名 where 筛选条件
分类
1.按条件表达式筛选
条件运算符：> < = != <> >= <=
2.按逻辑表达式筛选
逻辑运算符：&& || ！and or NOT
作用：连接条件表达式
3.模糊查询
like between and in is NULL

#一、按条件表达式筛选
#案例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;
#案例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) OR salary>15000;

#三、模糊查询
– like
– 特点：
– 一般和通配符搭配使用
– 通配符：
– %任意多个字符，包含0个字符
– _任意单个字符
– between and
– 使用between and可以提高代码简洁度
– 包含临界值
– 两个临界值不要调换顺序
– in
– 使用in提高语句简洁度
– in列表的值类型必须一致或兼容
– is null is not null
– =或<>不能用于判断null值
– is null或is not null可以判断null值

#1.LIKE
#案例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 last_name FROM employees where last_name LIKE ‘_%’;
SELECT last_name FROM employees where last_name LIKE ' $KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '$ ’;

#2.BETWEEN AND
#案例1：查询员工编号在100到120之间的所有的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

#4.IN
#案例：查询员工的工种编号是 IT_PROT，AD_VP，AD_PRES中的一个员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id IN(“IT_PROT”,“AD_VP”,“AD_PRES”);
SELECT last_name,job_id FROM employees WHERE job_id=“IT_PROT” OR job_id=“AD_VP” OR job_id=“AD_PRES”;

#4、is null
#案例1：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;

#安全等于<=>
#案例1：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct<=>NULL;
#案例2：查询工资为12000的员工信息
SELECT last_name,salary FROM employees WHERE salary<=>12000;

#is null pk <=>
is null:仅仅可以判断null值，可读性较高，建议使用
<=>:既可以判断null值，又可以判断普通的数值，可读性较低

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

#小练习
– 一、查询没有奖金，且工资小于18000的salary，last_name
SELECT salary,last_name FROM employees WHERE commission_pct IS NULL && salary<18000;
– 二、查询employees表中，job_id不为‘IT’或者工资为12000的员工信息
SELECT * FROM employees WHERE job_id <>‘IT’ OR salary=12000;
– 三、查看部门departments表的结构
DESC departments;
– 四、查询部门departments表中涉及到了那些位置编号
SELECT DISTINCT location_id FROM departments;
– 五、select * from employees;和select * fromemployees where commission_pct like ‘%%’ and last_name like ‘%%’;
– 结果是否一样？
不一样，如果判断的字段有null值

#进阶3：排序查询
– 语法：SELECT 查询列表 FROM 表【WHERE 筛选条件】 ORDER BY 排序列表【ASC|DESC】
– 特点：1.asc代表的是升序，desc代表的是降序。如果不写默认是升序
– 2.order by子句中可以支持单个字段，多个字段，表达式，函数，别名
– 3.order by子句一般是放在查询语句的后面，limit子句除外
#案例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;
#案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT ,salary12*(1+IFNULL(commission_pct,0))AS 年薪 FROM employees ORDER BY salary12(1+IFNULL(commission_pct,0)) desc;
#案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT ,salary12*(1+IFNULL(commission_pct,0))AS 年薪 FROM employees ORDER BY 年薪 desc;
#案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) 字节长度,last_name,salary FROM employees ORDER BY 字节长度 DESC;
#案例6：查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

#小练习
– 查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序
SELECT last_name,department_id,salary12(1+IFNULL(commission_pct,0))AS 年薪 FROM employees ORDER BY 年薪 DESC,last_name ASC;
– 选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary FROM employees WHERE !(salary>=8000 &&salary<=17000) ORDER BY salary DESC;
– 查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *,LENGTH(email) FROM employees WHERE email LIKE ‘%e%’ ORDER BY LENGTH(email) DESC,department_id ASC;

#进阶4：常见函数
– 好处：1.隐藏了实现细节2.提高代码的重用性
– 调用：select 函数名（实参列表）【from 表】
– 特点：
– 1.叫什么
– 2.干什么
– 分类：
– 1.单行函数：concat length ifnull
– 2.分组函数：
– 功能：统计使用，又称为统计函数，聚合函数，组函数

#一、字符函数
#1.length 获取参数值的字节个数
SELECT LENGTH(‘john’);
SELECT LENGTH(‘张三丰hahaha’);
#2.concat拼接字符串
SELECT CONCAT(last_name,’’,first_name) 姓名 FROM employees;
#3.upper、lower
SELECT UPPER(‘john’);
SELECT LOWER(‘JOHN’);
#实例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
#4.substr、substring
SELECT SUBSTR(‘李莫愁爱上陆展元’,6) out_put;
SELECT SUBSTR(‘李莫愁爱上陆展元’,1,3) out_put;
#案例：姓名中首字符大写，其他字符小写然后用_拼接，然后显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),’’,LOWER(SUBSTR(last_name,2))) FROM employees;
#5.instr
SELECT INSTR(‘杨不悔爱上殷六侠’,‘殷六侠’)AS out_put ;
#6.trim
SELECT TRIM(’ 张翠三 ‘) AS out_put;
SELECT TRIM(‘a’FROM’aaa张aaaa翠山aaaa’) as out_put;
#7.lpad用指定的字符实现左填充指定长度
SELECT LPAD(‘殷素素’,10,’’) AS out_put;
#7.rpad用指定的字符实现左填充指定长度
SELECT RPAD(‘殷素素’,10,’’) AS out_put;
#9.replace 替换
SELECT REPLACE(‘张无忌爱上了周芷若周芷若’,‘周芷若’,‘赵敏’) AS out_put;

#二、数学函数
#round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(-1.567,2);
#ceil 向上取整
SELECT CEIL(1.00);
#floor向下取整
SELECT FLOOR(-9.99);
#TRUNCATE 截断
SELECT TRUNCATE(1.6999,1);
#MOD取余
SELECT MOD(-10,-3);
SELECT 10%3;

#三、日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 范湖当前系统日期，不包含时间
SELECT CURDATE();
#curtime 返回当前时间，不包含日期
SELECT CURTIME();
#可以获取指定的部分，年，月，日，小时，分钟，秒
SELECT YEAR(NOW());
SELECT YEAR(‘1998-1-1’);
SELECT YEAR(hiredate) FROM employees;
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE(‘1998-3-2’,’%Y-%c-%d’) AS out_put;
#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate=‘1992-4-3’;
SELECT * FROM employees WHERE hiredate=STR_TO_DATE(‘4-3 1992’,’%c-%d %Y’);
#date_format 将诶器转换成字符
SELECT DATE_FORMAT(NOW(),’%y年%m月%d日’) AS out_put;
#查询有奖金的员工名和入职日期（xx年xx月 xx日）
SELECT last_name,DATE_FORMAT(hiredate,’%y年%m月 %d日’) AS 入职日期 FROM employees WHERE commission_pct IS NOT NULL;

#四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

#五、流程控制函数
#1.if函数：if else 的效果
SELECT IF(10>5,‘big’,‘small’);
SELECT IF(commission_pct IS NULL,‘无奖金’,‘有奖金’)备注 FROM employees;

#2.case函数的使用一：switch case 的效果
– case 要判断的字段或表达式
– when 常量1 then 值1或语句1
– when 常量2 then 值2或语句2
– …
– else 要显示的值n或语句n
– end
#案例：查询员工的工资，要求
– 部门号=30，显示的员工为1.1倍
– 部门号=40，显示的工资为1.2倍
– 部门号=50，显示的工资为1.3倍
– 其他部门，显示的工资为原工资

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN
salary1.1
WHEN 40 THEN
salary1.2
WHEN 50 THEN
salary*1.3
ELSE
salary
END AS 新工资 FROM employees;

#3.case 函数的使用二：类似于多重if
– case
– when 条件1 then 要显示的值1或语句1
– when 条件2 then 要显示的值2或语句2
– …
– else 要显示的值n或语句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 工资级别
FROM employees;

#小测试
#1.显示统计时间（注：日期+时间）
SELECT NOW();
#2.查询员工号，姓名，工资，以及工资提高百分之20的结果
SELECT employee_id,last_name,salary,salary*1.2 ‘new salary’ FROM employees;
#3.将员工的姓名按首字母排序，并写出姓名的长度
SELECT *,LENGTH(last_name),SUBSTR(last_name,1,1) FROM employees ORDER BY SUBSTR(last_name,1,1) desc;
#4.使用case-when，按照下面的条件：
– job grade
– AD_PRES A
– ST_MAN B
– IT_PROG C
SELECT
job_id,
CASE
job_id
WHEN ‘AD_PRES’ THEN
‘A’
WHEN ‘ST_MAN’ THEN
‘B’
WHEN ‘IT_PROG’ THEN
‘C’
END AS grade
FROM
employees;

#二、分组函数
– 功能：用作统计使用，有称为聚合函数或统计函数或组函数
– 分类：
– sum求和、avg平均值、max最大值、min最小值、count计算个数
– 特点：
– 1.sum、avg一般用于处理数值型
– max、min、count可以处理任何类型
– 2.以上分组函数都忽略null值

#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;
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary) FROM employees;
#2、参数支持哪些类型
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT COUNT(last_name) FROM employees;
#3、忽略null
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;

#4、和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

#5、count函数的详细
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;

#小练习
– 1.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),ROUND(AVG(salary),2),SUM(salary) FROM employees;
– 2.查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;
– 3.查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE department_id=90;

#进阶5：分组查询
– 语法：
– 特点：数据源位置关键字
– 分组前筛选原始表 group by子句之前 WHERE
– 分组后筛选分组后的结果集 group by子句之后 HAVING
– GROUP BY子句支持单个字段分组，也可以支持多个字段分组
select 分组函数，列 from 表 where 筛选条件 group by 分组的列表 order by 子句
#案例1：查询每个工种的最高工资
SELECT MAX(salary)工资,job_id FROM employees GROUP BY job_id ORDER BY 工资 desc;
#案例2：查询每个位置上的部门个数
SELECT COUNT(),location_id FROM departments GROUP BY location_id;
#添加筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary) ,department_id FROM employees WHERE email LIKE ‘%a%’ GROUP BY department_id ORDER BY AVG(salary);
#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
#添加复杂的筛选条件
#案例1：查询哪个部门的员工个数>2,分组后
SELECT COUNT(),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
#案例2：查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT job_id,MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;
#案例3：查询领导编号>102的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资
SELECT manager_id,MIN(salary) FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;

#按表达式或函数分组
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT()>5;

#按多个字段分组
#案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id from employees GROUP BY department_id,job_id;

#小练习
– 1.查询员工最高工资和最低工资的差距
SELECT MAX(salary)-MIN(salary) FROM employees;
– 2.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id HAVING MIN(salary)>6000;
– 3.查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT department_id,COUNT(),AVG(salary) FROM employees GROUP BY department_id;
– 4.选择具有各个job_id的员工人数
SELECT COUNT(),job_id FROM employees GROUP BY job_id;
– 5.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id FROM employees GROUP BY job_id ORDER BY job_id;

#进阶6：连接查询
– 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
SELECT * FROM beauty;
SELECT * FROM boys;
– 笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
SELECT name,boyName FROM beauty,boys;
– 发生原因：没有有效的连接条件
– 如何避免：添加有效的连接条件
SELECT name,boyName from beauty,boys WHERE beauty.boyfriend_id=boys.id;
– 分类：
– 按年代分类：
– sql92标准：仅仅支持内连接
– sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
– 按功能分类：
– 内连接：
– 等值连接
– 非等值连接
– 自连接
– 外连接：
– 左外连接
– 右外连接
– 全外连接
– 交叉连接

#一、sql92标准
#1.等值连接
– 1.多表等职连接的结果为多表的交集部分
– 2.n表连接，至少需要n-1个连接条件
– 3.多表的顺序没有要求
– 4.一般需要为表起别名
– 5.可以搭配前面介绍的所有子句使用，比如排序，分组，筛选
#案例1：查询女神名和对应的男神名
select beauty.name,boys.boyName FROM beauty,boys WHERE beauty.boyfriend_id=boys.id;
#案例2：查询员工名和对应的部门名
SELECT last_name,department_name FROM employees,departments WHERE employees.department_id=departments.department_id;
#2.查询工种号和员工名和工种名
SELECT last_name,employees.job_id,job_title FROM employees,jobs WHERE employees.job_id=jobs.job_id;
#3.表的顺序可以调换

#4.可以加筛选
#案例：查询有奖金的员工名和部门名
SELECT last_name,department_name,commission_pct FROM employees,departments WHERE employees.department_id=departments.department_id AND commission_pct is NOT NULL;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT * FROM locations;
SELECT * FROM departments;
SELECT d.department_name,l.city FROM locations l,departments d WHERE d.location_id=l.location_id AND l.city LIKE ‘_o%’ ;
#5.分组
#案例1：查询每个城市的部门个数
SELECT COUNT(),l.city FROM locations l,departments d WHERE l.location_id=d.location_id GROUP BY l.location_id;
#案例2：查询有奖金的每个部门的部门名和部门的领导和该部门的最低工资
SELECT MIN(e.salary),department_name,d.manager_id FROM departments d,employees e WHERE d.department_id=e.department_id AND commission_pct IS NOT NULL GROUP BY d.department_id,d.manager_id;
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT COUNT(),job_title FROM jobs,employees WHERE employees.job_id=jobs.job_id GROUP BY employees.job_id ORDER BY COUNT(*) DESC;
#7.三表连接
#案例：查询员工名，部门名和所在的城市
SELECT last_name,department_name,city FROM employees e,locations l,departments d WHERE e.department_id=d.department_id AND l.location_id=d.location_id;

#2.非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level FROM employees e,job_grades g WHERE e.salary<=g.highest_sal AND salary>=g.lowest_sal;

#3.自连接
#案例：
– 查询员工名和上级到的名称
SELECT e1.last_name,e1.manager_id,e2.employee_id,e2.last_name FROM employees e1,employees e2 WHERE e1.manager_id=e2.employee_id;

#小练习
– 1.显示员工表的最大工资，工资平均值
SELECT MAX(salary),AVG(salary) FROM employees;
– 2.查询员工表employee_id，job_id，last_name，按department_id降序，salary升序
SELECT employee_id,job_id,last_name FROM employees ORDER BY department_id DESC,salary ASC;
– 3.查询员工表的job_id中包含a和e的，并且a在e的前面
SELECT job_id FROM employees WHERE job_id LIKE ‘%a%e%’;
– 4.显示当前时期，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT SUBSTR(123,2);

#小练习：
#1.显示所有员工的姓名，部门号和部门名称
SELECT 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 job_id,location_id FROM employees e,departments d WHERE e.department_id=d.department_id AND e.department_id=90;
#3.选择所有有奖金的员工的last_name，department_name，location_id，city
SELECT last_name,department_name,l.location_id,commission_pct,city FROM employees e,departments d,locations l WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND e.commission_pct IS NOT NULL;
#4.选择city在Totonto工作的员工的last_name，job_id，department_id,department_name
SELECT last_name,d.department_id,d.department_name,e.job_id FROM employees e,departments d,locations l WHERE e.department_id=d.department_id AND l.location_id=d.location_id AND l.city=‘Toronto’;
#5.查询每个工种，每个部门的部门名、工种号和最低工资
SELECT d.department_name,j.job_title,MIN(salary) FROM employees e,departments d,jobs j WHERE e.department_id=d.department_id AND j.job_id=e.job_id GROUP BY e.job_id,d.department_id;
#6.查询每个国家下的部门个数大于2的国家编号
SELECT COUNT(),l.country_id FROM locations l,departments d WHERE l.location_id=d.location_id GROUP BY l.country_id HAVING COUNT()>2;
#7.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
– employees Emp# manager Mgr#
– Kochhar 101 king 100
SELECT e1.last_name employees,e1.employee_id ‘Emp#’ ,e2.last_name manager,e2.employee_id ‘Mgr#’ FROM employees e1,employees e2 WHERE e1.manager_id=e2.employee_id AND e1.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.等值连接
#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,j.job_title FROM employees e INNER JOIN jobs j on e.job_id=j.job_id WHERE last_name LIKE ‘%e%’;
#3.查询部门个数>3的城市名和部门个数
SELECT city,COUNT() FROM departments d inner join locations l on d.location_id=l.location_id group by city HAVING COUNT()>3;
#4.查询那个部门的部门员工个数>3的部门名和员工个数，并按个数降序
SELECT department_name,COUNT() FROM employees e inner join departments d on e.department_id=d.department_id group by e.department_id 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 j.job_id=e.job_id order by department_name DESC;

#二非等值连接
#查询员工的工资级别
SELECT salary,grade_level FROM employees e inner join job_grades j on e.salary>=j.lowest_sal AND e.salary<=j.highest_sal;
#查询每个工资级别的个数>2，并且按工资级别降序
SELECT COUNT(),grade_level FROM employees e inner join job_grades j ON e.salary>=j.lowest_sal AND e.salary<=j.highest_sal group by grade_level HAVING COUNT()>2;

#三自连接
#查询员工的名字，上级名字
SELECT e1.last_name,e2.last_name FROM employees e1 inner join employees e2 on e1.manager_id=e2.employee_id;
#查询姓名中包含字符k的员工的名字，上级的名字
SELECT e1.last_name,e2.last_name FROM employees e1 inner join employees e2 on e1.manager_id=e2.employee_id WHERE e1.last_name like ‘%K%’;

#二、外连接
– 应用场景：用于查询一个表中有，另一个表没有的记录
– 特点：
– 1.外连接的查询结果为主表中的所有记录
– 如果从表中有和他匹配的，则显示匹配的值
– 如果从表中没有和他匹配的，则显示null
– 外连接查询结果=内连接结果+主表中有而从表没有的记录
– 2.左外连接，left join左边的是主表
– 右外连接，right join右边的是主表
– 3，左外和右外交换两个表的顺序，可以实现同样的效果
#引入：查询没有男朋友的女神名
SELECT name FROM beauty b LEFT OUTER JOIN boys bs on b.boyfriend_id=bs.id WHERE boyName IS NULL;
#案例1：查询哪个部门没有员工
select d.*,e.employee_id FROM departments d LEFT JOIN employees e ON d.department_id=e.department_id WHERE employee_id IS NULL GROUP BY department_name ORDER BY d.department_id;

#全外(不支持)
SELECT b.,bo. FROM beauty b FULL OUTER JOIN boys bo ON b.boyfriend_id=bo.id;

#交叉连接
SELECT b.,bo. FROM beauty b CROSS JOIN boys bo ;

#小练习
– 1.查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.name,bo.* FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id=bo.id;
– 2.查询那个城市没有部门
SELECT city FROM locations l LEFT JOIN departments d ON l.location_id=d.location_id WHERE department_name IS NULL;
– 3.查询部门名为sal或it的员工信息
SELECT d.department_name,e.* FROM departments d LEFT JOIN employees e ON e.department_id=d.department_id WHERE d.department_name IN(‘SAL’,‘IT’);

#进阶7：子查询
– 含义：出现在其他语句中的select语句，称为子查询或内查询
– 外部的查询语句，称为主查询或外查询
– 分类：
– 按子查询出现的位置：
– select后面
– 标量子查询
– from后面
– 表子查询
– where或having后面（重点）
– 标量子查询
– 列子查询
– 行子查询
– exist后面
– 表子查询
– 按结果集的行列数不同：
– 标量子查询（一行一列）
– 列子查询（一列多行）
– 行子查询（一行多列）
– 表子查询（结果集）