mysql 常用的sql

vim

-- DBeaver Add vrapper repository http://vrapper.sourceforge.net/update-site/stable

mysql

批量删除sql

SELECT CONCAT( 'drop table we7.', table_name, ';' ) FROM information_schema.tables WHERE table_schema = 'we7' AND table_name LIKE 'hjmall%';

在linux模式下不用输入密码直接 -p 有时候-p都不用

mysqldump -h<ip> -u<username> -p<pass> <database> > name.sql
-- 导入sql source name.sql

创建数据库

CREATE DATABASE xcx DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

mysql查看表结构命令，如下:

desc 表名;
show columns from 表名;
describe 表名;
show create table 表名;

use information_schema
select * from columns where table_name='表名';

顺便记下：

show databases;
use 数据库名;
show tables;

创建数据库
create database abc;

当前日期

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

mysql limit 优化

SELECT 
  a.tid AS _id,
  a.tid,
  a.fid,
  a.author,
  a.authorid,
  a.subject,
  b.content,
  a.postdate,
  a.ifcheck,
  a.hits,
  a.replies 
FROM
  (SELECT 
    * 
  FROM
    pw_threads 
  WHERE pw_threads.tid > 0
  ORDER BY pw_threads.tid 
  LIMIT 100) AS a 
  LEFT JOIN pw_tmsgs b 
    ON a.`tid` = b.`tid` 
ORDER BY a.tid DESC 

msyql 数据结构恢复

mysqlfrm --diagnostic F:/admin/

mysql 导出指定的表

mysqldump -t database -u username -ppassword --tables table_name1 table_name2 table_name3 >D:\db_script.sql