MySQL学习(二)

MySQL数据管理

1、外键

在创建表的时候增加约束

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

-- 学生表的 gradeid 字段 要去引用年级表的 gradeid 
-- 定义外键key 
-- 给这个外键添加约束 （执行引用）  references 引用

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 '性别',
	`birthdate` DATETIME DEFAULT NULL COMMENT '出生日期',
        `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	`address` VARBINARY(20) DEFAULT NULL COMMENT '地址',
	`email` VARBINARY(20) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`),
        KEY `FK_gradeid` (`gradeid`),
        CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES 
)ENGINE=INNODB DEFAULT CHARSET=utf8;

删除有外键关系的表的时候，必须要先删除引用别人的表 （从表），再删除被引用的表 （主表）

方式二：创建成功后，添加外键约束

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

-- 学生表的 gradeid 字段 要去引用年级表的 gradeid 
-- 定义外键key 
-- 给这个外键添加约束 （执行引用）  references 引用

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 '性别',
	`birthdate` DATETIME DEFAULT NULL COMMENT '出生日期',
        `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	`address` VARBINARY(20) DEFAULT NULL COMMENT '地址',
	`email` VARBINARY(20) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`),
)ENGINE=INNODB DEFAULT CHARSET=utf8;
SHOW CREATE DATABASE school; -- 查看创建数据库的语句 
SHOW CREATE TABLE student;-- 查看student数据表的定义语句 
DESC student-- 显示表的结构

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

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

以上的操作都是物理外键，数据库级别的外键，我们不建议使用！ （避免数据库过多造成困扰，这里了解即可~）

2、DML语言

数据库意义：数据存储，数据管理

DML语言：数据操作语言

• insert
• update
• delete

3、添加

-- 插入语句（添加） 
-- insert into 表名([字段名1,字段2,字段3])values('值1'),('值2'),('值3'，....) 
INSERT INTO `grade` (`gradename`) VALUE('大四');

-- 由于主键字段我们可以省略(如果不写表的字段，他就会一一匹配) 
INSERT INTO `grade` VALUES('大三');
-- 上句相当于 INSERT INTO `grade` ([`gradeid`,`gradename`])VALUES('大三',null)会报错
-- 一般写插入语句，我们一定要数据和字段一一对应
-- 插入多个字段
INSERT INTO `grade` (`gradename`)
VALUES('大二'),('大一')

INSERT INTO `student`(`name`)VALUES('张三');

INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('张三','123','男');

INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('李四','456','男'),('王五','789','男'),('赵六','159','女');

语法： `nsert into 表名(字段名1,字段2,字段3)values('值1'),('值2'),('值3',...)

注意事项：

1、字段和字段之间使用 英文逗号','隔开;

2、字段是可以省略的，但是后面的值必须要要一一对应，不能少;

3、可以同时插入多条数据，VALUES 后面的值，需要使用','隔开即可。比如：VALUES(),(),....

4、修改

update 修改谁（条件） set原来的值=新值

-- 语法
-- UPDATE 表名 SET 字段名=新值,字段名=新值,字段名=新值 where[条件]
-- 修改学员名字
UPDATE `student` SET`name`='star' WHERE id = 1;

-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET`name`='哆啦A梦';

-- 修改多个属性，逗号隔开
UPDATE `student` SET`name`='star',`pwd`='abc',`email`='123@qq.com' WHERE id=2;

可以通过多个条件定位数据
条件：where 运算符 id等于某个值或者大于某个值或者在某个区间修改...，这个语句会返回布尔值

操作符	含义	范围
=	等于	1=1
<>或!=	不等于	5<>6

| |
<| |
<=| |
=| |
BETWEEN……and……|在某个范围内|[2,5]
AND|并且|5>1 and 1>2
OR|或|5>1 or 1>2

5、删除

delete命令

语法：delete from 表名[where 条件]

-- 删除数据 （避免这样写，会全部删除） 
DELETE FROM `student`

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

TRUNCATE命令

作用：完全清空一个数据库表，表的结构和索引约束不会变！

TRUNCATE `student`

delete和truncate的区别

• 相同点：都删除数据，不会删除表结构
• 不同点：
  • TRUNCATE：重新设置自增列，计数器回归零，不会影响事务

DELETE删除的问题，重启数据库，现象

• INNODB：自增列会从1开始（存在内存当中的，断电即失）
• MYISAM：继续从上一个自增量开始（存在文件中，不会丢失）

DQL查询数据

1、DQL

（Date Query Language：数据查询语言）

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

Select完整的语法：

SELECT语法

注意：[]代表可选的，{}代表必选的

2、指定查询字段

-- 查询全部的学生 
SELECT * FROM student;

-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM `student`;

-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表去别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM `student`;

-- 函数 CONCAT(a,b)
SELECT CONCAT('姓名:',`StudentName`) AS 新名字 FROM `student`;

语法：SELECT 字段,...FROM 表

去重 distinct

作用：去除SELECT查询出来的结果中重复的数据，重复的数据值显示一条

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

数据库的列（表达式）

-- 查询系统的版本（函数）
SELECT VERSION();
-- 用来计算(表达式)
SELECT 100*3-23 AS 计算结果;
SELECT @@auto_increment_increment; -- 查询自增的步长（变量）

-- 学员考试成绩+1后查看
SELECT `StudentNo`,`StudentResult` AS '提分后' FROM result;

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量...

语法 select 表达式 from 表

3、whhere条件子句

作用：检索淑君中符合条件的值

搜索的条件由一个或者多个表达式组成！结果：布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b / a&&b	逻辑与，两个都为真，结果为真
or
Not ！	not a / !a	逻辑非，真为假，假为真

尽量使用英文字母！

SELECT studentNo,`StudentResult` FROM result;

-- 查询考试成绩在 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;

模糊查询：比较运算符

运算符	语法	描述
is NULL	A IS NULL	如果操作符为 NUll, 结果为真
IS NOT NULL	A IS NOT NULL	如果操作符不为 null，结果为真
BETWEEN	A BETWEEN B AND C	若a 在 b 和c 之间，则结果为真
LIKE	A LIKE B	SQL匹配，如果a匹配b，则结果为真
IN	A IN (A1,A2,A3...)	假设a在a1，或者a2...其中的某一个值中，结果为真

-- 查询全部的学生 
SELECT * FROM student;

-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM `student`;

-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表去别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM `student`;

-- 函数 CONCAT(a,b)
SELECT CONCAT('姓名:',`StudentName`) AS 新名字 FROM `student`;

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

-- 查询系统的版本（函数）
SELECT VERSION();
-- 用来计算(表达式)
SELECT 100*3-23 AS 计算结果;
SELECT @@auto_increment_increment; -- 查询自增的步长（变量）

-- 学员考试成绩+1后查看
SELECT `StudentNo`,`StudentResult` AS '提分后' FROM result;
-- ======================where======================
SELECT studentNo,`StudentResult` FROM result;

-- 查询考试成绩在 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 （具体的一个或者多个值） ===== 
-- 查询 1001,1002,1003号学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentNo` IN ('1001','1002','1003');

-- 查询在安徽、河南洛阳的学生 
SELECT `StudentName`,`Address` FROM `student`
WHERE `Address` IN ('安徽','河南洛阳');

-- ==== null  not null====

4、联表查询

JOIN对比

-- ============== 联表查询 join ==============
-- 查询参加考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM `student`
SELECT * FROM `result`
/*思路
1、分析需求，分析查询的字段来自哪些表（连接查询）
2、确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 StudentNo = 成绩表中的 StudentNo
*/

-- join （连接的表） on（判断的条件） 连接查询 
-- where    等值查询

-- INNER JOIN
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`;

-- RIGHT JOIN
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`;

-- LEFT JOIN
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`;

-- 查询缺考的学生
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult` IS NULL;

操作	描述
inner join	如果表中至少由一个匹配，就返回行
right join	会从坐标种返回所有的值，即使在表中没有匹配
left join	会从右表中返回所有的值，即使左表中没有匹配

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

-- 这里要联三张表
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`

-- 我要查询哪些数据 select ... 
-- 从那几个表中查 FROM 表  XXX Join 连接的表  on  交叉条件 
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

-- From  a  left join b 以a表为基准
-- From  a  right join b 以b表为基准

5、分页和排序

-- 排序：升序ASC，降序DEESC
-- ORDER BY 通过字段排序，怎么拍
-- 查询的结果根据成绩降序 排序
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE `SubjectName` = '数据库结构-1'
ORDER BY `StudentResult` ASC;

分页

-- 为什么分页？
-- 缓解数据库压力，给人的体验更好，瀑布流
-- 分页，每页只显示五条数据
-- 语法：limit 起始值，页面大小
-- 网页应用：当前总的页数，页面的大小
-- LIMIT 0,5     1-5
-- LIMIT 1,5     2-6
-- LIMIT 6,5     6-10
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE `SubjectName` = '数据库结构-1'
ORDER BY `StudentResult` ASC;
LIMIT 6,5
-- 第一页  limit 0,5      （1-1）*5 
-- 第二页  limit 5,5       （2-1）*5 
-- 第三页  limit 10,5      （3-1）*5 
-- 第N页   limit 0,5       （n-1）* pageSize，pageSize -- 【pageSize：页面大小】 -- 【(n-1)* pageSize:起始值】 
-- 【n ：当前页 】 
-- 【数据总数/页面大小 = 总页数】

语法：limit（查询起始小标，pageSize）

6、子查询

where(这个值是计算出来的)

本质：在where语句中嵌套一个总查询语句

-- =================== where ==================
-- 1、查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序排列 
-- 方式一： 使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectNo` = r.`subjectNo`
WHERE `SubjectName` = '数据库结构-1'
ORDER BY `StudentResult` DESC

-- 方式二：使用子查询（由里及外）
-- 查询所有数据库结构-1 的学生学号
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
WHERE `SubjectNo` = (
	SELECT `SubjectNo` FROM `subject`
	WHERE `SubjectName` = '数据库结构-1'
)
ORDER BY `StudentResult` DESC

7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80 
-- 核心： （根据不同的课程分组）
SELECT `SubjectName`,AVG(`StudentResult`) AS 平均分,MAX(`StudentResult`),MIN(`StudentResult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.`SubjectNo` -- 通过什么字段来分组
HAVING 平均分 > 80; -- havaing是在分组后的次要条件