MySQL中如果说需要对数据表内的null值进行匹配必须要使用IS NULL或者IS NOT NULL进行匹配,使用=是没有办法匹配NULL的。
IS NULL:如果为空
WHERE id IS NULL //匹配ID为NULL的值
IS NULL:如果不为空
WHERE id IS NOT NULL //匹配不为NULL的所有值
实例
<?php
$conf = require_once('conf.php');
$sql_connect = mysqli_connect($conf['host'],$conf['username'],$conf['password'],$conf['dbname']);
if(mysqli_connect_error($sql_connect)){
die('数据库连接失败'.mysqli_error($sql_connect));
}
$employees = array(
array("employee_id" => 1, "employee_name" => "Alice", "manager_id" => 3),
array("employee_id" => 2, "employee_name" => "Bob", "manager_id" => 3),
array("employee_id" => 3, "employee_name" => "Carol", "manager_id" =>0),
array("employee_id" => 4, "employee_name" => "David", "manager_id" => 2),
array("employee_id" => 5, "employee_name" => "Eve", "manager_id" =>0)
);
$drop_table = "DROP TABLE employees";
$drop_query = mysqli_query($sql_connect,$drop_table);
if(!$drop_query){
die('数据表删除失败'.mysqli_error($sql_connect));
}
$create_table = "CREATE TABLE IF NOT EXISTS employees(
employee_id INT(6),
employee_name VARCHAR(30),
manager_id INT(6)
)";
$create_table_query = mysqli_query($sql_connect,$create_table);
if(!$create_table_query){
die('数据表创建失败'.mysqli_error($sql_connect));
}
foreach($employees as $arr){
// echo '<pre>';
// print_r($arr);
$insert = "INSERT INTO employees (employee_id,employee_name,manager_id) VALUES ({$arr['employee_id']}, '{$arr['employee_name']}', {$arr['manager_id']}) ";
$insert_query = mysqli_query($sql_connect,$insert);
}
$update = "UPDATE employees
SET manager_id = NULL
WHERE manager_id = 0"; //INSERT INTO 无法直接插入NULL,我这里现插入0,然后再将0替换成NULL,以便后面做NULL的演示
$update_query = mysqli_query($sql_connect,$update);
if(!$update_query){
die('数据替换为null失败'.mysqli_error($sql_connect));
}
if(!$insert_query){
die('数据插入失败'.mysqli_error($sql_connect));
}
//找出所有没有经理的员工。
$isnull = "SELECT *
FROM employees
WHERE manager_id IS NULL";
$isnull_query = mysqli_query($sql_connect,$isnull);
if(!$isnull_query){
die('查询没有经理的员工失败'.mysqli_error($sql_connect));
}
echo '<table border = "1">';
echo '<tr><td>没有经理的员工</td></tr>';
while($arr = mysqli_fetch_array($isnull_query,MYSQLI_ASSOC)){
echo "<tr><td>{$arr['employee_name']}</td></tr>";
}
echo '</table>';
//找出所有有经历的员工
$isnotnull = "SELECT *
FROM employees
WHERE manager_id IS NOT NULL";
$innotnull_query = mysqli_query($sql_connect,$isnotnull);
if(!$innotnull_query){
die('查询有经理的员工失败'.mysqli_error($sql_connect));
}
echo '<table border = "1">';
echo '<tr><td>有经理的员工</td></tr>';
while($arrs = mysqli_fetch_array($innotnull_query,MYSQLI_ASSOC)){
echo "<tr><td>{$arrs['employee_name']}</td></tr>";
}
echo '</table>';
mysqli_close($sql_connect);



还没有评论,来说两句吧...