当前位置:网站首页>SQL quick query

SQL quick query

2020-11-08 16:10:18 WYH grass

  • relational database , Based on the relational model , Use relationship ( surface ) Store the data , At the same time, the integrity constraint is defined . Common relational database systems include :Oracle、MySQL/MariaDB、SQL Server、PostgreSQL wait .
  • SQL, Structured query language , Standard language for accessing and manipulating relational databases .SQL It's declarative , Is a set oriented programming language .

1、 Single table query

SELECT col1, col2 AS c2 --  Column alias  FROM t; --  The basic query 

SELECT * FROM t; --  Query all fields 

SELECT col1, col2, … FROM t WHERE conditions; --  Filter conditions 

SELECT col1, col2, … FROM t ORDER BY col1 ASC, col2 DESC; --  Sort 

SELECT col1, col2, … FROM t ORDER BY col1 ASC, col2 DESC OFFSET m ROWS FETCH FIRST n ROWS ONLY; --  Limit the number of  
LIMIT n OFFSET m; --  Non standard implementation 

SELECT col1, col2, agg_fun() --  Aggregate functions  FROM t GROUP BY col1, col2 --  Group summary  
HAVING conditions; --  Filter after grouping 

2、 Multiple table joins

SELECT t1.col1, t2.col2, … FROM t1 INNER JOIN t2 ON conditions; --  Internal connection  SELECT t1.col1, t2.col2, … FROM t1 LEFT JOIN t2 ON conditions; --  Left connection 

SELECT t1.col1, t2.col2, … FROM t1 RIGHT JOIN t2 ON conditions; --  The right connection 

SELECT t1.col1, t2.col2, … FROM t1 FULL JOIN t2 ON conditions; --  Full connection 

SELECT t1.col1, t2.col2, … FROM t1 CROSS JOIN t2 ON conditions;--  Cross connect 

SELECT a.col1, b.col2, … FROM t1 a --  Table alias  JOIN t1 b ON conditions; --  Self join 

3、 Set operations

SELECT col1, col2, … FROM t1 UNION [ALL]

SELECT c1, c2, … FROM t2; --  Union operation 

SELECT col1, col2, … FROM t1 INTERSECT SELECT c1, c2, … FROM t2; --  Intersection operation 

SELECT col1, col2, … FROM t1 EXCEPT -- MINUS

SELECT c1, c2, … FROM t2; --  Subtraction operation 

4、 Subquery

SELECT col1, (subquery) AS c2 --  Scalar subquery  FROM t;

SELECT col1, col2, … --  Line sub query  FROM t WHERE (col1, col2) = (subquery);

SELECT t1.col1, t2.c2, … FROM t1 JOIN (subquery) t2 --  Table sub query  ON conditions;

SELECT t1.col1, t1.col2, … FROM t1 WHERE EXISTS ( --  Associated subquery  SELECT 1FROM t2 WHERE t2.c1 = t1.col1);

5、 Data manipulation

INSERT INTO t(col1, col2, …) VALUES (val1, val2, …); --  Insert a single piece of data 

INSERT INTO t(col1, col2, …) SELECT …; --  Insert query results  

UPDATE t SET col1 = val1, col2 = val2 WHERE conditions; --  Update data 

DELETE FROM t WHERE conditions; --  Delete data  

MEGRE INTO t1 USING t2 ON (condition) WHEN MATCHED THEN UPDATE SET col1 = val1, col2 = val2, ... WHEN NOT MATCHED THEN INSERT (col1, col2, ...) VALUES (val1, val2, ...); --  Merge data 

6、 Data definition

CREATE TABLE t ( col1 INT NOT NULL PRIMARY KEY, col2 VARCHAR(50) NOT NULL, col3 DATE ); --  Create table 

CREATE TABLE t(col1, col2, …) SELECT … ; --  Create tables based on query results 

ALTER TABLE t ADD col3 INT; --  Add fields 

ALTER TABLE t RENAME COLUMN col1 TO c1;

ALTER TABLE t DROP COLUMN col1;

DROP TABLE t; --  Delete table 

TRUNCATE TABLE t; --  Delete all data in the table 

CREATE VIEW v AS SELECT …; --  Create view 

DROP VIEW v; --  Delete view 

CREATE [UNIQUE] INDEX idx ON t(col1, col2); --  Create index 

DROP INDEX idx; --  Delete index 

Extended content
• The query criteria include :=、!=、<>、<、<=、>、>=、BETWEEN、IN、EXISTS、LIKE、AND、OR、NOT、IS [NOT] NULL、ANY、ALL
• Integrity constraints include :PRIMARY KEY、NOT NULL、FOREIGN KEY、CHECK、UNIQUE、DEFAULT.
• Common aggregate functions :AVG、COUNT、SUM、MIN、MAX.

版权声明
本文为[WYH grass]所创,转载请带上原文链接,感谢