2018-10-25数据库基础

数据库基础的思维导图

day09数据库基础.png

数据库的重要优先级顺序

image.png image.png

查询例子

SELECT *
FROM gy_user
WHERE sname IS NULL
字段不为空
SELECT *
FROM gy_user
WHERE sname IS NOT NULL

排序升序
SELECT *
FROM gy_user
ORDER BY age;

排序降序
SELECT *
FROM gy_user
ORDER BY age DESC;
编号以倒序的方式排列
SELECT *
FROM gy_user
ORDER BY sno DESC;

带判断条件降序升序
SELECT *
FROM gy_user
WHERE realname LIKE '%吴%'
ORDER BY sno DESC;
指定限制前10条
SELECT *
FROM gy_user
LIMIT 10

-- 指定限制前100-110   limit 数字
SELECT *
FROM gy_user
LIMIT 100,10

-- 指定限制前50-80
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 ;

-- 查询用户名包含guoya的用户,根据真实姓名去重
SELECT  DISTINCT  realname
FROM  gy_user
WHERE  sname  LIKE  '%guoya%';
SELECT *
FROM gy_user
-- 30.查询真实姓名,姓氏为 张  王 李 杨的学生,按年龄倒序排序 只显示真实姓名 年龄
SELECT realname,age
FROM gy_user
WHERE realname LIKE '%张%'OR realname LIKE '%王%'OR realname LIKE '%李%'OR realname LIKE '%张%'
ORDER BY age DESC
-- 31.查询用户名包含 guoya的用户 ,根据真实姓名去重
SELECT DISTINCT realname
FROM gy_user
WHERE sname LIKE '%guoya%'
-- 32.查询用户sno 在 50 60 70 80,并且真实姓名不为空的用户
SELECT *
FROM gy_user
WHERE sno IN(50,60,70,80) AND realname IS NOT NULL
-- 33.查询用户表 年龄在20,30,40 姓名包含张,吴,显示真实姓名,年龄,地址
SELECT realname,age,address
FROM gy_user
WHERE age IN(20,30,40)AND realname LIKE '%张%' OR realname LIKE '%吴%'
-- 34.查询用户表 编号大于50 地址在上海的展示 用户名,年龄,地址 的前100条数据
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.查询用户表 地址不为空,对用户名去重,显示前30条
SELECT
DISTINCT realname
FROM gy_user
WHERE address IS NOT NULL
LIMIT 30

-- 37 查询用户表年龄大于20,班级类型为1,以id倒叙排序,以年龄正序排序
SELECT*
FROM gy_user
WHERE age>20 AND class_type='1' 
ORDER BY education DESC, age ASC

-- 38 查询微信号,手机好=号不为空,姓名包含王,以年龄倒叙排序,显示内容真实姓名去重
SELECT DISTINCT realname
FROM gy_user
WHERE   weichat IS NOT NULL OR phone IS NOT NULL AND realname LIKE '%王%'
ORDER BY age DESC

-- 39 查询用户编号100-500区间,微信号不为空,以年龄倒叙,用户名去重显示
SELECT DISTINCT sname
FROM  gy_user
WHERE weichat  IS NOT NULL AND (sno BETWEEN 100 AND 500)
ORDER BY age DESC

-- 统计条目数 count()
SELECT COUNT (sno)
FROM gy_user
-- 统计最大
SELECT MAX (age)
FROM gy_user
-- 统计最小
SELECT MAX (age)
FROM gy_user
-- 求平均值
SELECT AVG (age)
FROM gy_user
-- 求和
SELECT SUM (age)
FROM gy_user

SELECT SUM(age),class_type
FROM gy_user
GROUP BY class_type

SELECT AVG (age),class_type
FROM gy_user
GROUP BY class_type

SELECT MAX(age),class_type
FROM gy_user
GROUP BY education

SELECT AVG(age),class_type
FROM gy_user
GROUP BY education
查询各班级中平均年龄大于20的班级是
SELECT class_type,AVG(age)
FROM gy_user
GROUP BY class_type
HAVING AVG(age)>20
-- 查询不同学历的各自人数
SELECT education,COUNT(sno)
FROM gy_user
GROUP BY education
-- 查询不同学历 最小年龄  小于30的学历
SELECT MIN(age),education
FROM gy_user
GROUP BY education
HAVING MIN(age)<30


-- 查询各班级中平均年龄大于20的班级
   SELECT AVG(age)
   FROM gy_user
   GROUP BY class_type
   HAVING AVG(age)>20
-- 查询各班级中最小年龄小于2的班级
   SELECT MIN(age)
   FROM gy_user
   GROUP BY class_type
   HAVING MIN(age)>2
-- 查询各班级中平均年龄小于23的班级
SELECT AVG(age)
   FROM gy_user
   GROUP BY class_type
   HAVING AVG(age)<23
-- 查询各班级中年龄总和小于500的班级
SELECT SUM(age)
   FROM gy_user
   GROUP BY class_type
   HAVING SUM(age)<500

**注:

单词	中文意思
or	或者
and	和
between....and	在什么之间
in	在什么之内
order by	排序
asc	升序
desc	降序
count	统计
sum	求和
avg	平均值
max	最大
min	最小
limit	限制
distinct	去重
having	持有