PHP 之 Mysql 操作2
mysql> desc customers+------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | customerid | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(50) | NO | | NULL | | | address | char(100) | NO | | NULL | | | city | char(30) | NO | | NULL | | +------------+------------------+------+-----+---------+----------------+
mysql> select*from customers; +------------+-----------------+--------------------+--------------+ | customerid | name | address | city | +------------+-----------------+--------------------+--------------+ | 3 | Julie Smith | 25 Oak Street | Airport West | | 4 | Alan Wong | 1/47 Haines Avenue | Box Hill | | 5 | Michelle Arthur | 357 North Road | Yarraville | +------------+-----------------+--------------------+--------------+
mysql> select * from orders; +---------+------------+--------+------------+ | orderid | customerid | amount | date | +---------+------------+--------+------------+ | 1 | 3 | 69.98 | 2007-04-02 | | 2 | 1 | 49.99 | 2007-04-15 | | 3 | 2 | 74.98 | 2007-04-19 | | 4 | 3 | 24.99 | 2007-05-01 | +---------+------------+--------+------------+
1. 插入
mysql> insert into customers values(null,'张三','碧石东街三号','鄂州'); mysql> select*from customers; +------------+-----------------+--------------------+--------------+ | customerid | name | address | city | +------------+-----------------+--------------------+--------------+ | 3 | Julie Smith | 25 Oak Street | Airport West | | 4 | Alan Wong | 1/47 Haines Avenue | Box Hill | | 5 | Michelle Arthur | 357 North Road | Yarraville | | 6 | 张三 | 碧石东街三号 | 鄂州 | +------------+-----------------+--------------------+--------------+
2. 查询
mysql> select name,city from customers; +-----------------+--------------+ | name | city | +-----------------+--------------+ | Julie Smith | Airport West | | Alan Wong | Box Hill | | Michelle Arthur | Yarraville | | 张三 | 鄂州 | +-----------------+--------------+
(1)获取满足特定条件的数据
mysql> select * from customers where customerid=3; +------------+-------------+---------------+--------------+ | customerid | name | address | city | +------------+-------------+---------------+--------------+ | 3 | Julie Smith | 25 Oak Street | Airport West | +------------+-------------+---------------+--------------+
or && and 组合
mysql> select * from customers where customerid=3 or customerid=4; +------------+-------------+--------------------+--------------+ | customerid | name | address | city | +------------+-------------+--------------------+--------------+ | 3 | Julie Smith | 25 Oak Street | Airport West | | 4 | Alan Wong | 1/47 Haines Avenue | Box Hill | +------------+-------------+--------------------+--------------+
(2) 多个表格获取数据
mysql> select orders.orderid,orders.amount from customers,orders where -> customers.name='Julie Smith' and customers.customerid = orders.customerid; +---------+--------+ | orderid | amount | +---------+--------+ | 1 | 69.98 | | 4 | 24.99 | +---------+--------+
(3)查找不匹配行 左关联 , 左关联是在两个表之间指定的关联条件下匹配的数据行，如果右边的表中没有匹配行，结果就会增加一行
mysql> select customers.customerid,customers.name,orders.orderid from customers -> left join orders on customers.customerid=orders.customerid; +------------+-----------------+---------+ | customerid | name | orderid | +------------+-----------------+---------+ | 3 | Julie Smith | 1 | | 3 | Julie Smith | 4 | | 4 | Alan Wong | NULL | | 5 | Michelle Arthur | NULL | | 6 | 张三 | NULL | +------------+-----------------+---------+
(4) 以特定顺序获取数据
mysql> select name,address from customers order by name; +-----------------+--------------------+ | name | address | +-----------------+--------------------+ | Alan Wong | 1/47 Haines Avenue | | Julie Smith | 25 Oak Street | | Michelle Arthur | 357 North Road | | 张三 | 碧石东街三号 | +-----------------+--------------------+
mysql> select name,address from customers order by name asc;
mysql> select name,address from customers order by name desc; descending降序
(5) 分组与合计数据
mysql> select avg(amount) from orders; +-------------+ | avg(amount) | +-------------+ | 54.985002 | +-------------+
合计函数：
AVG(列)
MIN(列)
MAX(列)
SUN(列)
(6) 子查询
最常见的用法是用一个查询结果作为另一个查询的比较条件,例如希望找到一个金额最大的订单
mysql> select customerid,amount from orders where amount = ( select max(amount) from orders); +------------+--------+ | customerid | amount | +------------+--------+ | 2 | 74.98 | +------------+--------+
3.更新数据库记录
mysql> update books set price = price*1.1; //图书价格整体提交10%
mysql> update customers set address='长沙' where customerid=4;
4.创建后修改表
mysql> alter table customers modify name char(70) not null; //modify 用来修改列的类型而不是名称 mysql> desc customers; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | customerid | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(70) | NO | | NULL | | | address | char(100) | NO | | NULL | | | city | char(30) | NO | | NULL | | +------------+------------------+------+-----+---------+----------------+
增加一列
mysql> alter table orders add tax float(6,2) after amount; mysql> select*from orders; +---------+------------+--------+------+------------+ | orderid | customerid | amount | tax | date | +---------+------------+--------+------+------------+ | 1 | 3 | 69.98 | NULL | 2007-04-02 | | 2 | 1 | 49.99 | NULL | 2007-04-15 | | 3 | 2 | 74.98 | NULL | 2007-04-19 | | 4 | 3 | 24.99 | NULL | 2007-05-01 | +---------+------------+--------+------+------------+
删除一列
mysql> alter table orders drop tax;
5.删除数据库中记录
delete from customers where customerid = 5;
6.表与数据库的删除
drop table table;
drop database database;
