Mysql 聚合函数资料

    SQL 脚本：

    1. --
    2. -- Database: `movies`
    3. --
    5. -- --------------------------------------------------------
    7. --
    8. -- Table structure for table `genres`
    9. --
    11. DROP TABLE IF EXISTS `genres`;
    12. CREATE TABLE `genres` (
    13.   `genre_id` int(11) NOT NULL,
    14.   `genre_title` varchar(30) NOT NULL
    15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    17. --
    18. -- Dumping data for table `genres`
    19. --
    21. INSERT INTO `genres` (`genre_id`, `genre_title`) VALUES
    22. (1, 'Fantasy'),
    23. (2, 'Sci-Fi'),
    24. (3, 'Action'),
    25. (4, 'Comedy'),
    26. (5, 'Drama'),
    27. (6, 'Horror'),
    28. (7, 'Romance'),
    29. (8, 'Family');
    31. -- --------------------------------------------------------
    33. --
    34. -- Table structure for table `movies`
    35. --
    37. DROP TABLE IF EXISTS `movies`;
    38. CREATE TABLE `movies` (
    39.   `movie_id` int(11) NOT NULL,
    40.   `movie_title` varchar(100) NOT NULL,
    41.   `director` varchar(50) NOT NULL,
    42.   `year` year(4) NOT NULL,
    43.   `genre_id` int(11) NOT NULL
    44. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    46. --
    47. -- Dumping data for table `movies`
    48. --
    50. INSERT INTO `movies` (`movie_id`, `movie_title`, `director`, `year`, `genre_id`) VALUES
    51. (1, 'Labyrinth', 'Jim Henson', 1986, 1),
    52. (2, 'Highlander', 'Russell Mulcahy', 1986, 1),
    53. (3, 'Alien', 'Ridley Scott', 1979, 2),
    54. (4, 'Conan the Barbarian', 'John Milius', 1982, 1),
    55. (5, 'The Hobbit: An Unexpected Journey', 'Peter Jackson', 2012, 1),
    56. (6, 'The Dark Crystal', 'Jim Henson', 1982, 1),
    57. (7, 'Star Wars: A New Hope', 'George Lucas', 1977, 2),
    58. (8, 'Harry Potter and the Order of the Phoenix', 'David Yates', 2007, 1),
    59. (9, 'Fantastic Beasts and Where to Find Them ', 'David Yates', 2016, 1),
    60. (10, 'Excalibur', 'John Boorman', 1981, 1),
    61. (11, 'Time Bandits', 'Terry Gilliam', 1981, 1),
    62. (12, 'Pan\'s Labyrinth', 'Guillermo Del Toro', 2006, 1),
    63. (13, 'Blade Runner', 'Ridley Scott', 1982, 2),
    64. (14, 'Interstellar', 'Christopher Nolan', 2014, 2),
    65. (15, 'A.I. Artificial Intelligence', 'Steven Spielberg', 2001, 2),
    66. (16, 'The Matrix', 'The Wachowskis', 1999, 2),
    67. (17, 'Gattaca', 'Andrew Niccol', 1997, 2),
    68. (18, 'Avatar', 'James Cameron', 2009, 2),
    69. (19, 'Moon', 'Duncan Jones', 2009, 2),
    70. (20, 'Galaxy Quest', 'Dean Parisot', 1999, 2),
    71. (21, 'The Fifth Element', 'Luc Besson', 1997, 2),
    72. (22, 'Inception', 'Christopher Nolan', 2010, 2),
    73. (23, 'District 9', 'Neill Blokamp', 2009, 2),
    74. (24, 'Her', 'Spike Jonez', 2013, 2),
    75. (25, 'Pulp Fiction', 'Quentin Tarantino', 1994, 5),
    76. (26, 'Reservoir Dogs', 'Quentin Tarantino', 1992, 5),
    77. (27, 'Transcendence', 'Wally Pfister', 2014, 2),
    78. (28, 'Contact', 'Robert Zemekis', 1997, 2);
    80. -- --------------------------------------------------------
    82. --
    83. -- Table structure for table `people`
    84. --
    86. DROP TABLE IF EXISTS `people`;
    87. CREATE TABLE `people` (
    88.   `email` varchar(100) NOT NULL,
    89.   `first_name` varchar(30) NOT NULL,
    90.   `last_name` varchar(30) NOT NULL,
    91.   `common_name` varchar(40) NOT NULL
    92. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    94. --
    95. -- Dumping data for table `people`
    96. --
    98. INSERT INTO `people` (`email`, `first_name`, `last_name`, `common_name`) VALUES
    99. ('arya@stark.org', 'Arya', 'Stark', ''),
    100. ('brandon@stark.org', 'Brandon', 'Stark', 'Bran'),
    101. ('bronn@blackwater.info', 'Bronn', 'Blackwater', ''),
    102. ('cersei@lannister.com', 'Cersei', 'Lannister', ''),
    103. ('daenerys@dragonstone.org', 'Daenerys', 'Targaryen', ''),
    104. ('davos@seaworth.org', 'Davos', 'Seaworth', 'The Onion Knight'),
    105. ('gregor@clegane.org', 'Gregor', 'Clegane', ''),
    106. ('jaime@lannister.org', 'Jaime', 'Lannister', ''),
    107. ('jon@snow.org', 'Jon', 'Snow', 'King Jon the First'),
    108. ('samwell@nights-watch.org', 'Samwell', 'Tarley', ''),
    109. ('sandor@clegane.org', 'Sandor', 'Clegane', ''),
    110. ('sansa@stark.org', 'Sansa', 'Stark-Bolton', '');
    112. --
    113. -- Indexes for dumped tables
    114. --
    116. --
    117. -- Indexes for table `genres`
    118. --
    119. ALTER TABLE `genres`
    120. ADD PRIMARY KEY (`genre_id`);
    122. --
    123. -- Indexes for table `movies`
    124. --
    125. ALTER TABLE `movies`
    126. ADD PRIMARY KEY (`movie_id`);
    128. --
    129. -- Indexes for table `people`
    130. --
    131. ALTER TABLE `people`
    132. ADD PRIMARY KEY (`email`);
    134. --
    135. -- AUTO_INCREMENT for dumped tables
    136. --
    138. --
    139. -- AUTO_INCREMENT for table `genres`
    140. --
    141. ALTER TABLE `genres`
    142. MODIFY `genre_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
    144. --
    145. -- AUTO_INCREMENT for table `movies`
    146. --
    147. ALTER TABLE `movies`
    148.   MODIFY `movie_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;