2、DQL语言

2、DQL语言

DQL（Data Query Language）数据查询语言

2.1.基础查询

语法：select　查询列表　from　表名；

注:①起别名：select　查询列表　as   别名    from   表名；

     ②去重：select   distinct   查询列表   from  表名；

     ③着重号"  "用来区分关键字和字段

 

2.2.条件查询

语法：select　查询列表　from　表名

           where    筛选条件；

筛选条件：①按条件筛选，条件运算符>  <   !=   >=   <=

　　　　　②按逻辑表达式筛选，逻辑运算符  and(与)   or(或)   not(非) 

　　　　　③模糊查询  like    between and     in    is null     is not null

注：①%为通配符，表示任意多个字符，包含0个字符，通常与like 搭配使用；_也为通配符，表示任意单个字符

　　②安全等于<=>，用于判断是否等于，相当于=，可用于判断null值

 

例1： 查询工资大于12000的员工信息

select   *    from   employees

where   salary>12000;

 

例2：查询员工姓名包含字符a的员工信息

select   *    from   employees

where   last_name   like   '%a%';

 

例3：查询员工编号在100-120之间的员工信息

select   *    from   employees

where   employee_id   between   100   and   120;

 

例4：查询员工的工种编号为 IT、AD、SQL中的一个的员工名和工种编号

select   last_name, job_id   from   employees

where   job_id  in('IT','AD','SQL');

 

例5：查询没有奖金的员工名和奖金率

select    last_name，commision_pact   from   employees

where   commision_pact    is  null;

 

2.3.排序查询

语法：select　查询列表　from　表名

           where    筛选条件

　　　order    by     排序列表    asc/desc;

注:①asc表示升序，desc表示降序，不写默认asc升序

     ②order  by 一般放在最后，limit字句除外

     ③order  by 可支持单个字段、多个字段、表达式、函数、别名进行排序

     ④排序除了order  by 还有sort by,distribute by,cluster by

 

例6：查询员工的姓名、部门号、年薪，按年薪降序，按姓名升序

select　last_name,department_id,salary*12*(1+ifnull(commision_pact,0))  年薪

from　employees

order    by    年薪   desc，last_name   asc;

 

2.4常见函数

 字符函数：length()，表示获取字节的个数

　　　　　 concat()，表示拼接字符

　　　　　 upper()，lower()，表示转换大小写

　　　　　 substr()，表示截取字符串，索引从1开始

　　　　　 instr()，表示子串第一次出现的索引，如果找不到返回0

　　　　　 trim()，表示去前后空格，中间的不能去

　　　　     repalce()，表示替换

数学函数：round()，四舍五入

　　　　　ceil()，向上取整；floor(),向下取整

　　　　　truncate(数值，小数点后保留位数)，截断

　　　　　mod(被除数，除数)，取余

日期函数：now()，返回当前系统日期＋时间

　　　　　str_to_date()，将日期格式的字符转化为指定格式日期

　　　　　date_format()，将日期转化为字符

其他函数：versin()，版本号；database()，数据库

流程控制函数：if(条件表达式，真值，假值)

　　　　　　　case函数：语法一　　case　　要判断的表达式

　　　　　　　　　　　　　　　　　 when　　常量1　　then　　要显示的值1

　　　　　　　　　　　　　　　　　 ……

　　　　　　　　　　　　　　　　　 else　　要显示的值n

　　　　　　　　　　　　　　　　　 end;

　　　　　　　　　　　　语法二　　 case　　

　　　　　　　　　　　　　　　　　 when　　条件1　　then　　要显示的值1

　　　　　　　　　　　　　　　　　 ……

　　　　　　　　　　　　　　　　　 else　　要显示的值n

　　　　　　　　　　　　　　　　　 end　　case;

分组函数：sum()、avg()、max()、min()、count()

 

2.5分组查询

语法1：select　分组函数　from　表名

           where    筛选条件

　　　group    by    分组列表　　#分组前查询，分组列表可为多个字段

　　　order    by　　子句;

语法2：select　分组函数　from　表名

           where    筛选条件

　　　order    by　　子句

　　　having    by    分组列表　　#分组后查询;

 

2.6连接查询（多表查询）

SQL99标准的语法：select　查询列表　

　　　　　　　　　from　表1    别名

　　　　　　　　　连接类型     jion   表2     别名

　　　　　　　　　on    连接条件 

           　　　　　　[where    筛选条件];

分类：内连接、左外连接、右外连接、全外连接

　　　内连接：查询交集部分　　select　查询列表　

　　　　　　　　　　　　　　　from　表1    别名A

　　　　　　　　　　　　　　　inner     jion   表2     别名B

　　　　　　　　　　　　　　　on    A.key=B.key;

　　　左外连接：查询左边主表A　　select　查询列表　

　　　　　　　　　　　　　　　   　from　主表1    别名A

　　　　　　　　　　　　　　　　　left     jion   从表2     别名B

　　　　　　　　　　　　　　　　　on    A.key=B.key;

　　   右外连接：查询右边主表B　　select　查询列表　

　　　　　　　　　　　　　　　   　from　从表1    别名A

　　　　　　　　　　　　　　　　　right     jion   主表2     别名B

　　　　　　　　　　　　　　　　　on    B.key=A.key;

　　   全连接：查询表A和B　　          select　查询列表　

　　　　　　　　　　　　　　　   　from　表1    别名A

　　　　　　　　　　　　　　　　　full    jion   表2     别名B

　　　　　　　　　　　　　　　　　on    A.key=B.key;

 

2.7 子查询

含义：出现在其他语句的select查询语句，称为子查询或内查询

分类：select后面仅仅支持标量子查询

　　　from后面支持表子查询

　　　where或having后面支持标量子查询，列子查询，行子查询

　　　exists后面支持表子查询

 

例1：查询最低工资大于50号部门最低工资的部门id和最低工资

①查询50号部门最低工资

select   min(salary)   

from    employees

where  department_id=50;

②组合起来

select   department_id，min(salary)   

from    employees

group   by    department_id

having    min(salary)>(

select   min(salary)   

from    employees

where  department_id=50;

);

 

例2：查询每个部门的员工数

select   d.*,(

　　　　select   count(*)

　　　　from   employees   e

　　　　where   e.department_id=d.department_id;

)  员工数

from   department   d;

 

例3：查询每个部门的平均工资的等级

①查询每个部门的平均工资

select   avg(salary),department_id

from  employees 

group by  department_id;

②组合起来

select   ag_dep.*,g.grade_level

from(

select   avg(salary),department_id

from  employees 

group by  department_id;

)   ag_dep

inner jion  job_grades  g

on   ag_dep.ag  between   lowest_sal   and   highest_sal;

 

2.8分页查询

语法：select   查询列表

　　　from    表

　　　limit    offset,size;　　#offset为起始索引，从0开始；size为要显示的条目数，limit子句要放在查询语句最后

例：查询有奖金的员工信息，显示工资最高的前10名

select   *

from    employess

where   commison_pct  is not  null

order   by   salary   desc

limit  10;

 

2.9 联合查询

语法：查询语句1

　　　union

　　　查询语句2

　　　……

特点：要查询的结果来自多表；要求查询语句的列一致，类型和顺序一致；union默认去重，union all 可以包含重复项

例：查询中国用户男性信息和外国用户男性信息

select   c_id,c_name,c_sex

from    china

where    c_sex="男"

union

select   t_id,t_name,t_gender

from    tokoyo

where    t_gender="male";