Mysql之数据表操作
1. 创建数据表
1. 语法
create table <表名>
（
字段1 数据类型 [约束条件] [默认值]，
字段2 数据类型 [约束条件] [默认值]，
...
）;
2. 例子
create table students ( id int(11),name varchar(25),age int(3));
3. 使用主键约束
可以是一列，也可以是多列的组合。主键约束要求主键列的数据唯一，且不为空。
1. 单字段主键
create table students ( id int(11) primary key,name varchar(25),age int(3));
create table students ( id int(11),name varchar(25),age int(3),primary key(id));
2. 多字段主键（联合主键）
create table students ( id int(11),name varchar(25),age int(3),primary key(id,name));
4. 使用外键约束
外键用来在两个表的数据之间建立链接，可以是一列或者多列。
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表是主表
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表是从表
外键用于一对多的关系，一为主表，多为从表
create table class ( id int(10) primary key,name varchar(25),position varchar(50)); 这是一张班级表
create table students (id int(10) primary key,name varchar(25),classID int(10),constraint fk_stu_class foreign key(classID) references class(id) ); 这是一张学生表
从表的外键必须关联主表的主键，且关联字段的数据类型必须匹配，如果不一样，则创建子表的时候，就会出现错误：ERROR 1005 (HY000): Can't create table 'database.tablename'(errno:150)
5. 使用非空约束
非空约束指字段的值不能为空
create table students ( id int(11),name varchar(25) not null,age int(3),primary key(id,name));
6. 使用唯一性约束
唯一性约束要求该列唯一，允许为空，但只能出现一个空值。
create table students ( id int(11),name varchar(25) not null unique,age int(3),primary key(id));
create table students ( id int(11),name varchar(25) not null,age int(3),primary key(id),constraint uniq_name unique(name)); 红色字为关键字
唯一约束允许有一个空值，但是主键不允许有空值
7. 使用默认值约束
create table students ( id int(11) default 1,name varchar(25) not null,age int(3),primary key(id));
8. 设置表的属性值自动增加
create table students ( id int(11) auto_increment,name varchar(25) not null,age int(3),primary key(id));
2. 查看数据表结构
1. 查看基本结构
describe table_name;
desc table_name;
mysql> desc students; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(25) | NO | | NULL | | | age | int(3) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
2. 查看详细结构
show create table table_name\G;
mysql> show create table students\G;
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
`age` int(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
3. 修改数据表
1. 修改表名
语法： alter table <旧表名> rename [to] <新表名>
例子： alter table students rename stu;
2. 修改字段的数据类型
语法： alter table <表名> modify <字段名> <数据类型>
例子： alter table stu modify name varchar(30);
3. 修改字段名
语法： alter table <表名> change <旧字段名> <新字段名> <新数据类型>
例子： alter table stu change name na varchar(40); 使用change可以修改字段名，也可以修改字段类型，可以同时修改字段名和字段类型，也可以单独修改字段名或者字段类型
4. 添加字段
语法： alter table <表名> add <新字段名> <新数据类型> [约束条件] [first | after 已存在字段名]
1. 添加没有约束的字段 alter table stu add class_id int(10); 默认添加到最后一列
2. 添加有约束的字段 alter table stu add class_id int(10) not null; 默认添加到最后一列
alter table students add constraint fk_stu_class foreign key(classID) references class(id); 添加外键约束
3. 添加到第一列的字段 alter table stu add class_id int(10) first; 添加到第一列
4. 添加到某列后面的字段 alter table stu add class_id int(10) after name; 添加到name字段后面
5. 删除字段
语法： alter table <表名> drop <字段名>
例子： alter table stu drop class_id ;
6. 修改字段的排列位置
语法： alter table <表名> modify <字段名1> <数据类型> first |after <字段名2>
1. 修改某个字段为第一列 alter table stu modify name varchar(40) first;
2. 修改某个字段在另外一个字段后 alter table stu modify name varchar(40) after id;
7. 更改表的存储引擎
语法： alter table <表名> engine=<更改后的存储引擎名>
例子： alter table stu engine=myisam;
8. 删除表的外键约束
语法： alter table <表名> drop foreign key <外键约束名>
例子： alter table students drop foreign key fk_stu_class;
4. 删除数据表
1. 删除没有被关联的表
语法： drop table [if exists] 表1,表2
例子： drop table if exists yangjianbo;
2. 删除被其它表关联的表
创建两张表
create table class ( id int(10) primary key,name varchar(25),position varchar(50));
create table students (id int(10) primary key,name varchar(25),classID int(10),constraint fk_stu_class foreign key(classID) references class(id) );
父表伟class,子表为students
删除父表
drop table class;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
如果要删除父表，需要先删除外键约束，再删主表。
删除子表
drop table students; 可以直接删除，即使有外键约束
5. 查看表注释或字段注释
1. 查看所有表的注释
select table_name as 表名,table_comment as 字段注释 from tables where table_schema='数据库名称' order by table_name;
SELECT
a.table_name 表名,
a.table_comment 表说明,
b.COLUMN_NAME 字段名,
b.column_comment 字段说明,
b.column_type 字段类型,
b.column_key 约束
FROM
information_schema. TABLES a
LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
WHERE
a.table_schema = ‘数据库名’
ORDER BY
a.table_name
2. 查询某表的所有字段的注释
show full columns from yangjianbo.students;
select COLUMN_NAME 字段名,column_comment 字段说明,column_type 字段类型,column_key 约束 from information_schema.columnswhere table_schema = ‘数据库名’ and table_name = ‘表名’ ;
3. 修改表注释
alter table t_user comment = ‘修改后的表注释信息(用户信息表)’;
4. 修改字段注释
alter table t_user modify column id int comment ‘主键ID’;
浙公网安备 33010602011771号