二、大数据之MYSQL基础

二、MYSQL

1、Mysql的分类

数据查询语言(DQL)	select
数据操纵语言(DML)	insert,delete,update
数据定义语言(DDL)	create,drop,alter
事务控制语言(TCL)	commit,rollback
数据控制语言(DCL)	grank,revoke

2、Mysql的语法

1、创建数据库和表

create database 库名
create table 表名

2、查询数据

select 字段 from 表名

3、删除

drop database 库名
drop table 表名
delete from table_name [wherer Clause]

4、插入数据

insert into 表名 (字段1，字段2，……) values(value1,value2,……)

5、更新数据

update 表名 set field1=new-value1,field2=new-value2
[where Clause]

6、修改字段和数据

alter table 表名 drop 字段  #删除字段
alter table 表名 add  字段 int #添加字段
alter table 表名 change 旧字段 新字段 数据类型 #修改字段的名称及类型
alter table 旧表名 rename to 新表名

3、Mysql的表操作

1、inner join

SELECT 
a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a 
INNER JOIN
tcount_tbl b
ON a.runoob_author = b.runoob_author;

可以等价于：

SELECT
a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a, tcount_tbl b
WHERE a.runoob_author = b.runoob_author;

2、left join

SELECT 
a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a 
LEFT JOIN tcount_tbl b
ON a.runoob_author = b.runoob_author;

3、right join

 SELECT
 a.runoob_id, a.runoob_author, b.runoob_count
 FROM runoob_tbl a
 RIGHT JOIN tcount_tbl b
 ON a.runoob_author = b.runoob_author;

4.union all

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
# 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）

4、Mysql的表字段操作

1、分组

使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name

另外，WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

在统计个数时，可以使用coalesce语法，来设置一个值代替NULL

select coalesce(a,b,c);

参数说明：如果anull,则选择b；如果bnull,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

2、排序

使用 ORDER BY 子句将查询数据排序后再返回数据(DESC为降序，ASC为升序)

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

3、Mysql数据的导入和导出

数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:

mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';

此外，也可以来设置参数，指定输出的格式，例如导出为CSV格式

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

还可以指定生成文件的数据之间隔开符号，大多数人都指定为逗号和空格。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

使用 LOAD DATA 导入数据，以读取dump.txt文件为例

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

另外，也可以使用命令 mysqlimport 进行数据的导入

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
password *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

$ mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt
password *****

5、Mysql的常用函数

函数	描述
CONCAT_WS(x, s1,s2…sn)	同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x，x 可以是分隔符
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串
RAND()	返回 0 到 1 的随机数
ADDDATE(d,n)	计算起始日期 d 加上 n 天的日期
ADDTIME(t,n)	n 是一个时间表达式，时间 t 加上时间表达式 n
CURDATE()	返回当前日期
CURRENT_TIME	返回当前时间
CURRENT_TIMESTAMP()	返回当前日期和时间
CAST(x AS type)	转换数据类型
CONNECTION_ID()	返回唯一的连接 ID
IF(expr,v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2
IFNULL(,v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2