数据库基础语句实例

编程笔记数据库

用户与模式

用户：对数据库的访问，需要以适当用户身份通过验证，并具有相关权限来完成一系列动作。
模式(schema)：是某个用户拥有所有对象的集合。具有创建对象权限并创建了对象的用户称为拥有某个模式。
注意：创建数据库对象（视图，表等）的任一用户都拥有一个以该用户名称开头的模式，且被视为模式用户。

新建用户

-- 新建一个叫user的用户，密码为password
CREATE USER user IDENTIFIED BY password;

修改用户密码

-- 将user的密码更改为password1
ALTER USER user IDENTIFIED BY password1;

锁定/解锁密码

-- 将user的密码锁定
ALTER USER user ACCOUNT LOCK;
-- 将user的密码解锁
ALTER USER user ACCOUNT UNLOCK

删除用户

DROP USER user CASCADE;

注：drop user xxx必须是这个用户下面没有任何对象，这样才可以使用这个命令，否则就会报错；如果用户下面有对象，就得用drop user xxx cascade来删除这个用户以及这个用户下的所有对象。

数据库权限

系统权限：允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等（对用户而言）

DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE：拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对象权限：允许用户操纵一些特定的对象，如读取视图，可更新某些列、执行存储过程等（是针对表或视图而言的）

给用户授权

GRANT CONNECT, RESOURCE TO user;
GRANT CONNECT, RESOURCE, DBA TO user;
-- 以下按英文字面意义理解即可
GRANT CREATE SESSION, CREATE TABLE TO user;

创建角色

-- 创建名为manager的角色
CREATE ROLE manager;

给角色授权

-- 给manager赋予创建表格和创建使徒的授权
GRANT CREATE TABLE, CREATE VIEW TO manager;

把角色授权给用户

-- 把manager这个角色赋予给用户user
GRANT manager TO user;

赋予用户查询表的权限

/*
假设我们拥有表 employees
下面语句赋予user查询employees的权限
*/
GRANT select ON employees TO user;

赋予用户和角色更新表指定字段的权限

/*
假设我们拥有表 employees
表中至少有salary和last_name两个字段
下面语句赋予user和manager更改employees中salary和last_name两个字段的权限
user：之前创建的用户
manager：之前创建的角色
*/
GRANT  update (salary, last_name)
ON     employees
TO     user, manager;

赋予用户拥有赋予其他用户的权限

-- 差不多就是让别人也有给别人权限的能力
-- user可以给别人添加在表employees中选取和添加的能力
GRANT  select, insert
ON     employees
TO     user
WITH GRANT OPTION;

赋予所有用户某些权限

-- 赋予所有用户查询表employees的权限
GRANT select ON employees TO PUBLIC;

收回权限

-- 从user里撤回从employees里查询和添加的权限
REVOKE  select, insert
ON      employees
FROM    user;

表操作

建表

/*
新建表dept
字段deptno是长度为2的数字
字段dname是长度为14的字符
字段loc是长度为13的字符
字段create_date是日期
*/
create table dept (
    deptno number(2)
    ,dname varchar2(14)
    ,loc varchar2(13)
    ,create_date date);

-- 查询表dept的属性，在这里，您会看到列名称，然后是列类型。
describe dept;

-- 下面是上面的缩写格式
desc dept;

-- 在dept中插入值
insert into dept values (1,'yunjisuan','beijing','28-8月 -22');
insert into dept values (2,'caiwubu','shanghai','27-8月 -22');
insert into dept values (3,'jishubu','shenzhen','26-8月 -22');

-- 用commit来告诉数据库上面的插入可以正式插入
commit;

-- 从dept里查询所有值
select * from dept;

使用字段默认值建表

-- 表dept2中create_date的默认值被设为了sysdate
create table dept2 (
    deptno number(2)
    ,dname varchar2(14)
    ,loc varchar2(13)
    ,create_date date default sysdate);

-- 查询sysdate格式，方便输出
select sysdate from dual;

--通过字段名进行赋值，这样就不必按顺序赋值
-- 下面是给deptno, dname, loc分别赋值为1, 'yunjisuan', 'beijing'
insert into dept2(deptno,dname,loc) values (1,'yunjisuan','beijing');
insert into dept2(deptno,dname,loc)  values (2,'caiwubu','shanghai');
insert into dept2(deptno,dname,loc)  values (3,'jishubu','shenzhen');
commit;

select * from dept2;

约束

行级约束

也叫列级约束，只能应用于一列上。

行级约束包含在列定义中，直接跟在该列的其它定义之后，用空格分隔；不必指定列名。

表级约束

可以应用于一列上，也可以应用在一个表中的多个列上。

即：如果你创建的约束涉及到该表的多个属性列，则必须创建的是表级约束（必须定义在表级上）；否则既可以定义在列级上也可以定义在表级上此时只是SQL语句格式不同而已。

与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称。

注：因为在创建列级约束时，只需将创建列约束的语句添加到该字段（列）的定义子句后面；而在创建表级约束时，需要将创建表级约束的语句添加到各个字段（列）定义语句的后面，因为并不是每个定义的字段都要创建约束，所以必须指明需要创建的约束的列名。

主键约束

-- 主键约束
-- 行级约束

-- 下面给employee_id设置了名为emp_emp_id_pk的主键约束
-- 同时下面也是一个行级约束
create table employees_pk(
    employee_id      NUMBER(6) constraint emp_emp_id_pk primary key
    ,last_name        VARCHAR2(25) NOT NULL
    ,email            VARCHAR2(25)
    ,salary           NUMBER(8,2)
    ,commission_pct   NUMBER(8,2)
    ,hire_date        DATE NOT NULL);

-- 表级约束
drop table employees_pk;
create table employees_pk(
    employee_id      NUMBER(6)
    ,last_name        VARCHAR2(25) NOT NULL
    ,email            VARCHAR2(25)
    ,salary           NUMBER(8,2)
    ,commission_pct   NUMBER(8,2)
    ,hire_date        DATE NOT NULL  
    ,CONSTRAINT emp_emp_id_pk PRIMARY KEY(employee_id));
 
-- 插入数据
insert into employees_pk values (100,'King','king@qq.com',3000,2,'29-8月 -22');
insert into employees_pk values (101,'Kochhar','Kochhar@qq.com',5000,12,'29-8月 -22');
insert into employees_pk values (102,'De Haan','Dehaan@qq.com',4200,21,'27-8月 -22');
insert into employees_pk values (103,'Hunold','Hunold@qq.com',3000,2,'29-8月 -22');
insert into employees_pk values (104,'Ernst','Ernst@qq.com',3500,12,'28-8月 -22');
insert into employees_pk values (105,'Lorentz','Lorentz@qq.com',3200,21,'29-8月 -22');
insert into employees_pk values (106,'Smith',NULL,3200,21,'29-8月 -22');
commit;

-- 主键不能为空值，插入数据失败
insert into employees_pk values (NULL,'Mith',NULL,'3200',21,'29-8月 -22');

唯一约束

-- 删除表employees_uq，防止有已经存在的表产生冲突
-- 不是必须要写
drop table employees_uq;
-- 这也是一个表级约束，给email添加了唯一约束
CREATE TABLE employees_uq(
    employee_id      NUMBER(6)
    ,last_name        VARCHAR2(25) NOT NULL
    ,email            VARCHAR2(25)
    ,salary           NUMBER(8,2)
    ,commission_pct   NUMBER(8,2)
    ,hire_date        DATE NOT NULL  
    ,CONSTRAINT emp_email_uk UNIQUE(email));

-- 插入数据   
insert into employees_uq values (100,'King','king@qq.com',3000,2,'29-8月 -22');
insert into employees_uq values (101,'Kochhar','Kochhar@qq.com',5000,12,'29-8月 -22');
insert into employees_uq values (102,'De Haan','Dehaan@qq.com',4200,21,'27-8月 -22');
insert into employees_uq values (103,'Hunold','Hunold@qq.com',3000,2,'29-8月 -22');
insert into employees_uq values (104,'Ernst','Ernst@qq.com',3500,12,'28-8月 -22');
insert into employees_uq values (105,'Lorentz','Lorentz@qq.com',3200,21,'29-8月 -22');
insert into employees_uq values (106,'Smith',NULL,3200,21,'29-8月 -22');
commit;

-- 违反唯一约束，插入数据失败（email和105号的那个冲突了）
insert into employees values (107,'Jisth','Lorentz@qq.com','3200',21,'29-8月 -22');

外键约束

create table t_customer(   	
	id number primary key  
	,name varchar2(200) not null
);

insert into t_customer values (10,'Fay');
insert into t_customer values (11,'Whalen');
insert into t_customer values (12,'Higgins');
insert into t_customer values (13,'Ciets');
commit;


-- 设置表t_customer的id为t_order中customer_id的外键
-- 如果customer_id的值不在t_customer的id中就会报错
create table t_order(   
	id number primary key   
	,content varchar2(200) not null 
	,customer_id number   
	,foreign key(customer_id) references t_customer(id)
);

insert into t_order values (1,'dingdan01',10);
insert into t_order values (2,'dingdan02',10);
insert into t_order values (3,'dingdan03',12);
insert into t_order values (4,'dingdan04',12);
commit;

-- 违反外键约束，插入数据失败
insert into t_order values (5,'dingdan05',15);

-- 删除被外键引用的表
-- 方法1
drop table t_customer; -- 直接删除失败，因为表中字段为其他表的外键
drop table t_order; -- 先删除引用过t_customer的表
drop table t_customer; -- 删除成功
-- 方法2
drop table t_customer cascade constraints; -- 自动连有外键约束的一起删除

检查约束

-- 给salary添加了约束，让salary必须大于0才能插入
CREATE TABLE employees_ck(
    employee_id      NUMBER(6)
    ,last_name        VARCHAR2(25) NOT NULL
    ,email            VARCHAR2(25)
    ,salary           NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0)
    ,commission_pct   NUMBER(8,2)
    ,hire_date        DATE NOT NULL);

  
insert into employees_ck values (100,'King','king@qq.com',3000,2,'29-8月 -22');
insert into employees_ck values (101,'Kochhar','Kochhar@qq.com',5000,12,'29-8月 -22');
insert into employees_ck values (102,'De Haan','Dehaan@qq.com',4200,21,'27-8月 -22');
insert into employees_ck values (103,'Hunold','Hunold@qq.com',3000,2,'29-8月 -22');
insert into employees_ck values (104,'Ernst','Ernst@qq.com',3500,12,'28-8月 -22');
insert into employees_ck values (105,'Lorentz','Lorentz@qq.com',3200,21,'29-8月 -22');
insert into employees_ck values (106,'Smith',NULL,3200,21,'29-8月 -22');
commit;

-- 违反检查约束条件，插入数据失败
insert into employees_ck values (107,'Jisth','Lorentz@qq.com',0,21,'29-8月 -22');

联合约束和非空约束

联合约束必须使用表级约束来声明
非空约束(not null)必须使用行级约束

--例如联合唯一约束：
create table student(   
	id number primary key,  
	class varchar2(50) not null,   
	name varchar2(50) not null,   
	unique(class, name)
);

增加字段

-- 新建表t_user
create table t_user(   
	id number constraint user_id_pk primary key,   
	name varchar2(100),   
	salary number
);

-- 查看表属性
desc t_user;

-- 给t_user添加数据类型为date的birthday字段
alter table t_user add birthday date;

desc t_user;

删除字段

alter table t_user drop column birthday;
desc t_user;

给表添加约束

-- 给t_user的name添加唯一约束，约束名为user_name_uq
alter table t_user add constraint user_name_uq unique(name);

删除表中的约束

-- 给t_user删除名为user_name_uq的约束
alter table t_user drop constraint user_name_uq;

修改表名

-- 把t_user重命名为mytest
rename t_user to mytest;
-- 把mytest重命名为t_user
rename mytest to t_user;

修改字段长度

alter table t_user modify name varchar2(200);
desc t_user;

删除表（表结构和数据都被删除）

drop table t_user;

-- 报错，因为表没了
desc t_user;

清空表（表结构还在，数据被删除）

truncate table dept;

-- 正常显示
desc dept;

-- 空
select * from dept;

创建索引

CREATE TABLE NEW_EMP
(employee_id NUMBER(6) PRIMARY KEY
    ,first_name  VARCHAR2(20)
    ,last_name   VARCHAR2(25)
);
CREATE INDEX idx_emp_id_last_name ON NEW_EMP(employee_id, last_name);

删除索引

DROP INDEX idx_emp_id_last_name;

查看索引信息

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'NEW_EMP';

DML

-- 先新建个表
create table employees(
    employee_id      NUMBER(6) constraint emp_emp_id_pk primary key
    ,last_name        VARCHAR2(25) NOT NULL
    ,email            VARCHAR2(25)
    ,salary           NUMBER(8,2)
    ,commission_pct   NUMBER(8,2)
    ,hire_date        DATE NOT NULL);

查询数据

select * from employees;

查询指定字段

-- 只从employees查询last_name和salary两列
select last_name ,salary from employees;

新增数据

-- 新增数据
insert into employees values (200,'King','king2@qq.com',3000,2,TO_DATE('8月 03, 2022', 'MON DD, YYYY'));
insert into employees values (201,'Kochhar','Kochhar2@qq.com',5000,12,'29-8月 -22');
insert into employees values (202,'De Haan','Dehaan2@qq.com',4200,21,'27-8月 -22');
insert into employees values (203,'Hunold','Hunold2@qq.com',3000,2,'29-8月 -22');
insert into employees values (204,'Ernst','Ernst2@qq.com',3500,12,'28-8月 -22');
insert into employees values (205,'Lorentz','Lorentz3@qq.com',3200,NULL,'29-8月 -22');
insert into employees values (206,'Smith',NULL,3200,21,'29-8月 -22');
insert into employees values (207,'Smith','',3200,21,TO_DATE('8月 03, 2022', 'MON DD, YYYY'));
commit;

select * from employees;

修改数据

-- 让所有的 salary都减少200
update employees set salary=salary-200;

-- 查询employee_id为200的行
select * from employees where employee_id=200;

-- 设置employee_id为200行中salary数值减少200
update employees set salary=salary-200 where employee_id=200;

-- 查询employee_id为200的行
select * from employees where employee_id=200;

删除数据

select * from employees where salary>4000;
-- 删除salary大于4000的所有行
delete from employees where salary>4000;

select * from employees;
-- 清空表，等同于truncate
delete from employees;

运算操作

-- 数字运算
select employee_id,last_name ,salary, 12*salary+100 from employees;
select employee_id,last_name ,salary, 12*(salary+100) from employees;
-- 对于包含空值的运算操作，结果也为空值
SELECT last_name, 12*salary*commission_pct FROM   employees;

-- 拼接字段值，字段别名
SELECT last_name ||' salary is '||salary  AS "Employee Salary" FROM   employees;

-- 比较运算符
select * from employees where salary>3000;
select * from employees where last_name='Kochhar';
SELECT last_name, salary FROM employees WHERE  salary BETWEEN 2500 AND 3500 ;

-- 只选择employee_id在100、101和201中的
SELECT employee_id, last_name, salary
FROM   employees
WHERE  employee_id IN (100, 101, 201) ;

/*使用Like操作进行模式匹配
_代表一个字符
%代表0或者多个字符
*/

-- 查询首字母为大写S的名字
SELECT	*
FROM 	employees
WHERE 	last_name LIKE 'S%' ;

-- 查询第二个字母为o的名字
SELECT *
FROM   employees
WHERE  last_name LIKE '_o%' ;

-- 查询空值
SELECT last_name, commission_pct
FROM   employees
WHERE  commission_pct IS NULL ;


-- 逻辑操作符
SELECT employee_id, last_name,  salary
FROM   employees
WHERE  salary >= 5000
AND    last_name LIKE '%ch%' ;

-- 排序
-- 正序
SELECT last_name, employee_id, hire_date,salary
FROM employees
ORDER BY salary ;

-- 倒序
SELECT last_name, employee_id, hire_date,salary
FROM employees
ORDER BY salary desc ;

-- 以查询字段序号排序
SELECT   last_name, employee_id, hire_date,salary
FROM     employees
ORDER BY 4;

-- 多字段排序
SELECT last_name, employee_id, hire_date,salary
FROM employees
ORDER BY last_name,salary ;

-- 查询所有结果中前五行的信息
SELECT employee_id, last_name
 FROM employees
 ORDER BY employee_id
 FETCH FIRST 5 ROWS ONLY;

-- 查询所有结果中，先跳过前五行后，展示后面的5条数据
SELECT employee_id, last_name
 FROM employees
 ORDER BY employee_id
 OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
 
 
-- 组函数
-- 查询8月29日的平均工资，最大工资，最小工资，工资总和
SELECT AVG(salary), MAX(salary),
       MIN(salary), SUM(salary)
FROM   employees
WHERE hire_date ='29-8月 -22';

-- 查看表中的所有记录数
SELECT COUNT(*)
FROM   employees;

SELECT COUNT(*)
FROM   employees
WHERE salary>3000;


-- 查询不为null的值的数据记录数
SELECT COUNT(commission_pct)
FROM   employees
WHERE  commission_pct is not null ;

-- 使用DSITINCT关键字去重
SELECT COUNT(DISTINCT last_name)
FROM   employees;


-- 分组
SELECT   last_name, AVG(salary)
FROM     employees
GROUP BY last_name ;


-- 分组+排序
SELECT   employee_id, last_name, SUM(salary)
FROM     employees
WHERE	 employee_id > 200
GROUP BY employee_id, last_name 
ORDER BY employee_id;

-- 错误示例
SELECT   last_name, AVG(salary)
FROM     employees;

SELECT   employee_id, last_name, SUM(salary)
FROM     employees
GROUP BY employee_id; 

-- 使用Having语法对分组后的显示结果进行筛选
SELECT   last_name, MAX(salary)
FROM     employees
GROUP BY last_name
HAVING   MAX(salary)> 3000 ;

-- 表连接
select * from employees;
select * from employees_pk;

-- 全连接:FULL JOIN 它表示返回两个表全部记录,表示两个表的所有记录
select * 
from employees em1
full join employees_pk em2
on em1.employee_id=em2.employee_id;

-- 内连接:INNER JOIN 它表示返回两个表或记录集连接字段的匹配记录,表示两个表中相互包含的部分
select * 
from employees em1
inner join employees_pk em2
on em1.employee_id=em2.employee_id;

-- 左连接:左外连接又叫左连接,意思是包含左边表所有记录,右边所有的匹配的记录,如果没有则用空补齐.换句话说就是,列出左边表全部的,及右边表符合条件的,不符合条件的以空值代替
select * 
from employees em1
left join employees_pk em2
on em1.employee_id=em2.employee_id;

-- 右连接
select * 
from employees em1
right join employees_pk em2
on em1.employee_id=em2.employee_id;

如果觉得文章对你有用，请随意赞赏

数据库

数据库基础语句实例

https://frostlock.cn/archives/shu-ju-ku-ji-chu-yu-ju-shi-li

作者

EDNC_锁寒大帝

发布于

2022-08-30

更新于

2024-10-27

许可协议

CC BY 4.0

数据库基础语句实例

用户与模式

新建用户

修改用户密码

锁定/解锁密码

删除用户

数据库权限

给用户授权

创建角色

给角色授权

把角色授权给用户

赋予用户查询表的权限

赋予用户和角色更新表指定字段的权限

赋予用户拥有赋予其他用户的权限

赋予所有用户某些权限

收回权限

表操作

建表

使用字段默认值建表

约束

行级约束

表级约束

主键约束

唯一约束

外键约束

检查约束

联合约束和非空约束

增加字段

删除字段

给表添加约束

删除表中的约束

修改表名

修改字段长度

删除表（表结构和数据都被删除）

清空表（表结构还在，数据被删除）

创建索引

删除索引

查看索引信息

DML

查询数据

查询指定字段

新增数据

修改数据

删除数据

运算操作

作者

发布于

更新于

许可协议

评论