MySQL数据库账号管理+DDL语言
一、MySQL数据库账号管理
这篇博客主要给大家讲解一下MySQL数据库账号管理和DDL语言哈。
让我们在热烈的掌声(没有掌声)中开始这次的讲解。
1、用户管理权限
1.修改用户管理权限
在了解账号创建之前,我们必然要先了解账号的一个管理,我画了个图,可以看一下:
不同的管理人员拥有对数据的不同管理权限,这其实也众多公司的员工管理制度。
毕竟能力越大,责任越大。相反,你不努力,就只有给别人砌墙的份(被打)。
了解了这个层面之后,我们就可以开始各个人员管理数据库权限的设置了。
//zouyan
GRANT ALL PRIVIEGES ON *.* TO zouyan@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
GRANT ALL PRIVIEGES ON root_db.* TO zouyan@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
GRANT ALL PRIVIEGES ON root_db.user_infor TO zouyan@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
然后做一个解释:
GRANT:关键字
ALL PRIVIEGES :全部(将哪个位置的权限赋给谁谁谁)
ON:后面接哪个位置的权限(所有数据库 || 某一数据库 || 某数据库某表)
TO:后面接对应的哪个地址
IDENTIFIED BY:定义(密码)
WITH GRANT OPTION :权限选项
语句词 | 解释 |
---|---|
ON * . * | 设置用户zouyan,可以访问mysql上的所有数据库 |
ON root_db . * | 设置用户zouyan,只能访问数据库root_db,其他数据库均不能访问 |
ON root_db . user_infor | 设置用户zouyan,只能访问数据库root_db的表user_infor,数据库中的其他表均不能访问 |
这个*.*就是说的某库的某表,这样去理解就知道怎么实现权限分配了。
2.修改用户操作权限
//zouyan
GRANT ALL PRIVIEGES ON *.* TO zouyan@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
GRANT SELECT ON *.* TO zouyan@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
GRANT SELECT,INSERT ON *.* TO zouyan@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO zouyan@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
REVOKE SELECT,INSERT ON WHAT FROM zouyan
然后下面看解释,通过自己的一个对比观察会更容易记住各个语句的区别和意义。
语句词 | 解释 |
---|---|
GRANT ALL PRIVIEGES ON | 设置用户zouyan,拥有所有的操作权限 |
GRANT SELECT ON | 设置用户zouyan,拥有【查询】操作权限 |
GRANT SELECT,INSERT ON | 设置用户zouyan,拥有【查询\增加】操作权限 |
GRANT SELECT,INSERT,UPDATE,DELETE ON | 设置用户zouyan,只拥有【增加\删除\修改\查询】操作权限 |
REVOKE SELECT,INSERT ON WHAT FROM zouyan | 取消用户zouyan的【查询\增加】操作权限 |
3.查看用户权限
查看某个用户的权限:SHOW GRANTS FOR zouyan@localhost;
结果:
//zouyan
GRANT ALL PRIVIEGES ON *.* TO zouyan@localhost WITH GRANT OPTION
我这里做演示的是我自己的,所以如果要更改就是更改语句FOR后面的。
2、MySQL数据库的默认四张表
然后我们来认识一下MySQL数据库的默认四张表:
表名 | 解释 |
---|---|
user表(用户层权限) | 因为字段太多,只截取了一部分。首先登陆的时候验证Host、user、Password也就是ip、用户名、密码是否匹配,匹配登陆成功将会为登陆者分配权限,分配权限的顺序也是按照上面四张表的排列顺序进行的,举个例子,如果user表的select_ priv为Y说明他拥有所有表的查找权限,如果为N就需要到下一级db表中进行权限分配了。其中的%是通配符,代表任意的意思。 |
db表(数据库层权限) | 来到db表之后会匹配Host、User然后会根据Db字段对应的表进行权限分配,像select_ priv这些字段对应的权限大家应该都能看出来是对应着什么权限了吧,这里不细说了(不愉懒,举个例子select. priv、 Insert priv、Update_ priv、 Delete. priv、 Create_ priv、Drop. priv分别代表着查询,增加,更新,删除,创建,销毁)。其中Y代表这拥有此项权限,N则代表没有此项权限。 |
table_priv(表层权限) | 与上面一样,这是通过Host、Db、User、Table来进行定位到表层的一个权限分配。不过它只有Table_priv和Column_priv两个字段来记录权限。 |
columms_priv(字段层权限) | 顾名思义,字段层权限,通过Host、Db、User、Table、Column来进行定位到字段层的一个权限分配,只有Column_ priv来记录权限。 |
这个稍作了解,知道都是什么就差不多了。
3、四大引擎的作用及区别
作用:数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。
MySQL的核心就是存储引擎。
不同的存储引擎都有各自的特点,以适应不同的需求,如下表所示
功能 | MYISAM | Memory | InnoDB | Archive |
---|---|---|---|---|
存储限制 | 256TB | RAM | 64TB | None |
支持事物 | No | No | Yes | No |
支持全文索引 | Yes | No | No | No |
支持数索引 | Yes | Yes | Yes | No |
支持哈希索引 | No | Yes | No | No |
支持数据缓存 | No | N/A | Yes | No |
支持外键 | No | No | Yes | No |
主要关注两个引擎(InnoDB存储引擎和MyISAM引擎)就可以了,因为目前是只用到这两个的。
二、DDL语言(数据定义语言)
1、库和表的管理
1.库的管理
//zouyan
//创建库
CREATE DATEBASE [IF NOT EXISTS] 库名;
//修改库名(一般不用)
RENAME DATABASE 原库名 TO 新库名;
//删除
DROP DATABASE IF EXISTS 库名;
2.表的管理
//zouyan
//创建表
CREATE TABLE 表名(
列名 列的类型【(长度)约束】,
列名 列的类型【(长度)约束】,
列名 列的类型【(长度)约束】,
……
列名 列的类型【(长度)约束】
);
//案例
CREATE TABLE zouyan(
id INT,#编号
bName VARCHAR(20),#图书名
Price DOUBLE,#价格
);
//修改表名
ALTER TABLE 旧表名 RENAME 新表名;
//删除表
DROP TABLE 表名;
这里就是有些具体针对表的操作:
//zouyan
#1.查询某表
SELECT * FROM 表名;
#2.仅仅复制表的结构
CREATE TABLE 新表 LIKE 复制的表; .
#3.复制表的结构+数据(一列书写方便理解)
CREATE TABLE 新表 SELECT * FROM 表名;
#4.只复制部分数据(一列书写方便理解)
CREATE TABLE 新表 SELECT 列名1,列名2 FROM 表名 WHERE 字段名='字段内容';
#5.仅仅复制某些字段
CREATE TABLE 新表 SELECT 列名1,列名2 FROM 表名 WHERE 0;
#6.删除表的某一列
ALTER TABLE 表名 DROP 列名;
#7.增加数据
INSERT INTO 表名(列名1,列名2,...列名N ) VALUES (数据1,数据2,...数据N );
#8.修改某列的单个数据
UPDATE 表名 SET 列名=新值(WHERE 列名=某值);
#9.修改某列的所有数据
UPDATE 表名 SET 列名='内容';
然后另外一些针对表数据的具体操作,后期会讲到的。
2、常见约束
1.六大约束
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确性和可靠性。
分类:六大约束
六大约束 | 解释 |
---|---|
NOT NULL | 非空,用于保证该字段的值不能为空。比如姓名、学号等。 |
DEFAULT | 默认,用于保证该字段有默认值。比如性别。 |
PRIMARY KEY | 主键,用于保证该字段的值具有唯一一性,并且非空。比如学号、员工编号等 |
UNIQUE | 唯一,用于保证该字段的值具有唯一性,可以为空.比如座位号。 |
CHECK | 检查约束[mysq1中不支持]比如年龄、性别。 |
FOREIGN KEY | 外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值,在从表添加外键约束,用于引用主表中某列的值。比如学生表的专业编号,员工表的部门编号,员工表的工种编号。 |
2.添加约束的时机:
1、创建表时添加约束
- 添加列级约束语法:直接在字段名和类型后面追加约束类型即可。
只支持默认、非空、主键、唯一。
//zouyan
//创建表时添加列级约束
//案例一
USE nc; I
DROP TABLE zouyan;
CREATE TABLE zouyan(
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)#外键
);
注:查看表中的所有索引:SHOW INDEX FROM 表名;
- 添加表级约束语法:在各个字段的最下面。
[constraint约束名]约束类型(字段名)
//zouyan
//案例一
DROP TABLE IF EXISTS zouyan;
CREATE TABLE zouyan(
id INT,
stuname VARCHAR(20) ,
gender CHAR(1) ,
seat INT,
age INT ,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id) ,#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK (gender ='男’OR gender = '女'),#检查
CONSTRAINT fk_ stuinfo_ major FOREIGN KEY (majorid)
REFERENCES major(id)#外键
);
2、修改表时添加约束
-
添加列级约束:
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束; -
添加表级约束
ALTER TABLE 表名 ADD 【constraint 约束名】 约束类型(字段名)【外键的引用】;
然后再细致的我就不讲了,根据前面的再去结合一下自己的思考。
3.约束的添加分类:
列级约束:六大约束语法上都支持,但外键约束没有效果。
表级约束:除了非空、默认,其他的都支持
4.主键和唯一的大对比
约束 | 保证唯一性 | 是否允许为空 | 一个表中可以有多少个 | 是否允许组合 |
---|---|---|---|---|
主键 | 是的 | 否 | 至多有一个 | 可以,但不必要的 |
唯一 | 是的 | 是 | 可以有多个 | 可以,但不必要的 |
5.外键
1、要求在从表设置外键关系。
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求。
3、主表的关联列必须是一个key (-般是主键或唯一 )。
4、插入数据时,先插入主表,再插入从表
删除数据时,先删除从表,再删除主表。
3、标识列
标识列又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
- 1、标识列必须和主键搭配吗?不一定,但要求是一-个key
- 2、一个表可以有几个标识列?至多一个!
- 3、标识列的类型只能是数值型
- 4、标识列可以通过SET auto_ increment. increment=3;设置步长。可以通过手动插入值,设置起始值。
//zouyan
#创建表是设置标识列
DROP TABLE IF EXISTS zouyan;
CREATE TABLE zouyan(
id INT
NAME FLOAT UNIQUE AUTO_ INCREMENT,
seat INT
);
TRUNCATE TABLE tab. _identity;
INSERT INTO zouyan(id ,NAME) VALUES (NULL, 'nancheng');
INSERT INTO zouyan(NAME) VALUES('lucv');
SELECT * FROM zouyan
//步长
SHOW VARIABLES LIKE '%auto_ increment%';
//设置步长
SET auto_ increment. increment=3
三、总结
其实很多的东西都是通过结果去推论的,这就是一个在企业中很常见的思维。
然后大家有什么问题可以在评论中留言,(不)一定会回复的。
我是南橙,一个逐渐秃头的橙子Orange。我们有缘下次见面。