MySQL知识补充

1 复习

1.1 约束

• 默认约束：default
• 非空约束：not null
• 唯一约束：unique
• 检查约束：check
• 主键约束：primary key

​ 唯一

​ 非空

​ 可以被从表的外键引用

• 外键约束：foreign key

​ 可以为空

​ 可以重复

​ 如果有值 此值必须在主表的主键列存在

​ 删除主表记录时 必须保证此记录不被从表引用

• 自增约束：auto_increment

  只能加给int类型的主键列

1.2 单表查询

SELECT * FROM student; -- 查询所有列
SELECT sid,sname FROM student; -- 查询指定列
SELECT sage+1,MOD(score,3) FROM student;-- 列运算
SELECT * FROM student WHERE sage>10 OR sex="男" AND score BETWEEN 10 AND 30 AND sdy IN(TRUE,FALSE);-- 条件查询
SELECT * FROM student WHERE sage IS NULL OR sname IS NOT NULL;-- 条件查询
SELECT DISTINCT sex FROM student;-- 除去重复行
SELECT * FROM student WHERE sname LIKE "_a%";-- 模糊查询
SELECT CONCAT(sname,"呵呵",sage) FROM student; -- 字符串拼接
SELECT CONCAT(IFNULL(sname,"无名氏"),"呵呵",IFNULL(sage,1)) FROM student; -- null转换
SELECT CONCAT(sname,"呵呵",sage) AS 自我介绍 FROM student; -- 字符串拼接-- 起别名
SELECT SUM(score) 总分,
AVG(score) 参加考试的平均分,
AVG(IFNULL(score,0)) 所有同学的平均分,
MAX(score) 最高分,
MIN(score) 最低分,
COUNT(score) 考试人数
COUNT(*) 总人数
FROM student;-- 聚合函数
SELECT sage,COUNT(*),MAX(score) FROM student GROUP BY sage HAVING sage >20-- 分组
SELECT * FROM  student ORDER BY score DESC,sage ASC;-- 排序
SELECT * FROM student LIMIT 1,6;-- 分页查询
CREATE TABLE stu2 AS SELECT sname,sage FROM student WHERE sex="男";-- 根据结果集创建表

1.3 表与表之间的关系

• 一对多

在多的一方 定义外键来引用一这一方的主键列即可

• 多对多

定义一个关系表：定义两列作为联合主键 并且作为外键来分别引用另外两张表的主键

• 一对一

根据创建顺序或者重要程度区分一个主表和从表
把从表的主键列定义为外键 来引用主表的主键

2 多表查询

一个sql语句查询多张表

2.0 装备工作

DROP TABLE student;
DROP TABLE teacher;
CREATE TABLE teacher(
    tid INT PRIMARY KEY AUTO_INCREMENT,
    tname VARCHAR(10),
    tsex CHAR(1),
    tsubject VARCHAR(10)
);
INSERT INTO teacher VALUES(NULL,"张三","男","java");
INSERT INTO teacher VALUES(NULL,"张三丰","男","c");
INSERT INTO teacher VALUES(NULL,"张无极","女","c++");
INSERT INTO teacher VALUES(NULL,"田七","女","c");
INSERT INTO teacher VALUES(NULL,"赵八","女","c++");
INSERT INTO teacher VALUES(NULL,"赵六","男","java");
INSERT INTO teacher VALUES(7,"fdb9","男","c#");
CREATE TABLE student(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(10),
    sex CHAR(1),
    sage INT,
    score FLOAT(4,1),
    stid INT
);
INSERT INTO student(
               sname,
               sex,
               sage,
               score,
               stid) VALUES(
               SUBSTR(UUID(),1,4),
               IF(RAND()>0.5,"男","女"),
               TRUNCATE(RAND()*8+18,0),
               TRUNCATE(RAND()*100,1),
               TRUNCATE(RAND()*3+2,0)
);
SELECT * FROM student;
SELECT * FROM teacher;

2.1 合并结果集

把两个结果集通过union或者union all 合并为一个结果集

-- 合并结果集
-- 必须满足条件：结果集列数相同  对应的列类型一致
-- 获取全校师生的编号 名字 性别
-- 获取所有学生的编号 名字 性别
SELECT sid,sname,sex FROM student;
SELECT tid,tname,tsex FROM teacher;
-- 以上两个结果集;都是三列 对应的列的数据类型一致  这样的结果集就可以合并
-- 重复行只显示一次
SELECT sid 编号,sname 名字,sex 性别 FROM student
UNION
SELECT tid,tname,tsex FROM teacher;
-- 不去除重复行
SELECT sid 编号,sname 名字,sex 性别 FROM student
UNION ALL
SELECT tid,tname,tsex FROM teacher;

2.2 连接查询

from后面有多张表：为连接查询

-- 笛卡尔积
-- 行：student行数*teacher行数
-- 列：student列数+teacher列数
SELECT * FROM student AS s,teacher AS t;
-- 通过主表的主键值=从表的外键值 来删除垃圾的笛卡尔积行
SELECT * FROM student AS s,teacher AS t WHERE s.stid=t.tid;

内连接

-- 内连接
-- 获取学生及其老师信息
SELECT * FROM student;
SELECT * FROM teacher;
-- 内连接的方言格式：
-- select 列 from 表1,表2 where 条件;
SELECT * FROM student AS s,teacher AS t WHERE s.stid=t.tid;
-- 内连接的标准格式：
-- select 列 from 表1 inner join 表2 on 条件;
SELECT * FROM student AS s INNER JOIN teacher AS t ON s.stid=t.tid;

外连接

--  外连接
--  保证一张表的完整性
--  获取所有学生及其老师信息
--  左外连接: 保证左边表的完整性 
--  格式：select 列 from 表1 left join 表2 on 条件;
SELECT * FROM student AS s LEFT JOIN teacher AS t ON s.stid=t.tid;
SELECT * FROM teacher AS t RIGHT JOIN student AS s ON s.stid=t.tid;
--  右外连接：保证右边表的完整性 
--  格式：select 列 from 表1 right join 表2 on 条件;
SELECT * FROM student AS s RIGHT JOIN teacher AS t ON s.stid=t.tid;

2.3 子查询

-- 子查询
-- select中嵌套select

-- 子select在from后面作为一张表
-- 获取学生和老师都为女的的师生信息
-- 通过内连接实现
SELECT * FROM student s,teacher t WHERE s.stid=t.tid AND s.sex="女" AND t.tsex="女";
-- 通过子查询实现
SELECT * FROM student WHERE sex="女"; -- 获取女同学
SELECT * FROM teacher WHERE tsex="女"; -- 获取女老师
-- 把以上两个结果集作为两个表来查询
SELECT * 
FROM 
     (SELECT * FROM student WHERE sex="女") s,
     (SELECT * FROM teacher WHERE tsex="女") t
WHERE s.stid=t.tid;

-- 子select在select后面作为结果集的列
SELECT   *,
  (SELECT AVG(IFNULL(score,0)) FROM student) 平均分,
  (score-(SELECT AVG(IFNULL(score,0)) FROM student)) 平均分差距
FROM student;


-- 获取最高分学生的信息
-- 子select在where后面作为条件
SELECT *  FROM student WHERE score=(SELECT MAX(score) FROM student);

/*子select作为条件可以使用的运算符/关键字：> >= < <= != */ 
SELECT * FROM student WHERE sage > (SELECT sage FROM student WHERE sid=1);-- 获取比1号学生年龄大的所有学生的信息
SELECT * FROM student WHERE stid=(SELECT tid FROM teacher WHERE tname="张三");-- 获取张三老师教的所有学生的信息
/*子select作为条件可以使用的运算符/关键字：in、not in */
-- 获取与女生分数相同的男生的信息
SELECT score FROM student WHERE sex="女";-- 获取女生的所有分数
SELECT * FROM student WHERE sex="男"  AND score IN(SELECT score FROM student WHERE sex="女"); -- 获取男生的信息
-- 获取与所有女生分数不同的男生的信息
SELECT * FROM student WHERE sex="男"  AND score NOT IN(SELECT score FROM student WHERE sex="女");

/*子select作为条件可以使用的运算符/关键字：all、any*/
-- 获取比所有20岁的学生分数都高的学生的信息
-- 获取所有20岁学生的分数
SELECT MAX(score) FROM student WHERE sage=20;
SELECT * FROM student WHERE score > (SELECT MAX(score) FROM student WHERE sage=20);
SELECT * FROM student WHERE score > ALL (SELECT score FROM student WHERE sage=20);
-- 获取比任意一个20岁的学生分数高的学生的信息
SELECT * FROM student WHERE score > (SELECT MIN(score) FROM student WHERE sage=20) AND sage!=20;
SELECT * FROM student WHERE score > ANY (SELECT score FROM student WHERE sage=20) AND sage!=20;

-- 获取所有学生的信息 并附加最高分
SELECT  s.*,ms.*
FROM student s,
     (SELECT MAX(score) FROM student) ms;
     
SELECT  s.*,(SELECT MAX(score) FROM student)
FROM student s;

3 实现数据库备份和恢复

方式1：通过数据库客户端软件即可实现

方式2：通过mysqldump和source分别实现备份和恢复

数据备份

• 打开dos命令行：windows+r

• 输入mysqldump的命令：mysqldump -uroot -proot 数据库名 > 备份的文件

  备份文件必须先创建好空的文件 文件后缀名为.sql

数据恢复

• 创建一个空的数据库 并制定编码集

• 通过source命令实现把备份文件读入到当前数据库中