博客

准备

-- 创建一个school数据库
CREATE DATABASE
IF
	NOT EXISTS `school`;
	
USE `school`;

-- 创建学生表
DROP TABLE
IF
	EXISTS `student`;
	
CREATE TABLE `student` (
	`studentno` INT ( 4 ) NOT NULL COMMENT '学号',
	`loginpwd` VARCHAR ( 20 ) DEFAULT NULL,
	`studentname` VARCHAR ( 20 ) DEFAULT NULL COMMENT '学生姓名',
	`sex` TINYINT ( 1 ) DEFAULT NULL COMMENT '性别， 0或1',
	`gradeid` INT ( 11 ) DEFAULT NULL COMMENT '年级编号',
	`phone` VARCHAR ( 50 ) NOT NULL COMMENT '联系电话，允许为空',
	`address` VARCHAR ( 255 ) NOT NULL COMMENT '地址，允许为空',
	`borndate` datetime DEFAULT NULL COMMENT '出生时间',
	`email` VARCHAR ( 50 ) NOT NULL COMMENT '邮箱账号允许为空',
	`identitycard` VARCHAR ( 18 ) DEFAULT NULL COMMENT '身份证号',
	PRIMARY KEY ( `studentno` ),
	UNIQUE KEY `identitycard` ( `identitycard` ),
	KEY `email` ( `email` ) 
) ENGINE = myisam DEFAULT charset = utf8;

-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` ( `studentno`, `loginpwd`, `studentname`, `sex`, `gradeid`, `phone`, `address`, `borndate`, `email`, `identitycard` )
VALUES
	( 1000, '123456', '张伟', 0, 2, '13800001234', '北京朝阳', '1980-1-1', 'text123@qq.com', '123456198001011234' ),
	( 1001, '123456', '赵强', 1, 3, '13800002222', '广东深圳', '1990-1-1', 'text111@qq.com', '123456199001011233' );

-- 创建年级表
DROP TABLE
IF
	EXISTS `grade`;
	
CREATE TABLE `grade` ( 
	`gradeid` INT ( 11 ) NOT NULL auto_increment COMMENT '年级编号',
	`gradename` VARCHAR ( 50 ) NOT NULL COMMENT '年级名称',
	 PRIMARY KEY ( `gradeid` ) 
	 ) ENGINE = INNODB auto_increment = 6 DEFAULT charset = utf8;
	 
-- 插入年级数据
INSERT INTO `grade` ( `gradeid`, `gradename` )
VALUES
	(1,'大一'),
	(2,'大二'),
	(3,'大三'),
	(4,'大四'),
	(5,'预科班');
	 
-- 创建科目表
DROP TABLE
IF
	EXISTS `subject`;
	
CREATE TABLE `subject` (
	`subjectno` INT ( 11 ) NOT NULL auto_increment COMMENT '课程编号',
	`subjectname` VARCHAR ( 50 ) DEFAULT NULL COMMENT '课程名称',
	`classhour` INT ( 4 ) DEFAULT NULL COMMENT '学时',
	`gradeid` INT ( 4 ) DEFAULT NULL COMMENT '年级编号',
	PRIMARY KEY ( `subjectno` ) 
) ENGINE = INNODB auto_increment = 19 DEFAULT charset = utf8;

-- 插入科目数据
INSERT INTO `subject` ( `subjectno`, `subjectname`, `classhour`, `gradeid` )
VALUES
	( 1, '高等数学-1', 110, 1 ),
	( 2, '高等数学-2', 110, 2 ),
	( 3, '高等数学-3', 100, 3 ),
	( 4, '高等数学-4', 130, 4 ),
	( 5, 'C语言-1', 110, 1 ),
	( 6, 'C语言-2', 110, 2 ),
	( 7, 'C语言-3', 100, 3 ),
	( 8, 'C语言-4', 130, 4 ),
	( 9, 'Java程序设计-1', 110, 1 ),
	( 10, 'Java程序设计-2', 110, 2 ),
	( 11, 'Java程序设计-3', 100, 3 ),
	( 12, 'Java程序设计-4', 130, 4 ),
	( 13, '数据库结构-1', 110, 1 ),
	( 14, '数据库结构-2', 110, 2 ),
	( 15, '数据库结构-3', 100, 3 ),
	( 16, '数据库结构-4', 130, 4 ),
	( 17, 'C#基础', 130, 1 );

-- 创建成绩表
DROP TABLE
IF
	EXISTS `result`;
	
CREATE TABLE `result` (
	`studentno` INT ( 4 ) NOT NULL COMMENT '学号',
	`subjectno` INT ( 4 ) NOT NULL COMMENT '课程编号',
	`examdate` datetime NOT NULL COMMENT '考试日期',
	`studentresult` INT ( 4 ) NOT NULL COMMENT '考试成绩',
KEY `subjectno` ( `subjectno` ) 
) ENGINE = INNODB DEFAULT charset = utf8;

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result` (
	`studentno`,
	`subjectno`,
	`examdate`,
`studentresult`)
VALUES 
( 1000, 1, '2013-11-11 16:00:00', 85 ),
( 1000, 2, '2013-11-12 16:00:00', 70 ),
( 1000, 3, '2013-11-11 09:00:00', 68 ),
( 1000, 4, '2013-11-13 16:00:00', 98 ),
( 1000, 5, '2013-11-14 16:00:00', 58 );

查询

查询

-- 查询 
-- SELECT 字段 FROM 表;
SELECT
	* 
FROM
	student;
	
-- 查询指定字段
SELECT
	`studentno`,
	`studentname` 
FROM
	`student`;

别名(AS)

-- 别名
-- 字段和表都可以起别名
SELECT
	`studentno` AS 学号,
	`studentname` AS 学生姓名 
FROM
	`student` AS 学生表;

字符串拼接函数(CONCAT)

	
-- 字符串拼接函数
SELECT
	CONCAT( '姓名:', studentname ) AS 新名字 
FROM
	student

去重(DISTINCT)

-- 去重
SELECT DISTINCT
	`studentno` 
FROM
	result;