a4使用DQL命令查询数据(二)

使用DQL命令查询数据(二)

练习题网站：https://blog.51cto.com/u_13002900/5278839

SELECT语法

语法：顺序不可乱
SELECT 列名 FROM 表名 [ WHERE 条件 ] [ GROUP BY …] [ HAVING …] [ ORDER BY… ][ LIMIT ];

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

ORDER BY排序：

[ ORDER BY… ]	#指定查询记录按一个或者多个条件排序

• ORDER BY排序查询

  对SELECT语句查询得到的结果，按某些字段进行排序

  与DESC (表排序字段名倒叙) 或ASC搭配使用，默认为ASC

LIMIT：

 #指定查询的记录从哪条至哪条
[ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ;

• LIMIT [m,]n 或 LIMIT n OFFSET m

  1. 限制SELECT返回结果的行数

  2. m 制定第一个返回记录行的偏移量

  3. n 制定返回记录行的最大数目

  m不指定则偏移量为0，从第一条开始返回前n条记录；LIMIT 常用于分页显示

SELECT * FROM `result` LIMIT 5       	#返回前5条记录
SELECT * FROM `result` LIMIT 5,10   	#返回6-15条记录 

子查询：

在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句

嵌套查询可由多个子查询组成，求解的方式是由里及外

子查询返回的结果一般都是集合，故而建议使用 IN 关键字

• 列中子查询

# 1.列中子查询	  单列单行
select a.StudentNo,a.SubjectNo,a.StudentResult,(select b.SubjectName from `subject` b 
where b.SubjectNo=a.SubjectNo
) as 课程名称
from result a;

• 表子查询

# 2.表子查询	
select tab.*,d.GradeName 
from (
select SubjectName,a.StudentNo,c.GradeId,StudentName,StudentResult from result a LEFT JOIN `subject` b 
on a.SubjectNo = b.SubjectNo 
LEFT JOIN student c on a.StudentNo = c.StudentNo
) tab  -- 必须有别名
LEFT JOIN grade d on tab.GradeId = d.GradeID;

• 条件子查询

# 3.查询课程为《高等数学-2 高等数学-1》且分数不小于80分的学生的学号和姓名
select * from result a where a.StudentResult>80
and a.SubjectNo in (
select SubjectNo 
from `subject` b 
where b.SubjectName = "高等数学-2" or b.SubjectName = "高等数学-1"
);

MySQL函数

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

统计函数：

函数名称	描述
COUNT( )	返回满足SELECT条件的记录总和数，如SELECT COUNT(*)…【不建议使用 *号，效率低】
SUM( )	返回数字字段或表达式列作统计，返回一列的总和
AVG( )	通常为数值字段或表达列作统计，返回一列的平均值
MAX( )	可以为数值字段、字符字段或表达式列作统计，返回最大的值
MIN( )	可以为数值字段、字符字段或表达式列作统计，返回最小的值

GROUP BY分组：

[ GROUP BY …]	#指定结果按照哪几个字段来分组

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

  对所有的数据进行分组统计；

  分组的依据字段可以有多个，并依次分组；

  与HAVING结合使用，进行分组后的数据筛选；

#按照不同的课程分组，分别算出其平均分、最高分和最低分，
#对于低于60分平均分的不予显示
#分组一般和聚合函数同时出现
# GROUP BY 字段
select a.SubjectNo,b.SubjectName,
sum(a.StudentResult) sumScore,
avg(a.StudentResult) avgScore 
 from result a 
LEFT JOIN `subject` b on a.SubjectNo = b.SubjectNo 
GROUP BY SubjectNo HAVING avgScore>80 ORDER BY avgScore desc ;

• 在sql语句的顺序上，where在groud by 前面，所以分组之后再去过滤条件要与HAVING一起使用

表合并（UNION和UNION ALL）:

• 表的列数要相同，字段别名是以第一个表为基准
• int列数为空可以用00表示，data列数为空可以用空字符串 (’ ')

UNION与UNION ALL的区别：

​ 1、对重复结果的处理：UNION在进行表链接后会去重，UNION All不会。

​ 2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。

UNION ALL 要比UNION效率高，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

字符串函数：

https://www.zybuluo.com/wangzhuanyun/note/1527306

常用举例：

1-- concat：将多个字符串收尾相连后返回

​ 如果有任何参数为空则为空，函数返回null

​ 如果参数是数字，自动转为字符串

-- 返回“concat是字符串连接”
select concat("concat","是","字符串连接");

2-- CONCAT_WS (separator,str1,str2,…) ：将多个字符串指定连接符separator的收尾相连后返回

​ 第一个参数用来指定分隔符

​ 如果有任何参数为null，则函数不返回null，而是直接忽略它

-- 返回11111-22222-33333
select concat_ws('-','11111','22222','33333');

3-- length：返回字符串存储长度(utf-8)

4-- CHAR_LENGTH(str)：返回字符串中的字符个数

-- 返回6
select char_length("我的长度为6")

5-- FORMAT(X,D[,locale])：以格式‘#,###,###.##’格式化数字X 【钱的形式】

# 保留4为小数,前面每三位自动分隔. 0不保留小数
-- 返回123,456.7890
select format(123456.789,4) 

日期函数：

https://www.cnblogs.com/ggjucheng/p/3352280.html

1.获得当前函数：

• now()
• sysdate()
• current_timestamp
• current_timestamp()

2.日期的转换函数 date_format(date,format)：

-- 20080808222301
select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

3.增减一个时间间隔：date_add()，date_sub()：

select date_add(now(), interval 1 day); -- add 1 day
select date_add(now(), interval 1 hour); -- add 1 hour
select date_add(now(), interval 1 minute); -- ...

select date_add(now(), interval 1 week);
select date_add(now()t, interval 1 month);
select date_add(now(), interval 1 quarter);
select date_add(now(), interval 1 year);

select date_add(now(), interval -1 day); -- sub 1 day 加负一天=减一天
select date_sub(now(), interval 1 day);

4.YEAR(date)返回指定日期的年份值

5.MONTH(date)返回指定日期的月份数值

6.DAY(date)返回指定日期的天数值

# 查询本月过生日的学生
SELECT s.s_name, s.s_birth
FROM student s
WHERE MONTH(s.s_birth) = MONTH(CURDATE()) ; --月

# 查询下周过生日的学生
SELECT s.s_name, s.s_birth
FROM student s
WHERE WEEK(s.s_birth) = WEEK(NOW())+1 ; -- 周