mysql常用命令

时间：2015-03-27 15:09:45 收藏：0 阅读：350

1.复制相同的表；
use database_name;
复制表结构
create table table_name select * from original_table_name where 1=2;
复制表结构且全部数据
create table table_name select * from original_table_name where 1=1;
2.查询表中前N行；
select * from table_name limit 0,n;
3.命令下导出备份数据库；
D:\wamp\bin\mysql\mysql5.1.30\bin>mysqldump.exe -u root -p pdexaminer > pdexaminer-20111114.sql
4.查询表中某字段包含clip_image014的信息；
select * from pd_tsubject_child where f_answer like ‘%clip_image014%‘
5.删除存在的表
DROP TABLE IF EXISTS `pd_tsubject`
6查询表中txueke_id为8和9的数据量
select count(*) from pd_tsubject where txueke_id in(8,9)

判断重复记录，某2个字段 (2009-6-26)
    select 名字,版本
    from 表
    group by 名字,版本
    having count(*)>1

编程排序 CASE WHEN END (2009-6-1)
    1. CASE WHEN THEN END
    2. IF THEN
    3. IF EXISTS
    4. IF NOT EXISTS

SELECT *,CASE graphic WHEN graphic IS NULL THEN 1 ELSE 10 END as graphic_order, CASE user_id WHEN ‘315268‘ THEN 999 WHEN ‘178599‘ THEN 1000 ELSE 1 END as user_id_order FROM user_contents_category WHERE 1 AND type=1 AND public=1 GROUP BY name ORDER BY graphic_order desc,user_id_order desc,popularity desc,updated_at desc LIMIT 0, 116

insert ignore into (2009-4-22)
    INSERT IGNORE 与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
    eg:
    insert ignore into table(name) select name from table2
    insert ignore into table set

查看sql执行语句 (2008-12-9)
    1.通过日志查看mysql正在执行的SQL语句
        在mysql的配置文件 my.ini 中最后添加
        log=d:/mysql/log/log.txt
        重启mysql
        可以记录所有的mysql执行的sql语句

    2.show processlist
        a.进入 mysql/bin 目录下输入 mysqladmin processlist;
        b.启动 mysql ，输入 show processlist;
        出结果的字段解释中可以分析执行了的sql语句类型

嵌套查询
    SELECT status,COUNT(*) as count
        FROM user_contents t1,
            (SELECT *
            FROM user_contents_assign
            WHERE assign_number=(SELECT MAX(assign_number)
                FROM user_contents_assign
                WHERE user_id=".$arrParams[‘user_id‘].") AND user_id=".$arrParams[‘user_id‘].")
            AS t2
    WHERE t1.v3_id=t2.v3_id AND t1.user_id=".$arrParams[‘user_id‘]."
    GROUP BY status;

RAND 随机取数据
select * from tablename order by rand() limit 10

MAX 最大值
SELECT MAX(article) AS article FROM shop

MAX 子查询, 拥有某个列的最大值的行

    SELECT article, dealer, price
    FROM   shop
    WHERE price=(SELECT MAX(price) FROM shop);

GROUP BY 分组,列的最大值, COUNT(*), HAVING,WITH ROLLUP

SELECT 子句中的列名必须为分组列或列函数
分析特定列数据，组由称为分组列的列组成

每项物品的的最高价格是多少?

    SELECT article, MAX(price) AS price
    FROM   shop
    GROUP BY article

    可以取的字段就是分组的字段
    分组目的是统计某个字段不重复值的总数
    having加过滤条件

    SELECT
        sd_post_date,sd_telphone,COUNT(*) AS count
    FROM
        `source_data`
    GROUP BY
        sd_post_date,sd_telphone
    HAVING
        sd_post_date>‘2006-06-10‘
        AND sd_telphone<>‘0310-7568005‘

MAX 拥有某个字段的组间最大值的行

对每项物品,找出最贵价格的物品的经销商。

    SELECT article, dealer, price
    FROM   shop s1
    WHERE price=(SELECT MAX(s2.price)
                  FROM shop s2
                  WHERE s1.article = s2.article);

UNION ALL, UNION 合并

    select字段的个数需要相同
    UNION在进行表链接后，会筛选掉重复的记录，对结果集进行排序运算，删除重复的记录，再返回结果
    UNION ALL操作，只是简单的将两个结果合并后就返回

    select * from tableA
    union all
    select * from tableB

LEFT JOIN 左连接,DATE_FORMAT, using

    on a.c1 = b.c1 等同于 using(c1)
    LEFT JOIN 是 LEFT OUTER JOIN的简写
    没有 LEFT INNER JOIN
    JOIN 是 INNER JOIN的简写

LEFT OUTER JOIN = LEFT JOIN， INNER JOIN = WHERE = Join
left join,right join速度要远快于inner join和where

LEFT JOIN
左表全部记录, 右表符合条件记录, 右表记录不足的地方均为NUL

    RIGHT JOIN
        与 LEFT JOIN 相反
        连接的记录数与右表的记录数同

    INNER JOIN
        只显示符合条件的记录
        select   *   from   a   inner   join   b   on   a.id=b.id
        等价
        select   *   from   a,b   where   a.id=b.id

select count(*) as num from login_log lo LEFT JOIN customer c ON lo.llog_memberid=c.memberid
where c.membertype=‘0‘ and DATE_FORMAT(lo.time, ‘%Y-%m-%d‘)=‘$date‘

IN ,子查询(出现在FROM里)

    SELECT
        *
    FROM
        keyword_name AS t1 ,
        (SELECT count(*) as count , knt_id
        FROM
            keyword_subject_msg
        WHERE
            knt_name_subject=‘4‘
        GROUP BY
            knt_id
        ) as count_ks
    WHERE
        1
        AND t1.kn_active=‘1‘
        AND t1.kn_id NOT IN (SELECT knt_id FROM keyword_subject_msg AS t2 WHERE t2.knt_name_subject=‘-1‘)
        AND t1.kn_id NOT IN (SELECT knt_id FROM keyword_subject_msg )
        AND count_ks.knt_id = t1.kn_id
        AND count_ks.count between 1 and 5
    LIMIT 0,10

TO_DAYS 日期, NOW()

查询选择所有 date_col 值在最后 30 天内的记录

SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

REPLACE 替换

update [table] set [field]=replace([field],‘[str_search]‘,‘[str_replace]‘);
可以在最后用 where 做条件选择

例如：update keyword_name_msg set knm_content=replace(knm_content,‘-‘,‘_‘);

DATE_FORMAT 日期格式化

DATE_FORMAT(date,format) 依照 format 字符串格式化 date 值

SELECT DATE_FORMAT("2006-8-8 23:23:23",‘%Y-%m-%d‘) where DATE_FORMAT(date,‘%Y-%m-%d‘)=‘$date‘

SELECT tb_id,tb_title,DATE_FORMAT(tb_pub_ymd,‘%Y-%m-%d‘) AS tb_pub_ymd FROM tradeleads_base

DATE 日期格式化, 相当于简化的 DATE_FORMAT

SELECT date( `b_date` ) as date FROM `b`

BETWEEN AND 之间

    SELECT
        t1.*,t2.knt_name AS knt_name
    FROM
        keyword_subject_msg AS t1
    LEFT JOIN
        keyword_name_templet AS t2
    ON
        t1.knt_name_subject=t2.knt_id
    WHERE
        1
        AND t1.knt_name_subject=‘4‘
        AND t1.ksm_active=‘0‘
        AND t1.ksm_date BETWEEN ‘2001-01-1‘ AND ‘2006-11-14 23:59:59‘
    ORDER BY
        ksm_date desc
    LIMIT 0,20

NOW() 当前的日期时间值

SELECT NOW();

DISTINCT 去重

SELECT distinct b_name FROM `b`

DATE_ADD 日期计算

前5天的记录

select count(*) as total from poke_audio_user_profile where lastaccess >= DATE_ADD( now(), INTERVAL -5 day )

插入语句
insert into 表名 set 字段名=""
insert into 表名(‘字段名‘) values(‘‘)

更新语句
update 表名 set 字段名="" where 条件

删除语句
delete from 表名 where 条件

清空表
truncate table table_name

>cd d:/mysql/bin
>mysqldump -h myhost -u root -pmypass dbname > dbname_backup.sql        //备份 --opt 参数作用尽可能最快的导出
#mysqldump --host=192.168.1.1 --user=root -p dbname > / dbname.sql      //unix下备份
>mysqladmin -h myhost -u root -pmypass create dbname                    //还原前先建立一个数据库
>mysql -h myhost -u root -pmypass dbname < dbname_backup.sql            //还原

>mysql -u root -p           //连接数据库
mysql>show databases;       //显示数据库
mysql>use test;             //选择库
mysql>show tables;          //显示数据表
mysql>describe table;       //显示表结构
mysql>select * from 表名;   //显示表中的记录
mysql>show create procedure jos_sp_name;       //显示存储过程内容

mysql>select version();         //查看版本
mysql>delimiter //              //选择分隔符默认为;
mysql>delimiter ;               //恢复使用";"（分号）作为分隔符

mysql>create database 库名;               //建库
mysql>create table 表名 (字段设定列表);   //建表
mysql>drop database 库名;                 //删库
mysql>drop table 表名;                    //删表
mysql>delete from 表名;                   //将表中记录清空

字符设置
SET NAMES ‘UTF8‘
SET CHARACTER SET UTF8

更改密码
d:/mysql/bin/mysqladmin -u 用户名 -p password 新密码

加载服务
d:mysql/bin/ mysqld -install

取消服务
d:mysql/bin/ mysqld -remove

退出MySQL命令：
mysql>exit （回车）

清空表中的数据
truncate pd_tsubject_child

只导出表数据
mysqldump -u root -p pastw pd_tfrom --no-create-info >d:\test.sql

select * from pd_tpapertrunk where autosave =1
select * from pd_tpaperconf where tpapertrunk_id in (select id from pd_tpapertrunk where f_creater=‘shuxuet2‘)
select * from pd_tpapertrunkclass where tpapertrunk_id in (select id from pd_tpapertrunk where f_creater=‘shuxuet2‘)
select * from pd_tpapertrunkdetail where tpapertrunk_id in (select id from pd_tpapertrunk where f_creater=‘shuxuet2‘)
select * from pd_tpapertrunkplan where tpapertrunk_id in (select id from pd_tpapertrunk where f_creater=‘shuxuet2‘)

linux下导表条件数据
mysqldump --where="txueke_id=1 and f_isdelete=0" pd_examiner pd_tfrom -uroot -p9msEWXUd@@ --default-character-set=utf8 > tfrom_xueke_1.sql

将1个表的字段更新到另一个表
update pd_tsubject_child as aa ,pd_tsubject_child11 as bb set aa.f_score=bb.f_score where aa.id=bb.id

select sum(f_score),parent_id from pd_tsubject_child GROUP BY parent_id HAVING COUNT(parent_id) > 1

update pd_tsubject as a ,sumscore as b set a.f_score=b.score where a.id=b.parent_id
update pd_tsubject as a inner join sumscore as b set a.f_score=b.score where a.id=b.parent_id;

explain select * from pd_tsubject as a inner join sumscore as b on a.id=b.parent_id;
建索引
ALTER TABLE sumscore ADD INDEX ( `parent_id` );
查看执行进程
show processlist

本文出自 “我的运维之路” 博客，谢绝转载！

原文：http://linuxpython.blog.51cto.com/10015972/1625406