DML和DQL 总结
一：MySql的存储引擎
问题的引入：
由于不同用户对数据的容量，访问速度，数据安全性有着不同的要求。
为了满足不同用户的需求，mysql数据库采用多种存储引擎来进行数据的存储！
1.1：查询mysql数据库中所有的存储引擎
show engines；
1.2：mysql数据库常用的存储引擎
1.3：查询默认的存储引擎
1.4：修改默认的存储引擎
找到安装目录的my.ini 文件 修改
default-storage-engine=引擎名称
之后重启mysql服务生效
1.5：mysql的数据文件
01.数据文件的存放位置
mysql安装目录下的data文件夹中
02.不同引擎的文件类型
InnoDB类型的文件
*.frm ：表结构定义文件,存放表的元数据，与存储引擎无关！MyISAM也有！
*.ibd : 数据文件，存放表中的数据！
MyISAM类型的文件
*.frm ：表结构定义文件
*.MYD ：数据文件
*.MYI ：索引文件
二：使用DML插入数据
新增数据
INSERT INTO student(id,`name`,sex) VALUES(10,'小白',DEFAULT);
INSERT INTO student(`name`,sex) VALUES('小黑',DEFAULT);
INSERT INTO student(`name`,sex) VALUES('小黑2',DEFAULT);
2.1：插入一条数据
INSERT INTO student(`name`,sex) VALUES('小黑1',DEFAULT);
INSERT INTO student(`name`,sex) VALUES('小黑2',DEFAULT);
INSERT INTO student(`name`,sex) VALUES('小黑3',DEFAULT);
INSERT INTO student(`name`,sex) VALUES('小黑4',DEFAULT);
2.2：同时增加多条数据
INSERT INTO student(`name`,sex) VALUES('小黑1',DEFAULT),('小黑2',DEFAULT),('小黑3',DEFAULT),('小黑4',DEFAULT);
注意点：
1.如果我们手动的更改了自动增长的列，那么下次自动增长的起点就变了
2.在使用insert语句的时候，如果没有书写表中的字段，那么values中的值必须和表中定义的字段顺序一致
3.建议在新增数据的时候 书写 字段名称
2.3：将查询结果插入到新表中
Create table 新表名(select 字段1,字段2.... from 旧表);
在执行查询语句的同时 会创建一张新表，字段就是查询出的字段！
前提： 新表不存在！ 若存在会报错！
三：使用DML更新数据
Update 表名 set 字段1=更新值,字段2=更新值 [where 条件];
注意点：
01.set之后可以跟多个字段，多个字段之间使用逗号隔开
02.where后面是更新那些数据的条件，如果不加条件，会对整个表的数据进行修改
四：使用DML删除数据
Delete from 表名 [where 条件];
Truncate table 表名;
delete 和 truncate的区别
01.delete
begin （开启事务）
select * from grade;(查询年级表中所有的数据)
delete from grade; (删除年级表中所有的数据)
select * from grade;(查询年级表中所有的数据，没有数据)
rollback; （事务回滚）
select * from grade;(查询年级表中所有的数据,删除的数据恢复)
commit （提交事务)
02.truncate
begin （开启事务）
select * from grade;(查询年级表中所有的数据)
truncate table grade; (删除年级表中所有的数据)
select * from grade;(查询年级表中所有的数据，没有数据)
rollback; （事务回滚）
select * from grade;(查询年级表中所有的数据，没有数据)
commit （提交事务)
区别：
01.delete后面可以拼接where条件，删除指定的行！
truncate只能删除表中所有的数据！不能有where！
02.delete可以回滚，数据库可以恢复！
truncate 不能事务混滚，数据不可以恢复！
03.truncate执行效率高！
04.都是删除数据，表的结构，索引，约束等属性不会删除！
五：DQL语句
sql素材
语法：
Select <列名|表达式|函数|常量>
From 表名
[Where 条件]
[group by 分组依据]
[having 分组条件]
[order by 排序（desc|asc）]
[limit 分页数据]
Sql语句的执行顺序
01.from
02.where
03.group by
04.having
05.select
06.order by
07.limit
5.1：查询所有的数据行和列
Select * from 表名; *代表所有的列,性能低
5.2：查询部分列
Select 字段1,字段2... from 表名;
5.3：查询中使用别名
SELECT gradeID AS 年级编号,gradeName '年级 名称' FROM grade;
格式
01. 列名 AS 别名
02. 列名 别名
03. 如果别名中有特殊符号，必须把 别名用 单引号 引起来！
5.4：查询空值
使用is null 的时候 要确保 查询的列 可以为空！
null:
01.标识 空值
02.不是0，也不是空串""
03.只能出现在定义 允许为null的字段
04.只能使用is null 或者is not null 进行比较！
SELECT * FROM student
WHERE loginPwd IS NULL
5.5：查询中使用常量列
如果需要在查询的时候，便于我们的统计和计算！可以使用常量列
Select name as ‘学生姓名’, ‘都是好学生’ as ‘备注信息’ from student;
模糊查询 % _ 的区别
5.6：常用的聚合函数
count() 查询某个字段的行数
max()查询某个字段的最大值
min()查询某个字段的最小值
sum()查询某个字段的和
avg()查询某个字段的平均值
-- 查询成绩表的总成绩
SELECT SUM(studentResult) FROM result;
-- 查询成绩的平均值
SELECT AVG(studentResult) FROM result;
-- 查询成绩的最高分
SELECT MAX(studentResult) FROM result;
-- 查询成绩的最低分
SELECT MIN(studentResult) FROM result;
-- 查询有成绩的总数
SELECT COUNT(studentResult) FROM result;
5.7：常用的字符串函数
1. concat(...) 连接字符串
SELECT CONCAT('a','d','m','i','n') AS '拼接的结果';
2.insert(str,begin,length,newStr) 替换字符串
str:原始的字符串
begin：从原始字符串的哪个位置开始 在数据库中下标从1开始
length：需要替换的长度
newStr：替换的字符串
SELECT INSERT('admin',2,2,'ha');
3.substring(str,begin,length) 截取字符串
str:需要截取字符串
begin：开始截取的位置
length：截取的长度
SELECT SUBSTRING('admin',2,3);
4.lower(str) 转换成小写
SELECT LOWER('ADMIN');
5.upper(str) 转换成大写
SELECT UPPER('admin');
5.8：常用的日期时间函数
01.获取年月日
SELECT CURDATE();
02.获取时分秒
SELECT CURTIME();
03.获取年月日 时分秒
SELECT NOW();
04.获取年
SELECT YEAR(CURDATE());
SELECT YEAR(NOW());
05.获取小时
SELECT HOUR(NOW());
SELECT HOUR(CURTIME());
06.获取分钟
SELECT MINUTE(NOW());
SELECT MINUTE(CURTIME());
07.获取当前日期是本年的第几周
SELECT WEEK(NOW());
SELECT WEEK(CURDATE());
08.获取两个日期之间的天数 第一个参数 减去 第二个参数
SELECT DATEDIFF(NOW(),'2017-09-01');
SELECT DATEDIFF('2017-09-01',NOW());
09.获取给定日期之后的日期 第一个参数必须是日期格式
SELECT ADDDATE(NOW(),-34);
5.9：常用的数学函数
01.天花板函数
SELECT CEIL(3.0) FROM DUAL;
SELECT CEIL(3.1) FROM DUAL;
SELECT FLOOR(3.9) FROM DUAL;
02. 返回0-1之间的随机数
SELECT RAND() FROM DUAL;
03.四舍五入
SELECT ROUND(2.4) FROM DUAL;
5.10：dual伪表
dual 我们称之为 伪表！
在mysql中是一个摆设
select 9*9;
select 9*9 from dual;
select * from dual; 报错
oracle中 必须使用 from dual;
select 9*9 from dual; 正确的
select 9*9 ; 错误
dual是一个只有一行一列的表！
只能查询！ 不能对 dual进行增删改！
5.11：和并列 分组
-- 面试题 和并列
-- 01.创建一个表 test 有字段（name,subject,sorce）
CREATE TABLE IF NOT EXISTS `exam`(
`name` VARCHAR(20) NOT NULL,
`subject` VARCHAR(20) NOT NULL,
sorce DOUBLE NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 02.一次性插入5条测试数据
INSERT INTO exam(`name`,`subject`,sorce)
VALUES
('小黑1','java',80),
('小黑1','c#',88),
('小黑2','c#',90),
('小黑2','java',99),
('小黑3','java',100,
('小黑3','c#',100);
-- 03.根据name进行分组显示 成绩和科目名称（显示在一列）
SELECT `name` AS '姓名',GROUP_CONCAT(`subject`,':',sorce) AS '合并列'
FROM exam
GROUP BY `name`;
5.12：order by , limit
# order by 和 limit
01.查询所有年级编号为1的学员信息
SELECT * FROM student WHERE gradeid=1
02.查询所有年级编号为1的学员信息 并按照学号进行 降序排列
SELECT * FROM student
WHERE gradeid=1
ORDER BY studentNo DESC # 默认是 asc 升序排列
03.查询所有年级编号为1的学员信息 并按照出生日期进行升序排列,学号进行降序排列
SELECT * FROM student
WHERE gradeid=1
ORDER BY BornDate ASC ,studentNo DESC;
04.在03例子的基础上，查询年龄最大的5名同学
SELECT * FROM student
WHERE gradeid=1
ORDER BY BornDate ASC ,studentNo DESC
LIMIT 0,5 # 0代表的是从第几条数据开始查询 5 显示几条数据
注意点：如果对多列进行排序时，每列之间使用逗号隔开，然后在列后面写上排序的依据！
六：子查询
-- 子查询
-- 把一个查询的结果 当成另一个查询的 字段，条件或者表！
SELECT studentName FROM student
-- 只能通过student 表 查询出 学生对应的 年级名称
-- 01. 先查询出 学生 武松 对应的 年级编号
SELECT GradeID FROM student WHERE studentName='武松'
-- 02.根据年级编号 取 年级名称
SELECT gradeName FROM grade WHERE GradeID=???
SELECT gradeName FROM grade WHERE GradeID
=(SELECT GradeID FROM student WHERE studentName='武松')
子查询注意事项
01.任何允许使用表达式的地方都可以使用子查询
02.嵌套在父查询SELECT语句的子查询可包括
SELECT子句
FROM子句
WHERE子句
GROUP BY子句
HAVING子句
select （子查询） from 表名
子查询的结果必须是单行单列！
03.只出现在子查询中而没有出现在父查询中的列不能包含在输出列中
04.出现在父查询中的where之后是作为一个条件存在的
05.出现在父查询中的from之后是作为临时表存在
select * from (子查询)
子查询在作为临时表存在的时候，必须赋予别名
SELECT * FROM (SELECT * FROM student WHERE gradeid=1) AS temp
七：IN和NOT IN子查询
-- 查询年级编号是1或者2 的 所有学生列表
SELECT * FROM student WHERE gradeId IN(1,2)
-- 查询 年级名称是 大一或者大二的所有学生信息
-- 学生表 中没有 年级名称 但是有年级编号
-- 01.根据 年级名称 查询出 编号
SELECT gradeID FROM grade WHERE gradeName IN('大一','大二');
-- 02.再根据id查询学生信息
SELECT * FROM student WHERE
gradeID
IN (SELECT gradeID FROM grade WHERE gradeName IN('大一','大二'))
-- 查询参加 最近一次 高等数学-1 考试成绩的学生的最高分和最低分
-- 01. 发现成绩表中 没有 科目名称 只有编号！根据名称取编号
SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1'
-- 02.查询最近一次 高等数学-1 考试的时间
SELECT MAX(ExamDate) FROM result
WHERE
SubjectNo=(SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1')
-- 所有最近考试的成绩
SELECT * FROM result
WHERE ExamDate='2013-11-11 16:00:00'
-- 03.开始获取最高分和 最低分
SELECT MAX(studentResult) AS 最高分,
MIN(studentResult) AS 最低分
FROM result
WHERE SubjectNo=(SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1')
AND ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE
SubjectNo=(SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1'))
SELECT
MAX(studentresult) '最高分',
(SELECT studentName FROM student s,result r
WHERE s.studentNo=r.studentNo
AND examdate='2017-11-11 16:00:00'
AND subjectNo=1
ORDER BY StudentResult DESC
LIMIT 0,1) AS '最高分姓名',
MIN(studentresult) '最低分',
(SELECT studentName FROM student s,result r
WHERE s.studentNo=r.studentNo
AND examdate='2017-11-11 16:00:00'
AND subjectNo=1
ORDER BY StudentResult
LIMIT 0,1) AS '最低分姓名'
FROM result r,student s WHERE subjectNo=1
AND examdate='2017-11-11 16:00:00'
AND s.studentNo=r.studentNo
-- 查询 高等数学-1 考试成绩是 60 分的 学生信息
-- 01.根据 科目名称 获取 科目编号
SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
-- 02.根据编号 查询 所有的学生编号
SELECT studentNo FROM result
WHERE SubjectNo=(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
AND StudentResult=60; -- 成绩=60
-- 03.查询学生信息
SELECT * FROM student
WHERE studentNo IN
(SELECT studentNo FROM result
WHERE SubjectNo=(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
AND StudentResult=60)
-- 使用in替换 等于（=）的子查询语句！
-- in后面的子查询可以返回多条记录！
-- not in :不在某个范围之内
-- 查询未参加 “高等数学-1” 课程最近一次考试的在读学生名单
-- 01.根据 科目名称 获取 科目编号
SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1'
-- 02.获取最近一次考试时间
SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=
(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
-- 03.查询没参加的学生编号
SELECT studentNo,StudentName FROM student
WHERE studentNo NOT IN
(
SELECT StudentNo FROM result
WHERE SubjectNo=
(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
AND ExamDate=
(SELECT MAX(ExamDate) FROM result
WHERE SubjectNo=
(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1'))
)
