Mysql学习笔记（七）查(补充)

//查找名字以b开头的宠物...
select * from pet where name like 'b%';
//查找名字以fy结尾的宠物..
select * from pet where name like '%fy';

查找名字里包含w的宠物...
select * from pet where name like '%w%';

//查找所有以b开头的宠物名字...不区分大小写

select * from pet where name regexp '^b';

//区分大小写...

select * from pet where name regexp BINARY '^b';

//查找以fy结尾的宠物名字...

select * from pet where name regexp 'fy$';

select count(*) from pet;//显示数据库数据的行数..

select species,sex count(*) from pet where species="dog" or species="cat"group by species,sex;

mysql>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)
->);

mysql>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);

1.查询列的最大值...
select max(article) as article from shop;

2.查询某个列的最大值的一行数据...两种方法..
select article,dealer,price from shop where price=(select max(article) from shop);
select article,dealer,price from shop order by price desc limit;

3.列的最大值，与第一个不一样的地方在于，第一个是显示所有物品里的最大值，这种属于显示每一种物品的一个最大值..
select article max(price) as price from shop order by article;

4.拥有某个字段的组间最大值的行..
select article,dealer,price from shop s1 where price=(select max(s2.price) from shop s2 where s1.article=s2.article);

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;

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;

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

CREATE TABLE IF NOT EXISTS contact(  
contact_id int(11) NOT NULL AUTO_INCREMENT,  
user_name varchar(255),  
nom varchar(255),  
prenom varchar(255),  
mail varchar(64),  
passcode char(64),  
PRIMARY KEY(contact_id)  
);  
CREATE TABLE IF NOT EXISTS droit(  
droit_id int( 11 ) NOT NULL AUTO_INCREMENT ,  
droit varchar(255),  
PRIMARY KEY(droit_id)  
);  
CREATE TABLE IF NOT EXISTS contactdroit(  
contactdroit_id int(11) NOT NULL AUTO_INCREMENT,  
contact_id int( 11 ),  
droit_id int( 11 ),  
PRIMARY KEY( contactdroit_id )  
);  
Insert into contact(contact_id, user_name) values(1,'user1');  
Insert into contact(contact_id, user_name) values(2,'user2');  
Insert into contact(contact_id, user_name) values(3,'user3');  
Insert into droit(droit_id, droit) values(1,'admin');  
Insert into droit(droit_id, droit) values(2,'superuser');  
Insert into contactdroit(contact_id, droit_id) values(1, 1);  
Insert into contactdroit(contact_id, droit_id) values(2, 1);  
Insert into contactdroit(contact_id, droit_id) values(3, 2);  
 
SELECT c.contact_id, d.droit_id, d.droit FROM contact c, contactdroit cd, droit d   
where c.contact_id = cd.contact_id  
and cd.droit_id = d.droit_id;