MySQL(4) 数据库的视图及函数

MySQL(4) 数据库的视图

#创建学员成绩视图
create view v_score
as
	select
		scoreId,
		stuName,
		score,
		if(isIn,'正常','缺考') isIn,
		if(level,'正常',concat('补考',(level-1)))
	from
		score S
	inner join
		student U
	on
		S.stuId = U.stuId
	inner join
		subject J
	on
	s.SubjectId = J.subjectId;
#测试concat用法
select '1'+1;
select 'a'+1;
select concat('a',1);
# 修改部分学员的考试状态
update score set level=2 where stuId%11=0;
# 分组 group_cat(col)
select
	provinceId,cityId,group_concat(countyId)
from
	student
group by
	provinceId,cityId;	
# 字符串函数+模糊查询
select
	*
from
	student
where
	stuName
like
	concat('张','%');
	#多字段分组
select
	provinceId,cityId,group_concat(countyId)
from
	student
group by
	provinceId,cityId;
************************************************************************
#查询索引 (表分区)
show index from student;
#删除索引
drop index ix_name on table_name;
# 查库容量
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
    concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
    from information_schema.tables
    group by TABLE_SCHEMA
    order by data_length desc;

# 查表容量	
select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,
    concat(truncate(index_length/1024/1024,2),' MB') as index_size
    from information_schema.tables where TABLE_SCHEMA = 'kh69'
    group by TABLE_NAME
    order by data_length desc;

函数

#数学函数
select ceil(1.0004);
select floor(1.9999);
select abs(-8);
select rand();
select rand(3.141592653,3);
select truncate(3.141592653,3)
#字符串函数: group_concat() 列字符串拼接
select group_concat(provinceName) 所有省份 from province;
select group_concat(cityName) from city;
select group_concat(concat(cityId,cityName)) from city;
select group_concat(concat(cityId,concat(',',cityName))) from city;
#字符串拼接 : select concat('','','',...)
#join : select concat_ws(sep,'','',...)
#大小写字母转换
select upper('aAC');
#字符串长度
#字节数:select length
select length('abc');
#字符数:select char_length
select char_length('1张');
#获取子字符串的位置(help locate/position) pos为开始检索位置（包含）
#SELECT locate (substr,str[,pos]);
select locate('bar','foobarbar');
select locate('xbar','foobarbar');
select locate('bar','foobarbar'4);
select locate('abc','eroiabackks',5)
select locate('abc'IN'foobabcarbar'4);
#指定位置查询 select mid(str,star,len)
select left('abcde',2);
select right('abcde',2);
select mid('abcde',2,2);
#字符串比较 0,1,-1
select strcmp('abc','abc')
select strcmp('bbc','abc')
select strcmp('abc','zbc')
#java字符串比较
String a = "abc",b= "abc";
System.out.println(a.compareTo(b));
#截取字符串
select left/right(str,len)

*******************************************************************
#日期函数
#当前日期+时间select now()
select date_add(now(),interval 3 hour)
select now();
select date();
#当前日期
select curdate()
#当前时间
select curtime()
#当前年、月、日select year/month/day(date)
select year(curdate());
select weekday(curdate());
select date(curdate());
select hour(curtime());
#某年第几周select week(date)
select week(curdate());
#某星期星期几 (星期一为0) select weekday(date)
select week('2017-07-28');
#计算日期 select date_add(date,INTERVAL int fromat);
select date_add(curdate(),interval 3 year)
select date_add(now(),interval 3 hour);
select date_add(now(),interval 3 minute);
select date_add(now(),interval 3 second);
select date_add(curdate(),interval -day(curdate())+1 day);
#计算两个日期之间的天数差值 select datediff(bigdate,smalldate);
select datediff(curdate(),'2019-1-1');
#某年的第几天 select dayofyear(date)
select dayofyear(curdate());