SQL语法

# 配置

# binlog

show variables like 'log_bin';

# information_schema

# 统计表数据量

SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'oms' AND table_name like 'table%' order by table_rows desc;

# 查看数据库编码

SELECT
    `SCHEMA_NAME` AS `Database`,
    `DEFAULT_CHARACTER_SET_NAME` AS `Character_Set`,
    `DEFAULT_COLLATION_NAME` AS `Collation`
FROM
    `information_schema`.`SCHEMATA`
WHERE
    `SCHEMA_NAME` = 'db_name';

1
2
3
4
5
6
7
8

# DDL

# 创建数据库

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4  COLLATE utf8mb4_general_ci;

# 更改主键

ALTER TABLE oms.shop_config DROP PRIMARY KEY;
ALTER TABLE oms.shop_config ADD PRIMARY KEY (shop_id,config_key);

1
2

# 添加字段

ALTER TABLE tally_out_report ADD COLUMN check_msg varchar(255) DEFAULT '' COMMENT '审核评语' AFTER check_status;

# 新增索引

ALTER TABLE table_name ADD UNIQUE (column_name);

# 修改字段

alter column用于设置或删除默认值，该操作会直接修改.frm文件而不涉及表数据。所以这个操作非常快。

alter table users_info alter column role_id set default 1;

change column 修改字段，包括改字段名

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;

modify column 修改字段功能与change colunm相同，但不能修改字段名 alter table oms.live_tag_strategy modify columnstrategy_snvarchar(100) NOT NULL COMMENT '策略编号';

# 创建临时表

CREATE TEMPORARY TABLE temp_order (
    name VARCHAR(255)
);

1
2
3

# DML

# insert 冲突

冲突时更新

INSERT INTO user_admin_t (_id,password) 
VALUES 
('1','多条插入1') ,
('UpId','多条插入2')
ON DUPLICATE KEY UPDATE 
password =  VALUES(password);

1
2
3
4
5
6

冲突时忽略

insert ignore into user_admin_t (_id,password) 
VALUES 
('1','多条插入1') ,
('UpId','多条插入2');

1
2
3
4

编辑

上次更新: 2025/01/08, 15:27:56

SQL函数→