mysql（3）

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;