# ************************************************************
# Sequel Pro SQL dump
# Version 4541
#
# Host: 127.0.0.1 (MySQL 5.7.15)
# Database: menagerie
# Generation Time: 2019-08-31 04:15:19 +0000
# ************************************************************
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
# Dump of table pet
# ------------------------------------------------------------
DROP TABLE IF EXISTS `pet`;
CREATE TABLE `pet` (
`name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`owner` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`species` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`sex` char(1) CHARACTER SET latin1 DEFAULT NULL,
`birth` date DEFAULT NULL,
`death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `pet` WRITE;
/*!40000 ALTER TABLE `pet` DISABLE KEYS */;
INSERT INTO `pet` (`name`, `owner`, `species`, `sex`, `birth`, `death`)
VALUES
('Fluffy ','Harold ','cat','f','1993-02-04',NULL),
('Claws','Gwen','cat','m','1994-03-17',NULL),
('Buffy ','Harold','dog','f','1989-05-13',NULL),
('Fang','Benny ','dog','m','1990-08-27',NULL),
('Bowser','Diane ','dog','m','1989-08-31','1995-07-29'),
('Chirpy','Gwen','bird','f','1998-09-11',NULL),
('Whistler ','Gwen','bird',NULL,'1997-12-09',NULL),
('Slim','Benny ','snack ','m','1996-04-29',NULL),
('Puffball','Diane','hamster','f','1999-03-30',NULL);
/*!40000 ALTER TABLE `pet` ENABLE KEYS */;
UNLOCK TABLES;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
-- 获取名称,日期,距今日有几年 ,并案名称排序
SELECT name,
`birth`,
Timestampdiff(year, birth, Curdate()) age
FROM pet
order by name;
-- 获取动物名称 ,日期,月份
SELECT name, birth, MONTH(birth) FROM pet;
注意:使用到了 Timestampdiff 函数 , curdate 函数 , YEAR 作为单位