【学习向】mysql基础知识整理

最新推荐文章于 2024-02-08 12:18:19 发布
Waq250
最新推荐文章于 2024-02-08 12:18:19 发布
阅读量305
点赞数 1
文章标签： sql 基础知识尚硅谷
本文链接：https://blog.csdn.net/Waq250/article/details/100009689
版权
最近面试问了一道sql的题，觉得有意思，就花了一段时间学习了sql语言。在此分享自己的学习感悟和学习笔记
一，学习材料

学习资料和演示代码集合
学习资料
学习视频
学习视频
二，学习笔记

这段时间的学习笔记，按视频的顺序来整理的。在资料里面有个测试.sql里面就有我的笔记，也可以从里面自己看。
#基础查询
/*
select 查询列表 from 表名

类似于：system.out.println（打印东西）
列表可以是变量值，函数，表达式，字段
查询结果是一个虚拟的表格
*/
USE myemplyees;
#查询单个字段
SELECT last_name FROM employees;
#查询多个字段
SELECT last_name,email FROM employees;
#查询所有字段
SELECT * FROM employees;
#查询常量值
SELECT 100;
SELECT 'jjkk';
#查询表达式
SELECT 100%98;
#查询函数名
SELECT VERSION();
#起别名
/*
1,便于理解
2，有重名，别名可以区分
*/
SELECT last_name AS 姓 FROM employees;
SELECT last_name 姓 FROM employees;
SELECT last_name AS "out put" FROM employees;#别名有关键字
#查询涉及的部门编号
SELECT department_id FROM employees;
SELECT DISTINCT department_id FROM employees;#去重
/*
+号作用
java中
1，运算符
2，连接符，只要其中一个为字符串

mysql中+号作用：
运算符
select 100+90;
select '100'+90;其中一方为字符型，试图先将其转成数值型，再做加法运算
select 'ss'+90;转化失败，则将字符型数值转成0
select null+90;只要一方为null，输出就位null
*/
#案例，查询员工和姓连接一个字段
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;#concat 拼接
 #条件查询
 /*
 语法：
 select
      查询列表  3//筛选顺序
 from
      表名     1
 where
      筛选条件 （if）    2 
 分类：
 一，按条件表达式筛选
 条件运算符：<>= !=
 二，逻辑运算符
 &&|| !
 and or not
 三，模糊查询
 like
 between and
 in
 is null
 */
 #查询工资>12000的员工信息
 SELECT
 *
 FROM
 employees
 WHERE
 salary>12000;
 #查询部门编号不等于90的的1员工名和部门编号<>
 SELECT
 `first_name`,`department_id`
 FROM
 employees
 WHERE
 department_id!=90;
 # #查询部门编号40到90之间的员工名和部门编号<>
  SELECT
 `first_name`,`department_id`
 FROM
 employees
 WHERE
 department_id>=40
 AND
  department_id<=90;
  三，模糊查询
  /*
 like
 一般搭配通配符使用，可以判断字符型或数值型
 通配符：%任意多个字符，_任意单个字符
 between and
 1,提高简洁渡
 2，包含临界值
 3，不能颠倒顺序
 in
 判断字段的值是否属于in列表的某一项
 1，使用in提高简洁渡
 2in列表的值类型必须统一或兼容
 is null
 =<>不能判断null值
 is not 和null搭配
 */
 #1.like
 #查询员工包含a字符的员工信息
  SELECT
 *
 FROM
 employees
 WHERE
 last_name LIKE '%a%';
 #查询员工包含第三个字符为n字符第五个为l的员工信息
 SELECT
 *
 FROM
 employees
 WHERE
 last_name LIKE '__n_l%';#一个下划线是一个空格
  #查询员工包含第二个字符是_的员工信息
   SELECT
 *
 FROM
 employees
 WHERE
 last_name LIKE '_$_%' ESCAPE '$';#把$作为转义符
 #查询数值型的部门编号100以上的值
SELECT
 *
 FROM
 employees
 WHERE
 department_id LIKE '1__';
 #2,between and
 #查询部门编号40到90之间的员工名和部门编号<>
   SELECT
 `first_name`,`department_id`
 FROM
 employees
 WHERE
 department_id>=40
 AND
  department_id<=90;
  #----------------------------------
  SELECT
 `first_name`,`department_id`
 FROM
 employees
 WHERE
 department_id
 BETWEEN
 40
 AND
 90;
 #3，in
 #查询工种名是AD_VP,AD_PRES的员工所有信息
 SELECT
 *
 FROM
 employees
 WHERE
 job_id='AD_VP'OR job_id='AD_PRES';
 #--------------------
  SELECT
 *
 FROM
 employees
 WHERE
 job_id IN ('AD_VP','AD_PRES');
 #4，is null
 #查询没有奖金的人员所有信息
  SELECT
 *
 FROM
 employees
 WHERE
 commission_pct IS NULL;# commission_pct = NULL是错误写法
  #查询有奖金的人员所有信息
  SELECT
 *
 FROM
 employees
 WHERE
 commission_pct IS NOT NULL;# commission_pct = NULL是错误写法
  #安全等于<=> 就是等于的意思
  
  #查询没有奖金的人员所有信息
  SELECT
 *
 FROM
 employees
 WHERE
 commission_pct <=> NULL;# commission_pct = NULL是错误写法
 
 #is null pk <=>
 /*
 is null:仅仅判断null值，可读性好
 <=>:既可以判断null值。又可以判断普通的数值，可读性差
 
 //计算年薪：
 salary*12*(1+ifnull(commission_pct,0)):ifnull判断是否为null，是则转为0
 */
  #判断commission_pct是否为null，是则返回1
 SELECT ISNULL(commission_pct),commission_pct FROM employees;
 #排序查询
 /*
 语法：
 select 查询列表               3
 from  表                1
 【where筛选条件】              2
 order by 排序列表【asc|desc】升序|降序 4
 特点：
 asc升序，desc降序
 什么都不写
 默认是升序
 2,order by 子句中可以支持单个字段，多个字段，表达式，函数，别名
 3，order by 子句一般放在查询语句的最后面，除了limit子句除外
 */
 #查询员工信息，要求工资从高到低
 SELECT
 *
 FROM
 employees
 ORDER BY
 salary DESC;
  #查询员工信息，要求部门编号>=90，按入职时间排序
 SELECT
 *
 FROM
 employees
 WHERE
 department_id>=90
 ORDER BY
 hiredate ASC;
 #按年薪高低显示员工的信息和年薪【按表达式排序】
  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;
#按姓名的长度显示员工的姓名和工资【按函数排序】
 SELECT
 LENGTH(last_name) AS 字节长度,last_name,salary
 FROM
 employees
 ORDER BY
LENGTH(last_name) DESC;
#查询工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
  SELECT
 *
 FROM
 employees
 ORDER BY
salary ASC,employee_id DESC;#先按工资升序，再按员工编号降序
#4，常见函数
/*
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处，1：隐藏了实现细节
2，提高了代码的重用性
调用：select函数名（实参函数）【from 表】
特点：
叫什么
干什么
分类：
单行函数（会有返回值），输入一个数，输出一个值
分组函数 功能：做统计用的。输入一组数，输出一个值
*/
#一，字符函数
#1，length获取参数值字节个数
SELECT LENGTH ("张三丰hahaha");#输出15，一个汉字三个字节
SHOW VARIABLES LIKE "%char%";#查询客户端字符集：utf8汉字三个字节，jdk一个汉字二个字节
#2，concat 拼接字符串
SELECT CONCAT (last_name,'_',first_name)AS 姓名 FROM employees;
#3,upper,lower 
SELECT UPPER("jok");#变大写
SELECT LOWER ("JIo");#变小写
#4，substr=substring
注意：索引从一开始
#截取从指定索引后面的字符
SELECT SUBSTR("李莫愁爱上了路站元",7)AS oueput;
#截取指定索引出指定长度的字符
SELECT SUBSTR("李莫愁爱上了路站元",1,3)AS ddd;
#5，instr 返回子串中第一次出现的索引，如果找不到就返回0
SELECT INSTR("李莫愁爱上了路站元","路站元")AS ddd;
#6 trim  去除前后制定的字符路站元"  输出：张aaaac催收
SELECT TRIM('a'FROM "aaaaaaaaaaaaa张aaaac催收aaaaaaaaa") AS outl;
#7，lpad 左填充的字符实现左填充制定长度   *******张小顺
SELECT LPAD("张小顺",10,'*')AS ooo;
#8，rpad 右填充的字符实现左填充制定长度   张小顺*******
SELECT RPAD("张小顺",10,'*')AS ooo;
#9，replace替换  ggggggggggccccccccbbb
SELECT REPLACE("aaaaaaaaaaccccccccbbb",'a','g') AS opj;

#二，数字函数
#round 四舍五入
SELECT ROUND(1.66);#2
SELECT ROUND(1.6678,2);#1.67
#ceil 向上取，返回>=概参数最小整数
#floor 向下取整，返回<=该参数的最大整数
#truncate 截断 1.79
SELECT TRUNCATE(1.799999,2);
#mod取余
/*
mod(a,b):a-a/b*b
mod(-10,-3):-10-(-10)/(-3)*(-3)=-1
*/
#三，日期函数
#now  返回当前系统日期+时间2019-08-08 10:27:45
SELECT NOW();
#curdate返回当前的日期不包含时间
#curtime 返回当前的时间不包含日期
#datadiff返回两个日期之差
SELECT DATEDIFF(NOW(),"1996-4-25");#8505
#可以获取指定日期的年，月，日。分，秒，
SELECT YEAR(NOW()); #2019
SELECT YEAR("1009-9-9"); #1009
SELECT YEAR(hiredate) AS nain FROM employees; 
SELECT MONTH(NOW()); #8
#str_to_date将字符通过指定的格式转换成日期
SELECT * FROM employees WHERE hiredate =STR_TO_DATE("4-3 1992","%c-%d %Y");#用户输入4-3 1992查找
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),"%y年/%m月/%d日") AS ppp;
#四其他函数
SELECT VERSION();
SELECT DATABASE();#数据库
SELECT USER();#用户
#五，流程控制函数
#1，IF函数：
SELECT IF(10<5,"大","小");#第一个是条件，真的返回大，假的返回小
#2，CASE函数
/*
java中
switch(表达式或变量)
{
case 常量1： 语句;break;
...
default:语句n,break;
}
mysql中
case 要判断的字或表达式
when 常量1 then 要显示的值或语句；
。。。。
else 要显示的其他值或语句n;
end
*/
/*
查询部门的1员工工资
部门号=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;
#另一种用法 相当于多重if 区间
/*
case 
when 条件1 then 要显示值或语句；
....
else 要显示值或语句；
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;
#二，分组数组
/*
sum ,avg.max,min,count计算个数
特点：
1，sum，avg一般用于处理数值型
max，min，count可以处理任何数据类型
2，以上分组都忽略null
3，可以和distinct搭配实现去重运算
4，一般使用count（*）用作统计函数
5，和分组函数一同查询的字段要求是group by后的字段
*/
#1,简单使用
SELECT SUM(salary) FROM employees;#691400.00其它类似
#2，distinct
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
#4count
SELECT COUNT(*) FROM employees;#统计列表函数
SELECT COUNT(1) FROM employees;#统计列表函数
/*
效率：
myisam存储引擎下，count(*)的效率要高
innodb存储引擎下，count(*)和count(1)效率差不多，比count（字段）要高点

*/
#6
SELECT AVG(salary),employee_id FROM employees;#没任何意义
#查询部门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id = 90;
#进阶5：分组查询
/*
语法：
select  分组函数，列（要求出现在group by）的后面
from 表
【where 筛选条件】
group by 分组的列表
【order by】子句
注意：查询的列表必须特殊，要求是分组函数和group by后出现的字段
特点：
分组筛选分为两类
分组前筛选（数据前分组后的筛选集） 位置在group之前 where
分组后筛选（数据源分组后的筛选集）位置在group之后 having
分组函数（min。max等）做条件肯定是放在having语句中
能用分组前筛选的，就优先考虑分组前筛选
group by 子句支持单个字段分组，多个字段分组，表达式分组
也可以添加排序（排序放在分组查询的最后）
*/
#查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#添加分组前筛选条件
#查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#添加分组后的筛选
#查询哪个部门的员工人数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2

#复杂案例
#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
#1，查询每个工种的有奖金的员工的最高工资
SELECT MAX(salary),job_id#显示的表头
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
#2,筛选后工资>12000
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000
#按表达式分组
#按员工姓名长度分组
SELECT COUNT(*),LENGTH(last_name) leng
FROM employees
GROUP BY leng;
#按多个字段进行分组
#查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id ;
#添加排序
#查询每个部门每个工种的员工的平均工资，按平均工资从高到低
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;
#连接查询
/*
含义：多表查询，当查询的字段来着多个表时，就会用到连接查询
笛卡尔现象：表一m行，表二n行，一共有m*n行
原因没有有效的连接
分类：
按年代分类：
sql192标准
sql199标准【推荐】：支持内连接+外连接（左外+右外）+交叉连接
按功能分类：
内连接：
外连接：
交叉连接

*/
#一，sql192标准
SELECT * FROM beauty;
#1，等值连接
/*
1,多表等值连接的结果为多表的交集部分
2，n表连接，至少需要n-1个连接条件
3，多表连接的顺序没有要求
4，一般需要起别名
5，可以搭配前面的介绍的所有子句使用，比如分组
*/
#找男朋友
SELECT NAME,boyname FROM boys,beauty
WHERE beauty.`boyfriend_id`=boys.`id`;
#为表起别名
/*
1.提高语句的简洁度
2，区分多个重名的字符
注意：如果为表起别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名，工种号，工种名
SELECT last_name,e.`job_id`,job_title
FROM employees AS e,jobs#为表起别名
WHERE e.`job_id`=jobs.`job_id`
#加筛选
#查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
AND city LIKE '_o%';
#分组
#查找每个部门的部门个数吧城市显示出来
SELECT COUNT(*) 个数,city
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
#排序
#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM `employees` e,`jobs` j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#三表连接
#案例：查询员工名，部门名和所在的城市
SELECT last_name,department_name,city
FROM `locations` l,`departments` d,`employees` e
WHERE d.`location_id`=l.`location_id`
AND e.`department_id`=d.`department_id`
#2，非等值连接
#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees AS e,job_grades AS g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level='A';#加个排序

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);
#3，自连接：一张表分成两张表
#查询员工名和上级的名称
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`
#一，sql199标准
/*
语法：
select 查询列表
from 表1 别名【连接类型】
join 表2 别名 
on 连接条件
【where 筛选条件】
【分组】
按连接类型分类：
内连接：inner【交集】
外连接：
1，左外 left
2，右外 right
3，全外  full
交叉连接 cross
*/
#一，内连
/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名 
on 连接条件
分类：
等值
非等值
自连接
特点：
1，可以添加排序，筛选
2，inner 可以省略
3，筛选条件放在where后面，连接条件在on后面
4，最终实现效果是一样的
*/
#1，等值连接
#查询员工名和部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`;
#查询部门个数>0的城市名和部门个数，并排序
#1.先查找每个部门个数
#2，在此条件上加筛选
SELECT city,COUNT(*) 部门个数
FROM `departments`d
INNER JOIN `locations` l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>0
ORDER BY COUNT(*) DESC;
#三表连接
#查询员工名，部门名和工种名，并按部门名降序
SELECT `department_name`,`job_title`,`last_name`
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 department_name DESC;
#2，非等值连接
#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees AS e
JOIN job_grades AS g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level='A';#加个排序
#3，自连接：一张表分成两张表
#查询姓名包含k的员工名和上级的名称
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,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左边的是主表
右外连接。right右边的是主表
3，左外和右外可以实现同样的效果
4,全外连接=内连接+表1有但表2没有+表2有表1没有【mysql不支持】
*/
#引入：查询男朋友，不在男神表的女神名
SELECT b.name
FROM `beauty` b
LEFT JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS  NULL;
#三交叉连接【实现笛卡尔乘积】
SELECT b.*,bo.*
FROM `beauty` b
CROSS JOIN `boys` bo;

#sql192和199
#功能：sq199支持较多
#可读性：sql199实现连接条件额筛选条件的分离，可读性较高

#子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询的出现的位置：
select后面：
标量子查询（结果集只有一行一列）
from后面：
表子查询（结果集只有多行多列）
where或having后面 重点
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列）
exists后面 
表子查询（结果集只有多行多列）
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列）
表子查询（结果集只有多行多列）
*/
#一，where或having后面 重点
/*
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列）
特点：
1，子查询都在小括号内
2，子查询都放在条件右侧
3，标量字查询，一般搭配单行操作符使用
><==><
列子查询，一般搭配多行操作符使用
in,any/some,all
4,子查询优先于主查询
*/
#1,标量子查询
#谁的工资比Abel高
#1，查询Abel工资
SELECT salary
FROM `employees`
WHERE last_name='Abel';
#2,查询员工信息，满足其条件
SELECT *
FROM `employees`
WHERE salary>(
	SELECT salary
	FROM `employees`
	WHERE last_name='Abel'
);
#返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
#1，查询141job_id
SELECT job_id
FROM `employees`
WHERE employee_id=141;
#2,查询143的salary
SELECT salary
FROM `employees`
WHERE employee_id=143;
#3，两个要求嵌套
SELECT last_name,job_id,salary
FROM `employees`
WHERE job_id=(
	SELECT job_id
	FROM `employees`
	WHERE employee_id=141
)
AND salary >(
SELECT salary
FROM `employees`
WHERE employee_id=143
);
#返回公司工资最少的员工的last_name,job_id和salary
#1,查询工资最小的
SELECT MIN(salary)
FROM `employees`
#2,返回last_name,job_id和salary
SELECT last_name,job_id,salary
FROM `employees`
WHERE salary=(
	SELECT MIN(salary)
	FROM `employees`
);
#查询最低工资大于50号部门最低工资的部门id和其最低工资
#1,50号部门的最低工资
SELECT MIN(salary)
FROM `employees`
WHERE `department_id`=50;
#2,各部门的最低工资
SELECT MIN(salary),`department_id`
FROM `employees`
GROUP BY`department_id`;
#3筛选2，满足min(salary)>1
SELECT MIN(salary),`department_id`
FROM `employees`
GROUP BY`department_id`
HAVING MIN(salary)>
(
	SELECT MIN(salary)
	FROM `employees`
	WHERE `department_id`=50
)
;
#非法使用标量子查询

#列子查询
#返回location_id是1400或1700的部门中的所有员工姓名
#1，location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM `departments`
WHERE location_id IN (1400,1700);
#2查询所有员工姓名
SELECT last_name
FROM `employees`
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM `departments`
	WHERE location_id IN (1400,1700)
);
#行子查询
#查询员工编号最小工资最高的员工信息
#1，查询员工最小的员工编号
SELECT MIN(employee_id)
FROM `employees`;
#1，查询工资最高的员工编号
SELECT MAX(salary)
FROM `employees`;
#3,查询员工信息
SELECT *
FROM `employees`
WHERE employee_id=(

SELECT MIN(employee_id)
FROM `employees`
)
AND
 salary=(
 SELECT MAX(salary)
FROM `employees`);
#-------------------------------
#另一种方法
SELECT *
FROM `employees`
WHERE (employee_id,salary)=(

	SELECT MIN(employee_id),MAX(salary)
	FROM `employees`
);
#select后面：
#标量子查询（结果集只有一行一列）
#查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)#结果集只有一行一列
FROM `employees` e
WHERE e.`department_id`=d.`department_id`
) 员工
FROM `employees` d;
#from后面：
#表子查询（结果集只有多行多列）
/*
将子查询的结果充当一张表，要求必须起别名
*/
#查询每个部门的平均工资的工资等级
#1,查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM `employees`
GROUP BY `department_id`
#2,连接1的结果集和job_grade表。筛选条件在最低和最高工资之间
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 highest_sal;
#exists后面 
#表子查询（结果集只有多行多列）
/*
语法：
exists(完整的查询语句)
结果：1或0
*/
SELECT EXISTS (SELECT `employee_id` FROM `employees`);
#查询有员工名的部门名
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`
);
#分页查询
/*
应用场景：
当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表 2
on 连接条件
where 筛选条件
group by
having 
order by
】
limit 【offset ,】size;
offset：索引开始【从0开始】
size;要显示的条目个数
特点：
1，limit 语句放查询语句的最后
2，公式
select 查询列表
from 列表
limit (page-1)*size,size;
*/
#查询前11到15条员工信息
SELECT * FROM `employees` LIMIT 10,15;
#有奖金员工信息，并把前十名显示出来
SELECT *
FROM `employees`
WHERE `commission_pct` IS NOT NULL
ORDER BY `salary` DESC
LIMIT 10;
#联合查询
/*
union 联合，合并：将多条查询语句合并成一个结果
语法：
查询语句1
union
查询语句2
....
查询语句n
应用场景：
要查询的结果来自于多个表，且多个表没有直接连接关系，但查询的信息一致时
特点：

1，要求多条查询的语句的查询列数一致
2，查询顺序一致
3，union关键字默认去重，使用union all可以包含重复项
*/
#查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM `employees` WHERE `email` LIKE '%a&' OR `department_id`>90;
SELECT * FROM `employees` WHERE `email` LIKE '%a&' 
UNION
SELECT * FROM `employees` WHERE `department_id`>90;


#DML语言
/*
数据操作语言
插入：insert
修改：update
删除：delete
*/
#一，插入语句
/*
语法一:
insert into 表名(列名....) value（值1,...);

*/
SELECT * FROM `beauty`;
#1,插入数据类型一致
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) 
VALUE(13,'hhu','女','1900-9-00','1898888888',NULL,2);
#2,不可以为null必须插入列，可以的null可以不写
INSERT INTO `beauty`(`id`,`name`,`sex`,`phone`) 
VALUE(14,'hhu','女','1898888888');
#3.列的顺序可以调换，但必须一一对立
#4.列数和值的个数必须一致
#5，可以省略列名，默认所有列，而且列的顺序和原来的顺序一致
INSERT INTO `beauty`
VALUE(15,'hhu','女','1900-9-00','1898888888',NULL,2);
/*
语法二:
insert into 表名(列名....) 
set 列名=值，列名=值,....

*/
INSERT INTO `beauty`
SET id=17,`name`='nv',phone='184017777';
#两种方式pk
#方式一支持插入多行
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) 
VALUE(23,'hhu1','女','1900-9-00','1898888888',NULL,2),
(24,'hhu2','女','1900-9-00','1898888888',NULL,2),
(25,'hhu3','女','1900-9-00','1898888888',NULL,2);
#方式一支持子查询，方式二不支持
INSERT INTO `beauty`(`id`,`name`,`sex`,`phone`) 
SELECT 26,'hhu','女','1898888888' ;
#二，修改语句
/*
1，修改单表的记录
语法：
update表名  1
set 列=新值，列=新值  3
where 筛选条件  2
2，修改多表
语法：
sql192：
update 表1 别名，表2 别名
set 列=值
where 连接条件
and 筛选条件
sql199：
update 表1 别名
Inner|left|rihgt join 表2  别名
on 连接条件
set 列=值
where 筛选条件
*/
#1，修改单表的记录
#修改boys表中id为2的值名称为张飞魅力值为10
SELECT * FROM `boys`;
UPDATE `boys`
SET `boyName`='张飞',`userCP`=10
WHERE id=2;
#2，修改多表
#修改张无忌的女朋友的手机号为114
UPDATE `boys` bo
INNER JOIN `beauty` b
ON bo.`id`=b.`boyfriend_id`
SET b.`phone`=114
WHERE bo.`boyName`='张无忌';
#三，删除语句
/*
方式一delete
语法：
1，单表删除
delete from 表名 where 筛选条件
2多表删除
sql192：
delete 表1的别名，表2的别名
from 表1 别名，表2 别名
where 连接条件
and 筛选条件
sql199：
delete 表1的别名，表2的别名
from 表1 别名，表2 别名
Inner|left|rihgt join 表2  别名
on 连接条件
where 筛选条件
方式二：truncate
语法：truncate table 表【不允许where】
*/
#方式一 删除
#1，单表删除
#以9结尾的女神信息
DELETE FROM `beauty` WHERE `phone` LIKE '%9';
SELECT * FROM `beauty`;
#删除张无忌女朋友的信息
DELETE b
FROM `beauty` b
INNER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='张无忌';
#删除黄晓明和她女朋友的信息
DELETE b,bo
FROM `beauty` b
INNER JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';
#方式二：truncate
TRUNCATE TABLE `boys`；
/*
delete和truncate区别
1，truncate不允许where
2，truncate效率高
3，假如要删除自增长列
delete删除，再插入数据，从断点开始
truncate删除再插入数据，从0开始
4，truncate 删除没有返回值，delete删除有返回值
5，truncate删除不能回滚，delete删除可以
*/
#DDL
/*
数据定义语言
一，库的管理
二，表的管理
创建：create
修改alter
删除drop
*/
#一，库的管理
#1，库的创建
/*语法：
create database[IS NOT EXISTS] 库名；
*/
#案例：创建books
CREATE DATABASE IF NOT EXISTS books;#只能执行一次
#2,库的修改
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
#3，库的删除
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;
#案例，创建author
CREATE TABLE author(
	id INT,
	auname VARCHAR(20),
	nation VARCHAR(10)
);
DESC author;
#2.表的修改

#1，修改表的列名
ALTER TABLE book CHANGE COLUMN publishdate pubdate DATETIME;
#2,列的类型
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
#3，添加新列
ALTER TABLE author ADD annual DOUBLE;
#4，删除列
ALTER TABLE DROP ADD annual;
#5，修改表名
ALTER TABLE author RENAME TO book_author;
#3.表的删除
DROP TABLE book_author;
SHOW TABLES;
#通用的写法
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名；
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 新表名；
#4.表的复制
INSERT INTO author VALUES
(1,'chun','ri'),
(2,'xia','ch');
SELECT * FROM author;
#1,仅仅复制表的结构
CREATE TABLE author1 LIKE author;
#2,复制表的结构和数据
CREATE TABLE author2 SELECT * FROM author;
SELECT * FROM author2;
#3,只复制部分数据
CREATE TABLE author3 
SELECT id,auname
FROM author
WHERE nation='ch';
SELECT * FROM author3;
#4,复制部分结构
CREATE TABLE author4 
SELECT id,auname
FROM author
WHERE 0;
SELECT * FROM author4;
#常见数据类型
/*
数值型：
整型:
小数：
	定点数
	浮点数
字符型：
较短的文本：
较长的文本：
日期型：
*/
#一，整型
/*
1,如果不设置无符号和有符号，默认有符号，设置无符号要UNSIGNED
2，如果插入数值超出整数范围，会报outofrange，并插入临界值
3，如果不设置长度，会有默认长度
长度代表显示的最大宽度，如果不够会用01在左边填充，但必须搭配zerofull使用
*/
#1，如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED
);
DESC tab_int;#查看表的结构
#二，浮点型
/*
分类：
1，浮点型
float(m,d)
double(m,d)
2，定点型
dec(m,d)
特点：
1，
m：整数部分+小数部分
d:小数部分
如果超出范围，则插入临界值
2，m和d都可以省略
如果是dec，m默认10，d默认0.
如果是float，double，则无所谓
3，定点型的精确值较高（货币运算）
*/
CREATE TABLE tab_float(
	f1 FLOAT(5,2),
	f2 DOUBLE(5,2),
	f3 DEC(5,2)
);
#原则
/*
所选择的类型越简单越好，所保存的数值类型越小越好
*/
#三，字符型：
/*
较短的文本：
char（m）
varchar（m）
比较
char：固定长度字符,m可省略，默认为1，最大字符数
varchar：可变长度字符，m不可省略，最大字符数
其他：
binary用于保存较短的二进制
enum：保存枚举
set：保存集合
较长的文本：
*/
#四，日期型
/*
DATETIME:字节8，不受时区的影响
 TIMESTAMP：字节4，受时区的影响
*/
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);
INSERT INTO tab_date VALUES (NOW(),NOW());
SELECT * FROM tab_date;

SHOW VARIABLES LIKE 'time_zone';
SET time_zone='+9:00';
#常见约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中的准确和可靠性
分类：
 not null ：非空约束（姓名）
 dffault:默认约束（性别）
 primary key：唯一约束+非空约束 比如学号、员工编号等【主键】
 UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
 check：检查约束（年龄，性别）【mysql不支持】
 foreign key：用于限制两个表的关系，在从表的添加外键约束，用于引用于主表的某列的值
 
 
 添加约束的时间：
 1，设计表的时候
 2，需要修改表时
 约束的添加分类：
 列级约束：
 六大约束都可以支持，除了外键约束
 表级约束
 除了非空和默认，其他都支持
 
 主键和唯一的大对比：

		保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
	主键	√		×		至多有1个           √，但不推荐
	唯一	√		√		可以有多个          √，但不推荐
外键：
	1、要求在从表设置外键关系
	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3、主表的关联列必须是一个key（一般是主键或唯一）
	4、插入数据时，先插入主表，再插入从表
	删除数据时，先删除从表，再删除主表
*/
CREATE TABLE 表名(
	字段名 字段类型 列的约束，
	表级约束

)
#一、创建表时添加约束

#1.添加列级约束
/*
语法：

直接在字段名和类型后面追加 约束类型即可。

只支持：默认、非空、主键、唯一



*/
CREATE DATABASE students;
USE students;
#DROP TABLE stuinfo;
CREATE TABLE stuinfo(
	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 major(id)#外键【不支持】

);
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);
#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
#2.添加表级约束
/*

语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	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  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
	
);
SHOW INDEX FROM stuinfo;
#通用的写法：##

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);

#二、修改表时添加约束

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

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;


*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一

#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);


#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 
#三、修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;
#标识列 AUTO_INCREMENT
/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值


特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值，设置起始值
*/
#一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	#id INT  UNIQUE AUTO_INCREMENT,
	id INT,
	NAME FLOAT ,
	seat INT 
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
SELECT * FROM tab_identity;
SHOW VARIABLES LIKE '%auto_increment%';#【设置起始值（mysql不支持）和步长】
SET auto_increment_increment=3;#【设置步长】
#二、修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#三、修表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
#TCL
/*
事务：事务由单独单元的一个或多个SQL语句组成，在这 
个单元中，每个MySQL语句是相互依赖的。而整个单独单 元
作为一个不可分割的整体，如果单元中某条SQL语句一 
旦执行失败或产生错误，整个单元将会回滚。所有受到影 
响的数据将返回到事物开始以前的状态；如果单元中的所 
有SQL语句均执行成功，则事物被顺利执行。
事务特性：
原子性：事务是一个不可分割的工作单位，事务中的操作要么 都发生，要么都不发生
一致性：使数据库从一个一致性状态变换到另外一个一致性状态 。
隔离性：指一个事务的执行不能被其他事务干扰
持久性：一个事务一旦被提交，它对数据库中数据的改变就是 永久性的
事务的创建
隐式事务：事务没有明显的开启和结束标识
比如:insert,delete
显式事务：
事务具有明显的开启和结束标记
前提：必须先设置自动提交功能为禁用
set autocommit =0;
步骤1：
set autocommit =0;
start transaction;可选的
步骤二：编写事务中的sql语句（select insert update delete）【create不可用】
语句1
语句2
步骤3：结束事务
commit提交事务
rollback;回滚事务【二选一】：不改变原始数据
savepoint 节点名：设置保护点

事务隔离级别:
read uncommitted：会脏读，会不可重复读，会幻读
read committed：不会脏读，会不可重复读，会幻读
repeatable read不会脏读，不会不可重复读，会幻读
serializable不会脏读，不会不可重复读，不会幻读
Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。
Oracle 默认的事务隔离级别为: READ COMMITED 
Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别
为: REPEATABLE READ
#看ppt
*/
#delete和truncate在事务使用时的区别
#演示delete
SET autocommit =0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#演示truncate【不支持回滚】
SET autocommit =0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;
#视图
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

比如：舞蹈班和普通班级的对比
	创建语法的关键字	是否实际占用物理空间	使用

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

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


*/

#一、创建视图
/*
语法：
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 j.job_id  = e.job_id;


#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
#2.查询各部门的平均工资级别

#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#3.查询平均工资最低的部门信息

SELECT * FROM myv2 ORDER BY ag LIMIT 1;

#4.查询平均工资最低的部门名和工资

CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
#二、视图的修改

#方式一：
/*
create or replace view  视图名
as
查询语句;

*/
SELECT * FROM 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;
#三、删除视图

/*

语法：drop view 视图名,视图名,...;【要求有权限，我们新建的root是最高权限】
*/

DROP VIEW emp_v1,emp_v2,myv3;
#四、查看视图
DESC myv3;
SHOW CREATE VIEW myv3;【命令行查询比较全】
#五、视图的更新
DROP VIEW `myv1`,`myv2`,`myv3`;#删除视图
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;


SELECT * FROM myv1;
SELECT * FROM employees;
#1.插入【视图和原表都修改了】
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
#2.修改【视图和原表都修改了】
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
#3.删除【视图和原表都修改了】
DELETE FROM myv1 WHERE last_name = '张无忌';
#具备以下特点的视图不允许更新
#①包含以下关键字的sql语句：分组函数、
#distinct、group  by、having、union或者union all
#②常量视图
CREATE OR REPLACE VIEW myv2
AS

SELECT 'john' NAME;

SELECT * FROM myv2;

#更新
UPDATE myv2 SET NAME='lucy';【更新失败】
#③Select中包含子查询

CREATE OR REPLACE VIEW myv3
AS

SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;

#更新【更新失败】
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;
#④join
#⑤from一个不能更新的视图
#⑥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';
#变量
/*
系统变量：
	全局变量
	会话变量

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

*/
#一、系统变量
/*
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加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 GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值【跨连接有效】
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
#2》会话变量
/*
作用域：针对于当前会话（连接）有效
*/
#①查看所有会话变量【SESSION省略效果一样，默认会话变量】
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 @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

#②赋值（更新变量的值）
#方式一：【不需要加类型】
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
	#案例
	SET @name='join';
	SET @name=1;
	SET @count=1;
#方式二：
	SELECT 字段 INTO @变量名
	FROM 表;
	#案例
	SELECT COUNT(*) INTO @count
	FROM `employees`;#把employees数据放入count中
#③使用（查看变量的值）
SELECT @变量名;
#案例
SELECT  @count;
#2》局部变量
/*
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/

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


#②赋值（更新变量的值）

#方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT 局部变量名:=值;
#方式二：
	SELECT 字段 INTO 具备变量名
	FROM 表;
#③使用（查看变量的值）
SELECT 局部变量名;
#用户变量和局部变量的对比

		作用域			定义位置		语法
用户变量	当前会话		会话的任何地方		加@符号，不用指定类型
局部变量	定义它的BEGIN END中 	BEGIN END的第一句话	一般不用加@,需要指定类型
#案例：声明两个变量，求和并打印

#用户变量
SET @m=1;
SET @n=2;
SET @sum=@m+@n;
SELECT @sum;

#局部变量【需要在BEGIN END中】
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
#存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作

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

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN#相当于普通的大括号{

	存储过程体（一组合法的SQL语句）
END#相当于普通的大括号}

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

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

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
*/


#二、调用语法

CALL 存储过程名(实参列表);
#1.空参列表
#案例：插入到admin表中五条记录

SELECT * FROM admin;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) 
	VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $


#调用
CALL myp1()$
#2.创建带in模式参数的存储过程

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息

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

END $

#调用
CALL myp2('小昭')$
#案例2 ：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	
	SELECT COUNT(*) INTO result#赋值给result
	FROM admin
	WHERE admin.username = username
	AND admin.password = PASSWORD;
	
	SELECT IF(result>0,'成功','失败');#使用【大于0成功，小于0失败】
END $

#调用
CALL myp3('张飞','8888')$
#3.创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名【一个out】

CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyname INTO boyname#赋值
	FROM boys bo
	RIGHT JOIN
	beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName ;
	
END $
#调用
CALL myp5('小昭',@name)$
SELECT @name$

#案例2：根据输入的女神名，返回对应的男神名和魅力值【多个out】

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
	SELECT boys.boyname ,boys.usercp INTO boyname,usercp
	FROM boys 
	RIGHT JOIN
	beauty b ON b.boyfriend_id = boys.id
	WHERE b.name=beautyName ;
	
END $


#调用
CALL myp6('小昭',@name,@cp)$
SELECT @name,@cp$
#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回

CREATE PROCEDURE myp7(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp7(@m,@n)$
SELECT @m,@n$
#三、删除存储过程
#语法：drop procedure 存储过程名
DROP PROCEDURE myp2;
DROP PROCEDURE myp2,myp3;#×【只能一次删除一个】
#四、查看存储过程的信息
DESC myp3;×#【错误，仅仅用于视图和表】
SHOW CREATE PROCEDURE  myp3;
#五、修改存储过程【删了重新写，内部不能修改】
#函数
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

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

*/
#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END
/*

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

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

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

*/
#二、调用语法
SELECT 函数名(参数列表)#执行完返回返回值
1.无参有返回
#案例：返回公司的员工个数
USE `myemployees`$
CREATE FUNCTION myf1() RETURNS INT
BEGIN

	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c;
	
END $

SELECT myf1()$
#2.有参有返回
#案例1：根据员工名，返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义用户变量 
	SELECT salary INTO @sal   #赋值
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $#返回两个值会报错

SELECT myf2('Kochar') $
#案例2：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END $

SELECT myf3('IT')$

#三、查看函数

SHOW CREATE FUNCTION myf3;

#四、删除函数
DROP FUNCTION myf3;

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

CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;#设置默认值为0
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT test_fun1(1,2)$
#流程控制结构
/*
顺序、分支、循环

*/

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

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

情况2：类似多重if
case 
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end 
特点：
1，
作为表达式，应用在begin end 中或外面
作为独立语句使用，只能用于begin end 中
2，执行中，如果when中的语句成立就执行then后的语句，然后跳出
并结束case
3，else省略，其他when不瞒足，则返回null

*/
#案例1：创建存储过程，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

CREATE PROCEDURE test_case(IN score INT)
BEGIN
	CASE
	WHEN score>=90 AND score<=100 THEN SELECT 'A';
	WHEN score>=80 THEN SELECT 'B';
	WHEN score>=70 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;	
END $
CALL test_case(87)$
#3.if结构

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

只能应用在begin end 中

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

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $

SELECT test_if(87)$
#二、循环结构
/*
分类：
while、loop、repeat

循环控制：

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

*/
#1.while
/*

语法：

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

联想：

while(循环条件){

	循环体;
}

*/

#2.loop
/*

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

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

*/

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


*/
#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
/*

int i=1;
while(i<=insertcount){

	//插入
	
	i++;

}

*/
USE `girls`$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
	
END $

CALL pro_while1(100)$
#2.添加leave语句[【添加循环控制】

#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$#【清空表】
DROP PROCEDURE test_while1$#【清空存储】
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;#a是标签
		END IF;
		SET i=i+1;
	END WHILE a;
END $
CALL test_while1(100)$
#3.添加iterate语句

#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;#求余
		END IF;
		
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		
	END WHILE a;
END $
CALL test_while1(100)$

/*

int i=0;
while(i<=insertCount){
	i++;
	if(i%2==0){
		continue;
	}
	插入
	
}

*/
Waq250
关注
1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
【学习向】mysql基础知识整理

最近面试问了一道sql的题，觉得有意思，就花了一段时间学习了sql语言。在此分享自己的学习感悟和学习笔记一，学习材料学习资料和演示代码集合学习资料学习视频学习视频二，学习笔记这段时间的学习笔记，按视频的顺序来整理的。在资料里面有个测试.sql里面就有我的笔记，也可以从里面自己看。#基础查询/*select 查询列表 from 表名类似于：system.out.print...
复制链接

扫一扫