🚀进修MySQL~(贰)

• DQL-数据查询
  • 总览
  • SELECT
  • 去重 DISTINCT
  • 对列使用表达式
  • where
    • 逻辑操作符
    • 模糊查询
    • 通配符
  • 联表查询
  • 自连接
  • 排序
  • 分页
  • 子查询
    

DQL-数据查询

总览

• 语句顺序不能变

  

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;

---

模糊查询

• 例子

  -- 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

  

  • 共有四种操作符 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`;

• 输出

  

• 其他样例

  -- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
  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;