Question

[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

  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}]
  [PROCEDURE procedure_name(argument_list)]
  [INTO OUTFILE 'file_name'
    [CHARACTER SET charset_name]
    export_options
   | INTO DUMPFILE 'file_name'
   | INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

• SELECT 语句的 JOIN 语法：这些语法用于 SELECT 语句的 table_references 部分和多表 DELETE 和 UPDATE 语句。
• SELECT 语句的 UNION 语法

INSERT ：

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

或：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name
  SET col_name={expr | DEFAULT}, ...
  [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [(col_name,...)]
  SELECT ...
  [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

UPDATE:

Single-table 语法：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
  SET col_name1=expr1 [, col_name2=expr2 ...]
   [WHERE where_definition]
  [ORDER BY ...]
  [LIMIT row_count]

Multiple-table 语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_references
  SET col_name1=expr1 [, col_name2=expr2 ...]
  [WHERE where_definition]

UPDATE 语法可以用新值更新原有表行中的各列。

DELETE:

单表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
  [WHERE where_definition]
  [ORDER BY ...]
  [LIMIT row_count]

多表语法：

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

INSERT ... SELECT 语法

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [(col_name,...)]
  SELECT ...
  [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

使用 INSERT...SELECT，您可以快速地从一个或多个表中向一个表中插入多个行。

示例：

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

DO 语法

DO expr [, expr] ...

DO 用于执行表达式，但是不返回任何结果。DO 是 SELECT expr 的简化表达方式。DO 有一个优势，就是如果您不太关心结果的话，DO 的速度稍快。

DO 主要用于执行有副作用的函数，比如 RELEASE_LOCK() 。

2.1.1 常用 SQL 语句

2.1.1.1 SELECT

1、查询结果添加序列号

法 1：

SELECT (@rowno:=@rowno +1) AS row, anchor.name from anchor, (SELECT @rowno:=0) AS t

法 2：

SET @rowno=0;
SELECT @rowno:=@rowno+1 AS rowno, name, room_id FROM anchor LIMIT 1,5;

2、查询排名或者获取某子段的排名值

结果集序列号

SELECT name,`room_fans`,
(SELECT COUNT(*)+1 FROM anchor WHERE a.`room_fans`<`room_fans`) AS sort
FROM anchor as a;

列出某子段的全数据库排名

注意：NAME 使用 LIKE 等于完全未使用索引，另外 GROUP BY 也要消耗时间。数据十万以上就很慢了，需 30 秒以上。

SELECT name,room_fans,
(SELECT COUNT(*)+1 FROM anchor WHERE a.room_fans<room_fans) AS sort
FROM anchor as a WHERE name LIKE '%沈%' ORDER BY room_fans LIMIT 10

2.1.1.2 INSERT INTO

跨表插入

INSERT INTO select_product(product_id, cast_id) SELECT cast.cast_id,product.product_id from cast,product WHERE cast.name='藍色しあん' AND product.pid='IPZ-733'

两表合并 （忽略自增长主键）

INSERT ignore INTO tbl_name (字段 1,字段 2) SELECT 字段 1, 字段 2 FROM tbl2_name

方案一：使用 ignore 关键字。

  INSERT IGNORE INTO table_1 (name) SELECT name FROM table_2;

方案二：使用 replace intok，先删除再增加，若 VALUE 不全会缺省。

REPLACE INTO table_name(col_name, ...) VALUES (...);
REPLACE INTO table_name (col_name, ...) SELECT ...;
REPLACE INTO table_name SET col_name='value',

方案三：ON DUPLICATE KEY UPDATE

INSERT INTO table (a, b, c)
VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;

示例 1：

INSERT ignore INTO star
(name, name_en, realname, sex, nationality, occupation, born, height, weight, bust,
waist, hips, blood, star, hometown, hobbies, intro, source_url, head_url, profile_url, groups)
    SELECT name, name_en, realname, sex, nationality, occupation, born, height,
    weight, bust, waist, hips, blood, star,
    hometown, hobbies, intro, source_url, head_url, profile_url,
    groups from star2;

示例 1.2：

INSERT ignore INTO anchor2(name,room_id,room_ctime,room_utime,
room_title,room_classify,room_classify_b,room_fans,room_popularitys,source_url,head_url)
    SELECT name,room_id,room_ctime,room_utime,room_title,room_classify,
    room_classify_b,room_fans,room_popularitys,source_url,head_url
    FROM anchor
    WHERE source_url NOT LIKE 'http://www.panda%';

示例 2：

REPLACE INTO anchor2(name,room_id,room_ctime,room_utime,room_title,
room_classify,room_classify_b,room_fans,room_popularitys,source_url,head_url)
    SELECT name,room_id,room_ctime,room_utime,room_title,room_classify,
    room_classify_b,room_fans,room_popularitys,source_url,head_url
    FROM anchor
    WHERE source_url NOT LIKE 'http://www.panda%';

示例 3：

INSERT INTO anchor2(name,room_id,room_ctime,room_utime,room_title,
room_classify,room_classify_b,room_fans,room_popularitys,source_url,head_url)
    SELECT name,room_id,room_ctime,room_utime,room_title,
    room_classify,room_classify_b,room_fans,room_popularitys,source_url,head_url
    FROM anchor
    WHERE source_url NOT LIKE 'http://www.panda%' ON DUPLICATE KEY UPDATE anchor2.name=anchor.name;

2.1.1.3 UPDATE 修改数据

MySQL 内部函数修改：如 SUBSTRING、replace、avg、count 等等。

UPDATE cast SET bust="" WHERE bust LIKE 'カップ';
UPDATE cast SET bust=replace(bust,'カップ)','') WHERE bust LIKE 'B%'
UPDATE cast SET blood=replace(blood,'型','') WHERE blood LIKE '%型'
UPDATE cast SET blood=replace(blood,'-','') WHERE blood LIKE '----'

SELECT 语句获取值，必需是单值

UPDATE cast SET bust=(select bust from cast WHERE bust LIKE '%カップ%')

2.1.1.4 ALTER

1、增加字段

ALTER TABLE star ADD tags VARCHAR (255) DEFAULT NULL;

2.1.1.5 批量操作数据

批量插入 ：

INSERT INTO tbl_name(field1,field2…) VALUES(value1,value2…)

批量更新 ：

• 单次更新：最慢，如 update test_tbl set dr='2' where id=1;
• replace into: 最快，如 replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');
• insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);
• 创建临时表，先更新临时表，然后从临时表中 update

create temporary table tmp(id int(4) primary key,dr varchar(50));
 insert into tmp values (0,'gone'), (1,'xx'),...(m,'yy');
 update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

注意：这种方法需要用户有 temporary 表的 create 权限。

replace into 和 insert into on duplicate key update 的不同在于：

• replace into　操作本质是对重复的记录先 delete 后 insert，如果更新的字段不全会将缺失的字段置为缺省值
• insert into 则是只 update 重复记录，不会改变其它字段。