MySQL命令操作（Linux平台）

Linux shell 批量创建数据库/表

Shell 脚本如下：

# create database and table
HOST='localhost'
PORT='3306'
USER='root'
PWD=''
DBNAME='top123'
TABLENAME='gametop800'

mysql_login=''
mysql_create_db=''
mysql_create_table=''


function mysql_create(){
    echo "login mysql $HOST:$PORT ..."
    mysql_login="sudo mysql -h $HOST -P $PORT -u $USER"  # mysql -h host -P port -u root -p pwd
    echo | ${mysql_login}
    if [ $? -ne 0 ]; then
        echo "login mysql ${HOST}:${PORT} failed.."
        exit 1
    fi

    echo "create database $DBNAME ..."
    mysql_create_db="create database if not exists $DBNAME"
    echo ${mysql_create_db} | ${mysql_login}
    if [ $? -ne 0 ]; then
        echo "create db ${DBNAME} failed.."
        exit 1
    fi

    echo "create table $TABLENAME ..."
    mysql_create_table="create table $TABLENAME(
        id char(50) not null,
        top int,
        name char(100),
        category char(50),
        rating float,
        ratingcount char(20),
        download char(30),
        price char(20),
        publishdate char(20),
        version char(40),
        filesize char(40),
        requireandroid char(40),
        contentrating char(40),
        country char(10),
        dtime datetime not null,
        primary key(id, dtime)
    )"

    echo ${mysql_create_table} | ${mysql_login} ${DBNAME}
    if [ $? -ne 0 ]; then
        echo "create table ${TABLENAME} fail..."
        exit 1
    fi

    echo "create table ${TABLENAME} success!"
}

# create database and table
HOST='localhost'
PORT='3306'
USER='root'
PWD=''
DBNAME='top123'
TABLENAME='gametop800'

mysql_login=''
mysql_create_db=''
mysql_create_table=''


function mysql_create(){
    echo "login mysql $HOST:$PORT ..."
    mysql_login="sudo mysql -h $HOST -P $PORT -u $USER"  # mysql -h host -P port -u root -p pwd
    echo | ${mysql_login}
    if [ $? -ne 0 ]; then
        echo "login mysql ${HOST}:${PORT} failed.."
        exit 1
    fi

    echo "create database $DBNAME ..."
    mysql_create_db="create database if not exists $DBNAME"
    echo ${mysql_create_db} | ${mysql_login}
    if [ $? -ne 0 ]; then
        echo "create db ${DBNAME} failed.."
        exit 1
    fi

    echo "create table $TABLENAME ..."
    mysql_create_table="create table $TABLENAME(
        id char(50) not null,
        top int,
        name char(100),
        category char(50),
        rating float,
        ratingcount char(20),
        download char(30),
        price char(20),
        publishdate char(20),
        version char(40),
        filesize char(40),
        requireandroid char(40),
        contentrating char(40),
        country char(10),
        dtime datetime not null,
        primary key(id, dtime)
    )"

    echo ${mysql_create_table} | ${mysql_login} ${DBNAME} 
    if [ $? -ne 0 ]; then
        echo "create table ${TABLENAME} fail..."
        exit 1
    fi

    echo "create table ${TABLENAME} success!"
}

执行脚本后，结果如下：

MySQL 从常用操作命令：

显示数据库

show databases;

切换数据库 use top123;

显示数据库表

show tables;

修改主键

alter table gametop800 add primary key(id);

删除主键

alter table gametop800 drop primary key;

添加组合主键

alter table gametop800 add primary key(id, dtime);

如果感兴趣的话，可以深入综合运用awk，cut，paste，正则表达式，数据库操作等较为复杂的语法，完成MySQL数据库操作

下面是实现了一个抓取网页，提取特征，抽取信息，结果汇总，批量插入数据库永久保存

示例：

附加：

1） 查看正在处理的进程：

show  processlist;

2） 查看数据库占空间大小：

show  table  status  from  some_database;      

例如： show table status from top_500;        # top_500  is  a  database

SELECT  table_schema  top_500, sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

查询结果如下：

3） 日期模糊查询

对于日期如： 2012-01-03 12：34：54 ，  2012-01-03 18：14：34， 2012-01-04 14：35：59等日期，想按日期，即 2012-01-03， 2012-01-04， 2012-01-05求和排序，可用 date_format(time, '%Y-%m-%d') 格式，如下：

select name, time, sum(num) from click_table where name='com.wordsmobile' group by date_format(time, '%Y-%m-%d') order by time desc limit 30;

2011-12-06 10:04:26 格式： 

date_format(time, '%Y-%m-%d %H:%m:%S')

如查询今天签到的同学

select true_name from sign_in WhereDATE_FORMAT((datetime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')group by true_name;

 

4）MySql 查询一周内最近7天记录  

 

本周内：
select * from wap_content where week(created_at) = week(now);

查询一天：
select * from table where to_days(column_time) = to_days(now());

select * from table where date(column_time) = curdate();

查询7天：
select * from table  where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);

查询一个月：
select * from table  where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(column_time);