mysql数据库(9):常用查询的例子

article, dealer, price FROM shop s1 WHERE price=( SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);

（7）使用用户变量

任务：要找出价格最高或最低的物品的

 

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

　　

（8）使用外键

 CREATE   TABLE   person (
    id SMALLINT UNSIGNED   NOT   NULL AUTO_INCREMENT,
    name   CHAR ( 60 )  NOT   NULL,
    PRIMARY KEY (id)
);

  CREATE   TABLE   shirt (
    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 person(id),
    PRIMARY KEY (id)
);

  INSERT   INTO  person  VALUES  (NULL,  ‘  Antonio Paz  ‘  );

  SELECT  @last :=  LAST_INSERT_ID();

  INSERT   INTO  shirt  VALUES  
(NULL,   ‘  polo  ‘ ,  ‘  blue  ‘  , @last),
(NULL,   ‘  dress  ‘ ,  ‘  white  ‘  , @last),
(NULL,   ‘  t-shirt  ‘ ,  ‘  blue  ‘  , @last);

  INSERT   INTO  person  VALUES  (NULL,  ‘  Lilliana Angelovska  ‘  );

  SELECT  @last :=  LAST_INSERT_ID();

  INSERT   INTO  shirt  VALUES  
(NULL,   ‘  dress  ‘ ,  ‘  orange  ‘  , @last),
(NULL,   ‘  polo  ‘ ,  ‘  red  ‘  , @last),
(NULL,   ‘  dress  ‘ ,  ‘  blue  ‘  , @last),
(NULL,   ‘  t-shirt  ‘ ,  ‘  white  ‘  , @last);

  SELECT  *  FROM   person;

  SELECT  *  FROM   shirt;

  SELECT  s.*  FROM   person p, shirt s
   WHERE  p.name  LIKE   ‘  Lilliana%  ‘ 
    AND  s.owner =  p.id
     AND  s.color <>  ‘  white  ‘ ;

 

 

 

 

 

 

 

 

 

 

 

 （8）根据天计算访问量

 

 CREATE   TABLE  t1 (year YEAR( 4 ), month INT( 2  ) UNSIGNED ZEROFILL,
             day INT(  2  ) UNSIGNED ZEROFILL);
  INSERT   INTO  t1  VALUES ( 2000 , 1 , 1 ),( 2000 , 1 , 20 ),( 2000 , 1 , 30 ),( 2000 , 2 , 2  ),
            (  2000 , 2 , 23 ),( 2000 , 2 , 23 );

 

 

 

 

 SELECT  year,month,BIT_COUNT(BIT_OR( 1 <<day)) AS days  FROM   t1
         GROUP   BY  year,month;

 

 

 （9）使用AUTO_INCREMENT

 CREATE   TABLE   animals (
     id MEDIUMINT   NOT   NULL AUTO_INCREMENT,
     name   CHAR ( 30 )  NOT   NULL,
     PRIMARY KEY (id)
 ); 

 

 

 INSERT   INTO  animals (name)  VALUES   
    (  ‘  dog  ‘ ),( ‘  cat  ‘ ),( ‘  penguin  ‘  ),
    (  ‘  lax  ‘ ),( ‘  whale  ‘ ),( ‘  ostrich  ‘ );

 

 

 

 

 SELECT  *  FROM  animals;

 

mysql数据库(9):常用查询的例子

标签：一个   from   enc   insert   外键   rem   medium   enum   value   

查看更多关于mysql数据库(9):常用查询的例子的详细内容...