mysql学习手记(尚硅谷李玉婷老师)

[toc]

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

一、基础查询方法

SHOW DATABASES;
SELECT VERSION();
SHOW TABLES;
USE mysql;
SHOW TABLES;
STATUS;

SELECT * FROM db;

USE myemployees;

查看有多少个表

SHOW TABLES;

查看表的结构

SELECT * FROM departments;
SELECT * FROM jobs;
SELECT * FROM employees;

查询表中的单个字段

SELECT last_name FROM employees;

查询表中的多个字段

SELECT last_name,first_name, FROM employees;
SELECT * FROM employees;
SELECT last_name,phone_number FROM employees;

查询表中所有字段

SELECT 
       employee_id,
       `first_name`,
       `last_name`,
       `phone_number`,
       `last_name`,
       `job_id`,
       `salary`,
       `commission_pct`,
       `manager_id`,
       `department_id`,
       `hiredate` 
   FROM
       employees ;

SELECT * FROM employees;

常量查询

SELECT 100;
SELECT "john";

查询表达式

SELECT 100*32.46545;

查询系统函数

查看MySQL系统版本
SELECT VERSION();

起别名

#1)使用as
SELECT 100.154*34.14 AS 结果;
SELECT first_name AS 姓,last_name AS 名 FROM employees;

#2)使用空格
SELECT email 邮箱 FROM employees;

#案例查询：salary，显示结果为out put
USE employees;
SELECT salary AS "output" FROM employees;

去重

#查询所有员工表中所有涉及到的部门编号
SELECT department_id FROM employees;
SELECT DISTINCT department_id FROM employees;

+号的作用

#仅仅是一个运算符
SELECT 100+80;
SELECT '123'+2;
SELECT 'yang'+299;
SELECT NULL+2134;
SELECT TRUE+234;

案例

查询员工和姓名连接成一个字段，并显示为姓名
SELECT CONCAT('a','b','c') AS 结果;

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

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

二、条件查询

/*

语法：
    select 
        查询列表
    from
        表名
    where
        筛选条件;

分类：
    一、按条件表达式筛选
    
    简单条件运算符：> < = != <> >= <=
    
    二、按逻辑表达式筛选
    逻辑运算符：
    作用：用于连接条件表达式
        && || !
        and or not
        
    &&和and：两个条件都为true，结果为true，反之为false
    ||或or： 只要有一个条件为true，结果为true，反之为false
    !或not： 如果连接的条件本身为false，结果为true，反之为false
    
    三、模糊查询
        like
        between and
        in
        is null

*/

按条件查询

#案例1：查询工资>12000的员工信息  大于>
SELECT * FROM employees WHERE salary>10000;

#案例2：查询部门编号不等于90号的员工名和部门名  不等于 <> !=
SELECT last_name,department_id FROM employees WHERE department_id<>90;
SELECT last_name,department_id FROM employees WHERE department_id!=90;

按逻辑表达式查询

#查询工资在10000到20000之间的员工名、工资以及奖金  && and 条件都为真时才执行
SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 AND salary <= 20000;
SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 && salary <= 20000;

#案例3：查询部门编号不是在90到110之间，或者工资高于15000的员工信息 
# 不在什么区间可以用 not( * and * ) 或 !( * and * )
SELECT
    *
FROM
    employees
WHERE
    NOT(department_id>=90 AND department_id<=110) OR salary>15000;

SELECT
    *
FROM
    employees
WHERE
    !(department_id>=90 AND department_id<=110) OR salary>15000;

模糊查询

/*
like
between and
in
is null|is not null
*/

like模糊查询

/*
特点：
    一般和通配符使用：
        %   任意多个字符,包含0个
        _   任意单个字符
*/

#案例1：查询员工名包含字符a的员工信息
SELECT last_name FROM employees WHERE last_name LIKE "%a%";

#案例2：查询员工名中包含第二个字符为u，第五个字符为o的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '_u_o%';

#案例3：查询员工名中第二个字符为_的员工名 可以用转义 \ ,也可以用ESCAPE $把 $当成转义符号.
SELECT last_name FROM employees WHERE last_name LIKE "_$_%" ESCAPE "$";
SELECT last_name FROM employees WHERE last_name LIKE "_\_%";

between and可以提高语句的简洁度

#案例1：查询员工编号在100到120之间的员工信息
#第一种方法
SELECT
    *
FROM
    employees
WHERE
    employee_id>=100 AND employee_id<=120;


#第二种方法
SELECT
    *
FROM
    employees
WHERE
    employee_id BETWEEN 100 AND 120;

/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
    ①使用in提高语句简洁度
    ②in列表的值类型必须一致或兼容
    ③in列表中不支持通配符
    

*/

#案例：查询员工的工种编号是IT——PROG、AD_VP、AD_PRES中的一个员工和工种编号
SELECT
    last_name,
    job_id
FROM
    employees
WHERE
    job_id = "IT_PROG" OR job_id="AD_VP" OR job_id="AD_PRES";

SELECT 
    last_name,
    job_id
FROM
    employees
WHERE
    job_id IN("IT_PROG","AD_VP","AD_PRES");

null

/*
=或<>不能用于判断null值
is null或is not null 可以判断null值
*/

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

#案例2：查询有奖金的员工名和奖金率,奖金
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
#安全等于 <=>可以结合null使用
# 可以结合<=>使用，还可以结合变量使用

#案例3：查询没有奖金的员工名和奖金率
SELECT
    last_name,salary,commission_pct
FROM
    employees
WHERE
    commission_pct <=> NULL;

#案例4：查询工资为12000的员工信息
SELECT * FROM employees WHERE salary <=>12000;
#IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
#<=>    :既可以判断NULL值，又可以判断普通的数值，可读性较低

#案例5：查询员工为176的员工的姓名和部门名和年薪
SELECT
    last_name,employee_id,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
    employees
WHERE
    employee_id = 176;
    #employee_id <=>176

SELECT job_id FROM employees;
SELECT DISTINCT job_id FROM employees;

SELECT * FROM employees;
SELECT * FROM employees WHERE commission_pct LIKE "%%" AND last_name LIKE "%%";
SELECT * FROM employees WHERE commission_pct LIKE "%%" OR last_name LIKE "%%";
#获取表的结构
DESC employees;
#查看详细表的结构
show create table employees;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

三、排序查询

/*
语法：
select 查询列表
from 表名
【where  筛选条件】
order by 排序的字段或表达式;


特点：
1、asc代表的是升序，可以省略
desc代表的是降序

2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段

3、order by子句在查询语句的最后面，除了limit子句

*/

按单个字段排序

#这里不写默认是asc代表升序,后面添加desc代表降序
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary DESC;

添加筛选条件再排序

#案例：查询部门编号>=90的员工信息，并按员工编号降序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;

按表达式排序

#案例：查询员工信息  按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

按别名排序

#案例：查询员工信息  按年薪排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;

按函数排序

#案例：查询员工名，并且按名字的长度排序
SELECT LENGTH(last_name) AS 名字长度,last_name
FROM employees
ORDER BY 名字长度 DESC;

SELECT LENGTH(last_name) AS 名字长度,last_name
FROM employees
ORDER BY LENGTH(last_name) ASC;

按多个字段排序

#案例： 查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

练习案例：

#案例1：查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
USE myemployees;
SHOW TABLES;
SELECT last_name AS 姓名,department_id AS 部门号,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,姓名 ASC;

#案例2：选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name 姓名,salary 工资
FROM employees
WHERE 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;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

四、常见函数

/*
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节  2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
    ①叫什么（函数名）
    ②干什么（函数功能）

分类：
    1、单行函数
    如 concat、length、ifnull等
    2、分组函数
    
    功能：做统计使用，又称为统计函数、聚合函数、组函数

常见函数：
    一、单行函数
    字符函数：
    length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
    concat
    substr
    instr
    trim
    upper
    lower
    lpad
    rpad
    replace
    
    数学函数：
    round
    ceil
    floor
    truncate
    mod
    
    日期函数：
    now
    curdate
    curtime
    year
    month
    monthname
    day
    hour
    minute
    second
    str_to_date
    date_format
    load_file
    其他函数：
    version
    database
    user
    控制函数
    if
    case
*/

单行函数

字符函数

#1. length 获取参数值的字节个数
SELECT LENGTH("TOM");
SELECT LENGTH("猪文文");
SHOW VARIABLES LIKE "%char%"; #查看系统的字符集

#2.concat 拼接字符串
SELECT CONCAT(last_name,"_",first_name) 姓名 FROM employees;

#3.upper(将字母都变大)    lower(将字母都变小)
SELECT UPPER("joung");
SELECT LOWER("HOjg");
#示例：将姓变大，名变小，然后拼接
SELECT CONCAT(UPPER(last_name),"-",LOWER(first_name)) FROM employees;

#4. substr  substring
#注意：索引从1开始
#截取从指定索引处后面所有字符 从字符7到结束
SELECT SUBSTR("猪文文爱上一头大蠢猪杨",7) 结果;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR("猪文文爱上一头大蠢猪杨",1,3) 结果;

#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(last_name,"_",first_name) 结果 FROM employees;
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),"_",LOWER(SUBSTR(last_name,2))) 结果 FROM employees;

#5. instr 返回字符串第一次出现的索引，如果找不到返回0,默认索引是从1开始的
SELECT INSTR("猪文文爱上一头大蠢猪文文","你") 结果;

#6. trim 默认只能去掉空格，不能去掉制表符/t,也可以指定去掉的字符(去掉前后的空格或指定其他的字符)
SELECT LENGTH(TRIM('    张翠   山    ')) 结果;
SELECT LENGTH('    张翠   山    ') 结果;
SELECT TRIM('a' FROM 'aaaaa张aaaaa翠山aaaaaaaaaaaaaaaaaaa') 结果;

#7. lpad用于指定字符串实现左填充指定长度
SELECT 
  LPAD("殿速速", 6, "$") 结果;

#8. rpad用于指定字符实现右填充长度
SELECT RPAD("你是谁",6,"$") jieuog;

#9. replace全部替换
SELECT REPLACE("猪宝宝大猪宝宝文文文文","宝","$") jieguo;

#10读取文件且返回文件内容为字符串
update t set blob_col=load_file('/tmp/picture') where id=1;

数学函数

#round 四舍五入
SELECT ROUND(-132.633511) result;
SELECT ROUND(-132.233511) result;
SELECT ROUND(431.5345) result;
SELECT ROUND(431.2345) result;

#ceil 向上取整,返回>=该参数的最大整数(进1法),对正数有用
SELECT CEIL(-12.4564);
SELECT CEIL(12.1545);
SELECT CEIL(12.6654);

#floor 向下取整，返回<=该参数的最大整数(进1法),只能对负数有用,对正数无用
SELECT FLOOR(-9.4564) result;
SELECT FLOOR(-121.1) result;
SELECT FLOOR(16546.5111) result;
SELECT FLOOR(2.4) resultl;

#truncate 截断，小数位截断,不会四舍五入,也不会向下取整
SELECT TRUNCATE(1.45646,1);
SELECT TRUNCATE(-4564.4654,2);
SELECT TRUNCATE(4654,2);

#mod取余
/*
mod(a,b):   等价于a-a/b*b
*/
SELECT MOD(10,-3); #10-(10)/(-3)*(-3) 
SELECT MOD(-10,-3);
SELECT MOD(-10,-3);
SELECT MOD(10,3);

#产生0-1的随机数
SELECT RAND();

#需求产生255-4000的随机数
select ROUND(RAND()*3745+255);

#利用md5产生32位随机数
SELECT MD5(RAND()*1000);
SELECT LENGTH(MD5(RAND()*1000));

#利用rand()和concat()函数生成11位随机数
#生成11位随机手机号函数：CONCAT('1',CEILING(RAND()*9000000000+1000000000));
select concat('1',ceiling(rand()*90000000000+1000000000));

日期函数

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

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

#也可以获取指定的部分,年,月,日,小时,分钟,秒 (YEAR,MONTH,DAY,minute,second)
SELECT YEAR(NOW());
SELECT YEAR("1893-10-12");
SELECT YEAR(hiredate) FROM employees;

SELECT  MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

#str_to_date 将指定字符通过指定格式转换成日期: "%Y-%c-%d"
SELECT STR_TO_DATE("1952_3_5","%Y_%m_%d"); #按照它指定字符格式去取日期
SELECT STR_TO_DATE("1952_3_5","%Y_%c_%d"); #按照它指定字符格式去取日期

#查询日志日期为1992-4-3的员工信息
SELECT hiredate,last_name FROM employees; #1992-04-03 00:00:00  K_ing 
SELECT hiredate,last_name FROM employees WHERE hiredate="1992-4-3";
SELECT hiredate,last_name FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format 将日期转成字符
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日");
SELECT DATE_FORMAT(NOW(),"%y年%m月%d日");
SELECT DATE_FORMAT(NOW(),"%Y年%c月%d日");
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日%W%H时%i分%s秒");
SELECT DATE_FORMAT(NOW(),"%Y年%c月%d日-星期%w-%H时%i分%s秒");

#查询有奖金的员工名和入职日期
SELECT last_name,DATE_FORMAT(hiredate,"%Y年%m月%d日") 日期,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

其他系统函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

流程控制函数

#1.if函数：if-else的效果
SELECT IF(10<3,'小','大');

#当没有奖金的用户表现出(没奖金呵呵),有奖金时变现为(有奖金呵呵)
SELECT last_name,commission_pct 奖金,IF(commission_pct IS NULL,"没奖金 呵呵","有奖金,嘻嘻") 备注
FROM employees
ORDER BY commission_pct DESC;

#2.case函数的使用一：switch case的效果

/*
mysql中
#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 last_name,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;

#case：第二种写法
/*
mysql中：
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
*/
SELECT last_name 姓名,salary 工资,
CASE
WHEN salary>20000 THEN "A"
WHEN salary>15000 THEN "B"
WHEN salary>10000 THEN "C"
ELSE "D"
END 工资登记
FROM employees
ORDER BY salary DESC;

练习题

#案例1：显示系统时间(注：日期+时间)
SELECT NOW();

#案例2：查询员工号，姓名，工资，以及工资提高百分之20后的结果(new salary)
SELECT last_name 姓名,employee_id 员工号,salary 原工资,salary*1.2 AS 新工资
FROM employees;

#案例3：将员工的姓名按首字母排序，并写出姓名的长度(length)
SELECT last_name 姓名,LOWER(SUBSTR(last_name,1,1)) 姓名首字母,LENGTH(last_name) 姓名长度
FROM employees
ORDER BY 姓名首字母 DESC;

SELECT last_name FROM employees;

#案例4： 做一个查询，产生下面的结果
/*
Dream Salary
K_ing earns 24000.00 monthly but wants 72000.00
*/
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
other       D
*/

SELECT job_id job,
CASE job_id
WHEN "AD_PRES" THEN "A"
WHEN "ST_MAN" THEN "B"
WHEN "IT_PROG" THEN "C"
ELSE "D"
END grade
FROM employees
ORDER BY grade ASC;

SELECT job_id FROM employees;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

五、分组函数

/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
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;

SELECT SUM(salary) 总和,TRUNCATE(AVG(salary),2) 平均值取两位,MAX(salary) 最大值,MIN(salary) 最小值,COUNT(salary) 个数
FROM employees;

#2.参数支持哪些类型
SELECT SUM(last_name) FROM employees;

SELECT AVG(last_name) FROM employees;

SELECT MIN(last_name) FROM employees;

SELECT COUNT(last_name) FROM employees;

#3. 是否会忽略null不计
SELECT MAX(last_name) FROM employees;   #null会忽略不计
SELECT MIN(commission_pct) FROM employees; #null会忽略不计
SELECT COUNT(commission_pct) FROM employees; #null会忽略不计

#4.和distinct去重搭配
SELECT SUM(DISTINCT(salary)),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT(salary)),COUNT(salary) FROM employees;

#5. count合并函数的详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; #所需时间慢些在inndob引擎慢，在myisam引擎下快
SELECT COUNT(1) FROM employees; #所需时间快些在inndob引擎快，在myisam引擎下慢
#扩展：查看当前什么存储引擎(利用系统变量和函数进行查询或模糊匹配)
SHOW TABLE STATUS FROM myemployees WHERE NAME="employees";
SHOW TABLE STATUS FROM mysql WHERE NAME="db";
SHOW VARIABLES LIKE "%storage_engine%";

#查看创建的表示什么引擎,
USE myemployees;
SHOW CREATE TABLE jobs; #这条命令不仅可以查看表示用什么引擎，还可以看到怎么创建表的
USE mysql;
SHOW CREATE TABLE db;

#desc table只能查看表的一部分结构
DESC jobs;
SELECT VERSION(); #查看版本号
SHOW ENGINES; #查看系统默认支持的存储引擎
USE mysql;
SHOW TABLES;

#6.和分组函数一同查询的字段有限制
USE myemployees;
SELECT AVG(salary),employee_id FROM employees; #执行不了

练习题

#案例1：查询员工表中的最大入职时间和最小入职时间的相差天数(datediff)
#用到datediff函数来计算两个日期相差的天数
#MAX(hiredate),MIN(hiredate)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate))FROM employees;
SELECT DATE_FORMAT(NOW(),"%Y-%c-%d");
SELECT DATEDIFF(DATE_FORMAT(NOW(),"%Y-%c-%d"),"2019-5-26");
SELECT DATEDIFF(NOW(),"2019-5-26");

#案例2：查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE department_id=90;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

六、分组查询

/*
语法：

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
        针对的表            位置      连接的关键字
分组前筛选   原始表             group by前   where
    
分组后筛选   group by后的结果集           group by后   having
    A.分组函数做条件肯定是放在having字句后
    B.能用分组前筛选的，就优先考虑使用分组前筛选

问题1：分组函数做筛选能不能放在where后面
答：不能

问题2：where——group by——having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

3、group by分组可以按单个字段也可以按多个字段(多个字段之间使用逗号隔开没有顺序要求),也支持表达式或函数(用得比较少)
4、可以搭配着排序order by使用,放在整个分组查询之后的
*/

引入：查询每个部门员工个数

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

简单的分组

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
SELECT DISTINCT job_id FROM employees;

#案例2：查询每个位置的部门个数
SELECT COUNT(*),department_name
FROM departments
GROUP BY department_name;

SELECT COUNT(*) FROM departments;
SELECT * FROM departments;
SHOW CREATE TABLE departments;

可以实现分组前的筛选

#案例1：查询邮箱中包含a字符的每个部门的最高工资
SELECT department_id,MAX(salary)
FROM employees
WHERE email LIKE "%a%"
GROUP BY department_id;

SELECT DISTINCT(department_id)
FROM employees;

#案例2： 查询有奖金的每个领导手下员工的平均工资

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

SELECT DISTINCT(manager_id)
FROM employees
WHERE commission_pct IS NOT NULL;

分组后的筛选

#案例1：查询部门的员工个数大于5个的部门
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id;

SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING 员工个数 > 5;

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
GROUP BY job_id
HAVING MAX(salary)>10000;

SELECT DISTINCT job_id FROM employees;

#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
#manager_id > 102
SELECT manager_id FROM employees WHERE manager_id>102;

SELECT DISTINCT manager_id FROM employees WHERE manager_id>102;
SELECT manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id;

SELECT manager_id,MIN(salary) FROM employees WHERE manager_id>102 GROUP BY manager_id;
SELECT manager_id,MIN(salary) FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;

添加排序

#案例4：每个工种有奖金的员工的最高工资>12000的工种编号最高工资，按最高工资升序
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000
ORDER BY MAX(salary) DESC;

#每个工种有奖金的员工的最高工资>=8000的工种编号最高工资，按最高工资升序
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary) >= 8000
ORDER BY MAX(salary) DESC;

#获取所以的job_id工种编号最高的工资
SELECT job_id,MAX(salary)
FROM employees
GROUP BY job_id;

按多个字段分组

#案例：查询每个工种每个部门的最低工资，并按最低工资降序
SELECT MIN(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY MIN(salary) DESC;

SELECT MIN(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY MIN(salary) DESC;

按表达式或函数分组

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

格式转换函数

#cast()和convert()两个函数都可以用来转换数据类型或者转换字符集
select convert('test',char character set utf8);
select cast('1996-10-6'as date);

练习解析：

#案例1：查询各个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 DESC;

SELECT DISTINCT job_id FROM employees;

#案例2：查询员工最高工资和最低工资的差距(difference)
SELECT (MAX(salary)-MIN(salary)) 差距
FROM employees;

#案例3：查询各个管理者手下员工的最低工资，其中最低的工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

#案例4：查询所有部门的编号，员工数量和工资的平均值，并按平均值工资降序
SELECT department_id FROM employees;

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

#案例5：选择具有各个job_id的员工个数
SELECT COUNT(*),job_id
FROM employees
GROUP BY job_id;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

七.连接查询

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

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

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
    按年代分类：
    sql92标准:仅仅支持内连接
    sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
    
    按功能分类：
        内连接：
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接
            右外连接
            全外连接
        
        交叉连接
*/

USE girls;
SELECT * FROM beauty;
SELECT * FROM boys;

SELECT NAME,boyName FROM boys,beauty; #出现笛卡尔乘积现象出现混乱

等值连接

/*
多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/

#案例1：查询女神名和对应的男神名
#添加有效连接条件(boys.id=beauty.`boyfriend_id`)
SELECT NAME,boyName
FROM boys,beauty
WHERE boys.`id`=beauty.`boyfriend_id`;

#案例2：查询员工名和对应的部门名
SHOW DATABASES;
SELECT DATABASE();
USE myemployees;

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

为表起别名

/*
提高语句的简洁读
区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定了
*/

#查询员工名、工种号、工种名
SELECT e.`last_name`,e.`job_id`,j.`job_title`
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;

#查询各个工种名包含的员工数
SELECT COUNT(*),j.`job_title`
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY e.`job_id`;

SELECT COUNT(*),job_id
FROM employees
GROUP BY job_id;

SELECT *
FROM jobs;

SELECT e.`job_id`,j.`job_title`
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;

两个表的顺序是否可以调换

#查询员工名、工种名、工种号
SELECT e.`last_name`,e.`job_id`,j.`job_title`
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;

可以加筛选条件

#案例：查询有奖金的员工名、部门名
SELECT e.`last_name`,d.`department_name`,e.`commission_pct`
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL;

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

#案例：查询城市市名中第二个字符为o的部门名和城市名
SELECT d.`department_name`,l.`city`
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id` AND l.`city` LIKE "_o%";

可以加分组

#案例1：查询每个城市的部门个数
SELECT COUNT(*) 部门个数,l.`city`
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`city`;

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

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.`department_name`,d.`manager_id`,MIN(e.`salary`)
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL
GROUP BY e.`department_id`,d.`manager_id`;

SELECT MIN(salary)
FROM employees
WHERE commission_pct IS NOT NULL AND department_id IS NOT NULL
GROUP BY department_id;

可以加排序

#案例：查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT j.`job_title`,COUNT(e.`last_name`) 员工个数
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY j.`job_title`
ORDER BY 员工个数 DESC;

可以实现三表或多表连接?

#案例：查询员工名、部门名和所在城市 
SELECT e.`last_name`,d.`department_name`,l.`city`
FROM departments d,employees e,locations l
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;

USE myemployees;
#在myemployees库中新建表job_grades
/*
SELECT salary,employee_id FROM employees;
SELECT * FROM job_grades;
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);
*/

SHOW CREATE TABLE job_grades;

非等值连接

#案例1：查询员工的工资和工资级别
SELECT e.`salary`,jb.`grade_level`
FROM employees e,job_grades jb
WHERE e.`salary` BETWEEN jb.`lowest_sal` AND jb.`highest_sal`;

#查询员工的工资和工资级别，并列出把工资级别为A的所有员工
SELECT e.`salary`,jb.`grade_level`
FROM employees e,job_grades jb
WHERE e.`salary` BETWEEN jb.`lowest_sal` AND jb.`highest_sal` AND jb.`grade_level`='A';

自连接

#案例：查询 员工名和上级的名称
SELECT e.`last_name`,e.`employee_id`,m.`last_name`,m.`employee_id`
FROM employees e,employees m
WHERE e.`manager_id`=m.`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,department_id,salary
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%";

#5.显示当前日期，以及去前后空格，截取字符串的函数
SELECT LENGTH(TRIM(NOW())); 

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

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

八、sql99语法

/*
语法：
    select 查询列表
    from 表1 别名 【连接类型】inner,left [outer],right [outer],full [outer]
    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连接和sq192语法中的等值连接效果一样的，都是查询多表的交集
*/

等值连接

#案例1；查询员工名、部门名
SELECT department_name,last_name
FROM departments d
INNER JOIN employees e
ON e.`department_id`=d.`department_id`;

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

#案例2：查询名字中包含e的员工名和工种名(添加筛选)
SELECT last_name,job_title
FROM employees e
JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE "%e%";

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

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

#案例5：查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
/*错误演示
select department_name,last_name,job_title
from departments d
join employees e,jobs j
on j.`job_id`=e.`job_id` and e.`department_id`=d.`department_id`
order by department_name desc;
*/

SELECT department_name,last_name,job_title
FROM employees e #在中间的值放在from 后面，join分别连接其他多个表名
JOIN departments d ON e.`department_id`=d.`department_id`
JOIN jobs j ON j.`job_id`=e.`job_id`
ORDER BY department_name DESC;

非等值连接

#查询员工的工资级别 employees job_grades
USE myemployees;
SELECT salary,grade_level
FROM employees e
JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

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

自连接

#查询员工的名字、上级的名字
SELECT e.`last_name`,m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

#查询姓名中包含字符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左边的是主表(也就是上面的from表示主表)
    右外连接，right join右边的是主表 (也就是后面的join表示主表)
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
*/

引入：查询男朋友不在男神表中的女神名

#右外连接，rigth join右边的是主表(也就是join后面的表是：主表)
USE girls;
SELECT  b.*
FROM boys bo #这个是附表
RIGHT OUTER JOIN beauty b #这个是主表
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

#左外连接：left join左边的是主表(也就是上面的from的表是: 主表)
USE girls;
SELECT  b.*
FROM beauty b #这个是主表
LEFT OUTER JOIN boys bo #这个是附表
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

#全外（不支持）
USE girls;
SELECT b.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`;

引入：查询哪个部门没有员工

#左外
USE myemployees;
SELECT d.*,e.*
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;

#右外
SELECT d.*
FROM employees e
RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL;

基础案例

#案例1：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null表示
USE girls;
SELECT b.name,b.id,bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3;

#案例2：查询哪个城市没有部门
USE myemployees;
SELECT l.`city`,d.`department_name`
FROM locations l
LEFT JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE d.`department_id` IS NULL;

#案例3：查询部门名为SAL或IT的员工信息
SELECT e.*,d.`department_name`
FROM employees e
JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE d.`department_name` = "SAL" OR d.`department_name`="IT";

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

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

九、子查询

/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

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

一、where或having后面

/*
1、标量子查询(单行子查询)
2、列子查询(多行子查询)

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

特点：
    子查询放在小括号内
    子查询一般放在条件的右侧
    标量子查询，一般搭配着单行操作符使用
    > < >= <= <> 
    
    列子查询，一般搭配着多行操作符使用
    in  any/some    all

子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果

*/

标量子查询 ※

#案例1：谁的工资比Abel高？
SELECT salary
FROM employees
WHERE last_name="Abel";

SELECT *
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name="Abel"
);

#案例2：返回job_id与141号员工相同，salary比143号员工 姓名,job_id和工资
SELECT job_id
FROM employees
WHERE employee_id="141";

SELECT salary
FROM employees
WHERE employee_id="143";

SELECT last_name,job_id,salary
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE employee_id="143"
) AND job_id=(
    SELECT job_id
    FROM employees
    WHERE employee_id="141"
);

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

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资 (在进行分组后在进行筛选having)
#I
SELECT MIN(salary)
FROM employees
WHERE department_id="50";

#II
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id;

#III 在II基础上再筛选，满足min(salary)>0
SELECT MIN(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id="50"
);

#这个是错误的非法使用标量子查询
SELECT MIN(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT salary
    FROM employees
    WHERE department_id="70"
);

列子查询（多行子查询）

#案例1：返回location_id是1400或1700的部门中所有员工姓名
#departments employees
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)
);

SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';

#案例2：返回其他部门中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id 以及salary
#方法一；
SELECT employee_id,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";

#方法二：
SELECT employee_id,last_name,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 employee_id,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";

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

行子查询（结果集一行多列或多行多列）

#案例：查询员工编号最小并且工资最高的员工信息
#方法二：
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 后面

/*
仅仅支持标量子查询
*/

#案例1：查询每个部门的员工个数，null也会返回
#方法一：
SELECT d.`department_name`,COUNT(*)
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_name`;

#方法二：可以将null值也取出来
SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id=d.department_id
) 个数
FROM departments d;

SELECT (
SELECT d.`department_name`
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`employee_id`=102
) 部门名;

三、from后面（将查询到的结果当做一张表来）

#案例：查询每个部门的平均工资的工资登记
SELECT AVG(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;

#将平均工资当做一张表来做工资登记
SELECT avg_dep.*,g.`grade_level`
FROM (
    SELECT AVG(salary) avg_s,department_id
    FROM employees
    WHERE department_id IS NOT NULL
    GROUP BY department_id
) avg_dep
JOIN job_grades g
ON avg_dep.avg_s BETWEEN g.`lowest_sal` AND g.`highest_sal`;

四、exists后面（相关子查询）

#子查询练习解析
#案例1：查询和Zlotkey相同部门的员工姓名和工资
SELECT department_id
FROM departments
WHERE department_name="Zlotkey";

SELECT last_name,salary,department_id
FROM employees
WHERE department_id=(
    SELECT department_id
    FROM employees
    WHERE last_name="Zlotkey"
);

#案例2：查询工资比公司平均工资高的员工的员工号，姓名，工资
SELECT AVG(salary)
FROM employees;

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

#案例3：查询各部门中平均工资比本部门平均工资高的员工的员工号，姓名，和工资
#先查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;

#这里可以把查询到的结果作为一个表，然后再将表和大表进行比较求值，利用join on 内连接sql99语法
SELECT last_name,employee_id,salary,department_id
FROM employees e
JOIN (
    SELECT AVG(salary) avg_s,department_id dep_id
    FROM employees
    WHERE department_id IS NOT NULL
    GROUP BY department_id
) avg_dep
ON e.`department_id`=avg_dep.dep_id
WHERE e.`salary` > avg_dep.avg_s;

#案例4：查询和姓名中包含字母u的员工都在相同部门的员工的员工工号和姓名
#先查包含字母u的员工所在的部门号
SELECT department_id,last_name
FROM employees
WHERE last_name LIKE "%u%";

#这里就相当于求最少两个人同属于在同一部门，且包含字母u的员工
SELECT m.last_name
FROM (
    SELECT department_id,last_name
    FROM employees
    WHERE last_name LIKE "%u%"
) m
JOIN (
SELECT COUNT(*),department_id
FROM (
    SELECT department_id,last_name
    FROM employees
    WHERE last_name LIKE "%u%"
) dep_ne
GROUP BY dep_ne.department_id
HAVING COUNT(*)>=2
) k
ON k.department_id=m.department_id;


SELECT last_name,employee_id
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
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id=1700
);

#案例6：查询管理者是K_ing的员工号，所管理的员工姓名和工资
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 CONCAT(first_name,".",last_name) 姓名
FROM employees
WHERE salary=(
    SELECT MAX(salary)
    FROM employees
);

分页查询

/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
    这里的执行顺序是：(1 2 3 4 5 6 7 8 9
    select 查询列表 (7
    from 表 (1
    【join type join 表2 (2
    on 连接条件 (3
    where 筛选条件  (4
    group by 分组字段 (5
    having 分组后的筛选 (6
    order by 排序的字段】 (8
    limit 【offset,】size; (9
    
    offset要显示条目的起始索引（起始索引从0开始）
    size 要显示的条目个数
特点：
    ①limit语句放在查询语句的最后
    ②公式
    要显示的页数 page，每页的条目数size
    
    select 查询列表
    from 表
    limit (page-1)*size,size;
    
    size=10
    page  
    1   0
    2   10
    3   20

*/

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

测试题

#已知表 stuinfo
/*
id 学号
name 姓名
email 邮箱    john@124.com
gradeId 年级编号
sex 性别 男 女
age 年龄
已知表 grade
id 年级编号
gradeName 年级称号
*/

#一：查询所有学生的邮箱的用户名 （注：邮箱中@前面的字符）
SELECT SUBSTR(email,1,INSTR(email,"@")-1)
FROM stuinfo;

#二：查询男生和女生的个数
SELECT COUNT(*) 个数
FROM stuinfo
GROUP BY sex;

#三：查询年龄>18岁的所有学生的姓名和年级称号
SELECT NAME,gradeName
FROM stuinfo s
JOIN grade g
ON s.gradeId=g.id
WHERE age>18;

#四：查询年龄哪个年级学生最小的年龄>20岁
SELECT MIN(age),gradeid
FROM stuinfo
GROUP BY gradeid;
HAVING MIN(age)>20;

SELECT * FROM girls.`beauty`;
#五：试说出查询语句中涉及到的所有的关键字，以及执行先后顺序
/*
select 查询列表     (7
from 表          (1
连接类型 join 表2    (2
on 连接条件     (3
where 筛选条件      (4
group by 分组列表   (5
having 分组后的筛选   (6
order by 排序列表   (8
limit 偏移，条目数    (9
*/

子查询经典案例题

#案例1：查询工资最低的员工信息：last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

#案例2：查询平均工资最低的部门信息
#方法一：
#1)查询平均工资的各个部门
SELECT AVG(salary) s
FROM employees
GROUP BY department_id;

#2)查询1)结果上的最低工资
SELECT MIN(sd.s)
FROM (
SELECT AVG(salary) s
FROM employees
GROUP BY department_id
) sd;

#3)查询哪个部门的平均工资=2)
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) =(
SELECT MIN(sd.s)
FROM (
SELECT AVG(salary) s
FROM employees
GROUP BY department_id
) sd
);

#4)查询部门信息
SELECT *
FROM departments
WHERE department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) =(
        SELECT MIN(sd.s)
        FROM (
        SELECT AVG(salary) s
        FROM employees
        GROUP BY department_id
        ) sd
    )
);

#方法二：可以用分页查询的方法来进行查询
#1）先查询出平均工资最低的部门，然后用正序排序的方法排列，然后再用limit分页查询出来
SELECT AVG(salary),department_id d
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1;

#2)结合1）查询出部门信息
SELECT dep.* 
FROM departments dep
WHERE department_id=(
    SELECT department_id d
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) ASC
    LIMIT 1
);

#有问题
SELECT dep.*
FROM departments dep
JOIN (
    SELECT AVG(salary),department_id d
    FROM employees e
    GROUP BY e.department_id
    ORDER BY AVG(salary) ASC
    LIMIT 1
) aep
ON dep.`department_id`=aep.d;

#案例3：查询平均工资最低的部门信息和该部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1;

#最终结果
SELECT d.*,ag
FROM departments d
JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) ASC
    LIMIT 1
) aep
ON d.`department_id`=aep.department_id;

#案例4：查询平均工资最高的job信息
SELECT j.*,sj.s
FROM jobs j
JOIN (
SELECT AVG(salary) s,job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
) sj
ON j.`job_id`=sj.job_id;

SELECT DISTINCT job_id FROM employees;

#案例5：查询平均工资高于公司平均部门的工资有哪些部门
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
);

#案例6：查询公司中所有manager的详细信息
#1）查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees;

#2)查询详细信息，满足manager_id=1)
SELECT *
FROM employees
WHERE employee_id =ANY(
    SELECT DISTINCT manager_id
    FROM employees
);

#案例7：各个部门中 最高工资中最低的那个部门 的最低工资是多少
#最高工资中最低的那个部门
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1;

#最终结果
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary) ASC
    LIMIT 1 
);

#案例8：查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary
#平均工资最高的部门
    SELECT department_id,AVG(salary)
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) DESC
    LIMIT 1;
#获得manager的员工编号
SELECT manager_id
FROM employees
WHERE department_id=(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
);

#查询详细信息
SELECT *
FROM employees
WHERE employee_id =ANY(
    SELECT manager_id
    FROM employees
    WHERE department_id=(
        SELECT department_id
        FROM employees
        GROUP BY department_id
        ORDER BY AVG(salary) DESC
        LIMIT 1
)
);

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十：联合查询

/*
union 联合 合并：将多条查询语句的结果合并成一个结果

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

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

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

#引入案例：查询部门编号>90或邮箱包含a的员工信息
USE myemployees;
SELECT * FROM employees WHERE email LIKE "%a%" OR department_id>90;

SELECT * FROM employees WHERE department_id > 90
UNION
SELECT * FROM employees WHERE email LIKE "%a%";

#案例：查询中国用户中男性的信息以及外国用户中男性的信息
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,值2,值3,....值n)
*/

#查看girls的beauty表
SELECT * FROM girls.beauty;

插入的值的类型要与列的类型一致或兼容(values这里不用空格隔开的)

USE girls;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,"唐艺昕","女",'1990-4-23','18865471245',NULL,8);

不可以为Null的列必须要插入值，可以为null的列如何插入值?

#方式一：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(14,"Alice","女",'1989-10-5','120',NULL,7);

#方式二：
SELECT * FROM girls.beauty;
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'Toms','女','192');

列的顺序可以调换

INSERT INTO beauty(NAME,id,phone,sex)
VALUES('刘亦菲',20,'1478','女');

和数值个数必须一致
省略列名，默认所有列，而且列的顺序和表中的列的顺序一致

SELECT * FROM girls.beauty;
INSERT INTO beauty
VALUES(21,'朱茵','女','1990-12-1','180',NULL,7);

方式二：用set 列名=值

/*
语法：
insert into 表名
set 列名=值,列名=值,...
*/

INSERT INTO beauty
SET id=22,NAME="巩俐",phone='196464';

values和set两种方式的不同

#1.values支持多行的插入，set不支持
INSERT INTO beauty(id,NAME,phone)
VALUES(30,'A1','123'),
(31,'A2','124'),
(32,'A3','125'),
(33,'A4','126');

#2.values支持子查询插入，set不支持
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1464'
FROM boys WHERE id<3;

二、修改语句

/*
1.修改单表的记录
语法：
update 表名
set 列=值,列=新值,...

2.修改多表的记录【补充】
语法：
sql92语法：
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;
语法：
sql99语法：
update 表1 别名
inner | left | right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;

*/

修改单表的记录

#案例1：修改beauty表中姓A的女神的性别为男
UPDATE beauty SET sex="男"
WHERE NAME LIKE"A%";

修改多变的记录

#案例1：修改张无忌的女朋友的手机号为1314
UPDATE beauty b
JOIN boys bo
ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='1314',bo.`userCP`=1000
WHERE bo.`boyName`="张无忌";

SELECT b.*
FROM beauty b
JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`="张无忌";

SELECT * FROM boys;

#案例2：修改没有男朋友的女神的男朋友编号都为2号
UPDATE beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

SELECT b.*
FROM beauty b
JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`="鹿晗";

三、删除语句

/*
方式一：delete
语法：
    1、单表的删除
    delete from 表名 where 筛选条件
    
    2、多表的删除【补充】
    sql92语法：
    delete 表1的别名，表2的别名
    from 表1的别名，表2的别名
    where 连接条件
    and 筛选条件;
    
    sql99语法：
    delete 表1的别名,表2的别名
    from 表1 别名
    inner | left | right join 表2 别名 on 连接条件
    where 筛选条件;
*/

单表的删除（整行的删除）

#案例：删除手机号以9结尾的女神信息
SELECT * FROM beauty WHERE phone LIKE "%9";
DELETE FROM beauty WHERE phone LIKE "%9";
SELECT * FROM beauty;

多表的删除

#案例：删除张无忌的女朋友的信息
DELETE b
FROM beauty b
JOIN boys bo
ON bo.id=b.boyfriend_id
WHERE bo.boyName="张无忌";

SELECT b.*
FROM beauty b
JOIN boys bo
ON bo.`id`=b.`boyfriend_id`
WHERE bo.`boyName`="张无忌";

#案例：删除黄晓明的信息以及他的女朋友的信息
#这里是查看黄晓明的女朋友信息
SELECT b.*,bo.*
FROM boys bo
JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
WHERE bo.`boyName`="黄晓明";

#下面是删除的
DELETE bo,b
FROM boys bo
JOIN beauty b
ON bo.id=b.boyfriend_id
WHERE bo.boyName="黄晓明";

#truncate语句进行删除的语句的操作
#truncate 不能删除行数据,要删就要把表清空

#案例：将表中的数据都清空
TRUNCATE TABLE boys;
#drop 删除一个表
#drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。

delete和truncate的区别 (面试题)

/*
1.delete可以加where条件进行删除，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长序列，
如果用delete删除后，再插入数据，自增长列的值从断点开始。
而truncate删除后，再插入数据，自增长列的值从1开始+

4.truncate删除后没有返回值，delete删除后有返回值
5.truncate删除后信息不能回滚，delete删除后信息有回滚
*/

#下面这些是测试语句
SELECT * FROM boys;
DELETE FROM boys;

TRUNCATE TABLE boys;

INSERT INTO boys (boyName,userCP)
VALUES("张飞",100),("刘备",100),("关云长",101);

数据的增删改的操作练习

#1.运行以下脚本创建my_employees表和users表
USE girls;
CREATE TABLE my_employees(
    Id INT(10),
    First_name VARCHAR(10),
    Last_name VARCHAR(10),
    Userid VARCHAR(10),
    Salary DOUBLE(10,2)
);
CREATE TABLE users(
    id INT,
    userid VARCHAR(10),
    department_id INT
);

#2.显示表的my_empoyees表的结构
DESC my_employees;
SHOW CREATE TABLE my_employees;

#3. 向my_employees表中插入下列数据
/*
ID  FIRST_NAME  LAST_NAME   USERID  SALARY
1   patel       Ralph       Rpatel  895
2   Dancs       Betty       Bdancs  860
3   Biri        Ben     Bbiri   1100
4   Newman      Chad        Cnewman 750
5   Ropeburn    Audrey      Aropebur    1550
*/
#方法一：
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbriri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1150);

SELECT * FROM my_employees;
DELETE FROM my_employees;

#方法二：
TRUNCATE TABLE my_employees;
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbriri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1150;
SELECT * FROM my_employees;

#4.向表users中插入以下数据
/*
1   Rpatel  10
2   Bdancs  10
3   Bbiri   20
4   Cnewman 30
5   Aropebur    40
*/
INSERT INTO users
VALUES(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20);
SELECT * FROM users;

DESC users;

#5.将所有工资少于900的员工的工资修改为1000
UPDATE my_employees
SET salary=1000
WHERE salary<900;

#6.将3号员工的last_name修改为"drelxer"
UPDATE my_employees
SET last_name='drelxer'
WHERE id=3;

#7.将userid 为Bbiri的user表和my_employees表的记录全部删除
DELETE u,m
FROM users u
JOIN my_employees m
ON u.userid=m.Userid
WHERE u.userid="Bbiri";

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十一：数据的定义语言

/*
库和表的管理
一、库的管理
创建、修改、删除

二、表的管理
创建、修改、删除

创建：create
修改：alter
删除：drop
*/

I、库的管理

库的创建

/*
语法：create database [if not exists]库名;
*/

#案例：创建库Books
CREATE DATABASE IF NOT EXISTS Books;

#也可以在创建库的时候加入字符集
CREATE DATABASE IF NOT EXISTS Message CHARACTER SET utf8mb4;
CREATE DATABASE IF NOT EXISTS test CHARSET utf8mb4 COLLATE utf8mb4_bin;

库的修改

#不适合用，现在被删除了
RENAME DATABASE books TO book;

#更改字符集
ALTER DATABASE books CHARACTER SET utf8mb4;

#查看系统字符集
SHOW VARIABLES LIKE 'collation_%';

SHOW VARIABLES LIKE 'character_set_%';

库的删除

DROP DATABASE IF EXISTS books;

II、表的管理

表的创建

/*
语法：
create table 表名(
    列名 列的类型 【(长度) 约束】,
    列名 列的类型 【(长度) 约束】,
    ..
    列名 列的类型 【(长度) 约束】
);
*/

#案例：创建Book表
CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;

USE books;
CREATE TABLE book(
    id INT,
    bName VARCHAR(20),
    pice DOUBLE,
    authorId INT,
    publishDate DATETIME
);
DESC book;
SHOW CREATE TABLE book;

#案例：创建author表
USE girls;
CREATE TABLE IF NOT EXISTS books.author(
    id INT,
    au_name VARCHAR(20),
    nation VARCHAR(20)
);

DESC author;
SHOW CREATE TABLE author;
SHOW CREATE DATABASE books;

表的修改

/*
语法：
    alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
    
    修改表的存储引擎
    alter table 表名 engine=MyISAM;
*/

#1)修改列名 这里也要添加修改列的类型 change column
#修改book表中的publishDate列名改为pubDate
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
ALTER TABLE book CHANGE COLUMN bName bn VARCHAR(20);

#2)修改列的类型或约束 modify column
DESC book;
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
SHOW CREATE TABLE book;

#3)添加新列 add column
ALTER TABLE author ADD COLUMN salary DOUBLE;

#4)删除列 drop column
ALTER TABLE author DROP COLUMN salary;

#alter table 表名 rename to 新表名
#5)修改表名
ALTER TABLE author RENAME TO book_author;

#修改表的存储引擎
USE books;
SHOW CREATE TABLE author;
ALTER TABLE author ENGINE=MYISAM;

表的删除

DROP TABLE IF EXISTS book_author;
SHOW TABLES;

#表的清空
DELETE FROM book_author;
TRUNCATE FROM book_author;

#通用写法
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 新表名;

DROP TABLE IF EXISTS author;
CREATE TABLE author(
    id INT,
    au_name VARCHAR(20),
    nation VARCHAR(20)
);

表的复制

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

#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;

#2.复制表的结构+数据,或复制部分数据+结构
CREATE TABLE copy2
SELECT * FROM author;

CREATE TABLE cp1
SELECT id,au_name
FROM author
WHERE nation="中国";

#只复制部分结构不包含数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE 1=2;


CREATE TABLE cp2
SELECT au_name
FROM author
WHERE 0;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十二：常见的数据类型

/*
数值型：
    整型
    小数：
        定点数
        浮点数
    字符型：
        较短的文本：char varchar
        较长的文本：text blod(较长的二进制数据)
    
    日期型:datetime,timestemp
*/

I、整型

/*
分类：
tinyint, smallint, mediumint, int/integer, bigint
1     2     3   4       5
特定：
1)如果不设置无符号还是有符号，默认就是有符号的，如果想设置无符号的，需要添加unsigned关键字，无符号就是没有负数，从0开始
2)如果插入的数值超出了整型的范围，会报out of range 异常，并且插入的值为临界值
3)如果不设置长度，会有默认的长度
长度代表了显示的宽度，如果不够用会用0在左边填充，但必须搭配zerofill使用!
使用zerofill 默认就是无符号的unsigned
*/

CREATE DATABASE IF NOT EXISTS DATA CHARSET utf8mb4  COLLATE utf8mb4_bin;
USE DATA;
SHOW DATABASES;
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
    t1 INT(7) UNSIGNED,
    t2 INT(7),
    t3 INT(7) ZEROFILL
);
DESC tab_int;

SELECT * FROM tab_int;
INSERT INTO tab_int VALUES(-1234,-1234,-1234);
INSERT INTO tab_int VALUES(04,-1234,1234);
INSERT INTO tab_int VALUES(0,-1234,0);

II、小数

/*
分类：
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)

M:整数部位+小数部位
D：小数部位
M和D都可以省略
如果是decimal,则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
*/

DROP TABLE IF EXISTS tab_float;
CREATE TABLE tab_float(
    f1 FLOAT,
    f2 DOUBLE,
    f3 DECIMAL(10,4)
);

SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.3453,123.3453,123.3453);
INSERT INTO tab_float VALUES(123.345,123.3453,123.3453);
INSERT INTO tab_float VALUES(123.3,123.3,123.3);
INSERT INTO tab_float VALUES(123.45,123.456745,123456.465);

III、字符型

/*
char
varchar
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合

较长的文本：
text
blob(较大的二进制)
*/

DROP TABLE IF EXISTS tab_char;
CREATE TABLE tab_char(
    c1 CHAR(4),
    c2 VARCHAR(4)
);

INSERT INTO tab_char VALUES('char','char');
SELECT * FROM tab_char;
INSERT INTO tab_char VALUES('char','char');

DROP TABLE IF EXISTS tab_char;
CREATE TABLE tab_char(
    c1 ENUM('a','b','c','6')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('d');
INSERT INTO tab_char VALUES("6");
SELECT * FROM tab_char;


DROP TABLE IF EXISTS tab_char2;
CREATE TABLE tab_char2(
    c2 SET('a','b','c','d')
);
INSERT INTO tab_char2 VALUES('a');
INSERT INTO tab_char2 VALUES('A,B,C');
INSERT INTO tab_char2 VALUES('a,b,c');
SELECT * FROM tab_char2;

IV、日期型

/*
分类：
date只保存日期
time只保存时间
year只保存年


datetime保存日期+时间
timestamp保存日期+时间
*/

DROP TABLE IF EXISTS tab_date;
CREATE TABLE tab_date(
    t1 DATETIME,
    t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT t1 时间1,t2 时间2 FROM tab_date;

SHOW VARIABLES LIKE 'time_zone';

SET time_zone='system';

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十三：常见约束

/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：六大约束
    not null: 非空，用于保证该字段的值不能为空
    比如：姓名、学号等
    default:默认，用于保证该字段有默认值
    比如：性别
    primary key:主键，用于保证该字段的值具有唯一性，并且非空
    比如：学号、员工编号等
    unique:唯一键，用于保证该字段的值具有唯一性，可以为空
    比如：座位号
    check:检查约束【mysql不支持】
    比如：年龄、性别
    foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表中的关联列的值
    
    在外表中添加外键约束，用于引用主表中某列的值
    比如学生表的专业编号，员工表的部门编号，员工表的工种编号
*/

简单的创建表添加列级约束

USE student;
DROP TABLE IF EXISTS stuinfo1;
CREATE TABLE stuinfo1(
    id INT PRIMARY KEY,#主键
    stuName VARCHAR(20) NOT NULL UNIQUE,#非空唯一键
    gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
    seat INT UNIQUE,#唯一
    age INT DEFAULT 18,#默认约束
    majorId INT REFERENCES major1(id) #外键（写在列级约束里不起作用）
);

CREATE TABLE major1(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);

DESC stuinfo1;
#查看stuinfo1中所有的索引，包括主键、外键、唯一键
SHOW INDEX FROM stuinfo1;

添加表级约束

/*
语法：在各个字段的最下面
    [constraint 外键名] 约束类型(字段名)
    
    创建表级的外键的语法：
        [constraint <外键名>] foreign key <从表的>字段名1 [,字段名2,...] references <主表名> 主键列1, [,主键列2,..]
*/

DROP TABLE IF EXISTS stuinfo2;
CREATE TABLE stuinfo2(
    id INT,
    stuName VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat), #唯一键
    CONSTRAINT ck CHECK(gender='男' OR gender='女'), #check检查键
    CONSTRAINT fk_stuinfo2_major2 FOREIGN KEY(majorid) REFERENCES major2(id) #外键
);

DROP TABLE IF EXISTS major2;
CREATE TABLE major2(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);
DESC stuinfo2;
SHOW INDEX FROM stuinfo2;


DROP TABLE IF EXISTS major;
SHOW TABLES;

/*
unique和primary key的区别：
一个表中可以有多个字段声明为UNIQUE,但只能最多有一个primary key声明,
声明为primary key的列不允许有空值,但只是声明为unique的字段允许空值null的存在。

*/

#通用写法
CREATE TABLE IF NOT EXISTS stuinfo3(
    id INT PRIMARY KEY,
    stuname VARCHAR(20),
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    marjorid INT,
    CONSTRAINT fk_stuinfo_marjorid FOREIGN KEY(marjorid) REFERENCES major(id)
);
SHOW CREATE TABLE stuinfo3;
DROP TABLE IF EXISTS major3;
DESC stuinfo3;
CREATE TABLE major3(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);

修改表时添加约束

/*
1.添加列级约束
alter table 表名 modify change column 字段名 字段类型 新约束;

2.添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    sex CHAR(1),
    age INT,
    seat INT,
    marjorid INT
);
DESC stuinfo;

#添加非空，主键，默认，唯一（方法一）
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo CHANGE age age INT DEFAULT 18 NOT NULL;


#添加主键，非空，默认，唯一（方法二）设置默认参数不能用ADD
ALTER TABLE stuinfo ADD UNIQUE(seat);
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#删除外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(marjorid) REFERENCES major(id);
DESC stuinfo;
SHOW CREATE TABLE stuinfo;
SHOW INDEX FROM stuinfo;

SHOW CREATE TABLE stuinfo2;
#三、删除表时的约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
DESC stuinfo;
#删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#删除唯一键
ALTER TABLE stuinfo DROP INDEX seat;
#删除外键
ALTER TABLE stuinfo  DROP FOREIGN KEY fk_stuinfo_major;
ALTER TABLE stuinfo MODIFY COLUMN id INT;
SHOW INDEX FROM stuinfo;

#系统信息函数
#1)查看系统用户的总连接数
SELECT CONNECTION_ID();

SHOW PROCESSLIST;

SHOW FULL PROCESSLIST;

SELECT DATABASE();
SELECT SCHEMA();

SELECT USER();

SELECT CURRENT_USER();
SELECT SYSTEM_USER();

SELECT CHARSET(VERSION());

SELECT PASSWORD('yag');

SELECT MD5('yang');

SHOW PROCEDURE STATUS LIKE 's%';

标识列

/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值


特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值，设置起始值
*/

USE student;
DROP TABLE IF EXISTS tab_identify;

#Incorrect table definition; there can be only one auto column and it must be defined as a key(你必须插入一个Key的值)

CREATE TABLE tab_identify(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
    #seat int
);
TRUNCATE TABLE tab_identify;
INSERT INTO tab_identify(id,NAME) VALUES(10,'yang');
INSERT INTO tab_identify(NAME) VALUES('zhuwenwne');
SELECT * FROM tab_identify;

#这里显示自增长的默认值，auto_increment_increment=1 这个是步长，auto_increment_offset=1这里是初始化值不能修改
#只对当前的环境有影响
SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3;
SET auto_increment_increment=1;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十四、事务TCL事务控制语言

/*
事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

例如：转账案例

事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行，要么都不执行
一致性：一个事务执行会使数据从一个一致状态到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据

事务的创建：
隐式事务：事务没有明显的开启和结束的标记
比如：insert update delete语句

显示事务：事务具有明显开启和结束的标记
前提：必须设置自动提交功能为禁用

set autocommit=0;
步骤1：开启事务
set autocommit=0
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1；
语句2；
...

步骤3：结束事务
commit;提交事务
rollback;回滚事务

savapoint 节点名;设置保存点

事务的隔离级别：       脏读      不可重复读           幻读
read uncommitted    √       √       √
(读取未提交)

read committed      ×       √       √
(读取已提交数据)

repeateable read    ×       ×       √
(可重复读)

serializable        ×       ×       ×
(串行化)

mysql中默认第三个隔离级别 repeateable read
oracle中默认第二个隔离级别 read committed

查看隔离级别

show @@tx_isolation;

设置隔离级别

set session|global transaction isolation level 隔离级别;

开启事务的语句：

update 表 set 张三丰的余额=500 where name='张三丰'
update 表 set 郭襄的余额=1500 where name='郭襄'

脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.
之后, 若 T2 回滚, T1读取的内容就是临时且无效的.

不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.
之后, T1再次读取同一个字段, 值就不同了.

幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插
入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行

*/

SHOW VARIABLES LIKE 'autocommit';

#查看当前的mysql提供什么存储引擎
SHOW ENGINES;

#查看当前的系统默认的存储引擎
SHOW VARIABLES LIKE '%storage_engine%';

#SHOW STATUS 查看MySQL服务器状态
SHOW STATUS;

#如果我们需要查询本次服务器启动之后执行select语句的次数，可以执行如下命令：
SHOW STATUS LIKE 'com_select';

#查看试图连接到MySQL(不管是否连接成功)的连接数
SHOW STATUS LIKE 'connections';
SHOW PROCESSLIST;

SHOW CREATE DATABASE DATA;
SHOW STATUS;

SHOW VARIABLES LIKE 'autocommit';
USE DATA;
DROP TABLE IF EXISTS account;
CREATE TABLE account(
    id INT,
    NAME VARCHAR(10),
    balance INT
);
INSERT INTO account VALUES(1,'张无忌',1000);
INSERT INTO account VALUES(3,'赵敏',1000);
SELECT * FROM account;

演示事务的使用步骤

SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=500 WHERE NAME='张无忌';
UPDATE account SET balance=1500 WHERE NAME='赵敏';
#结束事务
COMMIT;
SELECT * FROM account;

SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=1000 WHERE NAME='张无忌';
UPDATE account SET balance=1000 WHERE NAME='赵敏';
#结束事务
ROLLBACK;
#COMMIT;
SELECT * FROM account;
SHOW SESSION VARIABLES;

演示savepoint的使用

SET autocommit=0;
SHOW VARIABLES LIKE 'autocommit';
DELETE FROM account WHERE id=1;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=3;
ROLLBACK TO a;#回滚到保存点

INSERT INTO account VALUES(1,'张三丰',10000);
SELECT * FROM account;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十五、视图

/*
视图  create view     只是保存了sql逻辑  增删改查，只是一般不能增删改

表   create table    保存了数据       增删改查

*/

#案例：查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
JOIN major m ON s.`majorid`=m.`id`
WHERE s.`stuname` LIKE '张%';

CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
JOIN major m ON s.`majorid`=m.`id`;

SELECT * FROM v1 WHERE stuname LIKE '张';

I、创建视图

/*
语法：
create view 视图名
as 
查询语句
*/

USE myemployees;
#1.查询姓名中包含a字母的员工名、部门名和工种信息
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id=d.department_id
JOIN jobs j ON e.job_id=j.job_id;

#使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';

#查看视图的结构
DESC myv1;
SHOW CREATE VIEW myv1;
SHOW TABLES;

#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
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;


#3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY myv2.`ag` LIMIT 1;


#4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY myv2.`ag` LIMIT 1;

SELECT d*,m.`ag`
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;

II.视图的修改

方式一：

/*
create or replace view 视图名
as
查询语句
*/

DESC myv3;
SHOW CREATE VIEW myv3;

#查看视图的表的结果
SELECT * FROM myv3;

#修改视图myv3如果不存在则会重新建视图myv3
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

方式二、

/*
语法：
alter view 视图名
as
查询语句
*/

ALTER VIEW myv3
AS
SELECT * FROM employees;

SELECT * FROM myv3;

III. 删除视图(可以删除多个视图名)

/*
语法：drop view 视图名,视图名,...;
*/
SHOW TABLE STATUS WHERE COMMENT='view';

DROP VIEW myv1,myv2,myv3;

VI、查看视图

#查看所有的视图(不包括表)
SHOW TABLE STATUS WHERE COMMENT='view';

SHOW TABLES;#查看所有的表和视图

DESC myv3;#查看所有视图的结构

IV、视图的更新(一般不推荐对视图的更改，删除等操作)

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
#1.插入(对原视图employees也会做修改)
SELECT * FROM myv1;
SELECT * FROM employees;
INSERT INTO myv1 VALUES('张飞','zf@email.qq.com');

#2.修改
UPDATE myv1 SET last_name='张无忌' WHERE last_name='张飞';

#3.删除
DELETE FROM myv1 WHERE last_name='张无忌';

具备以下特点的视图不能更新

#1)包含于关键字的sql语句：分组函数:distinct、grop by、 having、 union或者union all

#2)常量视图
CREATE OR REPLACE VIEW myv1
AS
SELECT 'yang' NAME;
SELECT * FROM myv1;

#尝试更新
UPDATE myv1 SET NAME='zhuwenwen';

#3)select中包含子查询
CREATE OR REPLACE VIEW myv2
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;

#尝试更新
SELECT * FROM myv2;
UPDATE myv2 SET 最高工资=1000;

#4)from一个不能更新的视图
CREATE OR REPLACE VIEW myv3
AS 
SELECT * FROM myv2;

#尝试更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=10000 WHERE department_id=60;


#5)where字句的子查询引用了from 字句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN (
    SELECT manager_id
    FROM employees
    WHERE manager_id IS NOT NULL
);

#尝试更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name='K_ing';

案例讲解

#1.创建视图emp_v1，要求查询电话号码以'011'开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email,phone_number
FROM employees
WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1;

#2.创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) max_dep,department_id
FROM employees
GROUP BY department_id
HAVING max_dep>12000;

SELECT * FROM emp_V2;

SELECT d.*,m.`max_dep`
FROM departments d
JOIN emp_v2 m ON m.`department_id`=d.`department_id`;

#案例
#声明并初始化
SET @name:='john';
USE myemployees;

#赋值
SELECT COUNT(*) INTO @count
FROM employees;

#查看自定义变量
SELECT @count;
SELECT NOW();

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十六、变量

/*
系统变量：
    全局变量
    会话变量

自定义变量：
    用户变量
    局部变量
*/

一、系统变量

/*
说明：变量由系统定义，不是用户自定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
使用步骤：
    1.查看所有系统变量
    show global[session] variables;
    2.满足条件的部分系统变量
    show global|[session] variables like '%char%';
    3.查看指定的系统变量的值
    select @@global|[session] 系统变量名
    4.为某个系统变量赋值
    方式一：
    set global|[session] 系统变量名=值;
    方式二：
    set @@global|[session] 系统变量名=值;

*/

#1)全局变量
/*
作用域：针对于所有会话(连接)有效，但不能跨重启
*/
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;

SHOW GLOBAL VARIABLES LIKE '%char%';

#查看指定的系统变量值
SELECT @@global.autocommit;

#为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=1;

#2) 会话变量
/*
作用域：针对于当前会话（连接）有效
*/
#①查看所有会话变量
SHOW SESSION VARIABLES;
#②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

二、自定义变量

/*
使用步骤：
    1、声明
    2、赋值
    3、使用(查看、比较、运算等)
*/

#1）用户变量
#方式一：
SET @wen='zhubaobao';
SELECT @wen;

#方式二：
SELECT * FROM employees WHERE last_name='K_ing';
SELECT * FROM employees;
SELECT last_name INTO @yang
FROM employees
WHERE last_name='Kochhar';

SELECT '我是猪' INTO @y;
SELECT @yang;
SELECT @y;

#2.局部变量
/*
作用域：仅仅在定义它的begin end中有效
*/

#声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;


#案例：声明两个变量，求和并打印
SET @m=1;
SET @n=2;
SET @sum=@n+@m;
SELECT @sum;


#局部变量
BEGIN 
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 2;
    DECLARE SUM INT;
    SET SUM=i+j;
    SELECT SUM;
END
BEGIN
    #Routine body goes here...
    #变量的定义
    DECLARE var2 INT(4);

    #变量的赋值方式一：直接赋值
    SET var2 = 2;
    
    #变量的赋值方式二：将查询结果赋值给变量
    SELECT u_id INTO var2 FROM users WHERE u_name = 'zhangsan';
END

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十七、存储过程和函数

#------------------------------------------
/*
存储过程和函数：类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作
*/

#存储过程
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
*/

一. 创建语法:procedure

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体（一组合法的SQL语句）
END

#注意：
/*
1、参数列表包含三部分
参数模式  参数名  参数类型
举例：
in stuname varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
[delimiter $]修改默认的命令结束符为[$]，函数体以[$]结束，[delimiter ;]恢复默认的结束符为[;]。
*/

二、调用语法

CALL 存储过程名(实参列表);

#-----------------------案例演示---------------------------
#1.空参列表
#案例1：插入到admin表中的5条记录
SELECT * FROM admin;
DESC admin;
USE girls;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username,`password`)
    VALUES('tom','0000'),('alice','1000'),('tial','1000'),('apple','1000'),('juse','11000'),;
END $

SHOW TABLES;

#2.创建带in模式参数的存储过程
#案例1：创建存储过程 根据女神名，查询对应的男神信息
USE myemployees;
USE girls;

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
    JOIN beauty b ON b.boyfriend_id=bo.id
    WHERE b.name=beautyName;
END $

#调用myp2存储函数
CALL myp2('柳岩')$

#案例2：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20) IN PASSWORD VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0;#声明并初始化
    SELECT COUNT(*) INTO result #赋值
    FROM admin
    WHERE admin.`username`=username AND admin.`password`=`password`;
    SELECT IF(result>0,'成功','失败');
END $

#调用
CALL myp3('张飞','88888')$

#3.创建out模式参数的存储过程
#案例1:根据输入的女神名，返回对应的男神名
CREATE PROCEDURE myp6(IN byN VARCHAR(20),OUT boN VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO boN
    FROM beauty b
    JOIN boys bo ON b.boyfriend_id=bo.id
    WHERE b.name=byN;
END $
#调用
CALL myp6('小昭',$boN)$ #这里的$boN相当于自定义变量boN的值等于myp6的boN的值

#案例2：根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP VARCHAR(20))
BEGIN
    SELECT bo.boyName,bo.userCP INTO boyName,userCp
    FROM boys bo
    JOIN beauty b ON b.boyfriend_id=bo.id
    WHERE b.name=beautyName;
END $

#调用并定义变量的值
CALL myp7('小昭',@boN,@userCP)$
SELECT @boN,@usercp;

#4.创建带inout模式参数的存储过程
#案例1：传入a和b连个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT,INOUT b)
BEGIN
    SET a=a*2;
    SET b=b*2
END $

#定义变量
SET @m=1000$
SET @n=2000$
#调用定义的变量值
CALL myp8(@m,@n)$
SELECT @m,@n$

SHOW PROCEDURE;

四、删除存储过程

#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p5,p3;#不能进行多个删除存储过程名

五、查看存储过程信息，和创建所有的存储名

#查看所在库的所有创建的存储名
SHOW PROCEDURE STATUS WHERE db='girls';

#查看存储过程信息
DESC myp3; #不能查看

SHOW CREATE PROCEDURE myp3;
SHOW CREATE PROCEDURE myp4;

存储的案例解析

#1）创建存储过程实现传入用户名和密码，插入到admin表中
DELIMITER $
CREATE PROCEDURE p1(IN uname VARCHAR(20),IN pword VARCHAR(20))
BEGIN
    INSERT INTO admin (username,PASSWORD) VALUES(uname,pword);
END $

CALL p1('张飞','110')$

#2）创建存储过程或函数实现传入女神编号，返回女神名称和女神电话
CREATE PROCEDURE p2(IN bid INT,OUT bname VARCHAR(20),OUT bphone VARCHAR(20))
BEGIN
    SELECT b.name,b.phone INTO bname,bphone
    FROM beauty b
    WHERE b.id=bid;
END $
CALL p2(2,@name,@phone)$
SELECT @name 姓名,@phone 电话号码$

#3）创建存储过程或函数实现传入两个女神生日，返回大小
CREATE PROCEDURE p3(IN bdate1 DATETIME,IN bdate2 DATETIME,OUT result INT)
BEGIN
        SELECT DATEDIFF(bdate1,bdate2) INTO result;
END $
CALL p3('1998-1-1',NOW(),@result)$
SELECT @result$

#4）创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回
CREATE PROCEDURE p4(IN mydate DATETIME,OUT strdate VARCHAR(50))
BEGIN
    #date_format将日期转字符
    SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strdate;
END$
CALL p4(NOW(),@str)$
SELECT @str$

#5）创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串
#如 传入： 小昭
#   返回： 张无忌
DROP PROCEDURE p5$
CREATE PROCEDURE p5(IN bname VARCHAR(20),OUT str VARCHAR(50)) 
BEGIN
    SELECT CONCAT(bname,' and ', IFNULL(bo.boyName,'null')) INTO str
    FROM boys bo
    JOIN beauty b ON bo.id=b.boyfriend_id
    WHERE b.name=bname;
    SET str=
END$ 
CALL p5('小昭',@name)$
SELECT @name$

 #6）创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录
 CREATE PROCEDURE p6(IN size INT,IN startindex INT)
 BEGIN
    SELECT * FROM beauty LIMIT startindex,size;
 END $
 CALL p6(3,5)$

 SELECT * FROM beauty LIMIT 2,4;

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十八、函数

/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果
*/

一、创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END $

#注意：
#1.参数列表 包含两部分
#参数名 参数类型


#2.函数体：肯定会有return语句，如果没有会报错
#如果return语句没有放在函数体的最后也不会报错，但不建议

#retur 值
#3.函数体重仅有一句话，则可以省略begin end
#4.使用delimiter语句设置结束标记

二、调用语法 SELECT 函数名(参数列表)

无参有返回

#案例：返回公司的员工个数
USE myemployees;
DROP FUNCTION f1;
DELIMITER $
CREATE FUNCTION f1() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0;#定义局部变量
    SELECT COUNT(*) INTO c#赋值
    FROM employees;
    RETURN c;
END $

#查看函数体
SELECT f1()$

当出现了这个错误的时候，就是MySQL默认是不允许创建函数

/* ERROR 1418 (HY000): This FUNCTION has NONE of DETERMINISTIC, NO SQL, OR READS SQL DATA IN its declaration AND BINARY l
ging IS enabled (you might want TO USE the LESS safe log_bin_trust_function_creators variable) */
</font>

#1.更改全局配置
SET GLOBAL log_bin_trust_function_creators=1;

SHOW VARIABLES LIKE 'log_bin%';

SET GLOBAL log_bin_trust_function_creators=1;

#2、更改配置文件my.cnf  
LOG-BIN-trust-FUNCTION-creators=1;   #重启服务生效

有参有返回

#案例1：根据员工名，返回它的工资
CREATE FUNCTION f2(ename VARCHAR(20)) RETURNS INT
BEGIN
    SET @sal=0;
    SELECT salary INTO @sal
    FROM employees e
    WHERE e.`last_name`=ename;
    RETURN @sal;
END$
SELECT f2('Kochhar')$
SELECT * FROM employees WHERE last_name='Kochhar';

#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION f3(dname VARCHAR(20)) RETURNS INT
BEGIN
    DECLARE asal INT DEFAULT 0;
    SELECT AVG(salary) INTO asal
    FROM employees e
    JOIN departments d ON e.department_id=d.department_id
    WHERE d.department_name=dname;
    RETURN asal;
    
END $
SELECT f3('部门名');

#查看各部门的平均工资
SELECT AVG(salary),department_name
FROM employees e
JOIN departments d ON e.department_id=d.department_id
GROUP BY d.department_name;

三、查看函数

#查看所在库的所有创建的函数名
SHOW FUNCTION STATUS WHERE db='myemployees';

#查看详细的函数创建过程
SHOW CREATE FUNCTION f1;

四、删除函数

DROP FUNCTION f1;

案例：一、创建函数，实现传入两个float,返回两者之和

CREATE FUNCTION sums(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
    DECLARE s FLOAT DEFAULT 0;
    SET s=num1+num2;
    RETURN s;
END $
SELECT sums(14234.4325,2352.231)$

#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------

十九、流程控制结构

/*
顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序满足一定条件的基础，重复执行一段代码

*/
#一、分支结构
#1.if函数
/*
语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面

*/

#2.case结构
/*
语法：
情况1：类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end 

情况2：
case 
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end 

应用在begin end 中或外面


*/

#3.if结构

/*
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能：类似于多重if

只能应用在begin end 中
*/

案例：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80，返回B，如果成绩>60，返回C，否则返回D

#利用存储过程实现case语句
DELIMITER $
CREATE PROCEDURE pro_case(IN score FLOAT)
BEGIN
    CASE
    WHEN score>=90 AND score<=100 THEN SELECT "A";
    WHEN score>=80 THEN SELECT "B";
    WHEN score>=60 THEN SELECT "C";
    ELSE SELECT "D";
    END CASE;
END $

#利用函数实现if语句
CREATE FUNCTION pro_if(score FLOAT) RETURNS CHAR
BEGIN
    IF score>=90 AND score<=100 THEN RETURN 'A';
    ELSEIF score>=80 THEN RETURN 'B';
    ELSEIF score>=60 THEN RETURN 'C';
    ELSE RETURN 'D';
    END IF;
END $

#1.while
/*

语法：

【标签:】while 循环条件 do
    循环体;
end while【 标签】;

联想：

while(循环条件){
    循环体;
}
循环控制：

#2.loop
/*

语法：
【标签:】loop
    循环体;
end loop 【标签】;

可以用来模拟简单的死循环
*/

#3.repeat
/*
语法：
【标签：】repeat
    循环体;
until 结束循环的条件
end repeat 【标签】;
*/

iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于  break，跳出，结束当前所在的循环

*/

练习案例

#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
TRUNCATE admin;
CREATE PROCEDURE pro_while1(IN inserCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    a:WHILE i < inserCount DO
    INSERT INTO girls.`admin`(username,PASSWORD) VALUES(CONCAT('yang',i),CONCAT('1000',i));
    SET i=i+1;
    END WHILE a;
END$
CALL pro_while(100)$


#案例：生成10万个用户和随机密码
create table admin1(
    id int primary key,auto_increment,
    username varchar(8),
    password varchar(11)
);
TRUNCATE admin1;
CREATE PROCEDURE pro_while2(IN inserCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE randStr VARCHAR(52) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare s varchar(11) default '11111111111';
    a:WHILE i < inserCount DO
    #SELECT CONCAT('1',CEIL(RAND()*9000000000+100000000)) into s;
    set s=CONCAT('1',CEIL(RAND()*9000000000+100000000));
    SET name=CONCAT(SUBSTR(randStr,ceil(RAND()*LENGTH(randStr)-4)),4);
    INSERT INTO girls.`admin1`(username,PASSWORD) VALUES(name,s);
    SET i=i+1;
    END WHILE a;
END$
CALL pro_while2(10000000)$


#2.添加leave循环控制语句(跳出，结束当前所在的所有循环)（leave一般结合if语句去使用）
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE girls.`admin`;
CREATE PROCEDURE pro_leave1(IN inCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    a:WHILE i<inCount DO
    INSERT INTO girls.`admin`(username,PASSWORD) VALUES(CONCAT('猪文文',i),CONCAT('1000',i));
    IF i>=20 THEN LEAVE a;
    END IF;
    SET i=i+1;
    END WHILE a;
END $
CALL pro_leave1(100)$

#3.添加iterate语句(跳出当前循环继续下一次循环)，一般会结合If语句使用
TRUNCATE girls.`admin`;
CREATE PROCEDURE pro_iterate1(IN inCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    a:WHILE i<inCount DO
        SET i=i+1;
        IF MOD(i,2) THEN ITERATE a;
        END IF;
        INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('猪文文',i),CONCAT('1000',i));
    END WHILE a;
    
END $
CALL pro_iterate1(101)$


#已知表million表
DROP TABLE IF EXISTS million;
CREATE TABLE million(
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(32)
);
DELIMITER $
CREATE PROCEDURE test_million(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE str VARCHAR(32) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    DECLARE startIndex INT;#代表初始索引
    DECLARE len INT;#代表截取的字符长度
    a:WHILE i<=insertcount DO
        SET str=MD5(i);
        #SET startIndex=FLOOR(RAND()*32+1);#代表初始索引，随机范围1-26
        #SET len=FLOOR(RAND()*(36-startIndex)+1);#代表截取长度，随机范围1-（20-startIndex+1）
        INSERT INTO million(content) VALUES(SUBSTR(str,1,8));
        SET i=i+1;
    END WHILE a;
END $
call test_million(10000)$
#插入的结果时间大约为2分钟
mysql> call test_million(100000)$
Query OK, 1 row affected (2 min 49.34 sec)

最后编辑于：2021.09.18 02:21:27

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,293评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,604评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,958评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,729评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,719评论 5赞 366
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,630评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,000评论 3赞 397
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,665评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,909评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,646评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,726评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,400评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,986评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,959评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,197评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,996评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,481评论 2赞 342

mysql学习手记(尚硅谷李玉婷老师)

一、基础查询方法

案例

二、条件查询

三、排序查询

四、常见函数

单行函数

练习题

五、分组函数

练习题

六、分组查询

练习解析：

七.连接查询

练习解析：

八、sql99语法

一、内连接

二、外连接

基础案例

九、子查询

一、where或having后面

二、select 后面

三、from后面（将查询到的结果当做一张表来）

四、exists后面（相关子查询）

分页查询

测试题

子查询经典案例题

十：联合查询

十：数据的增删改

一、插入语句

方式一：经典插入语句

方式二：用set 列名=值

二、修改语句

三、删除语句

delete和truncate的区别 (面试题)

数据的增删改的操作练习

十一：数据的定义语言

I、库的管理

II、表的管理

十二：常见的数据类型

I、整型

II、小数

III、字符型

IV、日期型

十三：常见约束

修改表时添加约束

标识列

十四、事务TCL事务控制语言

十五、视图

I、创建视图

II.视图的修改

III. 删除视图(可以删除多个视图名)

VI、查看视图

IV、视图的更新(一般不推荐对视图的更改，删除等操作)

案例讲解

十六、变量

一、系统变量

二、自定义变量

十七、存储过程和函数

一. 创建语法:procedure

二、调用语法

四、删除存储过程

五、查看存储过程信息，和创建所有的存储名

存储的案例解析

十八、函数

一、创建语法

二、调用语法 SELECT 函数名(参数列表)

当出现了这个错误的时候，就是MySQL默认是不允许创建函数

三、查看函数

四、删除函数

案例：一、创建函数，实现传入两个float,返回两者之和

十九、流程控制结构

练习案例

推荐阅读更多精彩内容