USE db_name;

CREATE DATABASE school;Use school;CREATE TABLE `StuInfo` (`STU_ID`  varchar(10) NOT NULL ,`STU_NAME`  varchar(25) NOT NULL ,`STU_SEX`  char(2) NULL ,`CLASS`  var char(10) NULL ,PRIMARY KEY (`STU_ID`));ALTER TABLE `scoreinfo` ADD CONSTRAINT `SS` FOREIGN KEY (`STU_ID`) REFERENCES `StuInfo` (`STU_ID`);CREATE TABLE `NewTable` (`STU_ID`  varchar(10) NOT NULL ,`Tea_ID`  varchar(10) NOT NULL ,PRIMARY KEY (`STU_ID`, `Tea_ID`),CONSTRAINT `STS` FOREIGN KEY (`STU_ID`) REFERENCES `StuInfo` (`STU_ID`),CONSTRAINT `STT` FOREIGN KEY (`Tea_ID`) REFERENCES `TeaInfo` (`TEA_ID`));SELECTteainfo.TEA_NAME,stuinfo.STU_NAME,stuinfo.CLASSFROMstuinfo  join stuandteaon stuinfo.STU_ID=stuandtea.STU_IDjoin teainfoon teainfo.TEA_ID=stuandtea.Tea_ID

age TINYINT UNSIGNED,(无符号整型，>0)

salary FLOAT(8,2) UNSIGNED

SHOW TABLES FROM db_name;

SELECT DATABASE();//当前数据库

SHOW COLUMNS FROM table_name

 

INSERT [INTO] table_name [(cloumn_name,..)] VALUES(val...);

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(25) NOT NULL UNIQUE KEY,

sex ENUM('1','2','3') DEFAULT '3',

 

约束：

约束保证数据的完整性和一致性。

约束分为表级约束（两个字段以上）和列级约束(一个字段)

包括：NOT NULL,PRIMARY KEY,UNIEUQ KEY,DEFAULT，FOREIGN KEY（实现一对一，一对多）

 

default-storage-engine=INNODB

SHOW INDEXES FROM table_name

CASCADE：从父表删除或更新，子表会自动删除更新匹配的行

SET NULL:从父表删除或更新行，设置子表中外键列为null

FOREIGN KEY (id) REFERENCES table_name(id) ON DELETE CASCADE

RESTRICT 拒接对父表的删除或更新

 

ALTER TABLE table_name ADD [COLUNM] (col_name col_definition,...) [FIRST|AFTER col_name]

ALTER TABLE table_name DROP [COLUNM] col_name

ALTER TABLE table_name ADD [CONSTRAINT PK_symbol] PRIMARY KEY [index_type] (index_col_name,)

ALTER TABLE table_name ALTER [COLUNM] col_name SET DEFAULT