0825MySQL(1)_VIDEO(4)

DML:

INSERT/REPLACE DELETE UPADTE SELECT

 

INSERT:

单行插入   每次插入索引便更新一次

批量插入   索引更新一次

MySQL二次整理(3)v1.0

1.直接插入数据

                     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 INTO students (Name,Age,Gender) VALUES (Zhu bajie,100,M),(Pipa jing,100,F);

                     Or:

2.在其他表中查询数据并插入

                     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] ... ]

 

                     Or:

例:INSERT  INTO  test  SET  Name=Zhu Bajie;

                     INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

                     [INTO] tbl_name [(col_name,...)]

                     SELECT ...

                     [ ON DUPLICATE KEY UPDATE

                     col_name=expr

                            [, col_name=expr] ... ]

例:INSERT INTO test  SELECT * FROM students WHERE StuID <= 10;

DELETE:

DELETE  FROM tbl_name [WHERE where_condition]  [ORDER BY ...]  [LIMIT row_count]

注意:一定要有限制条件,否则将清空整个表

限制条件:

[WHERE where_condition]

    [ORDER BY ...]  [LIMIT row_count]

例:DELETE FROM students ORDER BY Name LIMIT 3;

从students表中,按照Name列排序 并删除前3个

UPDATE:修改数据

UPDATE       table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

    [WHERE where_condition]

    [ORDER BY ...]

    [LIMIT row_count]

例: UPDATE students SET Age=91

注意:一定要有限制条件,否则将修改整个表中的数据

(SET @@session.sql_safe_updates=ON 怎不指定条件不能修改)

限制条件:

[WHERE where_condition]

    [ORDER BY ...]  [LIMIT row_count]

SELECT:

                    

                     Query Cache:缓存查询的执行结果;

                            key:查询语句的hash值;

                            value:查询语句的执行结果;

                    

                     查询执行路径:

                            请求-->查询缓存

                            请求-->查询缓存-->解析器-->预处理器-->优化器-->查询执行引擎-->存储引擎-->缓存-->响应

                           

                     SELECT语句的执行流程:

                            FROM  --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit

单表查询:

                           

       SELECT

              [ALL | DISTINCT | DISTINCTROW ]      

              [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 col1, col2, ... FROM tble_name;

              SELECT col1, col2, ... FROM tble_name WHERE clause;

              SELECT col1, col2, ... FROM tble_name GROUP BY col_name HAVING clause;

                                  

       DISTINCT:数据去重;

       SQL_CACHE:显式指定缓存查询语句的结果;

       SQL_NO_CACHE:显式指定不缓存查询语句的结果;

                           

       query_cache_type服务器变量有三个值:

              ON:启用; 不包含可变化值 查询结果单挑不超过1MB(query_cache_limit)

                     SQL_NO_CACHE:不缓存;默认符合缓存条件都缓存;

              OFF:关闭;

              DEMAND:按需缓存;语句有 SQL_CACHE才被缓存

                     SQL_CACHE:缓存;默认不缓存;

                                         

       字段可以使用别名 :

              col1 AS alias1, col2 AS alias2, ...

                                  

       WHERE子句:指明过滤条件以实现“选择”功能;

              过滤条件:布尔型表达式;

                                  

              [WHERE where_condition]

                     算术操作符:+, -, *, /, %

                     比较操作符:=, <>, !=, <=>, >, >=, <, <=

                                         

                            IS NULL, IS NOT NULL 查看空|非空

                            区间:BETWEEN min AND max 区间值

                            IN:列表;

                            LIKE:模糊比较,%和_;

                            RLIKE或REGEXP 正则表达式

SELECT * FROM students WHERE age IN (25,35)

SELECT * FROM students WHERE Name RLINK ^(s|s).*$’’

                                                

                     逻辑操作符:

                            AND, OR, NOT, XOR

                                                

       GROUP BY:根据指定的字段把查询的结果进行“分组”以用于“聚合”运算;

              avg(), max(), min(), sum(), count()

              SELECT sum(age),Gender  FROM  students  GROUP  BY  Gender;

              HAVING:对分组聚合后的结果进行条件过滤;

              SELECT age(age) AS avg_age  FROM  students  GROUP  BY  Gender HAVING age_age>30;                            SELECT count(StuID) AS nos,ClassID FROM students WHERE ClassID ID NOT NULL GROUP BY ClassID HAVING nos>=2; 查询 统计班级(classid)不为空的学生编号(stuid) 并根据班级进行count统计 过滤掉班级人数小于2的班级 并显示

       ORDER BY:根据指定的字段把查询的结果进行排序;

              升序:ASC 默认

              降序:DESC

      

       LIMIT:对输出结果进行数量限制

              [LIMIT {[offset,] row_count | row_count OFFSET offset}]

多表查询:

                            连接操作:

                                   交叉连接:笛卡尔乘积;

SELECT * FROM row1,row2;

                                   内连接:

                                          等值连接:让表之间的字段以等值的方式建立连接;

SELECT tab1.row1,tab2.row2 FROM tab1,tab2 WHERE row1=row2;

SELECT students.Name,terchers.Name FROM students,teracheers WHERE students.TeracherID = terachers.TID;

有字段同名则设置别名 tab1.row1 AS XXX

                                          不等值连接:

                                          自然连接:

SELECT students.Name,terchers.Name FROM students,teracheers WHERE students.TeracherID = terachers.TID;

                                          自连接:自己链接自己(表内的某个列链接此表内的另外一个列):

SELECT * FROM students AS XXX,students AS YYY WHERE XXX.TercherID = YYY.studentTID;

                                   外连接:外连接默认只有等值数据才可建立 而字段中有(NULL)则无法链接

                                          左外连接:左侧为标准 右侧可为(NULL)

                                                 FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col

                                          右外连接:右侧为标准 左侧可为空

                                                 FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col

子查询:在查询的结果中潜逃查询:

用于WHERE子句中的子查询:

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

       (2) 用于IN中的子查询:子查询可以返回一个列表值;

       (3) 用于EXISTS中的子查询:

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

用于FROM子句中的子查询:

SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause;

SELECT [Name,Age|*] FROM (SELECT Name,Age FROM students ) AS s WHERE Age > 30;