导图社区 mysql 数据查询总结 -思维导图
- 208
- 16
- 1
- 举报
mysql 数据查询总结 -思维导图
MySQL 数据库使用SQL SELECT语句来查询数据。你可以通过 mysql> 命令提示窗口中在数据库中查询数据，或者通过PHP脚本来查询数据。
编辑于2021-05-12 23:47:31- 查询总结
- 相似推荐
- 大纲
3-3 数据查询总结 -思维导图
一. MYSQL中的查询语法
Select 列名
From 表名
Where 条件
Order by 列名 [ ASC | DESC ]
Group by 列名
Having 条件
二. Select 子句
1.功能：控制结果中的列及列的显示形式（一次性的）
2.用法：
（1） * —— 全部列
（2） 列名1，列名2，…… —— 部分列名
（3） 列名 [as] 别名 —— 为列名设置别名
（4） 列名+1 —— 计算字段（在列名上进行计算）
（5） 常量 —— 插入一个列名和值都为常量的列（一次性的）
（6） 统计函数 —— 对原始表数据进行统计
（7） distinct —— 去掉重复的值
（8） 纯计算值——不使用数据源（无from子句配合）
MYSQL中的统计函数 含义 语法结构 适用范围 和 SUM([ALL | DISTINCT ] expression) 数值型 平均值 AVG([ALL | DISTINCT ] expression ) 数值型 最大值 MAX([ALL | DISTINCT ] expression) 数值型、字符串型、日期时间型等 最小值 MIN([ALL | DISTINCT ] expression) 数值型、字符串型、日期时间型等 计数 COUNT([ [ALL | DISTINCT ] expression ] | *) 任意（计算的是行的数）
3.练习：
（1）查询所有人的基本情况
（2）在结果中显示姓名、生日、地址
（3）在结果中分别把各个列名显示为相应的中文
（4）在结果表格中把“生日”列换成显示每个人的年龄
（5）在第4题的基础上，在年龄列前面增加一列，其列名和内容都为“年龄为：”
（6）在结果中显示表格中出现过的城市名
（7）分别计算来自各省市的人有多少
二. Where子句
1.功能：对表数据分别从每一行进行筛选
2.用法：
（1） 当左侧”列名”为数值型时:
比较运算符可以有：=,,>,=,!>,!=,!,between a and b,is null,is not null,in,not in
（2） 当左侧“列名”为字符型时：
比较运算符可以有：=,like,not like,is null,is not null,in,not in
【补充】like表示匹配，可跟匹配%（任意字符），_（一个字符），escape(转义符)
如：like ‘刘%’,like ‘刘_‘，like ‘DB\_Ds_’ escape ‘\’
（3）当有多个条件时，使用and,or多重条件的逻辑运算
（4）当右侧的值为某次查询的结果时：
分析查询的结果是一个值还是多个值，再确定是用单值匹配还是多值的集合比较
“子查询”，即先查询小括号内部的数据，再将结果作为值去执行外部的查询。
子查询也分相关子查询与非相关子查询
非相关子查询：内部查询可独立于外部查询，单独进行。如：查询成绩高于平均成绩的人的信息
相关子查询：内部查询必须与条件表达式左侧的列名值有一定的关联。如：查询成绩高于本门课程的平均成绩的学生信息
MYSQL中的运算符 类型 运算符 用途 比较运算符 >，，>=，，!>，!，=，!=， 数值计算 确定范围 Between and ，not between and 数值范围 确定集合 In，not in 选择 字符匹配 Like , not like (通配符：%，_) 字符串 空值 Is null, is not null 多重条件 And , or , all , any , exists 正则表达式 Regexp *
3.练习
（1）查询年龄大于22岁的学生信息
（2）查询年龄在22-24之间的学生信息
（3）查询家住成都的学生信息
（4）查询一位姓“李”的学生信息
（5）查询学号尾数为1、3、8的学生信息
（6）查询一位姓“张”的，年龄大于22的学生信息
（7）查询无家庭地址信息的学生信息
（8）查询成绩高于平均成绩的学生的学号
（9）查询成绩高于本门课程平均成绩的学生的学号
（10）查询女生的成绩
（11）查询“软件工程”课程女生的成绩
三、from子句
1.功能：确定数据源
2.用法：
（1）表名1，表名2，… ——笛卡尔积
注：多张表先笛卡尔积，可能会占用大量的资源
（2）表名1 join 表名2 on (条件) ——连接
注：join分内连接和外连接，默认为内连接，即inner join
外连接分为：左外连接left join，右外连接 right join
（3）某次查询——多重查询
注：（select…）as 别名 的方式可以对某次查询的结果进行二次查询
3.练习：
（1）查询学生的成绩信息，要求显示为：学生姓名，课程名，成绩
（2）查询没有选课的学生的信息
（3）查询所有女生的“软件工程”课程的成绩
四、order by子句
1.功能：排序
2.用法：
Order by 列名1 Asc|DESC,列名2 ASC|DESC
3.练习
（1）查询年龄最小的三个学生的信息
（2）查询成绩最高的学生信息，若成绩相同的，则按年龄从大到小的顺序显示
五、group by 子句
1. 功能：分组
2.用法 ：
Group by 列名
注：一旦在需求中看到“每个”、“各个”之类的描述时，第一考虑使用group by 子句
注：group by语句通常与统计函数配套使用
注：一旦group by后，表被分为若干个组，就只能基于分组（一堆数据）进行统计，而不能显示每一行的详细信息。
3. 练习
（1）查询每门课程的平均成绩
（2）分别查询“软件工程”课程男女生的平均成绩
（3）查询每个学生的平均成绩、最高成绩、最低成绩
（4）查询每门课程的及格率
六、having子句
1.功能：
对分组进行筛选（类似于where，但他筛选的对象是每一组）
2.用法：
Having 分组值 比较运算符 值
3.练习
（1）查询平均分低于86的课程的信息
（2）查询选修人数大于5的课程信息
（3）查询选修的女生人数大于5的课程信息
七、limit子句
1.功能：限定显示的行记录
2.用法：
Limit a,b //a：返回记录行的偏移量（默认从0开始），b：返回记录行的最大数目
注：limit n等价于limit 0,n 表示前n行（即1~n行）
注：limit 5,10表示第6~15行
注：limit 5，-1表示第6行~最后一行
Limit常用于分页，如：
select * from student limit (page-1)*size,size; //page表示当前页码，size表示每页多少条记录
3.练习：
（1）查询成绩最高的前三个学生的学号
（2）查询成绩第3名的学生的学号
附：MYSQL中的常用函数
1 数学函数
abs(x)
pi()
mod(x,y)
sqrt(x)
ceil(x)或者ceiling(x)
rand(),rand(N):返回0-1间的浮点数，使用不同的seed N可以获得不同的随机数
round(x, D)：四舍五入保留D位小数，D默认为0， 可以为负数， 如round(19, -1)返回20
truncate(x, D):截断至保留D位小数，D可以为负数， 如trancate(19,-1)返回10
sign(x): 返回x的符号，正负零分别返回1， -1， 0
pow(x,y)或者power(x,y)
exp(x)：e^x
log(x)：自然对数
log10(x)：以10为底的对数
radians(x):角度换弧度
degrees(x):弧度换角度
sin(x)和asin(x):
cos(x)和acos(x):
tan(x)和atan(x):
cot(x):
2 字符串函数
char_length(str):返回str所包含的字符数，一个多字节字符算一个字符
length(str): 返回字符串的字节长度，如utf8中，一个汉字3字节，数字和字母算一个字节
concat(s1, s1, ...): 返回连接参数产生的字符串
concat_ws(x, s1, s2, ...): 使用连接符x连接其他参数产生的字符串
INSERT(str,pos,len,newstr):返回str,其起始于pos，长度为len的子串被newstr取代。
1. 若pos不在str范围内，则返回原字符串str
2. 若str中从pos开始的子串不足len,则将从pos开始的剩余字符用newstr取代
3. 计算pos时从1开始，若pos=3,则从第3个字符开始替换
lower（str)或者lcase(str):
upper(str)或者ucase(str):
left(s,n):返回字符串s最左边n个字符
right(s,n): 返回字符串最右边n个字符
lpad(s1, len, s2): 用s2在s1左边填充至长度为len, 若s1的长度大于len,则截断字符串s1至长度len返回
rpad(s1, len, s2):
ltrim(s):删除s左侧空格字符
rtrim(s):
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)或TRIM([remstr FROM] str)：从str中删除remstr, remstr默认为空白字符
REPEAT(str,count)：返回str重复count次得到的新字符串
REPLACE(str,from_str,to_str)： 将str中的from_str全部替换成to_str
SPACE(N):返回长度为N的空白字符串
STRCMP(str1,str2):若str1和str2相同，返回0， 若str1小于str2, 返回-1， 否则返回1.
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),MID(str,pos,len): 获取特定位置，特定长度的子字符串
LOCATE(substr,str), LOCATE(substr,str,pos),INSTR(str,substr),POSITION(substr IN str): 返回字符串中特定子串的位置，注意这里INSTR与其他函数的参数位置是相反的
REVERSE(str)
ELT(N,str1,str2,str3,...)：返回参数strN, 若N大于str参数个数，则返回NULL
FIELD(str,str1,str2,str3,...): 返回str在后面的str列表中第一次出现的位置，若找不到str或者str为NULL, 则返回0
FIND_IN_SET(str,strlist)：strlist是由','分隔的字符串，若str不在strlist或者strlist为空字符串，则返回0；若任意一个参数为NULL则返回ＮＵＬＬ
MAKE_SET(bits,str1,str2,...): 由bits的作为位图来选取strN参数，选中的参数用','连接后返回
3 日期和时间函数
CURDATE(), CURRENT_DATE, CURRENT_DATE():用于获取当前日期，格式为'YYYY-MM-DD'; 若+0则返回YYYYMMDD
UTC_DATE, UTC_DATE():返回当前世界标准时间
CURTIME([fsp]), CURRENT_TIME, CURRENT_TIME([fsp]): 用于获取当前时间， 格式为'HH:MM:SS' 若+0则返回 HHMMSS
UTC_TIME, UTC_TIME([fsp])
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP([fsp]), LOCALTIME, LOCALTIME([fsp]), SYSDATE([fsp]), NOW([fsp]): 用于获取当前的时间日期，格式为'YYYY-MM-DD HH:MM:SS'，若+0则返回YYYYMMDDHHMMSS
UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)：返回一个unix时间戳（'1970-01-01 00:00:00' UTC至今或者date的秒数），这实际上是从字符串到整数的一个转化过程
FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)：从时间戳返回'YYYY-MM-DD HH:MM:SS' 或者YYYYMMDDHHMMSS，加入format后根据所需的format显示。
MONTH(date)
MONTHNAME(date)
DAYNAME(date)
DAY(date)，DAYOFMONTH(date)：1-31或者0
DAYOFWEEK(date)：1-7==>星期天-星期六
DAYOFYEAR(date)： 1-365（366）
WEEK(date[,mode])：判断是一年的第几周，如果1-1所在周在新的一年多于4天，则将其定为第一周；否则将其定为上一年的最后一周。mode是用来人为定义一周从星期几开始。
WEEKOFYEAR(date)：类似week(date,3)，从周一开始计算一周。
QUARTER(date)：返回1-4
HOUR(time)：返回时间中的小时数，可以大于24
MINUTE(time)：
TIME_TO_SEC(time)
SEC_TO_TIME(seconds)
TO_DAYS(date): 从第0年开始的天数
TO_SECNDS(expr)：从第0年开始的秒数
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days),DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
ADDTIME(expr1,expr2)
SUBTIME(expr1,expr2)
DATE_FORMAT(date,format):
DATEDIFF(expr1,expr2):返回相差的天数
TIMEDIFF(expr1,expr2)：返回相隔的时间
4 条件判断函数
IF(expr1,expr2,expr3)：如果expr1不为0或者NULL,则返回expr2的值，否则返回expr3的值
IFNULL(expr1,expr2)：如果expr1不为NULL,返回expr1,否则返回expr2
NULLIF(expr1,expr2): 如果expr1=expr2则返回NULL, 否则返回expr2
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
当compare_value=value时返回result
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
当condition为TRUE时返回result
5 系统信息函数
VERSION():返回mysql服务器的版本，是utf8编码的字符串
CONNECTION_ID()：显示连接号（连接的线程号）
DATABASE()，SCHEMA()：显示当前使用的数据库
SESSION_USER(), SYSTEM_USER(), USER(), CURRENT_USER, CURRENT_USER():返回当前的用户名@主机，utf8编码字符串
CHARSET(str)
COLLATION(str)
LAST_INSERT_ID()：自动返回最后一个insert或者update查询， 为auto_increment列设置的第一个发生的值
6 加密和压缩函数
PASSWORD(str):这个函数的输出与变量old_password有关。old_password 在mysql5.6中默认为0。 不同取值的效果如下表
old_password=1时， password(str)的效果与old_password(str)相同，由于其不够安全已经弃用（5.6.5以后）。
old_password=2时，在生成哈希密码时会随机加盐。
MD5(str):计算MD5 128位校验和，返回32位16进制数构成的字符串，当str为NULL时返回NULL。可以用作哈希密码
SHA1(str), SHA(str)：计算160位校验和，返回40位16进制数构成的字符串，当str为NULL时返回NULL。
SHA2(str, hash_length)：计算SHA-2系列的哈希方法(SHA-224, SHA-256, SHA-384, and SHA-512). 第一个参数为待校验字符串，第二个参数为结果的位数（224， 256， 384， 512）
ENCRYPT(str[,salt]): 用unix crypt()来加密str. salt至少要有两位字符，否则会返回NULL。若未指定salt参数，则会随机添加salt。
ECODE(crypt_str,pass_str):解密crypt_str, pass_str用作密码
ENCODE(str,pass_str)：用pass_str作为密码加密str
DES_ENCRYPT(str[,{key_num|key_str}])：用Triple-DES算法编码str， 这个函数只有在mysql配置成支持ssl时才可用。
DES_DECRYPT(crypt_str[,key_str])
AES_ENCRYPT(str,key_str[,init_vector])
AES_DECRYPT(crypt_str,key_str[,init_vector])
COMPRESS(string_to_compress)：返回二进制码
UNCOMPRESS(string_to_uncompress)
7 聚合函数
若在没使用group by时使用聚合函数，相当于把所有的行都归于一组来进行处理。除非特殊说明，一般聚合函数会忽略掉NULL.
AVG([DISTINCT] expr): 返回expr的平均值，distinct选项用于忽略重复值
COUNT([DISTINCT] expr)：返回select中expr的非0值个数，返回值为bigint类型
group_concat:连接组内的非空值，若无非空值，则返回NULL
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
MAX([DISTINCT] expr)
MIN([DISTINCT] expr)
SUM([DISTINCT] expr)
VAR_POP(expr)
VARIANCE(expr)：同VAR_POP(expr)，但是这是标准sql的一个扩展函数
VAR_SAMP(expr)
STD(expr): 这是标准sql的一个扩展函数
STDDEV(expr)：这个函数是为了跟oracle兼容而设置的
STDDEV_POP(expr)：这个是sql标准函数
STDDEV_SAMP(expr)：样本标准差
8 格式或类型转化函数
FORMAT(X,D[,locale])：将数字X转化成'#,###,###.##'格式，D为保留的小数位数
CONV(N,from_base,to_base)：改变数字N的进制，返回值为该进制下的数字构成的字符串
INET_ATON(expr)：ip字符串转数字
INET_NTOA(expr)：数字转ip字符串
CAST(expr AS type)：转换数据类型
CONVERT(expr,type), CONVERT(expr USING transcoding_name)： type可以为BINARY[(N)]，CHAR[(N)]，DATE，DATETIME， DECIMAL[(M[,D])]，DECIMAL[(M[,D])]，TIME，UNSIGNED [INTEGER]等等。transcoding_name如utf8等等