CREATE TABLE course
(
cs_id
int(11) NOT NULL COMMENT ‘课程编号’,
cs_name
varchar(50) NOT NULL COMMENT ‘课程名称’,
cs_credit
tinyint(255) unsigned DEFAULT NULL COMMENT ‘课程学分’,
cs_type
char(12) DEFAULT NULL COMMENT ‘课程类别’,
cs_depart
char(6) DEFAULT NULL COMMENT ‘院系名称’,
PRIMARY KEY (cs_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.查询全部课程的信息。
mysql> select * from course;
2.查询信工学院开设的课程名、课程号及学分
mysql> select cs_name,cs_id,cs_credit from course
-> where cs_depart='信工';
3.查询学分超过3学分的课程代码、课程名和开课单位
mysql> select cs_id,cs_name,cs_depart from course
-> where cs_credit>3;
4.查询计科专业和大数据专业的学生信息
mysql> select * from student
-> where stu_major in ('计科','大数据');
5.查询不是信工学院的学生姓名和学号
mysql> select stu_id,stu_name from student
-> where stu_college not in ('信工学院');
6.查询年龄是17,18,19的学生姓名和专业
mysql> select stu_name,stu_major from student
-> where stu_age in (17,18,19);
7.查询学分在2到4之间课程的信息
mysql> select * from course
-> where cs_credit between 2 and 4;
8.查询课程名称中带“数据”的课程名、课程号及开课单位
mysql> select cs_id,cs_name,cs_depart from course
-> where cs_name like '%数据%';
9.查询信工学院的的专业有哪些
mysql> select cs_name ,cs_depart from course where cs_depart='信工' ;
10.查询年龄为空的学生信息
mysql> select * from student
-> where stu_age='NULL';
Empty set, 1 warning (0.00 sec)
11.查询不是信工学院开设的集中实践课的开课单位和课程名称
mysql> select cs_name,cs_depart from course
-> where cs_depart not in ('信工') and cs_type='集中实践';
12.查询信工学院开设的课程的类型有哪些
mysql> select cs_type from course
-> where cs_depart='信工';
13.查询学生所在的专业个数
mysql> select stu_major,count(1) from student
-> group by stu_major;