数据查询（上）

create table student(
	id char(36) primary key,
	name varchar(8) not null,
	age int(3) default 0,
	mobile char(11),
	address varchar(150)
)
insert into student 
values ('9b4435ec-372c-456a-b287-e3c5aa23dff4','张三',24,'12345678901','北京海淀');
insert into student 
values ('a273ea66-0a42-48d2-a17b-388a2feea244','李%四',10,'98765432130',null);
insert into student 
values ('eb0a220a-60ae-47b6-9e6d-a901da9fe355','张李三',11,'18338945560','安徽六安');
insert into student 
values ('6ab71673-9502-44ba-8db0-7f625f17a67d','王_五',28,'98765432130','北京朝阳区');
insert into student 
values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%',11,'13856901237','吉林省长春市宽平区');

select * from student

#where
#null
select * from student where address = null ##这是错误的
select * from student where address is null
select * from student where address is not null

#关系运算
select * from student where age>11

#and or
select * from student where age>11 and age<28
select * from student where age=11 or age=28

#between and 包括边界值；小数，大数
select * from student where age between 10 and 28
select * from student where age between 29 and 10    ##这是错误的，小数在前，大数在后

#模糊查询
#%   可以不占用字符
select * from student where name like '张%'

#_  必须占用一个字符
select * from student where name like '张_'

#escape

select * from student where name like '%A%%' escape 'A' ##查询含有%的，告诉数据库A后面的%不是模糊查询中的%，而是一个普通的%
select * from student where name like '%A_%' escape 'A'

#in
select * from student where age in(11,24)##查询age为11或者24的数据

#order by
select * from student order by age asc
select * from student order by age desc

select * from student order by age,mobile desc 
select * from student order by age asc,mobile desc   ##前面的优先级最高

#dual 伪表


#取余 %
select mod(1,3) from student

#distinct 去除重复的值 字段必须放在distinct的后面
select distinct name from student

#函数
#对多条数据进行统计，聚合函数，多行函数
#对某条数据特定列进行操作 单行函数

#单行函数
#length
select length(name) from student

#char_length
select name,char_length(name) from student

#concat
select concat(id,',',name,',',mobile,',',address) indo from student

#concat_ws


#trim去除空格

#substr subtring
select substr('abccba',2)

select substr('abccba',2,3)

select replace('110#112#119','#','$')

#resverse
select reverse('abc')

#获取当前时间
select now()

select date_format(now(),'%Y年%m月%d日 %h时%m分%秒')

#round  四舍五入
select round(1.1415926)
select round(1.1415926,3)

#truncate()  直接舍掉
select truncate(3.1415926,3)

#strcmp
select strcmp('abc','abc')  #相同返回0
select strcmp('bc','abc') 
select strcmp('abc','bc') 

#convert()强制转换
select convert(now(),date)

#if(a,b,c)
select if(address is null,'未知',address) from student

#ifnull
select ifnull(address,'未知') from student

#多行函数	用于统计
select avg(age) from student

select max(age) from student

select min(age) from student

select sum(age) from student

select count(age) from student

1、多行函数忽略null列
select count(id) from student		#5

select count(address) from student		#4

2、默认情况下，字段不能和聚合函数一起使用
select name,avg(age) from student

#group by 查询语句中，select后面可以接 聚合函数和分组字段
select name,count(id) from student group by name

#查询哪个姓名重名
select name,count(id) from student group by name having count(id)>1

#where 不能跟聚合函数

#where group by having order by
where 是过滤符合条件的数据
group by 对过滤好的数据进行分组
having 对过滤好的数据进行分组后再一次过滤
order by 最后进行排序

 

本文地址：https://blog.csdn.net/ArcsinTao/article/details/107319529