数据库表的基本操作

# 创建数据库 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`