数据库 SQL 脚本相关

1、字段增删改

新增字段

ALTER TABLE [表名] ADD [字段名]
varchar(100) NULL COMMENT '注释说明' AFTER [指定字段名位于其后];

修改字段

ALTER TABLE [表名] CHANGE [原字段名] [新字段名]
varchar(100) NULL COMMENT '注释说明' AFTER [指定字段名位于其后];

删除字段

ALTER TABLE [表名] DROP [字段名];

2、查看指定表的字段说明、类型、约束

select
  column_name '字段名',
  column_comment '字段说明',
  column_type '字段类型',
  column_key '约束'
from information_schema.columns
where table_schema = '数据库名'
and table_name = '表名';

3、容量查询

-- 查看指定数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema = '数据库名'
order by table_rows desc,data_length desc, index_length desc;

-- 查看指定数据库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema = '数据库名';

-- 查看所有数据库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

-- 查看所有数据库各表容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

4、分组查询取最大日期的记录

select [分组字段],[日期],[字段1],[字段2]
from [表名] t
where t.[日期] =
(
	select max([日期])
	from [表名]
	where [分组字段] = t.[分组字段]
)
group by t.[分组字段];

5、查询当天、最近一天、最近一周、本月、上一月数据

-- 今天
select * from [表名]
where to_days([时间字段名]) = to_days(now());

-- 昨天
select * from [表名]
where to_days(now()) - to_days([时间字段名]) <= 1;

-- 7天
select * from [表名]
where date_sub(curdate(), interval 7 day) <= date([时间字段名]);

-- 近30天
select * from [表名]
where date_sub(curdate(), interval 30 day) <= date([时间字段名]);

-- 本月
select * from [表名]
where date_format([时间字段名], '%Y%m' ) = date_format(curdate(), '%Y%m');

-- 上一月
select * from [表名] where
period_diff(date_format(now(), '%Y%m'), date_format([时间字段名], '%Y%m')) = 1;

6、索引创建

创建普通索引

CREATE INDEX index_name ON table_name (column1,column2,column3);

修改表结构（添加索引）

ALTER TABLE table_name ADD INDEX index_name (column1,column2,column3);

创建表时指定

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  column3 data_type,
  INDEX index_name (column1,column2,column3)
);

创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column1,column2,column3);

修改表结构（添加索引）

ALTER table mytable
ADD CONSTRAINT unique_constraint_name UNIQUE (column1,column2,column3);

创建表时指定

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  column3 data_type,
  CONSTRAINT index_name UNIQUE (column1,column2,column3)
);

删除索引

DROP INDEX index_name ON table_name;

ALTER TABLE table_name DROP INDEX index_name;

显示索引信息

SHOW INDEX FROM table_name;

7、使用正则表达式

使用 REGEXP 进行模式匹配

SELECT column1, column2, column3
FROM table_name
WHERE column1 REGEXP 'pattern';

使用 RLIKE 进行模式匹配

SELECT column1, column2, column3
FROM table_name
WHERE column1 RLIKE 'pattern';

8、处理 NULL 值

检查是否为 NULL

SELECT * FROM table_name WHERE column1 IS NULL;

SELECT * FROM table_name WHERE column1 IS NOT NULL;

使用 COALESCE 或 IFNULL 函数处理 NULL

SELECT COALESCE(column1,0) FROM table_name;

SELECT IFNULL(column1,0) FROM table_name;

使用 NULL 排序

SELECT column1, column2, column3
FROM table_name
ORDER BY column1 ASC NULLS FIRST;

SELECT column1, column2, column3
FROM table_name
ORDER BY column1 ASC NULLS LAST;

使用 <=> 操作符进行 NULL 比较

SELECT * FROM table_name WHERE column1 <=> NULL;

聚合函数对 NULL 的处理

SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;

9、导入导出

source 命令导入

 # 创建数据库
create database abc;

# 使用已创建的数据库
use abc;

# 设置编码
set names utf8;

# 导入备份数据库
source /home/abc/abc.sql

使用 SELECT ... INTO OUTFILE 语句导出数据

SELECT column1, column2, column3
INTO OUTFILE '/tmp/data.csv'
FROM table_name
WHERE conditions;