MySQL的建表规范以及增删改查

TABLE规范ALTERMODIFYCREATECHANGE

欢迎来到 来到大浪涛天的博客 ！

一、MySQL的建表规范和流程

一、MySQL的建表规范和流程

1. 建表规范

表名小写字母,不能数字开头,
不能是保留字符,使用和业务有关的表名
选择合适的数据类型及长度
每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
没个列设置注释
表必须设置存储引擎和字符集
主键列尽量是无关列数字列,最好是自增长
enum类型不要保存数字,只能是字符串类型

2. 列属性

PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
NOT NULL : 非空约束,不允许空值
UNIQUE KEY : 唯一键约束,不允许重复值
DEFAULT : 一般配合 NOT NULL 一起使用.
UNSIGNED : 无符号,一般是配合数字列,非负数
COMMENT : 注释
AUTO_INCREMENT : 自增长的列

3. 整型

tinyint ：短整型
int ：长整型

4. 字符串类型

char(100)

定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充

varchar(100)

变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间.
会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)

如何选择这两个数据类型?

少于255个字符串长度,定长的列值,选择char
多于255字符长度,变长的字符串,可以选择varchar

5. enum 枚举数据类型

在数据不大例如省份或者城市这些可以采取枚举类型，这样可以省空间而且加快查询速度

address enum('sz','sh','bj'.....)
			  1    2    3

6. 时间

datetime
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
timestamp
范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。

7. 建表事例

7-1. 表的操作 (注意操作表的时候为了保证数据的唯一性会进行锁表，需要提前申请临时操作或者pt-osc工具进行操作)

7-1-1. 查询建表信息

SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu;

7-1-2. 创建一个表结构一样的表,也就是复制表

mysql> CREATE TABLE teacher1 LIKE teacher;
Query OK, 0 rows affected (0.02 sec)

mysql> desc teacher1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| tno   | int(11)      | NO   | PRI | NULL    | auto_increment |
| tname | varchar(250) | NO   |     | NULL    |                |
| cno   | int(11)      | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

7-1-3. 删除表，生产基本不用

mysql> SHOW TABLES;
+---------------+
| Tables_in_stu |
+---------------+
| course        |
| score         |
| student       |
| teacher       |
| teacher1      |
+---------------+
5 rows in set (0.01 sec)

mysql> DROP TABLE teacher1;
Query OK, 0 rows affected (0.00 sec)

7-1-4. 修改表，如在教师表上增加一个联系电话列

mysql> ALTER TABLE teacher ADD phone INT NOT NULL DEFAULT 0 COMMENT '联系电话';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| tno   | int(11)      | NO   | PRI | NULL    | auto_increment |
| tname | varchar(250) | NO   |     | NULL    |                |
| cno   | int(11)      | NO   |     | NULL    |                |
| phone | int(11)      | NO   |     | 0       |                |
+-------+--------------+------+-----+---------+----------------+

7-1-5. 修改表，如在联系电话后面加上QQ号

mysql> ALTER TABLE teacher ADD qq INT NOT NULL DEFAULT 0 COMMENT 'QQ号' AFTER phone;      
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| tno   | int(11)      | NO   | PRI | NULL    | auto_increment |
| tname | varchar(250) | NO   |     | NULL    |                |
| cno   | int(11)      | NO   |     | NULL    |                |
| phone | int(11)      | NO   |     | 0       |                |
| qq    | int(11)      | NO   |     | 0       |                |
+-------+--------------+------+-----+---------+----------------+

7-1-6. 修改表，在教师编号的前面加上教师的入职时间

mysql> ALTER TABLE teacher ADD tintime DATETIME NOT NULL DEFAULT NOW() COMMENT '入职时间' FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC teacher;
+---------+--------------+------+-----+-------------------+----------------+
| Field   | Type         | Null | Key | Default           | Extra          |
+---------+--------------+------+-----+-------------------+----------------+
| tintime | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
| tno     | int(11)      | NO   | PRI | NULL              | auto_increment |
| tname   | varchar(250) | NO   |     | NULL              |                |
| cno     | int(11)      | NO   |     | NULL              |                |
| phone   | int(11)      | NO   |     | 0                 |                |
| qq      | int(11)      | NO   |     | 0                 |                |
+---------+--------------+------+-----+-------------------+----------------+

7-1-7. 删除不需要的相关列，生产基本不用

mysql> ALTER TABLE teacher DROP qq;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC teacher;
+---------+--------------+------+-----+-------------------+----------------+
| Field   | Type         | Null | Key | Default           | Extra          |
+---------+--------------+------+-----+-------------------+----------------+
| tintime | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
| tno     | int(11)      | NO   | PRI | NULL              | auto_increment |
| tname   | varchar(250) | NO   |     | NULL              |                |
| cno     | int(11)      | NO   |     | NULL              |                |
| phone   | int(11)      | NO   |     | 0                 |                |
+---------+--------------+------+-----+-------------------+----------------+

7-1-8. 修改列属性，如修改tname列的属性

mysql> ALTER TABLE teacher MODIFY tname VARCHAR(100) NOT NULL DEFAULT 0 COMMENT '教师姓名';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC teacher;
+---------+--------------+------+-----+-------------------+----------------+
| Field   | Type         | Null | Key | Default           | Extra          |
+---------+--------------+------+-----+-------------------+----------------+
| tintime | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
| tno     | int(11)      | NO   | PRI | NULL              | auto_increment |
| tname   | varchar(100) | NO   |     | 0                 |                |
| cno     | int(11)      | NO   |     | NULL              |                |
| phone   | int(11)      | NO   |     | 0                 |                |
+---------+--------------+------+-----+-------------------+----------------+

7-2. 修改列属性，连同列名一起修改

mysql> ALTER TABLE teacher CHANGE phone iphone INT NOT NULL DEFAULT 0 COMMENT '手机号码';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc teacher;
+---------+--------------+------+-----+-------------------+----------------+
| Field   | Type         | Null | Key | Default           | Extra          |
+---------+--------------+------+-----+-------------------+----------------+
| tintime | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
| tno     | int(11)      | NO   | PRI | NULL              | auto_increment |
| tname   | varchar(100) | NO   |     | 0                 |                |
| cno     | int(11)      | NO   |     | NULL              |                |
| iphone  | int(11)      | NO   |     | 0                 |                |
+---------+--------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

7-2-1. 插入数据

标准插入，最规范的

mysql> INSERT INTO student(sno,sname,ssex,sage,sintime) VALUES(1,'小红','w',20,NOW());  
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student;
+-----+--------+------+------+---------------------+
| sno | sname  | ssex | sage | sintime             |
+-----+--------+------+------+---------------------+
|   1 | 小红   | w    |   20 | 2020-09-18 20:33:49 |
+-----+--------+------+------+---------------------+

最简洁的录入方式：

mysql> INSERT student VALUES(2,'张明','m',23,NOW());   
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+-----+--------+------+------+---------------------+
| sno | sname  | ssex | sage | sintime             |
+-----+--------+------+------+---------------------+
|   1 | 小红   | w    |   20 | 2020-09-18 20:33:49 |
|   2 | 张明   | m    |   23 | 2020-09-18 20:46:10 |
+-----+--------+------+------+---------------------+

一次录入多个数据

mysql> INSERT student VALUES
    -> (3,'张凯','m','34',NOW()),
    -> (4,'张合','m','33',NOW()),
    -> (5,'刘芳','w','27',NOW());
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from student;
+-----+--------+------+------+---------------------+
| sno | sname  | ssex | sage | sintime             |
+-----+--------+------+------+---------------------+
|   1 | 小红   | w    |   20 | 2020-09-18 20:33:49 |
|   2 | 张明   | m    |   23 | 2020-09-18 20:46:10 |
|   3 | 张凯   | m    |   34 | 2020-09-18 23:15:08 |
|   4 | 张合   | m    |   33 | 2020-09-18 23:15:08 |
|   5 | 刘芳   | w    |   27 | 2020-09-18 23:15:08 |
+-----+--------+------+------+---------------------+

针对性的录入数据

mysql> INSERT INTO student(sname,ssex,sage)
    -> VALUES
    -> ('范恒','m',33),
    -> ('赵箱子','f',35),
    -> ('菜菜','f',28);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+-----+-----------+------+------+---------------------+
| sno | sname     | ssex | sage | sintime             |
+-----+-----------+------+------+---------------------+
|   1 | 小红      | w    |   20 | 2020-09-18 20:33:49 |
|   2 | 张明      | m    |   23 | 2020-09-18 20:46:10 |
|   3 | 张凯      | m    |   34 | 2020-09-18 23:15:08 |
|   4 | 张合      | m    |   33 | 2020-09-18 23:15:08 |
|   5 | 刘芳      | w    |   27 | 2020-09-18 23:15:08 |
|   6 | 范恒      | m    |   33 | 2020-09-18 23:24:51 |
|   7 | 赵箱子    | f    |   35 | 2020-09-18 23:24:51 |
|   8 | 菜菜      | f    |   28 | 2020-09-18 23:24:51 |
+-----+-----------+------+------+---------------------+

7-3. 创建一个学生系统成绩数据库表

7-3-1. 创建教师表


CREATE TABLE teacher(
tno INT PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT '教师编号',
tname VARCHAR(250) NOT NULL COMMENT '教师名字'
)ENGINE INNODB CHARSET utf8mb4 COMMENT '教师表';

7-3-2. 创建学生表

CREATE TABLE student(
sno INT PRIMARY KEY  AUTO_INCREMENT NOT NULL COMMENT '学号',
sname VARCHAR(250) NOT NULL  COMMENT '学生姓名',
sage TINYINT NOT NULL DEFAULT 0 COMMENT '学生年龄',
ssex ENUM('f','n','m') NOT NULL DEFAULT 'm' COMMENT '学生性别'
)ENGINE INNODB CHARSET utf8mb4 COMMENT '学生表';

7-3-3. 创建课程表

CREATE TABLE course(
cno INT PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT '课程编号',
cname VARCHAR(250) NOT NULL COMMENT '课程名字',
tno INT NOT NULL COMMENT '教师编号'
)ENGINE INNODB CHARSET utf8mb4 COMMENT '课程表';

7-3-4. 创建成绩表

CREATE TABLE score(
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score CHAR(20) NOT NULL COMMENT '成绩'
)ENGINE INNODB CHARSET utf8mb4 COMMENT '成绩表';

7-3-5. 查询所创建的表的表结构

show tables;
desc student;
desc course ;
desc teacher ;
desc score;

posted @ 2020-09-22 21:37 OuYangTao 阅读(276) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

大浪涛天的博客

chacha51的一路感悟与总结

MySQL的建表规范以及增删改查

MySQL的建表规范以及增删改查

一、MySQL的建表规范和流程

1. 建表规范

2. 列属性

3. 整型

4. 字符串类型

5. enum 枚举数据类型

6. 时间

7. 建表事例

7-1. 表的操作 (注意操作表的时候为了保证数据的唯一性会进行锁表，需要提前申请临时操作或者pt-osc工具进行操作)

7-1-1. 查询建表信息

7-1-2. 创建一个表结构一样的表,也就是复制表

7-1-3. 删除表，生产基本不用

7-1-4. 修改表，如在教师表上增加一个联系电话列

7-1-5. 修改表，如在联系电话后面加上QQ号

7-1-6. 修改表，在教师编号的前面加上教师的入职时间

7-1-7. 删除不需要的相关列，生产基本不用

7-1-8. 修改列属性，如修改tname列的属性

7-2. 修改列属性，连同列名一起修改

7-2-1. 插入数据

7-3. 创建一个学生系统成绩数据库表

7-3-1. 创建教师表

7-3-2. 创建学生表

7-3-3. 创建课程表

7-3-4. 创建成绩表

7-3-5. 查询所创建的表的表结构

公告