MySQL基础（一）

常见问题

1.cannot add foreign key constraint

不能添加外键约束

• 主表是否有主键以及从表关联字段是否为从表外键
• 核对从表字段与主表关联字段的数据类型是否相同
• 关键字reference后面必须有空格

2.不区分大小写,不区分单引号和双引号

select 和SelEct和SElect没有区别
一般用单引号

3.SELECT VERSION()

SELECT NOW() -- 2021-12-29 13:57:33
SELECT LOCALTIME() -- 2021-12-29 13:57:33
SELECT SYSDATE()--系统时间 2021-12-29 13:57:33
SELECT CURDATE() -- 2021-12-29

SELECT FLOOR(1.2) -- 1
SELECT CEIL(1.2)-- 2
SELECT ABS(-1.2) -- 1.2
SELECT RAND() -- 0-1之间的随机数
SELECT 100-3*10
SELECT @@auto_increment_increment -- 查询自增的步长

4. -uroot -pAdmin@123$ / show databases;

5. 注释

-- 第一种 两个短线和注释内容之间一定要有空格

#第二种

-- -----------------------------------------------------
-- 第二种的变形：常常这样三行书写 当成对下文中数据库定义语句的注释  
-- ----------------------------------------------------- 

/*   
第三种
*/
前面两种本质是一样的，缩起来都是-- Comment
第三种缩起来是/*Comment*/

2.SQL简介

SQL: Structured Query Language 结构化查询语言。

数据库语言，有交互性。

2.1 数据库分类

• 关系型数据库：MySql SQLServer Oracle 结构化

  • 类似Excel表，行和行之间的关系，表跟表之间的关系

• 非关系型数据库:Redis,MongDB(NO SQL not only sql) 非结构化

  • 存储对象，通过对象的属性来操作 有点类似与json数据

2.2 MySQL

MySQL 中文文档 | MySQL 中文网 (mysqlzh.com)

SQL语言(类似java语言) 开源 可集群

默认文件位置:C:\ProgramData\MySQL

也就是C:/ProgramData/MySQL

2.DDL

创建数据库：

CREATE DATABASE school:正常创建
CREATE DATABASE IF NOT EXISTS school：如果不存在school，就会创建一个新的school，否则也不会报错

删除数据库：

DROP DATABASE IF EXISTS school

2.1数据类型

数值

decimal函数 decimal（10，2）代表一共十位数，保留两位小数

字符串
char 和 varchar
区别：
1.char长度不可变 ，varchar长度可以变
比如说同样是指定长度8位，现在对应数据是4位，那char就会自动补成8位。
varchar则不会。取数据的时候，char类型的需要trim（）去掉空格，VARCHAR则不需要。
2.char的存取速度比varchar快得多。
长度固定，所以便于存储和查询。但是空间有所浪费。varchar则没有空间浪费。
varchar以空间效率为首位。
3.存储方式
char，一个英文字符占用1个字节，一个汉字占用2个字节。
varchar，一个英文字符和一个汉字都占2个字节。
4.两者的存储数据都是非Unicode字符数据

日期

对于datetime来说长度代表的是秒后面的小数点后面位数0-6

2.2字段属性

无符号数:不能为负值

填充零：不输入规定的字符长度，自动填充0

注释：

自动递增：可以设置递增的步长

默认：如果设置的字段的数据类型为varchar，呢么字段的默认值需要加上英文单引号 如 ‘男’

mysql在建表的时候int类型后的长度代表什么 是该列允许存储值的最大宽度吗 为什么我设置成int(1), 也一样能存10,100,1000呢.：

因为这个长度的数值代表的是最大显示宽度，并不是数据存储的范围位数

创建数据库表：

create table `表名`(
 `字段名` 字段类型（） 属性 索引 默认（default） 注释(comment),
)
PRIMARY KEY 	 AUTO_INCREMENT		

]

CREATE TABLE `student` (
  `学号` int(11) NOT NULL AUTO_INCREMENT,
  `登陆密码` varchar(20) NOT NULL,
  `姓名` varchar(10) NOT NULL,
  `性别` varchar(2) NOT NULL,
  `出生日期` date NOT NULL,
  `家庭住址` varchar(20) NOT NULL,
  `email` varchar(10) NOT NULL,
  PRIMARY KEY (`学号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
``

数据库表引擎类型：

INNODB 	MYISAM（锁完整张表，一个线程在更新第n行表的数据，这时候要想再更新第m行的数据，是不行的，必须等待这个线程结束。但她支持全文检索）





修改数据表：

```sql
-- 表重命名
ALTER TABLE student RENAME AS student1;
--增加表的字段
ALTER TABLE student1 ADD 年龄 INT(11);
--重命名字段名（后面的字段数据类型必须加上） 修改字段的约束
ALTER TABLE student1 CHANGE email emailq INT(11)
ALTER TABLE student1 MODIFY 年龄 VARCHAR(20) 

删除数据库表：

DROP TABLE student;

3. DML

主键：主键唯一且不能为空

子表外键就是引用了父表的主键，并通过此来获取父表的信息

3.1外键

MySQL有两种常用的引擎类型：MyISAM和InnoDB。目前只有InnoDB引擎类型支持外键约束。

MySql 外键约束 之CASCADE、SET NULL、RESTRICT、NO ACTION分析和作用 - yzuzhang - 博客园 (cnblogs.com)

cascade：级联

在父表上update/delete记录时，同步update/delete掉子表的匹配记录

InnoDB Foreign Key ibfk

不使用外键的原因：删除和改表的时候会非常麻烦，需要考虑到外键约束，所以这些东西都需要在应用层（java代码）去实现

如果出现”cannot add foreign key constraint”的情况，建议先检查一下，主表是否有主键以及从表关联字段是否为从表主键，然后，核对从表字段与主表关联字段的数据类型是否相同，其中是否有不符合的数据 (不好弄就先把数据备份，然后删完了试一下，如果可以，说明确实是存在不符合的数据问题)。 如果依然存在问题，那只好说一句MMP，然后找老司机解决一下。

insert into

delete from

update set

select from

3.2 增（INSERT）

mysql可以执行多行插入，Oracle和MongoDB不行

• INSERT INTO dept SET deptno=50,dname=“事业部”,loc=“旧金山”;

--原始写法
INSERT INTO `student1`(登陆密码,`姓名`) VALUES( 1234,'刘可');
--一个列添加多个值
INSERT INTO `student1`(`姓名`) VALUES( '刘宝'),('刘播');
--多行插入
INSERT INTO `student1`(`登陆密码`,`姓名`) VALUES(11,'刘aa'),(22,'刘bb');

3.3 删（DELETE）

• DELETE语句可以一次删除一张表的一条或者多条记录

• 格式：DELETE FROM 表名 WHERE 条件 , DELETE FROM 表名

  ex:删除编号是140的员工

  DELETE FROM emp WHERE empno = 140;

  delete from 表名与truncate 表名区别

  1.truncate清空数据表性能（速度）比delete快。

  2.truncate只能一次清空，不会影响事务，但自增归零

  TRUNCATE TABLE 是清空表数据，但是表还是会存在

3.4 改（UPDATE）

• UPDATE语句可以一次修改一条或者多条记录

• 格式：UPDATE 表名 SET 列1 = 值1, 列2 = 值2, …… WHERE 条件

  ex1：工龄满两年的员工每月工资上涨3%

  ​ UPDATE emp SET sal=sal+sal*0.03 WHERE DATEDIFF(NOW(),hiredate)/365>=2;

  ex2: 把30部门员工全部调往20 事务控制

  ​ UPDATE emp SET deptno=20 WHERE deptno=30;

  ex3：

  UPDATE `student1` SET `出生日期`=CURRENT_DATE
  

3.5 查（SELECT）

SELECT * FROM emp
SELECT ENAME,JOB FROM emp
SELECT ENAME,SAL*12 FROM emp -- 查询年薪
SELECT ENAME,SAL*12 AS 年薪 FROM emp -- 查询年薪加别名（带AS）
SELECT ENAME,SAL*12  年薪 FROM emp -- 查询年薪加别名(不带AS)
SELECT ENAME,SAL*12  `年 薪` FROM emp -- 查询年薪加别名(年和薪之间有空格)

SELECT NULL*2 -- 任何null参与运算其结果一定是NULL值

SELECT ENAME,SAL*12+ IFNULL(0,COMM) 总薪资  FROM emp  -- 薪资加绩效

SELECT ENAME,(SAL*12 + (CASE WHEN COMM IS NULL THEN 0 ELSE COMM END ) )总薪资  FROM emp  -- 薪资加绩效(2)

SELECT DEPTNO FROM emp
SELECT DISTINCT DEPTNO FROM emp -- 去重
SELECT DISTINCT DEPTNO , JOB FROM emp -- 组合去重

SELECT * FROM emp WHERE  deptno <> 10 -- 不等于10
SELECT * FROM emp WHERE  sal>800 AND sal<1000 -- 薪资大于800小于1000的
SELECT * FROM emp WHERE  sal BETWEEN 800 AND 1000 -- 包含800和1000
SELECT * FROM emp WHERE  sal=800 OR sal=1000 -- 等于800或1000
SELECT * FROM emp WHERE  sal IN (800,1000) -- 等于800或1000
SELECT * FROM emp WHERE  comm IS NULL -- 津贴为空
SELECT * FROM emp WHERE  comm IS NOT NULL -- 津贴为空
SELECT * FROM emp WHERE hiredate > '1981-02-20'

3.1 函数

聚合函数（重点）

总的来说就是三种：

count（*）和count（常量）和count（字段）

count（*）和count（1）没有区别

count（字段）统计的是除了null值的行

1.【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的 标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。 说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

SELECT SUM(sal) FROM emp
SELECT AVG(sal) FROM emp
SELECT MAX(sal) FROM emp
SELECT MIN(sal) FROM emp

条件函数

格式：

CASE exp
WHEN 表达式 THEN 语句
WHEN 表达式 THEN 语句
WHEN 表达式 THEN 语句
ELSE 语句
END

ex:根据工龄不同分为新员工 普通员工 老员工

SELECT ename,
CASE 
    WHEN DATEDIFF(NOW(), hiredate) / 365 <= 1  THEN '新员工'
    WHEN DATEDIFF(NOW(), hiredate) / 365 <= 3  THEN '普通员工'
    WHEN DATEDIFF(NOW(), hiredate) / 365 > 3   THEN '老员工'
END     
FROM emp;

常用的函数：

-- LOWER(str) UPPER(str) 大小写之间的转换
SELECT LOWER('AAA')
SELECT LOWER(ename) FROM emp
-- CONCAT(str1,str2,...) 字符串连接在一起
SELECT CONCAT('www.','baidu.','com')

分组和过滤

GROUP BY 和HAVING（和where功能一样，不同的就是一个在group by前，一个在后,HAVING 是对分组进行限制）

SELECT MAX(sal) FROM emp GROUP BY deptno -- 每组输出一个最大值，有几组输出几个
SELECT MAX(sal) FROM emp GROUP BY deptno,job -- 组合分组
SELECT COUNT(1),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000

3.2 where条件子句

3.3 模糊查询

LIKE :
%：通配符，任意个数的字符
_:代表一个字符
%:对%进行转义

SELECT ename  FROM emp WHERE ename LIKE '_S%' -- 第二个字母是S的结果

IN: a in (a1,a2,a3,a4…)

3.4 排序order by

默认升序 asc 降序desc

SELECT ename,sal,deptno FROM emp ORDER BY deptno ASC,sal DESC 
-- 先deptno升序排列，然后在每个depton中 sal降序排列

3.5 分页limit

分页：limit 百度图片往下拉，拉不完属于一个瀑布流

询分页我们一般都会传两个参数，例如：

select * from t_user limit 1,10
其实，如果我们只查询前几条数据的时候，分页参数只需要一个即可，比如我们查询t_user表的前五条数据，就可以这样写：

select * from t_user limit 5
它等价于：

select * from t_user limit 0,5

0,6 : 0,1,2,3,4,5
6,6 :6,7,8,9,10,11
12,6 :12,13,14,15,16,17
18,6 :18,19,20,21,22,23
(currentPage-1)*PageSize,pageSize

select * from table limit (currentPage-1)*pageSize,pageSize

3.6 联表查询 &&子查询

子查询的结果当成一张表

-- 只查到交集 inner join就是join 
SELECT e.empno,e.ename,d.dname  FROM emp e JOIN dept d ON e.deptno=d.deptno;
-- dept 表中的数据都会查出来 即使在emp 中没有匹配的值，显示为null
SELECT e.empno,e.ename,d.dname  FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
-- emp 表中的数据都会查出来 即使在dept 中没有匹配的值，显示为null
SELECT e.empno,e.ename,d.dname  FROM emp e LEFT JOIN  dept d ON e.deptno=d.deptno;

-- 1.每个部门平均薪水的等级 先求出每个部门的平均薪水

SELECT t1.DEPTNO,t1.avg_sal,t2.grade
FROM (SELECT DEPTNO,AVG(SAL) avg_sal FROM emp GROUP BY DEPTNO) t1 
JOIN salgrade t2 ON  t1.avg_sal BETWEEN t2.losal AND t2.hisal


-- 2.部门平均的薪水等级 先求出每个人的薪水等级
SELECT DEPTNO,AVG(grade)
FROM
(SELECT t1.ENAME,t1.SAL,t1.DEPTNO,t2.grade
FROM emp t1  JOIN
salgrade t2 ON t1.SAL BETWEEN t2.losal AND t2.hisal) t
GROUP BY DEPTNO

-- 3.哪些人是经理 一个经理有多个下属
SELECT DISTINCT t1.ENAME,t2.MGR
FROM emp t1 
JOIN emp t2 ON t1.EMPNO = t2.MGR

SELECT ENAME,EMPNO FROM emp WHERE EMPNO IN (SELECT DISTINCT MGR FROM emp)

--  CASE WHEN 例子

SELECT * FROM emp

SELECT ename,
CASE 
    WHEN DATEDIFF(NOW(), hiredate) / 365 <= 1  THEN '新员工'
    WHEN DATEDIFF(NOW(), hiredate) / 365 <= 3  THEN '普通员工'
    WHEN DATEDIFF(NOW(), hiredate) / 365 > 3   THEN '老员工'
END  员工   
FROM emp;


SELECT * FROM emp

SELECT ENAME ,
CASE 
	WHEN JOB ='CLERK' THEN
		'服务员'
	WHEN JOB ='SALESMAN' THEN
		 '销售'
	ELSE
	   '其他'	
END 职位
FROM emp


SELECT * FROM salgrade

-- 4.不用组函数求最高薪水

SELECT SAL 
FROM emp
WHERE SAL NOT IN (
SELECT  t1.SAL
FROM emp t1 JOIN emp t2 
ON t1.SAL< t2.SAL)

-- 5.平均薪水最高的部门编号与名称

SELECT *
FROM
(SELECT AVG(SAL) avg_sal,DEPTNO FROM emp GROUP BY DEPTNO ORDER BY avg_sal DESC LIMIT 1) t1 
JOIN (SELECT * FROM dept) t2 ON t1.DEPTNO=t2.DEPTNO 

-- 用视图简化查询
CREATE VIEW v1 AS SELECT AVG(SAL) avg_sal,DEPTNO FROM emp GROUP BY DEPTNO ORDER BY avg_sal DESC LIMIT 1

SELECT *
FROM
v1 t1 
JOIN (SELECT * FROM dept) t2 ON t1.DEPTNO=t2.DEPTNO 


-- 6. 求平均薪水级别最低的部门的部门名称 薪水最低则等级最低啊沙雕马士兵

SELECT t1.avg_sal,t1.DEPTNO,t3.dname,t2.grade
FROM (SELECT AVG(SAL) avg_sal,DEPTNO FROM emp GROUP BY DEPTNO ORDER BY avg_sal limit 1 ) t1 
JOIN salgrade t2  ON t1.avg_sal BETWEEN t2.losal and t2.hisal
JOIN dept t3 ON t1.DEPTNO=t3.DEPTNO 

-- 7.比普通员工的最高薪水还要高的的经理人的名称

SELECT  EMPNO,ENAME,SAL
FROM emp
WHERE EMPNO  IN (SELECT DISTINCT MGR  FROM emp WHERE MGR IS NOT NULL) 
AND SAL>
(SELECT MAX(SAL) FROM emp
WHERE EMPNO NOT IN (SELECT DISTINCT MGR  FROM emp WHERE MGR IS NOT NULL) )


-- 8.多对多关系面试题 S(SNO,SNAME)(学号，姓名)  C(CNO,CNAME, CTEACHER)(课号，课名，教师)  SC(SNO,CNO,SCGRADE)(学号，课号，成绩)

-- 1.找出所有没有选过黎明老师的所有学生的名字

SELECT sname FROM S WHERE sname NOT IN (
SELECT SNAME 
FROM  S 
JOIN SC 
ON S.SNO=SC.SNO 
JOIN  C 
ON SC.CNO=C.CNO 
WHERE C.CTEACHERE = '黎明'
)


-- 2.列出2门以上（包括两门）不及格学生姓名及平均成绩
SELECT 
SNAME,
avg_scgrade 
FROM S 
JOIN (SELECT AVG(SCGRADE) avg_scgrade,SNO FROM  SC WHERE SCGRADE < 60  GROUP BY SNO HAVING COUNT(*) >= 2 ) SC
ON S.SNO=SC.SNO  --不及格那几门的平均成绩



SELECT 
SNAME,
AVG(SCGRADE)
FROM S 
JOIN (SELECT SNO FROM  SC WHERE SCGRADE < 60  GROUP BY SNO HAVING COUNT(*) >= 2 ) SC1
ON S.SNO=SC1.SNO  
JOIN SC 
ON S.SNO=SC.SNO 

----
SELECT SNAME 
FROM S 
WHERE SNO IN (SELECT SNO FROM  SC WHERE SCGRADE < 60  GROUP BY SNO HAVING COUNT(*) >= 2 ) 



------------- 既学过1号课程又学过2号课程的学生
SC(SNO,CNO,SCGRADE)(学号，课号，成绩)

SELECT t1.SNO ,S.SNAME
(SELECT SNO FROM SC WHERE CNO='01') t1 
JOIN (SELECT SNO FROM SC WHERE CNO='02' )
ON t1.SNO = t2.SNO 
JOIN S 
ON t2.SNO=S.SNO

SELECT S.SNAME
FROM  S 
WHERE SNO IN 
(
SELECT SNO FROM SC WHERE CNO='01' AND SNO IN 
(SELECT SNO FROM SC WHERE CNO='02' )

总结：
select xx from 表名
where xxx and xx 对数据进行过滤
group by 分组
having 对分组进行限制
order by 排序
取数据，过滤 ，分组，分组限制，排序

SELECT AVG(sal) FROM emp
WHERE sal>1200
GROUP BY deptno
HAVING avg(sal)>1500
ORDER BY AVG(sal) DESC;

4.索引

CREATE INDEX idx_email ON student(email)

CREATE INDEX idx_email  USING HASH ON student(email) -- 指定hash，但是没用还是默认用BTree 也有复合索引 ，name

Mysql 索引用表面写的是 B Tree 实际底层用的还是B+Tree
数据结构：B+tree 和 Hash
INNODB只支持Btree
B+Tree：右侧节点放大于等于
Btree（B-Tree）：右侧节点只有大于，BTree是B+Tree的扩展

存储引擎：不同的数据文件在物理磁盘上不同的存储形式

查看存储引擎
mysql -uroot -pAdmin@123$
show engines;
分号一定要加

InnoDB:索引方法用B+Tree 自适应Hash,人为不可干预
MyISAM: 索引方法用B+Tree 自适应Hash,人为不可干预
MEMORY: 索引方法用Hash
use 数据库名；
show index from 表名；

磁盘预读，预读以页为单位（页的大小为4k）
主存和磁盘以页为单位交换数据。

1.索引是帮助MySQL高效获取数据的数据结构。
2.索引存储在文件系统中
3.索引文件存储形式与存储引擎有关
4.索引文件的结构：
hash
二叉树
B+树
B树

最终MySQL选择了B+树，为什么？

### 加密

```sql
--这个加密后的结果是一堆乱码  abc123456：加密内容  HelloWorld：密钥
SELECT AES_ENCRYPT("abc123456","HelloWorld");
--需要将乱码转化成十六进制
SELECT HEX(AES_ENCRYPT("abc123456","HelloWorld"));

--解密的话就是一个逆过程
SELECT AES_DECRYPT(UNHEX("F943968B28B2D93E2DC48CD72014FE1A"),"HelloWorld");

数据库级别的md5加密

--数据类型设置成varchar
UPDATE student1 SET 登陆密码=MD5(登陆密码)
--应当在插入的时候加密
INSERT INTO student1 SET 登陆密码=MD5(34567)
--校验就是将用户输入的密码进行md5加密后，再比对值
SELECT * FROM student1 WHERE `登陆密码`=MD5(34567)