1. 创建用户
  2. create user 'test1'@'localhost' identified by 'test1';
  3. 创建用户
  4. INSERT INTO mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) 
  5. VALUES ('%', 'test2', 'password', '', '', '');
  6. 查看用户
  7. select host,user,authentication_string from mysql.user;
  8. # FLUSH 命令让用户生效
  9. FLUSH PRIVILEGES; 
  11. DROP USER 'test1'@'localhost';   #删除用户'test1'@'localhost'
  13. --导出文件 不进入mysql
  14. mysqldump -uroot -p ms05 student > G:\student.sql
  15. # 然后输入密码
  17. --导入文件
  18. mysql -uroot -pcrossfire395612 -Dms05 < G:\student.sql
  19. mysql -uroot -p ms05< G:\student.sql
  20. source G:\student.sql
  23. # student表
  24. DROP TABLE IF EXISTS `student`;
  25. CREATE TABLE `student` (
  26. `stuid` varchar(16) NOT NULL COMMENT '学号',
  27. `stunm` varchar(20) NOT NULL COMMENT '学生姓名',
  28. PRIMARY KEY (`stuid`)
  29. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  31. -- ----------------------------
  32. -- Records of student
  33. -- ----------------------------
  34. INSERT INTO `student` VALUES ('1001', '张三');
  35. INSERT INTO `student` VALUES ('1002', '李四');
  36. INSERT INTO `student` VALUES ('1003', '赵二');
  37. INSERT INTO `student` VALUES ('1004', '王五');
  38. INSERT INTO `student` VALUES ('1005', '刘青');
  39. INSERT INTO `student` VALUES ('1006', '周明');
  40. INSERT INTO `student` VALUES ('1007', '吴七');
  41. #score表
  42. DROP TABLE IF EXISTS `score`;
  43. CREATE TABLE `score` (
  44. `stuid` varchar(16) NOT NULL COMMENT '学号',
  45. `courseno` varchar(20) NOT NULL COMMENT '课程号',
  46. `scores` float DEFAULT NULL COMMENT '分数',
  47. PRIMARY KEY (`stuid`,`courseno`)
  48. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  50. -- ----------------------------
  51. -- Records of score
  52. -- ----------------------------
  53. INSERT INTO `score` VALUES ('1001', 'C001', '67');
  54. INSERT INTO `score` VALUES ('1001', 'C002', '87');
  55. INSERT INTO `score` VALUES ('1001', 'C003', '83');
  56. INSERT INTO `score` VALUES ('1001', 'C004', '88');
  57. INSERT INTO `score` VALUES ('1001', 'C005', '77');
  58. INSERT INTO `score` VALUES ('1001', 'C006', '77');
  59. INSERT INTO `score` VALUES ('1002', 'C001', '68');
  60. INSERT INTO `score` VALUES ('1002', 'C002', '88');
  61. INSERT INTO `score` VALUES ('1002', 'C003', '84');
  62. INSERT INTO `score` VALUES ('1002', 'C004', '89');
  63. INSERT INTO `score` VALUES ('1002', 'C005', '78');
  64. INSERT INTO `score` VALUES ('1002', 'C006', '78');
  65. INSERT INTO `score` VALUES ('1003', 'C001', '69');
  66. INSERT INTO `score` VALUES ('1003', 'C002', '89');
  67. INSERT INTO `score` VALUES ('1003', 'C003', '85');
  68. INSERT INTO `score` VALUES ('1003', 'C004', '90');
  69. INSERT INTO `score` VALUES ('1003', 'C005', '79');
  70. INSERT INTO `score` VALUES ('1003', 'C006', '79');
  71. INSERT INTO `score` VALUES ('1004', 'C001', '70');
  72. INSERT INTO `score` VALUES ('1004', 'C002', '90');
  73. INSERT INTO `score` VALUES ('1004', 'C003', '86');
  74. INSERT INTO `score` VALUES ('1004', 'C004', '91');
  75. INSERT INTO `score` VALUES ('1004', 'C005', '80');
  76. INSERT INTO `score` VALUES ('1004', 'C006', '80');
  77. INSERT INTO `score` VALUES ('1005', 'C001', '71');
  78. INSERT INTO `score` VALUES ('1005', 'C002', '91');
  79. INSERT INTO `score` VALUES ('1005', 'C003', '87');
  80. INSERT INTO `score` VALUES ('1005', 'C004', '92');
  81. INSERT INTO `score` VALUES ('1005', 'C005', '81');
  82. INSERT INTO `score` VALUES ('1005', 'C006', '81');
  83. INSERT INTO `score` VALUES ('1006', 'C001', '72');
  84. INSERT INTO `score` VALUES ('1006', 'C002', '92');
  85. INSERT INTO `score` VALUES ('1006', 'C003', '88');
  86. INSERT INTO `score` VALUES ('1006', 'C004', '93');
  87. INSERT INTO `score` VALUES ('1006', 'C005', '82');
  88. INSERT INTO `score` VALUES ('1006', 'C006', '82');
  89. #course表
  90. DROP TABLE IF EXISTS `courses`;
  91. CREATE TABLE `courses` (
  92. `courseno` varchar(20) NOT NULL COMMENT '课程号',
  93. `coursenm` varchar(100) NOT NULL COMMENT '课程姓名',
  94. PRIMARY KEY (`courseno`)
  95. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';
  97. -- ----------------------------
  98. -- Records of courses
  99. -- ----------------------------
  100. INSERT INTO `courses` VALUES ('C001', '大学语文');
  101. INSERT INTO `courses` VALUES ('C002', '新视野英语');
  102. INSERT INTO `courses` VALUES ('C003', '离散数学');
  103. INSERT INTO `courses` VALUES ('C004', '概率论与数理统计');
  104. INSERT INTO `courses` VALUES ('C005', '线性代数');
  105. INSERT INTO `courses` VALUES ('C006', '高等数学(一)');
  106. INSERT INTO `courses` VALUES ('C007', '高等数学(二)');

in和exsits

  1.  select * from student s where s.stuid in(select stuid from score ss where ss.stuid = s.stuid)
  2.  select * from student s where EXISTS(select stuid from score ss where ss.stuid = s.stuid)

in: 先执行子查询，把它读到内存当中，然后外查询去对比，外查询的结果集逐一跟in集合判断
exists: 先执行外查询，一一判断
image.png

inner join和left join

  1. select * from student s
  2. inner join score sc on s.stuid=sc.stuid
  3. # 笛卡尔积
  5. -- distinct
  6. select distinct s.* from studenr s
  7. inner join score sc on s.stuid=sc.stuid
  9. -- left
  10. select distinct s.* from student s
  11. left join score sc on s.stuid=sc.stuid
  1. select s.stuid 学号, s.stunm 学生姓名, GROUP_CONCAT(o.coursenm )课程,GROUP_CONCAT(c.scores) 分数
  2. from student s
  3. left join score c on s.stuid=c.stuid
  4. left join courses o on o.courseno=c.courseno
  5. group by s.stuid,stunm

行转列

  1. -- 方式一
  2. select t.stuid 学号, t.stunm 学生姓名
  3.             ,max(c1) 大学语文
  4.             ,max(c2) 新视野英语
  5.             ,max(c3) 离散数学
  6.             ,max(c4) 概率论与数理统计
  7.             ,max(c5) 线性代数
  8.             ,max(c6) 高等数学
  9. from (
  10. select s.stuid
  11.              ,s.stunm
  12.              ,case courseno when 'C001' then scores end as c1
  13.              ,case courseno when 'C002' then scores end as c2
  14.              ,case courseno when 'C003' then scores end as c3
  15.              ,case courseno when 'C004' then scores end as c4
  16.              ,case courseno when 'C005' then scores end as c5
  17.              ,case courseno when 'C006' then scores end as c6
  19.       from  student s
  20.         left join score c 
  21.     on s.stuid=c.stuid
  22. )as t
  23. group by t.stuid,t.stunm;
  25. -- 方式二
  26. select s.stuid 学号
  27.      ,s.stunm 学生姓名
  28.      ,max(case courseno when 'C001' then scores end) 大学语文
  29.      ,max(case courseno when 'C002' then scores end) 新视野英语
  30.      ,max(case courseno when 'C003' then scores end) 离散数学
  31.      ,max(case courseno when 'C004' then scores end) 概率论与数理统计
  32.      ,max(case courseno when 'C005' then scores end) 线性代数
  33.      ,max(case courseno when 'C006' then scores end) 高等数学
  35. from  student s
  36. left join score c 
  37. on s.stuid=c.stuid
  38. group by s.stuid,s.stunm;

null为0

  1. ifnull(c1,0) -- 如果为0,值为0
  2. --或者加else
  3. select s.stuid 学号
  4.      ,s.stunm 学生姓名
  5.      ,max(case courseno when 'C001' then scores else 0 end) 大学语文
  6.      ,max(case courseno when 'C002' then scores else 0 end) 新视野英语
  7.      ,max(case courseno when 'C003' then scores else 0 end) 离散数学
  8.      ,max(case courseno when 'C004' then scores else 0 end) 概率论与数理统计
  9.      ,max(case courseno when 'C005' then scores else 0 end) 线性代数
  10.      ,max(case courseno when 'C006' then scores else 0 end) 高等数学
  12. from  student s
  13. left join score c 
  14. on s.stuid=c.stuid
  15. group by s.stuid,s.stunm;

行转列来源详解

行转列

创建视图

  1. -- 创建视图
  2. create view v_ss as select语句

列转行

  1. select t.学号,学生姓名,'大学语文' as 科目,`大学语文` as 分数 from v_ss t union all
  2. select t.学号,学生姓名,'新视野英语' as 科目,`新视野英语` as 分数 from v_ss t union all
  3. select t.学号,学生姓名,'离散数学' as 科目,`离散数学` as 分数 from v_ss t union all
  4. select t.学号,学生姓名,'概率论与数理统计' as 科目,`概率论与数理统计` as 分数 from v_ss t union all
  5. select t.学号,学生姓名,'线性代数' as 科目,`线性代数` as 分数 from v_ss t union all
  6. select t.学号,学生姓名,'高等数学' as 科目,`高等数学` as 分数 from v_ss t;
  1. # 报表开发 学生分数报表 ，要求 展示： 学号、学生姓名，大学语文 , 新视野英语 ,离散数学 ,概率论与数理统计,线性代数,高等数学1 ,高等数学2 
  3. select s.stuid 学号, s.stunm 学生姓名, GROUP_CONCAT(o.coursenm )课程,GROUP_CONCAT(c.scores) 分数
  4. from student s
  5. left join score c on s.stuid=c.stuid
  6. left join courses o on o.courseno=c.courseno
  7. group by s.stuid,stunm
  9. #-------------------------------------------------------------#
  10. -- 行转列
  11. create view v_ss as 
  12. select t.stuid 学号, t.stunm 学生姓名
  13.             ,max(ifnull(c1,0)) 大学语文
  14.             ,max(ifnull(c2,0)) 新视野英语
  15.             ,max(ifnull(c3,0)) 离散数学
  16.             ,max(ifnull(c4,0)) 概率论与数理统计
  17.             ,max(ifnull(c5,0)) 线性代数
  18.             ,max(ifnull(c6,0)) 高等数学
  19. from (
  20. select s.stuid
  21.              ,s.stunm
  22.              ,case courseno when 'C001' then scores end as c1
  23.              ,case courseno when 'C002' then scores end as c2
  24.              ,case courseno when 'C003' then scores end as c3
  25.              ,case courseno when 'C004' then scores end as c4
  26.              ,case courseno when 'C005' then scores end as c5
  27.              ,case courseno when 'C006' then scores end as c6
  29.       from  student s
  30.         left join score c 
  31.     on s.stuid=c.stuid
  32. )as t
  33. group by t.stuid,t.stunm;
  35. #-------------------------------------------------------------#
  37. select s.stuid 学号
  38.      ,s.stunm 学生姓名
  39.      ,max(case courseno when 'C001' then scores end) 大学语文
  40.      ,max(case courseno when 'C002' then scores end) 新视野英语
  41.      ,max(case courseno when 'C003' then scores end) 离散数学
  42.      ,max(case courseno when 'C004' then scores end) 概率论与数理统计
  43.      ,max(case courseno when 'C005' then scores end) 线性代数
  44.      ,max(case courseno when 'C006' then scores end) 高等数学
  46. from  student s
  47. left join score c 
  48. on s.stuid=c.stuid
  49. group by s.stuid,s.stunm;
  55. select t.stuid  as 学号
  56.       ,t.stunm  as 姓名
  57.       ,max(ifnull(c1,0))  as 大学语文
  58.       ,max(ifnull(c2,0))  as 新视野英语
  59.       ,max(ifnull(c3,0))  as 离散数学
  60.       ,max(ifnull(c4,0))  as 概率论与数理统计
  61.       ,max(ifnull(c5,0))  as 线性代数
  62.       ,max(ifnull(c6,0))  as 高等数学1
  63.       ,max(ifnull(c7,0))  as 高等数学2
  64. from (
  65. select s.stuid as stuid
  66.      , s.stunm as stunm
  67.      , case when courseno ='C001' then  scores end as 'c1'
  68.      , case when courseno ='C002' then  scores end as 'c2'
  69.      , case when courseno ='C003' then  scores end as 'c3'
  70.      , case when courseno ='C004' then  scores end as 'c4'
  71.      , case when courseno ='C005' then  scores end as 'c5'
  72.      , case when courseno ='C006' then  scores end as 'c6'
  73.      , case when courseno ='C007' then  scores end as 'c7'
  75.  from      student s
  76.  left join score c 
  77.         on s.stuid=c.stuid
  79. ) as t
  80. group by t.stuid 
  81.         ,t.stunm
  82. ;
  84. --------------------------------------------------------------
  85. -- 列转行
  86. -- SELECT * FROM v_ss;
  87. select t.学号,学生姓名,'大学语文' as 科目,`大学语文` as 分数from v_ss t;
  88. select t.学号,t.姓名,'大学语文' as 科目 ,`大学语文` as 分数 from v_ss t
  90. select t.学号,学生姓名 from v_ss t;
  94. select t.学号,学生姓名,'大学语文' as 科目,`大学语文` as 分数 from v_ss t union all
  95. select t.学号,学生姓名,'新视野英语' as 科目,`新视野英语` as 分数 from v_ss t union all
  96. select t.学号,学生姓名,'离散数学' as 科目,`离散数学` as 分数 from v_ss t union all
  97. select t.学号,学生姓名,'概率论与数理统计' as 科目,`概率论与数理统计` as 分数 from v_ss t union all
  98. select t.学号,学生姓名,'线性代数' as 科目,`线性代数` as 分数 from v_ss t union all
  99. select t.学号,学生姓名,'高等数学' as 科目,`高等数学` as 分数 from v_ss t;