SQL常用实列

SQL UNION 实例

表中选取所有不同的country（只有不同的值）：

select country from webs
union
select country from apps
order by country；

SQL UNION ALL 实例

表中选取所有的country（也有重复的值）：

select country from webs
union all
select country from apps
order by country；

SQL SELECT INTO 实例

复制多个表中的数据插入到新表中：

select webs.name,access.count,access.date
into web2016
from webs
left join access
on webs.id=access.id;

SQL 约束

create table if not exsits 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 exsits work(
  id bigint auto_increment comment '主键',
  name varchar(20) not null comment '工作名称',
  create_time date default '2021-04-02',
  primary key(id)
)

SQL SELECT TOP PERCENT 实例

表中选取前面百分之 50 的记录：

select top 50 percent *from Webs

MySQL SELECT LIMIT 实例

表中选取头两条记录：

select * from Webs limit 2;

CREATE INDEX 实例

create index PIndex 
on Persons(LastName,FirstName)

TRUNCATE TABLE 语句

仅仅需要删除表内的数据，但并不删除表本身:

truncate table  Webs

SQL ALTER TABLE 实例

“Persons” 表中添加一个名为 “DateOfBirth” 的列:

ALTER TABLE Persons
ADD DateOfBirth date

DROP COLUMN 实例

删除 “Person” 表中的 “DateOfBirth” 列:

alter table persons
drop column DateOfBirth

SQL CREATE VIEW 实例⭐

样本数据库 Northwind 拥有一些被默认安装的视图。

视图 “Current Product List” 会从 “Products” 表列出所有正在使用的产品（未停产的产品）。这个视图使用下面的 SQL 创建：

create view [Current Product  List] AS
select ProductID,ProductName
from Products
where Discontinued=No

select * from [Current Product List]

SQL 更新视图

视图添加 “Category” 列:

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

SQL 撤销视图

DROP VIEW 命令来删除视图:

DROP VIEW [Current Product List]

SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
YEAR - 格式：YYYY 或 YY

SQL 通用数据类型

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

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() - 格式化某个字段的显示方式

GROUP BY 简单应用⭐

统计 access_log 各个 site_id 的访问量：

select site_id,sum(access_log.count) AS nums
from access_logs
group by site_id;

SQL GROUP BY 多表连接

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

select Webs.name,count(access_log.aid) AS nums
from access_log
left join Webs
ON access_log.site_id=Webs.id
group by Webs.name;

嵌套查询

SQL HAVING 实例⭐

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。

想要查找总访问量大于 200 的网站:

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

SQL EXISTS 实例⭐

想要查找总访问量(count 字段)大于 200 的网站是否存在：

SELECT Webs.name, Webs.url 
FROM Webs
WHERE EXISTS 
(SELECT count FROM access_log WHERE Webs.id = access_log.site_id AND count > 200);

SQL Server 大小写用 UPPER() 和 LOWER()

大写：

SELECT UPPER(name) AS site_title, url FROM Webs;
小写：

SELECT LOWER (name) AS site_title, url FROM Webs;

SQL MID() 实例

SELECT MID(name,1,4) AS ShortTitle
FROM Webs;

SQL LEN() 实例

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

SELECT name, LENGTH(url) as LengthOfURL
FROM Websites;

SQL ROUND() 实例

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

select Round(1.298,1);

mysql> SELECT ROUND(1.298, 0);
        -> 1

SQL NOW() 实例

表中选取 name，url，及当天日期：

select name,url,Now() AS date
from Webs;

SQL FORMAT() 实例

用于对字段的显示进行格式化。

select name,url,Date_format(Now(),'%y-%m-%d')
AS date
from Webs