MySQL常用操作

  • Z时代
  • 2024-01-10
  • 分类：综合

database

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

create database db;
create database db character set utf8mb4;
use db;
drop database db;
alter database db character set 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 group by field;
select ... from ... where condition1 group by field having condition2;
select ... from ... where condition order by field;
select ... from ... where condition order by field1,field2 desc;
select ... from ... where condition limit 10;
select distinct field1,field2 from ...
select distinct table1.field1,table2.field2 from table1,table2 where condition1 group by field4 having condition2 order by field3 desc limit 20;

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 浏览

show databases;
show tables;
show fields from table1;
describe table1;
show create table table1;
show processlist;

9 连接查询

select ... from t1 join t2 on t1.id = t2.id where condition;
select ... from t1 left/right join 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常用操作 的全部内容， 来源链接： utcz.com/z/534443.html

回到顶部