本文给大家分享的是mysql学习笔记系列文章的入门篇，主要讲述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)
以上就是mysql学习之表的基本操作的代码分享的详细内容。