MySQL查询命令行脚本

查询

• 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 
    (
        select
        cate_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 表会失败
• 创建外键时，一定要注意类型要相同，否则失败