备忘——数据库最常用操作 SQL语言总结阿柠xn-

需要注意的是关键字部分，其他内容需要根据自己的表的而适当调整

用户和角色以及赋于权限（增，删，对用户基本操作）

创建用户

CREATE USER dbowner IDENTIFIED BY dbowner; 或 CREATE USER 'user_name'@'host'  IDENTIFIED BY  'password'；  user_name：要创建用户的名字。 host：表示要这个新创建的用户允许从哪台机登陆，如果只允许从本机登陆，则填　‘localhost’ ， 如果允许从远程登陆，则填 ‘%’ password：新创建用户的登陆数据库密码，如果没密码可以不写。 

删除用户：

DROP USER user_name; 

设置与更改用户密码：

SET  PASSWORD  FOR  ‘username’@‘host’ = PASSWORD(‘newpassword’);   如果是设置当前用户的密码: SET  PASSWORD = PASSWORD('newpassword'); 如： SET  PASSWORD = PASSWORD(‘123456’); 

创建角色

create role dbrole_t； 

授权

grant （权限名） to  （角色名or用户名）; grant (角色名) to   （用户名）; 

示例

GRANT DBA TO dbowner; GRANT SELECT ON students TO dbrole_t; GRANT SELECT, INSERT, DELETE ON operate_reports TO dbrole_t; GRANT EXECUTE ON   函数名或存储过程名  TO dbuser_t; 

撤销授权：

REVOKE   权限名  ON  表名  FROM  ‘用户名’@‘host’; 或 REVOKE   权限名  ON  表名  FROM  用户名; 

注意：
若授予权利是这样写： GRANT SELECT ON . TO ‘zje’@‘%’；
则用 REVOKE SELECT ON zje.aaa TO ‘zje’@‘%’；是不能撤销用户zje 对 zje.aaa 中的SELECT 权利的。

反过来 GRANT SELECT ON zje.aaa TO ‘zje’@‘%’；授予权力
用 REVOKE SELECT ON . FROM ‘zje’@‘%’；也是不能用来撤销用户zje 对zje库的aaa表的SELECT 权利的

创建表（带约束条件）

示例：

create table student(        studentid number(2) primary key, --主键约束        studentname varchar(10) not null, --非空约束        studentsex char(2) check(studentsex='男' or studentsex='女'), --check约束        studenttel number(11) unique, --唯一性约束        studentaddress varchar(50) default '上海', --默认值约束        classid number(2) references classinfo(classid) --外键约束 ); 

如果一个字段有多个约束呢，手动再增加一波

增加约束

主键约束

alter table Student  add constraint PK_Student_StuId primary key(StuId); --student是表名； PK_Student_StuId是约束名，是自己起的，不起的话系统一会自动分配； --stuid指出哪个字段是主键。 

检查约束

 alter table Student add constraint CK_Student_Gender check(gender='男' or gender='女'); alter table Student add constraint CK_Student_Age check(Age>=0 and Age<=100); 

唯一性约束

alter table Student add constraint UQ_Student_StuName unique(StuName); 

默认值约束

alter table （表名）add constraint （约束名）default '未填写' for （字段名）; --也可以这样 alter table Student Modify Address varchar(50) default '地址不详'; alter table Student Modify JoinDate Date default sysdate; 

外键约束

alter table Student add constraint FK_Student_StuCLass_ClassId   foreign key(ClassId) references StuClass(ClassId); 

序列操作（创建，删除，获取）

示例：（提供一个number(6)的序列）
创建序列：

create sequence seq_id increment by 1 --每次跳几 start with 1 --从几开始 maxvalue 999999 --最大值 minvalue 1; --最小值 

删除序列：

DROP SEQUENCE 序列; 

获取序列：

SELECT 序列名.NEXTVAL FROM DUAL 

索引操作（创建，删除，查询，禁用和重建）

创建单一索引：

Create Index <Index-Name> On <Table_Name>(Column_Name); 

创建复合索引：

Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。  　　select * from emp where deptno=66 and job='sals' ->走索引。  　　select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引  　　select * from emp where deptno=66 ->走索引。  　　select * from emp where job='sals' ->进行全表扫描、不走索引。 

如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引，将会进行全表扫描。

删除：
drop index 索引名;

查询：

--根据索引名，查询表索引字段 select * from user_ind_columns where index_name='索引名'; --根据表名，查询一张表的索引 select * from user_indexes where table_name='表名'; 

禁用和重建索引：

--禁用索引 alter index 索引名 unusable; --重新启用索引，重建索引  alter index 索引名 rebuild; 

查看当前用户下的各种信息

查看当前用户dbowner拥有多少数据库对象？

select count(*) from all_objects t; 

查看当前用户dbowner拥有哪些表。

select * from all_tables where owner='DBAOWNER'； 

查看当前用户dbowner拥有哪些序列。

select * from user_sequences; 

查看当前用户dbowner拥有哪些索引。

select index_name,index_type,table_name from user_indexes order by table_name; 

查看当前用户dbowner拥有哪些约束。

select constraint_name,constraint_type,search_condition from user_constraints; 

对表内容以及自身的操作（增，删，改，查）

增

增内容：

insert into student values("G0107","S201801200","小明","男","2001-08-09",17) ; #插入一整行值，不可错位 insert into student (student_name,gender,age) values("小李","男",19); #插入部分值 

增一个字段（一列）：

alter table  表名  add (字段  字段类型) [ default '输入bai默认值'] [null/not null] ;  例：ALTER TABLE student  ADD  gender varchar(20) Default 0 NOT NULL ; --default 和 not null 的顺序不能反过来 

删

删内容:

delete from student where student_name ="孙权" ; #删掉孙权所在的这一行 

删表中一列：

alter table student drop column gender  ; #删掉gender这一列 

删除表：

drop table student; 

改

改内容：

update student set gender ="女" where student_name ="小李" ; #将小李的gender改为女 

改列名：

ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名; 

改列类型：

ALTER TABLE 表名 MODIFY (列名VARCHAR(25)); --将列名修改为varchar（25）类型 

查

查内容：

select * from student ; #查询所有列，即整张表 select student_name,gender from student; #两列，列之间用逗号隔开 --带条件的查询 select * from student where student_name ="孙权"; #显示孙权这一行所有value select * from student where age > 14; #找出所有年龄大于14岁的学生 select * from student where student_name like "杨%" ; #匹配关键字like； 通配符%：替代0个或多个字符 select * from student order by class_id ASC ; #查询student表的信息 并按class_id排序；ASC可省略 

会一直更新完善，如有错误，欢迎留言纠正。