DB_练习题

查询名字为两个字符的人：

// 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

 

 

蚂蚁小姐： 带//的 都不建议，不规范，只做对比。