首页 » Mysql基础

Mysql工具集

运行Mysql脚本

从命令行直接运行脚本:

$ cat /tmp/my.sql 
show databases;
$ mysql -ubailing -p < /tmp/my.sql 
Enter password: 
Database
information_schema
mysql
test

数据库表管理

删除表:

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...

如:

mysql> drop table if exists books, orders;

select语句

select从数据库中获取数据。

简单的读取1列数据:

mysql> select username from orders where order_id = 1234;
+----------+
| username |
+----------+
| 3tgte    |
+----------+
1 row in set (0.00 sec)

读取多列数据(用逗号分隔各列):

mysql> select username, order_time from orders where order_id = 1234;  
+----------+---------------------+
| username | order_time          |
+----------+---------------------+
| 3tgte    | 2012-02-15 16:50:27 |
+----------+---------------------+
1 row in set (0.00 sec)

以上都用where子句限制了读取的条件。

使用count(*)作统计。统计所有订单数:

mysql> select count(*) as order_count from orders;                    
+-------------+
| order_count |
+-------------+
|       24923 |
+-------------+
1 row in set (0.00 sec)

group by

表格由两列组成,第一列表示区域,第二列是国家:

mysql> select * from airmail limit 5;
+------+----------------+
| zone | country        |
+------+----------------+
| A    | American Samoa |
| A    | Bangladesh     |
| A    | Bhutan         |
| A    | Brunei         |
| A    | Cambodia       |
+------+----------------+

查看每个区域有多少国家(对zone执行group by,然后统计count(zone)):

mysql> select zone,count(zone) from airmail group by zone;
+------+-------------+
| zone | count(zone) |
+------+-------------+
| A    |          56 |
| B    |          46 |
| C    |           2 |
| D    |         154 |
+------+-------------+

将查询结果写入文件

into outfile FILENAME将查询结果写入文件:

select * from airmail into outfile '/tmp/file.txt';

if()函数

if()的语法:

if(expr1, expre2, expr3)

用伪代码描述即是:

if expr1 != 0 and expr1 != NULL
    return expr2
else
    return expr3

expr2, expr3可以是字符串或数值。

select if(readed, 'Read', 'Unread') as status, ts from message where username='df_igor' limit 5;      
+--------+---------------------+
| status | ts                  |
+--------+---------------------+
| Unread | 2013-07-22 07:44:12 |
| Unread | 2013-07-22 07:38:49 |
| Unread | 2013-07-22 07:36:59 |
| Read   | 2013-07-18 14:04:27 |
| Read   | 2013-07-18 14:04:30 |
+--------+---------------------+
5 rows in set (0.00 sec)

as语句

目标:选出最近的500个订单,按用户下单量排序,列出订单量最高的5个用户,以及他们的订单数。

mysql> select username, count(order_id) as order_count from (select * from orders order by order_time desc limit 500) as sel_orders group by username order by count(order_id) desc limit 5;
+-----------+-------------+
| username  | order_count |
+-----------+-------------+
| u1        |          48 |
| xglrzge   |          26 |
| tahxen7   |          14 |
| xerlooda  |          12 |
| qwung1012 |          11 |
+-----------+-------------+
5 rows in set (0.04 sec)

分析。首先是挑出最近的500个订单:

select * from orders order by order_time desc limit 500

把他作为一个数据集,来统计用户下单数:

(select * from orders order by order_time desc limit 500) as sel_orders

接下来按用户名分组(group by),并按订单量排序(order by):

select username, count(order_id) as order_count from sel_orders group by username order by count(order_id) desc limit 5;

修改table

使用alter table TABLENAME修改表格:

mysql> alter table payments change pay_ts pay_ts timestamp default current_timestamp on update current_timestamp;

修改已有列,并指定其位置:

mysql> alter table cdngroup change gtype gtype char(32) after gid;

删除列:

mysql> alter table users drop login_count;

添加索引:

mysql> alter table orders add index(username);

添加注释:

mysql> alter table users change blevel blevel int comment "buyer level"

修改表名

使用rename table修改表名:

rename table OLDNAME to NEWNAME;

如:

mysql> rename table berlinix_coin_account to coin_account;

分享

0