首发于数据、算法、规划学习

SQL语法学习

1、常用的SQL命令

  • SELECT - 从数据库中提取数据
  • UPDATE - 更新数据库中的数据
  • DELETE - 从数据库中删除数据
  • INSERT INTO - 向数据库中插入新数据
  • CREATE DATABASE - 创建新数据库
  • ALTER DATABASE - 修改数据库
  • CREATE TABLE - 创建新表
  • ALTER TABLE - 变更（改变）数据库表
  • DROP TABLE - 删除表
  • CREATE INDEX - 创建索引（搜索键）
  • DROP INDEX - 删除索引

2、SQL基础语法

（1）SELECT 语句

SELECT 语句用于从数据库中选取数据。

SELECT column_name,column_name
FROM table_name;
SELECT * FROM table_name;

（2）SELECT DISTINCT 语句

SELECT DISTINCT 语句用于返回唯一不同的值，在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。DISTINCT 关键词用于返回唯一不同的值。

SELECT DISTINCT column_name,column_name
FROM table_name;

（3）WHERE 子句

WHERE 子句用于过滤记录。用于提取那些满足指定条件的记录。

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
运算符可以在 WHERE 子句中使用

（4）AND & OR 运算符

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

SELECT * FROM Websites
WHERE country='CN'
AND alexa > 50;

SELECT * FROM Websites
WHERE country='USA'
OR country='CN';

SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA');

（5）ORDER BY 关键字

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

order by A,B        这个时候都是默认按升序排列
order by A desc,B   这个时候 A 降序，B 升序排列
order by A ,B desc  这个时候 A 升序，B 降序排列

（6）INSERT INTO 语句

用于向表中插入新记录。

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

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

第二种形式需要指定列名及被插入的值：

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

（7）UPDATE 语句

用于更新表中的记录。

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

（8）DELETE 语句

用于删除表中的记录

DELETE FROM table_name
WHERE some_column=some_value;

3、SQL进阶语法

（1）SELECT TOP, LIMIT, ROWNUM 子句

用于规定要返回的记录的数目。

SQL Server / MS Access 语法

SELECT TOP number|percent column_name(s)
FROM table_name;

MySQL 语法

SELECT column_name(s)
FROM table_name
LIMIT number;

Oracle 语法

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

（2）LIKE 操作符

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

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

Select * from emp where ename like 'M%';
  • % 表示多个字值，_ 下划线表示一个字符；
  • M% : 为通配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
  • %M% : 表示查询包含M的所有内容。
  • %M_ : 表示查询以M在倒数第二位的所有内容。

（3）SQL 通配符

通配符可用于替代字符串中的任何其他字符。

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

下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站

SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';

下面的 SQL 语句选取 name 以 A 到 H 字母开头的网站：

SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';

下面的 SQL 语句选取 name 不以 A 到 H 字母开头的网站：

SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';

（4）IN 操作符

IN 操作符允许您在 WHERE 子句中规定多个值。

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

（5）BETWEEN 操作符

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

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

NOT BETWEEN 操作符实例

SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20;

带有 IN 的 BETWEEN 操作符实例

SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');

（6）SQL 别名

通过使用 SQL，可以为表名称或列名称指定别名

列的 SQL 别名语法

SELECT column_name AS alias_name
FROM table_name;

表的 SQL 别名语法

SELECT column_name(s)
FROM table_name AS alias_name;

（7）SQL 连接(JOIN)

SQL join 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

  • INNER JOIN 关键字

INNER JOIN 关键字在表中存在至少一个匹配时返回行。

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

INNER JOIN 与 JOIN 是相同的。

  • LEFT JOIN 关键字

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
  • RIGHT JOIN 关键字

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
  • FULL OUTER JOIN 关键字

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

（8）UNION 操作符

UNION 操作符合并两个或多个 SELECT 语句的结果。UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

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

UNION ALL 语法

下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

（9）SELECT INTO 语句

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

复制所有的列插入到新表中：
SELECT *
INTO newtable [IN externaldb]
FROM table1;

只复制希望的列插入到新表中：
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

（10）INSERT INTO SELECT 语句

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

从一个表中复制所有的列插入到另一个已存在的表中：

INSERT INTO table2
SELECT * FROM table1;

只复制希望的列插入到另一个已存在的表中：

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

（11）CREATE DATABASE 语句

创建数据库。

CREATE DATABASE dbname;

（12）CREATE TABLE 语句

CREATE TABLE 语句用于创建数据库中的表。表由行和列组成，每个表都必须有个表名。

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

（13）SQL 约束（Constraints）

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

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,
....
);
  • NOT NULL - 指示某列不能存储 NULL 值。
  • UNIQUE - 保证某列的每行必须有唯一的值。
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK - 保证列中的值符合指定的条件。
  • DEFAULT - 规定没有给列赋值时的默认值

（14）CREATE INDEX 语句

CREATE INDEX 语句用于在表中创建索引。在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。可以在表中创建索引，以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。

SQL CREATE INDEX 语法（在表上创建一个简单的索引。允许使用重复的值）

CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX 语法（在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值）

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

（15）撤销索引、撤销表以及撤销数据库

通过使用 DROP 语句，可以轻松地删除索引、表和数据库。

用于 MS SQL Server 的 DROP INDEX 语法：

DROP INDEX table_name.index_name

用于 MySQL 的 DROP INDEX 语法：

ALTER TABLE table_name DROP INDEX index_name

DROP TABLE 语句用于删除表

DROP TABLE table_name

DROP DATABASE 语句用于删除数据库。

DROP DATABASE database_name

如果我们使用 TRUNCATE TABLE 语句仅仅需要删除表内的数据，但并不删除表本身。

TRUNCATE TABLE table_name

（16）ALTER TABLE 语句

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name
ADD column_name datatype

如需删除表中的列，请使用下面的语法：

ALTER TABLE table_name
DROP COLUMN column_name

改变数据类型

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

（17）AUTO INCREMENT 字段

Auto-increment 会在新记录插入表中时生成一个唯一的数字。

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

（18）SQL 视图（Views）

视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。您可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition


CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

SELECT * FROM [Current Product List]

更新视图

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition


CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

撤销视图

DROP VIEW view_name

（19）SQL Date 函数

MySQL Date 函数

（20）SQL NULL 值

如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。

使用 IS NULL 操作符

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

（21）NULL 函数

判定NULL值，用于规定如何处理 NULL 值。

  • ISNULL() （SQL Server / MS Access）
  • NVL() （Oracle）
  • IFNULL() （MySQL）
  • COALESCE() （MySQL）


（22）通用数据类型

4、SQL函数

（1）SQL 聚合函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

  • AVG() - 返回平均值
  • COUNT() - 返回行数
  • FIRST() - 返回第一个记录的值
  • LAST() - 返回最后一个记录的值
  • MAX() - 返回最大值
  • MIN() - 返回最小值
  • SUM() - 返回总和

（2）SQL 标量函数

SQL Scalar 函数基于输入值，返回一个单一的值。

  • UCASE() - 将某个字段转换为大写
  • LCASE() - 将某个字段转换为小写
  • MID() - 从某个文本字段提取字符，MySql 中使用
  • SubString(字段，1，end) - 从某个文本字段提取字符
  • LEN() - 返回某个文本字段的长度
  • ROUND() - 对某个数值字段进行指定小数位数的四舍五入
  • NOW() - 返回当前的系统日期和时间
  • FORMAT() - 格式化某个字段的显示方式

（3）GROUP BY 语句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;


SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

GROUP BY 多表连接

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

（4）HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

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;


SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

（5）EXISTS 运算符

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

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

5、判断语句

（1）if语句

IF(expr1,expr2,expr3)

expr1条件，条件为true，则值是expr2 ，false，值就是expr3

select *,if(age=1,"男","女") as ages from user;

（2）case when 语句

CASE WHEN <判断表达式> THEN <表达式>
     WHEN <判断表达式> THEN <表达式> 
     WHEN <判断表达式> THEN <表达式>
     ...
     ELSE <表达式>
END

CASE 列名
    WHEN 条件 THEN 结果 
    ELSE 其他结果
    END 别名

UPDATE `线路主表` 
SET `距离` =
CASE
		WHEN `距离` < 100 THEN
		100 
		WHEN `距离` < 500 THEN
		500 ELSE 1000 
END
WHERE
	`距离` < 1000;

（3）IF...ELSE语句

IF search_condition THEN 
    statement_list  
[ELSEIF search_condition THEN]  
    statement_list ...  
[ELSE 
    statement_list]  
END IF

编辑于 2020-06-04 16:15
SQL
MySQL
数据库

文章被以下专栏收录