03-数据查询
符号 表达式 作⽤

• x1 + x2 加
• x1 – x2 减

• x1 * x2 乘
  / x1 / x2 除
  DIV x1 DIV x2 除
  % x1 % x2 求余
  MOD x1 MOD x2 求余
  数据查询操作
  ⼀、表达式
  1、定义
  操作数与操作符组合的式⼦。
  操作数：可以是常量、变量、函数的返回值、另⼀个查询语句返回的值；
  操作符：就是运算符，包括算术运算符、赋值运算符、⽐较运算符、逻辑运算符、字符匹配运算等。
  2、运算符
  1）算术运算符
  2）赋值运算符
  =（⽐较运算符也是=）
  select 1+1,1/2;
  符号 表达式 作⽤
  = x1 = x2 判断是否相等，不能判断等于NULL
  <> 或 != x1<>x2 或 x1!=x2 判断是否不相等
  <=> x1<=>x2 判断是否相等，可以判断是否等于NULL
  >、>= x1>x2、x1>=x2 判断是否⼤于、⼤于等于
  <、<= x1<x2、x1<=x2 判断是否⼩于、⼩于等于
  IS NULL、IS NOT NULL
  x1 IS NULL、x1 IS NOT
  NULL
  判断是否为NULL、或不为NULL
  BETWEEN AND、NOT BETWEEN
  AND
  X1 BETWEEN m AND n 判断是否在m与n范围内
  IN或NOT IN x1 IN (值1,…,值n) 判断是否在某个固定范围中
  LIKE、NOT LIKE x1 LIKE 表达式 判断是否匹配（模拟匹配、结合通配
  符）
  REGEXP x1 REGEXP 正则 判断是否正则匹配
  符号 形式 作⽤
  &&、AND 与 并且
  ||、OR 或 或者
  !、NOT ⾮ 取反
  XOR 异或 不同为真
  3）⽐较运算符
  注意：运算结果只能为真（1）或假（0）
  4）逻辑运算符

  匹配username是否以字符t开始

  select username,username REGEXP ‘^t’ from student;

  区别=与<=>

  select username,address=null from student;
  select username,address<=>null from student;
  优先
  级 运算符 优先
  级 运算符
  1 ！ 8 |
  2 ~ 9 =（⽐较）、<=>、<、<=、>=、!=、<>、IN、IS NULL、LIKE、
  REGEXP
  3 ^ 10 BETWEEN AND、CASE、WHEN、THEN、ELSE
  4 、/、DIV、%、
  MOD
  11 NOT
  5 +、- 12 &&、AND
  6 >>、<< 13 ||、OR、XOR
  7 & 14 =（赋值）、:=
  5）字符匹配运算
  BETWEEN…AND：如果操作数在某个范围之内，那么就为 TRUE
  IN：如果操作数等于表达式列表中的⼀个，那么就为 TRUE
  like：如果操作数与⼀种模式相匹配，那么就为 TRUE
  %：零个或任意个字符
  _：任意⼀个字符；
  []：指定⼀个字符、字符串或范围，从中选择⼀个匹配对象；
  [^]：所匹配的字符为指定字符以外的⼀个字符
  6）优先级
  注意：可以通过（）提⾼优先级
  ⼆、基本查询
  1、基本语法
  2、查询全部字段数据（）
  3、局部字段查询
  4、前N条数
  offset：起始值，默认从0开始
  rows：查询记录数
  5、条件查询
  查询条件可以是：
  带⽐较运算符和逻辑运算符的查询条件
  带 BETWEEN AND 关键字的查询条件
  带 IS NULL 关键字的查询条件
  select [distinct] [顶部条数| 聚合函数] 字段集合|常量|表达式|函数|*
  from 表名
  [where 查询条件语句集合]
  [group by 分组字段列表]
  [having 过滤条件语句集合] 分组查询条件
  [order by 排序字段集合 [asc | desc]]
  [LIMIT [,] ]

  执⾏顺序：FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

  select * from 表名 ;
  select 字段1,…,字段n from 表名 ;

  在MySQL中，top N⽆效，使⽤ limit 实现

  select top N | top n percent … from 表名 ;
  select from 表名 limit N
  select from 表名 limit [offset,] rows

  分⻚查询

  select from 表名 limit (当前⻚-1) 每⻚记录数,每⻚记录数
  select 字段集合 | * from 表名 where 条件 ;
  带 IN、NOT IN 关键字的查询条件
  带 LIKE 关键字的查询条件
  6、别名（ as）
  7、字符连接（+）
  8、排序查询

  为字段指定别名

  select 字段 [as] 别名 …

  为表名指定别名

  select *|字段集合 from 表名 as 别名

  注：在MySQL中，+表示的是运算符，不能⽤于字符串连接

  select 1 + 1;

  把字符尝试转换为数值，如果转换成功，则得到对应的数值，如果转换失败，则为0

  select 1 + ‘1’ ;
  select 1 + ‘你’ ;

  null + 任何数据 = null

  select 1 + null ;

  实现字符连接，可以使⽤concat函数

  单字段排序,默认为ASC

  order by 字段 ASC | DESC

  多字段排序

  注意：在对多个字段进⾏排序时，排序的第⼀个字段必须有相同的值，才会对第⼆个字段进⾏排序。如果第⼀个字段数据中所有

  的值都是唯⼀的，MySQL 将不再对第⼆个字段进⾏排序。
  order by 字段1 ASC | DESC,字段2 ASC | DESC
  9、聚合函数
  sum() : 求和
  avg() : 平均值
  max() : 最⼤值
  min() : 最⼩值
  count() : 记录数
  注：
  也叫分组函数，⼀般⽤于分组查询
  以上五个分组函数都忽略null值，除了count()；
  sum和avg⼀般⽤于处理数值型，max、min、count可以处理任何数据类型；
  如果count字段，则字段值为NULL的情况，将忽略统计
  10、筛选重复数据（去重）
  注意：如果DISTINCT 关键字后有多个字段，则会对多个字段进⾏组合去重，也就是说，只有多个字段组合起来完
  全是⼀样的情况下才会被去重。
  11、分组查询
  select后⾯跟着的字段必须满⾜两个条件，要么分组字段、要么使⽤聚合函数；
  分组查询⼀般结合聚合函数⼀起使⽤；
  where是分组前条件，having是分组后条件。
  select distinct |字段集合 from …
  select 字段，聚合函数
  from 表名
  group by 字段
  【WITH ROLLUP】
  having 条件
  SELECT 性别,COUNT(*) FROM 学⽣信息
  GROUP BY 性别
  WITH ROLLUP;

  解决NULL输出：coalesce(arg1,arg2,arg3,…)

  如果arg1⾮空，取值arg1，否则取值arg2;继续判断arg2是否为空，依此类推。如果参数都为NULL，则返回NULL

  SELECT coalesce(NULL,100) ;
  SELECT coalesce(性别,’总⼈数’),COUNT(*) FROM MARKDOWN_HASHa120a1c1550be9167648e684da8a9ea2MARKDOWNHASH
  GROUP BY 性别
  WITH ROLLUP;
  12、模糊查询
  其中，值⼀般结合以下三个通配符使⽤
  （下划线）：表示任意的1个字符（⻓度不能为0）
  % ：表示任意的0个或多个字符
  [] ：表示某范围（不⽀持？）
  [^]：表示某范围以外
  13、正则查询
  14、CASE WHEN
  … where 字段 like 值 …
  … where 字段 regexp 正则表达式 …
  CREATE TABLE test_user
  (
  id int primary key auto_increment ,
  name varchar(50) not null ,
  gender tinyint default 1 ,
  country_code smallint
  ) engine=InnoDb default charset=utf8;
  insert into test_user(name,gender,country_code) values (‘清⻛’,1,100) ;
  insert into test_user(name,gender,country_code) values (‘⽞武’,2,100) ;
  insert into test_user(name,gender,country_code) values (‘Kobe’,1,110) ;
  insert into test_user(name,gender,country_code) values (‘John Snow’,1,200) ;
  select id,name,gender,
  (
  case gender
  when 1 then ‘男’
  when 2 then ‘⼥’
  else ‘未知’
  end
  ) 性别,
  country_code
  from test_user;
  15、⾏转列
  三、多表查询
  如果我们要查询的数据分布在不同的表时，那么需要连接多张表进⾏多表查询。
  1、等值查询
  注意：
  条件⼀般是主键和外键的关联（可能包含筛选数据的条件）；
  ⼀般给各表取别名，提⾼阅读性、性能以及解决字段冲突
  create table grade
  (
  id int(10) not null auto_increment ,
  user_name varchar(20),
  course varchar(20),
  score float ,
  primary key(id)
  ) engine=InnoDb default charset=utf8 ;
  insert into grade(user_name,course,score) values (‘张三’,’数学’,34) ;
  insert into grade(user_name,course,score) values (‘张三’,’语⽂’,58) ;
  insert into grade(user_name,course,score) values (‘张三’,’英语’,58) ;
  insert into grade(user_name,course,score) values (‘李四’,’数学’,45) ;
  insert into grade(user_name,course,score) values (‘李四’,’语⽂’,87) ;
  insert into grade(user_name,course,score) values (‘李四’,’英语’,45) ;
  insert into grade(user_name,course,score) values (‘王五’,’数学’,76) ;
  insert into grade(user_name,course,score) values (‘王五’,’语⽂’,34) ;
  insert into grade(user_name,course,score) values (‘王五’,’英语’,89) ;
  SELECT user_name,
  max(case course when ‘数学’ then score else 0 end) 数学,
  max(case course when ‘语⽂’ then score else 0 end) 语⽂,
  max(case course when ‘英语’ then score else 0 end) 英语
  from grade GROUP BY user_name ;
  select 字段集合 from 表1,表2,…,表n
  where 条件 (主外键)
  2、内连接
  create table 教师表
  (
  编号 int identity(1,1) primary key not null ,
  姓名 char(30) ,
  性别 char(2) check(性别=’男’ or 性别=’⼥’) default ‘男’,
  专业 char(30)
  )
  create table 学⽣表
  (
  学号 int identity(1,1) primary key not null ,
  姓名 char(30) ,
  性别 char(2) check(性别=’男’ or 性别=’⼥’) default ‘男’,
  身⾼ float ,
  学分 float ,

  教师编号 int foreign key references 教师表(编号)
  )
  insert into 教师表 values (‘张三’,’男’,’计算机’)
  insert into 教师表 values (‘李四’,’男’,’⽇语’)
  insert into 教师表 values (‘王五’,’⼥’,’英语’)
  insert into 学⽣表 values (‘学⽣⼀’,’男’,1.5,50,1)
  insert into 学⽣表 values (‘学⽣⼆’,’⼥’,2.5,60,1)
  insert into 学⽣表 values (‘学⽣三’,’男’,3.5,70,2)
  insert into 学⽣表 values (‘学⽣四’,’⼥’,4.5,80,2)
  select from 教师表
  select from 学⽣表
  — 功能同等值连接
  — 好处：连接条件与筛选条件分离，简洁明了
  select 字段集合 from 表1 [inner] join 表2 on 条件(主外键|相同数据类型)
  where 条件
  3、外连接
  3.1、左外连接
  以第⼀张表为基础向第⼆张表匹配，匹配成功则正常显示，匹配不成功，则第⼆张表以NULL值显示
  3.2、右外连接
  以第⼆张表为基础向第⼀张表匹配，匹配成功则正常显示，匹配不成功，则第⼀张表以NULL值显示
  3.3、完全外连接
  左、右表的数据都要显示，如果能连接，则正常显示;如果不能连接，则以NULL值显示
  4、交叉连接
  5、⾃连接
  select 字段集合 from 表1 left [outer] join 表2 on 条件
  select 字段集合 from 表1 right [outer] join 表2 on 条件

  select 字段集合 from 表1 full [outer] join 表2 on 条件

  MySQL不⽀持，可以使⽤UNION实现

  SELECT from 表1 LEFT JOIN 表2 on 条件;
  UNION
  SELECT from 表1 RIGHT JOIN 表2 on 条件;
  — 语法⼀
  select 字段集合 from 表1 [inner] join 表2 #sql server 使⽤关键字 cross
  — 语法⼆
  select 字段集合 from 表1,表2
  — 语法⼀
  select from 表1 as 别名 join 表1 as 别名 on 条件
  — 语法⼆
  select from 表1 as 别名, 表1 as 别名 where 条件
  6、联合查询
  把多个查询的结果合并在⼀起
  注意:
  两个查询的字段个数必须相同;
  字段类型也要相同或兼容；
  union代表去重，union all代表不去重
  四、⼦查询
  1、概念
  ⼀条查询语句中被嵌套到另⼀条完整的查询语句中，其中被嵌套的查询语句，称之为⼦查询或内查询，⽽在外⾯的
  查询语句，称为主查询或外查询。⽤于⼦查询的关键字主要包括 in、not in、=、!=、exists、not exists 等。
  ⼦查询都放在⼩括号内；
  ⼦查询可以放在from后⾯、select后⾯、where后⾯、having后⾯，但常⽤于where后⾯；
  ⼦查询优先于主查询执⾏，⼦查询所得的结果服务于主查询；
  ⼦查询根据查询结果的⾏数不同分为以下两类：
  单⾏⼦查询：⼦查询的结果只有⼀⾏
  ⼀般结合单⾏操作符使⽤：>、<、=、 <>、 >=、 <=
  多⾏⼦查询：⼦查询的结果有多⾏
  ⼀般结合多⾏操作符使⽤：any、all、in、not in
  2、三种实现：
  2.1、[NOT] IN
  案例
  SELECT f1,f2 FROM T_1
  UNION [ALL]
  SELECT f1,f2 FROM T_2
  … where 字段 [NOT] IN (⼦查询|数值集合)
  create table t1
  (
  n int
  );
  2.2、⽐较运算符 [any | some | all]
  说明
  其中，ANY与SOME相同，对⼦查询的结果进⾏逻辑或运算; ⽽All则对⼦查询的结果进⾏逻辑与运算；
  ＝any 或 =some 等同于 in
  =any 或 =some 后⾯必须要是⼦查询，不能是具体某些数据；⽽in语句后⾯可以是⼦查询，也可以是具体的某
  些数据
  案例
  create table t2
  (
  n int
  );
  insert into t1 values (2);
  insert into t1 values (3);
  insert into t2 values (1);
  insert into t2 values (2);
  insert into t2 values (3);
  insert into t2 values (4);
  select from t2 where n in (1,3);
  select from t2 where n =1 || n=3;
  select from t2 where n not in (1,3);
  select from t2 where !(n = 1 || n = 3);
  … where 字段 ⽐较运算符 [any | some | all] (⼦查询)
  –如:=any,>any,>=all
  — all : 逻辑与(同时满⾜条件)
  SELECT FROM T2 WHERE N>all (SELECT N FROM T1)
  SELECT FROM T2 WHERE N>2 && N>3
  — some、any：逻辑或(满⾜其中之⼀)
  SELECT FROM T2 WHERE N>any (SELECT N FROM T1)
  SELECT FROM T2 WHERE N>some (SELECT N FROM T1)
  SELECT FROM T2 WHERE N>2 || N>3
  — =any、=some、in相同
  SELECT FROM T2 WHERE N=SOME (SELECT N FROM T1);
  SELECT FROM T2 WHERE N=ANY (SELECT N FROM T1);
  SELECT FROM T2 WHERE N IN (SELECT N FROM T1);
  — 思考：以下查询的结果是？
  2.3、[NOT] EXISTS
  3、特殊使⽤
  ⼦查询也可以使⽤在SELECT、INSERT、UPDATE或DELETE语句中。
  五、常⽤函数
  1、字符函数
  SELECT FROM T2 WHERE N <> any(SELECT FROM T1);
  — 判断⼦查询是否有查询结果，有返回true，否则返回false
  … where [NOT] EXISTS (⼦查询)
  函数 功能
  CONCAT(s1, s2, …,
  sn) 字符串拼接，将s1, s2, …, sn拼接成⼀个字符串
  LOWER(str) 将字符串全部转为⼩写
  UPPER(str) 将字符串全部转为⼤写
  LPAD(str, n, pad) 左填充，⽤字符串 pad 对 str 最左边进⾏填充，直到⻓度为 n 个字符⻓度
  RPAD(str, n, pad) 右填充，⽤字符串 pad 对 str 最右边进⾏填充，直到⻓度为 n 个字符⻓度
  TRIM(str) 去掉字符串头部和尾部的空格
  LTRIM(str) 去左边空格
  RTRIM(str) 去右边空格
  SUBSTRING(str,
  start, len) 返回从字符串str从start位置起的len个⻓度的字符串
  REPLACE(str,a,b) ⽤字符串 b 替换字符串 str 中所有出现的字符串 a
  INSERT(str,x,y,instr) 将字符串 str 从第 x 位置开始，y 个字符⻓的⼦串替换为字符串 instr
  LENGTH(str) 获取字节个数
  LEFT(str,x)和
  RIGHT(str,x)
  分别返回字符串最左边的x个字符和最右边的x个字符。 如果第⼆个参数是 NULL，那么
  将不返回任何字符串
  2、数学函数

  任何字符串与 NULL 进⾏连接的结果都将是 NULL

  SELECT concat(‘aaa’,’bbb’,’ccc’) ,concat(‘aaa’,NULL);
  SELECT substring(‘beijing2008’,8,4),substring(‘beijing2008’,1,7);
  SELECT INSERT(‘beijing2008you’,12,3, ‘me’) ;
  SELECT LEFT(‘beijing2008’,7),LEFT(‘beijing’,NULL),RIGHT(‘beijing2008’,4);
  SELECT lpad(‘2008′,20,’beijing’),rpad(‘beijing’,20,’2008′);
  函数 功能
  CEIL(x) 向上取整，返回⼤于 x 的最⼤整数
  FLOOR(x) 向下取整，返回⼩于 x 的最⼤整数，和 CEIL 的⽤法刚好相反
  MOD(x, y) 返回x % y的模
  RAND() 返回0~1内的随机数
  ROUND(x, y) 求参数x的四舍五⼊值，保留y位⼩数
  TRUNCATE(x,y) 返回数字 x 截断为 y 位⼩数的结果
  3、⽇期函数

  如果不写 y，则默认 y 为 0，即将 x 四舍五⼊后取整

  select ROUND(1.1),ROUND(1.1,2),ROUND(1,2);
  select RAND(),RAND();

  模数和被模数任何⼀个为 NULL 结果都为 NULL

  select MOD(15,10),MOD(1,11),MOD(NULL,10);
  select CEIL(-0.8),CEIL(0.8);
  select FLOOR(-0.8), FLOOR(0.8);
  select ROUND(1.235,2),TRUNCATE(1.235,2);
  函数 功能
  CURDATE() 返回当前系统⽇期
  CURTIME() 返回当前系统时间
  NOW() 返回当前系统⽇期 + 时间
  YEAR(date) 获取指定date的年份
  MONTH(date) 获取指定date的⽉份
  DAY(date) 获取指定date的⽇期
  STR_TO_DATE(str,format) 将字符转换成⽇期
  DATE_FORMAT(date,format) 将⽇期转换成字符
  DATE_ADD(date,INTERVAL expr
  type)
  返回⼀个⽇期/时间值加上⼀个时间间隔expr后的时间值。其中 INTERVAL
  是间隔类型关键字，expr 是⼀个表达式，这个表达式对应后⾯的类型，
  type 是间隔类型，如：year,month,day等
  DATEDIFF(date1，date2) 返回起始时间date1和结束时间date2之间的天数
  TIMESTAMPDIFF(type
  ,datetime_expr1,datetime_expr2)
  datetime_expr2与datetime_expr1的时间差，type 是间隔类型，如：
  year,month,day等
  4、流程控制函数
  select str_to_date(‘2021年03⽉09⽇’,’%Y年%m⽉%d⽇’);
  select date_format(now(),’%Y年%m⽉%d⽇’);

  两天后

  select now() 当前时间,date_add(now(),INTERVAL 2 day) ;
  select DATEDIFF(‘2021-05-01’,now());
  select TIMESTAMPDIFF(MONTH, ‘1985-06-24’, ‘1985-08-24’) ;
  函数 功能
  IF(value, t, f) 如果value为true，则返回t，否则返回f
  IFNULL(value1, value2) 如果value1不为空，返回value1，否则返回value2
  CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ]
  END
  如果val1为true，返回res1，… 否则返回default默认值
  CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [
  default ] END
  如果expr的值等于val1，返回res1，… 否则返回
  default默认值
  1）if：处理双分⽀
  2）IFNULL(value1,value2)：如果 value1 不为空返回 value1，否则返回 value2
  3）case语句：处理多分⽀

  如果value是真，返回t；否则返回f

  if(value,t,f)
  select if(5>1,’你好’,’我好’);
  select IFNULL(‘你好’,’我好’) ;
  select IFNULL(NULL,’我好’) ;

  情况1：处理等值判断

  如果 expr 等于 value，返回 result，否则返回 default

  CASE expr WHEN value THEN result … ELSE default END

  情况2：处理条件判断

  如果 value 是真，返回 result，否则返回 default

  CASE WHEN [value] THEN [result] … ELSE [default] END

  案例

  create table salary
  (
  userid int,
  salary decimal(9,2)
  );
  insert into salary values(1,1000),(2,2000), (3,3000),(4,4000),(5,5000), (1,null);
  select userid,salary,if(salary>2000,’high’,’low’) from salary;
  函数 功能
  version() 版本
  database() 当前库
  user() 当前连接⽤户
  5、其他函数
  select ifnull(salary,0) from salary;
  select case when salary<=2000 then ‘low’ else ‘high’ end from salary;
  select case salary when 1000 then ‘low’ when 2000 then ‘mid’ else ‘high’ end from salary;
  select version() ;
  select database() ;
  select user() ;