快捷搜索: 长连接 前端 源码 pan

实验2MySQL客户端应用实验_哈工大 数据库实验2-使用高级语言操作MySQL数据库

1.实验目的

学会使用高级语言访问MySQL数据库,并进行查询。

2.实验环境

Windows XP操作系统、MySQL关系数据库管理系统、MinGW编译器或Microsoft Visual C++编译器。

本次实验主要利用C语言访问MySQL数据库,也可以使用JAVA,PHP等其他语言。

3.实验内容

3.1实验任务

在上次上机实验课建立的COMPANY数据库上,用C语言编写程序,完成如下查询,程序的命令行参数为:

company_query –q -p [Parameters]

其中,Number代表待执行查询的序号,Parameters为第Number号查询需要的参数列表。

待执行的9个查询为如下:

1:参加了项目编号为%PNO%的项目的员工号,其中%PNO%为C语言编写的程序的输入参数;

SELECT ESSN

from employee natural join works_on

where pno=P1

2:参加了项目名为%PNAME%的员工名字,其中%PNAME%为C语言编写的程序的输入参数;

SELECT ename

from employee ,works_on, project

where employee.ESSN=works_on.ESSN and works_on.PNO=project.pno and PNAME =SQL Project

3:在%DNAME%工作的所有工作人员的名字和地址,其中%DNAME%为C语言编写的程序的输入参数;

SELECT ename,ADDRESS

from employee NATURAL join department

where DNAME=研发部

4:在%DNAME%工作且工资低于%SALARY%元的员工名字和地址,其中%DNAME%和%SALARY%为C语言编写的程序的输入参数;

SELECT ename,ADDRESS

from employee NATURAL join department

where DNAME=研发部 and SALARY<3000

5:没有参加项目编号为%PNO%的项目的员工姓名,其中%PNO%为C语言编写的程序的输入参数;

SELECT DISTINCT ename

from employee

where ename not in (

SELECT DISTINCt ename

from employee NATURAL join works_on

where pno="P1"

)

6:由%ENAME%领导的工作人员的姓名和所在部门的名字,,其中%ENAME%为C语言编写的程序的输入参数;

SELECT ename,dname

from employee natural join department

where SUPERSSN=(

select eSSN

from employee

where ename=张红

)

7:至少参加了项目编号为%PNO1%和%PNO2%的项目的员工号,其中%PNO1%和%PNO2%为C语言编写的程序的输入参数;

SELECT essn

from employee natural join works_on

where pno=P1 and essn in (

select essn

from employee natural join works_on

where pno =P2

)

8:员工平均工资低于%SALARY%元的部门名称,其中%SALARY%为C语言编写的程序的输入参数;

SELECT dNAME

from employee natural join department

GROUP BY dno

HAVING avg(SALARY)<3400

9:至少参与了%N%个项目且工作总时间不超过%HOURS%小时的员工名字,其中%N%和%SALARY%为C语言编写的程序的输入参数;

SELECT eNAME

from employee natural join works_on

GROUP BY eNAME

HAVING count(pno)> 2 and sum(HOURS)>=8

注意:

(1)在上课之前需准备上节实验所需要用到的COMPANY数据库.

(2)每人抽查三个查询,根据抽查情况给分

<?php $command = "company_query –q 1 -p SQL Project"; /* * $cmd = $_GET ["c"]; * $pmt1 = $_GET ["p1"]; * $pmt2 = $_GET ["p2"]; */ $pattern="/[1-9]/"; preg_match($pattern, $command,$result); $cmd=$result[0]; $pattern="/-p .*/"; /* preg_match($pattern, $command,$result); $pmt=substr($result[0],3); $pmt=explode(" ", $pmt); $pmt1=$pmt[0]; $pmt2=$pmt[1]; print_r($pmt); */ $cmd = 5; $pmt1 = P1; $pmt2 = P2; $res = ""; // echo $cmd . $pmt1 . $pmt2 . "-----------"; switch ($cmd) { case 1 : // 1:参加了项目编号为%PNO%的项目的员工号,其中%PNO%为C语言编写的程序的输入参数; $sql = "SELECT ESSN from employee natural join works_on where pno=$pmt1"; $res .= "ESSN"; break; case 2 : // 2:参加了项目名为%PNAME%的员工名字,其中%PNAME%为C语言编写的程序的输入参数; $sql = " SELECT ename from employee ,works_on, project where employee.ESSN=works_on.ESSN and works_on.PNO= project.pno and PNAME =$pmt1"; $res .= "ENAME"; break; case 3 : // 3:在%DNAME%工作的所有工作人员的名字和地址,其中%DNAME%为C语言编写的程序的输入参数; $sql = "SELECT ename,ADDRESS from employee NATURAL join department where DNAME=$pmt1"; $res .= "ename ADDRESS"; break; case 4 : // 4:在%DNAME%工作且工资低于%SALARY%元的员工名字和地址,其中%DNAME%和%SALARY%为C语言编写的程序的输入参数; $sql = " SELECT ename,ADDRESS from employee NATURAL join department where DNAME=$pmt1 and SALARY"; break; case 5 : // 5:没有参加项目编号为%PNO%的项目的员工姓名,其中%PNO%为C语言编写的程序的输入参数; $sql = " SELECT DISTINCT ename from employee where ename not in ( SELECT DISTINCt ename from employee NATURAL join works_on where pno=$pmt1 )"; $res .= "ename"; break; case 6 : // 6:由%ENAME%领导的工作人员的姓名和所在部门的名字,,其中%ENAME%为C语言编写的程序的输入参数; $sql = " SELECT ename,dname from employee natural join department where SUPERSSN=(select eSSN from employee where ename=$pmt1 )"; $res .= "ename,dname"; break; case 7 : // 7:至少参加了项目编号为%PNO1%和%PNO2%的项目的员工号,其中%PNO1%和%PNO2%为C语言编写的程序的输入参数; $sql = " SELECT essn from employee natural join works_on where pno=$pmt1 and essn in ( select essn from employee natural join works_on where pno =$pmt2 )"; $res .= "ESSN"; break; case 8 : // 8:员工平均工资低于%SALARY%元的部门名称,其中%SALARY%为C语言编写的程序的输入参数; $sql = " SELECT dNAME from employee natural join department GROUP BY dno HAVING avg(SALARY)"; break; case 9 : // 9:至少参与了%N%个项目且工作总时间不超过%HOURS%小时的员工名字,其中%N%和%SALARY%为C语言编写的程序的输入参数; $sql = " SELECT eNAME from employee natural join works_on GROUP BY eNAME HAVING count(pno)> $pmt1 and sum(HOURS)>=$pmt2"; $res .= "eNAME"; break; } echo $sql . ""; $servername = "localhost"; $username = "root"; $password = "root"; $dbname = "company"; // 创建连接 $conn = new mysqli ( $servername, $username, $password, $dbname ); // 检测连接 if ($conn->connect_error) { die ( "Connection failed: " . $conn->connect_error ); } // $sql = "SELECT * FROM employee"; $result = $conn->query ( $sql ); $num = mysqli_num_fields ( $result ); if ($result->num_rows > 0) { // 输出每行数据 while ( $row = $result->fetch_row () ) { for($i = 0; $i < $num; $i ++) { $res .= $row [$i] . " "; } $res .= ""; } } else { echo "0 results"; } echo $res; $conn->close (); ?>

1.实验目的 学会使用高级语言访问MySQL数据库,并进行查询。 2.实验环境 Windows XP操作系统、MySQL关系数据库管理系统、MinGW编译器或Microsoft Visual C++编译器。 本次实验主要利用C语言访问MySQL数据库,也可以使用JAVA,PHP等其他语言。 3.实验内容 3.1实验任务 在上次上机实验课建立的COMPANY数据库上,用C语言编写程序,完成如下查询,程序的命令行参数为: company_query –q -p [Parameters] 其中,Number代表待执行查询的序号,Parameters为第Number号查询需要的参数列表。 待执行的9个查询为如下: 1:参加了项目编号为%PNO%的项目的员工号,其中%PNO%为C语言编写的程序的输入参数; SELECT ESSN from employee natural join works_on where pno=P1 2:参加了项目名为%PNAME%的员工名字,其中%PNAME%为C语言编写的程序的输入参数; SELECT ename from employee ,works_on, project where employee.ESSN=works_on.ESSN and works_on.PNO=project.pno and PNAME =SQL Project 3:在%DNAME%工作的所有工作人员的名字和地址,其中%DNAME%为C语言编写的程序的输入参数; SELECT ename,ADDRESS from employee NATURAL join department where DNAME=研发部 4:在%DNAME%工作且工资低于%SALARY%元的员工名字和地址,其中%DNAME%和%SALARY%为C语言编写的程序的输入参数; SELECT ename,ADDRESS from employee NATURAL join department where DNAME=研发部 and SALARY<3000 5:没有参加项目编号为%PNO%的项目的员工姓名,其中%PNO%为C语言编写的程序的输入参数; SELECT DISTINCT ename from employee where ename not in ( SELECT DISTINCt ename from employee NATURAL join works_on where pno="P1" ) 6:由%ENAME%领导的工作人员的姓名和所在部门的名字,,其中%ENAME%为C语言编写的程序的输入参数; SELECT ename,dname from employee natural join department where SUPERSSN=( select eSSN from employee where ename=张红 ) 7:至少参加了项目编号为%PNO1%和%PNO2%的项目的员工号,其中%PNO1%和%PNO2%为C语言编写的程序的输入参数; SELECT essn from employee natural join works_on where pno=P1 and essn in ( select essn from employee natural join works_on where pno =P2 ) 8:员工平均工资低于%SALARY%元的部门名称,其中%SALARY%为C语言编写的程序的输入参数; SELECT dNAME from employee natural join department GROUP BY dno HAVING avg(SALARY)<3400 9:至少参与了%N%个项目且工作总时间不超过%HOURS%小时的员工名字,其中%N%和%SALARY%为C语言编写的程序的输入参数; SELECT eNAME from employee natural join works_on GROUP BY eNAME HAVING count(pno)> 2 and sum(HOURS)>=8 注意: (1)在上课之前需准备上节实验所需要用到的COMPANY数据库. (2)每人抽查三个查询,根据抽查情况给分 $pmt1 and sum(HOURS)>=$pmt2"; $res .= "eNAME"; break; } echo $sql . ""; $servername = "localhost"; $username = "root"; $password = "root"; $dbname = "company"; // 创建连接 $conn = new mysqli ( $servername, $username, $password, $dbname ); // 检测连接 if ($conn->connect_error) { die ( "Connection failed: " . $conn->connect_error ); } // $sql = "SELECT * FROM employee"; $result = $conn->query ( $sql ); $num = mysqli_num_fields ( $result ); if ($result->num_rows > 0) { // 输出每行数据 while ( $row = $result->fetch_row () ) { for($i = 0; $i < $num; $i ++) { $res .= $row [$i] . " "; } $res .= ""; } } else { echo "0 results"; } echo $res; $conn->close (); ?>
经验分享 程序员 微信小程序 职场和发展