Sql查询以及常用函数

查询分为：基本查询 带条件查询 查询的排序

一、查询基础

语法：

SELECT	<列名>
FROM	<表名>

[ORDER BY [ASC或DESC]]

"*"通配符代表所有的列

/*SELECT <列名>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC或DESC]]
"*"通配符代表所有的列*/
-- 1、查询所有的记录 查看所有的列
select * from student;
select * from `subject`;
-- 2、查询某几列的所有记录 加上要显示的列名，列名之间用逗号分隔
select StudentNo,StudentName,Phone,Address,Email from student;
select SubjectName,ClassHour from `subject`;
-- 3、查看所有的列 部分数据（记录） 加上条件过滤where 子句
select * from student where sex='男'; -- 单条件
select * from student where sex='男' and GradeId=1; -- 多条件 用逻辑运算符连
接;
-- 4、查询某几列 部分数据（记录）
select StudentName,Phone,Address,sex from student where sex='女';
select StudentName,Phone,Address,sex from student where sex='女' and
GradeId=2;
-- 5、几种空
is null： 没有值（null值）
is not null ：有值（非null值）
-- mysql中 基本上只会出现null
='' 值为空的
<>'' !='' 值不为空
select * from student where Email is null;
select * from student where Email is not null;
select * from student where Email !='';
-- 6、取别名的几种方式
-- 给表取别名 加空格
select s.StudentName,s.Address,s.Email,s.BornDate from student s;
-- 给列取别名
select s.StudentName 姓名,s.Address 地址,s.Email 邮箱,s.BornDate 生日 from
student s;
-- 使用as 取别名 推荐使用
select s.StudentName as 姓名,s.Address as 地址,s.Email as 邮箱,s.BornDate as
生日 from student as s;
-- 7、显示常量列
select '一年级' as 年级,s.StudentName 姓名,s.Address 地址,s.Email 邮
箱,s.BornDate 生日 from student s where s.GradeId=1;
-- 8、显示固定行数 前多少行（关键字limit）
select * from student s limit 10;
-- 指定范围 开始行 至 显示的行数
select * from student s limit 5,10;
-- 9 排序 ORDER BY （asc desc） 可以单列排 也可以多列
select * from student s order by GradeId asc; -- asc 默认升序 可以不写
select * from student s order by GradeId desc; -- 降序
select * from student s order by GradeId asc ,s.BornDate desc;
-- 10、去重复记录 使用关键字 DISTINCT
select distinct s.Address from student s where s.Sex='男'; -- 影响一列的
重复
select distinct s.StudentName,s.Address,s.BornDate from student s; -- 多列
记录全部相同
-- 11、使用'+'运算
-- 某列数据和固定值运算 所有科目id为1的成绩加5分
select *,r.StudentResult+5 加分后 from result r where r.SubjectId=1;
-- 某列数据和其他列数据参与运算 同一个查询可以多列数值类型数据相加
select *,r.StudentResult+SubjectId 测试运算 from result r;
/*注意：
1. + 连接参与运算的必须是数值类型 */

二、模糊查询

通配符：一类字符，代替一个或多个真正的字符

mysql通配符

通配符	说明
%	匹配任意多个字符字符串
_	匹配一个字符

可与LIKE关键字一起使用

1、模糊查询 like代替‘’=“配合通配符查询

注意：LIKE只与字符型数据联合使用

-- 模糊查询 like代替‘=’ 且配合 通配符 _（下杠）或%
select * from Student;
select * from Student where StudentName='王丽丽';
-- 1、"%"表示任意长度任何字符（0-n个字符）
-- 姓张的同学
select * from Student where StudentName like '张%';
-- 含有地址包含‘宿舍'的同学修改地址为'南昌市解放西路213号'
update Student set Address ='南昌市解放西路213号' where Address like '%宿舍%';
-- (名字中包含"三"任意长度字符)
select * from Student where StudentName like '%三%';
-- 2、"_"表示任何字符的一个（1个任意字符）姓王的所有两个字名字的同学
select * from Student where StudentName like '王_';
-- (任意一个字符开始以第二个字符为峰的结束)
select * from Student where StudentName like '_峰%';
-- 3、in 关键字 查询在列举范围内的多个数据值与之匹配的记录，值之间用 ，号分隔
select * from Result where SubjectId = 1 or SubjectId = 2; -- 如果有二十个我
要看其中9个
select * from Result where SubjectId in(1,4,5,9,7,11,12,15,16);-- 用in解决
-- 字符类 多个名字中的其中之一
select * from Student where StudentName in ('张峰','李四','王五','王二麻子','赵
六子');
-- (可以是很多个，多个值之间用,号分隔 可以是字符、数值) 注意数据类型匹配 可以尝试加
not关键字
select * from Result where SubjectId not in(1,4,5,9,7,11,12,15,16);
select * from Student where StudentName not in ('张峰','李四','王五','王二麻
子','赵六子');
-- 4、查询范围区间的数据记录 关键字 between and 包头又包尾 可以尝试加not关键字
select * from Result where StudentResult >=70 and StudentResult<=90; -- 原来
查询
select * from Result where StudentResult between 70 and 90; -- 模糊的方法
select * from Result where StudentResult between 90 and 70 ;
-- 注意开始结束的方向 反了的话语句是正确的结果是没有的
-- 也可以作用于日期类型的数据
select * from Student where BornDate between '1985-01-01 00:00:00:000' and
'1995-12-31 00:00:00:000';
-- 可以加not
select * from Result where StudentResult not between 60 and 80;

三、聚合函数

聚合函数：对一组（多个0-n）数据进行计算 返回类似统计效果的 “一个” 值

聚合查询只返回一个数值，因此，不能够直接与可能返回多行的列一起使用来进行查询，除非该列包含 在另一个聚合函数中或在GROUP BY语句后。(后面学分组查询)

-- 学生的总成绩、平均成绩、有成绩的学生总共有多少名
select * from Result; -- 有多少id就有多少学生
-- 1、sum()求和
select * from Result where SubjectId = 2;
-- 所有学生的成绩总和
select sum(StudentResult) 总分数 from Result where SubjectId=1; -- 某个科目总
分
select sum(StudentResult) from Result where StudentNo='S1101001';-- 某个学生
总分
-- 2、总数 count()
select count(StudentNo) 总人数 from Result where SubjectId=1; -- 科目1的总人数
select count(*) from Student; -- 也可以这么写 所有学生总数
-- 3、平均数 avg()
select avg(StudentResult) as 平均分 from Result where Subjectid in(1,2);
select avg(StudentResult) as 平均分,COUNT(*) 人数 from Result; -- 可以组合
-- 4、最大值 max()
select MAX(StudentResult) from Result where StudentResult between 60 and 90;
-- 5、最小值 min()
select min(StudentResult) as 最低分 from Result ;
-- 6、统计汇总
-- 年级考试合格的总人数 总分数 平均分 最高分 最低分
select count(StudentNo) 总人数,sum(StudentResult) as 总分,
AVG(StudentResult) 平均分, max(StudentResult) 最高分,
min(StudentResult) as 最低分 from Result where SubjectId='2';

四、常用函数

常用函数有：字符函数 、数值函数 、日期时间函数，以及聚合函数

1、字符函数

length：字符个数

select length('john');

concat：拼接字符串

select concat('I',' ','like',' ','mysql');

upper/lower：大小写转换

select upper('mysql');
select lower('MySQL');

substr/substring：字符串截取(功能相同)

-- 从第几个字符开始截取，注意：索引从1开始
select substr('I like MySQL',8);
-- 从第几个字符开始截取，截取几个字符
select substr('I like MySQL',8,2);

instr：子串在字符串中的起始索引

select instr('I like MySQL','MySQL');

trim：去掉前后空格

select trim(' My SQL ');

replace：替换

select replace('aabb','bb','cc');

所有的函数也可以用在各种sql语句中，也可以使用在sql的其它编程中，比如存储过程

-- 1、函数调函数
select length(trim(' My SQL '));
-- 2、像聚合与日期一样用在sql语句中
select concat(s.StudentName,s.Phone,s.Address) as 拼接后的结果 from student as
s;
-- 3、sql中也可以相互函数调函数
select round(avg(StudentResult)) from result;

 2、数学函数

abs:绝对值

select abs(-26);

round：四舍五入

select round(1.4);
-- 小数点后保留几位
select round(1.567,2);

ceil：向上取整

select ceil(1.2);

floor：向下取整

select floor(1.2);

truncate：截断(小数点后保留几位)

select truncate(1.61,2);

mod：取模

select mod(10,3);

五、日期时间

now：返回当前系统时间

select now(); -- 包括日期和时间

curdate：返回当前系统日期，不包含时间

select curdate();

curtime：返回当前系统时间

select curtime();

获取指定的部分

select year('2020-10-10');
select month('2020-10-10');

str_to_date ：将日期格式的字符转换成指定格式的日期

select str_to_date('2020年10月12','%Y年%m月%d');

感谢大家的阅读，如有不对的地方，可以私信我，感谢大家！