mysql命令 排序_mysql命令

语法

SELECT 函數名;

例：

mysql>SELECT DATABASE();

+------------+

| DATABASE() |

+------------+

| jiaowu |

+------------+

1 row in set (0.00 sec)

#查看默認數據庫是誰

mysql>SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

| 4 |

+------------------+

1 row in set (0.00 sec)

#查詢上一次生成的ID到幾了

mysql>SELECT @@global.sql_mode;

+--------------------------------------------+

| @@global.sql_mode |

+--------------------------------------------+

| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

+--------------------------------------------+

1 row in set (0.00 sec)

#@@表示調用服務器變量或內置變量

语法

SELECT select-list FROM tb WHERE qualification

SELECT * FROM tb_name;

#顯示表中所有字段

SELECT field1,field2 FROM tb_name;

#基於投影，只顯示哪些字段

例：mysql> SELECT Name,Age FROM students;

+------+------+

| Name | Age |

+------+------+

| Tom | 30 |

+------+------+

1 row in set (0.00 sec)

SELECT * FROM tb_name WHERE qualification

#選擇，顯示符合條件的行, qualification表示搜索碼，搜索碼是表中字段即可

例：mysql> SELECT * FROM students WHERE Age>=20;

+-----+------+------+--------+------+------+------+---------------------+

| SID | Name | Age | Gender | CID1 | CID2 | TID | CreateTime |

+-----+------+------+--------+------+------+------+---------------------+

| 1 | Tom | 30 | F | NULL | NULL | NULL | 2012-04-06 10:00:00 |

+-----+------+------+--------+------+------+------+---------------------+

1 row in set (0.00 sec)

mysql>SELECT Name,Age FROM students WHERE Age>=20;

+------+------+

| Name | Age |

+------+------+

| Tom | 30 |

+------+------+

1 row in set (0.00 sec)

SELECT [DISTINCT] * FROM tb_name WHERE qualification

#[DISTINCT]表示其後的相同的值只顯示一次，如下面語句中指Gender相同的只顯示一次

例：mysql> SELECT DISTINCT Gender FROM students;

+--------+

| Gender |

+--------+

| F |

+--------+

1 row in set (0.00 sec)

========================================================

*** 规范

1. FROM子句後可跟表、多個表、其它SELECT語句，表示要查詢的關系

2. WHERE子句指定一個布爾關系表達式，用到的符號有=、>、=、<=、!=、<=>，在做數值比較時，數值不能加引號，條件是字符時必須用’’引號引起，<=>表示就算表中有空值也可以正確比較

3. 邏輯表達式

AND、OR、NOT

BETWEEN…AND…# 在…之間

LIKE ''# 比較操作，這時要用通配符%和_

%表示任意長度任意字符

_表示一個長度任意字符

REGEXP或RLIKE# 支持正則表達式

IN ()# 做離散取值時使用，括號中是符合的條件

IS NULL# 判斷是否為空

IS NOT NULL # 判斷是否不空

ORDER BY field_name (ASC|DESC)

# 將查詢後的結果排序，默認是昇序，ASC是昇序，DESC是降序

AS# 字段別名

4. LIMIT子句：只显示几行结果或偏移几行显示几行

LIMIT [offset,]Count

5. 聚合計算：SUM(), MIN(), MAX(), AVG(),COUNT()

6. GROUP BY：分組，目的是將整張表的內容跟據某個標准碼分完組後求聚合函數

HAVING# 條件：分組時用的过滤。与WHERE使用方法一样

========================================================

例：

mysql>source /root/jiaowu.sql

mysql>SELECT Name,Age FROM students WHERE Age>=20;

+-------------+------+

| Name | Age |

+-------------+------+

| DingDian | 25 |

| HuFei | 31 |

mysql>SELECT Name,Age FROM students WHERE Gender='M';

+-------------+------+

| Name | Age |

+-------------+------+

| GuoJing | 19 |

| YangGuo | 17 |

mysql>SELECT Name,Age FROM students WHERE Age+1>=20;

+-------------+------+

| Name | Age |

+-------------+------+

| GuoJing | 19 |

| DingDian | 25 |

#用表達式做條件，但在字段中這樣用會不能索引

mysql>SELECT Name FROM students WHERE Age>20 AND Gender='M';

+-------------+

| Name |

+-------------+

| DingDian |

| HuFei |

#與

mysql>SELECT Name FROM students WHERE Age>20 OR Gender='M';

+-------------+

| Name |

+-------------+

| GuoJing |

| YangGuo |

#或

mysql>SELECT Name FROM students WHERE NOT Age>20;

+--------------+

| Name |

+--------------+

| GuoJing |

| YangGuo |

#非

mysql>SELECT Name,Age,Gender FROM students WHERE NOT Age>20 AND NOT Gender='M';

+--------------+------+--------+

| Name | Age | Gender |

+--------------+------+--------+

| HuangRong | 16 | F |

| YueLingshang | 18 | F |

mysql>SELECT Name,Age,Gender FROM students WHERE NOT (Age>20 OR Gender='M');

+--------------+------+--------+

| Name | Age | Gender |

+--------------+------+--------+

| HuangRong | 16 | F |

| YueLingshang | 18 | F |

mysql>SELECT Name,Age FROM students WHERE Age>=20 AND Age<=25;

+-------------+------+

| Name | Age |

+-------------+------+

| DingDian | 25 |

| ZhangWuji | 20 |

#年齡在20-25之間的同學

mysql>SELECT Name,Age FROM students WHERE Age BETWEEN 20 AND 25;

+-------------+------+

| Name | Age |

+-------------+------+

| DingDian | 25 |

| ZhangWuji | 20 |

#用BETWEEN…AND，与上面效果一样

mysql>SELECT Name FROM students WHERE Name LIKE 'y%';

+--------------+

| Name |

+--------------+

| YangGuo |

| YueLingshang |

#找姓名中以y開頭的，用LIKE，表示模糊查找，要用通配符

mysql>SELECT Name FROM students WHERE Name LIKE 'y____';

+-------+

| Name |

+-------+

| YiLin |

+-------+

#找y後跟4個字符的

mysql>SELECT Name FROM students WHERE Name LIKE '%ing%';

+--------------+

| Name |

+--------------+

| GuoJing |

| DingDian |

#找包含ing的

mysql>SELECT Name FROM students WHERE Name RLIKE '^[DHY].*$';

+--------------+

| Name |

+--------------+

| YangGuo |

| DingDian |

#RLIKE或REGEXP表示用正則表達式，找D、H、Y開頭的後面是任意字符，用正則時索引可能不太有用

mysql>SELECT Name FROM students WHERE Age IN (18,20,25);

+--------------+

| Name |

+--------------+

| DingDian |

| YueLingshang |

#找年齡爲18、20、25的，用IN來指定一個列表

mysql>SELECT Name FROM students WHERE CID2 IS NULL;

+-------------+

| Name |

+-------------+

| LingHuchong |

| YiLin |

+-------------+

#找課程2爲空的，用NULL時要用IS，不空用IS NOT NULL