曲径通幽论坛

标题: 实例操作 mysql (命令行) [打印本页]

作者: beyes 时间: 2009-6-20 16:23
标题: 实例操作 mysql (命令行)
全文操作例子均在命令行下进行：

设置 mysql 登录 IP 以及用户：
为了方便学习，设置 mysql 数据库可以从本地以及一个 IP 为192.168.2.165 (windows主机有图形管理工具)的地方登录，进行设置如下：
用 root 用户登录后，转到内置 mysql 数据库，然后查看相关设置信息：
mysql> select host, user, password from user;

mysql> update user set host='localhost' where user='beyes' and password='';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select host, user, password from user;
+---------------+-------+-------------------------------------------+
| host          | user | password                                  |
+---------------+-------+-------------------------------------------+
| localhost     | root | *90427DDE5E6F5547DA40AAF767C2DBA1ACCAB588 |
| linux-beyes   | root |                                           |
| 127.0.0.1     | root |                                           |
| 192.168.2.165 | beyes | *886EDAF45559DD3029BF938188B17C51243D68F9 |
| localhost     | beyes |                                           |
+---------------+-------+-------------------------------------------+

上面，通过修改数据库 mysql 中的 user 表，让 192.168.2.165 这个 IP 登录数据库时需要密码访问，而在本机登录时则无需密码访问。更新完这些信息后，mysql 可能需要重启才会生效：

/sbin/service mysql restart

1、登录 mysql：

beyes@linux-beyes:~/Desktop> mysql -hlocalhost -ubeyes -p******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.67 SUSE MySQL RPM

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

命令中，-h 选项后接服务器名，可以是 IP 地址。选项和主机名之间可有空格也可没有空格。
-u 选项后接用户名。
- p 后面解密码(这里隐藏掉了)

2、登录后，看都有哪些数据库可用：

[quote]mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| beyes_db           |
| mysql              |
| phpwind            |
| test               |
+--------------------+
5 rows in set (0.00 sec)

[/quote]

3、现在转到 phpwind 这个数据库里( 这个数据库是其实就是论坛的数据库 )：

mysql> use phpwind
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

4、查看 phpwind 这个数据库里所有的表：

mysql> show tables;
+-------------------+
| Tables_in_phpwind |
+-------------------+
| pw_actions        |
| pw_activity       |
| pw_actmember      |
| pw_administrators |
| pw_adminlog       |
| pw_adminset       |
| pw_announce       |
| pw_argument       |
| pw_attachbuy      |
| pw_attachs        |
... ... ...
+-------------------+
100 rows in set (0.00 sec)

注意：如果是在虚拟主机上，可能需要完整的命令： show tables from from 你的数据库名
否则会提示缺少数据库。

5、查看某个特定表的详细信息：
现在要查看 phpwind 这个数据库中的 pw_posts 这张表的详细信息，如下：

mysql> show columns from pw_posts;
+------------+-----------------------+------+-----+---------+----------------+
| Field      | Type                  | Null | Key | Default | Extra          |
+------------+-----------------------+------+-----+---------+----------------+
| pid        | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| fid        | smallint(6) unsigned | NO   | MUL | 0       |                |
| tid        | mediumint(8) unsigned | NO   | MUL | 0       |                |
| aid        | text                  | NO   |     | NULL    |                |
| author     | varchar(15)           | NO   |     |         |                |
| ... ... ... ...(太多省略)
+------------+-----------------------+------+-----+---------+----------------+
25 rows in set (0.00 sec)

也可以用下面这样的命令形式：
show columns from phpwind.pw_posts;
或者
describe pw_posts;

6、上面的几条命令效果一样，都是查看表中所有内容信息，如果只想查看某一行内容：

mysql> explain pw_posts tid;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| tid | mediumint(8) unsigned | NO | MUL | 0 | |
+-------+-----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

这里，只关心 tid 这一行的信息( tid 在论坛里表示帖子的编号信息).

7、现在，假如打算查看pw_posts 表中 author 这一项的里面的具体内容，用 slect 命令实现：

mysql> select author from pw_posts;
+--------+
| author |
+--------+
| beyes |
| beyes |
| beyes |
| beyes |
| lid4ji |
... ...( 省略 )
| beyes |
411 rows in set (0.00 sec)

这里，只查询 author(论坛中的帖子作者)里的所有记录。当然，我们可以指定多个条目。多个条目之间用逗号隔开，如：

mysql> select pid, author, postdate from pw_posts;
+------+--------+------------+
| pid | author | postdate |
+------+--------+------------+
| 1797 | beyes | 1234947213 |
| 1671 | beyes | 1230821696 |
| 1670 | beyes | 1230808918 |
| 1796 | beyes | 1234880514 |
| 1795 | beyes | 1234709520 |
... ... ...(省略)
+------+--------+------------+
411 rows in set (0.00 sec)

8、获取 pw_posts 表中关于 root 这个用户的所有记录信息：

mysql> select * from pw_posts where author="root";
+------+-----+-----+-----+--------+----------+------+------------+---------+-----------+--------+-----+-----------+------------+-----------+--------+-----------+-----------+---------+------------+--------+----------+----------+-----------+--------+
| pid | fid | tid | aid | author | authorid | icon | postdate | subject | userip | ifsign | buy | alterinfo | remindinfo | leaveword | ipfrom | ifconvert | ifwordsfb | ifcheck | content | ifmark | ifreward | ifshield | anonymous | ifhide |
+------+-----+-----+-----+--------+----------+------+------------+---------+-----------+--------+-----+-----------+------------+-----------+--------+-----------+-----------+---------+------------+--------+----------+----------+-----------+--------+
| 1986 | 41 | 870 | | root | 274 | 0 | 1245489364 | | 127.0.0.1 | 1 | | | | | ???? | 1 | 1 | 1 | i am root! | | 0 | 0 | 0 | 0 |
+------+-----+-----+-----+--------+----------+------+------------+---------+-----------+--------+-----+-----------+------------+-----------+--------+-----------+-----------+---------+------------+--------+----------+----------+-----------+--------+
1 row in set (0.00 sec)

说明：很多时候，需要显式的并不是所有的记录，尤其是在记录很多的时候。只有设定了查询条件，查询才有实际意义。比如上面要查找一个叫做 root 的作者的信息，此时就需要对查询语句进行设定。设定查询语句，是通过 where 子句来实现的。最简单的查询条件即 "=" ( 等于 )。上面，就是从数据库表 pw_posts 中查询出字段 author 的值为 root 的记录。

作者: beyes 时间: 2009-6-22 00:55
标题: 一系列的查询操作
继续对 pw_posts 这个表格进行查询操作：

1、在 pw_posts 表里有发帖者的 IP 信息，现在查看这些 IP 信息：
mysql> select userip from pw_posts;

+-----------------+
| userip          |
+-----------------+
| 124.66.48.186   |
| 121.34.176.129 |
| 121.34.48.126   |
| 124.66.51.51    |
| 124.66.51.168   |
| 124.66.51.168   |
| 117.24.64.98    |
... ... ...
+-----------------+
413 rows in set (0.00 sec)

2、这样的信息量无法满足要求，如果想看 lid4ji 这个用户的 IP 信息：

mysql> select author, userip from pw_posts where author='lid4ji';
+--------+---------------+
| author | userip |
+--------+---------------+
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
+--------+---------------+
8 rows in set (0.00 sec)

3、在第 2 中查看的只是 lid4ji 这个会员的信息，如果还想同时看 root 这个会员的信息，那么在 where 里使用 in 关键字：

mysql> select author, userip from pw_posts where author in ('lid4ji','root');
+--------+---------------+
| author | userip |
+--------+---------------+
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| lid4ji | 116.25.151.79 |
| root | 127.0.0.1 |
+--------+---------------+
9 rows in set (0.00 sec)

这里需要的是，in 关键字后面的括号里的字符串 lid4ji 和 root 要用单引号括起来。加入是数字类型的，则无需单引号。由此可见，在有许多个并列项的时候，使用 in 关键字是非常方便的。

4、假如我们想看都有哪些会员在第 23 号到第 51 号板块之间发表过帖子，并且显式出这些会员的 IP 信息：

mysql> select author, fid, userip from pw_posts where fid between 23 and 51;
+--------+-----+-----------------+
| author | fid | userip          |
+--------+-----+-----------------+
| beyes | 36 | 124.66.48.186   |
| beyes | 24 | 121.34.176.129 |
| beyes | 23 | 121.34.48.126   |
| beyes | 36 | 124.66.51.51    |
... ... ...
| beyes | 41 | 127.0.0.1       |
| ???    | 41 | 127.0.0.1       |
| root   | 41 | 127.0.0.1       |
+--------+-----+-----------------+
370 rows in set (0.01 sec)

由上面可见，基本都是我在这些板块里发帖子：）那几个问号由于是中文注册用户，这里没有显式出来，因为mysql默认使用的是lant1字符集，想显式中文见：http://www.groad.net/bbs/read.php?tid=879
这里使用了where 关键字的 between .. in ...

5、很多时候，我们的记忆会有所缺漏，比如有时可能只记得一个人的姓而没有记全名，此时就需要模糊查找。模糊查询时，使用到了关键字 like，这里找一下我们论坛中一位PLMM，她的名字我只记得前面一个字是“秦” ：

mysql> select author, userip from pw_posts where author like '秦%';
+--------------+-----------------+
| author       | userip          |
+--------------+-----------------+
| 秦荷朵朵 | 121.230.11.23   |
| 秦荷朵朵 | 121.230.11.23   |
| 秦荷朵朵 | 121.230.11.23   |
| 秦荷朵朵 | 121.230.11.156 |
| 秦荷朵朵 | 121.230.11.156 |
... ... ...
| 秦荷朵朵 | 121.230.2.153   |
| 秦荷朵朵 | 121.230.2.153   |
| 秦荷朵朵 | 121.230.8.43    |
+--------------+-----------------+
41 rows in set (0.00 sec)

找到了，这位 PLMM 的全名是秦荷朵朵。在命令中，'秦%' 的意思表示所有以 '秦' 字开头的字符串都会被检索到。如果想查询以 '朵'结尾的字符串，那么使用 '%朵' 来查询。同样，如果想查询中间为 '荷朵' 的字符串，可以使用 '%荷朵%' 。
由上面对 % 符号的应用，可以看出，这是类似于一些环境中的通配符 * 符号。联想到，如果一个主机希望一个用户能从任何一个地方都可以登录服务器，那么在 mysql 数据库中的 user 表里的 host 条目中对应改为 % 符号。

6、使用where中的 and 关键字进行查询：

mysql> select author, authorid, userip from pw_posts where author like '秦%' and userip like '%23';
+--------------+----------+-----------------+
| author       | authorid | userip          |
+--------------+----------+-----------------+
| 秦荷朵朵 |       11 | 121.230.11.23   |
| 秦荷朵朵 |       11 | 121.230.11.23   |
| 秦荷朵朵 |       11 | 121.230.11.23   |
| 秦荷朵朵 |       11 | 121.230.251.223 |
| 秦荷朵朵 |       11 | 121.230.251.223 |
| 秦荷朵朵 |       11 | 121.230.251.223 |
| 秦荷朵朵 |       11 | 121.230.251.223 |
+--------------+----------+-----------------+
7 rows in set (0.00 sec)

AND 关键字主要用来连接多个 where 查询子句，很多时候，查询需要多个条件一起成立了才可以。另外，还有个 OR 关键字，和 AND 一样，也是连接多个查询条件。通过 OR 关键字连接的多个条件，只要其中的一个条件符合，就会被检索出来。用法示例如：

select xxx from pw_posts where ( author like '%ro%' and authorid <>10 ) or userip like '%23%';

7、在进行数据查询时，有时为了方便显式或者便于用户理解，可以将查询的字段名称显示成想要的名称。比如上面，假如用户看不懂 author 的英文意思，如果换成中文 “作者” 后用户才能看懂，那么就使用 AS 这个别名关键字进行如下查询：

mysql> select author as 作者 from pw_posts where author='秦荷朵朵';
+--------------+
| 作者 |
+--------------+
| 秦荷朵朵 |
| 秦荷朵朵 |
| 秦荷朵朵 |
| 秦荷朵朵 |
+--------------+
41 rows in set (0.00 sec)

由上可见, 原来的条目 author 被替换成中文 '作者' 来显示了。

作者: beyes 时间: 2009-6-22 12:08
标题: 多表查询
很多时候，特别是规范的方法设计的数据库，在进行查询时，需要涉及到多表查询。

在执行多表操作时，必须通过 SQL 语句将两个表关联起来，进行关联查询。

下面，要同时查询两个表，一个是 pw_posts ，另外一个是 pw_administratros 。

现在先查看 pw_administrators 表里的内容：

mysql> select * from pw_administrators;
+-----+-----------------+---------+--------+
| uid | username        | groupid | groups |
+-----+-----------------+---------+--------+
|   1 | admin           |       3 |        |
|   2 | bibiyaki        |      17 |        |
|   8 | 我家的小鸟 |      -1 | ,17,   |
|   9 | natu            |      -1 | ,17,   |
| 10 | beyes           |      18 |        |
| 11 | 秦荷朵朵    |       3 |        |
|   5 | 星凌化心    |      16 |        |
|   6 | 星鬼          |      16 |        |
| 14 | 贰贰          |      16 |        |
| 37 | 都没小脑袋 |       6 |        |
| 48 | bibiyaki2       |      -1 | ,17,   |
| 147 | 泰光采德    |       6 |        |
| 192 | 都没大脑袋 |       6 |        |
| 234 | 分水娴       |       6 |        |
| 238 | 香阳学       |       6 |        |
| 268 | mikibobo        |      16 |        |
| 269 | sofeiya         |      16 |        |
| 275 | lid4ji          |      18 | ,5,    |
| 273 | l4nneret        |       7 |        |
+-----+-----------------+---------+--------+

现在我们查询 "秦荷朵朵“ 这个用户的 UID 是多少，并顺便看一下她的发帖时的 IP ：

mysql> select pw_posts.author, userip, pw_administrators.uid from pw_posts,pw_administrators where pw_posts.author='秦荷朵朵' and pw_administrators.username='秦荷朵朵';
+--------------+-----------------+-----+
| author       | userip          | uid |
+--------------+-----------------+-----+
| 秦荷朵朵 | 121.230.11.23   | 11 |
| 秦荷朵朵 | 121.230.11.23   | 11 |
| 秦荷朵朵 | 121.230.11.23   | 11 |
| 秦荷朵朵 | 121.230.11.156 | 11 |
| 秦荷朵朵 | 121.230.11.156 | 11 |
... ... ...
+--------------+-----------------+-----+
41 rows in set (0.00 sec)

由上面的查询可见，秦荷朵朵这个会员对应的 UID = 11 。
可以看到，在进行多表查询时，使用了 "表名.字段名" 的格式。这样做是为了防止 mysql 查询错误，因为很可能存在多个表的字段名称相同，所以必须要在字段前面添加表的名称加以区分。如果字段名称在表中是惟一的，则可以省略表名。

欢迎光临曲径通幽论坛 (http://www.groad.net/bbs/)