3:表的基本操作 -MySQL
作者:Yeats_Liao
- 2022-10-23 江西
本文字数:5449 字
阅读完需:约 18 分钟
3.1 提出问题,引入“表“的概念与思维模式 table
表的概念:
数据库类似于厂库,而表呢就是对数据进行抽象分类的货架
注意:在创建数据库的时候一定要记得设置字符编码
3.2 引用数据库和查看数据库中的表
1. 引用数据库
mysql> use student;
Database changed
复制代码
2. 查看数据库中的表
mysql> show tables;
Empty set (0.00 sec)
复制代码
3.3 创建表
创建表
mysql> create table student(
-> id int,
-> name varchar(30),
-> age int,
-> salary int
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> show tables
-> ;
+-----------------------+
| Tables_in_student |
+-----------------------+
| empolyee |
+-----------------------+
复制代码
注意:在 MySQL 中字符串的语句是不使用
String
的,而是使用varchar
,需要表明长度,同时一级一级的写语句能更加整洁明了
3.4 创建表(企业用,有 B 格)
mysql> create table if not exists teacher(
-> id int auto_increment primary key comment'主键id',
-> name varchar(30) not null comment '老师的名字',
-> phone varchar(20) comment '电话号码',
-> address varchar(100) default '暂时未知' comment '住址'
-> )engine=innodb;
Query OK, 0 rows affected (0.03 sec)
复制代码
注意:在创建表的时候,在
default
单引号中间的内容要和所定义的数据类型一样,最后一个属性写完不要用“,
”
关键词解释
auto_increment
: 对字段进行自动增长primary key
: 主键,是关系型数据库连接桥梁,必须填写不能空着comment
: 注释not null
: 不能为空,必须填写default
: 如果为空那么表中这个数据默认为' '中的内容,默认值engine = innodb
: 表示数据库引擎
两种方式创建的有什么区别呢?简单的
mysql> create table empolyee(
-> id int,
-> name varchar(30),
-> age int,
-> salary int
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> show create table empolyee;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| empolyee | CREATE TABLE `empolyee` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
复制代码
复杂的
mysql> create table staff(
-> id int auto_increment primary key comment'主键id',
-> name varchar(30) not null,
-> age int comment'年龄',
-> salary int default '0' comment'薪水'
-> )engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> show create table staff;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| staff | CREATE TABLE `staff` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(30) NOT NULL,
`age` int(11) DEFAULT NULL COMMENT '年龄',
`salary` int(11) DEFAULT '0' COMMENT '薪水',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
复制代码
即使没有写default
,但还是会默认default
3.5 查看表结构
1. 显示出表的 sql 语句
show create table teacher;
复制代码
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| teacher |
+-------------------+
1 row in set (0.00 sec)
mysql> show create table teacher;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(30) NOT NULL COMMENT '老师的名字',
`phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
`address` varchar(100) DEFAULT '暂时未知' COMMENT '住址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
复制代码
2. 查看表的结构
desc teacher;
复制代码
mysql> desc teacher;
+---------+--------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+----------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(100) | YES | | 暂时未知 | |
+---------+--------------+------+-----+----------+----------------+
4 rows in set (0.01 sec)
复制代码
Null
:代表的是否能为空Key PRI
:代表是唯一值Default
:说明是否设立 defaultExtra
:代表的是规则,上述文件是自动增加
3.6 删除表
1. 删除单个表
drop table if exists staff ;
复制代码
2. 删除多个表
drop table if exists staff,empolyee;
复制代码
3.7 修改表
1. 添加一个新的字段
alter table 表名 add 字段名 字段类型
复制代码
mysql> alter table student add name varchar(30);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
复制代码
2. 指定位置添加一个新的字段
在某字段之后
alter table 表名 add 字段名 字段类型 after 字段名
复制代码
mysql> alter table student add age int after id;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
复制代码
在第一行添加
alter table 表名 add 字段 字段类型 first
复制代码
mysql> alter table student add phone int(20) first;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| phone | int(20) | YES | | NULL | |
| id | int(4) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
复制代码
3. 删除字段
alter table 表名 drop 字段
复制代码
mysql> alter table student drop phone;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
复制代码
4. 修改字段(名、类型)
修改名和类型(完全修改)
alter table 表名 change 字段名 新的字段名 字段类型
复制代码
mysql> alter table student change age phone varchar(20);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
复制代码
修改类型
alter table 表名 modify 字段名 要改成的字段类型
复制代码
mysql> alter table student modify phone int(20);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| phone | int(20) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
复制代码
5. 修改表名
alter table 表名 rename to 新表名
复制代码
mysql> alter table student rename to empolyee;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------------+
| Tables_in_qiu_company |
+-----------------------+
| empolyee |
+-----------------------+
1 row in set (0.00 sec)
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 4
版权声明: 本文为 InfoQ 作者【Yeats_Liao】的原创文章。
原文链接:【http://xie.infoq.cn/article/553c5636617699eec7383f6f1】。文章转载请联系作者。
Yeats_Liao
关注
Hello,World! 2022-10-02 加入
这里更多的是记录个人学习,如果有侵权内容请联系我! 个人邮箱是:yeats_liao@foxmail.com
评论