MySQL命令行常用命令

登录

mysql –h 主机名 –u用户名 –p密码

C:/> mysql -h 192.168.1.45 -u root -p root

显示所有数据库

mysql > show databases;

进入指定数据库

use 库名;

mysql > use mysql;

查看库中所有表

show tables from 库名;

查看其他库中的所有表

mysql > show tables;
mysql > show tables from mysql;

查看当前库

mysql > select database();

查看版本

C:/ > mysql -version //进入mysql客户端之前
C:/ > mysql -V //进入mysql客户端之前
mysql > select version(); //进入mysql客户端之后

新建一个数据库

mysql > create database spark;

建表

mysql > create table customer(id varchar(30),age int, name
varchar(30),birthday date);

查看表结构

mysql > desc customer;

查看表

mysql > select * from customer;

查询特定列

mysql > select id,name,sex from student

查询数据并进行过滤

mysql > select id,name,sex from student where
id => 1003;

运算符查询

mysql > select id,name,sex from student where
age >=18 and age <=35;
mysql > select id,name,sex from student where
age between 18 and 35;

查询变种

mysql > select * from student where age =18 or
age =35 or age =23
mysql > select * from student where age in
(18,23,35);

mysql > select * from student where name like ‘%小%’;
mysql > select * from student where name like ‘_明%’;

mysql > select * from student where email is
null;
mysql > select * from student where email is
not null;

mysql > select * from student order by
age ; //升序
mysql > select * from student order by age
desc ; //降序

%匹配任意多个字符，_只匹配一个字符

插入数据

mysql > insert into student(id,name)
values(1001, ‘小明’);

插入 varchar 或 date 型的数据要用 单引号 引起来

修改表

mysql > update student set name=‘小红’ where
id = 1001;

删除表记录

mysql > delete from student where id =1001;

删除表

mysql > drop table customer;

删除数据库

mysql>
drop database girls

退出

一共三种方式

mysql > exit;
ctrl+C
quit;