关于Mysql的常规操作整理

关于Mysql的常规操作整理

 

 

--- HOST                主机
--- USERS                用户
--- PASSWORD            密码
--- DNAMES                数据库名
--- TNAMES                数据表名
--- WCONDITION            where条件
--- FIELDS                字段集合
--- FIELD                字段
--- VALUES                值集合
--- VALUE                值
--- NEW_TNAMES            新表名
--- NEW_PASSWORD        新密码
--- INAMES                索引名








--- 登录 Mysql
mysql -hHOST -uUSERS -pPASSWORD
mysql -uUSERS -pPASSWORD
mysql -uUSERS -p
--------------------------------------------------------------------------------

--- 备份数据库
mysqldump -hHOST -uUSERS -pPASSWORD DNAMES>FILE_URL
mysqldump -uUSERS -pPASSWORD DNAMES>FILE_URL
mysqldump -uUSERS -p DNAMES>FILE_URL
mysqldump -uUSERS -pPASSWORD DNAMES TNAMES>FILE_URL
--- 备份数据 -d: 没有数据库记录，-t: 没有数据库结构，-add-drop-table: 添加drop table
mysqldump -uUSERS -pPASSWORD -d -add-drop-table DNAMES>FILE_URL;
--- 备份数据 只要数据不要sql命令
mysqldump -uUSERS -pPASSWORD -T./ DNAMES TNAMES
mysqldump -uUSERS -pPASSWORD -T./ DNAMES
--------------------------------------------------------------------------------

--- 导入数据库
mysql -hHOST -uUSERS -pPASSWORD DNAMES<FILE_URL
mysql -hHOST -uUSERS -pPASSWORD DNAMES --default-character-set=utf8<FILE_URL
mysql -uUSERS -pPASSWORD DNAMES<FILE_URL
mysql -uUSERS -p DNAMES<FILE_URL
load data local infile FILE_URL into table TNAMES;
source FILE_URL;
--------------------------------------------------------------------------------

--- 显示所有数据库
show databases;
--- 选择数据库
use DNAMES;
--------------------------------------------------------------------------------

--- 显示所有表名
show tables;
--------------------------------------------------------------------------------

--- 设置字符编码
charset gbk;
--------------------------------------------------------------------------------

--- 显示表结构
describe TNAMES;
desc TNAMES;
show full fields from TNAMES;
--------------------------------------------------------------------------------

--- 显示创建数据库命令
show create database DNAMES;
--- 显示创建数据表命令
show create table  TNAMES;
--------------------------------------------------------------------------------

--- 创建数据库
create database DNAMES;
--- 指定编码 GBK
create database DNAMES default character set gbk collate gbk_chinese_ci;
--- 指定编码 UTF8
create database DNAMES default character set utf8 collate utf8_general_ci;
--------------------------------------------------------------------------------

--- 删除数据库
drop database DNAMES if exists DNAMES;
drop database DNAMES;
--- 删除数据表
drop table TNAMES if exists TNAMES;
drop table TNAMES,TNAMES,TNAMES .....;
drop table TNAMES;
--- 删除记录
delete from TNAMES;
delete from TNAMES where WCONDITION;
--- 清空数据表
truncate TNAMES;
--------------------------------------------------------------------------------

--- 插入数据记录
insert into TNAMES (FIELDS) values (VALUES);
insert into TNAMES values (VALUES);
insert into TNAMES set FIELD=VALUE;
--- 更新数据记录
update set FIELD=VALUE;
update set FIELD=VALUE where WCONDITION;
--------------------------------------------------------------------------------

--- 修改用户名密码
update mysql.user set password=password('NEW_PASSWORD') where user='USERS';
flush privileges;
--------------------------------------------------------------------------------

--- 增加一个用户并授权
--- references: Mysql外键
--- create temporary tables: 创建临时表
--- index:索引
--- create view: 操作视图源码
--- show view: 查看视图源码
grant select,insert,update,delete,create,drop,alter,references on DNAMES.TNAMES to USERS@HOST identified by PASSWORD with grant option;
grant select,insert,update,delete,create,drop,alter,references on DNAMES.TNAMES to USERS@HOST identified by PASSWORD;
--- execute: 存储过程
grant execute on procedure on DNAMES.TNAMES to USERS@HOST;
--- execute: 函数
grant execute on function on DNAMES.TNAMES to USERS@HOST;
flush privileges;
grant select,insert,update,delete on *.* to USERS@HOST identified by PASSWORD;
flush privileges;
--- 修改用户密码
update mysql.user set password=password('NEW_PASSWORD') where user=USERS;
--- 删除授权
revoke all privileges on *.* from USERS@HOST;
delete from mysql.user where user=USERS;
flush privileges;
---- 查看授权当前用户
show grants;
---- 查看授权指定用户
show grants for USERS@HOST;
--------------------------------------------------------------------------------

--- 显示当前Mysql版本和单签日期
select version(),current_date,current_time,now(),user(),database();
--------------------------------------------------------------------------------

--- 重名表名
alter table TNAMES rename as NEW_TNAMES;
alter table TNAMES rename NEW_TNAMES;
--- 字符串连接
select concat('str1','str2');
--- 删除索引
drop index INAMES on TNAMES;
alter table TNAMES drop index INAMES;
--- 创建索引
alter table TNAMES add index INAMES (FIELD);
alter table TNAMES add index INAMES (FIELD,FIELD);
create index INAMES on TNAMES (FIELD);
create index INAMES on TNAMES (FIELD,FIELD);
--- 建立唯一索引
create unique index INAMES on TNAMES (FIELD);
--------------------------------------------------------------------------------

--- 修改列类型 (id改为 int unsigned)
alter table TNAMES modify id int unsigned;
--- 修改列类型 (id改为 sid int unsigned)
alter table TNAMES change id sid int unsigned;
--- 复制表结构
create table NEW_TNAMES select * from TNAMES where 1!=1;
--- 复制表和记录
create table NEW_TNAMES select * from TNAMES;
--------------------------------------------------------------------------------

--- 显示数据库状态
show status;
--- 显示系统变量
show variables;
--------------------------------------------------------------------------------