数据库mysql第三章DQL命令查询select语句

mysql

DQL语言

DQL(Data Query Language,数据查询语言)
查询数据库数据,如SELECT语句
简单的单表查询或多表的复杂查询和嵌套查询
数据库语言中最核心、最重要的语句
使用频率最高的语句

SELECT语法

SELECT [ALL | DISTINCT]
{ *| table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2][, ·····]}
FROM table_ name [ as table_ alias]
[ left|out|inner jointable_ name2] #联合 查询
[ WHERE ..]#指定结果需满足的条件
[ GROUP BY ..]#指定结果按照哪几个字段来分组
[ HAVING ..]#过滤分组的记录必须满足的次要条件
[ ORDER BY...]#指定查询记录按-一个或者多个条件排序
[ LIMIT { [ offset,] row_ count| row_ count OFFSET offset }] ;#指定查询的记录从哪条至哪条
[]括号代表可选的:
{}括号代表必须的;
# MySQL语句中的注释符，也可以用/*该处为注释*/

where条件语句

用于检索数据表中符合条件的记录
搜索条件可由一个或多个逻辑表达式组成，结果- -般
为真或假
搜索条件的组成
逻辑操作符
比较操作符

逻辑操作符

比较操作符

BETWEEN AND范围查询

根据一个范围值来检索
SELECT  字段列1，字段2 ..FROM 表名WHERE 字段x
BETWEEN值1 AND值2
等同于>=和<=联合使用

查询课程表中课时在110和120之间的所有记录

SELECT * FROM subject WHERE ClassHour BETWEEN 110 AND 120;
等同于:
SELECT * FROM subject WHERE ClassHour >= 110 AND ClassHour <=120;

LIKE模糊查询.

在WHERE子句中，使用LIKE关键字进行模糊查询
与“%”一起使用，表示匹配0或任意多个字符
与“_”起使用，表示匹配单个字符

示列

查询包含“数学”的所有课程

SELECT * FROM subject WHERE SubjectName LIKE "%数学%";

查询所有姓名为“李**”三个字的学生信息

SELECT StudentNo,StudentName FROM student
WHERE StudentName LIKE "李_ ";
使用IN进行范围查询
在WHERE子句中使用IN进行范围查询
SELECT字段列1，字段2 ..FROM表名WHERE 字段x IN (值1,值2,值3...)
查询的字段x的值，至少与括号中的一个值相同
多个值之间用英文逗号隔开
SELECT * FROM subject where
ClassHour= 100 OR
ClassHour =110 OR ClassHour = 120; #普通处理方式
SELECT * FROM subject where ClassHour IN ( 100, 110,120 );

使用IN进行查询方式，更为简洁，效率更高

NULL空值条件查询

NULL
NULL代表"无值”
区别于零值0和空符串
只能出现在定义允许为NULL的字段
须使用IS NULL或IS NOT NULL比较操作符去比较

连接查询(多表查询)

连接查询
如需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询
分类包括
●内连接( inner join)
        等值和非等值的连接查询
        自身连接查询
外连接( out join )
        左连接(LEFT JOIN)
        右连接(RIGHT JOIN)

哪些学生考试了

select distinct 
r.StudentNo,r.ExamDate 
from result r ;

查询成绩低于60分 返回结果 +10分

select a.StudentNo,a.StudentResult+10  
from result a 
where a.StudentResult<60;
select * from grade a 
where a.GradeName is not null 
and  a.GradeName != '';

查询mysql版本

select VERSION();

查考试成绩60-80分的信息

WHERE 字段 BETWEEN 值1 AND 值2

select * from result r  where r.StudentResult
BETWEEN  60 and 80;
SELECT * FROM  result r where r.ExamDate 
BETWEEN '2012-11-10' and '2013-11-11';

查询姓张的学生

select * from student s where 
s.StudentName like '张_';

查询年级ID 为 1 2 3的所有学生信息

select * from student s where 
s.GradeId in(1,2,3);

内连接查询2-1

##INNER JOIN内连接
在表中至少一个匹配时， 则返回记录
SELECT字段1,字段2... FROM table_ 1
INNER JOIN table. 2 ON table_ 1.字段x = table_ 2.字段y;

INNER JOIN与JOIN是相同的;

如table. 1中的行在table_ 2中没有匹配，则不返回;

要求:从subject和grade 数据表查询课程名称和所属年级名称

SELECT SubiectName,GradeName FROM subject INNER JOIN grade
ON  subiect.GradelD= grade.GradelD;

内连接查询2-2

等值和非等值的连接查询
与单表查询类似，都是SELECT语句
把多个表放到FROM后，并用逗号隔开
可使用AS关键字取别名，便于引用
如无重名查询字段则可省略数据表的指定

要求:从subject和grade数据表查询课程名称和所属年级名称

非等值连接查询

SELECT SubjectName, GradeName FROM subject, grade,(返回记录数为两表记录数的乘积

等值查询

SELECT SubjectName, GradeName FROM subject, grade
等效于内连接WHERE subject.GradelD = grade.GradeID;
外连接
左外连接(LEFT JOIN)
从左表(table_ 1)中返回所有的记录，即便在右(table_ _2)中没有匹配的行
SELECT字段1,字段2... FROM table_ 1
LEFT [ OUTER] JOIN table_ 2 ON table_ 1.字段x = table_ 2.字段y;
右外连接(RIGHT JOIN)
从右表(table_ _2) 中返回所有的记录,即便在左(table_ 1)中没有匹配的行
SELECT  字段1,字段2... FROM table_ 1
RIGHT [ OUTER] JOIN table_ 2 ON table_ 1.字段x = table_ 2.字段y;

王老师讲课实录

select a.*,t1.* 
from result a 
LEFT JOIN  (
select b.StudentNo,
b.StudentName,
c.GradeName from student b
LEFT JOIN grade c 
on b.GradeId = c.GradeID
) t1 
on a.StudentNo = t1.StudentNo
select now()

一张表包含学生姓名，年级名称

不同的SQL JOIN对比

JOIN对比

自连接查询

数据表与自身进行连接

从一个包含栏目ID，栏目名称和父栏目ID的表中，查询父栏目名称和其子栏目名称，表结构如下:
#表结构语句
CREATE TABLE IF NOT EXISTS category(
    categoryld int(10) auto_ increment primary key,
    categoryName varchar(32) not null，
pid int(10)
);

ORDER BY排序

ORDER BY排序查询
对SELECT语句查询得到的结果，按某些字段进行排序
与DESC或ASC搭配使用，默认为ASC

MySQL的LIMIT

LIMIT[m,]n或LIMIT n OFFSET m
限制SELECT返回结果的行数
m制定第一个返回记录行的偏移量
n制定返回记录行的最大数目
m不指定则偏移量为0，从第一条开始返回前n条记录
LIMIT常用于分页显示
示例
SELECT * FROM `result` LIMIT 5   #返回前5条记录
SELECT * FROM `result` LIMIT 5,10 #返回6-15条记录
可以做分页查询
    查询所有《数据库结构-2》的考试成绩，并按照由高
    到低显示，同时把该成绩对应的学生的学号、姓名打
印出来

查询所有成绩 成绩降序排序

select * from result r 
order by r.StudentResult desc;

查询所有参加高等数学01考试的学生成绩

成绩降序排序

select b.StudentName,c.SubjectName,
a.StudentResult,a.ExamDate 
 from result a LEFT JOIN student b
on a.StudentNo = b.StudentNo LEFT JOIN 
`subject` c on a.SubjectNo=c.SubjectNo 
where c.SubjectName = '高等数学-1' 
ORDER BY a.StudentResult desc,a.ExamDate desc 
limit 10;

统计记录数

select count(1) 
from student a;

统计所有考试成绩和

select a.SubjectNo,avg(a.StudentResult) r
from result a GROUP BY 
a.SubjectNo HAVING avg(a.StudentResult)>80;

每个年级的人数

select @xh:=@xh+1 rownumber,t1.* from (
select b.GradeName ,count(1) as rs 
from student a left JOIN grade b
on a.GradeId = b.GradeID
where b.GradeName in(
'大一','大二','大三'
)
group by a.GradeId 
HAVING count(1)>0
ORDER BY rs desc 
#LIMIT 1
) t1 ,(select @xh:=0) t2;

子查询

在查询语句中的WHERE条件子句中，又嵌套了另外-个查询语句
示例
查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
注意
嵌套查询可由多个子查询组成，求解的方式是由里及外
子查询返回的结果一般都是集合，故而建议使用IN关键字

MySQL函数

数学函数
字符串函数
日期和时间函数
系统信息函数

MySQL的统计函数

GROUP BY分组

使用GROUP BY关键字对查询结果分组

对所有的数据进行分组统计
分组的依据字段可以有多个，并依次分组
与HAVING结合使用,进行分组后的数据筛选

查询2010年考试成绩

select * from result a 
where 
DATE_FORMAT(a.ExamDate,'%Y') = 2010;

查询所有考试成绩，返回几天前考的试

相距多少天

select datediff(now(), a.ExamDate) from result a;

日期比较

select DATE_FORMAT(now(),'%Y%m%d%H%i%s');
select DATE_FORMAT('2020-06-01','%Y%m%d%H%i%s') ;

时间累加或累减

select date_add(now(), interval -3 hour);
select date_add(now(), interval 3 hour);
select IFNULL(name,0);
select a.StudentNo,
a.StudentName,IFNULL(b.SubjectNo,0) subno,
SUM(b.StudentResult) 'ResultSum'
from student a 
LEFT JOIN result b 
on a.StudentNo=b.StudentNo 
where a.Sex = 1 
GROUP BY a.StudentNo 
UNION ALL 
select c.StudentNo,
c.StudentName,0 as subno,
'' as 'ResultSum' from student c where c.Sex=2;
select *,'1' as "人数" from grade where GradeName is not null 
UNION ALL 
select *,'' as "人数" from grade where GradeName is null;