March 11 2014

mysql的插入和查询

查询:

select 属性列表
from 表名和视图列表
[where 条件表达式]
[group by 属性名[having 条件表达式]]
[order by 属性名[asc|desc]]
[limit <offset>,row count]

where子句:按照“条件表达式”指定的条件进行查询。
group by子句:按照“属性名”指定的字段进行分组。
having子句:有group by才能having子句,只有满足“条件表达式”中指定的条件的才能够输出。
group by子句通常和count()、sum()等聚合函数一起使用。
order by子句:按照“属性名”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出,默认是按照“asc”来排序,即升序。
创建测试表
CREATE TABLE fruits
(
id CHAR(10)  PRIMARY KEY,
sid INT NOT NULL,
name  CHAR(255)  NOT NULL,
price DECIMAL(8,2) NOT NULL
)
插入数据
INSERT INTO fruits
VALUES(‘a1′,101,’apple’,5.2),
(‘b1′,102,’blackberry’,5.2),
(‘bs1′,105,’orange’,5.2),
(‘bs2′,103,’melon’,5.2),
(‘t1′,106,’banana’,5.2);

分组:
SELECT sid ,COUNT(1) AS total FROM fruits GROUP BY sid

再插入两行
INSERT INTO fruits
VALUES(‘a6′,101,’cherry’,6),
(‘a8′,102,’coconut’,7)

MYSQL中可以在GROUP BY中使用GROUP_CONCAT()函数,
将每个分组中各个字段的值显示出来
mysql> SELECT sid,GROUP_CONCAT(name) AS NAMES FROM fruits GROUP BY sid ;
+—–+——————–+
| sid | NAMES              |
+—–+——————–+
| 101 | apple,cherry       |
| 102 | coconut,blackberry |
| 103 | melon              |
| 105 | orange             |
| 106 | banana             |
+—–+——————–+
5 rows in set (0.00 sec)

在GROUP BY中使用WITH ROLLUP  –当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUP和ORDER BY是互相排斥的!  这是SQLSERVER中也有的关键字
SELECT sid ,COUNT(1) AS total FROM fruits GROUP BY sid WITH ROLLUP
在SQLSERVER中
with rollup 、with cube、grouping
CUBE 和 ROLLUP 之间的区别在于:
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
grouping:
当用 CUBE 或 ROLLUP 运算符添加行时,附加的列输出值为1,当所添加的行不是由 CUBE 或 ROLLUP 产生时,附加列值为0。

查询特定行

LIMIT[位置偏移量],行数
第一个“位置偏移量”参数指示MYSQL从哪一行开始显示,是一个可选参数,如果不指定“位置偏移量”
将会从表中第一条记录开始(第一条记录的位置偏移量是0,第二条记录的位置偏移量是1……以此类推)

第二个参数“行数”指示返回的记录条数
SELECT * FROM fruits LIMIT 4,3
在MYSQL5.6中可以使用 LIMIT 4 OFFSET 3 ,意思是获取从第5行记录开始的3条记录,和 LIMIT 4,3 返回的结果是一样的

子查询这个特性从MYSQL4.1开始引入。
使用UNION关键字,合并结果时,两个查询对应的列数和数据类型必须相同。
各个SELECT语句之间使用UNION或UNION ALL关键字分隔
UNION:执行的时候删除重复的记录,所有返回的行都是唯一的
UNION ALL:不删除重复行也不对结果进行自动排序

查询以特定字符或字符串开头的记录
字符“^”匹配以特定字符或者字符串开头的文本
SELECT * FROM fruits WHERE name REGEXP ‘^b’

查询以特定字符或字符串结尾的记录
字符“$”匹配以特定字符或者字符串结尾的文本
SELECT * FROM fruits WHERE name REGEXP ‘y$’
用符号“.”来代替字符串中的任意一个字符
字符“.”匹配任意一个字符
SELECT * FROM fruits WHERE name REGEXP ‘a.g’

使用“*”和“+”来匹配多个字符
星号“*”匹配前面的字符任意多次,包括0次。加号“+”匹配前面的字符至少一次
SELECT * FROM fruits WHERE name REGEXP ‘^ba*’

匹配指定字符串
正则表达式可以匹配指定字符串,只要这个字符串在查询文本中即可,如要匹配多个字符串,多个字符串之间使用分隔符“|”隔开
SELECT * FROM fruits WHERE name REGEXP ‘on|ap’

匹配指定字符中的任意一个
方括号“[]”指定一个字符集合,只匹配其中任何一个字符,即为所查找的文本
SELECT * FROM fruits WHERE name REGEXP ‘[ot]’   [456]也可以写成[4-6]即指定集合区间

匹配指定字符以外的字符
“[^字符集合]”匹配不在指定集合中的任何字符
SELECT * FROM fruits WHERE id REGEXP ‘[^a-e1-2]’

使用{n,} 或者{n,m}来指定字符串连续出现的次数
“字符串{n,}”,表示至少匹配n次前面的字符;“字符串{n,m}”表示匹配前面的字符串不少于n次,不多于m次
SELECT * FROM fruits WHERE name REGEXP ‘b{1,}’

插入
INSERT INTO table_name1(column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition)

当要导入的数据中有重复值的时候,MYSQL会有三种方案

方案一:使用 ignore 关键字
方案二:使用 replace into
方案三:ON DUPLICATE KEY UPDATE

replace的语法格式为:
1. replace into table_name(col_name, …) values(…)
2. replace into table_name(col_name, …) select …
3. replace into table_name set col_name=value, …

REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值,则在新记录被插入之前,旧记录被删除,即:
1.    尝试把新行插入到表中
2.    当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时:
从表中删除含有重复关键字值的冲突行再次尝试把新行插入到表中
旧记录与新记录有相同的值的判断标准就是:表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义。该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行。
返回值:
REPLACE语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和。
受影响的行数可以容易地确定是否REPLACE只添加了一行,或者是否REPLACE也替换了其它行:检查该数是否为1(添加)或更大(替换)。

ON DUPLICATE KEY UPDATE
如‍上所写,你也可以在INSERT INTO…..后面加上 ON DUPLICATE KEY UPDATE方法来实现。
如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,
则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值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;
如果列b也是唯一列,则INSERT与此UPDATE语句相当
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。



Copyright 2019. All rights reserved.

Posted 2014年3月11日 by ucjmh in category "mysql

Leave a Reply

Your email address will not be published. Required fields are marked *