MySql（二） DQL语言学习

一、基础查询 SELECT

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

1 查询列表可以是表中的字段、常量值、表达式、函数

2 查询的结果是一个虚拟的表格

查询表字段：SELECT 字段1,字段2 FROM 表名;

查询常量值：SELECT 1000;

查询表达式 :  SELECT 100*9;

查询函数     :  SELECT CURDATE();

取别名 AS

方式一(通过AS关键字） ：SELECT CURDATE()  AS "当前时间";    

 方式二（字段后+空格+别名) : SELECT  CURDATE()  "当前时间";

去重 DISTINCT

SELECT DISTINCT 字段 FROM 表名;

+号的作用

mysql中+号仅仅一个功能：作为运算符

select  100+90；  两个操作数都为数值，则做直接相加   （结果：190）

select  ’100‘+90； 如果一个操作数为字符型，则试图转换成数值型 转换成功则继续相加（结果：190）

select  ’ok‘+90；  试图将’ok‘转换成数值型，转换失败，则将字符串转换成0后相加（结果：90）

select null + 90   只要其中一方为null，则结果肯定是null （结果：null）

多个列作为一个字段输出 CONCAT

SELECT CONCAT(字段1,字段2) FROM 表名;

二、条件查询

select * from 表名 where 筛选条件

筛选条件： 条件运算符 : >,<、=、!= 、 <> 、 >= 、 <=

                    逻辑运算符： and  、 or 、  not

                     模糊查询： like 、 between and、 in 、 is null

ilke 

通配符

• % 表示任意多个字符
• _表示任意单个字符

SELECT 字段名 FROM 表名 WHERE 条件字段  like '%s%'；  #查询当某个字段中含有s的数据 

SELECT 字段名 FROM 表名 WHERE 条件字段  like '_s%' #查询当某个字段中第二个字符是s的数据

between and

• 查询结果包含临界值

SELECT 字段名  FROM 表名  WHERE 字段名  between 下限 and 上限 ； 

SELECT salary FROM employees  WHERE salary between 7700 and 8800；#查询salary大于等于7700并且小于等于8800 的数据

in

SELECT 字段名  FROM 表名  WHERE 字段名 IN （’A‘，’B‘） ；

SELECT first_name FROM employees  WHERE first_name in ('Ismael','Daniel');#查询first_name是Ismael、Daniel的数据

is null  、is not null

在mysql中，对于null的判断不能用= 号和！= 号 ，只能通过 is 和is not 关键字来判断

SELECT 字段名  FROM 表名  WHERE 字段名 IS NULL ；

SELECT commission_pct FROM employees  WHERE commission_pct  is null；#查询当commission_pct为null时的数据

SELECT commission_pct FROM employees  WHERE commission_pct  is not  null；#查询当commission_pct不为null时的数据

<=>

<=>是安全等于。 既可以判断null值 也可以判断普通字符

SELECT 字段名  FROM 表名  WHERE 字段名 <=> null ；

SELECT 字段名  FROM 表名  WHERE 字段名 <=> 'A'；

三、排序查询

select  查询列表  from 表  where 筛选条件  order by 排序内容 【asc|desc】

排序内容：列表字段，表达式，别名，函数，多个字段

【asc|desc】：asc 升序

                        desc 降序

按照字段排序

SELECT salary FROM employees  order by salary ； #查询所有薪资按照降序顺序排序

按照表达式排序

SELECT salary* 12  FROM employees  order by salary*12；

按照别名排序

SELECT salary AS 薪资 FROM employees  order by 薪资 ；

按照函数排序

SELECT first_name  FROM employees  order by  LENGTH(first_name)；

按照多个字段排序

SELECT *  FROM employees  order by salary,employee_id；#先按照salary排序，salary相同的再按照employee_id排序

【asc|desc】

SELECT salary FROM employees  order by salary asc ； #结果按照 salary 进行升序排序

SELECT salary FROM employees  order by salary desc；#结果按照 salary 进行降序排序

四、分组查询

select  查询列表  from 表  where 筛选条件  group by 分组内容 

简单分组

SELECT department_id, COUNT(*) FROM  employees GROUP BY department_id

分组后筛选

SELECT department_id, COUNT(*) FROM  employees GROUP BY department_id HAVING COUNT(*) >2;#只有当分组后数量大于2才被筛选出来

按照函数分组

SELECT FIRST_name, COUNT(*) FROM  employees GROUP BY LENGTH(FIRST_name)  HAVING COUNT(*) >2

按照别名分组，having也可以按照别名筛选

SELECT FIRST_name ,LENGTH(FIRST_name) len, COUNT(*) c FROM  employees GROUP BY len  HAVING c >2

按照多个字段分组，当分组字段相同时为一组

SELECT department_id,job_id, COUNT(*) FROM  employees GROUP BY department_id,job_id 

五、连接查询

连接查询是指连接多个表同时进行查询，在连接查询时需要设置有效的连接条件，否则就会出现笛卡尔集

内连接

SELECT    查询列表  from 表1  inner join  表2   on  连接条件

SELECT last_name ,department_name FROM employees e  INNER JOIN  departments d  on  e.department_id = d.department_id

注：上面这种方法和

SELECT last_name ,department_name FROM employees e ， departments d  where  e.department_id = d.department_id

这种方式的等值连接效果是一样的，包扩执行原理也是一样的,只是不同版本的不同写法（sql92和sql99)

左外连接

SELECT    查询列表  from 表1  left join  表2   on  连接条件

查询结果中含有左表中的全部数据，而右表没有的则为null

右外连接

SELECT    查询列表  from 表1  right join  表2   on  连接条件

查询结果中含有右表中的全部数据，而右表没有的则为null

全外连接

SELECT    查询列表  from 表1  full join  表2   on  连接条件

查询结果中含有两个表中的所有数据，如果没有数据的则用null表示

交叉连接

SELECT last_name ,department_name FROM employees e  CROSS JOIN  departments d  

实际上就是笛卡尔集

六、子查询

出现在其他语句中的select语句，成为子查询或者内查询，外部的查询语句成为主查询或者外查询

分类：

按子查询出现的位置：

select 后 :支持标量子查询

from 后：支持表子查询
 where或者having后：支持标量子查询，列子查询，行子查询

exists后面：支持表子查询

按照结果集的行列数不同：

标量子查询（结果只有一行一列）

列子查询（结果集只有一列多行）

行子查询（结果集只有一行多列或者多行多列）

表子查询（结果集一般为多行多列)

1、where后面或者having后面的子查询

标量子查询（单行子查询）：一般搭配着单行操作符使用  > ,< , >=, <=, =, <>

• 案例1：谁的工资比abel高？

① 查询abel的工资   select salary from employee where name = ’able‘ 

②  查询员工信息 满足salary>①的结果  select * from employee where salary > (①)

• 案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名和薪资

① 查询141号员工的job_id select job_id from employee where employee_id=141

② 查出143号员工的薪资 select salary from employee where employee_id =143

③ 查出题干  select name,salary from employee where job_id=(①) and salary >(②)

• 案例3：查询最低工资大于50号部门最低工资的部门id和其最低工资

①查出50号部门的最低工资 ：select MIN(salary) from employee where department_id =50

②查题干 select  department_id ,MIN(salary) from employee  group by department_id having  MIN(salary)>①

 

列子查询（多行子查询）:  一般搭配多行操作符使用  in，any，some，all

案例1：返回其他部门中比job_id为’TI_PROG'部门任意工资低的员工号、姓名、salary

①找出TI_PROG部门最低工资:select  distinct salary from  employee where job_id='TI_PROG'

②查题干 select employee_id,name,salary from employee where salary < any(①) and 

 department_id != 'TI_PROG'

行子查询（多列多行子查询）

案例1：查出员工编号最小并且工资最高的员工信息

select * from employee where (employee_id,salary) = (select MIN(employee_id) ,MAX(salary) from employee;

2、放在select后面的子查询

3、放在from后面的子查询

4、放在exists后面的子查询（相关子查询）

七、联合查询

应用场景：要查询的结果来自多个表，但是多个表之间没有直接关系，但查询的信息一致时可以使用。

特点：联合查询多个表查询的列数一致

           要求多条查询语句的每一列的类型和顺序要一致

            联合查询的结果列名以第一条查询语句的列名为准

             会自动去重

 

案例：查询外国青年和中国青年的集合（在不同的表中）

（自动去重）

select id，cname，csex from t_china UNION

select t_id,t_name,t_Gender from t_nochina 

（不去重）

select id，cname，csex from t_china UNION ALL

select t_id,t_name,t_Gender from t_nochina