mysql学习笔记(1)_DQL(Data Query Language)

╰+哭是因爲堅強的太久メ 2023-07-07 08:18 22阅读 0赞

***\#\#DQL(data query language)数据库查询语言***

*  ***基础查询  ***
 *  ***条件查询***
 *  ***排序查询 (order by)***
 *  ***常用函数***
 *  ***分组查询(group by)***
 *  ***多表查询***
 *  ***子查询***
 *  ***分页查询(limit)***
 *  ***联合查询(union)***

--------------------

***\#\#DQL(data query language)数据库查询语言***  
\#\#（1）***基础查询***  
/\*\*  
语法：***select 查询列表  
           from 表名***

1.  查询列表可以是：表中的字段，函数，常量值，表达式
2.  查询的结果是一个虚拟的表格
3.  **注意：MySQL中的‘+’只有运算符的作用：**如果其中一方为字符例如select '123'+100，会试图将字符转换为数字，在进行运算，这里为123+100=223
4.   如果转换失败例如select '哈哈'+123,会将其变为0再进行运算，这里结果为0+123=123
5.  此外还需注意null与任何数做运算都为null
6.  \*/

**\#1.查询表中的单个字段**  
select job from t\_employee;

**\#2.查询表中的多个字段**  
select job,name from t\_employee;

**\#3.查询表中的所有字段**  
select id,name,job,mgr from t\_employee;  
select \* from t\_employee;

**\#4.查询常量值**  
select 'jack';  
select 100;

**\#5.查询表达式**  
select 5\*6;

**\#6.查询函数(这里是查询当前版本）**  
select version();

**\#7起别名(如别名中含特殊符号如空格，\#号等，需要加引号）**  
select 5\*6 result;  
select 5\*6 as result;  
select 5\*6 'h h\#';

**\#8.查询去重（distinct)**  
select distinct mgr from t\_employee;

**\#9.concat号的作用，即将两个字段显示在一个字段里**  
select concat(id,name) as 姓名 from t\_employee;

**\#10.显示当前表结构**  
desc t\_employee;

**\#11,查询的列为空时可以进行的操作**  
select ifnull(mgr,1111) as mr from t\_employee;

--------------------

\#\#DQL(data query language)数据库查询语言  
\#\#（***2）条件查询***  
/\*\*  
语法：***select 查询列表  
           from 表名  
            where 筛选条件***

分类：1、按条件表达式筛选  
        条件运算符：> < =  !=  <>(不等于）  >=  <=  
          
      2、按逻辑表达式筛选  
      逻辑运算符：and  or  not(&& || !)  
        
      3、模糊查询  
      like  
      between and  
      in  
      is null  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***条件表达式案例***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
**\#1.查询学生分数大于90的**  
select \* from t\_student where score>90;

**\#2.查询学生分数小于90的**  
select \* from t\_student where score<90;

**\#3.查询学生分数等于90的**  
select \* from t\_student where score<90;

**\#4.查询学生分数不等于90的**  
select \* from t\_student where score!=90;  
select \* from t\_student where score<>90;

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***逻辑表达式案例***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
**\#1.查询学生分数在60到90的学生姓名**  
select name from t\_student where score>60 and score < 90;

**\#2.查询学生分数不在60到90的学生姓名**  
select name from t\_student where not (score between 60 and 90);  
select name from t\_student where score < 60 or score>90;

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***模糊查询案例(like与通配符 % , \_等配合使用***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
**\#1.查询学生中名字带'里'的学生姓名**  
select name from t\_student where name like '%里%';

**\#2.查询学生中第一个字母为a,第三个字母为g的学生信息**  
select \* from t\_student where name like 'a\_g%';

**\#3.查询学生姓名中第二字母为\_的学生信息，/为转义字符**  
select \* from  t\_student where name like '\_\\\_%';

**\#4.查询学生分数在60到90的学生姓名**  
select name from t\_student where score between 60 and 90;

**\#5.查询分数为90或者100或者60的学生信息(使用in比or使用更简洁）**  
select \* from t\_student where score in (90,100,75);

**\#6.查询学生密码为null的学生信息**  
select \* from t\_student where password is null;  
select \* from t\_student where password <=> null;

\#<=>既可以判断是否为null,也可以判断普通数值  
\#is null 仅仅能判断是否为null

--------------------

\#\#DQL(data query language)数据库查询语言  
\#\#***(3)排序查询***  
/\*\*  
语法：  
       ** select 查询列表  
        from 表  
        【where 筛选条件】  
        order by 排序列表【asc|desc】**

\*\*/  
**\#1.根据学生分数由高到低排序**  
select \* from t\_student order by score desc;

**\#2.根据学生分数由低到高排序**  
select \* from t\_student order by score asc;

**\#3.如果不写desc或asc，默认是asc（升序）**  
select \* from t\_student order by score;

**\#4.按年龄的一半升序查询【按表达式排序】**  
select id,name,age/2 半龄  
from t\_student  
order by age/2 asc;

**\#5.按年龄的一半升序查询【按别名排序】**  
select id,name,age/2 半龄  
from t\_student  
order by 半龄 asc;

**\#6.按姓名的长度对学生信息升序查询【按函数排序】**  
select id,name,length(name) 姓名长度 from t\_student order by 姓名长度 asc;

**\#7.按姓名的长度对学生信息升序排序同时对其相同的姓名的长度按id进行升序排序【对多个字段排序】**  
select id,name,length(name) 姓名长度 from t\_student order by 姓名长度 asc,id asc;

--------------------

**\#\#DQL(data query language)数据库查询语言**  
\#\#***(4)常见函数***  
/\*\*  
概念：  
类似于java里的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。  
好处：1.隐藏了实现细节 3.提高代码的复用性  
调用：select 函数名（实参列表） 【from 表】  
***分类：  
    1.单行函数，如concat(),length(),ifnull()等  
    2.分组函数，功能作统计使用，又称为聚合函数。***  
\*\*/

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#***字符函数***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
**\#1.length,获取参数值的字节个数**  
select length('hello');  
select length('hello世界');

**\#2.concat,拼接字符**  
select concat(name,'\_',id) from t\_student;  
select concat(name,id) from t\_student;

**\#3.upper,lower,即大写字符和小写字符**  
select upper('hello');  
select lower('WORLD');

**\#4.substr,substring截取字符串，索引从1开始**  
select substr('黑夜就是我的面纱',3) out\_put;\#从索引为3的位置上开始截取  
select substr('黑夜就是我的面纱',1,2) out\_put;\#从索引是1开始往后截取两个字符

**\#5.instr,返回出现的索引，如果找不到返回0**  
select instr('自立自主','自') result;

**\#6.trim 去前后空格,也可以指定去除那种字符**  
select trim('     lele ') result;  
select trim('a' from'aaaaadjiasjdksdjksaaaaaaaaaaa') as result;

**\#7.lpad指定字符实现左填充**  
select lpad('克列',6,'a');

**\#8.rpad指定字符实现右填充**  
select rpad('克隆',6,'b');

**\#9.replace替换**  
select replace('乌鸦坐个大飞机乌鸦','乌鸦','斑鸠') as result;

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***数学函数***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

**\#1.round(四舍五入）**  
select round(-1.55);  
select round(1.567,2);\#表示小数点后保留两位

**\#2.ceil向上取整**  
select ceil(1.22);

**\#3.floor向下取整**  
select floor(1.22);

**\#4.truncate 截断(表示保留几位小数）**  
select truncate(1.88,1);

**\#mod , 取余mod(a,b)->a-a/b\*b**  
select mod(10,3);  
select 10%3;

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#***日期函数***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

**\#1.now 返回当前系统日期+时间**  
select now();

**\#2.curdate 返回当前系统日期**  
select curdate();

**\#3.curtime 返回当前系统时间**  
select curtime();

**\#可以获取指定部分，年(%Y或%y)，月(%m或%c)，日(%d)，时(%H(24小时制)或%h（12小时制）)，分(%i)，秒(%s)**  
select year(now());  
select minute(now());  
select year('2018-2-2');

**\#4.str\_to\_date,将str通过指定的格式转化为日期**  
select str\_to\_date('1999-10-20','%Y-%c-%d') da;

**\#5.date\_format,将日期格式转化为str**  
select date\_format(now(),'%Y年%m月%d日') curday;

--------------------

***\#\#DQL(data query language)数据库查询语言***  
\#\#***(5)分组查询***  
/\*\*  
    语法：  
       * **select 分组函数，列（要求出现在group by的后面）  
        from 表  
       【where 筛选条件】  
        group by 分组的列表  
       【order by 字句】***  
         
       特点：1.分组查询的筛选分为两类：  
         
           *** （1）分组前筛选  原始表where(在group by 前）  
            （2）分组后筛选  分组后的结果集having(在group by 后）***  
            分组函数做筛选一定是放在having语句之后  
            能用分组前筛选尽量用分组前筛选  
            2.group by 语句支持单个字段分组，也支持多个字段分组，也可以是表达式或函数  
            3.也可以添加排序（order by放在最后）  
              
\*\*/

**\#1.查询学生中相同年龄中最高分数的学生信息**  
select max(score),age from t\_student group by age;

**\#2.查询学生中相同年龄中且名字中带'a'的最高分数的学生信息**  
select max(score),age from t\_student  where name like '%a%' group by age ;

**\#3.查询学生中相同年龄大于的个数大于2的学生年龄信息**  
select count(\*),age  
from t\_student  
group by age  
having count(\*)>2;

**\#4.按多个字段分组**  
**\#查询每种年龄每种分数的id之和学生信息**  
select sum(id),age,score  
from t\_student  
group by age,score;

**\#5.添加排序  
\#查询每种年龄每种分数的id之和学生信息，并根据id之和降序**  
select sum(id) sid,age,score  
from t\_student  
group by age,score  
order by sid desc;

**\#6.查询学生最高分数与最低分数的差距**  
select max(score)-min(score) difference  
from t\_student;

--------------------

***\#\#DQL(data query language)数据库查询语言***  
\#\#***(6)多表查询（连接查询）***  
/\*\*  
含义：查询的字段涉及多个

分类：按年代来分：  
            sql92标准：仅仅支持内连接；  
            sql99标准：支持内连接+外连接（左外+右外）+交叉连接  
       按功能分类：  
            内连接：  
                等值连接  
                非等值连接  
                自连接  
            外连接：  
                左外连接  
                右外连接  
                全外连接  
            交叉连接：                          
\*\*/  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
   *** \#sql92语法***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***等值连接***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
/\*\*  
    1.多表的等值连接的结果是多表的交集部分  
    2.n表连接，至少需要n-1个连接条件  
    3.多表的顺序没有要求  
    4.一般需要为表起别名  
\*\*/  
**\#1.查询球员以及他所属的球队**  
select p.name , t.name  
from t\_player p,t\_team t  
where p.tid = t.id;

**\#1.查询球员以及他所属的球队以及球队id(起别名）**  
/\*\*  
如果对表起了别名，则不能再用表名限定字段  
\*/  
select p.name , t.name, t.id  
from t\_player p,t\_team t  
where p.tid = t.id;

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***非等值连接***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

**\#1.查询员工的工资和工资级别**  
/\*  
select salary,grade\_level  
from employee e,job\_grades g  
where salary between g.low\_grades and g.hightest\_grades  
\*/

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***自连接***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
**\#1.在同一张表里查询员工名和员工上级的名称**  
select t.name,e.name  
from t\_employee t,t\_employee e  
where t.mgr = e.id;

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
   *** \#sql99语法***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
/\*\*  
语法：  
      * **select 查询列表  
        from 表1 别名 【连接类型】  
        join 表2 别名   
        on 连接条件  
        【where 筛选条件】**  
**        ...***  
    分类：  
    1.内连接:innner  
    2.外连接  
        左外:left【outer】  
        右外：right【outer】  
        全外：full【outer】  
    3.交叉连接：cross  
      
\*/  
\#**1.->内连接方式**  
/\*\*  
***select 查询列表  
        from 表1 别名 inner(inner可以省略）  
        join 表2 别名   
        on 连接条件  
        【where 筛选条件】***  
\*/

**\#1.查询球员以及对应的球队**  
select p.name,t.name  
from t\_player p  
inner join    t\_team t  on p.tid = t.id;

**\#2.自连接**  
select t.name,e.name  
from t\_employee t  
join  t\_employee e on t.mgr = e.id;

**\#3.外连接**  
/\*\*

应用场景：**用于查询一个表中有，另一个表中没有的记录**  
特点：  
    1.外连接的查询结果为主表中的所有记录  
    如果从表中有和他匹配的，则显示匹配的值  
    如果从表中没有和它匹配的，则显示null  
    外连接查询结果=内连接结果+主表中有而从表中没有的记录  
    2.左外连接：left join左边的是主表  
      右外连接，right join右边的是主表  
    3.左外和右外交换两个表的顺序，可以实现同样的效果  
\*\*/  
**\#案例：查询没有球队的球员(左外来连接）**  
select p.name,t.\*  
from t\_player p  
left outer join t\_team t  
on p.tid = t.id  
where t.id is null;

**\#左外连接  
\#查询没有球员的球队**  
select p.name  
from t\_team t  
left outer join t\_player p  
on t.id = p.tid  
where p.tid is null;

**\#右外连接 查询没有球队的球员**  
select t.id , t.name,p.name  
from t\_team t  
right outer join t\_player p  
on t.id = p.tid  
where t.id is null;

**\#交叉连接(笛卡尔乘积）**  
select t.\*,p.\*  
from t\_team t  
cross join t\_player p;

***\#\#sql92与sql99的比较  
\#\#功能：sql99支持的功能较多  
\#\#可读性：sql99实现连接条件和筛选条件的分离，可读性更高***

--------------------

***\#\#DQL(data query language)数据库查询语言***  
\#\#***(7)子查询***  
/\*\*  
含义：***出现在其他语句中的select语句，称为子查询或内查询***

分类：  
    按select出现的位置：  
        select后面：  
            仅仅支持标量子查询  
        from后面：  
            支持表子查询  
        where或having后面：  
            标量子查询（单行子查询），列子查询，行子查询  
        exists后面：  
            表子查询  
    按结果集的行列数不同：  
        标量子查询（结果只有一行一列）  
        列子查询（结果集只有一列多行）  
        行子查询（结果集有一行多列）  
        表子查询（结果集多行多列）  
\*\*/  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***where或having后面***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
/\*\*  
1.标量子查询（单行子查询）  
2.列子查询   
3.行子查询

特点：  
1.子查询放在小括号内  
2.子查询一般放在条件的右侧  
3.标量子查询，一般搭配着单行操作符的使用  
> , <  , <= , >= , <=>  
列子查询，一般搭配多行操作符使用  
in/any , some , all  
\*\*/  
**\#\#标量子查询  
\#1.查询比李四分数高的学生信息select \* from t\_student;**  
select \*   
from t\_student  
where score >(

select score  
    from t\_student   
    where name = '李四'  
      
);  
**\#2.查询比李四分数高,比bobo年龄小的学生信息**

select \*   
from t\_student  
where score >(

select score  
    from t\_student   
    where name = '李四'  
      
)  
and age < (  
    select age  
    from t\_student   
    where name = 'bobo'  
);

**\#\#列子查询**  
\#1.返回mgr是1001或1002的所有员工信息  
select name  
from t\_employee  
where mgr in(1001,1003);

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***select后面(仅仅支持标量子查询）***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

**\#1.查询id为1的学生信息**  
select(  
    select name  
    from t\_student  
    where id = 17  
) dd;

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***from后面***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

/\*\*  
**查询每个部门的平均工资以及对应的工资等级**  
select  
from(  
    select avg(salary) ag,department\_id  
    from employees  
    group by department\_id  
) ag\_dep  
inner join job\_grades g  
on ag\_dep.ag between lowest and hightest;

\*\*/

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#\#\#\#\#***exists后面（相关子查询）***  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

/\*\*  
语法：select exists(完整的查询语句）  
结果只有1或0  
查询有结果则1没有结果则0  
\*\*/

--------------------

***\#\#DQL(data query language)数据库查询语言***  
\#\#***(8)分页查询***  
/\*\*  
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求  
语法：  
    select 查询列表  
    from 表  
    【join type join 表2  
    where 筛选条件  
    group by 分组字段  
    having 分组后的筛选  
    order by 排序字段】  
  ***  limit offset,sise***  
    offset表示要显示的条目的起始索引(起始索引从0开始）  
    size表示要显示的条目个数  
      
    limit 放在执行语句的最后  
    要显示的页数page,每页的条目数size  
    select 查询列表  
    from 表  
    limit (page-1)\*size,size  
\*\*/

**\#1.查询员工表的前五条数据**  
SELECT   
    \*  
FROM  
    t\_employee  
LIMIT 3 , 3;

--------------------

***\#\#DQL(data query language)数据库查询语言***  
\#\#***(9)联合查询***  
/\*\*  
    union 联合 合并：将多条查询语句的结果合并为一个结果  
    语法：  
        查询语句1  
        union  
        查询语句2  
        union  
        ...  
          
    特点：  
        ***1.要求多条查询语句的列数是一致的  
        2.要求多条查询语句的查询的每一列的类型和顺序最好是一致的  
        3.union会自动去重，若不想去重则写为union all***  
\*\*/  
**\#1.查询分数大于90，而且年龄小于22的学生信息**  
select \* from t\_student where score > 90 or age < 22;  
\#\#\#等同于  
select \* from t\_student where score > 90  
union  
select \* from t\_student where age < 22;