打开数据库 

use DATABASE dbname;

检查打开的数据库

SELECT DATABASES();

创建表

CREATE TABLE [IF NOT EXISTS] table_name{  clumn_name data_type,  ...}CREATE TABLE student(  name  VARCHAR(20),  age TINYINT UNSIGNED,  avgscore FLOAT(2,8) UNSIGNED);*UNSIGNED 非负数

查看数据表

SHOW TABLES;SHOW TABLES FROM dbname;

查看数据表结构

SHOW COLUMNS FROM tablename;SHOW CREATE TABLE tablename;

插入纪录

CREATE TABLE teacher(	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,	name VARCHAR(20) NOT NULL UNIQUE KEY,	age TINYINT UNSIGNED,	classnum FOREIGN KEY);INSERT student  VALUES("张三丰",20,88);AUTO_INCREMENT  自增

约束整理

NOT NULL 		非空约束	PRIMARY KEY 	主键约束	UNIQUE			唯一约束	DEFAULT			默认约束	FOREIGN KEY		外键约束

添加列

ALTER TABLE student ADD classnum TINYINT UNSIGNED NOT NULL DEFAULT 99 AFTER age;ALTER TABLE studend ADD id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;

删除列

ALTER TABLE student DROP classnum;

添加约束

ALTER TABLE student ADD CONSTRANINT PK_STUDENT_ID PRIMARY KEY (id); 	/*主键约束*/ALTER TABLE student ADD UNIQUE (name);									/*唯一约束*/ALTER TABLE student ADD FOREIGN KEY (classnum) REFERENCES class (id); 	/*外键约束*/ALTER TABLE student ALTER classnum SET DEFAULT 88;						/*默认约束*/

删除约束

ALTER TABLE student DROP PRIMARY KEY;  									/*主键约束*/ALTER TABLE student DROP INDEX	NAME;									/*唯一约束*/ALTER TABLE student DROP FOREIGN KEY CONSTRAINT_NAME(约束名);			/*外键约束*/ALTER TABLE student ALTER classnum DROP DEFAULT;						/*默认约束*/