MySQL学习笔记7
CREATE TABLE 表名(
字段名 字段类型 列级约束
字段名....
表级约束
)
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,
stu_Name VARCHAR(10),
gender CHAR(1) CHECK(gender='nan' OR gender = 'nv'),
seat INT UNIQUE,
age INT DEFAULT 18,
major_id INT
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
DESC stuinfo;
SHOW INDEX FROM stuinfo;
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stu_Name VARCHAR(20),
gender CHAR(1),
seat INT ,
age INT ,
major_id INT,
CONSTRAINT pk PRIMARY KEY(id),
CONSTRAINT uq UNIQUE(seat),
CONSTRAINT ck CHECK(gender = 'nan' OR gender = '女'),
CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id)
);
SHOW INDEX FROM stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stu_Name VARCHAR(20) NOT NULL,
gender CHAR(1),
seat INT ,
age INT ,
major_id INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id)
)
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT ,
stu_Name VARCHAR(20),
gender CHAR(1),
seat INT ,
age INT ,
major_id INT
);
ALTER TABLE stuinfo MODIFY COLUMN stu_Name VARCHAR(20) NOT NULL;
ALTER TABLE stuinfo MODIFY COLUMN stu_Name VARCHAR(20) DEFAULT 10;
ALTER TABLE stuinfo MODIFY COLUMN stu_Name VARCHAR(20) PRIMARY KEY;
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
ALTER TABLE stuinfo MODIFY COLUMN stu_Name VARCHAR(20) UNIQUE;
ALTER TABLE stuinfo ADD UNIQUE(id);
ALTER TABLE stuinfo ADD FOREIGN KEY(major_id) REFERENCES major(id);
ALTER TABLE stuinfo MODIFY COLUMN stu_Name VARCHAR(20) NULL;
ALTER TABLE stuinfo MODIFY COLUMN age INT;
ALTER TABLE stuinfo DROP PRIMARY KEY;
ALTER TABLE stuinfo DROP INDEX seat;
ALTER TABLE stuinfo DROP FOREIGN KEY(major_id);
DROP TABLE IF EXISTS tab_identuty;
CREATE TABLE tab_identuty(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO tab_identuty VALUES(NULL,'xx');
SELECT *FROM tab_identuty;
ALTER TABLE tab_identuty MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE tab_identuty MODIFY COLUMN id INT PRIMARY KEY ;