《PHP MySQL和WEB开发》第十章笔记

<?
/*Unit 10 数据库的使用*/
//SQL=structured query language=结构化查询语言
//SQL有一个ANSI标准但是他和MYSQL还是有区别的
//SQL包括DDL（data definitely language）和DML（data manipulation language）

//插入数据的三种方法
/*
insert into customers values
(NULL,'Julie Smith','25 oak Street','Airport West');
()()...

insert into customers 
set name="",city="",address="";


insert into customers(name,city) values
('luhao','yucheng');

*/

//insert后面可以加LOW_PRIORITY（当数据不是从表格读出时等待并稍后插入）和DELAYED（插入的数据缓存）
//还可以IGNORE ignore 重复唯一键自动跳过
//在inset语句的末尾加上on duplicate key update experssion可以使常规的update修改重复值
//下次看的时候弄明白即可

echo <<<bookinsert
这里有一个book_insert.sql
通过cmd  mysql -u lustudy_web -p lustudy(数据库)<e:\wamp\www\test\bookinsert.sql来操作
的或者mysql<source d:/dbname.sql，代码如下
bookinsert;
echo "<pre><font color=\"#990066\">";
readfile("book_insert.sql");
echo "</font></pre>";

//后面的内容比较简单 …… 挑一些需要记忆的写上
//比较符= php为==
//between
//in('a','b') not in('c','d');
//like 模式匹配 _只匹配一个字符 %匹配任意字符
//regexp=rlike 匹配正则表达式，正则表达式忘光了。
//当代码中有一个字段时候应该写明他来自于哪个表 这样便于维护
/*
当查询效率不高时 可以这样查询：
select customers.name from customers,orders,order_items,books where 
customers.customerid=orders.customerid and 
orders.orderid=order_items.orderid and 
books.isbn=order_items.isbn and 
books.title like '%java%';
*/

//为了让我的结果和课本中一样 使用truncate 来清空数据表，在执行插入和查询。
//查询是null的时候 要用 is null不能用=null

//左关联 left join on 或者 left join (using customerid)

//as可以给表起别名  也可以给列起别名alias
//例如select c.name as nobyname ,c.customerid nobyid from customers as c left join orders as `or` using (customerid) where `or`.orderid is null;
//当连接的是表本身的时候 必须要起别名
//left join以左表为基准 right join以右表为基准
// order by asc/desc

/*10.3.4分组与合计数据*/
//avg count min max std=stddev（标准背离值）sum

/*
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
*/

//ANSISQL：使用了一个合计函数 或者group by分组 那么这些列一定要在select中罗列出来
/*
测试sql
*/
echo <<<studysql
<pre>
use lustudy;
#select * from customers;
#select c.name as nobyname ,c.customerid nobyid from customers as c left join orders as `or` using (customerid) where `or`.orderid is null;
#求平均数(无意义)
#select avg(price*quantity) as avgprice from order_items left join books using(isbn);
#求订单的总价格
#select sum(price*quantity) as sumprive from order_items left join books using(isbn);
#求客单价
#select sum(price*quantity)/(select count(orderid) from orders) as avgprice from order_items left join books using(isbn);
#将两个订单的客户改成45
#update orders set customerid=5 where customerid=2

#将客户的订单进行消费总金额的排序(四表联查分组排序)
#select customers.name as buyname,sum(books.price*order_items.quantity) as buyprice from customers inner join orders using(customerid) inner join order_items using(orderid) inner join books using(isbn) group by customers.name order by buyprice asc;
#用在group by 或者分组函数后面的having
#select books.title,sum(price*quantity) as avgprice from order_items left join books using(isbn) group by books.title having avgprice > 25 order by avgprice asc;
</pre>
studysql
/*选择要返回的行*/
//limit 1,2 从第1+1行开始返回2行,注意不带括号，请注意 limit 不属于ANSI SQL
#select books.title,sum(price*quantity) as avgprice from order_items left join books using(isbn) group by books.title having avgprice > 25 order by avgprice asc limit 1,1;

//相比于连接查询，子查询更容易理解
#子查询返回最贵的图书名字和价格
#select title,price from books where price=(select min(price) from books);
#这个查询将产生同样的输出
#select title, price from books order by price asc limit 0,1;

//子查询的操作符 anny=in=some,all
#子查询any in some all
#select title,price from books where price > any (select price from books where price <25 );
#select title,price from books where price > some (select price from books where price >25 );
#select title,price from books where price > all (select price from books where price >25 );
#select title,price from books where price in (select price from books where price >25 );

#查询没有被订购的图书
#先插入一个没有被订购的图书
#insert into books values('0-000-00000-1','Howroad','Loser Saying',25);
#select title from books where not exists (select * from books, order_items where order_items.isbn=books.isbn);
#select * from books,order_items where order_items.isbn=books.isbn;
#select * from order_items left join books using(isbn);
#观察select title from books where not exists (select * from books, order_items where order_items.isbn=books.isbn);
#观察select title from books where not exists (select * from order_items where order_items.isbn=books.isbn);
#关联子查询：查询匹配外部行的内部行

#使用子查询作为临时表,因为子查询作为一个表放在了from 后面 所以every drived table must have its own alias.
#select title from(select * from books where isbn='0-000-00000-1') as howroadtb;

//总结：数据的增删改 不用带TABLE关键字 而列名称的增删改则需要
/*
insert into books value();
delete from books where;
update books set XX="" where;
alter table books
add XX/modify XX/drop XX

//delete from bookes 的区别是auto_increment不重置，而truncate table 则会重置auto_increment.

表的删除和数据库的删除
drop table books;
drop database book;

*/



















?>

source e:/wamp/www/test/study.sql

use lustudy;
#select * from customers;
#select c.name as nobyname ,c.customerid nobyid from customers as c left join orders as `or` using (customerid) where `or`.orderid is null;
#求平均数(无意义)
#select avg(price*quantity) as avgprice from order_items left join books using(isbn);
#求订单的总价格
#select sum(price*quantity) as sumprive from order_items left join books using(isbn);
#求客单价
#select sum(price*quantity)/(select count(orderid) from orders) as avgprice from order_items left join books using(isbn);
#将两个订单的客户改成45
#update orders set customerid=5 where customerid=2

#将客户的订单进行消费总金额的排序(四表联查分组排序)
#select customers.name as buyname,sum(books.price*order_items.quantity) as buyprice from customers inner join orders using(customerid) inner join order_items using(orderid) inner join books using(isbn) group by customers.name order by buyprice asc;
#用在group by 或者分组函数后面的having
#select books.title,sum(price*quantity) as avgprice from order_items left join books using(isbn) group by books.title having avgprice > 25 order by avgprice asc;
#limit返回指定的行
#select books.title,sum(price*quantity) as avgprice from order_items left join books using(isbn) group by books.title having avgprice > 25 order by avgprice asc limit 1,1;
#子查询返回最贵的图书名字和价格
#select title,price from books where price=(select min(price) from books);
#这个查询将产生同样的输出
#select title, price from books order by price asc limit 0,1;
#子查询any in some all
#select title,price from books where price > any (select price from books where price <25 );
#select title,price from books where price > some (select price from books where price >25 );
#select title,price from books where price > all (select price from books where price >25 );
#select title,price from books where price in (select price from books where price >25 );

#查询没有被订购的图书
#先插入一个没有被订购的图书
#insert into books values('0-000-00000-1','Howroad','Loser Saying',25);
#select title from books where not exists (select * from books, order_items where order_items.isbn=books.isbn);
#select * from books,order_items where order_items.isbn=books.isbn;
#select * from order_items left join books using(isbn);
#观察select title from books where not exists (select * from books, order_items where order_items.isbn=books.isbn);
#观察select title from books where not exists (select * from order_items where order_items.isbn=books.isbn);
#关联子查询：查询匹配外部行的内部行

#使用子查询作为临时表,因为子查询作为一个表放在了from 后面 所以every drived table must have its own alias.
#select title from(select * from books where isbn='0-000-00000-1') as howroadtb;

#更新数据库记录
#update books set price=1.1*price;
#update books set price=25 where author='howroad';

#创建后修改表
/*create table luhaotb(
skillid int unsigned auto_increment not null primary key,
skillname char(50) not null,
skillscore float(3,2),
remark text
);*/
#insert into luhaotb values(null,'PHP',27.65,'studying');
#Alter table luhaotb modify skillscore float(5,2);
#update luhaotb set skillscore=27.56 where skillid=1;
#添加新列
alter table luhaotb add doilike char(10) default "yes";
alter table 

use lustudy;
insert into customers values
(3,'Julie Smith','25 oak Street','Airport West'),
(4,'Alan Wong','1/47Haines Avenue','Box Hill'),
(5,'Michelle Arthur','357North Road','Yarraville');

insert into orders values
(null,3,69.98,'2007-04-02'),
(null,1,49.99,'2007-04-15'),
(null,2,74.98,'2007-04-19'),
(null,3,24.99,'2007-05-01');

insert into books values
('0-672-31697-8','Michael Morgan','Java 2 for Professional Developers',34.99),
('0-672-31745-1','Thomas Down','Installing Debian GNU/LINUX',24.99),
('0-672-31509-2','Pruitt et al','Teach Yourself GIMP In 24 Hours',24.99),
('0-672-31769-9','Thomas Schenk','Caldera Openlinux System Administration Unleashed',49.99);

insert into order_items values
(1,'0-672-31697-8',2),
(2,'0-672-31769-9',1),
(3,'0-672-31769-9',1),
(3,'0-672-31509-2',1),
(4,'0-672-31745-1',3);

insert into book_reviews values
('0-672-31697-8','The Morgan book is clearly weitten and goes well beyond most of the basic Java books out there.');