数据库基础

SELECT*
FROM gy_user
WHERE sname IS NULL;

SELECT*
FROM gy_user
ORDER BY age;

SELECT*
FROM gy_user
ORDER BY age DESC;

SELECT *
FROM gy_user
WHERE realname LIKE '%吴%'
ORDER BY age DESC;

SELECT*
FROM gy_user
LIMIT 500;

SELECT*
FROM gy_user
LIMIT 100,10;

SELECT*
FROM gy_user
LIMIT 50,30;

SELECT DISTINCT  realname
FROM gy_user;

SELECT  realname,age
FROM gy_user
WHERE realname LIKE '张%' OR realname LIKE '王%' OR realname LIKE '李%' OR realname LIKE '杨%'
ORDER BY age DESC;

31;
SELECT DISTINCT  realname
FROM gy_user
WHERE sname LIKE '%guoya%';

32;
SELECT*
FROM gy_user
WHERE id IN (50,60,70,80) AND realname IS NOT NULL;

33;
SELECT realname,age,address
FROM gy_user
WHERE age IN (20,30,40) AND (reanlname LIKE '%张%' OR reanlname LIKE '%吴%');

34;
SELECT sname,age,address
FROM gy_user
WHERE sno>50  AND address LIKE '%上海%'
LIMIT 100;

35;
SELECT DISTINCT realname
FROM gy_user
WHERE realname LIKE '_' 
ORDER BY age DESC;

36;
SELECT DISTINCT sname
FROM gy_user
WHERE address IS NOT NULL
LIMIT 30;

37;
SELECT*
FROM gy_user
WHERE phone IS NOT NULL AND address IS NOT NULL AND sno>20
LIMIT 10,10;

38;
SELECT*
FROM gy_user
WHERE age>20 AND class_type=1
ORDER BY sno DESC ,age ASC;

39;
SELECT DISTINCT realname
FROM gy_user
WHERE weichat IS NOT NULL AND phone IS NOT NULL AND realname LIKE '%王%'
ORDER BY age DESC ;

40;
SELECT DISTINCT sname
FROM gy_user
WHERE weichat IS NOT NULL
ORDER BY age DESC 
LIMIT 100,400; 

41;
SELECT COUNT(sno)
FROM gy_user;

42;
SELECT MAX(age)
FROM gy_user;

43;
SELECT MIN(age)
FROM gy_user;

44;
SELECT AVG(age)
FROM gy_user;

45;
SELECT SUM(age)
FROM gy_user;

46;-- 求每一个班级的总年龄
SELECT SUM(age),class_type
FROM gy_user
GROUP BY class_type;

47;-- 求每一个班级的平均年龄
SELECT AVG(age),class_type
FROM gy_user
GROUP BY class_type;

48;-- 求每一个学历的最大年龄
SELECT MAX(age),education
FROM gy_user
GROUP BY education;

49;-- 求每一个学历的平均年龄
SELECT AVG(age),education
FROM gy_user
GROUP BY education;

50;-- 查询各班级中平均年龄大于20的班级是
SELECT class_type,AVG(age)
FROM gy_user
HAVING AVG(age)>20;

51;-- 查询不同学历的各自人数
SELECT COUNT(education)
FROM gy_user
GROUP BY education;

52;-- 查询不同学历 最小年龄 小于30的学历是
SELECT education,MIN(age)
FROM gy_user
GROUP BY education
HAVING MIN(age)<30;

53;-- 查询各班级中最小年龄大于2的班级是
SELECT class_type,MIN(age)
FROM gy_user
GROUP BY class_type
HAVING MIN(age)>2;

54;-- 查询各班级中平均年龄小于23的班级是
SELECT class_type,AVG(age)
FROM gy_user
GROUP BY class_type
HAVING AVG(age)<23;

55;-- 查询各班级中年龄总和小于500的班级是
SELECT class_type,SUM(age)
FROM gy_user
GROUP BY class_type
HAVING SUM(age)<500;

day9数据库基础

day9数据库基础.png