数据库_复习

MYSQl数据库基础
目标：

• 数据库的操作：创建数据库，删除数据库
• 常用数据类型
• 表的操作：创建表，删除表

1.数据库的操作
1.1 显示当前系统的数据库
show databases;
1.2 创建数据库：
create database if not exists XXX ;
1.3 使用数据库
use XXX;
1.4 删除数据库,删除之后相应数据库内的表和数据全部删除
drop database if exists XXX;

2.常用数据类型
2.1 数值类型：整型和浮点型

2.2 字符串类型

2.3 日期类型

3.表的操作
3.1 使用某个数据库中表之前先要使用对应数据库
user db_name;
3.2 查看表结构
desc 表名;

3.3 创建表:comment字段可以对自定义字段做别名
create table if not exists student(
id int,
name varchar(20) comment ‘姓名’,
age int comment ‘年龄’,
sex varchar(4) comment ‘性别’
);
3.4 删除表
drop table if exists student;

MYSQL表的增删改查
目标：

• CRUD：CCreate，Retrieve，Update，Delete
• 新增数据
• 查询数据
• 修改数据
• 删除数据

1. 新增
建表：
create table if not exists student(
id int comment ‘学号’,
age int comment ‘年龄’,
name varchar(20) comment ‘姓名’
);
1.1 单行数据+全列插入：每个字段都不能落下，且顺序正确
insert into student values (1702,23,‘张三’);
insert into student values (1702,25,‘李四’);
1.2多行数据+指定列插入
insert into student (id,age,name) values
(1702,21,‘武松’),
(1702,22,‘宋江’);

2.查询
建表：
– 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
插入数据：
– 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,‘唐三藏’, 67, 98, 56),
(2,‘孙悟空’, 87.5, 78, 77),
(3,‘猪悟能’, 88, 98.5, 90),
(4,‘曹孟德’, 82, 84, 67),
(5,‘刘玄德’, 55.5, 85, 45);

2.1 全列查询：将表中所有字段的值全部查询出来
select * from exam_result;

2.2 指定列查询
– 指定列的顺序不需要按照表中定义字段的顺序来
select id,name,english from exam_result;

2.3 查询字段为表达式
– 表达式不包含字段，不包含字段就是：查询结果会增加一个字段，为表达式，值为表达式
SELECT id, name, 10 FROM exam_result;

– 表达式包含一个字段
SELECT id, name, english + 10 FROM exam_result;

– 表达式包含多个字段
SELECT id, name, chinese + math + english FROM exam_result;

2.4 别名：为查询结果中的列指定别名，表示返回的结果集，以别名作为该列的名称
– 结果集中，表头的列名=别名
SELECT id, name, chinese + math + english 总分 FROM exam_result;

2.5 去重：使用关键词 distinct 对某列数据进行去重
– 98 分重复了
SELECT math FROM exam_result;

SELECT distinct math FROM exam_result;

2.6 排序：order by
– ASC 为升序（从小到大）
– DESC 为降序（从大到小）
– 默认为 ASC
1. 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面
– 查询id ，name和数学成绩，按数学成绩排序显示


3.使用表达式及别名进行排序
查询name，总分，由高到低


4.可以对多个字段进行排序，优先级顺序按照书写顺序
说明： 对于多字段排序是这个样子的
初始数据如下，观察c1字段中相同的数据：

观察上图，对于单一字段来说，可以进行排序
如下图，再增加一个字段c2，进行多字段排序，c1为升序，c2为降序，由此就能说明多字段排序：通俗说就是，首先对c1进行相应的排序，然后对c2进行排序，对c2的排序基于c1的排序。就是说，c1排序完之后值相同的部分为一个排序部分，也就是下图红框和蓝框两部分，c2的红框部分进行降序排序，c2的蓝框部分也进行降序排序，因为c2红框部分id对应的c1红框部分的值是相同的，同理c2蓝框部分也是一样的

2.7 条件查询 Where
比较运算符：

逻辑运算符：

注意：
1.where条件可以使用表达式，但是不能使用别名
2.and优先级高于or，在同时使用时，需要用小括号()包裹优先执行的部分

基本查询：
– 查询英语成绩不及格的同学及英语成绩
select name ,english from exam_result where english < 60;

– 查询数学成绩好于语文成绩的同学
select name ,math,chinese from exam_result where math >chinese;

– 查询总分小于200的同学
select name ,chinese+math+english total from exam_result where chinese+math+english <200;

and与or：
– 查询语文成绩大于80且英语成绩大于80的同学
select name,chinese,english from exam_result where chinese >80 and english >80;

– 查询语文成绩大于80或英语成绩大于80的同学
select name,chinese,english from exam_result where chinese >80 or english >80;
– 观察and和or的优先级

范围查询：

1. between…and…
   – 语文成绩在[80,90]之间的同学（使用and也可以完成）
   select name,chinese from exam_result where chinese between 80 and 90;
   
2. in
   – 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩（使用or也可以完成）
   SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
   
   模糊查询：like
   – % 匹配任意多个（包括 0 个）字符
   select name from exam_result where name like ‘孙%’;
   
   – _ 匹配严格的一个任意字符
   select name from exam_result where name like ‘孙_’;
   表中没有长度为2的孙某，所以为空

null 的查询：is （not）null
– 查询 qq_mail 已知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;
– 查询 qq_mail 未知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;

2.8 分页查询：limit
– 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET s;

修改：update…set…
– 将孙悟空同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = ‘孙悟空’;
– 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = ‘曹孟德’;
– 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT
3;
– 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

删除：delete
– 删除孙悟空同学的考试成绩
DELETE FROM exam_result WHERE name = ‘孙悟空’;
– 删除整张表数据
– 准备测试表
DROP TABLE IF EXISTS for_delete;
CREATE TABLE for_delete (
id INT,
name VARCHAR(20)
);
– 插入测试数据
insert into for_delete (name) values (‘A’),(‘B’),(‘C’);
– 删除整张表数据
delete from for_delete;– 表还存在，数据被销毁，区分和drop

MySQL表的增删查改进阶
目标：

• 数据库约束
• 表的关系
• 增删查改

1.数据库的约束
1.1约束类型：
（1） NOT NULL - 指示某列不能存储 NULL 值。
（2）UNIQUE - 保证某列的每行必须有唯一的值。
（3）DEFAULT - 规定没有给列赋值时的默认值。
（4）PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标
识，有助于更容易更快速地找到表中的一个特定的记录。
（5）FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
（6）CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句

1.2 null约束：创建表时，指定某列不为空

1.3 unique：唯一约束，指定某列值是唯一的，不可重复的

1.4 default：默认只约束
指定插入数据时，name列为空，默认值unkown：

1.5 primary key：主键约束
指定id列为主键：

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大
值+1。

1.6 foreign key：外键约束，外键用于关联其他表的主键或唯一键
案例：
创建班级表classes，id为主键
– 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
desc VARCHAR(100)
);
创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，
classes_id为外键，关联班级表id
– 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT ‘unkown’,
qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);

2. 查询：聚合查询——聚合函数

• count：查询数据的数量
  – 统计班级共有多少同学
  SELECT COUNT(*) FROM student;
  SELECT COUNT(0) FROM student;
  – 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
  SELECT COUNT(qq_mail) FROM student;
• sum：总和，不是数字没有意义
  – 统计数学成绩总分
  SELECT SUM(math) FROM exam_result;
  – 不及格 < 60 的总分，没有结果，返回 NULL
  SELECT SUM(math) FROM exam_result WHERE math < 60;
• avg：平均值，不是数字没有意义
  – 统计平均总分
  SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
• max：最大值
  – 返回英语最高分
  SELECT MAX(english) FROM exam_result;
• min：最小值
  – 返回 > 70 分以上的数学最低分
  SELECT MIN(math) FROM exam_result WHERE math > 70;

group by

查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;

having：GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary) <1500;

联合查询
实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
首先，先简单解释一下笛卡尔积。

现在，我们有两个集合A和B。

A = {0,1} B = {2,3,4}

集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；

B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；

以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。
注意：关联查询可以对关联表使用别名。初始化测试数据：

• classes表
  CREATE TABLE classes (
  id INT PRIMARY KEY auto_increment,
  name VARCHAR(20),
  desc VARCHAR(100)
  );
  
• student表
  CREATE TABLE student (
  id INT PRIMARY KEY auto_increment,
  sn INT UNIQUE,
  name VARCHAR(20) DEFAULT ‘unkown’,
  qq_mail VARCHAR(20),
  classes_id int,
  FOREIGN KEY (classes_id) REFERENCES classes(id)
  );
  
• course表
  – 创建课程表
  DROP TABLE IF EXISTS course;
  CREA TETABLE course (
  id INT PRIMARY KEY auto_increment,
  name VARCHAR(20)
  );
  
• score表
  – 创建课程学生中间表：考试成绩表
  CREA TETABLE score (
  id INT PRIMARY KEY auto_increment,
  score DECIMAL(3, 1),
  student_id int,
  course_id int,
  FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (course_id) REFERENCES course(id));
  
  内连接：
  语法：
  select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
  select 字段 from表1 别名1,表2 别名2 where 连接条件 and 其他条件;
  例子：
  （1）查询张三同学的成绩，
  select sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name=‘张三’;

将成绩表中student_id=学生表中id并且学生表中名字叫张三的成绩全部查询出来

（2）查询所有同学的总成绩，及同学的个人信息：
– 成绩表对学生表是多对1关系，查询总成绩是根据成绩表的同学id来进行分组的
select
stu.sn,
stu.name,
stu.qq_mail,
sum(sco.score)
from student stu join score sco on stu.id=sco.student_id group by sco.student_id;

（3）查询所有同学的成绩，及同学的个人信息：
select * from student stu join score sco on stu.id=sco.student_id;

– 学生表，成绩表，课程表三张表关联查询
select
stu.id,
stu.sn,
stu.name,
stu.qq_mail,
sco.score,
sco.course_id,
cou.name
from student stu
join score sco on stu.id=sco.student_id
join course cou on sco.course_id =cou.id
order by
stu.id;

外连接：
分为左外连接和右外连接，如果联合查询，左侧的表完全显示就是左外连接，右侧的表完全显示就是右外连接
语法：
– 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
– 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;**


– 学生表、成绩表、课程表3张表关联查询
SELECT
stu.id,
stu.sn,
stu.NAME,
stu.qq_mail,
sco.score,
sco.course_id,
cou.name
FROM
student stu
LEFT JOIN score sco ON stu.id = sco.student_id
LEFT JOIN course cou ON sco.course_id = cou.id
ORDERBY
stu.id;

左连接 ，右连接，内连接和全外连接的4者区别
本文为CSDN博主「灰太狼_cxh」的原创文章，遵
原文链接：https://blog.csdn.net/weixin_39220472/article/details/81193617

子查询：子查询是指嵌入到其他sql语句中的select语句，也叫嵌套查询

• 单行子查询：返回一行记录的子查询
  查询与“不想毕业” 同学的同班同学：
  select * from student where classes_id =(select classes_id from student where name =‘不想毕业’);
  
• 多行子查询：返回多行记录的子查询
  案例：查询“语文”或“英文”课程的成绩信息
  – 使用IN
  select * from score where course_id in (select id from course where name='语文’or name=‘英文’);
  
  – 使用 NOT IN
  select * from score where course_id not in (select id from course where name!='语文’and name!=‘英文’);
  
  MySQL 索引事务
• 索引
  1.概念：
  索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。
  2.作用：
  （1）数据库中的表，数据，索引之间的关系，类似于书架上的图书，书籍内容和书籍目录的关系
  （2）索引所起的作用类似于书籍目录，可用于快速定位，检索数据
  （3）索引对于提高数据库的性能有很大的帮助
  3.使用场景
  考虑对数据库表的某列或某几列创建索引，考虑如下几点：
  （1）数据量较大，且经常对这些列进行条件查询
  （2）该数据库表的插入操作，及对这些列的修改操作频率较低
  （3）索引会占用额外的磁盘空间
  4.使用
  创建主键约束，唯一约束，外键约束时，会自动创建对应的索引
  索引保存的数据结构主要是B+数，及hash的方式
• 事务
  概念：事务指的是逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败
  总结：
  索引：
  （1）对于插入、删除数据频率高的表，不适用索引
  （2）对于某列修改频率高的，该列不适用索引
  （3）通过某列或某几列的条件查询频率高的，可以对这些列创建索引