Mysql详解（二）-- DQL查询

11.DQL查询数据

11.1 基本查询语句和别名使用

-- 查询表中全部数据
select * from 表名;

-- 查询指定字段
select 字段名,字段名 from 表名;

-- 使用别名 （可以给字段起别名，也可以给表起别名）
select 字段名 as  别名 , 字段名 as 别名 from 表名;
select 字段名 from 表名 as 别名; 


-- 函数 concat(a,b)
select concat('姓名:',name) as 名字 from student;

11.2 去重

去重（distinct）作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条

-- 去重
select distinct stu_no from student ;

11.3 常用数据库表达式

-- 查询系统版本
select version();

-- 用于计算
select 100*3-1 as 计算结果;

-- 查询自增的步长
select @@auto_increment_increment;

11.4 where条件子句

作用：检索数据中符合条件的值

逻辑运算符

运算符	语法	描述
and   &&	a and b       a&&b	逻辑与，两个都为真，结果为真
or      ||	a or b         a||b	逻辑或，其中一个为真，则结果为真
not    !	not a           !a	逻辑非，真为假，假为真

尽量使用英文字母

-- 查询成绩在95~100分之间的数据
select * from result where score >=95 and score<=100;

-- 还可以通过 between and （区间查询）
select * from result where socre between 95 and 100 ;

-- 查询学号不是1000的学生
select * from student where stu_no != 1000;

11.5 模糊查询

比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null,结果为真
is not null	a is not null	如果操作符不为null,结果为真
between and	a between b and c	若a在b和c之间，则结果为真
like	a like b	sql匹配，如果a匹配b，则结果为真
in	a in (a1,a2,...)	假设a在a1，或者a2...其中一个值中，结果为真

like 结合 %(代表0到任意个字符)  _(一个字符)

-- 查询姓刘的学生
select * from student where name like '刘%';

-- 查询姓刘学生名字后面一个字的
select * from student where name like '刘_';

-- 查询姓刘学生名字后面两个字的
select * from student where name like '刘__';

-- 查询名字中有嘉字的学生
select * from student where name like '%嘉%';

in

-- 查询 1001,1002,1003号学生
select * from student where stu_no in (1001,1002,1003);

is null 和 is not null

-- 查询地址为空的学生
select * from student where address is null or address = '';

-- 查询出生日期不为空的学生
select * from student where birthday is not null;

11.6 联表查询

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

11.7 分页和排序

-- 根据学生分数排序（升序）
select * from result where stu_no!=1000 order by score asc;

-- 根据学生分数排序（降序）
select * from result where stu_no!=1000 order by score desc;

-- 分页(limit 起始页,展示条数)

-- 查询第一页，每页展示100条
select * from student where stu_no!=1000 order by id desc limit 0,100;
-- 查询第二页，每页展示100条
select * from student where stu_no!=1000 order by id desc limit 100,100;
-- 查询第三页，每页展示100条
select * from student where stu_no!=1000 order by id desc limit 200,100;
-- 查询第N页，每页展示100条 
select * from student where stu_no!=1000 order by id desc limit ((n-1)*100),100;

11.8 子查询

-- 查询刘燕的成绩
select * from result where stu_no = (select stu_no from student where name = '刘燕');

11.9 分组和过滤

-- 分组(group by )
-- 查询不同课程的平均分，最高分，最低分
select subjectName , avg(score),max(score),min(score) from result r inner join subject sub on r.subjectNo = sub.subjectNo group by r.subjectNo




-- 过滤(having 过滤分组的记录必须满足的次要条件)
-- 查询不同课程的平均分，最高分，最低分 并且平均分大于80
select subjectName , avg(score),max(score),min(score) from result r inner join subject sub on r.subjectNo = sub.subjectNo group by r.subjectNo having avg(score) >= 80

 11.10 where ,group by , having, order by ,limit 使用顺序

where ...
group by ...
having ... 
order by ...
limit ...

12. mysql函数

 12.1 常用函数

   数学运算

-- 绝对值
select abs(-8);
-- 向上取整  
select ceiling(9.4); 
-- 向下取整
select floor(9.4); 
-- 返回一个0~1之间的随机数
select rand(); 
-- 返回一个数的符号。负数返回-1，正数返回1,0返回0
select sign(); 

  字符串函数

-- 获取字符串长度
select char_length('ssdfjs');
-- 拼接字符串
select concat('i','love','you');
-- 替换字符串
select insert('love',1,2,'you');
-- 小写字母
select lower('Kiss');
-- 大写字母
select upper('kiss');
-- 返回第一次出现的字符串的索引
select instr('love','o');
-- 替换指定的字符串
select replace('love me','me','you');
-- 截取字符串(返回指定的字符串,源字符串,截取的位置,截取的长度)
select substr('i love you',1,6);
-- 反转字符
select reverse('i love you');

 时间和日期函数 

-- 获取当前日期(格式 yyyy-MM-dd)
select CURRENT_DATE();
select CURDATE();

-- 获取当时时间(格式 yyyy-MM-dd HH:mm:ss)
select now();

-- 获取本地时间(格式 yyyy-MM-dd HH:mm:ss)
select LOCALTIME();

-- 获取系统时间(格式 yyyy-MM-dd HH:mm:ss)
select SYSDATE();

-- 获取年
select year(now());

-- 获取月
select month(now());

-- 获取日
select day(now());

-- 获取小时
select hour(now());

-- 获取分钟
select minute(now());

-- 获取秒
select second(now());

 系统函数

-- 获取当前用户
select system_user();
select user();
-- 获取版本
select version();

 12.2 聚合函数

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
...	...

-- 查询学生表的条数

-- 会忽略所有的null值
select count(stu_no) from student; 
-- 不会忽略所有的null值
select count(1) from student;
select count(*) from student; 


-- 查询成绩的总分数
select sum(score) from result;

-- 查询成绩的平均数
select avg(score) from result;

-- 查询最高分
select max(score) from result;

--查询最低分
select min(score) from result;

13. 数据库级别的MD5加密

  什么是MD5？

 主要增强算法复杂度和不可逆性。

select  MD5('123456')