SQL基本

SQL SELECT

1
2
SELECT column1, column2, ...
FROM table_name;

SQL SELECT DISTINCT

SELECT DISTINCT 语句用于返回唯一不同的值。

在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。

DISTINCT 关键词用于返回唯一不同的值。

1
2
SELECT DISTINCT column1, column2, ...
FROM table_name;

SQL WHERE

WHERE 子句用于过滤记录。

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE condition;

SQL AND & OR 运算符

AND & OR 运算符用于基于一个以上的条件对记录进行过滤。

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

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

下面的 SQL 语句从 “Websites” 表中选取 alexa 排名大于 “15” 且国家为 “CN” 或 “USA” 的所有网站：

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

SQL ORDER BY 关键字

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

ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字

1
2
3
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

例如：

1
2
SELECT * FROM Websites
ORDER BY alexa DESC;

SQL INSERT INTO 语句

NSERT INTO 语句用于向表中插入新记录。

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

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

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

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

参数说明：

  • table_name：需要插入新记录的表名。
  • column1, column2, …：需要插入的字段名。
  • value1, value2, …：需要插入的字段值。

SQL UPDATE 语句

UPDATE 语句用于更新表中已存在的记录。

1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

参数说明：

  • table_name：要修改的表名称。
  • column1, column2, …：要修改的字段名称，可以为多个字段。
  • value1, value2, …：要修改的值，可以为多个值。
  • condition：修改条件，用于指定哪些数据要修改。

Update 警告！

在更新记录时要格外小心！在上面的实例中，如果我们省略了 WHERE 子句，如下所示：

1
2
UPDATE Websites
SET alexa='5000', country='USA'

执行以上代码会将 Websites 表中所有数据的 alexa 改为 5000，country 改为 USA。

执行没有 WHERE 子句的 UPDATE 要慎重，再慎重。

SQL DELETE 语句

1
2
DELETE FROM table_name
WHERE condition;

参数说明：

  • table_name：要删除的表名称。
  • condition：删除条件，用于指定哪些数据要删除。

SQL高级教程

SQL SELECT TOP 子句

SELECT TOP 语句用于在 SQL 中限制返回的结果集中的行数， 它通常用于只需要查询前几行数据的情况，尤其在数据集非常大时，可以显著提高查询性能。

SELECT TOP 子句对于拥有数千条记录的大型表来说，是非常有用的。

说明：

  • SELECT TOP 在 SQL Server 和 MS Access 中使用，而在 MySQL 和 PostgreSQL 中使用 LIMIT 关键字。

  • Oracle 在 12c 版本之前没有直接等效的关键字，可以通过 ROWNUM 实现类似功能，但在 12c 及以上版本中引入了 FETCH FIRST。

  • 当使用 TOP 或 LIMIT 时，最好结合 ORDER BY 子句，以确保返回的行是特定顺序的前几行.

由于主包用的是MYSQL：

1
2
3
SELECT column1, column2, ...
FROM table_name
LIMIT number;

SQL LIKE 语法

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

参数说明：

  • column1, column2, …：要选择的字段名称，可以为多个字段。如果不指定字段名称，则会选择所有字段。
  • table_name：要查询的表名称。
  • column：要搜索的字段名称。
  • pattern：搜索模式。

SQL 通配符

在 SQL 中，通配符与 SQL LIKE 操作符一起使用。

SQL 通配符用于搜索表中的数据。

在 SQL 中，可使用以下通配符：

通配符 描述
% 替代 0 个或多个字符
_ 替代一个字符
[charlist] 字符列表中的任何单一字符
[^*charlist*] 或 [!charlist] 不在字符列表中的任何单一字符 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查找城市名称以'Ne'开头的记录
SELECT * FROM Persons WHERE City LIKE 'Ne%';

-- 查找城市名称包含'lond'的记录
SELECT * FROM Persons WHERE City LIKE '%lond%';

-- 查找名字第二个字符是'eorge'的记录
SELECT * FROM Persons WHERE FirstName LIKE '_eorge';

-- 查找姓氏以'C'开头，第三个字符是'r'的记录
SELECT * FROM Persons WHERE LastName LIKE 'C_r_er';

-- 查找城市名称以'A'、'L'或'N'开头的记录
SELECT * FROM Persons WHERE City LIKE '[ALN]%';

-- 查找城市名称不以'A'、'L'或'N'开头的记录
SELECT * FROM Persons WHERE City LIKE '[!ALN]%';

SQL IN语法

IN 操作符

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

SQL IN 语法

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

参数说明：

  • column1, column2, …：要选择的字段名称，可以为多个字段。如果不指定字段名称，则会选择所有字段。
  • table_name：要查询的表名称。
  • column：要查询的字段名称。
  • value1, value2, …：要查询的值，可以为多个值。
1
2
3
4
-- 下面的 SQL 语句选取 name 为 "Google" 或 "菜鸟教程" 的所有网站：
SELECT *
FROM Websites
WHERE name IN ('Google','菜鸟教程');

SQL BETWEEN 语法

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

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;

BETWEEN 操作符实例

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

NOT BETWEEN 操作符实例

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

带有 IN 的 BETWEEN 操作符实例

下面的 SQL 语句选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站：

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

带有文本值的 BETWEEN 操作符实例

下面的 SQL 语句选取 name 以介于 ‘A’ 和 ‘H’ 之间字母开始的所有网站：

1
2
SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';

带有文本值的 NOT BETWEEN 操作符实例

下面的 SQL 语句选取 name 不介于 ‘A’ 和 ‘H’ 之间字母开始的所有网站：

1
2
SELECT * FROM Websites
WHERE name NOT BETWEEN 'A' AND 'H';

带有日期值的 BETWEEN 操作符实例

下面的 SQL 语句选取 date 介于 ‘2016-05-10’ 和 ‘2016-05-14’ 之间的所有访问记录：

1
2
SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

SQL 别名

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

基本上，创建别名是为了让列名称的可读性更强。

列的 SQL 别名语法

1
2
SELECT column_name AS alias_name
FROM table_name;

表的 SQL 别名语法

1
2
3
SELECT column_name(s)
FROM table_name AS alias_name;
-- (s) 是文档中的提示符号，表示“可选一个或多个”。

在下面的 SQL 语句中，我们把三个列（url、alexa 和 country）结合在一起，并创建一个名为 “site_info” 的别名：

1
2
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

下面的 SQL 语句选取 “菜鸟教程” 的所有访问记录。我们使用 “Websites” 和 “access_log” 表，并分别为它们指定表别名 “w” 和 “a”（通过使用别名让 SQL 更简短）：

1
2
3
SELECT w.name, w.url, a.count, a.date
FROM Websites AS w, access_log AS a
WHERE a.site_id=w.id and w.name="菜鸟教程";

在下面的情况下，使用别名很有用：

  • 在查询中涉及超过一个表
  • 在查询中使用了函数
  • 列名称很长或者可读性差
  • 需要把两个列或者多个列结合在一起

SQL 连接(JOIN)

image-20250507174743187
类型 描述
INNER JOIN 返回两个表中满足连接条件的记录（交集）。
LEFT JOIN 返回左表中的所有记录，即使右表中没有匹配的记录（保留左表）。
RIGHT JOIN 返回右表中的所有记录，即使左表中没有匹配的记录（保留右表）。
FULL OUTER JOIN 返回两个表的并集，包含匹配和不匹配的记录。
CROSS JOIN 返回两个表的笛卡尔积，每条左表记录与每条右表记录进行组合。
SELF JOIN 将一个表与自身连接。
NATURAL JOIN 基于同名字段自动匹配连接的表。 
1
2
-- 这样也是连接，相当于inner join
WHERE a.site_id=w.id;

示例数据

表1：Customers

CustomerID Name
1 Alice
2 Bob
3 Charlie

表2：Orders

OrderID CustomerID Product
101 1 Laptop
102 2 Phone
103 4 Tablet

各连接结果对比

JOIN 类型 结果
INNER JOIN 返回两个表中匹配的记录。在给定的例子中，只有 CustomerID 为 1 和 2 的记录在两个表中都有匹配，所以只会返回这些记录。
LEFT JOIN 返回左表（Customers）中的所有记录，即使右表（Orders）中没有匹配的记录。对于左表中没有匹配的右表记录，结果中的右表字段将为 NULL。在例子中，CustomerID 为 3 的记录在右表中没有匹配，所以其对应的 Product 将为 NULL。
RIGHT JOIN 返回右表（Orders）中的所有记录，即使左表（Customers）中没有匹配的记录。对于右表中没有匹配的左表记录，结果中的左表字段将为 NULL。在例子中，OrderID 为 103 的记录在左表中没有匹配，所以其对应的 Name 将为 NULL。
FULL OUTER JOIN 返回两个表中的所有记录，无论它们是否匹配。如果某个表中没有匹配的记录，那么该表的字段将为 NULL。在例子中，CustomerID 为 3 和 OrderID 为 103 的记录将分别在对方的表中显示为 NULL。
CROSS JOIN 返回两个表的笛卡尔积，即左表中的每一行与右表中的每一行组合。在例子中，每个顾客都将与每个订单组合，产生多个结果。
SELF JOIN 表与其自身进行连接。这通常用于查询表中相互关联的记录，比如员工与其经理之间的关系。 

1
2
3
SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition;

SQL INNER JOIN（简单的 JOIN，inner join与join是一样的）

1
2
3
4
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

例：

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

SQL LEFT JOIN

SQL RIGHT JOIN

SQL FULL JOIN

SQL UNION 语法

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。它可以从多个表中选择数据，并将结果集组合成一个结果集。使用 UNION 时，每个 SELECT 语句必须具有相同数量的列，且对应列的数据类型必须相似。

UNION 操作符默认会去除重复的记录，如果需要保留所有重复记录，可以使用 UNION ALL 操作符。

1
2
3
4
5
6
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
-- UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
image-20250507180637546

(如果select多个，id1有重复，id2没有，那么UNION与UNION ALL)?

UNION 的去重逻辑：基于所有选中列的完整组合判断是否重复（此处是 country + name/app_name）。

SQL SELECT INTO 语句

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

注意： MySQL 数据库不支持 SELECT … INTO 语句，但支持 INSERT INTO … SELECT 。 当然你可以使用以下语句来拷贝表结构及数据：

1
2
3
CREATE TABLE 新表
AS
SELECT * FROM 旧表

SQL INSERT INTO SELECT 语句

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

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

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

1
2
INSERT INTO table2
SELECT * FROM table1;

或者我们可以只复制指定的列插入到另一个已存在的表中：

1
2
3
4
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

例：

1
2
3
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;

select into from 和 insert into select 都是用来复制表

两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

select into from ：将查询出来的数据整理到一张新表中保存，表结构与查询结构一致。

1
select *（查询出来的结果） into newtable（新的表名）from where （后续条件）

即，查询出来结果—>复制一张同结构的空表—>将数据拷贝进去。

insert into select ：为已经存在的表批量添加新数据。

1
insert into  (准备好的表) select *（或者取用自己想要的结构）from 表名 where 各种条件

即，指定一张想要插入数据的表格—>对数据进行加工筛选—>填入一张准备好的表格。

嗯，可能理解的比较粗浅，希望有知识经验的大佬及时订正。

1
2
3
4
insert into scorebak select * from socre where neza='neza'   
-- 插入一行,要求表scorebak 必须存在
select * into scorebak from score  where neza='neza'  
-- 也是插入一行,要求表scorebak 不存在

SQL CREATE DATABASE 语句

CREATE DATABASE 语句用于创建数据库。

1
CREATE DATABASE dbname;

SQL CREATE TABLE 语句

CREATE TABLE 语句用于创建数据库中的表。

表由行和列组成，每个表都必须有个表名。

1
2
3
4
5
6
7
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

column_name 参数规定表中列的名称。

data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。

size 参数规定表中列的最大长度。

实例：

现在我们想要创建一个名为 “Persons” 的表，包含五列：PersonID、LastName、FirstName、Address 和 City。

我们使用下面的 CREATE TABLE 语句：

1
2
3
4
5
6
7
8
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

1.复制表结构及其数据：

1
create table table_name_new as select * from table_name_old

2.只复制表结构：

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

或者：

1
create table table_name_new like table_name_old

SQL 约束（Constraints）

SQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

在 SQL 中，我们有如下约束：

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

NOT NULL

确保列不能有 NULL 值。

1
2
3
4
5
6
CREATE TABLE Students (
    StudentID INT NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50),
    Age INT
);

添加 NOT NULL 约束

在一个已创建的表的 “Age” 字段中添加 NOT NULL 约束如下所示：

1
2
ALTER TABLE Persons 
MODIFY Age int NOT NULL;

删除 NOT NULL 约束

在一个已创建的表的 “Age” 字段中删除 NOT NULL 约束如下所示：

1
2
ALTER TABLE Persons
MODIFY Age int NULL;

UNIQUE

确保列中的所有值都是唯一的。

1
2
3
4
5
6
CREATE TABLE Employees (
    EmployeeID INT NOT NULL UNIQUE,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

UNIQUE 约束并在多列上定义

如需为 UNIQUE 约束指定名称，并在多个列上应用，可以使用以下语法：

MySQL / SQL Server / Oracle / MS Access：

1
2
3
4
5
6
7
8
CREATE TABLE Persons (
    P_Id INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(255),
    CONSTRAINT uc_PersonID UNIQUE (P_Id, LastName)
);

在 ALTER TABLE 时添加 UNIQUE 约束

如果表已存在，可以使用 ALTER TABLE 语句在指定列上添加 UNIQUE 约束。

在 “P_Id” 列上添加 UNIQUE 约束

MySQL / SQL Server / Oracle / MS Access：

1
2
ALTER TABLE Persons
ADD UNIQUE (P_Id);

命名 UNIQUE 约束并在多列上应用

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

1
2
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id, LastName);

删除 UNIQUE 约束

如果需要移除一个 UNIQUE 约束，可以使用以下 SQL 语句：

MySQL：

1
2
ALTER TABLE Persons
DROP INDEX uc_PersonID;

SQL Server / Oracle / MS Access：

1
2
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID;

PRIMARY KEY

唯一标识表中的每一行记录。PRIMARY KEY 约束是 NOT NULL 和 UNIQUE 的结合。

1
2
3
4
5
CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    OrderNumber INT NOT NULL,
    OrderDate DATE NOT NULL
);

如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

1
2
3
4
5
6
7
8
9
CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

注释：在上面的实例中，只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的。

已有表的创建于撤销于UNIQUE类似

（撤销PRIMARY KEY约束时，不论约束条件为一列还是多列，对于MySQL，撤销都是

1
2
ALTER TABLE Persons
DROP PRIMARY KEY

由于PRIMARY KEY唯一性，MYSQL处理办法简单。

但对于 SQL Server / Oracle / MS Access， 一个列

1
2
ALTER TABLE Persons
DROP CONSTRAINT P_Id

若起约束名，也可如下多个列

1
2
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

）

FOREIGN KEY

确保一个表中的值匹配另一个表中的值，从而建立两表之间的关系。

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

1
2
3
4
5
6
CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    OrderNumber INT NOT NULL,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

“Persons” 表：

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

“Orders” 表：

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1

请注意，“Orders” 表中的 “P_Id” 列指向 “Persons” 表中的 “P_Id” 列。

“Persons” 表中的 “P_Id” 列是 “Persons” 表中的 PRIMARY KEY。

“Orders” 表中的 “P_Id” 列是 “Orders” 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的行为。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

（注意是谁指向谁！！！）

下面的 SQL 在 “Orders” 表创建时在 “P_Id” 列上创建 FOREIGN KEY 约束：

MySQL：

1
2
3
4
5
6
7
8
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

SQL Server / Oracle / MS Access：

1
2
3
4
5
6
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

1
2
3
4
5
6
7
8
9
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

表创建后的增加与撤销与上文类似

CHECK

确保列中的值满足特定的条件。

1
2
3
4
5
CREATE TABLE Products (
    ProductID INT NOT NULL PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) CHECK (Price >= 0)
);

如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

1
2
3
4
5
6
7
8
9
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

表创建后的增加与撤销与上文类似

DEFAULT

为列设置默认值。

1
2
3
4
5
6
CREATE TABLE Customers (
    CustomerID INT NOT NULL PRIMARY KEY,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50),
    JoinDate DATE DEFAULT GETDATE()
);

INDEX

用于快速访问数据库表中的数据。

1
CREATE INDEX idx_lastname ON Employees (LastName);

综合示例：

1
2
3
4
5
6
7
8
CREATE TABLE Students (
    StudentID INT NOT NULL PRIMARY KEY,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 18),
    Email VARCHAR(100) UNIQUE,
    EnrollmentDate DATE DEFAULT GETDATE()
);

foreign key用法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table if not exists per(
  id bigint auto_increment comment '主键',
  name varchar(20) not null comment '人员姓名',
  work_id bigint not null comment '工作id',
  create_time date default '2021-04-02',
  primary key(id),
  foreign key(work_id) references work(id)
)

create table if not exists work(
  id bigint auto_increment comment '主键',
  name varchar(20) not null comment '工作名称',
  create_time date default '2021-04-02',
  primary key(id)
)

SQL CREATE INDEX 语句

您可以在表中创建索引，以便更加快速高效地查询数据。

用户无法看到索引，它们只能被用来加速搜索/查询。

注释：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

SQL CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值：

1
2
CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX 语法

在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。Creates a unique index on a table. Duplicate values are not allowed:

1
2
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

注释：用于创建索引的语法在不同的数据库中不一样。因此，检查您的数据库中创建索引的语法。

CREATE INDEX 实例

如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

1
2
CREATE INDEX PIndex
ON Persons (LastName, FirstName)

SQL 撤销索引、撤销表以及撤销数据库

DROP INDEX 语句

索引是一种优化数据库查询性能的结构，但有时候可能需要删除某个索引，例如当索引不再需要或需要替换为新的索引时。

DROP INDEX 语句用于删除表中的索引。

1
2
DROP INDEX [IF EXISTS] index_name
ON TABLE_NAME;
  • DROP INDEX：表示要删除索引的操作。
  • IF EXISTS：是一个可选的子句，用于检查索引是否存在。如果存在，就执行删除操作；如果不存在，不会报错。
  • index_name：要删除的索引的名称。
  • ON table_name：指定包含要删除索引的表的名称。

DROP TABLE 语句

1
DROP TABLE [IF EXISTS] TABLE_NAME;

执行DROP TABLE将永久删除表和其所有数据。在执行此类操作之前，请确保您已备份重要的数据，并且您有删除表的权限

TRUNCATE TABLE 语句

如果我们仅仅需要删除表内的数据，但并不删除表本身，那么我们该如何做呢？

在 SQL 中，TRUNCATE TABLE语句用于快速删除表中的所有数据，但保留表的结构（列、约束等），与 DELETE 语句相比，TRUNCATE TABLE 通常更快，因为它是通过删除表中的所有行而不是逐行删除实现的。

1
TRUNCATE TABLE TABLE_NAME;

SQL ALTER TABLE 语句

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

SQL AUTO INCREMENT 字段

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

我们通常希望在每次插入新记录时，自动地创建主键字段的值。

我们可以在表中创建一个 auto-increment 字段。

用于 MySQL 的语法

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：

1
2
3
4
5
6
7
8
9
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)
)

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：

1
ALTER TABLE Persons AUTO_INCREMENT=100

要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）：

1
2
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

上面的 SQL 语句会在 “Persons” 表中插入一条新记录。“ID” 列会被赋予一个唯一的值。“FirstName” 列会被设置为 “Lars”，“LastName” 列会被设置为 “Monsen”。

用于 SQL Server 的语法

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：

1
2
3
4
5
6
7
8
CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MS SQL Server 使用 IDENTITY 关键字来执行 auto-increment 任务。

在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1。

提示：要规定 “ID” 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)。

要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）：

1
2
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

上面的 SQL 语句会在 “Persons” 表中插入一条新记录。“ID” 列会被赋予一个唯一的值。“FirstName” 列会被设置为 “Lars”，“LastName” 列会被设置为 “Monsen”。

SQL 视图（Views）

SQL-sever数据类型

String 类型：

数据类型 描述 存储
char(n) 固定长度的字符串。最多 8,000 个字符。 Defined width
varchar(n) 可变长度的字符串。最多 8,000 个字符。 2 bytes + number of chars
varchar(max) 可变长度的字符串。最多 1,073,741,824 个字符。 2 bytes + number of chars
text 可变长度的字符串。最多 2GB 文本数据。 4 bytes + number of chars
nchar 固定长度的 Unicode 字符串。最多 4,000 个字符。 Defined width x 2
nvarchar 可变长度的 Unicode 字符串。最多 4,000 个字符。
nvarchar(max) 可变长度的 Unicode 字符串。最多 536,870,912 个字符。
ntext 可变长度的 Unicode 字符串。最多 2GB 文本数据。
bit 允许 0、1 或 NULL
binary(n) 固定长度的二进制字符串。最多 8,000 字节。
varbinary 可变长度的二进制字符串。最多 8,000 字节。
varbinary(max) 可变长度的二进制字符串。最多 2GB。
image 可变长度的二进制字符串。最多 2GB。

Number 类型：

数据类型 描述 存储
tinyint 允许从 0 到 255 的所有数字。 1 字节
smallint 允许介于 -32,768 与 32,767 的所有数字。 2 字节
int 允许介于 -2,147,483,648 与 2,147,483,647 的所有数字。 4 字节
bigint 允许介于 -9,223,372,036,854,775,808 与 9,223,372,036,854,775,807 之间的所有数字。 8 字节
decimal(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 5-17 字节
numeric(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 5-17 字节
smallmoney 介于 -214,748.3648 与 214,748.3647 之间的货币数据。 4 字节
money 介于 -922,337,203,685,477.5808 与 922,337,203,685,477.5807 之间的货币数据。 8 字节
float(n) 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。n 参数指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。 4 或 8 字节
real 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 4 字节

Date 类型：

数据类型 描述 存储
datetime 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。 8 字节
datetime2 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。 6-8 字节
smalldatetime 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。 4 字节
date 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 3 bytes
time 仅存储时间。精度为 100 纳秒。 3-5 字节
datetimeoffset 与 datetime2 相同，外加时区偏移。 8-10 字节
timestamp 存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 值基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。

SQL 函数

SQL GROUP BY 语句

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

SQL GROUP BY 语法

1
2
3
4
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

下面的 SQL 语句统计有记录的网站的记录数量：

实例

1
2
3
4
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;

SQL HAVING 子句

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

HAVING 子句可以让我们筛选分组后的各组数据。

1
2
3
4
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

现在我们想要查找总访问量大于 200 的网站，并且 alexa 排名小于 200。

我们在 SQL 语句中增加一个普通的 WHERE 子句：

1
2
3
4
5
SELECT Websites.name, SUM(access_log.count) AS nums 
FROM Websites INNER JOIN access_log ON Websites.id=access_log.site_id 
WHERE Websites.alexa < 200  
GROUP BY Websites.name 
HAVING SUM(access_log.count) > 200;

where 和having之后都是筛选条件，但是有区别的：

1.where在group by前， having在group by 之后

2.聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后

SQL EXISTS 运算符

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

1
2
3
4
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

现在我们想要查找总访问量大于 200 的网站是否存在:

1
2
3
4
5
6
7
8
select W.name,W.url
from Websites as w
where exists
(select 1
FROM access_log
where access_log.site_id = w.id
group by access.site_id
having sum(access_log.count) > 200)

SQL Aggregate 函数

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

有用的 Aggregate 函数：

AVG() - 返回平均值

COUNT() - 返回行数

FIRST() - 返回第一个记录的值

LAST() - 返回最后一个记录的值

MAX() - 返回最大值

MIN() - 返回最小值

SUM() - 返回总和

SQL Scalar 函数

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

有用的 Scalar 函数：

UCASE() - 将某个字段转换为大写

LCASE() - 将某个字段转换为小写

MID() - 从某个文本字段提取字符，MySql 中使用

SubString(字段，1，end) - 从某个文本字段提取字符

LEN() - 返回某个文本字段的长度

ROUND() - 对某个数值字段进行指定小数位数的四舍五入

NOW() - 返回当前的系统日期和时间

FORMAT() - 格式化某个字段的显示方式