当前位置:   article > 正文

sql语句练习50题(Mysql版)_sql语句练习50题(mysql)

sql语句练习50题(mysql)

–1.学生表 
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 
–2.课程表 
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 
–3.教师表 
Teacher(t_id,t_name) –教师编号,教师姓名 
–4.成绩表 
Score(s_id,c_id,s_score) –学生编号,课程编号,分数

 

测试数据

  1. --建表
  2. --学生表
  3. CREATE TABLE `Student`(
  4. `s_id` VARCHAR(20),
  5. `s_name` VARCHAR(20) NOT NULL DEFAULT '',
  6. `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
  7. `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
  8. PRIMARY KEY(`s_id`)
  9. );
  10. --课程表
  11. CREATE TABLE `Course`(
  12. `c_id` VARCHAR(20),
  13. `c_name` VARCHAR(20) NOT NULL DEFAULT '',
  14. `t_id` VARCHAR(20) NOT NULL,
  15. PRIMARY KEY(`c_id`)
  16. );
  17. --教师表
  18. CREATE TABLE `Teacher`(
  19. `t_id` VARCHAR(20),
  20. `t_name` VARCHAR(20) NOT NULL DEFAULT '',
  21. PRIMARY KEY(`t_id`)
  22. );
  23. --成绩表
  24. CREATE TABLE `Score`(
  25. `s_id` VARCHAR(20),
  26. `c_id` VARCHAR(20),
  27. `s_score` INT(3),
  28. PRIMARY KEY(`s_id`,`c_id`)
  29. );
  30. --插入学生表测试数据
  31. insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
  32. insert into Student values('02' , '钱电' , '1990-12-21' , '男');
  33. insert into Student values('03' , '孙风' , '1990-05-20' , '男');
  34. insert into Student values('04' , '李云' , '1990-08-06' , '男');
  35. insert into Student values('05' , '周梅' , '1991-12-01' , '女');
  36. insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
  37. insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
  38. insert into Student values('08' , '王菊' , '1990-01-20' , '女');
  39. --课程表测试数据
  40. insert into Course values('01' , '语文' , '02');
  41. insert into Course values('02' , '数学' , '01');
  42. insert into Course values('03' , '英语' , '03');
  43. --教师表测试数据
  44. insert into Teacher values('01' , '张三');
  45. insert into Teacher values('02' , '李四');
  46. insert into Teacher values('03' , '王五');
  47. --成绩表测试数据
  48. insert into Score values('01' , '01' , 80);
  49. insert into Score values('01' , '02' , 90);
  50. insert into Score values('01' , '03' , 99);
  51. insert into Score values('02' , '01' , 70);
  52. insert into Score values('02' , '02' , 60);
  53. insert into Score values('02' , '03' , 80);
  54. insert into Score values('03' , '01' , 80);
  55. insert into Score values('03' , '02' , 80);
  56. insert into Score values('03' , '03' , 80);
  57. insert into Score values('04' , '01' , 50);
  58. insert into Score values('04' , '02' , 30);
  59. insert into Score values('04' , '03' , 20);
  60. insert into Score values('05' , '01' , 76);
  61. insert into Score values('05' , '02' , 87);
  62. insert into Score values('06' , '01' , 31);
  63. insert into Score values('06' , '03' , 34);
  64. insert into Score values('07' , '02' , 89);
  65. insert into Score values('07' , '03' , 98);

 

表数据如下:

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/weixin_40725706/article/detail/780821
推荐阅读
相关标签
  

闽ICP备14008679号