常用数据库操作

常用数据库(PGSQL、oracle)的备份,导入导出

2026-03-03 · 13 分钟 · lei

MongoDB

MongoDB 简介 NoSQL NoSQL(Not Only SQL),意为"不仅仅是SQL",是对于传统的关系型数据库的数据库管理系统的统称 NoSQL 用于超大规模数据的存储,这些类型的数据存储不需要固定的模式,无需多余的操作就可以横向扩展 ...

2024-02-02 · 20 分钟 · lei

PostgreSQL

PostgreSQL 起步 简介 开源:可以基于任何目的使用、修改甚至发布 先进:高度兼容SQL标准,支持事务的ACID原则 功能丰富:基本数据类型、数组、范围类型、JSON、XML、几何类型;完整性约束;索引;复杂查询;先进的查询优化器;分区表;异步、同步、逻辑复制;安全认证;全文检索 ...

2022-10-09 · 26 分钟 · lei

常用SQL

常用SQL 查询 拼接SQL 1 SELECT CONCAT('update tb_2 set col1=',tb1.col1,' where col2=',tb1.col2,';') from tb_1 tb1 where .... 查询字符串出现次数 1 select id,LENGTH(string) - LENGTH(REPLACE(string, ",", "")) AS cnt from strings; 按名字最后两个单词排序 1 select first_name from employees order by substring(first_name,-2) 分组并连接字段 1 2 3 SELECT dept_no, group_concat(distinct emp_no order by emp_no asc) employees FROM dept_emp GROUP BY dept_no 查询除了最大最小值后的均值 1 2 3 4 5 6 7 8 9 10 11 select avg(salary) as avgt from salaries where to_date = '9999-01-01' and salary not in (select max(salary) from where to_date = '9999-01-01' ) and salary not in ( select min(sala where to_date = '9999-01-01' ) 使用强制索引查询 1 2 3 select * from salaries force index (idx_emp_no) where emp_no=10005 使用with子句查询 1 2 3 4 5 6 7 #with tb as (select ....) select WITH TB_TEMP AS ( SELECT role_id FROM sys_user_role WHERE user_id = '1533428933922820096' ) SELECT sr.role_name FROM sys_role sr WHERE EXISTS ( SELECT 1 FROM TB_TEMP te WHERE te.role_id = sr.id ) 创建 根据别的表数据新建表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 #根据别的表的数据新建表,方法1,MySQL适用 create table if not exists actor_name as ( select first_name, last_name from actor ) #方法2 create table if not exists actor_name( first_name varchar(45) not null, last_name varchar(45) not null); -- 创建表 insert into actor_name select first_name,last_name from actor; -- 插入查询结果 根据表创建视图 1 2 3 4 5 6 7 -- 创建视图,使用小括号创建视图中的字段名 create view actor_name_view (first_name_v, last_name_v) as select first_name,last_name from actor; -- 创建视图,在select后面对查询的列重命名作为视图字段名 create view actor_name_view as select first_name as first_name_v,last_name as last_name_v from actor 构造触发器 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 -- mysql中触发器如下: -- trigger_name:标识触发器名称,用户自行指定 -- trigger_time:标识触发时机,取值为 BEFORE 或 AFTER -- trigger_event:标识触发事件,取值为 INSERT、UPDATE 或 DELETE -- tbl_name:标识建立触发器的表名,即在哪张表上建立触发器 -- trigger_stmt:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句,每条语句结束要分号结尾 CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt -- 具体创建触发器语句;其中 new表示触发器的所在表新数据,old表示触发器的所在表老数据 create trigger audit_log after insert on employees_test for each row begin insert into audit values(new.id,new.name); end 修改 修改表名 1 2 3 -- 修改表名;ALTER TABLE 表名 RENAME TO/AS 新表名 ALTER TABLE titles_test RENAME TO titles_2017; 更新记录 1 2 3 4 5 -- 直接更新 update titles_test set to_date=null,from_date='2001-01-01' -- 替换 UPDATE titles_test SET emp_no = REPLACE(emp_no, 10001, 10005) WHERE id = 5 创建索引 1 2 3 4 5 6 7 8 9 10 11 -- 主键;该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL ALTER TABLE tbl_name ADD PRIMARY KEY (col_list); -- 唯一索引;这条语句创建索引的值必须是唯一的 ALTER TABLE tbl_name ADD UNIQUE index_name (col_list); -- 普通索引;添加普通索引,索引值可出现多次 ALTER TABLE tbl_name ADD INDEX index_name (col_list); -- 全文索引;该语句指定了索引为 FULLTEXT ,用于全文索引 ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list); 创建外键约束 1 2 3 4 5 6 -- ALTER TABLE <表名> ADD CONSTRAINT FOREIGN KEY (<列名>) REFERENCES <关联表>(关联列) ALTER TABLE audit ADD CONSTRAINT FOREIGN KEY (emp_no) REFERENCES employees_test(id); 新增列 1 2 3 -- ALTER TABLE <表名> ADD COLUMN <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]; alter table actor add column create_date datetime not null default '2020-10-01 00:00:00' after last_update; 删除重复记录 1 2 3 4 5 -- 需要把记录先展示出来并重命名,然后删除 DELETE FROM titles_test WHERE id NOT IN ( SELECT * FROM (SELECT MIN(id) FROM titles_test GROUP BY emp_no) a -- 分组查询出id最小的 );

2022-03-17 · 3 分钟 · lei

MySQL

MySQL 初见 序列化反序列化也可以开发项目,不过太不方便了 常见的数据库管理系统 Oracle:甲骨文公司;用于传统企业,银行等 MySql:甲骨文公司收购(MySql AB公司) DB2 Sybase MS SqlServer(支持标准sql的数据库管理系统) MySql安装;MySql卸载必须使用安装软件卸载,否则下次安装不上(卸载不干净),手动删除Program File下的Mysql以及ProgramData下的MySql 每一个软件都有特定的端口号,不能重复;端口是软件的代号;MySql服务的默认端口3306 登录MySql:mysql -u用户名 -p密码 sql,DB,DBMS DB:DataBase,数据库,在硬盘上以文件方式存在 DBMS:DataBase Management System,数据库管理系统(Mysql) SQL:结构化查询语言,是一门标准通用的语言,是一种高级语言(sql语句编译有DBMS完成) DBMS执行SQL语句来操作DB中的数据 表(table): table是数据库基本组成单元,所有数据都是由表格形式组织,目的可读性强 每个表有行,列;行被称为数据/记录(data),列被称为字段(column) 每个字段(列)应该有:字段名、数据类型、相关约束 SQL语句分类 DQL(数据查询语言):select 查询语句 DML(数据操作语言):insert delete update,对表中数据进行增删改 DDL(数据定义语言):create drop alter,对表结构的增删改 TCL(事务控制语言):commit提交事务,rollback回滚事务(T是Transaction) DCL(数据控制语言):grant授权,revoke撤销权限 导入数据(sql文件) 登录Mysql 查看有哪些数据库:show databases;(MySql命令) 创建自己的数据库:create database mydata;(MySql命令) 使用mydata数据库:use mydata; 查看当前使用数据库有哪些表格:show tables;(MySql命令) 导入sql:source 文件路径; .sql文件,以sql结尾,这样的文件被称为"sql脚本",该文件中编写了大量的sql语句;想批量执行sql语句使用sql脚本 表结构 常见命令使用 数据库 show databases:查看数据库列表; create mydata:创建数据库 use 数据库:使用数据库 select database():查询当前使用数据库 show version():查看数据库版本 drop database mydata:删除数据库 表 show tables:查看表列表 desc 表名:查看表结构 show create table emp:查看emp表创建语句 其他 \c:结束mysql语句 quit():退出数据库 查询(DQL) select 所有sql语句以**‘;’**结尾;sql不区分大小写;sql语句要求字符串使用单引号 语法格式 select 字段名1,字段名2,… from 表名; 字段可以参与数据运算 1 2 select ename,sql*12 as 年薪 from emp;//查询用户,年薪显示为年薪;可以省略as关键字 select * from emp;//查询所有字段,不建议,效率低 条件查询(where) select 字段 from 表名 where 条件 执行顺序:from–>where–>select <>:不等于(大于小于不等于) between … and …:闭区间,相当于>= and <=;可以用在字符方面(左闭右开) is null、is not null:在数据库中null不是值,代表什么都没有(空),不能使用等号衡量 and:和,并且;优先级高于or or:或者 in、not in:等同于or,in代表在其中的数据 like:模糊查询;**%代表任意多个字符;_代表任意一个字符;可以使用‘\’**转义字符 1 2 3 4 5 6 7 select sal from emp where ename='SMTTH'//查询名字叫SMTTH的员工的工资;字符串用单引号括起来(字段名修饰为varchar) select ename from emp where sal<>3000;//工资不等于3000的员工 select ename from emp where sql between 1000 and 3000;//闭区间,工资在1000-3000的员工 select ename from emp where comm is null or comm=0;//没有补助的员工 select ename from emp where sal>1000 and (deptno=20 or deptno=30);//查询工资大于1000并且部门编号是20或者30的员工;and优先级比or高,所以使用括号 select ename from emp where sal in (1000,5000);//工资等于1000和5000的员工,注意不是包括 select ename from emp where ename like '_A%';//名字中第二个字母是A的员工;可以使用转义查询:\_普通字符_ 排序(order by) select 字段 from 表 order by 字段 默认为升序,asc 降序:desc 多个排序规则,用**’,’**隔开,越靠前的字段,占主导地位;只有当前面字段无法排序时,才会启动后面字段 select … from … where … order by…:执行顺序from–>where–>select–>order by;order by是最后执行的 1 select ename,sal from emp order by sal desc,ename asc;//按照工资降序排列,工资相同再按照名字排序 分组函数 分组函数有:count:计数、sum:求和、max:最大值、min:最小值、avg:平均值 分组函数只有上面5个;又叫做多行处理,输入多行,输出一行,分组函数自动忽略NULL; 分组函数不可以直接出现在where子句中,原因:group by语句一定在where执行后执行 count(*)一定是总记录条数;count(字段)统计字段中不为null的元素 1 2 3 4 5 select count(ename) from emp;//找出总人数,多行处理函数 select avg(sal) from emp;//查出平均工资多行处理函数 select ename,ifnull(comm,0) from emp;//ifnull()空处理函数,如果是空,值为后面的 select ename,sal from emp where sal>avg(sal);//语法错误,分组函数不能出现在where子句中 select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;//分组函数组合起来使用 分组查询(group by,having) group:按照某个字段或者某些字段进行分组 having:是对分组之后的数据进行再次过滤,一定出现在group by中 分组函数一定在group by执行结束后执行,当一条sql语句没有group by,那么整张表自成一组;而group by语句一定在where执行后执行 当一个sql语句中出现group by时,select后只能跟参与分组的字段和分组函数;其他字段没意义 group by多个字段联合起来分组,将多个字段看成一个字段 能够使用where过滤的,尽量不用having,效率低 1 2 3 4 5 6 7 8 select max(sal) from emp group by job;//找出每个岗位的最高工资 select ename,sal from emp where sal>(select avg(sal) from emp);//找出薪资高于平均工资的人(1.找出平均工资,2.找出高于平均工资,子查询) select max(sal),job from emp group by job;//查询每个岗位的最高工资 select max(sal),job from emp group by deptno,job;//查询每个部门不同岗位的最高薪资,分组联合 select avg(sal),deptno from emp group by deptno having avg(sal)>2000;//查询每个部门平均薪资大于2000的 select distinct job from emp;//查询工作岗位,去除重复 select distinct deptno,job from emp;//distinct后面所有字段去重 select count(distinct job) from emp;//统计岗位数量,先去重岗位,然后计数 连接查询 实际开发中,大部分数据都不是从单表查询,而是多个表联合查询;一般一个业务都会对应多张表,为了解决数据冗余 ...

2020-09-09 · 41 分钟 · lei