04.管理表

管理表

1. 创建表

直接创建

# 创建表
mysql> create table student(
    -> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(20) NOT NULL,
    -> age tinyint UNSIGNED,
    -> gender ENUM('M','F') default 'M'
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | M       |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert student (name,age) values('zzz',18);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  1 | zzz  |   18 | M      |
+----+------+------+--------+
1 row in set (0.00 sec)

通过查询现存表创建;新表会被直接插入查询而来的数据

mysql> create table teacher select * from student;

通过复制现存的表的表结构创建，但不复制数据

mysql> create table user like student;

创建外键表

mysql> create table school (
    -> id int primary key auto_increment,
    -> name varchar(10));
   
mysql> create table teacher (
    -> id int primary key auto_increment,
    -> name varchar(10),
    -> school_id int,
    -> foreign key(school_id) references school(id));
    
注意：
外键约束要求：
被引用的表（这里是 school）必须已经存在。
被引用的列（这里是 school.id）必须是主键或具有唯一约束（UNIQUE）

2. 查看表

# 查看表
mysql> show tables;
mysql> show tables from db1;

# 查看表创建命令
mysql> show create table teacher;

# 查看表结构
mysql> desc teacher;
mysql> show columns from teacher;


# 查看当前库里的所有表或指定表的状态
mysql> show table status;
mysql> show table status like 'teacher';
mysql> show table status like 'teacher' \G;

# 查看库中的所有表状态
mysql> show table status from db1;
mysql> show table status from db1 \G;

# 查看支持的engine类型
mysql> show engines;

3. 修改表

# 修改表名
mysql> alter table student rename stu;

# 添加字段
mysql> alter table stu add phone varchar(11) after name;

# 修改字段类型
mysql> alter table stu modify phone int;

# 修改字段名称和类型
mysql> alter table stu change column phone mobile char(11);

# 修改字符集
mysql> alter table stu character set utf8;

4. 删除表

# 删除字段
mysql> alter table stu drop column mobile;

# 添加外键
mysql> alter table teacher add foreign key(school_id) references school(id);

# 删除外键（先查看外键名）
mysql> show create table teacher;
mysql> alter table teacher drop foreign key teacher_ibfk_1;


# 删除表
mysql> drop table teacher;

5. 表中插入数据

insert 语句

功能: 一次插入一行或多行数据

mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

# 全值插入
mysql> insert student values(2,'aaa',18,'F');

#部分插入
mysql> insert student(id,name,age) values(3,'sss',16);

6. 更新表中数据

注意:一定要有限制条件，否则将修改所有行的指定字段

mysql> update student set age=18 where id=1;

可利用mysql选项避免此错误
vim /etc/my.cnf
[mysql]
safe-updates

7. 删除表中数据

删除表中数据，但不会自动缩减数据文件的大小。
注意:一定要有限制条件，否则将清空表中的所有数据。

mysql> delete from student where id=1;

# 如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。
mysql> truncate table student;

# 缩减表大小
mysql> optimize table student;

# 删除数据可以使用逻辑删除，添加一个标识字段实现,删除数据即修改标识字段
ALTER TABLE student ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0;

is_deleted = 0：未删除（正常）
is_deleted = 1：已删除（逻辑上）

删除操作变成更新操作（逻辑删除）
UPDATE student SET is_deleted = 1 WHERE id = 1;

恢复数据
UPDATE student SET is_deleted = 0 WHERE id = 1;