MySql函数

mysql函数的介绍

1. 聚集函数

• 平均值：avg
• 计算行数：count
• 最大值：max
• 最小值：min
• 总合：sum

2. 用于处理字符串的函数

• 合并字符串函数：concat(str1,str2,str3…)
• 比较字符串大小函数：strcmp(str1,str2)
• 获取字符串字节数函数：length(str)
• 获取字符串字符数函数：char_length(str)
• 字母大小写转换函数：大写：upper(x),ucase(x)；小写lower(x),lcase(x)

3. 用于处理数值的函数

• 绝对值函数：abs(x)
• 向上取整函数：ceil(x)
• 向下取整函数:floor(x)
• 取模函数：mod(x,y)
• 随机数函数：rand()
• 四舍五入函数：round(x,y)
• 数值截取函数：truncate(x,y)

4. 用于处理时间日期的函数

• 获取当前日期：curdate()，current_date()
• 获取当前时间：curtime()，current_time()
• 获取当前日期时间：now()
• 从日期中选择出月份数：month(date),monthname(date)
• 从日期中选择出周数：week(date)
• 从日期中选择出年数：year(date)
• 从时间中选择出小时数：hour(time)
• 从时间中选择出分钟数：minute(time)
• 从时间中选择出秒数：second(time)
• 从时间中选择出今天是周几：weekday(date),dayname(date)

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT 'name',
`age` INT(4) NOT NULL DEFAULT '0' COMMENT 'age',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;


INSERT INTO `students`(`name`,`age`)
VALUES('aa','17');
INSERT INTO `students`(`name`,`age`)
VALUES('bb','17');
INSERT INTO `students`(`name`,`age`)
VALUES('ac','17');
INSERT INTO `students`(`name`,`age`)
VALUES('dd','17');
INSERT INTO `students`(`name`,`age`)
VALUES('ee','17');
INSERT INTO `students`(`name`,`age`)
VALUES('ff','17');

DELETE FROM `students`
WHERE `name`='ff'

UPDATE `students` SET `age`='25'
WHERE `name`='ee'

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`(
`id` INT(4) AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL DEFAULT'匿名',
`stuid` INT(4),
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


SELECT * FROM students AS s
INNER JOIN teacher AS t
ON s.id=t.stuid
WHERE s.id > 0
GROUP BY s.name
HAVING age>=20
ORDER BY s.id DESC
LIMIT 0,10


SELECT * FROM students AS s
LEFT JOIN teacher AS t
ON s.id=t.stuid

SELECT * FROM students AS s
RIGHT JOIN teacher AS t
ON s.id=t.stuid
UPDATE `students` SET `schoolTime`=NOW()

SELECT MONTH(schoolTime)
FROM `students`

SELECT YEAR(schoolTime)
FROM `students`

SELECT DAY(schoolTime)
FROM `students`

SELECT HOUR(schoolTime)
FROM `students`

SELECT MINUTE(schoolTime)
FROM `students`

SELECT SECOND(schoolTime)
FROM `students`

SELECT WEEKDAY(schoolTime)+1
FROM `students`

计算每月新增学生人数

SELECT id,COUNT(*) AS cou
FROM `students`
GROUP BY MONTH(schoolTime)

SELECT s.id AS id1,s1.id id2,s.cou AS sc,s1.cou AS sc1
FROM (SELECT id,COUNT(*) AS cou
FROM `students`
GROUP BY MONTH(schoolTime)) AS s
INNER JOIN (SELECT id,COUNT(*)  AS cou
FROM `students`
GROUP BY MONTH(schoolTime)) AS s1
ON s.id=s1.id-2

SELECT sc-sc1 AS result
FROM(
SELECT s.id AS id1,s1.id id2,s.cou AS sc,s1.cou AS sc1
FROM (SELECT id,COUNT(*) AS cou
FROM `students`
GROUP BY MONTH(schoolTime)) AS s
INNER JOIN (SELECT id,COUNT(*)  AS cou
FROM `students`
GROUP BY MONTH(schoolTime)) AS s1
ON s.id=s1.id-2) AS t