用户输入
用户输入
This slide is based on Jeffrey D. Ullman's work, which can be download from his website.
这些查询有可能会从用户输入中获取常数.
粗心的代码可能会允许那些相当出乎意料的查询被构建和执行.
现在有关系
SELECT acct FROM Accounts WHERE name = n AND passwd = p这里不同的语言有不同的处理方式.
SELECT acct FROM Accounts
WHERE name = 'gates' --' AND
passwd = 'who cares?'
对于常规语言连接数据库的第三种方式是利用库的调用.
一般大型数据库的通用环境有下面三层体系结构.
数据库中存储物品、顾客等的信息.
交易逻辑中包含了诸如“当某人点击了 'checkout' 后我该做什么”之类的事情.
在许多 DB-access 语言中, 数据库就是一个
数据库服务器维持一定数量的
应用服务器发布语句(statements): 通常是查询和更新.
这里我们并不象(如嵌套 SQL)那样利用预处理器, 而是使用函数库.
C 通过下面四种类型的结构(struct)来连接数据库:
句柄是记录的指针. 头文件
函数
SQLALLocHandle(SQL_HANDLE_STMT, myCon, &myStat);
#include "sqlcli.h"
SQLHENV myEnv; /* 创建环境句柄 */
SQLHDBC myCon; /* 创建连接句柄 */
SQLHSTMT execStat; /* 创建语句句柄 */
SQLRETURN errorCode1, errorCode2, errorCode3;
errorCode1=SQLALLocHandle(SQL_HANDLE_ENV,
SQL_NULL_HANDLE, &myEnv);
if(!errorCode1){
errorCode2=SQLALLocHandle(SQL_HANDLE_DBC,
myEnv, &myCon);
if(!errorCode2){
errorCode3=SQLALLocHandle(SQL_HANDLE_STMT,
myCon, &execStat);
}
}
上面最后创建了句柄为
与语句句柄相关联且执行 SQL 语句的进程与动态 SQL 很相似. 都是先准备(prepare)再执行(execute).
SQLPrepare(execStat,
"SELECT beer, price FROM Sells
WHERE bar='Joe''s Bar'",
SQL_NTS);
SQLExecute(execStat);
如果对于语句
当执行的 SQL 语句是一个查询, 我们需要从结果关系中取出这些元组.
当我们取出一个元组后, 我们需要将该元组的组成部分(各个属性值)用到其他地方去.
每个组成部分通过函数
假设我们刚执行了
SELECT beer, price FROM Sells WHERE bar='Joe''s Bar'
将所得元组的两个分量值绑定到变量
SQLBindCol(execStat,1,,&theBeer,,); SQLBindCol(execStat,2,,&thePrice,,);
现在, 我们利用循环可以取出所有的元组.
while(SQLFetch(execStat)!=SQL_NO_DATA)
{
/* do something with theBeer and thePrice */
}
在数据库中新建一个名为 test_db3 的数据库.
#include <my_global.h>
#include <mysql.h>
int main(int argc, char **argv)
{
MYSQL *conn;
conn = mysql_init(NULL);
if (conn == NULL) {
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
exit(1);
}
if (mysql_real_connect(conn, "localhost", "haifeng", "34klq*", NULL, 0, NULL, 0) == NULL) {
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
exit(1);
}
if (mysql_query(conn, "create database test_db3")) {
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
exit(1);
}
mysql_close(conn);
}
使用 gcc 编译
gcc version.c -o version `mysql_config --cflags --libs`
如果出现下面的提示:
In file included from /usr/include/pthread.h:21:0,
from /usr/include/mysql/my_global.h:274,
from connect.c:1:
/usr/include/features.h:330:4: 警告：#warning _FORTIFY_SOURCE requires compiling with optimization (-O) [-Wcpp]
则在刚才的编译命令中加一个 -O 参数, 即
gcc version.c -o version `mysql_config --cflags --libs` -O
类似于
import java.sql.*;
Class.forName(com.mysql.jdbc.Driver);
Connection myCon =
DriverManager.getConnection(
<URL>, <username>, <password>
);
JDBC 提供了两个类:
这个连接类(Connection class) 有
Statement stat1 = myCon.createStatement();
PreparedStatement stat2 =
myCon.createStatement(
"SELECT beer, price FROM Sells " +
"WHERE bar = 'Joe''s Bar' "
);
JDBC 根据具体更改(也称为“更新”)来将查询分类.
之前定义的
stat1.executeUpdate(
"INSERT INTO Sells " +
"VALUES('Brass Rail','Bud',3.00)"
);
ResultSet menu = stat2.executeQuery();
方法
当
方法
即
使用下面的循环从每一个元组中得到
while ( menu.next() ) {
theBeer = menu.getString(1);
thePrice = menu.getFloat(2);
/*something with theBeer and thePrice*/
}
References:
http://zetcode.com/db/mysqljava/
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package helloworld;
import java.util.Date;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author haife
*/
public class HelloWorld {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
// TODO code application logic here
System.out.println("Hello Java!\n The Date and Time is: ");
System.out.println(new Date());
Connection con = null;
Statement st = null;
ResultSet rs = null;
String url = "jdbc:mysql://localhost:3306/test_bar";
String user = "root";
String password = "34klq*";
try {
con = DriverManager.getConnection(url, user, password);
st = con.createStatement();
rs = st.executeQuery("SELECT * FROM Bars");
System.out.println("name\t addr\t license\n");
while(rs.next()) {
System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(HelloWorld.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(HelloWorld.class.getName());
lgr.log(Level.WARNING, ex.getMessage(), ex);
}
}
}
}
package ExamplePackage;
import java.sql.*;
import java.util.*;
public class ConnectionManager {
static Connection con;
static String url;
public static Connection getConnection()
{
try
{
//String ipAddr="127.0.0.1";
//String port="5432";
//String dbname="test_db";
//String url = "jdbc:postgresql://"+ipAddr+":"+ port + "/"+dbname;
String url = "jdbc:postgresql://127.0.0.1:5432/test_db";
// assuming "DataSource" is your DataSource name
Class.forName("org.postgresql.Driver");
try
{
con = DriverManager.getConnection(url,"postgres","");
// assuming your SQL Server's username is "username"
// and password is "password"
}
catch (SQLException ex)
{
ex.printStackTrace();
}
}
catch(ClassNotFoundException e)
{
System.out.println(e);
}
return con;
}
}
PHP 解决了很多语言都碰到的非常重要的问题, 即.
PHP 的解决办法是: 双引号括起来的变量在引用时用它的值替代, 单引号则忠实地表示字符串.
$100 = "one hundred dollars"; $sue = 'You owe me $100.'; $joe = "You owe me $100.";
PHP 有两种数组:
数字型数组用下标
关联数组
若
一个环境可以表示成一个关联数组, 例如:
$myEnv = array ( "phptype" => "oracle", "hostspec" => "www.stanford.edu", "database" => "cs145db", "username" => "ullman", "password" => "notMyPW" );
在导入 DB library 及上面的
$myCon = DB::connect($myEnv);
它的参数是字符串. 当发生错误时返回一个错误码, 当成功时返回一个关系.
对于给定的变量
$beer = 'Bud'; $result = $myCon->query( "SELECT bar FROM Sells " . " WHERE beer = $beer;");
while ($bar=$result->fetchRow()){
//do something with $bar
}
使用mysqli
<?php
$servername = "localhost";
$username = "root";
$password = "34klq*";//更改为你使用的mysql服务器密码
$dbname = "test_bar";//"urlsdb";
$conn = new mysqli($servername, $username, $password, $dbname);
echo "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">";
echo "<html>";
echo "<head><title>测试连接MySQL</title>";
echo "<meta http-equiv=\"content-type\" content=\"text/html; charset=utf-8\" />";
echo "</head><body>";
if ($conn->connect_error){
die("连接失败: ".$conn->connect_error);
}else{
print("Successfully connected to MySQL! <br/>The database is: <b>".$dbname."</b><br/><br/><hr/>");
@mysqli_query($conn, "set names 'utf8'");//最好加一下，不然会有乱码
@mysqli_select_db($dbname, $conn);
$sql2="SELECT * FROM Bars";
$result=mysqli_query($conn,$sql2);
echo "<table border=1>";
while($row=mysqli_fetch_assoc($result))
{
echo "<tr><td>".$row['name']."</td>";
echo "<td>".$row['addr']."</td>";
echo "<td>".$row['license']."</td></tr>";
}
echo "</table>";
}
echo "</body></html>";
?>