mysql常用命令大全及示例 mysql数据库常用命令大全

一、数据维护

1、备份数据

# mysqldump命令是在dos或bash窗口运行的命令
# mysqldump -u 用户名 -p 密码 备份数据的数据库名>要存放到指定位置的绝对路径
mysqldump -u root -p 123456 test_db>/usr/local/test_db.sql

2、导入数据

# 导入数据不包括创建数据库,需要先创建好数据库
# mysql -u 用户名 -p 密码 要导入数据的数据库名<存放数据文件的绝对路径
mysql -u root -p 123456 test_db</usr/local/test_db.sql

二、常用命令

1、数据库

# 1.查看已有数据库
show databases;

# 2.创建数据库 
# create database 数据库名;
create database test_db;

# 3.删除数据库 
# drop database 数据库名;
drop database test_db;

# 4.使用数据库 
# use 数据库名;
use test_db;

2、表

注意：操作表需要先进入对应的数据库中，使用 use 数据库名 进入数据库。

# 1.查看已有表
show tables;

# 2.创建表 括号里边是设置字段,多个字段以英文半角逗号分隔
# create table 表名(字段名 字段类型(长度),...);
create table test_table(id int(16),name varchar(32),age int(16));

# 3.删除表  
# drop table 表名;
drop table test_table;

三、CRUD基本命令

1、新增数据C（Create)

# 新增数据到test_table表中,values中为每个字段对应的数据
# insert into 表名 values(字段id的值,字段name的值,字段age的值);
insert into test_table values(1,'porty',18);

2、查询数据R（Retrieve)

# 查询test_table表里边的所有记录,*号代表所有字段
# select 字段名(多个字段名以英文半角逗号分隔,查询所有字段可直接使用*号) from 表名;
select * from test_table;

3、更新数据U（Update）

# 更新test_table表的数据
# update 表名 set 要设置的字段='值';    
update test_table set name='happy';

4、删除数据D（Delete）

# 删除test_table表里的所有数据
# delete from 表名;
delete from test_table;

四、where条件与 and ,or ,in ,not in, exists

说明：where条件可以使操作数据具有选择性

1、where条件的简单使用

# 1.查询语句加上where条件可查出满足条件的数据
# 例如 查询出test_table表中名字为porty的数据
select * from test_table where name='porty';

# 2.更新语句加上where条件可更新满足条件的数据
# 例如 将test_table表中名字为porty的数据更新成名字为happy
update test_table set name='happy' where name='porty';

# 3.删除语句加上where条件可删除满足条件的数据
# 例如 删除test_table表中名字为porty的数据
delete from test_table where name='porty';

2、and , or , in , not in

# 查询语句加上where条件可查出满足条件的数据，但是这个条件只有一个，如果需要多个条件就需要其他关键词来结合
# 1.例如 查询出test_table表中名字为porty 并且(and) id为1的数据
select * from test_table where name='porty' and id = 1;
# 2.例如 查询出test_table表中名字为porty或者happy的数据
select * from test_table where name='porty' or name = 'happy';
# 3.如果针对同一个字段，需要查询出大量符合条件的数据，可以使用in,跟or条件实现的结果是一样的，但是方便很多
# 例如 查询出test_table表中名字为porty或者happy的数据
select * from test_table where name in('porty','happy');
# 4.not in 跟 in 是相反的，查询出不符合某条件的
# 例如 查询出test_table表中名字不为porty或happy的数据
select * from test_table where name not in('porty','happy');

3、exists

# 查询语句的where条件加上exists来筛选数据
# 1.例如 当test_table表中有id=1的数据时，查询出test_table表中的所有数据
select * from test_table where exists(select * from test_table where id=1);
# exists中的查询语句 查询到有一行以上的数据会返回true , 如果没有数据会返回false. 外部的查询语句会根据exists内的查询语句返回结果 true 或者 false 判断是否要执行查询操作。

五、聚合函数

1、count()求表中的记录数

# 1.查询出test_table表中有多少条记录
# count(* 或者 字段名)
# 注意:如果括号中为字段名，那么查询出的结果不包括字段为null的数据
select count(*) from test_tables;

2、max()求出表里某字段值最大的数据

# 1.查询出test_table表中年龄age字段最大值的数据
# max(字段名)
select max(age) from test_table;

3、min()求出表里某字段值最小的数据

# 1.查询出test_table表中年龄age字段最小值的数据
# min(字段名)
select min(age) from test_table;

4、sum()求出表里某字段值的和

# 1.查询出test_table表中所有年龄age字段的和
# sum(字段名)
select sum(age) from test_table;

5、avg()求出表里某字段值的平均值

# 1.查询出test_table表中年龄age字段的平均值
# avg(字段名)
select avg(age) from test_table;

六、多表查询

说明：假设有两个表，表 test_table1和 表 test_table2，两个表的字段都为 id 和 name

1、笛卡尔积

# 1.笛卡尔积：表1的数据有三条，表2数据有三条，两个表的笛卡尔积数据就为 3*3=9条。
# 一般来说笛卡尔积直接查询出来的数据是没有意义的，特定情况加上where做条件处理
# select * from 表1,表2;
select * from test_table1,test_table2;
# 附加where条件，查询出当表1的id和表2的id相等的数据
select * from test_table1,test_table2 where test_table1.id = test_table2.id;

2、内连接

# 1.内连接
# 内连接的关键词为 inner join
# select * from 表1 inner join 表2;
select * from test_table1 inner join test_table2;
# 一般会加上 on 来做为条件判断，查出符合条件的数据
# 查询出当表1的id和表2的id相等的数据
select * from test_table1 inner join test_table2 on test_table1.id = test_table2.id;

3、左外连接

# 1.左外连接
# 左外连接关键词 left outer join,书写的时候outer可以省略 直接书写为 left join也是一样的
# 会查询出表1的所有数据，如果表2也有与表1相同的数据会拼接到同一条记录，如果表2没有与表1相同的数据会显示null
# select * from 表1 left join 表2;
select * from test_table1 left join test_table2;
# 一般会加上 on 来做为条件判断，查询出符合条件的数据
# 查询出当表1的id和表2的id相等的数据
select * from test_table1 left join test_table2 on test_table1.id = test_table2.id;
# 假如表1的数据为：
id	name
1	porty
2	happy
# 假如表2的数据为：
id	name
1	book
# 查询结果为：
id	name	id	name
1	porty	1	book
2	happy	null null

4、右外连接

# 1.左外连接
# 左外连接关键词 right outer join,书写的时候outer可以省略 直接书写为 right join也是一样的
# 会查询出表2的所有数据，如果表1也有与表2相同的数据会拼接到同一条记录，如果表1没有与表2相同的数据会显示null
# select * from 表1 right join 表2;
select * from test_table1 right join test_table2;
# 一般会加上 on 来做为条件判断，查询出符合条件的数据
# 查询出当表1的id和表2的id相等的数据
select * from test_table1 right join test_table2 on test_table1.id = test_table2.id;
# 假如表1的数据为：
id	name
1	porty
# 假如表2的数据为：
id	name
1	book
2	news
# 查询结果为：
id	name	id	name
1	porty	1	book
null null	2	news

5、全连接

说明：全连接可以将两张表的数据都获取到，Oracle数据库有全连接full join，而mysql并不支持。但是mysql可以通过各种操作完成与full join全连接实现的效果一致，后面会进行说明

6、union 和 union all

# 假如表1的数据为：
id	name
1	porty
2	happy
# 假如表2的数据为：
id	name
1	book
2	happy

# 将两个表的数据结合在一起可以使用 union 或者 union all来连接
# union不包括重复数据
select * from test_table1 union select * from test_table2;
# 查询结果
id	name
1	porty
2	happy
1	book
# union all 包括重复数据
select * from test_table1 union all select * from test_table2;
# 查询结果
id	name
1	porty
2	happy
1	book
2	happy

七、case when 语句

1、case when的使用

# 这里我们创建了一个员工表，字段有 id  name名称  sex性别 salary工资
create table employee(
   id int(16), 
   name varchar(6),
   sex int(2),
   salary double(16)
);
# 插入几行数据,sex性别一列咱们1代表男 2代表女 0代表其他
insert into employee values(1,"老王",1,5000.0);
insert into employee values(2,"老刘",2,6500.0);
insert into employee values(3,"老吴",0,4200.0);
# 查询下表中的所有数据，进行确认
id	name	sex	  salary
1	老王	   1	5000.0
2	老刘	   2	6500.0
3	老吴	   0	4200.0

# case when 语句的使用
# 例如 将查询出的数据中，性别为1的显示为男，2显示为女，0显示为其他
select * , 
case # 开始匹配
when sex = 1 then "男"     # 如果sex=1就在新的列中标为男
when sex = 2 then "女"	  # 如果sex=2就在新的列中标为女
when sex = 0 then "其他"	 # 如果sex=0就在新的列中标为其他
end # 结束匹配
as sex_ch # 给新出现的一列取个别名，如果不取别名，列名默认显示为case
from employee;
# 查询结果为
id	name	sex	salary	sex_ch
1	老王	   1   5000.0   男
2	老王	   2   6500.0   女
3	老王	   0   4200.0   其他
# 当然，如果这样查询出来会觉得sex有两列数据有点多余了，我们可以在查询列那里进行修改  
select id,name,salary, # 不再查询所有的列,指明查出id,name,salary 三列
case # 开始匹配
when sex = 1 then "男"     
when sex = 2 then "女"	  
when sex = 0 then "其他"	 
end # 结束匹配
as sex_ch # 给新出现的一列取个别名，如果不取别名，列名默认显示为case
from employee;
# 查询结果为
id	name	salary	sex_ch
1	老王	   5000.0   男
2	老王	   6500.0   女
3	老王	   4200.0   其他

持续更新中…