Mysql系列002：数据表的基本操作（面试笔试题）

⭐数据表的操作

---

⭐前言

本系列对刚入门的新手十分友好，欢迎各位学习，如有不对的地方，请指出谢谢，本人菜鸡一枚，望各位多多见谅

⭐常用指令

📚查看当前数据库中所有表

show tables;

📚创建表约束
– auto_increment表示自动增长
– not null 表示不允许空
– primary key 主键
– unsigned 无符号
– default 默认值

📚create table 数据表名字(字段 类型 约束[, 字段 类型 约束])
– 创建classes表（id、name）

create table classes(
	id int unsigned auto_increment primary key not null,
	name varchar(30) not null
)

– 创建students表（id, name, age, gender, height, cls_id）

create table students(
	id int unsigned primary key auto_increment not null, 
	name varchar(20), 
	age int, 
	height int, 
	gender enum('male','female', 'neutral', 'secret'), 
	cls_id int, 
	is_delete bit default 0
);

📚查看表结构
– desc 数据表名字;

desc classes;

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

📚查看表的创建语句
– show create table 表名字;

show create table students;

+----------+---------------------------------------------------- --+
| Table    | Create Table                                                                                                                                                                                                                                                                                                      |
+----------+-------------------------------------------------------+
| students | CREATE TABLE `students` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `gender` enum('male','female','neutral','secret') DEFAULT NULL,
  `cls_id` int(11) DEFAULT NULL,
  `is_delete` bit(1) DEFAULT b'0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------+

📚修改数据表编码
– alter table <表名> character set utf8;

alter table students character set utf8;

📚修改表-添加字段
– alter table 表名 add 列名 类型及约束;

alter table students add brithday datetime; 

+-----------+------------------------------------------+------+-----+---------+----------------+
| Field     | Type                                     | Null | Key | Default | Extra          |
+-----------+------------------------------------------+------+-----+---------+----------------+
| id        | int(10) unsigned                         | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20)                              | YES  |     | NULL    |                |
| age       | int(11)                                  | YES  |     | NULL    |                |
| height    | int(11)                                  | YES  |     | NULL    |                |
| gender    | enum('male','female','neutral','secret') | YES  |     | NULL    |                |
| cls_id    | int(11)                                  | YES  |     | NULL    |                |
| is_delete | bit(1)                                   | YES  |     | b'0'    |                |
| birthday  | datetime                                 | YES  |     | NULL    |                |
+-----------+------------------------------------------+------+-----+---------+----------------+

– 添加成绩字段到第1列

alter table students add score int first;

– 添加电话字段到name的后面

alter table students add phone int after name;

📚修改表-修改字段： 不修改列名
– alter table 表名 modify 列名 类型及约束;

alter table students modify birthday date;

+-----------+------------------------------------------+------+-----+---------+----------------+
| Field     | Type                                     | Null | Key | Default | Extra          |
+-----------+------------------------------------------+------+-----+---------+----------------+
| id        | int(10) unsigned                         | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20)                              | YES  |     | NULL    |                |
| age       | int(11)                                  | YES  |     | NULL    |                |
| height    | int(11)                                  | YES  |     | NULL    |                |
| gender    | enum('male','female','neutral','secret') | YES  |     | NULL    |                |
| cls_id    | int(11)                                  | YES  |     | NULL    |                |
| is_delete | bit(1)                                   | YES  |     | b'0'    |                |
| birthday  | date                                     | YES  |     | NULL    |                |
+-----------+------------------------------------------+------+-----+---------+----------------+

📚修改表-修改字段： 修改列名
– alter table 表名 change 原名 新名 类型及约束;

alter table students change birthday birth date default "2008-08-08";

+-----------+------------------------------------------+------+-----+------------+----------------+
| Field     | Type                                     | Null | Key | Default    | Extra          |
+-----------+------------------------------------------+------+-----+------------+----------------+
| id        | int(10) unsigned                         | NO   | PRI | NULL       | auto_increment |
| name      | varchar(20)                              | YES  |     | NULL       |                |
| age       | int(11)                                  | YES  |     | NULL       |                |
| height    | int(11)                                  | YES  |     | NULL       |                |
| gender    | enum('male','female','neutral','secret') | YES  |     | NULL       |                |
| cls_id    | int(11)                                  | YES  |     | NULL       |                |
| is_delete | bit(1)                                   | YES  |     | b'0'       |                |
| birth     | date                                     | YES  |     | 2008-08-08 |                |
+-----------+------------------------------------------+------+-----+------------+----------------+

📚修改表-删除字段
– alter table 表名 drop 列名;

alter table students drop birth;

+-----------+------------------------------------------+------+-----+---------+----------------+
| Field     | Type                                     | Null | Key | Default | Extra          |
+-----------+------------------------------------------+------+-----+---------+----------------+
| id        | int(10) unsigned                         | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20)                              | YES  |     | NULL    |                |
| age       | int(11)                                  | YES  |     | NULL    |                |
| height    | int(11)                                  | YES  |     | NULL    |                |
| gender    | enum('male','female','neutral','secret') | YES  |     | NULL    |                |
| cls_id    | int(11)                                  | YES  |     | NULL    |                |
| is_delete | bit(1)                                   | YES  |     | b'0'    |                |
+-----------+------------------------------------------+------+-----+---------+----------------+

📚删除表
– drop table 表名

drop table students;

---

🌺感谢收看🌺