目的
本文主要記錄一些常見的mysql知識, 可以理解為mysql掃碼貼。
瞭解mysql
資料庫: 一個儲存著有組織的資料的容器。
資料庫軟體: DBMS, 資料庫是通過DBMS來操作的容器。
表: 某中特定資料型別的結構化清單
模式(schema): 關於資料庫和表的佈局以及特性的相關資訊
列(column): 表由列組成, 列中儲存著表中某部分資訊, 每一列都有相應的資料型別
資料型別: 所容許的資料型別, 它限制該列所儲存的資料結構
行(row/record): 表中的一行記錄
主鍵(primary key): 唯一標識表中每行的這個列(這組列), 每個表都應該擁有一個主鍵
複製程式碼滿足主鍵的兩個條件:
1. 任意兩行都不會具有相同的主鍵值
2. 每行都要有一個主鍵值(不能為空)
複製程式碼像mysql, oracle, sql server等資料庫都是基於客戶機-伺服器的數控庫。 伺服器部分是負責所有資料的訪問與處理, 一般是安裝在資料庫伺服器上, 客戶機則是與使用者打交道的軟體。
以mysql為例:
伺服器軟體: mysql DBMS
客戶機: 支援mysql的工具, 如java,node,python等程式語言
複製程式碼使用mysql
連線資料庫, 需要主機名,埠，一個合法的使用者名稱, 或者使用者口令。
// 以本地連線為例
mysql -u root -p
複製程式碼選擇資料庫使用USE關鍵字。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| s3 |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use s3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
複製程式碼檢視一個資料庫中的列表: show tables;
mysql> show tables;
+--------------+
| Tables_in_s3 |
+--------------+
| person |
+--------------+
1 row in set (0.00 sec)
複製程式碼檢視錶中的列: show columns from dbName;
mysql> show columns from person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | int(11) | NO | | NULL | |
| birth | date | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
複製程式碼上述檢視的方式還可以使用describe語句
mysql> describe person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | int(11) | NO | | NULL | |
| birth | date | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
複製程式碼show status: 檢視伺服器狀態資訊。
show errors: 顯示伺服器錯誤資訊
show warnings: 顯示伺服器警告資訊
show grants: 顯示授予使用者的安全許可權
複製程式碼檢視建立表的sql語句: show create table tableName;
mysql> show create table person;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`sex` int(11) NOT NULL,
`birth` date DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
複製程式碼檢視建立資料庫的語句: show create database dbName;
mysql> show create database s3;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| s3 | CREATE DATABASE `s3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
複製程式碼檢索資料
主要介紹使用select語句從資料庫中檢索一個或者多個資料列。
- 檢索單個列
mysql> select name from person;
+-------+
| name |
+-------+
| kobe |
| james |
| james |
| james |
| james |
| james |
| james |
| james |
| james |
| james |
| james |
+-------+
11 rows in set (0.00 sec)
複製程式碼- 檢索多個列
檢索多個列, 列名之間使用逗號隔開。
mysql> select name, sex from person;
+-------+-----+
| name | sex |
+-------+-----+
| kobe | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
+-------+-----+
11 rows in set (0.00 sec)
複製程式碼- 檢索所有列
使用萬用字元*來進行。
mysql> select * from person;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
| 2 | james | 1 | 2013-09-12 |
| 3 | james | 1 | 2013-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 5 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 7 | james | 1 | 2013-09-12 |
| 8 | james | 1 | 2013-09-12 |
| 9 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-09-12 |
| 11 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
複製程式碼- 檢索不同的值
如果檢索資料時候不想要出現重複的結果，可以使用distinct關鍵字. distinct關鍵字需要放在所有列的前面, 因此不能部分使用distinct。
mysql> select distinct name from person;
+-------+
| name |
+-------+
| kobe |
| james |
+-------+
2 rows in set (0.00 sec)
複製程式碼- 限制結果
使用limit(不多於)關鍵字限制檢索出來的數量。
mysql> select * from person limit 5;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
| 2 | james | 1 | 2013-09-12 |
| 3 | james | 1 | 2013-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 5 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
複製程式碼limit後面可以跟兩個值, 第一個為起始位置, 第二個是要檢索的行數。
mysql> select * from person limit 5, 5;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 6 | james | 1 | 2013-09-12 |
| 7 | james | 1 | 2013-09-12 |
| 8 | james | 1 | 2013-09-12 |
| 9 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
5 rows in set (0.00 sec)
複製程式碼mysql> select * from person limit 0,1;
+-----+------+-----+------------+
| sid | name | sex | birth |
+-----+------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
+-----+------+-----+------------+
1 row in set (0.00 sec)
複製程式碼limit可以配合offset使用, 如limit 4 offset 3(從行3開始後的四行)。
mysql> select * from person limit 3,4
-> ;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 4 | james | 1 | 2013-09-12 |
| 5 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 7 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
4 rows in set (0.00 sec)
mysql> select * from person limit 4 offset 3;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 4 | james | 1 | 2013-09-12 |
| 5 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 7 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
4 rows in set (0.00 sec)
複製程式碼- 使用完全限定的表名
mysql> select distinct person.name from s3.person;
+-------+
| name |
+-------+
| kobe |
| james |
+-------+
2 rows in set (0.00 sec)
複製程式碼排序檢索資料
講述使用select語句的order by子句,來對檢索出來的資料進行排序。 order by子句可以取一個或者多個列的名字。
- 普通的排序
mysql> select * from person;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
| 2 | wade | 1 | 2013-09-12 |
| 3 | cup | 1 | 2013-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 5 | se | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
| 8 | jjs | 1 | 2013-09-12 |
| 9 | ass | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-09-12 |
| 11 | jams | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
mysql> select * from person order by name;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 9 | ass | 1 | 2013-09-12 |
| 3 | cup | 1 | 2013-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-09-12 |
| 11 | jams | 1 | 2013-09-12 |
| 8 | jjs | 1 | 2013-09-12 |
| 1 | kobe | 1 | 2018-11-27 |
| 5 | se | 1 | 2013-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
| 2 | wade | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
複製程式碼- 按照多個列進行排序
下面這個例子標示僅在相同的name時候才對birth欄位進行排序。
mysql> select * from person order by name, birth;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 9 | ass | 1 | 2013-09-12 |
| 3 | cup | 1 | 2003-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-11-12 |
| 6 | james | 1 | 2015-09-12 |
| 11 | jams | 1 | 2011-09-12 |
| 8 | jjs | 1 | 2013-09-23 |
| 1 | kobe | 1 | 2018-11-27 |
| 5 | se | 1 | 2013-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
| 2 | wade | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
複製程式碼- 指定排序方向
資料排序不限於升序(a-z), 升序是預設的排序方式, 同樣我們也可以通過指定desc關鍵字來表示降序。
mysql> select name from person order by name desc;
+-------+
| name |
+-------+
| wade |
| sssw |
| se |
| kobe |
| jjs |
| jams |
| james |
| james |
| james |
| cup |
| ass |
+-------+
11 rows in set (0.00 sec)
複製程式碼mysql> select * from person order by name desc, birth;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 2 | wade | 1 | 2013-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
| 5 | se | 1 | 2013-09-12 |
| 1 | kobe | 1 | 2018-11-27 |
| 8 | jjs | 1 | 2013-09-23 |
| 11 | jams | 1 | 2011-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-11-12 |
| 6 | james | 1 | 2015-09-12 |
| 3 | cup | 1 | 2003-09-12 |
| 9 | ass | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
複製程式碼desc關鍵字只能應用到它前面到列名。如果你想對多個列進行降序排序那麼你需要每個列指定desc。
order by 與 limit的順序問題: limit子句要放在order by 後面。
mysql> select name from person order by name desc limit 4;
+------+
| name |
+------+
| wade |
| sssw |
| se |
| kobe |
+------+
4 rows in set (0.00 sec)
複製程式碼過濾資料
通過select語句配合where子句進行資料過濾。
- 使用where子句
mysql> select name from person where sid > 4 order by name desc;
+-------+
| name |
+-------+
| sssw |
| se |
| jjs |
| jams |
| james |
| james |
| ass |
+-------+
7 rows in set (0.00 sec)
複製程式碼關於where子句的位置: 在同時使用where和order by子句時候, 我們應該讓order by位於where 子句之後。
- where子句操作符
mysql> select name from person where sid <> 6 and name = 'kobe';
+------+
| name |
+------+
| kobe |
+------+
1 row in set (0.01 sec)
複製程式碼範圍值檢測 => between 5 and 10;表示5到10之間。
mysql> select * from person where sid between 5 and 7;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 5 | se | 1 | 2013-09-12 |
| 6 | james | 1 | 2015-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
+-----+-------+-----+------------+
3 rows in set (0.00 sec)
複製程式碼空值檢測 => is null
mysql> select * from person where sid is null;
Empty set (0.00 sec)
複製程式碼資料過濾
- 組合where子句
我們可以通過操作符來組合多個where子句。
mysql> select * from person where sex = 1 and sid <= 4;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
| 2 | wade | 1 | 2013-09-12 |
| 3 | cup | 1 | 2003-09-12 |
| 4 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
4 rows in set (0.00 sec)
複製程式碼mysql> select * from person where sex = 1 and sid <= 4 and name = 'kobe';
+-----+------+-----+------------+
| sid | name | sex | birth |
+-----+------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
+-----+------+-----+------------+
1 row in set (0.00 sec)
複製程式碼- or操作符
mysql> select * from person where sid <= 4 or sid >= 8;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
| 2 | wade | 1 | 2013-09-12 |
| 3 | cup | 1 | 2003-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 8 | jjs | 1 | 2013-09-23 |
| 9 | ass | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-11-12 |
| 11 | jams | 1 | 2011-09-12 |
+-----+-------+-----+------------+
8 rows in set (0.00 sec)
複製程式碼- 計算次序
where可以通過若干個and或者or進行連線, 這個時候我們需要注意他們組合順序, 我們可以使用()明確的分組操作符組合。
mysql> select * from person where (sid = 4 or sid = 9) or name = 'james';
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 4 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2015-09-12 |
| 9 | ass | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-11-12 |
+-----+-------+-----+------------+
4 rows in set (0.00 sec)
複製程式碼- in操作符
in操作符可以用於指定操作範圍，範圍內每個條件都可以進行匹配。
mysql> select * from person where name in ('james', 'ass') order by name desc;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 4 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2015-09-12 |
| 10 | james | 1 | 2013-11-12 |
| 9 | ass | 1 | 2013-09-12 |
+-----+-------+-----+------------+
4 rows in set (0.00 sec)
複製程式碼使用in操作符有哪些優勢:
1. 使用長的合法選項清單時候, in操作符比較直觀。
2. in操作符計算的次序比較好管理
3. in操作符一般比or操作符效率快
4. in操作符可以包括其他select語句，能夠更加動態的建立where子句
複製程式碼- not操作符
not操作符只有一個優點, 就是否定它後面的任何條件。
mysql> select * from person where name not in ('james', 'ass');
+-----+------+-----+------------+
| sid | name | sex | birth |
+-----+------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
| 2 | wade | 1 | 2013-09-12 |
| 3 | cup | 1 | 2003-09-12 |
| 5 | se | 1 | 2013-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
| 8 | jjs | 1 | 2013-09-23 |
| 11 | jams | 1 | 2011-09-12 |
+-----+------+-----+------------+
7 rows in set (0.00 sec)
複製程式碼mysql支援not對in, between, exists子句取反。
用萬用字元進行過濾
萬用字元: 用來匹配值的一部分的特殊符號。
搜尋模式: 由字面值, 萬用字元或者兩者組合成的搜尋條件。
在使用萬用字元時候我們需要使用like操作符。
sql支援以下幾種萬用字元:
- %號萬用字元
%符表示任何字元出現任意次數。
mysql> select name from person where name like 's%';
+------+
| name |
+------+
| se |
| sssw |
+------+
2 rows in set (0.00 sec)
複製程式碼可以同時使用多個萬用字元。
mysql> select name from person where name like 'j%m%';
+-------+
| name |
+-------+
| james |
| james |
| james |
| jams |
+-------+
4 rows in set (0.00 sec)
複製程式碼%表示匹配0個,1個,多個字元，但是%號不能匹配null。
- 下劃線萬用字元
下劃線只能匹配單個字元, 不能多也不能少。
mysql> select name from person where name like '_ams';
+------+
| name |
+------+
| jams |
+------+
1 row in set (0.00 sec)
複製程式碼使用萬用字元的一些技巧:
1. 不過度使用萬用字元
2. 確實需要使用萬用字元時候, 不要將它們置於開始處, 把萬用字元置於搜尋模式的開始處是最慢的。
3. 多個萬用字元使用時候格外注意它們的位置
複製程式碼note: 明天繼續更新, 晚安。