mysql常用sql

show global variables like "%datadir%";
select @@basedir;
select @@datadir;

/usr/bin/mysql --verbose --help | grep -A 1 'Default options'

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

1 mysql表字段DDL

一、mysql中不同位置增加字段
增加字段（在特定的字段后增加）
alter table 表名 add 需要修改/增加的信息 afer 字段名(在哪个字段后增加)

alter table dept Add column name varchar(20) not null default 0 AFTER sex;

增加字段（把字段添加在第一个位置）
alter table 表名 add 需要修改/增加的信息 first;

alter table dept Add column name varchar(20) not null default 0 first;
alter table dept Add column name VARCHAR(16) COMMENT '用户名' AFTER id;

二、修改字段名
alter table 表名 change 原字段 要修改的字段名 字段类型；

alter table dept change name newname varchar(30);

三、修改字段类型
alter table users modify column username varchar(30);
alter table users modify column username VARCHAR(16) COMMENT '用户名';

四、删除字段：
alter table 表名 drop column 列名

alter table user_movement_log drop column Gatewayid;

五、调整字段顺序：

ALTER TABLE user_movement_log CHANGE GatewayId GatewayId int not null default 0 AFTER RegionID;

2 datetime与vchar比较大小

select * 
from product_info t
where t.create_time
between '2020-05-23 00:00:00' and '2020-05-23 17:39:52';
或
select * 
from product_info t
where t.create_time
between str_to_date('2020-05-23 00:00:00', '%Y-%m-%d %H:%i:%s') 
and str_to_date('2020-05-23 17:39:52', '%Y-%m-%d %H:%i:%s') ;

3 将一个表中的字段的值更新到另一个表

1、使用子查询方式：
update tablea a 
set a.bname = (select b.name from tableb b where b.id=a.bid)
where 各种约束条件;
缺点：数据量大的时候，非常慢；

2、多表合并方式1：
update tablea a, tableb b 
set a.bname=b.name 
where a.bid=b.id;
使用第二种方式比第一种方式要快许多；

当然如果数据量很多的话，可以创建索引提速；


3、多表合并方式2：
update h_log_load a inner join h_log_load b 
on a.load_prov=b.load_prov AND a.load_tab=b.load_tab AND a.load_date=b.load_date 
set a.load_rs=b.load_stat;

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

查看MySQL数据库中每个表占用的空间大小

-- 查询所有库的信息：
select * from information_schema.tables;

-- 查询所有数据的大小 
select concat(round(sum((data_length + index_length)/1024/1024/1024), 2),'GB') as data_sum 
from information_schema.tables;

-- 查看指定数据库实例的大小，比如说数据库 db_test
select concat(round(sum((data_length + index_length)/1024/1024/1024), 2),'GB') as data_sum 
from information_schema.tables 
where table_schema='db_test';

-- 查看指定数据库的表的大小，比如说数据库 db_test中的info表
select concat(round(sum((data_length + index_length)/1024/1024), 2),'MB') as index_sum 
from information_schema.tables 
where table_schema='db_test' and table_name='info';

呵呵哈哈哈

-- 判断表中是否存在这个字段，不存在就新增
if not exists(select * from information_schema.columns t where t.table_schema ='oam' and t.table_name ='v_school_info' and t.column_name='pr2oject_desc')
begin
 select 111;
end


GO

-- 判断表中是否存在这个字段，存在就删除
if exists(select * from sys.columns where name='字段名称' and [object_id]=object_id(N'表名'))
begin
 ALTER TABLE 表名 DROP COLUMN 字段名称;
end
GO