常见问题
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)