SQL的基础知识

系列文章目录

---

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

---

SQL基础操作

1 前言

根据某个朋友的情况,针对性地写一篇SQL的基础知识篇,适合是IT入门新手和需要了解点技术的非计算机专业人员.

其他:

• 基础语法的学习网站https://www.w3school.com.cn/sql/sql_where.asp

• 针对SQL操作的练习网站推荐: http://sqlfiddle.com/

---

2 概述

SQL主要是针对数据库数据进行一系列操作的语言,基础操作主要是增删改查.针对一些SQL操作,使用的注释格式

主要是以下两种方式。

# 注释               //单行注释
/*多行注释
* 嘻嘻嘻
*/                   //多行注释

2.1 创建和操纵表

2.1.1 创建表语法

create table <表名>(
<列名1> <数据类型> <约束条件>
<列名2> <数据类型> <约束条件>
<列名3> <数据类型> <约束条件>
...
);

创建表的注意事项:

• 在定义表时，可以在约束条件中指定该列允许或不允许NULL值。如果指定不允许NULL值，则在插入数据时如果为NULL就会报错。
• 可以在定义表时使用默认值，使用关键词default <值>进行设定，如果在插入数据时没有值插入，则该列会插入默认值。

2.1.2 更新表

使用alter table语句

alter table <表名> add column <列名> <数据类型>;     //增加新列
alter table <表名> drop column <列名>;               //删除列

2.1.3 重命名表

rename table <旧表名> to <新表名>;
alter table <旧表名> rename to <新表名>;

2.1.4 删除表

drop table <表名>;

---

2.2 检索数据

2.2.1 检索单列

select <列名> from <表名>;

2.2.2 检索多列

select <列名1>,<列名2>,... from <表名>;

2.2.3 检索所有列

select * from <表名>;

2.2.4 检索不同值

select distinct(<列名>) from <表名>;

2.2.5 限制结果

在MYSQL中，限制结果输出的条数，使用**limit <数字>**来表示限制的行数。

---

2.3 数据排序

对检索出来的数据进行排序:

order by <列名> asc/desc;

asc为升序排序，也是MYSQL中默认的排序方式；desc表示按照降序排序,也可以对多列进行排序，如下代码所示。

order by <列名1> <排序方法>,<列名2> <排序方式>...;

注意事项:

• order by语句通常位于查询语句的最后面。后面会具体介绍查询语句的完整构成。

---

2.4 过滤数据

2.4.1 使用where子句

在SQL中，我们使用where子句来对查询出的结果进行过滤，得到我们所需要的数据。它的格式如下。

select <列名> from <表名>
where <过滤条件>;

过滤条件有多个时，可使用and或or来进行多个条件的罗列。当有多个过滤条件是，and的优先级会高于or

2.4.2 where子句操作符

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

注意事项:

• 在判断某个值是否为空值是，应使用is null或is not null来判断，而不是使用"= null"或"!= null"。

2.4.3 使用通配符进行过滤

在用通配符进行过滤时，我们使用like操作符进行。通配符主要包括两个，它们分别为百分号’%‘和下划线’’。其中’%‘表示任意长度的字符串，而下划线’'只表示一个字符。

注意事项:

• ’%'不仅可以表示一个或多个字符，还可以表示0个字符。

---

2.5 创建计算字段

2.5.1 计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式,需要直接从数据库中检索出转化、计算或格式化过的数据,而不是检索出数据，然后再在客户机应用改程序或报告程序中重新格式化。只有数据库知道SELECT语句中那些列是实际的表列，哪些列是计算字段。

2.5.2 拼接字段

在MYSQL中，使用concat函数对字段进行拼接，格式如下。

concat(str1, str2, str3......)

对于字符串中存在的空格，用字符串处理函数**RTRIM()、LTRIM()、TRIM()**来进行处理。

2.5.3 使用别名

可以用关键字as来给列起一个别名。

select <列名> as <别名> from <表名>;

---

2.6 使用函数处理数据

函数的可移植性是很强的，而且使用函数可以很方便的对数据进行处理。

SQL函数主要有四种

• 文本函数

left(s, n)          //返回字符串左边的n个字符
right(s, n)         //返回字符串右边的n个字符
trim(s)             //去掉字符串s的空格
ltrim(s)            //去掉字符串左边的空格
rtrim(s)            //去掉字符串右边的空格
length(s)           //计算字符串的长度
lower(s)            //将字符串的所有字符变成小写
upper(s)            //将字符串的所有字符变成大写

• 数值函数

abs(x)             //绝对值
cos(x)
sin(x)
tan(x)
acos(x) asin() atan()     //三角函数
ceil(x)             //返回大于或等于x的最小整数
floor(x)            //舍去x的小数部分，只保留整数部分，并且不进行四舍五入
pi()               //返回圆周率
sqrt(x)            //平方根
pow(x, y)          //x的y次方
exp(x)             //返回e的x次方
rand()             //返回0-1的随机数
round(x, y)            //返回x的值小数点后面的第y位，进行四舍五入
truncate(x, y)     //返回x的值小数点后面的第y位，不进行四舍五入

• 日期与时间函数

current_date()                  //返回当前日期
current_time()                  //返回当前时间
current_timestamp()/now()             //返回当前日期和时间
date()                          //从日期或日期时间中提取出日期值
year() month() day() hour() minute() 
date_format(d, f)               //以指定格式显示日期
datediff(date1,date2)           //返回两个日期相差的天数，大日期在前
timediff(datetime1,datetime2)   //返回形式为时:分:秒，大日期在前
timestampdiff(interval,timeSQL1,time2)     //返回两个日期之差，大日期在后。精确到什么单位取决于第一个参数interval，可以为年---秒

• 系统函数

---

2.7 汇总数据

聚合函数:聚合函数就是用来数据汇总的一类函数。它们通常和分组数据进行结合使用。

• avg()
• min()
• max()
• count()
• sum()

需要注意的是，聚合函数均不会处理NULL值。

---

2.8 分组数据

2.8.1 创建分组

创建分组使用group by子句

select <列名> from <表名>
where <过滤条件>
group by <列名>
having <过滤条件>
order by <列名> <排序方式>

上面的格式也是select语句完整的SQL格式。

2.8.1 过滤分组

过滤分组使用having，where是行级过滤，having是组级过滤。

---

2.9 使用子查询

子查询也就是select语句的嵌套，有了子查询就可以进行复杂的查询。子查询返回的一张表，所以其位置也比较灵活，可以位于from后面，也可以位于where后面。子查询也可以说是一个一次性的视图。在执行顺序上，先执行子查询，再执行外层的查询。

注意事项:

• where子句中不能使用聚合函数。

---

2.10 联接表

SQL表的连接主要分为以下几种。

• 内连接
• 外连接(左连接、右连接)
• 自连接

2.10.1 内连接

语法格式如下：

SELECT 查询字段 FROM 表1 [as] 别名 [INNER] JOIN 表2 [as] 别名 ON 表1.关系字段 = 表2.关系字段

内连接就是得出两个表的交集部分。

2.10.2 左连接

语法格式如下：

SELECT 查询字段 FROM 表1 [as] 别名 LEFT JOIN 表2 [as] 别名 ON 表1.关系字段 = 表2.关系字段

左连接就是左边表的记录要全部显示出来，右表只有和左表匹配到的记录才进行显示。

2.10.3 右连接

和左连接相反。

2.10.4 自连接

把一张表看成两张表进行自连接,往往是一张表的某些列间存在逻辑关系。

---

2.11 组合查询

2.11.1 创建组合查询

创建组合查询时我们使用UNION将两个或多个select语句查询出的结果组合在一起形成一个表。

需要不同的select语句返回相同数量的列且对应列的数据类型相同，事实上应该是意义都一样才合理。

2.11.2 union与union all

对于组合查询出的结果，如果使用union，则会自动对结果进行去重。如果不想去重则使用union all。

2.11.3 组合查询的排序

对于组合查询结果的排序只需要使用一个order by子句，放在最后一个select语句的后面即可。

---

2.12 插入、更新与删除数据

2.12.1 插入数据

insert into <表名>(列名1,列名2,...) values (数据...);

虽然表名后面的列名表不是严格要求的，但是这样会更安全。如果不写出列名，则需要严格按照表的定义插入数据。如果我们只需要插入一行中的部分列，那么列出列名就变得必须了。

2.12.2 插入检索出的数据

使用insert…select语句进行。

insert into <表名>(列名表)
select <列名表> from <表名>;

2.12.3 复制表

create table copytable as
select * from <表名>;

2.12.4 更新数据

update <表名>
set <列名> = ...
where <过滤条件>;SQL

不要忘了过滤条件，否则将会对所有行的数据进行更新。

2.12.5 删除数据

delete from <表名>
where <过滤条件>;

如果要删除某行中某列的元素，可以将其值更新为NULL。

---

2.13 使用视图

视图是一张虚表，它并不存储实际的数据，存储的只是select语句。

使用视图有如下的优点

• 提高sql语句的重用性
• 保证了数据的安全性SQL
• 大大简化了SQL语句

创建视图

create view <视图名> as
<select语句>;

删除视图

drop view <视图名>;

---

2.14 事务

2.14.1 什么是事务

需要在同一个处理单元中执行的一系列更新处理的集合.

2.14.2 语法

start transaction；
DML语句1；
DML语句2；
DML语句3；
...
ROLLBACK/COMMIT;

2.14.3 理解事务

事务其实很好理解，它将一系列操作放在一个块中形成一个代码块，这个代码块要执行就全部执行，要么就不执行。拿银行转账举例，转账的过程为，我的账户金额减少——>对方账户金额增加——>交易完毕。如果我的账户金额减少后出现了故障导致对方账户金额没有增加，那么这肯定是不被允许的。所以这个转账过程中的每一步都必须完整的执行。rollback就是在操作执行出现故障时，来使得数据库回滚到执行操作前的状态。而commit则是所有操作正确执行后才执行，告诉系统这个事务正常完成了，系统可以进入下一个新的状态了。