DQL-数据查询

总览

  • 语句顺序不能变

    20210316194554
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

SELECT

SELECT 列名 [AS 显示列名] FROM 表名 [AS 显示表名];

-- 查询所有学生信息
SELECT
*
FROM
student;

-- 查询指定列
SELECT
studentid,
studentname
FROM
student;

-- 这里是为列取别名(as关键词可以省略)
SELECT
studentid AS `学 号`,
studentname `姓 名`
FROM
student;

-- as也可以为表取别名(并不会显示出来)
SELECT
studentid AS `学 号`,
studentname AS `姓 名`
FROM
student AS `表`;

-- 通过CONCAT()函数拼接字符串
SELECT
CONCAT('姓名: ', studentname) AS `拼接后的名字`
FROM
student;

去重 DISTINCT

SELECT DISTINCT * FROM student

对列使用表达式

  • 数据库中的表达式 : 一般由文本值 , 列值 , NULL , 函数和操作符等组成
-- 查询自增步长
SELECT
@@auto_increment_increment;

-- 查询版本号
SELECT
VERSION();

-- 表达式
SELECT
100 * 3 -1 AS `结果`;

-- 学员考试成绩集体提分一分查看
SELECT
studentid,
gradeid + 1 AS '升级后'
FROM
student;

where

表达式结果必须是 布尔值

逻辑操作符

  • AND &&
  • OR ||
  • NOT !

  • 例子

    -- 满足条件的查询
    SELECT Studentno,StudentResult FROM result;

    -- 查询考试成绩在95-100之间的
    SELECT Studentno,StudentResult
    FROM result
    WHERE StudentResult>=95 && StudentResult<=100;

    -- 模糊查询
    SELECT Studentno,StudentResult
    FROM result
    WHERE StudentResult BETWEEN 95 AND 100;

    -- 除了1000号同学,要其他同学的成绩
    SELECT studentno,studentresult
    FROM result
    WHERE studentno!=1000;

    -- 使用NOT
    SELECT studentno,studentresult
    FROM result
    WHERE ! studentno=1000;

模糊查询

20210312120236
  • 例子

    -- LIKE like结合使用的通配符:  % (代表0到任意个字符)   _ (一个字符)
    -- =============================================
    -- 查询姓刘的同学的学号及姓名
    SELECT
    studentno,
    studentname
    FROM
    student
    WHERE
    studentname LIKE '刘%';

    -- 查询姓刘的同学,后面只有一个字的
    SELECT
    studentno,
    studentname
    FROM
    student
    WHERE
    studentname LIKE '刘_';

    -- 查询姓刘的同学,后面只有两个字的
    SELECT
    studentno,
    studentname
    FROM
    student
    WHERE
    studentname LIKE '刘__';

    -- 查询姓名中含有 '嘉' 字的
    SELECT
    studentno,
    studentname
    FROM
    student
    WHERE
    studentname LIKE '%嘉%';
    -- 查询姓名中含有特殊字符的需要使用转义符号 '\'
    -- 自定义转义符关键字: ESCAPE ':'

    -- IN
    -- =============================================
    -- 查询学号为1000,1001,1002的学生姓名
    SELECT
    studentno,
    studentname
    FROM
    student
    WHERE
    studentno IN (1000, 1001, 1002);

    -- 查询地址在北京,南京,河南洛阳的学生
    SELECT
    studentno,
    studentname,
    address
    FROM
    student
    WHERE
    address IN ('北京', '南京', '河南洛阳');

    -- 对NULL判断 不能直接写=NULL, 用 is null
    -- =============================================
    -- 查询出生日期没有填写的同学
    SELECT
    studentname
    FROM
    student
    WHERE
    BornDate IS NULL;

    -- 查询出生日期填写的同学
    SELECT
    studentname
    FROM
    student
    WHERE
    BornDate IS NOT NULL;

    -- 查询没有写家庭住址的同学
    SELECT
    studentname
    FROM
    student
    WHERE
    Address = ''
    OR Address IS NULL;

通配符

比如有个表 user,有一个 name 数据为张三,需要通过’张’字模糊查询出来

  • 这样是查不出来的,这是全字匹配,换为’张三’可以查出来

    select *
    from user
    where name like '张'
  • 需要改成这样: 这样是作为通配符,name 中含有’张’字的都会被匹配出来.

    select *
    from user
    where name like '%张%'

联表查询

  • 七种 SQL joins

    20210313224946
    • 共有四种操作符 inner,full outer,left,right

    • ON 后面跟的是 A 表与 B 表数据交叉列,是 join 成立的条件.


  • 例子

    -- student和result表中都有studentno,如不指定select那个表中的,会报错"不明确"
    SELECT
    s.studentno,
    studentname,
    subjectno,
    StudentResult
    FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno

    -- 右连接
    SELECT
    s.studentno,
    studentname,
    subjectno,
    StudentResult
    FROM
    student s
    RIGHT JOIN result r ON r.studentno = s.studentno

    -- 等值连接
    SELECT
    s.studentno,
    studentname,
    subjectno,
    StudentResult
    FROM
    student s,
    result r
    WHERE
    r.studentno = s.studentno

    -- 左连接 (查询了所有同学,不考试的也会查出来)
    SELECT
    s.studentno,
    studentname,
    subjectno,
    StudentResult
    FROM
    student s
    LEFT JOIN result r ON r.studentno = s.studentno

    -- 查一下缺考的同学(左连接应用场景)
    SELECT
    s.studentno,
    studentname,
    subjectno,
    StudentResult
    FROM
    student s
    LEFT JOIN result r ON r.studentno = s.studentno
    WHERE
    StudentResult IS NULL

    -- 查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
    SELECT
    s.studentno,
    studentname,
    subjectname,
    StudentResult
    FROM
    `student` s
    INNER JOIN `result` r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON sub.subjectno = r.subjectno

自连接

  • 把一张表视作两张表,然后把同一列的不同数据筛选开作为两列输出

  • 初始化数据

    -- 创建一个表
    CREATE TABLE `category` (
    `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
    `pid` INT(10) NOT NULL COMMENT '父id',
    `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
    PRIMARY KEY (`categoryid`)
    ) ENGINE = INNODB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8;

    -- 插入数据
    INSERT INTO
    `category` (`categoryid`, `pid`, `categoryName`)
    VALUES('2', '1', '信息技术'),
    ('3', '1', '软件开发'),
    ('4', '3', '数据库'),
    ('5', '1', '美术设计'),
    ('6', '3', 'web开发'),
    ('7', '5', 'ps技术'),
    ('8', '2', '办公信息');
  • select 例子

    • 把同一张表中 categoryName 这一列 select 出父栏目和子栏目两列数据输出
    SELECT
    a.categoryName AS '父栏目',
    b.categoryName AS '子栏目'
    FROM
    category AS a,
    category AS b
    WHERE
    a.`categoryid` = b.`pid`;
  • 输出

    20210315112316
  • 其他样例

    -- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
    SELECT
    s.studentno,
    studentname,
    subjectname,
    StudentResult
    FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON sub.subjectno = r.subjectno;

    -- 查询学员及所属的年级(学号,学生姓名,年级名)
    SELECT
    studentno AS 学 号,
    studentname AS 学 生 姓 名,
    gradename AS 年 级 名 称
    FROM
    student s
    INNER JOIN grade g ON s.`GradeId` = g.`GradeID`;

    -- 查询科目及所属的年级(科目名称,年级名称)
    SELECT
    subjectname AS 科 目 名 称,
    gradename AS 年 级 名 称
    FROM
    SUBJECT sub
    INNER JOIN grade g ON sub.gradeid = g.gradeid;

    -- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
    SELECT
    s.studentno,
    studentname,
    subjectname,
    StudentResult
    FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON r.subjectno = sub.subjectno
    WHERE
    subjectname = '数据库结构-1'

排序

ORDER BY 列名 ASC/DESC(升序/降)

  • 例子

    -- 按成绩降序排序
    SELECT
    s.studentno,
    studentname,
    subjectname,
    StudentResult
    FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON r.subjectno = sub.subjectno
    WHERE
    subjectname = '数据库结构-1'
    ORDER BY
    StudentResult DESC;

分页

LIMIT n,m

  • 含义

    • 从第 n 条开始显示

    • 每页显示 m 条数


  • 比如需要每页显示 10 条数据,当前要看第一页

    • LIMIT 0,10

    • 第 n 页数据就是 LIMIT 10(n-1),10


  • 例子

    -- 每页显示5条数据
    SELECT
    s.studentno,
    studentname,
    subjectname,
    StudentResult
    FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON r.subjectno = sub.subjectno
    WHERE
    subjectname = '数据库结构-1'
    ORDER BY
    StudentResult DESC,
    studentno
    LIMIT
    0, 5 ;

    -- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
    SELECT
    s.studentno,
    studentname,
    subjectname,
    StudentResult
    FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON r.subjectno = sub.subjectno
    WHERE
    subjectname = 'JAVA第一学年'
    ORDER BY
    StudentResult DESC
    LIMIT
    0, 10;

子查询

查询语句中 where 中嵌套查询语句

-- 查询 数据库结构-1 的考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT
studentno,
r.subjectno,
StudentResult
FROM
result r
INNER JOIN `subject` sub ON r.`SubjectNo` = sub.`SubjectNo`
WHERE
subjectname = '数据库结构-1'
ORDER BY
studentresult DESC;

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT
studentno,
subjectno,
StudentResult
FROM
result
WHERE
subjectno =(
SELECT
subjectno
FROM
`subject`
WHERE
subjectname = '数据库结构-1'
)
ORDER BY
studentresult DESC;