基础SQ语法

基础SQ语法

1.SQL SELECT

SELECT  *  FROM table_name; SELECT column_name, column_name FROM table_name

2.SQL SELECT DISTINCT （ DISTINCT） 关键词用于返回唯一不同的值。

SELECT DISTINCT column_name,column_name FROM table_name;

3.SQL WHERE

SELECT * FROM table_name WHERE country='CN'; 

4. SQL AND & OR

SELECT * FROM table_name WHERE name="admin" AND age > 50;

SELECT * FROM table_name  WHERE name='USA' OR name='CN';

5.SQL ORDER BY

SELECT * FROM table_name ORDER BY  name DESC; // name降序

SELECT * FROM table_name ORDER BY  name ASC; // name 升序

6.SQL INSERT INTO

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

7.SQL UPDATE

UPDATE table_name SET column1=value1,column2=value2,...WHERE some_column=some_value;

8.SQL DELETE

DELETE FROM table_name WHERE some_column=some_value;

9.SQL SELECT TOP

SELECT column_name(s) FROM table_name LIMIT number;

10.SQL LIKE

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

SELECT * FROM Websites WHERE url LIKE  'https%';

SELECT * FROM Websites WHERE url LIKE '%https';

SELECT * FROM Websites WHERE url LIKE '%https%';

SELECT * FROM Websites WHERE url LIKE '_tt_s';

11.SQL IN

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);

12.SQL BETWEEN

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

13.SQL 别名 as newname

SELECT column_name AS alias_name FROM table_name; //列别名

SELECT column_name(s) FROM table_name AS alias_name; //表别名

14.SQL 连接(JOIN)

INNER JOIN：如果表中有至少一个匹配，则返回行

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

FULL JOIN：只要其中一个表中存在匹配，则返回行

SELECT Websites.id, Websites.name, access_log.count, access_log.date FROM Websites INNER JOIN access_log ON Websites.id=access_log.site_id;

15.SQL UNION

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

16.SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中

SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;

17.INSERT INTO SELECT 语句从一个表复制数据

INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;

18.SQL CREATE DATABASE

CREATE DATABASE my_db;

19.SQL CREATE TABLE

CREATE TABLE Persons (
PersonID int,  //column_name1 data_type(size)
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

20.SQL 约束（Constraints）

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (Id_P)  //PRIMARY KEY约束
)

SQL 语句	语法
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND|OR condition
ALTER TABLE	ALTER TABLE table_name ADD column_name datatype or ALTER TABLE table_name DROP COLUMN column_name
AS (alias)	SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE TABLE	CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, ... )
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name) or CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE	DELETE FROM table_name WHERE some_column=some_value or DELETE FROM table_name (Note: Deletes the entire table!!) DELETE * FROM table_name (Note: Deletes the entire table!!)
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name (SQL Server) DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL)
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
HAVING	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO	INSERT INTO table_name VALUES (value1, value2, value3,....) or INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....)
INNER JOIN	SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LEFT JOIN	SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
RIGHT JOIN	SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
FULL JOIN	SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]
SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO	SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name or SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name
SELECT TOP	SELECT TOP number|percent column_name(s) FROM table_name
TRUNCATE TABLE	TRUNCATE TABLE table_name
UNION	SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
UNION ALL	SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
UPDATE	UPDATE table_name SET column1=value, column2=value,... WHERE some_column=some_value
WHERE	SELECT column_name(s) FROM table_name WHERE column_name operator value