数据库知识点总结（1）

一、数据库

(一)简介

长期存在计算机内，有组织、可共享的、大量数据的集合。数据是按照特定的数据模型来组织、存储在数据库中的。

(二)优点

1）持久化数据到本地 2）可以实现结构化查询，方便管理

(三)相关概念：

1.DB（database）

数据库，保存一组有组织的数据的容器

2.DBMS(Database Management System)

数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据；
MySQL、Oracle、DB2、SqlServer等

分类
1)基于共享文件系统的DBMS——Access

2）基于客户机——服务器
MySQL、Oracle、DB2、SqlServer等

3.SQL

概念：结构化查询语言，用于和数据库通信的语言

优点
1）所有DBMS都支持
2）简单易学

语言分类
1）DML(Data Manipulate Language)
数据操纵语言，用于添加、删除、修改数据库记录，并检查数据完整性；
关键字：insert, delete, update 等；

2）DDL（Data Define Languge）
数据定义语言，用于库和表的创建、修改、删除；
关键字：create, drop,alter等；

3）TCL（Data Control Language）
数据事务语言

4）DQL（Data Query Language）
数据查询语言，用来查询数据库中表的记录(数据)；
关键字：select, where 等；

4.DBA——数据库管理员

二、MySQL

一款安全、跨平台、高效的，并与 PHP、Java 等主流编程语言紧密结合的数据库系统。该数据库系统是由瑞典的 MySQL AB 公司开发、发布并支持，由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的。

(一) 服务

启动——net start mysql
停止——net stop mysql

方式一：通过MySQL自带的客户端
​只限于root用户

​方式二：通过cmd命令提示行关闭，未配置MySQL环境变量，需在MySQL安装的bin下启动cmd
例如：
(1)登录
mysql 【-h主机名 -P端口号 】-u用户名 -p密码

(2)退出
exit或ctrl+C

(二)语法规范

1）不区分大小写
2）使用“；”结尾
3）各子句一般分行写
4）关键字不能缩写也不能分行
5）合理使用缩进
6）注释
单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */

(三)优点

1）开源、免费、成本低
2）性能高，移植性好
3）体积小，便于安装

三、DQL(数据查询)语言

(一)简单查询

查询表中所有记录——select * from 表名;

基础查询

1.多个字段的查询——select 字段名1，字段名2…from 表名

2.去除重复——distinct

3.计算列
一般可以使用四则运算计算一些列的值(数值型计算)
ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
表达式1：哪个字段需要判断是否为null
如果该字段为null后的替换值

4.起别名
as (可以省略)

(二)条件查询

where字句后跟条件

运算符
1.> 、< 、<= 、>= 、= 、<>

2.BETWEEN…AND

3.IN( 集合)

4.LIKE：模糊查询
占位符 _单个任意字符 %多个任意字符

5.IS NULL： 可以判断null值，不能判断普通类型的数值

6.ifnull：不为null返回原本的值

7.and 或 ||

8.or 或 ||

9.not 或 !

10.<=>
安全等于，但可读性较差
既可判断null值，也可判断数值

(三)排序查询

1.语法
select
要查询的东西
from
表
where
条件
order by 排序的字段|表达式|函数|别名 【asc|desc】

2.排序方式
ASC：升序，默认
DESC：降序

3.排序分类
单个字段
多个字段
表达式
别名
函数

4.注意
如果有多个排序条件，则当之前的条件值一样时，才会判断第二条件
默认是升序

5.常见函数

(四)分组查询

语法：
select 查询的字段、分组函数
from 表
group by 分组的字段

特点：
(1)可以按单个字段分组
(2)和分组函数一同查询的字段最好是分组后的字段
(3)可以按多个字段分组，字段之间用逗号隔开
(4)可以支持排序
(5)having后可以支持别名

where和having的区别
where在分组之前进行限定，如果不满足条件，则不参与分组。
having在分组之后进行限定，如果不满足结果，则不会被查询出来

where后不可以跟聚合函数，having可以进行聚合函数的判断

分组函数(聚合函数)

(五)多表连接查询

功能分类

1.内连接

(1)等值连接
结果为交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般为表起别名
可以搭配前面所有子句

(2)非等值连接

(3)自连接

2.外连接
(1)左外连接
(2)右外连接
(3)全外连接

3.交叉连接

笛卡尔积

产生条件：
省略连接条件
连接条件无效
所有表中所有行互相连接

解决方案：
添加有效筛选条件

内连接

SQL92语法
select 查询列表
from 表名1 别名1,表名2 别名2
where 连接条件
and 筛选条件
group by 分组列表
having 分组后筛选条件
order by 排序列表

SQL99语法
select 字段列表
from 表名1
[inner] join 表名2 on 条件
where 筛选条件
group by 分组条件
having 分组后的筛选条件
order by 排序字段

注意
使用表名前缀在多个表中区分相同的列
在不同表中具有相同列名的列可以在用表的别名加以区分
如果使用了表别名，则在select语句中需要使用表别名代替表名
表别名最多支持32个字符长度，但建议越少越好

子查询
概念
查询中嵌套查询，称嵌套查询为子查询

特点
子查询都放在小括号内
子查询可以放在from、select、where、having后面，但一般放在条件右侧
子查询优先于主查询执行，主查询使用了子查询的执行结果

根据查询结果的行数不同分为以下两类
1.单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空

2.多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替

3.行子查询

4.表子查询

位置
select 仅支持单行子查询
from 表子查询
where 或 having 单行和多行子查询
exists 都可以

分页查询

语法：

select 字段|表达式
from 表
where 条件
group by 分组字段
having 条件
order by 排序字段
limit 起始的条目索引，条目数

例：
SELECT * FROM student LIMIT 0,3; – 第1页
SELECT * FROM student LIMIT 3,3; – 第2页
SELECT * FROM student LIMIT 6,3; – 第3页

特点：
起始条目索引从0开始
limit子句放在查询语句的最后
公式：select * from 表 limit (page-1) *sizePerPage,sizePerPage
每页显示条目数sizePerpage
要显示的页数page

联合查询
语法
select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
…
select 字段|常量|表达式|函数 from 表 where 条件

特点
(1)多条查询语句的查询的列数必须是一致的
(2)多条查询语句的查询的列的类型几乎相同
(3)union代表去重，union all代表不去重

多条查询语句的结果合并成一个结果

四、DML语言(数据操作语言)

添加语句create 数据
语法：
insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);

注意：
列名和值要一一对应；
如果表名后不定义列名，则默认给所有列添加值 例如insert into 表名 values(值1,值2,…值n)；
除了数字类型，其他类型需要使用引号(单双都可以)引起来；

修改数据
语法
(1)修改单表
update 表名 set 列名1 = 值1, 列名2 = 值2,… [where 条件];

(2)修改多表
92语法
update 表1 别名1,表2 别名2 set 字段=新值，字段=新值 where 连接条件 and 筛选条件
99语法
update 表1 别名 INNER|LEFT|RIGHT join 表2 别名 on 连接条件 set 列=值 where 筛选条件

注意：如果不加任何条件，则会将表中所有记录全部修改

删除数据
语法
(1)单表删除
delete from 表名 where 筛选条件

(2)多表删除
92
delete 表1 别名1,表2 别名2 from 表1 别名1,表2 别名2 where 连接条件 and 筛选条件

99
delete 表1 别名1,表2 别名2 from 表1 别名1,表2 别名2 INNER|LEFT|RIGHT join 表2 别名 on 连接条件 where 筛选条件

注意
(1) 如果不加条件，则删除表中所有记录。
(2)如果要删除所有记录
delete from 表名; 不推荐使用。有多少条记录就会执行多少次删除操作
TRUNCATE TABLE 表名; 推荐使用，效率更高 先删除表，然后再创建一张一样的表。

delete与TRUNCATE区别
truncate不能加where条件，而delete可以加where条件;
truncate的效率高;
truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始;
delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始;
truncate删除不能回滚，delete删除可以回滚;

五、DDL语言(数据定义语言)

(一)数据库的CRUD

增(create)、删(delete)、改(update)、查(Retrieve)

创建数据库
create database 数据库名称;

创建数据库，判断不存在，再创建
create database if not exists 数据库名称;

创建数据库，并指定字符集
create database 数据库名称 character set 字符集名;
create database mydb2 character set utf8;

库查询
查询所有数据库的名称
show databases;

查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;

mysql默认语言集不是utf8，每次在创建数据库的时候应指定字符集。Oracle是在安装时，即指定了字符集。

查询当前正在使用的数据库名称
select database();

库的修改
rename database books to 新库名;

修改数据库的字符集
alter database 数据库名称 character set 字符集名称;

库的删除
删除数据库
drop database 数据库名称;

判断数据库存在，存在再删除
drop database if exists 数据库名称;

使用数据库
use 数据库名称;

(二)数据表的CRUD

表的创建
create table 表名(
列名1 数据类型1 【字段约束】,
列名2 数据类型2 【字段约束】,
…
列名n 数据类型n 【字段约束】
);

最后一列，不需要加逗号（,）

在mysql中对表操作前，必须先选择所使用的数据库。

表的查询

查看所有的表
show tables;

查看表结构
desc t1;

查看指定表的创建语句
show create table employee;
mysql表名称区分大小写

表的修改

修改表名
alter table 表名 rename to 新的表名;
rename table employee to worker;

修改表的字符集
alter table 表名 character set 字符集名称;

增加一个字段
alter table employee add column height double;

添加一列
alter table 表名 add 列名 数据类型;

修改列名称 类型
alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;

表的删除
drop table 表名;
drop table if exists 表名;

表的复制
复制表的结构
create table 新表 like 旧表；

复制表的结构和数据
create table 新表 select * from 旧表；

只复制部分数据
create table 新表 select 字段1,字段2… from 旧表 where 筛选条件

复制某些字段
create table 新表 select 字段1,字段2… from 旧表 where 不可满足条件(例如0或者1=2)

六、表设计

(一)数据库引擎

概念
在mysql中的数据用各种不同的技术存储在文件（或内存）中
数据库底层软件组织，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是插件式存储引擎。

操作
show engines: 查看mysql支持的存储引擎

常用的存储引擎
1.MyISAM
MySQL 5.1及之前版本的默认引擎

2.InnoDB
MySQL 5.5后成为默认索引

mvcc全称是multi version concurrent control（多版本并发控制）。mysql把每个=操作都定义成一个事务，每开启一个事务，系统的事务版本号自动递增。每行记录都有两个隐藏列：创建版本号和删除版本号

面试题：MyISAM 与 InnoDB 区别？

表结构设计

1.范式设计，消除冗余

数据库范式:确保数据库结构合理，满足各种查询需要、避免数据库操作异常的数据库设计方式。满足范式要求的表，称为规范化表，范式产生于20世纪70年代初，一般表设计满足前三范式就可以，在这里简单介绍一下前三范式

-第一范式
属性(字段)的原子性约束，要求属性具有原子性，不可再分割

第二范式
记录的唯一性约束，要求记录有唯一标识，每条记录需要有一个属性来做为实体的唯一标识

第三范式
属性(字段)的冗余性约束，即任何字段不能由其他字段派生出来，也就是主键没有直接关系的数据列必须消除——再创建一个表来存放他们，外键除外

2.反范式设计，适当冗余

数据库设计的实用原则——在数据冗余和处理速度之间找到合适的平衡点，满足范式的表一定是规范化的表，但不一定是最佳的设计。
为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。
比如我们有一个表，产品名称，单价，库存量，总价值。这个表是不满足第三范式的，因为“总价值”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“总价值”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。
合理的冗余可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

主键
根据第二范式，需要有一个字段去标识这条记录，主键是最好的标识，满足唯一性、非空性，但是很多表也不一定需要主键，但是对于数据量大，查询频繁的数据库表，一定要有主键
优点
增加效率
防止重复

选择数据类型
1.更小的通常更好
2.简单就好
3.尽量避免NULL
4.数据类型尽量用数字型，数字型的比较 比 字符型的快很多

七、MySQL常用数据类型

类型简单为主，保存数值类型越小越好

整型

特点

小数

浮点数
float(4)
double(8)

定点数
DEC(M,D)
DECIMAL(M,D) (M+2)
M:整数部位+小数部位
D:小数部位

特点

字符类型
较短文本

较长文本
text
blob 二进制

enum

Set ——一次可选多个成员

日期型

八、约束

作用
对表中的数据进行限定，保证数据的正确性、有效性和完整性

分类

非空约束
not null，某一列的值不能为null

创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL – name为非空
);

创建表完后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);

SQL中的null不区分大小写，所有数据类型的值都可以是null，包括int、float、boolean等数据类型，空字符串不等于null，0也不等于null，并且null不等于null,所以在唯一约束里允许多个null值。

唯一约束
unique，某一列的值不能重复

唯一约束可以有NULL值，但是只能有一条记录为null

同一个表内可建多个唯一约束，唯一约束也可由多列组合而成。

当为某列创建唯一约束时，MySQL会为该列相应地创建唯一索引。如果不给唯一约束起名，该唯一约束默认与列名相同

在创建表时，添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE – 手机号
);

删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;

在表创建完后，添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
ALTER TABLE stu ADD UNIQUE(列名1，列名2); – 2.添加组合约束

注意
唯一约束限定的列的值可以有多个null，创建组合约束

主键约束
primary key

注意
1.非空且唯一
2.一张表只能有一个字段为主键
3.主键就是表中记录的唯一标识

在创建表时，添加主键约束
CREATE TABLE stu(
id INT PRIMARY KEY, – 1.列级主键约束语法
name VARCHAR(20),
PRIMARY KEY(id), – 2.表级主键约束语法
PRIMARY KEY(id,name), – 3.表级组合主键约束语法
CONSTRSINT 别名 PRIMARY KEY(id,name) – 4.表级组合主键约束语法
);

删除主键
– 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;

创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY; – 1.列级语法添加单列主键约束
ALTER TABLE stu ADD PRIMARY KEY(id,name); – 2.表级语法添加组合主键约束

自动增长(标识列)

1.如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

2.在创建表时，添加主键约束，并且完成主键自增长
create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);

3.一个表至多有一个自增长列

4.只支持数值型

5.若要更改起始值，手动插入值

6.若要更改步长，更改系统变量
set auto_increment = 值

7.修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment

8.删除自增长列
alter table 表 modify column 字段名 字段类型 约束

9.删除自动增长
ALTER TABLE stu MODIFY id INT;

10.添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

外键约束
foreign key，让表与表产生关系，从而保证数据的正确性。

用于定义两个实体之间的一对多、一对一的关联关系。对于一对多的关联关系，通常在多的一端增加外键列。

在创建表时，可以添加外键
语法：
create table 表名(
…
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

创建表之后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

级联操作
当主表的记录被从表记录参照时，主表记录不允许被删除，必须先把从表里参照该记录的所有记录全部删除后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录。
如果想定义当删除主表记录时，从表记录也会随之删除，则需要在建立外键约束后添加on delete cascade或添加on delete set null。
删除主表记录时，把参照该主表记录的从表记录全部级联删除；
指定删除主表记录时，把参照该主表记录的从表记录的外键设为null；

级联更新
如果想定义当更新主表记录时，从表记录也会随之更新，则需要定义级联更新
ON UPDATE CASCADE
ON UPDATE SET NULL

级联删除
ON DELETE CASCADE

级联置空
ON DELETE SET NULL

添加级联操作
语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;

特点
从表设置外键关系
从表的外键列和主表的关联列类型一致
主表关联列必须是一个key(主键或唯一)
插入数据，先主后从；删除数据，先从后主

默认约束
default，用户保证该字段有默认值

添加默认
alter table 表名 modify 字段名 字段类型 default 值;

删除默认
alter table 表名 modify 字段名 字段类型 ;
alter table 表名 modify 字段名 字段类型 DEFAULT NULL;

检查约束
check，比如年龄，性别

添加约束的时机

1.建表的同时为相应的数据列指定约束。

2.建表后创建，以修改表的方式来增加约束。
添加列级约束——alter table 表名 modify column 字段名 字段类型 新约束；
添加表级约束——alter table 表名 add [ constraint 约束名] 约束类型 (字段名) [外键的引用]

添加分类

1.列级约束
语法上支持六大约束，但外键没有效果

2.表级约束
除了非空、默认，其他的都支持

主键和唯一的对比

都能保证唯一性，但主键不允许为空，唯一允许为空
一个表中至多有一个主键，唯一可有多个
都允许组合，但不推荐