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才允許其中一個表中的行出現在結果中。
• 由于名稱列同時出現在兩個表中，是以在引用該列時，必須明确說明要表示的表。 這是通過在表名之前添加列名來完成的。