mysql常用命令和语句

接触mysql很多年了，期间记录了常用的一些命令和语句，分享在这里，供大家借鉴。

-- 备份数据库：
mysqldump -uroot -p******  --databases base1 base2> backup.sql
mysqldump -uroot -p****** base > base.sql

mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql

-- 还原数据库 mysql -u root -p****** base< base.sql  -- 列出所有数据库： show databases; --选择数据库 use database; -- 查看数据库中的所有表 show tables; -- 查看建表语句 show create table 表名; -- 建立表 create table tablename (id int, user_mobile varchar(30), user_id varchar(30), user_name varchar(30), flag int, value int, red_use_period int, logdate varchar(30), logtime varchar(30)) default charset=utf8;-- 将数据导入表 load data local infile 'E:/log/query_result_20160620.csv' into table database.table fields terminated by ',' lines terminated by '\n'; 将表中的数据导出到外部文件 

select * from tablename into outfile 'E:/note/file.txt' 
fields terminated by ',' lines terminated by '\n'; 
更新表中的内容 
update touch_red set value=200 where flag=18; update touch_red set logtime=concat(user_id,user_name); update touch_red set logtime ='2015-04-01';  
-- 向表中插入数据 
insert into touch_red values ('667','18701687236','17836549','wangbin2188','18','200','7','2015-04-23','14:06:00'); insert into touch_red (id,user_mobile,value) values ('668','18511371536','200'); 
-- 删除表中的数据 
delete from touch_red where id='' or id='NULL' or id is null; 
-- 在表中增加删除列 
alter table touch_red add column create_time varchar(30); 
alter table touch_red drop column create_time ; 
-- 建立中间表，并导入数据 
INSERT INTO pangolin_order(order_id,task_no,product_type,is_pre_auth,section ) 
SELECT a.key_value,b.task_no ,b.product_type,b.is_pre_auth,a.section FROM (SELECT DISTINCT key_value,section,json FROM order_00.order_detail_0602)a JOIN (SELECT DISTINCT order_no,task_no,product_type,is_pre_auth FROM f_pangolin_ticket.ticket_order)b ON a.key_value=b.order_no WHERE a.key_value!='' ; 
-- 直接从表中抽取数据存放在新表 
create table new_table as select * from old_table where 1=0 ; 
-- 更改表名： 
alter table table_name rename table_new_name; 
-- 导出数据到外文件 
select vid,substr(timesae,1,5),if(substr(timesae,1,5)>=substr(timesae,7,5),'forward','after'),sum(dragc) from basename.tablename
where vid in (25520328,25520762,25535617,25535943,25556874,25557494,25569778,25576255,25581945,25581963,25593953,25593554,25607480,25607719,25627759,25631338,25651653,25652590,25666346,25671050,25677640,25677976) 
group by vid,substr(timesae,1,5),if(substr(timesae,1,5)>=substr(timesae,7,5),'forward','after') 
into outfile 'E:/data.txt' fields terminated by ',' lines terminated by '\n'; 

select * from basename.tablename where date between '2016-09-12' and '2016-09-18' into outfile 'E:/data.txt' fields terminated by ',' lines terminated by '\n'; 
select column_name from basename.tablename where table_name='daily' into outfile 'E:/data1.txt' fields terminated by ',' lines terminated by '\n'; 

-- 只修改列的数据类型的方法: 
alter table student modify column sname varchar(20); 
--同时修改列名和列的数据类型的方法: 
alter table student change column sname stuname varchar(20);

CREATE TABLE `table_name` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`aid` varchar(40) DEFAULT NULL,
`bid` varchar(40) DEFAULT NULL,
`cid` varchar(40) DEFAULT NULL,
`dt` varchar(20) DEFAULT NULL,
`create_time` datetime not NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `dt` (`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

#split功能
select substring_index(name,'-',1),substring_index(name,'-',-1) from channels

 转：https://www.cnblogs.com/benbenzhu/p/5604598.html

/* 创建表格 */mysql> create table enum_set_table(
    -> id int auto_increment primary key,
    -> gender enum('M','F'),
    -> hobby set('music','movie','swimming','footbal')
    -> );
Query OK, 0 rows affected (0.01 sec)
/* 一个enum值，一个set值，二者均使用选项的字符串格式 */
mysql> insert into enum_set_table(id,gender,hobby) values(null,'M','music');
Query OK, 1 row affected (0.01 sec)
/* 一个enum值，多个set值，二者均使用选项的字符串格式 */
mysql> insert into enum_set_table(id,gender,hobby) values(null,'F','music,movie,footbal');
Query OK, 1 row affected (0.00 sec)
/* 一个enum值，一个set值，二者均使用选项的数字格式 */
mysql> insert into enum_set_table(id,gender,hobby) values(null,1,1);
Query OK, 1 row affected (0.00 sec)
/* 一个enum值，多个set值，二者均使用选项的数字格式，其中enum的值 2<=>'F',15=1+2+4+8 <=> 'music,movie,swimming,footbal' */
mysql> insert into enum_set_table(id,gender,hobby) values(null,2,15);
Query OK, 1 row affected (0.00 sec)
/* 一个enum值，多个set值，enum值使用选项的字符串格式，set值使用选项的数字格式，7=1+2+4 <=> 'music,movie,swimming' */
mysql> insert into enum_set_table(id,gender,hobby) values(null,'F',7);
Query OK, 1 row affected (0.01 sec)
/* 查询结果 */
mysql> select * from enum_set_table;
+----+--------+------------------------------+
| id | gender | hobby                        |
+----+--------+------------------------------+
|  1 | M      | music                        |
|  2 | F      | music,movie,footbal          |
|  3 | M      | music                        |
|  4 | F      | music,movie,swimming,footbal |
|  5 | F      | music,movie,swimming         |
+----+--------+------------------------------+
5 rows in set (0.00 sec)

 添加唯一键和外键约束

--单列唯一键约束
alter table tables add unique (tbName);
--联合唯一键约束
alter table columns add unique key(tbName,columnName);
--外键约束
--创建时先父表后子表，删除时先子表后父表
alter table columns add constraint FK_1 foreign key(tbName) references tables (tbName) on delete restrict on update restrict;

 查看和删除唯一键

--查看表的索引
show index from table_name;
--删除唯一键
alter table table_name drop index idx_name;
--创建索引
create unique index idx_name on table_name(column_name);

批量添加字段

--批量添加字段
ALTER TABLE custom
ADD contacts2 VARCHAR(50) NOT NULL DEFAULT '' COMMENT '客户2',
ADD phone2 VARCHAR(20) NOT NULL DEFAULT '' COMMENT '联系人2',
ADD other_contact2 VARCHAR(50) NOT NULL DEFAULT '' COMMENT '联系人2',
ADD contacts_position2 VARCHAR(50) NOT NULL DEFAULT '' COMMENT '职位';

 

--查看表的元数据信息
select TABLE_SCHEMA ,TABLE_NAME ,ENGINE ,TABLE_ROWS ,
CREATE_TIME ,UPDATE_TIME ,ROW_FORMAT , DATA_LENGTH+INDEX_LENGTH  as storage
from information_schema.TABLES t where table_name='branch_meter_read'

--查看表的字段信息
select COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT from information_schema.COLUMNS 
 where table_name='{table}' and table_schema='{schema}'

 

--load指定字段，字段放最后
load data local infile '/root/lng.csv' into table bigdata_gas.huachen_gas_purchase fields terminated by ',' lines terminated by '\n' (dt,plg,lng)

 

--查询元数据
select TABLE_SCHEMA ,TABLE_NAME ,TABLE_COMMENT,ENGINE ,TABLE_ROWS ,
                    CREATE_TIME ,UPDATE_TIME ,ROW_FORMAT , DATA_LENGTH+INDEX_LENGTH  as STORAGE 
                    from information_schema.TABLES t 
                    where table_schema='${db}' and  table_name='${table}'

--表字段
select COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT,COLUMN_KEY,COLUMN_DEFAULT from information_schema.COLUMNS 
where  table_schema='%s' and table_name='%s'

--分区
select distinct partition_expression
                    from information_schema.partitions  where 
                      table_schema = '%s'  
                      and table_name='%s' 

 mysql连接条件

如果一个表的连接条件是id，另外一个表的id是一个id列表，比如长这样"1,2,3"，mysql也能进行关联

--使用find_in_set可以实现类似与in的关系连接
SELECT f.*, v.name FROM user_asian_game_facility f LEFT JOIN user_asian_game_venue v
ON FIND_IN_SET(f.`no`, v.facility_ids)
WHERE v.id = 1;