首先创建学生表:
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 --->1,模糊查询用%,匹配0次或多次都行。2,下划线_ 必须匹配一次,几个下划线匹配几次
如:
select* from student where name like '张%';查询姓张的学生信息,开头就是张字
select* from student where name like '%李%';查询姓名中含有李字的学生信息。
select* from student where name like '王_';查询姓王有俩字的学生信息。
3,escape :取消% 或_字符的通配符特性,可以再%或_ 前加任何符号,如*,它就成了转义符,将其后符变为普通符号,只用在后面escape '*'。注意escape后只能跟一个字符。如:
select * from student where name like '%李k%%'escape 'k'
会查找含有李%的学生信息
select * from student where name like '%李%%'
会在student表中查找含有李字的学生信息,%不再匹配
4, and or
#查询张姓且地址中含有北京的学生信息
select * from student where name like '张%' and address like '%北京%’;
#查询张姓或地址中含有北京的学生信息
select * from student where name like '张%' or address like '%北京%’;
5,between 下限 and 上限。
between and--->含两端 注意一定是小数据在前,大数据在后,输出这之间的符合条件的学生信息
select * from student where age between 11 and 28
6,in
select * from student where age in(28,24);
效果等同:
select * from student where age =28 or age=24
7,is null is not null
必须是严格写,不能写成=null;!=null;
8,关系运算符可写成如下:select * from student where age >= 11
9,order by 排序
select * from student order by age asc(如果不写,是默认升序)
select * from student order by age desc(降序)
#排序之后可以跟多个字段名,用,间隔,先按第一个排序,相同了按第二个依次推。
10,聚合函数 用于统计"多条"数据的函数。常用有:count,sum,avg,max,min.
#count
select count(id) from student
#sum
select sum(age) from student
#avg
select sum(age)/count(id) as age from student
select avg(age) age from student
#max min
select max(age),min(age)from student
11,group by 分组 一般与聚合函数一起使用
select age,count(id) from student group by age
select name,age,count(id) from student group by age 如果使用group by 则select字段列表只能是聚合函数或者分组字段
12,having 如何查出哪个姓名重名.
select * from student
select name,count(name) from student group by name
select name from student group by name having count(name)>1
where 与having:1、where 不能使用聚合函数;having与聚合函数使用;2、where先执行 再执行 group by having order by
select name from student where count(name)>1 group by name
13, where、 group by、 having 、order by是默认的使用顺序
如何查出王姓哪个姓名重名,并且排序
select * from student where name like '王%'
select name,count(name) from student where name like '王%' group by name
select name,count(name) from student where name like '王%' group by name having count(name)>1
select name,count(name) from student where name like '王%' group by name having count(name)>1 order by name
select name,count(name) from student where name like '王%' group by name having count(name)>1 order by convert(name USING gbk);
##convert(name USING gbk)是固定的模式,只要变变名字就可以套用。要是不加他,系统是把有重复姓名的学生信息列出,但按首字母降序排的,为使升序,加他。欧克
#如果是中文convert转译
下午茶:
#length(column_name|str):返回字符串存储长度
如:select length(name) length from student
一个汉字存储长度是3,其他字符是1,后面写length 是把输出结果以length标识,省略了as
#char_length(column_name|str):返回字符串中字符个数
如:select char_length(name) length from student
他真正返回字符个数
#concat(column_name1|str1, column_name2|str2,......):将多个字符串首尾相连后返回;
select concat(id,',',name,',',age,',',mobile,',',address)info from student
#concat_ws(separator,column_name1|str1, column_name2|str2,......):将多个字符串按照执行separator进行首尾相连;之后以info 命名返回
select concat_ws(',',id,name,age,mobile,address)info from student
#trim 去掉俩端左右空格
select trim(name)from student
#substr(str,pos[,len]):从源字符串str中的指定位置pos开始取一个字串并返回;等效substring
select substr(name,-1)from student:是从倒数第一个开始截取,切记,substr是下标是从1 开始的
select substr(name,2)from student:从第二个字符开始截取
select substr(mobile,2,3)from student从第二个字截取俩字符
•trim([{both | leading | trailing} [remstr] from] str):返回去掉str源字符串两端、前缀或后缀字符串remstr;不指定both、leading、trailing ,则默认为 both,此时不能使用from关键字;不指定remstr,则去掉str两端的空格;
select trim( both '王' from name)from student===select trim( '王' from name)from student切记要去掉仅对首尾有用。
select trim(leading '王' from name)from student 去掉name中首字符为'王',要是从第二个是王,不起作用。包括首字符是空格。
select trim(trailing '王' from name)from student 去掉name中最后一个是王字的王。
如:
select trim(‘ Tom ’) from dual-------;返回Tom
select trim(both 'a' from 'aaaTomaaa’)-----返回Tom
select trim(leading 'a' from 'aaaTom’);----返回Tom
select trim(trailing 'abc' from 'Tomabc');----返回Tom
#replace 替换
select replace(name,'_','*')from student
取代name中_为*
#dual 伪表,保证select语句的完整性,now()获取当前时间
select now() from dual --->select now();#默认
#reverse 反转
select reverse(name)from student
#strcmp 比较俩字符串,相等为零,第一个<后一个返回-1,>返回1。
#mod():取x,y的余数
是x除以y的余数
#truncate(x,y) 返回x截断后的结果,
#round(x,y)把x四舍五入,y代表小数位数,不写默认为0
#date_format(date,format):获取指定格式的日期
例子:select date_format(now(),'%Y%m%d%H%i%s');
结果是:
#datediff(date1,date2):返回(date1-date2)天;
例子: select datediff(now(),'2019-08-10');
结果:
#timediff(time1,time2):返回time1-time2,注意:函数的两个参数类型必须相同
select timediff(now(),'2019-8-17 16:02:00')
结果:
不重要----convert(value,type):将value转换为type类型,type可以是char(字符型)、date(日期型)、time(时间型)、datetime(日期时间型)、 signed(整型) 和decimal(浮点型)类型中的一个;
1,select convert(now(),char)
结果:
2,select convert(now(),date)
3,select convert(now(),time)
4,select convert(now(),datetime)
select convert(now(),signed)+1
select convert(now(),decimal(5,2))+2
#if(,,)
例子:select id,name,mobile,if(address is null ,'未知',address)from student
如果括号中第一个成立,返回第二个,否则返回第二个
#ifnull(,)
select count(ifnull(address,''))from student
括号中第一个如果满足null,返回第二个,不满足返回自身。
#distinct 只能用于select 之后
select distinct name,age from student
当名字相同时看age,满足不同仍然输出
select distinct name from student