Mysql学习笔记五

一、DQL

Data Query Language：数据查询语言

• 所有查询操作都用它 select
• 简单，复杂的查询它都能做
• 数据库最核心的语言，最重要的语句
• 使用频率最高的语句

完整语法：

SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]             -- 指定结果需要满足的条件
[GROUP BY…]			 -- 指定结果按照哪几个字段来分组
[HAVING…]			 -- 过滤分组的记录必须满足的次要条件
[ORDER BY…desc/asc]  -- 指定查询记录按一个或多个条件排序
[LIMIT…]  			 -- 指定查询的记录从哪条到哪条

注意join，where，group by，having，order by，limit的顺序不能错！

二、指定查询字段

语法：select 字段1，字段2,… from 表;

-- 查询所有的学生
-- 语法：select 字段 from 表;
select * from student;

-- 查询指定字段
select studentname from student;

-- 别名，给结果起一个名字:as可以给字段还有表取别名
-- 不要加引号！
select studentname as 学号 from student as s;

-- 函数
-- 拼接字符串concat(a,b)
select concat('姓名：',studentname) as 新名字 from student;

去重 distinct

作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询一下哪些同学参加了考试
select * from result; -- 查询全部考试成绩
select studentno from result; -- 发现重复数据
select distinct studentno from result; -- distinct去重

select的其他作用：

select version(); -- 查询系统版本（函数）
select 100*3-1 as 计算结果; -- 用于计算（表达式）
select @@auto_increment_increment;-- 查询自增步长（变量）

-- 学院考试成绩+1查看（表达式）
select studentno,studentresult+1 as 新成绩,studentresult as 原始成绩 from result;

三、where条件子句

作用：检索数据中符合条件的值。
搜索的条件由一个或多个表达式组成，返回结果一般是个布尔值

1.逻辑运算符

运算符	语法	描述
and	a and b	逻辑与，两个都为真结果才为真
or	a or b	逻辑或，一个为真则结果为真
not	not a	逻辑非，取反，真为假，假为真

select studentno ,studentresult from result;
-- 查询考试成绩在95-100分之间的
select studentno ,studentresult from result where studentresult>=95 and studentresult<=100;
select studentno ,studentresult from result where studentresult>=95 && studentresult<=100;
select studentno ,studentresult from result where studentresult between 95 and 100;  -- 三种写法效果相同

2.模糊查询：比较运算法

运算符	语法	描述
is null	a is null	如果操作符为null，则结果为真
is not null	a is not null	如果操作符不为空，结果为真
between	a between b and c	a在b和c之间则结果为真
like	a like b	sql匹配，如果a匹配b，则结果为真
in	a in (a1,a2,a3,…)	a为a1,a2,a3,…中的某一个，则结果为真

-- 模糊查询
-- 查询性张的同学
-- like结合：%（代表0到任意个字符），_（代表一个字符）
select * from student;
select studentno,studentname  from student where studentname like '张%';
-- 查询姓刘且名字后面只有一个字的
select studentno,studentname  from student where studentname like '张_';
-- 查询名字中间有强字的同学
select studentno,studentname  from student where studentname like '%强%';

-- in:
-- 查询1000,1001,1002号学生
select studentno,studentname  from student where studentno in (1000,1001,1002);
-- 查询北京朝阳的学生
select studentno,studentname  from student where address in ('北京朝阳')

四、联表查询

join

-- 联表查询
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
select * from student;
select * from result;
/*
	1.分析需求，分析查询的字段来自哪些表
	2.确定使用哪种连接查询(确定交叉点，即这两个表中那个数据是相同的
*/

student表：

result表：

-- inner join
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
on s.studentno=r.studentno;

查询结果：

-- left join
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno=r.studentno;

查询结果：

-- right join
select s.studentno,studentname,subjectno,studentresult
from student as s
right join result as r
on s.studentno=r.studentno;

查询结果：

操作	描述
inner join	如果表中至少有一个匹配，就返回匹配的行
left join	即使右表中没有匹配，也会从左表中返回所有的值
right join	即使左表中没有匹配，也会从右表中返回所有的值

• join on：连接查询
• where：等值查询

一些稍复杂的查询练习：

1. 查询缺考的同学信息
2. 查询参加考试的同学信息：学号，姓名，科目名，分数

-- 查询缺考的同学
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno=r.studentno
where studentresult is null;

-- 查询参加考试的同学信息：学号，姓名，科目名，分数
select * from student;
select * from result;
select * from subject;

select s.studentno,studentname,subjectname,studentresult
from student as s 
right join result as r
on r.studentno = s.studentno
inner join subject as sub
on r.subjectno = sub.subjectno;

五、自连接（了解即可）

即自己的表和自己的表连接，核心：一张表拆为两张一样的表
原表：

categoryid	pid	categoryname
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	ps技术
8	2	办公信息

拆分表1：
父类

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

拆分表2：
子类

pid	categoryid	categoryname
3	4	数据库
3	6	web开发
5	7	ps技术
2	8	办公信息

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息
select a.categoryname as '父栏目',b.categoryname as '子栏目'
from category as a,category as b
where b.pid = a.categoryid;

结果：

六、分页和排序

1.排序

语法：order by + 排序字段 + 怎么排

-- 排序：升序ASC，降序DESC
-- order by + 排序字段 + 怎么排
-- 根据成绩排序（降序）
select s.studentno,studentname,subjectname,studentresult
from student as s 
right join result as r
on r.studentno = s.studentno
inner join subject as sub
on r.subjectno = sub.subjectno
order by studentresult desc
;

2.分页

语法：limit + 当前页 , 页面的大小

-- 分页，每一页只显示两条数据
-- limit + 当前页 , 页面的大小,比如limit 1,5表示第二页，查五条数据，也就是第6~10条数据
select s.studentno,studentname,subjectname,studentresult
from student as s 
right join result as r
on r.studentno = s.studentno
inner join subject as sub
on r.subjectno = sub.subjectno
order by studentresult desc
limit 0,2
;

3.巩固练习

查询第一学年成绩排名前三并且分数大于80的学生信息（学号，姓名，课程名称，分数）

-- 思考：
-- 查询第一学年成绩排名前三并且分数大于80的学生信息（学号，姓名，课程名称，分数）
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join subject as sub
on r.subjectno = sub.subjectno
where studentresult>80
order by studentresult desc
limit 0,3;

七、子查询

本质：在where语句中嵌套查询一个子查询语句

-- 查询所有高等数学-1的考试结果（学号，科目号，成绩），降序排列
-- 方式一，使用连接查询
select studentno,s.subjectno,studentresult
from result as r
inner join subject as s
on r.subjectno = s.subjectno
where subjectname = '高等数学-1'
order by studentno desc
;

-- 方式二，使用子查询

-- 查询所有高等数学-1的学生的学号
select subjectno from subject
where subjectname = '高等数学-1';

-- 子查询
select studentno,subjectno,studentresult
from result
where subjectno = (
	select subjectno from subject
	where subjectname = '高等数学-1')
	order by studentresult desc;
;

八、Mysql函数

官网链接

1.常用函数

• 数学运算

select abs(-8); -- 绝对值
select ceiling(9.4); -- 向上取整
select floor(9.4); -- 向下取整
select rand(); -- 0~1之间的随机数
select sing(10); -- 返回一个数的符号：正数返回1,0返回0，负数返回-1

• 字符串函数

select CHAR_LENGTH('哈哈哈哈哈'); -- 返回字符串长度
select concat('我','爱','学习'); -- 拼接字符串
select insert('hhhh',1,2,'xxxxx'); -- 插入字符串,从某个位置开始替换某个长度
select lower('AAA');  -- 大写转小写
select upper('aaa');  -- 小写转大写
select instr('abcde','b'); -- 返回第一次出现的子串的索引
select replace('abcde','ab','ww'); -- 用指定的字符串替换子串
select substr('abcde',1,3); -- 返回指定位置的字符子串
select reverse('abcde'); -- 反转

• 时间和日期函数

select CURRENT_DATE(); -- 获取当前日期
select now(); -- 获取当前时间
select LOCALTIME() -- 获取本地时间
select SYSDATE() -- 系统时间
select year(now()) -- 年
select month(now()) -- 月
select day(now()) -- 日
select hour(now()) -- 时
select minute(now()) -- 分
select second(now()) -- 秒

• 系统函数

select SYSTEM_USER() 
select USER()
select version()

2.聚合函数

函数名称	描述
count	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
…	…

• 聚合函数的使用

-- 聚合函数
-- 三者都能统计表中的数据

select count(studentname) from student; -- count(指定列),会忽略所有的null值
select count(*) from student; -- count(*)，不会忽略null值
select count(1) from result; -- count(1)，不会忽略null值

select sum(studentresult) as 总和 from result;
select avg(studentresult) as 平均分 from result;
select max(studentresult) as 最高分 from result;
select min(studentresult) as 最低分 from result;

• group by和having的使用
  注意：
  使用group by分组后要进行条件筛选只能用having，且having的位置要放在group by的后面

-- 查询不同课程的平均分，最高分，最低分，且平均分必须大于80
select subjectname,max(studentresult),min(studentresult),avg(studentresult)
from subject as s
inner join result as r
on s.subjectno = r.subjectno
group by s.subjectno  -- 通过什么字段来分组
having min(studentresult)>80
;