SQL-小笔记

长的不像程序员的程序猿

于 2023-12-26 15:17:35 发布

阅读量92

点赞数

文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/weixin_47036600/article/details/129464587

版权

基础知识：

alter table emp_test rename to  emp_test2;  给表换名字，关键字：rename

alter table emp_test2 drop column  JOB_ID;删除表字段，关键字drop column

alter table emp_test2 add JOB_ID VARCHAR2(10);增加字段，关键字ADD 加表名和字段类型大小



comment on table emp_test2 is '员工表';

comment on column emp_test2.employee_id is '员工ID';

comment on column emp_test2.FIRST_NAME is '员工名';

comment on column emp_test2.LAST_NAME is '员工姓';

comment on column emp_test2.EMAIL is '邮箱';

comment on column emp_test2.PHONE_NUMBER is '电话号码';

comment on column emp_test2.HIRE_DATE is '入职日期';

comment on column emp_test2.JOB_ID is '工作ID';

comment on column emp_test2.SALARY is '薪酬';

comment on column emp_test2.COMMISSION_PCT is '佣金百分比';

comment on column emp_test2.MANAGER_ID  is '上级经理ID';

comment on column emp_test2.DEPARTMENT_ID is '部门ID';

Oracle常用数据类型

分类	备注和说明	数据类型	说明
字符型		Char	固定长度的非 Unicode 字符数据
		Varchar/varchar2	可变长度非 Unicode 数据
		Nchar	固定长度的 Unicode 数据
		Nvarchar/nvarchar2	可变长度 Unicode 数据
大对象		Clob	用来存储单字节的字符数据
		nclob	用来存储多字节的字符数据
		Blob	用于存储二进制数据
		Bfile	存储在文件中的二进制数据，这个文件只能被只读访问
日期和时间	日期和时间在单引号内输入	Date	日期和时间
数字数据	该数据仅包含数字，包括正数、负数以及分数	Number(m)	整数
		Number(m,n)	数字

修改表的名称

ALTER TABLE 表名 RENAME TO 新表名;
ALTER TABLE emp_test RENAME TO emp;

- Comment -- 注释

comment on table  表名  is ‘表名注释';

comment on column  表名.列名  is ‘字段名的注释';

comment on table EMP is '员工表';

comment on column EMP.EMPLOYEE_ID is '员工ID';

comment on column EMP.FIRST_NAME  is '员工的名';

comment on column EMP.LAST_NAME  is '员工的姓';

comment on column EMP.EMAIL  is '邮箱';

comment on column EMP.PHONE_NUMBER  is '电话号码';

comment on column EMP.HIRE_DATE  is '入职日期';

comment on column EMP.JOB_ID  is '工作ID';

comment on column EMP.SALARY  is '薪酬';

comment on column EMP.COMMISSION_PCT is '佣金百分比';

comment on column EMP.MANAGER_ID is '上级经理ID';

comment on column EMP.DEPARTMENT_ID  is '部门ID';

- 约束

1）主键约束（Primary Key）：要求主键列数据唯一，并且不允许为空

2）唯一约束（Unique Key）： 要求该列唯一，允许为空，但只能出现一个空值。

3）检查约束（Check Key）：某列取值范围限制、格式限制等，如有关年龄的约束

4）默认约束（Default Key）： 某列的默认值，如将性别默认为“男”

5）外键约束（Foreign Key ）： 用于两表间建立关系，需要指定引用主表的那列

- 创建约束语法

ALTER TABLE 表名

ADD CONSTRAINT 约束名  约束类型  （约束字段）

Alter  table  emp_test2；

Add  constraint  PK_EMP_EMP_ID  primary key (EMPLOYEE_ID);//加主键

DROP -- 删除数据库对象

drop table 表名;
drop table EMP;//删表

- DML(date manipulation language)数据操纵语言.
- 对数据库里的数据进行操作的语言。常用的是DML语句对数据进行增删改查。
- 常用的包括：select ：查询 insert：插入 update：修改 delete：删除

- SELECT -- 查询

SELECT <列名/表达式>

FROM <表名>

[WHERE<查询条件表达式>][GROUP BY<分组的列名>][HAVING <分组后的条件表达式，必须结合GROUP BY 子句使用>][ORDER BY <排序的列名>[ASC或DESC]]

- 设置别名：

SELECT <表别名.列名 AS 列别名> FROM <表名表别名>

设置表别名：

select t.employee_id, t.hire_date from employees t

设置列表名

select t.employee_id, t.hire_date AS Hire from employees t

正在上传…重新上传取消

select t.employee_id, t.hire_date AS "Hire" from employees t

正在上传…重新上传取消

- 注意：
- 1）对于表，通常情况下会给表设置别名；但对于字段，则会根据需要设置别名
- 2）设置列表名时，AS 可以省略
- 3）列别名可以加双引号，也可不加双引号。但显示结果中，字段名称不一致。如，查询员工表的所有员工的工号，入职日期（并显示为Hire）

Where查询语句

BETWEEN 2200 AND 2400//在2000和2400之间

IN (101,103)//等于101或103

使用LIKE做模糊匹配

可使用 % 或 _ 作为通配符

% 代表0或多个字符

_ 代表一个单个字符

- 若要对通配符本身进行模糊，则要使用escape关键字定义转义符

Select * from test_where where name like ‘%\%%’ escape ‘\';

正在上传…重新上传取消

- 取前N条记录

SELECT last_name, department_id, salary FROM employees WHERE ROWNUM <= 5

ORDER BY –排序

- 使用order by 子句进行排序。

用ASC关键字表示升序，缺省的话，则默认为升序排序

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ASC;

使用DESC关键字，降序排序

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date desc;

- 可以按字段名排序，也可以使用字段别名排序

SELECT last_name , job_id, department_id , hire_date "Hire" FROM employeesORDER BY "Hire" ;

- 可以单一字段排序，也可以多个字段排序，且排序规则也可以不一样

SELECT last_name, department_id, salary FROM employees ORDER BY department_id ASC, salary DESC;

正在上传…重新上传取消

WHERE –条件限制

逻辑操作符	意义
AND	所有条件都满足，返回true
OR	只要有一个条件满足，返回true
NOT	如果条件是false,返回true

字符串函数

- 字符函数接受字符输入并返回字符或数值

函数	输入	输出
initcap(char)	initcap('hello world')	Hello World
lower	lower('Hello World')	hello world
upper	upper('Hello World')	HELLO WORLD
replace(char,old,new)	replace('HelloWorld','H','M')	MelloWorld
instr(char,m,n)	INSTR('HelloWorld', 'W')	6
substr(char,m,n)	SUBSTR('HelloWorld',1,5)	Hello
concat(expr1,expr2)	CONCAT('Hello', 'World')	HelloWorld
lpad	LPAD(salary,10,'*')	*****24000
rpad	RPAD(salary, 10, '*')	24000*****
trim（只能截取字符串前后的空格）	TRIM('H' FROM 'HelloWorld')	elloWorld
trim	TRIM(' HelloWorld')	HelloWorld
trim	TRIM('Hello World')	Hello World
length	LENGTH('HelloWorld')	10

- 正在上传…重新上传取消

正在上传…重新上传取消

数学函数

- 数字函数接受数字输入并返回数值结果

函数	输入	输出	算法
Abs(n)	abs(-15)	15	取绝对值
Ceil(n)	ceil(44.778)	45	大于等于44.778的最小整数
Floor(n)	floor(100.2)	100	对n向负方向舍入取整
Power(m,n)	power(4,2)	16	4的平方
Mod(m,n)	mod(10,3)	1	除10取余数1
Round(m,n)	round(100.256,2)	100.26	取100.256后小数位2位（进行四舍五入）
Trunc(m,n)	trunc(100.256,2)	100.25	取100.256后小数位2位（不进行四舍五入）

日期函数

- 日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果
- 常用日期函数：

- ADD_MONTHS(日期,增值):追加N个月份(月份要是英文)
- Extract:获取两个日期之间的具体时间间隔，可以用extract()函数
- MONTHS_BETWEEN(日期，日期):两日期相差多少月
- LAST_DAY(日期):返回当月最大天数
- NEXT_DAY(日期,星期)：获得某一日期后的第一个星期几的值
- ROUND(日期,’YEAR’/’MONTH’)四舍五入
- TRUNC(日期’YEAR’/’MONTH’) :舍去某一日期类型
- 正在上传…重新上传取消

select extract(day from hire_date ) day from employees'

正在上传…重新上传取消

SELECT TO_CHAR (SYSDATE, 'DAY','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''') FROM DUAL

:NLS_DATE_LANGUAGE 表示你要把时间以什么区域方式显示，比如是要显示MONDAY还是要显示星期一

正在上传…重新上传取消

转换函数

- 转换函数将值从一种数据类型转换为另一种数据类型
- 常用的转换函数:
  - TO_CHAR
  - TO_DATE
  - TO_NUMBER
- SELECT TO_DATE('2016-01-06' , 'yyyy-mm-dd') as time FROM dual;

正在上传…重新上传取消

- SELECT TO_NUMBER('100') as num FROM dual;

正在上传…重新上传取消

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') as t_char FROM dual;

正在上传…重新上传取消

Select last_name, to_char(salary, 'L9,999,000,00') From employees e;

正在上传…重新上传取消

commit；提交若不写这个数据可以回滚不插入（delete可以回滚，truncate，drop不能回滚）

- 删除表语法
- drop table 表名;
- 举例：

Drop table emp_test2;删表

插入时间格式数据

insert into 表名(列名,日期列) values(值,to_date('1988-11-11','yyyy-mm-dd'));

函数	用途
NVL (expr1, expr2)	如果expr1为空，这返回expr2
NVL2 (expr1, expr2, expr3)	如果expr1为空，则返回expr3（第2个结果）否则返回expr2
NULLIF (expr1, expr2)	如果expr1和expr2相等，则返回空
COALESCE (expr1, expr2, ..., exprn)	如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到一个不为NULL的值或者如果全部为NULL，也只能返回NULL

Nvl如果不为空返回第一个值反之第二个值

select nvl(null,2)from dual;//结果为2

Select nvl(1,2)from dual;//结果为1

Select emp,ename,nva(sal,0)+nvl(comm,0) from emp where empno='121212'

常用的分析函数

常用的聚合函数

- SUM:求和
- AVG：求平均值
- MAX、MIN：求最大最小值
- COUNT：计数

HAVING子句：对分组后的组数据进行条件限制

Where 和 having 的区别

- WHERE 子句从数据源中去掉不符合其搜索条件的数据

HAVING子句从分组后的数

据中去掉不符合其组搜索条件的各组数据行

- HAVING必须与GROUP BY搭配使用，且一定是在GROUP BY子句后面

错误SQL:

SELECT t.department_id, t.job_id ,avg(t.salary) as av g_sal

FROM employees t

group by t.department_id;

- 注意：Group BY 后面的字段为分组字段，Select查询的字段，除了固定数据及聚合函数之外的字段，都必须包括在分组字段中！

INSERT—插入数据

INSERT [INTO] <表名> [字段列表] VALUES <值列表>

insert into dept (DEPTNO, DNAME, LOC) values (50, 'Training', ' SHANGHAI')

正在上传…重新上传取消

- 注意事项：
- 插入数据时，字段列表和值列表要一一对应，数量保持一致；
- 插入数据时，每个数据值的数据类型、精度和小数位数必须与相应的列匹配；
- 插入数据时，如果在设计表的时候就指定了某列不允许为空，则必须插入数据；
- 插入数据时，具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值

Update –更新数据

UPDATE <表名> SET <列名 = 更新值,…> [WHERE <更新条件>]

update dept

set DNAME = 'Training Dept', LOC = 'NEW YORK'

where DEPTNO = 50

正在上传…重新上传取消

Delete –删除数据

DELETE FROM <表名> [WHERE <删除条件>]

delete from dept where DEPTNO=50

正在上传…重新上传取消

TCL事务控制语言

- TCL(Transaction Control language)事务控制语言
- 常用的包括
  - commit：提交
  - rollback：回滚

DCL数据库控制语言

- DCL(date control language):数据库控制语言
- 用于定义数据库用户的权限。
- 常用的包括
  - grant ：赋权
  - revoke ：收回权限

Grant –赋权

- GRANT 命令可用于为用户分配权限或角色

GRANT CONNECT TO user_name;

----CONNECT角色允许用户连接至数据库，并创建数据库对象

GRANT RESOURCE TO user_name;

---- RESOURCE角色允许用户使用数据库中的存储空间

- 授予用户 user_name 操作table_name表对象的权限

GRANT SELECT ON table_name TO user_name;

----允许用户查询 table_name表的记录

GRANT UPDATE ON table_name TO user_name;

----允许用户更新 table_name表中的记录

GRANT ALL ON table_name TO user_name;

----允许用户插入、删除、更新和查询 table_name表中的记录

REVOKE – 收回权限

- REVOKE 命令可用于为用户回收权限或角色

REVOKE CONNECT FROM user_name;

----回收CONNECT角色允许用户连接至数据库，并创建数据库对象

REVOKE RESOURCE FROM user_name;

----回收RESOURCE角色允许用户使用数据库中的存储空间

- 授予用户 user_name 操作table_name表对象的权限

REVOKE SELECT ON table_name FROM user_name;

----回收用户查询 table_name表的记录的权限

REVOKE UPDATE ON table_name FROM user_name;

----回收用户更新 table_name表中的记录的权限

REVOKE ALL ON table_name FROM user_name;

----回收用户插入、删除、更新和查询 table_name表中的记录的权限

Distinct 查询去重复

SQL upper()函数实例代码教程 - 返回字符串str，根据当前字符集映射的所有字符更改为大写。

例子解释：

upper（字段名）：是将字段的内容转换成大写进行返回注意：字段的数据类型必须是字符串类型

upper（字符串）：Upper('aabbcc')返回的是‘AABBCC‘

经典语句

列出薪金水平处于第四位的雇员信息

- select * from (select rownum num, e.* from (select * from emp order by sal desc) e) where num = 4;

select * from emp order by sal desc

正在上传…重新上传取消

select rownum num, e.* from (select * from emp order by sal desc) e （select rownum num 新建一列num排序，e.*前面搜索的表格）

正在上传…重新上传取消

select * from (select rownum num, e.* from (select * from emp order by sal desc) e) where num = 4;

正在上传…重新上传取消

和 UNION 指令类似，INTERSECT 也是对两个 SQL 语句所产生的结果做处理的。

不同的地方是，

UNION 基本上是一个 OR (如果这个值存在于第一句或是第二句，它就会被选出)，

而 INTERSECT 则比较像 AND ( 这个值要存在于第一句和第二句才会被选出)。

UNION 是联集，而 INTERSECT 是交集

INTERSECT 的语法如下：

[SQL语句 1]

INTERSECT

[SQL语句 2];

正在上传…重新上传取消

请注意，在 INTERSECT 指令下，不同的值只会被列出一次。

decode()函数简介

主要作用：将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；

使用方法：

Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)的理解如下：

if （条件==值1）

then

return(翻译值1)

elsif （条件==值2）

then

return(翻译值2)

......

elsif （条件==值n）

then

return(翻译值n)

else

return(缺省值)

end if

注：其中缺省值可以是你要选择的column name 本身，也可以是你想定义的其他值，比如Other等；

正在上传…重新上传取消

Rownum<10 获取前10条数据

update gl_balances set W_UPDATE_DT=to_date('2019-12-13 16:40:00','yyyy-mm-dd HH24:MI:SS')改时间

删主键

ALTER TABLE 表名 DROP CONSTRAINT 主键名

长的不像程序员的程序猿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
SQL-小笔记

查询条件表达式>][GROUP BY<分组的列名>][HAVING <分组后的条件表达式，必须结合GROUP BY 子句使用>][ORDER BY <排序的列名>[ASC或DESC]]注意：Group BY 后面的字段为分组字段，Select查询的字段，除了固定数据及聚合函数之外的字段，都必须包括在分组字段中！注：其中缺省值可以是你要选择的column name 本身，也可以是你想定义的其他值，比如Other等；插入数据时，具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值。
复制链接

扫一扫