SQL速通

本文记录了我在阅读完SQL 教程 | 菜鸟教程和牛客网在线编程_SQL篇_非技术快速入门之后学到的全部知识点。学习完SQL后建议学习ADO或PHP MySQL

 

官方文档（可以用来查找date相关函数等）：MySQL :: MySQL 8.0 Reference Manual

目录

增删改合

create：增

drop、delete、truncate：删

Alter、update、SELECT INTO、INSERT INTO：改

Union：合

SQL约束

As：即alias

数据类型

SQL通用数据类型：

NULL

Select、条件

select、select top、limit

where、having

group by、order by

join

SQL主机

典型习题

---

增删改合

create：增

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 TABLE 新表 AS SELECT * FROM 旧表
CREATE INDEX 用户无法看到索引，它们只能被用来加速查询	CREATE INDEX index_name ON table_name (column_name) 或 CREATE UNIQUE INDEX index_name ON table_name (column_name) UNIQUE唯一的索引意味着两个行不能拥有相同的索引值
CREATE VIEW 视图总是自动显示最新的数据；视图可以被嵌套	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
CREATE OR REPLACE VIEW	CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

SQL对大小写不敏感

id列是自动更新的，表中的每条记录都有一个唯一的数字

如果列名称包含空格，要求使用双引号或方括号

只复制表结构，不复制其数据：

create table table_name_new as select * from table_name_old where 1=2;

或

create table table_name_new like table_name_old

drop、delete、truncate：删

DROP VIEW	DROP VIEW view_name
DELETE	DELETE FROM table_name WHERE some_column=some_value 删除整个table： DELETE FROM table_name或DELETE * FROM table_name
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	ALTER TABLE table_name DROP INDEX index_name（MySQL）
DROP/TRUNCATE TABLE	DROP/TRUNCATE TABLE table_name

drop、delete、truncate的不同点：

• delete：删除指定数据；删除整个表：仅删除表的所有内容，保留表的定义（又称表的结构），不释放空间，可以回滚恢复（系统的回滚段rollback segement要足够大）；
• drop：删除表内容和结构，释放空间，将表删除的一干二净，没有备份表之前要慎用；
• truncate：删除表的内容，释放空间，但保留表的定义，没有备份表前要慎用。若是整理表内部的碎片，可用truncate加reuse stroage，再重新导入/插入数据。
• 执行速度：drop>truncate>delete

Alter、update、SELECT INTO、INSERT INTO：改

ALTER TABLE （ALTER DATABASE类似	ALTER TABLE table_name ADD column_name datatype 或 ALTER TABLE table_name DROP COLUMN column_name
UPDATE	UPDATE table_name SET column1=value, column2=... WHERE condition

如果设置了sql_safe_updates=1，那么：

update语句必须满足如下条件之一才能执行成功：

• 使用where子句,并且where子句中列必须为prefix索引列
• 使用limit
• 同时使用where子句和limit(此时where子句中列可以不是索引列)

delete语句必须满足如下条件之一才能执行成功：

• 使用where子句,并且where子句中列必须为prefix索引列
• 同时使用where子句和limit(此时where子句中列可以不是索引列)

SELECT INTO FROM 要求目标表不存在	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
INSERT INTO SELECT 要求目标表存在	INSERT INTO table2 SELECT * FROM table1;#需两个表结构一样 或 INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;
INSERT INTO	INSERT INTO table_name VALUES (value1, value2, value3,....) or INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....)

Union：合

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

UNION中每个SELECT语句必须拥有相同数量的列；列也必须拥有相似的数据类型；每个SELECT语句中的列的顺序必须相同。

UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名。

只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序

SQL约束

NOT NULL：指示某列不能存储NULL值。

UNIQUE：保证某列的每行必须有唯一的值。（UNIQUE和PRIMARY KEY约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY：约束拥有自动定义的UNIQUE约束。

每个表可以有多个UNIQUE约束，但是每个表只能有一个PRIMARY KEY约束。

PRIMARY KEY是NOT NULL和UNIQUE的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。主键列不能包含NULL值。每个表都应该有一个主键，并且每个表只能有一个主键。

FOREIGN KEY：一个表中的FOREIGN KEY指向另一个表中的UNIQUE KEY(唯一约束的键)。用于预防破坏表之间连接的行为。

CHECK：保证列中的值符合指定的条件。

DEFAULT：规定没有给列赋值时的默认值。

AUTO_INCREMENT：在新记录插入表中时生成一个唯一的数字，即自动地创建主键字段的值。默认AUTO_INCREMENT的开始值是1。

约束可以在创建表时规定（通过CREATE TABLE语句），或者在表创建之后规定（通过ALTER TABLE语句）。以MySQL为例（SQL Server、MS Access、Oracle等的语法与之不同）：

添加约束：

ALTER TABLE Persons

ALTER City SET DEFAULT 'SANDNES'

删除约束：

ALTER TABLE Persons

ALTER City DROP DEFAULT

改变表中列的数据类型：

ALTER TABLE table_name

MODIFY COLUMN column_name datatype

As：即alias

SELECT column_name FROM table_name AS table_alias表的别名

或

SELECT column_name AS column_alias列的别名 FROM table_name

把3个列（url、alexa和country）结合在一起并创建一个名为"site_info"的别名：

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM table;

数据类型

SQL通用数据类型：

CHARACTER(n)	字符/字符串。固定长度n
VARCHAR(n)或 CHARACTER VARYING(n)	字符/字符串。可变长度，最大长度n
BINARY(n)	二进制串。固定长度n
BOOLEAN	存储TRUE或FALSE值
VARBINARY(n) 或 BINARY VARYING(n)	二进制串。可变长度，最大长度n
INTEGER(p)	整数值（没有小数点）。精度p
SMALLINT	整数值（没有小数点）。精度5
INTEGER	整数值（没有小数点）。精度10
BIGINT	整数值（没有小数点）。精度19
DECIMAL(p,s)	精确数值，精度p（=小数点前的位数+小数点后的位数s）
NUMERIC(p,s)	精确数值，精度p（=小数点前的位数+小数点后的位数s）
FLOAT(p)	近似数值，尾数精度p。一个采用以10为基数的指数计数法的浮点数。该类型的size参数由一个指定最小精度的单一数字组成
REAL	近似数值，尾数精度7
FLOAT	近似数值，尾数精度16
DOUBLE PRECISION	近似数值，尾数精度16
DATE	存储年、月、日的值
TIME	存储小时、分、秒的值
TIMESTAMP	存储年、月、日、小时、分、秒的值
INTERVAL	由一些整数字段组成，代表一段时间，取决于区间的类型
ARRAY	元素的固定长度的有序集合
MULTISET	元素的可变长度的无序集合
XML	存储XML数据

不同的数据库平台上一些数据类型的通用名称：

数据类型	Access	SQLServer	Oracle	MySQL	PostgreSQL
boolean	Yes/No	Bit	Byte	N/A	Boolean
integer	Number (integer)	Int	Number	Int Integer	Int Integer
float	Number (single)	Float Real	Number	Float	Numeric
currency	Currency	Money	N/A	N/A	Money
string (fixed)	N/A	Char	Char	Char	Char
string (variable)	Text (<256) Memo (65k+)	Varchar	Varchar Varchar2	Varchar	Varchar
binary object	OLE Object Memo	Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB)	Long Raw	Blob Text	Binary Varbinary

Microsoft Access、MySQL 和 SQL Server的数据类型：

SQL MS Access、MySQL 和 SQL Server 数据类型 | 菜鸟教程

NULL

无法将NULL用比较运算符和0等比较，必须用IS NULL和IS NOT NULL

NULL函数（以MySQL为例）：

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))

FROM Products

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))

FROM Products

Select、条件

select、select top、limit

SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT TOP	SELECT TOP number|percent column_name(s) FROM table_name 变相返回后N行：select top 5 * from table order by id desc
Limit	LIMIT后面跟两个参数：第一个数表示要跳过的数量，后一位表示要取的数量； LIMIT后面跟一个参数：表示要取的数据的数量，≈top； LIMIT和OFFSET组合使用：LIMIT后只能有一个参数，表示要取的的数量，OFFSET表示要跳过的数量

where、having

WHERE	SELECT column_name(s) FROM table_name WHERE column_name operator value
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 增加HAVING子句原因：WHERE无法与聚合函数一起用

WHERE子句中的运算符：

=、<>、>、<、>=、<=

BETWEEN and、not between

LIKE：+通配符，模糊查询；not like

IN：WHERE column_name IN (value1,value2,..)；not in

is null、is not null

0、1

逻辑运算的优先级：()、not、and、or

EXISTS运算符：用于判断查询子句是否有记录，若有一条或多条记录存在返回True，否则返回False。NOT EXISTS

SELECT column_name(s) FROM table_name

WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

通配符：

%：0个及以上个字符

_：1个字符

like '段\_' escape '\'：通过\转义，只能查出来“段_”

[charlist]：字符列中的任何单一字符

[^charlist]或[!charlist]：不在字符列中的任何单一字符

REGEXP或NOT REGEXP运算符(或RLIKE和NOT RLIKE)：用来操作正则表达式

where和having的区别：

• where在group by前，having在group by之后
• 聚合函数不能放在where后，但可放在having后

group by、order by

GROUP BY	SELECT column_name,aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
ORDER BY 默认升序	SELECT column_name(s) FROM table_name ORDER BY column_name(s) [ASC|DESC] desc和asc只对它紧跟着的第一个列名有效；可用函数来排序：order by abs(a);

join

JOIN=INNER JOIN

LEFT JOIN=LEFT OUTER JOIN

RIGHT JOIN=RIGHT OUTER JOIN

FULL OUTER JOIN：结合了LEFT JOIN和RIGHT JOIN的结果

SQL主机

SQL是一种与数据库系统协同工作的标准语言，这些数据库系统包括MS SQL Server、IBM DB2、Oracle、MySQL和MS Access等等。

如果您想要您的网站存储数据在数据库并从数据库显示数据，您的Web服务器必须能使用SQL语言访问数据库系统。

如果您的Web服务器托管在互联网服务提供商（ISP=Internet Service Provider），您必须寻找SQL主机计划。

您可在各操作系统上运行SQL主机数据库。操作系统上对应运行的数据库系统：

MS SQL Server2：在Windows和Linux操作系统上运行

MySQL：在Windows,Mac OS X和Linux/UNIX操作系统上运行

MS Access（只建议用于小型网站）：只在Windows OS上运行

函数

SQL Aggregate函数：

SELECT AVG/MAX/MIN/SUM(column_name) FROM table_name;

SELECT FIRST/LAST(column_name) FROM table_name;#用于MS Access

SELECT COUNT(column_name) FROM table_name;#NULL不计入

SELECT COUNT(*) FROM table_name;

SELECT COUNT(DISTINCT column_name) FROM table_name;#用于ORACLE和Microsoft SQL Server，不用于Microsoft Access

SQL Scalar函数：

SELECT UCASE/LCASE(column_name) FROM table_name;

SELECT UPPER/LOWER(column_name) FROM table_name;#用于SQL Server

SELECT MID(column_name,start[,length]) FROM table_name;#起始值是1

select substr(("列名",a,b) from <table_name>;#类似mid，用于Oracle

SELECT LEN(column_name) FROM table_name;

SELECT LENGTH(column_name) FROM table_name;#用于MySQL

SELECT ROUND(column_name[,decimals]) FROM TABLE_NAME;

SELECT NOW() FROM table_name;

SELECT FORMAT(column_name,format) FROM table_name;

典型习题

下面这些题是我看完上面的知识点后没能自己做出来的。牛客网的牛客网在线编程_SQL篇_非技术快速入门部分的其他题都能够独立做出来。

嵌套表（即带括号）：分组过滤练习题_牛客题霸_牛客网

2个表join：统计每个学校的答过题的用户的平均答题数_牛客题霸_牛客网

3个表join：统计每个学校各难度的用户平均刷题数_牛客题霸_牛客网

Case语句：计算25岁以上和以下的用户数量_牛客题霸_牛客网

group by的易错点、in：找出每个学校GPA最低的同学_牛客题霸_牛客网

If函数、where和having的区别：浙大不同难度题目的正确率_牛客题霸_牛客网

算法难：计算用户的平均次日留存率_牛客题霸_牛客网