Database knowledge supplement

2016.12.27. Tuesday

-- modify table alter

-- add field ADD

ALTER TABLE table name ADD field name various modifiers AFTER field name

ALTER TABLE library ADD booksnumber INT NOT NULL AFTER libraryid

-- modify field MODIFY CHANGE

ALTER TABLE table name MODIFY field name various modifiers

ALTER TABLE library MODIFY librarytype CHAR NOT NULL

ALTER TABLE table name CHANGE field name new field name various modifiers

-- Delete field DROP

ALTER TABLE table name DROP field name

-- View the table structure DESC table name

DESC library

-- check what tables are in the database you are using

SHOW TABLES

-- View the sql statement that creates the table

SHOW CREATE TABLE table name

SHOW CREATE TABLE library

-- change the table name RENAME

ALTER TABLE table name RENAME [to] New table name

2. Operation examples:

USE name

CREATE TABLE `books` (

`id` INT NOT NULL AUTO_INCREMENT UNIQUE COMMENT "self-growth"

`bookid` VARCHAR (30) NOT NULL PRIMARY KEY COMMENT "Book id"

`bookname` VARCHAR (30) NOT NULL COMMENT "title"

`author` VARCHAR (100) NOT NULL COMMENT "author"

`booktype` VARCHAR (100) NOT NULL COMMENT "category"

`borrow` VARCHAR (10) NOT NULL DEFAULT "No" COMMENT "whether to borrow"

`borrowtime` DATETIME DEFAULT NOW () COMMENT "borrowing time"

`borrowpeople` VARCHAR (100) NOT NULL COMMENT "borrower"

);