mysql基础知识学习笔记

javascript_good

已于 2023-06-25 18:16:56 修改

阅读量112

点赞数

分类专栏：数据库文章标签： mysql 学习笔记

于 2023-06-12 09:32:12 首次发布

本文链接：https://blog.csdn.net/javascript_good/article/details/131162304

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SQL语言

进阶1：基础查询

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

--#1、查询表中的单个字段
SELECT last_name FROM employees;

--#2、查询表中的多个字段
SELECT last_name,job_id,salary FROM employees;

--#3、查询所有字段
SELECT * FROM employees;

--#4、查询常量值
--#SELECT 100;

--#5、查询表达式
SELECT 2*9;

--#6、查询函数
SELECT VERSION();

--#6、起别名
--#方式一：使用AS
SELECT 3*8 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;


--#方式二：使用空格
SELECT last_name 姓,first_name 名 from employees;

--#案例 ，查询salary ,显示结果为out put,  有关键字的用双引号引起来
SELECT salary AS "out put" FROM employees;

--7、去重,加distinct
-- 案例
SELECT DISTINCT department_id FROM employees;

+号的作用
mysql 中的+仅仅只有一个功能，就是运算符
select 90+10; 两个操作数都为整型，则做加法运算
select ‘123’+10; 只要其中一方为字符型，试图将字符型转换为整型，如果转换成功继续做加法运算
select ‘jone’+10; 如果转换失败，则字符串转换为0
select null+10; 只要其中一方为null，则结果肯定为null。

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

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

-- 9、ifnull 如果为null的处理
SELECT IFNULL(commission_pct,0) AS 奖金率 FROM employees;

SELECT CONCAT(first_name,',',last_name,',',IFNULL(commission_pct,0)) AS out_put FROM employees;

进阶2：条件查询

语法：
SELECT 查询列表 FROM 表名 WHERE 筛选条件；
分类：

按条件表达式筛选
简单的条件运算符：> < = != <> >= <=
按逻辑表达式筛选
&& || ！
and or not
模糊查询
LIKE
between AND
in
is null
like 特点：
1、一般和通配符搭配使用
通配符有：
% ：任意多个字符，包含0个字符
_：任意单个字符

-- 一：按条件表达式筛选
-- 案例1：查询员工工资大于12000的员工信息
SELECT * FROM employees WHERE salary>12000;

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

-- 二：按逻辑表达式筛选
-- 案例1：查询工资在10000到20000直接的员工员工名、工资及奖金
SELECT last_name,salary,commission_pct  FROM employees  WHERE salary >=10000 AND salary<=20000;

-- 案例2：查询部门编号不在90到110之间，或者工资高于15000的员工信息。
SELECT * FROM employees WHERE NOT(department_id>=90 and department_id <=110) OR salary>15000;

-- 案例1：查询员工名中包含字符a的员工信息

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

-- 案例2：查询员工名中第三个字符为n,第五个字符为l的员工名。
SELECT last_name FROM employees WHERE last_name LIKE '__n_l%';

-- 案例3：查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';

SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';   -- 指定转义字符，字符自己定义


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

-- 3、in
-- 案例1：查询员工的工种编号是 PU_MAN IT_PROG ST_MAN的员工名和工种编号。
SELECT last_name,job_id FROM employees WHERE job_id in('PU_MAN','IT_PROG','ST_MAN');

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

-- 安全等于  <=>
-- 案例1：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;

-- 案例2：查询工资等于12000的员工名和工资；

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

经典面试题
试问 SELECT * FROM employees;
和 SELECT * FROM employees WHERE commission_pct LIKE ‘%%’ AND last_name LIKE ‘%%’;
结果是否一样？

答案：不一样，因为判断的commission_pct字段有null值.

进阶3：排序查询

语法：
SELECT 查询列表
FROM 表名
【where 筛选条件】
ORDER BY 排序列表【desc|asc】

asc 升序，desc 降序，不写默认是升序

-- 案例1： 查询员工信息，要求按工资从高到低排序

SELECT * FROM employees ORDER BY salary DESC;


-- 案例2；查询员工部门编号大于等于90的员工信息，并按入职日期从小到大排序
SELECT * FROM employees WHERE department_id >=90 ORDER BY hiredate asc;

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

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

-- 案例4：按年薪的高低显示员工信息和年薪 【按别名排序】

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

-- 案例5：按姓名的长度显示员工的姓名和工资 【函数排序】

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


-- 案例6:查询员工信息，先按工资升序，再按员工编号降序。【多字段排序】
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

进阶4：常见函数

调用： SELECT 函数名(实参列表) 【from 表名】
分类：
单行函数：
如： concat、length、 ifnull
分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数

-- 一：字符函数
-- LENGTH(str)  获取字符串长度

SELECT LENGTH('jhom');
SELECT LENGTH('张明ha');

-- CONCAT(str1,str2,...)  拼接字符串

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

-- UPPER(str)  LOWER(str)  转换字母大小写

SELECT UPPER('mary');
SELECT LOWER('MArry');

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

-- substr()  substring()
-- 注意 索引从1开始

-- 截取从指定索引处之后的所有字符
SELECT SUBSTR('我爱我的祖国',5) out_put;

-- 截取从指定索引处指定字符长度的字符
SELECT SUBSTR('我爱我的祖国',1,3) out_put;

-- 案例：将姓名中的首字母大小，其他字母小写，用_拼接，显示
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees;

-- INSTR(str,substr)    返回子字符串第一次出现的索引，没有则返回0

SELECT INSTR('滚滚长江东逝水','长江') AS out_put;

-- trim  去掉前后空格
SELECT LENGTH(TRIM('    赵盼儿   ')) AS out_put;

-- 去掉前后的a
SELECT TRIM( 'a' from 'aaaaaaaaaaaaaa赵aaaaaaa盼儿aaaaaaaaa') AS out_put;

-- LPAD(str,len,padstr)  用指定的字符左填充指定长度，总长度为len
SELECT LPAD('赵盼儿',10,'*') AS out_put;
-- 输出：*******赵盼儿

-- RPAD(str,len,padstr)  用指定的字符右填充指定长度
SELECT RPAD('赵盼儿',10,'AB') AS out_put;

-- REPLACE  替换

-- 将周芷若替换为赵敏
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;


-- 二、数学函数

-- ROUND(X)  四舍五入

SELECT ROUND(1.37);

-- 四舍五入保留2位小数
SELECT ROUND(1.358,2);  

-- CEIL(X)  向上取整，返回>=该参数的最小整数
SELECT CEIL(1.02);
SELECT CEIL(1.00);
SELECT CEIL(-1.01);

-- FLOOR(X)  向下取整，返回<=该参数的最大整数
SELECT FLOOR(1.45);
SELECT FLOOR(-1.02);

-- `TRUNCATE`(X,D)  截取，D表是小数位数
SELECT TRUNCATE(1.8767,2);

-- `MOD`(N,M)  取余, 被除数是负数，结果就是负数。

SELECT MOD(10,3);      -- 结果1

SELECT MOD(-10,-3);    -- 结果-1

SELECT MOD(10,-3);     -- 结果1


-- 三、日期函数
-- NOW()   返回系统当前日期+时间
SELECT NOW();

-- CURDATE() 返回系统当前日期，不包含时间
SELECT CURDATE();

-- CURTIME() 返回系统当前时间,不包含日期
SELECT CURTIME();

-- 可以获取指定部分的年、月、日、时、分、秒
SELECT YEAR(NOW());
SELECT YEAR('1998-10-12');

SELECT MONTH(NOW());   
SELECT MONTHNAME(NOW());  -- 显示英文的月

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

-- STR_TO_DATE(str,format)   将字符按指定的格式转换成日期类型
SELECT STR_TO_DATE('1991-2-20','%Y-%m-%d') AS out_put;

-- 查询入职日期为1992-4-3日的员工信息
SELECT * FROM employees WHERE hiredate='1992-4-3';
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3-1992','%c-%d-%Y');

-- DATE_FORMAT(date,format)  将日期格式转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');

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

-- 四：流程控制函数
-- `IF`(expr1,expr2,expr3)  if else的效果
SELECT IF(10>5,'大','小');

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没有奖金，呵呵','有奖金，嘻嘻') 备注 FROM employees;

-- CASE  

/*
case 要判断的值或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值1或语句2；
...
else 要显示的值n或语句n;
end
*/
/*
案例：查询员工工资，要求
部门id=30，显示的工资为1.1倍
部门id=40，显示的工资为1.2倍
部门id=50，显示的工资为1.3倍
其他部门，显示原工资
*/

SELECT salary 原工资,department_id,
CASE department_id
WHEN 30 then salary*1.1
WHEN 40 then salary*1.2
WHEN 50 then salary*1.3
ELSE salary
END AS 新工资
FROM employees;

/*
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

*/
/*
案例： 查询员工的工资情况，
如果工资>20000,显示A级别，
如果工资>15000,显示B级别，
如果工资>10000,显示C级别，
否则，显示D级别
*/

SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
else 'D'
END AS 工资级别
FROM employees;

分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum求和、 avg求平均值、max 求最大值、min求最小值、 count统计个数
特点：
sum、avg 一般处理数值型
max、min、count 可以处理任何类型
以上分组函数都忽略null值
可以跟distinct搭配使用去重运算。

-- 简单使用
SELECT SUM(salary) FROM employees;
SELECT avg(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,avg(salary) 平均值,MAX(salary) 最大值,MIN(salary) 最小值,COUNT(salary) 个数 FROM employees;


-- 和distinct搭配
SELECT count(DISTINCT salary),COUNT(salary) FROM employees;

-- COUNT
-- 统计所有行数
SELECT count(*) FROM employees;
SELECT COUNT(1) FROM employees;

进阶5；分组查询

语法
SELECT 分组函数，列（要求出现在group by 后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 句子】

注意：查询的列表必须特殊，要求是分组函数和group by 后面出现的字段
特点：
分组查询中的筛选条件分为两类

	数据源	位置	关键字
分组前筛选	原始表	group by子句的前面	where
分组后筛选	分组后的结果集	group by子句的后面	having

分组函数做筛选条件肯定放在having子句中

-- 查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

-- 查询每个位置上的部门个数
SELECT count(*),location_id
FROM departments
GROUP BY location_id;

-- 查询邮箱中包含字符a的，每个部门的平均工资
SELECT avg(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

-- 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;


-- 查询哪个部门的员工数>2
-- 1、查询出每个部门的员工数
SELECT count(*),department_id 
FROM employees
GROUP BY department_id;

-- 2、在第1步的基础上再筛选出大于2的员工数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) >2;

-- 查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

-- 查询领导编号大于102的每个领导手下的员工的最低工资大于5000的领导编号是哪个，以及最低工资

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


-- 按表达式分组
-- 按员工的姓名长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些
SELECT count(*),LENGTH(last_name) lenj
FROM employees
GROUP BY LENGTH(last_name)
HAVING count(*)>5;

-- 按多个字段分组
-- 查询每个部门每个工种的平均工资
SELECT avg(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

进阶6，连接查询

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

按年代分类
sql92标准：仅仅支持内连接
sql99标准【推荐】支持内连接+外连接（左外+右外）+ 交叉连接
按功能分类
内连接：等值连接、非等值连接、自连接
外连接：左外连接、右外连接
全外连接（mysql不支持）
交叉连接

sql92标准：
等值连接的特点：

1、多表等值连接为多表的交集部分
2、n表连接，至少需要n-1个连接条件
3、多表的顺序没有要求
4、一般需要为表起别名

-- 案例1：查询女神名对应的男神名

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

-- 案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

-- 为表起别名
/*
   注意：如果为表起了别名，则查询的字段就不能在用原来的的表名去限定
*/
-- 查询员工名、工种号、工种名（两个表相同的字段有歧义，需要加表名限定）
SELECT last_name,employees.job_id,job_title
FROM employees,jobs
WHERE employees.job_id=jobs.job_id;

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

-- 查询有奖金的员工名和部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND commission_pct IS NOT NULL;

-- 查询城市名中第二个字符为‘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,locations
WHERE departments.location_id=locations.location_id
GROUP BY city

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

-- 查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT job_title,count(*)
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY e.job_id
ORDER BY count(*) DESC;

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

-- 非等值连接
-- 查询员工的工资和工资级别
SELECT * from job_grades
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;

-- 自连接
-- 查询员工名和上级的名称
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

sql99语法：
语法：
select 查询列表
FROM 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 条件筛选】
【order by 排序】
分类：

内连接：inner
外连接
左外：left [outer]
右外：right [outer]
全外： full [outer]
交叉连接：cross
全外连接mysql不支持，可以用union 实现效果
左外连接 union 右外连接

1、内连接语法：
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件
2、分类：等值连接、非等值连接、自连接
3、特点：

1、添加排序、筛选、分组
2、inner 可以省略
3、筛选条件放到where后面，连接条件放到on后面，提供分离性，便于阅读
4、inner join 连接和sql92的等值连接效果是一样的，都是查询多表的交集

等值连接

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

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

-- 案例2：查询员工名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.last_name LIKE '%e%';

-- 案例3：查询部门个数>3的城市名和部门个数
SELECT city,count(*)
FROM locations l
INNER JOIN departments d
ON l.location_id = d.location_id
GROUP BY city
HAVING count(*)>3

-- 案例4、查询哪个部门的员工个数>3的部门名和员工个数，并那个数降序
SELECT count(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name
HAVING count(*)>3
ORDER BY count(*) DESC;

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

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
INNER JOIN jobs j ON e.job_id=j.job_id
ORDER BY department_name DESC;

非等值连接

-- 查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

-- 查询工资级别的个数>20的个数，并按工资级别降序

SELECT count(*),grade_level
FROM employees e
INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY grade_level
HAVING count(*)>20
ORDER BY grade_level DESC;

自连接

-- 查询员工的名字和领导的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id;

外连接
应用场景：用于查询一个表中有，一个表中没有的记录
特点：

1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的值，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2、左外连接，left join 左边的是主表
右外连接，right join 右边的是主表
3、左外和右外交换连个表的顺序，可以实现同样的效果

-- 引入、查询男朋友不在男生表的女生名
-- 左外链接
SELECT b.name
FROM beauty b
LEFT JOIN boys
ON b.boyfriend_id=boys.id
WHERE boys.id IS NULL

-- 案例1：查询哪个部门没有员工
 -- 左外
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL

-- 右外
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id
WHERE e.employee_id IS NULL

进阶7：子查询

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

按子查询出现的位置
SELECT 后面：仅仅标量子查询
FROM 后面：表子查询
WHERE或having 后面
标量子查询
列子查询
行子查询
EXISTS后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列）
表子查询（结果集一般为多行多列）

1、where 或是having后
特点：

1、子查询放在小括号内
2、子查询一般放在条件的右侧
3、标量子查询，一般搭配着单行操作符使用 > < >= <= = <>

1、标量子查询

-- 案例1：谁的工资比Abel高？
-- 查询出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 、和工资
-- 1、查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141

-- 2、查询出143号员工的工资
SELECT salary
FROM employees
WHERE employee_id=143

-- 3、将1、2的结果带入查询
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
   SELECT job_id
   FROM employees
   WHERE employee_id=141
) AND salary >(

    SELECT salary
    FROM employees
    WHERE employee_id=143
);

-- 案例3：返回公司工资最低的员工姓名、job_id 和salary
-- 1、查询出最低工资
SELECT MIN(salary)
FROM employees

-- 2、按最低工资查询需要字段
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
   SELECT MIN(salary)
   FROM employees
);

-- 案例4： 查询部门最低工资大于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的基础上，筛选出结果大于1结果的值
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id=50
);

2、列子查询（多行子查询）

-- 案例1：返回location_id 是1400或者是1700 的部门中所有员工姓名
-- 1、查询出location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

-- 2、在查询部门编号为1的结果中的员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT department_id
		FROM departments
		WHERE location_id IN(1400,1700)
);

-- 案例2：返回其他工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号，姓名、job_id、salary
-- 1、查询出job_id为‘IT_PROG’的工资
SELECT distinct salary
FROM employees
WHERE job_id='IT_PROG'

-- 2、查询员工的员工号，姓名、job_id、salary，salary<1结果中的任一一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary <ANY(
		SELECT distinct salary
		FROM employees
		WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG'

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


-- 案例3：返回其他工种中比job_id为‘IT_PROG’工种所有工资低的员工的员工号，姓名、job_id、salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary <ALL(
		SELECT distinct salary
		FROM employees
		WHERE job_id='IT_PROG'
)AND job_id<>'IT_PROG'

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

-- 3、行子查询（结果集一行多列或多行多列）
-- 案例：查询员工编号最小，并且工资最高的员工信息
SELECT * 
FROM employees
WHERE (employee_id,salary)=(
     SELECT MIN(employee_id),MAX(salary)
     FROM employees
);


-- 1、查询出最小的员工编号
SELECT MIN(employee_id)
FROM employees

-- 2、查询出员工的最高工资
SELECT MAX(salary)
FROM employees

-- 3、在1、2、的结果上查询出员工信息
SELECT * 
FROM employees
WHERE employee_id=(
     SELECT MIN(employee_id)
     FROM employees 
)AND salary=(
    SELECT MAX(salary)
    FROM employees
);

-- 二：select 后面
-- 案例1：查询每个部门的员工个数
-- 第一种
SELECT department_id,count(*)
FROM employees
GROUP BY department_id

-- 第二种，查询出departments表
SELECT d.*,(
     SELECT count(*)
     FROM employees e
     WHERE e.department_id=d.department_id
) 个数
FROM departments d;

-- 案例2：查询员工号=102的部门名
-- 1、查询出员工号102的部门id
SELECT department_id
FROM employees
WHERE employee_id=102
-- 2、查询出部门号为1结果的部门名称
SELECT department_name
FROM departments d
WHERE d.department_id=(
			SELECT department_id
			FROM employees
			WHERE employee_id=102
)

-- 或者
SELECT department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_id=102

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

-- 三：from后面
-- 案例1、查询每个部门的平均工资的工资等级
-- 1、查询出每个部门的平均工资
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
-- 2、将1查询出来的结果集跟job_grades表关联查询
SELECT  ag_de.*,j.grade_level
FROM (
   SELECT AVG(salary) ag,department_id
   FROM employees
   GROUP BY department_id
) ag_de
INNER JOIN job_grades j
ON ag_de.ag BETWEEN j.lowest_sal AND j.highest_sal


-- 四：exists后面，（相关查询）
SELECT EXISTS(SELECT employee_id FROM employees);

进阶8：分页查询

语法：
select 查询列表
FROM 表
【连接类型 join 表2
on 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后筛选条件
ORDER BY 排序字段】
LIMIT OFFSET,size;
offset：要显示条目的起始索引，（索引从0开始）
size:要显示的条目个数
特点：

1、limit语句放在查询语句的最后
2、公式
要显示的页数page,每页的条目数size
SELECT 查询列表
FROM 表
limit (page-1)*size,size;

-- 案例1: 查询前五条员工信息
SELECT * FROM employees LIMIT 5;

-- 案例2：查询第11条到25条信息
SELECT * FROM employees LIMIT 10,15;

-- 降序后随机取一条数据
SELECT * FROM employees ORDER BY RAND() DESC LIMIT 1;

进阶9：联合查询

union:联合，合并，将多条查询语句的结果合并成一条结果
语法：
SELECT * from 表
UNION
查询语句2
UNION
查询语句3
应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：

1、要求多条查询语句的查询列数是一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union默认去重，如果使用union all 可以包含重复项。

二：DML语言

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

1、插入语句
方法一：
语法：
insert into 表名(列名,…) values(值1,…);

-- 1、插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id) VALUES(13,'唐艺昕','女','1990-4-3','13898798765',NULL,2);
-- 2、不可以为null的列必须插入值，可以为null的值可以插入为null，或者不插入该列值

方法二：
insert into 表名
set 列名1=值,l列名2=值

2、修改语句

修改单表的记录
update 表名
set 列=新值,列=新值,…
where 筛选条件
修改多表的记录

3、删除语句
方法一：
语句：

单表删除
delete from 表名 where 筛选条件
多表删除

方式二：
语法：
truncate table 表名;
注意：truncate 不能加where筛选条件，该语句对表进行清空。
不同：

不同:
1、truncate 不能加where筛选条件，delete可以加筛选条件
2、truncate 删除效率高一丢丢
3、假如删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始
4、truncate 删除没有返回值，delete删除有返回值
5、truncate 删除不能回滚，delete删除可以回滚

三：DDL语言

数据库定义语言
库和表的管理
1、库的管理：创建、修改、删除
2、表的管理：创建、修改、删除
关键字：

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

库的创建
语法：
create database [if not exists] 库名;

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

-- 更改库的字符集
alter database books character set gbk;

-- 删除库
drop database if exists books;

表的管理
1、表的创建
语法：
CREATE TABLE IF NOT EXISTS 表名(
列名列的类型【(长度) 约束】,
列名列的类型【(长度) 约束】,
列名列的类型【(长度) 约束】,
…
列名列的类型【(长度) 约束】
);

-- 创建book表
CREATE TABLE IF NOT EXISTS book(
    id INT, # 编号
    bName VARCHAR(20),# 书名
    price DOUBLE, # 价格
    authorId INT, #作者编号
    publishDate DATETIME #出版日期


);

DESC book;

-- 创建author表
CREATE TABLE IF NOT EXISTS author(
   id INT,
   au_name VARCHAR(20),
   nation VARCHAR(10)
);

DESC author;

2、表的修改
ALTER TABLE 表名 add|drop|modify|change column 列名【类型约束】;

-- 1、修改列名
ALTER TABLE book CHANGE COLUMN publishDate pub_date datetime;

-- 2、修改列的类型或约束
ALTER TABLE book modify COLUMN pub_date TIMESTAMP;

-- 3、添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
-- 4、删除列
ALTER TABLE author DROP COLUMN annual;

-- 5、修改表名
ALTER TABLE author RENAME TO bo_author;

DESC bo_author;

3、表的删除

DROP TABLE IF EXISTS bo_author;

SHOW TABLES;

4、表的插入

INSERT INTO bo_author(id,au_name,nation) VALUES
(1,'村上春树','日本'),
(2,'钱钟书','中国'),
(3,'金庸','中国'),
(4,'莫言','中国');

5、表的复制

-- 1、仅仅复制表结构
CREATE TABLE copy LIKE bo_author;
DESC copy;

-- 2、复制表的结构+数据
CREATE TABLE copy1 SELECT * FROM bo_author;

SELECT * FROM copy1;


-- 3、只复制部分数据
CREATE TABLE copy2 SELECT id,au_name FROM bo_author WHERE nation='中国';

SELECT  * FROM copy2;

-- 4、仅仅复制某些字段
CREATE TABLE copy3 SELECT id,au_name FROM bo_author WHERE 0;
select * from copy3;

四：常见的数据类型

常见的数据类型：

数值型：整型、小数（定点数，浮点数）
字符型：
较短的文本：char、varchar
较长的文本：text、blob(较长的二进制文本)
日期型

1、整型
分类：

tinyint	smallint	mediumint	int/integer	bigint
1字节	2字节	3字节	4字节	8字节
特点：

1、如果不设置无符号还是有符号，默认有符号，如果想设置无符号，需要添加unsigned关键字
2、如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值。
3、如果不设置长度，会默认长度，长度代表显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用。

2、小数

浮点型
float(M,D)
DOUBLE(M,D)
定点型
dec(M,D)
decimal(M,D)

特点：

1、M :整数部位+小数部位
D:小数部位，如果超过范围，则插入临界值
2、M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double,则会根据插入的数值的精度来决定精度。
3、定点型精度较高，如果要求插入对的数值的精度较高，如货币运算则考虑。

3、字符型
较短的文本：char、varchar
较长的文本：text、blob(较长的二进制文本)
特点：

	M的意思	特点	空间的耗费	效率
char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar(M)	最大的字符数，不可省略	可变长度的字符	比较节省	低

4、枚举enum

CREATE TABLE tab_c1(
    c1 enum('a','b','c')

);

DESC tab_c1;
-- 只能是枚举值的中的一个
INSERT INTO tab_c1 VALUES('a');
INSERT INTO tab_c1 VALUES('b');

5、SET 集合

-- SET 集合

CREATE TABLE tab_set(
    c1 SET('a','b','c','d')
);

-- 可以是set中的多个值
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('b','c');

6、日期型
分类
date :只保存日期，
time；只保存时间
year :只保存年
datetime:保存日期+时间
timestamp:保存日期+时间
特点：

	字节	范围	时区等的影响
datetime	8	1000-9999	不受
timestamp	4	1970-2038	受

五：常见的约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性
分类：六大约束

NOT NULL :非空，用于保证该字段的值不为空，比如姓名、学号等。
DEFAULT: 默认，用于保证该字段有默认值，比如性别
PRIMARY KEY: 主键，用于保证该字段的值的唯一性，且不为空，比如学号，员工编号等
UNIQUE :唯一，用于保证该字段的唯一性，可以为空，只能有一个空值，比如座位号
CHECK:检查约束【mysql不支持】，比如年龄，性别
FOREIGN KEY:外键，用于限制两个表的关系，用于限制该字段的值必须来自主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值，比如员工表的部门编号，工种编号

添加约束的时机：
1、创建表时
2、修改表时
约束的添加分类：
列级约束
表级约束

1、创建时添加约束

/*
  直接在字段类型后面追加约束类型即可
  仅仅支持：主键、非空、默认、唯一

*/
-- 1、添加列级约束
DROP TABLE stuifo;
CREATE TABLE stuifo(
    id INT PRIMARY KEY,  #主键
    stuNamme VARCHAR(20) NOT NULL, #非空
    gender CHAR(1) CHECK(gender='男' or gender='女'), #检查，无效果
    seat INT UNIQUE,  #唯一
    age INT DEFAULT 18,  #默认
    majorId INT
);

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

);

DESC major;
DESC stuifo
SHOW INDEX FROM stuifo;

-- 2、添加表级约束
/*
 语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)

*/
DROP TABLE stuifo;
CREATE TABLE stuifo(
    id INT,
    stuNamme VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorId INT,
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat), #唯一
    CONSTRAINT mk FOREIGN KEY(majorId) REFERENCES major(id) #外键
);

DROP TABLE stuifo;
CREATE TABLE stuifo(
    id INT,
    stuNamme VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorId INT,
    PRIMARY KEY(id),#主键
    UNIQUE(seat), #唯一
    FOREIGN KEY(majorId) REFERENCES major(id) #外键
);

主键和唯一的大对比：

	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	是	不允许	至多一个	允许，但不推荐
唯一	是	允许	可以有多个	允许，但不推荐

外键：

1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key(一般是主键或唯一)
4、插入数据时先插入从表数据，再插入主表数据，删除时先删除从表数据再删除主表数据。

2、修改表时添加约束

1、添加列级约束：
alter table 表名 modify column 列名 字段类型  新约束；

2、添加表级约束
alter table 表名 add 约束类型(字段) 【外键的引用】；
*/
drop table book;
CREATE TABLE if not exists book(

 id int ,
 bname varchar(20),
 authorid int ,
 gender enum('男','女')
 );

desc book;
drop table author;
create table if not exists author(
    id int ,
		last_name varchar(20),
		birthday datetime
);
desc author;
-- 添加约束
alter table book modify column bname varchar(20) not null;

-- 添加主键
alter table author modify column id int primary key;
alter table author add primary key(id);
-- 去掉主键
alter table author modify column id int;
-- 添加外键
alter table book add foreign key(id) references author(id);

3、标识列
又称为自增长列
含义：不用手动的插入值，系统提供默认的序列值。
特点;

1、标识列不一定要和主键搭配使用，但要求是一个key
2、一个表可以有几个标识列？至多一个
3、标识列的类型只能是数值型
4、标识列可以通过 set auto_increment_increment=3 设置步长
或者通过手动插入起始值来设置。

-- 创建表时
CREATE TABLE tab_indetiny(
   id INT PRIMARY KEY auto_increment,
   bname VARCHAR(20)
);

INSERT INTO tab_indetiny VALUES(NULL,'Mary');

SELECT * from tab_indetiny;
-- 修改表时设置标识列
ALTER TABLE tab_indetiny MODIFY COLUMN id INT PRIMARY KEY auto_increment;

六： TCL
TRANSACTION Control LANGUAGE 事务控制语言

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

案例：转账

张三丰：1000
郭襄：1000

UPDATE 表 set 张三丰的余额=500 WHERE name='张三丰'
意外
UPDATE 表 set 郭襄的余额=1500 WHERE name='郭襄'

事务特点:
1.一致性：如果在执行事务之前数据库是一致的，那么在执行事务之后数据库也是一致的。
2.原子性：不可分割的操作单元，事务中所有操作，要么全部成功，要么撤回到执行事务前的状态
3.隔离性:事务之间的操作彼此独立和透明互不影响。事务独立运行。这通常使用锁来实现。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4.持久性：事务一旦提交，其结果是永久的。即便发生系统故障，也能恢复。

事务的创建：

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

显示事务：事务具有明显的开启和结束的标记
前提：必须设置自动提交功能为禁用
set autocommit=0; 只针对当前会话生效

mysql使用事务的步骤:
1.开启一个事务 (加锁)
set autocommit=0;
START TRANSACTION;
2.修改数据的语句(insert update delete)
3.事务的提交 (关闭该事务解锁) commit
4.取消事务 (关闭该事务解锁) rollback(第二步骤无效)

-- 查看自动提交是否开启
SHOW VARIABLES LIKE 'autocommit'
-- 查看数据引擎
show engines;

2、视图
视图是一个虚表,方便对实际表的查询操作
视图作用:
1.隐藏表的真实表名或字段名
2.视图保存在数据服务端,可以方便共享重复利用查询语句

视图创建语法:
create view 视图名
AS
查询语句;

-- 案例1：查询邮箱中包含字符a的员工名、部门名、和工种信息
-- 创建视图
CREATE VIEW v1
AS
SELECT last_name,email,department_name,j.*
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
LEFT JOIN jobs j
ON e.job_id=j.job_id;

-- 使用视图
SELECT * FROM v1 WHERE email LIKE '%a%'

-- 修改视图

  
  #方式一：
 CREATE OR REPLACE VIEW 视图名
 AS
 查询语句；

#方式二：
  ALTER VIEW 视图名
  AS
  查询语句;

#删除视图
drop view 视图名

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

-- 创建语法
CREATE PROCEDURE 存储过程名(参数名)
BEGIN
		存储过程体(一组合法的sql体)
END

/*
1、注意参数列表包含三部分
参数模式  参数名  参数类型
举例：

IN   stuname varchar(20)


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

2、如果存储过程只有一句话，begin  END 可以省略
存储过程体中的每条sql语句都需要加分号结尾
存储过程的结尾可以使用delimiter 重新设置
语法：
delimiter  结束标识付
如：
delimiter $

*/

-- 调用语法
CALL 存储过程名(实参列表);

-- 空参列表
-- 案例：插入到admin表中5条数据

SELECT * from girls.admin;

delimiter $
CREATE PROCEDURE myp1()
BEGIN
    INSERT into admin(username,password) VALUES('jake','000'),('nike','000'),('Mary','000'),('rose','000'),('tom','000');
END $

-- 调用
CALL myp1()$

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

函数和存储过程的区别：
存储过程：可以有0个返回值，也可以有多个返回值，适合做批量插入、批量更新
函数：只能有一个返回值，适合做处理数据后返回一个值

 -- 自定义函数的语法
 create  function 函数名(函数参数)
 returns  返回值类型
 begin
    函数体
 end;
 
 /*
 注意：
 1、参数列表 包含两个部分：
 参数名 参数类型
 
 2、函数体：肯定会有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不报错，但不建议
 return 值；
 3、函数体中仅有一句话，则可以省略begin end
 4、使用delimiter 语句设置结束标记
 
 */
 
 -- 二调用语句
 select 函数名(参数列表)
 
 ------------------------- 案例演示---------------------------------------
 -- 1、无参数返回
 -- 案例：返回公司的员工个数
 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('k_ing') $
 
 -- 案例2：根据部门名，返回该部门的平均工资
 create function myf3(deptName varchar(20)) returns double
 begin
		declare sal double;
		select avg() into sal
		from employees e
		inner join departments d
		on e.department_id=d.department_id
		where d.department_name=deptName;
		
		return sal;
 
 end $
 
 select myf3('IT')$