MySQL基础一

作者: ttiga | 来源:发表于2021-06-27 15:54 被阅读0次

数据库的特点

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

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 表名;显示表中的所有数据

执行本地sql脚本

image.png

DQL语言的学习

着重号: ` 用于区别关键字和字段名
where 的筛选条件不支持别名

#进阶1: 基础查询
/*
语法:
select 查询列表 from 表名;
类似于: System.out.println(打印的东西);

特点:
1.查询列表可以是: 表中的字段,常量值,表达式,函数
2.查询的结果是一个虚拟的表格
*/
#在做操作之前,在最上面打开或启动指定的数据库

USE myemployees;
#查询表中的单个字段

SELECT last_name FROM employees;

#同时查询表中的多个字段

SELECT last_name,salary,email FROM employees;

#查询表中的所有字段

SELECT * FROM employees;
#查询常量值

SELECT 100%98;

#给字段起别名
/*
1.便于理解
2.若要查询的字段有重名的情况,用别名可以区别开来
*/
#方式一: 用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

#方式二: 用空格

SELECT last_name 姓,first_name 名 FROM employees;

#案例: 查询salary,显示结果为 out put,要用引号引起来,否则会出现歧义

SELECT salary AS 'out put' FROM employees;

#去重
#案例: 查询员工表中涉及到的所有的部门的编号

SELECT DISTINCT department_id FROM employees;

# +号的作用
/*
java中的+号:
1.运算符,两个操作数都为数值型
2.连接符,只要有一个操作数为字符串
mysql中的+号:
仅仅只有一个功能:运算符
select 100+90; 两个操作数都为数值型,则做加法运算
select '123'+90;只要其中一方为字符型,试图将字符型数值转换成数值型
        若转换成功,则继续做加法运算
select 'John'+90;若转换失败,则将字符型数值转换成0

select null+90; 只要其中一方为null,则结果肯定为null
*/
# 字段拼接: 用concat函数
#案例: 查询员工名和姓连接成一个字段,并显示为姓名

SELECT CONCAT('a','b','c') AS 结果;

SELECT 
    CONCAT(last_name,first_name) AS 姓名
FROM 
    employees;
#显示表结构:
DESC employees;

# 显示出表employees的全部列,各个列之间用逗号连接,列头显示成OUT_PUT
#ifnull函数: 根据字段进行判断是否为空,参数1: 可能为null的字段,参数2: 若为null想返回的值,若不用ifnull函数,则字段为null时,则会影响字段拼接的显示
SELECT IFNULL(`commission_pct`,0) AS 奖金率,`commission_pct`
FROM employees;
SELECT
    CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0)) AS OUT_PUT
FROM 
    employees;

#进阶2: 条件查询
/*
语法:
select
    查询列表
from 
    表名
where
    筛选条件
分类:
    1.按条件表达式筛选
    简单条件运算符: > < = <>(!=) >= <= 
    2.按逻辑表达式筛选
    逻辑运算符:
        && || !
        and or not
    &&和and: 两个条件都为true,结果为true,反之为false
    
    ||或or: 只要有一个条件为true,结果为true,范志伟false
    !或not: 如果连接的条件本身为false,结果为true,反之为false
    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;

#2.按逻辑表达式筛选
#案例1:查询工资z在10000到20000之间的员工名、工资以及奖金

SELECT 
    last_name,salary,commission_pct
FROM 
    employees
WHERE salary >= 10000 AND salary <= 20000;

#案例2:查询部门编号不是在90到110之间，或者工资高于15000的员工信息

SELECT 
    *
FROM
    employees
WHERE 
    NOT(deparment_id >= 90 AND department_id <= 110) OR salary > 15000;
#department_id < 90 or department_id > 110 or salary > 15000;

#模糊查询:
/*
like
特点: 
1.一般和通配符一起搭配使用
    通配符:
    % 表示0个或任意多个字符
    _ 表示任意单个字符
between and
in
is null/is not null
*/
#1.like: 因为完全匹配,字符型值必须用单引号引起来,%代表通配符:不确定的字符
#案例1:查询员工名中包含字符a的员工信息

SELECT 
    *
FROM 
    employees
WHERE 
    last_name LIKE '%a%';#abc

#案例2:查询员工名中第三个字符为e，第五个字符为a的员工名和工资

SELECT 
    last_name,salary
FROM 
    employees
WHERE 
    last_name LIKE '__e_a%';

#特殊情况: 以通配符为查询结果    
#案例3:查询员工名中第二个字符为_的员工名

SELECT 
    *
FROM 
    employees
WHERE 
    last_name LIKE '_\_%';
    # last_name like '_$_%' escape '$';#指定符号为转义字符   

#2.between and
/*
1.使用between and可以提高语句的简洁度
2.包含两边临界值
3.两个临界值不要调换顺序
*/
#案例1:查询员工编号在100到120之间的员工信息

SELECT 
    *
FROM
    employees
WHERE 
    department_id BETWEEN 100 AND 120;
    #等价于department_id >= 100 and department <= 120;

#3.in
/*
含义: 判断字段的值是否属于in列表中的某一项
特点:
    1.用in提高语句间接度
    2.in列表的值必须一致或兼容
    3.不支持通配符的使用,因为 in('')等价于 = ''
#案例:查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
*/
SELECT 
    last_name,
    job_id
FROM 
    employees
WHERE 
    job_id IN('IT_PROG','AD_VP','AD_PRES');
    #等价于job_id = 'IT_PROT’OR job_id = 'AD_VP'OR JOB_ID ='AD_PRES';

#is null/is not null
/*
运算符的=或<>不能判断null值
is null/is not null可以判断null值
*/
#案例1:查询没有奖金的员工名和奖金率

SELECT 
    last_name,commission_pct
FROM 
    employees
WHERE
    commission_pct IS NULL;
    commission_pct IS NOT NULL;

SELECT 
    last_name,commission_pct
FROM 
    employees
WHERE
    commission_pct <=> NULL;
#安全等于: 可读性差
#案例1: 查询工资为12000的员工信息

SELECT last_name,commission_pct,salary
FROM employees
WHERE salary <=> 12000;

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

#2.查询员工号为176的员工的姓名和部门号和年薪

SELECT 
    last_name,
    department_id,
    salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM 
    employees
WHERE 
    employee_id = 176;
    
SELECT *
FROM employees
WHERE commission_pct LIKE '%%' # 
AND last_name LIKE '%%';

SELECT * FROM employees WHERE 1=0;#查询不到结果

经典面试题:
试问:
SELECT * FROM employees;
和
SELECT * FROM employees WHERE commission_pct LIKE '%%' AND last_name LIKE '%%';
是否一样? 原因

不一样,因为第一条可以查找出字段中含有NULL值的,因为,在 SQL 中NULL 表示“未知”。也就是说，NULL 值表示的是“未知”的值。,like不能查询得到不知道的值的字段.

SELECT * FROM employees WHERE commission_pct LIKE '%%' or last_name LIKE '%%' or employee_id like '%%';

而第一条和第三条一样,以为第三条语句中,查询条件之间是or,也就是取并集,始终会有一个字段是不能为NULL值的,例如当主键的时候.

进阶3: 排序查询

/*
引入:
select * from employees;
语法:
select 查询列表
from 表
where 筛选条件
order by 排序列表 (asc(升序)|desc(降序))
特点:
1、 asc代表的是升序,desc代表的是降序如果不写，默认是升序
2、order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3. order by子句一般是放在查询语句的最后面，limit子句除外
*/

#案例: 查询员工信息,要求工资从高到低排序

SELECT * FROM employees ORDER BY salary ASC;#ASC可省略,默认为升序
SELECT * FROM employees ORDER BY salary DESC;

#案例2:查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】

SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;

#案例3:按年薪的高低显示员工的信息和年薪【按表达式排序】

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) ASC;

#支持按别名排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 ASC;

#案例5:按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH('john');#查询字节长度函数

SELECT LENGTH(last_name) AS 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

#案例6:查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】第一个字段对排序影响最大

SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

sql语句执行顺序

image.png

# 练习:
#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 #not(salary between 8000 and 17000)
    salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;

#进阶4: 常见函数
/*
概念:类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名好处:
1、隐藏了实现细节
2、提高代码的重用性
调用:select函数名(实参列表: 数据表)【from表】;
函数调用时最需关注的特点:
1.叫什么(函数名)
2.干什么(函数功能)
分类:
1.单行函数: 如 concat,length,ifnull等
2.分组函数: 功能: 做统计使用,又称统计函数,聚合函数,组函数
*/
# 1.字符函数
#length: 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hhh');#一个中文占3个字节,一个英文占一个字节
#可以查询客户端用的字符类型
SHOW VARIABLES LIKE '%char%';

#2.concat 拼接字符串

SELECT CONCAT(last_name,'_',first_name) AS 姓名
FROM employees;

#3.upper,lower大小写转换函数
SELECT UPPER('john');
SELECT LOWER('JOHN');

#将姓变成大写,将名变成小写,然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名
FROM employees;

#substr,substring: 截取字符串
#注意: 数据库中的索引都是从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上陆展元',7) AS out_put;
#截取从指定索引处指定字符长度的字符
# 参数1: 指定索引值 参数2: 指定字符长度
SELECT SUBSTR('李莫愁爱上陆展元',1,3) AS out_put;
#案例:姓名中首字符大写，其他字符小写然后用_拼接，显示出来

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

#5.instr返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('李莫愁爱上陆展元','陆展元') AS out_put;

#6.trim函数: 取出前后两端内容(空格)

SELECT LENGTH(TRIM('     张翠山    ')) AS out_put;

SELECT TRIM('a' FROM 'aaaaa张翠aaaaa山aaaaaa') AS out_put;

#7.lpad用指定的字符实现左填充达到指定长度
#当指定字符长度小原字符长度时,从右开始往左截断
SELECT LPAD('哈哈哈哈哈',1,'*') AS out_put;

#8.rpad用指定的字符实现右填充指定长度      

SELECT RPAD('啊啊啊啊阿',10,'a') AS out_put;

#replace 替换字符
SELECT REPLACE('啊啊啊哦哦哦鹅鹅鹅','啊啊啊','呀呀呀');

image.png

#数学函数

#round 四舍五入,正负情况一样

SELECT ROUND(1.65);
SELECT ROUND(1.357,2);#参数2: 保留小数点后指定位数

#(屋顶)ceil:向上取整,返回>=该参数的最小整数

SELECT CEIL(1.001);
#(地板)floor: 向下取整,返回<=该参数的最小整数

#truncate: 截断: 参数1: 要截断的指定小数,参数2: 指定小数后几位开始截断

SELECT TRUNCATE(1.69999,1);

#取余: 正负跟被除数有关
/*
mod(a,b) = a-a/b*b
mod(-10,-3): -10-(-10)/(-3)*(-3)=-1
*/
SELECT MOD(10,-3);

#日期函数

#now 返回当前系统日期+时间
SELECT NOW();

#curdate: 返回当前系统日期,不包含时间
SELECT CURDATE();

#curtime: 返回当前时间,不包含系统日期

SELECT CURTIME();

#year: 可以获取指定的部分,年,月,日,小时,分钟,秒

SELECT YEAR(NOW()) AS 年;
SELECT YEAR('1999-1-1') AS 年;

#获取员工入职的年份

SELECT YEAR(hiredate) AS 年 FROM employees;
#获取月份
SELECT MONTH(NOW() 月份;
#获取月分名
SELECT MONTHNAME(NOW()) 月份名;

#str_to_date 将字符通过指定的格式转换成日期
#数据库接收前端页面日期的返回值格式有多种,若数据库中直接等号赋值可能会显示不出来,因此就需要用到str_to_date进行格式转换,
SELECT STR_TO_DATE('1999-4-3','%Y-%c-%d') AS out_put;

#查询入职日期为1992-4-3的员工信息

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3-1992','%c-%d-%Y');#有具体的业务场景

#date_format将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%c月%d日') AS 结果;

#查询有奖金的员工名和入职日期(xx月/xx日xx年)

SELECT last_name,DATE_FORMAT(hiredate,'%c月/%d日%y年')
FROM employees
WHERE commission_pct IS NOT NULL;

#其他函数

SELECT VERSION();#当前数据库版本号
SELECT DATABASE();#当前数据库
SELECT USER();#当前用户

#路程控制函数
# if函数: if-else的效果,参数1: 判断条件,参数2:条件成立,返回表达式,参数3:条件不成立,返回表达式

SELECT IF(10>5,'大','小');

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呜呜呜','有奖金,哈哈哈') AS 备注
FROM employees;

# case函数: 使用一: switch case 效果
/*
java中
适用于等值判断
switch(变量或表达式){
    case 变量1: 语句1;break;
    ...
    default: 语句n;break;
}
mysql中
case 判断的字段或表达式
when 常量1 then 要显示的值1或语句1(若为值可不加分号,若为语句则要加分号);
when 常量2 then 要显示的值2或语句2(若为值可不加分号,若为语句则要加分号);
...
else(代表default) 要显示的值n或语句n;
end

当case和select搭配使用时,相当于作为表达式来用,then后面就不能接语句了,只能接值
当在存储过程或函数中case判断可以当做单个语句来用,也就是不搭配select也可以直接用,then后面就接语句
当then后面接值的时候就不用加分号,若为语句则要加分号
*/

/*案例:查询员工的工资,要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/

SELECT salary AS 原始工资,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 AS 新工资
FROM employees;

#3.case 函数的使用二:类似于多重if
/*
java中:
if(条件1){
    语句1;
}else if(条件2){
    语句2;
}else if(条件3){
语句3;
}
...
else{
    语句n;
}

mysql中
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;

练习:
#显示系统时间(注:白期+时间)

SELECT NOW();

#2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果(new salary)

SELECT employee_id 工号, last_name 姓名,salary 工资, salary*1.2 AS 'new salary'
FROM employees;

#3.将员工的姓名按首字母排序，并写出姓名的长度( length）
# 按首字母排序和按姓名排序效果不同,姓名的每个字母都会排序
SELECT LENGTH(last_name) 姓名长度, SUBSTR(last_name,1,1) AS 首字母, last_name
FROM employees
ORDER BY 首字母;

#4.做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary* 3>
Dream salary
King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name,'earns',salary,'monthly but wants',salary*3) AS 'Dream salary'
FROM employees
WHERE salary = 24000;

# 5.用 case-when,按下面条件,
job     grade
AD_PRES     A
ST_MAN      B
IT_PROG     C
SA_REP      D
ST_CLFRK    E

产生下面的结果
Last_name job_id grade
king AD_PRES A
 
SELECT last_name,job_id AS job,CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLFRK' THEN 'E'
END AS grade
FROM employees
WHERE job_id = 'AD_PRES';

#二.分组函数
/*
功能: 用作统计使用,又称为聚合函数或统计函数或组函数

分类:
sum 求和,avg 平均值,max 最大值,min 最小值,count 计算个数
特点:
1.sum,avg一般只用于处理数值型
  max,min,count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct关键字搭配使用实现去重运算
4.count函数的单独介绍
一般用count(*)用作统计行数
5.和分组函数一同查询的字段要求是group by后的字段


*/
#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;#salary字段有值的一共有几个

#查询多个分组函数

SELECT SUM(salary) 总和,AVG(salary) 平均,MAX(salary) 最大,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

SELECT SUM(salary) 总和,ROUND(AVG(salary),3) 平均,MAX(salary) 最大,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

#2.参数支持哪些类型

SELECT SUM(last_name),AVG(last_name) FROM employees;#sum和avg一般只处理数值型
SELECT SUM(hiredate),AVG(hiredate) FROM employees;#日期型也不行

SELECT MAX(last_name),AVG(last_name) FROM employees;
SELECT MAX(hiredate), AVG(hiredate) FROM employees;
#count计算非空的值的个数
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;

#3.是否忽略null
都忽略null运算
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/72 FROM employees;

SELECT COUNT(commission_pct) FROM employees;

#4.和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

#5.count函数的详细介绍
#统计非空的行数
SELECT COUNT(*) FROM employees;
#count里面可以加个常量值(通常加1较为简单),相当于在表中加了一列这个常量值,来统计非null数据的个数
SELECT COUNT(1) FROM employees;

效率: 有多种方法能干一件事时
myisam存储引擎下,COUNT(*)的效率高,因为这个引擎内部有个计数器,他直接就返回了个数,所以*效率最高
innodb存储引擎下,COUNT(*)和count(1)效率差不多,但比count(字段)要高一些,因为括号里加字段,他实际上要有个判断,要判断该字段的值是否为null,若为null则不加1,不为null,则加1,他有个筛选在里面
综上所述,不管用哪个引擎下,用count(*)较多

#1.查询公司员工工资的最大值,最小值，平均值,总和

SELECT MAX(salary) 最大值,MIN(salary) 最小值, AVG(salary) 平均值, SUM(salary) 总和 FROM employees;

#日期函数: 求两个日期的差值
SELECT DATEDIFF(NOW(),'2000-6-2');

#2.查询员工表中的最大入职时间和最小入职时间的相差天数(DIFFRENCE)

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) 相差 FROM employees;

#3.查询部门编号为90的员工个数

SELECT COUNT(*) FROM employees WHERE department_id = 90; 

进阶5: 分组查询
/*
语法:
    select 分组函数,列(要求出现在group by的后面的字段)
    from 表
    [where 筛选条件]
    group by 分组的列表
    [order by 子句]
注意:
    查询列表必须特殊,要求是分组函数和group by后出现的字段
特点:
    1.分组查询中的筛选条件分为两类
            数据源     位置      关键字
    分组前筛选   原始表     group by子句前 where
    分组后筛选   分组后结果集  group by子句后 having
    ①分组函数做条件肯定是放在having子句中,也就是分组后筛选
    ②能用分组前筛选的,就优先考虑用分组前筛选,考虑到性能问题
    比如分组后的字段,用where或having都可以,优先用where
*/

#案例1 :查询每个工种的最高工资

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

#案例2:查询每个位置上的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

#添加筛分组前的筛选条件
#案例1:查询邮箱中包含a字符的,每个部门的平均工资
#group by 放在 where 后面,放在order by 前面

SELECT AVG(salary) 平均工资,department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#案例2:查询每个领导手下有奖金的员工的最高工资

SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#添加分组后的筛选条件

只要涉及的求个数,就用count(*)
#①查询每个部门的员工个数
where对分组前的结果进行筛选,必须凡在group BY 前,where过滤行
having对分组后的结果进行筛选,必须放在group BY 后,having过滤分组
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_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 LENGTH(last_name) 姓名长度,COUNT(*) 员工个数 
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;

#添加排序
#案例:查询每个部门每个工种的员工的平均工资，奖金不为空,平均工资大于10000,并且按平均工资的高低显示

SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary) > 10000
ORDER BY AVG(salary) DESC;

#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序

SELECT job_id, MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;

#2.查询员工最高工资和最低工资的差距(DIFFERENCE)

SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM employees;

#3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

#4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序

SELECT department_id,COUNT(*),AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;

#5.选择具有各个job_id的员工人数

SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;
进阶6：连接查询
/*
含义: 又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象: 表1 有m行,表2有n行,结果=m*n行

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

分类:
    按年代分类:
    sql92标准: 仅仅支持内连接
    sql99标准[推荐]: 支持内连接+外连接(左外和右外)+交叉连接
    按功能分类:
        内连接:
            等值连接
            非等值连接
        外连接:
            左外连接
            右外连接
            全外连接
        交叉连接
*/

SELECT * FROM beauty;

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

一.sql92标准
#1.等值连接
/*
①多表等值连接的结果为多表的交集部分,因为只有连接条件值一样才能查询出来
②n表连接,至少需要n-1个连接条件
③多表的顺序没有要求
④一般要为表起别名,起法和当时给字段起别名一样
⑤可以搭配前面介绍的所有子句使用,比如排序,分组,筛选
*/
#原理: 一个一个匹配,但是会通过连接条件筛选
#案例1:查询女神名和对应的男神名

SELECT NAME,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.可以给表起别名
/*
①提高语句的简洁度
②区分多个重名的字段

注意:
    加入在执行from时起了别名了,select查询时还用原始表名限定就会报错
    因为根据执行顺序来讲,第一先走from,走了from就有了别名了,相当于生成了虚拟试图,到select的时候就不认原始表名了
    如果为表起了别名,则查询的字段就不能使用原来的表名去限定,要么就不限定
*/
#3.两个表顺序可以调换,效果一样,因为都是看连接条件的值一样的才筛选出来
#查询员工名、工种号、工种名
一般情况都会为表起别名在连接查询的时候因为连接查询经常会有歧义的列
SELECT last_name,e.`job_id`,job_title
FROM employees e,jobs j #表间互换效果不变
WHERE e.`job_id` = j.`job_id`;

#4.可以加筛选
#案例:查询有奖金的员工名、部门名

SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE employees.`department_id` = departments.`department_id`
AND commission_pct IS NOT NULL;

#案例2:查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

#5、可以加分组
#案例1:查询每个城市的部门个数

SELECT COUNT(*),city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

#案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE commission_pct IS NOT NULL AND d.`department_id` = e.`department_id`
GROUP BY department_name,d.`manager_id`;#当分组时不确定部门名和领导id是否一一对应时,把两个都加上比较好

#6.可以加排序

#案例:查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id` = j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#7、可以实现三表连接
#案例:查询员工名、部门名和所在的城市

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`;

2.非等值连接
#案例1:查询员工的工资和工资级别

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_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`

3.自连接(自己连接自己)

#案例:查询员工名和上级的名称

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

练习:
一.显示员工表的最大工资,平均工资值

SELECT MAX(salary),AVG(salary)
FROM employees

二、查询员工表的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;

三、查询员工表的job_id中包含a和e的,并且a在e的前面

SELECT job_id
FROM employees
WHERE job_id LIKE'%a%e%';


四、已知表student,_里面有id(学号)，name，gradeId(年级编号)
已知表grade，里面有id(年级编号),NAME(年级名）
己知表result，里面有id,score,studentNo（学号)
要求查询姓名,年级名,成绩

SELECT s.name,g.name,r.score
FROM student s,grade g,result r
WHERE s.id = r.studentNo
AND g.id = g.gradeId;

五、显示当前日期，以及去前后空格，截取子字符串的函数

SELECT NOW();
SELECT TRIM(指定前后字符,FROM '')
SELECT SUBSTR(要截取的总字符串,起始索引,截取长度)

练习2:
#1.显示所有员工的姓名,部门号和部门名称。
USE myemployees;

SELECT last_name, e.department_id,department_name
FROM employees e,departments d
WHERE d.`department_id` = e.`department_id`;

#2.查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id,d.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 e.last_name,d.department_name,l.location_id,city
FROM employees e,departments d,locations l
WHERE commission_pct IS NOT NULL
AND e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND e.`commission_pct` IS NOT NULL;

#4.选择city在Toronto工作的员工的

SELECT last_name,job_id,e.department_id,d.department_name
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';

#5.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT d.department_name,job_title,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 department_name,job_title;

#6.查询每个国家下的部门个数大于2的国家编号

SELECT  COUNT(*) 部门个数,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 magager, 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 连接条件;

分类:
等值
非等值
自连接

特点:
①添加排序,分组,筛选
②inner关键字可以省略
③筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④inner join 连接和sql92语法中的内连接的等值连接效果一样,都是查询多表的交集
*/

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,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 departments d 
INNER JOIN employees e
WHERE d.`department_id` = e.`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;

二.非等值连接
#查询员工的工资级别

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

#查询工资级别的个数>20的个数,并且按工资级别降序

SELECT grade_level,COUNT(*)
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;

三.自连接

#查询员工名中包含字符k的员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.last_name LIKE '%k%';

四.外连接
/*
应用场景: 用于查询一个表中有,另一个表中没有的记录
特点:
1.外连接的查询结果为主表中的所有记录
    若从表中有和它匹配的(连接条件一致),则显示匹配的值
    若从表中没有和它匹配的,则显示null
    外连接查询结果=内连接结果+主表中有而从表中没有的记录
2.左外连接,left join 左边的是主表
  右外连接,right join 右边的是主表
3.左外和右外交换两个表的顺序,可以实现同样的效果
4.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的记录
要查询的信息主要来自于哪个表,那么谁就是主表
*/

#引入:查询男朋友不在男神表的的女神名
#左外连接
SELECT b.name,bo.boyName
FROM beauty b LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;

#右外连接
SELECT b.name,bo.*
FROM boys bo 
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

#案例1:查询哪个部门没有员工
#左外连接

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

#右外连接

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

#全外连接(相当于两个集合的并集),mysql中不支持

USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;

#交叉连接(就是笛卡尔乘积,只是把逗号改成cross join)
USE girls;
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

内连接

image.png

左外连接

image.png

添加筛选条件

image.png

右外连接

image.png

添加筛选条件

image.png

全外连接

image.png

添加筛选条件

image.png

#—、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

SELECT b.`id`,bo.*
FROM beauty b 
INNER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` > 3; 

#二、查询哪个城市没有部门
departments是主表,locations是从表
SELECT city,d.*
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id` IS NULL;#从表的主键设空

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

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

进阶7: 子查询
/*
含义: 
出现在其他语句中的select语句,称为子查询或内查询
外部的查询的语句,称为主查询或外查询
分类:
按子查询出现的位置:
    select后面:
        仅仅支持标量子查询
    from后面:
        支持表子查询
    where或having后面:(重点)
        标量子查询(重点): 单行子查询
        列子查询(重点): 多行子查询
        行子查询
    exists后面(相关子查询)
        支持表子查询
按结果集的行列数不同:
    标量子查询(结果集只有一行一列)
    列子查询(结果集只有一列多行)
    行子查询(结果集有多行多列,一般为一行多列)
    表子查询(结果集一般为多行多列)
    
*/

一.where或having后面
1.标量子查询(单行子查询)
2.列子查询(多行子查询)

3.行子查询(多列多行)

子查询的特点:
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询,一般搭配着单行操作符使用
> < >= <= = <>

列子查询,一般搭配着多行操作符使用
IN,ANY/SOME,ALL

④子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
1.标量子查询

#案例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和工资

#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;

#②查询143号员工的salary
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
);

#案例3:返回公司工资最少的员工的last_name,job_id和salary

#①查询工资的最低工资
SELECT MIN(salary)
FROM employees

#②查询last_name,job_id和salary，要求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;

#③筛选②,满足min (salary) >①

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);   

#非法使用标量子查询
筛选条件本应该用标量子查询,但却用了列子查询
筛选条件中用单行操作符时,括号里只能搭配标量子查询
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT salary
    FROM employees
    WHERE department_id = 50
);   

#2.列子查询(一列多行)
#案例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)
);

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

#案例1:返回location_id不是1400或1700的部门中的所有员工姓名

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

#或
SELECT last_name
FROM employees
WHERE department_id <>ALL(
    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<(1)的任意一个

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';

#或

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

#案例3:返回其它工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id 以及salary

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

#或

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

#3、行子查询(结果集一行多列或多行多列)

#案例:查询员工编号最小并且工资最高的员工信息
将多个字段当成一个虚拟字段来用

SELECT *
FROM employees
WHERE (employee_id,salary)=(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);  
#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees

#②查询最高工资
SELECT MAX(salary)
FROM employees

#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
    SELECT MIN(employee_id)
    FROM employees
)AND salary=(
    SELECT MAX(salary)
    FROM employees
);

二.select后面
/*
仅仅支持标量子查询
*/

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

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
)部门名;

#三,from后面
/*
将子查询结果充当一张表,要求必须起别名
*/
#案例:查询每个部门的平均工资的工资等级
#①查询每个部门平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#②连接①的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal

SELECT ag_dep.*,g.grade _level
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highestsal

四.exists后面(相关子查询)
/*
语法:
exists(完整的查询语句)
结果: 1或0
*/

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary = 30000);
#案例1:查询有员工的部门名
#查询某个字段的值,根据exists函数查询的过滤,所以叫相关子查询,意思是exists子查询涉及到了主查询的字段
SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT *
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
);  

#或
SELECT department_name
FROM departments d
WHERE d.department_id IN(
    SELECT department_id
    FROM employees
);

#案例2:查询没有女朋友的男神信息

#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` 
)

8.分页查询(重点)
/*
应用场景: 当要显示的数据,一页显示不全,需要分页提交SQL请求
语法:
    select 查询列表
    from 表1
    [join type join 表2
    on 连接条件
    where 分组前筛选条件
    group by 要分组的字段
    having 分组后的筛选条件
    order by 排序的字段]
    limit [offset] size; 若从第一条开始显示,其实索引可以省略
    
执行顺序: from > join > on > where > group by > having > select > order by > limit
    offset: 要显示的条目的起始索引(起始索引从0开始)
    size: 要显示的条目个数
特点:
    ①limit语句从执行上或是语法上都是放在查询的最后
    ②公式
    要显示的页数page,每页的条目数size
    select 查询列表
    from 表
    limit (page -1)*size,size;
    假如:size=10
    page 起始索引
    1   0
    2   10
    3   20
*/

#案例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 
ORDER BY salary DESC 
LIMIT 10 ;

进阶9.联合查询
联合和表连接的区别: 表连接是增加不同字段,联合是增加同字段多行数据
/*
union: 联合 合并: 将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
...

就是单纯的联合结果而已,没有内部逻辑联系

应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息要一致
特点:
1.要去多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好是一致的,顺序默认显示是第一个表的
3.union关键字默认是去重的,若使用union all 可以包含重复项
*/

#引入的案例:查询部门编号>90或邮箱包含a的员工信息
#旧方法
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id > 90;

#union
#当两个表的字段没有关联时,union可以将两个表关联起来，就起作用了
#联合的两个表的字段要一样多,否则会报错
SELECT * FROM employees WHERE email LIKE '%a%'
UNION 
SELECT * FROM employees WHERE department_id > 90;

#案例:查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca WHERE csex = '男'
UNION 
SELECT t_id,tName FROM t_ua WHERE tGender = 'male';

#union all 包含重复项

SELECT id,cname FROM t_ca WHERE csex = '男'
UNION ALL 
SELECT t_id,tName FROM t_ua WHERE tGender = 'male';

DML语言
/*
数据操作语言:
插入: insert
修改: update
删除: delete

*/
一.插入语句
方式一: 经典的插入
/*
语法:
insert into 表名((列名)字段名,...) values(值1,...);

*/
#字段类型Nullable意思是选填,可以不用管
#1.插久的值的类型要与列的类型一致或兼容
SELECT * FROM beauty;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) 
VALUES(13,'唐艺昕','女','1990-4-23','1986156261',NULL,2);   

#2.不可以为null的列必须插入值。可以为null的列如何插入值?

#方式一: 将可为空的列名写上,值用null填充
SELECT * FROM beauty;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) 
VALUES(13,'唐艺昕','女','1990-4-23','1986156261',NULL,2);   

#方式二: 可为空的字段和其对应的值都省略不写
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','18664844848');

#3.列的顺序可以调换
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',17,'110');

#4.列数和值的个数必须一致

#5.可以省略不写列名，但是默认是所  有列，而且列的顺序和表中列的顺序一致

INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);

方式二:
/*
语法:
insert into 表
set 列名=值,列名=值,...
*/

INSERT INTO beauty 
SET id = 19,NAME = '刘涛',phone = '999';

两种方式大pk

1.方式一支持插入多行,方式二不支持
一条语句批量插入三行
INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1986156261',NULL,2),
(24,'唐艺昕2','女','1990-4-23','1986156261',NULL,2),
(25,'唐艺昕3','女','1990-4-23','1986156261',NULL,2);

2.方式一支持子查询,方式二不支持
INSERT INTO + SELECT 相当于从其他表复制数据

INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','116686';#相当于把子查询查询出的结果集插入到对应的列里去

INSERT INTO beauty(id,NAME,phone)
SELECT id,boyName,'125616'
FROM boys WHERE id < 3;

二.修改语句
/*
1.修改单表的记录(重点)
语法:
update 表名
set 列=新值,列=新值,...
where 筛选条件;(若果不加where,则修改表中所有行)

执行顺序: update(锁定数据源) > where(进行过滤筛选) > set(修改值)
2.修改多表的记录[补充]
语法:
sql92语法: 只支持内连接
update 表1 别名,表2 别名
set 列=值
where 连接条件
and 筛选条件;

sql99语法:
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
*/

1.修改单表的记录
#案例1:修改beauty表中姓唐的女神的电话为13899888899

UPDATE beauty
SET phone = '13899888899'
WHERE NAME LIKE '唐%';

#案例2:修改boys表中id好为2的名称为张飞，魅力值10
UPDATE boys
SET boyName = '张飞',userCP = 10
WHERE id = 2;

2.修改多表的记录

#案例1:修改张无忌的女朋友的手机号为114

UPDATE boys bo
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
SET phone = '114'
WHERE boyName = '张无忌';

#案例2:修改没有男朋友的女神的男朋友编号都为2号

要改的字段是哪张表,那张表就是主表
#修改beauty表,所以beauty表是主表

UPDATE boys bo
RIGHT JOIN beauty b
ON bo.id = b.boyfriend_id
SET b.boyfriend_id = 2
WHERE bo.id IS NULL;

三.删除语句
/*
方式一: delete
语法:
1.单表的删除[重点]
delete from 表名 where 筛选条件

2.多表的删除[补充]
sql92语法:
delete 表的别名 #要删除哪张表的记录就写哪张表,若要删除两张表级联的,两张都要写
from 表1 别名,表2 别名 #表1和表2连接,笛卡尔乘积
where 连接条件
and 筛选条件;

sql99语法:
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 on 连接条件 #两个表连接出来一个新的结果集
where 筛选条件;


方式二: truncate
语法: truncate table 表名; 删除整个表的数据,不能加where条件
*/

#方式一: delete
#1.单表的删除
#案例1: 删除手机号以9结尾的女神信息

DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;

2.多表的删除
#案例:删除张无忌的女朋友的信息(存在的信息用内连接)

DELETE b
FROM beauty b 
INNER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyName = '张无忌';

#案例:删除黄晓明的信息以及他女朋友的信息(级联删除)

DELETE bo,b 
FROM boys bo
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE bo.boyName = '黄晓明';

方式二: truncate语句(也叫清空数据,不能加where)
#案例:将魅力值>100的男神信息删除

TRUNCATE TABLE boys;

DELETE vs TRUNCATE (面试题☆)
/*
1.delete可以加where条件,truncate不能加
2.truncate删除,效率高一点点
3.假如要删除的表中有自增长列,
若用delete删除后,再插入数据,自增长列的值从断点处(删除的位置)开始,
而truncate删除后,再插入数据,自增长列的值从1开始
4.truncate删除后没有返回值(受影响的行数为0),delete删除有返回值
5.truncate删除不能回滚,delete删除可以回滚   
*/

SELECT * FROM boys;

DELETE FROM boys;
TRUNCATE FROM boys;
INSERT INTO boys (boyName,userCP)
VALUES('张飞',100),('刘备',100),('关云长',100);

#DDL语言:
/*
数据定义语言

库和表的管理

一.库的管理
创建,修改,删除
二.表的管理
创建,修改,删除

创建: create
修改: alter
删除: drop

*/

一.库的管理
1.库的创建
/*
语法:
create database [if not exists]库名; xxx exists: 容错性处理
*/

#案例:创建库Books

CREATE DATABASE IF NOT EXISTS books;

2.库的修改(目前不能修改)

RENAME DATABASE bookes TO 新库名; #已废除

更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

库的删除

DROP DATABASE IF EXISTS books;

二.表的管理
1.表的创建(重点)

/*
语法:
create table 表名(
    列名 列的类型[(长度) 约束],
    列名 列的类型[(长度) 约束],
    列名 列的类型[(长度) 约束],
    ...
    列名 列的类型[(长度) 约束]
)

*/

#案例: 创建表Book

CREATE TABLE Book(
    id INT,
    bName VARCHAR(20),
    price DOUBLE,
    authorId INT,
    publishDate DATETIME

);

DESC book;

2.表的修改
只有库和表的创建和删除时候可以用exists处理容错性,列没有这功能
/*
alter table 表名 add|drop|modify|change column 列名 [列类型 约束];
*/
①修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;#change的column可以省略
②修改列的类型或约束

ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

③添加新的列

ALTER TABLE 表名 ADD COLUMN 列名 列类型;

④删除列

ALTER TABLE 表名 DROP COLUMN 列名;
⑤修改表名

ALTER TABLE 表名 RENAME TO 新列名;

3.表的删除

DROP IF EXISTS 表名;

SHOW TABLES;#查看当前库的所有表

#通用写法: 想自己设计数据库和表,可以这样写,数据库或表里有数据不能这么写

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();

4.表的复制

INSERT INTO 表名 VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');

#1.仅仅复制表的结构(表里没数据)

CREATE TABLE 新表名 LIKE 要复制的表名;

#2.复制表的结构+数据

CREATE TABLE 新表名 
SELECT * FROM 要复制的表名;

#3.只复制部分数据

CREATE TABLE 新表名
SELECT id,au_name
FROM author
WHERE nation = '中国';

#4.仅仅复制某些字段,但没有数据

CREATE TABLE 新表名
SELECT id,au_name
FROM author 
WHERE 0;#或 1=2; 代表任何字段都不满足

#标识列
标识列的类型只能数值类型
AUTO_INCREMENT

#TCL
/*
Transaction Control Language 事务控制语言

事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么没全部执行,要么全部不执行
在这个单元中,每个sql语句都是相互依赖的(捆绑式执行)
案例: 张三丰向郭襄转账500 
余额:
张三丰: 1000
郭襄: 1000

转账后:
update表set张三丰的余额=500 where name='张三丰'
假如在执行完第一条sql语句,出现了以外,数据库挂掉了,这样就会导致上面执行成功,下面失败了
这样就会变成张三丰500,郭襄1000,导致数据不可靠,所以事务就是用来解决这种现象的,主要用在这种场合
update表set郭襄的余额=1500 where name='郭襄'
所以我们希望,这个多条SQL语句组成一个独立的执行单元,这个执行单元要么全部执行成功,要么全部失败,这就是事务的意思
回滚: 把之前所有做的操作撤销,从新回到最初的状态; 举例: 加入平时安装软件的时候,安装到某个步骤是出现错误,软件提示继续或者撤销,假如选了撤销,安装进度就回到最初的起点

事务的ACID特性:(面试重点)
原子性(Atomicity)(最重要): 一个事务不可再分割,要么都执行,要么都不执行.(比如找工作时,两个人是捆绑式就业,要么两人都要,要么都不要)
一致性(Consistency): 一个事务执行会使数据从一个一致状态切换到另外一个一致状态.(比如转账的时候,转账前和转账后两人的余额之和一致,数据还是准确可靠的)
隔离性(Isolation): 一个事务的执行不受其他事务的干扰.(要看隔离级别,有的隔离级别会受干扰)(在开发中,有可能多个事务并发地操作同一个数据库的数据)
持久性(Durability): 一个事务一旦提交,则会永久改变数据库的数据.(比如: 删除就是一个事务)

事务的创建
隐式事务:事务没有明显的开启和结束的标记,也就是看出来是事务,自动开启自动结束
比如insert, update,delete语句,也就是平时写一条insert语句就相当于开启了一个事务并且提交了
*/
支持事务的原理是行锁,只有innodb支持行锁,所以只有innodb支持事务
存储引擎:不同的数据库服务器默认的存储引擎不同,在mysql中,数据不同的存储技术,称作存储引擎,也叫表类型

查看存储引擎

SHOW ENGINES;
用的最多的引擎:
INNODB 现在默认的存储引擎 支持事务
MYISAM 5.5版本前默认的 不支持事务
MEMORY 不支持事务    

查看变量
#自动提交的功能它的值默认是开启的
SHOW VARIABLES LIKE 'autocommit';#Value=ON

好比下面一句就代表一个事务,一执行就满足事务自动提交的特点
DELETE FROM 表 WHERE id = 1;

弊端: 当工作单元有多条sql语句就

假如要做一件事需要用到两条sql语句,希望把两条sql语句合并成一条事务,但是默认一条语句就是一条事务

这时就要用到显示事务: 事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用,因为默认是开启的
#关闭自动提交功能只对当前事务(会话)有效,并不是关一次就永远生效
#所以每次开机显示事务都要手动禁用
SET autocommit = 0;

书写步骤1:开启事务
SET autocommit = 0;#必须的
START TRANSACTION;#可选的
步骤2: 编写s事务中的sql语句(SELECT,INSERT,UPDATE,DELETE)(DDL语言没有事务之说,例如:CREATE,ALTER,DROP)
语句1;
语句2;
...
步骤3: 结束事务
COMMIT;#提交事务
ROLLBACK;#回滚事务,发生异常时

#演示事务的使用步骤

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 = 1000 WHERE username = '张无忌';
UPDATE account SET balance = 1000 WHERE username = '赵敏';
#结束事务: 要么提交要么回滚,不能同时一起执行,只有有了结束的标记,才决定是撤销还是提交到磁盘文件,在没结束前,数据只是驻留在内存
ROLLBACK;#数据没有变,可以理解为,结束事务之前,数据只是保存到了内存,并没有提交到磁盘文件
#commit;

SELECT * FROM account;

2.delete和truncate在事务使用时的区别
DDL语言不能回滚,truncate是DDL语言,delete是DML
#演示delete
SET autocommit = 0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;


#演示truncate
SET autocommit = 0;
START TRANSACTION;
TRUNCATE TABLE account;#实际删除,不支持回滚
ROLLBACK;
事务并发问题的介绍

脏读: 事务1读取已经被事务2更新的但还没被提交的字段.之后若事务2回滚,事务1读取的内容是临时且无效的
不可重复读: 事务1读取了一个字段,然后事务2更新了该字段,之后,事务1再次读取同一个字段,值就不同了;同一个事务多次查询的结果不一样
幻读: 事务1从表中读取一个字段,然后事务2在该表中插入了一些新的行,之后,如果事务1再次读取同一个表,就会多出几行

修改表的字符集

SET NAMES gbk;

savepoint的使用,只搭配rollback使用

SET autocommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 1;#已删除
SAVEPOINT a;#设置保存点名为a
DELETE FROM account WHERE id = 2;#未删除
ROLLBACK TO a;#回到保存点
SELECT * FROM account;

MySQL基础一

数据库的特点

MySQL服务的登录和退出

MySQL的常见命令

MySQL的语法规范

SQL的语言分类

SQL的常见命令

DQL语言的学习

进阶3: 排序查询

savepoint的使用,只搭配rollback使用

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读