软件实施-SQL基础

SQL

DQL（数据查询语言）

基本格式

    SELECT fied1, fied2...
    FROM table
    [WHERE ...]
    [GROUP BY ...]
    [HAVING ...]
    [ORDER BY ...] 

基本查询

DISTINCT 唯一性

    SELECT  DISTINCT field FROM table
    # 例子  
    # 查询部门信息表中姓名唯一的记录
    SELECT DISTINCT name FROM deptname;
    # 查询部门信息中有多少部门名称
    SELECT DISTINCT dept FROM tablename

GROUP BY 排序条件默认升序

    SELECT filelist FROM tablename
    WHERE selectcriteria 
    ORDER BY field ASC;

    # 例子 查找学生成绩升序和降序
    SELECT * FROM tablename WHERE grade=88 ORDER BY  ASC/DESC;

GROUP BY 分组显示

    SELECT fieldlist 
    FROM tablename
    WHERE critieria
    GROUP BY groupfieldlist
    # 例子
    # 查询参加考试的班级
    SELECT classname from classtables GROUP BY classname;

HAVING (分组过滤条件，必须与 GROUP BY)

    SELECT field1 
    FROM tablename
    WHERE   selectcriteria
    GROUP BY  groupfieldlist 
    HAVING groupcriteria

BETWEEN … AND … 之间上下包含