数据库知识复习07

第七部分 练习01 完成5个不同类型的数据库安装一、MySQL 安装Windows 10 系统1. 下载安装包访问 MySQL 官方下载地址https://dev.mysql.com/downloads/installer/选择对应系统的 MySQL Installer 安装包建议选择完整安装包包含所需组件点击下载并保存到本地。2. 启动安装双击下载完成的安装包启动安装程序选择“Custom”自定义安装勾选需要安装的组件如 MySQL Server、MySQL Workbench 等点击“Next”。3. 配置安装路径设置 MySQL 安装目录和数据存储目录建议选择非系统盘如 D:\MySQL\MySQL Server 8.0点击“Next”后续步骤默认下一步直至开始安装。4. 配置 MySQL安装完成后进入配置界面选择“Standalone MySQL Server / Classic MySQL Replication”点击“Next”设置端口号默认3306点击“Next”选择认证方式建议选择“Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)”点击“Next”。5. 设置 root 密码设置 MySQL 超级管理员 root 的密码确认密码后点击“Next”配置 MySQL 服务设置服务名称默认 MySQL80选择“Start the MySQL Server at System Startup”点击“Next”完成配置并启动服务。6. 验证安装打开 MySQL Workbench点击“”创建新连接输入连接名称、root 密码点击“Test Connection”提示连接成功即安装完成。二、PostgreSQL 安装Windows 10 系统1. 下载安装包访问 PostgreSQL 官方下载地址https://www.postgresql.org/download/windows/选择对应版本的安装包如 PostgreSQL 15点击下载并保存。2. 启动安装双击安装包点击“Next”设置安装目录如 D:\PostgreSQL\15点击“Next”勾选“PostgreSQL Server”“pgAdmin 4”等组件点击“Next”。3. 配置数据库集群设置数据存储目录默认与安装目录关联点击“Next”设置超级管理员 postgres 的密码确认密码后点击“Next”设置端口号默认5432点击“Next”完成安装。4. 验证安装启动 pgAdmin 4进入管理界面输入 postgres 密码登录能成功看到数据库集群即安装完成。三、SQL Server 安装Windows 10 系统Express 版本1. 下载安装包访问 SQL Server 官方下载地址https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads选择“SQL Server Express”版本点击下载。2. 启动安装双击安装包选择“基本”安装类型点击“接受”许可条款设置安装目录如 D:\SQL Server\Express点击“安装”。3. 配置服务安装完成后启动“SQL Server 配置管理器”确认“SQL Server (SQLEXPRESS)”服务已启动端口默认1433。4. 验证安装打开“SQL Server Management Studio (SSMS)”连接服务器服务器名称为“localhost\SQLEXPRESS”使用 Windows 身份验证登录能成功连接即安装完成。四、MongoDB 安装Windows 10 系统1. 下载安装包访问 MongoDB 官方下载地址https://www.mongodb.com/try/download/community选择对应系统和版本的安装包点击下载。2. 启动安装双击安装包点击“Next”接受许可条款点击“Next”勾选“Complete”完整安装或自定义安装目录点击“Next”勾选“Install MongoDB Compass”可视化工具点击“Install”完成安装。3. 配置环境变量找到 MongoDB 安装目录下的“bin”文件夹如 C:\Program Files\MongoDB\Server\6.0\bin将其路径添加到系统环境变量的“Path”中。4. 验证安装打开命令提示符CMD输入“mongo”或“mongosh”能成功进入 MongoDB 交互界面即安装完成。五、SQLite 安装Windows 10 系统1. 下载安装包访问 SQLite 官方下载地址https://www.sqlite.org/download.html下载“sqlite-tools-win32-x86-xxxxxxx.zip”压缩包对应版本保存到本地。2. 解压配置将压缩包解压到指定目录如 D:\SQLite解压后得到 sqlite3.exe 可执行文件将该目录添加到系统环境变量的“Path”中。3. 验证安装打开命令提示符CMD输入“sqlite3”能成功进入 SQLite 交互界面显示“sqlite”即安装完成。02 练习内容创建 student 数据库该数据库的默认字符集为 gbk默认的校对规则为 gbk_chinese_ci。在 student 数据库中创建 user_list 数据表该表使用 MyISAM 引擎该表定义了三个字段要求分别如下id 字段该字段数据类型为 int(3)username 字段该字段数据类型为 varchar(12)sex 字段该字段数据类型为 varchar(12)复制 user_list 数据表为 user_list_new 表但是要求不需要复制数据。在 user_list_new 表中增加新字段该字段位于 username 字段和 sex 字段中间该字段数据类型要求为 varchar(10)。操作过程使用 MySQL 数据库通过 CMD 或 Navicat 执行 SQL 语句1. 登录 MySQL 数据库打开命令提示符CMD输入以下命令登录 MySQL需输入 root 密码mysql -u root -p2. 创建 student 数据库执行以下 SQL 语句创建字符集为 gbk、校对规则为 gbk_chinese_ci 的 student 数据库CREATE DATABASE student DEFAULT CHARACTER SET gbk DEFAULT COLLATE gbk_chinese_ci;执行完成后提示“Query OK, 1 row affected”即创建成功。3. 使用 student 数据库执行以下命令切换到 student 数据库USE student;提示“Database changed”即切换成功。4. 创建 user_list 数据表执行以下 SQL 语句创建使用 MyISAM 引擎、包含指定字段的 user_list 表CREATE TABLE user_list ( id INT(3), username VARCHAR(12), sex VARCHAR(12) ) ENGINEMyISAM DEFAULT CHARSETgbk;执行完成后提示“Query OK, 0 rows affected”即创建成功。5. 复制 user_list 表为 user_list_new 表不复制数据执行以下 SQL 语句复制表结构但不复制数据CREATE TABLE user_list_new LIKE user_list;该语句会复制 user_list 的表结构、字段类型、引擎等信息但不会复制任何数据执行后提示“Query OK, 0 rows affected”即复制成功。6. 在 user_list_new 表中添加新字段执行以下 SQL 语句在 username 和 sex 字段中间添加 varchar(10) 类型的新字段此处命名为 age可自定义字段名ALTER TABLE user_list_new ADD COLUMN age VARCHAR(10) AFTER username;说明AFTER 关键字用于指定新字段的位置“AFTER username”表示新字段位于 username 字段之后、sex 字段之前执行后提示“Query OK, 0 rows affected”即添加成功。7. 验证操作结果执行以下命令查看 user_list_new 表的结构确认新字段添加成功DESCRIBE user_list_new;查看结果字段顺序应为id、username、age、sex字段类型符合要求。03 综合练习一、单表查询素材表名worker表中字段均为中文1. 创建 worker 表并插入数据CREATE TABLE worker ( 部门号 int(11) NOT NULL, 职工号 int(11) NOT NULL, 工作时间 date NOT NULL, 工资 float(8,2) NOT NULL, 政治面貌 varchar(10) NOT NULL DEFAULT 群众, 姓名 varchar(20) NOT NULL, 出生日期 date NOT NULL, PRIMARY KEY (职工号) ) ENGINEInnoDB DEFAULT CHARSETutf8 ROW_FORMATDYNAMIC; INSERT INTO worker (部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期) VALUES (101, 1001, 2015-5-4, 3500.00, 群众, 张三, 1990-7-1), (101, 1002, 2017-2-6, 3200.00, 团员, 李四, 1997-2-8), (102, 1003, 2011-1-4, 8500.00, 党员, 王亮, 1983-6-8), (102, 1004, 2016-10-10, 5500.00, 群众, 赵六, 1994-9-5), (102, 1005, 2014-4-1, 4800.00, 党员, 钱七, 1992-12-30), (102, 1006, 2017-5-5, 4500.00, 党员, 孙八, 1996-9-2);2. 单表查询题目显示所有职工的基本信息。SELECT * FROM worker;查询所有职工所属部门的部门号不显示重复的部门号。SELECT DISTINCT 部门号 FROM worker;求出所有职工的人数。SELECT COUNT(职工号) AS 职工总人数 FROM worker;列出最高工和最低工资。SELECT MAX(工资) AS 最高工资, MIN(工资) AS 最低工资 FROM worker;列出职工的平均工资和总工资。SELECT AVG(工资) AS 平均工资, SUM(工资) AS 总工资 FROM worker;创建一个只有职工号、姓名和参加工作的新表名为工作日期表。CREATE TABLE 工作日期表 ASSELECT 职工号, 姓名, 工作时间 AS 参加工作 FROM worker;显示所有女职工的年龄。说明素材中未提供“性别”字段无法直接筛选女职工此处假设存在“性别”字段varchar(2)SQL 语句如下SELECT 姓名, TIMESTAMPDIFF(YEAR, 出生日期, CURDATE()) AS 年龄FROM workerWHERE 性别 女;列出所有姓刘的职工的职工号、姓名和出生日期。SELECT 职工号, 姓名, 出生日期FROM workerWHERE 姓名 LIKE 刘%;说明素材中无姓刘的职工执行后无结果但 SQL 语句语法正确。列出1960年以前出生的职工的姓名、参加工作日期。SELECT 姓名, 工作时间 AS 参加工作日期FROM workerWHERE 出生日期 1960-01-01;说明素材中无1960年以前出生的职工执行后无结果SQL 语句语法正确。列出工资在10002000之间的所有职工姓名。SELECT 姓名FROM workerWHERE 工资 BETWEEN 1000 AND 2000;说明素材中无工资在此区间的职工执行后无结果SQL 语句语法正确。列出所有陈姓和李姓的职工姓名。SELECT 姓名FROM workerWHERE 姓名 LIKE 陈% OR 姓名 LIKE 李%;说明素材中只有李姓职工李四执行后会显示“李四”。列出所有部门号为2和3的职工号、姓名、党员否。SELECT 职工号, 姓名,CASE WHEN 政治面貌 党员 THEN 是 ELSE 否 END AS 党员否FROM workerWHERE 部门号 IN (2, 3);说明素材中部门号只有101、102执行后无结果SQL 语句语法正确。将职工表worker中的职工按出生的先后顺序排序。SELECT * FROM worker ORDER BY 出生日期 ASC;说明ASC 表示升序默认即按出生日期从早到晚排序。显示工资最高的前3名职工的职工号和姓名。SELECT 职工号, 姓名FROM workerORDER BY 工资 DESCLIMIT 3;求出各部门党员的人数。SELECT 部门号, COUNT(职工号) AS 党员人数FROM workerWHERE 政治面貌 党员GROUP BY 部门号;统计各部门的工资和平均工资。SELECT 部门号, SUM(工资) AS 部门总工资, AVG(工资) AS 部门平均工资FROM workerGROUP BY 部门号;列出总人数大于4的部门号和总人数。SELECT 部门号, COUNT(职工号) AS 部门总人数FROM workerGROUP BY 部门号HAVING COUNT(职工号) 4;说明素材中各部门人数最多为4102部门有4人执行后无结果SQL 语句语法正确。二、多表查询1. 创建 student 和 score 表并插入数据-- 创建 student 表 CREATE TABLE student ( id INT(10) NOT NULL UNIQUE PRIMARY KEY , name VARCHAR(20) NOT NULL , sex VARCHAR(4) , birth YEAR, department VARCHAR(20) , address VARCHAR(50) ); -- 创建 score 表 CREATE TABLE score ( id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT , stu_id INT(10) NOT NULL , c_name VARCHAR(20) , grade INT(10) ); -- 向 student 表插入记录 INSERT INTO student VALUES (901,张老大, 男,1985,计算机系, 北京市海淀区), (902,张老二, 男,1986,中文系, 北京市昌平区), (903,张三, 女,1990,中文系, 湖南省永州市), (904,李四, 男,1990,英语系, 辽宁省阜新市), (905,王五, 女,1991,英语系, 福建省厦门市), (906,王六, 男,1988,计算机系, 湖南省衡阳市); -- 向 score 表插入记录 INSERT INTO score VALUES (NULL,901, 计算机,98), (NULL,901, 英语, 80), (NULL,902, 计算机,65), (NULL,902, 中文,88), (NULL,903, 中文,95), (NULL,904, 计算机,70), (NULL,904, 英语,92), (NULL,905, 英语,94), (NULL,906, 计算机,90), (NULL,906, 英语,85);2. 多表查询题目查询student表的所有记录SELECT * FROM student;查询student表的第2条到4条记录SELECT * FROM student LIMIT 1, 3;说明LIMIT 第一个参数为起始索引从0开始第二个参数为查询条数1,3 表示从第2条开始查询3条第2、3、4条。从student表查询所有学生的学号id、姓名name和院系department的信息SELECT id AS 学号, name AS 姓名, department AS 院系 FROM student;从student表中查询计算机系和英语系的学生的信息SELECT * FROM student WHERE department IN (计算机系, 英语系);从student表中查询年龄18~22岁的学生信息SELECT * FROM studentWHERE TIMESTAMPDIFF(YEAR, birth, CURDATE()) BETWEEN 18 AND 22;从student表中查询每个院系有多少人SELECT department AS 院系, COUNT(id) AS 人数 FROM student GROUP BY department;从score表中查询每个科目的最高分SELECT c_name AS 科目, MAX(grade) AS 最高分 FROM score GROUP BY c_name;查询李四的考试科目c_name和考试成绩gradeSELECT s.c_name AS 考试科目, s.grade AS 考试成绩FROM score sJOIN student st ON s.stu_id st.idWHERE st.name 李四;用连接的方式查询所有学生的信息和考试信息SELECT st.*, s.c_name AS 考试科目, s.grade AS 考试成绩FROM student stLEFT JOIN score s ON st.id s.stu_id;说明使用左连接确保所有学生信息都显示即使没有考试成绩显示为NULL。计算每个学生的总成绩SELECT st.id AS 学号, st.name AS 姓名, SUM(s.grade) AS 总成绩FROM student stLEFT JOIN score s ON st.id s.stu_idGROUP BY st.id, st.name;计算每个考试科目的平均成绩SELECT c_name AS 考试科目, AVG(grade) AS 平均成绩 FROM score GROUP BY c_name;查询计算机成绩低于95的学生信息SELECT st.*, s.grade AS 计算机成绩FROM student stJOIN score s ON st.id s.stu_idWHERE s.c_name 计算机 AND s.grade 95;查询同时参加计算机和英语考试的学生的信息SELECT st.*FROM student stJOIN score s1 ON st.id s1.stu_id AND s1.c_name 计算机JOIN score s2 ON st.id s2.stu_id AND s2.c_name 英语;将计算机考试成绩按从高到低进行排序SELECT st.id AS 学号, st.name AS 姓名, s.grade AS 计算机成绩FROM student stJOIN score s ON st.id s.stu_idWHERE s.c_name 计算机ORDER BY s.grade DESC;从student表和score表中查询出学生的学号然后合并查询结果SELECT id AS 学号 FROM student UNION SELECT stu_id AS 学号 FROM score;说明UNION 用于合并两个查询结果自动去重若需保留重复值使用 UNION ALL。查询姓张或者姓王的同学的姓名、院系和考试科目及成绩SELECT st.name AS 姓名, st.department AS 院系, s.c_name AS 考试科目, s.grade AS 成绩FROM student stLEFT JOIN score s ON st.id s.stu_idWHERE st.name LIKE 张% OR st.name LIKE 王%;查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩SELECTst.name AS 姓名,TIMESTAMPDIFF(YEAR, st.birth, CURDATE()) AS 年龄,st.department AS 院系,s.c_name AS 考试科目,s.grade AS 成绩FROM student stLEFT JOIN score s ON st.id s.stu_idWHERE st.address LIKE 湖南省%;