数据库

--练习三
CREATE TABLE STUDENT(
编号 NUMERIC(4),
姓名 NVARCHAR(20),
性别 NVARCHAR(1),
身高 NUMERIC(5,2),
);
INSERT INTO STUDENT VALUES('2132','菅福堂','男',170.00);
SELECT*FROM STUDENT;
--练习四
CREATE TABLE STUDENT2(
ID NVARCHAR(20),
Gender NCHAR(1),
residence VARCHAR(100),
);
INSERT INTO STUDENT2 VALUES('菅福堂','男','上海市静安区南星路');
SELECT*FROM STUDENT2;
--练习五
CREATE TABLE STUDENT3(
出生年月 DATE
);
INSERT INTO STUDENT3 VALUES('1997-04-01');
SELECT*FROM STUDENT3;
--练习六
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;
--练习七
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;
--练习八
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);
--练习九
UPDATE myemp SET SALARY = 8500 WHERE SNAME = 'rose';
UPDATE myemp SET SALARY = 6500  WHERE ID = 1003;
UPDATE myemp SET JOB = 'ANALYST' WHERE ID = 1003;
--练习十
DELETE FROM myemp WHERE JOB IS NULL;
DELETE FROM myemp WHERE SNAME = 'rose';