SQL day 13

-- 查询 Java程序设计-1 课程排名前十的学生，并且分数大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s 
INNER JOIN result AS r 
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub 
ON r.subjectno = sub.subjectno
WHERE subjectname = 'Java程序设计-1' AND studentresult > 80  -- WHERE 条件在order by 前面
ORDER BY studentresult DESC
LIMIT 0,10

子查询

本质：在where语句中嵌套一个子查询语句

-- 查询 数据库结构-1 的所有考试结果 （学号，科目编号，成绩），降序排列
-- 方式一
SELECT subjectno , r.subjectno , studentresult
FROM result AS r 
INNER JOIN `subject` AS sub 
ON r.studentno=sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC 

-- 方式二 （子查询）由里到外
SELECT subjectno , r.subjectno , studentresult
FROM result
WHERE subjectno =(
        SELECT subjectno FROM `subject`
        WHERE subjectname ='数据库结构-1'
)        
ORDER BY studentresult DESC 

MySQL函数

常用函数

-- 数学运算
SELECT ABS(-3) -- 取绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND()-- 返回0到1的随机数
SELECT SIGN(-2) -- 判断一个属的符号

-- 字符串
SELECT CHAR_LENGTH('ssss') -- 返回字符串长度
SELECT CONCAT('s','f','g') -- 拼接字符串
SELECT INSERT ('hello',1,2,'cv') -- 插入替换  结果为cvllo
SELECT LOWER('HEloH') -- 转小写
SELECT UPPER(str)('HEloH') -- 转大写
SELECT INSTR('hello','e')-- 返回第一次出现字串的位置
SELECT REPLACE( 'hello','he','eh') -- 替换出现的指定字符串 结果为 ehllo
SELECT REVERSE('hello') -- 反转字符串

-- 时间和日期函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 获取本地时间

聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()
AVG()
MAX()
MIN()

SELECT COUNT(studentname) FROM student -- count（字段）忽略null值
SELECT COUNT (*) FROM student -- 不会忽略null值
SELECT COUNT (1) FROM student -- 不会忽略null值

SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;

-- 查询不同课程的平均分，最高分，最低分 且平均分大于80
SELECT subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM result AS r 
INNER JOIN `subject` AS sub 
ON r.subjectno=sub.subjectno
GROUP BY r.subjectno -- （通过什么字段来分组
HAVING AVG(studentresult)>80 -- 过滤分组的条件 （不能用where）

数据库级别的MD5加密（拓展）

MD5 主要增强算法复杂度和不可逆性

MD5不可逆，具体的值的MD5是一样的

CREATE TABLE testmd5(
  id INT(4) NOT NULL ,
  name VARCHAR (20) NOT NULL ,
  pwd VARCHAR (50) NOT NULL,
  PRIMARY KEY (id)
)ENGINE =INNODB DEFAULT CHARSET = utf8

-- 明文密码
INSERT INTO testmd5 VALUES (1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- 加密
UPDATE testmd5 SET pwd =MD5(pwd) WHERE id =1

-- 插入的时候加密
INSERT INTO tsetmd5 VALUES (4,'xiaoming',MD5('123456'))

SELECT总结

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条