- 查看数据库版本
select version();
- 查看当前用户连接数
select connection_id();
- 查看当前用户连接信息
show processlist;
含义 | 用途 |
---|---|
Id | connection_id |
User | 当前用户 |
Host | 显示这个语句从那个ip的哪个端口发出,用于追踪问题语句用户 |
db | 显示目前连接哪个数据库 |
Command | 显示当前连接执行命令,一般取值为休眠(Sleep),查询(Query),连接 (Connect) |
Time | 状态持续时间,单位是秒 |
State | 显示当前使用后连接的SQL语句状态 |
Info | 执行的SQL语句 |
- 返回当前被Mysql服务器验证的用户名与主机组合
select user();
- 改变数据类型
select cast(100 as char(2));
- 窗口函数
-- 先建表
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)
- distinct
消除重复值
select distinct <字段名> from 表名;
8.查看当前使用库
select database(),schema();