0
0

openGauss数据库开发指导手册（下）

本篇将介绍学校数据模型以及学校数据模型表操作。

1.4 学校数据模型

1.4.1 关于本实验

以学校数据库模型为例，介绍openGauss数据库数据库、表、表空间、用户及其它对象，以及SQL语法使用的介绍。
假设A市B学校为了加强对学校的管理，引入了华为openGauss数据库。在B学校里，主要涉及的对象有学生、教师、班级、院系和课程。本实验假设在B学校数据库中，教师会教授课程，学生会选修课程，院系会聘请教师，班级会组成院系，学生会组成班级。因此，根据此关系，本实验给出了相应的关系模式和ER图，并对其进行基本的数据库操作。

1.4.2 关系模型

对于B校中的5个对象，分别建立属于每个对象的属性集合，具体属性描述如下：
● 学生（学号，姓名，性别，出生日期，入学日期，家庭住址）
● 教师（教师编号，教师姓名，职称，性别，年龄，入职日期）
● 班级（班级编号，班级名称，班主任）
● 院系（系编号，系名称，系主任）
● 课程（课程编号，课程名称，课程类型，学分）

上述属性对应的编号为：
● student（std_id，std_name，std_sex，std_birth，std_in，std_address）
● teacher（tec_id，tec_name，tec_job，tec_sex，tec_age，tec_in）
● class（cla_id，cla_name，cla_teacher）
●school_department（depart_id，depart_name，depart_teacher）
● course（cor_id，cor_name，cor_type，credit）

对象之间的关系：
● 一位学生可以选择多门课程，一门课程可被多名学生选择
● 一位老师可以选择多门课程，一门课程可被多名老师教授
● 一个院系可由多个班级组成
● 一个院系可聘请多名老师
● 一个班级可由多名学生组成

1.4.3 E-R图

在这里插入图片描述

图1-1 E-R图

1.5 学校数据模型表操作

1.5.1 表的创建

根据B学校的场景描述，本实验分别针对学生（student），教师（teacher），班级（class），院系（school_department）和课程（course）创建相应的表。具体的实验步骤如下所示：

步骤 1 创建学生信息表。

  1. DROP TABLE IF EXISTS student;
  2. CREATE TABLE student
  3. (
  4.         std_id INT PRIMARY KEY,
  5.         std_name NCHAR(20) NOT NULL,
  6.         std_sex NCHAR(6),
  7.         std_birth DATE,
  8.         std_in DATE NOT NULL,
  9.         std_address VARCHAR(100));

步骤 2 创建教师信息表。

  1. DROP TABLE IF EXISTS teacher;
  2. CREATE TABLE teacher
  3. (
  4.         tec_id INT PRIMARY KEY,
  5.         tec_name CHAR(20) NOT NULL,
  6.         tec_job CHAR(15),
  7.         tec_sex CHAR(6),
  8.         tec_age INT,
  9.         tec_in DATE NOT NULL
  10. );

步骤 3 创建班级信息表。

  1. DROP TABLE IF EXISTS class;
  2. CREATE TABLE class
  3. (
  4.         cla_id INT PRIMARY KEY,
  5.         cla_name CHAR(20) NOT NULL,
  6.         cla_teacher INT NOT NULL
  7. );

步骤 4 创建院系信息表。

  1. DROP TABLE IF EXISTS school_department;
  2. CREATE TABLE school_department
  3. (
  4.         depart_id INT PRIMARY KEY,
  5.         depart_name NCHAR(30) NOT NULL,
  6.         depart_teacher INT NOT NULL
  7. );

步骤 5 创建课程信息表。

  1. DROP TABLE IF EXISTS course;
  2. CREATE TABLE course
  3. (
  4.         cor_id INT PRIMARY KEY,
  5.         cor_name NCHAR(30) NOT NULL,
  6.         cor_type NCHAR(20),
  7.         credit numeric
  8. );

1.5.2 表数据的插入

步骤 1 向student表中插入数据。

  1. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (1,'张一','男','1993-01-01','2011-09-01','江苏省南京市雨花台区');
  2. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (2,'张二','男','1993-01-02','2011-09-01','江苏省南京市雨花台区');
  3. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (3,'张三','男','1993-01-03','2011-09-01','江苏省南京市雨花台区');
  4. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (4,'张四','男','1993-01-04','2011-09-01','江苏省南京市雨花台区');
  5. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (5,'张五','男','1993-01-05','2011-09-01','江苏省南京市雨花台区');
  6. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (6,'张六','男','1993-01-06','2011-09-01','江苏省南京市雨花台区');
  7. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (7,'张七','男','1993-01-07','2011-09-01','江苏省南京市雨花台区');
  8. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (8,'张八','男','1993-01-08','2011-09-01','江苏省南京市雨花台区');
  9. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (9,'张九','男','1993-01-09','2011-09-01','江苏省南京市雨花台区');
  10. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (10,'李一','男','1993-01-10','2011-09-01','江苏省南京市雨花台区');
  11. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (11,'李二','男','1993-01-11','2011-09-01','江苏省南京市雨花台区');
  12. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (12,'李三','男','1993-01-12','2011-09-01','江苏省南京市雨花台区');
  13. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (13,'李四','男','1993-01-13','2011-09-01','江苏省南京市雨花台区');
  14. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (14,'李五','男','1993-01-14','2011-09-01','江苏省南京市雨花台区');
  15. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (15,'李六','男','1993-01-15','2011-09-01','江苏省南京市雨花台区');
  16. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (16,'李七','男','1993-01-16','2011-09-01','江苏省南京市雨花台区');
  17. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (17,'李八','男','1993-01-17','2011-09-01','江苏省南京市雨花台区');
  18. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (18,'李九','男','1993-01-18','2011-09-01','江苏省南京市雨花台区');
  19. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (19,'王一','男','1993-01-19','2011-09-01','江苏省南京市雨花台区');
  20. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (20,'王二','男','1993-01-20','2011-09-01','江苏省南京市雨花台区');
  21. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (21,'王三','男','1993-01-21','2011-09-01','江苏省南京市雨花台区');
  22. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (22,'王四','男','1993-01-22','2011-09-01','江苏省南京市雨花台区');
  23. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (23,'王五','男','1993-01-23','2011-09-01','江苏省南京市雨花台区');
  24. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (24,'王六','男','1993-01-24','2011-09-01','江苏省南京市雨花台区');
  25. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (25,'王七','男','1993-01-25','2011-09-01','江苏省南京市雨花台区');
  26. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (26,'王八','男','1993-01-26','2011-09-01','江苏省南京市雨花台区');
  27. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (27,'王九','男','1993-01-27','2011-09-01','江苏省南京市雨花台区');
  28. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (28,'钱一','男','1993-01-28','2011-09-01','江苏省南京市雨花台区');
  29. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (29,'钱二','男','1993-01-29','2011-09-01','江苏省南京市雨花台区');
  30. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (30,'钱三','男','1993-01-30','2011-09-01','江苏省南京市雨花台区');
  31. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (31,'钱四','男','1993-02-01','2011-09-01','江苏省南京市雨花台区');
  32. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (32,'钱五','男','1993-02-02','2011-09-01','江苏省南京市雨花台区');
  33. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (33,'钱六','男','1993-02-03','2011-09-01','江苏省南京市雨花台区');
  34. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (34,'钱七','男','1993-02-04','2011-09-01','江苏省南京市雨花台区');
  35. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (35,'钱八','男','1993-02-05','2011-09-01','江苏省南京市雨花台区');
  36. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (36,'钱九','男','1993-02-06','2011-09-01','江苏省南京市雨花台区');
  37. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (37,'吴一','男','1993-02-07','2011-09-01','江苏省南京市雨花台区');
  38. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (38,'吴二','男','1993-02-08','2011-09-01','江苏省南京市雨花台区');
  39. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (39,'吴三','男','1993-02-09','2011-09-01','江苏省南京市雨花台区');
  40. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (40,'吴四','男','1993-02-10','2011-09-01','江苏省南京市雨花台区');
  41. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (41,'吴五','男','1993-02-11','2011-09-01','江苏省南京市雨花台区');
  42. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (42,'吴六','男','1993-02-12','2011-09-01','江苏省南京市雨花台区');
  43. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (43,'吴七','男','1993-02-13','2011-09-01','江苏省南京市雨花台区');
  44. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (44,'吴八','男','1993-02-14','2011-09-01','江苏省南京市雨花台区');
  45. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (45,'吴九','男','1993-02-15','2011-09-01','江苏省南京市雨花台区');
  46. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (46,'柳一','男','1993-02-16','2011-09-01','江苏省南京市雨花台区');
  47. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (47,'柳二','男','1993-02-17','2011-09-01','江苏省南京市雨花台区');
  48. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (48,'柳三','男','1993-02-18','2011-09-01','江苏省南京市雨花台区');
  49. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (49,'柳四','男','1993-02-19','2011-09-01','江苏省南京市雨花台区');
  50. INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (50,'柳五','男','1993-02-20','2011-09-01','江苏省南京市雨花台区');

步骤 2 向teacher表中插入数据。

  1. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (1,'张一','讲师','男',35,'2009-07-01');
  2. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (2,'张二','讲师','男',35,'2009-07-01');
  3. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (3,'张三','讲师','男',35,'2009-07-01');
  4. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (4,'张四','讲师','男',35,'2009-07-01');
  5. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (5,'张五','讲师','男',35,'2009-07-01');
  6. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (6,'张六','讲师','男',35,'2009-07-01');
  7. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (7,'张七','讲师','男',35,'2009-07-01');
  8. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (8,'张八','讲师','男',35,'2009-07-01');
  9. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (9,'张九','讲师','男',35,'2009-07-01');
  10. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (10,'李一','讲师','男',35,'2009-07-01');
  11. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (11,'李二','讲师','男',35,'2009-07-01');
  12. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (12,'李三','讲师','男',35,'2009-07-01');
  13. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (13,'李四','讲师','男',35,'2009-07-01');
  14. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (14,'李五','讲师','男',35,'2009-07-01');
  15. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (15,'李六','讲师','男',35,'2009-07-01');
  16. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (16,'李七','讲师','男',35,'2009-07-01');
  17. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (17,'李八','讲师','男',35,'2009-07-01');
  18. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (18,'李九','讲师','男',35,'2009-07-01');
  19. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (19,'王一','讲师','男',35,'2009-07-01');
  20. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (20,'王二','讲师','男',35,'2009-07-01');
  21. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (21,'王三','讲师','男',35,'2009-07-01');
  22. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (22,'王四','讲师','男',35,'2009-07-01');
  23. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (23,'王五','讲师','男',35,'2009-07-01');
  24. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (24,'王六','讲师','男',35,'2009-07-01');
  25. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (25,'王七','讲师','男',35,'2009-07-01');
  26. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (26,'王八','讲师','男',35,'2009-07-01');
  27. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (27,'王九','讲师','男',35,'2009-07-01');
  28. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (28,'钱一','讲师','男',35,'2009-07-01');
  29. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (29,'钱二','讲师','男',35,'2009-07-01');
  30. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (30,'钱三','讲师','男',35,'2009-07-01');
  31. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (31,'钱四','讲师','男',35,'2009-07-01');
  32. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (32,'钱五','讲师','男',35,'2009-07-01');
  33. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (33,'钱六','讲师','男',35,'2009-07-01');
  34. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (34,'钱七','讲师','男',35,'2009-07-01');
  35. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (35,'钱八','讲师','男',35,'2009-07-01');
  36. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (36,'钱九','讲师','男',35,'2009-07-01');
  37. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (37,'吴一','讲师','男',35,'2009-07-01');
  38. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (38,'吴二','讲师','男',35,'2009-07-01');
  39. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (39,'吴三','讲师','男',35,'2009-07-01');
  40. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (40,'吴四','讲师','男',35,'2009-07-01');
  41. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (41,'吴五','讲师','男',35,'2009-07-01');
  42. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (42,'吴六','讲师','男',35,'2009-07-01');
  43. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (43,'吴七','讲师','男',35,'2009-07-01');
  44. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (44,'吴八','讲师','男',35,'2009-07-01');
  45. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (45,'吴九','讲师','男',35,'2009-07-01');
  46. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (46,'柳一','讲师','男',35,'2009-07-01');
  47. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (47,'柳二','讲师','男',35,'2009-07-01');
  48. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (48,'柳三','讲师','男',35,'2009-07-01');
  49. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (49,'柳四','讲师','男',35,'2009-07-01');
  50. INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (50,'柳五','讲师','男',35,'2009-07-01');

步骤 3 向class表插入数据。

  1. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (1,'计算机',1);
  2. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (2,'自动化',3);
  3. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (3,'飞行器设计',5);
  4. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (4,'大学物理',7);
  5. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (5,'高等数学',9);
  6. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (6,'大学化学',12);
  7. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (7,'表演',14);
  8. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (8,'服装设计',16);
  9. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (9,'工业设计',18);
  10. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (10,'金融学',21);
  11. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (11,'医学',23);
  12. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (12,'土木工程',25);
  13. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (13,'机械',27);
  14. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (14,'建筑学',29);
  15. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (15,'经济学',32);
  16. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (16,'财务管理',34);
  17. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (17,'人力资源',36);
  18. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (18,'力学',38);
  19. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (19,'人工智能',41);
  20. INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (20,'会计',45);

步骤 4 向school_department表插入数据。

  1. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (1,'计算机学院',2);
  2. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (2,'自动化学院',4);
  3. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (3,'航空宇航学院',6);
  4. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (4,'艺术学院',8);
  5. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (5,'理学院',11);
  6. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (6,'人工智能学院',13);
  7. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (7,'工学院',15);
  8. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (8,'管理学院',17);
  9. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (9,'农学院',22);
  10. INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (10,'医学院',28);

步骤 5 向course表插入数据。

  1. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (1,'数据库系统概论','必修',3);
  2. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (2,'艺术设计概论','选修',1);
  3. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (3,'力学制图','必修',4);
  4. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (4,'飞行器设计历史','选修',1);
  5. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (5,'马克思主义','必修',2);
  6. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (6,'大学历史','必修',2);
  7. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (7,'人力资源管理理论','必修',2.5);
  8. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (8,'线性代数','必修',4);
  9. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (9,'JAVA程序设计','必修',3);
  10. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (10,'操作系统','必修',4);
  11. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (11,'计算机组成原理','必修',3);
  12. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (12,'自动化设计理论','必修',2);
  13. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (13,'情绪表演','必修',2.5);
  14. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (14,'茶学历史','选修',1);
  15. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (15,'艺术论','必修',1.5);
  16. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (16,'机器学习','必修',3);
  17. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (17,'数据挖掘','选修',2);
  18. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (18,'图像识别','必修',3);
  19. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (19,'解剖学','必修',4);
  20. INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (20,'3D max','选修',2);

1.5.3 数据查询

1.5.3.1 单表查询

查询B校课程信息表的所有信息。

  1. postgres=# SELECT * from course;
  2.  cor_id |            cor_name            |      cor_type      | credit
  3. --------+--------------------------------+--------------------+--------
  4.       2| 艺术设计概论                   | 选修               |13| 力学制图                       | 必修               |44| 飞行器设计历史                 | 选修               |15| 马克思主义                     | 必修               |26| 大学历史                       | 必修               |27| 人力资源管理理论               | 必修               |2.58| 线性代数                       | 必修               |49| JAVA程序设计                   | 必修               |310| 操作系统                       | 必修               |411| 计算机组成原理                 | 必修               |312| 自动化设计理论                 | 必修               |213| 情绪表演                       | 必修               |2.514| 茶学历史                       | 选修               |115| 艺术论                         | 必修               |1.516| 机器学习                       | 必修               |317| 数据挖掘                       | 选修               |218| 图像识别                       | 必修               |319| 解剖学                         | 必修               |420| 3D max                         | 选修               |21| C语言程序设计                  | 必修               |3.5(20 rows)

1.5.3.2 条件查询

在教师信息表中查询教师编号大于45的老师的入学年份。

  1. postgres=# SELECT tec_id, tec_in FROM teacher WHERE tec_id>45;
  2.  tec_id |       tec_in
  3. --------+---------------------
  4.      46|2009-07-01 00:00:00
  5.      47|2009-07-01 00:00:00
  6.      48|2009-07-01 00:00:00
  7.      49|2009-07-01 00:00:00
  8.      50|2009-07-01 00:00:00
  9. (5 rows)

查询B校中所有选修的课程的信息。

  1. postgres=# SELECT * FROM course WHERE cor_type='选修';
  2.  cor_id |            cor_name            |      cor_type      | credit
  3. --------+--------------------------------+--------------------+--------
  4.       2| 艺术设计概论                   | 选修               |14| 飞行器设计历史                 | 选修               |114| 茶学历史                       | 选修               |117| 数据挖掘                       | 选修               |220| 3D max                         | 选修               |2(5 rows)

1.5.4 数据的修改和删除

1.5.4.1 修改数据

修改/更新课程信息表数据。

  1. postgres=# UPDATE course SET cor_name='C语言程序设计',cor_type='必修',credit=3.5 WHERE cor_id=1;
  2. UPDATE 1postgres=# SELECT * FROM course WHERE cor_id=1;
  3.  cor_id |         cor_name         |      cor_type      | credit
  4. --------+--------------------------+--------------------+--------
  5.       1| C语言程序设计            | 必修               |3.5(1 row)

1.5.4.2 删除指定数据

在B校中删除教师编号8和15所管理的院系。

  1. postgres=# DELETE FROM school_department WHERE depart_teacher=8 OR depart_teacher=15;
  2. DELETE 0postgres=# SELECT * FROM school_department;
  3.  depart_id |         depart_name         | depart_teacher
  4. -----------+-----------------------------+----------------
  5.          1| 计算机学院                  |22| 自动化学院                  |43| 航空宇航学院                |65| 理学院                      |116| 人工智能学院                |138| 管理学院                    |179| 农学院                      |2210| 医学院                      |28(8 rows)

本实验结束。

2 附录一：openGauss数据库基本操作

2.1 查看数据库对象

● 查看帮助信息：

  1. postgres=# \?

● 切换数据库：

  1. postgres=# \c dbname

● 列举数据库：
使用\l元命令查看数据库系统的数据库列表。

  1. postgres=# \l

使用如下命令通过系统表pg_database查询数据库列表。

  1. postgres=# SELECT datname FROM pg_database;

● 列举表：

  1. postgres=# \dt

● 列举所有表、视图和索引：

  1. postgres=# \d+

● 使用gsql的\d+命令查询表的属性。

  1. postgres=# \d+ tablename

● 查看表结构：

  1. postgres=# \d tablename

● 列举schema：

  1. postgres=# \dn

● 查看索引：

  1. postgres=# \di

● 查询表空间：
使用gsql程序的元命令查询表空间。

  1. postgres=# \db

检查pg_tablespace系统表。如下命令可查到系统和用户定义的全部表空间。

  1. postgres=# SELECT spcname FROM pg_tablespace;

● 查看数据库用户列表：

  1. postgres=# SELECT * FROM pg_user;

● 要查看用户属性：

  1. postgres=# SELECT * FROM pg_authid;

● 查看所有角色：

  1. postgres=# SELECT * FROM PG_ROLES;

2.2 其他操作

● 查看openGauss支持的所有SQL语句。

  1. postgres=#\h

● 切换数据库：

  1. postgres=# \c dbname

● 切换用户：

  1. postgres=# \c – username

● 退出数据库：

  1. postgres=# \q

以上是openGauss数据库开发指导手册全部内容，感谢您的阅读。

标签： 数据库开发 数据库 database
本文转载自: https://blog.csdn.net/GaussDB/article/details/122835509
版权归原作者 Gauss松鼠会 所有， 如有侵权，请联系我们删除。
登录后发布评论

“openGauss数据库开发指导手册（下）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？