《Linux命令行與shell腳本編程大全》-使用數據庫
MySQL數據庫
MySQL客戶端界面
mysql命令行參數9:08 2013-11-1
參數 描述
-A 禁用自動重新生成哈希表
-b 禁用 出錯後的beep聲
-B 不使用歷史文件
-C 壓縮客戶端和服務器之間發送的所有消息
-D 指定要用的數據庫
-e 執行指定語句並退出
-E 豎直方向顯示查詢輸出，每行一個數據字段
-f 如果有SQL錯誤產生，繼續執行
-G 使能命名命令的使用
-h 指定MySQL服務器主機名（默認為localhost）
-H 用HTML代碼顯示查詢輸出
-i 忽略函數名後的空格
-N 結果中不顯示列名
-o 忽略語句，除了在命令行上命名的默認數據庫的語句
-p 為用戶賬戶提示輸入命令
-P 指定網絡連接用的TCP端口號
-q 不緩存每條查詢結果
-r 顯示列輸出，不轉義
-s 使用安靜模式
-S 為本地（localhost）連接指定一個套接字
-t 以表的形式顯示輸出
-T 在程序退出時顯示調試信息、內存以及CPU統計信息
-u 指定登錄用戶名
-U 只允許指定了鍵值的UPDATE和DELETE語句
-v 使用詳細模式
-w 如果連接沒有完成，等待並重試
-X 用XHTML代碼顯示查詢輸出
mysql命令不加任何參數，則會使用Linux登錄名連接本地的MySQL服務器。
使用-u指定用戶名，-p則告訴mysql提示輸出相應的密碼
[plain]
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.1.72-0ubuntu0.10.04.1 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql使用兩種不同類型的命令：
1.特殊的mysql命令
2.標准SQL語句
mysql命令
命令 簡寫命令 描述
? \? 幫助信息
clear \c 清空命令
connect \r 連接到數據庫和服務器
delimiter \d 設置SQL語句分隔符
edit \e 用命令行編輯器編輯命令
ego \G 將命令發送到MySQL服務器並垂直顯示結果
exit \q 退出mysql程序
go \g 將命令發送到MySQL服務器
help \h 顯示幫助信息
nopaper \n 禁用輸出分頁並將輸出發送到STDOUT
note \t 不要將輸出發送到輸出文件
paper \P 將分頁命令設為指定的程序（默認是more）
print \p 打印當前命令
prompt \R 修改mysql命令提示符
quit \q 退出mysql程序（同exit）
rehash \# 重新構建命令補全哈希表
source \. 執行指定文件中的SQL腳本
status \s 從MySQL服務器提取狀態信息
system \! 在系統上執行shell命令
tee \T 將所有輸出附加到指定文件中
use \u 使用另外一個數據庫
charset \C 切換到另一個字符集
warnings \W 在每條語句之後顯示警告消息
nowarnings \w 不要在每條語句之後顯示警告消息
SHOW可以查看數據庫信息，比如：
[plain]
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
也可以查看數據庫中的表信息
[plain]
mysql> USE mysql;
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
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
mysql裡面的數據庫命令是不區分大小寫的，但是習慣是使用大寫字母
創建MySQL數據庫對象
創建數據庫：
CREATE DATABASES +庫名
[plain]
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
創建用戶賬戶
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON test.* TO test_user IDENTIFIED by 'pwd';
GRANT SELECT,INSERT,UPDATE,DELETE說明了可以對數據庫進行增刪改查
ON test.*指定了作用在test數據庫上面的所有表（格式為database.table）
TO test_user IDENTIFIED by 'pwd'指定了賬戶為test_user，如果test_user賬戶不存在，則自動創建，IDENTIFIED by允許設置默認密碼，此處密碼為pwd
[plain]
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON test.* TO test_user IDENTIFIED by 'pwd';
Query OK, 0 rows affected (0.00 sec)
之後就可以使用新賬戶登錄了
[plain]
$ mysql test -u test_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 59
Server version: 5.1.72-0ubuntu0.10.04.1 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
PostgreSQL數據庫
PostgreSQL命令行界面
psql命令行參數
簡寫名稱 完整名稱 描述
-a --echo-all 在輸出中顯示腳本文件中執行的所有SQL行
-A --no-align 將輸出格式設為非對齊模式，數據不顯示或格式化的表
-c --command 執行指定的SQL語句並退出
-d --dbname 指定要連接的數據庫
-e --echo-queries 將所有的查詢輸出到屏幕上
-E --echo-hidden 將隱藏的psql元命令輸出到屏幕上
-f --file 執行指定文件中的SQL命令並退出
-F --field-separator 指定在非對齊模式中分開列表數據的字符。默認是逗號
-h --host 指定遠程PostgreSQL服務器的IP地址或主機名
-l --list 顯示服務器上已有的數據庫列表並退出
-o --output 將查詢輸出重定向到指定文件中
-p --post 指定要連接的PostgreSQL服務器的TCP端口
-P --pset 將表打印選項設為指定的值
-q --quiet 安靜模式，不會顯示輸出消息
-R --record-separator 將指定字符做為數據行分隔符。默認為換行符
-s --single-step 在每個SQL查詢後 提示繼續還是退出
-S --single-line 指定回車鍵而不是分號為一個SQL查詢的結束
-t --tuples-only 在表輸出中禁用列的頭部和尾部
-T --table-attr 在HTML模式時使用指定的HTML表標簽
-U --username 使用指定的用戶名連接PostgreSQL服務器
-v --variable 將指定變量設成指定值
-V --version 顯示psql版本號並退出
-W --password 強制命令提示符
-x --expanded 使能擴展表輸出以顯示數據行的額外信息
-X --nopsqlrc 不要運行psql啟動文件
-? --help 顯示psql命令行幫助信息並退出
PostgreSQL管理員賬戶為postgres，而不是root
如果當前Linux登錄賬戶不是postgres，那麼需要使用sudo來以postgres賬戶運行psql
[plain]
$ sudo -u postgres psql
[sudo] password for su1216:
psql (8.4.17)
Type "help" for help.
postgres=#
提示符#表示已經做為管理員登錄psql。
psql使用兩種不同類型的命令：
1.Postgre元命令
2.標准SQL語句
Postgre元命令可以方便的獲取數據庫環境確切信息，還具有psql會話的set功能。
元命令用反斜線標示。
常用的元命令：
\l：列出已有數據庫
\c：連接到數據庫
\dt：列出數據庫中的表
\du列出Postgre的用戶
\z：列出表的權限
\?：列出所有可用元命令
\h：列出所有可用SQL命令
\q：退出數據庫
[plain]
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------------+-----------------------
postgres | postgres | UTF8 | C | zh_CN.UTF-8 |
template0 | postgres | UTF8 | C | zh_CN.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | C | zh_CN.UTF-8 | =c/postgres
: postgres=CTc/postgres
(3 rows)
創建PostgreSQL數據庫對象
CREATE DATABASE +庫名
[plain]
postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------------+-----------------------
postgres | postgres | UTF8 | C | zh_CN.UTF-8 |
template0 | postgres | UTF8 | C | zh_CN.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | C | zh_CN.UTF-8 | =c/postgres
: postgres=CTc/postgres
test | postgres | UTF8 | C | zh_CN.UTF-8 |
(4 rows)
postgres=# \c test
psql (8.4.17)
You are now connected to database "test".
test=#
連接到test數據庫上的時候，psql的提示符變了，顯示的是連接的數據庫名稱
說明：PostgreSQL在數據庫增加了一個控制層，稱為模式（schema）。
數據庫可以有多個模式，每個模式包含多個表。
默認情況下，每個數據庫都有一個稱為public的模式。上面的例子中使用的就是public模式。
PostgreSQL中用戶賬戶稱為登錄角色（Login Role）。PostgreSQL會將登錄角色和Linux系統用戶賬戶匹配。
所以有兩種常用方法來創建登錄角色來運行訪問PostgreSQL數據庫的shell腳本：
1.創建一個和PostgreSQL登錄角色對應的特殊Linux賬戶來運行所有的shell腳本
2.為每個需要運行shell腳本來訪問數據庫的Linux用戶賬戶創建PostgreSQL賬戶
CREATE ROLE +名稱
[plain]
test=# CREATE ROLE su login;
CREATE ROLE
test=#
這樣就建立了一個角色，如果不使用login參數的話，則不允許登錄到PostgreSQL服務器，但可以被授予一些權限。這種角色類型稱為組角色（group role）。
PostgreSQL不允許將所有權限賦給匹配到表一級的所有數據庫對象，需要為每一個新建的表授予權限。
使用數據表
創建數據表
在創建新表前確保用管理員用戶賬戶（MySQL中的root用戶，PostgreSQL中的postgres用戶）登錄來創建表
MySQL和PostgreSQL的數據類型
數據類型 描述
char 定長字符串值
varchar 變長字符串值
int 整數值
float 浮點值
Boolean 布爾類型true/false值
Date YYYY-MM-DD格式的日期值
Time HH:mm:ss格式的時間值
Timestamp 日期和時間值的組合
Text 長字符串值
BLOB 大的二進制值
使用CREATE TABLE建立表
[plain]
test=# CREATE TABLE employees (
test(# empid int not null,
test(# lastname varchar(30),
test(# firstname varchar(30),
test(# salary float,
test(# primary key (empid));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "employees_pkey" for table "employees"
CREATE TABLE
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | employees | table | postgres
(1 row)
在psql中還需要在表一級分配權限。
[plain]
test=# GRANT SELECT,INSERT,DELETE,UPDATE ON public.employees TO su;
GRANT
以postgres登錄角色來執行，並連接到test數據庫，且必須指定模式名。
插入和刪除數據
關於SQL部分，這裡不做詳細筆記。
[plain]
mysql> CREATE TABLE employees (
-> empid int not null,
-> lastname varchar(30),
-> firstname varchar(30),
-> salary float,
-> primary key (empid));
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO employees VALUES (1,'Blum', 'Rich', 1234.5);
Query OK, 1 row affected (0.03 sec)
查詢數據
[plain]
mysql> SELECT * FROM employees;
+-------+----------+-----------+--------+
| empid | lastname | firstname | salary |
+-------+----------+-----------+--------+
| 1 | Blum | Rich | 1234.5 |
+-------+----------+-----------+--------+
1 row in set (0.00 sec)
在腳本中使用數據庫
連接到數據庫
對於psql：
[plain]
$ cat psql_connection
#!/bin/bash
psql=`which psql`
sudo -u postgres $psql
$ psql_connection
could not change directory to "/home/su1216/android/source/linux_learned"
psql (8.4.17)
Type "help" for help.
postgres=#
對於mysql：
[plain]
$ cat mysql_connection
#!/bin/bash
mysql=`which mysql`
$mysql "test" -u "test" -p
$ mysql_connection
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.1.72-0ubuntu0.10.04.1 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
執行腳本時，mysql會停下來要求用戶輸入密碼，這個問題可以避免。
下面是一種糟糕的方式，直接將密碼放在腳本中明文顯示：
[plain]
$ cat mysql_connection
#!/bin/bash
mysql=`which mysql`
$mysql "test" -u "test" -ptest
-p與密碼緊密相連。
另一種解決方案：
mysql使用$HOME/.my.cnf文件來讀取特殊的啟動命令和設置。
如果沒有這個文件，我們自己建立一個即可
[plain]
$ touch /home/su1216/.my.cnf
$ gedit /home/su1216/.my.cnf
$ chmod 400 /home/su1216/.my.cnf
.my.cnf內容如下
[plain]
$ cat /home/su1216/.my.cnf
[client]
password = test
現在再執行mysql_connection就不會要求輸入密碼了
向服務器發送命令
1.發送一個命令並退出
2.發送多個命令
對於mysql，可以使用-e選項：
[plain]
$ cat mysql_test
#!/bin/bash
mysql=`which mysql`
$mysql "test" -u "test" -ptest -e "select * from employees"
輸出結果為：
[plain]
$ mysql_test
+-------+----------+-----------+--------+
| empid | lastname | firstname | salary |
+-------+----------+-----------+--------+
| 1 | Blum | Rich | 1234.5 |
+-------+----------+-----------+--------+
對於psql，可以使用-c選項
發送多條命令可以使用重定向，注意：最後的EOF所在行不能有其他字符串。
[plain]
$ cat mysql_test
#!/bin/bash
mysql=`which mysql`
$mysql "test" -u "test" -ptest << EOF
show tables;
select * from employees;
EOF
返回的結果是原始數據，沒有之前的邊框。
多條命令的結果之間沒有分隔符
[plain]
$ mysql_test
Tables_in_test
employees
empid lastname firstname salary
1 Blum Rich 1234.5
對於psql也適用，但是返回的結果是有邊框的。
[plain]
$ cat psql_test
#!/bin/bash
psql=`which psql`
sudo -u postgres $psql << EOF
\c test;
select * from employees;
EOF
輸出結果：
[plain]
$ psql_test
could not change directory to "/home/su1216/android/source/linux_learned"
You are now connected to database "test".
empid | lastname | firstname | salary
-------+----------+-----------+--------
1 | Blum | Rich | 1234.5
(1 row)
格式化數據
將結果集賦給變量：
[plain]
#!/bin/bash
mysql=`which mysql`
results=`$mysql "test" -u "test" -Bse 'show databases'`
for result in $results
do
echo "$result"
done
其中-B指明了mysql使用批處理模式（禁止了格式化符號）
-s（silent）使得列標題被禁止掉
使用格式化標簽
psql和mysql都使用-H來以HTML格式顯示結果
[plain]
$ mysql "test" -u "test" -He 'select * from employees'
<TABLE BORDER=1><TR><TH>empid</TH><TH>lastname</TH><TH>firstname</TH><TH>salary</TH></TR><TR><TD>1</TD><TD>Blum</TD><TD>Rich</TD><TD>1234.5</TD></TR><TR><TD>2</TD><TD>Blum</TD><TD>Poor</TD><TD>321.099</TD></TR></TABLE>
mysql還可以以XML格式顯示結果
[plain]
$ mysql "test" -u "test" -Xe 'select * from employees'
<?xml version="1.0"?>
<resultset statement="select * from employees
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="empid">1</field>
<field name="lastname">Blum</field>
<field name="firstname">Rich</field>
<field name="salary">1234.5</field>
</row>
<row>
<field name="empid">2</field>
<field name="lastname">Blum</field>
<field name="firstname">Poor</field>
<field name="salary">321.099</field>
</row>
</resultset>
