🚀进修MySQL~(贰)

DQL-数据查询

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;