数据库 SQL 脚本相关
1、字段增删改
新增字段
SQL
ALTER TABLE [表名] ADD [字段名]
varchar(100) NULL COMMENT '注释说明' AFTER [指定字段名位于其后];
修改字段
SQL
ALTER TABLE [表名] CHANGE [原字段名] [新字段名]
varchar(100) NULL COMMENT '注释说明' AFTER [指定字段名位于其后];
删除字段
SQL
ALTER TABLE [表名] DROP [字段名];
2、查看指定表的字段说明、类型、约束
SQL
select
column_name '字段名',
column_comment '字段说明',
column_type '字段类型',
column_key '约束'
from information_schema.columns
where table_schema = '数据库名'
and table_name = '表名';
3、容量查询
SQL
-- 查看指定数据库各表容量大小
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、分组查询取最大日期的记录
SQL
select [分组字段],[日期],[字段1],[字段2]
from [表名] t
where t.[日期] =
(
select max([日期])
from [表名]
where [分组字段] = t.[分组字段]
)
group by t.[分组字段];
5、查询当天、最近一天、最近一周、本月、上一月数据
SQL
-- 今天
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、索引创建
创建普通索引
SQL
CREATE INDEX index_name ON table_name (column1,column2,column3);
修改表结构(添加索引)
SQL
ALTER TABLE table_name ADD INDEX index_name (column1,column2,column3);
创建表时指定
SQL
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
column3 data_type,
INDEX index_name (column1,column2,column3)
);
创建唯一索引
SQL
CREATE UNIQUE INDEX index_name ON table_name (column1,column2,column3);
修改表结构(添加索引)
SQL
ALTER table mytable
ADD CONSTRAINT unique_constraint_name UNIQUE (column1,column2,column3);
创建表时指定
SQL
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
column3 data_type,
CONSTRAINT index_name UNIQUE (column1,column2,column3)
);
删除索引
SQL
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
显示索引信息
SQL
SHOW INDEX FROM table_name;
7、使用正则表达式
使用 REGEXP 进行模式匹配
SQL
SELECT column1, column2, column3
FROM table_name
WHERE column1 REGEXP 'pattern';
使用 RLIKE 进行模式匹配
SQL
SELECT column1, column2, column3
FROM table_name
WHERE column1 RLIKE 'pattern';
8、处理 NULL 值
检查是否为 NULL
SQL
SELECT * FROM table_name WHERE column1 IS NULL;
SELECT * FROM table_name WHERE column1 IS NOT NULL;
使用 COALESCE 或 IFNULL 函数处理 NULL
SQL
SELECT COALESCE(column1,0) FROM table_name;
SELECT IFNULL(column1,0) FROM table_name;
使用 NULL 排序
SQL
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 比较
SQL
SELECT * FROM table_name WHERE column1 <=> NULL;
聚合函数对 NULL 的处理
SQL
SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;
9、导入导出
source 命令导入
SQL
# 创建数据库
create database abc;
# 使用已创建的数据库
use abc;
# 设置编码
set names utf8;
# 导入备份数据库
source /home/abc/abc.sql
使用 SELECT ... INTO OUTFILE 语句导出数据
SQL
SELECT column1, column2, column3
INTO OUTFILE '/tmp/data.csv'
FROM table_name
WHERE conditions;