一推沸、數(shù)據(jù)庫
1.創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE BOOKS;
CREATE DATABASE IF NOT EXISTS BOOKS;
2.刪除數(shù)據(jù)庫
DROP DATABASE BOOKS;
DROP DATABASE IF EXISTS BOOKS;
二、表
1.創(chuàng)建表
CREATE TABLE JOB_GRADES(
GRADE_LEVEL VARCHAR(3),
LOWEST_SAL INT,
HIGHEST_SAL INT);
2.修改表
①.修改列名
ALTER TABLE BOOK CHANGE COLUMN PUBLISHDATE PUBDATE DATETIME
②.修改列的類型或約束
ALTER TABLE BOOK MODIFY COLUMN PUBDATE TIMESTAMP;
③.新增列
ALTER TABLE AUTHOR ADD COLUMN ANNUAL DOUBLE;
④.刪除列
ALTER TABLE AUTHOR DROP COLUMN ANNUAL;
⑤.修改表名
ALTER TABLE AUTHOR RENAME TO AUTHORR
3.刪除表
DROP TABLE IF EXISTS BOOK_AUTHOR;
4.復(fù)制表
CREATE TABLE COPY LIKE AUTHOR; -- 僅僅復(fù)制表結(jié)構(gòu)
CREATE TABLE COPY1 SELECT * FROM AUTHOR; -- 復(fù)制表的結(jié)構(gòu)+數(shù)據(jù)
CREATE TABLE COPY3 SELECT ID,AU_NAME FROM AUTHOR WHERER NATION = ''; -- 只復(fù)制部分?jǐn)?shù)據(jù)