mysql怎么给表设置查询语句_mysql表的操作与查询

//建表

//$sql="create table lo_article (".

// "id int not null auto_increment,".

// "title varchar(100) not null,".

// "author varchar(40) not null,".

// "create_date date ,".

// "primary key (id))engine=innodb default charset=utf8;";

//$res=mysqli_query($conn,$sql);

//if(!$res){

// die('table create fail');

//}

//echo "success
";

//

//mysqli_close($conn);

//删表

//$sql="drop table lo_article ;";

//$res=mysqli_query($conn,$sql);

//if(!$res){

// die('table drop fail');

//}

//echo "success
";

//

//mysqli_close($conn);

mysqli_query($conn,"set names utf8");//设置编码 防止中文乱码

//插入数据

//

//$title="hello world";

//$author="earth";

//$create_date="2020-10-19";

//$sql="insert into lo_article ".

// "(title,author,create_date)".

// "values ".

// "('$title','$author','$create_date')";

//$res=mysqli_query($conn,$sql);

//if(!$res){

// die('insert fail');

//}

//echo "success
";

//查询数据

$sql="select id,title,author,create_date from lo_article";$res=mysqli_query($conn,$sql);if(!$res){die('select fail');

}echo '

test

';echo '

ID

标题

作者

提交日期

//while($row = mysqli_fetch_array($res, MYSQLI_ASSOC))

while($row = mysqli_fetch_assoc($res))

{

echo "

{$row['id']} ".

"

{$row['title']} ".

"

{$row['author']} ".

"

{$row['create_date']} ".

"

";

}

//while($row = mysqli_fetch_array($res, MYSQLI_NUM))

//{

// echo "

{$row['0']} ".

// "

{$row['1']} ".

// "

{$row['2']} ".

// "

{$row['3']} ".

// "

";

//}

echo '

';

//释放内存

mysqli_free_result($retval);

mysqli_close($conn);

//更新数据

//$sql="update lo_article set title='this is problem' where id=2 ";

//$res=mysqli_query($conn,$sql);

//if(!$res){

// die('update fail');

//}

//echo "success
";

//删除数据

//$sql="delete from lo_article where id=3 ";

//$res=mysqli_query($conn,$sql);

//if(!$res){

// die('delete fail');

//}

//echo "success
";

mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行

//事务

mysqli_begin_transaction($conn);

if(!mysqli_query($conn,"insert into test (id) values (10)"))

{

mysqli_query($conn,'rollback');

}

if(!mysqli_query($conn,"insert into test (id) values (11)"))

{

mysqli_query($conn,'rollback');

}

mysqli_commit($conn);

mysqli_close($conn);