SQL学习记录

SQL 学习记录

数据库基础类型

SQL 中常用的数据类型

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

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 Server 使用下列数据类型在数据库中存储日期或日期/时间值
* DATE - 格式 YYYY-MM-DD
* DATETIME - 格式 YYYY-MM-DD HH:MM:SS
* SMALLDATETIME - 格式 YYYY-MM-DD HH:MM:SS
* TIMESTAMP - 格式 唯一的数字

MySQL 数据类型
在MySQL中，有三种主要的类型：文本、数字和日期/时间类型

Text类型

数据类型	描述
char(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多255个字符
varchar(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多255个字符。注释：如果值的长度大于255，则被转换为Text类型
tinytext	存放最大长度为255个字符的字符串
text	存放最大长度为65535【2^16】个字符的字符串
blob	用于blobs（Binary Large Objects）。存放最多65535【2^16】字节的数据
mediumtext	存放最大长度为16777215【2^24】个字符的字符串
mediumblob	用于blobs（Binary Large Objects）。存放最多16777215【2^24】字节的数据
longtext	存放最大长度为4294967295个字符的字符串
longblob	用于blobs（Binary Large Objects）。存放最多4294967295【2^32】字节的数据
enum(x,y,z,etc.)	允许你输入可能在的列表。可以在enum列表中列出最大65535个值。如果列表中不存在插入的值，则插入空值。注释：这些纸是爱面子输入的顺序存储的
set	于enum类似，set最多只能包含64个列表项，不过set可存储一个以上的值

Number类型：

数据类型	描述
tinyint(size)	-128到127常规。0到255无符号*。在括号中规定最大位数
smallint(size)	-32768【215-1】到-32767【215】常规。0到65535【2^16】无符号*。在括号中规定最大位数
mediumint(size)	-8388608【223】到-8388607【223-1】常规。0到16777215【2^24】无符号*。在括号中规定最大位数
int(size)	-2147483648【231】到2147483647【231-1】常规。0到4294967295【2^32】无符号*。在括号中规定最大位数
bigint(size)	-9223372036854775808【263】到9223372036854775807【263-1】常规。0到2^64无符号*。在括号中规定最大位数
float(size, d)	带有浮动小数点的小数字。在括号中规定最大位数。d规定小数点右侧的最大位数
double(size, d)	带有浮动小数点的大数字。在括号中规定最大位数。d规定小数点右侧的最大位数
decimal(size, d)	作为字符串存储的double类型，允许规定的小数点

*这些整数类型拥有额外的选项unsigned。通常，整数可以是负数或正数。如果添加unsigned属性，那么范围将从0开始，而不是负数。

Data类型：

数据类型	描述
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()	时间戳。使用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位格式的年。2位格式允许的值：70到69。4位格式的值：1901到2155

*即便datetime和timestamp返回相同的格式，它们的工作方式也不同，在insert或update查询中，timestamp自动把自身设置位当前的日期和时间。timestamp也接受不同的格式。比如YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD

1.SQL 基础教程

1.2 SQL 语法（重要）

重要事项

SQL对大小写不敏感，即不区分大小写

SQL语句后面的分号？

1、MS SQL Server 不需要加分号
2、MySQL 需要加分号

SQL DML 和 DDL

DML：数据操作语言。DDL：数据定义语言。

SQL(结构化查询语言)是用于执行查询的语法。但是SQL语言也包含用于更新、插入和删除记录的语法。

查询和更新指令构成了SQL的DML部分：

* select- 从数据库表中获取数据
* update - 更新数据库表中的数据
* delete - 从数据库表中删除数据
* insert into - 向数据库表中插入数据

DDL 使我们有能力创建或删除表格。万平米也可以定义索引（键），规定表之间的链接，以及施加表间的约束。

SQL中的重要DDL 语句

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

1.3 select (查询)

用途
用于选取表中选取的数据，结果被存储在一个结果表中

语法

> select column(s) from table_name
or
> select * from table_name

结果集
大多数数据库软件系统都允许使用编程函数在结果集中进行导航

1.4 distinct (唯一)

用途

使用关键字distinct 用于返回唯一不同的值。distinct只能用在select语句中。

1、只对一列进行操作
2、对多列进行操作，应用到多个字段的时候，应用的范围是其后面的所有字段，distinct只能放在select后面，即放在所有字段的前面。
3、distinct对NULL是不进行过滤的，返沪的结果中是包含NULL值的
4、与ALL不能同时使用
5、distinctrow与distinct作用一样
6、distinct可以对*操作，即对所有列进行操作

语法

> select distinct column_name from table_name

1.5 where (选择)

用途

有条件地从表中选取数据，是一个约束声明，用于约束数据，在返回结果集之前起作用。

语法

> select column(s) from table_name where [condition]

运算符

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
between	在某个范围内
like	搜索某种模式

引号使用

使用单引号来环绕文本值。如果是数值，不能使用引号。

1.6 and & or

用途

用于基于一个以上的条件对记录进行过滤。和where关键字一块使用

语法

> select column(s) from table_name where [condition] and [condition]
> select column(s) from table_name where [condition] or [condition]

1.7 order by (排序)

用途

1、用于根据指定的列对结果集进行排序
2、默认按照升序对记录进行排序
3、进行倒序排序，那么使用关键字desc

语法

以单个列进行排序

> select column(s) from table_name order by column

以多个列进行排序，先按照column_1进行排序，再按照column_2进行排序

> select column(s) from table_name order by column_1, column_2

1.8 insert into (插入语句)

用途
向表格中插入新的行

语法

按照表结构进行赋值插入

> insert into table_name values (val_1, val_2, ...)

给指定列进行插入数据

> insert into table_name (column_1, column_2, ...) values (val_1, val_2, ...)

1.9 update (更新)

用途
用于修改表中的数据

语法

> update table_name set column_1=new_value_1, [column_2=new_value_2,] where [condition]

1.10 delete (删除)

用途
删除表中的行

语法

删除符合条件的记录

> delete from table_name where [condition]
# 删除表中的所有记录
> delete from table_name

2.SQL 高级教程

2.1 SQL Top

用途

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

语法

SQL Server 的语法

> select top number | percent column_nam(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.2 SQL LIKE 操作符

用途

用于在where子句中搜索列中的指定模式

语法

> select column_name(s) from table_name where column_name LIKE pattern

例子

我们希望从表中选取居住在以N开始的城市里的人的所有记录

> select * from Persons where City like 'N%'

注意：“%”可用于定义通配符

使用NOT关键字，从表中选取居住在不包括lon的人的所有记录

> select * from Persons where City not like '%lon%'

2.3 SQL 通配符

用途

通配符可以替代一个或多个字符
通配符必须与LIKE运算符一起使用

通配符列表

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符

2.4 SQL IN 操作符

用途

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

语法

> select column_name(s) from table_name where column_name IN (value1, value2, ...)

2.5 SQL BETWEEN 操作符

用途

BETWEEN...AND... 操作符在 WHERE 子句中使用，作用是选取介于两个值之间的数据范围。
这些值可以是数值、文本或者日期。

语法

> select column_name(s) from table_name where column_name between value1 and value2

注意：不同的数据库对BETWEEN...AND...操作符的处理方式不同，主要体现在对边界值的处理，有的包括边界值有的不包括边界值

2.6 SQL Alias 操作符

用途

可以为列名称和表名称指定别名

语法

表的语法

> select column_name(s) from table_name AS alias_name

列的SQL Alias 语法

> select column_name AS alias_name from table_name

2.7 SQL JOIN 操作符

用途

SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在在表中，每个主键的值都是唯一的。
目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起

语法

INNER JOIN

> select colcumn_name(s) from table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.colcumn_name

LEFT JOIN: 关键字会从左表那里返回所有的行，即使在右表中没有匹配的行

> select column_name(s) from table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name

RIGHT JOIN: 关键字会从右表那里返回所有的行，即使在左表中没有匹配的行

> select column_name(s) from table_name1 RIGHT JOIN table_name2 ON table_name1.column_name = table_nam2.column_name

在某些数据库中，RIGHT JOIN 成为 RIGHT OUTRER 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 称为 FULL OUTER JOIN

2.8 SQL UNION 和 UNION ALL 操作符

用途

UNION 操作符用于合并两个或多个SELECT 语句的结果集

UNION 内部的SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT 语句中的列的顺序必须相同

语法

UNION

> select column_name(s) from table_name1 UNION select column_name(s) from table_name2

默认地，UNION 操作符选取不同的值，如果允许重复的值，需要使用UNION ALL

UNION ALL

> select column_name(s) from table_name1 UNION ALL select column_name(s) from table_name2

UNION结果集中的列名总是等于UNION 中第一个SELECT 语句中的列名

2.9 SELECT INTO 语句

用途

从一个表中选取数据，然后把数据插入另一个表中。
用于创建表的备份复件或用于对记录进行存档。

语法

> select * into new_table_name [IN externaldatabase] from old_table_name

> select column_name(s) INTO new_table_name [IN externaldatabase] from old_table_name

IN 子句用于向另一个数据库中拷贝表

2.10 SQL 创建表 约束语法

NOT NULL

用途：

约束强制列不接受NULL值
约束强制字段始终包含值，

UNIQUE

用途

约束唯一标识数据库表中的每条记录。
约束为列或列集合提供唯一性的保证

PRIMARY KEY

用途

约束唯一标识数据库表中的每条记录
主键必须包含唯一的值
主键列不能保护NULL值
每个表都应该有一个主键，并且每个表只能有一个主键

FOREIGN KEY （外键）

用途

一个表中的FOREIGN KEY指向另一个表中的PRIMARY KEY
约束用于预防破坏表之间连接的动作
能防止非法数据插入外键列

CHECK

用途

用于限制列中的值的范围
如果对单个列定义CHECK约束，那么该列只允许特定的值

DEFAULT

用途

用于向列中插入默认值
如果没有规定其他的值，那么会将默认值添加到所有的新纪录

2.11 SQL CREATE INDEX 语句

用途

用于在表中创建索引；
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据

语法

> create index index_name on table_name (index_column_name)

> create unique index inde_name on table_name (index_column_name)

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

用途

通过使用DROP 语句，删除索引、表和数据库

删除index语句

MS Jet 语法

> DROP index index_name on table_name

MS Server 语法

> DROP index table_name.index_name

IBM DB 2 和 Oracle 语法

> DROP index index_name

MySQL 语法

> ALTER table table_name DROP index index_name

删除Table语句

> drop table table_name

删除数据库语句

>drop database 数据库名称

删除 truncate table 语句

> truncate table 表名称

仅仅除去表内数据，不删除表本身

2.13 ALTER TABLE 语句

用途

用于在已有的表中添加、修改和删除列

语法

表中添加列

> alter table table_name add column_name datatype

删除表中的列

> alter table table_name drop column column_name

改变表中列的数据类型

> alter table table_name alter column column_name datatype

2.14 auto-increment 字段

用途
每次插入新记录时，自动地创建主键字段的值

语法

MySQL语法

create table Persons(
P_Id int not null auto_increment,
LastName varchar(255),
FirstName varchar(255),
City varchar(255),
primary key (P_Id)
)

MySQL 使用auto_increment关键字执行auto-increment任务；
默认地，auto_increment的开始值时1，每条新纪录递增1；
如果想从其他值开始，使用下面语法：

> alter table Persons auto_increment=100

MySQL不能对单表设置自增步长，只能全局设置步长增长值：

> SET @@auto_increment_increment=10

要在“Persons”表中插入新纪录，我们不必为“P_Id”列规定值：

> insert into Persons (FirstName, LastName) values ('Bill', 'Gates')

用于SQL Server的语法

create table Persons(
P_Id int key identity,
LastName varchar(255),
FirstName varchar(255),
City varchar(255)
)

SQL Server使用identity关键字来执行auto-increment任务；
默认地，identity开始值是1，每条新纪录递增1。
如果设置起始值和增长步长，那么改为：

P_Id int key identity(20, 10)

2.15 View(视图)

定义
视图是基于SQL语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表，视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以想视图添加SQL函数、where以及join语句，我们叶可以提交数据，就像这些来自于某个单一的表。

注释：数据库的设计和结构不会受到视图中的函数、where或join语句的影响。

优点

1、视图隐藏了底层的表结构，简化了数据访问操作；
2、视图提供了一个统一访问数据的接口；
3、允许用户通过视图访问数据的安全机制，不授予用户直接访问底层表的权限；
4、加强了安全性，用户只能看到视图所显示的数据；
5、视图可以被嵌套，一个视图中可以嵌套另一个视图

语法

创建视图

> create view view_name as select column_name(s) from table_name where condition

注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用SQL语句来重建数据

更新视图

SQL create or replace view Syntax
> create or replace view view_name as select column_name(s) from table_name where condition

SQL 撤销视图

SQL drop view Syntax
> drop view view_name

2.16 SQL NULL值

用途
NULL值时遗漏的未知数据了；
默认地，表的列可以存放NULL值；

SQL NULL 值

1、如果表中的某个列时可选的，那么我们可以在不想该列添加值的情况下插入新纪录或更新已有的记录；
2、NULL值的处理方式与其他值不同
3、NULL用作未知的或不适用的值的占位符

注释：无法比较NULL和0，他们是不等价的

NULL值 操作
选取某列中的NULL值的记录

> select column(s) from table_name where column is NULL

选取某列中不带NULL值的记录

> select column(s) from table_name where column is not NULL

2.17 SQL NULL 函数

下面表

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	computer	699	25	15
2	printer	365	36
3	telephone	280	159	57

可以看出UnitsOnOrder是可以包含NULL值。

> select ProductName, UnitPrice*(UnitsInStock+UnitsOnOrder) from Products

如果UnitsOnOrder的值是NULL，那么结果是NULL

用于处理NULL值的函数：

SQL Server： isnull()
oracle: nvl()
MySQL: ifnull()/coalesce()

语法

SQL Server

> select ProductName, UnitPrice*(UnitsInStock+isnull(UnitsOnOrder, 0)) from Products

Oracle

> select ProductName, UnitPrice*(UnitsInStock+nvl(UnitsOnOrder, 0)) from Products

MySQL

> select ProductName, UnitPrice(UnitsInStock+ifnull(UnitsOnOrder, 0)) from Products
#or
> select ProductName, UnitPrice(UnitsInStock+coalesce(UnitsOnOrder, 0)) from Products

3 SQL 函数

3.1 avg() (平均值)

用途

返回数值列的平均值。不包括NULL值

语法

> select avg(column_name) from table_name

3.2 count() (计数)

用途
返回匹配记录的行数

语法

返回指定列的值的数目，NULL不计入

> select count(column_name) from table_name
# 返回表中的记录
> select count(*) from table_name
# 返回指定列的不同值的数目
> select count(distinct column_name) from table_name

3.3 first() (第一个记录)

用途

返回指定的字段中的第一个记录的值。可与order by一块使用

语法

> select first(column_name) from table_name

3.4 last() (最后一个记录)

用途

返回指定字段中的最后一个记录的值。可与order by一块使用

语法

> select last(column_name) from table_name

3.5 max() (最大值)

用途

返回一列中的最大值。NULL值不包括在计算中

语法

> select max(column_name) from table_name

3.6 min() (最小值)

用途
返回一列中的最小值。NULL值不包括在计算中

语法

> select min(column_name) from table_name

3.7 sum() (求和)

用途

返回数值列的总数

语法

> select sum(column_name) from table_name

3.8 group by (进行分组)

用途
用于结合合计函数，根据一个或多个列对结果集进行分组

语法

> select column_name, aggregate_function(column_name) from table_name where [condition] group by column_name

用法

1、在select指定的字段要么需要包含在Group by语句的后面，作为分组的依据；要么就要被包含在聚合函数中。
2、如果select后指定的字段没有在Group By里，也没有再聚合函数中，那么将会报错

3.9 having

用途
与合计函数一块使用，进行条件判断。这是由于where关键字不能与合计函数一块使用

语法

> select column_name, aggregate_function(column_name) from table_name where [condition] group by column_name having aggregate_function(column_name) operator value

where、 group by、having顺序

where > group by > having

3.10 ucase() (字段值转为大写)

用途
把字段的值转换为大写

语法

> select ucase(column_name) from table_name

3.11 lcase() (字段值转为小写)

用途
把字段的值转换为小写

语法

> select lcase(column_name) from table_name

3.12 mid() (文本字段提取字段)

用途
用于文本字段中提取字符

语法

> select mid(column_name, start)

3.13 len() (文本字段的长度)

用途

返回文本字段中值的长度

语法

> select len(column_name) from table_name

3.14 round() (数值字段小数位)

用途

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

语法

> select round(column_name, decimals) from table_name

3.15 now() (返回当前日期和时间)

用途

返回当前的时间和日期。
如果是在SQL Server数据库，使用getdate()获得当前的日期时间

语法

> select new() from table)name

3.16 format() (显示字段格式化)

用途

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

语法

> sekect format(column_name, format) from table_name

参数	描述
column_name	要格式化的字段
format	规定格式

4.牛客网做题笔记(默认都是SQLite 3)

4.1 explain

用途

explain通过图形化或基于文本的方式详细说明SQL语句的每个部分是如何执行以及何时执行的。这包括一下一些基本信息

• 正被访问的表
• 正被使用的索引
• 何时连接数据
• 何时排序数据
• 表和索引基数
• 整连接的表和索引的顺序
• 结果集的基数
• 在查询的每部分正在选择哪些字段
• 排序方法
• SQL每部分的时间范围

4.2 insert或者ignore

用途
向数据库插入数据，如果存在那么忽略

语法

SQLite 3

> insert or ignore into table_name values(val1, val2, ...)

MySQL

> insert ignore into table_name values(val1, val2, ...)

4.3 从已有表创建表并将已有表数据导入创建表中

已有表

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

创建表actor_name，将actor表中的所有first_name以及last_name导入actor_name 表中

/SQLite 3/
create table actor_name as select first_name,last_name from actor
/MySQL/
create table actor_name select first_name,last_name from actor

4.4 强制索引进行查询

/SQLite 3/
select * from table_name indexed by column_name where [condition]
/MySQL/
select * from table_name force index column_name where [condition]

4.5 更改表明

/SQLite 3/
alter table table_name rename to new_table_name
/MySQL/
alter table table_name rename new_table_name

4.6 foreign key(外键约束)

由于SQLite 3不支持使用alter进行修改外键，所以只能在创建表时进行外键约束

create table table_name(
id int not null references employees(emp_no),
last_update datetime not null);
/或者/
create table table_name(
id int not null,
last_update datetime not null,
foreign key(id) references employees(emp_no));

/MySQL/
alter table add foreign key (EMP_no) references employees(ID)

4.7 SQLite 3的sqlite_master

sqlite_master属于SQLite独有的一个root表，其中存储着所有的表的数据，

select name from sqlite_master where type='table'

4.8 replace

用途
用于字符串的替换

语法

replace(str, from_str, to_str)
> select replace(str, from_str, to_str) go

4.9 substr() (截取字符串)

用途
用于对字符串的截取

substr(x,y,z)
x：要截取的字符串
y：字符串的起始位置（起始位置为1），取值范围是-(1_{length(x)),+(1}length(x))，当y等于length(x)时，截取最后一个字符。当y为负数-n时，则从倒数第n个字符处截取。
z：截取字符串的长度，取值范围时正整数，若z省略，则从y处一直到末尾；若z大于剩下的字符串长度，也是截取到末尾

4.10 group_concat() (进行拼接字符串)

用途
用于对字符串的拼接，返回一个字符串结果，与group by联用，该结果由分组中的值连接组合而来。

语法

select group_concat(column, seperate) from table_name group by column_name

4.11 分页

用途
进行分页显示

语法

limit x,y
x：从第几条记录开始返回（从0开始）
y：返回几条记录

limit x offset y
x：代表返回几条记录
y：从第几条记录开始返回（从0开始）

4.12 exists关键字

用途
判断结果集是否有返回，不要求知道返回什么。

与in的区别？
in引导的子句只能返回一个字段

4.13 case when then用法

用途
用于计算条件列表的表达式，并返回可能的结果集之一。类似于编程语言中的if-else或switch，不用于控制sql程序的执行流程，而是作为列的逻辑使用

语法

case [input_expression]
when expression_1 then result_1
when expression_2 then result_2
[else else_result]
end

4.14 累加和

方法
使用复用表，进行子查询