SQL数据库语句

---

讲义

---

一、insert into select 和select into from 的区别

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

二、 正则表达式REGEXP

三、 join

在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行

得到的结果数：

inner join <= min(left join, right join)
full join >= max(left join, right join)
当 inner join < min(left join, right join) 时， full join > max(left join, right join)

3.1 inner join

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

在使用 join 时，on 和 where 条件的区别如下：

1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
注释：INNER JOIN 与 JOIN 是相同的。
详细区别 点这里！

---

join

A inner join B 取交集。

A left join B 取 A 全部，B 没有对应的值为 null。

A right join B 取 B 全部 A 没有对应的值为 null。

A full outer join B 取并集，彼此没有对应的值为 null。

四、union

select country from websites union select country from apps;

--连接两个表的查询结果集，重复的不显示

select country from websites union all select country from apps order by country;

--连接俩个个表的查询结果集，显示重复

select country,name from websites where country = 'CN' union all 

select country,app_name from apps where country='CN' order by name; 

--通过where条件查询的结果，连接连个表的结果集，并根据名字排序。

注释：UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！
使用UNION命令时需要注意，只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序！绝对不能写两个ORDER BY命令。

另外，在使用ORDER BY排序时，注意两个结果的别名保持一致，使用别名排序很方便。当然也可以使用列数。

ORDER BY 除了可以对指定的字段进行排序，还可以使用函数进行排序:

order by abs(a);
ORDER BY 只能当前 SQL 查询结果进行排序，如要对 union all 出来的结果进行排序，需要先做集合。

select aa.* from 
(select country,name from websites where country = 'CN'
union all select country,app_name from apps where country='CN' ) aa
order by aa.name;

五、复合语句

5.1 SELECT INTO 语句

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

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

CREATE TABLE 新表
AS
SELECT * FROM 旧表 

创建 Websites 的备份复件：

SELECT *
INTO WebsitesBackup2016
FROM Websites;
只复制一些列插入到新表中：

SELECT name, url
INTO WebsitesBackup2016
FROM Websites;
只复制中国的网站插入到新表中：

SELECT *
INTO WebsitesBackup2016
FROM Websites
WHERE country='CN';
复制多个表中的数据插入到新表中：

SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;
提示：SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可：

SELECT *
INTO newtable
FROM table1
WHERE 1=0;

5.2 INSERT INTO SELECT 语句

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

INSERT INTO table2
SELECT * FROM table1;

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

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

六、SQL约束

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,
....
);

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

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

6.1 Unique约束

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

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

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

6.2 PRIMARY KEY 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

6.3 FOREIGN KEY 约束

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

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

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

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

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

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

• MYSQL

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)
)

6.4 SQL CHECK 约束

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。
下面的 SQL 在 “Persons” 表创建时在 “P_Id” 列上创建 CHECK 约束。CHECK 约束规定 “P_Id” 列必须只包含大于 0 的整数。

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

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')
)

当表已被创建时，如需在 “P_Id” 列创建 CHECK 约束，请使用下面的 SQL：

ALTER TABLE Persons
ADD CHECK (P_Id>0)

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

撤销约束
SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

MySQL：

ALTER TABLE Persons
DROP CHECK chk_Person

MySql 中如何删除未命名的外键？

删除外键需要知道外键的名称，如果创建时没有设置名称则会自动生成一个，你需要获取改外键的信息。

使用以下命令获取外键信息：

SELECT
  constraint_name
FROM
  information_schema.REFERENTIAL_CONSTRAINTS
WHERE
  constraint_schema = <'db_name'> AND table_name = <'table_name'>;

SELECT *
FROM
  information_schema.KEY_COLUMN_USAGE
WHERE
  constraint_schema = <'db_name'> AND table_name = <'table_name'> AND   
  referenced_table_name IS NOT NULL;

可以使用以下命令来删除外键：

ALTER TABLE <table_name> DROP INDEX <fk_name>;

6.5 SQL DEFAULT 约束

DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。
下面的 SQL 在 “Persons” 表创建时在 “City” 列上创建 DEFAULT 约束：

My SQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
)

当表已被创建时，如需在 “City” 列创建 DEFAULT 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

如需撤销 DEFAULT 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE Persons
ALTER City DROP DEFAULT

七、CREATE INDEX 语句

CREATE INDEX 语句用于在表中创建索引。

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。
您可以在表中创建索引，以便更加快速高效地查询数据。

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

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

SQL CREATE INDEX 语法

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

CREATE INDEX index_name
ON table_name (column_name)

在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

下面的 SQL 语句在 “Persons” 表的 “LastName” 列上创建一个名为 “PIndex” 的索引：

CREATE INDEX PIndex
ON Persons (LastName)

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

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

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

用于 MySQL 的 DROP INDEX 语法：

ALTER TABLE table_name DROP INDEX index_name

DROP TABLE 语句用于删除表。

DROP TABLE table_name

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

DROP DATABASE database_name

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

请使用 TRUNCATE TABLE 语句：

TRUNCATE TABLE table_name

九、ALTER TABLE 语句

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。
SQL ALTER TABLE 语法
如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name
ADD column_name datatype

如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

ALTER TABLE table_name
DROP COLUMN column_name

My SQL / Oracle：

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Oracle 10G 之后版本:

ALTER TABLE table_name
MODIFY column_name datatype;

详情请点这里！！！！！

10、AUTO INCREMENT 字段

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

我们可以在表中创建一个 auto-increment 字段
下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 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)
)

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

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

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

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

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

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

十一、视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

您可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

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

注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。Northwind 样本数据库的另一个视图会计算在 1997 年每个种类的销售总数。请注意，这个视图会从另一个名为 “Product Sales for 1997” 的视图那里选取数据：

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

更新视图

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

• schema_name: 视图所属架构的名称。
• view_name: 要更改的视图。
• column: 将成为指定视图的一部分的一个或多个列的名称（以逗号分隔）。
  SQL DROP VIEW 语法

DROP VIEW view_name

十二、SQL Date 函数

见链接里面就行

十三、 NULL类型

这里要注意：
我们必须使用 IS NULL 和 IS NOT NULL 操作符。
数据库建表的时候默认是 NULL，但在工作中一般建表的时候都会禁止使用 NULL 的！

NULL 表示的是什么都没有，它与空字符串、0 这些是不等价的，是不能用于比较的！ 如： = NULL 、 NULL = ‘’ 得到的结果为 false，判断 NULL 必须使用 IS NULL 或 IS NOT NULL 进行判断。

为什么工作中不使用 NULL？

不利于代码的可读性和可维护性，特别是强类型语言，查询 INT 值，结果得到一个 NULL，程序可能会奔溃…如果要兼容这些情况程序往往需要多做很多操作来兜底
若所在列存在 NULL 值，会影响 count()、 != 、 NULL + 1 等查询、统计、运算情景的结果

--如果alexa列为null值，则赋予0，否则，取原值
select id,name,url,ifnull(alexa,0)from websites;
select id,name,url,COALESCE(alexa,0) from websites;

十四、数据类型

十五、SQL函数

15.1 AVG（）求取平均值

SELECT AVG(column_name) FROM table_name

15.2 COUNT() 函数

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name;

count (表达式）--分组里非空记录数
count (表达式）--分组里非空记录数
count(*)--所有记录
count(1)--所有记录
count(case job = 'CLERK' then 2 end )--CLERK 人数
count(comm)--有奖金的人数
count(distinct job)--distinct(去重），共有多少种工作
-- 实例：以EMP表为例

select deptno,
count(1) 总人数,
count(case when job ='SALESMAN' then '1' end) 销售人数,
count(case when job ='MANAGER' then '1' end) 主管人数
from emp
group by deptno;--如果不group，会认为所有数据是一组，返回一个数据

15.3 FIRST() 函数

FIRST() 函数返回指定的列中第一个记录的值。

SQL FIRST() 语法

SELECT FIRST(column_name) FROM table_name;

15.4 SQL LAST() 语法

SELECT LAST(column_name) FROM table_name;

注释：只有 MS Access 支持 LAST() 函数。

15.5 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;

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

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

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之后

15.6 UCASE() 函数

UCASE() 函数把字段的值转换为大写。
SQL UCASE() 语法

SELECT UCASE(column_name) FROM table_name;

用于 SQL Server 的语法

SELECT UPPER(column_name) FROM table_name;

15.7 LCASE() 函数

LCASE() 函数把字段的值转换为小写。

SQL LCASE() 语法

SELECT LCASE(column_name) FROM table_name;

15.8 MID() 函数

MID() 函数用于从文本字段中提取字符。

SQL MID() 语法

SELECT MID(column_name,start[,length]) FROM table_name;

Oracle 中没有 MID 函数，有 substr 函数有类似功能:

select substr(("列名"，a,b) from <table_name>;

实例：

SELECT substr(name,1,4) AS ShortTitle
FROM Websites;

15.9 LEN() 函数

LEN() 函数返回文本字段中值的长度。

SQL LEN() 语法

SELECT LEN(column_name) FROM table_name;

MySQL 中函数为 LENGTH():

SELECT LENGTH(column_name) FROM table_name;

15.10 ROUND() 函数

ROUND() 函数用于把数值字段舍入为指定的小数位数。

SQL ROUND() 语法

SELECT ROUND(column_name,decimals) FROM TABLE_NAME;

参数 描述
column_name 必需。要舍入的字段。
decimals 可选。规定要返回的小数位数。
ROUND(X,D)： 返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

15.11 NOW() 函数

NOW() 函数返回当前系统的日期和时间。

SQL NOW() 语法
SELECT NOW() FROM table_name;

15.12 FORMAT() 函数

FORMAT() 函数用于对字段的显示进行格式化。

SQL FORMAT() 语法

SELECT FORMAT(column_name,format) FROM table_name;

下面的 SQL 语句从 “Websites” 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期：

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;