2024-04-11 MySQL操作

登录命令

mysql -uusername -ppasssword

查看MySQL信息

status;

2024-04-11_084533.jpg

查询MySQL安装路径

select @@basedir as basePath from dual;

导出数据

//导出某个数据库
mysqldump -uroot -p jx > jx.sql

//导出所有数据库
mysqldump -uroot -p -A > all.sql

//忽略数据,仅导出结构
mysqldump -uroot -p -A -d > all.sql

//忽略结构,仅导出数据
mysqldump -uroot -p -A -t > all.sql

导入数据

//系统命令
mysql -uroot -p < db.sql

//MySQL命令
source db.sql

创建数据库

create database if not exists jx;

删除数据库

drop database if exists jx;

创建表

create table if not exists user(id bigint auto_increment primary key, username varchar(50));

删除表

drop table if exists user;

添加数据

insert into user(username) values('账号');

查询所有数据库

show databases;

选择数据库

use databasename;

查询所有表

show tables;

查询

//查所有
select * from user;

//查某列
select host, user from user;

//条件查询
select host,user from user where user = 'root';

//模糊查询
select host,user from user where user like '%my%';

//区间查询
select id, created_time from user where id between 1 and 4;

//排序(desc=逆序，asc=升序(默认))
select * from user order by id desc;

修改

update user set username = '更新账号' where id = 1;

删除

delete from user where id = 1;