MySQL知识点总结(二)---查询操作

水深无声 2022-05-17 11:18 431阅读 0赞

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

*   *  一.基本操作
        
         *  1.查看当前有什么数据库
         *  2.查看当前有什么数据库/表格存在
            
             *   *  补充：查看在指定数据库中的表—可以在别的数据库中查看
         *  3.查看当前对什么数据库进行操作
         *  4.查看一张表的表结构
         *  5.查看表是怎么创建的
     *  二.单表查询
        
         *  1.查询一个字段
         *  2.条件查询
            
             *   *  常用的运算符\[^1\]
                    
                     *  1.等于( = )
                     *  2: 等于( <=> )
                     *  3: is判断(null)
                     *  4: null值判断还可以使用isnull();
                     *  5: 在区间(between)内 between min and max
                     *  ps:这里是一个闭区间
                     *  6: 不在区间内：not between min and max
                     *  7: and 和 or
                     *  8: 小于(<), 大于(>), 小于等于(<=), 大于等于(>=)
                     *  9：包含 in（相当于多个or）
                     *  10.not：取非，通常用于 is 和 in 中
                     *  11.like:模糊查询，支持 %和\_\_占位符 进行匹配
         *  3.排序查询
         *  4.去重查询
         *  5.分组查询
            
             *  1.group by
             *  2.having
         *  6.DQL语句总结
         *  \*7.limit语句
         *   
     *  三.多表查询
        
         *  连接查询（跨表查询）
            
             *  1.分类
             *  2.内连接
                
                 *  笛卡儿积连接
                 *  等值连接
                 *  非等值连接
                 *  自链接
             *  3.外连接
         *  多表连接查询
         *  子查询
            
             *  1.定义
             *  2.where /join on 后面的子查询
             *  3.from 后面的子查询
         *  union 并集连接

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

## 一.基本操作 ##

### 1.查看当前有什么数据库 ###

mysql> show databases

### 2.查看当前有什么数据库/表格存在 ###

1)指定数据库
    mysql> use mydb
    2）展示
    mysql> show tables

##### 补充：查看在指定数据库中的表—可以在别的数据库中查看 #####

`mysql mysql> show tables from city`

### 3.查看当前对什么数据库进行操作 ###

mysql> select database();

### 4.查看一张表的表结构 ###

`mysql mysql> use world mysql> desc city`

*desc:描述*

### 5.查看表是怎么创建的 ###

`mysql mysql> show creat table city`

*该语句是将当初创建表的语句再次呈现出来*

## 二.单表查询 ##

### 1.查询一个字段 ###

1）查询全表
    mysql> select * form city
    2) 查询某几列
    mysql> select id,name from city
    3) 查询过程中进行运算
    mysql> select id*2 from city
    4）查询过程中将列名重命名
    mysql> select id*2 as '233',name as 'lalala' from city

*  在java程序实现中一般不要使用 \* ，执行效率低，可读性差

==凡是select的语句，都不会修改底层表中的数据，仅仅只是检索出数据==

### 2.条件查询 ###

##### 常用的运算符\[^1\] #####

> ###### 1.等于( = ) ######
> 
>     select * from employee where sal = 3800;
>     select * from employee where sal = null;  --这里查询不到为null的数据
> 
> ###### 2: 等于( <=> ) ######
> 
>     select * from employee where sal <=> 3800;
>     select * from employee where sal <=> null; --这里可以查询到为null的数据
> 
> ###### 3: is判断(null) ######
> 
>     select * from employee where sal is null;
>     select * from employee where sal is not null;
> 
> ###### 4: null值判断还可以使用isnull(); ######
> 
>     select * from employee where isnull(sal);
>     select * from employee where !isnull(sal);
> 
> ###### 5: 在区间(between)内 between min and max ######
> 
> ###### *ps:这里是一个闭区间* ######
> 
> `mysql select * from employee where sal between 4500 and 5000; select * from students while name between 'a' and 'd';`
> 
> ###### 6: 不在区间内：not between min and max ######
> 
>     select * from employee where sal not between 4500 and 5000;--null不为包括进去
> 
> ###### 7: and 和 or ######
> 
>     select * from employee where sal not between 4500 and 5000 or sal is null;
>     select * from employee where sal = 4500 and emp_sex = '女';、
> 
> *\*and的优先级要比or高*
> 
>     select * from students where sex='女' and (mark = 99 or mark = 100 );
> 
> ###### 8: 小于(<), 大于(>), 小于等于(<=), 大于等于(>=) ######
> 
> `mysql select * from employee where sal >= 4500;`
> 
> ###### 9：包含 in（相当于多个or） ######
> 
> `mysql mysql> select * from city where id in(1000,2000,3000); mysql> select * from city where id not in(1000,2000,3000);`
> 
> ###### 10.not：取非，通常用于 is 和 in 中 ######
> 
> ###### 11.like:模糊查询，支持 %和\_\_占位符 进行匹配 ######
> 
> `mysql mysql> select * from city where name like '_hi%a';`

==查询语句中的条件语句，可以全都用字符串型，MySQL会将其自动转型==

==null 不是一个值，不可以用 = 来修饰==

### 3.排序查询 ###

`mysql 1)升序 select id,name from students where mark between 90 and 100 order by mark asc 2）降序 select id,name from students where mark between 90 and 100 order by mark desc 3）多层排序 select id,name from students where mark between 90 and 100 order by mark desc，name asc 4）简化版 select id,name，mark from students where mark between 90 and 100 order by 3 desc，1 asc`

*默认升序，可以不用写 asc*

*降序的 desc 并不是描述的意思*

### 4.去重查询 ###

mysql> select distinct id ,name from students ;

==distinct只能放在所有字段的最前端方==

*当 distinct 后面接的是多个属性，将会对其视为属性组来进行重复排除*

### 5.分组查询 ###

#### 1.group by ####

`mysql 1) 正确的做法 mysql> select class, max(mark) from s_mark group by class; 2）错误的做法 mysql> select class,name ,min(mark) from s_mark group by class; ---ps:该结果没有意义，但在MySQL没有报错，name项并不匹配 3）多字段联合分组查询 ---eg：查询不同班级不同性别的学生的最高成绩 mysql> select class, sex, max(mark) from s_mark group by class,sex; 4）排除某些数据的分组查询 ---eg：查询除了3班以外的所以班级的最高成绩 mysql> select class ,max(mark) from mark where class != 3 group by class; 5) 带条件的分组查询 ---查询每个班级里的最低成绩，要求最低成绩小于60； mysql> select class, min(mark),from mark where mark < 60 group by class;`

==group by 后面只能跟 参加分组的字段 或者 分组函数==

#### 2.having ####

功能等同于where 都是用于过滤，但它是在分组后过滤，即：在group by 后使用

`mysql mysql> select class ,max(mark) from mark group by class having class != 3;`

*原则：尽量在where中进行过滤，在需要先分组后过滤的情况下，再使用having*

### 6.DQL语句总结 ###

**select … from … where … group by … having … order by …**

**1.以上关键字顺序不能变**

**2.执行顺序：from >where >group by >having >select >order by**

### \*7.limit语句 ###

*  只用于获取一张表中的某部分数据
 *  只有在mysql中才存在，不通用

找出学生表表中的前5条记录
    select name from studnets order by id desc limit 5;
    找出学生表表中的5~10的记录
    select name from students order by id desc limit 5,10;
    找出学生表表中学号排序3-9的记录（9-3+1=7）
    select name from students order by id desc limit 2,7;

**通用的分页语句**(只适用于MySql)

EG：每页显示3条记录 则第一页 ： 0 , 3 第二页： 3 , 3 第三页：6 , 3 …

**每页显示k条数据**

**第n页: (n-1)k , k**

select * from students order by id limit (n-1)k , k

###  ###

## 三.多表查询 ##

### 连接查询（跨表查询） ###

#### 1.分类 ####

**根据出现年代分类：**

*  SQL92
 *  SQL99

**根据连接方式分类为：**

*  内连接：两表完全匹配  
    
    
     *  等值连接
     *  非等值连接
     *  自链接
 *  外连接：两表匹配又出现空  
    
    
     *  左连接（左外连接）
     *  右连接（右外连接）
 *  全连接

#### 2.内连接 ####

*  多张表连接查询的时候，若没有任何限制，会对表进行 笛卡儿积
 *  但即使使用了限制，也会在匹配前先进行 笛卡儿积

##### 笛卡儿积连接 #####

`mysql 1) 不使用别名 select id, name, mark from students,s_mark; 2) 使用别名 select s.id, s.name, m.mark from students s,s_mark m;`

==多表查询尽量用别名，这样查询效率高，可读性强==

##### 等值连接 #####

1）[SQL92语法]
    select s.id, s.name, m.mark from students s, s_mark m where s.id = m.id;
    2）[SQL99语法]
    select s.id, s.name, m.mark from studnets s join s_mark m on s.id = m.id ;

99的语法优势在于：可以在 on 筛选后 再接着用 where 追加一轮筛选（结构清晰，表连接独立）

##### 非等值连接 #####

案例：显示出学生成绩在分布区间的表

`mysql 1)[SQL92语法] mysql> select m.id, m.mark, g.grade from mark m , markGrade g where m.mark between g.low and g.high 2)[SQL99语法] mysql> select m.id, m.mark, g.grade from mark m join markGrade g on m.mark between g.low and g.high`

##### 自链接 #####

案例：找出每一个学生会干部的上级领导，要求显示成员名和对应的领导名

`mysql 1)[SQL92语法] mysql> select a.name, b.name from s_union a , s_union b where a.leaderID = b.ID ; 2)[SQL99语法] mysql> select a.name, b.name from s_union a join s_union b on a.leaderID = b.ID ;`

#### 3.外连接 ####

*外连接查询结果 >= 内连接*

案例：找出每个学生会干部的专业所属学院名称，要求学院名称要全部显示(没有人时置为空)

`mysql 1）左联接 mysql> select c.academe ,u.name from colleag c left join s_union u on c.profession =u.profession 2）右连接 mysql> select u.name, c.academe from s_union u right join colleag c on c.profession = u.profession;`

### 多表连接查询 ###

**格式**

join a and b on ….. join c on …..

*原理：a连接完 b ，连接结束后，a 再和 c 连接*

==a表的选择很关键，它会是一张枢纽表==

**案例**：找出所有学生的成绩以及成绩等级

`mysql mysql> select s.name, m.mark,l.level from s_mark m join students s on m.id = s.id join markLevel l on m.mark between l.low and l.high;`

### 子查询 ###

#### 1.定义 ####

select 语句嵌套着 select 语句

==嵌套查询的子查询不可以用group by==

#### **2.where /join on 后面的子查询** ####

EG：找出分数比平均分的高的学生，要求显示名字与分数

`mysql 1）第一步 select avg(mark) from s_mark 2）第二部 mysql> select s.name, m.mark from students s join s_mark on m.mark >= (select avg(mark) from s_mark);`

==分组函数不可以用在 where 后面==

#### **3.from 后面的子查询** ####

EG:找出每个班级的平均成绩，并且要求显示平均成绩的等级

`mysql 1)第一步 select class, avg(mark) as avgMark from s_mark; 2)第二部 select a.class,a.avgMark, l.level from (select class, avg(mark) as avgMark from s_mark) a join markLevel l on a.avgMark between l.low and l.high; 3)简便写法 select a.*, l.level from (select class, avg(mark) as avgMark from s_mark) a join markLevel l on a.avgMark between l.low and l.high;`

*将查询结果当作一个临时表*

### union 并集连接 ###

mysql> select id,name from students where major='SC'
           union
           select id,name from studnets where major='Math';

==要求两边的域要相同==