mysql数据库表格数据查询select

#查询全部 列
SELECT * FROM student;
#获取学生编号，姓名地址
SELECT studentno,studentname,address FROM student;
SELECT ALL studentno,studentname,address FROM student;
#查询结果去重  distinct去除重复的行  all默认值 选取全部的行
SELECT DISTINCT studentname,address FROM student;
#区分两个表的同名字段(完全连接，连接后的记录数=连个表之间的笛卡尔积r*r
SELECT student.`gradeId`,grade.`gradeid` FROM student,grade;
SELECT result.`subjectno`,subject.`subjectNo` FROM result,`subject`;
#as字句  起的别名不会影响到原表的数据，只在虚表中使用
#列起别名
SELECT studentno AS '学号',studentname AS '姓名',email AS '邮箱' 
FROM student;
#行起别名  适用于多表连接时区分同名列
SELECT s.studentno,s.studentname,s.`bornDate` FROM student AS s;
#表达式起别名
SELECT 10+20 AS '和',20-10 AS '差',3*4 AS '积',6/2 AS '商';
#表达式  函数  列值  数值  null  操作符组成
SELECT VERSION() AS '当前版本',studentname AS '姓名',NULL AS '空值',
'abc'+'字母' FROM student;

SELECT subjectName AS '课程名称',classHour AS '总课时',
classHour/10 AS '均课时' FROM `subject`;

#查询80-90分之间的成绩信息
SELECT * FROM result WHERE studentresult >=80 AND studentresult <=90;
#between and  (包含边界)
SELECT * FROM result WHERE studentresult BETWEEN 80 AND 90;

#查询一个地址为null的学生信息
/*
1.选表 student
2.构造条件 where从句
注意：''空串不等于null
*/
SELECT * FROM student WHERE address = '';
SELECT * FROM student WHERE address IS NULL;
#查询学生信息表中姓张的同学 %是通配符 匹配任意多的字符
SELECT * FROM student WHERE studentname LIKE '张%';
#查询学生信息表中名字中包含三的同学
SELECT * FROM student WHERE studentname LIKE '%三%';
#查询学生信息表中叫张某的同学 _下划线 匹配一个字符
SELECT * FROM student WHERE studentname LIKE '张_';
#查询年级是1，2的学生信息
SELECT * FROM student WHERE gradeId=1 OR gradeId = 2;
#使用in进行模糊查询
SELECT * FROM student WHERE gradeId IN(1,2);
#查询科目1，2的考试成绩
SELECT studentresult,subjectno FROM result WHERE subjectno IN(1,2);
#查询学生姓名，学生地址，学生的年级名称
SELECT studentname,address,gradeId FROM student;
/*
只有把student和grade表连接起来，才能获取以上信息
内连接：存在主外键关系或者逻辑关联的两表适用于连接
*/
SELECT s.`studentName`,s.`address`,g.`gradeName` FROM student AS s 
INNER JOIN grade AS g ON s.`gradeId` = g.`gradeid`;

#查询科目为html的学科的考试成绩
SELECT s.`subjectName`,r.* 
FROM `subject` AS s INNER JOIN result AS r 
ON s.`subjectNo`=r.`subjectno` 
WHERE subjectName='html';
#查询s2年级的学生信息
SELECT g.`gradeName`,s.* 
FROM student AS s INNER JOIN grade AS g ON s.`gradeId`=g.`gradeid`
WHERE g.`gradeName`='s2';
#三表连接
#查询参加html，javalogin学科的学生姓名，考试科目名称，考试成绩，
成绩在60-80分
/*
subject,result,student
*/
SELECT s.`studentName`,j.`subjectName`,r.`studentresult` 
FROM student AS s INNER JOIN result AS r ON s.`studentNo`=r.`studentno`
INNER JOIN `subject` AS j ON j.`subjectNo` = r.`subjectno`
WHERE r.`studentresult` BETWEEN 60 AND 80 
AND subjectname IN ('html','java login');

#查询考试科目名称，考试成绩 左连接
SELECT DISTINCT j.`subjectName`,r.`studentresult` 
FROM `subject` AS j LEFT JOIN result AS r 
ON j.`subjectNo` = r.`subjectno`;
#改成右连接 保证右表完全显示，左表没有的数据用null补全
SELECT DISTINCT r.`studentresult` ,j.`subjectName`
FROM result AS r RIGHT JOIN `subject` AS j
ON j.`subjectNo` = r.`subjectno`;
#改成等值连接
SELECT DISTINCT r.`studentresult` ,j.`subjectName`
FROM result AS r, `subject` AS j
WHERE j.`subjectNo` = r.`subjectno`;
#三张表等值连接
SELECT s.`studentName`,j.`subjectName`,r.`studentresult` FROM 
student AS s, result AS r ,`subject` AS j
WHERE  s.`studentNo`=r.`studentno`
AND j.`subjectNo` = r.`subjectno`
AND r.`studentresult` BETWEEN 60 AND 80 
AND subjectname IN ('html','java login');

#自连接
USE school;
SELECT * FROM grade AS g1
INNER JOIN grade AS g2
ON g1.`gradeid`=g2.`gradeid`;
CREATE TABLE IF NOT EXISTS category(
    categoryId INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    pid INT(10) NOT NULL,
    categoryName VARCHAR(32) NOT NULL,
    PRIMARY KEY(categoryId)
);
INSERT INTO category VALUES
(2,1,'美术设计'),
(3,1,'软件开发'),
(4,3,'数据库基础'),
(5,2,'photoshop基础'),
(6,2,'色彩搭配学'),
(7,3,'PHP基础'),
(8,3,'一起学JAVA');
SELECT * FROM category;
SELECT p.`categoryName`,c.`categoryName` 
FROM category AS c, category AS p
WHERE p.`categoryId` = c.`pid`;

#排序 order by 列名 排序方式 （升序asc  降序desc）
USE school;
SELECT * FROM student;
#按照生日从小到大进行学生信息排序
SELECT * FROM student ORDER BY bornDate DESC;
#查询考试成绩并按照由高到低排序
SELECT * FROM result ORDER BY studentresult DESC;
#参加科目号是1的学科的考试信息，并且按照分数由高到低排序
SELECT * FROM result WHERE subjectno = 1 ORDER BY studentresult DESC;
#参加Java login考试的信息，并且按照分数由高到低排序
SELECT * FROM result AS r , SUBJECT AS j
WHERE r.`subjectno` = j.`subjectNo`
AND subjectname = 'java login'
ORDER BY studentresult DESC;
#参加Java login考试的信息，找出前五名 limit 偏移量可不写,记录数
SELECT * FROM result AS r , SUBJECT AS j
WHERE r.`subjectno` = j.`subjectNo`
AND subjectname = 'java login'
ORDER BY studentresult DESC
LIMIT 0,5;
/*
利用limit进行分页
假设，每页显示3条数据
0,3 第一页（当前页-1）*每页记录数
3，3第二页
6，3第三页
*/
SELECT * FROM result AS r , SUBJECT AS j
WHERE r.`subjectno` = j.`subjectNo`
AND subjectname = 'java login'
ORDER BY studentresult DESC
LIMIT 6,3;
#查询第一学年课程成绩前10且分数大于80的学生信息
SELECT s.`studentNo`,s.`studentName`,j.`subjectName`,r.`studentresult` 
FROM student AS s,SUBJECT AS j,result AS r
WHERE s.`studentNo`=r.`studentno`
AND r.`subjectno` = j.`subjectNo`
AND r.`studentresult`>80
AND j.`gradeId` = 1
ORDER BY studentresult DESC
LIMIT 10;
#子查询
#查询课程为java login且分数不小于80分的学生学号和姓名
#1.表连接查询
SELECT s.`studentNo`,s.`studentName` 
FROM student AS s,SUBJECT AS j, result AS r
WHERE s.`studentNo` = r.`studentno` 
AND r.`subjectno` = j.`subjectNo`
AND subjectname = 'java login'
AND studentresult >=80;
#2.使用子查询
SELECT s.`studentNo`,s.`studentName` FROM student AS s,result AS r
WHERE s.`studentNo` = r.`studentno`
AND r.`subjectno` = (SELECT subjectno FROM `subject`
WHERE subjectname = 'java login')  #javalogin 的课程编号
AND r.`studentresult`>=80;        
#查询java login的前5名学生的成绩信息
SELECT s.`studentNo`,s.`studentName`,r.`studentresult` 
FROM student AS s,result AS r
WHERE s.`studentNo` = r.`studentno`
AND r.`subjectno` = (SELECT subjectno FROM `subject`
WHERE subjectname = 'java login')
ORDER BY  studentresult DESC
LIMIT 5;
#查看年龄比赵云小的学生，显示学生信息
SELECT * FROM student 
WHERE bornDate > (SELECT bornDate 
FROM student WHERE studentname = '赵云')
ORDER BY borndate ASC;
#查询java login课程至少一次考试恰好等于90分的学生
SELECT * FROM student AS s
WHERE studentno IN (SELECT studentno FROM result
WHERE studentresult = 90 AND (SELECT subjectno FROM `subject`
WHERE subjectname = 'java login'));

#常用聚合函数：将多条记录聚合成一个结果的函数
#统计数量count()   平均值avg()  max()  min()  sum()  求和
SELECT * FROM student;
SELECT COUNT(*) AS '学生人数' FROM student;
#统计学生年龄大于1990年的学生人数
SELECT COUNT(*) AS '学生人数' FROM student WHERE bornDate > '1990-1-1';
#查看学科1考试成绩的平均分，最高分，最低分，总分
SELECT SUM(studentresult) AS '总分', AVG(studentresult) AS '平均分',
MAX(studentresult) AS '最高分',MIN(studentresult) AS '最低分'
FROM result WHERE subjectno = 1;
#注意：聚合函数不能和普通的列一起使用，可以和分组的列一起使用
SELECT COUNT(*) AS '学生人数',studentname FROM student;

#统计学生表中的男女人数
SELECT COUNT(*),sex FROM student
GROUP BY sex;
#分组后的筛选 having
#统计学生表中大于10的男女人数
SELECT COUNT(*) AS '人数',sex FROM student
GROUP BY sex
HAVING COUNT(*)>10;
#where和having的使用
#查询年级大于1的男女生人数，获取人数大于10的记录
SELECT COUNT(*),sex FROM student
WHERE gradeId>1 GROUP BY sex
HAVING COUNT(*)>5;
#按照不同的课程分组，分组算出其平均分，最高分和最低分，60分以下不显示
SELECT s.`subjectName` AS '课程名',
AVG(r.`studentresult`) AS '平均分',
MAX(r.`studentresult`) AS '最高分',
MIN(r.`studentresult`) AS '最低分'
FROM result AS r,SUBJECT AS s
WHERE r.`subjectno`=s.`subjectNo`
GROUP BY s.`subjectName`
HAVING AVG(r.`studentresult`)>60;
#数学函数
SELECT CEIL(2.3);
SELECT FLOOR(2.9);
SELECT RAND();
#产生一个0-9随机整数
SELECT FLOOR(RAND()*10);

SELECT ABS(-1);
#字符串函数
SELECT CONCAT('a','b','c');
#字符串替换 insert 索引从1开始，len表示索引之后的长度被替换
SELECT INSERT('bdqn 学习改变生活',4,7,'haha');
#字符串截取
SELECT SUBSTRING('bdqn 学习改变生活',5,7);
#改变大小写
SELECT UPPER('abcD');
SELECT LOWER('ABCd');
#时间函数
#获取当前时间
SELECT NOW();
SELECT WEEK(NOW());
#提取年份
SELECT YEAR(NOW());
#要求查找学生表中出生年份大于李四出生年份的学生信息
SELECT YEAR(borndate) FROM student 
WHERE YEAR(borndate)>(SELECT YEAR(borndate) FROM student
WHERE studentname = '李四');
#提取李四的年月日，按照格式(年/月/日)格式展示
SELECT CONCAT(YEAR(borndate),'/',MONTH(borndate),'/',DAY(borndate)) 
AS 年月日 FROM student WHERE studentname='李四';

SELECT DATEDIFF(NOW(),'1999-4-13');
SELECT ADDDATE(NOW(),10000);

S02E03