关于数据库的一些基本操作语句
时间：2021-07-01 10:21:17
帮助过：52人阅读
创建一个表
======
CREATE TABLE `test1` (
`id` INT(
6 ) UNSIGNED
NOT NULL AUTO_INCREMENT
PRIMARY KEY ,
`name` VARCHAR(
30 )
NOT NULL ,
`age` VARCHAR(
30 )
NOT NULL ,
`timestamp`
TIMESTAMP NOT NULL
) ENGINE = MYISAM ;
====== 插入新数据
======
INSERT INTO `test1` (`id` ,`name` ,`age`)
VALUES (
NULL ,
‘aa‘,
‘11‘), (
NULL ,
‘dd‘,
‘12‘);
====== 删除数据
======
DELETE FROM `test1`
WHERE `test1`.`id`
= 12;
====== 更新数据
======
UPDATE `test1`
SET `age`
= ‘13‘ WHERE `test1`.`id`
=12
1. 查看过滤重复后的数据
SELECT id, `name` , age,
COUNT(
1 )
AS aaa
FROM test1
GROUP BY `name` , age
2. 查看重复的数据
SELECT id, `name` , age,
COUNT(
1 )
AS c
FROM test1
GROUP BY `name` , age
HAVING c
>1
3.分组数据，从每组里面取出一个最小的id
SELECT MIN( id )
FROM test1
GROUP BY name, age
4.删除重复项，只保留一项
DELETE FROM test1
WHERE id
NOT IN (
SELECT temp.min_id
FROM (
SELECT MIN( id ) min_id
FROM test1
GROUP BY name, age
) AS temp
);
SELECT * FROM test1;
关于数据库的一些基本操作语句
标签：基本 pre tab signed where values engine 语句 ===
