数据库(DATABASE):
SHOW DATABASES;
USE db_name;
CREATE DATABASE db_name;
DROP DATABASE db_name;
表(TABLE):
SHOW TABLES;
CREATE TABLE table_name(col1type [NOT NULL PRIMARY KEY],col2type [NOT NULL]);
DROP TABLE table_name;
ALTER TABLE table_name ADD COLUMNcoltype;//列增加后不能被删除
主键(PRIMARY KEY):
ALTER TABLE table_name ADD PRIMARY KEY(col);
ALTER TABLE table_name DROP PRIMARY KEY(col);
外键(FOREIGN KEY):
ALTER TABLE table1_name ADD [CONSTRAINTfk_name ]FOREIGN KEY (col1) REFERENCEStable2_name(col2);
//向table1_name表中添加col1属性关联table2_name表col2属性的外键fk_name
自增(AUTO INCREMENT):
MySQL:CREATE TABLE table_name(col type AUTO_INCREMENT);
ALTER TABLE table_name AUTO_INCREMENT=100;//设置起始值为100
SQL Server:CREATE TABLE table_name(col typeIDENTITY[(start,step)]);//start为起始数值,step为每次增长的数值,不设置则都为1
注释(comment):
CREATE TABLE table_name(col type NOT NULL COMMENT '注释内容');
索引(INDEX):
CREATE [UNIQUE] INDEX index_name ON table_name(col);
DROP INDEX index_name ON table_name;
//索引不可更改,只有删除后重建
视图(VIEW):
CREATE VIEW view_name AS SELECTcol FROMtable_name WHEREcondition;
DROP VIEW view_name;
MySQL查看表结构:
DESC table_name;
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;
//三种效果一样
显示该表创建的SQL语句:
SHOW CREATE TABLE table_name;