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

96
陌问MW
2018.10.07 15:20 字数 2233

一、数据库操作

- 创建数据库

先通过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;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(10)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| age        | int(4)      | YES  |     | NULL    |       |
| updateTime | date        | 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)要求主键列的数据唯一性,且不能为空。主键能唯一的标识一条记录,可以结合外键来定义不同数据表之间的关系,且可以增加数据库的查询速度。

  1. 单字段主键
    可以在创建表时,定义表列的同时指定主键,也可以定义好表结构以后设置主键,具体方法如下:
    定义的同时指定主键:
create table mw_table1( id INT(10) PRIMARY KEY, name VARCHAR(20), age INT(4), updateTime DATE );

查看表结构结果如下:

mysql> desc mw_table1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(10)     | NO   | PRI | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| age        | int(4)      | YES  |     | NULL    |       |
| updateTime | date        | 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;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(10)     | NO   | PRI | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| age        | int(4)      | YES  |     | NULL    |       |
| updateTime | date        | 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;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(10)     | YES  |     | NULL    |       |
| name       | varchar(20) | NO   | PRI | NULL    |       |
| age        | int(4)      | YES  |     | NULL    |       |
| tel        | varchar(20) | NO   | PRI | NULL    |       |
| updateTime | date        | YES  |     | 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软件可以查看外键,如下图:
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软件可以查看外键(需重连数据库),结果如下图:
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;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(10)     | NO   | PRI | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| tel        | varchar(20) | YES  | UNI | NULL    |       |
| updateTime | date        | YES  |     | 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;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(10)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20) | YES  |     | NULL    |                |
| tel        | varchar(20) | NO   |     | NULL    |                |
| updateTime | date        | YES  |     | 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;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(10)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| age        | int(4)      | YES  |     | NULL    |       |
| updateTime | date        | 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;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(10)     | YES  |     | NULL    |       |
| username   | varchar(20) | YES  |     | NULL    |       |
| age        | int(4)      | YES  |     | NULL    |       |
| updateTime | date        | 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;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(10)     | YES  |     | NULL    |       |
| username   | varchar(20) | YES  |     | NULL    |       |
| age        | varchar(10) | YES  |     | NULL    |       |
| updateTime | date        | 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;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(10)     | YES  |     | NULL    |       |
| username   | varchar(20) | YES  |     | NULL    |       |
| age        | int(4)      | YES  |     | NULL    |       |
| updateTime | date        | YES  |     | NULL    |       |
| tel        | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

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

#注释:添加新的字段到第一列,格式: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;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| gender     | int(1)      | YES  |     | NULL    |       |
| id         | int(10)     | YES  |     | NULL    |       |
| username   | varchar(20) | YES  |     | NULL    |       |
| age        | int(4)      | YES  |     | NULL    |       |
| birthday   | date        | YES  |     | NULL    |       |
| updateTime | date        | 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;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(10)      | YES  |     | NULL    |       |
| gender     | int(1)       | YES  |     | NULL    |       |
| username   | varchar(20)  | YES  |     | NULL    |       |
| age        | int(4)       | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| tel        | varchar(20)  | YES  |     | NULL    |       |
| location   | varchar(100) | NO   |     | NULL    |       |
| updateTime | date         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
- 删除字段
#注释:格式:ALTER TABLE 表名 DROP 字段名

alter table new_table drop gender;
- 修改表的存储引擎

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

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| 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         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
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:此处重点说明:当数据表之间存在外键关联的情况下,直接删除主表,会显示失败,可以先删除从表,在删除主表,但是如果不想删除从表,只删除主表,可将主表与从表之间关联关系解除后,在删除主表。删除外键关联的方法上面已经说过,这里不再赘述!

数据库