写点什么

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 :说明是否设立 default

  • Extra :代表的是规则,上述文件是自动增加

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
用户头像

Yeats_Liao

关注

Hello,World! 2022-10-02 加入

这里更多的是记录个人学习,如果有侵权内容请联系我! 个人邮箱是:yeats_liao@foxmail.com

评论

发布
暂无评论
3:表的基本操作-MySQL_数据库_Yeats_Liao_InfoQ写作社区