mysql建库建表流程_记录 MySQL的学习过程（6）建库 建表

1. 创建数据库

create database zabbix charset utf8mb4 collate utf8mb4_general_ci; #charset 指定字符集 collate 指定校对规则

2. 查看数据库

show create database 数据库名

3. 修改数据库字符集

注意:必须从小往大了改，比如utf8--> utf8mb4

mysql> create database wordpress;

Query OK, 1 row affected (0.00 sec)

mysql> show create database wordpress; #默认情况下字符集为latin1

+-----------+----------------------------------------------------------------------+

| Database | Create Database |

+-----------+----------------------------------------------------------------------+

| wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+-----------+----------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> alter database wordpress charset utf8mb4; #修改wordpress的字符集为uft8mb4

Query OK, 1 row affected (0.00 sec)

mysql> show create database wordpress;

+-----------+-----------------------------------------------------------------------+

| Database | Create Database |

+-----------+-----------------------------------------------------------------------+

| wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |

+-----------+-----------------------------------------------------------------------+

1 row in set (0.00 sec)

同理还可以修改字符集校对规则

4.库的定义规范

建库时库名必须使用小写字母(开发环境若是windows，则不区分大小写，这样在linux环境中会出错)

库的名字不能使用数字开头

不能使用内部关键字命名

建库时必须设置字符集

5.表的定义

先来看列属性

PRIMARY KEY : 主键约束，表中只能有一个，非空且唯一

NOT NULL : 非空约束，不允许空值

UNIQUE KEY : 唯一键约束，不允许重复值

DEFAULT : 一般配合 NOT NULl 一起使用

UNSIGNED : 无符号，一般配合数字列使用，非负数

COMMENT : 注释

AUTO_INCREMENT : 自增长的列

建议在建标时，每个列都非空，这样可以避免索引失效

下面开始手动创建一张表

CREATE TABLE stu (

id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号',

sname VARCHAR(255) NOT NULL COMMENT '姓名',

age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',

gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',

intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'

) ENGINE INNODB CHARSET utf8mb4;

6.建表规范

a.表名需要是小写字母,不能以数字开头

b.不能是保留字符，使用和业务有关的表名

c.选择合适的数据类型和长度

d.每个列设置 NOT NULL + 默认值 对于数字使用0填充，对于字符串使用空格或者有效字符串填充

e.每个列加注释

f.表必须设置存储引擎和字符集

g.主键列尽量是无关列或者数字列，最好是自增长的有顺序的

h.enum类型不要保存数字，只能是字符串类型

下面来看一个面试题

图片中的问题

id 应该给数字的数据类型

所有的字段都是varchar类型，要根据实际情况使用varchar的长度

注释不全

创建时间的列的数据类型应为是datetime这类的时间类型，而不是varchar

图片末尾的字符集应该设置成utf8mb4

列该设置not null的要设置not null

查看建表信息

show create table 表名;

如何创建一个和目标表一样结构的表呢

create table test like 目标表名;

修改表操作

以下面的表为例

mysql> desc stu;

+--------+---------------------+------+-----+-------------------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------+------+-----+-------------------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| sname | varchar(255) | NO | | NULL | |

| age | tinyint(3) unsigned | NO | | 0 | |

| gender | enum('m','f','n') | NO | | n | |

| intime | datetime | NO | | CURRENT_TIMESTAMP | |

+--------+---------------------+------+-----+-------------------+----------------+

5 rows in set (0.00 sec)

a.增加一个列名为qq varchar 14 非空

alter table stu add qq varchar(14) NOT NULL comment 'qq num';

注意：在业务繁忙期，进行这些ddl操作会导致锁表，应尽量避开高峰期，但是有些特殊情况需要在高峰期做的话，可以使用

pt-osc这种工具，或者使用最原始的方法，即先将要修改的表复制出一份作为一个临时表，然后再修改临时表的结构，最后再

将临时表名改为正式表

b.增加一个wechat列，要求在sname后面

alter table stu add wechat varchar(64) NOT NULL UNIQUE comment 'wechatid' after sname;

注意：只有after 没有before

c.第一列加一个列

alter table stu add num INT NOT NULL UNIQUE comment 'shenfenzheng' FIRST;

d.删除刚刚添加到列

alter table stu drop num;

注意： 列只能一次删除一个，不能删除多个

注意： 只要是alter的命令都会导致锁表，如果数据量大的话，执行此类操作一定要慎重

e.修改某一列的数据类型

alter table stu modify sname varchar(64);

注意：这样修改后，会将not null 注释都恢复为默认状态，所以完整的写法为

alter table stu modify sname varchar(64) NOT NULL comment 'xingming';

f.即修改列名，又修改列数据类型

alter table stu change gender sex char(4) not null comment 'xingbie';

此例中，将gender修改成了sex，并将enum修改成了char(4)

DML

a.insert

insert into stu VALUES(1,'zhangsan','wx12378s238',18,'m',NOW(),'8888888');

完整的写法为

insert into stu (id,sname,wechat,age,sex,intime,qq) VALUES(2,'zhangsan','wx12378s138',18,'m',NOW(),'8888888');

因为之前定义表的时候，有些列设置为了默认值，所以就可以忽略他们，可以写这样写

insert into stu (sname,wechat,age,sex,qq) VALUES('lisi','wx12378s131',18,'m','8888888');

多行用，分隔

insert into stu (sname,wechat,age,sex,qq) VALUES ('leefor','wx12378s431',18,'m','8888888'),('wangwu','w413378s151',18,'m','8888888'),('zhaoliu','wx12358x431',18,'m','8888888');

b.update

update stu set sname='aaa' where id =1;

一定要加where 条件，否则所有的行都会被update

c.delete

delete from stu where id =1;

同样要加where条件，否则会删除所有的数据

生产环境中屏蔽delete功能，一般在表中新增is_delete的列，然后设置1为已删除，0为未删除，这样可以保存记录