mysql02

-- 聚合函数
-- 查询表中一共有多少条记录
select count(*) from sudent

-- 查询表中字段不为空的行
1.不为空
select * from table where id <> "";
select * from table where id != "";
2.为空
select * from table where id ="";
select * from table where isNull(id);

-- 查询电话号码为空的行 is null
SELECT * FROM student
WHERE phone IS NULL
-- 查询电话号码不为空的行 is not null
SELECT * FROM student
WHERE phone IS NOT NULL
具体情况具体分析，如果字段是char或者varchar类型的，使用id=""可以的；
如果字段是int类型的，使用isNull会好些。
-- 查询年龄最大的数据
select max(age) from student
-- 查询年龄最小的数据
select min(age) from student
-- 求年龄平均值
select avg(age) from studnet
-- 求所有的年龄之和
select sum(age) from student

-- 字符串函数
-- 字符串的连接
select concat('my','s','ql');
-- 字符串的替换
select insert('大家辛苦了'，1,3)；
-- 字符串的截取
select substring ('大家辛苦了'，1,3)；
-- 字符串变大变小写
select lower('AAA')
select upper('aaa')

　　函数：

　　1、从左开始截取字符串

　　left(str, length)

　　说明：left(被截取字段，截取长度)

　　例：select left(content,200) as abstract from my_content_t

　　2、从右开始截取字符串

　　right(str, length)

　　说明：right(被截取字段，截取长度)

　　例：select right(content,200) as abstract from my_content_t

　　3、截取字符串

　　substring(str, pos)

　　substring(str, pos, length)

　　说明：substring(被截取字段，从第几位开始截取)

　　substring(被截取字段，从第几位开始截取，截取长度)

　　例：select substring(content,5) as abstract from my_content_t

　　select substring(content,5,200) as abstract from my_content_t

　　(注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度)

　　4、按关键字截取字符串

　　substring_index(str,delim,count)

　　说明：substring_index(被截取字段，关键字，关键字出现的次数)

　　例：select substring_index(”blog.chinabyte.com”，”。”，2) as abstract from my_content_t

　　结果：blog.chinabyte

　　(注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束)

　　结果：chinabyte.com

常用的时间和日期函数
--01 获取当前日期
select curdate();
--02 获取当前时间
select curtime();
--03 获取当前时间和日期
select now();
--04 返回一年中的第几周
select week(now());
--05 只获取年
select year(now());
--06 只获取小时
select hour(now());
--07 获取两个日期差
select datediff(now(),2015-08-08);
--08 返回现在的时间加上某个时间后的日期
select adddate(now(),30);

-- 数学函数
select cell(3.1) ---天花板函数
select floor(3.99)
select rand()---0-1随机数

 

-- 把所有的成绩降低10%后加5分，再查询及格成绩
-- 并且按照成绩 降序排列
SELECT studentNo AS '学号',(studentResult*0.9+5) AS '成绩'
FROM result
WHERE (studentResult*0.9+5)>=60
ORDER BY '成绩' DESC

-- 查询所有年级编号为1 的学生信息，并且按照 学号升序排列
-- 01. 显示前4条记录
SELECT * FROM student
WHERE gradeid=1
ORDER BY studentno
LIMIT 4
-- 02. 每页显示4条 显示第2页的内容
SELECT * FROM student
WHERE gradeid=1
ORDER BY studentno
LIMIT 4,4
-- 需要注意的是：第一条记录的是 0！
-- limit a,b
-- a:从那一条记录开始
-- b:每页显示多少条数据

-- 将学生表中学号为20000的学生邮箱改成stu@20000.163.com
-- 密码改成000
UPDATE student SET email='stu@20000.163.com',loginpwd='000'
WHERE studentNo=20000

-- 将科目表中课时数大于200的并且年级编号是1的课时减少10！
UPDATE `subject` SET classHour=classHour-10
WHERE classHour>=200 AND gradeid=1

-- 将所有年级编号是1的学生姓名，性别，出生日期，手机号码
-- 都保存到一个新的表中 student_new
CREATE TABLE IF NOT EXISTS student_new(
SELECT studentName,sex,bornDate,phone FROM student
WHERE gradeid=1
)

-- 01.查询2016年2月17日考试 前5名的学员的学号和分数
SELECT studentNo 学号,studentResult 分数 FROM result
WHERE examDate='2016-02-17'
ORDER BY 分数 DESC
LIMIT 5

-- 02. 将所有女学生按年龄从大到小排序
-- 从第2条记录开始显示6名女学生的姓名、年龄、出生日期、手机号信息
SELECT studentName 姓名,(YEAR(NOW())-YEAR(bornDate)) 年龄,
bornDate 出生日期,phone 手机号码 FROM student
WHERE sex='女'
ORDER BY 年龄 DESC
LIMIT 1,6

-- 03.按出生年份分组统计学生人数
-- 将各组中人数达到2人的年份和人数显示出来

SELECT YEAR(bornDate) 出生年份, COUNT(studentNo) 人数
FROM student
GROUP BY 出生年份
HAVING 人数>=2

-- 04.查询参加2016年2月17日考试的所有学员的最高分、最低分、平均分。
SELECT MAX(studentResult) 最高分,MIN(studentResult) 最低分,
AVG(studentResult) 平均分 FROM result
WHERE examDate='2016-02-17'

5. select 字段1，字段2....
1. from 表名
2. where 条件1 and 条件2
3. group by 分组依据
4. having 分组条件
6. order by 排序字段
7. limit 分页

-- 查询grade和student所有的数据
SELECT * FROM student,grade
13+4
13*4
-- 笛卡尔积
-- 怎么解决这个问题？ 找出两个表中关联的字段
SELECT * FROM student,grade
WHERE student.`gradeId`=grade.`gradeId`

 

-- 查询出 年龄比 李斯文 小的学生信息
-- 01.先查询李斯文的年龄 1993-07-23
SELECT bornDate FROM student WHERE studentName='李斯文'
-- 02.把查询出来的结果当成条件继续查询
SELECT bornDate FROM student
WHERE bornDate>'1993-07-23'
-- 疑问？ 能不能把两个sql合并在一起执行
-- 我们就把 '1993-07-23'当成一个java中的变量名
-- 指向了01的sql
STRING a="521";
Stirng b=a+"125";
syso(b)
syso(a+"125")

 

-- 使用子查询来实现
-- 子查询就是一个嵌套在select,insert,update或者delete语句或者
-- 其他查询语句中的查询语句！
-- 语法： select ... from 表 where 字段 比较运算符（子查询）
SELECT bornDate FROM student
WHERE
bornDate>(SELECT bornDate FROM student WHERE studentName='李斯文')
-- 子查询和比较运算符联合使用的使用，必须保证子查询返回的值是不能多于一个！

-- 查询出 最近一次java课程 考试成绩的最高分和最低分
-- 01.时间是最大的 最高分和最低分 result
-- 02.java课程 subject

-- 分析 分解书写
-- 01.先获取课程java的编号
SELECT subjectNo 学号 FROM `subject`
WHERE
subjectName='java'

-- 02.最近java课程 的考试日期
SELECT MAX(examDate) 考试日期,MAX(studentResult) 最高分,
MIN(studentResult) 最低分 FROM result
WHERE subjectNo=1

-- 03.最近的日期
SELECT MAX(examDate) FROM result

-- 使用子查询
SELECT MAX(examDate) 考试日期,MAX(studentResult) 最高分,
MIN(studentResult) 最低分 FROM result
WHERE subjectNo=(SELECT subjectNo 学号 FROM `subject` WHERE subjectName='java')
AND examDate=(SELECT MAX(examDate) FROM result) -- 日期是最近的！

-- 查询java课程考试成绩为60的学生姓名
-- 01.先找java的课程编号
SELECT subjectNo 学号 FROM `subject`
WHERE
subjectName='java'

-- 02.找java的成绩是60的
SELECT studentResult 成绩 FROM result
WHERE studentResult=60
AND subjectNo=(SELECT subjectNo 学号 FROM `subject` WHERE subjectName='java')

-- 03.找学生编号 student 怎么和 result表关联的？？？
SELECT studentNo 编号 FROM result
WHERE studentResult=60
AND subjectNo=(SELECT subjectNo 学号 FROM `subject` WHERE subjectName='java')

-- 使用IN替换=之后的子查询
-- IN后面的子查询语句可以返回多条结果
SELECT studentName 姓名 FROM student
WHERE studentNo IN
(SELECT studentNo 编号 FROM result
WHERE studentResult=95
AND subjectNo=(SELECT subjectNo 学号 FROM `subject` WHERE subjectName='java'))

-- 查询学生姓名是张秋丽的信息
SELECT * FROM student WHERE studentName='张秋丽'
-- 查询学生姓名是张秋丽 或者 肖梅 的信息
SELECT * FROM student WHERE studentName='张秋丽' OR studentName='肖梅'

-- 查询学生姓名是张秋丽 或者 肖梅 再 或者是 王宝宝 的信息
SELECT * FROM student WHERE studentName='张秋丽'
OR studentName='肖梅' OR studentName='王宝宝'

-- 使用IN替换 多个条件
SELECT * FROM student WHERE studentName IN ('张秋丽','肖梅','王宝宝')

-- 使用NOT IN
-- 查询出 没有参加最近一次java课程考试的学生姓名以及编号
SELECT studentName 姓名,studentNo 编号 FROM student
WHERE studentNo NOT IN
(
SELECT studentNo FROM result
WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='java')
AND examDate=(SELECT MAX(examDate) FROM result)
)

-- EXISTS : select ... from 表名 where exists(子查询)
-- 子查询有返回结果 exists(子查询)返回true 外层查询执行
-- 子查询没有返回结果 exists(子查询)返回false 外层查询不执行

-- 检查Java课程最近一次考试。
-- 如果有成绩达到80分以上者，则显示分数排在前5名学员的学号和分数

-- 01.查询java课程的编号
SELECT subjectNo FROM `subject`
WHERE subjectName='java'
-- 02.上面的当成一个条件 继续查询 result
SELECT * FROM result WHERE subjectNo=(SELECT subjectNo FROM `subject`
WHERE subjectName='java')
-- 拿到了java所有的考试信息
-- 03. 再次拿到最近考试的时间
SELECT MAX(examDate) FROM result
-- 04.再把03当成条件 拼接查询02
SELECT * FROM result WHERE subjectNo=(SELECT subjectNo FROM `subject`
WHERE subjectName='java')
AND examDate=(SELECT MAX(examDate) FROM result)
-- 05.如果成绩有大于80的 查询出前5名
SELECT * FROM result WHERE
studentResult>80 AND
subjectNo=(SELECT subjectNo FROM `subject`
WHERE subjectName='java')
AND examDate=(SELECT MAX(examDate) FROM result)

-- 06.整体放在exists中 并增加条件
SELECT studentNo 学号,studentResult 分数 FROM result
WHERE EXISTS
(
SELECT * FROM result WHERE
studentResult>80 AND
subjectNo=(SELECT subjectNo FROM `subject`
WHERE subjectName='java')
AND examDate=(SELECT MAX(examDate) FROM result)
)
AND subjectNo=(SELECT subjectNo FROM `subject`
WHERE subjectName='java')
ORDER BY studentResult DESC LIMIT 5;

-- 检查java课程最近一次考试成绩
-- 如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分
-- not exists

-- 01.查询java课程的编号
SELECT subjectNo FROM `subject` WHERE subjectName='java'
-- 02.查询考试成绩 01的结果是1
SELECT studentResult FROM result WHERE
subjectNo=1
-- 03. 合并 12
SELECT studentResult FROM result WHERE
subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='java')
-- 04.获取最近一次考试的时间
SELECT MAX(examDate) FROM result
-- 05. 合并 34
SELECT studentResult FROM result WHERE
subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='java')
AND examDate=(SELECT MAX(examDate) FROM result)
-- 06. 获取成绩小于60的
SELECT studentResult FROM result WHERE
subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='java')
AND examDate=(SELECT MAX(examDate) FROM result)
AND studentResult>60
-- 07.如果都没及格 06没有结果集
SELECT AVG(studentResult)+5 平均分 FROM result
WHERE NOT EXISTS
(
SELECT studentResult FROM result WHERE
subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='java')
AND examDate=(SELECT MAX(examDate) FROM result)
AND studentResult>60
)
AND subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='java')
AND examDate=(SELECT MAX(examDate) FROM result)

 

-- 如果有1年级的学生
-- 就查询参加2年级学科考试的学员学号、科目编号、考试成绩，考试时间

-- 01.查询有没有1年级的学生 student表
SELECT * FROM student WHERE gradeId=
(SELECT gradeId FROM grade WHERE gradeName='1年级')

-- 02.查询2年级下面的学科
SELECT subjectNo FROM `subject`
WHERE gradeId=(SELECT gradeId FROM grade WHERE gradeName='2年级')

-- 03.整体的结果
SELECT studentNo 学号,subjectNo 科目编号,studentResult 考试成绩,
examDate 考试时间 FROM result
WHERE EXISTS(
SELECT * FROM student WHERE gradeId=
(SELECT gradeId FROM grade WHERE gradeName='1年级')
)
AND subjectNo IN
(SELECT subjectNo FROM `subject`
WHERE gradeId=(SELECT gradeId FROM grade WHERE gradeName='2年级'))

-- 使用子查询的注意事项
-- 01.任何允许使用表达式的地方都可以使用子查询
-- 02.只出现在子查询中但是没有在父查询中出现的列，结果集中的列不能包含！

-- 为每个学生制作在校期间每门课程的成绩单
-- 要求每个学生参加每门课程的最后一次考试成绩作为该生本课程的最终成绩
-- 成绩单的数据项 学生姓名 课程所属的年级名称 课程名称 考试日期 考试成绩
SELECT studentName 学生姓名,
(SELECT gradeName FROM grade WHERE gradeId=`subject`.`gradeId`) 课程所属的年级名称,
subjectName 课程名称 , examDate 考试日期 ,studentResult 考试成绩
FROM result r1
INNER JOIN student ON r1.`studentNo`=student.`studentNo`
INNER JOIN `subject` ON r1.`subjectNo`=`subject`.`subjectNo`
WHERE r1.`examDate` IN
(SELECT MAX(examDate) FROM result r2
WHERE r1.`subjectNo`=r2.`subjectNo`
GROUP BY r2.`subjectNo`
)