MySQL数据表的常用操作

         数据表（或称表）是数据库最重要的组成部分之一，是其他对象的基础。没有数据表的数据库只是一个空壳而已。

创建表的语句形式

CREATE TABLE 表名 (

属性名数据类型 [列级完整性约束条件],

属性名数据类型 [列级完整性约束条件],

......

属性名数据类型 ,

[表级完整性约束]

);

 

完整性约束条件表：

PRIMARY KEY 主键

FOREIGN KEY 外键

NOT NULL  不能为空

UNIQUE  唯一索引

AUTO_INCREMENT 自动增加

DEFAULT  默认值 

 

CREATE TABLE example0 (

id INT,    

name VARCHAR(20),    

sex BOOLEAN );

设置表的主键

单字段主键

属性名 数据类型 PRIMARY KEY

CREATE TABLE example1(stu_id INT PRIMARYKEY,

stu_name VARCHAR(20),

stu_sex BOOLEAN,

);

多字段主键

PRIMARYKEY(属性名 1, 属性名 2…属性名 n)

CREATE TABLE example2(stu_id INT,

course_id INT,

grade FLOAT,

PRIMARY KEY(stu_id, course_id)

);

设置表的外键

CONSTRAINT外键别名 FOREIGN KEY (属性 1.1, 属性 1.2,…, 属性 1.n)

REFERENCES表名(属性 2.1, 属性 2.2,…, 属性 2.n)

CREATE TABLE example3(stu_id INT,

course_id INT,

CONSTRAINT c_fk FOREIGN KEY (stu_id,course_id)

REFERENCES example2(stu_id, course_id)

);

设置表的非空约束

属性名 数据类型 NOT NULL

CREATE TABLE example4(id INT NOT NULLPRIMARY KEY,

name VARCHAR(20) NOT NULL,

stu_id INT,

CONSTRAINT d_fk FOREIGN KEY(stu_id),

REFERENCES example1(stu_id)

);

设置表的唯一性约束

属性名 数据类型 UNIQUE

CREATE TABLE example5(id INT PRIMARY KEY,

stu_id INT UNIQUE,

name VARCHAR(20) NOT NULL

);

设置表的属性值自动增加

属性名 数据类型 AUTO_INCREMENT

CREATE TABLE example6(id INT PRIMARY KEYAUTO_INCREMENT,

stu_id INT UNIQUE,

name VARCHAR(20) NOT NULL

);

设置表的属性的默认值

属性名 数据类型 DEFAULT 默认值

CREATE TABLE example7(id INT PRIMARY KEYAUTO_INCREMENT,

stu_id INT UNIQUE,

name VARCHAR(20) NOT NULL.

english VARCHAR(20) DEFAULT ‘zero’,

computer FLOAT DEFAULT 0

);

根据已有的表创建新表

create table tab_new liketab_old

完全拷贝，包括约束

 

create table tab_new asselect col1,col2… from tab_old

没有拷贝约束

Mysql临时表

当你创建临时表的时候，你可以使用temporary关键字。如：

create temporary table tmp_table(name varchar(10) not null,passwd char(6)not null)‘

或

     CREATE TEMPORARY TABLE IF NOT EXISTS sp_output_tmp ENGINE= MEMORY [AS] SELECT …from… where ID=current_id;

　    临时表只在当前连接可见，当这个连接关闭的时候，会自动drop。这就意味着你可以在两个不同的连接里使用相同的临时表名，并且相互不会冲突，或者使用已经存在的表，但不是临时表的表名。(当这个临时表存在的时候，存在的表被隐藏了，如果临时表被drop，存在的表就可见了)。创建临时表你必须有

create temporary table 权限。

　　下面几点是临时表的限制：

　　临时表只能用在 memory,myisam,merge,或者innodb

　　临时表不支持mysql cluster(簇)

　　在同一个query语句中，你只能查找一次临时表。例如：下面的就不可用

mysql> SELECT * FROM temp_table, temp_table AS t2;

ERROR 1137: Can't reopen table: 'temp_table'

　　如果在一个存储函数里，你用不同的别名查找一个临时表多次，或者在这个存储函数里用不同的语句查找，这个错误都会发生。

show tables 语句不会列举临时表

　　你不能用rename来重命名一个临时表。但是，你可以alter table代替：

mysql>ALTER TABLE orig_name RENAME new_name;

 

临时表用完后要记得drop掉：

    DROP [TEMPORARY] TABLE IF EXISTS sp_output_tmp;

 

查看数据表列表

SHOWTABLES;

注意：可以通过FROM来查看其它数据库中的所有表，当前数据库不发生改变

查看表结构

查看表基本结构语句

DESCRIBE

SHOW COLUMNS

查看表详细结构语句

SHOWCREATE TABLE

修改数据表

修改表字段类型

ALTER TABLE tb_name MODIFY[COLUMN]column_definition[FIRST|AFTER col_name]

 

ALTER TABLE tb_name CHANGE[COLUMN]old_col_name column_definition[FIRST|AFTER col_name]

        change和modify都可以修改表的定义（字段类型），不同的是change后面需要写两次列名，不方便。但是change的优点是可以修改列名称，modify则不能。

增加表字段

ALTER TABLE tb_name ADD[COLUMN]column_definition[FIRST|AFTER col_name]

删除表字段

ALTER TABLE tb_name DROP[COLUMN] col_name

更改表名

ALTER TABLE tb_name RENAME [TO|AS] new_ tb_name

 

RENAME TABLE tb_name1 TO new_ tb_name1[, tb_name2TO new_ tb_name2,……]

修改表存数引擎

ALTER TABLE 表名ENGINE=INNODB|MYISAM|MEMOERY;

复合修改

修改主键

Alter table tabname addprimary key(col)

Alter table tabname dropprimary key[(col)]

注意：删除主键约束，该字段的上的索引也自动删除

修改外键

注意：添加外键约束会自动在该字段上添加上索引。（见上表中的KEY）

删除了外键约束，索引还在。索引必须在外键约束删除之后方能删除。

级联

修改唯一键