ThinkPHP6.0 database

##ThinkPHP6 Database

ThinkORM library
think\facade\Db
) to call
Db::

1, phpMyAdmin (web page database management)

2, Navicat for MySql (windows software database management)

2. Create database

1, Administrator table

CREATE TABLE `shop_admin` (

`uid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'User ID',
`account` varchar(50) NOT NULL COMMENT 'Account',
`password` char(32) NOT NULL COMMENT 'password',
`name` varchar(50) NOT NULL COMMENT 'name',
`status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'State 1 on and 2 off',
`add_time` int(10) unsigned NOT NULL COMMENT 'Add time',
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='Backend Administrator';
##INSERT INTO `shop_admin` VALUES (1, 'ouyangke' , 'e10adc3949ba59abbe56e057f20f883e', 'Ouyang Ke', 1, 1576080000);
2. Product classification table

DROP TABLE IF EXISTS `shop_cat`;

CREATE TABLE `shop_cat` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(50) NOT NULL COMMENT 'Category name' ,
`status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'Status 1 on 2 off',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COMMENT='Classification table';
##INSERT INTO `shop_cat` VALUES (1, 'Women's clothing', 1);

INSERT INTO `shop_cat` VALUES (2, 'Men's clothing', 1);
INSERT INTO `shop_cat` VALUES (3, 'Maternity', 1);
INSERT INTO `shop_cat` VALUES (4, 'Children's clothing', 1);
INSERT INTO `shop_cat` VALUES (5, 'TV', 1);
INSERT INTO `shop_cat` VALUES ( 6, 'Mobile phone', 1);
INSERT INTO `shop_cat` VALUES (7, 'Computer', 1);
3. Product list

CREATE TABLE `shop_goods` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ' Product ID',

`cat` int(10) unsigned NOT NULL DEFAULT ' 1' COMMENT 'Category ID',
`title` varchar(200) NOT NULL COMMENT 'Product title',
`price` double(10,2) unsigned NOT NULL COMMENT 'Price ',
`discount` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'discount',
`stock` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'Stock',
`status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'Status 1 on 2 off 3 Delete',
`add_time` int(10) unsigned NOT NULL COMMENT 'Add time',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT= 20 DEFAULT CHARSET=utf8mb4 COMMENT='Product List';

##INSERT INTO `shop_goods` VALUES (1, 1, 'Cloud-like light fairy skirt high-end beaded waist Long skirt elegant sleeveless dress', 279.99, 0, 1100, 1, 1576080000);
INSERT INTO `shop_goods` VALUES (2, 1, 'High-cold royal sister style corduroy a-line dress for women autumn and winter 2019 New year's new waist slimming retro skirt', 255.90, 0, 100, 1, 1576080000);

4. Menu table

CREATE TABLE `shop_menu` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`title` varchar(50) NOT NULL COMMENT 'Menu name',
`fid ` int(10) NOT NULL COMMENT 'Parent ID',
`status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'Status 1 on 2 off',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COMMENT='Left menu table';

INSERT INTO `shop_menu ` VALUES (1, 'Product Management', 0, 1);
INSERT INTO `shop_menu` VALUES (2, 'Product List', 1, 1);
INSERT INTO `shop_menu ` VALUES (3, 'Product Category', 1, 1);
INSERT INTO `shop_menu` VALUES (4, 'User Management', 0, 1);
INSERT INTO `shop_menu ` VALUES (5, 'User List', 4, 1);
INSERT INTO `shop_menu` VALUES (6, 'Shopping Cart', 4, 1);
INSERT INTO `shop_menu ` VALUES (7, 'User Address', 4, 1);
INSERT INTO `shop_menu` VALUES (8, 'Order Management', 4, 1);
INSERT INTO `shop_menu ` VALUES (9, 'Backend management', 0, 1);
INSERT INTO `shop_menu` VALUES (10, 'Administrator list', 9, 1);
INSERT INTO ` shop_menu` VALUES (11, 'Personal Center', 9, 1);
INSERT INTO `shop_menu` VALUES (12, 'Left Menu', 9, 1);

3. Execute native MySql

query method is used to perform MySql query operations

public function index(){
$ query = Db::query("SELECT * FROM `shop_goods` where status=1");
print_r($query);
}

2 ,

execute method is used to perform MySql new and modified operations

public function index(){
$execute = Db::execute("INSERT INTO `shop_goods` VALUES (3, 1, '2019 Autumn and Winter New Fashion Simple Pure Cashmere Thickened Round Neck Cashmere Long Skirt Slim and Temperamental Dress for Women', 1179.00, 0, 200, 1, 1576080000)");
print_r($execute);
$execute = Db::execute("UPDATE `shop_goods` set `price`='1100' where `id`=3 ");
print_r($execute);
}

4. Query

1. Single data query find

find method query result does not exist , return null, otherwise return the result array

public function index(){
$find = Db::table('shop_goods') ->find(5);
print_r($find);
}

2. Multiple data query select

select The query result of the method is a two-dimensional array. If the result does not exist, an empty array is returned.

public function index(){
$select = Db::table('shop_goods')->select();
print_r($select);
}

3. Query the value of a certain field value

value The method query result does not exist and returns null

public function index(){
$value = Db::table('shop_goods')->value('title');
print_r($ value);
}

4. Query the value of a certain column column

column The method query result is not Exists, returns an empty array

public function index(){
$column = Db::table('shop_goods')-> column('title');
print_r($column);
$column = Db::table('shop_goods')->column('title','id') ;
print_r($column);
}

5. Add

1. Add a data insert

insert The method adds data successfully and returns the number of successfully added items. Normally, it returns 1

public function index( ){
$data = ['cat'=>'1','title'=>'Japanese romantic and warm wool knitted splicing mesh pleated mid-length waist dress' ,'price'=>'1598.35','add_time'=>1576080000];
$insert = Db::table('shop_goods')->insert($data);

print_r($insert);
}

2. Add a piece of data insertGetId

insertGetId The method adds data successfully and returns the auto-incremented primary key of the added data

public function index(){
$data = ['cat '=>'1','title'=>'Knitted sweater dress 2019 autumn and winter new style loose wool long-sleeved mid-length casual bottoming skirt for women','price'=>'690.00','add_time'= >1576080000];
$insert = Db::table('shop_goods')->insertGetId($data);
print_r($insert);
}

3. Add multiple pieces of data insertAll

insertAll The method adds data successfully and returns the number of successfully added pieces

public function index(){
$data = [
] ['cat'=>'1','title'=>'Autumn and Winter Thickened dress for women, extra long loose cashmere sweater, turtleneck pullover, over-the-knee sweater, versatile knitted long skirt','price'=>'658.00','add_time'=>1576080000],
[' cat'=>'1','title'=>'2019 New Autumn and Winter Lazy Style Loose Sweater Knitted Dress Retro Hong Kong Style Internet Celebrity Two-piece Set','price'=>'408.00','add_time'= >1576080000],
['cat'=>'2','title'=>'Men's long-sleeved T-shirt autumn round neck black and white T-shirt T solid color tops and bottoming shirts for men',' price'=>'99.00','add_time'=>1576080000]
];
$insert = Db::table('shop_goods')->insertAll($data );
print_r($insert);
}

6. Modify

1. Modify data update

update The method returns the number of affected data, and returns 0 if no data is modified

public function index(){
$data = ['price'=>'68'];
$update = Db::table('shop_goods')->where('id',8)-> ;update($data);
print_r($update);
}

2, self-increment inc

inc Method to increment the value of a field

public function index(){
$inc = Db::table ('shop_goods')->where('id',5)->inc('stock')->update();
print_r($inc);
# Increase the value of the field by 5
$inc = Db::table('shop_goods')->where('id',6)->inc('stock',5)->update ();
print_r($inc);
}

3. Decrement dec

dec Method to decrement the value of a field

public function index(){
#The value of the field minus 1
$dec = Db::table('shop_goods')->where('id',7)->dec('stock')->update();
print_r($dec);
# Subtract 5 from the field value
$dec = Db::table('shop_goods')->where('id',8)-> ;dec('stock',5)->update();
print_r($dec);
}

7. Delete

1. Delete data delete

delete Method returns the number of affected data, if not deleted it returns 0

public function index(){
# Delete data based on conditions
$delete = Db::table('shop_goods')->where('id',1)->delete();
print_r($ delete);
# Delete data with primary key 2
$delete = Db::table('shop_goods')->delete(2);
print_r( $delete);
#Delete the entire table data
$delete = Db::table('shop_goods')->delete(true);
print_r($ delete);
}

2. Soft deletionuseSoftDelete

It is not recommended to actually delete data for business data. The TP system provides a soft deletion mechanism

public function index(){
# Soft deletion
$delete = Db::table ('shop_goods')->useSoftDelete('status',3)->delete();
print_r($delete);
}

8. Other operations (self-study)

save The method writes data uniformly and automatically determines whether to add or update data (to determine whether there is a primary key in the written data) based on data).

public function index(){
# Add data
$data = ['cat'=>'2', 'title'=>'Metersbonwe three-quarter jeans women's 2018 summer new mid-waist slim washed jeans store model','price'=>'49.90','add_time'=>1576080000];

$save = Db::table('shop_goods')->save($data);
print_r($save);
# Modify data
$data = ['price'=>'99.00','id'=>3];
$save = Db::table('shop_goods')->save($data) ;
print_r($save);
}

Note: addition, deletion, checking and modification are routine operations

9. Data set

Thinkphp provides many methods for processing data sets

Method	Description
toArray	Convert to array
isEmpty	Is it empty?
all	All data
merge	Merge other data
diff	Compare arrays and return the difference set
flip	Exchange the keys and values in the data
intersect	Compare the arrays and return the intersection
keys	Return all key names in the data
pop	Delete the last element in the data
shift	Delete the first element in the data
unshift	Insert an element at the beginning of the data
push	Insert an element at the end
reduce	Return an array as a string by using a user-defined function
reverse	Rearrange data in reverse order
chunk	Data is separated into multiple data chunks
each	Execute a callback for each element of the data
filter	Use the callback function to filter the elements in the data
column	Return the specified column in the data
sort	Sort the data
order	Specify field sorting
shuffle	Shuffle the data
slice	Intercept a part of the data
map	Use the callback function to process the elements in the array
where	Filter elements in the array based on field conditions
whereLike	Like query filter elements
whereNotLike	Not Like filter element
whereIn	IN query elements in the filter array
whereNotIn	Not IN query the elements in the filter array
whereBetween	Between query the elements in the filter array
whereNotBetween	Not Between elements in the query filter array

public function index(){
$select = Db::table('shop_goods')->select();
if($select->isEmpty()){
echo '未找到数据';
}
print_r($select->toArray());
}

十、示例

controller代码

namespace app\controller;
use think\facade\View;
class Index{
public function index(){
$title = '商城';
$login = '欧阳克';
$menu = Db::table('shop_menu')->where('fid',0)->select();
$left = $menu->toArray();
foreach($left as &$left_v){
$left_v['lists'] = Db::table('shop_menu')->where('fid',$left_v['id'])->select();
}
$list = Db::table('shop_goods')->select();
$right = $list->toArray();
foreach($right as &$right_v){
$right_v['cat'] = Db::table('shop_cat')->where('id',$right_v['cat'])->value('name');
}
View::assign([
'title' => $title,
'login' => $login,
'left' => $left,
'right' => $right
]);
return View::fetch();
}
}

view代码：index.html

{include file="public/head" /}
{include file="public/left" /}
<div class="main" style="padding:10px;">
<div class="content">
<span>商品列表</span>
<button class="layui-btn layui-btn-sm" onclick="add()">添加</button>
<div></div>
</div>
                                                                                                                                                                             ;/th>
/th>
;

# & LT;/TR & GT;
& LT;/Thead & GT;
## & LT;
## {Volist name = "right" id = "right_v" }
}< /td>
#
right_v.price*($right_v.discount/10)}
                                                                                                               /if}
</td>
<td>{$right_v.stock}</td>
<td>{if $right_v['status']==1}开启{else/}关闭{/if}</td>
<td>{$right_v.add_time|date='Y-m-d'}</td>
<td>
<button class="layui-btn layui-btn-xs" onclick="edit()">编辑</button>
<button class="layui-btn layui-btn-danger layui-btn-xs" onclick="del()">删除</button>
</td>
</tr>
{/volist}
</tbody>
</table>
</div>
{include file="public/bottom" /}