数据库基础知识整理

##数据库(DataBase)

基本概念
​ 用于存储和管理数据的仓库
特点
​ 1.持久化存储数据，其实数据库就是一个文件系统
​ 2.方便存储和管理数据
​ 3.使用了统一的方式操作数据库 --SQL
MySQL服务启动
​ 1.cmd --> services.msc 打开服务的窗口
​ 2.使用管理员打开cmd

net start mysql57 启动mysql的服务
net stop mysql57 关闭mysql的服务

MySQL登陆

1.mysql -uroot -p密码
2.mysql -uroot -p
3.mysql -hip地址 -uroot -p连接目标的密码
4.mysql --host=ip地址(例127.0.0.1) --user=root --password=密码

MySQL退出

1.exit
2.quit

MySQL目录结构
​ MySQL安装目录
​ 配置文件 my.ini
​ MySQL数据目录
​ 几个概念：数据库：文件夹
​ 表：文件
​ 数据：数据

#SQL

​ 1.Structured Query Language：结构化查询语言
​ 其实就是定义了操作所有关系型数据库的规则，每一种数据库操作的方式存在不一样的地方，称为"方言"
​ 2.SQL通用语法
​ *SQL语句可以单行或多行书写，以分号结尾
​ *可使用空格和缩进来增强语句的可读性
​ *MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
​ 3种注释：
​ 单行注释：-- 注释内容或# 注释内容(mysql特有)
​ 多行注释：/ 注释 */
​ 3.SQL分类
​ 1）DDL(Data Definition Language) 数据定义语言
​ 用来定义数据库对象：数据库，表，列等。关键字：create，drop，alter等；
​ 2）DML(Data Manipulation Language)数据操作语言
​ 用来对数据库中表的数据进行增删改。关键字：insert，delete，update等；
​ 3）DQL(Data Query Language)数据查询语言
​ 用来查询数据库中表的记录( 数据)。关键字：select，where等；
​ 4）DCL(Data Control Language)数据控制语言(了解)
​ 用来定义数据库的访问权限的安全级别，及创建用户。关键字：GRANT,REVOKE等。

DDL

操作数据库，表

操作数据库：CRUD

1）C(Create):创建

​ 创建数据库

create database 数据库名称;

​ 创建数据库，判断不存在，再创建

create database if not exists 数据库名称;

​ 创建数据库，并指定字符集

create database 数据库名称 character set 字符集名;

​ 练习：创建数据库，判断是否存在，并指定字符集为gbk

create database if not exists db4 character set gbk;

2）R(Retrieve)：查询

​ 查询所有数据库的名称：

show databases;

​ 查询某个数据库的字符集:查询某个数据库的创建语句

show create database 数据库名称;

3）U(Uptate)：修改

​ 修改数据库的字符集

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

4）D(Delete)：删除

​ 删除数据库

drop database 数据库名称;

​ 判断数据库存在，再删除

drop database if exists 数据库名称;

5）使用数据库

​ 查询当前正在使用的数据库名称

select database();

​ 使用数据库

use 数据库名称;

操作表

1）C(Create):创建

​ 语法

create table 表名称(列名1 数据类型1,列名2 数据类型2...列名n 数据类型n);

​ 注意最后一列不需要加逗号
​ 数据库类型
​ 1.int：整数类型
​ age int,
​ 2.double:小数类型
​ score double(5,2)(小数最多有5位，小数点后面保留两位)
​ 3.date：日期，只包含年月日yyyy-MM-dd
​ 4.datetime：日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss
​ 5.timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
​ 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间来自动赋值
​ 6.varchar：字符串
​ name varchar(20)：姓名最大20个字符 zhangsan 8个字符 张三 2个字符

创建表

create table student(
	id int,
    name varchar(32),
    age int,
    score double(4,1),
    birthday date,
    insert_time timestamp
);

复制表

create table 表名 like 被复制的表名; 

2）R(Retrieve)：查询

​ 查询某个数据库中所有的表名称

show tables;

​ 查询表结构

desc 表名称;

3）U(Uptate)：修改

​ 修改表名

alter table 表名 rename to 新的表名;

​ 修改表的字符集

alter table 表名 character set 字符集;

​ 添加一列

alter table 表名 add 列名 数据类型;

​ 修改列名称和类型

alter table 表名 change  列名 新列名 新数据类型();		

​ 修改列的数据类型

alter table 表名 modify  列名 新数据类型();

​ 删除列

alter table 表名 drop 列名;

4）D(Delete)：删除

​ 删除表

drop table 表名称;

​ 判断表是否存在再删除

drop table if exists 表名称;

DML

​ 增删改表中数据

1）添加数据

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

​ 注意：1.列名和值要一一对应
​ 2.如果表名后不定义列名则默认给所有列添加值
​ 3.除了数字类型，其他类型需要使用引号(单双皆可)引起来

2）删除数据

delete from 表名 where 条件;  -- 注意：如果不加条件则删除表中所有记录

​ 如果要删除所有记录

1.delete from 表名; -- 不推荐使用，有多少条记录就会执行多少次删除操作。
2.truncate table 表名； -- 推荐使用，效率更高  先删除表，然后再创建一张一模一样的的表

3）修改数据

update 表名 set 列名1 = 值1,列名2 = 值2, ... where 条件;

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

DQL

​ 查询表中的记录

语法

select	字段列表	
from	表名列表	
where	条件列表	
group by	分组字段	
having	分组之后的条件	
order by	排序
limit	分页限定;

基础查询

1）多个字段的查询

select	字段名1，字段名2 ... from 表名; -- 如果查询所有字段，则可以使用*来代替字段列表;

2）去除重复
​ distint
3）计算列
​ 一般可以使用四则运算计算一些列的值，(一般只会进行数值型的计算)（null参与的运算，计算结果都为null）

ifnull(表达式1，表达式2) -- 表达式1：需要判断是否为null的字段; 表达式2：表达式1为null的替换值

4）起别名
​ as：as也可以省略
3）条件查询
​ where子句后跟条件
​ 运算符
​ < <= >= = <>
​ BETWEEN …AND
​ IN(集合)
​ LIKE：模糊查询
​ 占位符：
​ _ 单个任意字符
​ %：多个任意字符

SELECT * FROM stu WHERE name LIKE '马%'; -- 查询姓马的人；

SELECE * FROM stu WHERE name LIKE "_化%"; -- 查询名字第二个字是化的人；

SELECT * FROM stu WHERE name LIKE '%马%';  -- 查询姓名中包含马的人；

IS NULL
​ and 或 &&
​ or 或 ||
​ not 或 ！

SELECT * FROM stu WHERE age BETWEEN 20 AND 50;-- 查询年龄在20到50之间；

SELECT * FROM stu WHERE english IS NOT NULL; -- 查询英语成绩不为空；

1.排序查询

order by 排序字段1 排序方式1,排序字段1 排序方式1,...; -- 如果有多个排序条件，则当前边的条件值一样时，才会判断后面的条件

​ 排序方式：

SELECT * FROM stu ORDER BY math DESC; -- 按照数学成绩降序排列

SELECT * FROM stu ORDER BY math DESC,english DESC; -- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名（降序）

ASC：升序（默认） DESC：降序

2.聚合函数

​ 将一列数据作为一个整体，进行纵向的计算
count：计算个数

SELECT COUNT(id) FROM stu; -- 查询id的个数

​ 一般选择非空的列：主键
​ count(*)
max：计算最大值

SELECT MAX(math) FROM stu; -- 查询数学成绩的最大值

min：计算最小值
sum：计算和

SELECT SUM(math) FROM stu; -- 数学成绩的和

avg：计算平均值
​ 聚合函数的计算排除null值

3.分组查询

语法：group by 分组字段;
注意
​ 1.分组之后查询的字段：分组字段，聚合函数
​ 2.where 和 having的区别：
​ 1）where在分组之前进行限定，不满足条件则不参与分组。having在分组之后进行限定，如果不满足结果则不会被显示出来
​ 2）where后不可以跟聚合函数，having可以进行聚合函数的判断

 SELECT sex,AVG(math) from stu GROUP BY SEX;-- 按照性别分组，分别查询男，女同学数学的平均分

 SELECT sex,AVG(math) from stu WHERE math > 70 GROUP BY SEX;-- 按照性别分组，分别查询男，女同学数学的平均分，要求分数低于70分的人不参与分组

4.分页查询

语法：limit 开始的索引，每页查询的条数;
公式：开始的索引 = （当前的页码 - 1） 每页显示的条数；*

SELECT * FROM stu LIMIT 0,3; -- 第一页
SELECT * FROM stu LIMIT 3,3; -- 第二页
SELECT * FROM stu LIMIT 6,3; -- 第三页

limit是一个MySQL“方言”

DCL

1.用户管理

1.添加用户：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

2.删除用户：

DROP USER '用户名'@'主机名';

3.修改用户密码：

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';  -- 第一种方式
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码'); -- 第二种方式

4.查询用户：

USE mysql；
SELECT * FROM USER;

​ mysql忘记了root用户的密码：
​ 1.管理员启动cmd – > net stop mysql停止mysql服务
​ 2.使用无验证方式启动mysql服务：mysqld --skip-grant-tables
​ 3.重新进行无密登陆修改密码
​ 4.手动关闭mysqld的服务进程

2.权限管理

​ 1.查询权限

SHOW GRANTS FOR '用户名'@'主机名';

​ 2.授予权限

 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; -- 授予用户某张表上的权限
 GRANT ALL ON *.* TO '用户名'@'主机名'; -- 授予用户所有的权限

​ 3.撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

#约束

​ 概念：对表中的数据进行限定，保证数据的准确性，有效性和完整性
​ 分类：主键约束：primary key
​ 非空约束：not null
​ 唯一约束：unique
​ 外键约束：foreign key

非空约束：not null

1. 创建表时添加约束：

   CREATE TABLE st(
   	id INT,
   	NAME VARCHAR(20) NOT NULL -- name为非空
   );
   

2. 创建表后添加约束：

   alter table stu modify name varchar(20) not null;
   

3. 删除非空约束

   alter table stu modify name vachar(20);
   

唯一约束：unique

mysql中唯一约束限定的值可以有多个null；
​ 1.创建表时添加唯一约束

CREATE TABLE st(
	id INT,
	phone_number VARCHAR(20) UNIQUE -- phone_number唯一
);

​ 2.创建表后添加唯一约束

alter table stu modify phone_number varchar(20) unique;

3.删除唯一约束

alter table st drop index phone_number;

主键约束：primary key

​ 注意：1.含义：非空且唯一
​ 2.一张表只能有一个字段为主键
​ 3.主键就是表中记录的唯一标识
1.在创建表时添加主键约束

create table st(
		id int primary key, -- 给id添加主键约束
    	name varchar(20)
);

2.创建表后添加主键约束

alter table st modify id int primary key;

3.删除主键约束

alter table st drop primary key;

自动增长

​ 概念：如果某一列是数值类型的，使用 auto_increment可以完成值的自动增长
1）在创建表时添加主键约束并且实现自动增长

create table st(
		id int primary key auto_increment, -- 给id添加主键约束
    	name varchar(20)
);

2)添加自动增长

alter table st modify id int auto_increment;

3）删除自动增长

alter table st modify id int;

外键约束：foreign key

​ 让表与表产生关系，从而保证数据的正确性
​ 1）在创建表时可以添加外键
​ 语法：create table 表名(
​ …
​ 外键列
​ constraint 外键名称 foreign key（外键列名称） references 主表名称（主表列名称）
​ );
​ 2）删除外键

alter table 表名 drop foereign key 外键名;

​ 3)创建表之后，添加外键

alter table 表名 add constraint 外键名称 foreign key（外键列名称）	references	主表名称（主表列名称）;

级联操作

​ 添加级联操作

alter table 表名 add constraint 外键名称 foreign key（外键列名称）	references	主表名称（主表列名称） ON UPDATE CASCADE  ON DELETE CASCADE;

1.分类：

​ 级联更新：ON UPDATE CASCADE
​ 级联删除：ON DELETE CASCADE

#数据库的设计

1.多表之间的关系

​ 1）一对一（了解）
​ **实现：**在任意一方添加唯一外键指向另一方的主键
​ 2）一对多（多对一）
​ **实现：**在多的一方建立外键，指向一的一方的主键
​ 3）多对多
​ **实现：**借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

2.数据库设计的范式

分类
​ 1.第一范式(1NF)：每一列都是不可分割的原子数据项
​ 2.第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
​ 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性值）为该表的码
​ 主属性：码属性组中的所有属性
​ 非主属性：除码属性组中的属性
​ 3.第三范式(3NF)：在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

#数据库的备份与还原

​ 1.命令行：
​ 语法：
​ 备份：mysqldump -uroot -p密码 数据库名称 > 保存的路径
​ 还原 1：sql语句：
​ 1）.登录数据库
​ 2）.创建数据库
​ 3）.使用数据库
​ 4）.执行文件 .source路径
​ 2.图形化工具

#多表查询

使用的表：

CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) 
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10), -- 姓名
	gender CHAR(1), -- 性别
	salary DOUBLE, -- 工资
	join_data DATE, -- 入职日期
	dep_id INT, -- 部门编号
	CONSTRAINT abc FOREIGN KEY (dep_id) REFERENCES dept(id)  -- 外键
);
INSERT INTO emp(NAME,gender,salary,join_data,dep_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_data,dep_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_data,dep_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_data,dep_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_data,dep_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

​ 多表查询的分类：

1.内连接查询：

​ 隐式内连接：使用where条件查询无用的数据

**例如：**查询员工表的名称，性别，部门表的名称

SELECT  t2.`name`,
		t1.`gender`,
		t1.`name`
FROM 
		emp t1,
		dept t2
WHERE 
		t1.`dep_id` = t2.`id`;

​ 显示内连接：
例如：查询所有员工信息和对应的部门信息

SELECT 字段列表 FROM 表名1 [INNER] JOIN 表名2 ON 条件

SELECT * FROM emp INNER JOIN dept ON emp.`dep_id` = dept.`id`;

SELECT * FROM emp JOIN dept ON emp.`dep_id` = dept.`id`;

2.外连接查询：

​ 左外连接：查询左表所有数据以及其交集部分

SELECT 字段列表 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

​ 右外连接：查询右表所有数据以及其交集部分

SELECT 字段列表 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;

3.子查询：

​ 查询中嵌套查询，称嵌套查询为子查询
例如：查询工资最高的员工信息

SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

子查询不同情况

​ 1）子查询的结果是单行单列的：子查询可以作为条件，使用运算符判断:< > =…

SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp); -- 查询工资最高的员工信息

​ 2）子查询的结果是多行单列的：子查询可以作为条件，使用运算符in来判断

SELECT * FROM emp WHERE dep_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部'); -- 查询"财务部"和"市场部"所有的员工信息

​ 3）子查询的结果是多行多列的：子查询可以作为虚拟表参与查询（必须取别名）

SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.`join_data` > '2011-11-11') t2 WHERE t2.dep_id = t1.`id`; -- 查询员工入职日期时2011-11-11日之后的员工信息和部门信息

等价于普通内连接

SELECT * FROM dept t1,emp t2 WHERE t1.`id` = t2.`dep_id` AND t2.`join_data` > '2011-11-11';

#事务

​ 1.事务的基本介绍：
​ 概念：如果一个包含多个步骤的业务操作被事务管理，那么这些操作要么同时成功，要么同时失败
​ 操作：
​ 1）开启事务：start transaction
​ 2）回滚：rollback
​ 3）提交：commit 每次修改后，必须commit后更改才能生效（必须先提交，后回滚）
例如：

START TRANSACTION; -- 开启事务
UPDATE stu SET money = money -500 WHERE NAME = 'zhangsan'; -- 张三账户-500
UPDATE stu SET money = money +500 WHERE NAME = 'lisi'; -- 李四账户+500
COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务

​ MySQL数据库中事务默认自动提交
​ 事务提交的两种方式
​ 1.自动提交：一条DML（增删改）语句会自动提交一次事务
​ 2.手动提交：需要先开启事务，再commit提交
​ 查看事务的默认提交方式：

SELECT @@autocommit; -- 1代表自动提交	0代表手动提交

​ 修改事务的默认提交方式：

SET @@autocommit = 0;

事务的四大特征

​ 1.原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
​ 2.持久性：当事务提交或回滚后，数据库会持久化的保存数据
​ 3.隔离性：多个事务之间相互独立
​ 4.一致性：事务操作前后数据总量不变
事物的隔离级别（了解）：多个事务之间是隔离，相互独立的，但是如果多个事务操作同一批数据就会引发一些问题，设置不同的隔离级别就可以解决这些问题
​ 存在问题：
​ 1.脏读：一个事务读取到另一个事务中没有提交的数据
​ 2.不可重复读：在同一个事务中，两次读取到的数据不一样
​ 3.幻读：一个事务操作（DML）数据表中所有记录。另一个事务添加了一条数据，则第一个事务查询不到自己的修改
​ 隔离级别：
​ 1.read uncommitted：读未提交
​ 产生的问题：脏读，不可重复读，幻读
​ 2.read committed：读已提交（Oracle默认）
​ 产生的问题：不可重复读，幻读
​ 3.repeatable read ：可重复读（MySQL默认）
​ 产生的问题：幻读
​ 4.serializable：串行化：不会产生任何问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低