postgresql环境搭建和sql语句学习

数据库、数据库管理系统、数据库应用系统
#二、SQL语言
DDL、数据库定义语言
DML、管理语言，
DQL、数据库查询
DCL、控制语言、事务提交、日志
#三、数据库访问技术
ODBC、JDBC对不同的数据库有不同的数据库驱动
ADO.NET.PDO
#四、postgres特点
1、伯克利（科研）服务器，不包括sql语言解释器和口让那
主要特点：free、速度快对性能比较重视（复杂查询）、window跨平台、多个操作系统课稳定执行、丰富的接口（存储过程自定义）、安全性
开源软件比较多、缓存
2、对比mysql和postgres 应用
#五、pgAdmin工具连接数据库
关闭服务器防火墙
修改服务器监听
修改配置
1、安装pgAdmin
2、检查虚拟机和主机是否可以互相ping通
3、监听所有客户端
但是我安装的时候在ubuntu中无法继续使用psql工具
报错：但是笔者这里报错了psql始终出现问题
psql: error: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
这里笔者因为多次没有成功使用psql所以又重新装系统了
但是呢，重装系统后也是碰到了一样的问题，就是无法使用psql，经过qq群交流后发现问题是需要sudo service postgresql restart 重新启用这个库，同时service也是需要看看的
4、其中笔者在psql中\l和\du;他们共同的postgres这个用户感到疑惑
\l是显示所有可用的数据库，\du呢是显示当前数据库服务器的所有用户
5、由于技术问题ubuntu上遇到一些问题，固转centos了、但是centos镜像源的问题，最终联系助教奥、具体的方法
使用psql修改postgresql默认用户ppostgres的密码
[PostgreSQL数据库默认用户postgres的密码 - 郑道杰 - 博客园 (cnblogs.com)](https://www.cnblogs.com/zhengdaojie/p/13816782.html)
但是又有点问题，所以我们打算换一个方式来弄:搭建后，继续按照上文修改监听和防火墙
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql
sudo su postgres
psql（启动postgresql数据库）

#六、pgAmin对数据库的创建与修改，修改数据表对象
主要是表的修改
#七、SQL语言来操作数据库对象
数据库用户的增删查改,对一张表的操作
表重命名 ：alter table *** rename to   *** ;
字段重命名: alter table *** rename *** (column name) to *** (new column name)；
字段类型重命名: alter table  *** (table name) alter colunm *** type *** (varchar(39));
字段添加：alter table *** add column *** (new column name) *** (typename);
字段删除： alter table *** student drop column *** (column name);
删除表： drop table *** (table name); || drop table if exist *** (table name);

#八、常用数据类型（优化存储，提高性能当数据量大起来了就回极大的降低性能）
1、数值类型
        SMALLINT，INTEGER、REAL、NUMERIC（m,n）最大存储长度和精度，小数点不占有一位，
2、日期与时间类型
        TIME一天的时间, DATE一个具体的日期, TIMESTAMP合体8，4，8byte
3、字符串类型
        CHAR（检索速度快）/CHARACTER(固定长度，会用空白补齐),  VARCHAR、TEXT（无长度限制）
#九、运算符的介绍
加减乘除![[Pasted image 20231004172529.png]]
有参数为null，不比较。字符串和整数比较，把字符串变成整数比较。
between and 是闭区间
in 运算，前者这个数字是否在后面的集合中，也可以用not操作
Like 进行模糊匹配%是任意多个字符，_ 是一个字符

not、and、or运算符
select not '1', not 'y',not '0', not 'n'

#十、pottgresql常用函数 
#十一、数据库索引
B-tree索引(>=)顺序存储了，如果数据重复率低和分散
Hash索引，等于索引
GiST 一种索引架构
GIN多个值的建，某个字段是个数组
create index emp_name_index on employee (e_name) 创建的索引创建的是默认B-tree索引
提高查询速度，加速表的连接
缺点：数据更新索引也需要更新，占用磁盘空间
#十二、视图
创建一个视图，将一些数据打包好封成一个屏蔽了底层sql实现的数据表
我不想让他们看见。
select * from employee;
create view *** as select (column name),(),() from employee where (column = ?) order by (column name) tread；

#十三、简单、批量数据插入，数据更新、删除操作，主键、外键
单表、指定字段insert
穷举字段内容，如果不存在也要用null表示
插入单个内容时，insert into （table name） values （column1，column2，column3..）;
批量插入表的内容是，insert into （table name）select （column name）from table2 ；

**数据**更新操作
使用update命令
update （table name）set (column) = (new value) 约束条件
或者不用约束直接 update table name set （column ） = （new value）谨慎使用
显示操作select * from table name + 约束条件；

数据删除操作
使用delete 和 truncate语句删除完成删除操作
![[Pasted image 20231005115219.png]]
delete会记录信息到日志里面，而truncate是直接将数据表的所有数据drop然后直接创建一个空表。delete可以between之间（指定条件）。

#十五、定义主键和外键约束，和约束应用
这是在定义一张表的时候就应该完成的。
主键约束
id int primary key
或者使用 constraint pk_emp primary key（column 属性）
列级约束和表级约束，区别是单列与多列
外键约束(首先你要有两张表)，保证被关联的表的数据不会被随意删除。
constraint fk_emp3_dept(外键名字) foreign key (colunm) references dept（另一张表的名字）(column)

#十六、非空约束、唯一约束、默认值约束定义和作用（数据的完整性exp：邮箱不为空）
虽然可以在应用，但是也可以在数据库上加上保险
非空约束：在约定的数据表的属性上加上not null
唯一约束：和主键有点区别，unique可以在多个属性中出现，但是主键只有一个，唯一约束中，属性可以为null，主键必须有值。
默认值约束（记录时间）：时间上指定默认值，会自动维护这个时间。

#十七、数据查询操作
有简单数据查询，条件查询，单表复杂查询
![[Pasted image 20231005161825.png]]
select * from (table) 其中的* 是所有字段
当然你可以把* 认为是这个表的任何字段内容，table.column表明表的来源
别名操作（引用）（table）as （extre name）同样的as 不是必须的
select e_no as a, e_name as b, e_hireDate as c from (table);
这里查询出来的结果column name被替换成extre name， as在这里也不是必须的

**范围查询**
where，
between and
select colunm  from table where column bewteen 值 and 值.
in(一个内容是否在一个范围内)
select （呆会要显示出来的）column from table where cloumn in (范围);
同样也可以使用not关键字，比如not in （范围）；
like(模糊匹配) %无限，_ 有限
select 'abc' like 'a_';
select column from employee where column like li%

单表复杂查询，空值内容
我想查null呢
select column  from table where column is (not) null

使用逻辑符号进行查询and、or、not
select colunm from table where column = ? or/and column =?

order by (默认升序排序)des,asc（默认是null last）
select colunm from table where column = ? or/and column =? order by des;
它多个order by 组合在一起order by 。。。asc ，order by 。。。des null first（空值情况）

limit 可以显示查询结果集，显示前5条
select * from table limit 5 offset 5; 偏移5条直接查询
可以实现分页操作，第二页是offset，当前（页码-1）* 展示数量

#十八、内连接，左连接，右连接
select column from table (inner join) table (条件)；
后者select column from table ，table where + 条件
但是这里有点问题，空值是无法匹配的，只显示满足条件的信息
进行left join查询（返回可以和不可以的数据）
select column from from table left join table + where + 条件
他会把表直接拼在原表的不同的地方。

#十九、查询语句的嵌套
EXISTS ，IN，标量子查询
![[Pasted image 20231005173436.png]]
exist 返回的是bool类型的数据，条件满足时会返回对应的内容，同样的也可以加入not指令
![[Pasted image 20231005173838.png]]
使用IN查询来查找这个的操作，他是找到这个集合
![[Pasted image 20231005174149.png]]
标量子查询的方式（没听懂。。）

#二十、查询结果集合并
![[Pasted image 20231005175135.png]]
直接在union all
直接拼两个结果集，但是它不会去重
使用union 会去掉重复记录
两个有什么区别呢
![[Pasted image 20231005175302.png]]
来源是从很多不同表中取出的，是否会出现数据的重复
对于数据量特别大的时候性能就会下降
两个结果集返回的结果的字段数量应该时一致的，如果字段有点不同的话，我们就要使用null来进行占位操作。