MySQL：数据表基本操作

数据表基本操作

注意点：

1、数据表中已经有数据时，轻易修改数据类型，有可能因为不同的数据类型的数据在机器

中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据类型。

2.  数据表名不区分大小写，不能使用sql语言中的关键字，如drop，alter，insert。

3.  数据表中的每一个列（字段）的名称和数据类型，如果多个列，要用逗号分开。                                                     

4.  字段（属性，列）例：编号、姓名、性别、职业、年龄

   记录（行）例：10、11

例：

编号	姓名	性别	职业	年龄
10	林峰	f	会计	45
11	李款	m	兽医	60

 

一、基础操作

1、创建数据表(二维表）

附：在创建表之前必须先转到指定数据库，即use加数据库名字

mysql> create table zixue（数据名）
-> (                   （附：括号必须打）
-> name    varchar(25),（附：必须加英文逗号，不加和加中文逗号都无效，血的教训）
-> id      int(11),     注意如果格式为ID int字段值代表可以为空值
->salary  float        （注意最后一个数据无逗号）
-> );

2、显示数据表

语法：show tables

实例：mysql>Show tables；

3、删除数据表

（1）删除没有关联的表

格式：drop table [IF EXISTS]表1，表2…表n;

实例：

【例1】删除一个表：

mysql>droptable zixue;

【例2】删除多个表：

mysql> droptable a,b;

【例3】删除多个表且有表不存在：

mysql> droptable a,b,c;
ERROR 1051(42S02): Unknown table 'zixi.a,zixi.b'
mysql> showtables;
+----------------+
| Tables_in_zixi|
+----------------+
| d              |
+----------------+

结果：照样删除存在的数据表

【例4】使用参数删除多个数据表：

mysql> droptable if exists a,b;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

结果：虽然报错有那几个数据表不存在。但是依然删除

 

附：

 

• 表n表示要删除的表的名称，后面可以同时删除多个表，只需将要删除的表名称依次写在后面，相互之间用逗号隔开。
• 参数不区分大小写
• 参数‘IF EXISTS’用于在删除前判断删除的表是否存在，加上该参数后，再删除表的时候，如果表不存在，如果表不存在，sql语句可以顺利执行，但是会发出警告。

 

（2）删除被其他表关联的主表

方法1：先删除子表，再删除父表。（直接删除父表显示错误的原因，直接删除父表将破坏表的参照完整性）
方法2：先去除外键约束条件，再删父表
注意点：

• 主键，又称主码，是表中一列或者多列的组合。
• 主键约束要求主键列的数据唯一，并且不允许为空。
• 主键分两类：单字段主键和多字段联合主键
• 当主键表和外键表相连时，显示无法删除，实际是外键表已经删除

【例】：

 create table bufangqiyi
-> (
-> id int primary key auto_increment,                       primary key主键  auto_increment自动增长
->name varchar(25) not null,                                not nul 非空
-> salary decimal(10.2) not null default 1000（             如果不是空集，默认1000）default默认值
-> );

二、约束条件

1、定义主键

（1）单字段主键

格式：

  字段名 数据类型 primary key[默认值]

实例：

【例1】：

mysql> create tabledangziduan
-> (
-> id int(11)primary key,
-> name varchar(25),
-> deptld int(11),
-> salary float
-> );

【例2】：

mysql> create tabledangziduan1
-> (
-> id int(11),
-> name varchar(25),
-> deptld int(11),
-> salary float,
-> primary key(id)
-> );

（2）多字段联合主键

格式：

primary key[字段1，字段2，字段3…字段4]字段不能一模一样

实例：

mysql> create table duoziduan
-> (
-> name varchar(25),
-> deptld int(11),
-> salary float,
-> primary key(name,deptld)
-> );

注意点：

• 外键是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键
• 外键的主要作用：保证数据引用的完整性
• 外键的基本作用：保持数据的一致性、完整性。
• 定义外键后不允许删除在另外一个表中具有关联关系的行
• 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表
• 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表

2.使用外键约束

格式：

[constraint<外键名>]foreign key 字段1[，字段2，…] references<主表名>主键列1[,主键2，…]

• 外键名：定义的外键约束的名称，一个表中不能有相同名称的外键，可以随便取；
• 字段名：字表需要添加外键约束的字段列；
• 主表名：被字表外键所依赖的表的名称；
• 主键列：主表中定义的主键列，或者列组合。

实例：

1、主键表：字段-----字段的外键表

mysql> desc dangziduan;
+--------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id  | int(11)  | NO   | PRI | NULL    |      |
| name| varchar(25) | YES  |     |NULL    |      |
| deptld| int(11)     | YES |     | NULL    |      |
| salary | float       | YES |     | NULL    |      |
+--------+-------------+------+-----+---------+-------+

2、外键表：需要建外键约束的表

mysql> create table waijian
   -> (
   -> id int(13),
   -> name varchar(29),
   -> deptld int(9),
   -> salary float primary key,
   -> foreign key (deptld) references dangziduan(id)
   -> );

注意：

 

 

 

 

 

• 首先创建主键表，在创建外键表必须是与主键表数据类型相同的列
• 删表时，先删外键表，在删主键表。
• 插入、修改外键表字段，必须和主键表相同数据处理时，先查主表，在插入外键表。 

 

 

3.删除外键约束

方法一：删除外键表

方法二:

格式：alter table 表名 drop foreign key 外键约束名

    附：

• 外键约束名:指在定义表时constraint关键字后面的约束名
• 不知道的话可以使用show create table语句查看在constraint后面单引号中。

例： CONSTRAINT `waijian_ibfk_1` FOREIGN KEY (`deptld`) REFERENCES `dangziduan` (`id`) 外键约束是waijian_ibfk_1

例：

mysql> alter table waijian drop foreign key waijian_ibfk_1;

4.非空约束：字段的值不能为零

格式：字段名 数据类型 not null

5.唯一性约束：要求该列唯一，允许为空，但只能出现一个空值

格式1：

字段名 数据类型 unique

实例1：

mysql> create table ziduan
    -> (
    ->  id int(11)primary key,
    -> name varchar(25)unique
    -> );

格式2：

[constraint<约束名>]unique(<字段名>)

实例2：

mysql> create table ziduan1
    -> (
    ->  id int(11)primary key,
    -> name varchar(25),
    -> constraint sth unique(name) //sth为约束名可以忽略
　　-> );

格式3：多个唯一性约束
   [constraint<约束名>]unique(<字段名1>，<字段名2>，<字段名3>)
注意：
每个字段都一模一样时是重复Unique（字段1，字段2）与unique（字段1），unique（字段2）的区别

注意：unique和primary key的区别

• 一个表中可以有多个字段是unique，但只有一个primary key
• 在unique中可以有空值，但在primary key中不能有空值

 

6. 默认约束

格式：

字段名 数据类型 default 默认值

实例

mysql> create table moren
    -> (
    ->  id int(11)primary key auto_increment,
    -> name varchar(25),
    -> deptid int(11) default 30
-> );

7.表的属性值自动增长

格式：

字段名 数据类型 auto_increment

注意：

• 但一个记录删除，下一个记录不会再次使用上个记录，即1,2,3，4,5删掉3，下一个增长的值为6，而不   是3.
• 一个表只能有一个自动增加
• 自动增加最好是主键。

8.定义空值

格式：字段名 数据类型 null

9.非负

格式：字段名 数据类型 unsigned

三、查看数据表结构

1、查看表基本结构语句

格式：

desc 数据表表名 //SHOW COLUMNS FROM 数据表表名 //describe 数据表名

注意:

• Null:表示该列是否可以储存null值
• Key;表示是否已编制索引。
• PRI表示该列是表主键的一部分；
• UNI表示该列是unique索引的一部分；
• MUL表示在列中某个给定值允许出现多次。
• Default:表示该列是否有默认值，如果有值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如auto_increment等。

2、查看表详细结构

格式:

show create table <表名\G>

例:不加\G或者加\g

mysql> show create table a;
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table a\g;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

例：加\G

mysql> show create table a\G;
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意点:

• show create table 语句，不仅可以查看创建时候的详细语句，而且还可以查看存储引擎和字符编码。
• 如果加“\g”参数，显示的结果无效，如果加“\G”参数易于查看

四、修改数据表

1、改变数据表表名

格式：alter table <旧表名> rename <新表名>;

注意；修改表名并不修改表的结构

2、修改字段的数据类型

格式：alter table 数据表名modify 字段名 数据类型;

3. 添加字段

注意：一个完整的字段包括字段名、数据类型、完整性约束
格式：

alter table 表名 add 新字段名 数据类型 [约束条件][FIRST|AFTER 已存在字段名];

FIRST为可选参数，其作用是将新添加的字段设置为表的第一个字段；
AFTER为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面

（1）添加无完整性约束条件的字段

格式：alter table 数据表名 add 字段名 字段类型
例：mysql> alter table a add managerid int(10);

（2）添加有完整性约束条件的字段

格式：alter table 数据表名 add 字段名 字段类型 约束条件
例：mysql> alter table a add managerid int(10) primary key;

（3）在表的第一列添加一个字段

a. 添加无完整性约束条件的字段

格式：after table 数据表名 add 字段名 字段类型 first；
例;mysql> alter table a add column2 int(11) first;

b. 添加有完整性约束条件的字段

格式：after table 数据表名 add 字段名 字段类型  约束条件 first；
例：mysql> alter table a add managerid2 int(11) not null first;

（4）在表的指定列之后添加一个字段

a. 添加无完整性约束条件的字段

格式：alter table 数据表名 add 字段名 字段类型 after 字段名
例：mysql> alter table a add id1 int(10) after id;把id1放的id后面；

b.添加有完整性约束条件的字段

格式：alter table 数据表名 add 字段名 字段类型 约束条件 after 字段名
例：mysql> alter table a add id2 int(10) not null after id;把id2放在id后面；

4. 删除字段

格式：alter table 表名 drop 字段名
例：mysql> alter table a drop id1;删除在表a中的字段id1；

5. 修改字段的排列位置

格式：alter table 表名 modify 字段1 数据类型 first/after 字段2 ；
字段1指要修改位置的字段，数据类型指字段1 的数据类型
First指将字段修改为表的第一个字段
After 字段2 指将字段1 插入到字段2后面

（1）修改字段为表的第一个字段

修改无完整性约束条件的字段为第一个字段

格式：after table 数据表名 modify 字段名 字段类型 first；
例;mysql> alter table a modify id2 int(10) first;

修改有完整性约束条件的字段为第一个字段

格式：after table 数据表名 modify 字段名 字段类型  约束条件 first；
例：mysql> alter table a modify managerid2 int(11) not null first;

（2）修改字段到表的指定列之后

修改无完整性约束条件的字段

格式：alter table 数据表名 modify 字段名 字段类型 after 字段名
例：mysql> alter table a modify managerid2 int(11) after id;；

修改有完整性约束条件的字段

格式：alter table 数据表名 modify 字段名 字段类型 约束条件 after 字段名
例：mysql> alter table a modify managerid2 int(11) not null after id;

6. 更改表的存储引擎

格式：

alter table 表名 engine=更改后的存储引擎名

例：

mysql> alter table a engine=innodb;

注意：

• • 存储引擎不区分大小写
  • 被定义的主键不可以更改存储引擎。

7.输入值

格式:insert into 数据表名 values（字段名）

8. 查看数据值

格式：select*from 数据表名

9. 查看警告信息

格式：show warnings;

10. 修改表的位置（重命名表）

重命名：

语法：rename table 表名 to 表名；

修改表的位置：

语法：rename table 旧库名.表名 to 新库名.表名；

注意：

表名一致