SELECT * FROM Student;
SELECT * FROM subject;
--添加一条数据
insert into Student(stuID,stuName,sex,age,address,phone,email)
values(01,'张三','男',21,'北京市海淀区杏石口路98号','18236886051','182281448@qq.com');
insert into subject(subID,subName)
values(01,'数学');
commit;
insert into Student(stu,stuName,sex,age,address,phone)
values(02,'小雪','女',9,'北京市海淀区杏石口路98号','18276668801');
insert into Student(stu,stuName,sex,age,address,phone)
values(02,'天明','男',8,'北京市海淀区杏石口路98号','18276668801');
--从现有表中提取数据放入新表中
create table tongxunlu as select stuName,phone from Student;
--从现有表中提取数据放入已有表中
insert into tongxunlu SELECT stuName,phone FROM Student;
--修改信息
update Student set email='1194919182@qq.com'
where email is null;
--修改信息
update Student set age=age-1,sex='女'
where stuName like '张_%';
--删除表中地址为空的数据
delete from Student where address is null;
--删除年龄在18·20之间的学生信息
delete from Student where age>=18 and age<=20;
--删除性别为男的学生信息
delete from Student where sex='男';
SELECT * FROM Student;
--删除所有的数据
delete from Student;
rollback;
truncate table Student;
--给性别添加约束:男|女
alter table Student
add constraint ck_sex check(sex='男' or sex='女');
add constraint ck_sex chexk(sex in('男','女'));
--检查约束 年龄必须在18~50之间
alter table Student
add constraint ck_age check(age>=18 and age<=50);
--检查约束 邮箱必须包含‘@’
alter table Student
add constraint ck_email check(email like '_%@_%');