MySQL入门代码(附案例)

Mysql入门学习笔记，包含简单查询、常用函数、分组查询、多表查询、(嵌套)子查询、分页查询、联合查询、表中数据的增删改(DML)、表和库的增删改(DDL)、数据类型、常见约束、标识列、事务(TCL)与视图、变量、存储过程、函数和流程控制等相关内容。

根据b站上视频整理，作为入门级的资料，带有案例库和练习原码，方便实际操练，与大家交流学习。

b站视频网址：https://www.bilibili.com/video/BV12b411K7Zu?from=search&seid=8595952504919475801

一、简单查询

show databases;#查看所有库
use myemployees;#调用myemployees库

#  基础查询  select命令 select...from...
SELECT last_name FROM employees;
SELECT last_name,salary,email FROM employees;
SELECT `department_name`,`department_name` FROM departments; #查询效果
SELECT 100;
SELECT 'join'; #打印效果
SELECT 100*99;
SELECT 100%99;  #求余数
SELECT VERSION(); #查询版本函数
DESC departments; #desc 描述表

# 字段起别名
SELECT 100*99 AS 结果;  # as或用空格 起别名 
SELECT last_name AS 姓,first_name AS 名 FROM employees; 
SELECT last_name 姓,first_name 名 FROM employees; 
SELECT salary AS 'out put' FROM `employees`;

# distinct 去重
SELECT DISTINCT department_id FROM `employees`;

#  加号的作用:仅为运算符
SELECT 100+999 结果;
SELECT 'join'+99 打印;
SELECT NULL+99 结果;  # 只要有一方为null，结果为null

# concat()拼接函数，+号无法拼接
SELECT `last_name`+`first_name` 姓名 FROM employees; 
SELECT CONCAT(`last_name`,',',`first_name`) 姓名 FROM employees; # 用逗号隔开
# ifnull()函数 如果为null，作为0，否则正常
SELECT 
      IFNULL(`commission_pct`,0) AS 奖金率,
      commission_pct
FROM
      employees;


#  条件查询  select...from...where...
/* where后面的筛选条件有：
   条件运算符： > < = <> != >= <=
   逻辑运算符： and or not
   模糊查询：   like 
		between and
		in
		is null
*/
# 一、条件运算符
#  查询工资大于12000的员工信息
SELECT * FROM employees WHERE salary>12000;
#  查询部门编号≠90的员工姓名和部门编号
SELECT 
	CONCAT(`first_name`,`last_name`),`department_id` 
FROM 
	employees
WHERE 
	`department_id`<>90;
#  二、逻辑运算符
#  查询工资在10000到20000之间的员工名、工资以及奖金
SELECT 
	`last_name`,`salary`,`commission_pct`
FROM
	employees
WHERE
	`salary`>10000 AND 20000;
#  查询部门编号不在90到110之间，或者工资高于15000的员工
SELECT
	`last_name`
FROM
	employees
WHERE
	NOT(`department_id`>=90 AND `department_id`<=110) OR (`salary`>15000);
#  三、模糊查询
#  查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE `last_name` LIKE '%a%';
#  查询员工名中第三个字符为c,第五个字符为a的员工信息
SELECT 
	*
FROM 
	employees
WHERE 
	last_name LIKE '__c_a%';
#  查询员工名中第二个字符为_的员工信息
SELECT 
	*
FROM 
	employees
WHERE 
	last_name LIKE '_\_%';
# 查询员工编号在100到120内的员工信息
SELECT 
	*
FROM 
	employees
WHERE 
	employee_id BETWEEN 100 AND 120; # between...and...顺序不能颠倒
# 查询工作种类是PU_CLERK和ST_MAN的员工信息
SELECT 
	*
FROM 
	employees
WHERE 
	job_id IN ('PU_CLERK','ST_MAN');
# 查询奖金为null的员工名和奖金
SELECT 
	last_name,commission_pct
FROM 
	employees
WHERE 
	commission_pct  IS NULL;
# 安全等于 <=>
SELECT 
	last_name,commission_pct
FROM 
	employees
WHERE 
	commission_pct  <=> NULL;


# 排序查询
/* select 查询列表(3)
from 表(1)
where 筛选条件(2)
order by 排序列表 [asc/desc](4)
*/
SELECT * FROM employees ORDER BY salary DESC; # desc降序
SELECT * FROM employees ORDER BY salary ASC;  # asc升序
#  按年薪将序
SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC;
#  按函数排序
SELECT LENGTH(last_name) 字节长度，last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#  多个字段排序
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

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

二、常用函数

/* 1.单行函数：length,concat,ifnull,isnull （传入一个值传出一个值）
   2.分组函数：做统计使用（传入多个值传出一个值，如求均值） */
#-------------------------------单行函数-----------------------------------
# 一、常见函数
# length 长度
SELECT LENGTH('aa'); #2
SELECT LENGTH('对a');#3+1=4
SHOW VARIABLES LIKE '%char%';
# concat 拼接
SELECT CONCAT(last_name,'_',first_name) 姓名
FROM employees;
# upper、lower 大小写
SELECT UPPER('john');#JOHN
SELECT LOWER('joHn');#john
SELECT # 姓大写，名小写，两者拼接
	CONCAT(UPPER(last_name),'_',LOWER(first_name)) 姓名
FROM 
	employees;
# substr 字符串截取
#截取从指定索引处后所有的字符
SELECT SUBSTRING('李莫愁爱上了陆展元',7) output;#陆展元
#截取从指定索引处后指定长度的字符，注意，都是字符，而不是字节
SELECT SUBSTRING('李莫愁爱上了陆展元',1,3) output;#李莫愁
# instr 返回子串第一次出现的索引，如果找不到，返回0
SELECT INSTR('杨不殷爱上殷六侠','殷六侠') AS output;
# trim 删除两端指定字符
SELECT TRIM("   zhang   ") AS output; #删除两端空格
SELECT TRIM('a' FROM "aaaaazhang aaa") AS output; #删除两端指定值a
# lpad 用指定字符实现左填充指定长度
SELECT LPAD('殷素数',10,'*') AS output; #长度不足，左填充
SELECT LPAD('殷素数',2,'*') AS output;  #长度超过，截取
# rpad 用指定字符实现右填充指定长度
SELECT RPAD('殷素数',10,'*') AS output; #长度不足，右填充
SELECT RPAD('殷素数',2,'*') AS output;  #长度超过，截取
# replace 替换
SELECT REPLACE("张无忌爱上周芷若，周芷若",'周芷若','赵敏');

# 二、数学函数
# round 四舍五入
SELECT ROUND(-1.50);# -2
SELECT ROUND(1.567,2);# 指定取两位小数
# ceil 向上取整
SELECT CEIL(1.0);#1
SELECT CEIL(1.2);#2
SELECT CEIL(-1.2);#-1
# floor 向下取整
SELECT FLOOR(9.9);#9
SELECT FLOOR(-9.9);#-10
SELECT CEIL(1.0);#1
# truncate 截断
SELECT TRUNCATE(1.999,1);# 小数点后保留1位（不进行四舍五入）
# mod 取余数
SELECT MOD(10,3); # 10/3余数为1
# rand 随机数
SELECT RAND(); 区间取0不取1 [0,1)

# 三、日期函数
SELECT NOW(); #日期+时间
SELECT CURDATE(); # 只有日期
SELECT CURTIME(); # 只有时间
# 获取指定部分年，月，日，小时，分钟，秒
SELECT YEAR(NOW());#年
SELECT YEAR(hiredate) 年 FROM employees; # 表中的年
SELECT MONTH(NOW());# 月
SELECT MONTHNAME(NOW()); # 英文显示月份
SELECT DAY(NOW());# 日
SELECT HOUR(NOW());# 小时
SELECT MINUTE(NOW());# 分钟
SELECT SECOND(NOW());# 秒
# str_to_date 将字符串转化为时间
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS output; # Y四位数年,c月,d日
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');
# date_format 将时间转化为字符串
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS output;# y二位数年,m月,d日
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees 
WHERE commission_pct IS NOT NULL;

# 四、其他函数
SELECT VERSION(); # 显示当前系统版本
SELECT DATABASE(); # 显示当前数据库
SELECT USER(); # 显示当前用户

#五、流程控制函数
# if函数 ：if else效果
SELECT IF(10>5,'大','小');
SELECT `last_name`,IF(`commission_pct` IS NULL,'没奖金','有奖金') FROM employees;
# case函数     （判断的字段在case后）
/* case 要判断的字段或表达式
   when 常量1 then 要显示的值1或语句1;
   when 常量2 then 要显示的值2或语句2;
   ...
   else 要显示的值n或语句n;
   end  */
 /* 案例：查询员工的工资，要求
   部门号=30，显示的工资为1.1倍
   部门号=40，显示的工资为1.2倍
   部门号=50，显示的工资为1.3倍
   其他部门，显示工资为原工资  */
SELECT salary 原工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资 
FROM employees;
# case 做多重if使用 （判断的字段在when后）
/* case 
   when 条件1 then 要显示的值1或语句1
   when 条件2 then 要显示的值2或语句2
   ...
   else 要显示的值n或语句n 
   end   */
/* 案例 ： 查询员工的工资情况
  如果工资>20000，显示A级别
  如果工资>15000，显示B级别
  如果工资>10000，显示C级别
  否则，显示D级别 */
SELECT `last_name`,`salary`,
CASE 
WHEN salary>20000 THEN 'A级别'
WHEN salary>15000 THEN 'B级别'
WHEN salary>10000 THEN 'C级别'
ELSE 'D级别'
END AS 工资级别
FROM employees;
#--------------------------------练习练习练习---------------------------------------------
# 显示系统时间（日期+时间）
SELECT NOW();
# 查询员工号、姓名、工资以及工资提高百分之20%后的结果
SELECT `employee_id`,`last_name`,`salary`,`salary`*1.2 新工资 FROM employees;
# 将员工的姓名按首字母排序，并写出姓名长度
SELECT LENGTH(last_name) 长度, SUBSTR(`last_name`,1,1) 首字符
FROM employees
ORDER BY SUBSTR(`last_name`,1,1);
#  使用case-when
SELECT job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
ELSE job_id
END
FROM employees;
#-----------------------------------分组函数---------------------------------------------
# 简单使用
SELECT SUM(`salary`) FROM employees; # 工资之和
SELECT AVG(`salary`) FROM employees; # 平均数
SELECT MIN(`salary`) FROM employees; # 最小值
SELECT MAX(`salary`) FROM employees; # 最大值
SELECT COUNT(`salary`) FROM employees; # 计数
/* 1、sum、avg一般用于处理数值型 (不计算null值)
   2、min、max、count支持任何类型 
   3、和分组函数一同查询的字段要求是group by后的字段  */
# 和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT `commission_pct`),COUNT(`commission_pct`) FROM employees;
# count函数的详细命令
SELECT COUNT(*) FROM employees; # 统计行数
SELECT COUNT(1) FROM employees; # 与上同
#----------------------------------练习练习练习------------------------------------------
# 查询公司员工工资的最大、最小、平均值，求总和
SELECT MAX(`salary`),MIN(`salary`),AVG(`salary`),SUM(`salary`) FROM employees;
# 查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(`hiredate`),MIN(`hiredate`)) FROM employees;
SELECT DATEDIFF(NOW(),'1996-5-29'); # 查看自己活了多少天
# 查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE `department_id`=90;
#---------------------------------------------------------------------------------------

三、分组查询

/* # 分组查询 group by
   select 分组函数,列（要求出现在group by后面）
   from 表
   where 筛选条件(原始表源)
   group by 分组的列(单个、多个字段都可以)
   having 条件 (分组后结果集)
   order by 排序子句 */
# 查询每个工种的最高工资
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
SELECT COUNT(*),`department_id`
FROM employees
GROUP BY `department_id`# 查询每个部门的员工数
HAVING COUNT(*)>2; # having 找出个数>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`)
FROM employees
GROUP BY LENGTH(`last_name`)
HAVING COUNT(*)>5;

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

# 添加排序
# 查询每个部门每个工种的平均工资，按平均工资的高低显示出来
SELECT AVG(`salary`) AS 工资,`department_id`,`job_id`
FROM employees
GROUP BY `department_id`,`job_id`
ORDER BY AVG(`salary`) DESC;

#---------------------------------练习练习练习-------------------------------------------
# 查询各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`;
# 查询员工最高工资和最低工资的差距
SELECT MAX(`salary`)-MIN(`salary`) FROM employees;
# 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(`salary`),`manager_id`
FROM employees
WHERE `manager_id` IS NOT NULL
GROUP BY `manager_id`
HAVING MIN(`salary`) > 6000;
# 查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT AVG(`salary`) 平均工资,COUNT(*) 员工数量,`department_id`
FROM employees
GROUP BY `department_id`
ORDER BY 平均工资 DESC;
# 选择具有各个job_id的员工人数
SELECT COUNT(*) 人数,`job_id`
FROM employees
GROUP BY `job_id`;
#---------------------------------------------------------------------------------------

四、多表查询

/* 多表查询：查询的字段来自多个表 select...from...where(连接条件)...
   笛卡尔乘积现象：表1有m行，表2有n行，结果为m*n行
   发生原因：没有有效的连接条件
   如何避免：添加有效的连接条件
   分类： 	
	按年代分类：sql92标准(内连接)、sql99标准(内连接+左右外连接+交叉连接)
	按功能分类：
		内连接:等值连接、非等值连接、自连接
		外连接:左外连接、右外连接、全外连接
		交叉连接
*/
SELECT * FROM beauty;
SELECT * FROM boys;
# 一、sq192标准
/*1、等值连接
  ①、多张表等值连接的结果为多表的交集部分
  ②、n表连接至少需要n-1个连接条件
  ③、多表的顺序没有要求
  ④、一般需要起别名  */
# 查询女神名和对应的男神名
SELECT NAME,boyName 
FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id;
# 查询员工名和对应部门名
SELECT `last_name`,`department_name`
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
# 为表起别名，提高简洁度
# 查询员工名、工种号、工种名
SELECT `last_name`,e.`job_id`,j.`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(*),l.city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
# 查询有奖金的每个部门的部门名、领导编号和该部门的最低工资
SELECT MIN(salary),d.`manager_id`,d.`department_name`
FROM employees e,departments d
WHERE e.`department_id`= d.`department_id` AND `commission_pct` IS NOT NULL
GROUP BY d.`department_name`,d.`manager_id`;

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

# 实现三表连接
#