MySQL--查询

一.DQL语言（数据查询语言，查）

distinct, current_timestemp, getdate(),substring(),left(),right(),len(),rtrim()消除右部空格,ltrim(),concat()拼接?

（一）.基础查询

select 查询列表 from 表名；

特点：查询列表可以是：表中的字段、常量值、表达式、函数..

           查询结果是虚拟的列表

查询前，最好加上改变的表名。例如在最上方加上USE test；

1.查询单个字段  select id from test；

2.查询多个字段                                                                     // id可以加着重号（``）也可不加

3.查询所有字段  select * from test；                                     // *表示所有

4.查询常量值      select 10；

                           select  'sala';

5.查询表达式      select 12*12；

6.查询函数         select version()；

7.起别名             select televition as TV;                               // as可换成空格

8.去重                select distinct age from test;                      // distinct把一样的只出一遍

9.+号的作用       select '100'+100；（结果为200）

                          select 'hurry'+100；（结果为100）

                          select null+10；（结果为null）

10.select concat('name','id') as 编号;                              // concat拼接起来

---

（二）.条件查询

select 查询列表 from 表名 where 筛选条件；

分类：按条件表达式（=、<>···）、逻辑表达式（and、or、not）筛选

           模糊查询（like、between and、in、is null）

1.按条件表达式筛选  select * from test where age > 20;

2.按逻辑表达式筛选  select * from test where age > 20 and sexy = '女'；

3.模糊查询               

(like)                          select * from test where name like '%云%'

    // &代表任意n个字符；

    //_代表任意单个字符

escape<转义符>‘\’

 特别：查询第二个字符为_的员工 select name from test where name like '_\_%';

                                                  或 select name from test where name like '_$_%' escape '$';

(between and)           select * from teat where age between 1 and 17;

    //包含了临界值，前后顺序有关系

(in)                             select * from test where name in ('李白','杜甫')；

    //不支持通配符，即_，%在括号里面就是_,%，而不能代表其他的字符

(is null)                      select * from test where sexy is null；

                                 select * from test where sexy is not null；

4.安全等于(不建议)

(<=>)                         select * from test where sexy <=> null;

---

（三）.排序查询

select 查询列表 from 表名 (where 筛选条件 可不写) order by 排序列表 asc|desc（升|降）

 注意：如果asc或desc不写，默认是升序。

            order by 子句一般在查询语句的最后面，limit子句除外。

1.select * from test order by age desc;

   select * from test order by age asc;

2.select * from test where id>90 order by age asc;

3.select * ,salary*12*(1+ifnull(rate,0)) as 年薪

                                           from test order by 年薪 desc; 

    // ifnull(rate,0)如果rate是null，则结果为0

4.select length(name) as 长度,age from test order by 长度 desc;   

    // length(name)是求取name的字节个数

5.select * from test order by age asc, salary desc;                  

    //先按年龄升序，再按薪资降序(即在年龄相同的时候按薪资大小降序)

---

（四）.常见函数的查询(单行函数、分组函数)

select 函数名(实参列表) from 表名；
//弱国函数名后的括号中用到了表中的内容，则from 表名要写；如果没用到可以不写

分类：单行函数、分组函数（又叫统计函数、聚合函数、组函数）

单行函数

1.字符函数

#求参数值的字节个数length

length('sara');            4

length('张大爷')        12

#拼接字符串concat

concat(last_name,'_',first_name); 

#改变大小写upper,lower

upper('sara');

lower('SARA');

#截取字符串substr,substring，字符长度

select substr('今天又是学习的一天',4) as output;         学习的一天

//SQL语言索引从1开始

select substr('今天又是学习的一天',1,2) as output;      今天

#返回第一次出现时的首索引instr，字符长度

select instr('我好想好想吃东西','吃东西') as output;      6

select instr('我好想好想吃东西','吃个屁') as output;      0

#消除前后面连续出现的空格或其他内容trim

select trim('    张翠山     ') as output;

select trim('a' from 'aaa张aa翠aaa山aaa') as output;    张aa翠aaa山

select trim('aa' from 'aaa张aa翠aaa山aa') as output;    a张aa翠aaa山

#用指定的字符左填充指定长度lpad，字符长度

select lpad('胖大海',5,'*') as output;        **胖大海

select lpad('胖大海',2,'*') as output;        大海

#用指定的字符右填充指定长度rpad，字符长度

select lpad('胖大海',6,'ab') as output;        胖大海aba

select lpad('胖大海',2,'ab') as output;        胖大

#所有都替换replace

select replace('今天要好好学习','今天','每天都') as output;      每天都要好好学习

 例：姓中首字符大写，其他字符小写，然后用_拼接
select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) as output;

2.数学函数

#四舍五入round

select round(1.65);       2

select round(-1.65);      -2

select round(-1.65635,2);      -1.66

#向上取整ceil

select ceil(1.01);         2

select ceil(-1.01);        -1

#向下取整floor

selectfloor(-0.3)          -1

#截断truncate

select truncate(1.656785,1);      1.6

#取余mod

celect mod(10,6);       4

celect mod(-10,6);      -4

3.日期函数

#now返回当前系统具体日期加时间

select now();

#curdate返回日期

select curdate();

#curtime返回时间

select curtime();

#year,month...获取指定的年、月、日、小时、分钟、秒，中英文都可以

select year(now()) as 年;

select year('2002-10-5');

select monthname(now());     英文的现在的月份

#str_to_date将日期格式的字符转化成指定格式的日期

select str_to_date('10-5-2002','%m-%d-%Y') as 日期;     2002-10-05

#date_format将日期转化成字符

select date_format('2002/10/5','%Y年%m月%d日') as 日期;    2002年10月05日

#current_timastamp获取系统日期

select current_timestamp

#getdate()?????????

#datepart()返回时间的具体部分

datepart函数介绍

#dateadd()

#dateoff()

#datediff()

4.其他函数

#select version();

#select database();

#select user();

5.流程控制函数

#if函数(相当于三元运算符)

select if(10>9,'大','小');

#case函数(两种情况)

/*

case 要判度的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n；
end

*/(相当于switch)

select salary,id

case id

when 10 then salary*1.3

when 40 then salary*4.2

else salary

end

年终奖

from test;

/*

case 

when 条件1 then 要显示的值1或语句1 

when 条件2 then 要显示的值2或语句2

...

else 要显示的值n或语句n

end

*/(相当于多重if语句)

分组函数（sum,avg,min,max,count）

#sum求和

null记录在内

select sum(salary) from test;

#avg求平均值

null不记录在内

select avg(salary) from test;

#min求最小值

select min(salary) from test;

#max求最大值

select max(salary) from test;

    //日期、字符可以比较大小

#count计算个数

select count(salary) from test;

select count(*) from test;              //只要那一行不全为null就+1

select count(1) from test;              

    //这几个分组函数都忽略null值

    //可以和distinct搭配使用去重

    //和分组行数一同查询的字段要求是group by后的字段

---

（五）.分组查询

select 分组函数，列(要求出现在group by后面) 
from 表名 
[where 筛选条件] 
group by 分组的列表
[having 筛选条件]
[order by 排序列表]

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

特点：分组前筛选，筛选原始表，用where在group by前

           分组后筛选，筛选分组后的表，用having在group by后

           分组函数做条件肯定是放在having语句中

例：

1.查询每个工种的最高工资

select max(salary),job_id from test group by job_id;

2.查询每个位置上的部门个数

select count(*),location_id from test group by location_id;

3.查询邮箱中包含a字符的，每个部门的平均工资

select avg(salary),dep_id from test where email like '%a%' group by dep_id;

4.查询哪个部门的员工个数>2

select count(*),dep_id from test group by dep_id having count(*)>2;

5.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select max(salary),job_id

from test

where rate is not null                           // 分组前

group by job_id

having max(salary)>12000;                 // 分组后

6.查询每个年龄每种性别的平均工资，并且按平均工资的高低显示(按多个字段查询)

select avg(salary) avg,age,sexy from test group by age,sexy order by avg desc;     

    // 两个都相同才能合并，交换效过一样

---

（六）.连接查询

错误：笛卡尔乘积现象

分类：按年代分(sq192标准，sql199标准)推荐199

           按功能分        内连接(等值连接、非等值连接、自连接)，

                                 外连接(左外连接、右外连接、全外连接)，

                                 交叉连接

注意：sql199不支持全外连接，sql192只支持内连接

为表起别名

如果在from处为表起了别名，在别的地方就得使用别名

sql192标准

a.等值连接

查询对应情侣名

select girlname,boyname from boys,beauty

where beauty.boyfriend_id=boys.id;

查询有奖金的员工名和部门名

select name,dep_name,rate from employee e,dep d

where e.dep_id=d.dep_id and e.rate is not null;

查询每个城市的部门 个数

select count(*) 个数,city from dep d,location l

where d.locatiob_id=l.location_id group by city;

查询每个工种的工种名和员工的个数，并按照员工的个数降序

select job_name,count(*) from employee e,job j

where e.job_id=j.job_id group by e.job_name order by count(*) desc;

#查询员工名、部门名和所在的城市

select e.name,d.name,city from employee e,dep d,location l

where e.dep_id=d.dep_id and d.location_id=l.location_id;

b.非等值连接

查询员工的工资和工资级别

select salary,grade_level from employee e,grade g

where salary between g.low and g.high;

c.自连接

查询员工名和对应上级的名字

select e.employee_id,e.name, m.employee_id,m.name

from employee e,employee m

where e.manager_id=m.employee_id;

sql199标准

select 查询列表
from 表1 别名 
[连接类型] join 表2 别名 
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]

连接类型

内连接(inner)、外连接((左外left/left outer)、(右外right)、(全外full))、交叉连接(cross)

注意：两表连接的顺序不影响

1.内连接(inner)可以省略

select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件；

a.等值连接

#查询员工名、部门名

select employee_name,dep_name

from employee e inner join employee m

on e.dep_id=m.dep_id;

#查询名字中包含e 的员工名和工种名

select employee_name,job_name

from employee e inner join job j

on e.job_id=j.job_id

where e.employee_name like '%e%';

#查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数排序

select count(*),dep_name

from employee e inner join dep d

on e.dep_id=d.dep_id

group by dep_name

having count(*)>3

order by count(*) desc;

#查询员工名、部门名、工种名，并按部门名降序

select employee_name,dep_name,job_name from employee e

inner join dep d on e.dep_id=d.dep_id

inner join job j on e.job_id=j.job_id

order by dep_name desc;

b.非等值连接

#查询员工的工资级别

select salary,grade_level from employee e

(inner) join job_grades g on e.salary between g.lowest and highest;

#查询工资级别的个数>20的个数，并且按照工资级别降序

select count(*),grade_level from employee e

(inner) join job_grades g on e.salary between g.lowest and highest

group by grade_level

having count(*)>20

order by grade_level desc;

c.自连接

#查询姓名中包含字符k的员工的名字和其上级的名字

select e.last_name,m.lastname 

from employee e

join employee m

on e.manager_id=m.employee_id

where e.lastname like '%k%';

2.外连接

应用场景：用于查询一个表有，另一个表没有

特点：

1.外连接的查询结果为主表中的所有记录。如果从表中有与之匹配的，显示匹配的值；否则显示null。外连接查询结果=内连接结果+主表中有而从表中没有的记录。

2.左外连接中，left左边的是主表。右外连接中，right右边的是主表。

左（右）连接

# 查询男朋友不在男神表里的女神名

select b.name,bo.*

from beauty b

left outer join boys bo

on b.boyfriend_id = bo.id

where bo.id is null;
//最好选从表中的主键列

#查询哪个部门没有员工

select d.*,e.employee_id

from employees e

right outer join departments d

on e.department_id=d.department_id

where e.employee_id is null;

全外连接（MySQL5/7不支持全外连接，但是可以使用左外连接+union+右外连接实现）

全外连接=内连接结果+表1中有但表2中没有  的+表2中有单表1中没有的

（没有对应的则显示null）

#查询女神的男朋友

SELECT b.*, bo.*

FROM beauty b

FULL OUTER JOIN boys bo

ON b.boyfriend_id = bo.id

全外连接的实现

SELECT * FROM t1

LEFT JOIN t2 ON t1.id = t2.id

UNION

SELECT * FROM t1

RIGHT JOIN t2 ON t1.id = t2.id

交叉连接

把两张表做笛卡尔积，n*m对应

SELECT b.*, bo.*

from beauty b

cross join boys bo;

                  内连接                                   左外连接                                    右外连接

 

---

（七）.子查询

含义：出现在其他语句中的select语句，成为子查询活内查询；内部嵌套其他的select语句的查     询，称为外查询或主查询。

---

（八）.分页查询

（九）.联合查询

二.DML语言（数据操作语言，增删改）

三.DDL语言（数据定义语言，库和表）

四.TCL语言（事务控制语言）

五.视图的讲解

六.存储过程和函数

七.流程控制结果

看菜鸟教程适当添加一些知识点

单引号表示字符串，双引号表示变量