计算机基础@产品程序员

数据库物理设计

2018-03-23  本文已影响64人  木可大大

物理设计

物理设计就是根据所选择的关系型数据库的特点对逻辑模型进行存储结构设计。它涉及的内容包含以下4方面：1. 定义数据库、表及字段的命名规范；2. 选择合适的存储引擎；3. 为表中的字段选择合适的数据类型；4. 建立数据库结构。

定义数据库、表及字段的命名规范

存储引擎

数据类型

原则：当一个列可以选择多种数据类型时，应该优先考虑数值类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据

类型，应该优先选择占用空间小的数据类型。

1. 数值类型

数值类型可以分为整数类型和实数类型。

image.png

其中，M表示整数类型的最大显示宽度； 对于浮点和定点类型，M是可以存储的总位数（精度）；对于字符串类型，M是最大长度。 M

的最大允许值取决于数据类型。注意：当我们只存储两位数时，一种错误用法就是int(2)，其实int(i)存储空间是由数据类型和是否是

unsigned决定的，i只是表示显示长度。int(11)和int(1)的存储空间是没差别的， int(11)的数据长度如果小于11，则默认补充空格，如果

int (11)字段被zerofill修饰，则默认补充0，而且字段被zerofill修饰会自动添加unsigned。因此，当我们只存储两位数时，使用tinyint才能

达到我们简约空间的目的。

注意：Decimal类型是精确类型的，如果我们需要存储精确数据，例如财务数据就必须使用Decimal类型，而不能使用float和double类型。

2. 字符串类型

字符串类型中有两种类型：char和varchar。


image.png
varchar(n)的存储规范
create table t1(c varchar(n)) charset=gbk,则此处n的最大值为(65535-1-2)/2=32766

create t(c int ,c2 char(30),c3 varchar(n)) charset=utf8,则此处n的最大值（65535-1-2-4-90）/3=21812

能导致存储页的分裂，引起存储碎片；使用多字节字符集存储字符串，以UTF-8为例，存储中文需要3个字节，而存储英文或数字只需

要1个字节。

char(n)的存储规范

使用char(1)就比varchar(1)更节省空间，因为varchar还需要存储额外字节存储其他信息；char类型适合存储经常更新的字符序列，

由于char类型的长度是固定的，MySQL会一次性地分配存储空间，在多次更新时也不会出现页分裂的情况，减少存储碎片。

image.png

3. 日期类型

image.png

注意：timestamp占4byte，实际上是用int存储的。由于只有4byte，因此它只能显示1970-01-01 到 2038-01-19，也正是这个原因，如

果在这个时间内的字段推荐使用timestamp。timestamp类型显示依赖于所指定的时区。除此之外，还需要注意一点，**在行的数据

修改时可以自动修改timestamp列的值**，这个特性可以帮助我们在进行数据分析时自动提取出最新变化的数据。

MySQL5.5 datetime类型字段不能设置默认值为now()

MySQL5.6 datetime类型字段可以设置默认值为now()

建立数据库结构

1. DML（Data manipulation language，数据操作语言）

2. DDL（Data definition language，数据定义语言）

create table tea(id int not null auto_increment,name varchar(6) not null,remark char(6),primary key(id));
show create table tea;

修改字段定义:
ALTER TABLE emp MODIFY first_name VARCHAR(20) NOT NULL DEFAULT '-';  
添加字段:
alter table emp add column age int(3) not null default 0;  
修改字段名字，同时修改定义:
alter table emp change age age1 int(4) not null default 0;  
在表Column的尾部追加新的column:
alter table emp add birth date not null after empno;  
移动column到表column的首位:
alter table emp modify age1 int(3) not null default 0 first;  
将某个column移动到另一个columon后面:
alter table department change deptno deptno int(11) NOT NULL after deptname;  
删除主键:
alter visit_log drop primary key;  
修改表名:
alter table emp rename employees; 
删除字段
alter table guess_product_info  drop column backstyle;

3. DCL（Data Control Language，数据控制语言）

赋予权限
grant all privileges on *.* to 'root'@'localhost' with grant option; 授权远程用户注意不要with grant option
grant select,update,create,delete on *.* to guest@'115.220.46.%' identified by 'test@home';创建业务用户，并要求只限制IP段
查看权限
Select user,password,host from mysql.user;
Show grants for root@'localhost';
select user,password,host ,grant_priv,super_priv from mysql.user;
重命名用户
rename mysql.user root@' ' to admin@' ';
刷新权限
flush privileges
回收权限
revoke select,insesrt,update,delete,create on *.* from 'guest'@'115.220.46.%';
show grants for guest@'115.220.46.%';
修改密码
update mysql.user set password =password('test') where user='guest';password()函数对字符串进行MD5加密
mysqladmin -uroot -ptest password 12345
                                    -p 旧密码  password 新密码

忘记密码
mysqld_safe --defaults-file=/etc/mysql/my3306.cnf --skip-grant-tables &
删除用户
drop user root@'';
删除test数据库
select * from mysql.db where db like '%test%'\G;
delete from mysql.db where db like '%test%';
删除不需要的用户

mysql> select user,password,host from mysql.user;
+------+-------------------------------------------+------------+
| user | password                                  | host       |
+------+-------------------------------------------+------------+
| root |                                           | localhost  |
| root |                                           | nbview.com |
| root |                                           | 127.0.0.1  |
| root |                                           | ::1        |
|      |                                           | localhost  |
|      |                                           | nbview.com |
| kewy | kewy126@home                              | %          |
| root | *08F411191A8F7130F09F0A961DB8E87983620D5B | %          |
+------+-------------------------------------------+------------+
8 rows in set (0.00 sec)
mysql> delete from mysql.user where user='kewy';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    59
Current database: student_db1
Query OK, 1 row affected (0.01 sec)
mysql> delete from mysql.user where user='root' and host='::1';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where user='root' and host='nbview.com';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where host='nbview.com';
Query OK, 1 row affected (0.00 sec)

mysql> delete from mysql.user where host='localhost' and user is null;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from mysql.user where host='127.0.0.1' ;
Query OK, 1 row affected (0.00 sec)
mysql> select user,password,host from mysql.user;
+------+-------------------------------------------+-----------+
| user | password                                  | host      |
+------+-------------------------------------------+-----------+
| root |                                           | localhost |
| root | *08F411191A8F7130F09F0A961DB8E87983620D5B | %         |
+------+-------------------------------------------+-----------+
2 rows in set (0.00 sec)

4. TCL（Transaction Control Language，事务控制语言）

开始事务
START TRANSACTION  
可选:创建一个存档点
SAVEPOINT sp  
开始操作SQL
select  
insert  
...
操作完成提交数据
COMMIT  
或者，操作失败，回滚数据(回滚到某个存档点)
ROLLBACK  
ROLLBACK TO sp  
释放存档点
RELEASE SAVEPOINT sp

总结

在进行数据库物理设计时，我们需要计算每张表的存储空间， 选择存储引擎和表中的数据类型。

image

欢迎关注微信公众号：木可大大，所有文章都将同步在公众号上。

上一篇下一篇

猜你喜欢

热点阅读