位置：首页-资讯-数据库

MySql笔记(二)

2014-11-09 22:35

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒

MySql笔记(二)

MySQL笔记(二)

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

MySQL笔记(二)

13、条件查询

普通条件查询
语法：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 SCORES
    WHERE GNO=1 AND SCORE>4000

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

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

模糊查询

示例1：查询分数在2500（含）到3000（含）的分数信息
        SELECT * FROM SCORES
        WHERE SCORE>2500 AND SCORE<=3000
    或
        SELECT * FROM SCORES
        WHERE SCORE BETWEEN 2500 AND 3000
示例2：查询分数不在2500（含）到3000（含）的分数信息
    SELECT * FROM SCORES
    WHERE SCORE NOT BETWEEN 2500 AND 3000

示例3：查询1987年1月1日到1992年7月31日出生的玩家
    SELECT * FROM USERS
    WHERE USER_BIRTHDAY
    BETWEEN '1987-01-01' AND '1992-07-31'

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

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

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

查询空值的运算符

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

14、对查询结果排序

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

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

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

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

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

15、汇总和分组数据结果

聚合函数
聚合函数支持的数据类型描述
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'

在结果集内分组
使用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

筛选分组结果

示例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、连接查询

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

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

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

语法：SELECT USER_NAME ,score
        FROM 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_QQ
    GROUP 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_QQ
    GROUP BY U.USER_QQ,USERS.USER_NAME 
    HAVING AVG(SCORE)>3500
    ORDER BY AVG(SCORE) DESC

外连接
外连接分为左连接和右外连接
外连接的特点：
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、子查询

使用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)

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

18、联合查询

联合查询语法格式

    SELECT_STATEMENT
UNION[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)