表操作

作者: Bruce_King | 来源:发表于2018-07-14 16:24 被阅读2次

    1、创建表(DDL)

    • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      (create_definition,...)
      [table_options]
      [partition_options]

        CREATE TABLE mytest(id INT UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR(30) NOT NULL,age TINYINT UNSIGNED NOT NULL,gender ENUM('F','M'));
      
    • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      [(create_definition,...)]
      [table_options]
      [partition_options]
      select_statement

      CREATE TABLE mytest1 SELECT * FROM mytest;
      
    • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      { LIKE old_tbl_name | (LIKE old_tbl_name) }

       CREATE TABLE mytest2 LIKE mytest;
      

    2、插入数据(DML)

    • INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_name [(col_name,...)]
      {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
      [ ON DUPLICATE KEY UPDATE
      col_name=expr
      [, col_name=expr] ... ]

       INSERT students(Name,Age,Gender,ClassID,TeacherID)  VALUE("Jrc",22,'M',NULL,NULL);
      
    • INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_name
      SET col_name={expr | DEFAULT}, ...
      [ ON DUPLICATE KEY UPDATE
      col_name=expr
      [, col_name=expr] ... ]

       INSERT students SET Name="Zl",Age=23,Gender="F",ClassID=NULL,TeacherID=NULL;
      
    • INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_name [(col_name,...)]
      SELECT ...
      [ ON DUPLICATE KEY UPDATE
      col_name=expr
      [, col_name=expr] ... ]

    3、删除数据(DML)

    • Single-table syntax:
      DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
      [WHERE where_condition]
      [ORDER BY ...]
      [LIMIT row_count]

      DELETE FROM students WHERE Name='Jrc';
      
    • Multiple-table syntax:
      DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
      tbl_name[.] [, tbl_name[.]] ...
      FROM table_references
      [WHERE where_condition]

      DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
      tbl_name[.] [, tbl_name[.]] ...
      FROM table_references
      [WHERE where_condition]

    4、更新数据(DML)

    • Single-table syntax:
      UPDATE [LOW_PRIORITY] [IGNORE] table_reference
      SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
      [WHERE where_condition]
      [ORDER BY ...]
      [LIMIT row_count]

      UPDATE students set Name='Jrc',Age=22,Gender='M' WHERE Name='Zl';
      
    • Multiple-table syntax:
      UPDATE [LOW_PRIORITY] [IGNORE] table_references
      SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
      [WHERE where_condition]

    5、查询数据(DML)

    • 简单查询
      SELECT
      [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
      select_expr [, select_expr ...]
      [FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    SELECT语句的执行流程:

        FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> SELECT --LIMIT
    

    DISTINCT:数据去重;
    SQL_CACHE:指定存储查询结果与缓存中;
    SQL_NO_CACHE:查询结果不予缓存;
     query_cache_type值为ON时,查询缓存功能打开,SELECT的结果符合缓存即会缓存;
     query_cache_tyoe值为DEMAND时,指定SQL_CACHE的SELECT语句才会缓存;

    字段显示可以显示别名:
      col1 AS alias1,col2 AS alias2,......

    • WHERE子句:指明过滤条件以实现”选择“的功能;
        过滤条件:布尔型表达式;
        算术操作符:+,-,*,、,%
        比较操作符:=,!=,< , >
          BETWEEN min_num AND max_num
          IN (element1,element2,...)
          IS NULL
          IS NOT NULL
          LIKE:
            %:任意长度的任意字符;
            _:任意单个字符;
          RLIKE
          REGEXP
        逻辑操作符:
          NOT
          AND
          OR
          XOR

    • GROUP BY:根据指定的条件把查询结果进行“分组”以用于做“聚合”运算;
        avg(),max(),min(),count(),sum()
        HAVING:对分组聚合运算后的结果做指定条件过滤;

       SELECT ClassID,count(StuID) AS NOS FROM students GROUP BY ClassID HAVING NOS>=3;
      
    • ORDER BY:根据指定的字段对查询结果进行排序;
        升序:ASC
        降序:DESC

    • LIMIT [[offset,]row_count]]:对查询的结果进行输出行数限制;

        SELECT * FROM students LIMIT 10,3;
      

    对查询结果中的数据请求施加锁:
      FOR UPDATE:写锁,排他锁;
      LOCK IN SHARE MODE:读锁,共享锁;

    • 多表查询
        交叉连接:笛卡尔乘积
        内连接:
           等值连接:让表之间的字段以“等值”建立连接关系;
          不等值连接
          自然连接
          自连接
        外连接:
          左外连接:
            FROM tb1 LEFT JOIN tb2 ON tb1.col1=tb2.col
            FROM tb1 RIGHT JOIN tb2 ON tb1.col1=tb2.col

      SELECT students.Name,classes.Class FROM classes,students WHERE classes.ClassID=students.ClassID;
      
    • 子查询:在查询语句嵌套着查询语句
        基于某语句的查询结果再次进行查询

      用在WHERE子句中的子查询:
        (1)用于比较表达式中的子查询,子查询仅能返回单个值

        SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
    

        (2)用于IN中的子查询,子查询应该单键查询并返回一个或多个值从而构成列表;

        SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
    

        (3)用于EXISTS

      用于FROM子句中的子查询

    • 联合查询:UNION

       SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
      

    相关文章

      网友评论

          本文标题:表操作

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