命令行脚本"/>
MySQL查询命令行脚本
目录
- 查询
- 创建数据库、数据表
- 准备数据
- 条件
- 比较运算符
- 逻辑运算符
- 模糊查询
- 范围查询
- 空判断
- 优先级
- 排序
- 聚合
- 总数
- 最大值
- 最小值
- 求和
- 平均值
- 分组
- group by
- group by + group_concat()
- group by + 聚合函数
- group by + having
- group by + with rollup
- 分页
- 连接
- 自关联
- 子查询
- 标量子查询
- 列级子查询
- 行级子查询
- 子查询中特定关键字使用
- 总结
- SQL演练
- 准备数据
- SQL语句的强化
- 创建 "商品分类"表
- 同步表数据
- 创建 "商品品牌表" 表
- 同步数据
- 修改表结构
- ==外键==
- 数据库设计
- 创建"商品" 表,"商品分类" 表,"商品品牌" 表——前面已经创建
- 创建 "顾客" 表
- 创建 "订单" 表
- 创建 "订单详情" 表
- 说明
查询
- SQL 语句支持函数
- 单条语句内可通过回车隔开,不影响操作
- SELECT 语句都需要以
;
结尾 - 当语句中的数据库名称存在特殊字符无法正常处理时,使用 ` 将名称扩起即可处理
创建数据库、数据表
-- 创建数据库
create database python_test charset=utf8;-- 使用数据库
use python_test;-- students表
create table students(id int unsigned primary key auto_increment not null,name varchar(20) default '',age tinyint unsigned default 0,height decimal(5,2),gender enum('男', '女', '中性', '保密') default '保密',cls_id int unsigned default 0,is_delete bit default 0
);
准备数据
-- 向students表中插入数据
insert into students values
(0,'学生一',18,180.00,2,1,0),
(0,'学生二',18,180.00,2,2,1),
(0,'学生三',29,185.00,1,1,0),
(0,'学生四',59,175.00,1,2,1),
(0,'学生五',38,160.00,2,1,0),
(0,'学生六',28,150.00,4,2,1),
(0,'学生七',18,172.00,2,1,1),
(0,'学生八',36,NULL,1,1,0),
(0,'学生九',27,181.00,1,2,0),
(0,'学生十',25,166.00,2,2,0),
(0,'学生十一',33,162.00,3,3,1),
(0,'学生十二',12,180.00,2,4,0),
(0,'学生十三',12,170.00,1,4,0),
(0,'学生十四',34,176.00,2,5,0);-- 向classes表中插入数据
insert into classes values (0, "python_01期"), (0, "python_02期");
- 查询所有字段
select * from 表名;
eg:
select * from students;
- 查询指定字段
select 列1, 列2, ... from 表名;
eg:
select name from students;
- 通过 as 给字段起别名
select id as 序号, name as 名字, gender as 性别 from students;
- 通过 as 给表起别名
-- 如果是单表查询,可以省略表名
select id, name, gender from students;-- 表名.字段名
select students.id, students.name, students.gender from studnets;-- 使用as起别名
select s.id, s.name, s.gender from students as s;
- 消除重复行:在 select 后列前使用 distinct 可以消除重复的行
select distinct 列1, ... from 表名;
eg:
select distinct gender from students;
条件
- 使用 where 子句对表中的数据筛选,结果为 True 的记录会出现在结果集中
select * from 表名 where 条件;
eg:
select * from students where id=1;
- where 后面支持多种运算符进行条件的处理
- 比较运算符
- 逻辑运算符
- 模糊查询
- 范围查询
- 空判断
比较运算符
- 等于:=
大于:>
大于等于:>=
小于:<
小于等于:<=
不等于:!= - 示例:
-- 例1:查询编号大于3的学生
select * from students where id > 3;-- 例2:查询编号不大于4的学生
select * from students where id <= 4;-- 例3:查询姓名不是“学生五”的学生
select * from students where name != '学生五';-- 例4:查询没被删除的学生
select * from students where is_delete=0;
逻辑运算符
- and
or
not - 示例:
-- 例1:查询编号大于3的女同学
select * from students where id > 3 and gender=0;-- 例2:查询编号小于4或没被删除的学生
select * from students where id < 4 or is_delete=0;
模糊查询
- like
- % 表示任意多个任意字符
- _ 表示一个任意字符
- 示例:
-- 例1:查询姓李的学生
select * from students where name like '李%';-- 例2:查询姓李并且名是一个字的学生
select * from students where name like '李_';-- 例3:查询姓李或叫琪的学生
select * from students where name like '李%' or name like '%琪';
范围查询
- in 表示在一个非连续的范围内
- 示例:
-- 例1:查询编号是1或3或8的学生
select * from students where id in(1,3,8);
- between … and … 表示在一个连续的范围内
- 示例:
-- 例1:查询编号为3至8的学生
select * from students where id between 3 and 8;-- 例2:查询编号是3至8的男生
select * from students where (id between 3 and 8) and gender=1;
空判断
- 注意:null 与 ‘’ 不同
- 判空 is null
- 示例:
-- 例1:查询没有填写身高的学生
select * from students where height is null;
- 判非空 is not null
-- 例1:查询填写了身高的学生
select * from students where height is not null;-- 例2:查询填写了身高的男生
select * from students where height is not null and gender=1;
优先级
- 优先级由高到低的顺序:小括号,not,比较运算符,逻辑运算符
- and 比 or 先运算,如果同时出现并希望先算 or,需要结合()使用
- 如果先进行优先运算,使用()即可
排序
- 语法:
select * from 表名 order by 列1 asc|desc [,列2 asc|desc, ...]
- 说明:
- 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
- 默认按照列值从小到大排列( asc )
- asc 从小到大排列,即升序
- desc 从大到小排列,即降序
- 示例:
-- 例1:查询未删除男生信息,按学号降序
select * from students where gender=1 and is_delete=0 order by id desc;-- 例2:查询未删除学生信息,按名称升序
select * from students where is_delete=0 order by name;-- 例3:显示所有的学生信息,先按照年龄从大到小排序;当年龄相同时,按照身高从高到矮排序
select * from students where order by age desc, height desc;
聚合
总数
- count(*) 表示计算总行数,括号中无论写星还是字段名,结果相同
-- 查询学生总数
select count(*) from students;
最大值
- max(字段) 表示求此字段的最大值
-- 查询女生的编号的最大值
select max(id) from students where gender=2;
最小值
- min(列) 表示求此列的最小值
-- 查询未删除的学生的最小编号
select min(id) from students where is_delete=0;
求和
- sum(列) 表示求此列的和
-- 查询男生的总年龄
select sum(age) from students where gender=1;
平均值
- avg(列) 表示求此列的平均值
-- 查询未删除女生的编号的平均值
select avg(id) from students where is_delete=0 and gender=2;
分组
group by
- 含义:将查询结果按照一个或多个字段进行分组,字段值相同的为一组
- 可用于单个字段分组,也可用于多个字段分组
- 示例:
-- 查询学生按性别分组情况
select gender from students group by gender;-- 查询结果如下:
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 中性 |
| 保密 |
+--------+
group by + group_concat()
- group_concat(字段名) 可以作为一个输出字段来使用
- 在分组之后,根据分组结果,使用 group_concat(某字段名) 来放置每一组的某字段值的集合
- 示例:
-- 查询学生按性别分组情况并列出id
select gender, group_concat(id) from students group by gender;-- 查询结果如下:
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男 | 3,4,8,9,14 |
| 女 | 1,2,5,7,10,12,13 |
| 中性 | 11 |
| 保密 | 6 |
+--------+------------------+
group by + 聚合函数
- 根据统计出的每个分组的某字段值的集合,使用聚合函数来对这个值的集合做一些操作
- 示例:
-- 例1:统计不同性别的学生的年龄平均值
select gender, avg(age) from students group by gender;-- 查询结果如下:
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男 | 32.6000 |
| 女 | 23.2857 |
| 中性 | 33.0000 |
| 保密 | 28.0000 |
+--------+----------+-- 例2:统计不同性别的学生的个数
select gender, count(*) from students group by gender;-- 查询结果如下:
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
+--------+----------+
group by + having
- 用来分组查询后指定一些条件来输出查询结果
- 作用与 where 相同,但只能用于 group by
- 示例:
-- 查询学生按性别分组情况并选出大于两人的人数
select gender, count(*) from students group by gender having count(*)>2;-- 查询结果如下:
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
+--------+----------+
group by + with rollup
- 在最后新增一行,来记录当前列里所有记录的总和
-- 例1:查询学生按性别分组情况并获取分组总和以及总体情况
select gender,count(*) from students group by gender with rollup;-- 查询结果如下:
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
| NULL | 14 |
+--------+----------+-- 例2:查询学生按性别分组情况并获取具体年龄信息以及总体情况
select gender,group_concat(age) from students group by gender with rollup;-- 查询结果如下:
+--------+-------------------------------------------+
| gender | group_concat(age) |
+--------+-------------------------------------------+
| 男 | 29,59,36,27,12 |
| 女 | 18,18,38,18,25,12,34 |
| 中性 | 33 |
| 保密 | 28 |
| NULL | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+
分页
- 当数据量过大时,在一页中查看数据非常麻烦,需要进行分页显示
- 从 start 开始,获取 count 条数据
select * from 表名 limit start, count;
- 示例:
-- 查询前三行男生信息
select * from students where gender=1 limit 0,3;
- 分页算法:每页显示 m 条数据,当前显示第 n 页,求第 n 页的数据
- 求总页数的逻辑:
- 查询总条数 p1
- 使用 p1 除以 m 得到 p2
- 如果整除,则 p2 为总页数
- 如果不整除,则 p2+1 为总页数
- SQL语句如下:
select * from students where is_delete=0 limit (n-1)*m, m;
连接
当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回
MySQL支持三种类型的连接查询,分别为:
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
-
内连接查询( inner join ):查询的结果为两个表匹配到的数据
-
左连接查询( left join ):查询的结果为两个表匹配到的数据;左表特有的数据,对于右表中不存在的数据使用 null 填充
-
右连接查询( right join ):查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用 null 填充
将两个表调换顺序使用左连接即可实现右连接查询
-
示例:
-- 例1:使用内连接查询班级表与学生表
select * from students inner join classes on students.id = classes.id;-- 例2:查询学生姓名及班级名称
select s.name, c.name from students as s inner join classes as c on s.cls_id = c.id;
自关联
- 设计省信息的表结构 provinces
- id
- ptitle
- 设计市信息的表结构 citys
- id
- ctitle
- proid (表示城市所属的省,对应着 provinces 表的 id 值)
问题:是否可以将两个表合成一张呢?
思考:观察两张表发现,citys 表比 provinces 表多一个列 proid ,其它列的类型都是一样的
意义:存储的都是地区信息,而且每种信息的数据量有限,没必要增加一个新表,或者将来还要存储区、乡镇信息,都增加新表的开销太大
解决方案:
定义表areas,结构如下:
- id
- atitle
- pid
- 说明:
- 因为省没有所属的省份,所以可以填写为 null
- 城市所属的省份 pid,填写省所对应的编号 id
- 这就是自关联,表中的某一列,关联了这个表中的另外一列,但是它们的业务逻辑含义是不一样的,城市信息的 pid 引用的是省信息的 id
- 在这个表中,结构不变,可以添加区县、乡镇街道、村社区等信息
- 示例:
-- 创建areas表
create table areas(aid int primary key,atitle varchar(20),pid int
);-- 查询一共有多少个省
select count(*) from areas where pid is null;-- 例1:查询省的名称为“辽宁省”的所有城市
select city.* from areas as city
inner join areas as province on city.pid=province.aid
where province.atitle='辽宁省';-- 例2:查询市的名称为“沈阳市”的所有区
select dis.* from areas as dis
inner join areas as city on city.aid=dis.pid
where city.atitle='沈阳市';
子查询
- 主查询
主要查询的对象,第一条 select 语句 - 子查询
在一个 select 语句中,嵌入了另外一个 select 语句,那么被嵌入的 select 语句称之为子查询语句 - 主查询和子查询的关系
- 子查询是嵌入到主查询中
- 子查询是辅助主查询的,要么充当条件,要么充当数据源
- 子查询是可以独立存在的语句,是一条完整的 select 语句
- 子查询分类
- 标量子查询:子查询返回的结果是一个数据(一行一列)
- 列级子查询:返回的结果是一列(一列多行)
- 行级子查询:返回的结果是一行(一行多列)
标量子查询
- 查询大于平均年龄的学生
select * from students where age > (select avg(age) from students);
列级子查询
- 查询还有学生在班的所有班级名字
select name from classes where id in (select cls_id from students);
行级子查询
- 查找班级年龄最大,身高最高的学生
将多个字段合成一个行元素,在行级子查询中会使用到行元素
select * from students where (height,age) = (select max(height),max(age) from students);
子查询中特定关键字使用
- in 范围
主查询 where 条件 in (列子查询)
总结
- 执行顺序:
- from 表名
- where …
- group by …
- select distinct *
- having …
- order by …
- limit start, count
- 实际使用中,只是语句中某些部分的组合,而不是全部
- 完整的 select 语句
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start, count
SELECT select_expr [,select_expr,...] [ FROM tb_name[WHERE 条件判断][GROUP BY {col_name | postion} [ASC | DESC], ...] [HAVING WHERE 条件判断][ORDER BY {col_name|expr|postion} [ASC | DESC], ...][ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]
SQL演练
准备数据
- 创建数据表
-- 创建 "京东" 数据库
create database jing_dong charset=utf8;-- 使用 "京东" 数据库
use jing_dong;-- 创建一个商品goods数据表
create table goods(id int unsigned primary key auto_increment not null,name varchar(150) not null,cate_name varchar(40) not null,brand_name varchar(40) not null,price decimal(10,3) not null default 0,is_show bit not null default 1,is_saleoff bit not null default 0
);
- 插入数据
-- 向goods表中插入数据insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);
insert into goods values(0,'at7-7414lp 台式电脑 linux )','台式机','宏碁','3699',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
SQL语句的强化
- 查询类型 cate_name 为 ‘超级本’ 的商品名称、价格
select name, price from goods where cate_name = '超级本';
- 显示商品的种类
select cate_name from goods group by cate_name;
- 求所有电脑产品的平均价格,并且保留两位小数
select round(avg(price),2) as avg_price from goods;
- 显示每种商品的平均价格
select cate_name, avg(price) from goods group by cate_name;
- 查询每种类型的商品中的最贵价、最便宜价、平均价、数量
select cate_name, max(price), min(price), avg(price), count(*) from goods group by cate_name;
- 查询所有价格大于平均价格的商品,并且按价格降序排序
select id, name, price from goods
where price > (select round(avg(price),2) as avg_price from goods)
order by price desc;
- 查询每种类型中最贵的电脑信息
select * from goods
inner join (selectcate_name, max(price) as max_price, min(price) as min_price, avg(price) as avg_price, count(*) from goods group by cate_name) as goods_new_info
on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max_price;
创建 "商品分类"表
- 创建商品分类表
create table if not exists goods_cates(id int unsigned primary key auto_increment not null,name varchar(40) not null
);
- 查询 goods 表中商品的种类
select cate_name from goods group by cate_name;
- 将分组结果写入到 goods_cates 数据表
insert into goods_cates (name) select cate_name from goods group by cate_name;
同步表数据
- 通过 goods_cates 数据表来更新 goods 表
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
创建 “商品品牌表” 表
- 通过 create…select 语句来创建数据表并且同时写入记录,一步到位
-- 在创建数据表的时候一起插入数据
create table goods_brands (id int unsigned primary key auto_increment not null,name varchar(40) not null) select brand_name as name from goods group by brand_name;
- 注意:需要对 brand_name 用 as 起别名,否则 name 字段就没有值
同步数据
- 通过 goods_brands 数据表来更新 goods 数据表
update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;
修改表结构
- 查看 goods 的数据表结构,会发现 cate_name 和 brand_name 对应的类型为 varchar 但是存储的都是数字
desc goods;
- 通过 alter table 语句修改表结构
alter table goods
change cate_name cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;
外键
- 分别在 goods_cates 和 goods_brands 表中插入记录
insert into goods_cates(name) values ('路由器'),('交换机'),('网卡');
insert into goods_brands(name) values ('海尔'),('清华同方'),('神舟');
- 在 goods 数据表中写入任意记录
insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4,'1849');
- 查询所有商品的详细信息 (通过内连接)
select g.id,g.name,c.name,b.name,g.price from goods as g
inner join goods_cates as c on g.cate_id=c.id
inner join goods_brands as b on g.brand_id=b.id;
- 查询所有商品的详细信息 (通过左连接)
select g.id,g.name,c.name,b.name,g.price from goods as g
left join goods_cates as c on g.cate_id=c.id
left join goods_brands as b on g.brand_id=b.id;
为了防止无效信息的插入,就是在插入前判断类型或者品牌名称是否存在,可以使用外键来解决
- 外键约束:对数据的有效性进行验证
- 关键字:foreign key,只有 innodb 数据库引擎支持外键约束
- 对于已经存在的数据表,更新外键约束
-- 给brand_id添加外键约束成功
alter table goods add foreign key (brand_id) references goods_brands(id);
-- 给cate_id添加外键失败
-- 会出现1452错误
-- 错误原因:已经添加了一个不存在的cate_id值12,因此需要先删除
alter table goods add foreign key (cate_id) references goods_cates(id);
- 在创建数据表的时候就设置外键约束
注意: goods 中的 cate_id 的类型一定要和 goods_cates 表中的 id 类型一致
create table goods(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),foreign key(brand_id) references goods_brands(id)
);
- 取消外键约束
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
- 在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率
数据库设计
创建"商品" 表,“商品分类” 表,“商品品牌” 表——前面已经创建
-- "商品" 表
create table goods(id int unsigned 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),foreign key(brand_id) references goods_brands(id)
);
-- "商品分类" 表
create table goods_cates(id int unsigned primary key auto_increment not null,name varchar(40) not null
);
-- "商品品牌" 表
create table goods_brands (id int unsigned primary key auto_increment not null,name varchar(40) not null
);
创建 “顾客” 表
create table customer(id int unsigned auto_increment primary key not null,name varchar(30) not null,addr varchar(100),tel varchar(11) not null
);
创建 “订单” 表
create table orders(id int unsigned auto_increment primary key not null,order_date_time datetime not null,customer_id int unsigned,foreign key(customer_id) references customer(id)
);
创建 “订单详情” 表
create table order_detail(id int unsigned auto_increment primary key not null,order_id int unsigned not null,goods_id int unsigned not null,quantity tinyint unsigned not null,foreign key(order_id) references orders(id),foreign key(goods_id) references goods(id)
);
说明
- 以上表的创建是有顺序的,如果 goods 表中的外键约束用的是 goods_cates 或者是 goods_brands,那么就应该先创建这两个表,否则创建 goods 表会失败
- 创建外键时,一定要注意类型要相同,否则失败
更多推荐
MySQL查询命令行脚本
发布评论