SQL章节-记录1

好长时间都没写过东西了，记录完整一下吧

1.创建和修改表字段

CREATE TABLE customers(
       id   INT              NOT NULL,
       name VARCHAR (20)     NOT NULL,
       age  INT              NOT NULL,
       address  CHAR (25) ,
       salary   DECIMAL (18, 2),       
       PRIMARY KEY (id)
);
alert table customers MODIFIES salary DECIMAL(18,2) not NULL;
show TABLE customers

修改默认值和取消默认值

ALTER TABLE customers
   MODIFY salary  DECIMAL (18, 2) NOT NULL;
show TABLE customers
ALTER TABLE customers
   MODIFY salary  DECIMAL (18,2) DEFAULT 5000.00;
alter table customers
    alter column salary drop DEFAULT;

默认非空，默认值，取消默认值

ALTER TABLE customers
   MODIFY age INT NOT NULL UNIQUE;

非空唯一

创建主键

CREATE TABLE CUSTOMERS(
       ID   INT              NOT NULL,
       NAME VARCHAR (20)     NOT NULL,
       AGE  INT              NOT NULL,
       ADDRESS  CHAR (25) ,
       SALARY   DECIMAL (18, 2),       
       PRIMARY KEY (ID)
);

修改主键

ALTER TABLE CUSTOMERS 
   ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);

删除主键

alter table customers
    alter column salary drop DEFAULT;
-- 删除修改主键，主键可联立一起写
ALTER TABLE customers 
   ADD CONSTRAINT PK_CUSTID PRIMARY KEY (id, name);
ALTER TABLE customers DROP PRIMARY KEY ;

ALTER TABLE CUSTOMERS DROP PRIMARY KEY ;

 

ALTER TABLE ORDERS 
   ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

修改外键