MySQL二次整理(3)v1.0
0825MySQL(1)_VIDEO(4)
DML:
INSERT/REPLACE DELETE UPADTE SELECT
INSERT:
单行插入 每次插入索引便更新一次
批量插入 索引更新一次
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子句中的子查询:
(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;
转载于:https://blog.51cto.com/zx337954373/1851697