Mysql学习笔记二

接着笔记一来讲：

--------------------

使用数据库：

mysql> show databases;--显示服务器端所有数据库(在当前用户权限之内)
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ibatis             |
| mysql              |
| supercrm           |
| test               |
+--------------------+
5 rows in set (0.09 sec)

mysql> use test;--定位到自己要使用的数据库
Database changed

--------------------

数据表操作：

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));--创建表
Query OK, 0 rows affected (0.11 sec)


mysql> show tables;--显示当前数据库中的表
+----------------+
| Tables_in_test |
+----------------+
| shop           |
+----------------+
1 row in set (0.00 sec)

mysql> insert into shop values(1,'A',3.45),(2,'B',3.33);--插入数据
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from shop;--查询表中所有数据
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0002 | B      |  3.33 |
+---------+--------+-------+
2 rows in set (0.06 sec)

mysql> select max(article) as article from shop;--查询编号最大的记录
+---------+
| article |
+---------+
|       2 |
+---------+
1 row in set (0.36 sec)

“找出最贵的文章的编号、商人和价格”

mysql> select article,dealer,price from shop
    -> where price=(select max(price) from shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
+---------+--------+-------+
1 row in set (0.11 sec)
mysql> select article,dealer,price from shop
    -> order by price desc
    -> limit 1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
+---------+--------+-------+
1 row in set (0.05 sec)

mysql> select article,dealer,price from shop
    -> order by price desc
    -> limit 0,1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
+---------+--------+-------+
1 row in set (0.00 sec)

如果有多个最贵的文章，LIMIT解决方案仅仅显示他们之一！

从上面可以看出来，效率最高的是最后一种

“每篇文章的最高的价格是什么？”

mysql> select article,max(price) as price from shop
    -> group by article;
+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.45 |
|    0002 |  6.00 |
+---------+-------+
2 rows in set (0.08 sec)

“对每篇文章，找出有最贵的价格的交易者。” 

如果使用ANSI SQL：

mysql> select article,dealer,price from shop s1
    -> where price=(select max(s2.price) from shop s2 where s2.article=s1.articl
e);
+---------+--------+-------
| article | dealer | price
+---------+--------+-------
|    0001 | A      |  3.45
|    0002 | C      |  6.00
+---------+--------+-------
2 rows in set (0.05 sec)

我感觉price后面的结果也不是一个，但是这样写就是对的，如果换种别的写法如：

mysql> select article,dealer,price from shop s1
    -> where price=(select max(s2.price) from shop s2 group by article);

ERROR 1242 (21000): Subquery returns more than 1 row

有些东西只可意会不可言传，就行UML一样。

在MYsql中建议分步解决这种问题：

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;

8.3.5----Mysql学习笔记三会继续更新