三、表的操作

---------------------------------------------------------       小路原创           ------------------------------------------------

1、创建表

USE DATABASE_NAME;

CREATE TABLE TABLE_NAME(

属性名1 数据类型,

.

.

属性名n 数据类型

)

2、查看表结构

a、使用DESCRIBE语句查看表定义

DESCRIBE/DESC TABLE_NAME;

example:

USE school;

DESCRIBE students

b、使用SHOW CREATE TABLE 语句查看表的详细定义

SHOW CREATE TABLE TABLE_NAME;

example:

USE school;

SHOW CREATE TABLE students;

3、删除表

USE school;

DROP TABLE TABLE_NAME;

4、修改表

a、修改表名

ALTER TABLE old_table_name RENAME [TO] new_table_name;

example；

USE school;

ALTER TABLE students RENAME stus;

b、增加字段

在表的最后一个位置增加字段:

ALTER TABLE table_name

ADD 属性名 属性类型;

example:

ALTER TABLE students

ADD classnumber int;

在表的第一个位置增加字段：

ALTER TABLE table_name

ADD 属性名 属性类型 FIRST;

example:

alter table students

add classnumber int first;

在表的指定字段之后增加字段：

alter table table_name

add 属性名 属性类型

after 属性名

example:

alter table students

add classnumber int 

after Age;

c、删除字段

alter table table_name

drop 属性名

example:

use school;

alter table students

drop classnumber;

d、修改字段

修改字段类型：

alter table table_name

modify 属性名 属性类型

example:

use school;

alter table students

modify Age smallint;

修改字段名称：

alter table table_name

change 旧属性名 新属性名  旧数据类型

example:

use school;

alter table students

change ID id int(11);

同时修改字段的名称和属性：

alter table table_name

change 旧属性名 新属性名 新数据类型

example:

use school;

alter table students

change id ID int(10);

修改字段的顺序：

alter table table_name

modify 属性名1 数据类型 first|after 属性名2

example:

use school;

alter table students

modify Name varchar(255) after Age;

alter table students

modify Name varchar(255) first; 

e、操作表的约束

MySQL支持的完整性约束：

not null: 约束字段的值不能为空

default: 设置字段的默认值

unique(UK): 约束字段的值为唯一

primary key: 约束字段为表的主键，可以作为该表记录的唯一标识

auto_increment: 约束字段的值为自动增加

foreign key(FK): 约束字段为表的外键

注意MySQL不支持Check约束！

设置非空约束：

create table table_name(

属性名 数据类型 not nll,

……);

设置字段的默认值：

create table table_name(

属性名 属性类型 default 默认值,

……);

设置唯一约束：

create table table_name(

属性名 属性类型 unique,

……);

或

create table table_name(

属性名1,属性类型,

属性名2,属性类型,

……

constraint uk_属性名1 unique(属性名1));--为属性名1设置唯一约束，并指定约束名为uk_属性名1

设置主键约束：

create table table_name(

属性名1,属性类型 primary key,

……);

或

create table table_name(

属性名1,属性类型,

……

constraint pk_属性名1 primary key(属性名1));--为属性名1指定主键约束，并指定约束名为pk_属性名1

或

create table table_name(

属性名1,属性类型,

属性名2,属性类型,

……

[constraint pk_属性名1_属性名2] primary key(属性名1,属性名2));--指定多个字段为主键[指定约束名字]

设置字段值自动增加：

create table table_name(

属性名1,属性类型 auto_increment,

……);

example:

use school;

create table new(

id int primary key auto_increment);--如果表中只有一列，那么这列必须是主键才可以设置为自动增加字段

设置外键约束：

create table table_name(

属性名1,属性类型,

属性名2,属性类型,

……

constraint 外键约束名 foreign key(属性名1)

references 表名(属性名));

example:

下面的列级外键约束并不起作用！

use school;

create table depart(

no smallint primary key,

name varchar(30));

insert into depart

values(1,'计科院'),

(2,'化工院');

create table student(

ID int primary key,

dept varchar(30)

references depart(name));

--插入成功

insert into student

values(10001,'计科院'),

(10002,'化工院');

--同样插入成功

insert into student

values(10003,'法学院');

要使MySQL的外键约束生效，应使用表级约束

--参考primary key 创建外键成功

create table depart(

no smallint not null primary key,

name varchar(30) not null);

insert into depart

values(1,'计科院'),

(2,'化工院');

create table student(

ID int primary key,

deptno smallint not null,

constraint foreign key(deptno) references depart(no));

--插入成功

insert into student

values(10001,1),

(10002,2);

--插入失败

insert into student

values(10003,3);

--参考unique not null的创建外键成功

use school;

create table depart(

no smallint not null primary key,

name varchar(30) unique not null);

insert into depart

values(1,'计科院'),

(2,'化工院');

create table student(

ID int primary key,

dept varchar(30) unique not null,

constraint foreign key(dept) references depart(name));

--插入成功

insert into student

values(10001,'计科院'),

(10002,'化工院');

--插入失败

insert into student

values(10003,'数学院');

----参考单独not null的创建外键失败

use school;

drop table depart;

drop table student;

create table depart(

no smallint not null primary key,

name varchar(30) not null);

insert into depart

values(1,'计科院'),

(2,'化工院');

create table student(

ID int primary key,

dept varchar(30) not null,

constraint foreign key(dept) references depart(name));

--插入成功

insert into student

values(10001,'计科院'),

(10002,'化工院');

--插入失败

insert into student

values(10003,'数学院');

创建外键总结：设置外键约束的字段必须依赖于数据库中已经存在的父表的主键约束列或者Unique约束列，并且外键列可以为null。

---------------------------------------------------------       小路原创           ------------------------------------------------