DQL拓展

DQL拓展

where子语句

where的作用是检索数据中心符合条件的值。

 

常用逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or ||	a or b a||b	逻辑或
not !	not a !a	逻辑非

 

常用比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null，返回ture。
is not null	a is not null	如果操作符为not null，返回ture。
between	a between b and c	如果a在b与c之间，返回ture。
like	a like b	SQL匹配，如果a匹配b，返回ture。
in	a in(a1,a2,a3,a4...)	a如果在in参数的某一个值中，返回ture。

 

常用模糊查询通配符（主要配合like使用）

运算符	语法	描述
%	a%	表示任意个字符。
_	a_	表示单个字符。

 

 

联表查询

 

 

jion的简单使用方法

比如我们需要查询学生每一科的成绩，但是数据分属于两张表的情况。

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.`studentno` = r.`studentno`;

 

得到上表后我们觉得没有学科名看起来还是很麻烦，于是我们使用subjectno再关联subject表。

select s.`studentno`,`studentname`,`subjectname`,`studentresult`
from `student` as s
inner join `result` as r
inner join `subject` as su
where s.`studentno` = r.`studentno` and r.`subjectno` = su.`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','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');

 

---

 

查询：

SELECT a.`categoryname` AS '顶级科目',b.`categoryname` AS '子科目'
FROM `category` a,`category` b
WHERE a.`categoryid` = b.`pid`;

 

排序和分页

排序

用得较少，一般使用前端去排序。

ORDER BY `字段名` DESC;
#降序

ORDER BY `字段名` DESC;
#升序

 

修改之前的jion代码，使用学生分数来降序排列：

select s.`studentno`,`studentname`,`subjectname`,`studentresult`
from `student` as s
inner join `result` as r
inner join `subject` as su
where s.`studentno` = r.`studentno` and r.`subjectno` = su.`subjectno`
order by `studentresult` desc;

 

分页

用得较多，使用分页是为了缓解数据库的压力。

现在一些网站对于大数据会使用瀑布流来代替分页。

语法

LIMIT 起始值,显示数量

 

实际应用

LIMIT (n-1)*pageSize,pageSize

• pageSize：页面大小

• n：当前页

• 总页数：数据总数/页面大小，向上取整。

 

Mysql部分常用方法

数学运算相关

SELECT ABS(-6);
#绝对值
​
SELECT CEILING(9.4);
#向上取整
​
SELECT FLOOR(9.4);
#向下取整
​
SELECT RAND();
#随机数

 

字符串相关

select char_length('破碎的人没有心');
#字符串长度
​
select concat('我','永远','喜欢','桃子');
#拼接字符串
​
select insert('我爱编程',1,2,'超级热爱');
#指定位置插入字符串，类似windows的insert键
​
select upper('abc');
#转换大写
​
select lower('ABC');
#转换小写
​
select substring('破碎的人没有心',4,2)
#截取字符串

 

时间和日期相关

select current_date();
#获取当前日期
​
select curdate();
#获取当前时间
​
select now();
#获取当前的时间
​
select localtime();
#获取本地时间

 

聚合方法（常用）

名称	作用
COUNT()	计数
SUM()	求和
AVG()	平均数
MIN()	最小值
MAX()	最大值

 

count

select count(`studentno`)
from `student`;
#忽略null行
​
select count(*)
from `student`;
#不忽略null行
#查询行数
​
SELECT COUNT(1)
FROM `student`;
#不忽略null行
#所有数据用1填充，查询行数

 

other

SELECT AVG(`studentresult`) AS '平均分'
FROM `result`;
​
SELECT SUM(`studentresult`) AS '总分'
FROM `result`;
​
SELECT MAX(`studentresult`) AS '最高分'
FROM `result`;
​
SELECT MIN(`studentresult`) AS '最低分'
FROM `result`;