mysql学習一のselect文

84464 ワード

mysql

mysql学習一のselect文

完全構文

Userテーブル

を作成する

単一テーブルクエリ

クエリNULL

AND多条件クエリ

OR多条件クエリ

DISTINCT

クエリー結果ソート

パケットクエリ

完全な構文
まず完全な文法をあげて、後で一つ一つ説明します.

基礎構文:selectフィールドリストfromデータソース;

完全構文:

select           [as     ] 
from     
[where  ] 
[group by   ] 
[having  ] 
[order by   ] 
[limit  ];

Userテーブルの作成

Userテーブル

を作成する

use test;
CREATE TABLE `test`.`user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `pwd` VARCHAR(32) NOT NULL,
  `age` INT NOT NULL,
  `sex` VARCHAR(8) NOT NULL,
  PRIMARY KEY (`id`));

挿入データ

use test;
insert into user(name,pwd,age,sex) 
values ('   ','xiaohuihui',6,' '),('   ','heitailang',20,' ');

クエリーデータ

./mysql -h 127.0.0.1 -u root -P 3306 -p 
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select id,name from user;
+----+-----------+
| id | name      |
+----+-----------+
|  1 |        |
|  2 |        |
|  3 |        |
|  4 |        |
|  7 |        |
|  8 |        |
|  9 |        |
| 10 |        |
| 11 |        |
| 12 |        |
| 13 |        |
| 14 |        |
+----+-----------+
12 rows in set (0.00 sec)

単一テーブルクエリ

SELECT文で「*」ワイルドカードを使用して、すべてのフィールド

を問合せます.

mysql> SELECT * FROM user;
+----+-----------+-------------+-----+-----+
| id | name      | pwd         | age | sex |
+----+-----------+-------------+-----+-----+
|  1 |        | xiyangyang  |  12 |    |
|  2 |        | lanyangyang |  10 |    |
|  3 |        | meiyangyang |  10 |    |
|  4 |        | manyangyang |  14 |    |
|  7 |        | feiyangyang |  16 |    |
|  8 |        | huayangyang |  18 |    |
|  9 |        | benyangyang |  20 |    |
| 10 |        | souyangyang |  24 |    |
| 11 |        | hongtailang |  20 |    |
| 12 |        | jiaotailang |  24 |    |
| 13 |        | xiaohuihui  |   6 |    |
| 14 |        | heitailang  |  20 |    |
+----+-----------+-------------+-----+-----+
12 rows in set (0.00 sec)

SELECT文ですべてのフィールド

を指定

mysql> SELECT id,name,pwd,age,sex FROM user;
+----+-----------+-------------+-----+-----+
| id | name      | pwd         | age | sex |
+----+-----------+-------------+-----+-----+
|  1 |        | xiyangyang  |  12 |    |
|  2 |        | lanyangyang |  10 |    |
|  3 |        | meiyangyang |  10 |    |
|  4 |        | manyangyang |  14 |    |
|  7 |        | feiyangyang |  16 |    |
|  8 |        | huayangyang |  18 |    |
|  9 |        | benyangyang |  20 |    |
| 10 |        | souyangyang |  24 |    |
| 11 |        | hongtailang |  20 |    |
| 12 |        | jiaotailang |  24 |    |
| 13 |        | xiaohuihui  |   6 |    |
| 14 |        | heitailang  |  20 |    |
+----+-----------+-------------+-----+-----+
12 rows in set (0.00 sec)

クエリー指定フィールド:SELECT列名FROM表名;

mysql> SELECT name from user;
+-----------+
| name      |
+-----------+
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
+-----------+
12 rows in set (0.00 sec)

クエリー複数フィールド:SELECTフィールド名1、フィールド名2、...フィールド名n FROMテーブル名;

mysql> SELECT name pwd,sex from user;
+-----------+-----+
| pwd       | sex |
+-----------+-----+
|        |    |
|        |    |
|        |    |
|        |    |
|        |    |
|        |    |
|        |    |
|        |    |
|        |    |
|        |    |
|        |    |
|        |    |
+-----------+-----+
12 rows in set (0.00 sec)

クエリ指定レコードSELECTフィールド名1,フィールド名2,…フィールド名n FROMテーブル名WHEREクエリ条件

mysql> SELECT name,pwd,sex from user where sex=' ';
+-----------+-------------+-----+
| name      | pwd         | sex |
+-----------+-------------+-----+
|        | meiyangyang |    |
|        | feiyangyang |    |
|        | huayangyang |    |
|        | souyangyang |    |
|        | hongtailang |    |
+-----------+-------------+-----+
5 rows in set (0.01 sec)

クエリー年齢が20未満のレコード

mysql> SELECT name,age FROM user where age <20;
+-----------+-----+
| name      | age |
+-----------+-----+
|        |  12 |
|        |  10 |
|        |  10 |
|        |  14 |
|        |  16 |
|        |  18 |
|        |   6 |
+-----------+-----+
7 rows in set (0.01 sec)

INキーワードクエリ

mysql> SELECT id,name,sex FROM user  where id in (1,2,3,4);
+----+-----------+-----+
| id | name      | sex |
+----+-----------+-----+
|  1 |        |    |
|  2 |        |    |
|  3 |        |    |
|  4 |        |    |
+----+-----------+-----+
4 rows in set (0.00 sec)

NOT INキーワードクエリ

mysql> SELECT id,name,sex FROM user  where id not in (1,2,3,4);
+----+-----------+-----+
| id | name      | sex |
+----+-----------+-----+
|  7 |        |    |
|  8 |        |    |
|  9 |        |    |
| 10 |        |    |
| 11 |        |    |
| 12 |        |    |
| 13 |        |    |
| 14 |        |    |
+----+-----------+-----+
8 rows in set (0.01 sec)

BETWEEN ANDキーワードクエリ

mysql> SELECT id,name,sex,age FROM user where age between 20 and 24;
+----+-----------+-----+-----+
| id | name      | sex | age |
+----+-----------+-----+-----+
|  9 |        |    |  20 |
| 10 |        |    |  24 |
| 11 |        |    |  20 |
| 12 |        |    |  24 |
| 14 |        |    |  20 |
+----+-----------+-----+-----+
5 rows in set (0.00 sec)

NOT BETWEEN ANDキーワードクエリ

mysql> SELECT id,name,sex,age FROM user where age not between 20 and 24;
+----+-----------+-----+-----+
| id | name      | sex | age |
+----+-----------+-----+-----+
|  1 |        |    |  12 |
|  2 |        |    |  10 |
|  3 |        |    |  10 |
|  4 |        |    |  14 |
|  7 |        |    |  16 |
|  8 |        |    |  18 |
| 13 |        |    |   6 |
+----+-----------+-----+-----+
7 rows in set (0.00 sec)

クエリー「小」で始まるデータ

mysql> SELECT id,name,age,sex FROM user where name like ' %';
+----+-----------+-----+-----+
| id | name      | age | sex |
+----+-----------+-----+-----+
| 13 |        |   6 |    |
+----+-----------+-----+-----+
1 row in set (0.00 sec)

クエリー「喜」の先頭と「羊」の末尾のデータ

mysql> SELECT id,name,age,sex FROM user where name like ' % ';
+----+-----------+-----+-----+
| id | name      | age | sex |
+----+-----------+-----+-----+
|  1 |        |  12 |    |
+----+-----------+-----+-----+
1 row in set (0.00 sec)

下線ワイルドカードクエリー

mysql> SELECT id,name,age,sex FROM user where name like '__ ';
+----+-----------+-----+-----+
| id | name      | age | sex |
+----+-----------+-----+-----+
| 13 |        |   6 |    |
+----+-----------+-----+-----+
1 row in set (0.00 sec)

空の値の問合せ

フィールドemail

を追加

ALTER TABLE `test`.`user` 
ADD COLUMN `email` VARCHAR(64) NULL AFTER `sex`;
UPDATE `test`.`user` SET `email` = '[email protected]' WHERE (`id` = '1');
UPDATE `test`.`user` SET `email` = '[email protected]' WHERE (`id` = '2');
UPDATE `test`.`user` SET `email` = '[email protected]' WHERE (`id` = '3');
UPDATE `test`.`user` SET `email` = '[email protected]' WHERE (`id` = '4');
UPDATE `test`.`user` SET `email` = '[email protected]' WHERE (`id` = '7');

IS NULLクエリ

mysql> SELECT id,name,age,sex,email FROM user WHERE email IS NULL;
+----+-----------+-----+-----+-------+
| id | name      | age | sex | email |
+----+-----------+-----+-----+-------+
|  8 |        |  18 |    | NULL  |
|  9 |        |  20 |    | NULL  |
| 10 |        |  24 |    | NULL  |
| 11 |        |  20 |    | NULL  |
| 12 |        |  24 |    | NULL  |
| 13 |        |   6 |    | NULL  |
| 14 |        |  20 |    | NULL  |
+----+-----------+-----+-----+-------+
7 rows in set (0.00 sec)

IS NOT NULLクエリ

mysql> SELECT id,name,age,sex,email FROM user WHERE email IS NOT NULL;
+----+-----------+-----+-----+---------------------+
| id | name      | age | sex | email               |
+----+-----------+-----+-----+---------------------+
|  1 |        |  12 |    | xiyangyang@163.com  |
|  2 |        |  10 |    | lanyangyang@163.com |
|  3 |        |  10 |    | meiyangyang@163.com |
|  4 |        |  14 |    | manyangyang@163.com |
|  7 |        |  16 |    | feiyangyang@163.com |
+----+-----------+-----+-----+---------------------+
5 rows in set (0.00 sec)

AND多条件照会

mysql> SELECT id,name,age,sex,email FROM user where id IN (4,7) AND age = 16 and name='    ';
+----+-----------+-----+-----+---------------------+
| id | name      | age | sex | email               |
+----+-----------+-----+-----+---------------------+
|  7 |        |  16 |    | feiyangyang@163.com |
+----+-----------+-----+-----+---------------------+
1 row in set (0.00 sec)

OR多条件照会

mysql> SELECT id,name,age,sex,email FROM user where id=4 OR id=7;
+----+-----------+-----+-----+---------------------+
| id | name      | age | sex | email               |
+----+-----------+-----+-----+---------------------+
|  4 |        |  14 |    | manyangyang@163.com |
|  7 |        |  16 |    | feiyangyang@163.com |
+----+-----------+-----+-----+---------------------+
2 rows in set (0.00 sec)

DISTINCT

データ

を挿入

INSERT INTO `test`.`user` (`name`, `pwd`, `age`, `sex`, `email`) 
VALUES ('   ', 'xiyangyang', '12', ' ', '[email protected]');

distinctキーワード

を使用しない

mysql> SELECT  name FROM user;
+-----------+
| name      |
+-----------+
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
+-----------+
13 rows in set (0.00 sec)

distinctキーワード

を使用

mysql> SELECT distinct name FROM user;
+-----------+
| name      |
+-----------+
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
+-----------+
11 rows in set (0.00 sec)

クエリー結果のソート

ORDER BYを使用しない:データがデータテーブルに挿入する順序に従って

を表示する.

mysql> SELECT name FROM user;
+-----------+
| name      |
+-----------+
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
+-----------+
13 rows in set (0.00 sec)

ORDER BY

を使用

mysql> SELECT name FROM user ORDER BY name;
+-----------+
| name      |
+-----------+
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
+-----------+
13 rows in set (0.00 sec)

DESCキーワード

を使用

mysql> SELECT name FROM user ORDER BY name DESC;
+-----------+
| name      |
+-----------+
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
|        |
+-----------+
13 rows in set (0.00 sec)

グループクエリー

GROUP BYキー

を使用

mysql> SELECT sex,COUNT(*) FROM user GROUP BY sex;
+-----+----------+
| sex | COUNT(*) |
+-----+----------+
|    |        8 |
|    |        5 |
+-----+----------+
2 rows in set (0.01 sec)

GROUP_を使用CONCAT関数

mysql> SELECT sex,GROUP_CONCAT(name)  FROM user GROUP BY sex;
+-----+---------------------------------------------------------------------------------+
| sex | GROUP_CONCAT(name)                                                              |
+-----+---------------------------------------------------------------------------------+
|    |    ,   ,   ,   ,                                                 |
|    |    ,   ,   ,   ,   ,   ,   ,                            |
+-----+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

HAVING

を使用

mysql> SELECT sex,GROUP_CONCAT(name) as names  FROM user GROUP BY sex HAVING COUNT(name) > 5;
+-----+---------------------------------------------------------------------------------+
| sex | names                                                                           |
+-----+---------------------------------------------------------------------------------+
|    |    ,   ,   ,   ,   ,   ,   ,                            |
+-----+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

WITH ROLLUP

を使用

mysql> SELECT sex,GROUP_CONCAT(name)  FROM user GROUP BY sex WITH ROLLUP;
+------+-----------------------------------------------------------------------------------------------------------------------------------+
| sex  | GROUP_CONCAT(name)                                                                                                                |
+------+-----------------------------------------------------------------------------------------------------------------------------------+
|     |    ,   ,   ,   ,                                                                                                   |
|     |    ,   ,   ,   ,   ,   ,   ,                                                                              |
| NULL |    ,   ,   ,   ,   ,   ,   ,   ,   ,   ,   ,   ,                                           |
+------+-----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> SELECT sex,COUNT(name)  FROM user GROUP BY sex WITH ROLLUP;
+------+-------------+
| sex  | COUNT(name) |
+------+-------------+
|     |           5 |
|     |           8 |
| NULL |          13 |
+------+-------------+
3 rows in set (0.00 sec)

APPのコールドスタート時の最初のホワイトスクリーンと処理方法について

SQLmapの紹介と使用(メモ)