1. 数据是从表里面获取
例子: 默认进来商品列表页显示全部的商品
链接数据库
$connect = mysqli_connect("localhost", "root", "", "db_name")
==
例子: 默认进来商品列表页显示全部的商品
链接数据库
$connect = mysqli_connect("localhost", "root", "", "db_name")
==
mysqli_connect("localhost", "root", "")
mysqli_select_db($db, "db_name");
2. 查找商品信息
$sqlKeyword = "";
if ($keyword) {
$sqlKeyword = " where title like '%合肥%';";
}
$sql = "select * from product {$sqlKeyword};";
$result = mysqli_query($connect, $sql);//resouces
while ($row = mysqli_fetch_array($result,MYSQL_ASSOC) {
echo $row["title"];
echo $row['id'];
}
//udpate ,delete, insert
3、重点
mysqli_select_db($db, "db_name");
2. 查找商品信息
$sqlKeyword = "";
if ($keyword) {
$sqlKeyword = " where title like '%合肥%';";
}
$sql = "select * from product {$sqlKeyword};";
$result = mysqli_query($connect, $sql);//resouces
while ($row = mysqli_fetch_array($result,MYSQL_ASSOC) {
echo $row["title"];
echo $row['id'];
}
//udpate ,delete, insert
select * from product
where price > 50
group by price having count(price) > 1
order by id desc limit 10;
1、 先写基本选择表
2、再写where
3、 group by , having
4、排序 order by
5、 limit
4、
SELECT p.name,psxg.kucun FROM product as p left join product_sxg as psxg on p.sxg_id = psxg.id where p.id = 1
5、添加外键
ALTER TABLE edu ADD FOREIGN KEY(cv_id) REFERENCES cv(id) ON DELETE CASCADE;
ALTER TABLE edu ADD FOREIGN KEY(school_id) REFERENCES school(id) ON DELETE CASCADE;
ALTER TABLE tbl_name ADD INDEX index_name (col_name);
检查select语句是否使用了正确的索引,我们一般在select之前添加explain
ALTER TABLE cv ADD INDEX idx_gender(gender);
EXPLAIN SELECT * FROM cv WHERE gender = 1;
show index from product
查看索引
删除索引
ALTER TABLE product DROP INDEX product_cate1_index
对多个字段组合创建的索引
ALTER TABLE tbl_name ADD INDEX index_name (column1, column2);
假设有组合索引(A,B,C)三个字段,相当于创建了三个索引可以使用(以左为准)
(A,B,C)
(A,B)
(A)
alter table product drop foreign key product_ibfk_1
所以,查询的顺序很重要
生成不了(A,C)
7、关联查询
select
table2.customer_id, table2.order_id
from
table2 left join table1
on table1.customer_id=table2.customer_id
where table1.city='hangzhou';
select
table2.customer_id, table2.order_id
from
table2 left join table1
on table1.customer_id=table2.customer_id
where table1.customer_id = 163;
table2.customer_id, table2.order_id
from
table2 left join table1
on table1.customer_id=table2.customer_id
where table1.city='hangzhou';
select
table2.customer_id, table2.order_id
from
table2 left join table1
on table1.customer_id=table2.customer_id
where table1.customer_id = 163;
SELECT order_id,customer_id FROM `table2` where customer_id in (select customer_id from table1 where city='hangzhou');
第一步
select customer_id from table1 where city='hangzhou';
获取城市是hangzhou的custom_id
第二步把上面的查询结果套在里面
SELECT order_id,customer_id FROM `table2` where customer_id in ('163','baidu','tx');
select customer_id from table1 where city='hangzhou';
获取城市是hangzhou的custom_id
第二步把上面的查询结果套在里面
SELECT order_id,customer_id FROM `table2` where customer_id in ('163','baidu','tx');