直接取出出生年
select year(sbirthday) from student
算术表达式
通过年龄计算出生日期select sno,2018-sage as 出生日期 from student
去除重复行distinct
找出成绩表中学生名单select distinct(sno) from student
比较运算符=,<,>,<=等
找出年龄小于20 的学生select sno,sage from student where sage<=20
范围运算符:between and,not between and
找出年龄在20和30之间的学生select sno,sage from student where sage between 20 and 30
确定集合:in ,not in
找出数学系和中文系的学生select sno,sdept from student where sdept in('数学系','中文系')
字符匹配:like,not like
找出姓张的学生select sno,sname from student where sname like '张_%' 下划线表示一个字符,%表示0或多个字符
- 空值:is null,is not null
- 逻辑:not,and,or
排序:升序ASC, 降序desc
将学生成绩按降序排列select distinct(sno),grade from sc where grade is not null order by grade desc
count 统计
统计参加考试的人数select count(sno) from sc where grade is not null
sum 总计
统计每个学生的总分select sum(grade) as 总分,sno from sc group by sno
avg 平均值
统计各科成绩的平均分select avg(grade),cno from sc group by cno
max最大值,min最小值
找出每课程成绩的最高分SELECT max(grade),cno FROM sc GROUP BY cno
聚合
找出总分最高的学生select sum(grade),sno from sc where grade is not null group by sno order by sum(grade) desc limit 1
各科成绩的平均值及格的降序排列
select avg(grade),cno from sc group by cno having avg(grade)>=60 order by avg(grade) desc
将每门课程的最高分由高到低排序,并指出是哪个同学
select max(grade),sno from sc group by cno order by max(grade) desc
内置函数
把课程2 的成绩都加上10分updata sc set grade=grade+10 where grade is not null and grade<=90
replace(字符串,要改的字符,改之后的字符)
去掉省份字段的所有省updata student set sprovice=replace(sprovice,'省','')
concat_ws()使用连接符连接其他参数的字符串
把学生和地址一起输出select sname,concat_ws('-',sprovice,scity,sstreet) as 地址 from student
- left(str,n)返回字符串最左边的n个字符
substr(字符串,起始位置,长度)
截取所有人的姓select substr(sname,1,1) from student 截取最后一个字符 select substr(sname,1,char_length(sname)-1) from student
时间函数
CURDATE()
current_date当前日期
current_time当前时间
current_timestamp当前日期加时间
now() 当前日期加时间
unix_timestamp()当前时间戳
把注册时间改为当前时间updata student set regist_time=now()
系统信息函数
version():当前版本
database():当前数据库
charset(str):字符编码
last_insert_id():返回最后一个id加密函数
给所有密码加密updata student set password=sha1(password)
登录查看信息
select * from student where sno=2 and password=sha1('666666')
内置函数
数学函数
- abs(x)
- pi()
- mod(x,y)
- sqrt(x)
- ceil(x)或者ceiling(x)
- rand(),rand(N):返回0-1间的浮点数,使用不同的seed N可以获得不同的随机数
- round(x, D):四舍五入保留D位小数,D默认为0, 可以为负数, 如round(19, -1)返回20
- truncate(x, D):截断至保留D位小数,D可以为负数, 如trancate(19,-1)返回10
- sign(x): 返回x的符号,正负零分别返回1, -1, 0
- pow(x,y)或者power(x,y)
- exp(x):e^x
字符串函数
- char_length(str):返回str所包含的字符数,一个多字节字符算一个字符
- length(str): 返回字符串的字节长度,如utf8中,一个汉字3字节,数字和字母算一个字节
- concat(s1, s1, …): 返回连接参数产生的字符串
- concat_ws(x, s1, s2, …): 使用连接符x连接其他参数产生的字符串
- INSERT(str,pos,len,newstr):返回str,其起始于pos,长度为len的子串被newstr取代。
- 若pos不在str范围内,则返回原字符串str
- 若str中从pos开始的子串不足len,则将从pos开始的剩余字符用newstr取代
- 计算pos时从1开始,若pos=3,则从第3个字符开始替换
- lower(str)或者lcase(str):
- upper(str)或者ucase(str):
- left(s,n):返回字符串s最左边n个字符
- right(s,n): 返回字符串最右边n个字符
- ltrim(s):删除s左侧空格字符
- rtrim(s):
- TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)或TRIM([remstr FROM] str):从str中删除remstr, remstr默认为空白字符
- REPEAT(str,count):返回str重复count次得到的新字符串
- REPLACE(str,from_str,to_str): 将str中的from_str全部替换成to_str
- SPACE(N):返回长度为N的空白字符串
- STRCMP(str1,str2):若str1和str2相同,返回0, 若str1小于str2, 返回-1, 否则返回1.
- SUBSTRING(str,pos), SUBSTRING(str FROM pos), 1. SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),MID(str,pos,len): 获取特定位置,特定长度的子字符串
日期函数
- CURDATE(), CURRENT_DATE, CURRENT_DATE():用于获取当前日期,格式为’YYYY-MM-DD’
- CURTIME([fsp]), CURRENT_TIME, CURRENT_TIME([fsp]): 用于获取当前时间, 格式为’HH:MM:SS’
- CURRENT_TIMESTAMP, CURRENT_TIMESTAMP([fsp]), LOCALTIME, LOCALTIME([fsp]), SYSDATE([fsp]), NOW([fsp]): 用于获取当前的时间日期,格式为’YYYY-MM-DD HH:MM:SS’
- UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date):返回一个unix时间戳(’1970-01-01 00:00:00’ UTC至今或者date的秒数),这实际上是从字符串到整数的一个转化过程
FROM_UNIXTIME(UNIX_TIMESTAMP(‘2010-3-3’))从时间戳返回日期
提取时间
MONTH(date) MONTHNAME(date) DAYNAME(date) DAY(date),DAYOFMONTH(date):1-31或者0 DAYOFWEEK(date):1-7==>星期天-星期六 DAYOFYEAR(date): 1-365(366) WEEK(date[,mode]):判断是一年的第几周,如果1-1所在周在新的一年多于4天,则将其定为第一周;否则将其定为上一年的最后一周。mode是用来人为定义一周从星期几开始。 WEEKOFYEAR(date):类似week(date,3),从周一开始计算一周。 QUARTER(date):返回1-4 HOUR(time):返回时间中的小时数,可以大于24 MINUTE(time): SECOND(time):
系统信息函数
- VERSION():返回mysql服务器的版本,是utf8编码的字符串
- DATABASE(),SCHEMA():显示当前使用的数据库
- SESSION_USER(), SYSTEM_USER(), USER(), CURRENT_USER, CURRENT_USER():返回当前的用户名@主机,utf8编码字符串
- CHARSET(‘hello’) 字符编码
- COLLATION(str) 字符排序规则
- LAST_INSERT_ID():自动返回最后一个insert或者update查询, 为auto_increment列设置的第一个发生的值
加密函数
- password()
- MD5(str):计算MD5 128位校验和,返回32位16进制数构成的字符串,当str为NULL时返回NULL。可以用作哈希密码
- SHA1(str), SHA(str):计算160位校验和,返回40位16进制数构成的字符串,当str为NULL时返回NULL。
- SHA2(str, hash_length):计算SHA-2系列的哈希方法(SHA-224, SHA-256, SHA-384, and SHA-512). 第一个参数为待校验字符串,第二个参数为结果的位数(224, 256, 384, 512)