mysql学习笔记之表的基本操作

创建表

create table 表名

create table if not exists 表名

mysql> create database company;
query ok, 1 row affected (0.00 sec)
mysql> use company;
database changed
mysql> create table if not exists t_dept(
  -> deptno int,
  -> dname varchar(20),
  -> loc varchar(40));
query ok, 0 rows affected (0.20 sec)
mysql> show tables;
+-------------------+
| tables_in_company |
+-------------------+
| t_dept      |
+-------------------+
1 row in set (0.00 sec)
mysql>

显示当前库下的所有表

show tables;

mysql> show tables;
+-------------------+
| tables_in_company |
+-------------------+
| t_dept      |
+-------------------+
1 row in set (0.00 sec)

查看表的结构

describe 表名


简写

desc 表名

mysql> describe t_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

查看表的详细

show create table 表名

mysql> show create table t_dept;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table | create table                                                                            |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_dept | create table `t_dept` (
 `deptno` int(11) default null,
 `dname` varchar(20) default null,
 `loc` varchar(40) default null
) engine=innodb default charset=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
show create table t_dept \g

mysql> show create table t_dept \g
*************************** 1. row ***************************
    table: t_dept
create table: create table `t_dept` (
 `deptno` int(11) default null,
 `dname` varchar(20) default null,
 `loc` varchar(40) default null
) engine=innodb default charset=utf8
1 row in set (0.00 sec)

删除表

drop table 表名
drop table if exists 表名

mysql> drop table if exists t_dept;
query ok, 0 rows affected (0.12 sec)
mysql> show tables;
empty set (0.00 sec)

修改表名

alter table old_table_name rename [to] new_table_name
old_table_name 原表名
new_table_name 新表名
将t_dept修改为tab_dept

mysql> alter table t_dept rename tab_dept;
query ok, 0 rows affected (0.09 sec)
mysql> show tables;
+-------------------+
| tables_in_company |
+-------------------+
| tab_dept     |
+-------------------+
1 row in set (0.00 sec)
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

为表增加一个字段默认在最后
alter table table_name add 属性名 属性类型


为tab_dept增加一个字段descri varchar(20)

mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table tab_dept add descri varchar(20);
query ok, 0 rows affected (0.33 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | varchar(20) | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

在表的第一个位置增加一个字段

alter table table_name add 属性名 属性类型 first

mysql> alter table tab_dept add id int first;
query ok, 0 rows affected (0.38 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id   | int(11)   | yes |   | null  |    |
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | varchar(20) | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

在表的指定字段之后增加字段

alter table table_name add 属性名 属性类型 after 属性名

mysql> alter table tab_dept add comm varchar(20) after dname;
query ok, 0 rows affected (0.31 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id   | int(11)   | yes |   | null  |    |
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| comm  | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | varchar(20) | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

删除字段

alter table table_name drop 属性名

mysql> alter table tab_dept drop comm;
query ok, 0 rows affected (0.32 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id   | int(11)   | yes |   | null  |    |
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | varchar(20) | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

字段修改-修改字段数据类型
alter table table_name modify 属性名 数据类型

mysql> alter table tab_dept modify descri int;
query ok, 0 rows affected (0.45 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id   | int(11)   | yes |   | null  |    |
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | int(11)   | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

字段修改-修改字段名称

alter table table_name change 旧属性名 新属性名 旧数据类型

mysql> alter table tab_dept change id deptid int;
query ok, 0 rows affected (0.07 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| deptid | int(11)   | yes |   | null  |    |
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | int(11)   | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

字段修改-同时修改字段名称与数据类型

alter table table_name change 旧属性名 新属性名 新数据类型

mysql> alter table tab_dept change deptid id varchar(32);
query ok, 0 rows affected (0.49 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id   | varchar(32) | yes |   | null  |    |
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | int(11)   | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改顺序

alter table table_name modify 属性名1 数据类型 first|after 属性名2

2个属性必须存在
将deptno调到第一个位置

mysql> alter table tab_dept modify deptno int first;
query ok, 0 rows affected (0.33 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)   | yes |   | null  |    |
| id   | varchar(32) | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | int(11)   | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

将id放在最后

mysql> alter table tab_dept modify deptno int after descri;
query ok, 0 rows affected (0.29 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id   | varchar(32) | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | int(11)   | yes |   | null  |    |
| deptno | int(11)   | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table tab_dept modify deptno int first;
query ok, 0 rows affected (0.34 sec)
records: 0 duplicates: 0 warnings: 0
mysql> alter table tab_dept modify id int after descri;
query ok, 0 rows affected (0.47 sec)
records: 0 duplicates: 0 warnings: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| field | type    | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)   | yes |   | null  |    |
| dname | varchar(20) | yes |   | null  |    |
| loc  | varchar(40) | yes |   | null  |    |
| descri | int(11)   | yes |   | null  |    |
| id   | int(11)   | yes |   | null  |    |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)