MySQL命令：
mysql -uroot -p -A //连接本地mysql -A 禁用数据表自动补全
mysql -h127.0.0.1 -uroot -p123 //连接远程mysql
select version(); or status; //版本
DCL篇(数据控制)
create user username@host identified by password -- 创建用户
drop user username@host -- 删除用户
grant all privileges on databasename.tablename to username@host -- 授予用户权限
grant privileges on databasename.tablename to username@host identified by 'password' -- 授予用户不存在则会新建并授权。
UPDATE mysql.user SET Host='%' WHERE User='your_username'; -- 修改Host
revoke privileges on databasename.tablename from username@host; -- 撤销权限
grant all privileges on *.* to 'root'@'%' identified by '1234' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1234' WITH GRANT OPTION;
show grants for username@host -- 查看用户授权
flush privileges; -- 刷新权限
-- 修改用户密码
set password for username@host=password('123');
set password=password('newpassword'); --当前登录用户
ALTER USER 'root'@'%' IDENTIFIED BY '1234'; --mysql8
DDL(数据定义)
-- 创建数据库
create database databasename;
-- 删除数据库
drop database databasename;
-- 查看所有数据库
show databases;
-- 查看当前数据库
select databases();
status;
-- 使用数据库
use databasename;
-- 创建数据库
create table tablename(字段名 类型1 is null key default value [extra] [comment],...) [engine] [charset]
create table if not exists student(
id int unsigned not null comment '主键ID' auto_increment,
...
)engine=MyISAM default charset=utf8 auto_inicrement=1
-- 查看引擎
show engines; --查看支持的引擎
show variables like '%storage_engine' --查看默认存储引擎
-- 删除数据表
drop table [tablename];
drop table if exists [tablename];
drop table if exists [tablename1,tablename2,...];
-- 查看库有那些数据表
show tables;
show tables like "%name%";
show tables from [databasename];
-- 查看数据表结构
desc tablename;
describe tablename;
-- 查看建表语句
show create table [tablename];
-- 重命名数据表
rename table [tablename] to [newtablename];
-- 删除字段自增长
alter table [tablename] change [columnname] [columnname] [type];
alter table student change sid sid int(10) unsigned;
-- 增加字段自增长
alter table [tablename] modify [columnname] [type] auto_increment;
alter table student modify sid int(11) unsigned auto_increment;
-- 修改自增长起始值
alter table [tablename] auto_increment=[value];
show create table [tablename] --查看当前自增长值
-- 增加列
alter table [tablename] add column [columnname] [columdefinition] [after columnname];
alter table student add column age tinyint(2) comment '年龄' after name;
-- 删除列
alter table [tablename] drop column [columnname];
alter table student drop column age,drop age1;
-- 重命名
alter table [tablename] change [columnname] [newcolumnname] [type];
-- 修改列属性
alter table [tablename] modify [columnname] [newdefinition];
alter table student modify age tinyint(2) not null;
-- 添加索引
alter table [tablename] add index [indexname](字段名1，字段名2...);
alter table student add index phone_idex(phone);
show index from [tablename] 查看索引
-- 删除索引
alter table [tablename] drop index [indexname];
alter table student drop index phone_index;
-- 创建临时表
create temporary table [tablename](
[字段1] [类型1] [IS NULL] [KEY] [default value] [extra] [comment],
...
);
-- 创建内存表
create table [tablename] (
[字段1] [类型1] [IS NULL] [KEY] [default value] [extra] [comment],
...
)engine=memory;
-- 修改数据表存储引擎
alter table [tablename] type|engine=[enginename];
alter table student type=InnoDB;
-- 清空表内容
truncate [tablename];
DQL(数据查询)
-- 简单查询
SELECT [列名称] FROM [表名称] WHERE [筛选条件];
DML(数据操作篇)
-- 插入记录
insert into [tablename](c1,c2,...) values(v1,v2,...);
insert into [tablename] select ...;
insert into [tablename] set [c1]=[v1] ...
replace into [tablename](c1,c2,...) values(...);
replace into [tablename](c1,c2,...) select ...;
replace into [tablename] set [c1]=[value],...;
-- 删除记录
delete from [tablename] where [cond];
-- 修改记录
update [tablename] set [c_name]=[new value] where [cond];
-- 备份还原
mysqldump -u username -p databasename > export_file_name;
mysqldump -u username -p databasename tablename > export_file_name;
select * from student into oufile "student.csv"; -- 导出到csv
source datafile.sql;
load data infile '[file]' into table [tablename] fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
TCL(事务)
-- 查看事务是否提交
select @@global.autocommit;
show variables like '%autocommit%';
-- 临时/永久关闭
set autocommit = 0;
my.conf 配置中增加此项 重启即可永久关闭
-- 保存点
savepoit [pointname];
rollback to savepoint [pointname];
CCL(游标)
DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH cursor_name INTO var_name1,var_name2...;
CLOSE cursor_name;
