mysql 常用命令

最新推荐文章于 2025-08-09 20:21:24 发布

weixin_33795093

最新推荐文章于 2025-08-09 20:21:24 发布

阅读量55

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/dslcode/blog/1591211

本文提供了一系列MySQL操作指令，包括创建数据库、用户、表等基础操作，以及权限管理、表结构调整等高级技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

-- root登录
mysql -u root -p

-- 列出所有数据库
show database;

-- 切换数据库
use mallh5;

-- 列出所有表
show tables;

-- 创建用户 mallh5 123456
create user mallh5 identified by '123456';

-- 创建数据库mallh5
create database mallh5 character set utf8;

-- 授权
grant all privileges on mallh5.* to 'mallh5'@'localhost' identified by '123456' with grant option;
grant all privileges on mallh5.* to 'mallh5'@'%' identified by '123456' with grant option;
flush privileges;

-- 删除数据库
drop database mallh5;

-- 删除表
DROP TABLE IF EXISTS request_log;

-- 创建表
CREATE TABLE request_log (
id bigint UNSIGNED NOT NULL AUTO_INCREMENT,
create_time int UNSIGNED NOT NULL,
device_name varchar(50) DEFAULT NULL,
session_id varchar(50) NOT NULL,
request_url varchar(100) NOT NULL,
app_alias varchar(50) NOT NULL,
remote_ip varchar(20) NOT NULL,
client_user varchar(20) DEFAULT NULL,
call_uuid varchar(32) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

-- 删除表
drop table request_log;

-- 显示表结构
describe request_log;

-- 添加主键
alter table request_log add constraint pk_request_log primary key request_log(id);

-- 添加外键
alter table request_log add constraint fk_xxx_xxx foreign key request_log(user_id) references users(id);
-- 删除外键
alter table request_log drop foreign key fk_xxx_xxx;

-- 添加列
ALTER TABLE request_log ADD COLUMN call_source TINYINT NOT NULL DEFAULT 2;
-- 修改列
ALTER TABLE request_log MODIFY COLUMN call_source INT NOT NULL DEFAULT 2;
-- 删除列
ALTER TABLE request_log DROP COLUMN call_source;

-- 唯一性约束
ALTER TABLE t_user ADD unique(username);

-- 创建索引
CREATE INDEX index_create_time ON request_log(create_time);

-- 创建表和用户的常用命令
mysql -u root -p
create user mallh5 identified by '123456';
create database mallh5 character set utf8;
grant all privileges on mallh5.* to 'mallh5'@'localhost' identified by '123456' with grant option;
grant all privileges on mallh5.* to 'mallh5'@'%' identified by '123456' with grant option;
flush privileges;

转载于:https://my.oschina.net/dslcode/blog/1591211