DB_练习题
时间：2021-07-01 10:21:17
帮助过：17人阅读
查询名字为两个字符的人：
// SELECT id, NAME, birth, sex FROM t_student WHERE LENGTH(NAME) IN(2,4,6) ORDER BY birth DESC; (汉字3个字节长度，数字和字母是1个长度)
SELECT id, NAME, birth, sex FROM t_student WHERE NAME LIKE ‘__‘ ORDER BY birth DESC;
查询名字的长度：
SELECT NAME,LENGTH(NAME) FROM t_student;
对性别是男的进行计数：
SELECT COUNT(1) FROM t_student WHERE SEX=‘男‘;
查询名字不为空的数据：
SELECT * FROM t_student WHERE NAME IS NOT NULL;
查询2000年以后出生的人：
// SELECT * FROM t_student WHERE birth BETWEEN ‘2000-01-01‘ AND NOW();
SELECT * FROM t_student WHERE birth BETWEEN STR_TO_DATE(‘2000-01-01‘,‘%Y-%m-%d‘) AND NOW();
查询年龄在10周岁以内的人：
//SELECT id, NAME ,birth, DATEDIFF(NOW(),birth), sex FROM t_student WHERE DATEDIFF(NOW(),birth)<=3650;
SELECT * FROM (SELECT id, NAME ,birth, DATE_SUB(NOW(),INTERVAL 10 YEAR)m , sex FROM t_student) S WHERE m <= birth;
查询年龄最小的人，以及他现在几个月了，不足一个月按一个月算：
//SELECT * FROM (SELECT id, NAME ,birth, DATEDIFF(NOW(),birth) m , sex FROM t_student) S WHERE m=( SELECT MIN(m) FROM (SELECT id, NAME ,birth, DATEDIFF(NOW(),birth) m , sex FROM t_student) S );
SELECT *, TIMESTAMPDIFF(MONTH, MAX(a.`birth`), NOW()) MONTH FROM t_student a WHERE a.`birth` = (SELECT MAX(t.`birth`) birth FROM t_student t)
对每个性别的人进行计数：
SELECT sex , COUNT(sex) FROM t_student GROUP BY sex;
SELECT COUNT(sex=‘女‘ OR NULL) ‘女‘,COUNT(sex=‘男‘OR NULL) ‘男‘FROM t_student
检索重复的名字：
//SELECT NAME, c FROM (SELECT NAME,COUNT(NAME) C FROM t_student GROUP BY NAME) S WHERE C>=2
SELECT NAME,COUNT(NAME) C FROM t_student GROUP BY NAME HAVING c>=2
蚂蚁小姐： 带//的 都不建议，不规范，只做对比。
DB_练习题
标签：size 查询 select 习题 count rom gpo text 年龄
