1 数据库的单表操作:
> 1.1 创建表
CREATE TABLE tableName(
fieldName Type constraints,
);
> 1.2 记录操作:
> > 1.2.1 增加:
INSERT INTO tableName(?,?,?,?) VALUES('1','2','3','4');
或者
INSERT INTO tableName('1','2','3','4');
> > 1.2.2 删除:
DELETE
FROM tableName
WHERE someConditions;
> > 1.2.3 修改:
UPDATE tableName
set fieldNname = newData
WHERE someConditions;
> > 1.2.4 查询:
SELECT columesName
FROM tableName
WHERE someConditions(多条件时,使用AND和OR连接);
当使用正则表达式匹配一些记录的数据时,使用LIKE(_和%)。排序时,使用ORDER BY(DESC, ASC)。
> 1.3 字段操作:
> > 1.3.1 增加:
ALTER TABLE tableName ADD columnName varchar(30);
> > 1.3.2 修改:
修改类型:
ALTER TABLE tableName ALTER COLUMN columnName varchar(4000);
修改列名:
ALTER TABLE tableName RENAME COLUMN oldFieldName TO newFieldName;
> > 1.3.2 删除:
ALTER TABLE tableName DROP COLUMN columnName;
> 1.4 删除表
DROP TABLE tableName;
或者(在表内含有约束键的时候)
ALTER TABLE tableName
DROP CONSTRAINTS constraintsKey;
2 数据库的表连接查询:
2.1 交
SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name
2.2 并
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
或者
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableA.id IS null OR TableB.id IS null
2.3 左连接(右为空的时候,null补充)
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name
或者
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null
2.4 右连接(左为空的时候,null补充)
SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.name = TableB.name
或者
SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null
2.5 UNION 与 UNION ALL
同列,同列属性,同列顺序。
SELECT name FROM TableA UNION SELECT name FROM TableB
笛卡尔积
SELECT * FROM TableA CROSS JOIN TableB