8.2. 创建库,权限,创建表
持之以恒,方得始终!
前言
我们以上章的书店例子,它应该有如下这些表:
customers
customerID name address city
orders
orderID customerID amount date
books
isbn author title price
order_books
orderID isbn quantity
book_reviews
isbn reviews
安装 MySQL 后还需要做什么:
- 为mysql创建一个用户,以该用户运行所创建的数据库。
- 设置 path 路径。
- 运行 mysql_install_db
- 设置 root 密码。
- 删除匿名用户,和测试数据库。
- 设置 mysql 开机自启动。
拥有访问主机mysql的权限,不一定需要访问主机的权限。
一个好习惯是,我们可以用 mysql 的 root 用户登录,创建一个普通用户和库,让这个普通用户去管理这些库。
注意:mysql 中有它自己的用户,跟操作系统的用户不是一个东西,千万不要混淆了。
比方说,一个管理后台项目,它里面有很多自己的用户(类比 mysql 用户),并且用户分了不同的操作权限,但是它和登录浏览器的用户(类比操作系统用户)不是一回事。不登录浏览器用户,我们照样可以登录到管理后台去。
创建一个库
登录到 mysql
// 当然使用 mysql 命令的前提是有 mysql 客户端,一般安装 mysql后,会自带有客户端
mysql -h hostname -u username -p
// -h 运行 mysql 的主机地址,如果就是本地,可以忽略
// -u mysql中的用户,如果不指定,默认是登录当前操作系统时的用户名
// -p 给密码,不用直接写,敲回车后,会让你输入密码的
正常情况下,我们安装 mysql 后,应该用 root 登录,先修改 root 的密码,然后创建一个普通用户,最后创建需要的库。可以让这个普通用户管理这个库,这样才比较安全。
还可以用 GUI 工具,比如 Navicat
。
创建一个库
一般情况下,一个 web 项目至少需要一个库。我们为 图书 项目 创建一个 books 库。
create database books;
权限系统说明
一般 mysql 的 root 用户只做管理,比如手下的人需要一个 mysql 的账号,我们可以用 root 登录 mysql,创建一个,要注意的是,这些普通用户最好不要与操作系统用户相同,更重要的一点是,操作系统用户和 mysql 用户的密码千万不要相同。
其实 root 和普通用户,我们可以类比为,master 进程和 worker 进程,master 负责管理 worker 进程的调度,负责把接收进来的消息,交个某个 worker 进程处理。
一般而言,一个 web 项目,一个库,然后库对应的管理用户。
设置用户权限
我们创建一个 mysql 用户时,就赋予了其操作权限,也就是能让其做什么,不能做什么。
最少权限原则
一个用户,或一个进程,应该拥有能够执行分配给它任务的最低级别权限。
比如,一个用户,只需要查询库,我们就仅给它某个库的查询权限即可。这种原则,很多地方都适用,提高安全性。
创建用户,并赋予权限 grant
GRANT privileges [columns]
ON item
TO user_name [identified by 'password']
[require ssl_options]
[with [grant option | limit_options] ]
[]
中的是可选的。privileges
是由 ,
分开的一组权限。[columns]
是可选的,指定某列的权限,或者一组列,,
分割。item
权限对应的库,表。比如:
*.*
所有库下的所有表。库.*
该库下的所有表。库.表
该库下的单个表。
可以看到,权限范围有:库–》表–》字段
如果执行这个命令时,已经使用了某个库,则默认库就是这个当前库。
user_name,创建的用户名
password,用户密码[require ssl_options]
用户是否必须通过加密套接字连接,还可以指定 ssl 选项。[with grant option]
如果加了这个,表示允许指定的用户向别人授予自己所拥有的权限。也就是说,这个普通用户,也可以赋予别人权限的意思。
[with limit_options]
:
MAX_QUERIES_PER_HOUR n
每一个用户每小时执行的查询数量MAX_UPDATES_PER_HOUR n
每个用户每小时执行的更新数量MAX_CONNECTIONS_PER_HOUR n
每个用户每小时执行的连接数量
在共享的系统上,限制单个用户的负载时,可以用这个。
创建好用户之后,其实用户,权限等也都存储在名称为 mysql 库下的表中,我们也可以直接修改这些表中的配置:
mysql.user
mysql.db
mysql.host
mysql.tables_priv
mysql.columns_priv
权限的分类
注意:不要将 mysql 这个库的访问权限,赋予普通用户,以为它里面存有用户,密码,权限相关的配置
- 适合给普通用户的权限
select 表,列 允许用户从表中选择行 insert 表,列 允许用户在表中插入新行 update 表,列 允许用户修改现存表里行中的值 delete 表 允许用户删除现存表的行 index 表 允许用户创建和删除特定表索引 alter 表 允许用户改变表的结构,比如,可添加列,重命名列或表,修改列的数据类型 create 库,表 允许用户创建库,表,如果在grant中指定了库或表,则只能创建该库,表,当然需要先删除它们。 drop 库,表 允许用户删除库,表。
alter 权限的赋予要慎重,比如改表名称,可能会破坏权限系统。
注意:grant的权限,是以 with grant option
赋予的,上面列表中就不写了。
- 适合给管理员的权限
create temporary tables 允许管理员在create table语句中使用temporary
file 允许将数据从文件读入表,或从表读入文件
lock tables 允许使用 lock tables语句
process 允许管理员查看属于所有用户的服务进程
reload 允许管理员重新载入授权表,清空授权,主机,日志,表。
replication client 允许在复制主机 master,和从机 slave 上使用 show status
replication slave 允许复制 "从服务器连接" 到 "主服务器"
show databases 允许使用 show databases语句查看所有数据库列表,没有这个权限,用户只能看到他们能够看到的数据库。
shutdown 允许管理员关闭mysql服务
super 允许管理员关闭属于任何用户的线程
- 特殊的权限
all 上面的所有权限
usage 不授予权限。比如我先创建一个用户,他仅能登录,但是没有任何权限,后面我可以再赋予。
收回权限 revoke
REVOKE privileges [(columns)]
ON item
FROM user_name
如果已经给出了 with grant option ,可以按下面的方式撤销,包括其它的所有权限:
revoke all privileges, grant from user_name;
使用 grant,revoke 例子
- 创建一个管理员junwind,密码abc123,拥有所有权限,还可以向其他人授权。
grant all on *.* to junwind identified by 'abc123' with grant option;
- 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM junwind;
- 创建一个没有任何权限的普通用户
grant usage on books.* to tom identified by 'tom123';
- 授予上面 tom 用户更多权限
grant select,insert,update,delete,index,alter,create,drop on books.* to tom;
- 如果给 tom 用户的权限高了,也可以撤销一些权限
revoke alter,create,drop on books.* from tom;
- 后来,tom离职了,不需要使用数据库了,我们可以撤销所有权限
revoke all on books.* from tom;
给php web项目创建一个mysql用户
php中连接到mysql,是需要提供一个mysql用户的,我们根据最少权限原则,可以创建一个仅能 select,insert,update,delete
的用户。
grant select,insert,update,delete
on books.*
to jerry identified by 'jerry123';
再创建一个权限大一点的,用来平时管理 books库。因为一般不会直接把 root 用户给别人的。
grant select,insert,update,delete,index,alter,create,drop
on books.*
to jerryadmin identified by 'jerryadmin123';
如果创建的用户不能登录,我们可以删除匿名用户试试。
选择要使用的库
登录mysql后,我们一般是要先进入一个库里面,然后针对这个库,做一些操作:
use books; // 使用 books 库
或者,也可以在登录时就指定好
mysql -D dbname -h hostname -u username -p
创建表
把要创建的表,写入 sql 文件,然后载入文件。
// books.sql
create table customers(
customerid int unsigned not null auto_increment primary key,
name char(50) not null,
address char(100) not null,
city char(30) not null
);
create table orders(
orderid int unsigned not null auto_increment primary key,
customerid int unsigned not null,
amount float(6,2),
date date not null
);
create table books(
isbn char(13) not null primary key,
author char(50),
title char(100),
price float(4,2)
);
create table order_books(
orderid int unsigned not null,
isbn char(13) not null,
quantity tinyint unsigned,
primary key (orderid, isbn)
);
create table book_reviews(
isbn char(13) not null primary key,
review text
);
载入文件中的 sql 执行
mysql -h host -u username -D books -p < books.sql
- not null,此字段的值不能为空,可以结合 default,设置一些默认值,比如
0,''
。 推荐的做法就是每个字段都要 not null default xx - auto_increment,一般是整数类型字段,并且是索引字段使用它,它会让其值自增,也就是不给值情况下,会自动生成一个当前表中的最大数值。一个表中只能有一个。
- primary key,该字段是主键,主键的值必须都是唯一的,mysql 会自动索引该列。
- unsigned,只能是0或一个正数。
- amount float(6,2),显示宽度6,保留到小数点后2位。
- primary key(orderid, isbn),多个字段作为一个主键。
show ,describe
use books;
show tables; // 查看该库下的所有表
show databases; // 查看所有的库,当然得有访问权限的库
describe books; // 查看表结构
索引
create [unique|fulltext] index index_name
on table_name (index_column_name [(length)] [asc|desc], ...)
查看库的存放位置
show variables;
datadir 变量
字段类型
(m) 显示宽度
(m,d) d小数点保留几位
数字
整数
tinyint[(m)] 1byte -127~128, 0~255
smallint[(m)] 2byte -32768~32767, 0~65535
mediumint[(m)] 3byte -8388608~8388607, 0~16777215
int[(m)] -2^31~2^31-1, 0~2^32-1 4byte
bigint[(m)] -2^63~2^63-1 , 0~2^64-1 8byte
浮点
float[(m,d)]
double[(m,d)]
decimal[(m,d)]
日期时间
date yyyy-mm-dd
time hh:mm:ss
datetime yyyy-mm-dd hh:mm:ss
timestamp[(m)] 根据 m 决定格式
默认就是 YYYYMMDDHHMMSS
字符串
小文本 :char(m), varchar(m)
char(m) 0~255 定长,多余的空格补上
varchar(m) 1~255 变长
大文本 :text, 大二进制对象 blob(如图像,声音)
blob 2^16-1 65535
text 2^16-1 65535
mediumblob 2^24-1
mediumtext 2^24-1
longblob 2^32-1
longtext 2^32-1
set,列的值必须是一个指定的集合中的,可以多个。
enum,列的值是集合中的一个,或null。
set('v1', 'v2', ...) 64个
enum('v1', 'v2', ...) 65535个
如有任何侵权行为,请通知我删除,谢谢大家!
个人邮箱:865460609@qq.com
推荐文章: