Create database <数据库名>; //创建数据库
如Create database mis;
Show databases; //展开数据库;
use <数据库名>;
如use mis;//使用当前数据库
例子:
建立教学数据库基本表,定义主码,姓名不能重复,成绩小于等于100,并录入模拟数据。
student(sno,sname,ssex,sage,sdept)
course(cno,cname,cpno,ccredit)
sc(sno,cno,grade)
create table student ( //创建一个表,名字是student
sno char(12) not null ,
sname varchar(20) unique,
ssex char(3) not null check(ssex='男' or ssex='女'),
sage int not null,
sdept varchar(20) ,
primary key(sno)
);
insert into student(sno, sname, ssex, sage, sdept) //向表中插入数据
values('200215121', '李勇', '男',20,'cs');
insert into student(sno, sname, ssex, sage, sdept)
values('200215122', '刘晨', '女',19,'cs');
insert into student(sno, sname, ssex, sage, sdept)
values('200215123', '王敏', '女',18,'ma');
insert into student(sno, sname, ssex, sage, sdept)
values('200215125', '张立', '男',19,'is');
create table course (
cno varchar(6) not null ,
cname varchar(20) not null ,
cpno varchar(6) ,
ccredit int ,
primary key (cno)
);
insert into course(cno, cname, cpno ,ccredit)
values('1', '数据库',5,4);
insert into course(cno, cname, cpno ,ccredit)
values('2', '数学',null,2);
insert into course(cno, cname, cpno ,ccredit)
values('3', '信息系统',1,4);
insert into course(cno, cname, cpno ,ccredit)
values('4', '操作系统',6,3);
insert into course(cno, cname, cpno ,ccredit)
values('5', '数据结构',7,4);
insert into course(cno, cname, cpno ,ccredit)
values('6', '数据处理',null,2);
insert into course(cno, cname, cpno ,ccredit)
values('7', 'pascal语言',6,4);
create table sc (
sno char(12) not null ,
cno varchar(6) not null ,
grade int check(grade<=100) ,
primary key (sno,cno)
) ;
insert into sc(sno, cno, grade)
values('200215121','1',92);
insert into sc(sno, cno, grade)
values('200215121','2',85);
insert into sc(sno, cno, grade)
values('200215121','3',88);
insert into sc(sno, cno, grade)
values('200215122','2',90);
insert into sc(sno, cno, grade)
values('200215122','3',80);
例子:
使用select语句完成以下操作:
(1)查询“cs”系,姓“刘”的学生学号和姓名;
select sno,sname from student where lower(sdept)='cs' and sname like'刘%';
(2)查询先行课(cpno)为空的课程名称;
select cname from course where cpno is null;
(3)查询选修了“1”号课程的学生学号,按学号由小到大排列;
select sno from sc where cno='1' order by sno;
(4)统计选修了课程的学生人数;
select count(distinct sno) from sc ;
(5)查询选修了“1”号课程,并且成绩90分以上的学生人数;
select count(sno) from sc where cno='1' and grade>=90;
(6)统计各门课程的选修人数;
select cno,count(sno) from sc group by cno;
(7)统计每个学生的选课门数;
select sno,count(cno) from sc group by sno;
(8)查询选修的课程超过二门的学生学号及选课门数;
select sno,count(cno) from sc group by sno having count(cno)>2;
(9)查询每个系男女生的人数;
select sdept,ssex,count(sno) from student student group by sdept,ssex;