MySql笔记(二) / 开普饭

一幅画，一次瞬间的回眸，就在那次画展上，那个眼神，温柔的流转，还是那干净的皮鞋，一尘不染，俊朗的眉宇性感的唇，悄悄走近，牵手一段浪漫

13、条件查询
14、对查询结果排序
15、汇总和分组数据结果
16、连接查询
17、子查询
18、联合查询
19、日期和时间函数
20、数字函数
21、字符串函数
22、系统函数

MySQL笔记(二)

13、条件查询

1.普通条件查询
语法：SELECT COL_LIST FROM TABLE_NAME
[WHERE CONDITION_EXPRESSION]

示例1：查询QQ号为12301的玩家信息SELECT*FROM USERS WHERE USER_QQ='12301'示例2：查询分数大于2500分的数据SELECT*FROM  SCORES WHERE SCORE>2500

2.比较运算符
等于 =
不等于 <>
大于>
大于等于 >=
小于 <
小于等于 <=

示例：查询游戏编号为1且分数大于4000分的分数信息SELECT * FROM SCORESWHERE GNO=1 AND SCORE>4000

3.逻辑运算符
并且 AND
或者 OR
非 NOT

示例：查询游戏编号为1和2的分数信息SELECT * FROM SCORES WHERE GNO=1 OR GNO=2

4.模糊查询

示例1：查询分数在2500（含）到3000（含）的分数信息SELECT * FROM SCORESWHERE SCORE>2500 AND SCORE<=3000或SELECT * FROM SCORESWHERE SCORE BETWEEN 2500 AND 3000示例2：查询分数不在2500（含）到3000（含）的分数信息SELECT * FROM SCORESWHERE SCORE NOT BETWEEN 2500 AND 3000示例3：查询1987年1月1日到1992年7月31日出生的玩家SELECT * FROM USERSWHERE USER_BIRTHDAYBETWEEN '1987-01-01' AND '1992-07-31'

5.通配符
'_' 一个字符
% 任意字符
[] 指定范围内
[^] 不在括号中

示例1：查询所有姓孙的玩家信息SELECT * FROM USERS        WHERE USER_NAME LIKE '孙%'示例2：查询所有非姓孙的玩家信息SELECT * FROM USERS        WHERE USER_NAME NOT LIKE '孙%'

6.查询空值的运算符

示例1：查询生日为NULL的玩家信息SELECT * FROM USERS WHERE USER_BIRTHDAY IS NULL示例2：查询生日不为NULL的玩家信息SELECT * FROM USERS WHERE USER_BIRTHDAY IS NOT NULL

14、对查询结果排序

1.对指定列进行排序
单列排序要素：
排序依据
排序方式
ASC表示：升序排序（默认方式）
DESC表示：降序排序

语法：SELECT COL_LIST FROM TABLE_NAME
ORDER BY ORDER_BY_LIST[ASC|DESC]

示例1：查询分数表中编号为1的所有分数信息，并按照分数升序排序SELECT * FROM SCORESWHERE GNO=1ORDER BY SCORE ASC示例2：查询分数表中编号为1的所有分数信息，并按照分数降序排序SELECT * FROM SCORESWHERE GNO=1ORDER BY SCORE DESC

2.多列排序
多列排序要点：
排序依据
排序方式
优先级

示例1：查询分数表所有信息，并按照游戏编号的升序和分数的降序进行排序SELECT * FROM SCORES ORDER BY GNO ASC,SCORE DESC

15、汇总和分组数据结果

1.聚合函数
聚合函数支持的数据类型描述
sum() 数字对指定列中的所有非空值求总和
avg() 数字对指定列中的所有非空值求平均值
min() 数字字符 dateinme 返回指定列中的最小数字，最早的日期或者最小的字符串
max() 数字字符 dateinme 返回指定列中的最大数字，最近的日期或者最大的字符串
conut() 任意基于行的数据类型统计结果集合中全部记录行的数量

示例1：查询玩家表中一共有多少名玩家信息语法1：SELECT COUNT(USER_QQ)FROM USERS语法2：SELECT COUNT(*)FROM USERS示例2：查询QQ号是12301的玩家游戏的总分数语法：SELECT SUM(SCORE) AS '总分数' FROM SCORES WHERE USER_QQ='12301'示例3：查询QQ号是12301的玩家游戏的平均分数语法：SELECT AVG(SCORE) AS '平均分数' FROM SCORES WHERE USER_QQ='12302'示例4：查询游戏编号是1的游戏的最高分数语法：SELECT MAX(SCORE)AS'最高分数' FROM SCORES WHERE GNO=1示例5：查询QQ号是12302的玩家的总分，平均分和最高分语法：SELECT SUM(SCORE)AS'总分' AVG(SCORE)AS'平均分'  MAX(SCORE)AS'最高分'FROM SCORES WHERE USER_QQ='12302'

2.在结果集内分组
使用GROUP BY 分组

示例1：查询每个玩家的总分数，平均分，最高分数语法：SELECT SUM(SCORE)AS'总分' AVG(SCORE)AS'平均分'  MAX(SCORE)AS'最高分'      FROM SCORES GROUP BY  user_qq示例2：查询每个玩家的平均分数，并显示玩家QQ和平均分数语法：SELECT USER_QQ,AVG(SCORE)AS'平均分'FROM SCORES GROUP BY  user_qq

3.筛选分组结果

示例1：查询平均分数大于4000的玩家QQ号，总分数，平均分数语法：SELECT USER_QQ,SUM(SCORE)AS'总分数',AVG(SCORE)AS'平均分数'FROM SCORES GROUP BY USER_QQ HAVING AVG(SCORE)>4000示例2：查询所有用户的平均分数和总分数，并按平均分倒序排列语法:SELECT USER_QQ,AVG(SCORE)AS'平均分数',SUM(SCORE)AS'总分数'FROM SCORES GROUP BY USER_QQ HAVING AVG(SCORE)DESC

SELECT语句的执行顺序
1.from子句指定数据源
2.where子句基于指定的条件对记录进行筛选
3.group by子句将数据划分为多个分组
4.使用聚合函数进行进一步的计算统计
5.使用having子句筛选分组
6.使用order by子句对结果集进行排序

16、连接查询

1.FROM子句进行多表查询
示例：查询分数信息，显示玩家昵称，游戏名称和分数

语法：SELECT USER_NAME AS'昵称',GNAME AS'游戏名称',SCORE AS'分数'FROM USERS,GAMES,SCOREWHERE USERS.USER_QQ=SCORES.USER_QQAND GAMES.GNO=SCORES.GNO

2.内连接
连接查询分为内连接和外连接两种
内连接特点：
1.相连接的两张表地位平等
2.如果一张表中在另一张表中不存在对应数据，侧不做连接

语法：SELECT USER_NAME ,scoreFROM USERS,SCORE WHERE users.user_qq=scores.user_qq

示例1：查询每个玩家的昵称，总分和平均分语法：SELECT USER_NAME AS'昵称',SUM(SCORE)AS'总分',AVG(SCORE)AS'平均分'FROM USERS U INNER JOIN SCORES S ON S.USER_QQ=U.USER_QQGROUP BY U.USER_QQ,USERS.USER_NAME示例2：查询平均分数大于3500的分数信息，显示玩家昵称，总分数，平均分数，并按照平均分数降序排列语法：SELECT USER_NAME AS'昵称',SUM(SCORE)AS'总分',AVG(SCORE)AS'平均分'FROM USERS U INNER JOIN SCORES S ON S.USER_QQ=U.USER_QQGROUP BY U.USER_QQ,USERS.USER_NAME HAVING AVG(SCORE)>3500ORDER BY AVG(SCORE) DESC

3.外连接
外连接分为左连接和右外连接
外连接的特点：
1.做连接的两个表地位不平等，其中有一张是基础表
2.基础表中的每条数据必须出现，即使另一张表没有数据与之匹配，也要用NULL补齐
3.左外连接时左表是基础表，右外连接时右表是基础表

外连接语法：SELECT COL_LIST    FROM TABLE1 LEFT|RIGHT[OUTER]JOIN TABLE2 ON TABLE1.COL=TABLE2.COL      示例：查询所有玩家关于5号游戏的分数信息      语法：SELECT USER_NAME AS'昵称'    GNO AS '游戏编号',SCORE AS'分数'    FROM USERS U LEFT JOIN SCORES S ON U.USER_QQ=S.USER_QQ    AND S.GNO=5

17、子查询

1.使用IN关键字的子查询
示例：查询游戏类型是‘棋牌类’的游戏的分数信息
1.游戏分数表中并包含游戏类型信息
2.采用连接查询
3.分两步进行，首先找到所有“棋牌类”游戏的编号，再以这一组编号为查询依据完成查询

语法：SELECT * FROM SCORES  WHERE GNO IN           (SELECT gno from games WHERE gtype='棋牌')

注意：子查询一般不写成SELECT*FROM

示例：查询没有参与5号游戏的玩家QQ语法：SELECT USER_QQ FROM USERS      WHERE USER_QQ NOT IN      (SELECT USER_QQ FROM SCORES WHERE GNO=5)

2.使用EXISTS关键字的子查询
示例；如果存在昵称为“孙悟空”侧查询分数表中数据
SELECT * FROM SCORES
WHERE EXISTS
(SELECT*FROM USERS WHERE USER_NAME='孙悟空')

18、联合查询

联合查询语法格式

SELECT_STATEMENTUNION[ALL]SELECT_STATEMENT[UNION[ALL]SELECT_STATEMENT][...n]

作用与特点：
1.可以把多条查询语句所产生的结果集纵向连接为一体
语句：SELECT user_name from users
UNION
SELECT gname from gemes
2.有ALL关键字可以显示全部数据（重复的也显示出来）
3.列的数量与类型都要兼容

示例1：查询玩家表中所有女性玩家和生日为空的玩家 语法：SELECT *FROM USERS WHERE USER_SEX='女'   UNION   SELECT * FORM USERS WHERE USER_BIRTHDAY IS NULL 示例2：查询QQ是"12301"的玩家所有分数并计算出总分数和平均分数，并显示到同一结果集中 语法：SELECT USER_QQ,GNO,SCORE FROM SCORES WHERE USER_QQ='12301'   UNION ALL   SELECT '总分', ' ',SUM(SCORE)FROM SCORES   WHERE USER_QQ='12301'   UNION ALL   SELECT '平均分', ' ',AVG(SCORE)FROM SCORES       WHERE USER_QQ='12301'

19、日期和时间函数

函数的概念：按指定格式输入参数，返回正确结果的运算单元
返回当前日期：
1.CURDATE()
2.CURRENT_DATE()
3.CURDATE()+0可以将当前日期值转换为数值型

语法：SELECT CURDATE()返回当前时间：    1.CURTIME()    2.CURRENT_TIME()            3.CURTIME()+0可以将当前时间值转换为数值型语法：SELECT CURTIME()返回当前日期和时间：  1.CURRENT_TIMESTAMP()  2.LOCALTIME()          3.NOW()          4.SYSDATE()语法：SELECT  NOW()获取月份：1.MONTH(DATE)2.MONTHNAME(DATE)语法：SELECT  MONTH("2015-09-22")获取星期：1.DAYNAME(DATE)2.DAYOFWEEK(DATE)3.WEEKDAY(DATE)4.WEEK(DATE)5.WEEKOFYEAR(DATE)    语法：SELECT  DAYNAME(2017-04-05)获取天数：1.DAYOFYEAR(DATE)2.DAYOFMONTH(DATE)语法：SELECT 1.DAYOFYEAR(2017-04-05)获取年份，季度，小时，分钟，秒钟1.YEAR(DATE)2.QUARTER(DATE)3.HOUR(TIME)4.MINUTE(TIME)5.SECOND(TIME)

20、数字函数

绝对值函数：
ABS(X)
语法：SELECT ABS(7)

符号函数：
SIGN(X)
语法：SELECT SIGN(-1)

获取随机数函数
1.RAND()
2.RAND(X)
语法：SELECT RAND()

获取整数的函数
1.CEIL(X)/CEILING(X)
2.FLOOR(X)

四舍五入函数
1.ROUND(X)
2.ROUND(X.Y)
3.TRUNCATE(X,Y)

求余数函数
MOD(X,Y)

域运算函数(平方根)
1.pow(x,y)或Power(x,y)
2.EXP(X)
3.SQRT(X)

角度弧度互换函数
1.RADIANS(X)
2.DEGREES(X)

圆周率函数
PI()

三角函数
1.SIN(X)
2.ASIN(X)
3.COS(X)
4.ACOS(X)
5.TAN(X)
6.ATAN(X)
7.COT(X)

21、字符串函数

1.计算字符数和字符串长度的函数
1.CHAR_LENGTH(S)
2.LENGTH(S)
语法：SELECT LENGTH('s')

2.合并字符串函数
1.CONCAT()
语法：SELECT CONCAT('HH','逗逼')

3.替换字符串函数
1.INSERT(S1,X,LEN,S2)
2.REPLACE(S,S1,S2)
语法：SELECT INSERT('ADCDE',2,3,'##')

4.截取字符串的函数
1.LEFT(S,N)
2.RIGHT(S,N)
语法：SELECT LEFT()

5.重复生成字符串函数
REPEAT(S,N)
语法：SELECT REPEAT('ABC',3)

6.大小写转换函数
1.LOWER(X)/LCASE(X)
2.UPPER(X)/UCASE(X)
语法：SELECT LCASE('ABCD')

7.填充字符串函数
1.LPAD(S1,LEN,S2)
2.RPAD(S1,LEN,S2)
语法：SELECT LPAD('abcd',10,'#')

8.删除空格函数
1.LTRIM(S)/RTRIM(S)
2.TRIM(S)
语法：SELECT RTRIM(' ABCD ')

9.删除指定字符串
TRIM(S1 FROM S)
语法：SELECT TRIM('A' FROM 'ABCADE')
10.获取子字符串
SUBSTRING(S,N,LEN)
MID(S,N,LEN)
语法：SELECT SUBSTRING('abcade',3,2)

11.返回指定位置字符串函数
ELT(N,S1,S2)
语法：SELECT ELT(2,'ABC','DEF','MYSQL')

12.返回指定字符串位置
FIELD(S,S1,S2)

语法：SELECT FIELD('ABC','ABC','DEF','MYSQL')

22、系统函数

1.获取MySQL版本号的函数
VERSION()

2.查看当前用户的连接数
CONNECTION_ID()

3.查看当前所用数据库函数
1.DATABASE()
2.SCHEMA()

4.获取用户名的函数
1.USER()
2.CURRENT_USER()
3.SYSTEM_USER()
4.SESSION_USER()

MySql笔记(二)