-
PostgreSQL常用函数介绍
函数名称 作用 AVG() 返回某列的平均值 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列的值之和
示例代码：select max(e_salary) from employee 这行代码表示的是在employee表中返回e_salary数值中的最大值。
- 常用字符串函数
| 函数名称 | 作用 |
|---|---|
| LENGTH(s) | 计算字符串长度 |
| CONCAT(s1,s2,…) | 字符串合并函数 |
| LTRIM(s)/RTRIM(s)/TRIM(s) | 删除字符串空格函数 |
| REPLACE(s,s1,s2) | 字符串替换函数 |
| SUBSTRING(s,n,len) | 获取子串函数，完成字符串截取 |
select name, length(name) from employee 在employee表中查询name和每个name的长度
select name, date, concat(name, date) from employee在employee表中将name和 date进行拼接，并输出
select city_name,REPLACE(city_name,'市','省') as city from tmp_city在tmp_city表中将city_name中的市换成省
| 函数名称 | 作用 |
|---|---|
| EXTRACT(type FROM d) | 获取日期指定值函数 |
| CURRENT_DATE | 获取当前日期函数 |
| CURRENT_TIME | 获取当前时间函数 |
| NOW() | 获取当前日期时间函数 |
select no, name, date, extract(year from date), extract(month from date), extract(day from date) from employyee
-
自定义函数
-
- 基本语法格式
create function //声明创建函数
add(integer, integer) //定义函数名称，参数类型
returns integer //定义函数返回值
as 'select $1 + $2' // 定义函数体
language SQL //用以实现函数的语言的名字
returns NULL on NULLINPUT // 定义参数为NULL时的处理情况
- 数据库索引介绍
| 索引名称 | 使用场景 |
|---|---|
| b-tree 索引 | 适合处理那些能够按顺序存储数据 |
| Hash索引 | 只能处理简单的等于比较 |
| GIST索引 | 一种索引架构 |
| GIN索引 | 反转索引，处理包含多个值的键 |
-
索引的创建，创建时默认是b-tree 索引。
create index emp_name_index on employee(e_name);，emp_name_index为索引名称，employee(e_name)为employee下的e_name字段 -
索引的删除
drop index emp_name_index -
SQL视图
-
- 定义一个试图
create view v_emp_dev as select no, name, salary from employee where dapt_no = 10 order by e_salary desc
创建一个名为v_emp_dev的试图，在employee表中查找dapt_no = 10的字段，并用e_salary降序输出no, name, salary
-
视图的使用：
select * from v_emp_dev -
删除视图
drop view v_emp_dev -
优点：1、屏蔽操作细节，直接调用视图就可
2、提高效率
3、安全 -
数据操作
-
- 单条插入
insert into student values (1, '张三', '1990-01-01', 3.85);，student表中有id int, name varchar, birthday date, score numeric(5,2)，在表中按顺序插入(1, '张三', '1990-01-01', 3.85)
注意：插入的内容的数量要和表中的一致。 - 向表中指定字段插入
insert into student (id, name, birthday) values (1, '李四', '1990-01-01') - 批量数据操作
- 单条插入
(3, '张三', '1990-02-01'),
(4, '李四', '1990-02-01'),
(5, '王五', '1990-02-01')；
insert into student_new select * from student;
上面一行代码表示的是查询student表中的所有数据，并将其复制到student_new表中。
insert into student_new (id, name) select id, name from student;
上面一行代码表示查询student表中的(id, name)数据，并将其复制到student_new表中的(id, name)。
- 数据更新操作 将id为2的学生的name更新为李四。
update student set name = '李四' where id = 2;
将student表中所有score更新为0
update student set score = 0;
- 数据删除操作 将id为4的数据删除掉
delete from student where id = 4;
将birthday在1990-01-01和1990-02-01之间的数据删除delete from student where birthday between '1990-01-01' and '1990-02-01';
将表中的的所有数据删除delete from student;
将表中的的所有数据删除truncate table student
delete和truncate的区别
| delete | truncate | |
|---|---|---|
| 执行速度 | 慢 | 快 |
| 可指定条件 | 可以 | 不可以 |
| 语句分类 | DML | DDL |
| 可以回滚事务 | 可以 | 不可以 |
| 删除操作记录日记 | 记录 | 不记录 |
- 主键和外键 创建主键方式一
create table student(
id int primary key, //创建主键，具有唯一性
name varchar(30),
score numeric(5,2)
);
创建主键方式二
create table student(
id int,
name varchar(30),
score numeric(5,2),
constraint pk_student1 primary key(id) //创建主键，具有唯一性
);
创建外键的方式
create table family (
id int,
name varchar(30),
deptId int,
constraint fk_family foreign key(deptId) reference student(id) //创建了deptId外键，关联了student表中的id
);
注意：关联的表中的字段一定是另外一张表中的字段
- 主键约束作用
1、唯一标识
2、提高数据的检索效率 - 外键约束作用
1、保证数据的完整性
2、提高数据的检索效率 - 非空约束
create table student1(
id int,
name varchar(30) not null, //不能为空
score numeric(5,2)
);
- 唯一约束
create table student2(
id int,
name varchar(30) not null,
score numeric(5,2),
phone varchar(30) unique //唯一约束，不能重复
);
主键和唯一约束的区别：
1、主键不能赋值为null，唯一约束可以，且可以赋值多条
2、一个表中只能有一个主键，但可以有多个唯一约束
- 默认约束
create table student2(
id int,
name varchar(30) not null,
phone varchar(30) unique //唯一约束，不能重复
score numeric(5,2) default 0.0, //默认为0.0
);
- 三种约束作用：
1、维护数据的完整性
2、在业务层面保证数据的正确性 - 简单数据查询操作
基本语法
select
{* | <字段列表>} //查询结果字段内容
from
[
<表1>,<表2>... //需要查询的数据表
[where <表达式>] //where查询条件表达式
[group by <group by definition>] //group by 数据分组
[order by <order by definition>] //查询结果排序
[linit [<offset>]<row count>] //限制结果显示数量
]
- 简单数据查询操作
select * from employee;
select e_no, e_name from employee;
select employee.e_no, employee.e_name from employee;推荐使用这种方法，在现实操作中，我们会查询很多表，可以区分字段来自哪张表
简化写法：为表取别名，as可以省略。这种方式同样适用于为字段取别名
select e.e_no, e.e_name from employee as e; - 单表指定条件查询操作
1、检索score小于60的数据
select no, name, from student where score < 60;
2、IN查询，返回布尔值
如下一行代码查询了score为(90, 91)的数据
select no, name, from student where score in (90, 91);
如下一行代码查询了score不为(90, 91)的数据
select no, name, from student where score not in (90, 91);
3、between查询
如下一行代码查询了score在90和99之间的数据
select no, name, from student where score between 90 and 99;
4、like模糊查询，返回布尔值
查找表中姓李的数据，使用%可以匹配多个字符，使用_可以匹配单个字符，
select no, name from student where name like '李%';
5、空值查询，使用is null或者is not null，使用= null，查询不会返回任何数据，因为null不等于任何值
select no, name, from student where score is null;
6、and或者or查询
select no, name, from student where score is not null and name = '李四';
7、order by查询，默认升序排序，需要降序则在语句后面加desc
select no, name from student order by score;
在表中查询no, name，先按照score升序排序，假如score一样，则相同的项按照id降序排序
select no, name from student order by score asc, id desc;
假如查询的数据是空值，默认会在最后显示，在语句最后面加nulls first则可以排在前面显示
select no, name from student order by score nulls first;
8、limit程序
只显示前五条数据
select no, name from student order by score limit 5;
忽略前五条数据，然后再从第六条数据开始显示前五条数据
select no, name from student order by score limit 5 offset 5; - 多表链接查询操作
select no, name, id from student, family - lift join 查询
LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。https://www.runoob.com/sql/sql-join-left.html - 子查询操作
会先执行exist后面的查询，假如有查询结果，则会显示student表的所有内容，假如没有查询结果，则不会显示数据。https://www.runoob.com/sql/sql-exists.html
select * from student where exists (select no from family where name = '李%')
in查询https://www.runoob.com/sql/sql-in.html
select * from student where name in (select no from family where name = '李%') - 标量子查询
| |表示合并字符串
select no, name, (select name | | ' ' | | id from family where student.no = family.no) as number from student; - 查询结果合并
1、union all，只需要将union all放在两条查询结果之间即可完成合并，只是简单的将两条查询结果合并，并不会去重，但执行速度快。用union可以去重，但执行速度慢。两个查询语句需要查询相同数量的字段，数量不一样要用null补上
select no, name, from student where score not in (90, 91)
union all
select no, name, from student where score in (80, 89)
扫一扫
502
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
