MySQL（六）表操作

创建表 create table
查看表结构 desc table, show create table
表完整性约束
修改表 alter table
复制表 create table ...
删除表 drop table

一、创建表

1、语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集];
==在同一张表中，字段名是不能相同
==宽度和约束条件可选
==字段名和类型是必须的

二、 查看表结构

DESCRIBE 表名;

eg: mysql> desc student1;

查看表详细结构
SHOW CREATE TABLE 表名;

三、向表中插入内容

语法：
insert into 表名(字段1,字段2...) values(字段值列表...);

-------不为空,自增长,主键

->id int not null auto_increment primary key,

顺序插入 ----对应建表是的字段名一次插入

mysql> insert into student1 values
         -> (1,'tom','m',23),
         -> (2,'jack','m',21),
         -> (3,'alice','f',19);

Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

只向指定的字段插入值 ----按指定字段插入

mysql> insert into student1(id,name,sex,age) values         

          ->(4,'john','f',20),

          ->(5,'bob','f',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

四. 复制一个表结构

复制一个表结构的实现方法有两种
方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。

create table 新表名 like 源表;
方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。

create table 新表名 select * from 源表;

五. 表完整性约束

作用：用于保证数据的完整性和一致性

        约束条件                      说明
PRIMARY KEY (PK)         标识该字段为该表的主键，可以唯一的标识记录， 不可以为空 UNIQUE + NOT NULL
FOREIGN KEY (FK)         标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联
NOT NULL                       标识该字段不能为空
UNIQUE KEY (UK)           标识该字段的值是唯一的， 可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT         标识该字段的值自动增长（ 整数类型，而且为主键）
DEFAULT                         为该字段设置默认值
UNSIGNED                       无符号，正数
ZEROFILL                        使用0填充，例如0000001

说明：
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 forengn key
索引 (index,unique...)

===设置主键约束 PRIMARY KEY
primary key字段的值是不允许重复，且不允许不NULL（UNIQUE + NOT NULL）
单列做主键
多列做主键（复合主键）

单列做主键

表school.student6 方法一

mysql> create table student6(
-> id int primary key not null auto_increment,
-> name varchar(50) not null,
-> sex enum('male','female') not null default 'male',
-> age int not null default 18
-> );
Query OK, 0 rows affected (0.00 sec)

表school.student7 方法

mysql> create table student7(
-> id int auto_increment not null,
-> name varchar(50) not null,
-> sex enum('male','female') not null default 'male',
-> age int not null default 18,
-> CONSTRAINT pk_id primary key(id)
-> );

复合主键

表school.service

host_ip -----存储主机IP
service_name ----- 服务名
port -----服务对应的端口
allow(allow,deny) -----服务是否允许访问
主键： host_ip + port = primary key

mysql> create table service(
-> host_ip varchar(15) not null,
-> service_name varchar(10) not null,
-> port varchar(5) not null,
-> allow enum('Y','N') default 'N',
-> primary key(host_ip,port)
-> );
Query OK, 0 rows affected (0.00 sec)

===设置字段值增 AUTO_INCREMENT

表company.department3

CREATE TABLE department3 (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(30),
comment VARCHAR(50)
);


===设置外键约束 FOREIGN KEY

父表company.employees

mysql> create table employees(
-> name varchar(50) not null,
-> mail varchar(20),
-> primary key(name)
-> )engine=innodb;

子表company.payroll

mysql> create table payroll(
-> id int not null auto_increment,
-> name varchar(50) not null,
-> payroll float(10,2) not null,
-> primary key(id),
-> CONSTRAINT fk_name foreign key(name)
-> references employees(name)
-> on update cascade
-> on delete cascade
-> )engine=innodb;

子表name外键，关联父表（employees 主键name），同步更新，同步删除

结论：
当父表中某个员工的记录修改时，子表也会同步修改
当父表中删除某个员工的记录，子表也会同步删除

六. 修改表 Alter table

语法：
1. 修改表名
ALTER TABLE 表名 RENAME 新表名;

2. 增加字段
ALTER TABLE 表名
                            ADD 字段名 数据类型 [完整性约束条件…],
                            ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
                            ADD 字段名 数据类型 [完整性约束条件…] FIRST; ----排在表的第一列;

ALTER TABLE 表名
                            ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;

3. 删除字段
ALTER TABLE 表名 DROP 字段名;

4. 修改字段
ALTER TABLE 表名
                             MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
                             CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
                             CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

示例：
1. 修改存储引擎
mysql> alter table service
-> engine=innodb;             //engine=myisam|memore|....

2. 添加字段
mysql> create table student10 (id int);
mysql> alter table student10
         -> add name varchar(20) not null,
         -> add age int not null default 22;

mysql> alter table student10
         -> add stu_num int not null after name;      //添加name字段之后

mysql> alter table student10
         -> add sex enum('male','female') default 'male' first;     //添加到最前面

3. 删除字段
mysql> alter table student10
-> drop sex;

4. 修改字段类型modify
mysql> alter table student10
         -> modify age tinyint;

mysql> alter table student10
         -> modify id int not null primary key ;     //修改字段类型、约束、主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int not null primary key auto_increment;            //错误，该字段已经是primary key
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

6. 增加复合主键
mysql> alter table service2
          -> add primary key(host_ip,port);

7. 增加主键
mysql> alter table student1
         -> add primary key(id);

8. 增加主键和自动增长
mysql> alter table student1
         -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int not null;

b. 删除主键
mysql> alter table student10
-> drop primary key;

七. 复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;

只复制表结构
mysql> create table new1_service select * from service where 1=2;      //条件为假，查不到任何记录

复制表结构，包括Key
mysql> create table t4 like employees;

八. 删除表

DROP TABLE 表名;

删除表中数据
在MySQL中有两种方法可以删除数据，一种是DELETE语句，另一种是TRUNCATE TABLE语句。DELETE语句可以通过WHERE对要删除的记录进行选择。而使用TRUNCATE TABLE将删除表中的所有记录。
　　DELETE FROM table1
　　TRUNCATE TABLE table1
　　其中第二条记录中的TABLE是可选的。
如果要删除表中的部分记录，只能使用DELETE语句。
　　DELETE FROM table1 WHERE ...;