function

--  According to ID number   Determine gender 
SELECT MOD(SUBSTR(`idcard`,17,1),2) as 'gender' FROM `student`;
SELECT IF(MOD(SUBSTR(`idcard`,17,1),2),' Spirit boy ',' Grilled garlic girl ') as 'gender' FROM `student`;

--  According to ID number   Find out all male information 
SELECT * FROM `student`  WHERE MOD(SUBSTR(`idcard`,17,1),2) = 1;

--  According to ID number   Calculate the number of men and women 
SELECT IF(MOD(SUBSTR(`idcard`,17,1),2),' Spirit boy ',' Grilled garlic girl ') as 'gender',COUNT(*) AS `count` FROM `student` GROUP BY `gender`;

--  According to ID number   Calculate installation birthday sorting 

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`;

--  According to ID number   Calculate the age 
SELECT DATE_FORMAT(SUBSTR(`idcard`,7,8),'%Y year %m month %d Japan ') 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`;
--  If the current date month   Greater than   Date of birth 
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;

View

--  When querying a student, you can also query the student's professional grades 
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 
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`;

--  View query 
SELECT * FROM `stu_info`

--  Modify view data 
UPDATE `stu_info` SET `s_name` = ' Zhang Sanfeng ' WHERE `s_id` = 8;
--  Check and modify the view 
SELECT * FROM `stu_info` WHERE `s_id` = 8;
--  Check the original data table after modifying the view 
SELECT * FROM `student` WHERE `s_id` = 8;

--  Modify the original data table 
UPDATE `stu_info` SET `s_name` = ' Zhang San ' WHERE `s_id` = 8;
--  Check the modified original data sheet 
SELECT * FROM `student` WHERE `s_id` = 8;
--  Check the view after modifying the source data table 
SELECT * FROM `stu_info` WHERE `s_id` = 8;