数据库

技术2022-07-15 70

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的优先级范围查询：

between…and… – 语文成绩在[80,90]之间的同学（使用and也可以完成） select name,chinese from exam_result where chinese between 80 and 90; 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者区别本文为博主「灰太狼_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）通过某列或某几列的条件查询频率高的，可以对这些列创建索引

Processed: 0.056, SQL: 9