sql - Serbipunk/notes GitHub Wiki

primary key

http://c.biancheng.net/sql/primary-key.html#:~:text=%E4%B8%BB%E9%94%AE%EF%BC%88Primary%20Key%EF%BC%89%E7%94%B1%E8%A1%A8,%E4%B8%8D%E8%83%BD%E5%87%BA%E7%8E%B0%E7%9B%B8%E5%90%8C%E7%9A%84%E5%80%BC%E3%80%82

ALTER TABLE website ADD PRIMARY KEY (id);

one or multiple (composite primary key) cols in table, must be unique and cannot be null

foreign key

linked to another table's primary key.

create table with foreign key:

CREATE TABLE `order`(
   oid       INT           NOT NULL  AUTO_INCREMENT,
   uid       INT  references user(id),
   PRIMARY KEY (oid)
);

delete foreign keys

ALTER TABLE `order`
DROP FOREIGN KEY;

sql select constraint

http://c.biancheng.net/sql/constraint.html

order by

SELECT column_list FROM table_name [WHERE condition] [ORDER BY col1, col2, ...] [ASC | DESC]

group by

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2

if column name with the same name of GROUP BY item, the next value will be merged into a new result.

SELECT country, TRUNCATE(SUM(uv), 2) AS total FROM website
GROUP BY country;

distinct

duplicated select condition logs will be merged into one log

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

http://c.biancheng.net/sql/distinct.html

sqlite

insert

insert into books_book  (title, subtitle, author, isbn) values ("search for morden china 2", "1500 to 1990 history", "Jonathan Spence", "1234567");