mysql中初始化datatable_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(，，)

注意：

每个字段都一模一样时是重复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

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 新库名.表名；

注意：

表名一致