MySQL入门教程-CRUD

3、MySQL数据管理

3.1、外键

外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

外键作用：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

目标：学生表（student）的gradeid字段 要去引用年级表（grade）的 gradeid字段

创建外键

方式一：在创建表的时候增加约束

?/*
?    1. 定义外键key
?    2. 给外键添加约束（执行引用）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 '性别',
?    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
?    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
?    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
?    `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
?    PRIMARY KEY (`id`),
?    KEY `FK_gradeid` (`gradeid`),
?    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
?)ENGINE=INNODB DEFAULT CHARSET=utf8
??
?-- 创建年级表
?CREATE TABLE `grade`(
?    `gradeid` INT(10) NOT NULL COMMENT '年级id',
?    `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
?    PRIMARY KEY (`gradeid`)
?)ENGINE=INNODB DEFAULT CHARSET=utf8

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

---

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

?/*
?    1. 定义外键key
?    2. 给外键添加约束（执行引用）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 '性别',
?    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
?    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
?    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
?    `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
?    PRIMARY KEY (`id`)
?)ENGINE=INNODB DEFAULT CHARSET=utf8
??
?-- 创建表的时候没有外键关系
?ALTER TABLE `student`
?ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
??
?-- 创建年级表
?CREATE TABLE `grade`(
?    `gradeid` INT(10) NOT NULL COMMENT '年级id',
?    `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
?    PRIMARY KEY (`gradeid`)
?)ENGINE=INNODB DEFAULT CHARSET=utf8

数据库就是用来单纯的表，只用来存数据，只有行（数据）和列（属性）

推荐:

?-- 创建表的时候没有外键关系
?ALTER TABLE `student`
?ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

3.2、DML语言

Data Manipulation Luaguge：数据操作语言

添加 insert

?-- 普通用法
?INSERT INTO `student`(`name`) VALUES ('zsr');
??
?-- 插入多条数据
?INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');
??
?-- 省略字段
?INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1); 

语法：

?INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

注意：

1. 字段和字段之间使用英文逗号隔开

2. 字段是可以省略的，但是值必须完整且一一对应

3. 可以同时插入多条数据，VALUES后面的值需要使用逗号隔开

修改 update

?-- 修改学员名字,指定条件
?UPDATE `student` SET `name`='zsr204' WHERE id=1;
??
?-- 不指定条件的情况,会改动所有表
?UPDATE `student` SET `name`='zsr204';
??
?-- 修改多个属性
?UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;
??
?-- 通过多个条件定位数据
?UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';

语法：

?UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

关于WHERE条件语句：

删除 delete

?-- 删除数据(避免这样写,会全部删除)
?DELETE FROM `student`;
??
?-- 删除指定数据
?DELETE FROM `student` WHERE id=1;

语法：

?DELETE FROM 表名 [WHERE 条件]
?关于DELETE删除的问题，重启数据库现象：
??
?INNODB 自增列会从1开始（存在内存当中，断电即失）
?MYISAM 继续从上一个子增量开始（存在内存当中，不会丢失）
?TRUNCATE

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

DELETE和TRUNCATE 的区别：

DELETE可以条件删除（where子句），而TRUNCATE只能删除整个表

TRUNCATE 重新设置自增列，计数器会归零，而DELETE不会影响自增

DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

?CREATE TABLE `test`(
?    `id` INT(4) NOT NULL AUTO_INCREMENT,
?    `coll` VARCHAR(20) NOT NULL,
?    PRIMARY KEY (`id`)
?)ENGINE=INNODB DEFAULT CHARSET=utf8;
??
?INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');
??
?-- 不会影响自增
?DELETE FROM `test`;
??
?-- 会影响自增
?TRUNCATE TABLE `test`;

3.3DQL查询数据

Data Query Language 数据查询语言

?SELECT [ALL | DISTINCT]
?{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
?FROM table_name [as table_alias]
?  [left | right | inner join table_name2]  -- 联合查询
?  [WHERE ...]  -- 指定结果需满足的条件
?  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
?  [HAVING]  -- 过滤分组的记录必须满足的次要条件
?  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
?  [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

• 查询数据库数据 , 如SELECT语句

• 简单的单表查询或多表的复杂查询和嵌套查询

• 是数据库语言中最核心,最重要的语句

• 使用频率最高的语句

  前提配置：

  ?-- 创建学校数据库
  ?CREATE DATABASE IF NOT EXISTS `school`;
  ??
  ?-- 用school数据库
  ?USE `school`;
  ??
  ?-- 创建年级表grade表
  ?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;
  ??
  ?-- 给grade表插入数据
  ?INSERT INTO `grade`(`GradeID`,`GradeName`) 
  ?VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');
  ??
  ?-- 创建成绩result表
  ?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;
  ??
  ?-- 给result表插入数据
  ?INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) 
  ?VALUES (1000,1,'2019-10-21 16:00:00',97),(1001,1,'2019-10-21 16:00:00',96),
  ?(1000,2,'2019-10-21 16:00:00',87),(1001,3,'2019-10-21 16:00:00',98);
  ??
  ?-- 创建学生表student
  ?CREATE TABLE `student`( 
  ?    `StudentNo` INT(4) NOT NULL COMMENT '学号',
  ?    `LoginPwd` VARCHAR(20) DEFAULT NULL,
  ?    `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
  ?    `Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,取值0或1',
  ?    `GradeID` INT(11) DEFAULT NULL COMMENT '年级编号',
  ?    `Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空,即可选输入',
  ?    `Adress` 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`)
  ?)ENGINE=MYISAM DEFAULT CHARSET=utf8;
  ??
  ?-- 给学生表插入数据
  ?INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeID`,`Phone`,`Adress`,`BornDate`,`Email`,`IdentityCard`) 
  ?VALUES (1000,'1241','dsaf',1,2,'24357','unknow','2000-09-16 00:00:00','1231@qq.com','809809'),
  ?(1001,'1321','dfdj',0,2,'89900','unknow','2000-10-16 00:00:00','5971@qq.com','908697');
  ??
  ?-- 创建科目表
  ?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 AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
  ??
  ?-- 给科目表subject插入数据
  ?INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) 
  ?VALUES(1,'高数','96',2),(2,'大物','112',2),(3,'程序设计',64,3);
  ??
  ?SELECT 字段 FROM 表;

基础查询

语法：

?SELECT 查询列表 FROM 表名;

• 查询列表可以是：表中的（一个或多个）字段，常量，变量，表达式，函数

• 查询结果是一个虚拟的表格

?-- 查询全部学生
?SELECT * FROM student;
??
?-- 查询指定的字段
?SELECT `LoginPwd`,`StudentName` FROM student;
??
?-- 别名 AS(可以给字段起别名,也可以给表起别名)
?SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;
??
?-- 函数 CONCAT(str1,str2,...)
?SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;
??
?-- 查询系统版本(函数)
?SELECT VERSION();
??
?-- 用来计算(计算表达式)
?SELECT 100*53-90 AS 计算结果;
??
?-- 查询自增步长(变量)
?SELECT @@auto_increment_increment;
??
?-- 查询有哪写同学参加了考试,重复数据要去重
?SELECT DISTINCT `StudentNo` FROM result;

条件查询

where 条件字句：检索数据中符合条件的值

语法：

?select 查询列表 from 表名 where 筛选条件;
?-- 查询考试成绩在95~100之间的
?SELECT `StudentNo`,`StudentResult` FROM result
?WHERE `StudentResult`>=95 AND `StudentResult`<=100;
?-- &&
?SELECT `StudentNo`,`StudentResult` FROM result
?WHERE `StudentResult`>=95 && `StudentResult`<=100;
?-- BETWEEN AND
?SELECT `StudentNo`,`StudentResult` FROM result
?WHERE `StudentResult`BETWEEN 95 AND 100;
??
?-- 查询除了1000号以外的学生
?SELECT `StudentNo`,`StudentResult` FROM result
?WHERE `StudentNo`!=1000;
?-- NOT
?SELECT `StudentNo`,`StudentResult` FROM result
?WHERE NOT `StudentNo`=1000;
??
?-- 查询名字含d的同学
?SELECT `StudentNo`,`StudentName` FROM student
?WHERE `StudentName` LIKE '%d%';
??
?-- 查询名字倒数第二个为d的同学
?SELECT `StudentNo`,`StudentName` FROM student
?WHERE `StudentName` LIKE '%d_';
??
?-- 查询1000,1001学员
?SELECT `StudentNo`,`StudentName` FROM student
?WHERE `StudentNo` IN (1000,1001);

分组查询

语法：

?select 分组函数，分组后的字段
?from 表
?【where 筛选条件】
?group by 分组的字段
?【having 分组后的筛选】
?【order by 排序列表】

区别：

?-- 查询不同科目的平均分、最高分、最低分且平均分大于90
?-- 核心：根据不同的课程进行分组
?SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
?FROM result r
?INNER JOIN `subject` s
?on r.SubjectNo=s.SubjectNo
?GROUP BY r.SubjectNo
?HAVING AVG(StudentResult)>90;

连接查询

?-- 查询学员所属的年级（学号，学生姓名，年级名称）
?SELECT `StudentNo`,`StudentName`,`GradeName`
?FROM student s
?INNER JOIN grade g
?ON s.GradeID=g.GradeID;
??
?-- 查询科目所属的年级
?SELECT `SubjectName`,`GradeName`
?FROM `subject` s
?INNER JOIN `grade` g
?ON s.GradeID=g.GradeID;
??
?-- 查询列参加程序设计考试的同学信息（学号，姓名，科目名，分数）
?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 SubjectName='课程设计';

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

?-- 创建一个表
?CREATE TABLE `course` (
?`courseid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程id',
?`pid` INT(10) NOT NULL COMMENT '父课程id',
?`courseName` VARCHAR(50) NOT NULL COMMENT '课程名',
?PRIMARY KEY (`courseid`)
?) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
??
?-- 插入数据
?INSERT INTO `course` (`courseid`, `pid`, `courseName`)
?VALUES('2','1','信息技术'),
?('3','1','软件开发'),
?('4','3','数据库'),
?('5','1','美术设计'),
?('6','3','web开发'),
?('7','5','ps技术'),
?('8','2','办公信息');

将该表进行拆分：

?SELECT a.`courseid` AS '父课程',b.`courseid` AS '子课程'
?FROM course AS a,course AS b
?WHERE a.`courseid`=b.`pid`;

排序和分页

排序

语法：

?select 查询列表
?from 表
?where 筛选条件
?order by 排序列表 asc/desc

order by的位置一般放在查询语句的最后（除limit语句之外）

?SELECT `StudentNo`,`StudentName`,`GradeName`
?FROM student s
?INNER JOIN grade g
?ON s.GradeID=g.GradeID
?ORDER BY `StudentNo` DESC;

分页

语法：

?select 查询列表
?from 表
?limit offset,pagesize;

• offset代表的是起始的条目索引，默认从0开始

• size代表的是显示的条目数

• offset=(n-1)*pagesize

?-- 第一页 limit 0 5
?-- 第二页 limit 5,5
?-- 第三页 limit 10,5
?-- 第n页  limit (n-1)*pagesize,pagesize
?-- pagesize:当前页面大小
?-- (n-1)*pagesize:起始值
?-- n:当前页面
?-- 数据总数/页面大小=总页面数
?-- limit n 表示从0到n的页面

子查询

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

?-- 查询‘课程设计’的所有考试结果（学号，科目编号，成绩）降序排列
??
?-- 方式一:使用连接查询
?SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
?FROM result r
?INNER JOIN `subject` s
?on r.StudentNo=s.SubjectNo
?WHERE SubjectName='课程设计'
?ORDER BY StudentResult DESC;
?-- 方式二:使用子查询（由里到外）
?SELECT StudentNo,SubjectNo,StudentResult
?from result
?WHERE SubjectNo=(
?    SELECT SubjectNo FROM `subject`
?    WHERE SubjectName='课程设计'
?)