数据库基础
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
