mysql的账号管理与DDL语言

数据库账号管理

账号权限是为了防止公司信息泄露以及泄露用户信息，因为一般公司的表中的信息是比较重要的，不能说改就随便改，所以学会分配账号权限，划分等级还是比较重要的。
一般公司里的等级划分如下：

划分权限

那怎么用代码划分权限呢
大致格式：

grant 权限 on 数据库对象 to 用户 identified by 密码

GRANT是关键字
ALL PRIVILEGES代表全部权限
zhangsan是用户名
@后面的单引号内加地址 %代表本地地址，如果用%后续连接不上的话，就把%改成localhost或127.0.0.1就可以
identified by定义的意思，后面是密码
with grant option所有权限的选项(增删改查)

GRANT ALL PRIVILEGES 
ON *.* 
TO 'zhangsan'@'%' IDENTIFIED BY 'zhangsan123' WITH GRANT OPTION;

创建了一个peach账号，这个账号只能看到mysql数据库，对表的操作只能查询和修改,如果要增加对表的删除，增加之类的权限就在GRANT后面加关键字就行（delete，insert）

GRANT SELECT ,UPDATE
ON mysql.*
TO 'peach'@'127.0.0.1' 
IDENTIFIED BY '123';

然后连接：

然后测试一下：
然后看一下结果，因为刚刚只给了peach这个账号看mysql数据库的权限，所有它里面就只能看到mysql数据库。

如果讲了这么多你还是不明白权限的大概意思的话，就请看我画的一个概念图，就假设你是一个普通的人，把数据库想成一个大房子，里面的表是很多个小房间，现在你首先要能打开大房子的门，才能看到小房间，但要进去小房间的话得要钥匙才行。钥匙也就是权限。

修改用户

在博客上也看到很多种方法，我总结了一下，如下：
1、 用SET PASSWORD命令

2、用mysqladmin

mysqladmin -u用户名 -p旧密码 password 新密码 

3、用UPDATE直接编辑user表

 
use mysql;#选择操作的数据库
update user set password=password('123') where user='root' and host='localhost'; 
flush privileges; #刷新权限

4、用ALTER修改

ALTER USER ‘你的用户名默认root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘你的密码’;

5、在忘记root密码的时候，可以这样
以windows为例：

1. 关闭正在运行的MySQL服务。
2. 打开DOS窗口，转到mysql\bin目录。
3. 输入mysqld --skip-grant-tables 回车。–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。
6. 连接权限数据库： use mysql; 。
7. 改密码：update user set password=password(“123”) where user=“root”;（别忘了最后加分号） 。
8. 刷新权限（必须步骤）：flush privileges;　。
9. 退出 quit。
10. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录

注意：
如果没有效果就检查一下作用域
下面的代码含义是使用mysql这个名字的数据库

use mysql;

还是不行的话，就刷新一下试试看
下面的代码含义是刷新权限

flush privileges;

删除用户

格式： DROP USER ‘用户名’@‘地址’
举例如下：

DROP  USER 'peach'@'127.0.0.1'

权限相关表

mysql数据库中存在自带的几个关于权限的比较重要的表，如下：
（表中的含义：Y代表支出，N代表不支持）

表名	内容
db	匹配Host、User然后会根据Db字段对应的表进行权限分配
user	所有账号
tables_priv	表层权限，定位到表是否可视的权限
columns_priv	字段层权限，定位到字段是否可视的权限

四大引擎

其实我们经常用到的引擎只有两个
开发面试的时候最常问到和用到的
就是
InnoDB存储引擎和MyISAM存储引擎
其他两个是Memory，Archive，我们不怎么用到，这里就不多介绍了。

特点

（我这里只列出我认为比较重要的,Y代表支持，N代表不支持）：

功能	InnoDB	MYISAM
存储限制	64TB	256TB
支持事物	Y	N
支持全文索引	N	Y
支持外键	Y	N

存储限制:指的是如果你的电脑硬盘足够大，就可以支持存储256TB的数据
举个例子：我现在有很多的糖，但你只有两只手可以拿，那我就只能给你一些，而不能全部给你，但如果你有一个麻袋，那我就可以全部给你，就是相同的原理。
事物：如果这个表要经常更改那就一定需要支持事物，所有一般数据库默认InnoDB存储引擎，那反之，如果这个表不怎么需要更改的话，就不一定支持。

InnoDB和MYISAM的优点对比

InnoDB
InnoDB是事务型数据库的首选引擎，支持事务安全表，支持外键，，InnoDB是默认的MySQL引擎，InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全存储引擎
在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
MyISAM
MyISAM基于ISAM存储引擎，并对其进行扩展，是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一，MyISAM拥有较高的插入、查询速度，但不支持事物。

建库建表等操作

库的操作

建库跟oracle和sql数据库差不多，就是类型不太一样
IF NOT EXISTS：如果没有则创建

CREATE DATABASE IF NOT EXISTS books;

删除库
如果books存在就删除它

DROP DATABASE IF EXISTS books;

表的操作

查看表结构

DESC book;

创建表

CREATE TABLE book(
	id INT,
	bname VARCHAR(20),
	price DOUBLE,
	authorID INT,
	publishDate DATETIME

	
);

修改表列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

修改表类型与约束

ALTER TABLE book MODIFY COLUMN  pubDate TIMESTAMP;

添加新列

ALTER TABLE book ADD COLUMN  bz VARCHAR(200);

修改表名

ALTER TABLE book RENAME TO  book_author;

删除表
有三种
1、删除表数据，可以跟where条件

delete from book;

2、删除表数据，不可以跟where条件，只能全部删除

truncate table book;

3、删除表结构

drop table book;

复制表
1、复制整张表

create table bookk select * from book;

2、复制部分数据

create table bookk select * from book where price=10;

3.复制字段

create table bookk select id,bname from book;

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。
分类：

约束名	含义
not null	非空，该字段不能为空
default	默认，该字段有默认值
primary key	主键，保证该字段的值具有唯一性，并且非空（例如id）
unique	唯一，保证该字段的值具有唯一性，可以为空
check	检查约束（mysql中不支持）比如年龄，性别
foreign key	外键，限制两个表的关系，保证该字段的值必须来源于主表的关联列的值

那根据上图可以看出主键（primary key）和唯一（unique）有点像，那现在我们来对比一下

约束	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	Y	N	只能有1个	Y
唯一	Y	Y	可有多个	Y

根据上图，相信大概可以把他们区分开来了，上图有提到是否允许组合，虽然主键和唯一都支持，但是还是不推荐。

一般在创建表和修改表时添加约束。

约束的分类

列级约束
就是加在字段名的后面，语法上都支持，但外键会没效果
举例如下

CREATE TABLE book(
		id INT PRIMARY KEY,#主键
		bname VARCHAR(20) NOT NULL，#非空
		price DOUBLE,
		authorID INT,
		publishDate DATETIME
);

表级约束

not null和default不支持，其他都支持。
表级约束一般写在字段名写完之后的最后面。
比如联合主键

标识列
含义：自增长列，可以不用手动的插入值，系统提供默认的序列值。
注意的点：

1、标识列必须跟key搭配。
2、一个表只有一个标识列。
3、标识列的类型只能是数值型。
4、可以通过手动插入值。
5、标识列可以通过set auto_increment_increment=3;
   设置次增加多少（步长默认是加1）。

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

总结

还有什么问题欢迎在评论区补充 ，白白~