MYSQL之DQL（数据库查询语言）

DQL（数据库查询语言）：

DQL（data query language）数据查询语言，专门用来查询数据。

• 一般使用的都是Select 所有的查询操作都是这个
• 简单的查询和复杂的查询都能做
• 数据库中最核心的语言，也是最重要的语句。
• 使用频率最高的语句

查询所有

   select * from 表名;  -- 一般不推荐使用 sql优化不推荐使用
1

查询指定字段

select id , name from	表名;
1

查询起别名，然后通过别名得到数据

as 可以给字段起别名，也可以给表起别名

如 ： from 表名 as ‘别名’

select 列名1 as '别名1' , 列名2 as '别名2' ... from 表名；

去重查询distinct

去除查到的重复的值

select distinct 列名 from 表名；  -- 查询表中重复的数据，使得重复的数据只显示一条。

select version() ; -- 查询系统版本
select 100*3 -1 as '结果' ;  -- 用来计算  
select @@auto_increment_increment ;   -- 查询自增的步长（变量）

-- 例子 查询考试成绩 + 1 查看
select id , scode + 1 as '加分后' from 表名;
1
2
3
4
5
6
7
8

数据库中表达式：

文本值，列，NULL，函数，计算表达式，系统变量…

select 表达式 from 表名;

WHERE条件

一般作为检索数据中的符合条件的值

搜索的条件一般由一个或多个条件组成。

select id , score from 表名；
-- 查询成绩在95 - 100 直接的 id
select id from 表名 where score >=95  and score <= 100;

-- and  && 
select id from 表名 where score >=95  && score <= 100;

-- 模糊查询（between）
select id from 表名 where score between 95 and 100;

-- 除了1000号同学以为的所有同学的成绩
select id ,score from 表名 where id != 1000;

-- != not 
select id ,score from 表名 where not id = 1000;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

了解逻辑运算符：

模糊查询

举例：

-- 查询姓刘的同学的名字
select id , name from 表名 where name like '刘%'；

-- 查询姓刘的同学，且只有一个字的
select id , name from 表名 where name like '刘_'；

-- 查询姓刘的同学，且只有两个字的
select id , name from 表名 where name like '刘__'；

-- 查询名字中有嘉的同学，%嘉%
select id , name from 表名 where name like '%嘉%'；

-- =================== in（具体的一个或多个值） ================
 -- 查询101,102,103 号学员
 select id, name from 表名 where id in ( 101,102,103);
 
 -- 用in查询名字等于张三，李四。。。的学生
 select id , name from 表名 where name in ('张三', '李四' ,'王五'...)；
 
 -- 查询姓名为空的学习（当然我们设置默认值，必定不存在这个值）
  select id , name from 表名 where name  = '' or name is null;
  
  
  -- 查询姓名不为空的同学
    select id , name from 表名 where name  != '' or name is not null;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

连表查询（JOIN on）

 -- 连接两个表
    select s.id , s.name ,cid ,cname 
    from stu s inner join class as r
    where s.id = r.id;
     
    -- 右连接 right join 
    select s.id , name , cid , cname from stu s
    right join class c
    on s.id = c.id
    
    -- 左连接 left join  
    select s.id , name , cid , cname from stu s
    left join class c
    on s.id = c.id
    
    -- join  on  连接查询
    -- where  等值查询
    
    -- 查询name为空的同学
    select s.id , sname , cid, cname 
    from stu s left join 
    class c on s.id = c.id 
    where name is null;
    
    -- 查询时间为空的学生信息
    select s.id ,date, name ,cid , cname from stu s
    left join class c
    on s.id = c.id
    where date is null;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

  -- 测试数据库
    create table `table_a` (
      `aid` int(11) NOT NULL AUTO_INCREMENT,
      `a_name` varchar(255) NOT NULL,
      `age` smallint NOT NULL,
      PRIMARY KEY(`aid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表A';
     
    create table `table_b` (
      `bid` int(11) NOT NULL AUTO_INCREMENT,
      `b_name` varchar(255) NOT NULL,
      `age` smallint NOT NULL,
      PRIMARY KEY(`bid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表B';
     
    # 插入测试数据
    INSERT INTO `table_a`(aid, a_name, age) VALUES(1, 'test1', 1),(2, 'test2', 2),(3, 'test3', 3);
    INSERT INTO `table_b`(bid, b_name, age) VALUES(1, 'test2', 2),(2, 'test3', 3),(4, 'test4', 4);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

这三条SQL都是等价的

SELECT * FROM table_a JOIN table_b;
SELECT * FROM table_a INNER JOIN table_b;
SELECT * FROM table_a CROSS JOIN table_b;
1
2
3

结果如下：

inner join连接

   -- 相当于笛卡尔积
    mysql> SELECT * FROM table_a INNER JOIN table_b;
    +-----+--------+-----+-----+--------+-----+
    | aid | a_name | age | bid | b_name | age |
    +-----+--------+-----+-----+--------+-----+
    |   1 | test1  |   1 |   1 | test2  |   2 |
    |   2 | test2  |   2 |   1 | test2  |   2 |
    |   3 | test3  |   3 |   1 | test2  |   2 |
    |   1 | test1  |   1 |   2 | test3  |   3 |
    |   2 | test2  |   2 |   2 | test3  |   3 |
    |   3 | test3  |   3 |   2 | test3  |   3 |
    |   1 | test1  |   1 |   4 | test4  |   4 |
    |   2 | test2  |   2 |   4 | test4  |   4 |
    |   3 | test3  |   3 |   4 | test4  |   4 |
    +-----+--------+-----+-----+--------+-----+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

有ON和WHERE筛选条件，此时得到的结果是两张表的交集（中间的图），对于内连接，ON和WHERE是等价的，但是对于外连接则不是，在下面会讲到。

 # 这两条SQL是等价的，不过建议使用ON关键字，约定俗成
    SELECT * FROM table_a a INNER JOIN table_b b ON a.a_name=b.b_name;
    SELECT * FROM table_a a INNER JOIN table_b b WHERE a.a_name=b.b_name;
    
    # 结果如下：
    mysql> SELECT * FROM table_a a INNER JOIN table_b b ON a.a_name=b.b_name;
    +-----+--------+-----+-----+--------+-----+
    | aid | a_name | age | bid | b_name | age |
    +-----+--------+-----+-----+--------+-----+
    |   2 | test2  |   2 |   1 | test2  |   2 |
    |   3 | test3  |   3 |   2 | test3  |   3 |
    +-----+--------+-----+-----+--------+-----+
1
2
3
4
5
6
7
8
9
10
11
12

右连接(right join)

右连接的关键字是 RIGHT JOIN，从上图可以得到（右边的图），右连接其实就是两个表的交集+右表剩下的数据 ，当然这是在没其他过滤条件的情况下。

mysql> SELECT * FROM `table_a` a RIGHT JOIN `table_b` b ON a.a_name=b.b_name;
+------+--------+------+-----+--------+-----+
| aid  | a_name | age  | bid | b_name | age |
+------+--------+------+-----+--------+-----+
|    2 | test2  |    2 |   1 | test2  |   2 |
|    3 | test3  |    3 |   2 | test3  |   3 |
| NULL | NULL   | NULL |   4 | test4  |   4 |
+------+--------+------+-----+--------+-----+
1
2
3
4
5
6
7
8

左连接(left join)

左连接的关键字是 LEFT JOIN，从上图可以得到（左边的图），左连接其实就是两个表的交集+左表剩下的数据 ，当然这是在没其他过滤条件的情况下。

  # 没找到的被置为NULL
    mysql> SELECT * FROM `table_a` a LEFT JOIN `table_b` b ON a.a_name=b.b_name;
    +-----+--------+-----+------+--------+------+
    | aid | a_name | age | bid  | b_name | age  |
    +-----+--------+-----+------+--------+------+
    |   2 | test2  |   2 |    1 | test2  |    2 |
    |   3 | test3  |   3 |    2 | test3  |    3 |
    |   1 | test1  |   1 | NULL | NULL   | NULL |
    +-----+--------+-----+------+--------+------+
    
1
2
3
4
5
6
7
8
9
10

WHERE子句中的过滤条件就是我们常见的那种，不管是内连接还是外连接，只要不符合WHERE子句的过滤条件，都会被过滤掉。

而ON子句中的过滤条件对于内连接和外连接是不同的，对于内连接，ON和WHERE的作用是一致的，因为匹配不到的都会过滤，所以你可以看到内连接并不强制需要 ON 关键字；但是对于外连接，ON决定匹配不到的是否要过滤，所以你可以看到外连接是强制需要 ON 关键字的。

我要查询哪些数据 select …

从那几个表中查 from 表 XXX join 连接的表 on 交叉条件

假设存在一种多张表查询，慢慢来，先查询两张表 然后在慢慢添加。

自连接(了解)

自己的表和自己的表连接： 核心是一张表拆为两张表使用

将数据同一张表中数据进行查分，然后起别名。将一张表变成两张表处理。

分为父和子，也就是拆分的表可以成为父和子关系或者说包含和被包含关系。

分页和排序

limit 和 order by

limit（分页）

limit  当前页，页面大小

limit  0 , 5       --前边数字表示第几页，后边数据每页显示的数据条数
1
2
3

order by （排序）

- 升序	asc
  order by id  asc 根据id升序排序
- 降序        desc
  order by  id  desc根据id降序排序
1
2
3
4

子查询和嵌套查询

出现在其他语句中的select语句被称为子查询

🔝查询顺序是由里及外。

-- 查询 数据库结构的所有考试结果(学号，科目编号，成绩)，降序排列
-- 方式1  id 是stu 表 学生编号，sid 是 score 表，成绩编号
select id , s.sid, score
from score s,
inner join sub  u
on s.id = u.id
where sname = '数据结构'
order by score desc

-- 方式2 使用子查询
select id , sid ,score
from score 
where sid = (
    -- 作为子查询 先查询里边的然后在查询外边的
select sid 
    from score 
    where sname = '数据结构'
)
order by score desc;


-- 查询所有数据库结构 的学生学号
select id from score where sname = '数据结构'； 

-- 查询分数不小于80分的学号和姓名
select distinct id , name 
from stu t
inner join score s
on s.id = t.id
where score > 80;

-- 在此基础上添加一个科目，高等数学
select distinct id , name 
from stu t
inner join score s
on s.id = t.id
where score > 80 and sid = (
	select sid  from score 
    where sname = '高等数学'
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

按位置分类：

• select后面

• from后面

• where或having后面

• exists后面

按结果级的行数不同分类：

• 标量子查询 (结果集有一行一列)
• 列子查询 (结果集只有一列多行)
• 行子查询 (结果集只有一行多列)
• 表子查询 (结果集一般为多行多列)

where或having后面长放的

• 标量子查询
• 列子查询
• 行子查询

特点：

• 子查询都放在小括号内，结尾不用加 ;
• 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
• 子查询优先于主查询执行，主查询使用了子查询的执行结果
• 子查询根据查询结果的行数不同分为以下两类：单行子查询和多行子查询

单行子查询

• 结果集只有一行
• 一般搭配单行操作符使用：> < = <> >= <=

非法使用子查询的情况：

• 子查询的结果为一组值
• 子查询的结果为空
• 多行子查询

结果集有多行

• 一般搭配多行操作符使用：any、all、in、not in

• in：属于子查询结果中的任意一个就行

• any和all:往往可以用其他查询代替

MySQL常用函数

-- 数学运算
SELECT ABS(-10);-- 绝对值
SELECT CEIL(10.1);-- ceil和ceiling 向上取整
SELECT FLOOR(10.1);-- 向下取整
SELECT RAND();-- 返回0-1随机数
SELECT SIGN(NULL);-- 返回参数的符号 负数返回-1 正数返回1 
-- 0和非数字返回0 null返回null

-- 字符串函数
SELECT CHAR_LENGTH('fwebw');-- 返回字符串长度
SELECT CONCAT('1','2','3');-- 拼接字符串
SELECT INSERT('123456',1,3,'0');-- 先把第一个字符串的第1位起的0个字符去除
-- 然后再把第二个字符串插入到对应位置
SELECT LOWER('JIOji');-- 转小写字母
SELECT UPPER('wfdINOI');-- 转大写字母 
SELECT INSTR('asdasd','sd');-- 返回第一个字串出现的位置
SELECT REPLACE('123456789123','123','hhh');-- 把s1中s2部分替换为s3（所有s2）
SELECT SUBSTR('123456789',2,4);-- 截取字符串，从第2个字符开始截取4个字符
SELECT REVERSE('123465');-- 翻转字符串

-- 查询姓周的同学并把姓换为邹
SELECT REPLACE(studentName,'周','邹') FROM students
WHERE studentName LIKE '周%';


-- 时间和日期函数
SELECT CURRENT_DATE();-- 获取当前日期
SELECT CURDATE()-- 获取当前日期
SELECT CURRENT_TIME();-- 获取当前时间（时分秒）
SELECT SYSDATE();-- 获取系统时间
SELECT NOW();-- 获取时间
SELECT YEAR(NOW());-- 获取当前年
SELECT MONTH(NOW());-- 获取当前月
-- ······获取日时分秒同理

    

    -- 系统
    SELECT SYSTEM_USER();
    SELECT USER();-- 获取当前用户
    SELECT VERSION(); -- 获取当前版本信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

聚合函数和分组过滤（having by ）

having的用法

having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。

   -- 查询不同科目的平均分最高分和最低分 平均分大于80
    -- 由于按照顺序执行，所以where不能限定分组后计算出的平均值等信息
    -- 可以使用having
    SELECT sname,AVG(score),MAX(score),MIN(score)
    FROM score s
    INNER JOIN `sub` sub
    ON s.id=sub.id
    GROUP BY s.id
    HAVING AVG(score)>=80;
1
2
3
4
5
6
7
8
9