《Linux命令行与shell脚本编程大全》-使用数据库

来源：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="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>