MySQL

MySQL基础

数据库可以解决什么问题
- 实现数据持久化
- 使用完整的管理系统统一管理，易于查询

相关概念
- DB
	数据库(database):存储数据的仓库。它保存了一系列有组织的数据。

- DBMS
	数据库管理系统(Database Management System).数据库是通过DBMS创建和操作的容器
	
- SQL
	结构化查询语句(Structure Query Language):专门用来与数据库通信的语句。

SQL命令
1.查看所有数据库
show databases;
2.查看所有表
show tables;
show tables from 库名;
3.进入表内
use 表名;
4.查看目前在那个库
select database();
5.创建表
create table stuinfo(
	id int,
	name varchar(20));
6.查看表结构
desc 表名; 例如:desc stuinfo;
7.查询表中数据
select * from stuinfo;
8.查看数据库版本
select version();

1.DQL:主要用来查询 关于Select
2.DML:主要用来操作 插入、修改、删除
3.DDL:库和表的定义 操作库的
4.DCL:事务控制语言 操作事务

进阶1:基础查询
语法:
select 查询列表 from 表名;
1.查询列表可以是:表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格

#1.查询表中的单个字段
select last_name from employees;
#2.查询表中的多个字段
select last_name,salary,email from employees;
#3.查询表中的所有字段
select * from employees;
#4.查询常量值
select 100;
select 'john';
#5.查询表达式
select 100%98; 2
#6.查询函数
select VERSION(); 5.7.14    
#7.起别名
select 100%98 AS 结果;
select last_name AS 姓,first_name AS 名 from employees;
#8.去重
select DISTINCT(department_id) from employees;
#9.+的作用
/**
*Java中+号
*1.运算符，两个操作数都为数值型
*2.连接符，只要有一个操作数为字符串，结果还是字符串
*
*mysql的+
*仅仅只有一个功能：运算符
*1.select 100+90;两个操作数都为数值型，则做加法运算190
*2.select '123'+90;其中一方为字符串，试图将字符型数值转换成数值型，
*										如果转换成功，则继续做加法运算 213
*3.select 'john'+90; 90 如果转换失败，则将字符型数值转换成0
*4.select null+10; null 只要其中一方为null，则结果肯定是null
*/
#案例：查询员工名和姓连接成一个字段，并显示为姓名
select last_name+first_name as 姓名 from employees; 错误的查询，结果为0
select CONCAT(last_name,first_name) as 姓名 from employees; 正确
#10.IFNULL
select IFNULL(commission_pct,0) AS 奖金率,commission_pct from employees;

进阶2:条件查询
/**
语法：
		select
					查询列表
		from
    			表名
    where
    			帅选条件;
    			
分类：   
		一、按条件表达式筛选
		条件运算符：> < = != <> >= <=
		二、按逻辑表达式筛选
		逻辑运算符：&& ｜｜ ！
							and or not
		三、模糊查询
    		like
    		between and
    		in
    		is null
 */
 
#1.按条件表达式筛选
#案例1:查询工资>12000的员工信息
SELECT * FROM employees WHERE salary > 12000;
#案例2:查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id <> 90;
#案例3:查询工资在1000到2000之间的员工姓名、工资以及奖金
SELECT
	last_name,
	salary,
	commission_pct 
FROM
	employees 
WHERE
	salary >= 10000 
	AND salary <= 20000;

#案例4:查询部门编号不是在90到110之间，或者工资高于15000的员工信息	
SELECT
	* 
FROM
	employees 
WHERE
	department_id < 90 OR department_id > 110 
	OR salary > 15000;
 
 
#3.模糊查询
1.%:任意多个字符，包含0个字符
2._:任意单个字符
#案例一:查询员工名中包含字符a的员工信息
SELECT
	* 
FROM
	employees 
WHERE
	last_name LIKE '%a%';
#案例二:查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	last_name LIKE '__e_a%';
#案例三:查询员工名中第二个字符为_的员工名
转译：
1.符号'_':可以用'\_'来转译
2.还可以用 例2方法，ESCAPE '$'指出
SELECT
	last_name 
FROM
	employees 
WHERE
	last_name LIKE '_\_%';
	
例2
SELECT
	last_name 
FROM
	employees 
WHERE
	last_name LIKE '_$_%' ESCAPE '$';

#4.between and 包含临界值
#案例一:查询员工编号在100到200之间的员工信息
SELECT
	* 
FROM
	employees 
WHERE
	employee_id BETWEEN 100 
	AND 120;

#5.in	
#案例一:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES
SELECT
	last_name,
	job_id 
FROM
	employees 
WHERE
	job_id IN ( 'IR_PROT', 'AD_VP', 'AD_PRES' );
	
#6.is null
#案例一:查询没有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct 
FROM
	employees 
WHERE
	commission_pct IS NULL;
#案例二:查询有奖金的员工和奖金率
SELECT
	last_name,
	commission_pct 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL;
#7.安全等于 <=>	
#案例一:查询工资为12000的员工信息
SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	salary <=> 12000;
	
IS NULL:仅仅可以判断null值
<=>		 :既可以判断NULL值，又可以判断普通的数值

#进阶三
#1.排序
#语法
select 查询列表
form 表
[where 筛选条件]
order by 排序列表 [asc|desc]

1.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)) 年薪
FROM
	employees 
ORDER BY
	salary*12*(1+IFNULL(commission_pct,0)) DESC;
#案例四:按年薪的高低显示员工的信息和年薪(按别名排序)
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;s	

#进阶四:常见函数
分类
1.单行函数：concat、length、ifnull函数
2.分组函数：

- 字符函数
- 数学函数
- 日期函数
-	其他函数
- 流程控制函数

# 一.字符函数
#length
SELECT LENGTH
-- 获取参数值的字节个数
SELECT LENGTH('john'); -- 4
SELECT LENGTH('张三丰hahaha'); -- 15
-- 查看字符集
SHOW VARIABLES LIKE '%char%';-- utf8mb4

#2.cincat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) FROM employees;

#3.uppder、LOWER(str)
#4.substr substring 索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',6) out_put; -- 了陆展元
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; -- 李莫愁

#5.instr 返回字符串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不梅爱上了殷六侠','殷六侠') AS out_put; -- 7

#6.TRIM
SELECT LENGTH(TRIM('   张翠山   ')) AS out_put;-- 9
# 去掉前后的a
SELECT TRIM('a' FROM 'aaaaaaaaaaaaaa牛aaaaaaaaaaniuzzzaa') AS out_put;-- 牛aaaaaaaaaaniuzzz
#7.LPAD 左填充指定字符长度
SELECT LPAD('牛牛牛',10,'*') AS out_put;-- *******牛牛牛
#8.RPAD 右填充指定字符长度
SELECT RPAD('牛牛牛',10,'*') AS out_put;-- 牛牛牛*******
#9.replace替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;-- 张无忌爱上了赵敏

# 二.数学函数
# 1.round 四舍五入
SELECT ROUND(-1.55);-- -2
# 小数点后面保留两位
SELECT ROUND(1.567,2);-- 1.57
# 2.CEIL(X) 向上取整，返回>=该参数的最小整数
SELECT CEIL(1.00);-- 1
# 3.FLOOR(X) 向下取整，返回<=该参数的最大整数
SELECT FLOOR(9.99);-- 9
SELECT FLOOR(-9.99); -- -10
# TRUNCATE(X,D)截断
SELECT TRUNCATE(1.666666,1); -- 1.6
# MOD(N,M)去余数 a-a/b*b
SELECT MOD(10,-3); -- 1

# 三.日期函数
# NOW()返回当前系统日期+时间
SELECT NOW();-- 2021-07-07 22:41:14
# CURDATE()返回当前系统日期，不包含时间
SELECT CURDATE();-- 2021-07-07
# CURTIME()返回当前时间，不包含日期
SELECT CURTIME();-- 22:43:20
# 可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年; -- 2021
SELECT YEAR('1998-1-1') 年; -- 1998
SELECT YEAR(hiredate) 年 FROM employees;
# 月
SELECT MONTH(NOW()) 月;-- 7
SELECT MONTHNAME(NOW()) 月; -- July

#STR_TO_DATE(str,format)将字符通过指定的格式转换成日期
SELECT STR_TO_DATE( '4-3 1992', '%c-%d %Y' );-- 1992-04-03
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日') AS out_put;	-- 21年07月07日

#四.其他函数
SELECT VERSION(); -- 5.7.14
SELECT DATABASE(); -- myemployees
SELECT USER(); -- root@localhost

#五.流程控制函数
#1.if函数：if else的函数
SELECT IF(10>5,'大','小');-- 大

SELECT
	last_name,
	commission_pct,
IF
	( commission_pct IS NULL, '没有奖金，呵呵', '有奖金，哈哈' ) 备注 
FROM
	employees;
	
2.case函数的使用一：switch case的效果
switch(变量或表达式){
	case 常量1:语句1;break；
	...
	 default:语句n;break;
}

mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值1或语句2;
...
else 要显示的值n或语句n
end

-- 部门号=30，显示的工资为1.3倍
-- 部门号=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;
3.case函数的使用二 多重if
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
特点
1.sum、avg、一般用于处理数值型；max、min、count可以处理任何类型
2.以上分组函数都忽略null值
# 1.简单使用
select sum(salary) from employees;