mysql 基础操作

远程连接MySQL

mysql -u[user] -p[pwd] -h [hostip] -P [port]

展示数据库

show databases;

MySQL [information_schema]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mr                 |
| mysql              |
| performance_schema |
| pm_gsm             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

选择数据库

use [database name]

展示当前数据表

show tables;

查看表的详细信息

先进入information_schema，再查询表大小

MySQL [(none)]> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [information_schema]> select table_name,(data_length+index_length)/1024/1024/1024 as datasize,
    -> table_rows from tables where table_schema='mr' limit 5;
+-----------------------------+----------------+------------+
| TABLE_NAME                  | datasize       | TABLE_ROWS |
+-----------------------------+----------------+------------+
| a_pm_kpi_mscserver          | 0.000030517578 |          0 |
| area_scene_indicator_15_agg | 2.191421508789 |    2457548 |
| area_scene_indicator_60_agg | 0.578140258789 |     553143 |
| bulletin_type               | 0.000015258789 |          2 |
| cell_threshold_config       | 0.000015258789 |          6 |
+-----------------------------+----------------+------------+
5 rows in set (0.01 sec)

存储过程

MySQL 存储过程 declare语句
• Declare语句通常用来声明本地变量、游标、条件或者handler
• Declare语句只允许出现在begin … end语句中而且必须出现在第一行
• Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler

查询数据库中的存储过程和函数

select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE'   //存储过程
select * from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' and name='xx'
select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION'   //函数
show procedure status; //存储过程
show function status;     //函数

查看存储过程或函数的创建代码

show create procedure proc_name;
show create function func_name;

查看视图

SELECT * from information_schema.VIEWS   //视图
SELECT * from information_schema.TABLES   //表

查看触发器

SHOW TRIGGERS [FROM db_name] [LIKE expr]
SELECT * FROM triggers T WHERE trigger_name=”mytrigger”

存储示例

delimiter ;;
drop procedure if exists sp_schedule_quarter;
create procedure sp_schedule_quarter()
begin
  declare quarter_now datetime;
  declare current datetime default now();

  SET quarter_now =(current - INTERVAL (TIME_TO_SEC(current) MOD 900) SECOND);

  call sp_all_indicator_quarter(quarter_now - interval 30 minute);
  call sp_all_indicator_quarter(quarter_now - interval 45 minute);
 
end;;
delimiter ;

SET quarter_now =(current - INTERVAL (TIME_TO_SEC(current) MOD 900) SECOND);
将当前时间转换成整点，去掉秒的尾数。

MySQL [mr]> select now()- INTERVAL (TIME_TO_SEC(now()) MOD 900) SECOND;
+-----------------------------------------------------+
| now()- INTERVAL (TIME_TO_SEC(now()) MOD 900) SECOND |
+-----------------------------------------------------+
| 2021-01-30 19:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

MySQL [mr]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-01-30 19:13:29 |
+---------------------+
1 row in set (0.00 sec)

新增字段语法

alter table 表名 add 字段名称 类型（大小）默认值位置；
alter table user add test varchar(20) default null after name;
位置：after、first(第一个位置，first后边不需要跟在谁的后面)

如果没有写就跟在最后面，位置很重要，用到replace into 等插入语句时，字段和顺序相关。

删除字段语法：
alter table 表名 drop 字段名
alter table user drop test;