表的修改
学习如何在创建后修改 SQL 表。
-- Products 表定义
-- CREATE TABLE Products ( -- ProductID INT PRIMARY KEY AUTO_INCREMENT, -- ProductName VARCHAR(50) NOT NULL UNIQUE, -- CategoryID INT, -- Price DECIMAL(10, 2) NOT NULL, -- Stock INT NOT NULL, -- FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) -- );
ALTER TABLE Products ADD COLUMN ProductDescription VARCHAR(255);
DESCRIBE Products;
-- Products 表定义
-- CREATE TABLE Products ( -- ProductID INT PRIMARY KEY AUTO_INCREMENT, -- ProductName VARCHAR(50) NOT NULL UNIQUE, -- CategoryID INT, -- Price DECIMAL(10, 2) NOT NULL, -- Stock INT NOT NULL, -- ProductDescription VARCHAR(255), -- FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) -- );
ALTER TABLE Products DROP COLUMN ProductDescription;
DESCRIBE Products;
-- Products 表定义
-- CREATE TABLE Products ( -- ProductID INT PRIMARY KEY AUTO_INCREMENT, -- ProductName VARCHAR(50) NOT NULL UNIQUE, -- CategoryID INT, -- Price DECIMAL(10, 2) NOT NULL, -- Stock INT NOT NULL, -- FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) -- );
ALTER TABLE Products MODIFY COLUMN ProductName VARCHAR(100);
DESCRIBE Products;
ALTER TABLE TableName MODIFY ColumnName Type Constraint;
-- Customers 表定义
-- CREATE TABLE Customers ( -- CustomerID INT PRIMARY KEY AUTO_INCREMENT, -- CustomerName VARCHAR(50) NOT NULL, -- Email VARCHAR(50), -- Phone VARCHAR(15), -- Address VARCHAR(100) -- );
ALTER TABLE Customers MODIFY Phone VARCHAR(15) NOT NULL;
DESCRIBE Customers;
-- Customers 表定义
-- CREATE TABLE Customers ( -- CustomerID INT PRIMARY KEY AUTO_INCREMENT, -- CustomerName VARCHAR(50) NOT NULL, -- Email VARCHAR(50), -- Phone VARCHAR(15) NOT NULL, -- Address VARCHAR(100) -- );
ALTER TABLE Customers MODIFY Phone VARCHAR(15) NULL;
DESCRIBE Customers;
DROP TABLE TempTable;
| 字段 | 类型 | 允许为空 | 键 | 默认值 | 额外 |
|---|---|---|---|---|---|
| CategoryID | int | NO | PRI | NULL | auto_increment |
| CategoryName | varchar(50) | NO | UNI | NULL | |
| IsActive | tinyint(1) | YES | 1 |
-- Categories 表定义
-- CREATE TABLE Categories ( -- CategoryID INT PRIMARY KEY AUTO_INCREMENT, -- CategoryName VARCHAR(50) NOT NULL UNIQUE -- );
ALTER TABLE Categories ADD COLUMN IsActive BOOLEAN DEFAULT TRUE;
DESCRIBE Categories;
ALTER TABLE Categories ADD COLUMN IsActive BOOLEAN DEFAULT TRUE;
DESCRIBE Categories;
| 字段 | 类型 | 允许为空 | 键 | 默认值 | 额外 |
|---|---|---|---|---|---|
| CategoryID | int | NO | PRI | NULL | auto_increment |
| CategoryName | varchar(50) | NO | UNI | NULL |
-- Categories 表定义
-- CREATE TABLE Categories ( -- CategoryID INT PRIMARY KEY AUTO_INCREMENT, -- CategoryName VARCHAR(50) NOT NULL UNIQUE, -- OldCategoryCode INT -- );
ALTER TABLE Categories DROP COLUMN OldCategoryCode;
DESCRIBE Categories;
ALTER TABLE Categories DROP COLUMN OldCategoryCode;
DESCRIBE Categories;
| 字段 | 类型 | 允许为空 | 键 | 默认值 | 额外 |
|---|---|---|---|---|---|
| SupplierID | int | NO | PRI | NULL | auto_increment |
| SupplierName | varchar(50) | NO | NULL | ||
| varchar(50) | YES | NULL | |||
| Phone | varchar(20) | NO | UNI | NULL | |
| Address | varchar(100) | YES | NULL |
-- Suppliers 表定义
-- CREATE TABLE Suppliers ( -- SupplierID INT PRIMARY KEY AUTO_INCREMENT, -- SupplierName VARCHAR(50) NOT NULL, -- Email VARCHAR(50), -- Phone VARCHAR(15), -- Address VARCHAR(100) -- );
ALTER TABLE Suppliers MODIFY COLUMN Phone VARCHAR(20) NOT NULL UNIQUE;
DESCRIBE Suppliers;
ALTER TABLE Suppliers MODIFY COLUMN Phone VARCHAR(20) NOT NULL UNIQUE;
DESCRIBE Suppliers;