澳门金沙vipCUBRID学习笔记 45 REPLACE DELETE MERGE 教程

mysql关于update的一道测试题

c#,net,cubrid,教程,学习,笔记欢迎转载
,转载时请保留作者信息。本文版权归本人所有,如有任何问题,请与我联系wang2650@sohu.com

要想取利用group by 分组后第一条记录我们就需要结合order by
来操作,原是是很利用group by 把所有分组取出来,然后来利用order by
对分组里面的数据进行desc排序取第一条就KO了。

 

。 过错

官方文档是英文的,看不明白可以参看ocracle的同类函数说明.很多都是一样的.

REPLACE
和insert类似,但是他是删除已存在的记录,然后插入.
当存在重复值的时候(主键,唯一所以冲突),不会报错.
CREATE TABLE a_tbl4 LIKE a_tbl1;
INSERT INTO a_tbl4 SELECT * FROM a_tbl1 WHERE id IS NOT NULL and name
IS NOT NULL;

SELECT * FROM a_tbl4;

先看看group by 语句的用法

前段时间去面试看到了一个测试题

id name phone

1 ‘aaa’ ‘000-0000’
2 ‘bbb’ ‘000-0000’
3 ‘ccc’ ‘333-3333’
6 ‘eee’ ‘000-0000’
–insert duplicated value violating UNIQUE constraint
REPLACE INTO a_tbl4 VALUES(1, ‘aaa’, ‘111-1111’),(2, ‘bbb’,
‘222-2222’);
REPLACE INTO a_tbl4 SET id=6, name=’fff’, phone=DEFAULT;

SELECT * FROM a_tbl4;

GROUP BY 语句
GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。

 

id name phone

3 ‘ccc’ ‘333-3333’
1 ‘aaa’ ‘111-1111’
2 ‘bbb’ ‘222-2222’
6 ‘fff’ ‘000-0000’

DELETE
删除一条
DELETE FROM a_tbl WHERE phone IS NULL LIMIT 1;

DELETE FROM a_tbl;删除所有
DELETE a, b FROM a_tbl a, b_tbl b, c_tbl c
WHERE a.id=b.id AND b.id=c.id;

DELETE a, b FROM a_tbl a INNER JOIN b_tbl b ON a.id=b.id
INNER JOIN c_tbl c ON b.id=c.id;

删除多表用using

DELETE FROM a, b USING a_tbl a, b_tbl b, c_tbl c
WHERE a.id=b.id AND b.id=c.id;

DELETE FROM a, b USING a_tbl a INNER JOIN b_tbl b ON a.id=b.id
INNER JOIN c_tbl c ON b.id=c.id;

MERGE
用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表(原数据表,source
table)或子查询的连接条件对另外一张(目标表,target
table)表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE.
但是为了保证准确,你不能多次更新目标表的相同记录.
(不要在一个执行计划中,多次更新目标表的相同记录)

CREATE TABLE source_table (a INT, b INT, c INT);
INSERT INTO source_table VALUES (1, 1, 1);
INSERT INTO source_table VALUES (1, 3, 2);
INSERT INTO source_table VALUES (2, 4, 5);
INSERT INTO source_table VALUES (3, 1, 3);

— target_table
CREATE TABLE target_table (a INT, b INT, c INT);
INSERT INTO target_table VALUES (1, 1, 4);
INSERT INTO target_table VALUES (1, 2, 5);
INSERT INTO target_table VALUES (1, 3, 2);
INSERT INTO target_table VALUES (3, 1, 6);
INSERT INTO target_table VALUES (5, 5, 2);

MERGE INTO target_table tt USING source_table st
ON (st.a=tt.a AND st.b=tt.b)
WHEN MATCHED THEN UPDATE SET tt.c=st.c
DELETE WHERE tt.c = 1
WHEN NOT MATCHED THEN INSERT VALUES (st.a, st.b, st.c);

— the result of above query
SELECT * FROM target_table;

SQL GROUP BY 语法 点击可查看源文

题目为:

a b c

1 2 5
1 3 2
3 1 3
5 5 2
2 4 5

 代码如下

 

复制代码

表创建语句为:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

 

在SQL的
语句一起使用同样数目的SQL聚合函数提供分组的某些数据库教程表列(第结果数据集方法)。

CREATE TABLE ·test_table`{

实例

 

以下是 test 表,测试sql

id int(5) unsigned NOT NULL AUTO_INCREMENT,

 

 

 代码如下

a int(1) NOT NULL,

复制代码

 

CREATE TABLE IF NOT EXISTS `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`install` int(10) unsigned NOT NULL,
`day` int(10) unsigned NOT NULL,
`aid` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

b int(1) NOT NULL DEFAULT 1

INSERT INTO `test` (`id`, `install`, `day`, `aid`) VALUES
(1, ‘www.bKjia.c0m’, 20120308, 1),
(2, 2321, 20120309, 2),
(3, 1236, 20120310, 3),
(5, ‘www.hzhuti.com’, 20120309, 1),
(6, 2312, 20120310, 1),
(7, 1432, 20120311, 1),
(8, 2421, 20120308, 2),
(9, 4245, 20120311, 2),
(10, ‘www.bKjia.c0m’, 20120310, 2),
(11, 412, 20120308, 3);

 

实现sql语句

)ENGINE=INNODB;

 代码如下

 

复制代码

然后将表中增加数据

SELECT A.* FROM test A,
(SELECT aid, MAX(day) max_day FROM test GROUP BY aid) B
WHERE A.aid = B.aid AND A.day = B.max_day
ORDER BY a.install DESC

 

这样我们只要取集合的还desc排序的第一条就可以了。

INSERT INTO test_table SET a=0,b=1;

再看个mssql server实例