最全、最详细的MySQL常用命令（MySQL）

前言

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一。

一、MySQL 是什么？

MySQL 是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL作为网站数据库。

二、MySQL 基础命令（sql语句最后需要有分号结尾）

1、连接数据库

mysql -uroot -pmysql

-- 不显示密码
mysql -uroot -p

2、退出数据库

quit
exit
ctrl + d

3、显示数据库版本

select version();

4、显示时间

select now();

5、导入sql文件数据

source 具体地址/areas.sql;

-- windows中文报错问题解决，改变编码模式
chcp 65001 

三、数据库的操作命令

1、查看所有数据库

show databases;

2、查看当前使用的数据库

select database();

3、创建数据库

create database 数据库名 charset=utf8;

4、查看创建数据库的语句

show create database 数据库名;

5、使用数据库

use 数据库名;

6、删除数据库

drop database 数据库名;

四、数据库表的操作命令

1、查看当前数据库中所有的数据表

show tables;

2、创建数据库表

-- 数据库常用的数据类型
    -- int 整型
    -- int unsigned 无符号整形
    -- decimal 小数；如 decimal(5, 2) 表示共存5位数，小数占 2 位
    -- varchar 表示可变长度的字符串；如varchar(3)，填充'ab'时就会存储'ab'，3表示字符数
    -- char 表示固定长度的字符串；如char(3)，如果填充'ab'时会补一个空格为'ab '，3表示字符数
    -- date, time, datetime 日期时间
    -- enum 枚举类型

-- 数据库常用的数据约束
    -- auto_increment 表示自动增长
    -- not null 表示不能为空
    -- primary key 表示主键；类型大多为 int unsigned
    -- foreign key 外键
    -- unique 惟一；此字段的值不允许重复
    -- default 默认值
--create table 表名(字段 类型 约束,[字段 类型 约束]……);
-- 例子
create table students(
	id int unsigned primary key auto_increment not null,
	name varchar(30) not null,
	age int unsigned default '保密',
	sex enum('男','女') default '保密'
	);

3、外键的使用

-- 外键是一种约束,约束了插入或者更新必须在外键的表中存在这个数据

     -- 1.表已经存在后添加外键的命令
     -- alter table 主表名 add foreign key (主表的外键) references 外键表(外键主键);
alter table goods add foreign key (cate_id) references goods_cates(id);

     -- 2.在创建主表表的同时设置外键 (注意:goods_cates和goods_brands两个外键表必须先创建，之后再创建goods(主表))
     -- foreign key (主表的外键) references 外键表(外键表的主键)
create table if not exists goods_key(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key (cate_id) references goods_cates(id)
);

     -- 3.如何取消外键约束
     -- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
     -- show create table 表名;
show create table goods;

     -- 获取名称之后就可以根据名称来删除外键约束
     -- alter table 表名 drop foreign key 外键名;
     -- CONSTRAINT `goods_ibfk_1`
     --删除外键的功能
alter table goods drop foreign key goods_ibfk_1;
     --删除外键的标识
alter table goods drop key cate_id;

4、查看表的创建语句

show create table 表名;

5、查看表结构

desc 表名;

6、删除表

drop table 表名;

7、修改表的字段

-- 添加字段
alter table 表名 add 列名 类型;
-- 修改字段，不重命名版
alter table 表名 modify 列名 类型及约束;
-- 修改字段，重命名版
alter table 表名 change 原名 新名 类型及约束;
-- 删除字段
alter table 表名 drop 列名;

8、修改表名

rename table 原表名 to 新表名;

五、表中数据的基本操作

1、添加数据

-- 全列插入,字段不写也行，不过要一一对应，也可以全列多行插入
insert into 表名(id,name,age,height,weight,sex,cls_id,is_delete) values(0,'小张',25,175,56,'男',1,0);
-- 部分插入,也可以部分列多行插入
insert into 表名(字段1,...) values(值1,...);
-- 单列多行插入
insert into 表名(字段) values(值1),(值2),...;

2、删除表中数据

delete from 表名

-- 条件删除
delete from 表名 where 条件

3、修改数据

update 表名 set 字段1=值1,字段2=值2... where 条件;
-- 全部修改；在修改的时候要注意加条件,如果不加就是全部修改
update 表名 set age = 20;
-- 按条件修改
update 表名 set age = 50 where id = 2; 
-- 按条件修改多个值
update 表名 set sex ="女",age=15 where id=5 ;

4、查询数据

-- * 代表表中所有的字段，也可以手动指定想要显示的字段，将*改成想要显示字段的字段名即可（字段名之间以 ，隔开）
select * from 表名;

-- 条件查询
select ... from 表名 where ...
-- 比较运算符：>，>=，<，<=，!= <>，=
-- 例子
select id,name from 表名 where age > 18;

-- 逻辑查询
-- 逻辑运算符：and，or，not；逻辑运算符一般不单独使用，可以和其它查询方式联合使用
-- 例子
select id,name,age from 表名 where age >18 and age < 28;

-- 模糊查询（like）
-- %替换任意个，_ 替换1个
-- 例子
select * from 表名 where name like '小_%';

-- 范围查询
 -- 例子：查询年龄为18或34的姓名（in (18，34）表示在一个非连续的范围内）
 select name from 表名 where age in(18,34);
 -- 例子：年龄不是18或34岁的信息（not in (18,34)不非连续的范围之内）
 select * from 表名 where age not in (18,34);
 -- 查询年龄在18到34之间的的信息（between ... and ...表示在一个连续的范围内，包含两端的数据）
 select * from 表名 where age between 18 and 34;
 -- 查询 年龄不在18到34之间的的信息（not between ... and ...表示不在一个连续的范围内）
 select * from students where age not between 18 and 34;

5、空判断

-- 判空is null（height：身高）
select * from 表名 where height is null;
-- 判非空is not null
select * from 表名 where height is not null;

6、消除重复行(查性别)

-- distinct 字段 
select distinct sex from 表名；

7、起别名

-- 使用as为列指定别名（这个只针对当前这一次查询显示）
select 字段[as 别名] , 字段[as 别名] from 表名;
-- 使用as为表指定别名
select 别名.字段 .... from 表名 as 别名;

8、表数据的排序

-- order by 字段 asc(或desc)
-- asc从小到大排列，即升序
-- desc从大到小排序，即降序
select * from 表名 order by age asc;
-- order by 多个字段
-- 按照身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序, 如果年龄也相同那么按照id从大到小排序;排序有优先级,第一个优先级最高
select * from 表名 order by height desc, age asc,id desc;

9、表数据的聚合函数

-- count 总数
-- 查询男性有多少人
select count(*) from students where sex ='男';

-- max 最大值
-- 查询最大的年龄
select max(age) from students;

-- min 最小值
-- 查询最小的年龄
select min(age) from students;

-- sum 求和
-- 计算所有人的年龄总和
select sum(age) from students;

-- avg 平均值
-- 计算平均年龄
select avg(age) from students;
-- 计算平均年龄 sum(age)/count(*)
select sum(age)/count(*) from students;

-- round(数值,小数位数)函数,四舍五入
-- 计算所有人的平均年龄，保留2位小数
select round(avg(age),2) from students;

10、分组

-- group by
-- 按照性别分组,查询所有的性别
=select sex from students group by sex;

-- group by+聚集函数
-- 计算每种性别中的人数
=select sex,count(*) from students group by sex;
-- 查询每组性别的平均年龄
=select sex,avg(age) from students group by sex;

-- group by+having
-- having 作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
-- 按照sex字段进行分组，统计分组条数大于6的
-- select 分组字段 from 表名 group by 分组字段 having 分组的条件;
=select sex,count(*) from students group by sex having count(*)>=6;

-- group by+group_concat(字段名)
-- group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
-- 查询同种性别中的姓名
=select sex,group_concat(name) from students group by sex;

-- having(注意having和group by 连用 having后通常也要跟 聚合函数)
-- 查询平均年龄超过30岁的性别，以及姓名
=select sex,avg(age),group_concat(name) from students group by sex having avg(age) > 25;

-- 查询每种性别中的人数多于2个的信息
=select sex,count(*),group_concat(name) from students group by sex having count(*) > 5;

-- with rollup 汇总的作用(了解)
=select sex,count(*) from students group by sex with rollup;
=select sex,count(*),group_concat(name) from students group by sex with rollup having count(*) > 2;

11、分页

-- 分页用在数据量大的时候,分批传输数据,提升用户体验
-- limit start, count
-- limit 放在最后面(注意)
-- 起始位置 = (页数-1)*每一页展示的个数
-- 限制查询出来的数据个数
-- 查询前5个数据 0从第一个数据开始
=select * from areas limit 0,5;

12、连接查询

-- inner join ... on
-- select ... from 表A inner join 表B on 条件;
-- 查询有能够对应班级的学生以及班级信息
=select * from students inner join classes on students.cls_id = classes.id;

-- 按照要求显示姓名、班级,取别名是为了分清两个name;s_name和c_name
=select students.name as s_name,classes.name as s_name from students inner join classes on students.cls_id = classes.id;

-- 给数据表起名字(用在自联操作时候,很少用),将students取别名为s;将classes取别名为c
=select s.name as s_name,c.name as c_name from students as s inner join classes as c on s.cls_id = c.id;

-- 查询有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.
=select students.*,classes.name from students inner join classes on students.cls_id = classes.id;

-- 在以上的查询中，将班级名显示在第1列
=select classes.name,students.* from students inner join classes on students.cls_id = classes.id;

-- 查询有能够对应班级的学生以及班级信息, 按照班级名进行排序
=select classes.name,students.* from students inner join classes on students.cls_id = classes.id order by classes.name asc;

-- 当时同一个班级的时候，按照学生的id进行从小到大排序
=select classes.name,students.* from students inner join classes on students.cls_id = classes.id order by classes.name asc,students.id asc;


-- left join
-- 查询每位学生对应的班级信息
-- 左联,左边的表的数据都显示,不管在右边有没有找到对应的,如果没有找到就填Null
=select * from students left join classes on students.cls_id = classes.id;

-- 查询没有对应班级信息的学生
=select * from students left join classes on students.cls_id = classes.id where classes.id is null;

-- right join   on
-- 将数据表名字互换位置，用right join   on完成
=select * from classes  right join students on students.cls_id = classes.id;

13、子查询

-- 标量子查询: 子查询返回的结果是一个数据(一行一列)
-- 列子查询: 返回的结果是一列(一列多行)
-- 行子查询: 返回的结果是一行(一行多列)

-- 查询出高于平均身高的信息(height)

=select avg(height) from students;
=select * from students where height > 172.076923;
=select * from students where height > (select avg(height) from students);

-- 查询学生的班级号能够对应的学生名字
=select id from classes;
=select * from students where cls_id in(1,2);
=select * from students where cls_id in(select id from classes);

14、自联

-- 自已联接自己的表,注意要给表取别名
-- 数据操作前的准备

-- 查询一共有多少个省
=select count(*) from areas;
=select count(*) from areas where pid is not null;
=select count(*) from areas where pid is not null;

-- 查询省的名称为“山西省”的所有城市
=select aid from areas where atitle='山西省';
=select * from areas where pid = 140000;
=select * from areas where pid = (select aid from areas where atitle='山西省');
-- 查询市的名称为“广州市”的所有区县
=select * from areas where pid = (select aid from areas where atitle='广州市');
-- 自联,将areas取别名为a1和a2，两个表a1和a2连接查询
=select * from areas as a1 inner join areas as a2 on a1.pid = a2.aid where a2.atitle='北京市';

15、修改表结构

-- 1.创建 "商品种类表" -goods_cates
create table goods_cates(id int unsigned primary key auto_increment,name varchar(50) not null);

-- 2.创建 "商品品牌表" -goods_brands
create table goods_brands(id int unsigned primary key auto_increment,name varchar(50) not null);


-- 3.把查询出来的种类数据插入到"商品种类表" -goods_cates
insert into goods_cates(name) (select cate_name from goods group by cate_name);

-- 4.把查询出来的品牌数据插入到"商品品牌表" -goods_brands
insert into goods_brands(name) (select brand_name from goods group by brand_name);


-- 注意:通过特殊的update进行更新,两张表连接的时候 select * from 这个必须删除
-- 5.将goods表中的cates_name,修改成 goods_cates表中的 id 
update ( goods inner join goods_cates on goods.cate_name = goods_cates.name) set goods.cate_name = goods_cates.id;

-- 6.将goods表中的brand_name,修改成 goods_brands表中的 id
update (goods inner join goods_brands on goods.brand_name = goods_brands.name) set goods.brand_name = goods_brands.id;


-- 注意类型必须跟外键的主键类型一致
-- 7.修改goods表中cate_name和brand_name字段的名字和类型,alter table 表名 change 旧字段 新字段 类型
alter table goods change cate_name cate_id int unsigned;
alter table goods change brand_name brand_id int unsigned;

16、事务

-- 查看存储引擎，一般默认是InnoDB 类型
show engines;

-- 开启事务
begin;
-- 或者
start transaction;

-- 提交事务
commit;
-- 回滚事务
rollback;


-- 注意
-- MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启一个事务,那么每条sql语句都会被当作一个事务执行提交的操作
set autocommit = 0；
--  表示取消自动提交事务模式，需要手动执行commit完成事务的提交

17、索引

-- 索引
-- 加快数据库的查询速度
-- 查看表中的已有索引
-- show index from 表名；
show index from goods;

-- 索引的创建
-- 索引名不指定，默认使用字段名
-- alter table 表名 index 索引名（字段名）
alter table goods add index id_name(name);

-- 索引的删除
-- alter table 表名 drop index 索引名;
alter table goods drop index id_name;