1. 创建数据库
mysql> create database stuworkdata character set utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
2. 使用数据库
mysql> use stuworkdata;
Database changed
3. 创建年级表
mysql> create table grade(
-> id bigint auto_increment comment '主键',
-> grade_name varchar(10) not null comment '年级名称',
-> primary key(id)
-> )comment '年级表';
Query OK, 0 rows affected (0.02 sec)
4. 创建科目表(对应年级)
mysql> create table subject(
-> id bigint auto_increment comment '主键',
-> subject_name varchar(10) not null comment '科目名称',
-> grade_id bigint not null comment '年级ID',
-> primary key(id)
-> )comment '科目表';
Query OK, 0 rows affected (0.02 sec)
5. 创建学生表(对应年级)
mysql> create table student(
-> id bigint auto_increment comment '主键',
-> student_name varchar(10) not null comment '学生姓名',
-> grade_id bigint not null comment '年级ID',
-> age int default 18 comment '年龄',
-> primary key(id)
-> )comment '学生表';
Query OK, 0 rows affected (0.02 sec)
6. 创建成绩表(对应学生和科目)
mysql> create table scores(
-> id bigint auto_increment comment '主键',
-> score int not null comment '成绩',
-> student_id bigint not null comment '学生ID',
-> subject_id bigint not null comment '科目ID',
-> primary key(id)
-> )comment '成绩表';
Query OK, 0 rows affected (0.02 sec)
7. 修改学生表,添加一个生日字段
mysql> alter table student add birthday date comment '出生日期';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
8. 增加5条学生数据
mysql> insert into student values
-> (0,'熊大',1,default,'2000-1-1'),
-> (0,'熊二',2,default,'2000-1-2'),
-> (0,'张三',3,default,'2000-1-3'),
-> (0,'李四',4,default,'2000-1-4'),
-> (0,'王五',5,default,'2000-1-5');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
9. 修改名字为张三的学生的年龄
mysql> update student set age = 21
-> where student_name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select*from student;
+----+--------------+----------+------+------------+
| id | student_name | grade_id | age | birthday |
+----+--------------+----------+------+------------+
| 1 | 熊大 | 1 | 18 | 2000-01-01 |
| 2 | 熊二 | 2 | 18 | 2000-01-02 |
| 3 | 张三 | 3 | 21 | 2000-01-03 |
| 4 | 李四 | 4 | 18 | 2000-01-04 |
| 5 | 王五 | 5 | 18 | 2000-01-05 |
+----+--------------+----------+------+------------+
5 rows in set (0.00 sec)