SQL基础语法

DevLGQ

DataBaseMySQL

发布于：2019年10月6日

次浏览

字数：4.1k字

时长：18分钟

SQL基础语法

SQL基础语法学习笔记。SQL对大小写敏感。

DDL

Data Definition Language，数据定义语言。

create database 创建新数据库
alter database 修改数据库
create table 创建新表
alter table 修改表
drop table 删除表
create index 创建索引
drop index 删除索引

数据库的基本操作

-- 创建数据库
create database database_name

-- 创建一张表
create table Persons
(
    Id_P int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
)

-- 删除索引，表以及数据库
-- 删除table_name的index_name索引
alter table table_name drop index index_name
-- 删除表
drop table table_name
-- 删除数据库
drop database database_name
-- 删除表的数据，不删除表本身
truncate table table_name

-- 修改表
-- 已存在的表添加列
-- 添加一个 Birthday 类型是 date 的列
alter table Persons
add Birthday date
-- 修改列的数据类型
alter table Persons
alter column Birthday year
-- 删除列
alter table Person
drop column Birthday


-- 自增 auto_increment 默认开始值是1，递增1
CREATE TABLE Persons
(
    P_Id int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (P_Id)
)

数据类型	描述
integer(size) int(size) smallint(size) tinyint(size)	仅容纳整数。在括号内规定最大位数。
decimal(size, d) numberic(size, d)	容纳带有小数的数字。size规定数字的最大位数。d规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串。在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母，数字以及特殊字符）。在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

SQL约束

not null 不接受 null 值
unique
primary key
foreign key
check
default

unique

唯一键约束，可以多个。

-- unique 唯一键约束
-- MySQL
CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    UNIQUE (Id_P)
)
-- 命名 unique 和 多列约束
CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)
-- 添加唯一键约束
alter table Persons
add unique (Id_P)
alter table Persons
add constraint uc_PersonID unique (Id_P)
# 删除唯一键约束
alter table Persons
drop index uc_PersonID

primary key

主键约束，唯一一个，不能为 null，每个表都有，如果不指定，默认自动生成

-- 指定主键
CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (Id_P)
)
-- 给主键命名并添加多列为主键
CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)
-- 添加主键，添加的键必须是 not null 的
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
# 移除主键
ALTER TABLE Persons
DROP PRIMARY KEY

foreign key

外键约束。一个表的 foreign key 指向另外一个表的 primary key。用于预防破坏表之间的连接动作，也能防止非法数据插入外键列，因为它必须指向的那个表中的值之一。

-- Orders表的Id_P与Persons的Id_P建立外键约束
CREATE TABLE Orders
(
    Id_O int NOT NULL,
    OrderNo int NOT NULL,
    Id_P int,
    PRIMARY KEY (Id_O),
    FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
-- 给外键约束起别名
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)
)

-- 给已存在的表添加外键约束
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders 
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

-- 删除外键约束
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

check

用于限制列中值的范围。

-- 对 Id_P 进行约束，要大于0
CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    CHECK (Id_P>0)
)
-- 起别名，并且为多个列添加约束
CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)

-- 为已经存在的表添加 check约束
ALTER TABLE Persons
ADD CHECK (Id_P>0)

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

-- 删除外键约束
ALTER TABLE Persons
DROP CHECK chk_Person

default

如果插入时没有值，就向列中插入默认值。

-- 为 city 列添加默认值
CREATE TABLE Persons
(
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
)
-- 使用函数插入默认值
CREATE TABLE Orders
(
    Id_O int NOT NULL,
    OrderNo int NOT NULL,
    Id_P int,
    OrderDate date DEFAULT GETDATE()
)
-- 为已经存在的表添加默认值
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
-- 删除默认值
ALTER TABLE Persons
ALTER City DROP DEFAULT

索引

为列添加索引，使用恰当的话可以加快查询速度。

更新一个包含索引的表需要比更新一个没有索引的表需要更多的时间，所以一般做法时仅仅在常常被搜索到的列上面创建索引。

-- 为 table_name 的 column_name 列添加 index_name 普通索引，允许使用重复值
creata index PersonIndex
on Person (LastName) 
-- 创建唯一索引。意味着两个
create unique index index_name
on table_name (column_name)
-- 以降序索引某个列的值
create index PersonIndex
on Person (LastName desc)
-- 多个列添加索引
create index PersonIndex
on Person (LastName, FirstName)

SQL View

基于SQL语句结果的可视化的表。

-- 创建 Current Product List 视图，列出 Products 中正在使用的产品
create view [Current Product List] as
select ProductID,ProductName
from Products
where Discontinued=No

-- 查询视图
select * from [Current Product List]

-- 创建一个 Products 中所有单位价格大于平均价格的产品
create view [Products Above Average Price] as
select ProductName, UnitPrice
from Products
where UnitPrice > (select avg(UnitPrice) from Products)
-- 之后要查询直接查询视图即可
select * from [Products Above Average Price]

-- 更新视图
-- 给视图添加多一列数据
create view [Current Product List] as
select ProductID,ProductName,Category
from Products
where Discontinued=No

-- 删除视图
drop view view_name

DML

Data Manipulation Language，数据操作语言。

select 查询数据
update 更新数据
delete 删除数据
insert into 插入数据

基本增删改查

-- 查询table下的所有行数据
select * from tableName

-- distinct
-- 查询student表中所有学生的名字并且去重
select distinct name from student

-- where 条件
-- = 等于 <> != 不等于 
-- > 大于 < 小于 
-- >= 大于等于  <= 小于等于
-- between 范围内 like 搜索某种模式
select * from Persons where City='GuangZhou'
select * from Persons where Year>1965

-- and 与    or 或
select * from Persons where FirstName='Thomas' and LastName='Carter'
select * from Persons where FirstName='Thomas' or LastName='Carter'
-- 结合使用
select * from Persons where (FirstName='Thomas' or LastName='William') and LastName='Carter'

-- order by 对结果集进行排序，默认升序
-- 按公司名称排序
select Company, OrderNumber from Orders order by Company
-- 按公司名称排序，再以数字顺序排序（相同公司名称）
select Company, OrderNumber from Orders order by Company,OrderNumber
-- 降序
select Company, OrderNumber from Orders order by Company desc
-- 公司名降序，编号升序
select Company, OrderNumber from Orders order by Company desc,OrderNumber asc

-- insert into 插入新的行
insert into Persons values ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
insert into Persons(LastName, Address) values ('Bill', 'Beijing')

-- update 修改行数据
-- 更新一行中的某一列
update Person set FirstName = 'Fred' where LastName = 'Wilson'
-- 更新一行中的多列
update Person set Address='Zhongshan 23', City='Nanjing' where LastName = 'Wilson'

-- delete 删除某一行
delete from Person where LastName = 'Wilson'
-- 删除所有行
delete from table_name

过滤相关语法

-- limit 限定返回的行数
select *
from Persons
limit 5

-- like 搜索列中指定模式
select * from Persons
where City like 'N%'

-- 通配符
-- % 代表零个或多个字符
-- _ 仅代表一个字符
-- [charlist] 字符中任何单一字符
-- [^charlist] [!charlist] 不在列表中的任意字符

-- in 规定多个值
select * from Persons
where LastName in ('Adams','Carter')

-- between 介于两者之间的数据范围
-- 在 Adams 和 Carter 之间的人，不包括 Carter
select * FROM Persons
where LastName
between 'Adams' AND 'Carter'
-- 范围之外
select * FROM Persons
where LastName
not 'Adams' AND 'Carter'

-- alias 别名
-- 使用表名称别名
select po.OrderID, p.LastName, p.FirstName
from Persons as p, Product_Orders as po
where p.LastName='Adams' and p.FirstName='John'
-- 使用列名别名(查询结果
select LastName as Family, FirstName as Name
from Persons

表操作相关

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

-- 带 where 的 select into
select LastName,Firstname
into Persons_backup
from Persons
where City='Beijing'

-- 带 join 的 select into
select Persons.LastName,Orders.OrderNo
into Persons_Order_Backup
from Persons
inner JOIN Orders
on Persons.Id_P=Orders.Id_P

多表查询

根据两张或多个表中列的关系进行数据查询。

-- 引用两个表的方式
select Persons.LastName, Persons.FirstName, Orders.OrderNo
from Persons, Orders
where Persons.Id_P = Orders.Id_P

-- 使用 join 的方式
-- inner JOIN 内连接
select Persons.LastName, Persons.FirstName, Orders.OrderNo
from Persons
inner JOIN Orders
on Persons.Id_P = Orders.Id_P
order BY Persons.LastName

不同的 SQL Join

Join: 如果表中至少有一个匹配的，则返回行
Left Join: 即使右表没有匹配，也从左表返回所有行
Right Join: 即使左表没有匹配，也从右表返回所有行
Full Join: 只要其中一个表存在匹配，就返回行

inner join

内连接，和 join 是一样的，在表中存在至少一个匹配时才返回行，否则不返回。

select Persons.LastName, Persons.FirstName, Orders.OrderNo
from Persons
inner join Orders
on Persons.Id_P=Orders.Id_P
order by Persons.LastName

inner join 关键字在表中存在至少一个匹配时返回行。如果 “Persons” 中的行在 “Orders” 中没有匹配，就不会列出这些行。

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

-- 例子
select Persons.LastName, Persons.FirstName, Orders.OrderNo
from Persons
left join Orders
on Persons.Id_P=Orders.Id_P
order by Persons.LastName

left join 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

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

-- 例子
select Persons.LastName, Persons.FirstName, Orders.OrderNo
from Persons
right join Orders
on Persons.Id_P=Orders.Id_P
order by Persons.LastName

right join 关键字会从右表 (Orders) 那里返回所有的行，即使在左表 (Persons) 中没有匹配的行。

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 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 “Persons” 中的行在表 “Orders” 中没有匹配，或者如果 “Orders” 中的行在表 “Persons” 中没有匹配，这些行同样也会列出。

union

用于合并两个或者多个 select 语句的结果集。

注意：union内部的 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 select 语句中的列顺序必须相同。

-- 返回的结果集会进行去重
select column_name(s) from table_name1
union
select column_name(s) from table_name2
-- 返回结果集不进行去重
select column_name(s) FROM table_name1
union all
select column_name(s) FROM table_name2

SQL的NULL值

默认，表的默认值可以存放NULL值。NULL是未知或者不适用的值的占位符。

注意，无法比较NULL和0，是不等价的。

-- 选出NULL值
select LastName,FirstName,Address from Persons
where Address is null
-- 选出不是NULL值
select LastName,FirstName,Address from Persons
where Address is not null

MySQL数据类型

MySQL中三种数据类型，文本，数字和日期/时间类型。

文本类型：

数据类型	描述
char(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
varchar(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。如果值的长度大于 255，则被转换为 TEXT 类型。
tinytext	存放最大长度为 255 个字符的字符串。
text	存放最大长度为 65_535 个字符的字符串。
blob	用于 BLOBs (Binary Large Objects)。存放最多 65_535 字节的数据。
mediumtext	存放最大长度为 16_777_215 个字符的字符串。
mediumblob	用于 BLOBs (Binary Large Objects)。存放最多 16_777_215 字节的数据。
longtext	存放最大长度为 4_294_967_295 个字符的字符串。
longblob	用于 BLOBs (Binary Large Objects)。存放最多 4_294_967_295 字节的数据。
enum(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。这些值是按照你输入的顺序存储的。
set	与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

数字类型：

数据类型	描述
tinyint(size)	-128 到 127 常规。0 到 255 无符号（unsigned）。在括号中指定最大位数。
smallint(size)	-32_768 到 32_767 常规。0 到 65_535 无符号（unsigned）。在括号中指定最大位数。
mediumint(size)	-8388608 到 8388607 普通。0 到 16777215 无符号（unsigned）。在括号中指定最大位数。
int(size)	-2147483648 到 2147483647 常规。0 到 4294967295 无符号（unsigned）。在括号中指定最大位数。
bigint(size)	-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号（unsigned）。在括号中规定最大位数。
float(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
double(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
decimal(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。

日期/时间类型：

数据类型	描述
date()	日期。格式：YYYY-MM-DD。支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
datetime()	日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS。支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
timestamp()	时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS。支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
time()	时间。格式：HH:MM:SS。支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
year()	2 位或 4 位格式的年。4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

SQL函数

Date函数

函数	描述
now()	返回当前的日期和时间
curdate()	返回当前的日期
curtime()	返回当前的时间
date()	提取日期/时间表达式的日期部分
extract()	返回日期/时间按的单独部分
date_add()	给日期添加指定的时间间隔
date_sub()	给日期减去指定的时间间隔
datediff()	返回两个日期之间的天数
date_format()	用不同的格式显示日期/时间

date – 格式 YYYY-MM-DD
datetime – 格式 YYYY-MM-DD HH:MM:SS
timestamp – 格式 YYYY-MM-DD HH:MM:SS
year – 格式 YYYY 或 YY

NULL函数

对于是null的值，用null的话不利于计算。

-- 如果是null的话，就值为0
select ProductName,UnitPrice*(UnitsInStock+ifnull(UnitsOnOrder,0))
from Products
-- 或者
select ProductName,UnitPrice*(UnitsInStock+coalesce(UnitsOnOrder,0))
from Products

统计函数

-- avg() 计算平均值
-- 计算‘OrderPrice’的平均值
select avg(OrderPrice) as OrderAverage from Orders
-- 选出OrderPrice大于平均值的客户
select Customer from Orders where OrderPrice>(select avg(OrderPrice) from Orders)

-- count() 返回匹配条件的行数
select count(*) from table_name
-- 计算客户Carter的订单数
select count(Customer) as CustomerNilsen from Orders
where Customer='Carter'

-- first() 返回指定字段的第一个记录值
select first(OrderPrice) as FirstOrderPrice from Orders

-- last() 返回指定字段的最后一个记录值
select last(OrderPrice) as FirstOrderPrice from Orders

-- max() 返回结果集中的最大值
select max(OrderPrice) as FirstOrderPrice from Orders

-- min() 返回结果集中的最小值
select min(OrderPrice) as FirstOrderPrice from Orders

-- sum() 合计
select sum(OrderPrice) as FirstOrderPrice from Orders

-- group by 分组
-- 根据 Customer 进行分组，然后合计
select Customer,sum(OrderPrice) from Orders
group by Customer

-- having
-- 为了解决 where 无法和合计函数一起使用
select Customer,sum(OrderPrice) from Orders
group by Customer
having sum(OrderPrice)<2000

-- ucase() 把字段的值转换成大写
select ucase(LastName) as LastName,FirstName from Persons

-- lcase() 把字段的值转换成小写
select lcase(LastName) as LastName,FirstName from Persons

-- mid() 从文本中提取字符
-- 提取City的前3个字符
select mid(City,1,3) as SmallCity from Persons

-- len() 返回字段中值的长度
select len(City) as LengthOfCity from Persons

-- round() 把数值字段舍入为指定的小数位数
-- 把小数部分去掉
select ProductName, round(UnitPrice,0) as UnitPrice from Products

-- format() 对字段显示进行格式化
select ProductName, UnitPrice, format(now(),'YYYY-MM-DD') as PerDate
from Products