……看了w3school后的总结章。一些小总结。
w3school链接:
~ sql教程:https://www.w3school.com.cn/sql/index.asp
~ sql测试:https://www.w3school.com.cn/sql/sql_quiz.asp
总结:
一、SQL(Structured Query Language)
1.什么是 SQL?
SQL 指结构化查询语言
SQL 使我们有能力访问数据库
SQL 是一种 ANSI 的标准计算机语言
编者注:ANSI,美国国家标准化组织
2.RDBMS
RDBMS 指的是关系型数据库管理系统。
RDBMS 是 SQL 的基础,同样也是所有现代数据库系统的基础,比如 MS SQL Server, IBM DB2, Oracle, MySQL 以及 Microsoft Access。
RDBMS 中的数据存储在被称为表(tables)的数据库对象中。
表是相关的数据项的集合,它由列和行组成。
3.SQL语法
一定要记住,SQL 对大小写不敏感!
SQL DML 和 DDL
可以把 SQL 分为两个部分:数据操作语言 (DML) 和 数据定义语言 (DDL)。
SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。
查询和更新指令构成了 SQL 的 DML 部分:
SELECT- 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引(键),规定表之间的链接,以及施加表间的约束。
SQL 中最重要的 DDL 语句:
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更(改变)数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引(搜索键)
DROP INDEX - 删除索引
(1)SELECT
SELECT 语句用于从表中选取数据。
结果被存储在一个结果表中(称为结果集)。
SQL SELECT 语法
SELECT 列名称 FROM 表名称
以及:
SELECT * FROM 表名称
(2)DISTINCT
关键词 DISTINCT 用于返回唯一不同的值。
语法:
SELECT DISTINCT 列名称 FROM 表名称
(3)WHERE
如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句。
语法:
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
操作符 描述
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式
引号的使用 :
请注意,我们在例子中的条件值周围使用的是单引号。
SQL 使用单引号来环绕文本值(大部分数据库系统也接受双引号)。如果是数值,请不要使用引号。
(4)AND 和 OR 运算符
AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。
如果第一个条件和第二个条件都成立,则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立,则 OR 运算符显示一条记录。
(5)ORDER BY语句
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。
ASC升序 (从小到大)
DESC降序(从大到小)
PS:对于生日、时间排序时要注意。
(6)INSERT INTO 语句
INSERT INTO 语句用于向表格中插入新的行。
语法:
INSERT INTO 表名称 VALUES (值1, 值2,....)
我们也可以指定所要插入数据的列:
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
(7)Update 语句
Update 语句用于修改表中的数据。
语法:
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
(8)DELETE 语句
DELETE 语句用于删除表中的行。
语法:
DELETE FROM 表名称 WHERE 列名称 = 值
(9)TOP 子句
TOP 子句用于规定要返回的记录的数目。
对于拥有数千条记录的大型表来说,TOP 子句是非常有用的。
注释:并非所有的数据库系统都支持 TOP 子句。
SQL Server 的语法:
SELECT TOP number|percent column_name(s)
FROM table_name
(10)LIKE 操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
SQL LIKE 操作符语法:
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
(11)SQL 通配符
在搜索数据库中的数据时,SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。
在 SQL 中,可使用以下通配符:
通配符 描述
% 替代一个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]或者[!charlist] 不在字符列中的任何单一字符
使用 [charlist] 通配符
例子 1
现在,我们希望从上面的 “Persons” 表中选取居住的城市以 “A” 或 “L” 或 “N” 开头的人:
我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons
WHERE City LIKE '[ALN]%'
现在,我们希望从上面的 “Persons” 表中选取居住的城市不以 “A” 或 “L” 或 “N” 开头的人:
我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'
(12)IN 操作符
IN 操作符允许我们在 WHERE 子句中规定多个值。
SQL IN 语法:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
(13)BETWEEN 操作符
操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
SQL BETWEEN 语法:
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
如需以字母顺序显示介于 “Adams”(包括)和 “Carter”(不包括)之间的人,请使用下面的 SQL:(曾经做错的题)
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'
(14)SQL Alias (AS)
表的 SQL Alias 语法:
SELECT column_name(s)
FROM table_name
AS alias_name
列的 SQL Alias 语法:
SELECT column_name AS alias_name
FROM table_name
(15)不同的 SQL JOIN
1° INNER JOIN(内连接)
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释:INNER JOIN 与 JOIN 是相同的。
JOIN
: 如果表中有至少一个匹配,则返回行
2° SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
3°SQL RIGHT JOIN 关键字
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释:在某些数据库中, RIGHT JOIN 称为 RIGHT OUTER JOIN。
4°SQL FULL JOIN 关键字
只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释:在某些数据库中, FULL JOIN 称为 FULL OUTER JOIN。
(16)UNION操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
SQL UNION语法:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
SQL UNION ALL 语法:
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
(17)SELECT INTO 语句
SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
SQL SELECT INTO 语法
您可以把所有的列插入新表:
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
或者只把希望的列插入新表:
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename
(18)CREATE DATABASE 语句
CREATE DATABASE 用于创建数据库。
CREATE DATABASE database_name
(19)CREATE TABLE 语句
CREATE TABLE 语句用于创建数据库中的表。
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
PS:数据类型见Web前端之sql(2)
(20)SQL 约束 (Constraints)
约束用于限制加入表的数据的类型。
可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)。
1° SQL NOT NULL 约束
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。
下面的 SQL 语句强制 “Id_P” 列和 “LastName” 列不接受 NULL 值:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
2° SQL UNIQUE 约束
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
3°SQL PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键,并且每个表只能有一个主键。
创建表格时用主键:
PRIMARY KEY (Id_P) #句末
或者:
Id_P int NOT NULL PRIMARY KEY #类型之后
如果需要命名 PRIMARY KEY 约束,以及为多个列定义 PRIMARY KEY 约束,请使用下面的 SQL 语法:(用在末尾)
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
4°SQL FOREIGN KEY 约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
创建表格时用外键:
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) #句末
或者:
Id_P int FOREIGN KEY REFERENCES Persons(Id_P) #类型之后
如果需要命名 FOREIGN KEY 约束,以及为多个列定义 FOREIGN KEY 约束,请使用下面的 SQL 语法:
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)
5°SQL CHECK 约束
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制。
CHECK同主键外键类似用法。三者要添加用ADD撤销用DROP。
6°SQL DEFAULT 约束
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值,那么会将默认值添加到所有的新记录。
City varchar(255) DEFAULT 'Sandnes'
如果在表已存在的情况下为 “City” 列创建 DEFAULT 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'
如需撤销 DEFAULT 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
(21)SQL CREATE INDEX 语句
CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。
(22)SQL CREATE INDEX 语法
在表上创建一个简单的索引。允许使用重复的值:
CREATE INDEX index_name
ON table_name (column_name)
注释:“column_name” 规定需要索引的列。
SQL CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name
ON table_name (column_name ASC/DESC)
(23)DROP 语句
通过使用 DROP 语句,可以轻松地删除索引、表和数据库。
我们可以使用 DROP INDEX 命令删除表格中的索引。
DROP TABLE 语句用于删除表(表的结构、属性以及索引也会被删除)。
DROP DATABASE 语句用于删除数据库。
TRUNCATE TABLE 命令(仅仅删除表格中的数据):
TRUNCATE TABLE 表名称
(24)ALTER TABLE 语句
ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
SQL ALTER TABLE 语法:
如需在表中添加列,请使用下列语法:
ALTER TABLE table_name
ADD column_name datatype
要删除表中的列,请使用下列语法:
ALTER TABLE table_name
DROP COLUMN column_name
注释:某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。
要改变表中列的数据类型,请使用下列语法:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
注意:COLUMN要保留
(25)AUTO INCREMENT 字段
我们通常希望在每次插入新记录时,自动地创建主键字段的值。
我们可以在表中创建一个 auto-increment 字段。
P_Id int NOT NULL AUTO_INCREMENT,
(26)VIEW视图
注释:数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
SQL CREATE VIEW 语法:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SQL 更新视图
您可以使用下面的语法来更新视图:
SQL CREATE OR REPLACE VIEW Syntax #syntax语法
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
现在,我们希望向 “Current Product List” 视图添加 “Category” 列。我们将通过下列 SQL 更新视图:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
SQL 撤销视图
您可以通过 DROP VIEW 命令来删除视图。
SQL DROP VIEW Syntax
DROP VIEW view_name
4.SQL函数
函数的语法:
内建 SQL 函数的语法是:
SELECT function(列) FROM 表
函数的类型:
在 SQL 中,基本的函数类型和种类有若干种。函数的基本类型是:
- Aggregate 函数
- Scalar 函数 (标量函数)
合计函数(Aggregate functions)
聚合函数COUNT( )/SUM( )/AVG( )/MAX( )/MIN( )
Aggregate 函数的操作面向一系列的值,并返回一个单一的值。
注释:如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句,则这个 SELECT 必须使用 GROUP BY 语句!
(1)SQL AVG 函数
AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
SQL AVG() 语法:
SELECT AVG(column_name) FROM table_name
(2)SQL COUNT() 函数
SQL COUNT(column_name) 语法:
COUNT(column_name) 函数返回指定列的值的数目(NULL 不计入):
SELECT COUNT(column_name) FROM table_name
SQL COUNT() 语法:
COUNT() 函数返回表中的记录数:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) 语法
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目:
SELECT COUNT(DISTINCT column_name) FROM table_name
注释:COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server,但是无法用于 Microsoft Access。
(3)FIRST() 函数
FIRST() 函数返回指定的字段中第一个记录的值。
提示:可使用 ORDER BY 语句对记录进行排序。
SQL FIRST() 语法:
SELECT FIRST(column_name) FROM table_name
(4)LAST() 函数
LAST() 函数返回指定的字段中最后一个记录的值。
SQL LAST() 语法:
SELECT LAST(column_name) FROM table_name
(5)MAX() 函数
MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
SQL MAX() 语法:
SELECT MAX(column_name) FROM table_name
(6)MIN() 函数
MIN 函数返回一列中的最小值。NULL 值不包括在计算中。
SQL MIN() 语法:
SELECT MIN(column_name) FROM table_name
注释:MIN 和 MAX 也可用于文本列,以获得按字母顺序排列的最高或最低值。
(7)SUM() 函数
SUM 函数返回数值列的总数(总额)。
SQL SUM() 语法:
SELECT SUM(column_name) FROM table_name
(8)GROUP BY 语句
GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。
合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。
SQL GROUP BY 语法:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
(9)HAVING 子句
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
SQL 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作用类似,对合计函数结果进行操作。
(10)UCASE() 函数
UCASE 函数把字段的值转换为大写。
SQL UCASE() 语法:
SELECT UCASE(column_name) FROM table_name
(11)LCASE() 函数
LCASE 函数把字段的值转换为小写。
SQL LCASE() 语法:
SELECT LCASE(column_name) FROM table_name
(12)MID() 函数
MID 函数用于从文本字段中提取字符。
SQL MID() 语法:
SELECT MID(column_name,start[,length]) FROM table_name
参数 描述
column_name 必需。要提取字符的字段。
start 必需。规定开始位置(起始值是 1)。
length 可选。要返回的字符数。如果省略,则 MID() 函数返回剩余文本。
(13)LEN() 函数
LEN 函数返回文本字段中值的长度。
SQL LEN() 语法:
SELECT LEN(column_name) FROM table_name
(14)ROUND() 函数
ROUND 函数用于把数值字段舍入为指定的小数位数。
SQL ROUND() 语法:
SELECT ROUND(column_name,decimals) FROM table_name
参数 描述
column_name 必需。要舍入的字段。
decimals 必需。规定要返回的小数位数。
(15)NOW() 函数
NOW 函数返回当前的日期和时间。
提示:如果您在使用 Sql Server 数据库,请使用 getdate() 函数来获得当前的日期时间。
SQL NOW() 语法:
SELECT NOW() FROM table_name
关于时间的问题:参考这个
(16)FORMAT() 函数
FORMAT 函数用于对字段的显示进行格式化。
SQL FORMAT() 语法:
SELECT FORMAT(column_name,format) FROM table_name
参数 描述
column_name 必需。要格式化的字段。
format 必需。规定格式。
三、个人补充(待补)
1.中文字符转换utf8 charset=utf8
2.分页查询 limit start()