函数

-- 根据身份证号 判断性别
SELECT MOD(SUBSTR(`idcard`,17,1),2) as gender FROM `student`;
SELECT IF(MOD(SUBSTR(`idcard`,17,1),2),精神小伙儿,扒蒜老妹儿) as gender FROM `student`;

-- 根据身份证号 找出所有男性信息
SELECT * FROM `student`  WHERE MOD(SUBSTR(`idcard`,17,1),2) = 1;

-- 根据身份证号 计算男性人数和女性人数
SELECT IF(MOD(SUBSTR(`idcard`,17,1),2),精神小伙儿,扒蒜老妹儿) as gender,COUNT(*) AS `count` FROM `student` GROUP BY `gender`;

-- 根据身份证号 计算安装生日排序

SELECT UNIX_TIMESTAMP(SUBSTR(`idcard`,7,8)) as birthday FROM `student` ORDER BY `birthday`;
SELECT DATE(SUBSTR(`idcard`,7,8)) as birthday FROM `student` ORDER BY `birthday`;

-- 根据身份证号 计算年龄
SELECT DATE_FORMAT(SUBSTR(`idcard`,7,8),%Y年%m月%d日) as birthday FROM `student` ORDER BY `birthday`;
SELECT YAER(DATE(SUBSTR(`idcard`,7,8))) as birthday FROM `student` ORDER BY `birthday`;
SELECT MONTH(DATE(SUBSTR(`idcard`,7,8))) as birthday FROM `student` ORDER BY `birthday`;
SELECT DAY(DATE(SUBSTR(`idcard`,7,8))),(DATE(SUBSTR(`idcard`,7,8))) as birthday FROM `student` ORDER BY `birthday`;
-- 如果当前日期月份 大于 出生日期
SELECT IF ((MONTH(NOW())- MONTH(DATE(SUBSTR(`idcard`,7,8)))) > 0,
	YEAR(NOW())- YEAR(DATE(SUBSTR(`idcard`,7,8))),
		IF ((MONTH(NOW())- MONTH(DATE(SUBSTR(`idcard`,7,8)))) < 0,
			YEAR(NOW())- YEAR(DATE(SUBSTR(`idcard`,7,8))) - 1,
			IF((DAY(NOW())- DAY(DATE(SUBSTR(`idcard`,7,8)))) > 0,
				YEAR(NOW())- YEAR(DATE(SUBSTR(`idcard`,7,8))),
				YEAR(NOW())- YEAR(DATE(SUBSTR(`idcard`,7,8))) - 1
			)
		)
) FROM student;

视图

-- 查询某学生的时候同时查询该学生个专业成绩
SELECT * FROM `student` WHERE `s_id` = 3;
SELECT `s`.`s_name`,``.`c_name`,`sc`.`score` FROM `student` s INNER JOIN `student_course` sc INNER JOIN `course` c
 ON `s`.`s_id` = `sc`.`s_id` AND `sc`.`c_id` = `c`.`c_id` AND `s`.`s_id` = 3 ;

-- 创建视图
CREATE VIEW `stu_info` AS SELECT `s`.`s_id`,`s`.`s_name`,``.`c_name`,`sc`.`score` FROM `student` s INNER JOIN `student_course` sc INNER JOIN `course` c ON `s`.`s_id` = `sc`.`s_id` AND `sc`.`c_id` = `c`.`c_id`;

-- 视图查询
SELECT * FROM `stu_info`

-- 修改视图数据
UPDATE `stu_info` SET `s_name` = 张三丰 WHERE `s_id` = 8;
-- 检查修改视图
SELECT * FROM `stu_info` WHERE `s_id` = 8;
-- 检查修改视图后的原数据表
SELECT * FROM `student` WHERE `s_id` = 8;

-- 修改原数据表
UPDATE `stu_info` SET `s_name` = 张三 WHERE `s_id` = 8;
-- 检查修改后原数据表
SELECT * FROM `student` WHERE `s_id` = 8;
-- 检查修改源数据表后的视图
SELECT * FROM `stu_info` WHERE `s_id` = 8;