SQL基础知识汇总

参考：https://mp.weixin.qq.com/s/RCoROHY7C4oFVq0lUhzPwg

SQL运行先后顺序

(8)SELECT (9)DISTINCT (11)[Top Num][select list]
(1)FROM [left_table]
(3)[join_type]JOIN [right_table]
(2) ON [join_condition]
(4)WHERE [where_condition]
(5)GROUP BY [group_by_list]
(6)WITH [CUBE | RollUP]
(7)HAVING [having_condition]
(10)ORDER BY [order_by_list]

SELECE

select column_name from table_name

DISTINCT

distinct用于返回唯一不同的值
select distince column_name from table_name
例：
select distince Company from Orders

TOP/LIMIT/WHERE

规定要返回的记录的数目
SQL Server：
select Top number|percent column_name(s) from table_name
例：
select Top 2 * from Persons
select Top 50 percent from Persons

MySQL：
select column_name(s) from table_name limit number
例：
select * from Persons limit 5

Oracle：	
select column_name(s) from table_name where rownum<=number
例：
select * from Persons where rownum<=5

WHERE

select column_name from table_name where column_name  operator valuse
例：
select * from renrendai where title='装修'
select * from renrendai where months=6
select * from renrendai where officeType like '%机关'
select * from renrendai where amount between 2000 and 4000

operator	名称
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
between	在某个范围内
like	搜索某种模式

OR/AND

在where子句中将多个条件结合

ORDER BY

用于升序或降序对结果集进行排序
select column1,column2,... from table_name order by column1,column2,...ASC|DESC
select * from Customers order by '省份' ASC,'姓名' DESC

INSERT INTO

insert into table_name (column1,column2,...) values (value1,value2,...)
insert into table_name values (value1,value2,...)

Auto_increment

alter tabel table_name Modify acctid int(11) Auto_incrementame
对于自动递增字段，在将新纪录插入到表中时自动生成

UPDATE

update table_name set column1=value1,column2=value2,... where condition

DELETE

delete from table_name where condition

LIKE

select column1,column2,... from table_name where column like pattern

通配符

通配符	解释
%	表示0个，1个或多个字符
_	表示单个字符
[charlist]	定义要匹配的字符的集合和范围 MS Access/SQL Server
[!charlist]or[^charlist]	定义不匹配的字符的集合和范围 MS Access/SQL Server

IN

select  column_name(s) from table_name where column_name in (value1,value2,...)
select  column_name(s) from table_name where column_name in (select statement)
例:
select * from renrendai where amount in(select amount from test1)

BETWEEN

select  column_name(s) from table_name 
	where column_name between value1 and value2
例：
select * from rrenrendai 
	where (amount between 1000 and 2000) AND not position in ('公务员','管理人员')

SQL通用数据类型

创建新数据库

CREATE DATABASE deldb

创建新表

CREATE TABLE test1(
	NAME VARCHAR(10),
	age INT(3),
	birthday DATE,
	POSITION VARCHAR(50)
	)

插入列

ALTER TABLE test1 ADD userid INT

删除列

ALTER TABLE test1 DROP COLUMN age

创建索引

CREATE INDEX id ON test1 (userid)

重命名

SELECT POSITION AS address FROM test1##??这个是什么数据库的语法

重命名列名称

ALTER TABLE test1 CHANGE POSITION address VARCHAR(50) #MySQL数据库

重命名表名称

RENAME TABLE test1 TO Users

创建视图

CREATE VIEW lookview AS SELECT nickName FROM renrendai 
	WHERE gender='女' AND amount>400000

删除一条记录

DELETE FROM test1 WHERE LoanId=2000003

删除全部记录

DELETE FROM account

删除表

DROP TABLE stu

删除INDEX索引

ALTER TABLE users DROP INDEX id

group by分组

SELECT AVG(amount),AVG(sumCreditPoint),marriage FROM renrendai 	
	WHERE POSITION IN ('公务员','管理人员') 
	GROUP BY marriage HAVING marriage!='null'

INNER JOIN内连接

SELECT * FROM USER INNER JOIN account ON user.`acctid`=account.`acctid`

LEFT JOIN左连接

SELECT *FROM USER 
	LEFT JOIN account ON user.`acctid`=account.`acctid`

right join右连接

SELECT * FROM USER 
	RIGHT JOIN account ON user.`acctid`=account.`acctid` 
	ORDER BY money DESC

full join全连接 (MySQL没有)

SELECT * FROM USER 
	FULL JOIN account ON user.`acctid`=account.`acctid`

建立新表

INSERT INTO newtable (acctid,NAME,age,address)
	SELECT * FROM USER 
	WHERE acctid BETWEEN 14 AND 16

清空表

TRUNCATE TABLE newtable

去重并集

SELECT (acctid) FROM USER 
	UNION SELECT (acctid) FROM account

不去重并集

SELECT (acctid) FROM USER 
	UNION ALL SELECT (acctid) FROM account

更新

UPDATE USER SET NAME='zhang'WHERE acctid =16

IS NULL

SELECT * FROM USER WHERE NAME IS NULL

IS NOT NULL

SELECT * FROM  USER WHERE NAME IS NOT NULL