一、数据库操作
- 创建数据库
先通过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)要求主键列的数据唯一性,且不能为空。主键能唯一的标识一条记录,可以结合外键来定义不同数据表之间的关系,且可以增加数据库的查询速度。
- 单字段主键
可以在创建表时,定义表列的同时指定主键,也可以定义好表结构以后设置主键,具体方法如下:
定义的同时指定主键:
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)
添加后可以发现,新添加的字段位于表的最后一条(即表中的最后一列),如果想在特定位置添加字段,可以使用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;
+------------+-------------+------+-----+---------+-------+
| 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:此处重点说明:当数据表之间存在外键关联的情况下,直接删除主表,会显示失败,可以先删除从表,在删除主表,但是如果不想删除从表,只删除主表,可将主表与从表之间关联关系解除后,在删除主表。删除外键关联的方法上面已经说过,这里不再赘述!
网友评论