mysql笔记

注：最近用到了MySQL，在这里复习一下~，Linux环境。
登录MySQL服务器

mysql -h127.0.0.1 -uroot -p[pwd -Ddb_name]  -- 这样会直接进入该db_name库下

1.创建数据库

create database db_name;

2.创建用户及赋权

create user user_name@localhost identified by 'pwd';
grant select on db_name to user_name@localhost;
flush privileges;

3.创建(分区)表

create table t_name (
id bigint not null auto_increment comment '自增主键'
,name varchar(255) comment '名称'
,dt int comment '分区'
primary key(id,dt)
) engine=InnoDB default charset=utf8 collate=utf8_general_ci 
auto_increment=1 
comment='表名'
-- 分区表
partition by range(dt)(
	partition p0 values less than (2010) engine=InnoDB
	,partition p1 values less than (2011) engine=InnoDB
);

4.csv/excel/txt数据导入导出

-- ftp上传数据文件
wget ftp://127.0.0.1/data.csv  --ftp-user=user --ftp-password=pwd
--编码转换(含有中文字符的文件默认编码可能是gb18030，可以本地文本编译器转换编码，如果文件不大的话)
icon -f gb18030 -t utf-8 data.csv > new_data.csv
--行尾存在^M(win下换行符为\r\n,二Linux下换行符为\n,所以上传到Linux下文件行尾会存在^M)
vi data.csv  -- 注：vi下^M用ctrl+v,ctrl+m
%s/^M//gn  -- 统计^M次数
%s/^M//g  -- 处理^M

-- 导入csv/txt(\t分割)
load data local infile '/user_name/data.csv(data.txt)' into table t_name fields terminated by '\t' lines terminated by '\n' ignore 1 lines;  -- 忽略第一行（一般第一行为列名）

-- 导出数据文件
mysql -h127.0.0.1 -uroot -ppassword -Ddb_name -e'select * from t_name;' > ./data.csv

5.执行.sql

-- mysql命令行
source sql.sql

-- linux命令行
mysql -h127.0.0.1 -uroot -ppassword -Ddb_name -e'select * from t_name;' < ./sql.sql 

-- linux后台执行
nohup mysql -h127.0.0.1 -uroot -ppassword -Ddb_name -e'select * from t_name;' < ./sql.sql &
-- 查看进程
top -H -p id

6.日期格式

-- 日期符号
%Y  -- 年 4位
%y  -- 年 2位
%m  -- 月 （00-12）
%c  -- 月 （00-12）
%d  -- 天 （00-31）
%e  -- 天 （0-31）
%H  -- 小时 （00-23）
%k  -- 小时 （0-23）
%i  -- 分钟 （00-59）
%s  -- 秒 （00-59）

select str_to_date(str, '%y-%m-%d %H:%i:%s') from dual;

7.collate关键字

collate，校对集，用来排序的规则。对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个collate类型来告知mysql如何对该列进行排序和比较。
简而言之，collate会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响**DISTINCT**、**GROUP BY**、**HAVING**语句的查询结果。
另外，mysql建索引的时候，如果索引列是字符类型，也会影响索引创建。
总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。

collate通常是和数据编码（CHARSET）相关的，一般来说每种CHARSET都有多种它所支持的collate，并且每种CHARSET都指定一种collate为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci，GBK编码的默认COLLATE为gbk_chinese_ci，utf8编码的默认值为utf8_general_ci。
其中，_ci，Case Insensitive的缩写，即大小写无关；_cs，Case Sensitive，即大小写敏感；_bin，二进制串

collate设置级别：
设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。
**实例级别**：mysql配置文件或启动指令中的collation_connection系统变量；
**库级别**：创建db时指定collate。例：create database db_name default charset=utf8 collate=utf8_general_ci;
	如果库级别没有设置CHARSET和COLLATE，则库级别默认的CHARSET和COLLATE使用实例级别的设置。
**表级别**：创建table时指定collate。例：create table t_name () engine=InnoDB default charset=utf8 collate=utf8_general_ci comment'';
	如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE。
**列级别**：创建table声明column时指定collate。例：create table t_table (id varchar(255) chartset=utf8 collate=utf8_general_ci);
	如果列级别没有设置CHARSET和COLLATE，则表级别会继承表级别的CHARSET与COLLATE。
**SQL指定**：SQL语句声明collate。例：select id collate utf8_general_ci from t_table;

collate设置级别优先级：
SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别