mysql查询语句使用

在这里插入图片描述
#=常用函数===

SELECT MAX(score) FROM student_info

SELECT MIN(score) FROM student_info;

SELECT AVG(score) FROM student_info;

#求和
SELECT SUM(score) FROM student_info;

#统计条数
SELECT COUNT(*) FROM student_info;

#统计某个字段数量
SELECT COUNT(username) FROM student_info;

SELECT NOW() AS cur_time;

#只获取 时分秒
SELECT CURTIME();

#获取系统年月日
SELECT CURDATE();

#向上去舍
SELECT CEIL(2.3);

#向下取舍
SELECT FLOOR(2.3);

SELECT RAND();

#查询某几条字段
SELECT * FROM student_info WHERE id IN(1,11,14);

#查询处记录并排序 asc 升序
SELECT * FROM student_info WHERE id NOT IN(2) ORDER BY score ASC;

3.20
#===分组查询

#查询类别
SELECT student_id FROM id_subjects GROUP BY student_id;

SELECT * FROM id_subjects GROUP BY student_id;

SELECT * FROM id_subjects GROUP BY student_id HAVING score=100

#分页查询

第一页

SELECT * FROM student_info LIMIT 0,5;

第2页

SELECT * FROM student_info LIMIT 5,5;

第3页

SELECT * FROM student_info LIMIT 10,5;

#===多表查询

#子查询
SELECT * FROM student_info WHERE id IN(1,11,13)

SELECT MAX(score) AS top,username FROM student_info;

SELECT MAX(score) username FROM student_info;

SELECT MIN(score) FROM student_info;

SELECT username FROM student_info WHERE

#同时查询多张 表
SELECT u.username ,temp.maxScore FROM student_info u,
(SELECT MAX(score) AS maxScore FROM student_info) temp
WHERE u.score=temp.maxScore

SELECT * FROM id_subjects s,subjects temp WHERE s.course_num=temp.course_num

SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp WHERE s.course_num=temp.course_num

SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp
WHERE s.course_num=temp.course_num AND s.student_id=011118239

SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp
WHERE s.course_num=temp.course_num AND s.student_id IN(011118239,011118234)

#联合查询
SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp
WHERE s.course_num=temp.course_num AND s.student_id=011118239
UNION
SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp
WHERE s.course_num=temp.course_num AND s.student_id=011118234

#=模糊查询========
SELECT * FROM subjects WHERE teacher LIKE ‘Mrs%’

SELECT * FROM subjects WHERE teacher LIKE ‘%in’

SELECT * FROM subjects WHERE teacher LIKE ‘%Li%’

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值