数据库操作技术_常用数据库操作1

SQL语句	说明
CREATE DATABASE company	创建company数据库
CREATE TABLE offices ( officeCode  INT         NOT NULL   UNIQUE, city        VARCHAR(50) NOT NULL, address     VARCHAR(50) NOT NULL, country     VARCHAR(50) NOT NULL, postalCode  VARCHAR(15) NOT NULL, PRIMARY KEY(officeCode) );	创建office表
CREATE TABLE employees ( employeeNumber       INT  NOT NULL  PRIMARY KEY, lastName            VARCHAR(50) NOT NULL, firstName                   VARCHAR(50) NOT NULL, mobile                     VARCHAR(25) NOT NULL, officeCode INT       NOT NULL, jobTitle               VARCHAR(50) NOT NULL, birth                    DATE, note                    VARCHAR(255), sex                     VARCHAR(5), CONSTRAINT office_fk FOREIGN KEY(officeCode) REFERENCES offices(officeCode) );	创建表employees，并增加外键约束
ALTER TABLE employees RENAME birth TO employee_birth;	将employees表中的birth字段改为employee_birth
ALTER TABLE employees ALTER COLUMN sex TYPE CHAR(1);	修改sex字段，数据类型改为CHAR(1)
ALTER TABLE employees ALTER COLUMN sex SET NOT NULL;	设置sex字段为非空字段
ALTER TABLE employees DROP note;	删除employees表中的note字段
ALTER TABLE employees ADD COLUMN favoriate_activity VARCHAR(100);	增加favoriate_activity，数据类型为VARCHAR(100)
ALTER TABLE employees DROP CONSTRAINT office_fk;	删除employees表的外键约束
DROP TABLE offices;	删除表offices
ALTER TABLE employees RENAME TO employees_info;	将表名称由employees修改为employees_info