mysql - sql语句/基础sql/DDL/DML/DQL

目录

一、数据操纵语句

二、控制语句

三、实用程序语句

---

一、数据操纵语句

1.SELECT语句
(1)SELECT语句语法
select select_expr [,select_expr,…] [
from tb_name
[join 表名]
[on 连接条件]
[where 条件判断]
[group by {col_name | postion} [asc | desc], …]
[having where 条件判断]
[order by {col_name|expr|postion} [asc | desc], …]
[ limit {[offset,]rowcount | row_count offset offset}]
]

(2)SELECT INTO…OUTFILE语法
SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]
 
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

FIELDS有三个子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。如果指定了FIELDS则三个必指定一个。
(1)TERMINATED BY指定字段之间分隔符号
(2)ENCLOSED BY指定包裹字段值的符号，如“ENCLOSED BY ' " '”表示值放在双引号之间，若加上OPTIONALLY表示所有的值都放在双引号之间。
(3)ESCAPED BY子句用来指定转义字符，例如“ESCAPED BY '*'”将“*”指定为转义字符，取代“\”，如空格将表示为“*N”。
(4)LINES子句：使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY '?'”表示一行以“?”作为结束标志。

2.UPDATE 语句
Solution 1:  修改1列
update student s, city c
set s.city_name = c.name
where s.city_code = c.code;

Solution 2:  修改多个列
update  a,  b 
set a.title=b.title, a.name=b.name
where a.id=b.id

Solution 3: 采用子查询
update student s set city_name = (select name from city where code = s.city_code);

3.REPLACE语句
1.replace介绍
REPLACE与此类似 INSERT，除非表中的旧行与a PRIMARY KEY或UNIQUE 索引的新行具有相同的值， 否则在插入新行之前将删除旧行
要使用REPLACE，您必须拥有表的权限INSERT和 DELETE权限。

2.MySQL replace into 有三种形式
(1)replace into tbl_name(col_name, ...) values(...)
(2)replace into tbl_name(col_name, ...) select ...
(3)replace into tbl_name set col_name=value, ...

第三种replace set用法类似于update set用法，使用一个例如“SET col_name = col_name + 1”的赋值，
则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。
前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，
MySQL 将自动为这些列赋上默认值。

select start_date,end_date,count(1) from rds.amz_all_order_3 group by start_date,end_date order by start_date,end_date;

3.table2中存在多条主键重复数据，覆盖次序如何
replace into table1 select * from table2;
当table2中有重复多条数据时，重复数据中排序后的数据覆盖排序前的数据，结果只保留排序最后的一条数据

4.INSERT语句
(1)INSERT语法
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

2.INSERT ... SELECT语法
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

--指定IGNORE忽略会导致重复键违规的行
--INSERT ... SELECT会忽略DELAYED
--INSERT语句 的目标表 可能出现在SELECT .. FROM子句中，但无法从子查询中选择目标表数据插入目标表。
--为确保二进制日志可用于重新创建原始表，MySQL不允许对INSERT ... SELECT语句进行并发插入。
insert into tbl_temp2 (fld_id) 
select tbl_temp1.fld_order_id from tbl_temp1 where tbl_temp1.fld_order_id > 100;
--
insert into book select "abc","bdd" union all select "add","dsf";

(3)INSERT ... ON DUPLICATE KEY UPDATE语法
[1]ON DUPLICATE KEY UPDATE语法说明与案例
插入数据时，目标表数据已存在(基于唯一索引或主键)，则修改将插入的数据，不存在，则新增。
如果一个表中包含了一个自增的字段，使用INSERT ... ON DUPLICATE KEY UPDATE新增或修改t数据后，可以通过last_insert_id()方法返回最后自动生成的值，如果是多条，实际测试是返回第一条数据自增的值。

例1:
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;

例2:
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
等同于:
INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;

例3:
INSERT INTO tablename(field1,field2, field3, ...) VALUES(value1, value2, value3, ...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...;
tablename是表名，field1，field2，field3等是字段名称，value1，value2，value3等是字段值。

例4:
INSERT INTO t1 (a, b)
SELECT * FROM
  (SELECT c, d FROM t2 UNION SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;

[2]使用注意点
INSERT INTO ... ON DUPLICATE KEY UPDATE b=values(b); values(b)表示为VALUES中对应b字段的值
同一pk值，当同时插入多条时取第一条或是最后一条：生效是最后一条

[3]应用情景
如合成一张多字段大表时，如juvo_agg_metadata_sku表(sku为主键)，可拆分字段分组分步骤导入。

(4)INSERT IGNORE语法
MySQL提供了Ignore 用来避免数据的重复插入.若有导致unique key 或 primary key 冲突的记录，则该条记录不会被插入到数据库中.
例:sample 
insert ignore into books (c_name) values ('mysql manual')
INSERT IGNORE INTO table_name  SELECT ... FROM table_2;

5.WHERE语句
(1)实现组合字段条件
SELECT a.* from yida_org.temp_buyerid_orderid_2B a 
where (amazon_order_id,asin) NOT in (select amazon_order_id,asin from 
yida_org.temp_buyerid_orderid_2A);

6.BETWEEN语句
(1)基本的语法格式
[NOT] BETWEEN 取值1 AND 取值2
NOT：可选。加上NOT表示不能满足指定范围的条件。
取值1：表示范围的起始值。
取值2：表示范围的终止值。

(2)例子
age between 18 and 24           查询条件是age字段的取值范围为18～24
age not between 18 and 24    查询条件是age字段的取值不在18～24之间

7.DELETE语句
(1)单表语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

-- 该DELETE语句从中删除行 tbl_name并返回已删除行的数量。要检查已删除行的数量，请调用ROW_COUNT()功能。
-- where_condition是一个表达式，对于要删除的每一行，计算结果为true。
-- 如果ORDER BY指定了该子句，则按指定的顺序删除行。该 LIMIT子句限制了可以删除的行数。这些子句适用于单表删除，但不适用于多表删除。

(2)多表语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

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

(1)性能
当需要删除全表时，TRUNCATE TABLE语句是一种更快的方式来清空表，但TRUNCATE TABLE不能在事务中使用或者如果您对表有锁定。
(2)自动增量列
如果删除包含列的最大值的行 AUTO_INCREMENT，则不会对该 表MyISAM或InnoDB表重用该值。
如果在模式下删除表中的所有行（没有 子句） ，则除了和之外，所有存储引擎的序列都将重新开始。
(3)修饰符
该DELETE语句支持以下修饰符：
如果指定LOW_PRIORITY修饰符，则服务器会延迟执行， DELETE直到没有其他客户端从表中读取。这会影响只使用表级锁只存储引擎（例如 MyISAM，MEMORY和 MERGE）。
对于MyISAM表，如果使用 QUICK修饰符，则存储引擎在删除期间不会合并索引叶，这可能会加速某些类型的删除操作。
该IGNORE修改导致MySQL删除行的过程中忽略的错误。（解析阶段遇到的错误以常规方式处理。）由于使用而IGNORE被忽略的错误 将作为警告返回。
(4)删除顺序
如果DELETE语句包含 ORDER BY子句，则按照子句指定的顺序删除行。这主要与结合使用有用LIMIT。
例如，以下语句查找与WHERE子句匹配的行，对其进行排序timestamp_column，并删除第一个（最旧的）：
DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;

3.多表删除
可以在DELETE语句中指定多个表，根据WHERE子句中的条件从一个或多个表中删除行,但不能在多表 DELETE中使用ORDER BY或LIMIT。
对于第一个多表语法，仅FROM删除子句之前列出的表中的匹配行。对于第二个多表语法，仅删除FROM子句（在USING子句之前）中列出的表中的匹配行。
结果是您可以同时从多个表中删除行，并具有仅用于搜索的其他表：
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
或:
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;

多表DELETE语句可以使用语句中允许的其他类型的连接 SELECT，例如 LEFT JOIN。例如，要删除不存在t1匹配项的行 t2，请使用LEFT JOIN：
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

4.其他跨表删除案例
(1)从数据表t1中把那些id值在数据表t2里有匹配的记录全删除掉
DELETE t1 FROM t1,t2 WHERE t1.id=t2.id    
或DELETE FROM t1 USING t1,t2 WHERE t1.id=t2.id
实例:
delete a from  yida_org.ama_businessreport_info a ,yida_org.temp_businessreport_17_01 b
where a.store_nam=b.store_nam and a.area_nam=b.area_nam and a.data_date=b.data_date;

(2)从数据表t1里在数据表t2里没有匹配的记录查找出来并删除掉
DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL 或
DELETE FROM t1,USING t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL

(3)从两个表中找出相同记录的数据并把两个表中的数据都删除掉
DELETE t1,t2 from t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25
注意此处的delete t1,t2 from 中的t1,t2不能是别名;

8.EXISTS语句
这条语句适用于a表比b表大的情况
select * from ecs_goods a where cat_id in(select cat_id from ecs_category);

这条语句适用于b表比a表大的情况
select * from ecs_goods a where EXISTS (select cat_id from ecs_category b where a.cat_id = b.cat_id);

9.JOIN语句
(1)实现full join
mysql不支持full join，可以同过 左外连接+ union+右外连接来实现
SELECT * FROM Persons p LEFT OUTER JOIN Orders o ON p.pid=o.pid  
UNION   
SELECT * FROM Persons p RIGHT OUTER JOIN Orders o ON p.pid=o.pid;

10.MOIFY语句
(1)修改字段类型或排列位置
ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2 
参数说明 
FIRST，将字段1修改为表的第一个字段。 
AFTER 字段名2 
将字段1，插入到字段2的后面。 
例子:
alter table deptment modify name VARCHAR(30); 
ALTER TABLE tb001 MODIFY COLUMN C1 VARCHAR(20) NOT NULL DEFAULT 'A1';

11.MySQL注释
MySQL服务器支持3种注释风格：
(1)从‘#’字符从行尾。
(2)从‘-- ’序列到行尾。‘-- ’注释要求第2个破折号后面至少跟一个空格符(tab、换行符)。
(3)从/*序列到后面的*/序列。结束序列不一定在同一行中，允许注释跨越多行。

例子:下面的例子显示了3种风格的注释：
mysql> SELECT 1+1;     # This comment continues to the end of line
mysql> SELECT 1+1;     -- This comment continues to the end of line
mysql> SELECT 1;         /* this is an in-line comment */ + 1;
mysql> SELECT 1+;

 

二、控制语句

1.IF条件语句( IF..ELSE.. / IF(expr1,expr2,expr3) )
(1)if else条件语句
if stu_grade>=90 then 
    select stu_grade,'A';  
elseif stu_grade<90 and stu_grade>=80 then 
    select stu_grade,'B';  
elseif stu_grade<80 and stu_grade>=70 then 
    select stu_grade,'C';  
elseif stu_grade<70 and stu_grade>=60 then 
    select stu_grade,'D';  
else 
    select stu_grade,'E';  
end if;

(2)if判断语句
IF(expr1,expr2,expr3) 若expr1为true则返回expr2,否则返加expr3
if(stu_grade>=60,'合格','不合格')

2.CASE分支语句
(1)案例1
SELECT 
        CASE WHEN salary <= 500 THEN '1' 
             WHEN salary > 500 AND salary <= 600  THEN '2' 
             WHEN salary > 600 AND salary <= 800  THEN '3' 
             WHEN salary > 800 AND salary <= 1000 THEN '4' 
        ELSE NULL END salary_class, 
        COUNT(*) 
FROM    Table_A 
GROUP BY 
        CASE WHEN salary <= 500 THEN '1' 
             WHEN salary > 500 AND salary <= 600  THEN '2' 
             WHEN salary > 600 AND salary <= 800  THEN '3' 
             WHEN salary > 800 AND salary <= 1000 THEN '4' 
        ELSE NULL END; 

（2)案例2
SELECT  SUM(population), 
        CASE country 
                WHEN '中国'     THEN '亚洲' 
                WHEN '印度'     THEN '亚洲' 
                WHEN '日本'     THEN '亚洲' 
                WHEN '美国'     THEN '北美洲' 
                WHEN '加拿大'  THEN '北美洲' 
                WHEN '墨西哥'  THEN '北美洲' 
        ELSE '其他' END 
FROM    Table_A 
GROUP BY CASE country 
                WHEN '中国'     THEN '亚洲' 
                WHEN '印度'     THEN '亚洲' 
                WHEN '日本'     THEN '亚洲' 
                WHEN '美国'     THEN '北美洲' 
                WHEN '加拿大'  THEN '北美洲' 
                WHEN '墨西哥'  THEN '北美洲' 
        ELSE '其他' END; 

(3)案例3 
SELECT country, 
       SUM( CASE WHEN sex = '1' THEN 
                      population ELSE 0 END),  --男性人口 
       SUM( CASE WHEN sex = '2' THEN 
                      population ELSE 0 END)   --女性人口 
FROM  Table_A 
GROUP BY country; 

 

三、实用程序语句

1.explain语句
(1)概要描述

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
概要描述：
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

(2)各字段说明
1) id
SELECT识别符。这是SELECT的查询序列号
我的理解是SQL执行的顺序的标识，SQL从大到小的执行
[1]id相同时，执行顺序由上至下
[2]如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
[3]id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

2)select_type
示查询中每个select子句的类型
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
 
3)table
显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称
 
4)type
对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
 
5)possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
 
6)Key
key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
 
7)key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好 
 
8)ref
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
 
9)rows
 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
 
10)Extra
该列包含MySQL解决查询的详细信息,有以下几种情况：
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为"文件排序"
-- 测试Extra的filesort
explain select * from emp order by name;
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句
-- explain select now() from dual;
 
总结：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。