mariadb的查询流程图

mariadb的查询流程图

mariadb的select语句,mariadbselect语句

mariadb的询问流程图

澳门金沙国际 1

 

select语句的从句分析各种:from(过滤表)–>where(过滤行)–>group
by(分组)–>having(分组过滤)–>order by(排序)–

>select(选拔字段)–>limit(查询范围)–>最终结出

 

DISTINCT: 数据去重

SQL_CACHE: 显式内定期存款款和储蓄查询结果于缓存之中

SQL_NO_CACHE: 显式查询结果不予缓存

show global variables like ‘%query%’;

query_cache_type             | ON    表示缓存开启

query_cache_size             | 0     表示缓存空间大小,如若为0则不缓存

query_cache_type的值为’DEMAND’时,显式钦定SQL_CACHE的SELECT语句才会缓存;别的均反对缓存

缓存并不会缓存全数查询结果,例如select now();就不会缓存

 

WHERE子句:指明过滤条件以达成“选拔”的功效

算术操作符:+, -, *, /, %

比较操作符:=, !=, <>, <=>, >, >=, <, <=

BETWEEN min_num AND max_num

IN (element1, element2, …)

IS NULL

IS NOT NULL

LIKE:

  %: 任意长度的任意字符

  _:任意单个字符

RLIKE

逻辑操作符:  NOT,AND,ORubicon

GROUP:依据钦点的尺度把询问结果实行“分组”以用于做“聚合”运算:avg(),
max(), min(), count(), sum()

HAVING: 对分组聚合运算后的结果钦赐过滤条件

O普拉多DESportage BY: 依据钦点的字段对查询结果实行排序:升序ASC    降序:DESC

LIMIT [[offset,]row_count]MySQL基本命令。:对查询的结果开始展览输骑行数数量限制

 

例如:

select name,age from students where age/2=11;

澳门金沙国际 2

select name,age from students where age+30>50;

 

select distinct gender from students;

澳门金沙国际 3

 

 

select name as stuname from students;

澳门金沙国际 4

 

 

select name,classid from students where classid is null;

 

select avg(age),gender from students group by gender;

澳门金沙国际 5

 

 

select avg(age) as ages,gender from students group by gender having
ages>20;

澳门金沙国际 6

 

 

select count(stuid) as NO,classid from students group by classid;

澳门金沙国际 7

 

select count(stuid) as NO,classid from students group by classid having
NO>2;

 

select name,age from students order by age limit 10,10;
 (第⑦个象征偏移十三个,第③个代表取拾个)

 

多表查询:

  交叉连接:笛Carl乘积(最消功耗源的一种查询)  例如:select * from
students,teachers; 如果students有20行,teachers也有20行,则显示400行

  内连接:

    等值连接:让表之间的字段以“等值”建立连接关系;

    不等值连接

    自然连接

    自连接

  外连接:

    左外连接  例如:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

    右外接连  例如:FROM tb1 奥德赛IGHT JOIN tb2 ON tb1.col=tb2.col

 

等值连接:

澳门金沙国际 8

 

 

select s.name as studentname,t.name as teachername from students as
s,teachers as t where s.teacherid=t.tid;(那几个够复杂)

澳门金沙国际 9

 

 

select s.name,c.class from students as s,classes as c where
s.classid=c.classid;

澳门金沙国际 10

 

 

左外连接:

select s.name,c.class from students as s left join classes as c on
s.classid=c.classid;

澳门金沙国际 11

 

以左表作为条件

 

select s.name,c.class from students as s right join classes as c on
s.classid=c.classid;

澳门金沙国际 12

以右表为条件

 

子查询:在查询语句嵌套着询问语句,基于某语句的查询结果再度开始展览的查询
(mariadb对子查询优化不够,提出一般景观不利用)

select name,age from students where age>(select avg(age) from
students);

select name,age from students where age in (select age from teachers);

 

select s.ages,s.classid from (select avg(age) as ages,classid from
students where classid is not null group by classid) as s where
s.ages>30;

澳门金沙国际 13

 

联手查询:UNION

select name,age from students union select name,age from teachers; 

  

 

mariadb的询问流程图
select语句的从句分析种种:from(过滤表)–where(过滤行)–group
by(分组)–having(分组过滤)…

服务端命令SQL

style=”font-family: 微软雅黑; font-size: medium;”>在数据库系统中,SQL语句不区分轻重缓急写(建议用小写)
SQL语句可单行或多草书写,以“;”结尾
关键词不可能跨多行或简写
用空格和缩进来增长语句的可读性
子句普通位于独立行,便于编辑,提升可读性
注释:
SQL标准:
/*申明内容*/ 多行注释
— 注释内容 单行注释,注意有空格
MySQL注释:
#

澳门金沙国际 14

澳门金沙国际 15

SQL优化

查询时,能不要*就不用*,尽量写全字段名
大多数意况总是功用远大于子查询
多表连接时,尽量小表驱动大表,即小表 join 大表
在相对级分页时利用limit
对于平日利用的询问,能够开启缓存
多使用explain和profile分析查询语句
查看慢查询日志,找出执行时间长的sql语句优化

 

sql查询:单表查询和多表查询

 

两张表合并:横联、纵向合并

纵向合并:两张表挑出一致的字段进行联合(注意顺序)

 

 

 

范例

SQL查询范例

壹 、给表的字段名添加外号 select stuid as
学生编号,name 姓名,gender 性别 from students;

② 、查询年龄大于40的 select * from
students where age >40;

叁 、查找年龄大于20低于40的 select * from
students where age < 40 and age > 20; select * from students
where age between 20 and 40;(那种是包蕴)

四 、查询以姓名以X开头的 select * from
students where name like ‘x%’;

五 、查找字段中为空值得音讯 select * from
students where classid is null;

⑥ 、查找字段值不为空得新闻 select * from
students where classid is not null;

柒 、查找报1,2,6班得学生音讯 select *
from students where classid in (1,2,6);

八 、查找年龄,并去掉重复得年龄 select
distinct age from students;

⑨ 、查询年龄,去掉重复并排序 select
distinct age from students order by age;(暗中认可正序) select distinct age
from students order by age desc;(倒叙)

⑩ 、总计students表总共有多少行 select
count(*) from students;

11、计算age年龄的总数 select sum(age)
from students;

1贰 、统计年龄最大的 select max(age) from
students;

1叁 、总计男女平均年龄 select
gender,avg(age) from students group by gender;

1肆 、分别总计每班的女孩子男一生均战绩(gender性别classid班级age战绩)
select gender,classid,avg(age) from students group by gender, classid;

1⑤ 、基于上条再总括女孩子的最新年纪 select
gender,max(age) from students group by gender having gender=’f’;
备注:分完组后再条件用having不能够用where

1⑥ 、遵照课程总计没课考试最棒的实绩select courseid,max(score) as 最佳成绩 from scores group by courseid;

1柒 、取排序的前3名 select age from
students order by age desc limit 3;

1⑧ 、基于排序跳过1个显示三个 select age
from students order by age desc limit 2,3;

select语句的从句分析种种:from(过滤表)–>where(过滤行)–>group
by(分组)–>having(分组过滤)–>order by(排序)–

select语句的从句分析各类:from(过滤表)–>where(过滤行)–>group
by(分组)–>having(分组过滤)–>order by(排序)–

多表

1、纵向合并两张表
select stuid as id,name,age,gender from students union select tid,name,age,gender from teachers;
2、基于上条查询 查找age字段大于50的信息
select * from (select stuid as id,name,age,gender from students union select tid,name,age,gender from teachers)as b  where b.age >50;
备注:利用了子查询

子查询:

查找比平均年龄大的信息
select name,age from students where age >(select avg(age) from students);

接力链接

两张表交叉链接组合
select * from students cross join teachers;

内连接

取两张表的交集实现查找出学生对应的老师
select s.name as 学生name,t.name as 老师name from students as s inner join teachers as t on s.teacherid=t.tid;
备注:因为两种表有相同的字段,为了群分开给它加别名as,

左外链接

两张表A 和 B ,
取A表和B表的与A表相关的部分,A加B的一部分
select stuid,s.name,s.age,s.gender,classid,teacherid,tid,t.name,t.age,t.gender from students as s left join teachers as t on s.teacherid=t.tid;

 

有那般二个表emp

店铺人士音信,即对应的决策者–(leaderid领导编号)

id name leaderid
1 xiaoming null
2 wanger 1
3 zhangsan 2
4 lisi 3

明日有那般八个须求,查询每种职员对应的带头人士是哪个人

把emp表当作两张表来处理,自链接

 select e.name as emp,l.name as leader from emp as e left outer join emp as l on e.leaderid=1.id;

 

 

>select(选拔字段)–>limit(查询范围)–>最后结出

>select(选用字段)–>limit(查询范围)–>最后结出

 

 

DISTINCT: 数据去重

DISTINCT: 数据去重

SQL_CACHE:
显式内定期存款储查询结果于缓存之中

SQL_CACHE:
显式钦定期存款款和储蓄查询结果于缓存之中

SQL_NO_CACHE:
显式查询结果不予缓存

SQL_NO_CACHE:
显式查询结果不予缓存

show global variables like
‘%query%’;

show global variables like
‘%query%’;

query_cache_type             | ON  
 表示缓存开启

query_cache_type             | ON  
 表示缓存开启

query_cache_size             | 0    
表示缓存空间大小,如若为0则不缓存

query_cache_size             | 0    
表示缓存空间大小,如果为0则不缓存

query_cache_type的值为’DEMAND’时,显式钦定SQL_CACHE的SELECT语句才会缓存;其它均反对缓存

query_cache_type的值为’DEMAND’时,显式内定SQL_CACHE的SELECT语句才会缓存;别的均不予缓存

缓存并不会缓存全体查询结果,例如select
now();就不会缓存

缓存并不会缓存全体查询结果,例如select
now();就不会缓存

 

 

WHERE子句:指明过滤条件以贯彻“选取”的功效

WHERE子句:指明过滤条件以完结“选取”的作用

算术操作符:+, -, *, /, %

算术操作符:+, -, *, /, %

正如操作符:=, !=, <>, <=>,
>, >=, <, <=

正如操作符:=, 澳门金沙国际 ,!=, <>, <=>,
>, >=, <, <=

BETWEEN min_num AND max_num

BETWEEN min_num AND max_num

IN (element1, element2, …)

IN (element1, element2, …)

IS NULL

IS NULL

IS NOT NULL

IS NOT NULL

LIKE:

LIKE:

  %: 任意长度的私下字符

  %: 任意长度的人身自由字符

  _:任意单个字符

  _:任意单个字符

RLIKE

RLIKE

逻辑操作符:  NOT,AND,O酷威

逻辑操作符:  NOT,AND,O福特Explorer

GROUP:依据内定的规范把询问结果进行“分组”以用来做“聚合”运算:avg(),
max(), min(), count(), sum()

GROUP:依照钦命的规格把询问结果进行“分组”以用来做“聚合”运算:avg(),
max(), min(), count(), sum()

HAVING:
对分组聚合运算后的结果内定过滤条件

HAVING:
对分组聚合运算后的结果钦定过滤条件

O宝马7系DE揽胜极光 BY:
依据钦定的字段对查询结果开始展览排序:升序ASC    降序:DESC

ORubiconDE路虎极光 BY:
依照钦点的字段对查询结果开始展览排序:升序ASC    降序:DESC

LIMIT
[[offset,]row_count]:对查询的结果进行输出游数数量限制

LIMIT
[[offset,]row_count]:对查询的结果开始展览输骑行数数量限制

 

 

例如:

例如:

select name,age
from students where age/2=11;

select name,age
from students where age/2=11;

澳门金沙国际 16

澳门金沙国际 17

select name,age
from students where age+30>50;

select name,age
from students where age+30>50;

 

 

select distinct
gender from students;

select distinct
gender from students;

澳门金沙国际 18

澳门金沙国际 19

 

 

 

 

select name as stuname from
students;

select name as stuname from
students;

澳门金沙国际 20

澳门金沙国际 21

 

 

 

 

select name,classid from students where
classid is null;

select name,classid from students where
classid is null;

 

 

select avg(age),gender from students
group by gender;

select avg(age),gender from students
group by gender;

澳门金沙国际 22

澳门金沙国际 23

 

 

 

 

select avg(age)
as ages,gender from students group by gender having ages>20;

select avg(age)
as ages,gender from students group by gender having ages>20;

澳门金沙国际 24

澳门金沙国际 25

 

 

 

 

select count(stuid) as NO,classid from
students group by classid;

select count(stuid) as NO,classid from
students group by classid;

澳门金沙国际 26

澳门金沙国际 27

 

 

select count(stuid) as NO,classid from
students group by classid having NO>2;

select count(stuid) as NO,classid from
students group by classid having NO>2;

 

 

select name,age from students order by
age limit 10,10;  (第三个象征偏移拾贰个,第三个代表取10个)

select name,age from students order by
age limit 10,10;  (第三个代表偏移拾一个,第四个代表取13个)

 

 

多表查询:

多表查询:

  交叉连接:笛Carl乘积(最消耗电源的一种查询)  例如:select
* from students,teachers;
如果students有20行,teachers也有20行,则显示400行

  交叉连接:笛卡尔乘积(最消功耗源的一种查询)  例如:select
* from students,teachers;
如果students有20行,teachers也有20行,则显示400行

  内连接:

  内连接:

    等值连接:让表之间的字段以“等值”建立连接关系;

    等值连接:让表之间的字段以“等值”建立连接关系;

    不等值连接

    不等值连接

    自然连接

    自然连接

    自连接

    自连接

  外连接:

  外连接:

    左外连接  例如:FROM tb1 LEFT
JOIN tb2 ON tb1.col=tb2.col

    左外连接  例如:FROM tb1 LEFT
JOIN tb2 ON tb1.col=tb2.col

    右外接连  例如:FROM tb1 KoleosIGHT
JOIN tb2 ON tb1.col=tb2.col

    右外接连  例如:FROM tb1 HighlanderIGHT
JOIN tb2 ON tb1.col=tb2.col

 

 

等值连接:

等值连接:

澳门金沙国际 28

澳门金沙国际 29

 

 

 

 

select s.name as studentname,t.name as
teachername from students as s,teachers as t where
s.teacherid=t.tid;(那些够复杂)

select s.name as studentname,t.name as
teachername from students as s,teachers as t where
s.teacherid=t.tid;(那几个够复杂)

澳门金沙国际 30

澳门金沙国际 31

 

 

 

 

select s.name,c.class from students as
s,classes as c where s.classid=c.classid;

select s.name,c.class from students as
s,classes as c where s.classid=c.classid;

澳门金沙国际 32

澳门金沙国际 33

 

 

 

 

左外连接:

左外连接:

select s.name,c.class from students as s
left join classes as c on s.classid=c.classid;

select s.name,c.class from students as s
left join classes as c on s.classid=c.classid;

澳门金沙国际 34

澳门金沙国际 35

 以左表作为条件

 以左表作为标准

 

 

select s.name,c.class from students as s
right join classes as c on s.classid=c.classid;

select s.name,c.class from students as s
right join classes as c on s.classid=c.classid;

澳门金沙国际 36

澳门金沙国际 37

以右表为准绳

以右表为规范

 

 

子查询:在查询语句嵌套着询问语句,基于某语句的询问结果再一次进行的查询
(mariadb对子查询优化不够,提出一般意况不应用)

子查询:在查询语句嵌套着询问语句,基于某语句的询问结果再一次进行的查询
(mariadb对子查询优化不够,建议一般情状不应用)

select name,age from students where
age>(select avg(age) from students);

select name,age from students where
age>(select avg(age) from students);

select name,age from students where age
in (select age from teachers);

select name,age from students where age
in (select age from teachers);

 

 

select s.ages,s.classid from (select
avg(age) as ages,classid from students where classid is not null group
by classid) as s where s.ages>30;

select s.ages,s.classid from (select
avg(age) as ages,classid from students where classid is not null group
by classid) as s where s.ages>30;

澳门金沙国际 38

澳门金沙国际 39

 

 

同台查询:UNION

协助实行查询:UNION

select name,age from students union
select name,age from teachers; 

select name,age from students union
select name,age from teachers; 

  

  

 

 

相关文章