MySQL 基础知识快学-查询

        废话不多说，这篇文章主要先容实用且口试很容易考到基础知识，得当初学者或者口试前复习用。增编削查之查询篇。
1、查询语句结构先容

select   
  distinct    ------去重
  字段1， ------要展示的字段
  字段2，
  字段3
from  表名1   ---------表名1 as 表名2，从哪个表查询并能重新命名
where  条件          ----筛选条件
group by 汇总字段       -----分组汇总
having  汇总字段条件  ----汇总后的条件
order by 字段  desc    -----排序
limit  行数                    -----限制输出行数
一样平常的语句包罗上面的结构字段

2、常用函数

1）substr(字段1，n1,n2)  n1位置开始，截取n2个字符
例：substr('ABCDS',2)='BCDS'
2）trim(字段1)  去除左右空格
3）concat(字段1，字段2，字段3)：链接字符串
4）current_date():获取当前时间
5）date_add('YYYY-MM-DD',n):开始日期加n天
6）last_day('YYYY-MM-DD'):日期所属月份的最后一天日期。
7）if
if（条件，TRUE,FALSE）
如：if（1=1，‘x’,'y'）='x'
8）case when
case
   when 条件1  then 值1   -----满足条件1，字段值为：值1
   when 条件2  then 值2    -----满足条件2，字段值为：值3
......
else 值N  end              ----------都不满足条件，字段值为：值N


3、运算符：

 =  <>  >=  AND  OR   IN  NOT IN
BETWEEN AND   IS NULL  IS NOT NULL
LIKE    RLIKE (正则匹配)
4、 INSERT…VALUES语句

通过这个语句为表中添加数据
INSERT VALUES 的语法格式为：
   INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];
  

use dewi_practice

insert into Employee

values(002,'王子行','男','26')

开始insert 报错，
解决mysql插入中笔墨符报错的问题ERROR 1366 (HY000): Incorrect string value: '\xE5\xB0\x8F\xE6\x98\x8E' for column
解决办法将上面的字符集改为utf-8

5、查询小例子-汉语


SELECT score_a.`分数` from score_a;#如下表，表字段是汉语，必要加``

  

`` 用这个符号是不是可以了
delete from score_a where `分数`=81;  也用符号``。。


6、连表查询

1）union &union all

建表
insert into score_A values(1,"张三",'数学',81);
insert into score_A values(2,"李四",'数学',75);
insert into score_A values(3,"王五",'地理',50);
insert into score_A values(4,"张三",'语文',62);

insert into score_b values(1,"李四",'语文',76);
insert into score_b values(2,"李四",'地理',80);
insert into score_b values(3,"王五",'数学',61);
insert into score_b values(4,"陈留",'数学',91);

------ score_A

 --------- score_b

union  和 union all  前者过滤重复 的，后者不外滤
SELECT * FROM score_b
UNION ALL
SELECT * FROM score_a;

SELECT b.`序号`,b.`姓名`,b.`课程`,b.`分数`
FROM score_b as b
UNION ALL
SELECT a.`序号`,a.`姓名`,a.`课程`,a.`分数`
FROM score_a as a;

效果：

增加一个重复的试试：
insert into score_b values(1,"张三",'数学',81);
连表union，去除重复项
SELECT b.`序号`,b.`姓名`,b.`课程`,b.`分数`
FROM score_b as b
UNION
SELECT a.`序号`,a.`姓名`,a.`课程`,a.`分数`
FROM score_a as a;

查看效果：

表b

姓名张三是重复的但是没有去重，是完全重复才会去除
效果如下

2）left  join  on

左连接，左表全部展示，
左表中的第一行与右表的每一行依次对比，符合条件的保留下来，
左表中的第二行与右表的每一行依次对比，符合条件的保留下来，
......

右表有值的展示，（对应左表的大概多条），右表轮询后无对应的值时显示null（一条）
若a表

c 表，插入数据
insert into score_c values(2,15,'二班','女');

使用left  join 语句
select * FROM score_a as a LEFT JOIN score_c c ON
a.`序号` = c.`序号`;

如上的例子现实中一样平常不用。

但是如果从A表中剔除C表的内容（注意应该是is null ，开始写成=null）
select a.`序号`,a.`姓名`,a.`课程`,a.`分数`
FROM score_a as a
LEFT JOIN score_c c
 ON
a.`序号` = c.`序号`
 where c.`序号`is NULL;

3）inner  join  on

内连接，只是取交织存在的值
左表中的第一行与右表的每一行依次对比，符合条件的保留下来，
左表中的第二行与右表的每一行依次对比，符合条件的保留下来，
......
不符合条件的过滤。
例如

a 表

C表

select a.`序号`,a.`姓名`,a.`课程`,a.`分数`
FROM score_a as a
inner JOIN score_c c
 ON
a.`序号` = c.`序号`;


上面的语句等价于下面
select a.`序号`,a.`姓名`,a.`课程`,a.`分数`
FROM score_a as a
left JOIN score_c c
 ON
a.`序号` = c.`序号`
where c.`序号` is not null;



执行效果

4、数据汇总查询

insert INTO score_a
VALUES(5,"张三",'地理',99);  ---增加一条

SELECT * FROM score_a;
SELECT a.`课程` from score_a as a
GROUP BY a.`课程`;
----效果为按课程分组，展示课程

a. 计算出每个课程的总分并显示出课程和总分（以分数汇总显示出来）
SELECT a.`课程`,sum(a.`分数`)as "分数汇总" 
from score_a as a
GROUP BY a.`课程`;

效果如下：

b.筛选出平均分大于70的课程

第一个错误的：记着group by 搭配的是having
SELECT a.`课程`,avg(a.`分数`)as "平均分" 
from score_a as a
GROUP BY a.`课程`
where avg(a.`分数`) > 70;

SELECT a.`课程`,avg(a.`分数`)as "平均分" 
from score_a as a
GROUP BY a.`课程`
HAVING avg(a.`分数`) >= 70;//注意要用avg(a.`分数`)而非"平均分"

5、排序原理及应用

order by :默认升序，如果降序desc

SELECT a.`课程`,avg(a.`分数`)as "平均分" 
from score_a as a
GROUP BY a.`课程`
ORDER BY avg(a.`分数`);
a.实现每个课程的平均分升序排列

降序：
SELECT a.`课程`,avg(a.`分数`)as "平均分" 
from score_a as a
GROUP BY a.`课程`
ORDER BY avg(a.`分数`) DESC;

b.将结果表中的姓名按升序排序，且相同姓名的课程按分数从高到低排序


SELECT * 
from score_a as a
ORDER BY a.`姓名`,a.`分数`DESC;//先按姓名，再按分数

c.在结果中，获取同一学生分数最高的课程，并按姓名，课程，分数举行输出

SELECT * FROM score_a;
SELECT a.`姓名`, a.`课程`,max(a.`分数`) as `分数`
from score_a as a
group by a.`姓名`;

还可以使用分组排序
以字段1分组，字段2排序，并且有序号可以作为字段
row_number() over(partition by 字段1 order by 字段2)
avg(字段2) over(PARTITION by 字段1)  ---以字段1分组，计算字段的平均值，最大值，等

例子：
select cust_id,sex,income,prov,age,
       avg(age) over(PARTITION by prov) as avg_age
from cust1;
如下：增加一列为分组排序，按名字分组，默认升序排序，
注意MySQL8.0以上才有
SELECT  score_a.`序号`,score_a.`姓名`,score_a.`课程`,score_a.`分数`,
row_number()over(PARTITION by score_a.`姓名` ORDER BY score_a.`分数`) as `分组排序`
from score_a ;

卸载原来的重新安装，
重新试试

SELECT  score_a.`序号`,score_a.`姓名`,score_a.`课程`,score_a.`分数`,
row_number()over(PARTITION by score_a.`姓名` ORDER BY score_a.`分数` desc ) as `分组排序`
from score_a ;

效果：

然后：在结果中，获取同一学生分数最高的课程，并按姓名，课程，分数举行输出

SELECT T1.`姓名`,T1.`课程`,T1.`分数`
FROM
(SELECT  score_a.`序号`,score_a.`姓名`,score_a.`课程`,score_a.`分数`,
row_number()over(PARTITION by score_a.`姓名` ORDER BY score_a.`分数` desc) as `分组排序`
from score_a) as T1
where T1.`分组排序`=1;

-----思路，先分组排序，然后取1号，一号为最大的。

6、综合练习

案例1

表名：
INSERT INTO sales VALUES(1,'2017',60);
INSERT INTO sales VALUES(1,'2018',50);
INSERT INTO sales VALUES(1,'2019',80);
INSERT INTO sales VALUES(2,'2017',90);
INSERT INTO sales VALUES(2,'2018',100);
INSERT INTO sales VALUES(2,'2019',120);
INSERT INTO sales VALUES(3,'2017',60);
INSERT INTO sales VALUES(3,'2018',70);
INSERT INTO sales VALUES(3,'2019',80);

把他改成表sales_b

INSERT INTO sales_b VALUES(1,60,50,80,63.33);
INSERT INTO sales_b VALUES(2,90,100,120,103.33);
INSERT INTO sales_b VALUES(3,60,70,80,70);

思路：先看看如何转换已往

         

 select
a.DEP_ID,          
a.YEAR,
a.SALES AS SALE,
a.SALES AS SALE_2017,
a.SALES AS SALE_2018,
a.SALES AS SALE_2019,
a.SALES AS AVG_SALES
FROM sales a;    -----语句这样，增加字段

接着变

select a.DEP_ID,
a.YEAR,
a.SALES AS SALE,
CASE WHEN a.YEAR='2017' THEN a.SALES ELSE 0 END AS SALE_2017,
CASE WHEN a.YEAR='2018' THEN a.SALES ELSE 0 END AS SALE_2018,
CASE WHEN a.YEAR='2019' THEN a.SALES ELSE 0 END AS SALE_2019,
a.SALES AS AVG_SALES
FROM sales a;

接着变，取出最大值作为每一年度的销售值，按季度分群，求平均值

select a.DEP_ID,
max(CASE WHEN a.YEAR='2017' THEN a.SALES ELSE 0 END) AS SALE_2017,
max(CASE WHEN a.YEAR='2018' THEN a.SALES ELSE 0 END) AS SALE_2018,
max(CASE WHEN a.YEAR='2019' THEN a.SALES ELSE 0 END) AS SALE_2019,
avg(a.SALES) AS AVG_SALES
FROM sales a
GROUP BY a.DEP_ID;
注意：聚合函数必要和group by 一起使用

以上是最常用的一些基本知识，背面有时机不停深入举一些范例例子分析。谢谢！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。