SQL复习-ddl

DDL

创造，毁灭，更改

CREATE TABLE Branch(branch_id VARCHAR(15), name VARCHAR(30) NOT NULL, asset INT UNSIGNED NOT NULL, PRIMARY KEY(branch_id) );

DROP TABLE Branch;

当有referential constraints时系统会拒绝drop table

ALTER TABLE Branch ADD branch_phone INT(12);

ALTER TABLE Branch DROP branch_phone;

ALTER TABLE Owner ADD FOREIGN KEY(customer_id) REFERENCES Customer(customer_id); 

添加，删除，更新

INSERT INTO Branch VALUES('B1','Central',700000),('B2','Causeway Bay',9000000);

LOAD DATA LOCAL INFILE 'text.txt' INTO TABLE Branch FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n';

DELETE FROM Branch;

DELETE FROM Branch WHERE name = 'Central';

UPDATE Branch SET asset = 0 WHERE branch_id = 'B1';

这里可以用各种四则运算和比较大小

想要有if从句的：

UPDATE Account SET balance = CASE

WHEN balance<=500 THEN balance*1.05

ELSE balance*1.06

END