索引操作

查看索引

SHOW INDEX FROM <表名>;

删除索引

alter table <表名> drop index <索引名>;

删除主键

alter table <表名> drop primary key;

删除外键

alter table <表名> drop foreign key <外键名称>;

添加主键索引

ALTER TABLE <表名> ADD PRIMARY KEY (`column`) USING BTREE 

添加唯一索引

ALTER TABLE <表名> ADD UNIQUE (`column`) USING BTREE

添加全文索引

ALTER TABLE <表名> ADD FULLTEXT idx_name (`column`)
-- 例如
ALTER TABLE news ADD FULLTEXT idx_full_text (`content`);

添加普通索引，索引的名称推荐 idx_ 开头

ALTER TABLE <表名> ADD INDEX idx_name (`column` ) USING BTREE

添加组合索引

ALTER TABLE <表名> ADD INDEX idx_name (`column1`, `column2`, `column3`) USING BTREE

修改列

修改列

列名称必须存在

alter table <表名> 
modify column 
`<字段名>` varchar(6) default '' comment '接单地区（区）';

添加列

alter table <表名>
add column 
`<字段名>` varchar(6) default '' comment '接单地区（区）'
after `<字段名>`;

删除列

alter table <表名> drop column <列名>;

修改列的名称

ALTER TABLE erp_material_request_order_detail
CHANGE COLUMN no_return_reason is_need_return_reason varchar(100) NOT NULL DEFAULT '' COMMENT '操作退回或者无需退回的原因';

表操作

截断表

truncate table <表名>; 

创建表

mysql 5.5 之后默认就是 innodb，所以不指定存储引擎也是可以的

CREATE TABLE `erp_api_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '类型（1api发起，2api接受）',
  `url` text COMMENT '请求地址',// text 类型不可以设定默认值字，默认就是空字符,只能通过 = '' 和 ！= '' 可以筛选
  `ip` varchar(500) NOT NULL DEFAULT '' COMMENT '请求id',
  PRIMARY KEY (`id`),
  KEY `idx_dev_ids` (`dev_ids`,`status`,`check_time`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='api调用请求日志';

以某张表为模板创建表

CREATE TABLE IF NOT EXISTS content_operation_log_2023_08 LIKE content_operation_log

修改表名称

alter table ts01 rename to ts01_new;

查看表状态

可以查看数据库所有表的大小等信息

SHOW TABLE STATUS FROM db_name;

查看表的创建语句

--- 显示表创建语句
SHOW CREATE TABLE 104_server_auto_unregister_config;

--- 这个只有显示字段信息
DESC 104_server_auto_unregister_config;

用户权限

创建用户

mysql 5.6 5.7 用户管理，'david'@'%' 会作为一个唯一索引，localhost 和 127.0.0.1 是不一样的东西

create user 'david'@'%' identified by '03203511';// 添加

删除用户

删除用户，已经在连接的还是会继续连接

drop user 'david'@'%';

修改密码

这个条语句只适合 mysql 5.7，5.6 不行，8.0 没有试过

alter user 'test'@'%' identified by '03203512';

5.6 版本使用这个修改密码

SET PASSWORD FOR 'david'@'%' = PASSWORD('mypass');

查看权限

显示有什么权限

show grants for 'test'@'%';

赋予权限

grant select,update,insert,delete,create,index on test.* to 'test'@'%';

回收权限

回收部分权限

revoke create,index on test.* from 'test'@'%';

回收所有的权限

revoke all on test.* from 'test'@'%';

数据库

创建数据库

创建数据库，utf 8 只能保存 3 字节，不能保存表情符号等四字节的

create database egg default character set utf8mb4 collate utf8mb4_bin;

增删改查

删除操作

delete from <表名称> where id = 1 limit 1

如果唯一 key 冲突了，则更新，否则是插入数据

下面例子中，假设唯一索引是字段 logical_region_id，如果已经存在 logical_region_id=1010 的，则是修改字段 day、updated_at、updater 三个字段

INSERT INTO
    `104_server_auto_unregister_config` (
        `logical_region_id`,
        `day`,
        `updater`,
        `updated_at`,
        `created_at`
    )
VALUES (
    1,
    1010,
    '小明',
    1703640088,
    1703640088
) ON
DUPLICATE KEY
UPDATE
    `day` = VALUES(`day`),
    `updated_at` = VALUES(`updated_at`),
    `updater` = VALUES(`updater`) 


-- 
insert into `uto_ad_task_hourly_data` 
( 
    `ad_creatives_version_id`, 
    `ad_task_id`, 
    `adv_id`, 
    `cash_cost`, 
    `company_id`, 
    `convert_cnt`,
    `convert_upload_cnt`,
    `created_at`, 
    `estimated_cost`,
    `estimated_income`, 
    `media_convert_cnt`, 
    `platform`, 
    `rebate_value`, 
    `start_date`,
    `start_hour`,
    `start_six_hour`, 
    `task_plan_id`, 
    `updated_at`, 
    `user_id`
) values 
(
    888,
    888,
    888, 
    888,
    888, 
    888,
    888,
    '2024-08-09 10:29:32', 
    888, 
    888,
    888, 
    'ocean_engine',
    888, 
    '2024-08-09',
    '2024-08-09 00:00:00',
    '2024-08-09 00:00:00', 
    27, 
    '2024-08-09 11:29:32', 
    888
)
ON duplicate KEY UPDATE 
    -- values 代表从上面的values里取值
    `task_plan_id` = values(`task_plan_id`),
    `platform` = values(`platform`),
    `company_id` = values(`company_id`),
    `user_id` = values(`user_id`), 
    `adv_id` = values(`adv_id`), 
    `ad_task_id` = values(`ad_task_id`), 
    `start_date` = values(`start_date`),
    `start_six_hour` = values(`start_six_hour`),
    `start_hour` = values(`start_hour`),
    `estimated_cost` = values(`estimated_cost`),
    `cash_cost` = values(`cash_cost`),
    `rebate_value` = values(`rebate_value`), 
    `estimated_income` = values(`estimated_income`), 
    `convert_cnt` = values(`convert_cnt`), 
    `convert_upload_cnt` = values(`convert_upload_cnt`),
    `media_convert_cnt` = values(`media_convert_cnt`),
    `ad_creatives_version_id` = values(`ad_creatives_version_id`),
    `updated_at` = values(`updated_at`)