配置好数据库连接后,你就可以通过下面的语法来使用了:
1
|
$connection
= \Yii::
$app
->db;
|
你可以参考 [[yii\db\Connection]] 以了解有哪些属性可以配置。而且你可以配置多个连接,在应用程序中同时使用它们:
1
2
|
$primaryConnection
= \Yii::
$app
->db;
$secondaryConnection
= \Yii::
$app
->secondDb;
|
如果你不想把数据库连接定义为应用程序的组件,你也可以直接创建一个实例:
1
2
3
4
5
6
|
$connection
=
new
\yii\db\Connection([
'dsn'
=>
$dsn
,
'username'
=>
$username
,
'password'
=>
$password
,
]);
$connection
->open();
|
提示:如果你需要在建立连接时执行额外的SQL查询,你可以添加如下配置:
1234567891011121314return
[
// ...
'components'
=> [
// ...
'db'
=> [
'class'
=>
'yii\db\Connection'
,
// ...
'on afterOpen'
=>
function
(
$event
) {
$event
->sender->createCommand(
"SET time_zone = 'UTC'"
)->execute();
}
],
],
// ...
];
基本SQL查询
有了数据库连接实例,你可以使用 [[yii\db\Command]] 来执行SQL查询。
SELECT
返回多行数据:
1
2
|
$command
=
$connection
->createCommand(
'SELECT * FROM post'
);
$posts
=
$command
->queryAll();
|
返回单行数据:
1
2
|
$command
=
$connection
->createCommand(
'SELECT * FROM post WHERE id=1'
);
$post
=
$command
->queryOne();
|
返回列数据:
1
2
|
$command
=
$connection
->createCommand(
'SELECT title FROM post'
);
$titles
=
$command
->queryColumn();
|
返回统计数:
1
2
|
$command
=
$connection
->createCommand(
'SELECT COUNT(*) FROM post'
);
$postCount
=
$command
->queryScalar();
|
UPDATE, INSERT, DELETE etc.
对于非查询语句,你可以使用[[ yii\db\Command]]的 execute
方法:
1
2
|
$command
=
$connection
->createCommand(
'UPDATE post SET status=1 WHERE id=1'
);
$command
->execute();
|
也可以使用下面的语法,会自动处理好表名和列名引用:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
// INSERT
$connection
->createCommand()->insert(
'user'
, [
'name'
=>
'Sam'
,
'age'
=> 30,
])->execute();
// INSERT multiple rows at once
$connection
->createCommand()->batchInsert(
'user'
, [
'name'
,
'age'
], [
[
'Tom'
, 30],
[
'Jane'
, 20],
[
'Linda'
, 25],
])->execute();
// UPDATE
$connection
->createCommand()->update(
'user'
, [
'status'
=> 1],
'age > 30'
)->execute();
// DELETE
$connection
->createCommand()->
delete
(
'user'
,
'status = 0'
)->execute();
|
引用表名和列名
大多数情况下,你使用如下语法引用表名和列名:
1
2
|
$sql
=
"SELECT COUNT([[$column]]) FROM {{$table}}"
;
$rowCount
=
$connection
->createCommand(
$sql
)->queryScalar();
|
在上述代码中 [[$column]]
将被转换为合适的列名引用,而 {{$table}}
将被转换为表名引用。
对于表名,有一个特殊变量 {{%$table}}
,会自动为表名添加前缀(如果有的话):
1
2
|
$sql
=
"SELECT COUNT([[$column]]) FROM {{%$table}}"
;
$rowCount
=
$connection
->createCommand(
$sql
)->queryScalar();
|
上述代码将会应用于 tbl_table
,如果你在配置文件中配置了如下的表前缀的话:
1
2
3
4
5
6
7
8
9
10
|
return
[
// ...
'components'
=> [
// ...
'db'
=> [
// ...
'tablePrefix'
=>
'tbl_'
,
],
],
];
|
另外一个可选方法是使用[[yii\db\Connection::quoteTableName()]]和 [[yii\db\Connection::quoteColumnName()]] 方法来手动引用:
1
2
3
4
|
$column
=
$connection
->quoteColumnName(
$column
);
$table
=
$connection
->quoteTableName(
$table
);
$sql
=
"SELECT COUNT($column) FROM $table"
;
$rowCount
=
$connection
->createCommand(
$sql
)->queryScalar();
|
在Model 或 Controller中获取表前缀
var_dump(Admin::getDb()->tablePrefix); exit();
预备声明(Prepared statements)
为了安全传递查询参数,你可以使用预备声明(prepared statements),(译注:先声明参数,对用户输入进行escape后,进行参数替换,主要为了防止SQL注入):
1
2
3
|
$command
=
$connection
->createCommand(
'SELECT * FROM post WHERE id=:id'
);
$command
->bindValue(
':id'
,
$_GET
[
'id'
]);
$post
=
$command
->query();
|
此外,使用预备声明还可以对查询命令进行复用,如下使用不同的参数查询只需要准备一次command:
1
2
3
4
5
6
7
8
|
$command
=
$connection
->createCommand(
'DELETE FROM post WHERE id=:id'
);
$command
->bindParam(
':id'
,
$id
);
$id
= 1;
$command
->execute();
$id
= 2;
$command
->execute();
|
事务(Transaction)
你可以向下面这样执行一个数据库事务:
1
2
3
4
5
6
7
8
9
|
$transaction
=
$connection
->beginTransaction();
try
{
$connection
->createCommand(
$sql1
)->execute();
$connection
->createCommand(
$sql2
)->execute();
// ... 执行查询语句 ...
$transaction
->commit();
}
catch
(Exception
$e
) {
$transaction
->rollBack();
}
|
还可以嵌套事务:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
// 外层事务
$transaction1
=
$connection
->beginTransaction();
try
{
$connection
->createCommand(
$sql1
)->execute();
// 内层事务
$transaction2
=
$connection
->beginTransaction();
try
{
$connection
->createCommand(
$sql2
)->execute();
$transaction2
->commit();
}
catch
(Exception
$e
) {
$transaction2
->rollBack();
}
$transaction1
->commit();
}
catch
(Exception
$e
) {
$transaction1
->rollBack();
}
|