mysql数据库表操作_「MySQL」MySQL数据库及表基础操作

最新推荐文章于 2024-01-08 20:15:00 发布

王润莲

最新推荐文章于 2024-01-08 20:15:00 发布

阅读量253

点赞数

文章标签： mysql数据库表操作

本文链接：https://blog.csdn.net/weixin_42322219/article/details/113656087

版权

一、数据库操作

- 创建数据库

先通过show databases;命令查看现有数据库，结果如下：

#注释 \g与;功能相同

mysql> show databases \g

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

创建一个新的数据库，命令如下：

#注释：格式 create database 数据库名

create database mw_test;

再次查看数据库结果如下：

mysql> show databases;

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

| Database |

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

| information_schema |

| mw_test |

| mysql |

| performance_schema |

| sys |

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

5 rows in set (0.00 sec)

可以发现成功创建了数据库mw_test。

也可以查看单个数据库信息，命令如下：

show create database mw_test;

结果如下：

mysql> show create database mw_test;

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

| Database | Create Database |

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

| mw_test | CREATE DATABASE `mw_test` /*!40100 DEFAULT CHARACTER SET utf8 */ |

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

1 row in set (0.00 sec)

可以看到当前数据库编码为utf8。

PS：新创建数据库的名称不能与现有数据库名称重名

- 删除数据库

首先创建一个新的数据库用于删除操作：

create database mw_test1;

删除操作命令如下：

#注释：格式：drop database 数据库名

drop database mw_test1;

执行结果如下：

mysql> drop database mw_test1;

Query OK, 0 rows affected (0.01 sec)

PS:删除数据库不会有任何提示，删除数据库后，数据库中的表数据也会全部被删除，不能恢复，此操作应提前做好备份，谨慎操作！

二、数据表创建

- 创建表

通过use命令切换到要创建表的数据库进行操作，命令如下：

use mw_test;

查看当前选择数据库下的表：

show tables;

执行结果如下：

mysql> show tables;

Empty set (0.00 sec)

可以发现当前数据库mw_test下没有任何表。

建立名为mw_table的表(暂且不考虑数据类型):

create table mw_table(id INT(10), name VARCHAR(20), age INT(4), updateTime DATE );

继续查看数据表表：

mysql> show tables;

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

| Tables_in_mw_test |

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

| mw_table |

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

1 row in set (0.00 sec)

查看表信息：

#注释：也可以写全称：describe mw_table;

desc mw_table;

结果：

mysql> desc mw_table;

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

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

| id | int(10) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

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

4 rows in set (0.01 sec)

Null 表示该列是否可以存储Null值；

Key 表示该列是否已编制索引；

Default 表示该列是否有默认值；

Extra表示可以获取的与给定列有关的附加信息；

查看表相信信息：

#注释：此命令会显示表所使用的存储引擎及字符编码

show create table mw_table;

结果如下：

mysql> show create table mw_table;

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

| Table | Create Table |

| mw_table | CREATE TABLE `mw_table` (

`id` int(10) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL,

`age` int(4) DEFAULT NULL,

`updateTime` date DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1 row in set (0.05 sec)

- 主键

MySQL基础知识及数据库安装文章中简单介绍过主键的作用，这里在详细说下：

主键，是表中一列或多列的组合。主键约束(Primary Key Constraint)要求主键列的数据唯一性，且不能为空。主键能唯一的标识一条记录，可以结合外键来定义不同数据表之间的关系，且可以增加数据库的查询速度。

单字段主键

可以在创建表时，定义表列的同时指定主键，也可以定义好表结构以后设置主键，具体方法如下：

定义的同时指定主键：

create table mw_table1( id INT(10) PRIMARY KEY, name VARCHAR(20), age INT(4), updateTime DATE );

查看表结构结果如下：

mysql> desc mw_table1;

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

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

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

| age | int(4) | YES | | NULL | |

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

4 rows in set (0.00 sec)

PS：可以发现id的Null为NO，表示不可以为空，Key值为PRI，表示该列为表主键的一部分。

定义好结构后指定主键方法如下：

create table mw_table2( id INT(10), name VARCHAR(20), age INT(4), updateTime DATE, PRIMARY KEY(id) );

查看表结构结果如下：

mysql> desc mw_table2;

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

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

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

| age | int(4) | YES | | NULL | |

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

4 rows in set (0.01 sec)

PS:这种方法就是在表结构设计好以后，在创建语句的最后一行指明设置为主键的名字即可

2.多字段联合主键

create table mw_table3( id INT(10), name VARCHAR(20) , age INT(4), tel VARCHAR(20), updateTime DATE, PRIMARY KEY(name, tel) );

查询表结构结果如下：

mysql> desc mw_table3;

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

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

| id | int(10) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

| tel | varchar(20) | NO | PRI | NULL | |

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

5 rows in set (0.00 sec)

- 外键

用于在两个表的数据之间建立链接，可以是一列或多列。一个表可以存在多于一个的外键，外键可以为空值，若不为空，则每一个外键值必须等同于另一个表中主键的某个值。外键，是一个表中的一个字段，可以不是表的主键，但应对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有相关联关系的行。

既然是外键，那么就需要至少存在两个表，一个主表，一个从表。通过以下方法创建：

#注释：创建主表

create table mw_test_1( id INT(10) PRIMARY KEY, name VARCHAR(20), age INT(4), updateTime DATE );

#注释：创建从表并设置外键

#注释：格式 CONSTRAINT 外键名 FOREIGN KEY(从表外键名) REFERENCES 主表名(主表主键)

create table mw_test_2( tid INT(10) PRIMARY KEY, name VARCHAR(20), tel VARCHAR(20), updateTime DATE, CONSTRAINT fk_test_1_test_2 FOREIGN KEY(tid) REFERENCES mw_test_1(id) );

以上就创建了一个名为fk_test_1_test_2的外键约束，外键为tid，依赖表mw_test_1的主键id

PS:通过以上可以看出，外键可以是当前表的主键，也可以不是主键，也可以以组合的方式设置

查看表结构如下：

mysql> show create table mw_test_2 \G

*************************** 1. row ***************************

Table: mw_test_2

Create Table: CREATE TABLE `mw_test_2` (

`tid` int(10) NOT NULL,

`name` varchar(20) DEFAULT NULL,

`tel` varchar(20) DEFAULT NULL,

`updateTime` date DEFAULT NULL,

PRIMARY KEY (`tid`),

CONSTRAINT `fk_test_1_test_2` FOREIGN KEY (`tid`) REFERENCES `mw_test_1` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

通过Navicat for MySQL软件可以查看外键，如下图：

b89cda888a63?utm_source=oschina-app

mw_test_2_fk_mysql.jpg

- 删除外键约束

删除外键约束会解除主表与从表之间的关联关系！

#注释：格式：ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名

alter table mw_test_2 drop foreign key fk_test_1_test_2;

再次查看表结构：

mysql> show create table mw_test_2 \G

*************************** 1. row ***************************

Table: mw_test_2

Create Table: CREATE TABLE `mw_test_2` (

`tid` int(10) NOT NULL,

`name` varchar(20) DEFAULT NULL,

`tel` varchar(20) DEFAULT NULL,

`updateTime` date DEFAULT NULL,

PRIMARY KEY (`tid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

再次通过Navicat for MySQL软件可以查看外键(需重连数据库)，结果如下图：

b89cda888a63?utm_source=oschina-app

mw_test_2_nofk_mysql.jpg

- 非空约束

顾名思义，就是指字段的值不能为空，对于设置了非空约束的字段没如果添加数据时，没有指定数据，则会报错！

create table mw_table4( id INT(10) PRIMARY KEY, name VARCHAR(20) NOT NULL, updateTime DATE );

- 唯一性约束

数据唯一性约束，要求该列不能有重复值，可以为空，但空只能出现一次。唯一性约束能保证一列或多列不出现重复值。

create table mw_table5( id INT(10) PRIMARY KEY, name VARCHAR(20), tel VARCHAR(20) UNIQUE, updateTime DATE );

查看表结构如下：

mysql> desc mw_table5;

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

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

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

| tel | varchar(20) | YES | UNI | NULL | |

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

4 rows in set (0.00 sec)

也可以通过以下方式设置：

create table mw_table5( id INT(10) PRIMARY KEY, name VARCHAR(20), tel VARCHAR(20), updateTime DATE, CONSTRAINT tel UNIQUE(tel) );

- 默认值约束

指定某列的默认值，即如果不显示的设置值时，自动用默认值填充。

create table mw_table6( id INT(10) PRIMARY KEY, name VARCHAR(20), tel VARCHAR(20) DEFAULT 18888888888, updateTime DATE );

- 设置自增属性

通过AUTO_INCREMENT来指定自增属性，初始值默认为1，一个表中只能有一个字段使用AUTO_INCREMENT，且该字段必须为主键的一部分。可以实现每增加一条新记录后，字段值自动加1，设置AUTO_INCREMENT属性约束的字段可以是任意整数类型。

create table mw_table7( id INT(10) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), tel VARCHAR(20) NOT NULL, updateTime DATE );

查看表结构结果如下：

mysql> desc mw_table7;

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

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

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

| tel | varchar(20) | NO | | NULL | |

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

4 rows in set (0.00 sec)

也可以在创建表时，设置好自增属性后，设置自增的初始值，方法如下：

create table auto_table( id INT(10) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), tel VARCHAR(20) NOT NULL, updateTime DATE) AUTO_INCREMENT=100;

也可在创建好后修改自增的初始值，方法如下：

alter table auto_table AUTO_INCREMENT=20;

三、数据表修改

主要介绍数据库中已存在的表的结构修改。

- 修改表名

#注释：格式：ALTER TABLE 旧表名 RENAME TO 新表名; //TO可以省略

alter table mw_table rename to new_table;

PS:修改表名不影响表结构

- 修改字段名

同样以修改过名称的新表new_table为例，先来查看下当前未修改之前的表结构如下：

mysql> desc new_table;

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

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

| id | int(10) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

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

4 rows in set (0.00 sec)

然后修改字段名：

#注释：格式：ALTER TABLE 表名 CHANGE 要修改的字段名新的字段名新字段的类型

alter table new_table change name username VARCHAR(20);

再次查看表结构如下：

mysql> desc new_table;

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

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

| id | int(10) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

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

4 rows in set (0.00 sec)

PS:这里修改字段名，后面的新字段名对应的数据类型应与原字段的数据类型相同，即达到了修改字段名的目的，当然也可不同，但是字段类型不可省略，看下面说明

- 修改字段数据类型

修改字段数据类型有两种方法，一种是使用上面的change方法，也可以使用modify方法，具体如下：

#注释：modify方式修改，格式：ALTER TABLE 表名 MODIFY 字段名新的字段类型

alter table new_table modify age VARCHAR(10);

查看修改后的表结构：

mysql> desc new_table;

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

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

| id | int(10) | YES | | NULL | |

| age | varchar(10) | YES | | NULL | |

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

4 rows in set (0.00 sec)

使用change方式再次修改回来：

#注释：change方式修改，格式：ALTER TABLE 表名 CHANGE 要修改的字段名新的字段名新字段的类型

alter table new_table change age age INT(4);

PS:上面的修改只做演示之用，不考虑数据类型的合理性。由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录，所有要谨慎修改！

- 添加字段

#注释：格式：ALTER TABLE 表名 ADD 添加的字段名字段类型

alter table new_table add tel VARCHAR(20);

查看表结构如下：

mysql> desc new_table;

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

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

| id | int(10) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

| tel | varchar(20) | YES | | NULL | |

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

5 rows in set (0.00 sec)

添加后可以发现，新添加的字段位于表的最后一条(即表中的最后一列)，如果想在特定位置添加字段，可以使用FIRST或AFTER参数来做修饰，方法如下：

#注释：添加新的字段到第一列,格式：ALTER TABLE 表名 ADD 字段名字段类型 FIRST;

alter table new_table add gender INT(1) first;

#注释：添加新的字段到特定位置，格式 ALTER TABLE 表名 ADD 字段名字段类型 AFTER 添加到该字段的后面

alter table new_table add birthday DATE after age;

查看表结构结果如下：

mysql> desc new_table;

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

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

| gender | int(1) | YES | | NULL | |

| id | int(10) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

| tel | varchar(20) | YES | | NULL | |

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

7 rows in set (0.00 sec)

当然也可以在添加新字段的同时设置数据的完整性约束，如下：

alter table new_table add location varchar(100) not null;

- 修改现有字段的排列位置

#注释：将字段移动到表的第一列格式：ALTER TABLE 表名 MODIFY 字段名字段类型 FIRST;

alter table new_table modify id int(10) first;

#注释：将字段移动到指定位置格式： ALTER TABLE 表名 MODIFY 字段名字段类型 AFTER 要移动到该字段的后面

alter table new_table modify updateTime DATE after location;

查看表结构如下：

mysql> desc new_table;

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

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

| id | int(10) | YES | | NULL | |

| gender | int(1) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

| tel | varchar(20) | YES | | NULL | |

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

8 rows in set (0.00 sec)

- 删除字段

#注释：格式：ALTER TABLE 表名 DROP 字段名

alter table new_table drop gender;

- 修改表的存储引擎

可通过show engines;来查看系统支持的存储引擎，结果如下:

mysql> show engines;

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

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

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

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

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

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

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

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

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

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

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

9 rows in set (0.00 sec)

PS：DEFAULT 表示当前数据库使用的存储引擎

查看当前表使用的存储引擎：

show create table new_table;

结果如下：

mysql> show create table new_table;

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

| Table | Create Table |

| new_table | CREATE TABLE `new_table` (

`id` int(10) DEFAULT NULL,

`username` varchar(20) DEFAULT NULL,

`age` int(4) DEFAULT NULL,

`birthday` date DEFAULT NULL,

`tel` varchar(20) DEFAULT NULL,

`location` varchar(100) NOT NULL,

`updateTime` date DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

修改：

#注释：格式：ALTER TABLE 表名 ENGINE=引擎名

alter table new_table engine=MyISAM;

再次查看，结果如下：

mysql> show create table new_table \G

*************************** 1. row ***************************

Table: new_table

Create Table: CREATE TABLE `new_table` (

`id` int(10) DEFAULT NULL,

`username` varchar(20) DEFAULT NULL,

`age` int(4) DEFAULT NULL,

`birthday` date DEFAULT NULL,

`tel` varchar(20) DEFAULT NULL,

`location` varchar(100) NOT NULL,

`updateTime` date DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

PS:外键约束不能跨引擎使用，虽然数据表可以指定不同的存储引擎，但是如果两个表需要做关联，如果使用不同的存储引擎，则不能创建外键约束

- 删除数据表

删除数据表会删除表的定义和所有数据，所以应谨慎操作，在删除之前做好备份工作！

#注释：格式：DROP TABLE 表名1，表名2

drop table mw_table7, mw_table6;

可以发现可以一次性删除多个数据表，且在删除过程中没有任何提示性信息，但是如果删除一个不存在的表则会报错，如下：

#注释：直接删除不存在的表(mw_table7已经被删除，不存在该表)，如下

mysql> drop table mw_table7;

ERROR 1051 (42S02): Unknown table 'mw_test.mw_table7'

#注释：同时删除两个表，一个存在，一个不存在

mysql> drop table mw_table5,mw_table7;

ERROR 1051 (42S02): Unknown table 'mw_test.mw_table7'

通过上面的结果可以看出，当同时删除多个表时，如果有不存在的表存在，则命令不会执行，可通过以下办法解决：

drop table if exists mw_table5,mw_table7;

!!PS：此处重点说明：当数据表之间存在外键关联的情况下，直接删除主表，会显示失败，可以先删除从表，在删除主表，但是如果不想删除从表，只删除主表，可将主表与从表之间关联关系解除后，在删除主表。删除外键关联的方法上面已经说过，这里不再赘述！

王润莲

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库表操作_「MySQL」MySQL数据库及表基础操作

一、数据库操作- 创建数据库先通过show databases;命令查看现有数据库，结果如下：#注释 \g与;功能相同mysql> show databases \g+--------------------+| Database |+--------------------+| information_schema || mysql || pe...
复制链接

扫一扫

mysql数据库表操作_「MySQL」MySQL数据库及表基础操作

“相关推荐”对你有帮助么？