MySQL数据表基本操作
数据表基本操作数据表是数据库的重要构成部分，每一个数据库都是由若干个数据表构成。没有数据表就没法在数据库中存放数据。
1.创建数据表
创建数据表是指在已经创建的数据库中建立新表，创建数据表的过程是规定数据列的属性的过程。
CREATETABLE tb1_name
案例1：
创建基本表：
创建如下要求的表tb_emp1
字段名称数据范例备注idint(11)员工编号namevarchar(25)员工名称deptidint(11)地点部分编号salaryfloat工资 ##创建test数据库
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
##切换数据库
mysql> use test;
Database changed
##在test数据库中创建表
mysql> create table tb_emp1(id int(11),name varchar(25),deptid int(11),salary float);
Query OK, 0 rows affected, 2 warnings (0.06 sec)
##查看当前数据库中所有表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_emp1 |
| test_table |
+----------------+
2 rows in set (0.02 sec)
##查询表结构
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field| Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES| | NULL | |
| name | varchar(25) | YES| | NULL | |
| deptid | int | YES| | NULL | |
| salary | float | YES| | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
##查询表定义
mysql> show create table tb_emp1\G;
*************************** 1. row ***************************
Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
`id` int DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`deptid` int DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
案例2：
创建暂时表：
暂时表不生成物理文件，会花内 有用。
mysql> create temporary table new_tb_emp1 select * from tb_emp1;
Query OK, 0 rows affected (0.01 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_emp1 |
| test_table |
+----------------+
2 rows in set (0.00 sec)
mysql> show create table new_tb_emp1;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| new_tb_emp1 | CREATE TEMPORARY TABLE `new_tb_emp1` (
`id` int DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`deptid` int DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.修改数据表
ALTER TABLE命令可以修改表布局，比如增加和删除索引，修改已经存在字段的数据范例，重命名字段或表名称，也可以修改表的存储引擎等特性。
ALTER TABLE tb_name .....
案例1（增，删）
mysql> create table t1(a int,b char(10));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| tb_emp1 |
| test_table |
+----------------+
3 rows in set (0.00 sec)
##修改表名
mysql> alter table t1 rename t2;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t2 |
| tb_emp1 |
| test_table |
+----------------+
3 rows in set (0.00 sec)
mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | int | YES| | NULL | |
| b | char(10) | YES| | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
##增加字段
mysql> alter table t2 add d timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0Duplicates: 0Warnings: 0
##修改数据类型和字段名称
mysql> alter table t2 modify a tinyint not null,change b c char(20);
Query OK, 0 rows affected (0.07 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> desc t2;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| a | tinyint | NO | | NULL | |
| c | char(20)| YES| | NULL | |
| d | timestamp | YES| | NULL | |
+-------+-----------+------+-----+---------+-------+
3 rows
##删除字段
mysql> alter table t2 drop c;
Query OK, 0 rows affected (0.02 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> desc t2;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| a | tinyint | NO | | NULL | |
| d | timestamp | YES| | NULL | |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
##增加自增新字段
mysql> alter table t2 add c int unsigned not null auto_increment,add primary key (c);
Query OK, 0 rows affected (0.07 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> desc t2;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| a | tinyint | NO | | NULL | |
| d | timestamp | YES| | NULL | |
| c | int unsigned | NO | PRI | NULL | auto_increment |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
##指定字段最左增加字段
mysql> alter table t2 add first_col int(11) first;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0Duplicates: 0Warnings: 1
mysql> desc t2;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| first_col | int | YES| | NULL | |
| a | tinyint | NO | | NULL | |
| d | timestamp | YES| | NULL | |
| c | int unsigned | NO | PRI | NULL | auto_increment |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
##指定相对位置增加字段
mysql> alter table t2 add second_col int(11) after d;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0Duplicates: 0Warnings: 1
mysql> desc t2;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| first_col| int | YES| | NULL | |
| a | tinyint | NO | | NULL | |
| d | timestamp | YES| | NULL | |
| second_col | int | YES| | NULL | |
| c | int unsigned | NO | PRI | NULL | auto_increment |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
##修改字段位置
mysql> alter table t2 modify c int unsigned first;
Query OK, 0 rows affected (0.07 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> desc t2;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| c | int unsigned | NO | PRI | NULL | |
| first_col| int | YES| | NULL | |
| a | tinyint | NO | | NULL | |
| d | timestamp | YES| | NULL | |
| second_col | int | YES| | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3.复制数据表
复制指定表的表布局，如列定义和索引定义等，但不复制内容，指定的表不能为视图。
案列：
##为表插入数据
mysql> insert into tb_emp1(id,name,deptid,salary) value(1,'zhangsan',10,1000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb_emp1;
+------+----------+--------+--------+
| id | name | deptid | salary |
+------+----------+--------+--------+
| 1 | zhangsan | 10 | 1000 |
+------+----------+--------+--------+
1 row in set (0.00 sec)
#复制数据表，但不复制数据
mysql> create table like_tb_emp1 like tb_emp1;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| like_tb_emp1 |
| t2 |
| tb_emp1 |
| test_table |
+----------------+
4 rows in set (0.00 sec)
mysql> desc like_tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field| Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES| | NULL | |
| name | varchar(25) | YES| | NULL | |
| deptid | int | YES| | NULL | |
| salary | float | YES| | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select *from like_tb_emp1;
Empty set (0.00 sec)
###要复制表数据，可以通过查询指定数据创建新表
mysql> create table select_tb_emp1 select *from tb_emp1;
Query OK, 1 row affected (0.03 sec)
Records: 1Duplicates: 0Warnings: 0
mysql> select * from select_tb_emp1;
+------+----------+--------+--------+
| id | name | deptid | salary |
+------+----------+--------+--------+
| 1 | zhangsan | 10 | 1000 |
+------+----------+--------+--------+
1 row in set (0.00 sec)
4.删除数据表
在MySQL数据库中，对于不再需要的数据表，可以将其从数据库中删除。
在删除表的同时，表的布局和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。
DROP TABLE 表名......
案例：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| like_tb_emp1 |
| select_tb_emp1 |
| t2 |
| tb_emp1 |
| test_table |
+----------------+
5 rows in set (0.00 sec)
mysql> drop table t2;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| like_tb_emp1 |
| select_tb_emp1 |
| tb_emp1 |
| test_table |
+----------------+
4 rows in set (0.00 sec)
mysql> desc t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]