数据库安装、配置与SQL基本查询
一、实验目的
1、了解Oracle数据库的基本架构、概念，系统安装与客户端使用；
2、熟悉Oracle、MySQL主要的管理、开发工具；
3、运用SQL访问数据库，创建tablespace、user、table和index；
4、了解并掌握Oracle、MySQL的数据类型、创建表和基本查询语句；
5、连接不同数据库之间进行模式、数据转换的基本方法；
6、了解并掌握向表中插入数据、更新数据的基本方法。
二、实验要求
每一个实验内容，根据要求，进行实际操作，并把具体的步骤记录下来，如给出数据查询/修改等的SQL语句，查询结果截图后附在后面（如果数据很多，可只截第一页页面，不需要把全部的查询结果截图）；安装配置类的，可以只截重要的配置页面，以及测试成功的页面。
三、实验内容
1、下载实验课相关的工具和测试数据集。
2、Oracle21cXE的安装
Oracle数据库缺省安装的数据库字符集是AL32UTF8，如果需要改变数
据库的缺省字符集，Oracle21cXE的安装命令行如下：
setup.exe/v"CHAR_SET=ZHS16GBK"
3、Oracle客户端工具sqlplus、dbeaver、navicat等工具的配置与使用方法
4、参考：DBSEC_Oracle安装与使用参考手册.pdf第1.3节，创建oracle测试用户OT，并对用户进行授权。
5、下载测试数据集的建库脚本，并导入测试数据
(1)SampleDB数据库说明
⚫ 建库脚本文件
⮚ ot_create_user.sql--创建用户OT（需要根据实际设置进行修改）
⮚ ot_schema.sql--创建测试数据库模式表结构
⮚ ot_data.sql--插入测试数据
⮚ ot_drop.sql--清空数据库表（为重新建表，插入数据做准备）
⚫ 创建OT用户和数据导入，步骤如下：
⮚ 使用sqlplus以sys用户登录到pdb：xepdb1；
⮚ 执行ot_create_user.sql脚本（需要修改脚本中的password）;
⮚ 退出sqlplus
⮚ 以新建的用户OT登录sqlplus，再进行建库（ot_schema.sql）
和数据导入（ot_data.sql）;
⮚ 如果上述步骤有问题，可以使用sqlplus执行ot_drop.sql删除
数据和表结构，或者使用:DROPUSEROTCASCADE(删除用户及其数据)，然后重新执行上面的初始化程序
⚫ 某些工具（没有自动或者隐式提交commit功能）需要修改ot_data.sql，在文件尾部增加一行sql命令：commit将上述所有的插入数据提交数据库永久更新，否则有可能退出程序后就看不到数据更新。
⚫ 数据库的实体关系图ERD如下图所示
(2)以OT用户登录数据库，导入测试数据。如果有to_date('10-APR-16','DD-MON-RR')函数报错的问题，查oracle手册解决，该问题与日期显示格式有关。解决思路：①设置正确的时间格式；②修改转换格式和数据，适应当前的环境日期格式设置。【参考：DBSEC_Oracle安装与使用参考手册.pdf第1.6节】
⚫ 数据导入步骤如下
Sqlplus以OT用户登录后执行下面的命令，创建表结构和导入测试数据
@ot_schema.sql
@ot_data.sql
6、导入数据记录条数统计：使用SQL统计函数，查询统计每张表中的记录条数，给出sql语句，并与下表对照验证：
表记录条数统计参考SQL语句：selectcount(*)fromemployees;
7、基本SQL查询练习
(1)数据更新
⚫ 插入1条完整的数据到COUNTRIES、LOCATIONS和WAREHOUSES，要求说明性文字为中文，例如：
仓库名称：ABC技术有限公司
地址：九龙荔枝角道838号
城市：香港
州名：香港
邮编：123456
⚫ 构建1条完整的客户、订单、定单项、产品、库存记录，插入到CUSTOMERS、ORDERS、ORDER_ITEMS、PRODUCTS和INVENTORIES中，所有文字性描述要求使用中文。
⚫ 更新REGIONS，将所有REGION_NAME修改为翻译后的中文。
(2)字符串匹配、时间格式练习
⚫ 查询employees表中的名、姓、email和电话号码
⚫ 查询employees中姓氏以字母’J’开头的雇员信息，显示格式如下：
FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE
以’YYYY/MM/DD’格式显示（提示：需要日期转换函数）
⚫ 查询COUNTRIES表中第二个字母是’a’的国家信息
⚫ 查询2016-06-01与2016-12-01之间入职的雇员，查询结果显示名、姓、职位和雇佣时间。
⚫ 查询没有主管经理的雇员名单，查询结果显示名、姓、职位和雇佣时间。
(3)多表查询
⚫ 查询客户姓名为'CenturyLink'、且该客户的订单状态为'Shipped'的订单信息，显示格式如下：客户名称，订单ID，订单状态，订单日期
⚫ 查询在'UnitedStatesofAmerica'的仓库信息，包括如下字段：仓库名称、仓库地址、所在城市、州名、国家名
8、安装MySQL8数据库，熟悉客户端工具mysql,heidisql的用法
9、创建一个新的databasesalesdb和一个新的数据库用户sales，然后对sales用户授权新建的salesdb，将上面的Oracle测试数据集进行适当的模式（主要是数据类型、约束关系语法）与数据格式（按照mysql支持的数据和函数转换格式要求）修改，导入MySQL数据库。
10、导入测试数据库后，选择上面第7项中的任意2个SQL查询，测试MySQL数据库的运行结果。
四．实验报告
2. Oracle 21c XE 的安装
成功安装后的界面如下：
检查安装结果如下：
根据上述输出结果，可知安装正常。
3.配置sqlplus，并成功登录账户sys：
Dbeaver工具可正常使用：
4.首先用命令sqlplus sys@xepdb1 as sysdba登录sys用户，然后使用sql命令执行脚本ot_create_user.sql即可创建OT用户并对OT进行授权。
创建完成后，可以成功登录OT用户：
5-6以OT用户登录数据库，执行下列命令创建表结构和导入测试数据：
@ot_schema.sql ；@ot_data.sql
使用SQL统计函数，查询统计每张表中的记录条数如下：
因在查询之前先执行了后续添加操作，故部分表中的记录条数比原表结果多1.
7.（1）数据更新
·插入1条完整的数据到 COUNTRIES 、 LOCATIONS 和WAREHOUSES：
指令如下：
Insert into OT.COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('RS','俄罗斯',1);
Insert into OT.LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (24,'九龙荔枝角道 838 号','123456','香港',null,'CN');
Insert into OT.WAREHOUSES (WAREHOUSE_ID,WAREHOUSE_NAME,LOCATION_ID) values (10,'九龙荔枝',24);执行结果如下：
·构建 1 条完整的客户、订单、定单项、产品、库存记录，插入到CUSTOMERS 、 ORDERS 、 ORDER_ITEMS 、 PRODUCTS 和INVENTORIES 中：
指令和执行结果如下：
Insert into OT.CUSTOMERS (CUSTOMER_ID,NAME,ADDRESS,CREDIT_LIMIT,WEBSITE) values (505,'外星人','美国国会大厦总部',3600,'https://www.usa.gov/');
Insert into OT.ORDERS (ORDER_ID,CUSTOMER_ID,STATUS,SALESMAN_ID,ORDER_DATE) values (108,505,'Pending',66,to_date('19-JUL-15','DD-MON-RR'));
Insert into OT.PRODUCTS (PRODUCT_ID,PRODUCT_NAME,DESCRIPTION,STANDARD_COST,LIST_PRICE,CATEGORY_ID) values (666,'外星科技 ALIEN114514','Series:Green,Type:SSD,Capacity:1024GB,Cache:N/A',69.03,88.98,5);
Insert into OT.ORDER_ITEMS (ORDER_ID,ITEM_ID,PRODUCT_ID,QUANTITY,UNIT_PRICE) values (108,9,666,120,1514.99);
Insert into OT.INVENTORIES (PRODUCT_ID,WAREHOUSE_ID,QUANTITY) values (666,8,139);
·更新 REGIONS，将所有 REGION_NAME 修改为翻译后的中文：
指令如下：
UPDATE OT.REGIONS SET REGION_NAME='欧洲' WHERE REGION_ID=1;
UPDATE OT.REGIONS SET REGION_NAME='北美洲' WHERE REGION_ID=2;
UPDATE OT.REGIONS SET REGION_NAME='亚洲' WHERE REGION_ID=3;
UPDATE OT.REGIONS SET REGION_NAME='中东和非洲' WHERE REGION_ID=4;执行结果如下：
（2）字符串匹配、时间格式练习
·查询 employees 表中的名、姓、email 和电话号码
指令如下：
SELECT FIRST_NAME,LAST_NAME,EMAIL,PHONE FROM EMPLOYEES;执行结果如下：
·查询 employees 中姓氏以字母’J’开头的雇员信息，显示格式如下：FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE以’YYYY/MM/DD’格式显示
指令如下：
SELECT FIRST_NAME,LAST_NAME,EMAIL,to_date(HIRE_DATE,'YYYY/MM/DD') FROM employees WHERE LAST_NAME LIKE 'J%';查询结果如下：
·查询 COUNTRIES 表中第二个字母是’a’的国家信息
指令如下：
SELECT * FROM OT.COUNTRIES WHERE COUNTRY_NAME LIKE '_a%';查询结果如下：
·查询 2016-06-01 与 2016-12-01 之间入职的雇员，查询结果显示名、姓、职位和雇佣时间。
指令如下：
SELECT FIRST_NAME,LAST_NAME,JOB_TITLE,TO_DATE(HIRE_DATE,'YYYY-MM-DD') FROM employees WHERE HIRE_DATE BETWEEN TO_DATE('2016-06-01','YYYY-MM-DD') AND TO_DATE('2016-12-01','YYYY-MM-DD');查询结果如下：
·查询没有主管经理的雇员名单，查询结果显示名、姓、职位和雇佣时间。
指令如下：
SELECT FIRST_NAME,LAST_NAME,JOB_TITLE,TO_DATE(HIRE DATE,’YYYY-MM-DD')FROM employees WHERE MANAGER_ID is null;查询结果如下：
（3）多表查询
·查询客户姓名为'CenturyLink'、且该客户的订单状态为'Shipped'的订单信息，显示格式如下：客户名称，订单 ID， 订单状态，订单日期
指令如下：
SELECT C.NAME,O.ORDER_ID,O.STATUS,TO_DATE(O.ORDER_DATE,'YYYY-MM-DD') FROM CUSTOMERS C,ORDERS O WHERE C.CUSTOMER_ID=O.CUSTOMER_ID AND C.NAME='CenturyLink' AND O.STATUS='Shipped';查询结果如下：
·查询在'United States of America'的仓库信息，包括如下字段：仓库名称、仓库地址、所在城市、州名、国家名
SELECT W.WAREHOUSE_NAME,L.ADDRESS,L.CITY,L.STATE,C.COUNTRY_NAME FROM WAREHOUSES W,LOCATIONS L,COUNTRIES C WHERE W.LOCATION_ID=L.LOCATION_ID AND L.COUNTRY_ID=C.COUNTRY_ID AND C.COUNTRY_NAME='United States of America’;查询结果如下：
8-9.安装MySQL 8 数据库，创建一个新的 database salesdb 和一个新的数据库用户 sales，然后对 sales用户授权新建的 salesdb如下：
将上面的 Oracle 测试数据集进行适当的模式与数据格式修改，导入 MySQL 数据库，相关文件内容详见附件。
使用客户端工具heidisql登录用户sales并查看数据库salesdb内容如下：
10. 导入测试数据库后，选择上面第 7 项中的任意 2 个 SQL 查询，测试MySQL 数据库的运行结果。
（1）查询 employees 中姓氏以字母’J’开头的雇员信息，显示格式如下：FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE以’YYYY/MM/DD’格式显示：
指令：
SELECT FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE FROM employees WHERE LAST_NAME LIKE 'J%';查询结果：
（2）查询在'United States of America'的仓库信息，包括如下字段：仓库名称、仓库地址、所在城市、州名、国家名：
查询指令：
SELECT W.WAREHOUSE_NAME,L.ADDRESS,L.CITY,L.STATE,C.COUNTRY_NAME FROM WAREHOUSES W,LOCATIONS L,COUNTRIES C WHERE W.LOCATION_ID=L.LOCATION_ID AND L.COUNTRY_ID=C.COUNTRY_ID AND C.COUNTRY_NAME='United States of America';查询结果：
