MySQL记录操作

发表于

insert 插入记录

INSERT…VALUES

比较常用，一次性插入多条记录，可输入表达式甚至是函数。

1
2
INSERT [INTO] table_name [(column_name,...)] 
{VALUES|VALUE} ({expr|DEFAULT},...),(...),...;

expr 表示表达式
对于自动编号的字段，插入NULL或DEFAULT系统将自动依次递增编号；
对于有默认约束的字段，可以插入DEFAULT表示使用默认值；
列值可传入数值、表达式或函数，如密码可以用md5()函数进行加密（如md5(‘123’)）；
可同时插入多条记录，多条记录括号间用逗号,隔开

1
2
3
4
5
6
7
INSERT test VALUES(NULL,'Tom');
//插入单条记录
INSERT users 
VALUES(DEFAULT,'Tom','123',1+9*2,1),
(NULL,'John',md5('123'),DEFAULT,0); 
//插入两条记录

INSERT…SET

这种方法一次只能插入一条记录
列值中有自动编号、默认值或Boolean类型均可以不赋值(使用子查询)

1
INSERT [INTO] table_name SET col_name={ expr | DEFAULT },...;

1
INSERT users SET username='Ben',password='456';

INSERT…SELECT

一般用于将其他表的数据插入到指定表，注意对应

1
2
INSERT [INTO] table_name(col_name,...) 
SELECT col_name,... FROM table_name [GROUP BY/HAVING/ORDER BY/LIMIT...];

1
2
3
4
5
6
7
8
INSERT test SELECT username FROM users WHERE age >=30; 
// 字段不匹配提示
INSERT test (username) SELECT username FROM users WHERE age >=30;
INSERT tdb_goods_cates(cate_name) 
SELECT goods_cate FROM tdb_goods 
GROUP BY goods_cate;
//在表tdb_goods_cates中插入tdb_goods中的cate


update 更新记录

1
2
3
4
UPDATE [LOW_PRIORITY] [IGNORE] table_reference 
SET col_name1={exp1|DEFAULT}
[,col_name2={exp1|DEFAULT},...]
[WHERE where_condition]

省略WHERE条件则更新所有记录该列的值

单表更新

1
2
3
4
UPDATE users SET age = age + 5,sex = 0; 
//使表里所有记录的age加5
UPDATE users SET age = age + 10 WHERE id % 2=0; 
//取id为偶数的位置

多表更新

使用连接进行多表更新，以下为连接的语法

1
2
3
4
table_reference
{[INNER | CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

1
2
3
4
UPDATE tdb_goods INNER JOIN tdb_goods_cates 
ON goods_cate=cate_name 
SET goods_cate=cate_id;
//如果goods_cate等于cate_name那么就让goods_cate=cate_id

创建数据表同时将查询结果写入到数据表

1
2
3
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE tdb_goods_brands (
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) 
SELECT brand_name FROM tdb_goods 
GROUP BY brand_name;
 
UPDATE tdb_goods AS g 
INNER JOIN tdb_goods_brands AS b 
ON g.brand_name = b.brand_name 
SET g.brand_name = b.brand_id;
//多表更新
 
ALTER TABLE tdb_goods 
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
//通过ALTER TABLE语句修改数据表结构


delete 删除记录

单表删除

1
DELETE FROM table_name [WHERE where_conditon];

省略条件则默认删除该表所有记录(仅删除所有记录，不删除数据表)
删除后再插入，插入的id号从最大的往上加，而不是填补删除的。

1
DELETE FROM users WHERE id=6;

多表删除

INSERT … SELECT实现复制

1
2
3
4
INSERT tdb_goods(goods_name,cate_id,brand_id) 
SELECT goods_name,cate_id,brand_id 
FROM tdb_goods 
WHERE goods_id IN (19,20);

查找重复记录
SELECT goods_id,goods_name
FROM tdb_goods
GROUP BY goods_name HAVING count(goods_name) >= 2;

1
2
删除重复记录

DELETE t1
FROM tdb_goods AS t1
LEFT JOIN (
SELECT goods_id,goods_name
FROM tdb_goods
GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2
ON t1.goods_name = t2.goods_name
WHERE t1.goods_id > t2.goods_id;

1
2
3
4
<br>
## select 查找记录

SELECT select_expr [,select expr2…] //只查找某一个函数或表达式
[
FROM table_references //查询表名
[WHERE where_conditon] //查询条件
[GROUP BY {col_name|position} [ASC|DESC],…] //按某个字段进行分组，相同的只显示第一个
[HAVING where_conditon] //分组时，给出显示条件
[ORDER BY {col_name|expr|position} [ASC|DESC],…] //排序
[LIMIT {[offset,]row_count|row_count OFFSET offset}] //限制返回数量
}

1
2
3
4
5
6
7
8
9
### 查询表达式
* 每一个表达式表示想要的一列，必须至少有一个
* 多个列之间以英文逗号分隔
* 星号(*)表示所以列 table_name.*可以表示命名表的所有列
* 查询表达式可以使用`[As]alias_name`为其赋予别名
* 别名可用于GROUP BY，ORDRE BY或HAVING子句
SELECT 字段出现顺序影响结果集出现顺序，字段别名也影响结果集字段别名

SELECT * FROM users;
SELECT id AS userid,usrname AS uname FROM users;
SELECT users.id FROM users;

1
2
3
4
5
### WHERE 条件表达式
对记录进行过滤，如果没有指定的WHERE子句，则显示所有记录
在WHERE表达式中，可以使用MYSQL支持的函数或运算符

SELECT * FROM users WHERE id=1;

1
2
3
### GROUP BY 查询结果分组

[GROUP BY {col_name|position} [ASC|DESC],…]

1
2
3
ASC：升序，默认
DESC：降序
position：SELECT语句中列的序号

SELECT sex FROM users GROUP BY sex;
//对users中的sex按sex进行分组
SELECT * FROM users GROUP BY 1;
//1表示查询的第一个字段，这里是表中的第一个字段id
SELECT sex FROM users GROUP BY 1;
//1表示sex

1
2
3
4
5
6
如果存在相同的值（例如上面的age可能有多个相同的值），**只会保留一个**。但使用ORDER BY 就不会省略。
### HAVING 分组条件
用在GROUP BY后面追加条件，条件要么为一个聚合函数，要么出现在SELECT要查询的字段中。

[HAVING when where_condition]

SELECT sex FROM users GROUP BY 1 HAVING age > 35; //有错
SELECT sex,age FROM users GROUP BY 1 HAVING age > 35;
SELECT sex FROM users GROUP BY 1 HAVING count(id) >= 2;
//按照sex分组，条件id数大于2的分组留下显示
//工作流程就是先从表中查询字段，然后根据GROUP BY的条件对查询结果进行分组，再根据HAVING条件对分组进行筛选，最后留下满足所有条件的分组结果。

1
2
3
4
5
6
7
8
聚合函数永远只有一个返回结果
count是记录指定列的值的个数（NULL不计入）
### ORDER BY 排序
对查询结果进行排序，默认是升序，DESC是降序

[ORDER BY [col_name | expr | position } [ASC|DESC],…]

SELECT FROM users ORDER BY id DESC;
//对一个字段排序
SELECT FROM users ORDER BY age,id DESC;
//多字段排序，先按照age排序，如果age有重复的，重复的字段里按id排序

1
2
3
4
5
6
多个排序条件之间以逗号相隔，在前面的排序条件满足时，忽略后面的排序条件，否则按照后面的排序条件进行排序。
### LIMIT 限制查询结果数量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

1
2
获取头部记录LIMIT 10; //获取查询结果的前10条记录，**默认从0开始**
获取中间记录LIMIT 2,10;//获取从第3条开始的10条记录

SELECT FROM users LIMIT 2;
//从第一条开始（第一个为0），返回两条
SELECT FROM users LIMIT 2,3;
//从第三条开始（第一个为0），返回三条
```