MySqlベース(3)基本クエリー

6942 ワード

MYSQLのSQL文は大文字と小文字を区別しないで、普通はキーワードを大文字にすることに慣れて、データ列と表名は小文字を使って、1つの良い習慣を身につけて、書いたSQL文に対してもっと読みやすくてメンテナンスします.

IN照会

//     18  30   
mysql> SELECT id,name,age,salary,daptid
    -> FROM user
    -> WHERE age in(18,30);
+----+--------+------+--------+--------+
| id | name   | age  | salary | daptid |
+----+--------+------+--------+--------+
| 13 |      |   18 |   3000 |      2 |
+----+--------+------+--------+--------+
1 row in set (0.00 sec)

 //      18 30   
mysql> SELECT id,name,age,salary,daptid
    -> FROM user
    -> WHERE age not  in(18,30);
+----+-----------------+------+---------+--------+
| id | name            | age  | salary  | daptid |
+----+-----------------+------+---------+--------+
|  7 |               |   21 | 7000.14 |      2 |
|  8 |               |  180 |    8000 |      4 |
|  9 |               |  200 |    7000 |      6 |
| 10 |              |   20 |    1000 |      1 |
| 11 |            |  170 |    7000 |      1 |
| 12 |              |  500 |    5000 |      2 |
| 15 |               |    1 |       1 |      2 |
| 16 |               |    1 |       1 |      2 |
| 17 |               |   12 |    1000 |      1 |
| 18 |               |   12 |    1000 |      1 |
| 19 |               |   12 |    1000 |      1 |
| 20 |               |   12 |    1000 |      1 |
| 21 |               |   12 |    1000 |      1 |
| 24 | 1               |    1 |       1 |      1 |
| 25 | xiaoming        |   22 |   15555 |      5 |
| 26 | 1               |    1 |       1 |      1 |
+----+-----------------+------+---------+--------+
16 rows in set (0.00 sec)

範囲照会BETWEEN AND

//       18~30   
mysql> SELECT id,name,age,salary,daptid
    -> FROM user
    -> WHERE age  BETWEEN 18 AND 30;
+----+-----------+------+---------+--------+
| id | name      | age  | salary  | daptid |
+----+-----------+------+---------+--------+
|  7 |         |   21 | 7000.14 |      2 |
| 10 |        |   20 |    1000 |      1 |
| 13 |         |   18 |    3000 |      2 |
| 25 | xiaoming  |   22 |   15555 |      5 |
+----+-----------+------+---------+--------+
4 rows in set (0.00 sec)

//        18~30   
mysql> SELECT id,name,age,salary,daptid
    -> FROM user
    -> WHERE age NOT  BETWEEN 18 AND 30;
+----+-----------------+------+--------+--------+
| id | name            | age  | salary | daptid |
+----+-----------------+------+--------+--------+
|  8 |               |  180 |   8000 |      4 |
|  9 |               |  200 |   7000 |      6 |
| 11 |            |  170 |   7000 |      1 |
| 12 |              |  500 |   5000 |      2 |
| 15 |               |    1 |      1 |      2 |
| 16 |               |    1 |      1 |      2 |
| 17 |               |   12 |   1000 |      1 |
| 18 |               |   12 |   1000 |      1 |
| 19 |               |   12 |   1000 |      1 |
| 20 |               |   12 |   1000 |      1 |
| 21 |               |   12 |   1000 |      1 |
| 24 | 1               |    1 |      1 |      1 |
| 26 | 1               |    1 |      1 |      1 |
+----+-----------------+------+--------+--------+
13 rows in set (0.00 sec)

LIKE照会

1パーセント(%)ワイルドカードは任意の長さの文字に一致し、ゼロ文字を含む

//       “ ”     ,
mysql> SELECT id,name,age,salary,daptid
    -> FROM user
    -> WHERE name like " %";
+----+--------+------+--------+--------+
| id | name   | age  | salary | daptid |
+----+--------+------+--------+--------+
| 13 |      |   18 |   3000 |      2 |
| 17 |      |   12 |   1000 |      1 |
| 18 |      |   12 |   1000 |      1 |
| 19 |      |   12 |   1000 |      1 |
| 20 |      |   12 |   1000 |      1 |
| 21 |      |   12 |   1000 |      1 |
+----+--------+------+--------+--------+
6 rows in set (0.00 sec)

//             
mysql> SELECT id,name,age,salary,daptid
    -> FROM user
    -> WHERE name like "% %";
+----+--------+------+--------+--------+
| id | name   | age  | salary | daptid |
+----+--------+------+--------+--------+
| 15 |      |    1 |      1 |      2 |
| 16 |      |    1 |      1 |      2 |
+----+--------+------+--------+--------+
2 rows in set (0.00 sec)

下線ワイルドカード(),1文字一致

//         ，          
mysql> SELECT id,name,age,salary,daptid
    -> FROM user
    -> WHERE name like "__ ";
+----+-----------+------+--------+--------+
| id | name      | age  | salary | daptid |
+----+-----------+------+--------+--------+
| 12 |        |  500 |   5000 |      2 |
+----+-----------+------+--------+--------+
1 row in set (0.00 sec)

ANDマルチ条件クエリSELECTクエリを使用する場合、クエリの制限条件を増やすことで、クエリの結果をより正確にすることができます.MYSQLは、WHERE句でANDオペレータを使用して、すべてのクエリ条件を満たすレコードのみが返されるように定義します.ANDを使用して、2つ以上のクエリー条件を接続できます.複数の条件式の間はANDで区切られます.

//      16 25             
mysql> SELECT id,name,age,salary,daptid
    -> FROM user
    -> WHERE  age>16 AND age <25 and  name LIKE " %";
+----+---------+------+--------+--------+
| id | name    | age  | salary | daptid |
+----+---------+------+--------+--------+
| 13 |       |   18 |   3000 |      2 |
| 19 |   2   |   22 |   1000 |      1 |
| 20 |   3   |   17 |   1000 |      1 |
+----+---------+------+--------+--------+
3 rows in set (0.00 sec)

DISTINCT重複除外mysql> SELECT DISTINCT age FROM user;

select `user`.ID,`user`.`Name`,`user`.Age ,department.DapName from `user` INNER 
JOIN department ON department.DaptID=`user`.DaptID

     
select a.ID,a.Name,a.Age ,department.DapName from `user` a INNER JOIN department ON department.DaptID=a.DaptID

+----+-----------------+------+-----------+
| ID | Name            | Age  | DapName   |
+----+-----------------+------+-----------+
|  4 |              |  112 |        |
|  5 |              |   20 |        |
|  6 |              |   27 |        |
|  7 |               |   21 |        |
|  8 |               |  180 |        |
|  9 |               |  200 |        |
| 10 |              |   20 |        |
| 11 |            |  170 |        |
| 12 |              |  500 |        |
| 13 |               |   18 |        |
| 15 |               |    1 |        |
| 16 |               |    1 |        |
| 17 |               |   12 |        |
| 18 |               |   12 |        |
+----+-----------------+------+-----------+
14 rows in set (0.00 sec)

ORDER BYをソートしてSELECTでデータを照会する場合、表示される順序はデフォルトでデータ挿入順となります.SELECT name,age,salary,createtime FROM user ORDER BY name; // 複数列ソート:SELECT name,age,salary,createtime FROM user ORDER BY name,createtime;// ， ORDER BYデフォルトでは昇順方式が採用されており、キーワードDESCで降順に変更可能:mysql> SELECT name,age,salary,createtime FROM user ORDER BY name DESC;// SELECT name,age,salary,createtime FROM user ORDER BY name DESC,createtime;// ，

Vueルータから始める

グローバル例外の処理