各种数据库方言 | 郭飞的笔记

SQL 系列文章

🔥【SQL】SELECT专题，主要内容是 SELECT 语句
【SQL】通用语法，主要内容是 CREATE、ALTER、INSERT 等通用的 SQL 语法
各种数据库方言，各种数据库的相关命令，如 HIVE、MySQL、SQL Server
【python】sqlAlchemy

MySQL

登录到MySQL

mysql -h 主机名 -u 用户名 -p

-h : 指定客户端所要登录的MySQL主机名, 登录当前机器该参数可以省略;
-u : 用户名;
-p : 密码, 如果密码为空, 可以忽略此选项。

修改 root 用户密码

mysqladmin -u root -p password 新密码

视图

-- 1、创建视图

CREATE VIEW view_name AS
SELECT id,name
FROM table_name;

-- 2、查询视图
SELECT * FROM view_name;

-- 3、删除视图
DROP VIEW view_name;

-- 4、修改视图
ALTER VIEW view_name as select 语句;

-- 5、插入数据
INSERT INTO view_name (属性1,属性2) values (。。。)

-- 6、更改数据
UPDATE view_name
SET 属性1=值1
WHERE ...

对 VIEW 的插入/更改：

更改的是 table 中的数据
只有简单 VIEW 才允许插入/更改。简单 VIEW：单表、没有 GROUP BY、DISTINCT、子查询等，而且必须包含主键。否则数据库不知道如何更改对应的 Table

索引

1、创建表时创建索引

create table table_name(
属性名 数据类型
属性名 数据类型
...
属性名 数据类型
index [索引名](属性名1)
)；

例子：

create table t_dept(
deptno int,
dname varchar(20),
loc varchar(40),
index index_deptno(deptno)
);

检验索引是否创建成功

show create table t_dept \G

检验索引是否被启用

explain
select * from t_dept where deptno=1;

2、已经存在的表中添加索引

create index 索引名
    on 表名(属性名【(长度)】)【(ASC|DESC)】;

3、用alter

alter talbe table_name
    ADD index 索引名(属性名【(长度)】【(ASC|DESC】);

4、以上 index 可以更改为 key，效果相同以上index可以更改为unique index，或者unique key，变成不允许重复的索引

5、以上 index 可以更改为 full text index|key

6、多列索引

末尾换成：

索引名(属性名1【(长度)】【(ASC|DESC】,属性名2,属性名3);

7、删除索引

drop index index_name on table_name;

SQL Server

系统数据库分为这几个：

Master：存储元数据，例如别的数据库的 table 信息
Model：模版数据库，新的数据库以此为模版创建
Msdb：代理服务数据库，提供一个存储空间
Tempdb：临时数据库，所有的临时表、存储过程、临时操作提供的存储空间。断开连接时自动删除。

Hive-SQL

建表

建一个普通表

DROP TABLE IF EXISTS app.app_example;
CREATE TABLE app.app_example(
id int comment 'id对应的解释',
col1 int comment '解释1',
col2 int comment '解释2'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

建一个分区表

DROP TABLE IF EXISTS app.app_example;
CREATE TABLE app.app_example(
id int comment 'id对应的解释',
col1 int comment '解释1',
col2 int comment '解释2'
)
partitioned by (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
location '/user/guofei/app_example'
stored as orc
TBLPROPERTIES ('author'='guofei');

插入数据

从本地数据插入

load data local inpath 'file_path' into table tbname;
load data local inpath 'file_path' overwrite into table tbname; -- overwrite抹除原数据
LOAD DATA LOCAL INPATH  'file_path' INTO TABLE tbname PARTITION (country='US', state='CA') -- 插入分区表，增量添加，不是覆盖
LOAD DATA LOCAL INPATH  'file_path' OVERWIRTE INTO TABLE tbname PARTITION (country='US', state='CA') -- 插入分区表，带overwirte表示覆盖

从查询插入数据

-- 新建一个表，并以overwrite的方式写入数据
CREATE TABLE tb2 AS SELECT * from tablename1; 
-- 覆盖式插入数据
insert overwrite table tb2 select * from tb1; 
-- 新增式插入数据
INSERT INTO TABLE tablename2 SELECT * FROM tablename1；
-- 静态方式插入分区表：
insert overwrite table tmp.tmp_test
partition (country='US',state='OR')
select names from tbname1;
-- 动态方式插入到分区表：
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE app.app_example PARTITION(dt)
SELECT col1,col2,sysdate(-1) as dt
FROM
app.example_1
-- 动态方式必须把分区key放在最后，优点是可以同时向多个分区插入数据
-- overwrite表示覆盖插入，去掉overwrite表示增量添加

例子：

DROP TABLE IF EXISTS app.app_example;
CREATE TABLE app.app_example AS
SELECT * FROM app.example_1;

删除数据

ALTER TABLE tablename DROP IF EXISTS PARTITION(year = 2015, month = 10, day = 1);


-- 清除数据
TRUNCATE TABLE tablename;

修改列

Hive修改表名，列名，列注释，表注释，增加列，调整列顺序，属性名等操作

-- 重命名表名
ALTER TABLE table_name RENAME TO new_name


ALTER TABLE table_name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE table_name DROP [COLUMN] column_name
ALTER TABLE table_name CHANGE column_name new_name new_type
ALTER TABLE table_name REPLACE COLUMNS (col_spec[, col_spec ...])

改变列名/类型/位置/注释

ALTER TABLE table_name
CHANGE COLUMN
col_old_name col_new_name column_type
[COMMENT col_conmment]
[FIRST|AFTER column_name];

用来修改表的列名、数据类型，列注释和列所在的位置 FIRST将列放在第一列，AFTER col_name将列放在col_name后面一列，

ADD COLUMNS允许用户在当前列的末尾，分区列之前添加新的列

ALTER TABLE table_name ADD
COLUMNS (col_name data_type [CONMMENT col_comment], ...);

其它操作

show databases
use database_name

hive模糊搜索表

show tables
show tables like '*name*';
show table extended like 'test_partition';
-- 加上 extended 后，会额外显示以下信息：
-- location，format，columns，partition，文件数，文件大小，最后一次编辑时间和访问时间
SHOW TBLPROPERTIES [table_name] -- 显示指定表名的TBLPROPERTIES信息（好像show create table [table_name]更全）

查看表结构信息

desc formatted table_name;
desc table_name;

查看分区信息
```
show partitions table_name;
```

参考资料

《mySQL从入门到精通》
https://wenku.baidu.com/view/f7f6e6dcd1f34693daef3e8b.html
http://www.w3school.com.cn/sql/
https://blog.csdn.net/helloxiaozhe/article/details/80749094
http://blog.csdn.net/mulangren1988/article/details/77509195