(1) Mysql ---- ABC 数据库基本操作、数据类型和运算符、函数 《MYSQL 5.5从零开始学》笔记

回顾下 db的相关知识 找了本mysql pdf看了下。

(1)--------------------------------------------------------------------------------------------

查看当前所有的数据库
show databases;

创建数据库
create database db_name;

选择使用数据库
use db_name;

查看数据库的定义
show create database db_name;
show create database db_name\G;

删除数据库
drop database db_name;

查看系统所支持的存储引擎
show engines;
show engines\G;
        support列的值表示某种引擎是否能使用 YES NO DEFAULT 三种值

查看当前系统默认的存储引擎
show variables like 'storage_engine';
       默认的存储引擎支持修改，具体配置在 my.ini中的default-storage-engine=INNODB

(2)数据库基本操作--------------------------------------------------------------------------------

创建表

create table <表名>(
   字段1,数据类型 [列级约束条件][默认值],
   字段2,数据类型 [列级约束条件][默认值],
   ......
   [表级约束条件]
);

创建my2015数据库并使用该库
create database my2015;
use my2015;

建表tb_emp1
mysql> create table tb_emp1(
    -> id int(11),
    -> name varchar(25),
    -> deptId int(11),
    -> salary float
    -> );
Query OK, 0 rows affected (0.25 sec)
-----------------------------------
备注1:

id int(11) 中的 （11）指的是显示宽度，int类型占4个字节。

显示宽度和数据类型的取值范围是无关的。显示宽度只是指明MYSQL最大可能显示的数字个数，数值的位数小于指定的宽度时会有空格填充

如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能显示出来。

例如，向year字段插入一个数值19999，当使用select查询的时候，MYSQL显示的将是完整带有5位数字的19999，而不是4位数字的值

 如果使用了zerofill的时候  将用0补足4位。

如果不指定显示宽度，则MYSQL为每一种类型指定默认的宽度值

tips：显示宽度只用于显示，并不能限制取值范围和占用空间，例如：INT(3)会占用4个字节的存储空间，并且允许的最大值也不会是999，而是INT整型所允许的最大值。

int 内部存储int都是4个字节。括号内的数字是显示宽度。不设置zerofill时，没有任何用处，设置了之后，不足位时会用0补全。

http://zhidao.baidu.com/question/1732734643572093387.html?oldq=1
-----------------------------------

查看当前数据库的所有表
 show tables;


单字段主键：（主键约束）

[1] 定义列时同时指定主键。语法: 字段名 数据类型 primary key [默认值] 
建表tb_emp2
mysql> create table tb_emp2(
    ->  id int(11) primary key,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float
    -> );
Query OK, 0 rows affected (0.09 sec)

[2] 定义完所有的列再指定主键。语法：[constraint <约束名>] primary key [字段名]
建表tb_emp3
mysql> create table tb_emp3(
    -> id int(11),
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    ->  primary key(id)
    -> );
Query OK, 0 rows affected (0.12 sec)

多字段联合主键：（主键约束）

语法：primary key(字段1,字段2,字段3.......)

外键约束  （tb_dept1是父表  tb_emp5子表）

部门表 id  name localtion

mysql> create table tb_dept1(
    -> id int(11) primary key,
    -> name varchar(22),
    -> location varchar (50)
    -> );
Query OK, 0 rows affected (0.12 sec)

建表tb_emp5使用外键约束

mysql> create table tb_emp5(
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
    -> );
Query OK, 0 rows affected (0.14 sec)

执行完毕在表tb_emp5添加了名为fk_emp_dept1的外键约束，外键名称deptId，其依赖于tb_dept1表的主键id

    过程报错：ERROR 1005 (HY000): Can't create table 'my2015.tb_emp5' (errno: 150)

    原因：在建tb_dept1表时 id int(11) primary key 没有指定主键 写成了 id int(11) 导致一直报错。

非空约束  

语法：字段名  数据类型 not null

唯一性约束

要求该列唯一，允许为空。但只能出现一个空值。

语法：字段名  数据类型 unique 

           [constraint <约束名> ] unique(<列名>)

默认约束

语法：字段名  数据类型  default  默认值

设置表属性自动增加

关键字 auto_increment  （一个表只能有一个字段使用auto_increment 关键字，且该字段必须为主键的一部分）

查看表结构

desc tb_name;

查看建表语句

 show create table tb_name;

 show create table tb_name\G;

修改表名

语法： alter tbale <旧表名> rename <新表名>;

alter table tb_emp1 rename tb_emp1new;

修改字段数据类型

语法： alter tbale <表名>  modify <字段名> <数据类型> ;

alter table tb_emp1new   modify  name varchar(100);

修改字段名（change 既可以改名 又可以该数据类型）

语法： alter tbale <表名>  change <旧字段名>  <新字段名> <数据类型> ;

alter table tb_emp1new   change name   namenew varchar(200);

添加字段

语法： alter tbale <表名> add  <新字段名> <数据类型> [约束条件] [first | after 已存在的字段名]

alter table tb_emp1new add sbsb   int(10);

alter table tb_emp1new add sbsb2 int(10) first;

alter table tb_emp1new add sbsb3 int(10) after salary;

删除字段

 语法：alter tbale <表名>  drop <字段名> 

alter table tb_emp1new drop sbsb2 ;

修改字段的排列位置

语法： alter tbale <表名>  modify <字段名1> <数据类型>   first| after <字段名2> ;

更改表的存储引擎

语法： alter tbale <表名>engine = <更改后的引擎名称>

删除表的外键约束

语法： alter tbale <表名> drop foreign key <外键约束名>

删除表(无关联)

语法(一次可以删除多张)：drop tbale  [if exists] 表1，表2，表3.......

删除被其他表关联的主表

删除表中数据

delete from tb_name

truncate  tb_name

备注2:

truncate table 和delete  fromtable  的区别?

1.delete不能使自动编号返回为起始值。但是truncate能使自动增长的列的值返回为默认的值
2.truncate只能一次清空，不能按条件删除。但是delete可以按条件清除部分记录。 
3.truncate清空数据表性能（速度）比delete快。 
4.truncate不会记录到系统日志，不会触发delete触发器。

(3)数据类型和运算符-----------------------------------------------------------------------------------------------------------------------------------

数据类型

1.整型（xxxint）

MySQL数据类型	含义
tinyint(m)	1个字节表示(-128~127)
smallint(m)	2个字节表示(-32768~32767)
mediumint(m)	3个字节表示(-8388608~8388607)
int(m)	4个字节表示(-2147483648~2147483647)
bigint(m)	8个字节表示(+-9.22*10的18次方)

2.浮点型(float和double) 

MySQL数据类型	含义
float(m,d)	单精度浮点型，8位精度(4字节),m是十进制数字的总个数，d是小数点后面的数字个数
double(m,d)	双精度浮点型，16位精度(8字节)

参数m只影响显示效果，不影响精度，d却不同，会影响到精度。

3.定点数(decimal) 
decimal(m,d) 定点类型浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。参数m是定点类型数字的最大个数（精度），范围为0~65，d小数点右侧数字的个数，范围为0~30，但不得超过m。对定点数的计算能精确到65位数字。 

4.字符串(char,varchar,xxxtext) 

MySQL数据类型	含义
char(n)	固定长度的字符串，最多255个字符
varchar(n)	固定长度的字符串，最多65535个字符
tinytext	可变长度字符串，最多255个字符
text	可变长度字符串，最多65535个字符
mediumtext	可变长度字符串，最多2的24次方-1个字符
longtext	可变长度字符串，最多2的32次方-1个字符

5.二进制数据(xxxBlob) 
XXXBLOB和xxxtext是对应的，不过存储方式不同，xxxTEXT是以文本方式存储的，如果存储英文的话区分大小写，而xxxBlob是以二进制方式存储的，不区分大小写。xxxBlob存储的数据只能整体读出。xxxTEXT可以指定字符集，xxxblob不用指定字符集。 

6.日期时间类型(year,date,time,datetime,timestamp) 

MySQL数据类型	含义
date	日期'2008-12-2'
time	时间'12:25:36'
datetime	日期时间'2008-12-2 22:06:44'
timestamp	不固定

 year                     yyyy

7.数据类型的属性 

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT xxx	默认值，如果插入记录的时候没有指定值，将取这个默认值
PRIMARY KEY	主键
AUTO_INCREMENT	递增，如果插入记录的时候没有指定值，则在上一条记录的值上加1，仅适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

备注3:

mysql中char和varchar有什么区别?

char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：
char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）。

在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）．

(4)函数-----------------------------------------------------------------------------------------------------------------------------------

数学函数

-----------------------

abs(x)  返回数值x的绝对值

select abs(22),abs(-11),abs(-9);

pi()  返回圆周率的π的值    3.141593默认显示6位小数

select pi();

sqrt(x)  求x的平方根函数

select sqrt(9);

mod(x,y) 求余函数

select mod(9,2);

ceil(x)  ceiling(x)  floor(x)  获取整数 的函数

       ceil(x) 等效 ceiling(x) 返回不小于x的最小整数值 返回值转化为bigint  

       floor(x)      返回不大于x的最大整数值 

select ceil(-3.35),ceil(3.35);

select ceiling(-3.35),ceiling(3.35);

select floor(-3.35),floor(3.35);

rand() 和 rand(x)  获取随机数

       rand()  返回[0,1]中的一个数值

       rand(x) x值相同则返回的数值相同，同一次。

select rand(),rand(),rand();

select rand(10),rand(10),rand(11);

round(x)   round(x,y)  truncate(x,y) 四舍五入函数

 select round(-1.14),round(-1.67),round(1.14),round(1.66);

sign(x) 符号函数   

        x 负 0  正 返回 -1 0 1

select sign(22),sign(0),sign(-9);

pow(x,y)  power(x,y) exp(x) 幂运算函数

     pow(x,y)  power(x,y)返回x的y次方

     exp(x)返回e的x次方

log(x)  log10(x) 对数运算函数

     log(x)     返回x基于e的自然对数

     log10(x) 返回x基于10的自然对数

三角函数略去吧......

----------------------------------------------

字符串函数

char_length(str) 字符串长度函数

select char_length('date');

concat(str1,str2....)   concat_ws(x,str1,str2....)  合并字符串

           concat(str1,str2....)           str中有一个为null则最终返回null 

           concat_ws(x,str1,str2....)   x为分隔符 ，x为null则最终返回null ，str中有null值则忽略掉

insert(s1,x,len,s2)替换字符串函数

lower(str)  lcase(str)  小写

upper(str) ucase(str) 大写

left(str,n)   right(str,n) 获取指定长度的字符串

select  left('football',5);

select  right('football',4);

lapd(s1,len,s2) rpad(s1,len,s2) 填充字符串函数

ltrim(str)  rtrim(str) trim(str) 去除空格函数

ltrim(str) 去掉左侧空格 

rtrim(str) 去掉右侧空格

trim(str) 删除两侧空格

trim(s1 from s ) 删除指定字符串函数

repeat(str,n)重复生成字符串函数

space(n) 返回由n个空格组成的字符串

replace(s,s1,s2) 替换函数

strcmp(s1,s2)比较字符串大小函数

substring(s,nlen)    mid(s,n,len) 获取子串的函数     

匹配子串开始位置的函数

locate(str1,str)     poisition(str1  in  str)     instr(str ,str1) 三个函数等效(  参数位置 形式不一样 )

字符串逆序函数 rerverse(str)

返回指定位置的字符串elt(n,str1,str2........)

返回指定字符串 位置的函数 field(s,s1,s2....)

返回子串位置的函数 find_in_set(s1,s2)       s2   “xx ， xx，xx，xx ”

选取字符串的函数 make_set(x,s1,s2......)

----------------------------------------------

时间函数

1获取当前日期函数 curdate() 等效于 current_date()    
curdate() 等效于 current_date() 将当前日期按照'YYYY-MM-DD'或YYYYMMDD
select curdate(),current_date(),curdate()+0; (curdate()+0将当前日期转换成数值型)
2015-01-09 | 2015-01-09     |    20150109 


2获取当前时间的函数 curtime() 等效于 current_time()
curtime() 等效于 current_time() 将当前时间按照'HH:MM:SS'或HHMMSS
select curtime(),current_time(),curtime()+0;
15:35:03  | 15:35:03       | 153503.000000


3获取当前日期和时间函数  四个函数作用相同
current_timestamp()
localtime()
now()
sysdate()


4unix时间戳函数  unix_timestamp()
select unix_timestamp(), unix_timestamp(now()),now();
1420789245 |   1420789245 | 2015-01-09 15:40:45


5将unix时间戳转换成普通格式时间 from_unixtime()
select from unixtime('1311476091');
2011-07-24 10:54:51


6utc日期函数和utc时间函数
utc_date();
utc_time();


7获取月份函数
month(date)  返回date对应的月份  1~12
monthname(date) 返回月份的名称 


8获取星期的函数
dayname(d)
dayofweek(d)  1周日  2周一 .....
weekday(d)    0周一  1周二 .....


9获取星期数的函数
week(d)          
weekofyear(d)     


10获取天数函数
dayofyear(d)
dayofmonth(d)


11获取年度、季度、时、分、秒函数
year(d)
quarter(d)
hour(t)
minute(t)
second(t)

12获取日期的指定值extract(type from date)

13 time_to_sec(time)         sec_to_time(seconds)

----------------------------------------------------------

补充：              

mysql 将本地文件（非sql文件，hdfs通过hive查询之后结果导入到本地的文件）导入到数据库对应的表中。

data.txt 文件中 只有一行数据。2015-01-20 0   两个数据使用 \t 作为间隔

[hadoop2@nn1-hadoop2 uv]$ cat data.txt 
2015-01-20 0

在100.240.151.4这台机器执行命令

 mysql -h100.240.8.200 -uhive -p123456 -e "load data local infile '/mnt/resource/dist/uv/data.txt' into table vdnaccesslog.uv_info character set utf8 (accessymd,totaluv);"

将data.txt文件中的数据导入到100.240.8.200这台机器mysql对应的vdnaccesslog.uv_info库表中。

(1)--------------------------------------------------------------------------------------------