MySQLクエリ文(1)
一:データベースの作成
二:SELECT検索と並べ替え
三:LIMIT制限
四:ORDER BYのソート
五:WHEREの選択
六:データフィルタAND
七:単純マッチングと正則.
1:CREATE DATABASE test; // test
2:SHOW DATABASES; // , 3:USE test; // test
4:SHOW TABLES; //
5:SHOW COLUMNS FROM test.table // test
二:SELECT検索と並べ替え
1:SELECT col FROM test.table; // test.table col
2:SELECT col,col1 FROM test.table; // test.table col,col1
3:SELECT * FROM test.table; // test.table
4:SELECT DISTINCT col FROM test.table; // test.table col , 。
SELECT DISTINCT col,col1 FROM test.table; // , col col1 , 。
三:LIMIT制限
5: SELECT col FROM test.table LIMIT 5; // test.table col , 5
SELECT col FROM test.table LIMIT 5,5; // 5 , 5 , ,MySQL 0
四:ORDER BYのソート
6: SELECT col FROM test.table ORDER BY col; // test.table col , col 。
SELECT col,col1 FROM test.table ORDER BY col,col1; // col col1
SELECT col FROM test.table ORDER BY col DESC; //DESC , , DESC
//ORDER BY LIMIT , LIMIT ORDER BY 。
五:WHEREの選択
7: SELECT col FROM test.table WHERE col=5; // test.table col 5 。
//WHRER ORDER BY , ORDER BY WHERE 。
//WHERE : = <> != < <= > >= BETWEEN A AND B A B
SELECT col FROM test.table WHERE col <> 5; //col 5 , !=
SELECT col FROM test.table WHERE col BETWEEN 5 AND 10; //col 5 10 。
SELECT col FROM test.table WHERE col1 IS NULL; // col1 , 。
六:データフィルタAND
8: SELECT col,col1 FROM test.table WHERE col=1 AND col1=2; // col=1 col1=2
SELECT col,col1 FROM test.table WHERE col=1 OR col1=2; // col=1 col1=2
SELECT col,col1 FROM test.table WHERE (col=1 OR col1=2) AND col3 = 5; // col=1 col1=2 col3=5
// : AND OR 。
SELECT col FROM test.table WHERE col IN(3,5,...); //IN OR , ()
SELECT col FROM test.table WHERE col NOT IN(3,5); // col=3,4,5, ,
七:単純マッチングと正則.
9: SELECT col FROM test.table WHERE col LIKE 'Jet%'; //% ,'Jet%' Jet 。
10: SELECT col FROM test.table WHERE col LIKE '_ ton'; //_ ,'_ ton' ton 。
// , , 。
// : REGEXP
// , , , ,
// 'hello' ,WHERE col LIKE 'hell' 'hello' , REGEXP
// , REGEXP 。
11: SELECT prod_name FROM test.table WHERE col REGEXP '.Jet'; // . ,
12:SELECT prod_name FROM test.table WHERE col REGEXP 1000|2000; // col 1000 2000, OR
13: SELECT prod_name FROM test.table WHERE col REGEXP '[123] Ton'; // 1 Ton|2 Ton|3 Ton;
SELECT prod_name FROM test.table WHERE col REGEXP '[1-3] Ton'; // , 。
SELECT prod_name FROM test.table WHERE col REGEXP '1|2|3 Ton'; // , 1 | 2 | 3 Ton;
SELECT prod_name FROM test.table WHERE col REGEXP '[^123] Ton'; // 123 .Ton, ^ [] 。
// MySQL \\X x \MySQL , \
SELECT prod_name FROM test.table WHERE col REGEXP '\\.'; // . 。
//
SELECT prod_name FROM test.table WHERE col REGEXP '\\([0-9] sticks?\\)'; // \\( [0-9] 0-9 ? ,\\) 。
//^
SELECT prod_name FROM test.table WHERE col REGEXP '^[0-9]\\.'; // . 。
//REGEXP LIKE
SELECT prod_name FROM test.table WHERE col REGEXP '1000' SELECT prod_name FROM test.table WHERE col REGEXP '^1000$' // , .