mysql数据库简单操作语句

建表

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;

查询语句

1. 模糊查询

SELECT id,code FROM table1 WHERE name LIKE '%%';	// 查询所有
SELECT id,code FROM table1 WHERE name LIKE '%e1%';

2. 数学计算

求表内总条数: 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;

3. 排序查询

SELECT * FROM table1 ORDER BY id DESC;
SELECT * FROM table1 ORDER BY name ASC;	//	不写默认为 asc

4. 精确条件查询

SELECT id,code FROM table1 WHERE id=1;
SELECT id,code FROM table1 WHERE id=1 AND name='name1';

5. 分页查询

SELECT id,code FROM table1 LIMIT 2;		// 只传一个参数n时默认从第一条开始n条
SELECT id,code FROM table1 LIMIT 2,3;	// a 开始条数  b 查询条数 （a< result <=b）

6. 多表联查

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

7. 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

建议
操作数据库时，如果涉及用户，订单数据的删除一般采用字段标识形式，不建议真删