MySQL之账号管理，四大引擎以及DDL（数据定义语言）

前言

之前讲过了MySQL的安装与卸载还有使用，这篇我们来讲讲关于MySQL的账号管理、四大引擎以及DDL。

账号管理与相关案例

账号的权限一般在公司中分成四种：

1、创建全权限的账号

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'IP地址' IDENTIFIED BY '密码' WITH GRANT OPTION;

2、案例：将所有的权限赋给创建了的一个叫做zengjing的用户，地址对应的是本地，密码是111222

GRANT ALL PRIVILEGES ON *.* TO 'zengjing'@'localhost' IDENTIFIED BY '111222' WITH GRANT OPTION;

连接测试：
步骤：
1、创建完账号后需要重启Navicat 8 for MySQL
2、然后用刚才新建的管理账号创建连接，随后也可以在Manage Users中看到刚才创建的管理账号

3、创建部分权限的账号

GRANT 权限名，权限名 ON 数据库 TO '用户名'@'IP地址' IDENTIFIED BY '密码';

4、案例：创建一个zara的账号，只赋给他修改和查询的权限,地址对应的是本地，并且只能看到girls这个数据库

GRANT SELECT,UPDATE ON girls.* TO 'zara'@'localhost' IDENTIFIED BY 'zara123';

测试连接成功后，可以看到我用的连接名为cc，其中girls的可操作数据库，test和information_schema是自带的数据库：
步骤：
1、创建完账号后需要重启Navicat 8 for MySQL
2、然后用刚才新建的管理账号创建连接，随后也可以在Manage Users中看到刚才创建的管理账号

与权限相关的表

存在创建连接后自带的mysql数据库中

db：存在操作这个数据库的账号
user：记录着所有的账号
tables_priv：表层权限，定位到表的权限分配
columns_priv：字段层权限，定位到字段是否可视的权限

四大引擎

平时主要用到了两个（需借助工具SQLyog查看）

InnoDB与myISAM，一般MySQL建表默认的是innoDB

myISAM，存储限制256TB，存储事物No，支持全文索引Yes，不支持外键，一般不涉及到增删改就用这个引擎，系统奔溃后，MyISAM恢复起来更困难。

innoDB，存储限制64TB，存储事物Yes，支持全文索引No，支持外键，如果要提供提交，回滚，崩溃回复能力的事物安全（ACID兼容）能力，并要求实现并发控制，就使用这个。

存储限制：指的是自身电脑硬盘足够大就可以存储的空间；

存储事物：指的是假如有人银行转账，甲方转钱失败，乙方也会收不到钱，甲方转钱成功乙方就可以收到钱，两边同时成功，同时失败；

DDL（数据定义语言）

库和表的管理

库和表都有创建、修改、删除的功能。

创建：create
修改：alter
删除：drop

库

概念：电子化统一管理的、有组织的、可共享的管理大量数据的仓库

1、创建库Books

create database if not exists books;

创建后的库：

2、修改库（一般不用）

rename database 旧库名 to 新库名

3、库的删除

drop database if exists 库名;

表

1、表的创建

语法：
create table 表名(
	列名 列的类型 （长度）约束,
	列名 列的类型（长度）约束,
	列名 列的类型（长度）约束
)

案例：
create table book(
	id int,#编号
	bName varchar(20),#图书名
	price double,#价格
	authorId int,#作者编号
	publishDate datetime#出版日期
)

创建的表：

desc book;查看表结构：

2、表的修改

改表里的某一个字段

alter table book change column publishDate pubDate DATETIME;
将publishDate 改成了pubDate：

修改列的类型或者约束

alter table book modify column pubdate TIMESTAMP;
将pubDate的类型改成了TIMESTAMP：

添加新列

alter table book add column annual double;
增加了一个annual ，类型为double：

删除列

alter table book drop column annual;
删除了 annual这列：

修改表名

alter table book_author rename to book;
将表名book改成了book_author了

表的删除
drop table if exists book_author;
将book_author表删除了

显示当前数据库所有的表

show tables;

查看所有索引,包括主键外键唯一：

show index from stuinfo;

3、表的复制

仅仅复制表结构

create table copy like book;
复制了book表

复制表结构和数据

create table copy2 select * from book;
复制了book表以及全部数据

只复制部分符合条件的数据

create table copy3 select * from book where address='中国';
只复制了book表结构和地址为中国的数据

六大约束

一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性，一般在创建或者修改表时添加约束。

六大约束分类

约束名	解释
not null	非空约束，用于保证该字段的值不能为空
default	默认约束，用于保证该字段有默认值
primary	主键，用于保证该字段的值具有唯一性，并且非空
unique	唯一约束，用于保证该字段具有唯一性，可以为空
check	唯一约束，检查约束（MySQL中不支持），给字段设置条件,添加数据时满足则可以添加数据，不满足则不能添加
foreing key	外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表关联列的值

主键和唯一约束的对比：

主键	唯一约束
保证唯一性	保证唯一性
不允许为空	允许为空
不能有多个	可以有多个
允许组合但不推荐	允许组合但不推荐

关于外键的几点：
1、要求在‘从表’中设置外键关系；
2、‘从表’的外键列的类型和‘主表’的关联列类型要求一致或兼容，名称无要求；
3、‘主表’的关联列必须是一个key（一般是主键或者唯一）；
4、插入数据时，先插入主表，再插入从表
5、删除数据时，先删除从表，再删除主表。

约束分为表级约束和列级约束

创建表时添加约束

添加列级约束语法

create table 表名(
	列名 列的类型 列级约束,
	列名 列的类型 列级约束,
	列名 列的类型 列级约束
)

只支持：默认、非空、主键、唯一

案例：

create table stuinfo(
	id int primary key,#主键
	stuName varchar(20) not null unique,#非空&唯一
	gender char(1) check(gender='男' or gender ='女'),#检查
	seat int unique,#唯一
	age int default 18,#默认
	majorId int references major(id)#外键
)

添加表级约束
语法：

constraint 约束名 约束类型（字段名）

案例：

create table stuinfo(
	id int ,
	stuName,
	gender char(1),
	seat int,
	age int ,
	majorId int,
	
	constraint pk primary(id),#主键
	constraint uq unique(seat),#唯一
	constraint fk foreing key(majorid)references major(id),#外键
)

修改表时添加约束

添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束;

添加表级约束

alter table 表名 add 【constraint 约束名】 约束类型（字段名）【外键的引用】

案例

//添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
//添加默认约束
alter table stuinfo modify column age int default 18;
//添加主键
//1、列级约束
alter table stuinfo modify column id int primary key;
//2、表级约束
alter table stuinfo add primary key(id);
//添加外键
alter table stuinfo add constraint fk foreing key(majorId) references major(id);

删除约束

//删除非空约束
alter table stuinfo modify column stuname carchar(20) null;
//删除默认约束
alter table stuinfo modify column age int;
//删除主键
alter table stuinfo drop primary key;
//删除唯一
alter table stuinfo drop index seat;
//删除外键
alter table stuinfo drop foreing key fk;

标识列

又称为自增长列，可以不用手动的插入值，系统提供默认的序列值。

特点：
1、标识列必须和主键搭配？不不不，但要求是一个key；
2、一个表可以有几个标识列？至多一个；
3、标识列的类型只能是数值型；
4、标识列可以通过set auto_increment_increment=3;设置步长，就是每次增加多少；
5、可以通过手动插入值、设置起始值。

创建表时设置标识列

CREATE TABLE stuinfo(
		id INT
		NAME FLOAT UNIQUE AUTO_ INCREMENT，
		seat INT
);

TRUNCATE TABLE tab. _identity;
//增加数据
INSERT INTO stuinfo(id ,NAME) VALUES (NULL, 1);
INSERT INTO stuinfo(NAME) VALUES(2);
//步长
SHOW VARIABLES LIKE '%auto_ increment%';
//设置步长
SET auto_increment_increment=3

总结（思维导图）

希望我的博客能够帮助到你们哦，有问题欢迎指教呀债见！