《Mysql基础》【Mysql表的基本操作 新建表、修改表、删除表、外键约束、主键约束、完整性约束、修改表约束、添加表约束、候选键等】 编程入门 学习分享 【公开免费】

 -- mysql数据库程序设计笔记：

表基本操作：

1、新建表：格式如：
1）、建表加主键：
create table 表名
(
id int NOT NULL auto_increment comment  '自增主键id',
列名  类型（范围） comment '列备注',
...
primary key (id)
)engine=InnoDB;

2）、建表 加候选键副键约束
create table 表名
(
id int not null unique,
列名  类型（范围） comment '列备注',
......
)engine=InnoDB;

  注意：候选键一定要写：unique  
3）、新建表：不为空且唯一约束：  
  create table 表名
(
id int ,
xxxName varchar(255) not null unique  comment 'xxxxx名称,不为空且唯一',
.....
.....
)engine=InnoDB default charset='utf8' comment='xxx表' ;
  
4）、新建表：check 条件约束：举例如下名称为：华为时，状态为2.

create table 表名
(
id int not null auto_increment  comment 'id自增',
xxx_name varchar(255) ,
status  int(3) comment '状态',
constraint ck_S check(xxx_name !='华为' or status=2),
primary key (id)
)engine=InnoDB default charset='utf8' comment='xxxx表 表说明' ;

5）、新建表：添加外键：（要求：1、不能加自增列，2、必须关联主键，3、自定义外键列类型必须和关联表中关联列一致）
格式：constraint pk_自定义外键名称 foreign key(外键列) references 外键列主表名称(外键列)
举例：
create table a表名(
id int not null auto_increment  comment 'id自增',
primary key (id)
)engine=InnoDB default charset='utf8' comment 'A表名备注 xxx表';

create table b表名(
id int not null auto_increment  comment 'id自增',
primary key (id)
)engine=InnoDB default charset='utf8' comment 'B表名备注 xxx表';


create table c表名(
自定义a表外键列 关联a表主键-类型,
自定义b表外键列 关联b表主键-类型,
....
....
....
constraint pk_自定义约束1  primary key (自定义a表外键列,自定义b表外键列),
constraint fk_自定义约束2 foreign key(自定义a表外键列) references a表名(关联a表主键),
constraint fk_自定义约束3 foreign key(自定义b表外键列) references b表名(关联b表主键)
)engine=InnoDB default charset='utf8' comment 'c 表名备注 xxx表';


2、查看表详细结构：（新建表sql语句）： show create table 表名\G;
   或：
   查看表基本结构：desc 表名;

3、插入新数据：insert into 表名(col1,col2,col3,,,,,)values(val1,val2,val3,,,,,);

4、添加字段：
格式：alter table 表名 add column 新字段名 类型;

1）、举例：向数据db_school 表tb_student2 加入一个int类型的id字段,
要求 不为null且唯一且自增，并在表第一个字段显示。
答：
alter table db_school.tb_student2 add id int auto_increment not null unique first;

前提：新建表tb_student2：
create table tb_student2
(
studentNo char(10) not null  unique comment '学号',
studentName varchar(20)  not null comment '姓名',
)engine=InnoDB charset=utf8 comment='学生表';

2）、添加到最前：sql最后加：first;
3）、添加到xx字段之后：sql最后加： after xx;

5、修改字段：
格式：alter table 表名 modify column 字段名 数据类型; 
      alter table 表名 alter column 字段名 修改操作;
1)、举例：把tb_student2表中studentNo改为varchar(255)类型，可以为空 默认100293
alter table tb_student2 modify column studentNo varchar(255) default 100293;

2）、删除默认值：举例：删除tb_student2表中studentNo默认值
alter table tb_student2 alter column studentNo drop default;

3）、修改默认值：举例：修改tb_student2表中studentNo默认值为：1000010
alter table tb_student2 alter column studentno set default '1000010';

6、删除字段：格式：alter table 表名 drop 字段名;

举例：alter table tb_student2 drop column ainfoxx;
desc tb_student2;
前提先新增一个：alter table tb_student2 add column ainfoxx int null;

7、重命名表名：alter table 表名 rename 新表名;

8、删除表：drop table 表名;

9、主键约束：
1）、表级约束：
create table 表名
(
id int not null auto_increment comment '自增id',
NO int,
constraint 约束主键名称 primary key (id)
)engine=InnoDB charset=utf8 comment='测试表，表级约束';

2）、列级约束：
create table 表名
(
id int not null auto_increment comment '自增id',
NO int ,
constraint 约束主键名称 primary key (id)
)engine=InnoDB charset=utf8 comment='测试表，表级约束';

10、候选键约束：
候选键必须唯一且不能为null,不能加自增列。用unique来定义。
可以加多个unique：
1）、表级约束：
create table 表名
(
NO int not null  ,
xxtype varchar(255) not null ,
constraint 自定义约束名 unique(NO)
)engine=InnoDB charset=utf8 comment='测试表，表级约束';

2）、列级约束：
create table 表名
(
NO int not null unique ,
xxtype varchar(255) not null unique
)engine=InnoDB charset=utf8 comment='测试表，表级约束';

11、完整性约束 ：
参考添加外键。和check约束。

12、更新外键约束：
1）、删除外键约束：
格式：alter table 表名 drop foreign key 外键约束名;

举例：（在已添加外键数据库操作）
alter table c drop foreign key fk_c1;

2）、添加外键约束：
格式：alter table 子表名 add foreign key (自定义主表外键列) references 主表名(主表主键列);

测试：alter table c add foreign key (a_cxxx_info) references a(id);

13、更新主键约束：
1）、删除主键约束：格式：alter table 表名 drop primary key;
测试：
create table test3(
id int not null,
xName varchar(255) unique,
primary key (id)
)engine=InnoDB;

alter table test3 drop primary key;
-- Query OK ,.....
2）、添加主键：
格式：alter table 表名 add constraint 自定义约束名 primary key(列名);
测试： alter table test3 add constraint  pk_testxx01 primary key (id);

14、更新候选键：
1）、删除候选键约束：（删除unique）：
格式：alter table 表名 drop index 唯一约束列名;

测试：
create table test(
id int not null unique,
xxName varchar(255)
)engine=InnoDB;

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `xxName` varchar(255) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

mysql> alter table test drop index id;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `xxName` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

2）、添加候选键约束：
格式：alter table 表名 add constraint 自定义候选键约束名 unique key(给约束的表中列名);
举例：
mysql> alter table test add constraint cp_test2 unique key(xxName);
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `xxName` varchar(255) DEFAULT NULL,
  UNIQUE KEY `cp_test2` (`xxName`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)