数据表(或称表)是数据库最重要的组成部分之一,是其他对象的基础。没有数据表的数据库只是一个空壳而已。
创建表的语句形式
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)
删除了外键约束,索引还在。索引必须在外键约束删除之后方能删除。
级联
修改唯一键