数据表基本操作
注意点:
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 新库名.表名;
注意:
表名一致