use student;
select *from student;
insert into student(same) values('胡利华');
select *from student;
insert into student values('sa',12,null);
insert into student values('sb',13,null);
insert into student values('sc',14,null);
insert into student values('sd',15,null);
insert into student values('sae',16,null);
insert into student values('saf',17,null);
delete from student where id=2;
select *from student;
delete from student where same='sa';
select *from student;
/*对表进行删除作 */
truncate table student;
create database ;
truncate order by desc
select *from student where sage>16 or sage<13;
select *from student where sage between 13 and 16;
select *from student where sage >22 or sage<21;
select same from student where sage=(select max(sage) from studet);
select same, count(*) from student group by same;
/*select count_big(select same from student group by same)from student;*/
select same,num=count(same) from student group by same;
select same,count(same) as 'num' from student group by same;
select *from student order by id,sage desc; /*where sage=(select sage from student order by sage desc);*/
select distinct same from student;
select distinct * from student where sage!=(select sage from student where id=3);
select distinct sno from student where sage<>(select max(sage)from student);
create table study(
id int primary key identity(1,1),
stuname varchar(50),
stuId int,
stuage int
);
select *from study;
insert into study values('林狐冲',1,20);
insert into study values('任盈盈',2,21);
insert into study values('左冷残',3,40);
insert into study values('岳不群',4,45);
insert into study values('林平之',1,18);
insert into study values('向问天',2,42);
insert into study values('任我行',3,48);
insert into study values('东方不败',5,30);
delete from study where id=1;
select *from study;
select *from student,study where student.id=stuId;
select *from student s,study stu where s.id=stuId;
select s.* ,stu.stuname from student s,study stu where s.id=stuId;
select same, sno,stuname ,stuage from student s,study stu where s.id=stuId and stuname like '%任%';
select same,sno,stu.* from student s,study stu where s.id=stuId order by stuage;
insert into student(same) values('安春林');
insert into student(same) values('安春林1');
insert into student(same) values('安春林2');
select *from student;
/*关键之字is的使用*/
select * from student where sage is null;
select *from student where sage is not null;
select *from student where not(sage is null);
delete from student where sage is null;
select *from student;
select * from student where not(sage>21);
select *from student where id not in(select id from student where sage>21);
select top 4 sage from student;
select top 4 * from student;
select top 2 * from student where id not in(select top 4 id from student);
insert student(sex)set;
datatype
alter table student add ssex varchar(10);
select *from student;
alter table student drop column ssex;