现在数据库开发中高阶需要掌握哪些知识点呢？

一、现在数据库开发中高阶需要掌握哪些知识点呢？

1.数据库基础知识点

1.1数据库概念

什么是数据库

简而言之，就是存储数据，管理数据的仓库。

常见的数据库分为：

关系型数据库： Oracle、MySQL、SQLServer、Access非关系型数据库： MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

1.1.2关系型和非关系型

早期发展的数据库建立在数据的紧密关系基础之上（如：父子关系、师生关系），我们称其为关系型数据库，也称为传统数据库；现今数据库建立在数据的松散关系基础之上（如：中国人和美国人、中国人和印度人、视频、音频），我们称其为非关系型数据库nosql（not only sql）。业界总在争论nosql能否干掉传统数据库，很多初学者也有这个困惑。以我来看，两者没有矛盾，它们各有特点，根据业务情况互补才是真谛。但总的来说原来关系型数据库一统天下的格局早被打破，领土不断被蚕食，规模一再的缩小，虽然无法全面被替代，但却早已风光不在，沦落到一偶之地，Oracle的衰落就是最好的证明，早期只要是全球大企业无一例外都是部署Oracle，但现在都在去Oracle化，阿里就已经全面排斥Oracle。

既然干不掉，很多传统项目的还是围绕关系型数据库的居多，所以我们先来学习关系型数据库，目前最流行的关系型数据库是MySQL。

1.1.3关系型数据库关系型数据库有特定的组织方式，其以行和列的形式存储数据，以便于用户理解。关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。

1.2Mysql数据库

Mysql数据库（1）mysql服务端，它来处理具体数据维护，保存磁盘（2）mysql客户端，CRUD新增，修改，删除，查询

1.2.2MySQL数据存放在哪里？

在MySQL的配置文件my.ini中会进行默认配置

1.2.3MySQL服务端mysql-5.5.27-winx64.msi

Mysql数据库默认的编码是latin1等价于iso-8859-1，修改为utf-8

注意：配置完，mysql开始执行，最后一步出错有时仍可以使用，使用SQLyog工具测试，如不行，再执行安装程序，选择remove，删除，然后重新安装。同时注意必须是管理员权限。

1.2.4MySQL客户端1：DOS窗口

mysql -uroot -proot

语法：mysql.exe执行文件

代表参数-u 用户名，紧接着写的-p 密码，紧接着写的

1.2.5MySQL客户端2：可视化工具

1.3数据库的结构

数据库结构

1.4SQL语句

1.4.1定义结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ “S-Q-L”)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。

1.4.2分类（1）DML（Data Manipulation Language）数据操纵语言如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

（2）DDL（Data Definition Language）数据库定义语言如：create table之类

（3）DCL（Data Control Language）数据库控制语言如：grant、deny、revoke等，只有管理员才有相应的权限

（4）DQL（Data Query Language）数据库查询语言如: select 语法注意：SQL不区分大小写

1.5数据库常用操作

1.5.1建库创建数据库，数据库名称：cgb2022

create database cgb2022 DEFAULT CHARACTER SET utf8;

1.5.2删库删除名称是cgb2022的数据库

drop database cgb2022;

1.5.3查看所有数据库查看所有数据库

show databases;

1.5.4使用数据库

use cgb2022;

1.6表的常用操作

表设计门店表：** tb_door**

订单详情表：** tb_order_detail**

1.6.1创建表创建tb_door表，有id,door_name,tel字段

create table tb_door(
id int primary key auto_increment,
door_name varchar(100),
tel varchar(50)
);

1.6.2修改表添加列

alter table tb_door add column money NUMERIC(7,2)

1.6.3删除表删除名称是tb_door的表

drop table tb_door;

1.6.4查看所有表查看所有表

show tables;

1.6.5查看表结构/设计表查看tb_door表结构

desc tb_door;

1.7表记录的常用操作

1.7.1插入记录向tb_door表中插入2条记录

insert into tb_door values(null,'永和大王1店',666);
insert into tb_door values(null,' 永和大王2店',888);

1.7.2查询记录查询tb_door表中的所有记录

SELECT * FROM tb_door;

1.7.3修改记录修改tb_door表中id为1的记录

update tb_door set tel=555 where id=1;

1.7.4删除记录删除tb_door表中id为2的数据

Delete from tb_door where id=2;

1.7.5排序将tb_door表记录按照tel排序

Select * from tb_door order by tel desc;

1.7.6记录总数查询tb_door表中的总记录数

Select count(*) from tb_door;

1.8数据类型

1.8.1命名规则（1）字段名必须以字母开头，尽量不要使用拼音（2）长度不能超过30个字符（不同数据库，不同版本会有不同）（3）不能使用SQL的保留字，如where,order,group（4）只能使用如下字符az、AZ、0~9、$ 等（5）racle习惯全大写：USER_NAME，mysql习惯全小写：user_name（6）多个单词用下划线隔开，而非java语言的驼峰规则

1.8.2字符（1）char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间（2）varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2（3）大文本: 大量文字（不推荐使用，尽量使用varchar替代）

以utf8编码计算的话，一个汉字在u8下占3个字节注：不同数据库版本长度限制可能会有不同

1.8.3数字(1)tinyint,int整数类型（2）float,double小数类型（3）numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数（4）decimal和numeric表示精确的整数数字

1.8.4日期（1）date 包含年月日（2）time时分秒（3）datetime包含年月日和时分秒（4）timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

1.8.5图片blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

1.9准备数据

1.9.1部门表 dept

CREATE TABLE dept(
deptno int primary key auto_increment ,
dname VARCHAR(20),
loc VARCHAR(13)
);
INSERT INTO dept VALUES(null,'accounting','一区');
INSERT INTO dept VALUES(null,'research','二区');
INSERT INTO dept VALUES(null,'operations','二区');

1.9.2员工表 emp

Mysql:

CREATE TABLE emp(
empno int primary key auto_increment,
ename VARCHAR(10),
job VARCHAR(10),
mgr int,
hiredate DATE,
sal double,
comm NUMERIC(7,2),
deptno int
);
INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);

1.10字段约束

1.10.1主键约束**主键约束：**如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。

添加主键约束，例如将id设置为主键：

主键自增策略当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1

create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;

1.10.2非空约束非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

添加非空约束，例如为password添加非空约束：

create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,123;);//OK

1.10.3唯一约束唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

添加唯一约束，例如为username添加唯一约束及非空约束：

create table test(
id int primary key auto_increment,
username varchar(50) unique--唯一约束
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重复会报错的
select * from test;

2.数据库进阶

2.1基础函数

lower

SELECT 'ABC',LOWER('ABC') from dept; --数据转小写

upper

select upper(dname) from dept --数据转大写

length

select length(dname) from dept --数据的长度

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

concat

select dname,concat(dname,'123') X from dept --拼接数据

replace

select dname,replace(dname,'a','666') X from dept --把a字符替换成666

ifnull

select ifnull(comm,10) comm from emp #判断,如果comm是null,用10替换

round & ceil & floorround四舍五入，ceil向上取整，floor向下取整

–直接四舍五入取整

select comm,round(comm) from emp

–四舍五入并保留一位小数

select comm,round(comm,1) from emp

–ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm) from emp

uuidSELECT UUID()

返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

now

select now() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

year & month & day–hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

–year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

转义字符'作为sql语句符号，内容中出现单撇就会乱套，进行转义即可

select 'ab'cd' -- 单引号是一个SQL语句的特殊字符

select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符

2.2条件查询

distinct使用distinct关键字，去除重复的记录行

SELECT loc FROM dept;

SELECT DISTINCT loc FROM dept;

where注意:where中不能使用列别名!!

select * from emp

select * from emp where 1=1 --类似没条件

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系

select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800;

-- 或

select name, sal from emp where sal in(1400,1600,1800);

select name, sal from emp where sal not in(1400,1600,1800);

like通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置

null

select * from emp where mgr is null --过滤字段值为空的

select * from emp where mgr is not null --过滤字段值不为空的

between and

SELECT * FROM emp

select * from emp where sal<3000 and sal>10000

select * from emp where sal<=3000 and sal>=10000--等效

select * from emp where sal between 3000 and 10000--等效

limit分数最高的记录：按分数排序后，limit n，返回前n条。Oracle做的很笨，实现繁琐，后期有介绍，而mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询：

select * from emp limit 2 --列出前两条

select * from emp limit 1,2 --从第二条开始,展示2条记录

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

order by

SELECT * FROM emp order by sal #默认升序

SELECT * FROM emp order by sal desc #降序

2.3统计案例

入职统计#2015年以前入职的老员工

SELECT * FROM emp WHERE DATE_FORMAT(hiredate,'%Y-%m-%d')<'2015-01-01';

SELECT * FROM emp WHERE YEAR(hiredate)<2015

#2019年以后签约的员工，日期进行格式转换后方便比较

SELECT * FROM emp WHERE YEAR(DATE_FORMAT(hiredate,'%Y-%m-%d'))<=2019;

#2015年到2019年入职的员工

SELECT * FROM emp

WHERE

STR_TO_DATE(hiredate,'%Y-%m-%d')>='2015-01-01'

AND

STR_TO_DATE(hiredate,'%Y-%m-%d')<='2019-12-31'

年薪统计公司福利不错13薪，年底双薪，统计员工的年薪=sal13+comm13

SELECT empno,ename,job,sal*13+comm*13 FROM emp;

SELECT empno,ename,job,sal*13+comm*13 **as**  **年薪** FROM emp;--用as给列起个别名

SELECT empno,ename,job,sal*13+comm*13 年薪 FROM emp; --as也可以省略

select ename, sal+comm from emp

select ename, sal , comm, sal+ifnull(comm,0) from emp--用0替换掉null

2.4聚合 aggregation

根据一列统计结果

count
select count(*) from emp --底层优化了

select count(1) from emp --效果和*一样

select count(comm) from emp --慢,只统计非NULL的

max / min

select max(sal) from emp --求字段的最大值

select max(sal) sal,max(comm) comm from emp

select min(sal) min from emp --获取最小值

select min(sal) min,max(sal) max from emp --最小值最大值

SELECT ename,MAX(sal) FROM emp group by ename --分组

sum / avg

select count(*) from emp --总记录数

select sum(sal) from emp --求和

select avg(sal) from emp --平均数

2.5分组 group

用于对查询的结果进行分组统计group by表示分组, having 子句类似where过滤返回的结果

group by#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对

SELECT deptno,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno #按照deptno分组

SELECT job,MAX(sal),AVG(sal) FROM emp

GROUP BY job #按照job分组

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno,job #deptno和job都满足的

having#平均工资小于8000的部门

select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having

#deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp

GROUP BY deptno #按deptno分组

HAVING COUNT(deptno)>1 #次数多的

2.6扩展

char和varchar有什么区别？char为定长字符串，char(n)，n最大为255varchar为不定长字符串，varchar(n)，n最大长度为65535char(10)和varchar(10)存储abc，那它们有什么差别呢？char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。

datetime和timestamp有什么区别？数据库字段提供对日期类型的支持，是所有数据类型中最麻烦的一个，慢慢使用就会体会出来。

date 是 年与日

time是 时分秒

datetime年月日时分秒，存储和显示是一样的

timestamp时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数

中文乱码如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：

set names gbk;

设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码？怎么办呢？很简单，两个都尝试下，哪个最后操作完成，查询数据库不乱码，就用哪个。

那为何会造成乱码呢？

Mysql数据库默认字符集是lantin1，也就是以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：

create database yhdb charset utf8;

但这样很容易造成服务器和客户端编码集不同，如服务器端utf8，客户端ISO8859-1。mysql和客户端工具都有习惯的默认编码设置，好几个地方，要都统一才可以保证不乱码。

我们只要保证创建数据库时用utf8，使用可视化工具一般就基本正确。

注释/* 很多注释内容 */

#行注释内容

– 行注释内容，这个使用较多

主键、外键、唯一索引的区别？Primary Key 主键约束，自动创建唯一索引Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写Unique Index 唯一索引，唯一值但不是主键

对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

drop、delete和truncate之间的区别？drop删除库或者表，数据和结构定义

delete和truncate只是删除表的数据

delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录

对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数

3.数据库整合

3.1事务 transaction

什么是事务

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

下面以银行转账为例，A转100块到B的账户，这至少需要两条SQL语句：

给A的账户减去100元；update 账户表 set money=money**-100** where name=‘A’;

给B的账户加上100元。update 账户表 set money=money**+100** where name=‘B’;

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么B的账户没有加上100元，而A却减去了100元，在现实生活中这肯定是不允许的。

如果在转账过程中加入事务，则整个转账过程中执行的所有SQL语句会在一个事务中，而事务中的所有操作，要么全都成功，要么全都失败，不可能存在成功一半的情况。

也就是说给A的账户减去100元如果成功了，那么给B的账户加上100元的操作也必须是成功的；否则，给A减去100元以及给B加上100元都是失败的。

事务4个特性ACID

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离级别

事务隔离分为不同级别，包括

读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好读提交（read committed） Oracle默认的隔离级别可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

查询mysql的隔离级别

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

开启事务：start transaction；结束事务：commit（提交事务）或rollback（回滚事务）。在执行SQL语句之前，先执行start transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

SELECT @@tx_isolation;

Repeatable Read（可重读）

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

事务处理

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的MySQL默认数据库的事务是开启的，执行SQL后自动提交。MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

提交 commit

#多条语句时，批量执行，事务提交

#有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱

#mysql的事务默认就是开启的 – 多条语句一起操作时,要么一起成功要么一起失败

BEGIN; #关闭事务的自动提交,相当于start transaction
 
INSERT INTO user (id) VALUES(25);#成功
 
INSERT INTO user (id) VALUES(5);#已经存在5了，会失败
 
COMMIT; #手动提交事务

回滚 rollback

#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚

BEGIN;
 
INSERT INTO user (id) VALUES(15);
 
INSERT INTO user (id) VALUES(35);#存在了
 
ROLLBACK;#事务回滚，就不会再提交了

3.2表强化：6约束 constraints

非空约束 not null

唯一约束 uniqueName字段创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错：

主键约束 primary key主键是一条记录的唯一标识，具有唯一性，不能重复

外键约束 foreign key

默认约束 default

默认值

检查约束 check很少使用，了解即可，录入age超过200将报错

3.3表关联 association

概念表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。

同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关。

下面我们讨论表的关系分为四种：

一对一 one to one QQ和QQ邮箱，员工和员工编号一对多 one to many 最常见，部门和员工，用户和订单多对一 many to one 一对多反过来，员工和部门，订单和用户多对多 many to many 老师和学生，老师和课程

创建表表设计特点：

表都以s结束，标识复数字段多以表的首字母作为开头，在多表联查时，方便标识出是哪个表的字段

drop table if exists courses;
drop table if exists scores;
drop table if exists students;
drop table if exists teachers;


create table courses
(
cno varchar(5) not null,
cname varchar(10) not null,
tno varchar(3) not null,
primary key (cno)
);


create table scores
(
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10,1) not null,
primary key (sno, cno)
);


create table students
(
sno varchar(3) not null,
sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
class varchar(5),
primary key (sno)
);



create table teachers
(
tno varchar(3) not null,
tname varchar(4),
tsex varchar(2),
tbirthday datetime,
prof varchar(6),
depart varchar(10),
primary key (tno)
);

插入测试数据

3.4多表联查 join

笛卡尔积 Cartesian product多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表emp中不只显示deptno，还要显示部门名称，而部门名称dname在dept表中。

#把两个表的数据都拼接起来

SELECT * FROM dept,emp

上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。

这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

这就是阿里规范中禁止3张表以上的联查的原因：

三种连接 join内连接 inner join左（外）连接 left join右（外）连接 right join

案例：列出research部门下的所有员工的信息

SELECT * FROM emp
 
WHERE deptno = ( SELECT deptno FROM dept WHERE dname='research' )

inner join、left join、right join的区别？

INNER JOIN两边都对应有记录的才展示，其他去掉LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充子查询 subquery概念子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用，记录多时最好使用其它方式替代。

单行子查询 =返回结果为一个

--列出tony所在部门的所有人员
 
select deptno from emp where ename='tony';
 
select * from emp where deptno = (select deptno from emp where ename='tony');

多行子查询 inin子查询

3.6索引 index

定义索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

分类单值索引：一个索引只包括一个列，一个表可以有多个列唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引复合索引：一个索引同时包括多列创建索引#查看索引，主键会自动创建索引

show index from dept;

删除索引

alter table dept drop index fuhe_index

索引扫描类型type：

ALL 全表扫描，没有优化，最慢的方式index 索引全扫描，其次慢的方式range 索引范围扫描，常用语<，<=，>=，between等操作ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况null MySQL不访问任何表或索引，直接返回结果

最左特性

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)、(k1,k3)和(k1,k2,k3)索引，这就是最左匹配原则，也称为最左特性。

为何索引快？明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。

其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

排序，tree结构，类似二分查找索引表小

小结优点:索引是数据库优化表的主键会默认自动创建索引每个字段都可以被索引大量降低数据库的IO磁盘读写成本，极大提高了检索速度索引事先对数据进行了排序，大大提高了查询效率

缺点:索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

3.7视图View

概念可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直接缓存到了视图中。下次还要发起相同的sql，直接查视图。现在用的少,了解即可.使用： 1，创建视图 2，使用视图

测试

3.8SQL优化

创建mysql-db库

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mysql-db` /*!40100 DEFAULT CHARACTER SET utf8 */;
 
USE `mysql-db`;

准备student表

DROP TABLE IF EXISTS `student`;
 
CREATE TABLE `student` (
 
`id` varchar(4) NOT NULL,
 
`NAME` varchar(20) DEFAULT NULL,
 
`sex` char(2) DEFAULT NULL,
 
`birthday` date DEFAULT NULL,
 
`salary` decimal(7,2) DEFAULT NULL,
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
insert into `student`(`id`,`NAME`,`sex`,`birthday`,`salary`) values ('1','张慎政','男','2020-01-01','10000.00'),('2','刘沛霞','女','2020-01-02','10000.00'),('3','刘昱江','男','2020-01-03','10000.00'),('4','齐雷','男','2020-01-04','20000.00'),('5','王海涛','男','2020-01-05','20000.00'),('6','董长春','男','2020-01-06','10000.00'),('7','张久军','男','2020-01-07','20000.00'),('8','陈子枢','男','2020-10-11','3000.00');

准备tb_dept表

DROP TABLE IF EXISTS `tb_dept`;
 
CREATE TABLE `tb_dept` (
 
`id` int(11) NOT NULL AUTO_INCREMENT,
 
`name` varchar(50) DEFAULT NULL,
 
`parent_id` int(11) DEFAULT NULL,
 
`sort` int(11) DEFAULT NULL,
 
`note` varchar(100) DEFAULT NULL,
 
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
`updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
insert into `tb_dept`(`id`,`name`,`parent_id`,`sort`,`note`,`created`,`updated`) values (1,'集团',0,1,'集团总部','2018-10-02 09:15:14','2018-09-27 16:35:54'),(2,'财务部',1,2,'财务管理','2018-09-27 16:35:52','2018-09-27 16:34:15'),(3,'软件部',1,3,'开发软件、运维','2018-09-27 16:35:54','2018-09-27 16:34:51');

准备tb_user表

DROP TABLE IF EXISTS `tb_user`;
 
CREATE TABLE `tb_user` (
 
`id` int(11) NOT NULL AUTO_INCREMENT,
 
`dept_id` int(11) DEFAULT NULL,
 
`username` varchar(50) DEFAULT NULL,
 
`password` varchar(100) DEFAULT NULL,
 
`salt` varchar(50) DEFAULT NULL,
 
`email` varchar(100) DEFAULT NULL,
 
`mobile` varchar(100) DEFAULT NULL,
 
`valid` tinyint(4) DEFAULT NULL,
 
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
`updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
insert into `tb_user`(`id`,`dept_id`,`username`,`password`,`salt`,`email`,`mobile`,`valid`,`created`,`updated`) values (1,1,'陈集团','123456',NULL,'tony@sina.com','13572801415',1,'2018-09-30 09:32:18','2018-09-30 09:32:18'),(2,3,'牛软件','567890',NULL,'niu@sina.com','13208737172',0,'2018-10-02 09:23:19','2018-09-20 09:32:18');

*查询SQL尽量不要使用select ，而是具体字段

查询尽量避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。

通常采用分页，一页习惯10/20/50/100条。

使用explain分析你SQL执行计划SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。

EXPLAIN
 
SELECT * FROM student WHERE id=1

是否使用了索引及其扫描类型

type：

ALL 全表扫描，没有优化，最慢的方式index 索引全扫描range 索引范围扫描，常用语<，<=，>=，between等操作ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询null MySQL不访问任何表或索引，直接返回结果key：

真正使用的索引方式创建name字段的索引

ALTER TABLE student ADD INDEX index_name (NAME)

优化like语句模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效

反例：

EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '%1'
 
EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '%1%'

正例：

EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '1%'

字符串怪现象反例：

#未使用索引
 
EXPLAIN
 
SELECT * FROM student WHERE NAME=123

正例：

#使用索引
 
EXPLAIN
 
SELECT * FROM student WHERE NAME='123'

理由：为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

索引不宜太多，一般5个以内索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要索引不适合建在有大量重复数据的字段上如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

where限定查询的数据数据中假定就一个男的记录

反例：

SELECT id,NAME FROM student WHERE sex='男'

正例：

SELECT id,NAME FROM student WHERE id=1 AND sex='男'

理由：

需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销

避免在where中对字段进行表达式操作反例：

EXPLAIN
 
SELECT * FROM student WHERE id+1-1=+1

正例：

EXPLAIN
 
SELECT * FROM student WHERE id=+1-1+1
 
EXPLAIN
 
SELECT * FROM student WHERE id=1

理由：

SQL解析时，如果字段相关的是表达式就进行全表扫描避免在where子句中使用!=或<>操作符

应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

反例：

EXPLAIN
 
SELECT * FROM student WHERE salary!=3000
 
EXPLAIN
 
SELECT * FROM student WHERE salary<>3000

理由：

使用!=和<>很可能会让索引失效去重distinct过滤字段要少

索引失效

EXPLAIN
 
SELECT DISTINCT * FROM student

索引生效

EXPLAIN
 
SELECT DISTINCT id,NAME FROM student
 
EXPLAIN
 
SELECT DISTINCT NAME FROM student

理由：

带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间

where中使用默认值代替null#修改表，增加age字段，类型int，非空，默认值0

ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;

批量插入性能提升大量数据提交，上千，上万，批量性能非常快，mysql独有

多条提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷');
 
INSERT INTO student (id,NAME) VALUES(5,'刘昱江');

批量提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷'),(5,'刘昱江');

理由：

默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升数据量小体现不出来

批量删除优化避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

反例：

#一次删除10万或者100万+？

delete from student where id <100000;

采用单一循环操作，效率低，时间漫长

for（User user:list）{
 
delete from student;
 
}

正例：

//分批进行删除，如每次500

for(){
 
delete student where id<500;
 
}
 
delete student where id>=500 and id<1000;

理由：

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作

伪删除设计商品状态（state）：1-上架、2-下架、3-删除

理由：这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用操作速度快，特别数据量很大情况下

提高group by语句的效率可以在执行到该语句前，把不需要的记录过滤掉

反例：先分组，再过滤

select job，avg（salary） from employee
group by job
having job ='president' or job = 'managent';

正例：先过滤，后分组

select job，avg（salary） from employee
where job ='president' or job = 'managent'
group by job;

复合索引最左特性创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

EXPLAIN
 
SELECT * FROM student WHERE NAME='陈子枢'

没有出现左边的字段，则不满足最左特性，索引失效

EXPLAIN
 
SELECT * FROM student WHERE salary=3000

复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAIN
 
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000

虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN
 
SELECT * FROM student WHERE salary=3000 AND NAME='陈子枢'

理由：复合索引也称为联合索引当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

排序字段创建索引什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。

#使用 *，包含了未索引的字段，导致索引失效

EXPLAIN
 
SELECT * FROM student ORDER BY NAME;

#name字段有索引

EXPLAIN
 
SELECT id,NAME FROM student ORDER BY NAME

#排序字段未创建索引，性能就慢

EXPLAIN
 
SELECT id,NAME FROM student ORDER BY sex

删除冗余和重复的索引

SHOW INDEX FROM student

#创建索引index_name

ALTER TABLE student ADD INDEX index_name (NAME)

#删除student表的index_name索引

DROP INDEX index_name ON student ;

#修改表结果，删除student表的index_name索引

ALTER TABLE student DROP INDEX index_name ;

#主键会自动创建索引，删除主键索引

ALTER TABLE student DROP PRIMARY KEY ;

不要有超过5个以上的表连接关联的表个数越多，编译的时间和开销也就越大每次关联内存中都生成一个临时表应该把连接表拆开成较小的几个执行，可读性更高如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了阿里规范中，建议多表联查三张表以下

inner join 、left join、right join，优先使用inner join三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

inner join 内连接，只保留两张表中完全匹配的结果集left join会返回左表所有的行，即使在右表中没有匹配的记录right join会返回右表所有的行，即使在左表中没有匹配的记录

理由：如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

in子查询的优化日常开发实现业务需求可以有两种方式实现：

一种使用数据库SQL脚本实现一种使用程序实现如需求：查询所有部门的所有员工：

#in子查询

SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);

#这样写等价于：

#先查询部门表

SELECT id FROM tb_dept

#再由部门dept_id，查询tb_user的员工

SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：

上面的需求使用SQL就远不如程序实现，特别当数据量巨大时。

理由：数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿

3.9数据库设计的三范式

概述简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式,也是作为数据库 设计的一些规则.

关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）第四范式(4NF）第五范式（5NF，又称完美范式）。范式越高，冗余最低，一般到三范式，再往上，表越多，可能导致查询效率下降。所以有时为了提高运行效率，可以让数据冗余.

1NF的定义为：符合1NF的关系中的每个属性都不可再分

2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖,也就是说,表里的每个字段都要依赖于主键第一步：找出数据表中所有的码。第二步：根据第一步所得到的码，找出所有的主属性。第三步：数据表中，除去所有的主属性，剩下的就都是非主属性了。第四步：查看是否存在非主属性对码的部分函数依赖

3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖就是指没个属性都跟主键有直接关系而不是间接关系。像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）这样一个表结构，就存在上述关系。 学号–> 所在院校 --> (院校地址，院校电话)这样的表结构，我们应该拆开来，如下。（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）

总结三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

3.10SQL面试题

笔记补充：

1、create database cgb210801 default character set utf8; #指定字符集,避免了中文乱码2、表里有几个字段values需要提供几个值+值的顺序和字段顺序一致3、主键约束,给字段添加PRIMARY KEY ，特点是:字段值必须唯一 + 值不能为null，主键自增策略:是指主键的值不需要程序员数,交给数据库自增,给主键添加 AUTO_INCREMENT4、查询多个字段的值时用逗号隔开5、like模糊查询 %是通配符,通配0~n个字符6、汉字排序时会查utf8里对应的数字,按照数字升序排序7、按照什么分组?按照非聚合列分组8、什么时候要分组?查询时出现了混合列9、group by后的过滤必须用having10、where里不能用别名,不能出现聚合函数,比having高效11、having和where能互换吗?效率谁高? 可以，但如果有别名或者值聚合函数就不能互换，where的效率比having高12、检查约束:给字段使用check添加合法性的检查14、外键foreign key的作用：通过两张表的主键来约束两张表，防止了冗余的数据，并通过外键来描述两张表的关系外键约束：给字段添加外键约束特点：给子表中的主键的值添加数据时，子表的主键值 必须 取自主表当主表删除数据时，子表没有相关的记录，子表不在占用主表资源创建外键的语法：foreign key(本表的主键名) references 对方的表名（对方的主键）15、索引的使用步骤：创建索引（索引创建原则：经常按照指定字段查询）+使用索引16、索引的操作可以增删改查17、主键自带索引，提高查询效率18、explain关键字：查询SQL的执行计划／性能（看看用没用索引）和sql一起执行，用来查看在查询中有没有用索引,possible_keys用到的索引19、创建索引的时候一般把表设计好以后就创建了，因为在填充数据以后再来添加索引的话，比如像唯一索引有时候就不太方便了21、字段里面的信息如果有重复的话，不能创建唯一索引21、复合索引可能会存在一种特殊情况，复合索引失效，查询要满足最左特性【A,B,C】不管怎么查多要包括字段A,另外比如查询(C,A)也不会影响不按照最左特性来查询的话，最坏的结果也就是查询的慢一些，像是不加索引一样优势：简化了一个一个创建索引的方式，多个列同时使用一个复合索引22、索引:好处是:提高查询效率 坏处是:索引需要单独的一张表23、创建单值索引:一个索引只包含一个字段24、视图：语法:create view 视图名 as 查询的SQL语句（1）创建唯一索引:一个索引只包 含一个字段,索引列值不能重复、（2）视图查询的优势感受比索引更直观，但前提是把SQL写出来，然后不执行结果存在视图中（3）视图的增删改效率很低，所以视图通常用来查询（4）和索引一样,都是对数据库优化的有效方案.（5）特点:1, 可以把视图当做表来使用2,视图里存的数据是 SQL查询到的结果3,SQL无法优化,要合理的使用视图（6）好处和坏处：好处是:1.简化了查询的SQL(相同的SQL需求不必再写SQL了,直接查视图)2.视图可以被共享,视图屏蔽了真实业务表的复杂性坏处是:视图一旦创建,SQL无法被优化

26、多表联查三种方式

二、专升本数据库原理知识点总结归纳？

数据库原理

信息与数据

信息：现实世界事物的存在方式和运动状态反应的综合

源于物质和能量：信息不能脱离物质而存在，信息的传递和获取都需要消耗能量

可以被感知：不同信息源感知形式不同，如感官，仪表盘

可存储、加工、传递、再生：如大脑的存储就叫记忆

数据：纪录信息的可识别符号，信息的表现形式

同一信息可以有不同的表现形式，信息本身不随数据形式的变化而变化

数据与信息的联系

表示与被表示

信息＝数据＋处理

数据库演变

人工管理

数据不保存

数据 + 程序 => 内存 => 处理 => 完成 => 释放

无专门管理软件

逻辑结构和物理结构均由应用程序决定

数据不共享

数据面向程序单独定义

数据不独立

程序与数据强依赖

文件系统

数据以文件形式保存

由文件系统管理数据

数据组织成具有一定结构文件

共享性差、冗余度大、易造成数据不一致

设备独立性

程序只需要考虑文件路径，不需考虑物理结构

独立性仍然较差

数据库系统

数据结构化

共享性高，冗余度底

一组数据可为多个应用和用户共同使用

独立性高

物理独立性

存储结构、存取方式、存储设备

逻辑独立性

数据定义、数据类型、数据关系

数据控制

安全性

口令验证

权限管理

完整性

正确性

数据合法性如 data type

有效性

数据范围如月份

相容性

同事实多表示如性别不能同时男女

并发

多应用多用户同时使用

数据恢复

错误、故障恢复

数据库系统

用户

终端用户

应用程序员

数据库管理员（DBA）

设计、建立、管理、维护数据库

数据库设计

存取结构、存取策略

帮助终端用户和应用程序员

定义安全性和完整性

监督数据库使用和运行

改进数据库

软件系统

应用系统

应用开发工具

数据库管理系统（DBMS）

操作系统

管理计算机资源

数据库

数据库系统模式结构

模式：数据库中全体数据的逻辑结构和特征描述

实例：模式的具体值

例

模式：员工基本情况数据库

实例：A公司、B公司员工基本情况

数据库三级模式

外模式（用户级）

一个数据库有多个外模式

保证数据安全性

DBMS提供子模式定义语言

模式（概念级）

一个数据库只有一个模式

定义逻辑结构

定义数据关系

内模式（物理级）

一个数据只有一个内模式

记录存储模式

DBMS提供内模式定义语言

数据库二级映像

外模式／模式映像

确定局部逻辑结构与全局逻辑结构的关系

模式／内模式映像

确定全局逻辑结构与存储结构之间的对应关系

数据库管理系统（DBMS）

DBMS组成

语言编译处理程序

数据定义语言（DDL）编译程序

源模式编译为数据库结构信息描述

数据操纵语言（DML）编译程序

转换成可执行的增删改查

系统运行控制程序

系统总控制程序

控制协调各程序的活动

安全性控制程序

权限管理

完整性控制程序

完整性约束

并发控制程序

协调多应用、多用户

数据存取更新

对数据的增删改查操作

系统建立、维护程序

装配程序

初始数据库的数据装入

重组程序

数据库性能降低时重组

系统恢复程序

恢复到以前的某个正确状态

数据字典

描述数据库中有关信息的数据目录，如数据库三级模式、数据类型、用户名、用户权限等有关数据库系统的信息

DBMS数据存取

用户使用特定操作语言向DBMS发出存取请求

DBMS将请求转换成代码指令

DBMS检查三级模式和二级映像

DBMS对数据库执行存取操作

DBMS接收存取操作结果

DBMS数据结果进行必要的处理（如格式转换）

DBMS将处理结果返回给用户

数据模型

三个世界

现实世界

客观描述

事物总体、事物个体、特征、事物联系

信息世界

现实世界在人脑中的反映

实体（一辆汽车）

实体型（汽车）

实体集（所有汽车）

属性

实体具有的某一特性

键

能唯一标识实体的属性

域

属性取值范围

联系

1对1

1对n

n对n

计算机世界

字段

标记实体属性的命名单位

记录

字段的有序集合

文件

同一类记录的集合

关键字

唯一标识文件中记录的字段

实体联系模型（概念模式）

基本组成

实体型（矩形框）

属性（椭圆框）

联系（菱形框）

联系类型

两个实体间的联系

三个及三个以上实体间的联系

同一实体集内部各实体间的联系

数据模型

层次模型（树）

特点

每棵树有且仅有一个节点没有双亲，此为根节点

根以外的其他节点有且仅有一个双亲节点

父子节点之间的联系是1对n关系

数据操纵与完整型约束

主要操纵：增删改查

插入子节点必须要有双亲节点

删除双亲节点必须同时删除子节点

修改时必须修改所有相应记录

优缺点

优点

结构简单、层次分明

联系简单，查询效率高

良好的数据完整性支持

缺点

不能表示三个及以上实体型联系

对插入、删除限制太强

查询子节点必须通过双亲节点

网状模型

特点

有一个以上的节点没有双亲节点

允许节点有多个双亲节点

允许两个节点之间有多种联系

数据操纵与完整性约束

主要操纵：增删改查

允许插入未确定双亲节点的子节点

允许只删除双亲节点

修改时只需要更新指定记录

优缺点

优点

更为直接的描述复杂客观世界

良好的性能和存储效率

缺点

数据结构复杂

DDL、DML语言复杂

加重编码应用程序负担（记录间的联系本质是通过存取路径实现，应用程序需要指定存取路径）

关系模型

特点

由表名、表头、表体组成的规范二维表

每个二维表表示关系

基本要素：关系、元组、属性、域、分量、关键字、关系模式、关系实例

数据操纵与完整性约束

主要操纵：增删改查

实体完整性

参照完整性

用户自定义完整性

优缺点

优点

严格的数据支持

结构简单清晰

存取路径透明

缺点

查询效率底

关系数据库

关系模型结构和定义

关系形式化定义

域（值域）：一组具有相同数据类型的值的集合

笛卡尔积：D1xD2x...Dn={(d1,d2,...,dn)|属于Di}

笛卡尔积即为一个二维表、表的框架由域构成，行为一个元组，每列来自同一个域

关系：笛卡尔积的任一子集称为定义在域上的n元关系

关系的性质

列是同质的

每列需要来自同一个域，同一数据类型

不同列可以来自同一域

列的顺序可以任意交换

关系中的元组顺序可任意

关系中不允许存在相同的元组

关系中每一个分量必须是原子的（不可再分）

关系的键与完整性

键

候选键：能唯一标识关系中元组的一个属性或属性集

特点

唯一性

最小性

主关系键（主键）：候选键中用以增删改查的操作变量

主属性：包含在主关系键中的各个属性

非码属性：不包含在任何候选键中的属性

外部关系键：X是A中的一个（组）属性，非A表主键、是B表主键，则X是A的外部关系键

关系完整性

实体完整性

主关系键的值不能为空或部分为空

参照完整性

外部关系键要么为某一个值、要么为空

用户自定义完整性

必须满足针对某一具体关系的约束语义要求（如月份）

关系代数

运算对象

运算结果

运算符

集合运算

并、差、交、笛卡尔积

专门的关系运算符

选取、投影、连接、自然连接、除

算术比较运算符

大于、大于等于、小于、小于等于、等于、不等于

逻辑运算符

与、或、非

三、数据库备份与导入导出的知识点？

导出数据最常用的是直接复制mysql的data文件，这种方式简单粗暴，但是有一些注意事项；

这种方式不支持增量备份，所以复制之前要停掉mysql服务，如果某张表正在被用的话会复制失败。

2，mysql数据库的版本最好要保持一致，不同版本之间可能会出现一些问题，虽然我现在还没遇到。

四、大学数据库知识点总结

大学数据库知识点总结

随着信息时代的到来，数据库管理系统成为了现代社会重要的技术基础。对于计算机科学与技术专业的学生来说，掌握数据库的知识是非常重要的。本文将总结大学数据库课程的重要知识点，帮助学生更好地理解和应用数据库技术。

1. 数据库基础概念

首先，我们来看一些数据库的基础概念。数据库是指按照特定数据模型组织、存储和管理数据的集合，是数据的集合和数据管理系统的集合。其中，数据是描述事物的符号记录，数据模型是对数据的抽象描述，数据管理系统是对数据进行管理和操作的软件工具。

数据库系统由数据库、数据库管理系统和应用程序组成。数据库管理系统是指用于管理和操作数据库的软件系统，它可以提供数据的存储、检索、更新、管理和保护等功能。

2. 关系数据库与SQL

关系数据库是目前应用最广泛的数据库模型，它采用表格的形式存储数据，并通过表格之间的关系进行数据的管理和操作。SQL（Structured Query Language）是结构化查询语言的简称，是关系数据库的标准语言，用于对数据库进行查询和操作。

在学习关系数据库与SQL时，需要了解关系模型、实体以及各种关系之间的联系。同时，还需要掌握SQL语言的基本语法，包括数据查询、数据更新、数据插入、数据删除等操作。通过学习SQL语言，可以实现对数据库的高效管理与操作。

3. 数据库设计与规范化

数据库的设计是数据库开发的重要环节。一个好的数据库设计能够提高数据库的性能和可靠性。在数据库设计中，需要进行实体-关系模型的设计，确定数据库的表结构和字段。同时，还需要考虑数据的完整性约束、范式设计等问题。

数据库的规范化也是数据库设计中的重要内容。规范化是通过一系列的规则将数据库中的数据进行分解，消除冗余数据，提高数据的一致性和准确性。常用的规范化形式包括第一范式（1NF）、第二范式（2NF）和第三范式（3NF）等。

4. 数据库索引与性能优化

数据库索引是提高数据库查询性能的重要手段。在数据库中，索引是根据表的某个列或多个列进行排序的数据结构，可以加快数据的检索速度。学习数据库索引的时候，需要了解索引的原理、不同类型的索引以及索引的创建和使用等。

除了索引的设计，还需要学习数据库的性能优化技术。数据库性能优化是通过合理的配置和调整，提高数据库的运行效率和响应速度。学习数据库性能优化时，可以从查询优化、索引优化、存储优化、系统参数优化等方面入手，不断改进数据库的性能。

5. 数据库安全与备份

数据库安全是保护数据库免受非法访问和恶意攻击的重要措施。在学习数据库安全时，需要了解数据库的各种安全机制，包括用户权限管理、访问控制、数据加密、漏洞修复等。只有做好数据库安全工作，才能保护用户的数据安全。

此外，数据库备份也是数据库管理的重要内容。通过定期备份数据库，可以在数据丢失或损坏时进行恢复。学习数据库备份的时候，需要了解备份策略、备份工具的使用以及备份数据的恢复等。

6. 数据库管理与应用

数据库管理是数据库管理员的主要职责之一。数据库管理员负责数据库的日常管理与维护，包括数据库的安装与配置、数据库的备份与恢复、数据库的性能优化、数据库的监控与故障处理等工作。同时，数据库管理员还需要解决用户的问题和需求，保证数据库的正常运行。

除了数据库管理，数据库在各个领域有着广泛的应用。数据库技术在企业资源管理、客户关系管理、电子商务、物流管理等方面发挥着重要的作用。学习数据库应用时，需要关注数据库在不同领域的具体应用案例，了解数据库技术在实际工程中的应用方法。

总结

通过本文的总结，相信大家对大学数据库课程的重要知识点有了更清晰的了解。掌握数据库的基础概念、关系数据库与SQL、数据库设计与规范化、数据库索引与性能优化、数据库安全与备份以及数据库管理与应用等知识，将为大家未来的学习与工作打下坚实的基础。

五、学生成绩管理系统设计的数据库知识点有哪些？

登陆的话你要学习表单知识，php验证表单提交的数据，以及如何跟数据库进行匹配，注册的话也差不多，首先表单提交内容，php接收内容，最后一点不同的是注册是要写数据库的。所以三个知识点你最起码要懂。

六、如何建立数据库，利用什么软件建立数据库？

啥叫数据库？excel也可以算，access也可以算，mysql也可以算，hbase也可以算，你要数据库干啥，决定了你怎么搭建数据库。

七、数据库设计?

本文档明确数据库设计原则和规范，规范数据库对象命名方式，见名知意，强化分工，保证数据库高效稳定运行

1 数据库设计原则

1) 充分考虑业务逻辑和数据分离，数据库只作为一个保证ACID特性的关系数据的持久化存储系统，尽量减少使用自定义函数、存储过程和视图，不用触发器。

2) 充分考虑数据库整体安全设计，数据库管理和使用人员权限分离。

3) 充分考虑具体数据对象的访问频度及性能需求，结合主机、存储等需求，做好数据库性能设计。

4) 充分考虑数据增长模型，决策是否采用“分布式（水平拆分或者垂直拆分）”模式。

5) 充分考虑业务数据安全等级，设计合适的备份和恢复策略。

2 设计规范

2.1 约定

1) 一般情况下设计遵守数据的设计规范3NF，尽量减少非标准范式或者反模式使用。

3NF规定：

Ø 表内的每一个值都只能被表达一次。

Ø 表内的每一行都应该被唯一的标识（有唯一键）。

Ø 表内不应该存储依赖于其他键的非键信息。

常见关键字（不得直接作为相关命名）：range、match、delayed、select、and、from、where、not、in、out、add、as、user、name、key、index、type、group、order、max、min、count、concat、by、desc、asc、null等等，更多请参考 MySQL 官方保留字。

2) 数据库和表的字符集统一：字符集（utf8mb4），排序规则（utf8mb4_general_ci）

2.2 表设计规范

1) 应该根据系统架构中的组件划分，针对每个组件所处理的业务进行组件单元的数据库设计；不同组件间所对应的数据库表之间的关联应尽可能减少，确保组件对应的表之间的独立性，为系统或表结构的重构提供可能性。

2) 采用领域模型驱动的方式和自顶向下的思路进行数据库设计，首先分析系统业务，根据职责定义对象。对象要符合封装的特性，确保与职责相关的数据项被定义在一个对象之内，不会出现职责描述缺失或多余。

3) 应针对所有表的主键和外键建立索引，有针对性地建立组合属性的索引。

4) 尽量少采用存储过程。

5) 设计出的表要具有较好的使用性。

6) 设计出的表要尽可能减少数据冗余，确保数据的准确性。

2.3 字段规范

1) 一行记录必须表内唯一，表必须有主键。

2) 如果数据库类型为MYSQL ，应尽量以自增INT类型为主键。如果数据库类型为ORACLE，建议使用UUID为主键。

3) 日期字段，如需要按照时间进行KEY分区或者子分区，则使用VARCHAR2类型存储，存储格式为：YYYYMMDD 。如若不需要以KEY形式作为分区列，则使用DATE或者DATETIME类型存储。不建议使用时间戳存储时间。

4) 字段名称和字段数据类型对应，如DATE命名字段，则存储时间精确到日，如TIME命名字段，则存储时间精确到时分秒，甚至毫秒。

2.4 命名规范类

2.4.1 约定

1) 数据库对象命名清晰，尽量做到见名知意，在进行数据库建模时备注对象，便于他人理解。

2) 数据库类型为MYSQL,采用全小写英文单词

3) 数据库类型为ORACLE，则使用驼峰式命名规范

4) 数据库对象命名长度不能超过30个字符

3 管理范围

管理数据库中所有对象，包括库，表，视图，索引，过程，自定义函数，包，序列，触发器等

3.1 建库

1) 数据库名：采用小写英文单词简拼或汉字小写拼音,多个单词或拼音采用下划线"_"连接

2) 数据库编码规则及排序规则：字符集（utf8mb4），排序规则（utf8mb4_general_ci）

3) 建库其他要求：库名与应用名称尽量一致

3.2 建表

表名应使用名词性质小写英文单词。如果需要单词词组来进行概括，单词与单词之间使用英文半角输入状态下_连接。如果超长，则从前面单词开始截取，保留单词前三位，保留完整的最后一个单词，如果依然超长，则保留前面单词首字母，直接和最后一个单词连接；临时表命名以TMP开头，命名格式为TMP_模块/用途名称_名字拼音首字母；表名不能直接采用关键字命名

1) 表命名：采用“业务名称_表的作用”格式命名（例如：alipay_task / force_project / trade_config）

2) 建表其他要求：表名长度不能超过30个字符；一定要指定一个主键字段；必须要根据业务对表注释；如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释；

3) 表必备字段：

`is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '状态（1删除、0未删除）',

`is_enabled` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态（1启用、0作废）',

`op_first` varchar(50) DEFAULT NULL COMMENT '创建人',

`op_first_time` datetime DEFAULT NULL COMMENT '创建时间',

`op_last` varchar(50) DEFAULT NULL COMMENT '更新人',

`op_last_time` datetime DEFAULT NULL COMMENT '更新时间',

3.3 建字段

1) 字段命名：

表中标识唯一性字段必须以标识性简称+id命名。其余字段根据存储信息，使用名词性质英文单词表示，如需要单词词组来进行概括，单词与单词之间使用英文半角输入状态下_连接。外键引用字段使用外键表_id的形式命名；字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字；表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint；表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除

2) 字段类型、长度

如果存储的字符串长度几乎相等，使用 char 定长字符串类型；小数类型为 decimal；id 必为主键，类型为 bigint unsigned；应尽量以自增INT类型为主键；优先选择符合存储需要的最小的数据类型；将字符串转化为数字类型存储；对于非负数据采用无符号整形进行存储signed int -2147483648-2147483648，unsigned int 0-2147483648，有符号比无符号多出一倍的存储空间；varchar(n) n代表字符数，不是字节数，varchar(255)=765个字节，过大的长度会消耗更多的内存；避免使用text\BLOB数据类型，建议text\BLOB列分离到单独的扩展表中，text\BLOB类型只能使用前缀索引；避免使用enum数据类型，修改enum需要使用alter语句，enum类型的order by操作效率低，需要额外操作，禁止使用数值作为enum的枚举值；尽可能把所有列定义为not null，索引null列需要额外的空间来保存，所以要占用更多的空间，进行比较和计算时要对null值做特别的处理；禁止字符串存储日期型的数据，缺点1：无法用日期函数进行计算和比较，缺点2：用字符串存储日期要占用更多的空间；使用timestamp或datetime类型存储时间，timestamp存储空间更小；财务的相关金额使用decimal类型，decimal类型为精准浮点数，在计算时不会丢失精度，float、double非精准浮点数

3) 字段其他要求

字段名称长度不能超过30个字符、尽量减少或者不使用联合主键、字段尽可能不允许为null(为null时设定默认值)、文本类型字段，属性 字符集（utf8mb4），排序规则（utf8mb4_general_ci）、字段必须根据业务进行注释。

3.4 建索引

主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。

说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。

3.5 创建数据库表视图

1) 视图命名：以"v_项目名/模块名_用途"格式命名

2) 视图其他要求：视图名称长度不能超过30个字符

3.6 建存储过程及自定义数据库函数

1) 存储过程命名：以"sp_用途"格式命名

2) 自定义数据库函数：以“fn_用途”格式命名

3) 存储过程或自定义数据库函数：参数命名以“p_”开头命名；内部变量命名以“v_”开头命名；游标命名以“cur_loop_”开头命名；循环变量命名以“i_found_”开头命名。

3.7 建数据库用户

用户命名：采用授权用户姓名全拼小写命名

3.8 其他要求

1) 查询大数据表，参数字段需建索引；

2) 数据库表、字段删除或变更操作（a-不需要的表或字段，一般备注“作废”即可;b-需要修改的表或字段，先备注作废原表或原字段，再创建新表或新字段，且备注好作废原因。）；

八、there is知识点？

There is开头的陈述句句型结构：

There is是There be句型中的单数形式。There is+可数名词单数/不可数名词+地点或位置。表示某地或某处有某物。

There isn't+any+可数名词复数/不可数名词+地点或位置。表示某地或某处没有某物。

例：

There is a strawberry on the top of the cake.

蛋糕上有一个草莓。

There is some juice in the cup.

杯子里有一些果汁。

There isn't any people in the living room.

客厅里没有人。

九、数据库类型有哪些，目前主流数据库是哪种？

关系型数据库，非关系型数据库（NoSQL），键值（Key-value）数据库。主流的数据库那就是关系型数据库了，特别是关系型数据库中的分布式数据库。墨天轮最新排名（2022.11）数据库前十榜单中关系型数据库占了1-9名，前二十榜单中也仅有两个非关系型数据库。关系型数据库之所以占了绝大部分数据库份额，是因为关系型数据库作为成熟的数据库技术理念，其精髓的范式设计，严谨的一致性，原子性，完整性等优势是无法被取代的。

AntDB在运营商深耕了十几年，覆盖了OLTP与OLAP场景，是非常典型的HTAP类型的关系型数据库，业务覆盖计费、CRM等核心交易，同时覆盖清算分析等分析型业务。比如AntDB数据库服务于中国电信某省计费系统上云，包含数据层、批价和出账流程等大规模业务。在系统设计上，将资源、资产等交易热数据迁移到AntDB数据库，极大地提高了业务关键数据的访问效率，整体提高了话单事务的处理性能。AntDB数据库支撑10亿用户的通信交易场景，进行在线交易与数据分析处理的HTAP混合负载，帮助客户解决核心系统解决海量数据管理难题，基于分布式的架构设计，实现了在线弹性伸缩、强一致性事务、跨机房高可用等能力。

十、数据库设计 案例?

以下是一个简单的MySQL数据库设计案例，以存储学生和课程信息为例：

假设我们有两个实体：学生（Student）和课程（Course），每个学生可以选择多个课程，每个课程可以被多个学生选择。

首先，我们创建两个表来表示学生和课程：

Student表

Course表

接下来，我们需要创建一个关联表来存储学生和课程之间的关系，表示学生选择了哪些课程：

Student_Course表

在Student_Course表中，student_id和course_id列分别作为外键，关联到Student表和Course表的主键。

这种设计模式称为"多对多"关系，通过使用关联表来实现学生和课程之间的多对多关系。

通过以上的数据库设计，你可以存储和查询学生、课程以及学生选择的课程的信息。当然，具体的数据库设计取决于你的实际需求和业务规则，上述仅提供了一个简单的示例。