mysql 8.3_8.3 - mysql 表操作

最新推荐文章于 2024-10-14 16:07:38 发布

一路涨

最新推荐文章于 2024-10-14 16:07:38 发布

阅读量234

点赞数

文章标签： mysql 8.3

本文链接：https://blog.csdn.net/weixin_30302513/article/details/113289947

版权

本文详细介绍了MySQL中存储引擎的概念，解释了存储引擎与表类型的关系，以及不同存储引擎如MyISAM、InnoDB、Memory、BLACKHOLE等的特性和应用场景。通过实例展示了如何创建指定存储引擎的表，并探讨了表的增删改查操作。

摘要由CSDN通过智能技术生成

什么是存储引擎

mysql中建立的库===>文件夹

库中建立的表===>文件

存储引擎就是表的类型

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等

数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方

法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(即存储和

操作此表的类型)

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql

数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据

自己的需要编写自己的存储引擎

mysql支持的存储引擎

mysql>show engines\G;*************************** 1. row ***************************Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO

XA: NO

Savepoints: NO*************************** 2. row ***************************Engine: MRG_MYISAM

Support: YES

Comment: Collection of identical MyISAM tables

Transactions: NO

XA: NO

Savepoints: NO*************************** 3. row ***************************Engine: CSV

Support: YES

Comment: CSV storage engine

Transactions: NO

XA: NO

Savepoints: NO*************************** 4. row ***************************Engine: BLACKHOLE

Support: YES

Comment:/dev/nullstorage engine (anything you write to it disappears)

Transactions: NO

XA: NO

Savepoints: NO*************************** 5. row ***************************Engine: PERFORMANCE_SCHEMA

Support: YES

Comment: Performance Schema

Transactions: NO

XA: NO

Savepoints: NO*************************** 6. row ***************************Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

XA: YES

Savepoints: YES*************************** 7. row ***************************Engine: ARCHIVE

Support: YES

Comment: Archive storage engine

Transactions: NO

XA: NO

Savepoints: NO*************************** 8. row ***************************Engine: MEMORY

Support: YES

Comment: Hash based, storedin memory, useful fortemporary tables

Transactions: NO

XA: NO

Savepoints: NO*************************** 9. row ***************************Engine: FEDERATED

Support: NO

Comment: Federated MySQL storage engine

Transactions: NULL

XA: NULL

Savepoints: NULL9 rows in set (0.00sec)

ERROR:

No query specified

mysql>show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

mysql>show tables;+---------------+

| Tables_in_db1 |

+---------------+

| t1 |

+---------------+

1 row in set (0.00sec)

# 创建指定引擎的表

mysql> create table t2(id int) engine=innodb;

Query OK,0 rows affected (0.03sec)

# 查看创建的过程

mysql>show create table t2\G;*************************** 1. row ***************************Table: t2

Create Table: CREATE TABLE `t2` (

`id`int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)

ERROR:

No query specified

# 其他类型的引擎方式

mysql> create table t3(id int) engine=memory; # 存在内存中，只有表结构，没有数据

Query OK,0 rows affected (0.00sec)

mysql> create table t4(id int) engine=blackhole; # 存入的数据就没啦

Query OK,0 rows affected (0.00sec)

mysql> create table t5(id int) engine=myisam;

Query OK,0 rows affected (0.00 sec)

mysql> insert into t3 values(1);

Query OK,1 row affected (0.00sec)

mysql> insert into t4 values(1);

Query OK,1 row affected (0.00sec)

mysql> insert into t5 values(1);

Query OK,1 row affected (0.00sec)

mysql> select * fromt3; # 存于内存，重启mysql就啦+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00sec)

mysql> select * fromt4; # 数据没有啦，黑洞

Emptyset (0.00sec)

mysql> select * fromt5;+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00sec)

# 重启mysql 查看t3的数据

leco@leco:/etc/mysql/mysql.conf.d$ /etc/init.d/mysql restart

[ ok ] Restarting mysql (via systemctl): mysql.service.

leco@leco:/etc/mysql/mysql.conf.d$ mysql -uroot -pleco

mysql: [Warning] Using a password on the command lineinterfacecan be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 3Server version:5.7.21-0ubuntu0.16.04.1(Ubuntu)

Oracleis a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;' or '\h' for help. Type '\c'to clear the current input statement.

mysql>use db1;

Reading table informationforcompletion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql>show tables;+---------------+

| Tables_in_db1 |

+---------------+

| t1 |

| t2 |

| t3 |

| t4 |

| t5 |

+---------------+

5 rows in set (0.00sec)

mysql>desc t3;+-------+---------+------+-----+---------+-------+

+-------+---------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00sec)

mysql> select * fromt3; # 发现数据么有啦

Emptyset (0.00 sec)

mysql 表的增删改查

id,name,age,sex 是表字段，其他均是数据

创建表

语法

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

);

#注意：1. 在同一张表中，字段名是不能相同2. 宽度和约束条件可选3. 字段名和类型是必须的

4. []数据中是可有可无的

5. 每行有逗号，除了最后一条数据

详细操作步骤

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| db1 |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00sec)

mysql>create database db2 charset utf8;

Query OK,1 row affected (0.00sec)

mysql>use db2;

Database changed

mysql>create table t1(-> id int,-> name varchar(50),-> sex enum('male','female'),-> age int(3)->);

Query OK,0 rows affected (0.03sec)

mysql>desc t1;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | YES | | NULL | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

插入数据

mysql> insert into t1 values(1,'cmz','male',18); # 单条数据插入

Query OK,1 row affected (0.01sec)

mysql> insert into t1 values(2,'leco','female',10),(3,'loocha','male',8); # 多条数据插入

Query OK,2 rows affected (0.00sec)

Records:2 Duplicates: 0 Warnings: 0mysql> select * fromt1;+------+--------+--------+------+

| id | name | sex | age |

+------+--------+--------+------+

| 1 | cmz | male | 18 |

| 2 | leco | female | 10 |

| 3 | loocha | male | 8 |

+------+--------+--------+------+

3 rows in set (0.00 sec)

修改表结构

语法：1. 修改表名

ALTER TABLE 表名

RENAME 新表名;2. 增加字段

ALTER TABLE 表名

ADD 字段名数据类型 [完整性约束条件…],

ADD 字段名数据类型 [完整性约束条件…];

ALTER TABLE 表名

ADD 字段名数据类型 [完整性约束条件…] FIRST; # 字段放在开头

ALTER TABLE 表名

ADD 字段名数据类型 [完整性约束条件…] AFTER 字段名; # 字段放在 after字段，该字段后3. 删除字段

ALTER TABLE 表名

DROP 字段名;4. 修改字段

ALTER TABLE 表名

MODIFY 字段名数据类型 [完整性约束条件…];

ALTER TABLE 表名

CHANGE 旧字段名新字段名旧数据类型 [完整性约束条件…];

ALTER TABLE 表名

CHANGE 旧字段名新字段名新数据类型 [完整性约束条件…];

示例

示例：1. 修改存储引擎

mysql>alter table service-> engine=innodb;2. 添加字段

mysql>alter table student10-> add name varchar(20) not null,-> add age int(3) not null default 22;

mysql>alter table student10-> add stu_num varchar(10) not null after name; //添加name字段之后

mysql>alter table student10-> add sex enum('male','female') default 'male' first; //添加到最前面

3. 删除字段

mysql>alter table student10->drop sex;

mysql>alter table service->drop mac;4. 修改字段类型modify

mysql>alter table student10-> modify age int(3);

mysql>alter table student10-> modify id int(11) not null primary key auto_increment; //修改为主键

5. 增加约束(针对已有的主键增加auto_increment)

mysql> alter table student10 modify id int(11) not nullprimary key auto_increment;

ERROR1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not nullauto_increment;

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0

6. 对已经存在的表增加复合主键

mysql>alter table service2->add primary key(host_ip,port);7. 增加主键

mysql>alter table student1-> modify name varchar(10) not nullprimary key;8. 增加主键和自动增长

mysql>alter table student1-> modify id int not nullprimary key auto_increment;9. 删除主键

a. 删除自增约束

mysql> alter table student10 modify id int(11) not null;

b. 删除主键

mysql>alter table student10->drop primary key;

五、复制表

ｓｑｌ

具体演示操作

mysql>show tables;+---------------+

| Tables_in_db2 |

+---------------+

| t1 |

+---------------+

1 row in set (0.00sec)

mysql>desc t1;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | YES | | NULL | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

mysql> select * fromt1;+------+--------+--------+------+

| id | name | sex | age |

+------+--------+--------+------+

| 1 | cmz | male | 18 |

| 2 | leco | female | 10 |

| 3 | loocha | male | 8 |

+------+--------+--------+------+

3 rows in set (0.00sec)

#1. 修改存储引擎

mysql> alter table t1 engine=innodb;

Query OK,0 rows affected (0.07sec)

Records:0 Duplicates: 0 Warnings: 0mysql>show create table t1\G;*************************** 1. row ***************************Table: t1

Create Table: CREATE TABLE `t1` (

`id`int(11) DEFAULT NULL,

`name` varchar(50) DEFAULT NULL,

`sex`enum('male','female') DEFAULT NULL,

`age`int(3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)

ERROR:

No query specified

#2. 添加字段

mysql> create table student(id int);

Query OK,0 rows affected (0.02sec)

mysql>desc student;+-------+---------+------+-----+---------+-------+

+-------+---------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00sec)

mysql> alter table student add name varchar(20) not null, # 默认不能为空-> add age int(10) not null default 22; # 默认不能为空，默认值是22

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc student;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| age | int(10) | NO | | 22 | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00sec)

# 添加到最开始

mysql>desc student;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+mysql> alter table student add sex enum('male','female') default 'male'first;

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc student;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | YES | | NULL | |

+-------+-----------------------+------+-----+---------+-------+

3 rows in set (0.00sec)

# 插入××之后

mysql>desc student;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | YES | | NULL | |

+-------+-----------------------+------+-----+---------+-------+

3 rows in set (0.00sec)

mysql> alter table student add stu_num varchar(10) not nullafter id; #插入在ｉｄ之后

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc student;+---------+-----------------------+------+-----+---------+-------+

+---------+-----------------------+------+-----+---------+-------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | YES | | NULL | |

+---------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

#3. 删除字段

mysql>desc student;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| age | int(10) | NO | | 22 | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00sec)

mysql>alter table student drop age;

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc student;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00sec)

#4. 修改字段类型modify

mysql>desc student;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | YES | | NULL | |

| age | int(3) | NO | | 22 | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

mysql> alter table student modify name varchar(10) not null;

Query OK,0 rows affected (0.05sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc student;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | YES | | NULL | |

| age | int(3) | NO | | 22 | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

mysql> alter table student modify id int(11) not nullprimary key auto_increment; # 修改id为主键

Query OK,0 rows affected (0.06sec)

Records:0 Duplicates: 0 Warnings: 0mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+

+-------+-----------------------+------+-----+---------+----------------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(3) | NO | | 22 | |

+-------+-----------------------+------+-----+---------+----------------+

4 rows in set (0.00sec)

#5. 增加约束(针对已有的主键增加auto_increment)

mysql> alter table student modify id int(11) not nullprimary key auto_increment;

Query OK,0 rows affected (0.06sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc student;+-------+-----------------------+------+-----+---------+----------------+

+-------+-----------------------+------+-----+---------+----------------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(3) | NO | | 22 | |

+-------+-----------------------+------+-----+---------+----------------+

4 rows in set (0.00sec)

mysql> alter table student modify id int(11) not nullprimary key auto_increment;

ERROR1068 (42000): Multiple primary key defined

mysql> alter table student modify id int(11) not nullauto_increment;

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc student;+-------+-----------------------+------+-----+---------+----------------+

+-------+-----------------------+------+-----+---------+----------------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(3) | NO | | 22 | |

+-------+-----------------------+------+-----+---------+----------------+

4 rows in set (0.00sec)

#6. 对于存在的表增加复合主键

mysql>desc student;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | NO | | NULL | |

| age | int(3) | NO | | 22 | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

mysql>alter table student add primary key(id,age);

Query OK,0 rows affected (0.05sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc student;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | NO | PRI | NULL | |

| age | int(3) | NO | PRI | 22 | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

#7.　增加主键

mysql> alter table student modify id int(11) not nullprimary key;

Query OK,0 rows affected (0.06sec)

Records:0 Duplicates: 0 Warnings: 0#8. 增加主键，自动增长

mysql> alter table student modify id int(11) not nullprimary key auto_increment;

Query OK,0 rows affected (0.06sec)

Records:0 Duplicates: 0 Warnings: 0#9. 删除主键

#1. 先删除子增约束

#2. 删除主键

mysql>desc student;+-------+-----------------------+------+-----+---------+----------------+

+-------+-----------------------+------+-----+---------+----------------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | int(3) | NO | | 22 | |

+-------+-----------------------+------+-----+---------+----------------+

4 rows in set (0.00sec)

mysql> alter table student modify id int(11) not null;

Query OK,0 rows affected (0.06sec)

Records:0 Duplicates: 0 Warnings: 0mysql>alter table student drop primary key;

Query OK,0 rows affected (0.05sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc student;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| sex | enum('male','female') | YES | | male | |

| id | int(11) | NO | | NULL | |

| age | int(3) | NO | | 22 | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

复制表

1. 复制表结构 + 记录(数据)

2. 只复制表结构，不复制记录

1. 复制部分表结构

2. 复制全部表结构

复制表结构＋记录 (key不会复制: 主键、外键和索引)

# 复制表结构哦和数据

root@leco:~# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 6Server version:5.7.21-0ubuntu0.16.04.1(Ubuntu)

Oracleis a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;' or '\h' for help. Type '\c'to clear the current input statement.

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| db1 |

| db2 |

| mysql |

| performance_schema |

| sys |

+--------------------+

6 rows in set (0.00sec)

mysql>use db2;

Reading table informationforcompletion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql>show tables;+---------------+

| Tables_in_db2 |

+---------------+

| t1 |

+---------------+

1 row in set (0.00sec)

mysql>desc t1;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | YES | | NULL | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

mysql> select × fromt1;

ERROR1054 (42S22): Unknown column '×' in 'field list'mysql> select * fromt1;+------+--------+--------+------+

| id | name | sex | age |

+------+--------+--------+------+

| 1 | cmz | male | 18 |

| 2 | leco | female | 10 |

| 3 | loocha | male | 8 |

+------+--------+--------+------+

3 rows in set (0.00sec)

mysql> create table new_t1 select * fromt1;

Query OK,3 rows affected (0.03sec)

Records:3 Duplicates: 0 Warnings: 0mysql>show tables;+---------------+

| Tables_in_db2 |

+---------------+

| new_t1 |

| t1 |

+---------------+

2 rows in set (0.00sec)

mysql>desc new_t1;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | YES | | NULL | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.01sec)

mysql> select * fromnew_t1;+------+--------+--------+------+

| id | name | sex | age |

+------+--------+--------+------+

| 1 | cmz | male | 18 |

| 2 | leco | female | 10 |

| 3 | loocha | male | 8 |

+------+--------+--------+------+

3 rows in set (0.00sec)

# 只复制表结构，不复制数据

mysql> create table t2 select * from t1 where 1=2; # 条件为假，查不到任何记录，此时就只会复制表结构，不复制表数据

Query OK,0 rows affected (0.03sec)

Records:0 Duplicates: 0 Warnings: 0mysql>show tables;+---------------+

| Tables_in_db2 |

+---------------+

| new_t1 |

| t1 |

| t2 |

+---------------+

3 rows in set (0.00sec)

mysql>desc t2;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | YES | | NULL | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

mysql> select * fromt2;

Emptyset (0.00sec)

mysql>create table t3 like t1;

Query OK,0 rows affected (0.03sec)

mysql>show tables;+---------------+

| Tables_in_db2 |

+---------------+

| new_t1 |

| t1 |

| t2 |

| t3 |

+---------------+

4 rows in set (0.00sec)

mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+

+-------+-----------------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | YES | | NULL | |

+-------+-----------------------+------+-----+---------+-------+

4 rows in set (0.00sec)

mysql> select * fromt3;

Emptyset (0.00 sec)

注意：

create table t2 select * from t1 where 1=2; 和 create table t3 like t1;都是创建表结构有神码区别？

前者可以选择性的复制，比如只复制其中部分字段的表结构，而后者是全部复制表结构字段。

删除表

DROP TABLE 表名;

mysql>show tables;+---------------+

| Tables_in_db2 |

+---------------+

| new_t1 |

| t1 |

| t2 |

| t3 |

+---------------+

4 rows in set (0.00sec)

mysql>drop table t3; # 指定表名

Query OK,0 rows affected (0.02sec)

mysql>show tables;+---------------+

| Tables_in_db2 |

+---------------+

| new_t1 |

| t1 |

| t2 |

+---------------+

3 rows in set (0.01 sec)

一路涨

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫