Mysql入门基础 数据库创建篇
程序员文章站
2023-12-15 11:37:04
1.创建数据表---基础（高手跳过） 正统方法：create [temporary] table 表名 [if not exists] (创建的列项定义) [表的选项] [...
1.创建数据表---基础（高手跳过）
正统方法：create [temporary] table 表名 [if not exists]
(创建的列项定义)
[表的选项]
[分区的选项];#正统的创建方式，具体的参数，请参考mysql手册，在这里不做详细的解释，只说一些比较特别的。
例：
create table user(id int unsigned not null auto_increment,
username char(15),
sex enum('m','f') default 'm',
userid varchar(20),
primary key(id,userid),
index idx_user(userid),
)engine='innodb' charset=utf8;
复制数据库结构：
create [temporary] table 表名 [if not exists] like 已存在的表名;//模仿已存在的表创建一个结构完全相同的表
mysql>create table vip_user like user;
mysql>create table vip_user select * from user where 0;//上例还可以这样做,仅复制结构
复制并copy数据库
create [temporary] table 新表名 select * from 旧表名;#用户可以人为的指定后续select组合成需要的语句。
create table dst_tbl(
id int not null auto_increment,
primary key(id)
) select a,b,c from src_tbl;
以此来创建各种各样的符合用户要求的数据库，这里只是做一个抛砖引玉。
2.查看修改的数据表结构
mysql> desc 数据表名;#查看数据表的结构
mysql> show create table 数据表名\g #查看数据表的构成语句,\g和;的意义相同，只不过\g是纵向显示，这样看的更清楚。
mysql> show table status like '数据表名'\g #查看数据表的状态
mysql> show columns from 数据表名;#查看数据表的结构,同desc一样，不过使用这条语句后面加like '字段'可以只显示指定字段
3. 更改变据库的结构
我们首先创建一个表
mysql> create table vip(id int null,username varchar(30));
修改表结构中的数据类型
mysql>alter table vip modify id smallint default 1;#更改数据记录为1.
mysql>#alter table vip modify id smallint auto_increment;#大家执行一下这句话会提示错误，因为mysql中要求自动增长的列设为主键
mysql>alter table vip add primary(id);#设vip中的值的主键为id,执行这条语句后再执行上面的就没问题了。
mysql>alter table vip modify username char(30) not null;#改变vip的username为char(30);
mysql>alter table vip modify username mediumtext first; #改变vip中的username,并将它设在最前面字段，还有一个是after 字段，是指放在某字段后
对已有表新增字段
mysql> alter table vip add sex enum('m','f') default 'm' not null after id;#新增sex字段为enum类型，放在id的后面.
对已有字段改名
mysql>alter table vip change sex usersex tinyint default 0 not null after username;改名字段sex为usersex并改变类型和位置。
注：仅alter中modify,change很相似，但是modify不能改名只能改结构，但change即可以改名，也可以改类型。
删除字段
mysql>alter table vip drop usersex;#删除 usersex字段，警告，所有的该字段数据都会丢失。
数据库改名
mysql>alter table vip rename to vip_user;数据库进行改名。
改变数据表引擎
mysql>alter table vip engine="myisam";
注意，修改数据结构是一个很危险的事，最好做好备份，以防不侧。
还有部分的alter的语句和技巧我们将在以后涉及处一一到来.....
4. 关于数据约束
数据约束在mysql5中支持的越来越好了，但是现有的数据约束仅限于innodb,传说中mysql5.2也会支持对数据约束的支持（期待..）
首先我们了解一下什么是数据约束，因为我们平常创建表中都可能会有互相关联的信息，而数据约束是将两个表进行关联的一种纽带。
例如：两个表，一个usertype,一为userid,usertype中有一个关键字key为用户类型编号，userid表中也有一 user_key对应着usertype表中的
1.首先我们要保证userid表中的所有值都在usertype中
2.其次我们要保证usertype中的值key进行改变userid表中的user_key值也会变化。
3.usertype中的值不能随意删除，除非userid表中不存在该usertype类型的值，如果要强制删除则会删除userid中的所有 usertype的值。
如果没有数据约束，我们每次insert/update可能要用数条语句才能保证数据的正确完整性，如果使用数据约束则只需要在定义的时候进行一下处理，而不用担心太多。而且最重要的是使用数据约束能够很好的保证数据，业务的完整性。
呵呵，说了这么多，还没有说数据约束的缺点：慢，使用数据约束要比不使用数据约束慢得多，而且用户每次插入数据或更改数据，数据库系统都会花一定的时间进行一定的检查.但是随着 mysql的日益成熟，这种速度会有着很大的改进。
就个人而言，我觉得非商务、实时系统对数据业务完整性要求较高的情况下使用数据约束还是很有必要的。其它情况下就仁者见仁智者见智了。
5.数据约束简明解析
所以外键的定义必须必须满足以下三种情况：
1.两个表必须是innodb表类型
2.指定为外键的列必须进行索引
3.两个表中关联的外键类型必须相符。
我们先来看例子，在例子中进行学习：
mysql> create table parent(id int null,primary key(id)) engine=innodb; #创建一个主表
mysql> create table child(id int,parent_id int,
foreign key(parent_id)
references parent(id) on delete restrict on update cascade
) engine=innodb; #创建一个从表，并约外键关键字为parent_id,建立之间的关联关系。
mysql> insert into parent values(1),(2),(3);#对主表插入数据
mysql> insert into child values(1,1),(1,2),(1,3);#对子表进行插入数据，对应不同的parent_id子child的id值均为1;
mysql> #insert into child values(1,1),(1,2),(1,3),(1,4); #看一下这会发生什么？报错是吧？什么原因？大家想想
说明：因为我们在创建语句的时候就约定了数据是进行外键关联的，而parent中不存在id值为4的主键，那么子键当然更新不了了。
这时数据库中的值为：
parent child
id id parent_id
1 1 1
2 1 2
3 1 3
我们继续操作以体现外键关联表的作用
mysql> update parent set id=4 where id=1;#改变parent的值看一下child的反应
mysql> select * from parent;
mysql> select * from child;
这时数据库中的值为：
parent child
id id parent_id
2 1 4
3 1 2
4 1 3
通过以上的例子大家可以清楚的看到用户只是改变parent的值，而关联的child值会自动改变。我们继续
mysql> insert into child values(2,4),(3,4),(4,4);#为子表再添加一些其它的值。
mysql> #delete from parent where id=4; #大家执行这条语句看一下有什么结果，错误吧？我们来分析一下提示错误
我们回顾一下我们创建外键的说细情况和关键语句：
foreign key(parent_id) #这句话的意思是指定对外关联键为本表的parent_id;
references parent(id) on delete restrict on update cascade#这句话是则约束语句，references可以约束本数据库的外键即parend_id与 parent数据表的id子键对应，并约束了on delete,on update时的操作，mysql共有以下几种操作：
(1) restrict、no action 表示如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 ，大家现在明白了吧？为什么我们执行delete from parent where id=4;时出现错误，因为子表(child)中还存在值.
(2) set null 在父表上update/delete记录时，将子表上匹配记录的列设为null，但要注意子表的外键列不能为not null
(3) cascade 在父表上update/delete记录时，同步update/delete掉子表的匹配记录
大家可以根据自己的需要设置不同的动作，例如，我们要在父表删除时，自动删除子表的关联值，则我们需进行设置：
references parent(id) on delete cascade on update cascade,实验之前我们须要知道
1.已经定义设置好的外键是无法再行更改的，必须要删除外键后再行创建（可能还有别的方法，哪位高手指点一下）
mysql> show create table child/g #得到 constraint（约束）的名字
说明：constraint是可以缺省的，用于指定约束的名字，如果不指定则系统会自动为它取名，例如我们可以这样：
constraint fk_child_key
foreign key(parent_id)
references parent(id) on delete restrict on update cascade;
这样我们就指定了这个约束的整体名称为fk_child_key,以后可以对这个进行操作了.
mysql> alter table child drop foreign key fk_child_key;#删除约束
mysql> alter table child add foreign key ('parent_id)
references parent(id) on delete cascade
on update cascade;
mysql> show create table child/g #至此约束已更改，用户可以查看一下更改情况
mysql>delete from parent where id=4;#我们再执行上面的那句，这时没错误了吧？
mysql>select * from parent;
mysql>select * from child; #我们可以看到现在与parent_id为4的全部删除。呵呵，以后都可以方便的使用了
这时数据库中的值为：
parent child
id id parent_id
2 1 2
3 1 3
6.数据约束 的额外说明
如果定义了数据约束，则数据的插入或更改速度会很慢，特别是更改数据结构，插入数据时，效率低的可怕。
当客户执行load data（载入数据，后续会介绍），alter table时建议使用以下命令，暂时关闭数据约束，等完成后再开启，这样速度至少可以提升20倍以上。
mysql> set foreign_key_checks=0;#关闭数据约束
mysql> load data infile '文件绝对地址' into table 表名;#从文本文件中载入大量数据
mysql> set foreign_key_checks=1;#打开数据约束
正统方法：create [temporary] table 表名 [if not exists]
(创建的列项定义)
[表的选项]
[分区的选项];#正统的创建方式，具体的参数，请参考mysql手册，在这里不做详细的解释，只说一些比较特别的。
例：
复制代码 代码如下:
create table user(id int unsigned not null auto_increment,
username char(15),
sex enum('m','f') default 'm',
userid varchar(20),
primary key(id,userid),
index idx_user(userid),
)engine='innodb' charset=utf8;
复制数据库结构：
复制代码 代码如下:
create [temporary] table 表名 [if not exists] like 已存在的表名;//模仿已存在的表创建一个结构完全相同的表
mysql>create table vip_user like user;
mysql>create table vip_user select * from user where 0;//上例还可以这样做,仅复制结构
复制并copy数据库
复制代码 代码如下:
create [temporary] table 新表名 select * from 旧表名;#用户可以人为的指定后续select组合成需要的语句。
create table dst_tbl(
id int not null auto_increment,
primary key(id)
) select a,b,c from src_tbl;
以此来创建各种各样的符合用户要求的数据库，这里只是做一个抛砖引玉。
2.查看修改的数据表结构
复制代码 代码如下:
mysql> desc 数据表名;#查看数据表的结构
mysql> show create table 数据表名\g #查看数据表的构成语句,\g和;的意义相同，只不过\g是纵向显示，这样看的更清楚。
mysql> show table status like '数据表名'\g #查看数据表的状态
mysql> show columns from 数据表名;#查看数据表的结构,同desc一样，不过使用这条语句后面加like '字段'可以只显示指定字段
3. 更改变据库的结构
我们首先创建一个表
mysql> create table vip(id int null,username varchar(30));
修改表结构中的数据类型
复制代码 代码如下:
mysql>alter table vip modify id smallint default 1;#更改数据记录为1.
mysql>#alter table vip modify id smallint auto_increment;#大家执行一下这句话会提示错误，因为mysql中要求自动增长的列设为主键
mysql>alter table vip add primary(id);#设vip中的值的主键为id,执行这条语句后再执行上面的就没问题了。
mysql>alter table vip modify username char(30) not null;#改变vip的username为char(30);
mysql>alter table vip modify username mediumtext first; #改变vip中的username,并将它设在最前面字段，还有一个是after 字段，是指放在某字段后
对已有表新增字段
mysql> alter table vip add sex enum('m','f') default 'm' not null after id;#新增sex字段为enum类型，放在id的后面.
对已有字段改名
mysql>alter table vip change sex usersex tinyint default 0 not null after username;改名字段sex为usersex并改变类型和位置。
注：仅alter中modify,change很相似，但是modify不能改名只能改结构，但change即可以改名，也可以改类型。
删除字段
mysql>alter table vip drop usersex;#删除 usersex字段，警告，所有的该字段数据都会丢失。
数据库改名
mysql>alter table vip rename to vip_user;数据库进行改名。
改变数据表引擎
mysql>alter table vip engine="myisam";
注意，修改数据结构是一个很危险的事，最好做好备份，以防不侧。
还有部分的alter的语句和技巧我们将在以后涉及处一一到来.....
4. 关于数据约束
数据约束在mysql5中支持的越来越好了，但是现有的数据约束仅限于innodb,传说中mysql5.2也会支持对数据约束的支持（期待..）
首先我们了解一下什么是数据约束，因为我们平常创建表中都可能会有互相关联的信息，而数据约束是将两个表进行关联的一种纽带。
例如：两个表，一个usertype,一为userid,usertype中有一个关键字key为用户类型编号，userid表中也有一 user_key对应着usertype表中的
1.首先我们要保证userid表中的所有值都在usertype中
2.其次我们要保证usertype中的值key进行改变userid表中的user_key值也会变化。
3.usertype中的值不能随意删除，除非userid表中不存在该usertype类型的值，如果要强制删除则会删除userid中的所有 usertype的值。
如果没有数据约束，我们每次insert/update可能要用数条语句才能保证数据的正确完整性，如果使用数据约束则只需要在定义的时候进行一下处理，而不用担心太多。而且最重要的是使用数据约束能够很好的保证数据，业务的完整性。
呵呵，说了这么多，还没有说数据约束的缺点：慢，使用数据约束要比不使用数据约束慢得多，而且用户每次插入数据或更改数据，数据库系统都会花一定的时间进行一定的检查.但是随着 mysql的日益成熟，这种速度会有着很大的改进。
就个人而言，我觉得非商务、实时系统对数据业务完整性要求较高的情况下使用数据约束还是很有必要的。其它情况下就仁者见仁智者见智了。
5.数据约束简明解析
所以外键的定义必须必须满足以下三种情况：
1.两个表必须是innodb表类型
2.指定为外键的列必须进行索引
3.两个表中关联的外键类型必须相符。
我们先来看例子，在例子中进行学习：
mysql> create table parent(id int null,primary key(id)) engine=innodb; #创建一个主表
mysql> create table child(id int,parent_id int,
foreign key(parent_id)
references parent(id) on delete restrict on update cascade
) engine=innodb; #创建一个从表，并约外键关键字为parent_id,建立之间的关联关系。
mysql> insert into parent values(1),(2),(3);#对主表插入数据
mysql> insert into child values(1,1),(1,2),(1,3);#对子表进行插入数据，对应不同的parent_id子child的id值均为1;
mysql> #insert into child values(1,1),(1,2),(1,3),(1,4); #看一下这会发生什么？报错是吧？什么原因？大家想想
说明：因为我们在创建语句的时候就约定了数据是进行外键关联的，而parent中不存在id值为4的主键，那么子键当然更新不了了。
这时数据库中的值为：
parent child
id id parent_id
1 1 1
2 1 2
3 1 3
我们继续操作以体现外键关联表的作用
mysql> update parent set id=4 where id=1;#改变parent的值看一下child的反应
mysql> select * from parent;
mysql> select * from child;
这时数据库中的值为：
parent child
id id parent_id
2 1 4
3 1 2
4 1 3
通过以上的例子大家可以清楚的看到用户只是改变parent的值，而关联的child值会自动改变。我们继续
mysql> insert into child values(2,4),(3,4),(4,4);#为子表再添加一些其它的值。
mysql> #delete from parent where id=4; #大家执行这条语句看一下有什么结果，错误吧？我们来分析一下提示错误
我们回顾一下我们创建外键的说细情况和关键语句：
foreign key(parent_id) #这句话的意思是指定对外关联键为本表的parent_id;
references parent(id) on delete restrict on update cascade#这句话是则约束语句，references可以约束本数据库的外键即parend_id与 parent数据表的id子键对应，并约束了on delete,on update时的操作，mysql共有以下几种操作：
(1) restrict、no action 表示如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 ，大家现在明白了吧？为什么我们执行delete from parent where id=4;时出现错误，因为子表(child)中还存在值.
(2) set null 在父表上update/delete记录时，将子表上匹配记录的列设为null，但要注意子表的外键列不能为not null
(3) cascade 在父表上update/delete记录时，同步update/delete掉子表的匹配记录
大家可以根据自己的需要设置不同的动作，例如，我们要在父表删除时，自动删除子表的关联值，则我们需进行设置：
references parent(id) on delete cascade on update cascade,实验之前我们须要知道
1.已经定义设置好的外键是无法再行更改的，必须要删除外键后再行创建（可能还有别的方法，哪位高手指点一下）
mysql> show create table child/g #得到 constraint（约束）的名字
说明：constraint是可以缺省的，用于指定约束的名字，如果不指定则系统会自动为它取名，例如我们可以这样：
constraint fk_child_key
foreign key(parent_id)
references parent(id) on delete restrict on update cascade;
这样我们就指定了这个约束的整体名称为fk_child_key,以后可以对这个进行操作了.
mysql> alter table child drop foreign key fk_child_key;#删除约束
mysql> alter table child add foreign key ('parent_id)
references parent(id) on delete cascade
on update cascade;
mysql> show create table child/g #至此约束已更改，用户可以查看一下更改情况
mysql>delete from parent where id=4;#我们再执行上面的那句，这时没错误了吧？
mysql>select * from parent;
mysql>select * from child; #我们可以看到现在与parent_id为4的全部删除。呵呵，以后都可以方便的使用了
这时数据库中的值为：
parent child
id id parent_id
2 1 2
3 1 3
6.数据约束 的额外说明
如果定义了数据约束，则数据的插入或更改速度会很慢，特别是更改数据结构，插入数据时，效率低的可怕。
当客户执行load data（载入数据，后续会介绍），alter table时建议使用以下命令，暂时关闭数据约束，等完成后再开启，这样速度至少可以提升20倍以上。
mysql> set foreign_key_checks=0;#关闭数据约束
mysql> load data infile '文件绝对地址' into table 表名;#从文本文件中载入大量数据
mysql> set foreign_key_checks=1;#打开数据约束