Mysql(2)

3 MySQL数据管理

3.1 外键

外键概念

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

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

外键作用

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

外键所在的表是从表 引用的字段所在的表就是主表

创建表成功后，添加外键约束

CREATE TABLE `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

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

删除外键

删除grade表，报错 （student 是子表。grade是主表）

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

3.2 DML语言（全部记住）

• INSERT (添加数据语句)
• UPDATE (更新数据语句)
• DELETE (删除数据语句)

3.3 添加

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

注意事项：

• 字段或值之间用英文逗号隔开 .
• ’ 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .
• 可同时插入多条数据 , values 后用英文逗号隔开 .

3.4 修改

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE 条件];

3.5 删除

delete命令

DELETE FROM 表名 [WHERE condition];  

truncate 命令

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

delete和truncate的区别

• 相同点：都能删除数据，但都不会删除表结构

• 不同：

  • truncate 重新设置 自增列 计数器会归零

  • truncate 不会影响事务

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

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

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

4 DQL查询数据（最重点！）

4.1 DQL(Data Query Language)

数据库最核心的语言

-- 查询所有学生信息
SELECT * FROM student;
-- 查询指定列(学号 , 姓名)
SELECT studentno,studentname FROM student;

4.2 指定查询字段

去重 distinct

作用：去除select查询结果中重复的数据，重复的数据只显示一条

-- # 查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT studentno FROM result; -- 查看哪些同学参加了考试
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)

数据库的列（表达式）

select 表达式 from 表

-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

-- 学员考试成绩集体提分一分查看
SELECT studentno,StudentResult+1 AS '提分后' FROM result;

4.3 where条件子句

作用：检索数据中符合条件的值

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

SELECT `StudentNo`,`StudentResult` FROM result
WHERE studentresult BETWEEN 95 AND 100

模糊查询：比较运算符

LIKE

-- 查询姓刘的同学的学号及姓名
-- 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 (1000,1001,1002);

null

-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

4.4 联表查询

JOIN 对比

-- 查询参加了考试的同学（学号，姓名。科目编号。分数）
SELECT * FROM student
SELECT * FROM result
/*
 1. 分析查询的字段来自那些表（如果不是同一张表，则需要联接查询）
 2. 确定使用哪种联接查询？7种
 确定交叉点
 判断的条件：学生表中的studentno = 成绩表 studentno
 */
 
 -- join （连接的表）on（判断的条件） 联接查询
 -- where 等值查询
 -- on条件是生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录，还会返回on条件为真的记录
 -- where条件是在临时表生成好后，再对临时表进行过滤的条件，此时已经没有left join的含义（必须返回左边表的记录）了，条件不为真    的就全部过滤掉
 SELECT s.studentno,studentname,subjectno,studentresult 
 FROM student AS s
 INNER JOIN result AS r 
 WHERE s.studentno = r.studentno
 # s.studentno 指student中的学号，避免ambiguous
 # inner join 查看并集

操作	描述
inner join	如果表中至少有一个匹配，就返回
left join	即使右表中没有匹配，也会从左表中返回所有的值 # e.g:王凯没有成绩，依旧返回
right join	即使左表中没有匹配，也会从右表中返回所有的值 # 右表result中没有王凯的成绩，所以没有返回

-- left join 
-- 查询缺考的同学
 SELECT s.studentno,studentname,subjectno,studentresult 
 FROM student s
 LEFT JOIN result r
 ON s.studentno = r.studentno
 WHERE studentresult IS NULL
 
 -- 思考题（查询了参加考试的同学信息:学号，学生姓名，科目名，分数）
 /*
 1. 分析查询的字段来自那些表（studentno,studentname,subjectname,studentresult）
 2. 确定使用哪种联接查询？7种
 确定交叉点
 判断的条件：学生表中的studentno = 成绩表 studentno
 */
 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
 
from a left join b #以左表为基准
from a right join b #以右表为基准

-- 查询学员所属的年级（学号，学生姓名，年级名称）
 SELECT  studentno,studentname,gradename
 FROM student s
 INNER JOIN `grade` g
 ON s.gradeid = g.gradeid
 
-- 查询科目所属的年级（科目名称，年级名称）
 SELECT  subjectname,gradename
 FROM `subject` sub
 INNER JOIN grade g
 ON sub.gradeid = g.gradeid

-- 思考题（查询了 参加数据库结构-1 数据库考试的同学信息:学号，学生姓名，科目名，分数）
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 = '数据库结构-1'

– 我要查询哪些数据 select…

– 我从哪几个表查 from 表 xxx join 连接的表 on 交叉条件

–假设存在一种多张表查询，慢慢来，先查询两张表再慢慢增加

自连接(了解)

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

-- 查询父子信息：把一张表看为两个一模一样的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

4.5 分页和排序

排序

– 排序：升序 ASC 降序：DESC

-- 排序：升序 ASC 降序：DESC
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
ORDER BY studentresult ASC

分页

– 分页：每页只显示五条数据

–语法：limit 当前页，页面的大小

–limit 0，5 1~5

–limit 1，5 2~6

/*============== 分页 ================
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好处 : (用户体验,网络传输,查询压力)

推导:
   第一页 : limit 0,5
   第二页 : limit 5,5
   第三页 : limit 10,5
   ......
   第N页 : limit (pageNo-1)*pagesize,pagesize
   [pageNo:页码,pageSize:单页面显示条数]
   
*/

-- 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

4.6 子查询

-- 使用子查询（由里及外）
SELECT studentno,subjectno,studentresult
FROM result
WHERE subjectno = (
	SELECT subjectno FROM `subject`
	WHERE subjectname= '数据库结构-1'
)

4.7 Select语法

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 * FROM student;

-- 查询指定列(学号 , 姓名)
SELECT studentno,studentname FROM student;