--練習(xí)三
CREATE TABLE STUDENT(
編號(hào) NUMERIC(4),
姓名 NVARCHAR(20),
性別 NVARCHAR(1),
身高 NUMERIC(5,2),
);
INSERT INTO STUDENT VALUES('2132','菅福堂','男',170.00);
SELECT*FROM STUDENT;
--練習(xí)四
CREATE TABLE STUDENT2(
ID NVARCHAR(20),
Gender NCHAR(1),
residence VARCHAR(100),
);
INSERT INTO STUDENT2 VALUES('菅福堂','男','上海市靜安區(qū)南星路');
SELECT*FROM STUDENT2;
--練習(xí)五
CREATE TABLE STUDENT3(
出生年月 DATE
);
INSERT INTO STUDENT3 VALUES('1997-04-01');
SELECT*FROM STUDENT3;
--練習(xí)六
CREATE TABLE employee(
ID NUMERIC(4),
SNAME VARCHAR(20),
GENDER CHAR(2),
BIRTH DATE,
SALARY NUMERIC(7,2),
COMM NUMERIC(7,2),
JOB VARCHAR(30),
MANAGER NUMERIC(4),
DEPTNO NUMERIC(2),
);
INSERT INTO employee VALUES(1234,'菅福堂','男',NULL,NULL,NULL,NULL,NULL,NULL);
SELECT*FROM employee;
--練習(xí)七
EXEC sp_rename employee,mymyee;
EXEC sp_rename mymyee,myemp;
ALTER TABLE myemp ADD hiredate date;
UPDATE myemp SET hiredate = GETDATE();
alter table myemp alter column JOB varchar(40);
ALTER TABLE myemp ADD DEFAULT'CLERK'FOR JOB WITH VALUES;
ALTER TABLE myemp DROP COLUMN hiredate;
SELECT*FROM myemp;
--練習(xí)八
INSERT INTO myemp VALUES(1001,'rose',NULL,NULL,5500,NULL,'PROGRAMMER',NULL,NULL);
INSERT INTO myemp VALUES(1002,'martha',NULL,'1978-09-01',NULL,NULL,'ANALYST',NULL,NULL);
INSERT INTO myemp VALUES(1003,'donna',NULL,'1978-09-01',NULL,NULL,'MANAGER',NULL,NULL);
--練習(xí)九
UPDATE myemp SET SALARY = 8500 WHERE SNAME = 'rose';
UPDATE myemp SET SALARY = 6500  WHERE ID = 1003;
UPDATE myemp SET JOB = 'ANALYST' WHERE ID = 1003;
--練習(xí)十
DELETE FROM myemp WHERE JOB IS NULL;
DELETE FROM myemp WHERE SNAME = 'rose';