数据库操作
create DATABASE MYDATABASE;
drop database MYDATABASE;
创建表
CREATE TABLE tab_name (
col1 col1_type PRIMARY KEY,
col2 INTEGER AUTOINCREMENT,,
col3 col3_type NOT NULL,
.....
colN colN_type,
);
PRIMARY KEY 标记主键,NOT NULL标记非空。AUTOINCREMENT 自增,只能用于整型。
删除/更新表
DROP TABLE tab_name;
ALTER TABLE ADD COLUMNS col_name col_type;
ALTER TABLE old_tab RENAME TO new_tab
ALTER TABLE tab_name RENAME COLUMN old_col TO new_col
新增记录
INSERT INTO tab_name VALUES (xx, xx)
INSERT INTO tab_name (col1, col3) VALUES (xx, xx)
INSERT INTO tab_name (col1, col2, col3) VALUES
(xx, xx, xx),
...
(xx, xx, xx);
查询记录
SELECT * FROM tab_name;
SELECT DISTINCT col1 FROM tab_name;
SELECT COUNT(*) FROM tab_name
SELECT col1, col2 FROM table_name;
SELECT * FROM table_name WHERE col2 >= 18;
SELECT * FROM table_name
WHERE col2 >= 18 AND col1 LIKE %stu%;
SELECT * FROM table_name LIMIT 1;
SELECT col1, count(*) FROM tab_name
WHERE [ conditions ]
GROUP BY col1
SELECT col1, count(*) FROM tab_name
WHERE [ conditions ]
GROUP BY col1
HAVING [ conditions ]
SELECT * FROM table_name ORDER BY col2 DESC;
删除/更新记录
DELETE FROM tab_name WHERE condition;
UPDATE tab_name SET col1=value1, col2=value2
UPDATE tab_name
SET col1=value1, col2=value2
WHERE [ conditions ]
事务(Transaction)
BEGIN;
INSERT INTO ...
...
COMMIT;
BEGIN;
...
ROLLBACK;
事务具有原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)四个标准属性,缩写为 ACID。