数据库表的基本操作

# 创建数据库
CREATE DATABASE studyMysql;
# DROP DATABASE studyMysql;

# 进入数据库
USE studyMysql;

# 创建班级信息表
CREATE TABLE class_info(
`id` INT PRIMARY KEY UNIQUE NOT NULL AUTO_INCREMENT,    # 班级id
`name` VARCHAR(20) NOT NULL    # 班级名字
);

# 删除班级信息表
# DROP TABLE class_info

# 创建学生信息表
CREATE TABLE student_info(
`id` INT PRIMARY KEY UNIQUE NOT NULL AUTO_INCREMENT,    # 学生id
`name` VARCHAR(20) NOT NULL,    # 学生姓名
`gender` VARCHAR(20) NOT NULL,    # 性别
`age` INT NOT NULL,        # 年龄
`heigent` INT NOT NULL,        # 身高
`weigent` INT NOT NULL,        # 体重
`c_id` INT NOT NULL        # 班级id
);

# 删除学生信息表
# DROP TABLE student_info

# 创建学生课程分数表
CREATE TABLE f_info(
`id` INT PRIMARY KEY UNIQUE NOT NULL AUTO_INCREMENT,    # 科目分数id
`name` VARCHAR(20) NOT NULL,    # 课程名字
`fnumber` INT NOT NULL,        # 分数
`s_id` INT             # 学生id
);

# 删除学生信息表
# DROP TABLE f_info

# 查看当前数据库表名
SHOW TABLES;

# 查询班级信息表数据
SELECT * FROM class_info;
# 添加班级信息表数据
INSERT INTO class_info VALUES
(NULL,'python一班'),
(NULL,'python二班'),
(NULL,'java一班'),
(NULL,'java二班');
# 删除班级信息表数据
# delete from class_info;

# 查询学生信息表数据
SELECT * FROM student_info;
# 添加班级信息数据
INSERT INTO student_info VALUES
(NULL,'jack','man','17','170','110','1'),
(NULL,'alex','woman','16','165','100','1'),
(NULL,'mak','man','19','180','132','2'),
(NULL,'vat','man','18','185','131','2'),
(NULL,'jian','woman','20','170','110','3');

# 查询分数信息表数据
SELECT * FROM f_info;
# 添加分数信息表数据
INSERT INTO f_info VALUES
(NULL,'语文','60','1'),
(NULL,'数学','70','1'),
(NULL,'英语','80','1'),
(NULL,'语文','56','2'),
(NULL,'数学','58','2'),
(NULL,'英语','60','2'),
(NULL,'语文','53','3'),
(NULL,'数学','70','3'),
(NULL,'英语','73','3'),
(NULL,'语文','33','4'),
(NULL,'数学','44','4'),
(NULL,'英语','55','4'),
(NULL,'语文','55','5'),
(NULL,'数学','77','5'),
(NULL,'英语','88','5'),
(NULL,'语文','99','6'),
(NULL,'数学','100','6'),
(NULL,'英语','73','6');

# 将名字为jack的同学身高修改为165
UPDATE student_info SET `heigent` = 165 WHERE `name` = 'jack';

# 查询数据
SELECT * FROM class_info;
SELECT * FROM student_info;
SELECT * FROM f_info;
# 多表联查
SELECT * FROM class_info LEFT JOIN student_info
ON class_info.`id` = student_info.`c_id`
LEFT JOIN f_info
ON student_info.`id`= f_info.`s_id`