• mysql基础

    目录

    一、mysql的元数据库

    1.1什么是元数据库

    1.2有哪些元数据库

    1.2.1information_schema

    1.2.2mysql

    1.2.3performance_schema

    1.3切换数据库 

    二、账户管理

    2.1创建账户

    2.2查看账户

    2.3删除账户

    2.4修改密码

    2.5刷新配置

    2.6设置权限

    2.7查看权限

    2.8撤销权限

    三、mysql引擎

    3.1什么是数据库引擎

    3.2查看数据引擎

    3.3常用引擎

    3.3.1MyISAM引擎

    3.3.2memory

    3.3.3InnoDB

    3.3.4ARCHIVE

    四、数据库命令

    4.1创建数据库

    4.2查看数据库

    4.3删除数据库

    五、建表与约束

    5.1建表

    5.2建表约束

    ​编辑

    六、基本数据操作（CRUD）

    6.1.表结构与所需数据

    6.2建表

    6.3练习题以及答案

    题目：

    答案：

    七、mySql常见的函数

    7.1数字函数

    7.2字符串函数

    7.3日期函数

    7.4MySQL高级函数

    一、mysql的元数据库

    1.1什么是元数据库

    记录mysql自身数据的数据库

    1.2有哪些元数据库

    1.2.1information_schema

     信息数据库，保存mysql所维护的其他数据库信息，例如：数据库名，数据库的表，表栏的数据类型与访问权 限等

    1.2.2mysql

    核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

    1.2.3performance_schema

    用于mysql的监控数据的存放

    1.3切换数据库 

    1. use  数据库名
    2. 示例： use  mysql

    其实不使用代码可以，手动即可，如图所示（右上角直接切换或者左边选择也可以） 

    二、账户管理

    2.1创建账户

    1. 命令格式：create user 用户名 identified by ‘密码’
    2. 说明：identified by会将纯文本密码加密作为散列值存储
    3. 示例：CREATE USER test IDENTIFIED BY '123456';

    2.2查看账户

    1. 命令格式：select host,user,password from user;
    2. host列说明：% 匹配所有主机
    1.  
    2. localhost    localhost不会被解析成IP地址，直接通过UNIXsocket连接
    3. 同一主机通讯，不经过网络协议栈，不用打包拆包，计算校验和、维护序列号应答等。只是将应用层数据从一个进程拷贝到另一个进程
    127.0.0.1 会通过TCP/IP协议连接，并且只能在本机访问
    ::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

    2.3删除账户

    1. 命令格式：drop user 用户名
    2. 示例：DROP USER test;--test是上面创建的
    3. 一般不用，用时需谨慎

    2.4修改密码

    1. 命令格式：set password for 用户名=password('新密码')
    2. 示例：SET PASSWORD FOR test=PASSWORD('123456');

    2.5刷新配置

    命令格式：flush privileges

    也就是光标所指处（F5）：

    2.6设置权限

    2.7查看权限

    show grants 查看当前用户（自己）权限
    show grants for dba@localhost; 查看其他 MySQL 用户权限

    2.8撤销权限

    命令格式  revoke privileges on databasename.tablename from username@'host'
    1. 示例 REVOKE UPDATE ON bookshop.t_book FROM test@'%'; 
    2.  
    3. 收回test用户对于bookshop库中t_book表的update权限（ip不限）

    三、mysql引擎

    3.1什么是数据库引擎

    数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据， 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能

    3.2查看数据引擎

    命令  show engines
    1. Support字段说明：
    2. DEFAULT的为默认的引擎
    3. 为YES表示可以使用
    4. 为NO表示不能使用

    3.3常用引擎

    3.3.1MyISAM引擎

    MYISAM强调了快速读取操作

    使用场景： 大量查询，很少修改的场景

    3.3.2memory

    所有的数据都保存在内存中，一旦服务器重启，所有Memory存储引擎的表数据会消失但是表结构会保存下来

    使用场景：由于易失性，可以用于存储在分析中产生的中间表

    3.3.3InnoDB

    后者修改快，支持事务
    使用场景：一般事务性，均使用该引擎，用途最广，如果把握不准使用何种引擎，就使用该引擎

    3.3.4ARCHIVE

    只允许插入和查询，不允许修改和删除，压缩存储，节约空间，可以实现高并发的插入，支持在自增id上建立索引

    使用场景：在日志和数据采集的时候可以使用

    四、数据库命令

    4.1创建数据库

    1. 命令格式：create database 数据库名
    2.  
    3. create database if not exists 数据库名 default charset utf8 collate utf8_general_cs;
    4.  
    5. 默认的数据库编码集：utf8
    6.  
    7. collate表示排序规则为utf8_general_ci

    4.2查看数据库

    show databases

    4.3删除数据库

    drop database 数据库名

    危险操作，先备份

    五、建表与约束

    5.1建表

    1. 命令格式：
    2. CREATE TABLE 表名称(
    3.         列名称1 数据类型 NOT NULL,
    4.         列名称2 数据类型,
    5.         列名称3 数据类型,
    6.         unique(列名称1[,列名称2,...,列名称N])
    7.     )
    8. 示例（主表）：
    9. create table t_student
    10. (
    11.    sid                  int not null comment '学号',
    12.    sname                varchar(60) not null comment '姓名',
    13.    sex                  tinyint not null default 1 comment '性别：1男， 2女',
    14.    age                  tinyint not null comment ' 年龄',
    15.    icard                varchar(18) not null comment '身份证，唯一约束',
    16.    primary key (sid),
    17.    unique key AK_Key_2 (icard)
    18. ) comment '学生信息表';

    5.2建表约束

    六、基本数据操作（CRUD）

    6.1.表结构与所需数据

    1. 一、表结构要求：
    2.  
    3. -- 1.学生表-t_student
    4. -- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别
    5.  
    6. -- 2.教师表-t_teacher
    7. -- tid 教师编号,tname 教师名称
    8.  
    9. -- 3.课程表-t_course
    10. -- cid 课程编号,cname 课程名称,tid 教师名称
    11.  
    12. -- 4.成绩表-t_score
    13. -- sid 学生编号,cid 课程编号,score 成绩
    14.  
    15.  
    16.  
    17.  
    18. 二、表数据：
    19.  
    20. -- 学生表
    21. INSERT INTO t_student VALUES(1, '赵雷' , '1990-01-01' , 1);
    22. INSERT INTO t_student VALUES(2 , '钱电' , '1990-12-21' , 1);
    23. INSERT INTO t_student VALUES(3 , '孙风' , '1990-12-20' , 1);
    24. INSERT INTO t_student VALUES(4 , '李云' , '1990-12-06' , 1);
    25. INSERT INTO t_student VALUES(5 , '周梅' , '1991-12-01' , 2);
    26. INSERT INTO t_student VALUES(6 , '吴兰' , '1992-01-01' , 2);
    27. INSERT INTO t_student VALUES(7 , '郑竹' , '1989-01-01' , 2);
    28. INSERT INTO t_student VALUES(9 , '张三' , '2017-12-20' , 2);
    29. INSERT INTO t_student VALUES(10 , '李四' , '2017-12-25' , 2);
    30. INSERT INTO t_student VALUES(11 , '李四' , '2012-06-06' , 2);
    31. INSERT INTO t_student VALUES(12 , '赵六' , '2013-06-13' , 2);
    32. INSERT INTO t_student VALUES(13 , '孙七' , '2014-06-01' , 2);
    33.  
    34. -- 教师表
    35. INSERT INTO t_teacher VALUES(1 , '张五哥');
    36. INSERT INTO t_teacher VALUES(2 , '李卫');
    37. INSERT INTO t_teacher VALUES(3 , '年羹尧');
    38.  
    39. -- 课程表
    40. INSERT INTO t_course VALUES(1 , '语文' , 2);
    41. INSERT INTO t_course VALUES(2 , '数学' , 1);
    42. INSERT INTO t_course VALUES(3 , '英语' , 3);
    43.  
    44. -- 成绩表
    45. INSERT INTO t_score VALUES(1 , 1 , 80);
    46. INSERT INTO t_score VALUES(1 , 2 , 90);
    47. INSERT INTO t_score VALUES(1 , 3 , 99);
    48. INSERT INTO t_score VALUES(2 , 1 , 70);
    49. INSERT INTO t_score VALUES(2 , 2 , 60);
    50. INSERT INTO t_score VALUES(2 , 3 , 80);
    51. INSERT INTO t_score VALUES(3 , 1 , 80);
    52. INSERT INTO t_score VALUES(3 , 2 , 80);
    53. INSERT INTO t_score VALUES(3 , 3 , 80);
    54. INSERT INTO t_score VALUES(4 , 1 , 50);
    55. INSERT INTO t_score VALUES(4 , 2 , 30);
    56. INSERT INTO t_score VALUES(4 , 3 , 20);
    57. INSERT INTO t_score VALUES(5 , 1 , 76);
    58. INSERT INTO t_score VALUES(5 , 2 , 87);
    59. INSERT INTO t_score VALUES(6 , 1 , 31);
    60. INSERT INTO t_score VALUES(6 , 3 , 34);
    61. INSERT INTO t_score VALUES(7 , 2 , 89);
    62. INSERT INTO t_score VALUES(7 , 3 , 98);

    6.2建表

    1. -- 测试库建库脚本
    2. DROP TABLE IF EXISTS t_student;
    3. CREATE TABLE t_student 
    4. (
    5. 	sid INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    6. 	sname VARCHAR(40) NOT NULL COMMENT '名称',
    7. 	birthday DATE NOT NULL COMMENT '年龄',
    8. 	ssex TINYINT NOT NULL DEFAULT 1 COMMENT '1男，2女',
    9. 	PRIMARY KEY (sid)
    10. );
    11.  
    12. DROP TABLE IF EXISTS t_teacher;
    13. CREATE TABLE t_teacher 
    14. (
    15. 	tid INT NOT NULL AUTO_INCREMENT COMMENT '教师ID',
    16. 	tname VARCHAR(40) NOT NULL COMMENT '教师名称',
    17. 	PRIMARY KEY (tid)
    18. );
    19.  
    20. DROP TABLE IF EXISTS t_course;
    21. CREATE TABLE t_course 
    22. (
    23. 	cid INT NOT NULL COMMENT '课程ID',
    24. 	cname VARCHAR(50) COMMENT '课+        程名称',
    25. 	tid INT COMMENT '教师id',
    26. 	PRIMARY KEY (cid)
    27. );
    28.  
    29. ALTER TABLE t_course ADD CONSTRAINT fk_ref_cou_tea_tid FOREIGN KEY (tid) REFERENCES t_teacher (tid) ON DELETE RESTRICT ON UPDATE RESTRICT;
    30.  
    31. DROP TABLE IF EXISTS t_score;
    32. CREATE TABLE t_score 
    33. (
    34. 	sid INT NOT NULL COMMENT '学号，外键',
    35. 	cid INT NOT NULL COMMENT '课程id',
    36. 	score decimal(5,2) COMMENT '成绩',
    37. 	UNIQUE KEY ak_key_sid_cid (sid, cid)
    38. );
    39.  
    40. ALTER TABLE t_score ADD CONSTRAINT fk_ref_sco_stu_sid FOREIGN KEY (sid) REFERENCES t_student (sid) ON DELETE RESTRICT ON UPDATE RESTRICT;
    41. ALTER TABLE t_score ADD CONSTRAINT fk_ref_sco_tea_cid FOREIGN KEY (cid) REFERENCES t_course (cid) ON DELETE RESTRICT ON UPDATE RESTRICT;
    42.

    6.3练习题以及答案

    题目：

    01）查询" 1 "课程比" 2 "课程成绩高的学生的信息及课程分数

    1.  SELECT stu.sid,stu.sname,stu.ssex,c1.cid, c1.score, c2.cid, c2.score
    2.    FROM t_student stu
    3.         INNER JOIN  (SELECT t1.sid, t1.cid, t1.score FROM t_score t1 WHERE t1.cid = 1 ) c1 ON stu.sid = c1.sid
    4.         INNER JOIN  (SELECT t2.sid, t2.cid, t2.score FROM t_score t2 WHERE t2.cid = 2) c2 ON stu.sid = c2.sid 
    5.  WHERE c1.score > c2.score


    02）查询同时存在" 01 "课程和" 02 "课程的情况


    03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
    04）查询不存在" 01 "课程但存在" 02 "课程的情况
    05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    06）查询在t_score表存在成绩的学生信息
    07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
    08）查询「李」姓老师的数量
    09）查询学过「张三」老师授课的同学的信息
    10）查询没有学全所有课程的同学的信息
    11）查询没学过"张三"老师讲授的任一门课程的学生姓名
    12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
    13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
    14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    15）查询各科成绩最高分、最低分和平均分：
    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

    答案：

    1. -- 01）查询" 1 "课程比" 2 "课程成绩高的学生的信息及课程分数
    2. SELECT stu.sid,stu.sname,stu.ssex,stu.birthday,c2.score s2 ,c1.score s1
    3. FROM t_student stu 
    4. INNER JOIN
    5. (SELECT sid,score FROM t_score WHERE cid = 1) c1 ON stu.sid = c1.sid
    6. INNER JOIN
    7. (SELECT sid,score FROM t_score WHERE cid = 2) c2 ON stu.sid = c2.sid
    8. WHERE c1.score > c2.score;
    9.  
    10. -- 02）查询同时选修" 1 "课程和" 2 "课程的学生信息
    11. -- 方法一
    12. SELECT  stu.sid,stu.ssex,stu.sname,c2.score s2 ,c1.score s1
    13. FROM t_student stu
    14. INNER JOIN
    15. (SELECT sid.cid FROM t_score WHERE cid = 1) c1 ON stu.sid = c1.sid
    16. INNER JOIN
    17. (SELECT sid.cid FROM t_score WHERE cid = 2) c2 ON stu.sid = c2.sid
    18. -- 方法二
    19. -- 通过stu.sid 与 成绩表中的sid相连,查他们的课程id.....
    20. SELECT * FROM t_student stu 
    21. WHERE stu.sid IN (SELECT s1.sid FROM t_score s1 WHERE s1.cid = 1)
    22. AND   stu.sid IN (SELECT s2.sid FROM t_score s2 WHERE s2.cid = 2)
    23. -- 方法三
    24. SELECT stu.sid,stu.sname,stu.ssex,stu.birthday,tmp.s1num,tmp.s2num FROM t_student stu 
    25. INNER JOIN 
    26. (SELECT s.`sid`,
    27. SUM(CASE WHEN s.`cid`=1 THEN s.`score` ELSE 0 END)s1num,
    28. SUM(CASE WHEN s.`cid`=2 THEN s.`score` ELSE 0 END)s2num 
    29. FROM t_score s GROUP BY s.`sid`)tmp 
    30. ON stu.`sid` = tmp.sid AND tmp.s1num>0 AND tmp.s2num>0;
    31.  
    32. -- 03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
    33. -- 方法一(not可以加到随便一个in前面)
    34. SELECT * FROM t_student stu 
    35. WHERE stu.sid IN (SELECT s1.sid FROM t_score s1 WHERE s1.cid = 1)
    36. AND   stu.sid NOT IN (SELECT s2.sid FROM t_score s2 WHERE s2.cid = 2)
    37. -- 方法二(换个等号就行了)
    38. SELECT stu.sid,stu.sname,stu.ssex,stu.birthday,tmp.s1num,tmp.s2num FROM t_student stu 
    39. INNER JOIN 
    40. (SELECT s.`sid`,
    41. SUM(CASE WHEN s.`cid`=1 THEN 1 ELSE 0 END)s1num,
    42. SUM(CASE WHEN s.`cid`=2 THEN 1 ELSE 0 END)s2num 
    43. FROM t_score s GROUP BY s.`sid`)tmp 
    44. ON stu.`sid` = tmp.sid AND tmp.s1num=0 AND tmp.s2num>0;
    45. -- 方法三(换个等号就行了)
    46. SELECT stu.sid,stu.sname,stu.ssex,stu.birthday,tmp.s1num,tmp.s2num FROM t_student stu 
    47. INNER JOIN 
    48. (SELECT s.`sid`,
    49. SUM(CASE WHEN s.`cid`=1 THEN s.`score` ELSE -1 END)s1num,
    50. SUM(CASE WHEN s.`cid`=2 THEN s.`score` ELSE -1 END)s2num 
    51. FROM t_score s GROUP BY s.`sid`)tmp 
    52. ON stu.`sid` = tmp.sid AND tmp.s1num>=0 AND tmp.s2num<0;
    53.  
    54. -- 04）查询不存在" 01 "课程但存在" 02 "课程的情况
    55. -- 方法一 
    56. SELECT * FROM t_student stu 
    57. WHERE stu.sid NOT IN (SELECT s1.sid FROM t_score s1 WHERE s1.cid = 1)
    58. AND   stu.sid IN (SELECT s2.sid FROM t_score s2 WHERE s2.cid = 2)
    59. -- 方法二(换个等号就行了)
    60. SELECT stu.sid,stu.sname,stu.ssex,stu.birthday,tmp.s1num,tmp.s2num FROM t_student stu 
    61. INNER JOIN 
    62. (SELECT s.`sid`,
    63. SUM(CASE WHEN s.`cid`=1 THEN 1 ELSE 0 END)s1num,
    64. SUM(CASE WHEN s.`cid`=2 THEN 1 ELSE 0 END)s2num 
    65. FROM t_score s GROUP BY s.`sid`)tmp 
    66. ON stu.`sid` = tmp.sid AND tmp.s1num>0 AND tmp.s2num=0;
    67. -- 方法三(换个等号就行了)
    68. SELECT stu.sid,stu.sname,stu.ssex,stu.birthday,tmp.s1num,tmp.s2num FROM t_student stu 
    69. INNER JOIN 
    70. (SELECT s.`sid`,
    71. SUM(CASE WHEN s.`cid`=1 THEN s.`score` ELSE -1 END)s1num,
    72. SUM(CASE WHEN s.`cid`=2 THEN s.`score` ELSE -1 END)s2num 
    73. FROM t_score s GROUP BY s.`sid`)tmp 
    74. ON stu.`sid` = tmp.sid AND tmp.s1num<0 AND tmp.s2num>=0; 
    75. -- 05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    76. SELECT stu.`sid`,stu.`sname`,tmp.avgscore FROM t_student stu
    77. INNER JOIN (SELECT sid,ROUND(AVG(score),1)avgscore FROM t_score GROUP BY sid HAVING avgscore>=60)tmp
    78. ON stu.`sid` = tmp.sid
    79.  
    80. -- 06）查询在t_score表存在成绩的学生信息数量(存在可以用in)
    81. -- 方法一
    82. SELECT COUNT(*) 
    83. FROM t_student stu 
    84. WHERE stu.`sid` 
    85. IN (SELECT s.sid FROM t_score s);
    86. -- 方法二
    87. SELECT COUNT(*)
    88. FROM t_student stu
    89. WHERE EXISTS(SELECT * FROM t_score s WHERE s.`sid`=stu.sid);
    90.  
    91. -- 07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
    92. -- (没成绩的显示为 null )
    93. -- 多表可以使用左关联
    94. -- 方法一
    95. SELECT stu.`sid`,stu.`sname`,stu.`ssex`,stu.`birthday`,s1.courses,s2.total
    96. FROM t_student stu
    97. LEFT JOIN(SELECT sid,COUNT(*)courses 
    98. FROM t_score 
    99. GROUP BY sid)s1 
    100. ON stu.`sid`=s1.sid 
    101. LEFT JOIN(SELECT sid,SUM(score)total
    102. FROM t_score 
    103. GROUP BY sid)s2
    104. ON stu.`sid` = s2.sid
    105. -- 方法二
    106. SELECT stu.sid,stu.sname, stu.ssex, tmp.courses, tmp.totalScore
    107.    FROM t_student stu 
    108.    LEFT JOIN  (
    109.        SELECT sid, COUNT(*) courses, SUM(score) totalScore FROM t_score GROUP BY sid
    110.    ) tmp ON stu.`sid` = tmp.sid;
    111.  
    112. -- 08）查询「李」姓老师的数量
    113. SELECT COUNT(*) FROM t_teacher WHERE tname LIKE '李%';
    114.  
    115. -- 09）查询学过「张三」老师授课的同学的信息
    116. -- 三表通过sid,cid,tid三表的连接才能得到信息
    117. -- sid是学生表和成绩表才有，cid是成绩表和课程表才有
    118. -- tid是课程表和教师表才有
    119. SELECT * FROM t_student stu
    120. INNER JOIN t_score s1 
    121. ON stu.`sid` = s1.sid
    122. INNER JOIN t_course c1 
    123. ON s1.`cid`= c1.`cid`
    124. INNER JOIN t_teacher t1
    125. ON c1.`tid`= t1.`tid`
    126. WHERE t1.`tname` = '张三';
    127.  
    128. -- 10）查询没有学全所有课程的同学的信息
    129. SELECT t1.`sid`,t1.`sname`,t1.`ssex`, t2.courses 
    130. FROM t_student t1 
    131. LEFT JOIN (SELECT sid,COUNT(*) courses 
    132. FROM t_score 
    133. GROUP BY sid) t2 
    134. ON t1.sid = t2.sid
    135. WHERE t2.courses < (SELECT COUNT(*) FROM t_course) OR t2.courses IS NULL;
    136.  
    137. -- 11）查询没学过"张三"老师讲授的任一门课程的学生姓名
    138.  SELECT t1.sid, t1.sname,t4.tname
    139.     FROM t_student t1 
    140.          LEFT JOIN t_score t2 ON t1.sid=t2.sid 
    141.          LEFT JOIN t_course t3 ON t2.cid=t3.cid
    142.          LEFT JOIN t_teacher t4 ON t3.tid = t4.tid
    143.     WHERE t4.tname != '李卫' OR t4.tname IS NULL;
    144.  
    145. -- 12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
    146. SELECT t3.sid, t3.sname, ROUND(AVG(t1.score), 2) scoreavg
    147.   FROM t_score t1
    148.        INNER JOIN (
    149.           -- 统计两门以上不及格的学生id
    150.           SELECT t.sid, COUNT(*) num FROM (SELECT sid, cid FROM t_score WHERE score < 60) t GROUP BY t.sid HAVING num >= 2
    151.        ) t2 ON t1.sid = t2.sid 
    152.        INNER JOIN t_student t3 ON t1.sid = t3.sid
    153.  GROUP BY t3.sid, t3.sname
    154.  
    155. -- 13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
    156. SELECT stu.`sid`,stu.`sname`,t3.score
    157.   FROM t_student stu 
    158.        INNER JOIN (SELECT sid,score FROM t_score WHERE cid = 1 AND score < 60) t3
    159.        ON stu.`sid` = t3.sid
    160.   ORDER BY stu.score DESC;
    161.  
    162. -- 14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    163. SELECT 	t2.sid,t2.sname ,
    164. 	SUM(CASE WHEN t1.cid = 1 THEN t1.score ELSE 0 END) '语文',
    165. 	SUM(CASE WHEN t1.cid = 2 THEN t1.score ELSE 0 END) '数学',
    166. 	SUM(CASE WHEN t1.cid = 3 THEN t1.score ELSE 0 END) '英语',
    167. 	ROUND(AVG(t1.`score`), 2) avgscore
    168. FROM t_score t1 
    169.      INNER JOIN t_student t2 ON t1.sid = t2.sid
    170.  GROUP BY t2.sid, t2.sname
    171.  ORDER BY avgscore DESC;
    172.  
    173. -- 15）查询各科成绩最高分、最低分和平均分：
    174. -- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，
    175. -- 及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，
    176. -- 优良为：80-90，优秀为：>=90
    177. -- 要求输出课程号和选修人数，查询结果按人数降序排列，
    178. -- 若人数相同，按课程号升序排列
    179. SELECT t2.cid '课程ID', 
    180.        t2.cname '课程名称',
    181.        MAX(t1.score) '最高分',
    182.        MIN(t1.score) '最低分',
    183.        ROUND(AVG(t1.score), 2) '平均分',
    184.        COUNT(t1.sid) '选修人数',
    185.        ROUND(SUM(CASE WHEN t1.score >= 60 THEN 1 ELSE 0 END) / COUNT(t1.sid), 2) '及格率',
    186.        ROUND(SUM(CASE WHEN t1.score >=70 AND t1.score < 80 THEN 1 ELSE 0 END)/COUNT(t1.sid),2) '中等率',
    187.        ROUND(SUM(CASE WHEN t1.score >=80 AND t1.score < 90 THEN 1 ELSE 0 END)/COUNT(t1.sid),2) '优良率',
    188.        ROUND(SUM(CASE WHEN t1.score >= 90 THEN 1 ELSE 0 END)/COUNT(t1.sid), 2) '优秀率'
    189.  FROM t_score t1
    190.       INNER JOIN t_course t2 ON t1.cid = t2.cid
    191.   GROUP BY t2.cid, t2.cname
    192.   ORDER BY COUNT(t1.sid) DESC, t2.cid ASC;

    七、mySql常见的函数

    7.1数字函数

    1、ABS(x) 返回x的绝对值

    1. SELECT ABS(-1);
    2. ---- 返回1

    2、AVG(price) 返回一个表达式的平均值，price是一个字段

    SELECT AVG(age) FROM student;

       3、CEIL(x)/CEILING(x) 返回大于或等于 x 的最小整数（向下取整）

    1. SELECT CEIL(1.5);
    2. SELECT CEILING(1.5);   
    3. ---- 返回2

     4、FLOOR(x) 返回小于或等于 x 的最大整数（向下取整）

    1. SELECT FLOOR(1.5); 
    2. ---- 返回1

    5、EXP(x) 返回 e 的 x 次方

    1. SELECT EXP(3);
    2. ---- 计算 e 的三次方,返回20.085536923188

    6、GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值

    1. SELECT GREATEST(3, 12, 34, 8, 25);
    2. ---- 返回以下数字列表中的最大值34

    7、LEAST(expr1, expr2, expr3, …) 返回列表中的最小值

    1. SELECT LEAST(3, 12, 34, 8, 25);
    2. ---- 返回以下数字列表中的最小值3

    8、LN 返回数字的自然对数

    1. SELECT LN(2);
    2. ---- 返回 2 的自然对数：0.6931471805599453

    9、LOG(x) 返回自然对数(以 e 为底的对数)

    1. SELECT LOG(20.085536923188);
    2. ---- 返回 3

    10、MAX(expression)返回字段 expression 中的最大值

    1. SELECT MAX(age) AS maxAge FROM Student;
    2. ---- age最大值

    11、MIN(expression)返回字段 expression 中的最大

    1. SELECT MIN(age) AS minAge FROM Student;
    2. ---- age最小值

    12、POW(x,y)/POWER(x,y)返回 x 的 y 次方

    1. SELECT POW(2,3);
    2. SELECT POWER(2,3);
    3. ---- 返回2 的 3 次方：8

    13、RAND()返回 0 到 1 的随机数

    1. SELECT RAND();
    2. ---- 返回 0 到 1 的随机数，若()里面有数字,RAND(x),x相同时，返回值相同

    14、ROUND(x)返回离 x 最近的整数（四舍五入）

    1. SELECT ROUND(1.23456);
    2. ---- 返回 1
    3.  
    4. SELECT ROUND（(scroe),2）;
    5. ---- 返回 表示返回的数字只能保留两个小数

    15、SIGN(x)返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1

    1. SELECT SIGN(-10);
    2. ---- 返回 -1

    16、SQRT(x)返回x的平方根

    1. SELECT SQRT(25);
    2. ---- 返回5

    17、SUM(expression)返回指定字段的总和

    1. SELECT SUM(age) AS totalAage FROM Student;
    2. ---- 返回age的总和

    18、TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）

    1. SELECT TRUNCATE(1.23456,3);
    2. ---- 返回1.234

    7.2字符串函数

    1、返回字符串 s 的第一个字符的 ASCII 码

    1. SELECT ASCII('AB');
    2. ---- 返回A的ASCII码值：65

    2、LENGTH/CHAR_LENGTH(s)/CHARACTER_LENGTH(s)返回字符串 s 的字符数

    1. SELECT LENGTH('1234');
    2. ---- 返回4

    3、CONCAT(s1,s2…sn)字符串 s1,s2 等多个字符串合并为一个字符串

    1. SELECT CONCAT('hel','llo');
    2. ---- 返回hello

    4、FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置

    1. SELECT FIND_IN_SET("c", "a,b,c,d,e");
    2. ---- 返回3

    5、FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入

    1. SELECT FORMAT(250500.5634, 2); 
    2. ---- 返回250,500.56

    6、INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

    1. SELECT INSERT("google.com", 1, 6, "runnob");
    2. ---- 返回runoob.com

    7、LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置

    1. SELECT LOCATE('st','myteststring');
    2. ---- 返回5

    8、LCASE(s)/LOWER(s)将字符串 s 的所有字母变成小写字母

    1. SELECT LOWER('RUNOOB');
    2. ---- 返回runoob

    9、UCASE(s)/UPPER(s)将字符串 s 的所有字母变成大写字母

    1. SELECT UCASE('runoob');
    2. ---- 返回RUNOOB

    10、TRIM(s)去掉字符串 s 开始和结尾处的空格

    1. SELECT TRIM('    RUNOOB    ');
    2. ---- 返回RUNOOB

    11、LTRIM(s)去掉字符串 s 开始处的空格

    1. SELECT LTRIM('    RUNOOB    ');
    2. ---- 返回 ’RUNOOB   ‘

    12、RTRIM(s)去掉字符串 s 结尾处的空格

    1. SELECT RTRIM('    RUNOOB    ');
    2. ---- 返回 ’    RUNOOB‘

    13、SUBSTR(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串

    1. SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString;
    2. ---- 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符，返回UNO

    14、SUBSTR/SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串

    1. SELECT SUBSTR/SUBSTRING("RUNOOB", 2, 3);
    2. ---- 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符，返回UNO

    15、POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置

    1. SELECT POSITION('b' in 'abc');
    2. ---- 返回2

    16、REPEAT(s,n)将字符串 s 重复 n 次

    1. SELECT REPEAT('runoob',3);
    2. ---- 返回runoobrunoobrunoob

    17、REVERSE(s)将字符串s的顺序反过来

    1. SELECT REVERSE('abc');
    2. ---- 返回cba

    18、STRCMP(s1,s2)比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1 

    1. SELECT STRCMP("runoob", "runoob");
    2. ---- 返回0

    7.3日期函数

    1、CURDATE()/CURRENT_DATE()返回当前日期

    1. SELECT CURDATE();
    2. SELECT CURRENT_DATE();
    3. ---- 返回2019-02-19

    2、CURRENT_TIME()/CURTIME()返回当前时间

    1. SELECT CURRENT_TIME();
    2. ---- 返回11:40:45

    3、CURRENT_TIMESTAMP()返回当前日期和时间

    1. SELECT CURRENT_TIMESTAMP();
    2. ---- 返回2019-02-19 11:41:32

    4、ADDDATE(d,n)计算起始日期 d 加上 n 天的日期

    1. SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
    2. ---- 返回2017-06-25

    5、ADDTIME(t,n)时间 t 加上 n 秒的时间

    1. SELECT ADDTIME('2011-11-11 11:11:11', 5);
    2. ---- 返回2011-11-11 11:11:16

    6、DATE()从日期或日期时间表达式中提取日期值

    1. SELECT DATE("2017-06-15 11:11:16");
    2. ---- 返回2017-06-15

    7、DAY(d)返回日期值 d 的日期部分

    1. SELECT DAY("2017-06-15"); 
    2. ---- 返回15

    8、DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数

    1. SELECT DATEDIFF('2001-01-01','2001-02-02'); 
    2. ---- 返回-32

    9、DATE_FORMAT按表达式 f的要求显示日期 d

    1. SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');
    2. ---- 返回2011-11-11 11:11:11 AM

    10、DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday

    1. SELECT DAYNAME('2011-11-11 11:11:11');
    2. ---- 返回Friday

    11、DAYOFMONTH(d)计算日期 d 是本月的第几天

    1. SELECT DAYOFMONTH('2011-11-11 11:11:11');
    2. ---- 返回11

    12、DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推

    1. SELECT DAYOFWEEK('2011-11-11 11:11:11');
    2. ---- 返回6

    13、DAYOFYEAR(d)计算日期 d 是本年的第几天

    1. SELECT DAYOFYEAR('2011-11-11 11:11:11');
    2. ---- 返回315

    14、EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值 


    type可取值为：
    MICROSECOND
    SECOND
    MINUTE
    HOUR
    DAY
    WEEK
    MONTH
    QUARTER
    YEAR
    SECOND_MICROSECOND
    MINUTE_MICROSECOND
    MINUTE_SECOND
    HOUR_MICROSECOND
    HOUR_SECOND
    HOUR_MINUTE
    DAY_MICROSECOND
    DAY_SECOND
    DAY_MINUTE
    DAY_HOUR
    YEAR_MONTH

    1. SELECT EXTRACT(MINUTE FROM '2011-12-13 14:15:16');
    2. ---- 返回15

    15、DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推

    1. SELECT DAYOFWEEK('2011-11-11 11:11:11');
    2. ---- 返回6

    16、UNIX_TIMESTAMP()得到时间戳

    1. SELECT UNIX_TIMESTAMP('2019-2-19');
    2. SELECT UNIX_TIMESTAMP(expression);
    3. ---- 返回1550505600

    17、FROM_UNIXTIME()时间戳转日期

    1. SELECT FROM_UNIXTIME(1550505600);
    2. ---- 返回2019-02-19 00:00:00
    3. SELECT FROM_UNIXTIME(1550505600, '%Y-%m-%d');
    4. ---- 返回2019-02-19

    7.4MySQL高级函数

    1、IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2

    1. SELECT IF(1>0,'yes','no');
    2. ---- 返回yes

    2、CONV(x,f1,f2)返回 f1 进制数变成 f2 进制数

    1. SELECT CONV(13,10,2);
    2. ---- 返回1101

    3、CURRENT_USER()/SESSION_USER()/SYSTEM_USER()/USER()返回当前用户
    4、DATABASE()返回当前数据库名
    5、VERSION()返回数据库的版本号
     

     

  • 相关阅读:
    【数据结构与算法】栈与队列相关算法的实现
    优思学院｜新版ISO9001:2015质量体系的优势（一）高阶结构
    6.DesignForPlacement\ALIGN_SYM...
    南开大学计算机考研资料汇总
    Databend 开源周报第 108 期
    prim生成树
    09 更真实的云原生：Kubeadm实际搭建多节点的Kubernetes集群
    “加密上海·喜玛拉雅Web3.0数字艺术大展”落幕，AIGC和数字艺术衍生品是最大赢家？...
    Day 2 Abp框架下，MySQL数据迁移时，添加表和字段注释
    做大模型产品，如何设计prompt？
  • 原文地址：https://blog.csdn.net/qq_62881798/article/details/126060111