1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| DROP DATABASE IF EXISTS `school`;
CREATE DATABASE `school`;
USE `school`;
CREATE TABLE `classes`( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) )CHARSET=utf8; CREATE TABLE `student`( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `age` INT NOT NULL, `gender` VARCHAR(2) NOT NULL, `telephone` VARCHAR(20) NOT NULL, `email` VARCHAR(50) NOT NULL, `classId` INT )CHARSET=utf8;
INSERT INTO classes VALUES(DEFAULT,'one'),(DEFAULT,'two'),(DEFAULT,'three'); INSERT INTO student VALUES(DEFAULT,'张三',14,'男','10086','admin@icloud.com',1); INSERT INTO student VALUES(DEFAULT,'李四',15,'女','10086','admin@icloud.com',2); INSERT INTO student VALUES(DEFAULT,'王五',16,'男','10086','admin@icloud.com',3);
SELECT * FROM `classes`; SELECT s.*,c.`id` 'classes.id',c.`name` 'classes.name' FROM `student` s,`classes` c WHERE s.`classId`=c.`id`;
|