MySQL的建表规范以及增删改查
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;