回顾下 db的相关知识找了本mysql pdf看了下。 (1)-------------------------------------------------------------------------------------------- 查看当前所有的数据库 show databases; 创建数据库 create database db_name; 选择使用数据库 use db_name

回顾下 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 first| after ;

更改表的存储引擎

语法： 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有什么区别?

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

登入後複製

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

登入後複製

(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-200 两个数据使用 \t 作为间隔

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

在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) Mysql