来源：blog.csdn.net/su1216
《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="">
<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>
