MySQL_常用查询的例子

底下是少许进修怎样用mysql处置少许罕见题目的例子。　　　　少许例子运用数据库表“shop”，包括某个贩子的每篇作品(货色号)的价钱。假设每个贩子的每篇作品有一个独立的固订价格，那么(货色，贩子)是记载的主键。 　　　　你能如许创造例子数据库表： 　　create table shop (　　article int(4) unsigned zerofill default '0000' not null,　　dealer　char(20)　　　　　　　　 default ''　　 not null,　　price　 double(16,2)　　　　　　 default '0.00' not null,　　primary key(article, dealer));　　　　insert into shop values　　(1,'a',3.45),(1,'b',3.99),(2,'a',10.99),(3,'b',1.45),(3,'c',1.69),　　(3,'d',1.25),(4,'d',19.95);　　　　好了，例子数据是如许的： 　　　　select * from shop　　　　+---------+--------+-------+　　| article | dealer | price |　　+---------+--------+-------+　　|　　0001 | a　　　|　3.45 |　　|　　0001 | b　　　|　3.99 |　　|　　0002 | a　　　| 10.99 |　　|　　0003 | b　　　|　1.45 |　　|　　0003 | c　　　|　1.69 |　　|　　0003 | d　　　|　1.25 |　　|　　0004 | d　　　| 19.95 |　　+---------+--------+-------+　　3.1 列的最大值　“最大的货色号是什么？” 　　　　select max(article) as article from shop　　　　+---------+　　| article |　　+---------+　　|　　　 4 |　　+---------+　　3.2 具有某个列的最大值的行　“找到最贵的作品的编号、贩子和价钱”　　　　在ansi-sql中这很简单用一个子查问做到： 　　　　select article, dealer, price　　from　 shop　　where　price=(select max(price) from shop)　　 在mysql中（还没有子查问)就用2步做到： 　　 用一个select语句从表中获得最大值。 　　运用该值编出本质的查问： 　　select article, dealer, price　　from　 shop　　where　price=19.95　　　另一个处置计划是按价钱降序排序一切行并用mysql一定limit子句只获得的第一条龙： 　　　　select article, dealer, price　　from　 shop　　order by price desc　　limit 1　　　　提防:即使有多个最贵的作品( 比方每个19.95)，limit处置计划只是表露她们之一！ 　　3.3 列的最大值：按组：惟有值　“每篇作品的最高的价钱是什么？” 　　　　select article, max(price) as price　　from　 shop　　group by article　　　　+---------+-------+　　| article | price |　　+---------+-------+　　|　　0001 |　3.99 |　　|　　0002 | 10.99 |　　|　　0003 |　1.69 |　　|　　0004 | 19.95 |　　+---------+-------+[page_break]3.4 具有某个字段的组间最大值的行　“对每篇作品，找到有最贵的价钱的买卖者。” 　　　　在ansi sql中，我不妨用如许一个子查问做到： 　　　　select article, dealer, price　　from　 shop s1　　where　price=(select max(s2.price)　　　　　　　　　from shop s2　　　　　　　　　where s1.article = s2.article)　　　在mysql中，最佳是分几步做到： 　　　获得一个表(作品，maxprice)。见3.4 具有某个域的组间最大值的行。 　对每篇作品，获得对应于保存最大价钱的行。 　这不妨很简单用一个偶尔表做到： 　　　　create temporary table tmp (　　　　　　article int(4) unsigned zerofill default '0000' not null,　　　　　　price　 double(16,2)　　　　　　 default '0.00' not null);　　　　lock tables article read;　　　　insert into tmp select article, max(price) from shop group by article;　　　　select article, dealer, price from shop, tmp　　where shop.article=tmp.articel and shop.price=tmp.price;　　　　unlock tables;　　　　drop table tmp;　　　即使你不运用一个temporary表，你也必需锁定“tmp”表。 　　“它能一个单个查问做到吗？” 　　　是的，然而惟有运用我称之为“max-concat阴谋”的一个十分低效的阴谋： 　　　　select article,　　　　　 substring( max( concat(lpad(price,6,'0'),dealer) ), 7) as dealer,　　　0.00+left(　　　max( concat(lpad(price,6,'0'),dealer) ), 6) as price　　from　 shop　　group by article;　　　　+---------+--------+-------+　　| article | dealer | price |　　+---------+--------+-------+　　|　　0001 | b　　　|　3.99 |　　|　　0002 | a　　　| 10.99 |　　|　　0003 | c　　　|　1.69 |　　|　　0004 | d　　　| 19.95 |　　+---------+--------+-------+　　结果例子固然能经过在存户步调中分隔连结的列使它更灵验一点。 　　3.5 运用外键　不须要外键结合2个表。 　　　　mysql独一不做的工作是check以保护你运用的键真实在你正在援用表中生存，而且它不机动从有一个外键设置的表中简略行。即使你象凡是那么运用你的键值，它将处事得很好！ 　　　　create table persons (　　　　id smallint unsigned not null auto_increment, 　　　　name char(60) not null,　　　　primary key (id)　　);　　　　create table shirts (　　　　id smallint unsigned not null auto_increment,　　　　style enum('t-shirt', 'polo', 'dress') not null,　　　　color enum('red', 'blue', 'orange', 'white', 'black') not null,　　　　owner smallint unsigned not null references persons,　　　　primary key (id)　　);　　　　insert into persons values (null, 'antonio paz');　　　　insert into shirts values　　(null, 'polo', 'blue', last_insert_id()),　　(null, 'dress', 'white', last_insert_id()),　　(null, 't-shirt', 'blue', last_insert_id());　　　　insert into persons values (null, 'lilliana angelovska');　　　　insert into shirts values　　(null, 'dress', 'orange', last_insert_id()),　　(null, 'polo', 'red', last_insert_id()),　　(null, 'dress', 'blue', last_insert_id()),　　(null, 't-shirt', 'white', last_insert_id());　　　　select * from persons;　　+----+---------------------+　　| id | name　　　　　　　　|　　+----+---------------------+　　|　1 | antonio paz　　　　 |　　|　2 | lilliana angelovska |　　+----+---------------------+　　　　select * from shirts;　　+----+---------+--------+-------+　　| id | style　 | color　| owner |　　+----+---------+--------+-------+　　|　1 | polo　　| blue　 |　　 1 |　　|　2 | dress　 | white　|　　 1 |　　|　3 | t-shirt | blue　 |　　 1 |　　|　4 | dress　 | orange |　　 2 |　　|　5 | polo　　| red　　|　　 2 |　　|　6 | dress　 | blue　 |　　 2 |　　|　7 | t-shirt | white　|　　 2 |　　+----+---------+--------+-------+　　　　select s.* from persons p, shirts s　　 where p.name like 'lilliana%'　　　 and s.owner = p.id　　　 and s.color <> 'white';　　　　+----+-------+--------+-------+　　| id | style | color　| owner |　　+----+-------+--------+-------+　　|　4 | dress | orange |　　 2 |　　|　5 | polo　| red　　|　　 2 |　　|　6 | dress | blue　 |　　 2 |　　+----+-------+--------+-------+