MySql语法

create table student(
sno int,
sname varchar(20),
ssex char(2),
sage int,
sdept varchar(30),
primary key(sno)
);

create table course(
cno int,
cname varchar(30),
cpno int,
ccredit int,
primary key(cno),
foreign key(cpno) references course(cno)
);

create table sc(
sno int,
cno int,
grade int,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);

alter table student add s_entrance date;
alter table student modify column sage int;
alter table course add unique(cname);

create unique index stusno on student(sno);
create unique index coucno on course(cno);
create unique index scno on sc(sno asc,cno desc);

select sno,sname
from student;

select sno,sname,sdept
from student;

select *
from student;

select sname,2014 - sage
from student;

select sname,'year of birth',2014 - sage,lower(sdept)
from student;

select sname,'year of birth:'birth,2004-sage birthday,lower(sdept) department
from student;

select sno
from sc;

select distinct sno
from sc;

select sname
from student
where sdept = 'cs';

select sname,sage
from student
where sage < 20;

select distinct sno
from sc
where grade < 60;

select sname,sdept,sage
from student
where sage between 20 and 23;

select sname,ssex
from student
where sdept in('cs','ms','is');

select sname,ssex
from student
where sdept not in('cs','ms','is');

select *
from student
where sno = '220215121';

select sname,sno,ssex
from student
where sname like '刘%';

select sname
from student
where sname like '欧阳__';

select sname,sno,ssex
from student
where sname not like '刘%';

select sno,cno
from sc
where grade is null;

select sno,cno
from sc
where grade is not null;

select sname
from student
where sage < 20 and sdept = 'cs';

select sno,grade
from sc
where cno = 3 order by grade desc;

select *
from student
order by sdept,sage desc;

select count(*)
from student;

select count(distinct sno)
from sc;

select avg(grade)
from sc
where cno = 1;

select max(grade)
from sc
where cno = 1;

select sum(ccredit)
from sc,course
where sno = 200215012 and sc.cno = course.cno;

select cno,count(sno)
from sc
group by sno;

select sno
from sc
group by sno
having count(*) > 3;

select student.*,sc.*
from student,sc
where student.sno = sc.sno;

select student.sno,sname,ssex,sage,sdept,cno,grade
from student,sc
where student.sno = sc.sno;

select student.sno,sname,ssex,sage,sdept,cno,grade
from student
left join sc
on student.sno=sc.sno;

select student.sno,sname,ssex,sage,sdept
from student,sc
where student.sno = sc.sno and sc.cno = 2 and sc.grade > 90;

select sname
from student
where sno in
    (select sno
    from sc
    where cno =2);

select sno,sname,sdept
from student
where sdept in
    (
    select sdept
    from student
    where sname = '刘晨'
    );

select sno,sname
from student
where sno in(
    select sno
    from sc
    where cno in(
        select cno
        from course
        where cname = '信息系统'
        )
    );

select student.sno,sname
from student,sc,course
where student.sno = sc.sno and
      sc.cno = course.cno and
      course.cname = '信息系统';

select sno,sname,sdept
from student
where sdept =
    (
    select sdept
    from student
    where sname = '刘晨'
    );

select sno,cno
from sc x
where grade >=(
    select avg(grade)
    from sc y
    where y.sno = x.sno
    );

select sname,sage
from student
where sage <any (
    select sage
    from student
    where sdept = 'cs'
    )
and sdept <> 'cs';

select sname,sage
from student
where sage < (
    select max(sage)
    from student
    where sdept = 'cs'
    )
and sdept <> 'cs';



select sname,sage
from student
where sage <all (
    select sage
    from student
    where sdept = 'cs'
    )
and sdept <> 'cs';

select sname,sage
from student
where sage < (
    select min(sage)
    from student
    where sdept = 'cs'
    )
and sdept <> 'cs';

select *
from student
where sdept = 'cs'
union
select *
from student
where sage <= 19;

select sno
from sc
where cno = 1
union
select sno
from sc
where cno = 2;

转载于:https://www.cnblogs.com/SArwen/p/4104303.html