MySQL 帮助文档 数据库 知识点 基本操作 笔记 事务 视图 存储过程 存储函数 总结

帮助文档下载：微信公众号 日常分享菌

---

前言

1. 数据库的英文单词：
   DataBase 简称 ： DB
   

2. 什么数据库？
   用于存储和管理数据的仓库。

3. 数据库的特点：
   ① 持久化存储数据的。其实数据库就是一个文件系统
   ② 方便存储和管理数据
   ③ 使用了统一的方式操作数据库 – SQL

   DDL ：定义数据库
   数据定义语言，用来定义数据库对象，数据库，表，列等。关键字 create，drop，alter等

   DML ：操作数据库
   数据操作语言，用来对数据库中表的数据进行增删改，关键字 insert，delete，update等

   DQL：查询记录
   数据查询语言，用来查询数据库中表的记录（数据），关键字 select，where等
   
   

注释

单行注释
①--
②#
多行注释
/* 注释内容 */

约束

1. 主键约束 primary key
   非空且唯一，一个表只能有一个主键，主键是表中记录的唯一标识。

--建表时添加主键
create table 表名(
		列名 类型 primary key,--给id添加主键约束
		);
		
--建表后添加主键
alter table 表名 modify 列名 类型 primary key;

--添加自动增长
alter table 表名 modify 列名 int auto _increment;

--删除主键
alter table 表名 drop primary key;

--删除自动增长
alter table 表名 modify 列名 int auto_increment;

3. 非空约束 not null

4. 唯一约束 unique

5. 外键约束

小细节

• 判断null值时应用is

数据库的备份和还原

1. Mysql服务启动

1. 手动启动
2. cmd–>services.msc 打开服务的窗口
3. 使用管理员打开cmd
    net start mysql:启动MySQL的服务器
   net stop mysql 关闭MySQL的服务器

2. Mysql登录

mysql -uroot(root为用户名) -p密码
mysql -uroot(root为用户名) -p连接目标的密码 -h目标ip地址
   host=ip
   user=root
   pssword=连接目标的密码

3. Mysql退出

exit
quit

DDL操作数据库和表

操作数据库 CRUB

C(create):创建

create database 数据库名;

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

create database if not exists 数据库名;

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

创建数据库tb并判断是否存在，使用字符集
create database if not exists 数据库名 character set 字符集名;

R(retrieve):查询

1.查询所有数据库
show databases
2.查询某个数据库的字符集
查询某个数据库的创建语句
show database 数据库名称;
3.查询当前使用的数据库
select database();

U(update):修改

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

B(delete):删除

删除数据库
drop database 数据库名；

判断数据库是否存在，存在则删除
drop database if exists 数据库名称；

操作表 CRUB

首先应选择数据库
use 数据库名；

C(create):创建

1. 语法(最后一列不需要加逗号)
   create database(
   列名1.数据类型1,
   列名2.数据类型2,
   ……
   列名n.数据类型n
   );

2. 数据库库类型
   int 整数型
   age int,

   double 小数类型
   变量名,double(a,b)
   此变量有a位，小数点后保留b位

   date 日期类型，只包含年月日，yyyy-mm-dd

   datatime 日期，包含年月日时分秒，yyyy-mm-dd HH:mm:ss

   timestamp 时间错类型，包含年月日时分秒，yyyy-mm-dd HH:mm:ss

   varchar 字符串
   变量名 varchar(a)
   变量名的最长为a个字符

3. 复制表
   create table 新表明 like 待复制表；

R(retrieve):查询

查询数据库中所有表的名称
show tables；

查询表的结构
desc 表名；

U(update):修改

1. 修改表名
   alter table 旧表名 rename to 新表名；

2. 修改表的字符集
   alter table 旧表名 character set 字符集名称；

3. 添加列
   alter table 表名 add 列名 数据类型；

4. 修改列名
   alter table 表名 change 旧列名 新列名 新数据类型；

5. 修改列的数据类型
   alter table 表名 modify 列名 新数据类型；

B(delete):删除

drop table 表名；

如果存在，则删除表
drop table if exists 表名；
删除列
alter table drop 列名；

DML增删改表中的数据

添加数据

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

删除数据

delete from 表名 where 条件；
*如果不写结构，删除表中所有记录

删除表中所有记录，并创建同名空表（效率很高）
truncate table 表名；

修改数据

updata 表名 set 列名=值1，列名2=值2 [where 条件]

DQL查询表中的数据

基础查询

1. 基础查询

• 多个字段查询(查询所有字段，*可用来代替字段列表)
  select 字段1，字段2，……*from 表名；
• 去除重复(完全一样才可去除)
  select distinct 字段名 from 表名；
• 计算列
  • 一般可以使用四则运算计算一些列的值(一般只会进行数值型的计算)
  • ifnulll(表达式1,表达式2) 判断表达式1的值是否为null，若为null则替换为表达式2
• 起别名 字段名 as 别名
       as可以省略

2. 条件查询

• where字句后跟条件
• 运算符
  - > 、< 、<= 、>= 、= 、<>
  - BETWEEN…AND
  - IN( 集合)
  - LIKE：模糊查询
     - 占位符：
        - _:单个任意字符
        - %:多个任意字符
  - IS NULL
  - and 或 &&
  - or 或 ||
  - not 或 !
  例：
  查询年龄不等于20岁
  SELECT * FROM student WHERE age != 20;
  SELECT * FROM student WHERE age <> 20;
  
  查询姓名中包含德的人
  SELECT * FROM student WHERE NAME LIKE ‘%德%’;

分组查询

例：按照性别分组，分别查询男女同学的平均分，人数。要求分数低于70分的人不参与分组,分组之后人数要大于2

select sex,avg(math),count(id) from student where math>70 group by sex having count(id)>2;

*分组之后查询的字段：分组字段，聚合函数
*where和having的区别：

1. where在分组之前进行限定，having是在分组之后进行限定
2. where后不能跟聚合函数

分页查询

limit 开始的索引,每页查询的条数;
开始的索引=（当前的页码-1）*每页显示的条数

多表查询

笛卡尔积：有两个集合A,B,去这两个集合的所有组成情况

*在完成多表查询时，需要消除无用的数据

一、 内连接查询

  1. 隐式内连接

使用where条件消除无用数据
语法： select 字段列表 from 表1,表2 where 条件

  2. 显式内连接

语法： select 字段列表 from 表1 [inner] join 表2 on 条件

例：
select *from emp inner join dept on emp.dept_id =dept.dept_id

二、 外连接查询

   1. 左外连接

查询的是左表所有数据以及其交集部分
语法： select 字段列表 from 表1 left [outer] join 表2 on 条件

   2. 右外外连接

查询的是右表所有数据以及其交集部分
语法： select 字段列表 from 表1 right [outer] join 表2 on 条件

三、 子查询

查询中嵌套查询

1. 子查询的结果是单行单列的

子查询可以作为条件，使用运算符去判断。运算符>,>=,<,<=

例：查询平均工资小于平均工资的
select * from emp where salary<(select avg(salary) from emp)

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

例：查询财务部和市场部的所有员工
select *from emp where id in (select id from dept where name =“财务部” or name=“市场部”)

3. 子查询的结果是多行多列的
   子查询可以作为虚拟表参与查询

例：查询员工入职日期是2011-11-11日之后的员工信息和部门信息
select *from emp t1,(select * from emp where join_date>2011-11-11) t2 where t1.id=t2.id

排序

select * from 表名 order by 字段1,字段2 [排序方式]

asc 升序(默认状态)
desc 降序
例 :   select *from student order by math asc，english asc;
在student表中按照数学成绩升序排名，如果相同，则按英语升序排列

聚合函数

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

1. count 计算个数
   select count(字段名) from 表名;

例： select count(ifnull(english,0)) from student;
在student表中，计算English列数据的个数（如果为空返回0，计入结果中）

2. max 计算最大值
3. min 计算最小值
4. sum 计算和
5. avg 计算平均值

*聚合函数的计算，排除null值

DCL管理用户

数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户。关键字 grant ，revoke等。
查询

一、 管理用户


二、管理权限

例：

事务

一、 基本介绍
如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么全部成功，要么同时失败

二、 操作

1. 开启操作：start transaction


2. 回滚	：rollback


3. 提交： commit

	MySQL数据库中事务默认自动提交

例：

三、事务的四大特征

1. 原子性：是不可分割的最小单位，要么同时成功，要么同时失败
2. 持久性：当事务提交或回滚后，数据库会持久化的保持数据
3. 隔离性：多个事务之间相互独立
4. 一致性：事务操作前后，数据总量不变

四、事务的隔离级别

多个事务操作同一批数据会引发一些问题，多个事物之间是隔离的，相互独立的，设置不同的隔离级别就可以解决

存在的问题：

1. 脏读：一个事务，读到另一个事务中没有提交的数据
2. 不可重复读（虚读）：在同一个事务中，两次读取的数据不一样
3. 幻读：一个事务操作（DML）在数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

隔离级别

1. read uncommitted        读未提交
   产生的问题：脏读，不可重复读，幻读

2. read committed        读已提交
   产生的问题：不可重复读，幻读

3. repeatable read        可重复读
   产生的问题：幻读

4. serializable        串行化
   产生的问题：可以解决所有的问题

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

查询隔离级别：   select @@tx_isolation;

设置隔离级别：   set global transaction isolation level 级别字符串；

例：

视图

一种虚拟存在的数据表，并不在数据库中实际存在

作用：将一些较为复杂的查询语句的结果，封装到一个虚拟表中

视图的创建和查询

1. 创建视图
   create view 视图名称 as 查询语句;
2. 查询视图
   select * from 视图名称;

例:

--创建city_country视图，保存城市和国家的信息（使用指定列名）
create view city_country (city_id,city_name,country_name) as
select c1.id,c1.name,c2.name from city c1,country c2 
			where c1.cid=c2.id;

--查询视图
select * from city_country;

视图的修改和删除

1. 修改视图数据

update 视图名称 set 列名 =值 where 条件;

2. 修改视图结构

修改视图表中的数据后，原表中的数据也发生改变

alter view 视图名称 as 查询语句;

3. 删除视图

drop view [if exists] 视图名称;

例：

--修改视图数据，将北京修改为深圳。
update city_country set city_name='深圳' where city_name='北京';

将视图中的country_name修改为name
alter view city_country (city_id,city_name,name) as
select  c1.id,c1.name,c2.name 
		from city c1,country c2 where c1.cid=c2.id;	

--删除city_country视图	
drop view if exists city_country;

存储过程和函数

事先经过编译并存储在数据库中的一段SQL语句的集合

好处：

1. 提高代码的复用性
2. 减少数据在数据库中和应用服务器间的传输，提高效率
3. 减少代码层面的业务处理能力

区别：

1. 存储函数必须有返回值
2. 存储过程可以没有返回值

存储过程

创建 调用 存储过程

--**创建存储过程**
--修改结束分隔符
delimiter$
create procedure 存储过程名称(参数列表)
begin
		sql 语句列表;
end$
--修改结束分割符
delimiter ;

--**调用存储过程**
call 存储过程名称(实际参数)

例：
--创建stu_group()存储过程，实现封装 分组查询总成绩并按升序排序的功能
delimiter $
create procedure stu_group()
begin 
	select gender,sum(score) getsum from student 
		group by getsum Asc;
end $
delimiter;
--调用stu_group()存储过程
call stu_group();

存储过程的查看和删除

--**查看数据据库中所有的存储过程**
select *from mysql.proc where db="数据库名称"

--**删除存储过程**
drop producedure [if exists] 存储过程名称;

例：
--查看db6数据库中所有的存储过程
select * from mysql.proc where db="db6";

存储过程—变量

定义变量
declare 变量名 数据类型 [default 默认值]

变量赋值
set 变量名=变量值;
select 列名 into 变量名 from 表名 [where 条件]

例：


存储过程—if语句

if 判断条件1 then 执行的sql语句1;
[elseif 判断条件2 then 执行的sql语句2;]
……
[else 执行的sql语句n;]
end if;

例：

存储过程—参数传递

--存储过程的参数和返回值
delimiter $
create procedure 存储过程名称 ([in|out|inout] 参数名 数据类型)
begin 
		sql 语句列表;
end $
delimiter ;
--in：代表输入参数，需要由调用者传递实际数据（默认）
--out:代表输出参数，该参数可以作为返回值
--inout:代表既可以作为输入参数，也可以作为输出参数

例：

存储过程—while循环

初始化语句;
while 条件判断语句 do
			循环体语句;
			条件控制语句;
end while;

例：

存储函数

--创建存储函数
delimiter $
create function 函数名称(参数列表)
returns 返回值类型
begin 
		sql 语句列表;
		return 结果;
end $
delimiter ;
--调用存储函数
select 函数名称(实际参数);
--删除存储函数
drop function 函数名称;

例：

触发器

• 触发器是与表有关的数据对象可以在insert、update、delete之前或之后触发并执行触发器中定义的sql语句。
• 这种特性可以协助应用系统在数据库端确保数据的完整性，日志记录、数据校验等操作
• 使用别名new和old来引用触发器中发生变化的内容记录

创建，查看，删除

例：

索引

帮助MySQL高效获取数据的一种数据结构（索引的本质就是数据结构）

基本操作

--创建索引
reeate [unique|fulltext] index 索引名称
[using 索引类型]--默认是BTREE
ON (列名……);

--查看索引
show index from 表名;

--添加索引
alter table 表名 add index 索引名称(列名)--普通索引
alter table 表名 add index 索引名称(列名1,列名2……)--组合索引
alter table 表名 add primary key(主键列名)--主键索引
--外键索引
alter table 表名 add index constraint 外键名 
			foreign key (本表外键列名) references 主表名(主键列名)
alter table 表名 add unique 索引名称 (列名);--唯一索引
alter table 表名 add fulltext 索引名称 (列名)--全文索引

--删除索引
drop index 索引名称 on 表名;

例：

索引的设计原则

索引的原理

锁

锁机制：数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全所设计的一种规则，锁机制类似于多线程中的同步，可以保证数据的一致性和安全性。

存储引擎

操作

--查询数据库支持的存储引擎
show engines;
--查询某个数据库中所有数据表的存储引擎
show table status from 数据库名称;
--查询某个数据库中某个数据表的存储引擎
show table status from 数据库名称 where name="数据表名称"
--创建数据表，指定储存引擎
reeate table 表名(
		列名,数据类型，
		……
)engine =引擎名称;
--修改数据表的存储引擎
alter table 表名 engine =引擎名称;

例：

存储引擎的选择

数据库设计

一、 多表之间的关系

1. 一对一

一对一关系实现可以在任意一方添加唯一外键指向另一方的主键

2. 一对多(多对一)

在多的一方建立外键，指向一的一方的主键

3. 多对多

多对多关系的实现需要借助第三张中间表。中间表至少包含两个字段，分别指向两个表的主键

二、 数据库设计范式

1. 第一范式(1NT)：每一列都是不可分割的原子数据项

2. 第二范式(2NT)：在1NT 的基础上，非码属性必须完全依赖于码（在1NT基础上清除非主属性对主码的部分函数依赖）

3. 第三范式(3NT)：
   在2NT基础上，任何非主属性不依赖于其它非主属性（在2NT的基础上消除传递依赖）

函数依赖：A- ->B,如果通过A属性（属性组）的值可以确定唯一B属性的值，则称B依赖于A

例如：学号- ->姓名，(学号,课程名称)- ->分数

完全函数依赖：A- ->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值

例如：(学号,课程名称)- ->姓名

部分函数依赖：A- ->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中部分的属性值

例如：学号- ->系名，系名- ->系主任

码：如果在一个表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码

主属性：码属性组中的所有数据

非主属性：除码属性组的属性