头歌 数据库原理与设计 成都理工大学
·
数据库开发实训（Java篇）
第1关数据库连接与数据库实例创建
package step1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MySQLWithJDBC {
//指定数据库驱动
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//设定本地数据库的URL，并指定编码方式为UTF-8且关闭SSL连接
static final String DB_URL = "jdbc:mysql:///?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
// 创建数据库实例
public void createDatabaseInstance(Connection connection, String databaseName){
Statement stmt = null;
//请在此处补全创建数据库实例的SQL语句，不要改动其他代码
/******* Begin ******/
String sqlScript = "create database " + databaseName;
/******* End ******/
try {
//创建数据库查询对象
stmt = (Statement) connection.createStatement();
//执行查询
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
// 注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
// 创建于指定数据库的连接
connection = DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//删除数据库
public void dropDatabase(Connection connection, String databaseName){
Statement stmt = null;
String sqlScript = "drop database if exists " + databaseName;
try {
stmt = (Statement) connection.createStatement();
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 获取数据库中已经存在的数据库实例
public ResultSet getExistDB(Connection connection, String databaseName) throws SQLException
{
ResultSet resultSet = null;
Statement stmt = null;
String sqlScript = "SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME=\"" + databaseName + "\"";
try {
stmt = (Statement) connection.createStatement();
resultSet = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
}
第2关数据表的创建
package step2;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class CreateTable {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
* 在指定数据库中创建以tableName命名的表
* @param connection mysql连接对象
* @param tableName 数据表名
* @param tableInfo 存放表的字段及其属性的二维数组，如tableInfo[k][0]代表第k个字段名，tableInfo[k][1]代表第k个字段的属性
*/
public void createTable(Connection connection, String tableName,String[][] tableInfo){
Statement stmt = null;
//请在此处补全创建表的SQL语句，不要改动其他代码
/******* Begin ******/
StringBuilder sql = new StringBuilder();
sql.append("CREATE TABLE ").append(tableName).append(" (");
for (int i = 0; i < tableInfo.length; i++) {
sql.append(tableInfo[i][0]).append(" ").append(tableInfo[i][1]);
if (i < tableInfo.length - 1) {
sql.append(", ");
}
}
sql.append(")");
String sqlScript = sql.toString();
/******* End ******/
try {
//创建查询语句对象
stmt = connection.createStatement();
//执行查询
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//获取databaseNames数据库中的tablenName表
public ResultSet getExistTable(Connection conn,String databaseName,String tableName) {
ResultSet resultSet = null;
Statement stmt = null;
String sqlScript = "select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=\'" +databaseName +
"\' and TABLE_NAME=\'" + tableName + "\'";
try {
stmt = (Statement) conn.createStatement();
resultSet = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return resultSet;
}
//加载sql脚本
List<String> loadSql(String sqlFile) throws Exception {
List<String> sqlList = new ArrayList<String>();
try {
InputStream sqlFileIn = new FileInputStream(sqlFile);
StringBuffer sqlSb = new StringBuffer();
byte[] buff = new byte[1024];
int byteRead = 0;
while ((byteRead = sqlFileIn.read(buff)) != -1) {
sqlSb.append(new String(buff, 0, byteRead));
} // Windows 下换行是 \r\n, Linux 下是 \n
String[] sqlArr = sqlSb.toString().split("(;\\s*\\r\\n)(;\\s*\\n)");
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i].replaceAll("--.*", "").trim();
if (!sql.equals("")) {
sqlList.add(sql);
}
}
return sqlList;
} catch (Exception ex) {
throw new Exception(ex.getMessage());
}
}
}
第3关数据查询操作
package step3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class QueryRecord {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
*在tableName中查询出书名为bookName的出版社
* @param Connection 数据库连接
* @param tableName 数据表名
* @param bookName 书名
*/
public ResultSet queryPublisherByBookName(Connection connection, String tableName,String bookName)
{
Statement stmt = null;
ResultSet result = null;
//请在此处补全查询数据的SQL语句，不要改动其他代码
/******* Begin ******/
String sqlScript = "SELECT DISTINCT publisher FROM " + tableName + " WHERE title = '" + bookName + "'";
/******* End ******/
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
第4关数据库的插入操作
package step4;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class InsertTable {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
* 向指定表中插入数据
* @param connection 数据库连接对象
* @param id 记录的id
* @param title 书名
* @param author 作者
* @param publisher 出版社
* @param year 出版年份
*/
public void InsertRecord(Connection connection, int id, String title, String author, String publisher, int year){
Statement stmt = null;
//请在此处补全向指定表插入数据的SQL语句，不要改动其他代码
/******* Begin ******/
String sqlScript = "INSERT INTO book VALUES (" + id + ", '" + title + "', '" + author + "', '" + publisher + "', " + year + ")";
/******* End ******/
try {
stmt = connection.createStatement();
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public ResultSet queryDB(Connection connection,String tableName) {
ResultSet result = null;
Statement stmt = null;
String sqlScript = "select * from " + tableName + " order by id desc";
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
List<String> loadSql(String sqlFile) throws Exception {
List<String> sqlList = new ArrayList<String>();
try {
InputStream sqlFileIn = new FileInputStream(sqlFile);
StringBuffer sqlSb = new StringBuffer();
byte[] buff = new byte[1024];
int byteRead = 0;
while ((byteRead = sqlFileIn.read(buff)) != -1) {
sqlSb.append(new String(buff, 0, byteRead));
} // Windows 下换行是 \r\n, Linux 下是 \n
String[] sqlArr = sqlSb.toString().split("(;\\s*\\r\\n)(;\\s*\\n)");
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i].replaceAll("--.*", "").trim();
if (!sql.equals("")) {
sqlList.add(sql);
}
}
return sqlList;
} catch (Exception ex) {
throw new Exception(ex.getMessage());
}
}
}
第5关数据表的删除操作
package step5;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteRecord {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
* 根据作者名删除某条记录
* @param connection 数据库连接
* @param tableName 数据表名
* @param author 作者名
*/
public int deleteRecordByAuthor(Connection connection, String tableName,String author)
{
Statement stmt = null;
int updatedNum = 0;
//请在此处补全删除数据库记录的SQL语句，不要改动其他代码
/******* Begin ******/
String sqlScript = "DELETE FROM " + tableName + " WHERE author = '" + author + "'";
/******* End ******/
try {
stmt = connection.createStatement();
updatedNum = stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return updatedNum;
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//查询数据库
public ResultSet queryDB(Connection connection,String tableName) {
ResultSet result = null;
Statement stmt = null;
String sqlScript = "select * from " + tableName + " order by id desc";
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
第6关数据表的更新操作
package step6;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;
import org.apache.tools.ant.*;
import org.apache.tools.ant.taskdefs.*;
import org.apache.tools.ant.types.*;
public class UpdateRecord {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
/**
* 更新数据库表中的数据
* @param connection 数据库连接
* @param tableName 数据库表名
* @param title 书名
* @param publisher 出版社
*/
public int updatePublisherByTitle(Connection connection,String tableName, String title, String publisher){
Statement stmt = null;
int updatedNum=0;
//请在此处补全创建更新数据库记录的SQL语句，不要改动其他代码
/******* Begin ******/
String sqlScript = "UPDATE " + tableName + " SET publisher = '" + publisher + "' WHERE title = '" + title + "'";
/******* End ******/
try {
stmt = connection.createStatement();
updatedNum = stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return updatedNum;
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//查询数据库
public ResultSet queryDB(Connection connection,String tableName) {
ResultSet result = null;
Statement stmt = null;
String sqlScript = "select * from " + tableName + " order by id desc";
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
第7关数据库的应用
package step7;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.spi.DirStateFactory.Result;
public class TransferTable {
// 设定JDBC驱动以及本地数据库的URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql:///bookstore?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// 指定数据库的用户名和密码
static final String USER = "root";
static final String PASS = "123123";
public void transferTable(Connection connection, String tableName) throws SQLException{
//请在此处补全转换表的SQL语句，不要改动其他代码
/******* Begin ******/
Statement stmt = connection.createStatement();
// 创建新表 transformedBook
stmt.executeUpdate("CREATE TABLE IF NOT EXISTS transformedBook ("
+ "id INT,"
+ "column_name VARCHAR(255),"
+ "value VARCHAR(255))");
// 插入 author
stmt.executeUpdate("INSERT INTO transformedBook(id, column_name, value) "
+ "SELECT id, 'author', author FROM " + tableName + " WHERE author IS NOT NULL AND author <> ''");
// 插入 title
stmt.executeUpdate("INSERT INTO transformedBook(id, column_name, value) "
+ "SELECT id, 'title', title FROM " + tableName + " WHERE title IS NOT NULL AND title <> ''");
// 插入 publisher
stmt.executeUpdate("INSERT INTO transformedBook(id, column_name, value) "
+ "SELECT id, 'publisher', publisher FROM " + tableName + " WHERE publisher IS NOT NULL AND publisher <> ''");
stmt.close();
/******* End ******/
}
//插入数据
public void InsertRecord(Connection connection,String sqlScript){
Statement stmt = null;
try {
stmt = connection.createStatement();
stmt.executeUpdate(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
}
//查询数据库
public ResultSet queryDB(Connection connection, String tableName){
Statement stmt = null;
ResultSet result = null;
String sqlScript = "select * from " + tableName;
try {
stmt = connection.createStatement();
result = stmt.executeQuery(sqlScript);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
// 建立与指定数据库的连接，并返回该连接
public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
Connection connection = null;
//注册JDBC驱动
try {
Class.forName(jdbc_driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//创建于指定数据库的连接
try {
connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
MySQL-函数的使用
第1关字符函数
-- 写出能实现以下查询的SQL语句：
-- 查询空调型号(产品)数最少的那个生产厂家全部产品的产品号，生产厂家，型号，功率(命名为power)和价格。功率需从型号中提取，并换算成功率，单位为W，如3500W。查询结果以产品号排序。
-- 请在以下位置填写语句：
SELECT pid,manufacturer,model,CONCAT(SUBSTR(model,INSTR(model,'-')+1,2),'00W') AS power,price FROM products WHERE manufacturer=(SELECT manufacturer FROM products GROUP BY manufacturer ORDER BY COUNT(*) ASC LIMIT 1) ORDER BY pid;
-- EOF第2关数学函数
-- 写出能实现以下查询的SQL语句：
-- 统计销售记录表中各生产厂家的总销售额(命名为total_amount)，对总销售额进行四舍五入，保留小数点后两位。输出厂家和总销售额，并依总销售额从搞到低排序。
-- 请在以下空白处填写代码：
SELECT manufacturer, ROUND(SUM(quantity * price * discount), 2) AS total_amount FROM sales_record JOIN products ON sales_record.pid = products.pid GROUP BY manufacturer ORDER BY total_amount DESC;
-- EOF第3关日期时间函数
-- 写出能实现以下查询的SQL语句：
-- 统计销售记录表中每月的销售总额并取整。输出月份(命名为“月”)和销售总额(命名为“销售额”)，并依月份排序。
-- 请在以下空白处填写SQL语句：
SELECT MONTH(sdate) AS 月, ROUND(SUM(quantity*price*discount),4) AS 销售额 FROM sales_record JOIN products ON sales_record.pid=products.pid GROUP BY MONTH(sdate) ORDER BY 月;
-- EOF第4关流程控制函数
-- 实现以下查询的SQL语句：
-- 统计2023年国庆小长假(2023-9-29到2023-10-6)期间,各厂家空调的销售情况:订单数(命名为orders,每条销售记录视为一个订单),总销售金额(命名为order_amount), 金额超过20000元订单数(命名为large_orders).结果依厂家名称排序。
-- 请在此处添加实现代码
SELECT manufacturer, COUNT(*) AS orders, SUM(quantity*price*discount) AS order_amount, SUM(CASE WHEN quantity*price*discount>10000 THEN 1 ELSE 0 END) AS large_orders FROM sales_record JOIN products ON sales_record.pid=products.pid WHERE sdate BETWEEN '2023-09-29' AND '2023-10-06' GROUP BY manufacturer ORDER BY manufacturer;
-- EOFMySQL-连接查询(内连,外连,多表分组统计)
第1关内连接查询
-- 实现以下查询的SQL语句：
-- 查询2023年国庆小长假期间(9月29日到10月6日)的销售明细，包括：型号(model)，厂家(manufacturer)，数量(qunantity), 单价(price)和折扣(discount)。查询结果依销售记录号(rid)排序。
-- 请在此处添加实现代码
select model,manufacturer,quantity,price,discount from products
inner join sales_record on products.pid = sales_record.pid
where sdate between'2023-9-29'and '2023-10-6'
order by rid;
-- EOF第2关外连接查询
-- 实现以下查询的SQL语句：
-- 查询2023年国庆节小长假期间(9月29日到10月6日)“格力”空调的销售明细，包括：型号(model)，数量(qunantity), 单价(price)和折扣(discount)。没有销售记录的型号也要包括在查询结果中。查询结果按产品号(pid)排序,产品号相同时，再依销售记录号(rid)排序。
-- 请在此处添加实现代码
select model,quantity,price,discount from products
left join sales_record on products.pid=sales_record.pid
and sdate between '2023-9-29'and '2023-10-6'
where manufacturer ='格力'
order by products.pid,rid;
-- EOF第3关多表连接查询
-- 实现以下查询的SQL语句：
-- 统计各位销售人员在2023年国庆节小长假期间(9月29日到10月6日)期间的销售业绩(即销售总金额)，列出销售人员姓名，销售额(命名为total)。依销售额从高到低排序。
-- 请在此处添加实现代码
SELECT sname, SUM(quantity*price*discount) AS total FROM staff JOIN sales_record ON staff.sid = sales_record.sid JOIN products ON sales_record.pid = products.pid WHERE sdate BETWEEN '2023-09-29' AND '2023-10-06' GROUP BY sname ORDER BY total DESC;
-- EOFMySQL-子查询(in,exists,子查询的位置,分组统计)
第1关带IN谓词的子查询
-- 任务1: 写出能完成以下查询任务的SQL语句:
-- 查询“郑点”没有卖过那些厂家的空调。结果依厂家名字排序。
-- 请在此处添加实现代码
SELECT DISTINCT manufacturer FROM products WHERE manufacturer NOT IN (SELECT DISTINCT manufacturer FROM products WHERE pid IN (SELECT pid FROM sales_record WHERE sid = (SELECT sid FROM staff WHERE sname = '郑点'))) ORDER BY manufacturer;
-- EOF第2关带EXISTS谓词的子查询
-- 任务1: 写出能完成以下查询任务的SQL语句:
-- 被1-9号(正式)员工都卖过的产品(空调)编号，型号，生产厂家和价格。结果依产品号排序。
-- 请在此处添加实现代码
SELECT pid,model,manufacturer,price FROM products WHERE NOT EXISTS (SELECT * FROM staff WHERE sid BETWEEN 1 AND 9 AND NOT EXISTS (SELECT * FROM sales_record WHERE sales_record.sid=staff.sid AND sales_record.pid=products.pid)) ORDER BY pid;
-- EOF第3关复杂子查询及分组统计
-- 任务1: 写出能完成以下查询任务的SQL语句:
-- 统计每个厂家空调的型号数，其中单冷总型号数，冷暖总型号数。统计结果按总型号数从高到底排列。列出内容：
-- 生产厂家：manufacturer
-- 总型号数: model_total
-- 单冷型号数:single_total
-- 冷暖型号数：dual_total
SELECT manufacturer, COUNT(*) AS model_total, SUM(IF(model LIKE 'KF-%',1,0)) AS single_total, SUM(IF(model LIKE 'KFR-%',1,0)) AS dual_total FROM products GROUP BY manufacturer ORDER BY model_total DESC;
-- EOFMySQL-单表查询(分组统计,限定输出行数)
第1关使用 limit 限制查询结果的数量
-- 实现以下查询的SQL语句：
-- 查询产品表中制冷量3500W室内分体壁挂式冷暖空调的价格个最低的三个产品的厂家，型号和价格。按价格从低到高排序。。
-- 请在此处添加实现代码
SELECT manufacturer,model,price FROM products WHERE model LIKE '%35GW%' ORDER BY price ASC LIMIT 3;
-- EOF第2关分组统计查询(group by)
-- 实现以下查询的SQL语句：
-- 统计每个空调厂家，制冷功率为3500W的空调平均价格(取整)。结果表的标题分别为：“厂家”，“平均价格”。查询结果依生产厂家名称排序。
-- 请在此处添加实现代码
SELECT manufacturer AS 厂家, ROUND(AVG(price)) AS 平均价格 FROM products WHERE model LIKE '%35GW%' GROUP BY manufacturer ORDER BY manufacturer;
-- EOF第3关对分组统计的结果再筛选(having)
-- 实现以下查询的SQL语句：
-- 查询2023国庆长假期间(9月29日到10月6日)所有员工销售空调的总台数，并按销售量从高到底排序。只输出总台数超过20(含)台数的结果。总台数命名为total。
-- 请在此处添加实现代码
SELECT sid, SUM(quantity) AS total FROM sales_record WHERE sdate BETWEEN '2023-09-29' AND '2023-10-06' GROUP BY sid HAVING total >= 20 ORDER BY total DESC;
-- EOFMySQL-单表查询(统计总和,平均,最大,最小)
第1关COUNT( )函数及对计算列重命名
-- 实现以下查询的SQL语句：
-- 查询2023年国庆长假期间(9-29至10-6)共有多少员工共完成了多少笔销售交易，涉及多少个型号的空调。统计结果分别命名为：人数,笔数，型号数。
-- 请在此处添加实现代码
SELECT COUNT(DISTINCT sid) AS 人数, COUNT(*) AS 笔数, COUNT(DISTINCT pid) AS 型号数 FROM sales_record WHERE sdate BETWEEN '2023-09-29' AND '2023-10-06';
-- EOF第2关SUM( )函数
-- 实现以下查询的SQL语句：
-- 查询7号员工2023年上半年共销售了几个型号的总共多少台空调？统计结果分别命名为：“型号数”、“总台数”。
-- 请在此处添加实现代码
SELECT COUNT(DISTINCT pid) AS 型号数, SUM(quantity) AS 总台数 FROM sales_record WHERE sid=7 AND sdate BETWEEN '2023-01-01' AND '2023-06-30';
-- EOF第3关AVG( )函数
-- 实现以下查询的SQL语句：
-- 查询产品表中制冷量3500W室内分体空调的平均价格,对均价取整，并命名为：“均价”。
-- 请在此处添加实现代码
SELECT ROUND(AVG(price)) AS 均价 FROM products WHERE model LIKE '%35GW%';
-- EOF第4关MAX( )函数
-- 实现以下查询的SQL语句：
-- 查询产品表中制冷量7200W室内分体落地式冷暖空调的最高价,并命名为：“最高价”。。
-- 请在此处添加实现代码
SELECT MAX(price) AS 最高价 FROM products WHERE model LIKE 'KFR-72LW%';
-- EOF第5关MIN( )函数
-- 实现以下查询的SQL语句：
-- 查询产品表中制冷量3500W室内分体壁挂式冷暖空调的最低价,并命名为：“最低价”。
-- 请在此处添加实现代码
SELECT MIN(price) AS 最低价 FROM products WHERE model LIKE 'KFR-35GW%';
-- EOFMySQL-单表查询(模式匹配,null,去重,and,or)
第1关带 LIKE 的字符匹配查询
-- 实现以下查询的SQL语句：
-- 查询1.5匹，即功率3500W的冷暖空调，列出型号，生产厂家和价格，依价格从低到高排序。
-- 请在此处添加实现代码
SELECT model, manufacturer, price
FROM products
WHERE model LIKE 'KFR%35%'
ORDER BY price ASC;
-- EOF第2关带RLIKE的正则表达式字符匹配查询
-- 实现以下查询的SQL语句：
-- 查询分体式室热泵制热冷暖双制，制冷量7200W或6000W的落地式空调的型号、生产厂家和价格，查询结果依价格从低到高排序输出。
-- 请在此处添加实现代码：
select model, manufacturer,price
from products
where model rlike'^KFR-7[2]LW.*' OR model rlike '^KFR-6[0]LW.*'
order by price asc;
-- EOF第3关查询空值与去除重复结果
-- 实现以下查询的SQL语句：
-- 任务1：查查询产品表(products)表中，有多少厂家(manufacturer)的产品，一个厂家只列一次，即便该厂家有多个型号的产品。查询结果依厂家名称排序输出。
-- 请在此处添加实现代码
select distinct manufacturer
from products
order by manufacturer;
-- 任务2：查询出生日期(出生日期)未填写(值为NULL)的员工编号，姓名。查询结果依姓名排序输出。
-- 请在此处添加实现代码
select sid,sname
from staff
where dob is NULL
order by sname;
-- EOF
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
5
0- 0
已为社区贡献1条内容
所有评论(0)