Mysql-建立資料庫和使用
- 顯示已建立的資料庫
- 切換使用的資料庫
- 進行相關授權
- 建立和選擇資料庫
-
- 建立資料庫
- 查詢正在使用的資料庫
- 建立資料表
-
- 查詢資料庫中的表
- 建立資料庫表
- 查詢資料表的詳情
- 加載資料和添加表資料
-
- 加載資料
- 檢索資料表中的資料
-
- 文法
- 查詢所有資料
- 删除資料庫中所有資料
- 修改記錄
- 查詢指定的行
- 查詢指定的列
- 對資料進行字段排序
- 資料表中時間計算
- NULL處理
- 模糊比對
- 計數操作
- 使用多個表
在前面，我們說到了連接配接mysql,連接配接上mysql進行相關語句就可以進行操作資料庫。
顯示已建立的資料庫
#查詢mysql已建立的資料庫
mysql> show databases;
結果：
上面是查詢的資料庫清單，沒有标記的mysql自帶的資料庫，我們來看看這幾個資料庫的作用：(語句顯示的資料庫清單在您的機器上可能不同;如果您沒有SHOW DATABASES特權，SHOW DATABASES不會顯示您沒有特權的資料庫)
- mysql: mysql資料庫描述了使用者通路權限
- test: test資料庫通常可以作為工作空間供使用者試用
- information_schema:儲存了MySQL伺服器所有資料庫的資訊
- sys: sys庫裡面的表、視圖、函數、存儲過程可以使我們更友善、快捷的了解到MySQL的一些資訊
- performance_schema：用于監控MySQL server在一個較低級别的運作過程中的資源消耗、資源等待等情況
切換使用的資料庫
#切換使用的資料庫
mysql>use mysql;
結果：
USE與QUIT一樣，不需要分号，也可以輸入分号進行區分（推薦）。USE語句也有另一種特殊之處：必須在一行上給出。
進行相關授權
其中your_mysql_name是MySQL使用者名，而your_client_host是連接配接到伺服器的主機
建立和選擇資料庫
建立資料庫
#使用create database 資料庫名
#建立名為test_stay的資料庫
mysql>create database test_stay;
結果：
如果建立的時候，報錯：ERROR 1044 (42000): Access denied for user ‘tony’@‘localhost’ to database ‘test_stay’，說明你目前登入的使用者沒有建立的權限。
我們在連接配接mysql的時候，可以直接指定相關的資料庫，就不用使用use指令去切換資料庫了
C\:>mysql -h localhost -u root -p test_stay
輸入密碼之後，直接進入test_stay資料庫。
提示：剛顯示的指令中的test_stay不是登入的密碼.如果要在-p選項後在指令行上提供密碼,則必須在中間沒有空格（例如，以-ppassword而不是-p password）.但是，不建議将密碼放在指令行中,因為這樣做會使密碼容易被登入到計算機上的其他使用者監聽.
查詢正在使用的資料庫
#使用select database()查詢正在使用的資料庫
mysql>select database();
結果：
建立資料表
查詢資料庫中的表
#查詢目前資料庫中的表
mysql>show tables;
如果傳回Empty set 證明目前資料庫中沒有建立表
建立資料庫表
通過create table 進行建立資料庫
mysql> create table t_test(
-> username varchar(12),
-> password varchar(8),
-> age int(3),
-> birthday datetime);
證明建立成功，就可以使用show tables；查詢資料庫中的資料表
查詢資料表的詳情
使用describe或者desc查詢表的詳情
查詢的結果：
使用desc的效果将是一樣的
加載資料和添加表資料
加載資料
我們這裡使用load data加載本地的資料，将本地資料插入到資料庫指定的資料庫中。
您可以建立一個文本檔案，每行包含一個記錄，其值由制表符分隔，并按在CREATE TABLE語句中列出的順序給出。 對于缺失值可以使用NULL值. 要在文本檔案中表示這些，請使用\ N.
我們在D盤中建立一個txt檔案，内容如下：
我們如下指令記性将本地文本資料插入到資料庫中：
插入結果：
提示有五條插入成功了。
如果在Windows上使用\ r \ n作為行終止符的編輯器建立了檔案，則應改用以下語句：
您可以根據需要在LOAD DATA語句中顯式指定列值分隔符和行尾标記,但是預設值為制表符和換行符。這些足以使該語句正确讀取檔案d:/my_test_stay.txt
如果該語句失敗，則可能是預設情況下您的MySQL安裝未啟用本地檔案功能。
我們也可以使用insert進行插入資料
在load data 和insert中對null值處理不一樣，load data使用\N,insert直接使用null。
檢索資料表中的資料
文法
檢索資料表中的資料，需要使用select語句。
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
- what_to_select:訓示要檢視的内容。 這可以是列的列名，也可以是*表示“所有列”
- which_table：訓示要從中檢索資料的表
- conditions_to_satisfy: WHERE子句是可選的。 如果存在，conditions_to_satisfy指定行必須滿足的一個或多個條件才有資格進行檢索。
查詢所有資料
删除資料庫中所有資料
上面是先删除資料表中的資料，在進行查詢
修改記錄
修改表中的記錄，我們可以使用update進行修改表中的指定的資料或者多行資料
由上面的操作可以看出，birthday的值已經修改。
UPDATE僅更改有問題的記錄，不需要重新加載表。
查詢指定的行
我們知道select指定後面可以接where語句，進行相關條件的篩選。下面是幾個例子
#根據使用者名查詢t_test
mysql>select * from t_test where username='tony';
我們可以and或者or關聯多個條件
#根據使用者名和密碼查詢t_test
mysql>select * from t_test where username='tony' and password='123456';
#查詢使用者名為‘tony’或者密碼為‘123456’的記錄
mysql>select * from t_test where username='tony' or password='123456';
查詢指定的列
如果不想看到表中的整個行，隻需用逗号分隔感興趣的列即可
#查詢表t_test的username列
mysql>select username from t_test;
查詢多個列
#查詢username和age列
mysql>select username,age from t_test;
将查詢出列的資料去重，使用DISTINCT關鍵字
在查詢列的時候，我們也可以在後面添加where子句進行資料過濾
#查詢年齡大于18的username和age列
mysql>select username,age from t_test where age >18;
對資料進行字段排序
我們有時候需要将資料查詢出來進行排序，mysql查詢的時候有一個預設的排序規則，但是這個規則可以不适合我們的業務使用，是以我們可以定義根據特定字段進行排序操作
我們可以使用ORDER BY,DESC，ASC關鍵字進行排序,下面是幾個列子
上面兩個排序是不一樣的，在字元類型列上，與所有其他比較操作一樣，排序通常以不區分大小寫的方式執行。 這意味着除了大小寫相同以外，其他列均未定義順序。 可以這樣使用BINARY強制對列進行區分大小寫的排序：ORDER BY BINARY col_name
預設為升序排序，我們可以使用DESC進行降序
我們可以使用ASC進行升序排序
#多個字段同時排序
mysql>select username,age from t_test order by username,age DESC;
DESC關鍵字僅适用于緊接其前age的列名； 它不影響username列的排序順序。
資料表中時間計算
MySQL提供了一些函數，可用于對日期執行計算。
通過使用者的出生年月計算出年齡，可以使用TIMESTAMPDIFF函數。 TIMESTAMPDIFF函數的參數是要表示結果的機關，以及兩個日期之間的內插補點。
mysql>select username,birthday,CURDATE(),
TIMESTAMPDIFF(YEAR,birthday,CURDATE()) AS realAge from t_test;
上面使用了CURDATE()，得到目前的時間，TIMESTAMPDIFF函數進行計算
AS關鍵字将相關計算的結果取一個别名
對計算的進行排序
#使用查詢出的結果realAge進行排序
mysql>select username,birthday,curdate(),timestampdiff(year,birthday,curdate()) as realAge from t_test order by realAge;
我們可以使用is not null 進行列的非空處理，反之可以使用is null進行空值處理
MySQL提供了一些用于提取部分日期的函數，例如YEAR()，MONTH()和DAYOFMONTH()。
#擷取出生年月的月份
mysql>select username,birthday,month(birthday) as monthNum from t_test;
#擷取出生年月的年份
mysql>select username,birthday,year(birthday) as monthNum from t_test;
month()作為where條件
當月份為12月的時候，不能進行添加操作，不然會變為13，将會出現錯誤資訊，可以編寫查詢以便無論目前月份是什麼都可以使用，進而不必在特定月份使用該數字。 DATE_ADD（）使您可以将時間間隔添加到給定的日期。 如果您在CURDATE（）的值上加上一個月，則用MONTH（）提取月份部分。
可以使用mod函數達到相同的效果
如果計算使用無效日期，則計算将失敗并産生警告
NULL處理
NULL表示“缺少未知值”，并且與其他值的處理方式有所不同
不能使用算術比較運算符（例如=，<或<>）測試NULL
由于任何與NULL進行算術比較的結果均為NULL，是以您無法從此類比較中獲得任何有意義的結果。
在MySQL中，0或NULL表示false，其他表示true。 布爾運算的預設真值是1。
在GROUP BY中，兩個NULL值被視為相等.
在執行ORDER BY時，如果執行ORDER BY … ASC，則首先顯示NULL值；如果執行ORDER BY … DESC，則最後顯示NULL值。
使用NULL時的一個常見錯誤是假定無法在定義為NOT NULL的列中插入零或空字元串，但事實并非如此。 這些實際上是值，而NULL表示“沒有值”。
是以，完全有可能在NOT NULL列中插入零或空字元串，因為這些實際上不是NOT NULL.
模糊比對
MySQL提供了标準的SQL模式比對以及基于擴充的正規表達式的模式比對形式，該正規表達式類似于vi，grep和sed等Unix實用程式所使用的擴充正規表達式。
SQL模式比對使您可以使用 _ 來比對任何單個字元，并使用 ％ 來比對任意數量的字元（包括零個字元）。在MySQL中，預設情況下，SQL模式不區分大小寫。 使用SQL模式時，請勿使用=或<>。改用LIKE或NOT LIKE比較運算符。
#查詢使用者名為h開頭的資料
mysql>select username,birthday from t_test where username LIKE "h%";
#查詢使用者名為yu結尾的資料
mysql>select username,birthday from t_test where username LIKE "%yu";
#查詢使用者名包含‘on’的資料
mysql>select username,birthday from t_test where username LIKE "%on%";
#查找使用者名正好包含4個字元的資料，請使用_模式字元
mysql>select username,birthday from t_test where username LIKE '____';
MySQL提供的另一種模式比對使用擴充的正規表達式。 在測試此類型的模式是否比對時，可以使用REGEXP_LIKE函數或REGEXP或RLIKE運算符，它們可以達到REGEXP_LIKE同樣的效果
下表描述了擴充正規表達式的一些特征：
- . 比對任何單個字元。
- […] 字元類與括号内的任何字元比對。 例如，[abc]比對a，b或c。 要命名字元範圍，請使用破折号。 [a-z]比對任何字母，而[0-9]比對任何數字。
- * 比對零個或多個前面事物的執行個體。 例如，x* 比對任意數量的x個字元，[0-9]*比對任意數量的數字，.*比對任意數量的任何字元。
- 如果正規表達式模式比對成功，則該模式比對成功.(這與LIKE模式比對不同，後者僅在模式比對整個值時才成功.)
- 要固定模式，使其必須與要測試的值的開頭或結尾比對，請在模式的開頭使用^或在模式的結尾使用$。
#查詢使用者名為h開頭的資料
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'^h');
#查詢使用者名為yu結尾的資料
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'yu$');
#查詢使用者名包含‘on’的資料
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'on');
#查找使用者名正好包含4個字元的資料
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'^....$');
#查找使用者名正好包含4個字元的資料
mysql>select username,birthday from t_test where REGEXP_LIKE(username,'^.{5}$');
計數操作
使用 COUNT(*)計算行數
#進行分組統計計數
mysql>select username,count(*) from t_test group by username;
使用GROUP BY對每個記錄的所有記錄進行分組
#針對多個列進行分組
mysql>select username,age,count(*) from t_test group by username,age;
計數的時候，可以進行條件查找
如果除了COUNT()值之外還為要命名的列命名，則應該存在一個GROUP BY子句來命名那些相同的列。 否則，将發生以下情況：
- 如果啟用了ONLY_FULL_GROUP_BY SQL模式，則會發生錯誤
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by
如果未啟用ONLY_FULL_GROUP_BY，則通過将所有行視為一個組來處理查詢，但是為每個命名列選擇的值是不确定的。 伺服器可以從任何行中自由選擇值：
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Harold | 8 |
+--------+----------+
1 row in set (0.00 sec)
使用多個表
我們有時候将使用者記錄在一張表，相關使用者的位址記錄在一張表，這時候，我們想拿到使用者的位址資訊就要操作多張表。
我們建立一個位址表
mysql>create table addree(
username varchar(12),
address varchar(64),
city varchar(16));
多表關聯查詢使用者的位址資訊
關于此查詢，有幾件事要注意：
- FROM子句連接配接兩個表，因為查詢需要從兩個表中提取資訊。
- 在合并（合并）來自多個表的資訊時，您需要指定如何将一個表中的記錄與另一個表中的記錄比對。 這很容易，因為它們都有名稱列。 該查詢使用ON子句根據名稱值比對兩個表中的記錄。
- 該查詢使用INNER JOIN組合表。 當且僅當兩個表都滿足ON子句中指定的條件時，INNER JOIN才允許其中一個表中的行出現在結果中。
- 由于名稱列同時出現在兩個表中，是以在引用該列時，必須明确說明要表示的表。 這是通過在表名之前添加列名來完成的。