首先我们打开命令提示符 登录MySQL
输入 show databases; 查看一下所有数据库
随后输入 use 表名 ,使用数据库
出现 Database changed 就表示成功了
之后输入create table 表名()括号中输入你的字段与数据类型,
可以使用comment"" 在数据类型后增加注释信息
出现Query OK, 0 rows affected (0.02 sec) 就代表成功了
我们查看一下表的创建信息
之后我们可以尝试修改表名
我们将数据表 stt 改为 std
我们查看一下数据表信息
可以看到表名已经被修改了
随后我们可以修改字段名 和 数据类型
输入 alter table 表名 change 旧name 新name 字段char(5); 就可以修改表的字段 信息
添加字段
输入 alter table 表名 add (新字段)即可添加新字段
在这段代码后加一个first便可将改字段添加到第一个
例如:
输入 alter table 表名 add (新字段)新数据类型 后加 after 便可 将此字段添加到任意字段后
例如:
修改字段位置
输入
alter table 表名 modify 待修改字段名 数据类型 after 字段名:
例如:
删除字段
alter table 表名drop 字段名;
例如:
我们查看一下数据表
最后删除数据表 输入drop table 表名;
表的约束
创建教师表
输入 create table teacher(
-> no char(4) comment"教师号" primary key,
-> name varchar(10) comment"教师姓名" not null,
-> prof varchar(20) comment"职称" not null default"助教",
-> sal int(2) comment"工资"not null,
-> comm smallint(2) comment"岗位津贴"
-> );
查看
创建学生表 student
create table student(
-> no char(4) comment"学生号" primary key,
-> name varchar(10) comment"学生姓名" not null,
-> age tinyint(1) comment"年龄" not null,
-> dept varchar(20) comment"系名" not null default"计算机系"
-> );
查看
创建课程表
create table course(
-> no char(4) comment"课程号" primary key,
-> name varchar(20) comment"课程名" not null unique,
-> class_hours int(2) comment"课时数" default"45"
-> );
Query OK, 0 rows affected, 1 warning (0.03 sec)
查看
创建授课表
输入
create table school_teaching(
-> id int(4) comment"序号" primary key auto_increment,
-> course_no char(4) comment"课程号" not null,
-> teacher_no char(4) comment"教师号" not null,
-> week int(2) comment "周数" default"15",
-> class_num varchar(10) comment"教师号",
-> constraint fk_course_no foreign key (course_no) references course(no),
-> constraint fk_teacher_no foreign key (teacher_no) references teacher(no)
-> );
查看
创建成绩表
输入 create table chengweiqiang_grade(
-> course_no char(4) comment"课程号" not null,
-> student_no char(4) comment"学生号" not null,
-> score float comment"成绩" not null default"60",
-> primary key(course_no,student_no)
-> );
新增数据
如下表新增数据:
指定字段插入数据时若插入值为空时,无需指定该字段名,
- 教师表 teacher
- 指定字段插入数据
教师号 | 教师姓名 | 职称 | 工资 | 岗位津贴 |
T1 | Anne | Professor | 5000 | 2000 |
T2 | Adam | 2000 | ||
T3 | Alan | Associate Professor | 4000 | 1500 |
T4 | Ben | 2000 | 500 | |
T5 | Bob | Lecturer | 3000 |
输入: insert into teacher (no,name,prof,sal,comm)
-> values ("T1","Anne","professor","5000","2000"),
-> ("T2","Adam","0","2000","0"),
-> ("T3","Alan","Associate professor","4000","1500"),
-> ("T4","Ben","0","2000","500"),
-> ("T5","Bob","Lecturer","3000","0");
Query OK, 5 rows affected (0.01 sec)
查看:
-
- 所有字段插入数据
教师号 | 教师姓名 | 职称 | 工资 | 岗位津贴 |
T6 | Brant | Lecturer | 3000 | |
T7 | Bruce | Teaching Assistant | 2000 | 500 |
输入代码:insert into teacher values ("T6","brant","lecturer","3000","1500"),("T7","bruce","teaching Assistant","3000","1000");
Query OK, 2 rows affected (0.01 sec)
查看
-
- 所有字段批量插入数据
教师号 | 教师姓名 | 职称 | 工资 | 岗位津贴 |
T8 | Cary | Associate Professor | 4000 | 1500 |
T9 | Chris | 2000 | ||
T10 | Daniel | Lecturer | 3000 | 1000 |
输入
insert into teacher values
-> ("T8","cary","Associate professor","4000","1500"),
-> ("T9","chris","0","2000","0"),
-> ("T10","daniel","Lecturer","3000","1000");
Query OK, 3 rows affected (0.01 sec)
查看
-
- 指定字段批量插入数据
教师姓名 | 职称 | 工资 | 岗位津贴 | |
T11 | David | Associate Professor | 4000 | |
T12 | Edward | Teaching Assistant | 2000 | |
T13 | Eric | Professor | 5000 |
输入
insert into teacher (no,name,prof,sal,comm)
-> values ("T11","david","Associate professor","4000","0"),
-> ("T12","edward","teaching Assistant","2000","0"),
-> ("T13","eric","professor","5000","0");
Query OK, 3 rows affected (0.01 sec)
查看
-
- 查看教师表中 教师号、教师姓名、职称、工资 字段数据
- 课程表 course
- 指定字段插入数据
课程号 | 课程名 | 课时数 |
C1 | Mysql | 80 |
C2 | cloud computing |
输入
insert into course(no,name,class_hours) values
-> ("c1","mysql","80"),
-> ("c2","cloud computing","0");
Query OK, 2 rows affected (0.01 sec)
查看
-
- 所有字段插入数据
课程号 | 课程名 | 课时数 |
C3 | Professional English | 30 |
C4 | Network foundation |
输入
insert into course values
-> ("c3","Professional English","30"),
-> ("c4","Network foundation","0");
Query OK, 2 rows affected (0.01 sec)
查看
-
- 所有字段批量插入数据
课程号 | 课程名 | 课时数 |
C5 | Flash | 50 |
C6 | marketing |
输入
insert into course values
-> ("c5","flash","50"),
-> ("c6","marketing","0");
查看
-
- 指定字段批量插入数据
课程名 | 课时数 | |
C7 | Situation and Policy | |
C8 | Patriotic education |
输入
insert into course(no,name,class_hours) values
-> ("c7","Situation and Policy","0"),
-> ("c8","Patriotic education","0");
输入
查看
-
- 查看课程表中 课程号、课程名、课时数 字段数据
- 授课表 school_teaching
- 指定字段插入数据
序号 | 课程号 | 教师号 | 周数 | 教室号 |
1 | C1 | T1 | 13 | Y201 |
C2 | T1 | Y202 |
输入
insert into school_teaching(id, course_no,teacher_no,week,class_num) values
-> ("1","c1","t1","13","y201"),
-> ("2","c2","t1","0","y202");
查看
-
- 所有字段插入数据
序号 | 课程号 | 教师号 | 周数 | 教室号 |
C3 | T2 | 16 | J101 | |
4 | C3 | T3 | J302 |
输入
mysql> insert into school_teaching values
-> ("3","c3","t2","16","y101"),
-> ("4","c3","t3","0","y302");
查看
-
- 所有字段批量插入数据
序号 | 课程号 | 教师号 | 周数 | 教室号 |
C4 | T4 | J102 | ||
6 | C5 | T6 | 8 | Y402 |
C5 | T7 | 8 | Y401 |
输入
insert into school_teaching values
-> ("5","c4","t4","0","j102"),
-> ("6","c5","t6","8","y402"),
-> ("7","c5","t7","8","y401");
查看
-
- 指定字段批量插入数据
序号 | 课程号 | 教师号 | 周数 | 教室号 |
C6 | T5 | Y303 | ||
C7 | T8 | J201 | ||
C8 | T9 | Y102 |
输入
insert into school_teaching(id, course_no,teacher_no,week,class_num) values
-> ("8","c6","t5","0","y303"),
-> ("9","c7","t8","0","y201"),
-> ("10","c8","t9","0","y102");
Query OK, 3 rows affected (0.01 sec)
查看
-
- 查看教师表所有数据
备份与恢复
备份所有数据库
输入
mysqldump -u root -p123456 --all-databases>D:\MYSQL\mysql-8.0.28-winx64\suoyou.sql
查询所有库
输入
show databases
备份A库
输入
>mysqldump -u root -p123456 a>D:\MYSQL\mysql-8.0.28-winx64\a.sql
备份B、C库
输入
mysqldump -u root -p123456 --databases b c>D:\MYSQL\mysql-8.0.28-winx64\bc.sql
删除A、B、C库
创建A库
输入: create database A;
Mysql命令还原A库
输入
mysql -uroot -p123456 a<a.sql
Mysql命令还原B、C库
输入
查询所有库
输入
show databases;
备份A库
输入
mysqldump -u root -p123456 a>D:\MYSQL\mysql-8.0.28-winx64\a.sql
备份B、C库
输入
mysqldump -u root -p123456 --databases b c>D:\MYSQL\mysql-8.0.28-winx64\bc.sql
删除A、B、C库
创建并使用A库
Source命令还原A库
Source命令还原B、C库
查询所有库