2018-11-12

第一天学习数据库

USE myDb;

create table myemp(
id numeric(4) not null,
name varchar(20),
gender char(2),
birth date,
salary numeric (7,2),
comm numeric(7,2),
job varchar(30),
manager numeric(4),
deptno numeric(2)
);
--给字段添加不允许为空
alter table myemp alter column name varchar(20) not null;
--给性别设置默认值
ALTER TABLE myemp ADD default ('男') for gender
--给表格添加hiredate
alter table myemp ADD hiredate date;
--给hiredatate设置默认值
alter table myemp ADD default ('当前日期') for hiredate
--修改job列的长度为40
alter table myemp alter column job varchar(40)
--删除hiredate列
alter table myemp drop column hiredate;
--先删除约束名
alter table myemp drop constraint DF__myemp__hiredate__3C69FB99;
--插入三条员工信息,给指定字段添加数据
insert into myemp (id,name,job,salary)
values('1001','rose','teacher','5500');
insert into myemp (id,name,job,birth)
values('1002','martha','analyst','1978-9-01')
insert into myemp (id,name,job,birth)
values('1003','donna','manager','1978-9-01')
--修改数据
update myemp set salary = 8500 where name='rose'
--删除表中job为空的员工记录，删除表中名name为rose的员工
delete from myemp where job is null;
delete from myemp where name in('rose');
--将表格里的数据显示出来
select * from myemp;