Mysqlのクエリー文の使用

3799 ワード

単純クエリー

       : SELECT * FROM   
       : SELECT       FROM

クエリー表示行番号

  -         (@rownum := @rownum + 1) AS rownum
  -  FROM        (select @rownum:=0)r
  -     :
 
     select (@rownum := @rownum + 1) AS rownum,book_name from   (select @rownum:=0)r, book;
         :
     +--------+--------------------+
     | rownum | book_name          |
     +--------+--------------------+
     |      1 |             |
     |      2 |           |
     +--------+--------------------+

クエリー文には算術式があり、通常は計算カラムと呼ばれる計算結果を表示する新しいカラムが形成されます.

   :select book_price/2 AS new_book_price,author_id from book;

SELECT句でASキーワードを使用してフィールドに別名を付ける

基本使用

        mysql> select book_price AS New_book_price,author_id as my_idea from book;
        +----------------+---------+
        | New_book_price | my_idea |
        +----------------+---------+
        |            214 |       1 |
        |           34.5 |       2 |
        +----------------+---------+
        2 rows in set (0.07 sec)

-            ()       ,            

  
        mysql> select book_name as "the new book name" from book;
        +--------------------+
        | the new book name  |
        +--------------------+
        |             |
        |           |
        +--------------------+
        2 rows in set (0.06 sec)
        
        mysql> select book_name as the new book name from book;
        1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'new book name from book' at line 1

DISTINCTを用いる重複行表示を消去

     mysql> select volume FROM book;
     +--------+
     | volume |
     +--------+
     | 45     |
     | 45     |
     +--------+
     2 rows in set (0.07 sec)
     
     mysql> select DISTINCT volume FROM book;
     +--------+
     | volume |
     +--------+
     | 45     |
     +--------+
     1 row in set (0.06 sec)

クエリー結果のソート、order byフィールド(asc,desc)、ascは昇順、descは降順、指定しない場合は昇順

  mysql> SELECT * FROM author ORDER BY id;
  +----+--------------+---------+
  | id | name         | address |
  +----+--------------+---------+
  |  1 | chenchen     |         |
  |  2 | chenqingyang |         |
  |  5 | 666          |      |
  | 14 | gafv         | gfgd    |
  +----+--------------+---------+
  4 rows in set (0.05 sec)

  mysql> select * from author order by id desc;
  +----+--------------+---------+
  | id | name         | address |
  +----+--------------+---------+
  | 14 | gafv         | gfgd    |
  |  5 | 666          |      |
  |  2 | chenqingyang |         |
  |  1 | chenchen     |         |
  +----+--------------+---------+
  4 rows in set (0.05 sec)

がデフォルトで使用されます.

注意すべきことは、order by句において、このフィールドが文字タイプである場合、

    mysql> select * from author order by name ;
     +----+--------------+---------+
     | id | name         | address |
     +----+--------------+---------+
     |  1 | chenchen     |         |
     |  2 | chenqingyang |         |
     | 14 | gafv         | gfgd    |
     +----+--------------+---------+
     3 rows in set (0.05 sec)

を文字テーブルの順序で比較することである.

インデックスの作成と削除

作成:CREATE INDEXインデックス名on table_name(フィールド名)

削除:DROP INDEX索引名on table_name

      :
  mysql> create index name_index on author(name);
  Query OK, 0 rows affected (0.05 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
      :
  mysql> DROP index name_index on author;
  Query OK, 0 rows affected (0.02 sec)
  Records: 0  Duplicates: 0  Warnings: 0

本文はブログの1文の多発プラットフォームOpenWriteから発表します!

[JS]Propertyフラグ

JavaScript関数オブジェクト、シンボル関数式