USE db;
-- 列出查看当前数据库中的所有表
SHOW TABLES;
SHOW TABLES LIKE ‘s%‘;
CREATE TABLE 表名
(
列名 类型 修饰 约束,
sid INT(3) UNSIGNED ZEROFILL PRIMARY KEY AUTO_INCREMENT
sgender ENUM(‘男‘,‘女‘,‘保密‘) DEFAULT ‘男‘
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 建立表
CREATE TABLE IF NOT EXISTS s
(
sid INT(3) UNSIGNED ZEROFILL PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(5) NOT NULL,
sgender ENUM(‘男‘,‘女‘,‘保密‘) DEFAULT ‘男‘
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 快速复制表的结构并建立新表
CREATE TABLE IF NOT EXISTS yy LIKE student;
CREATE TABLE IF NOT EXISTS xx SELECT * FROM student;
SELECT * FROM student;
SELECT * FROM yy;
INSERT INTO xx SELECT * FROM xx;
INSERT INTO student SELECT * FROM student;
SELECT * FROM xx;
DESC yy;
INSERT INTO s VALUES(30,‘张三‘,‘aa‘);
INSERT INTO s(sname) VALUES(‘李勇‘);
DESC s;
SELECT * FROM s WHERE sgender = ‘‘;
-- 删除表
DROP TABLE IF EXISTS st;
-- 删除一批表
DROP TABLE IF EXISTS `s`,`xx`,`cc`;
-- 修改表的名子
RENAME TABLE student TO bbs_student;
ALTER TABLE bbs_student RENAME student;
ALTER TABLE bbs_student RENAME TO student;
-- 修改列的名称 及 列的数据类型
ALTER TABLE student CHANGE sid sno INT UNSIGNED;
-- 删除一个表的主键
ALTER TABLE student DROP PRIMARY KEY;
-- 给一个表加上主键
ALTER TABLE student ADD CONSTRAINT pk PRIMARY KEY(sno);
DESC student;
-- 在表的最后增加一列
ALTER TABLE student ADD COLUMN sscore TINYINT UNSIGNED;
ALTER TABLE student ADD COLUMN sscore TINYINT UNSIGNED FIRST;
ALTER TABLE student ADD COLUMN sscore TINYINT UNSIGNED AFTER sname;
-- 删除一列
ALTER TABLE student DROP COLUMN sscore;
-- 查看表结构
DESC student;表的操作-建立表-删除表-修改表的名字-修改列的名字及数据类型-删除/添加主键-在表的最后增加一列-查看表的结构
原文：http://blog.csdn.net/buzhang1314/article/details/39530621
