Mysql之旅 一(续)

 

1，计数行
  COUNT(*)函数计算行数，所以计算动物数目的查询应为：
  mysql> select count(*) from pet;
  +----------+
  | count(*) |
  +----------+
  |        8 |
  +----------+
  在前面，你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你可以使用COUNT( )函数：
  mysql> select owner ,count(*) from pet group  by owner;
  +--------+----------+
  | owner  | count(*) |
  +--------+----------+
  | Benny  |        2 |
  | Diane  |        1 |
  | Gwen   |        3 |
  | Harold |        2 |
  +--------+----------+
  注意：对于select column_name,count(*)...格式的检索必须在后面加上group by 子句。
  下列例子显示出进行动物普查操作的不同方式。
  每种动物的数量：
  mysql> select species ,count(*) from pet group by species;
  每种性别的动物数量：
  mysql> select sex,count(*) from pet group by sex;
  +------+----------+
  | sex  | count(*) |
  +------+----------+
  | NULL |        1 |
  | f    |        3 |
  | m    |        4 |
  +------+----------+
  按种类和性别组合的动物数量：
  mysql> select species,sex,count(*) from pet group by species,sex;
  若使用COUNT( )，你不必检索整个表。例如, 前面的查询，当只对狗和猫进行时，应为：
  mysql> select species,sex ,count(*)
    -> from pet where species='cat' or
    -> species='dog'
    -> group by species,sex;
  +---------+------+----------+
  | species | sex  | count(*) |
  +---------+------+----------+
  | cat     | f    |        1 |
  | cat     | m    |        1 |
  | dog     | f    |        1 |
  | dog     | m    |        2 |
  +---------+------+----------+
  或，如果你仅需要知道已知性别的按性别的动物数目：
  mysql> select species,sex ,count(*)
    -> from pet where sex is not null
    -> group by species,sex;

2，使用一个以上的表
  pet表追踪你有哪个宠物。如果你想要记录其它相关信息，例如在他们一生中看兽医或何时后代出生，你需要另外的表。这张表应该像什么呢？需要：
·         它需要包含宠物名字以便你知道每个事件属于哪个动物。   ·         需要一个日期以便你知道事件是什么时候发生的。      ·         需要一个描述事件的字段。
·         如果你想要对事件进行分类，则需要一个事件类型字段。

  mysql> create table event (name varchar(20),date date,
    -> type varchar(15),remark varchar(25)); 
  装载数据：
----------------------------------------------------------
  event.txt
  Fluffy    1995-05-15    litter    4 kittens, 3 female,   1 male
  Buffy    1993-06-23    litter    5 puppies, 2 female, 3 male
  Buffy    1994-06-19    litter    3 puppies, 3 female
  Chirpy    1999-03-21    vet    needed beak straightened
  Slim    1997-08-03    vet    broken rib
  Bowser    1991-10-12    kennel
  Fang    1991-10-12    kennel
  Fang    1998-08-28    birthday    Gave him a new chew toy
  Claws    1998-03-17    birthday    Gave him a new flea collar
  Whistler    1998-12-09    birthday    First birthday
----------------------------------------------------------
  mysql> load data local infile
    -> 'c:/event.txt' into table event
    -> lines terminated by '\r\n';
  现在你需要查询母亲生产时的年龄以及当时的remark:
  mysql> select pet.name,year(date)-year(birth)-(
    -> right(date,5)    -> remark
    -> from pet,event
    -> where event.name=pet.name and event.type='litter';
  +--------+------+---------------------------+
  | name   | age  | remark                    |
  +--------+------+---------------------------+
  | Fluffy |    2 | 4 kittens, 3 female, 1 ma |
  | Buffy  |    4 | 5 puppies, 2 female, 3 ma |
  | Buffy  |    5 | 3 puppies, 3 female       |
  +--------+------+---------------------------+

  关于该查询要注意的几件事情：

• FROM子句列出两个表，因为查询需要从两个表提取信息。
• 当从多个表组合(联结)信息时，你需要指定一个表中的记录怎样能匹配其它表的记录。这很简单，因为它们都有一个name列。查询使用WHERE子句基于name值来匹配2个表中的记录。
• 因为name列出现在两个表中，当引用列时，你一定要指定哪个表。把表名附在列名前即可以实现。

  你不必有2个不同的表来进行联结。如果你想要将一个表的记录与同一个表的其它记录进行比较，可以将一个表联结到自身。例如，为了在你的宠物之中繁殖配偶，你可以用pet联结自身来进行相似种类的雄雌配对：
  mysql> select p1.name,p1.sex,p2.name,p2.sex,p1.species
    -> from pet as p1,pet as p2
    -> where p1.species=p2.species and p1.sex='f' and p2.sex='m';
  +--------+------+--------+------+---------+
  | name   | sex  | name   | sex  | species |
  +--------+------+--------+------+---------+
  | Fluffy | f    | Claws  | m    | cat     |
  | Buffy  | f    | Fang   | m    | dog     |
  | Buffy  | f    | Bowser | m    | dog     |
  +--------+------+--------+------+---------+

3，获取数据库和表的信息
  为了找出当前选择了哪个数据库，使用DATABASE( )函数：
  mysql> select database();
  为了找出当前的数据库包含什么表(例如，当你不能确定一个表的名字)，使用这个命令：
  mysql> show tables;
  如果你想要知道一个表的结构，可以使用DESCRIBE命令；它显示表中每个列的信息：
  mysql> describe pet;
  Key显示列是否被索引，如果表有索引，SHOW INDEX FROM tbl_name生成有关索引的信息。

4，在批处理模式下使用Mysql
  你也可以以批模式运行mysql。为了做到这些，把你想要运行的命令放在一个文件中，然后告诉mysql从文件读取它的输入：
  linux:
  shell> mysql   如果在Windows下运行mysql，并且文件中有一些可以造成问题的特殊字符，可以这样操作：
  mysql> mysql -e "source batch_file"
 
  如果你想在语句出现错误的时候仍想继续执行脚本，则应使用--force命令行选项。

为什么要使用一个脚本？有很多原因：

• 如果你需要重复运行查询(比如说，每天或每周)，可以把它编成一个脚本，则每次执行时不必重新键入。
• 可以通过拷贝并编辑脚本文件从类似的现有的查询生成一个新查询。
• 当你正在开发查询时，批模式也是很有用的，特别对多行命令或多语句命令序列。如果你犯了一个错误，你不必重新输入所有内容，只需要编辑脚本来改正错误，然后告诉mysql再次执行脚本。
• 如果你有一个产生多个输出的查询，你可以通过一个分页器而不是盯着它翻屏到屏幕的顶端来运行输出：

·                shell> mysql < batch-file | more

• 你可以捕捉文件中的输出以便进行进一步的处理：

·                shell> mysql < batch-file > mysql.out

• 你可以将脚本分发给另外的人，以便他们也能运行命令。
• 某些情况不允许交互地使用，例如, 当你从一个cron任中运行查询时。在这种情况下，你必须使用批模式。

  cd c:\
  mysql>source test.txt
  mysql> source c:/test.txt;

5,常用查询的例子
 

#创建shop.txt
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);
#执行以下命令
mysql> source c:/mysql/shop.txt;

 
  i，列的最大值
  “最大的物品号是什么？”
  mysql> select max(article) as article from shop;
  +---------+
  | article |
  +---------+
  |       4 |
  +---------+
  ii，拥有某个列的最大值的行
  任务：找出最贵物品的编号、销售商和价格。
  mysql> select article,dealer,price
    -> from shop
    -> where price=(select max(price) from shop);
  +---------+--------+-------+
  | article | dealer | price |
  +---------+--------+-------+
  |    0004 | D      | 19.95 |
  +---------+--------+-------+
  另一个解决方案是按价格降序排序所有行并用MySQL特定LIMIT子句只得到第一行，但这个方法有缺陷：
  mysql> select article,dealer,price from shop
    -> order by price
    -> limit 1;
  +---------+--------+-------+
  | article | dealer | price |
  +---------+--------+-------+
  |    0003 | D      |  1.25 |
  +---------+--------+-------+
  iii，列的最大值，按组
  任务：每项物品的的最高价格是多少？
  mysql> 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 |
  +---------+-------+
  iv，任务：找出每项物品，找出最贵价格的物品的经销商。
   mysql> select article ,dealer,price
    -> from shop s1
    -> where price=(select max(price) from shop s2
    -> where s1.article=s2.article);
  +---------+--------+-------+
  | article | dealer | price |
  +---------+--------+-------+
  |    0001 | B      |  3.99 |
  |    0002 | A      | 10.99 |
  |    0003 | C      |  1.69 |
  |    0004 | D      | 19.95 |
  +---------+--------+-------+ 
  v，使用用户变量
  例如，要找出价格最高或最低的物品的，其方法是：
  mysql> select
@min_price:=min(price),@max_price:=max(price) from
 shop;
  mysql> select * from shop where price=@min_price or price=@max_price;
  +---------+--------+-------+
  | article | dealer | price |
  +---------+--------+-------+
  |    0003 | D      |  1.25 |
  |    0004 | D      | 19.95 |
  +---------+--------+-------+
  vi，根据两个键进行搜索union
  可以充分利用使用单关键字的OR子句，如同AND的处理。
  select field_index1,field_index3 from table_name
where field_index1='1' or field_index2='2';
  还可以使用UNION将两个单独的SELECT语句的输出合成到一起来更有效地解决该问题。
  select field_index1,field_index2 from table_name
where field_index1='1'
  union
  select field_index1,field_index2 from table_name
where field_index2='2';
  vii,使用auto_increment
  可以通过AUTO_INCREMENT属性为新的行产生唯一的标识：
  mysql> create table animals(
    -> id mediumint not null auto_increment,
    -> name char(30) not null,
    -> primary key(id)
    -> );
  注意以下两种insert方式：
  mysql> insert into animals values (1,'dog'),(2,'cat'),(3,'penguin');
  mysql> insert into animals (name) values
    -> ('cat'),('dog'),('penguin');
  当表中有主键时，可以利用系统自动生成，而无须插入之。但必须指定，如（name）.
  你可以使用LAST_INSERT_ID()SQL函数或mysql_insert_id() C API函数来查询最新的AUTO_INCREMENT值。当前animals表的auto_increment值为4：
  mysql> select last_insert_id();
  +------------------+
  | last_insert_id() |
  +------------------+
  |                4 |
  +------------------+
  要想以AUTO_INCREMENT值开始而不是1，你可以通过CREATE TABLE或ALTER TABLE来设置该值，如下所示:

  mysql> ALTER TABLE tbl AUTO_INCREMENT = 100；

  mysql命令的常用选项
  mysql常用的选项是-u，-p，同时还可以指定使用某个数据库。
  偶尔会使用到-e或--execute选项，可用来将SQL语句传递给服务器。当使用该选项时，语句被执行，然后mysql立即退出命令外壳。
  C:\mysql>mysql -u root -p -e "select User ,Host from User" mysql
  mysql是系统预设的一个数据库。
  Enter password: ******
  +------+-----------+
  | User | Host      |
  +------+-----------+
  | root | localhost |
  +------+-----------+

  C:\mysql>
  也可以使用以下语句：
  C:\mysql>mysql -u root -p -e "select User ,Host from mysql.User"
  这是容易理解的，比如(此时并没有选中database)：
  mysql> select * from demo.pet;
 
  可以按这种方式传递多个SQL语句，用分号隔开
  mysql -u root -p --execute="SELECT Name FROM Country WHERE Name LIKE 'AU%';SELECT COUNT(*) FROM City" world；