常见的MySQL常用命令
文章目录
前言
MySQL 是一个 关系型数据库 管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一。
一、MySQL 是什么？
MySQL 是一种关系型数据库 管理系统 ，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL作为网站数据库。
二、MySQL 基础命令（sql语句最后需要有分号结尾）
1、连接数据库
mysql -uroot -pmysql
– 不显示密码
mysql -uroot -p
- 1
- 2
- 3
- 4
2、退出数据库
quit
exit
ctrl + d
- 1
- 2
- 3
3、显示数据库版本
select version();
- 1
4、显示时间
select now();
- 1
5、导入sql文件数据
source 具体地址/areas.sql;
– windows中文报错问题解决，改变编码模式
chcp 65001
- 1
- 2
- 3
- 4
三、数据库的操作命令
1、查看所有数据库
show databases;
- 1
2、查看当前使用的数据库
select database();
- 1
3、创建数据库
create database 数据库名 charset=utf8;
- 1
4、查看创建数据库的语句
show create database 数据库名;
- 1
5、使用数据库
use 数据库名;
- 1
6、删除数据库
drop database 数据库名;
- 1
四、数据库表的操作命令
1、查看当前数据库中所有的数据表
show tables;
- 1
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、外键的使用
-- 外键是一种约束,约束了插入或者更新必须在外键的表中存在这个数据<span class="token comment">-- 1.表已经存在后添加外键的命令</span> <span class="token comment">-- alter table 主表名 add foreign key (主表的外键) references 外键表(外键主键);</span>
alter table goods add foreign key (cate_id) references goods_cates(id);
<span class="token comment">-- 2.在创建主表表的同时设置外键 (注意:goods_cates和goods_brands两个外键表必须先创建，之后再创建goods(主表))</span>
<span class="token comment">-- foreign key (主表的外键) references 外键表(外键表的主键)</span>
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)
);
<span class="token comment">-- 3.如何取消外键约束</span>
<span class="token comment">-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称</span>
<span class="token comment">-- show create table 表名;</span>
show create table goods;
<span class="token comment">-- 获取名称之后就可以根据名称来删除外键约束</span>
<span class="token comment">-- alter table 表名 drop foreign key 外键名;</span>
<span class="token comment">-- CONSTRAINT `goods_ibfk_1`</span>
<span class="token comment">--删除外键的功能</span>
alter table goods drop foreign key goods_ibfk_1;
–删除外键的标识
alter table goods drop key cate_id;
4、查看表的创建语句
show create table 表名;
- 1
5、查看表结构
desc 表名;
- 1
6、删除表
drop table 表名;
- 1
7、修改表的字段
-- 添加字段
alter table 表名 add 列名 类型;
-- 修改字段，不重命名版
alter table 表名 modify 列名 类型及约束;
-- 修改字段，重命名版
alter table 表名 change 原名 新名 类型及约束;
-- 删除字段
alter table 表名 drop 列名;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
8、修改表名
rename table 原表名 to 新表名;
- 1
五、表中数据的基本操作
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),...;
- 1
- 2
- 3
- 4
- 5
- 6
2、删除表中数据
delete from 表名
– 条件删除
delete from 表名 where 条件
- 1
- 2
- 3
- 4
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 ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
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;
- 1
- 2
- 3
- 4
6、消除重复行(查性别)
-- distinct 字段
select distinct sex from 表名；
- 1
- 2
7、起别名
-- 使用as为列指定别名（这个只针对当前这一次查询显示）
select 字段[as 别名] , 字段[as 别名] from 表名;
-- 使用as为表指定别名
select 别名.字段 .... from 表名 as 别名;
- 1
- 2
- 3
- 4
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
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);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
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;
扫一扫
9万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
