sql多表查询,嵌套查询,函数查询

1.多表查询

select * from user u,card c where u.userId=c.userId
1

2.嵌套查询

• 简单嵌套查询

select * from user where city =(select city from user where userId='101' )
1

• 带in的嵌套查询

select * from  user  where city in (select city from user where userId !='101')
1

• 带any的嵌套查询

SELECT * from user where credit > any(select credit from `user` where city='北京')
1

• 带all的嵌套查询

SELECT * from user where credit > all(select credit from `user` where city='北京')
1

• union:并查询，无重复。 union all:有重复

SELECT * from user where credit >300 union SELECT * from user where city='北京'
1

SELECT * from user where credit >300 union all SELECT * from user where city='北京'
1

• 总结

select * from 表 where 字段 = (select 字段 from 表)
select * from 表 where 字段 in (select 字段 from 表)
select * from 表 where 字段 > any (select 字段 from 表)
select * from 表 where 字段 > all (select 字段 from 表)

select 字段，(select 字段 from 表2 where 条件) from 表1

select * from (select 字段 字段2 字段3 from 表)t1,(select 字段 字段2 字段3 from 表)t2 
         where t1.字段=t2.字段
select * from (select 字段 字段2 字段3 from 表)t1,table where t1.字段=table.字段
1
2
3
4
5
6
7
8
9
10

3.逻辑函数

• if函数

select *,if(credit>100,'优秀','中等') from user
1

• ifNull函数

select *,IFNULL(ename,'--') from user
1

• Case函数

select *,(case when credit>300 then '优质客户' when credit>200 then '中等客户' else '一般客户' end) from user
1

select *,(case city when '北京' then '首都' when '上海' then '魔都' when '深圳' then '特区'  else '省会' end) from user
1

4.字符函数。第一个位置被标记为1

• 连接字符串concat

select userId, concat(name,','sex) from user
1

• 字节长度length

select userId,name,length(name) from user
1

• 字符长度char_length

select name,char_length(name) from user
1

• 查找字符:locate(substr,str)
  返回字符串substr中第一次出现子字符串的位置 str，没有返回0。

select name,locate('晓',name) from user
1

• 区间查找字符位置:locate(substr,str,pos)
  返回字符串substr中第一次出现子字符串的位置 str，从位置pos开始。没有返回0。

select ename,locate('o',ename,2) from user
1

• 左填充:lpad(str,len,padstr)
  左面用字符串padstr填补直到str是len个字符长。

select name,lpad(name,3,'#') from user
1

• 右填充:rpad(str,len,padstr)
  右面用字符串padstr填补直到str是len个字符长。

select name,rpad(name,3,'#') from user
1

• 左截取:left(str,len)
  返回字符串str的最左面len个字符。

select name,left(name,1) from user
1

• 右截取:right(str,len)
  返回字符串str的最右面len个字符。

select name,right(name,1) from user
1

• 截取:substring(str,pos,len) substring(str,pos)

select ename,substring(ename,2,2) from user
1

select ename,substring(ename,2) from user
1

• 截去前后空格:trim(str)

select trim(' java ')
1

• 替换字符:replace(str,from_str,to_str)
  返回字符串str，其字符串from_str的所有出现由字符串to_str代替。

select ename,replace(ename,'x','a') from user
1

• 重复:repeat(str,count)
  返回由重复count次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。

select name,repeat(name,2) from user
1

• 反转字符:reverse(str)
  返回颠倒字符顺序的字符串str。

select name,reverse(name) from user
1

• 插入字符:insert(str,pos,len,newstr)
  在位置pos起始的子串且len个字符长得子串由字符串newstr代替。

select ename,insert(ename,2,2,'txc')from user
1

select name,insert(name,2,0,'-') from user
1

• 转小写:lcase(str) lower(str)

select ename,lower(ename) from user
1

• 转大写:ucase(str)upper(str)

select ename,upper(ename) from user
1

5.数字函数

• 绝对值：abs(x)

select money,abs(money) from user
1

• 天花板数:ceil(x)返回不小于x的最小整数值

select money,ceil(money) from user
1

• 地板数:floor(x)返回不大于x的最大整数

select money,floor(money) from user
1

• 四舍五入:round(x,d)返回数字x四舍五入到指定的小数位
  -1:个位数进行四舍五入；1:保留一位小数；0:将小数位进行四舍五入。

select money,round(money/100),round(money/100,1),round(money,-1) from user
1

• 截去：truncate(x,d)返回数字x截断为d位小数的值

select money,truncate(money/100,1),truncate(money,-1) from  card
1

• 随机数：rand( )返回0到1的随机值

select money,money*rand() from user
1

• 求模:mod(x,y)返回数字x除以y后的余数

select money,mod(money,2)from user
1

6.日期函数

• 取得系统时间:now()，sysdate()

select now();
1

select sysdate()
1

• 日期转换字符串:date_format(date,format), time_format(time,format)

-- 年月日时分秒
select date_format(openDate,'%Y年%m月%d日 %H:%i:%s') from card
1
2

-- 时分秒
select time_format(transDate,'%H:%i:%s') from  transinfo
1
2

• 字符串转换成日期:str-to-date

insert into card(cardNo,openDate,money,password) 
values('011', str_to_date('05/20/2019', '%m/%d/%Y'),1000,'123456')
1
2

• date_add(date,interval expr unit):对日期的年月日时分秒进行运算

select openDate,date_add(openDate,interval 1 day) from card
1

• datediff(expr1,expr2):对两个日期进行相减

-- 结果是得到的是天数
select openDate, datediff(now(),openDate) from card
1
2

• year(date)：返回日期date的年。
  month(date)：返回日期date的月份。
  day(date)：返回日期date的日。
  hour(date)：返回日期date的小时。
  minute(date)：返回日期date的分钟。
  second (date)：返回日期date的秒。

select transDate,year(transDate),month(transDate),day(transDate) ,hour(transDate),minute(transDate),second(transDate) from transinfo
1

• dayOfWeek(date)：返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。
• weekDay(date)：返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

select transDate,dayOfWeek(transDate),weekDay(transDate) from transinfo
1

• last_day(date):返回date所在月的最后一天

select LAST_DAY(opendate) from card
1

7.多行函数

• sum

select sum(age) from user
1

• avg

select avg(age) from user
1

• count

select count(userId) from user
1

• max

select max(age) from user
1

• min

select min(age) from user
1

• group_concat

select group_concat(name) from user
1