MySQL 常用操作

1 创建/打开/删除数据库

createdatabase db;

createdatabase db characterset utf8mb4;

use db;

dropdatabase db;

alterdatabase db characterset utf8mb4;

2 修复表

mysqlcheck --all-databases

mysqlcheck --all-databases --fast

3 查询

select * from table1;

select * from table1,table2;

select field1,field2,... from table1,table2;

select ... from ... where condition;

select ... from ... where condition groupbyfield;

select ... from ... where condition1 groupbyfieldhaving condition2;

select ... from ... where condition orderbyfield;

select ... from ... where condition orderby field1,field2 desc;

select ... from ... where condition limit10;

selectdistinct field1,field2 from ...

selectdistinct table1.field1,table2.field2 from table1,table2 where condition1 groupby field4 having condition2 orderby field3 desclimit20;

4 插入/删除/更新数据

# 插入

insert into table1(field1,field2,...) values(value1,value2,...);

# 删除

delete from table1;

truncate from table1;

delete from table1 where condition;

delete table1,table2 from table1,table2 where condition;

# 更新

update table1 set field=new_value where condition;

update table1,table2 set field1=new_value1,field2=new_value2,... where table1.id=table2.id and condition;

5 创建/删除/修改表

# 创建

create table table1(field1 type,field2 type,...);

create table table1(field type,...,index(field));

create table table1(field type,...,primary key(field));

create table table1(field1 type,field2 type,...,primary key(field1,field2));

create table table1(field type,...,foreign key(field) references table2(field));

create table table1(field1 type,field2 type,...foreign key(field1,field2) references table2(field1,field2));

create table if not exists table1(field type,...);

create temporary table table1(field type,...);

# 删除

drop table table1;

drop table if exists table1;

drop table table1,table2;

# 修改

alter table table1 modify old_name new_type;

alter table table1 modify old_name new_type not null;

alter table table1 change old_name new_name new_type;

alter table table1 change old_name new_name new_type not null;

alter table table1 alter field set default ...;

alter table table1 alter field drop default;

alter table table1 add new_name new_type;

alter table table1 add new_name new_type first;

alter table table1 add new_name new_type after field;

alter table table1 drop field;

alter table table1 add index(field);

# 改变字段顺序

alter table table1 modify field type first;

alter table table1 modify field1 type after field2;

alter table table1 change old_name new_name new_type first;

alter table table1 change old_name new_name new_type after field;

6 重置root密码

# 停止服务,各个机器具体不一样,可以使用图形界面停止

systemctl stop mysqld

mysqld_safe --skip-grant-tables

# 若失败可以尝试

# mysqld_sage --shared-memory --skip-grant-tables

# 另一个终端

mysql

# 进入之后

# MySQL 8+

flush privileges;

alter user 'root'@'localhost' identified by 'new_pass';

# MySQL 旧版

update mysql.user set password=password('new_password') where user='root';

7 备份与恢复

# 备份

mysqldump -u username -p dbname > backup.sql

# 恢复

mysql -u username -p dbname < backup.sql

8 浏览

showdatabases;

showtables;

showfieldsfrom table1;

describe table1;

showcreatetable table1;

showprocesslist;

9 连接查询

select ... from t1 join t2 on t1.id = t2.id where condition;

select ... from t1 left/rightjoin t2 on t1.id = t2.id where condition;

select ... from t1 join (t2 join t3 on ...) on ...;

10 条件

field = value

field <> value

field like 'value%'

filed is null

field is not null

field in (value1,value2,...)

field not in (value1,value2,...)

condition1 and conditoin2

condition1 or condition2

11 用户与权限

grant all privileges on database.table to 'user'@'localhost' identified by 'password';

grant select,insert,delete on database.* to 'user'@'xxx.xxx.xxx.xxx' identified by 'password';

revoke select on database.table from 'user'@'host';

revoke all privileges,grant option from 'user'@'host';

alter user 'user'@'host' identified with mysql_native_password by 'new_password';

drop user 'user'@'host';

以上是 MySQL 常用操作 的全部内容, 来源链接: www.h5w3.com/29937.html

回到顶部