练习（一）

1、创建一个HelloSchool数据库，

CREATE DATABASE IF NOT EXISTS HelloSchool;

2、去使用HelloSchool数据库

USE helloschool;

3、创建学生表和分数表之前，初始化表，

DROP TABLE IF EXISTS sutdent;
CREATE TABLE `sutdent`(
	`Id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`Name` VARCHAR(20) NOT NULL COMMENT '姓名',
	`Sex` VARCHAR(4) COMMENT '性别',
	`Birth` YEAR COMMENT '出生年份',
	`Department` VARCHAR(20) NOT NULL COMMENT '院系',
	`Address` VARCHAR(50) COMMENT '家庭住址',
	PRIMARY KEY(`Id`)
)ENGINE=INNODB CHARSET=utf8 COMMENT='学生表';

DROP TABLE IF EXISTS score;
CREATE TABLE `score`(
	`Id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
	`Stu_id` INT(10) NOT NULL COMMENT '学号',
	`C_name` VARCHAR(20) COMMENT '课程名',
	`Grade` INT(10) COMMENT '分数',
	PRIMARY KEY(`Id`)
)ENGINE=INNODB CHARSET=utf8 COMMENT='分数表';

# 将Sutdent表改名为Student表
ALTER TABLE Sutdent RENAME AS Student;

在Score表中添加字段 Time

ALTER TABLE Score ADD `Time` DATE COMMENT '时间';

将Score表的字段 C_name 改名为 Class_name，长度变成30

ALTER TABLE Score CHANGE `C_name` `Class_name` VARCHAR(30) COMMENT '课程名称';

为student表和score表增加记录

INSERT INTO `student` 
(`Id`,`Name`,`Sex`,`Birth`,`Department`,`Address`)
VALUES
(901,' Spider-Man ', 'boy',1995,'Computer', 'NewYork'),
(902,' Iron Man ', 'boy',1996,'Chinese', 'NewYork'),
(903,' Captain America ', 'girl',2000,'Chinese', ' America '),
(904,' Black Widow ', 'girl',2000,'English ', ' Russian '),
(905,' Hulk ', 'girl',2001,'English ', ' Brasil '),
(906,' Thor ', 'boy',2098,'Computer', ' Australia ');

INSERT INTO `score`
(`Stu_id`,`Class_name`,`Grade`,`Time`)
VALUES
(901,'Computer',98,'2020-10-01'),
(901,'English',80,'2020-10-01'),
(902,'Computer',65,'2020-10-01'),
(902,'Chinese',88,'2020-10-01'),
(903,'Chinese',95,'2020-10-01'),
(904,'Computer',70,'2020-10-01'),
(904,'English',92,'2020-10-01'),
(905,'English',94,'2020-10-01'),
(906,'Computer',90,'2020-10-01'),
(906,'English',85,'2020-10-01');

查询student表的所有记录

SELECT * FROM `student`;

将Thor的出生日期改为1998

UPDATE `student` SET `Birth`=1998 WHERE `Id`=906;

从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

SELECT `Id`,`name`,`department` FROM `student`;

从student表中查询计算机系(Computer)和英语系(English)的学生的信息

SELECT * FROM `student` WHERE `Department`='Computer' OR `Department`='English';