[大数据]SQL常用方法

1. 查看数据库版本

select version();

2. 查看当前用户连接数

select connection_id();

3. 查看当前用户连接信息

show processlist;

4. 返回当前被Mysql服务器验证的用户名与主机组合

select user();

5. 改变数据类型

select cast(100 as char(2));

6. 窗口函数

-- 先建表
mysql> create table branch
    -> (
    -> name char(255) not null,
    -> brcount int(11) not null
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

-- 插入数据
mysql> insert into branch (name,brcount) values
    -> ('jhon',99),
    -> ('ammy',20),
    -> ('lufy',3),
    -> ('peace',2),
    -> ('python',5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

-- 查看
mysql> select * from branch;
+--------+---------+
| name   | brcount |
+--------+---------+
| jhon   |      99 |
| ammy   |      20 |
| lufy   |       3 |
| peace  |       2 |
| python |       5 |
+--------+---------+
5 rows in set (0.00 sec)

-- 利用窗口函数对 brcount 从大到小排名
mysql> select * ,rank() over w1 as `rank` from branch
    -> window w1 as (order by brcount);
+--------+---------+------+
| name   | brcount | rank |
+--------+---------+------+
| peace  |       2 |    1 |
| lufy   |       3 |    2 |
| python |       5 |    3 |
| ammy   |      20 |    4 |
| jhon   |      99 |    5 |
+--------+---------+------+
5 rows in set (0.00 sec)

7. distinct
   消除重复值

select distinct <字段名> from 表名;