MySQL学习

Mysql学习

【狂神】Mysql学习（代码）

-- SELECT * FROM student

-- SELECT s_sex,COUNT(s_sex) as 人数 FROM student GROUP BY s_sex

-- SELECT * FROM student WHERE s_name LIKE '%风%'

-- SELECT c_id,COUNT(s_id)


-- SHOW DATABASES

-- CREATE DATABASE kuang_a

--  目标：创建一个school数据库
-- 创建学生表（列，字段）使用sOL创建
-- 学号int 登录密码varchar（20）姓名，性别varchar（2），出生日期（datatime），家庭住址，email

-- 注意点，使用英文（），表的名称和字段尽量使用··括起来
-- AUTO INCREMENT 自增
-- 字符串使用单引号括起来！
-- 所有的语句后面加，（英文的），最后一个不用加


USE kuang_a;
CREATE TABLE IF NOT EXISTS `student1`(
        `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
        `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
        `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
        `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
        `birthday` DATETIME DEFAULT NULL COMMENT '生日',
        `address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
        `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
        PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 查看mysql所支持的引擎类型（表类型）
SHOW ENGINES;

--  查看创建数据库的语句
SHOW CREATE DATABASE kuang_a;

-- 查看student数据表的定义语句
SHOW CREATE TABLE student;

-- 显示结构
DESC student;


-- 关于数据库引擎
/*
    INNODB 默认使用~
    MYISAM 早些年使用的
*/


-- 修改表 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1;

-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student ADD agee INT(11);

-- 修改表的字段（重命名，约束修改）
/*
最终结论：
    change用来字段重命名，可以修改字段类型和约束；
    modify不用来字段重命名，只能修改字段类型和约束；
*/
ALTER TABLE student MODIFY agee VARCHAR(11) -- 修改约束
ALTER TABLE student CHANGE agee age1 INT(1) -- 字段重命名

-- 删除表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE student DROP age1;

--  删除表
DROP TABLE IF EXISTS student1;

-- 注意事项：所有的创建和删除操作尽量加上判断（IF EXISTS），以免报错
-- 所有字段名称 使用``包裹起来
-- SQL关键字大小写不敏感 
-- 所有的符号用英文！


-- 外键

CREATE TABLE `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename` INT(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

DROP TABLE IF EXISTS student;


-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 第一步、定义外键key
-- 第二步、给这个外键添加约束（执行引用）
USE kuang_a;
CREATE TABLE IF NOT EXISTS `student`(
        `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
        `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
        `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
        `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
        `birthday` DATETIME DEFAULT NULL COMMENT '生日',
        `gradeid` INT(10) NOT NULL COMMENT '学生的年纪',
        `address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
        `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
        PRIMARY KEY(`id`),
        KEY `FK_gradeid` (`gradeid`),
        CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 主表：被引用的表
-- 从表：引用别人的表
-- 这个案例中，grade是主表，student是从表；

-- KEY `外键约束名`（列如：FK_哪个字段为约束）
--constraint 外键（形如：FK_从表_主表） foreign key (从表外键字段) references 引用表/主表(引用列/主表主键);

-- 注意 : 删除具有主外键关系的表时 , 要先删子表 , 后删主表


-- 创建表的时候没有外键关系
ALTER TABLE  `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`)     REFERENCES `grade`(`gradeid`)
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES `哪个表`(哪个字段);

-- 以上操作都是物理外键，数据库级别的外键，不建议使用！

/*
最佳实践：

        数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
        我们想使用多张表的数据，想使用外键（程序去实现）
*/


SHOW ENGINE INNODB STATUS;


-- 插入数据
-- INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
ALTER TABLE grade MODIFY gradename VARCHAR(5);

-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO grade(gradename) VALUES('大三');

-- 一般写插入语句，我们一定要数据和字段一一对应！


-- 插入多个字段
INSERT INTO grade(gradename) VALUES('大一'),('大二');


INSERT INTO student(name,pwd,sex) VALUES('刘德华','123','男');
INSERT INTO student(name,pwd,sex) VALUES('张翼德','332244','男');
INSERT INTO student(name,pwd,sex) VALUES('花木兰','2211aa','女');
INSERT INTO student(name,pwd,sex) VALUES('赵云','123321','男');


-- UPDATE

-- 修改学院的名称
UPDATE student SET `name`='郭富城' WHERE id=1;

UPDATE student SET `name`='狂神';

UPDATE student SET `name`= '张学友',email='321313@qq.com' WHERE id = 2;

UPDATE student SET birthday=CURRENT_TIME WHERE `name`='狂神' AND '女';

-- DELETE

-- 删除数据
DELETE FROM student WHERE id=1;


DELETE FROM student -- 不会影响自增
TRUNCATE TABLE student -- 自增会归零

-- SELECT

CREATE DATABASE if NOT EXISTS `school`;

CREATE TABLE `grade`(
    `GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号', 
    `GradeName` VARCHAR(50) NOT NULL COMMENT '年纪名称',
    PRIMARY KEY (`GradeID`)
) ENGINE = INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO grade(GradeID,GradeName) VALUES(1,'大一');
INSERT INTO grade(GradeID,GradeName) VALUES(2,'大二');
INSERT INTO grade(GradeID,GradeName) VALUES(3,'大三');
INSERT INTO grade(GradeID,GradeName) VALUES(4,'大四');
INSERT INTO grade(GradeID,GradeName) VALUES(5,'预科班');

CREATE TABLE `result`(
    `StudentNo` INT(4) NOT NULL COMMENT '学号',
    `SubjectNo` INT(4) NOT NULL COMMENT '课程编号',
    `ExamDate` DATETIME NOT NULL COMMENT '考试时间',
    `StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
    KEY `SubjectNo` (`subjectNo`)
)ENGINE=INNODB DEFAULT CHARSET = utf8;


insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',68),
(1001,6,'2013-11-14 16:00:00',78),
(1002,7,'2013-11-14 16:00:00',88);


truncate table result;

DROP TABLE IF EXISTS student;
CREATE TABLE student(
    `StudentNo` INT(4) NOT NULL COMMENT '学号' ,
    `Loginpwd` VARCHAR(20) DEFAULT NULL COMMENT '密码',
    `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `Sex` TINYINT DEFAULT NULL COMMENT '性别，0/1',
    `Gradeid` INT(11) DEFAULT NULL COMMENT '年纪编号',
    `Phone` VARCHAR(50) NOT NULL COMMENT '联系电话，不允许为空', 
    `Address` VARCHAR(255) NOT NULL COMMENT '地址，不允许为空', 
    `BornDate` DATETIME DEFAULT NULL COMMENT '出生时间', 
    `Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,不允许为空',
    `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号', 
    PRIMARY KEY (`StudentNO`),  -- 主键
    UNIQUE KEY `IdentityCard` (`IdentityCard`), -- 唯一索引
    KEY `Email` (`Email`)        -- index索引
) ENGINE=INNODB DEFAULT CHARSET=utf8;


insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456434334011234'),
(1001,'123456','郭德',0,2,'13832343234','北京朝阳','1980-1-1','text123@qq.com','123456194001011234'),
(1002,'123456','李振',0,2,'13854325234','北京朝阳','1980-1-1','text123@qq.com','123456123001011234'),
(1003,'123456','刘强',0,2,'12130021234','北京朝阳','1980-1-1','text123@qq.com','123456193141011234'),
(1004,'123456','马云',0,2,'13832131234','北京朝阳','1980-1-1','text123@qq.com','123454332001011234'),
(1005,'123456','赵青',0,2,'13545441234','北京朝阳','1980-1-1','text123@qq.com','123456142101011234'),
(1006,'123456','赵强',1,3,'13805445222','广东深圳','1990-1-1','text111@qq.com','123132131301011233');

TRUNCATE TABLE student;


CREATE TABLE `subject`(
    `SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
    `GradeID` INT(4) DEFAULT NULL COMMENT '年纪编号',
    PRIMARY KEY(`SubjectNo`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);


/*
原子性（A）：要么全成功，要么全失败！
一致性（C）：SQL执行前后的数据完整性一致！
持久性（D）：事务一旦提交就不可逆，不受外部因素影响（如断电之类的）！
隔离性（I）：并发事务时，不能被其它事务的操作数据所干扰，业务之间要相互隔离

脏读：指一个事务读取了另外一个事务未提交的数据。（读取未提交）
{A事务正在访问数据，并且对数据进行了修改，但是没有提交。同时，B事务也访问了这个数据，然后使用了这个数据。}

不可重复读：在一个事务内读取表的某一行数据，多次读取结果不同（多次读）

{A事务多次读取一个数据，A事务没有提交前，B事务也在访问这个数据。那么，在A事务两次读取之间，由于B事务的修改，那么A事务两次读取到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的。}


虚读（幻读）：指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致（读取插入）
{A事务对x表中数据进行修改，这种修改涉及到表中的全部数据。同时，B事务对x表插入了一行新数据。后面A事务再次访问x表发现其中还没有修改的数据行。}

*/

SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student;

--  查询一下有哪些同学参加了考试，成绩
SELECT * FROM result; -- 查询全部的考试成绩
SELECT StudentNo FROM result; -- 查询有哪些同学参加了考试
SELECT DISTINCT StudentNo FROM result; -- 发现重复数据，去重

SELECT VERSION() -- 查询系统版本
SELECT 100*3-22 计算结果 -- 用来计算
SELECT @@auto_increment_increment -- 查询自增的步长


-- 给结果加分
SELECT StudentNo,StudentResult+1 AS '提分后' FROM result;

-- ============================  WHERE =============================

-- 查询考试成绩在95~100分之间
SELECT studentNo, StudentResult FROM result 
where studentresult>=95 AND studentresult<=100;

SELECT studentNo, StudentResult FROM result 
where studentresult>=95 && studentresult<=100;

-- 模糊查询（区间）
SELECT studentNo, StudentResult FROM result 
where studentresult between 95 and 100;

-- 除了1000号学生之外的同学的成绩
SELECT studentNo, StudentResult FROM result 
where studentno !=1000;

SELECT studentNo, StudentResult FROM result 
where not studentno = 1000;

-- ================= 模糊查询 ==================

-- like结合%（代表0到任意个字符）（一个字符）
select studentno,studentname from student
where studentname like '刘%';

-- 查询姓刘的同学，名字后面只有一个字的
select studentno,studentname from student
where studentname like '刘_';

-- 查询姓刘的同学，名字后面只有两个字的
select studentno,studentname from student
where studentname like '刘__';

-- 查询名字中间有嘉字的同学%嘉%
select studentno,studentname from student
where studentname like '%嘉%';


-- ========== in （具体的一个或者多个值）=========

-- 查询学号为1000,1001,1002的学生姓名
select studentno,studentname from student
where studentno in(1001,1002);


-- 查询地址在北京,南京,河南洛阳的学生
select studentno,studentname from student
where address in('北京朝阳');


-- ======= null nut null =============

-- 查询地址为空的学生null ''
select studentno,studentname from student
where address='' or address is null;

-- 查询有出生日期的同学   不为空
select studentno,studentname from student
where borndate is not null;

-- 查询没有出生日期的同学  为空
select studentno,studentname from student
where borndate is null;


-- ============ 联表查询join =============

-- 查询参加了考试的同学（学号，姓名，科目标号，分数）
select * from student
select * from result


/*
思路：
1、分析需求，分析查询的字段来自哪些表，（连接查询）
2、确定使用哪种连接查询？7种
确定交叉点（这两个表中，哪个数据是相同的）
判断条件：学生表中的studentno = 成绩表 studentno
*/


-- join（连接的表） on(判断条件) 连接查询
-- where 等值查询
-- 他们两个结果没有区别
/*
-- join on 连接查询
-- where 等值查询
-- 他们两个结果没有区别
在多表查询时，ON和where都表示筛选条件，on先执行，where后执行。
区别：
外连接时，on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
而where条件是在临时表生成好后，再对临时表进行过滤的条件。

*/


-- Inner-join的 where和on可以近似看作等效的

-- 参考：https://blog.csdn.net/lu0422/article/details/78892497


select s.studentno,studentname,subjectno,studentresult
from student s
inner join result r
where s.studentno = r.studentno;

select s.studentno,studentname,subjectno,studentresult
from student s
inner join result r
ON s.studentno = r.studentno;

-- Right JOIN

select s.studentno,studentname,subjectno,studentresult
from student s
right join result r
ON s.studentno = r.studentno;


-- 查询缺考的同学
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
ON s.studentno = r.studentno
where studentresult is null


-- 查询了参加了考试的同学的信息，学号，学生姓名，科目名，分数。
-- 来自 studnet result subject 

select s.studentno,studentname,subjectname,studentresult
from student s
right join result r
on r.studentno = s.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno


-- 要查询那些数据，
-- 从那几个表中查询 
-- 假设存在多张表格查询，先查询两张表格，然后，慢慢增加

-- 练习题

-- 查询学员所属的年级
-- 要用到 student，grade
select studentno as '学号', studentname as '姓名', gradename as '年级'
from student 
left join grade
on student.gradeid = grade.GradeID


-- 查询科目所属的年级
select subjectname as '科目名称', gradename as '年级'
from `subject` s
LEFT JOIN grade g
on s.gradeid = g.gradeid

-- 查询了参加数据结构考试的同学信息：学号，学生姓名，科目名，分数）, 
-- 使用student，result, subject
select s.studentno as '学号', studentname as '学生姓名', subjectname as '科目名称',studentresult as '考试分数'
from student s
RIGHT JOIN result r
on s.studentno = r.studentno
left JOIN `subject` sub
on r.subjectno = sub.subjectno
where sub.subjectno in(13,14,15,16)


-- =========== 自连接 ============


CREATE TABLE `category` (
    `categoryid` INT(10) UNSIGNED NOT NULL auto_increment COMMENT '主题ID',
    `pid` INT(10) NOT NULL COMMENT '父ID',
    `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
    PRIMARY KEY(categoryid)
) ENGINE=INNODB auto_increment=9 DEFAULT CHARSET = utf8;

INSERT INTO category(categoryid,pid,categoryName) VALUES 
(2,1,'信息技术'),
(3,1,'软件开发'),
(4,3,'数据库'),
(5,1,'美术设计'),
(6,3,'web开发'),
(7,5,'PS技术'),
(8,2,'办公信息');

-- 自连接: 数据表与自身进行连接


-- 查询父子信息
select a.categoryname as '父栏目', b.categoryname as '子栏目'
from category a,category b
where a.categoryid = b.pid

-- =========== 分页和排序 =============

-- 排序：升序 asc 降序 desc

-- 查询了参加考试的同学信息：学号，学生姓名，科目名，分数）, 成绩降序排
-- 使用student，result, subject
select s.studentno as '学号', studentname as '学生姓名', subjectname as '科目名称',studentresult as '考试分数'
from student s
RIGHT JOIN result r
on s.studentno = r.studentno
left JOIN `subject` sub
on r.subjectno = sub.subjectno
order by studentresult desc 


-- 分页

/*
为什么要分页？
    缓解数据库压力，给人的体验更好，瀑布流
*/

-- 分页每一页只显示5条数据。；limit 是排在最后的关键字
-- limit 0,5 1~5
-- limit 1,5 2~6
-- limit 6,5 从第6个开始，显示5个数据
select s.studentno as '学号', studentname as '学生姓名', subjectname as '科目名称',studentresult as '考试分数'
from student s
RIGHT JOIN result r
on s.studentno = r.studentno
left JOIN `subject` sub
on r.subjectno = sub.subjectno
order by studentresult desc 
limit 5


-- 思考：查询JAVA第一学年课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
-- 需求表，student，result，subject

select s.studentno, studentname,subjectname,studentresult
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where studentresult>80 and sub.subjectno=5
order by studentresult desc
limit 0,10;

-- ========== 子查询 ============

/*
什么是子查询?
    在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
    嵌套查询可由多个子查询组成,求解的方式是由里及外;
    子查询返回的结果一般都是集合,故而建议使用IN关键字;

*/

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列

-- 方式一：使用连接查询
select studentno,sub.subjectname,studentresult
from result r
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult desc


-- 方式二：使用子查询（由里及外）
select studentno,subjectno,studentresult
from result 
where subjectno in (select subjectno from `subject` 
                                     where subjectname = '数据库结构-1')
order by studentresult desc


-- 查询所有数据库结构-1的学生学号
select subjectno from `subject` where subjectname = '数据库结构-1'


-- 分数不小于80分的学生的学号和姓名
-- 需要 student，result表
select distinct studentno,studentname
from student
where studentno in (select studentno from result 
                                                where studentresult>=80)


-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名

select s.studentno,studentname
from student s
INNER JOIN result r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1' and studentresult >= 80


-- 嵌套查询（由里及外）
select s.studentno,studentname 
from student s
where studentno in (select studentno from result 
                                        where studentresult>80 and subjectno=
                                        (select subjectno from `subject` 
                                         where subjectname = '数据库结构-1'))


-- 练习：查询 C语言-1 前5名同学的成绩的信息（学号，姓名，分数）
-- 使用子查询

select s.studentno,studentname,studentresult
from student s,result r
where s.studentno =r .studentno 
and  subjectno in (select subjectno from `subject` where subjectname = 'C语言-1')
order by studentresult desc
LIMIT 0,5


-- ============= 常用函数 ===============

-- 数学运算
select ABS(-8)   -- 绝对值
select ceiling(5.2)   -- 向上取整
select FLOOR(9,5)  -- 向下取整
select rand()    -- 随机数,返回一个0-1之间的随机数
select sign(-3)    -- 符号函数: 负数返回-1,正数返回1,0返回0


-- 字符串函数 
select CHAR_LENGTH('英雄所见略同，是谁说的')   -- 返回字符串包含的字符数*
select CONCAT('我爱你','的','一切')
select INSERT('我爱编程helloworld',1,2,'超级热爱')  -- 查询，从某个位置开始替换某个长度
select LOWER('KUANg')
select UPPER('fdsafas')
SELECT INSTR('kuangshen','h')  
SELECT replace('kuangshen','g','ggfff')
SELECT substr


-- 查询姓周的同学，名字邹
select replace(studentname,'赵','李')
from student 
where studentname like '赵%' 

-- 时间和日期函数（记住）

SELECT CURRENT_DATE()
select CURDATE() -- 获取当前日期
SELECT now()
SELECT localtime()
SELECT SYSDATE()

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

SELECT SYSTEM_USER()
SELECT VERSION()

-- ========== 聚合函数 =============
-- 都能够统计 表中的数据（想查询一个表中有多少个记录，就使用这个count（））
SELECT count(studentname) from student;  -- count(字段)，会忽略所有的null值
SELECT count(*) FROM student; -- 不会忽略null值，本质 计算行数
SELECT count(1) FROM result;  -- 不会忽略null值


SELECT SUM(studentresult) as '总和' from result;
SELECT avg(studentresult) as '平均分' from result;
SELECT max(studentresult) as '最高分' from result;
SELECT min(studentresult) as '最低分' from result;


-- 查询不同课程的平均分，最高分，最低分，
-- 核心：根据不同的课程分组，

select subjectname,avg(studentresult) as 平均分,max(studentresult) as 最高分,min(studentresult) as 最低分
from result r
inner join `subject` sub
on r.subjectno = sub.subjectno
GROUP BY r.subjectno  -- 通过什么字段来分组
having 平均分>80

-- ========== 测试MD5 加密 ================

drop table testmd5

create table `testmd5`(
    `id` int(4) not null auto_increment, 
    `name` varchar(20) not null,
    `pwd` varchar(50) not null,
    primary key(`id`)
)engine=innodb default charset=utf8

truncate table testmd5

-- 明文密码
insert into testmd5(`name`,`pwd`) values('刘德华','123456'),('张翼德','123456'),('霍华德','123456'),('许冠杰','123456')

-- 加密
update testmd5 set pwd = MD5(pwd) where id = 1;

update testmd5 set pwd = MD5(pwd) where id!=1

insert into  testmd5(`name`,`pwd`) values('小米',MD5('1234556'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
select * from testmd5 where `name`='小米' and pwd=MD5('1234556')


-- ================= 事务 =================

/*
原子性（atomicity）
    要么都成功，要么都失败

一致性（consistency）
    事务 前后的数据完整性要保证一致，开始1000块钱，后面转账结束了，也得是1000块钱。

持久性（durability）
    事务 一旦提交则不可逆，被持久化到数据库中。

隔离性（isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，
不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

隔离所导致的问题：

脏读：
指一个事务读取了另外一个事务未提交的数据。

不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
（一般是行影响，如下图所示：多了一行）

*/

-- mysql 是默认开启事务自动提交的

set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启（默认的）


-- 手动处理事务
set autocommit = 0 -- 关闭自动提交


-- 事务开启
start transaction  -- 标记一个事务的开始，从这个之后的sql 都在同一个事务内
-- 提交（commit）：持久化（成功！）
-- 回滚（rollback）：回到原来的样子（失败！）


-- 事务结束
set autocommit = 1 -- 开启自动提交

-- 了解
savepoint 保存点名         -- 设置一个事务的保存点
rollback to savepoint 保存点名   -- 回滚到保存点
release savepoint 保存点名        -- 释放保存点

-- 转账的场景

create table `account`(
    `id` int(3) not null auto_increment,
    `name` varchar(30) not null,
    `money` decimal(9,2) not null,
    primary key(`id`)
)engine=innodb default charset=utf8

insert into account(`name`,`money`)
VALUES ('A',2000),('B',10000);


-- 模拟转账：事务

set autocommit = 0;   -- 关闭事务提交

start TRANSACTION         -- 开启一个事务

update account set money=money-500 where `name` = 'A' -- A减500
update account set money=money+500 where `name` = 'B' -- B加500
 
commit;   -- 提交事务，被持久化了
rollback;  -- 回滚

set autocommit = 1; -- 恢复默认值


-- ============= 索引 ============

/*
在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（PRIMARY KEY）
    唯一的标识，主键不可重复，只能有一个列作为主键

唯一索引（UNIQUE KEY）
    唯一索引是被索引的列中，不允许有相同的行值。亦即行值唯一

常规索引（KEY/INDEX）
    默认的，index。key 关键字来设置

全文索引（FullText）
    在特定的数据库引擎下才有，MyISAM
    快速定位数据


*/

-- 索引的使用
-- 1、在创建表的时候，给字段添加索引
-- 2、创建完毕后，增加索引

-- 显示所有的索引信息
show index from student;


-- 增加一个索引  ,studentname(studentname) 前面是索引名，括号内的是列名
alter table student add FULLTEXT index studentname(studentname);


-- explain 分析sql执行的状况

EXPLAIN select * from student;   -- 非全文索引

EXPLAIN select * from student where match(studentname) against('刘');

--  测试索引

drop table app_user;

CREATE TABLE `app_user` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT '' COMMENT '用户昵称',
    `email` varchar(50) NOT NULL COMMENT '用户邮箱',
    `phone` varchar(20) DEFAULT '' COMMENT '手机号',
    `gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
    `password` varchar(100) NOT NULL COMMENT '密码',
    `age` tinyint(4) DEFAULT '0' COMMENT '年龄',
     `create_time` datetime DEFAULT null,
    `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
    CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'


truncate table app_user;


-- 插入100万数据
delimiter $$    -- 写函数之前必须要写的 标志
create FUNCTION mock_data()
returns INT
BEGIN
    DECLARE num int default 1000000;
    DECLARE i int default 0;
        
    WHILE i<num DO
            -- 插入语句
        INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`,`age`)
        VALUES(CONCAT('用户', i), '24736743@qq.com', 
        CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),
        FLOOR(RAND()*100));
        set i=i+1;
    end WHILE;
    return i;
end;

select mock_data(); -- 调用函数


 
select * from student;


-- 创建一个索引
--  索引名称： id_表名_字段名
-- create index 索引名 on 表（字段）
create index id_app_user_name on app_user(`name`);

遇到的优化问题：

limit后面加了0，确实比没有加0要快不少！！！

解释MySQL中limit关键字：

– 分页每一页只显示5条数据。；limit 是排在最后的关键字
– limit 0,5 1~5
– limit 1,5 2~6

– limit 6,5 从第6个开始，显示5个数据

慢：
select device_id as user_infos_example from user_profile limit 2

快：
SELECT device_id as user_infors_example from user_profile
where device_id limit 0,2

总结：
limit后面加了0，确实比没有加0要快不少！！！