练习1

  1. use 29_db1;
  2. create table student(
  3.     id int,
  4.     name varchar(20),
  5.     chinese float,
  6.     english float,
  7.     math float
  8. );
  10. -- 2. 请添加2列信息，出生日期，籍贯。
  11.     alter table student add birthday date;
  12.     alter table student add birthplace varchar(20);
  13. -- 3，请修改语文成绩的数据类型为int型.
  14.     alter table student modify chinese int;
  15. -- 4. 请将各科成绩修改为默认值为0  ★★★★
  16. alter table student alter column chinese  SET     DEFAULT 0;
  17. alter table student alter column math  SET     DEFAULT 0;
  18. alter table student alter column english  SET     DEFAULT 0;
  19. -- 5. 请在里面插入10名学生数据。（名字请以汉字输入）
  20. insert into student values (1,"张三",80,90,70,'2002-7-4',"江苏盐城");
  21. insert into student values (2,"李四",80,50,70,'2001-2-11',"北京西城");
  22. insert into student values (3,"王五",80,90,50,'2002-9-7',"浙江衢州");
  23. insert into student values (4,"赵六",49,90,70,'1997-7-6',"福建福州");
  24. insert into student values (5,"赵耀",80,22,70,'1996-4-16',"江苏盐城");
  25. insert into student values (6,"笨笨张",79,49,70,'1998-6-1',"安徽蚌埠");
  26. insert into student values (7,"王二麻子",80,90,70,'2002-7-16',"江苏盐城");
  27. insert into student values (8,"黑哥",80,90,90,'2002-7-16',"四川德阳");
  28. insert into student values (9,"小张伟",57,90,59,'2000-12-16',"江苏盐城");
  29. insert into student values (10,"四书五经",80,32,70,'2002-4-19',"贵州遵义");
  30. desc student;
  31. select * from student;
  32. -- 6. 请分别找出单科成绩最高的学生名单。语文最高的，英语最高的，数学最高的。 
  33. select * from student order by chinese desc limit 1;
  34. select * from student order by english desc limit 1;
  35. select * from student order by math desc limit 1;
  36. -- 7. 请找出总成绩最高的学生。
  37. select * from student order by chinese+math+english desc limit 1;
  38. -- 10.假设10名同学中有同姓的，如王，请找出姓王同学的信息.
  39. select * from student where name like '王%';
  40. -- 11.请找出各科不及格学生的信息。
  41. select * from student where chinese<60 and english<60 and math<60;
  42. -- 12.请找出有任何一科不及格学生的名称
  43. select * from student where chinese<60 or english<60 or math<60;
  44. -- 13.请找出两科成绩在90分以上的学生名称。
  45. select name from student where chinese+english>90 or english+math>90 or chinese+math>90;
  46. -- 14.请找出没有一科挂科的学生名称。
  47. select * from student where chinese>=60 and english>=60 and math>=60;

练习2

  1. -- 新建一个学生表S，有包含如下信息
  2. -- 学号 id，
  3. -- 学生姓名 name，
  4. -- 性别 gender，
  5. -- 年龄 age，
  6. -- 专业 dept
  7. create table S(
  8. id int,
  9. name varchar(20),
  10. gender varchar(20),
  11. age int,
  12. dept char(10)
  13. );
  14. insert into S values (1,"张三",'male',20,'信息系');
  15. insert into S values (2,"李四",'female',23,'数学系');
  16. insert into S values (3,"王五",'male',24,'信息系');
  17. insert into S values (4,"赵六",'male',21,'计算机科学系');
  18. insert into S values (5,"田七",'female',17,'数学系');
  19. insert into S values (6,"麻花",'male',28,'计算机科学系');
  20. insert into S values (7,"张雪高",'female',28,'信息系');
  21. insert into S values (8,"二蛋",'male',19,'数学系');
  22. insert into S values (9,"大脚丫子",'female',20,'计算机科学系');
  23. insert into S values (10,"五和",'male',24,'数学系');
  24. select * from S;
  26. -- 1.         查询全体学生的学号与姓名。
  27. select id,name from S;
  28. -- 3.         查询全体学生的详细记录。
  29. select * from S;
  30. -- 5.         查询全体学生的姓名、出生年份和所有系使用列别名改变查询结果的列标题
  31. select name,2021-age as birthyear,dept from S;
  32. -- 7.         查询所有年龄在20岁以下的学生姓名及其年龄。
  33. select name,age from S where age<20;
  34. -- 8.       查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
  35. select name,dept,age from S where age between 20 and 23;
  36. -- 9.       查询年龄不在20~23岁之间的学生姓名、系别和年龄。
  37. select name,dept,age from S where age not between 20 and 23;
  39. -- 10.     查询信息系、数学系和计算机系学生的姓名和性别。
  40. select name,gender from S where dept in('信息系','数学系','计算机系');
  41. -- 11.     查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
  42. select name,gender from S where dept not in('信息系','数学系','计算机系');
  43. -- 12.     查询学号为200518的学生的详细情况。
  44. select * from S where id=200518;
  45. -- 13.     查询所有姓刘学生的姓名、学号和性别。
  46. select name,id,gender from S where name like '王%';
  47. -- 14.     查询姓“李”且全名为两个汉字的学生的姓名。
  48. select name from S where name like '李_';
  49. -- 15.     查询名字中第2个字为“立"字的学生的姓名和学号。
  50. select name,id from S where name like '_立%';
  51. -- 16.     查询所有不姓刘的学生姓名。
  52. select name from S where name not like '刘%';
  53. -- 17.     查询学号在201801~201809之间的学生姓名。
  54. select name from S where id between 201801 and 201809;
  55. -- 18.     查询不同院系学生的人数。★★★★
  56. select dept,count(1) from S group by dept;
  57. -- 20.     查询计算机系年龄在20岁以下的学生姓名。
  58. select name from S where age<20 and dept='信息系';

练习3

  1. -- 练习3：某大学研究生院有若干研究生导师，包括职工编号、姓名、职称、研究方向，其中每个导师的职工编号是唯一的。
  2. -- 若干研究生，包括学号、姓名、性别、入学日期，其中每个研究生的学号是唯一的。
  3. -- 每个导师可以带若干研究生，但每个研究生只能有一个导师。
  4. -- 请设计一个数据库，要求可以正确体现导师和研究生之间的关系。
  5. -- 设计完毕之后，请插入一定量的数据，并验证你设计的数据库是否满足要求。
  6. -- 在你设计的表中插入若干数据。
  8. use 29_db2;
  9. create table teacher (
  10. id int primary key auto_increment,
  11. name varchar(20),
  12. title varchar(20),
  13. direation varchar(20)
  14. );
  15. drop table teacher;
  17. create table student(
  18. id int primary key auto_increment,
  19. name varchar(20),
  20. gender varchar(20),
  21. admission date,
  22. supervisor_id int
  23. );
  24. drop table student;
  26. INSERT INTO teacher VALUES (1, '王一', '教授', '人工智能');
  27. INSERT INTO teacher VALUES (2, '谢二', '讲师', '大数据');
  28. INSERT INTO teacher VALUES (3, '张三', '副教授', '计算机图形学');
  29. INSERT INTO teacher VALUES (4, '李四', '研究员', '数据挖掘');
  30. INSERT INTO teacher VALUES (5, '王五', '教授', '人工智能');
  31. INSERT INTO teacher VALUES (6, '赵六', '讲师','物联网');
  34. INSERT INTO student VALUES (1, '赵日天', '男', '2009-09-08', 2);
  35. INSERT INTO student VALUES (2, '叶良辰', '男', '2011-03-04 ', 4);
  36. INSERT INTO student VALUES (3, '龙傲天', '男', '2015-09-04 ', 3);
  37. INSERT INTO student VALUES (4, '马保国', '男', '2019-01-23 ', 1);
  38. INSERT INTO student VALUES (5, '翠花', '女', '2011-04-06 ', 2);
  39. INSERT INTO student VALUES (6, '李雷', '男', '2020-11-14 ', 6);
  40. INSERT INTO student VALUES (7, '韩梅梅', '女', '2020-11-14 ', 4);
  41. INSERT INTO student VALUES (8, '杜子腾', '男', '2020-09-01 ', 6);
  42. INSERT INTO student VALUES (9, '钱多多', '女', '2015-09-04 ', 1);
  43. INSERT INTO student VALUES (10, '李狗蛋', '男', '2017-09-01', 5);
  1. -- 1.请查出每个导师所带研究生的姓名。
  2. select t.name as t_name,s.name as s_name from 
  3.     teacher as t inner join student as s on t.id = s.supervisor_id
  4.     order by s.supervisor_id;

image.png
========================================================================

  1. -- 2.清查出特定姓名的导师所带研究生的姓名。
  2. select t.name as t_name,s.name as s_name from 
  3.     teacher as t inner join student as s on t.id = s.supervisor_id
  4.     and t.name like "王%" order by s.supervisor_id;

image.png
========================================================================

  1. -- 3.请查出每个导师所带研究生的数量。
  2. select t.name, count(s.name) from teacher as t inner join student as s
  3.     on t.id = s.supervisor_id group by s.supervisor_id;

image.png
========================================================================

  1. -- 4.请查出每个导师所带的男研究生的数量。
  2. select t.name, count(s.name) from teacher as t inner join student as s
  3.     on t.id = s.supervisor_id and s.gender = "男" group by s.supervisor_id;

image.png
========================================================================

  1. -- 5.请找出选择哪个研究方向的导师最多。
  2. select direation,count(id) from teacher group by direation order by count(id)
  3.     desc limit 1;

image.png
========================================================================

  1. -- 6.请统计不同职称的导师的个数。
  2. select title,count(id) from teacher group by title;

image.png

练习3_Version2

  1. 作业3：某大学研究生院有若干研究生导师，包括职工编号、姓名、职称、研究方向，其中每个导师的职工编号是唯一的。
  2. 若干研究生，包括学号、姓名、性别、入学日期，其中每个研究生的学号是唯一的。
  3. 每个导师可以带若干研究生，但每个研究生只能有一个导师。
  4. 请设计一个数据库，要求可以正确体现导师和研究生之间的关系。
  6. 研究生导师表
  7. guider
  8. use 29_db1;
  9. create table guider(
  10. id int primary key auto_increment,
  11. name varchar(20) not null,
  12. title char(25) not null,
  13. direction varchar(20)
  14. );
  15. select * from guider;
  16. 研究生表
  17. pg_student
  19. create table pg_student(
  20. id int primary key auto_increment,
  21. name varchar(20) not null,
  22. gender char(5),
  23. entrytime date,
  24. guider_id int
  25. );
  26. select * from pg_student;
  28. 设计完毕之后，请插入一定量的数据，并验证你设计的数据库是否满足要求。
  30. 在你设计的表中插入若干数据。
  31. insert into guider values(1,"张导师","副教授","人工智能");
  32. insert into guider values(2,"王导师","教授","大数据");
  33. insert into guider values(3,"钱导师","副教授","人工智能");
  34. insert into guider values(4,"林导师","副教授","大数据");
  35. insert into guider values(5,"刘导师","院长教授","Python");
  37. insert into pg_student values(101,"赵学生","female","2020-3-19",1);
  38. insert into pg_student values(102,"钱学生","male","2021-7-9",1);
  39. insert into pg_student values(103,"孙学生","female","2019-7-12",2);
  40. insert into pg_student values(104,"李学生","male","2019-9-19",1);
  41. insert into pg_student values(105,"赵周学生","female","2020-4-13",4);
  43. 1.请查出每个导师所带研究生的姓名。
  44. select t.name,s.name from guider as t left join pg_student as s on t.id = s.guider_id;
  46. 2.请查出特定姓名的导师所带研究生的姓名。
  47. select t.name,s.name from guider as t inner join pg_student as s on t.id = s.guider_id and t.name like '张导师';
  49. 3.请查出每个导师所带研究生的数量。
  50. select t.name,count(1) from guider as t left join pg_student as s on t.id = s.guider_id group by t.name;
  52. 4.请查出每个导师所带的男研究生的数量。
  53. select t.name,count(s.gender) from guider as t left join pg_student as s on (t.id = s.guider_id and s.gender = 'male') group by t.name;
  54. 5.请找出选择哪个研究方向的导师最多。
  55. select max(t.direction) from guider as t left join pg_student as s on t.id = s.guider_id;
  57. 6.请统计不同职称的导师的个数。
  58. select title,count(guider.id) from guider group by title;

image.png