建表
CREATE TABLE table1(id INT NOT NULL PRIMARY KEY,code VARCHAR(20) NOT NULL, name VARCHAR(50) NOT NULL);
table1
id | code | name |
---|---|---|
1 | A | 123 |
2 | B | 456 |
3 | C | 789 |
table2
id | code |
---|---|
1 | A |
2 | B |
table3
id | code |
---|---|
1 | A |
2 | B |
1 | C |
插入
INSERT INTO table1(id, code, name) VALUES(1, 'code1', 'name1');
更新/修改
UPDATE table1 SET name='name3'; // 不写默认更改列中所有值
UPDATE table1 SET name='name3' WHERE id=3;
删除
DELETE FROM table1; // 不写默认删除表中所有数据
DELETE FROM table1 WHERE id=4;
查询语句
- 模糊查询
SELECT id,code FROM table1 WHERE name LIKE '%%'; // 查询所有
SELECT id,code FROM table1 WHERE name LIKE '%e1%';
- 数学计算
求表内总条数: SELECT COUNT(*) as totalcount FROM table1;
求和: SELECT SUM(id) as sumvalue FROM table1;
平均值: SELECT AVG(id) as totalcount FROM table1;
最小值: SELECT MIN(id) as totalcount FROM table1;
最大值: SELECT MAX(id) as totalcount FROM table1;
- 排序查询
SELECT * FROM table1 ORDER BY id DESC;
SELECT * FROM table1 ORDER BY name ASC; // 不写默认为 asc
- 精确条件查询
SELECT id,code FROM table1 WHERE id=1;
SELECT id,code FROM table1 WHERE id=1 AND name='name1';
- 分页查询
SELECT id,code FROM table1 LIMIT 2; // 只传一个参数n时默认从第一条开始n条
SELECT id,code FROM table1 LIMIT 2,3; // a 开始条数 b 查询条数 (a< result <=b)
- 多表联查
SELECT table1.`code`, table2.id from table1, table2 WHERE table1.id = table2.id;
// 右联查 以右表为主 1:1
SELECT table1.`code`, table2.id from table1 RIGHT JOIN table2 on table1.id = table2.id;
--------
code id
A 1
C 3
// 左联查 left join 以左表为主 1:1
SELECT table1.`code`, table2.id from table1 LEFT JOIN table2 on table1.id = table2.id;
--------
code id
A 1
B null
C 3
// 左联查 left join 以左表为主 1:n
SELECT table1.`code`, table3.id from table1 LEFT JOIN table3 on table1.id = table3.id;
--------
code id
A 1
B null
C 3
A 1
// right join n:1
SELECT table1.`code`, table3.id from table1 RIGHTJOIN table3 on table1.id = table3.id;
--------
code id
A 1
C 3
A 1
- union查询
SELECT id,code FROM table1 UNION SELECT id,code FROM table2;
--------
id code
1 A
2 B
3 B
3 C
SELECT id,code FROM table1 UNION ALL SELECT id,code FROM table2;
--------
id code
1 A
1 A
2 B
3 B
3 C
建议
操作数据库时,如果涉及用户,订单数据的删除一般采用字段标识形式,不建议真删