美文网首页
14.SQL语句设计

14.SQL语句设计

作者: dptms | 来源:发表于2017-03-27 16:08 被阅读661次

    操作数据表中的数据记录(DML语句)

    使用INSERT语句向数据表中添加数据

    插入数据是向已经存在的数据表中添加一条新的记录,应该使用INSERT INTO语句。
    格式:

    INSERT INTO 表名 [(字段名1,字段名2,…字段名n)] VALUES(‘值1’,‘值2’,…,’值n’);
    

    或者

    INSERT INTO 表名 [(字段名1,字段名2,…字段名n)] VALUES(‘值1’,‘值2’,…,‘值n’),(‘值1’,‘值2’,…,’值n’);
    

    使用UPDATE语句更新数据表中已存在的数据

    SQL语句可以使用UPDATE语句对表中的一列或多列数据进行修改,必须指定需要修改的字段,并且需要赋予的新值。还要给出必要的WHERE子句指定要更新的数据行。
    格式:

    UPDATE 表名 SET 字段名=表达式 [,…][WHERE 条件]
    [ORDER BY 字段][LIMIT 行数]
    

    使用DELETE语句删除数据表中不需要的数据记录

    DELETE语句用来删除数据表中的一条或多条数据记录。
    格式:

    DELETE FROM 表名 [WHERE 条件][ORDER BY 字段][LIMIT 行数]
    

    通过DQL命令查询数据表中的数据

    查询语句可以完成简单的单表查询,也可以完成复杂的多表查询和嵌套查询。SELECT语句主要用于数据的查询检索,是SQL语言的核心,在SQL语言中SELECT语句的使用频率是最高的。
    格式:

    SELECT [ALL|DISTINCT]{*|table.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}FROM 表名
     [WHERE子句]
    [GROUP BY..][HAVING..][ORDER BY..][LIMIT count];
    

    选择特定的字段

    最简单的查询语句是使用SELECT语句检索记录的特定字段,多个字段可以用逗号分隔,例如:

    SELECT username,password FROM user;
    

    也可以使用*从表中检索出所有字段,使用SELECT *主要是针对用户的书写方便而言的。如果一张表当中的数据多大几百万,就意味着资源的浪费和漫长的查询等待,所以实际应用时要尽量避免使用它,而把查询的列名准确地列出来,也可以按自己指定的列顺序输出。

    使用AS子句为字段取别名

    如果想为返回的列取一个新的标题,以及经过对字段的计算或总结之后,产生了一个新的值,希望把它放到一个新的列里显示,则用AS保留。例如:在上例的输出结果中使用中文字段名,可以在MySQL控制台中输入的命令如下所示:

    SELECT username as '用户名' FROM user;
    

    定义别名时一定要使用单引号引起来,其中AS关键字是可选的,在原字段名和别名之间使用一个空格即可。

    SELECT username ‘用户名’ FROM user;
    

    DISTINCT关键字的使用

    如果在使用SELECT语句返回的记录结果中包含重复的记录,可以使用DISTINCT关键字取消重复的数据,只返回一个。另外,要注意DISTINCT关键字的作用是整个查询的列表,而不是单独的一列。DISTINCT会消耗一定的服务器资源,如果不指定,会默认使用ALL关键字作为检索模式

    SELECT DISTINCT gid FROM user_group;
    

    在SELECT语句中使用表达式的列

    在SQL中的表达式用法和PHP程序相似,主要包括算术表达式、逻辑表达式,以及使用SQL函数表达式等。
    例如:

    SELECT version(),1.23*10;
    

    使用WHERE子句按条件检索

    在SELECT语句中,可以使用WHERE子句指定搜索条件,实现从数据表中检索出符合条件的记录。其中,搜索条件可以由一个或多个逻辑表达式组成,这些表达式指定关于某一记录是真或假的条件。在WHERE子句中,可以通过逻辑操作符和比较操作符指定基本的表达式条件。

    • 逻辑操作符
    操作符 语法 描述
    AND 或 && a AND b 或 a && b 逻辑与,若两个操作数同时为真,则为真
    OR 或 丨丨 a OR b 或 a丨丨b 逻辑或,只要有一个操作数为真,则为真
    XOR a XOR b 逻辑异或,若有且仅有一个操作数为真,则为真
    NOT 或 ! NOT a 或 !a 逻辑非,若操作数为假,则为真
    • 比较操作符
    数据列类型 存储空间 说明 取值范围
    = a=b 若操作数a与操作数b相等,则为真
    <=> a<=>b 若a与b相等,则为真,可以用于NULL值比较
    !=或<> a!=b或a<>b 若操作数a与b不相等,为真
    < a<b 若操作数a小于b,为真
    <= a<=b 若操作数a大于b,为真
    > a>b 若操作数a大于b,为真
    >= a>=b 若操作数a大于等于b,为真
    IS NULL a IS NULL 若操作数a为NULL,为真
    IS NOT NULL a IS NOT NULL 若操作数a不为NULL,为真
    BETWEEN a BETWEEN b AND c 若a在b和c之间,为真
    NOT BETWEEN a NOT BETWEEN b AND c 若操作数a不在b和c之间,为真
    LIKE a LIKE b SQL模式匹配,若a匹配b,为真
    NOT LIKE a NOT LIKE b SQL模式匹配,若a不匹配b,则为真
    IN a IN (b1,b2….) 若a等于b1,b2,b3,…中的某一个,则为真

    在构造搜索条件时,要注意只能对数值数据类型的记录进行算术运算,并且只能在相同的数据类型之间进行记录的比较。

    根据空值(NULL)确定检索条件

    空值只能定义在允许NULL字段中出现,NULL值是特殊的值,代表“无值”,与零值(0)和空字符串('')都不相同。当在不支持默认值的字段中未输入值,或在字段中显式的设置为空,就会出现空值,但不能用处理已知值的方式来处理NULL。为了进行NULL值的搜索,必须采用特殊的语法。如果要检索NULL值,必须使用IS NULL和IS NOT NULL关键字。

    使用BEETWEEN AND进行范围比较查询

    如果需要对某个字段通过范围的值进行比较查询,可以使用BETWEEN AND关键字实现,其中AND是多重条件符号,比较时也包括边界条件。也可以使用>=<=完成同样的功能。

    使用IN进行范围比对查询

    在WHERE子句中,使用IN关键字并在后面的括号()中提供一个值的列表,以供与相应的字段进行比较。该列表中至少应该存在一个值,如果有多个值可以使用逗号,分隔。

    使用LIKE进行模糊查询

    在SELECT语句的WHERE子句中,可以使用LIKE关键字对数据表中的记录进行模糊查询,将查询结果锁定在一个范围内。在查询条件中通常会与_%两个通配符一起使用,可以实现复杂的检索查询。这两个通配符的含义分别如下:

    • 百分号“%”:表示0个或任意多个字符。
    • 下画线“_”:表示单个的任意一个字符。

    相反的,不匹配我们使用NOT LIKE

    多表查询(连接查询)

    多表查询给用户带来很大的灵活性,可以在任何时候增加新的数据类型,为不同实体创建新的表,然后通过连接进行查询。包括两种形式:

    • 非等值和等值的多表查询

    多表查询和普通的单表查询相似,都是使用SELECT语句。只不过在多表查询时需要把多张表的名字,全部填写在FROM子句中,并用逗号,将表名分开。同时,也可以对数据表使用别名进行引用。另外,为了在查询时区分多个表中出现的重复字段名,可以在字段列表中使用表名.列名的形式,如果不存在重名的列,可以省略表名。

    • 自身连接查询

    连接查询操作不仅可以用于多个表之间,也可以是一个表与其自己进行连接,称为自身连接查询。当一个表所代表的实体之间有关系时,就可以使用自身连接查询。

    • 复合连接查询

    前面介绍的多表查询是两个表之间,只有一个WHERE子句查询条件。如果在FROM子句后面有n个表需要查询,则在WHERE子句中就需要有多个连接条件。至少要比出现的表格数量少1个,也就是不能少于n−1个查询条件,多个条件使用AND关键词连接即可。

    嵌套查询(子查询)

    这种查询是在一个SELECT语句的WHERE子句中,包含另一个SELECT语句,也可以称为子查询。在子查询中只能返回一列,并将形成的结果又作为父查询的条件,在主句中进行进一步查询。 SQL语言允许多层嵌套查询,即一个子查中还可以有其他子查询。嵌套查询的求解方法是由里向外处理,即每个子查询都是在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。

    使用ORDER BY对查询结果排序

    使用SELECT语句获取数据表中的数据时,返回的记录一般是无规则排列的,有可能每次获取的查询记录截然不同。为了使用检索的结果方便阅读,可以在SELECT语句中使用ORDER BY子句,对检索的结果进行排序。

    ORDER BY后面可以接一列或多列用于排序的字段,并且使用DESCASC关键字设计字段排序的方式。默认情况下按照升序排列,即使用ASC关键字。否则要按照降序排列,必须使用DESC关键字。ORDER BY子句可以和SELECT语句中的其他子句一起使用,但在子查询中不能有ORDER BY子句,因为ORDER BY子句只能对最终查询结果排序。

    使用LIMIT

    如果在数据表中的记录数非常多,一次从表中返回大量的记录不仅检索的速度慢,用户阅读也很不方便。所以在通过SELECT语句检索时,使用LIMIT子句一次取少量的记录,而用分页的方式继续阅读后面的数据。

    LIMIT子句也可以和其他的SELECT子句一起使用,它可以指定两个参数,分别用以设置返回记录的起始位置,和返回记录的数量。

    LIMIT子句也可以只使用一个参数,表示从开头位置,即偏移量为0的位置返回指定数量的记录,在上例中使用的“LIMIT 0, 5”等价于“LIMIT 5”。

    使用统计函数

    在数据库系统中提供了一系列的内置统计函数,在SQL查询中使用这些统计函数可以更有效地处理数据。这些统计函数把存储在数据库中的数据,描述为一个整体而不是一行行孤立的记录。

    统计函数 描述
    COUNT() 返回满足SELECT语句中指定条件的记录数,例如,COUNT(*)返回找到的记录行数
    SUM() 通常为数值字段或表达列作统计,返回一列的总和
    AVG() 通常为数值字段或表达列作统计,返回一列的平均值
    MAX() 可以为数值字段、字符字段或表达列作统计,返回一列中最大的值
    MIN() 可以为数值字段、字符字段或表达列统计,返回一列中最小的值

    这些函数通常用在SELECT子句中,作为结果数据集的字段返回的结果。在SELECT语句的SELECT子句中使用函数的语法如下:

    SELECT 函数名(列名1 或*),...函数名(列名n) FROM 表名; 
    

    使用GROUP BY对查询结果分组

    前面使用统计函数返回的是所有记录的统计结果,如果要对数据进行分组统计,就需要使用GROUP BY子句。这将可以允许用户在对数据进行分类的基础上,进行再查询。GROUP BY子句将表按列值分组,列的值相同的分为一组。如果GROUP BY后面有多个列名,则先按第一个列名分组,再在每组中按第二个列名分组。

    需要注意的是,在GROUP BY子句中不支持对字段分配别名,也不支持任何使用了统计函数的集合列。

    在完成数据结果的分组查询和统计后,还可以使用HAVING子句来对查询的结果,进行进一步的筛选。

    在SELECT语句的子句中:WHERE子句选择所需要的行;GROUP BY子句进行了必要的分组整理;而HAVING子句对最后的分组结果进行了重新筛选。

    查询优化

    EXPLAIN语句是检测索引和查询能否良好匹配的简便方法。

    EXPLAIN SELECT * FROM table WHERE a>’0’ AND b<’1’ ORDER BY c; 
    

    各个属性的含义

    • id

    select查询的序列号

    • select_type

    select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。

    • table

    输出的行所引用的表。

    • type

    联合查询所使用的类型。

    type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    一般来说,得保证查询至少达到range级别,最好能达到ref。

    • possible_keys

    指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。

    • key

    显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。

    • key_len

    显示MySQL决定使用的键长度。如果键是NULL,长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

    • ref

    显示哪个字段或常数与key一起被使用。

    • rows

    这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。

    • Extra

    如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。

    如果是where used,就是使用上了where限制。

    如果是impossible where 表示用不着where,一般就是没查出来啥。

    如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

    在命令行模式下登录MySQL数据库,使用SQL实现下面要求(写出对应sql语句):

    • 创建留言数据库: liuyandb;
    • 在liuyandb数据库中创建留言表liuyan,结构如下
      表名 liuyan 留言信息表
      序号 字段名称 字段说明 类型 属性 备注
      1 id 编号 int(4) 非空 主键,自增1
      2 title 标题 varchar(32) 非空
      3 author 作者 varchar(16) 可以空
      4 addtime 留言时间 datatime 非空
      5 content 留言内容 text 非空
    • 为留言表添加>15条测试数据,要求分三个用户添加.
    • 查询所有留言信息。
    • 查询某一用户的留言信息。
    • 查询所有数据,按时间降序排序。
    • 获取id在2到6之间的留言信息,并按时间降序排序。
    • 统计每个用户留了多少条留言,并对数量按从小到大排序。
    • 将id为8、9的两条数据的作者改为’doudou’.
    • 取出最新的三条留言。(使用limit)。
    • 查询留言者中包含”d”字母的留言信息,并按留言时间从小到大排序。
    • 退出sql,将数据库导出,并以liuyanban.sql保存上交。
    • 扩展(自己发挥):设计一个博客的都需要哪些功能。

    相关文章

      网友评论

          本文标题:14.SQL语句设计

          本文链接:https://www.haomeiwen.com/subject/icjrottx.html