MySQL数据导出、删除、表重命名、时间转化及级联查询

本文链接：https://ligang.blog.csdn.net/article/details/43613393

1. 远程登录

mysql -h host -u user -p

2. 数据导出

#导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
#导出数据库中某个表：
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
#导出一个数据库结构(-d 没有数据)：
mysqldump -u 用户名 -p -d  数据库名 > 导出的文件名
	
mysqldump -u root -p test > test.sql
mysqldump -u root -p test users> test_users.sql
mysqldump -u root -p -d test > test.sql
	
create database test2 DEFAULT CHARACTER SET UTF8
source /tmp/test.sql

3. 符合条件的记录导出到指定文件中

select * from site where uid = 0 into outfile '/tmp/ligang.txt'

#注意：导出到/data下无权限，可以导出到tmp下，tmp为数据库所在服务器的tmp目录

4. 删除表或数据

#要清空表中的所有记录
DELETE FROM 表名;
TRUNCATE TABLE 表名;
#如果要删除表中的部分记录，只能使用DELETE语句：
DELETE FROM 表名 WHERE ...;

#删除表：
DROP TABLE tbl_name;
DROP TABLE IF EXISTS tbl_name;

5. 将旧表中的数据灌入新表

INSERT INTO 新表 SELECT * FROM 旧表;

6. 含有主键等信息的完整表结构

CREATE table 新表名 LIKE book;
CREATE TABLE members_tmp LIKE members
RENAME TABLE members TO members_bak,members_tmp TO members; #RENAME期间表是被锁死的

7. 重命名表

ALTER TABLE t1 RENAME t2;
#改变列a，从INTEGER改为TINYINT NOT NULL(名字一样)，并且改变列b，从CHAR(10)改为CHAR(20)，同时重命名它，从b改为c:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
#增加一个新TIMESTAMP列，名为d：
ALTER TABLE t2 ADD d TIMESTAMP;
#在列d上增加一个索引，并且使列a为主键：
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
#删除列c：
ALTER TABLE t2 DROP COLUMN c;
#增加一个新的AUTO_INCREMENT整数列，命名为c：
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c);
#注意，我们索引了c，因为AUTO_INCREMENT柱必须被索引，并且另外我们声明c为NOT NULL，因为索引了的列不能是NULL。

8. 插入or替换

首先判断数据是否存在——>如果不存在，则插入 ——>如果已存在，则更新一条记录必有唯一键，如果insert的时候，唯一键冲突了，则执行update操作【insert会执行一条语句，update会执行两条语句】

#(1)关键字：REPLACE INTO
#其中id为主键，如果001不存在则插入、存在则替换
REPLACE INTO student(id,NAME,age,address) VALUES('001','ligang','24','山东省');
#其中id为主键，如果002不存在则插入、存在则替换
REPLACE INTO student SET id='002',NAME='ligang',age='24',address='山东省';
#(2)关键字：INSERT ON DUPLICATE KEY UPDATE
#其中id为主键，如果003不存在则插入、存在则替换
INSERT INTO student(id,NAME,age,address) VALUES('003','ligang','24','山东省') ON DUPLICATE KEY UPDATE NAME='leegang';

9. 时间操作

#将时间转化成时间戳格式
select unix_timestamp(now());
#将时间戳转化成时间格式：
select from_unixtime(1251884321);
#mysql中使用convert_tz函数可以实现时区转换查询，示例如下：
select convert_tz('2014-02-14 00:00:00','+08:00','+09:00');
#其中，参数的意义：CONVERT_TZ (datetime, from_tz, to_tz);

10.级联查询

select TABLE1.*, TABLE2.* from TABLE1 left join TABLE2 on TABLE1.id = TABLE2.id where TABLE1.type = 1;
select ... from TABLE1 left join TABLE1 on 条件1 where 条件2; 
#on后面条件(条件1)：只针对left join后面的表(TABLE2)其作用，对TABLE1不起作用。 
#where后面条件(条件2)：对TABLE1和TABLE2都其作用。

select TABLE1.*, TABLE2.* from TABLE1 inner join TABLE2 on TABLE1.id = TABLE2.id where TABLE1.type = 1;
#on后面的限制条件：全部起作用，与where的执行结果是一样的。
#另外，where语句与inner join确实能得到相同的结果，只是效率不同。