数据库命令常用

 

--删除表
drop table tb_person;

--删除记录delete,删除所有的记录
delete tb_person;

--彻底删除表中所有记录,释放表空间(oracle)
truncate table tb_csii;

--删除数据库  数据文件和日志文件会删除
drop database test;

--删除一个字段(列) 表中所有记录的该字段的值都删了
alter table tb_person drop column persiondesc;
alter table tb_student drop column stuno;

--删除一行
--delete from tb_student where stuId=1;

--删除约束
alter table tb_emp drop constraint dk_sex;

--删除主键
alter table tb_test drop constraint PK__tb_test__1920BF5C;

--修改字段  修改字段的数据类型
--对于值可以转换的字段可以修改
--对于值不可以转换(varchar--> int) 必须先将表中的数据清空 再修改
alter table tb_person alter column personage varchar(10);
alter table tb_person alter column personname int;

-------------------------------------------------
----oracle中添加/修改字段------
--给tb_book表增加一个字段 不需要column关键字
alter table tb_book add book_desc varchar2(100);
--给字段修改数据类型
alter table tb_book modify  book_desc number(10);
--删除字段
alter table tb_book drop column book_desc;

--给tb_test表在id字段上创建名为tbtestid的索引
create index tbtestid on tb_test(id);

--查找表的所有索引（包括索引名，类型，构成列）表名需大写并加单引号
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name ='TB_TEST';

--删除索引
drop index tbtestid;

--创建表
--出版社
create table tb_publish(
publish_id  int primary key,
publish_name varchar2(20) not null,
publish_loc varchar2(50)    
);
select * from tb_publish;
--书籍
create table tb_book(
book_id int primary key,
book_name varchar2(30) not null,
book_author varchar2(20) not null,
book_price float not null,
book_date date not null,
book_num number,
publish_id int
);

--给两张表添加关键 增加外键
alter table tb_book add constraint fk_book_publish foreign key(publish_id) references tb_publish(publish_id);

--添加记录
insert into tb_book(book_id,book_name,book_author,book_price,book_date,publish_id) values(seq_book.nextval,'射雕英雄传','金庸',50,to_date('2012-12-12','yyyy-MM-dd'),3);

----oracle序列
--创建序列
create sequence seq_publish  --这一句必写 其余都可以省略
start with 1  --序列初始化值
increment by 1  --每次改变量 该值可以取负值
maxvalue 999999 --序列的最大值
minvalue 1      --最小值
cache 10        --缓存
cycle           --循环

--获取序列的当前值
select seq_publish.currval from dual;

--获取序列的下一个值
select seq_publish.nextval from dual;

--复制表结构及其数据
create table new_table as select * from tb_user;

--只复制表结构
create table new_table as select * from tb_user where 1=9;

--复制表数据
--如果表结构相同
insert into new_table select * from tb_user;
--如果表结构不相同
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old
pasting

oracle创建用户，赋予权限/角色，撤销权限，权限转移，删除用户等操作（sqlplusw）
conn system/abc;
create user xiaohong identified by m123
grant connect to xiaohong;  //连接角色
grant resource to xiaohong; //资源角色
grant dba to xiaoming;      //数据库管理员角色,一般不给
connect xiaohong/m123;

修改密码
password 用户名
alter user 用户名 identified by 新密码;

给用户解锁：alter user 用户名 account unlock;

-------------------------------------------------

-- 将tb_emp表中所有人的加1岁
update tb_emp set empAge=empAge+1 where empName='张三';
update tb_emp set empAge=empAge+1 ;--批处理sql语句就相当于把这些事捆绑成一个事物

--将表中的字段重命名,修改字段名  用存储过程
sp_rename 'tb_student.stuBirthday','stubirthday','column';
sp_rename 'tb_student.stuDesc','studesc','column';
sp_rename 'tb_person.persionid','personid','column';

--修改表名
sp_rename 'tb_person','person','object';

--插入记录,插入记录的时候 非空字段一定要给值
insert into tb_student(stuname,stuage,stubirthday) values('zhangsan',45,'1967-12-12');

--查询tb_student表
select * from tb_student;

--查询id为1人得姓名和年龄
select stuname ,stuage from tb_student where stuid=1;

--修改列宽,将names最大列宽增加到10个字符（假定原为6个字符）。
alter table tb_card alter column names varchar(10);

--DB2将表ot_trans的字段trans_id修改为6（假定原来为4）
alter table ot_trans alter trans_id set data type varchar(6);

--添加字段(列)， 为该表增加1列NAMEs
alter table tb_card add NAMEs varchar(20);

--修改(添加)主键
alter table tb_person add constraint pk_person1 primary key(personid);

--添加唯一约束
alter table tb_depart add constraint uk_departname unique(depp;artName);

-- 添加检查约束  年龄必须在18~50
alter table tb_emp add constraint ck_age check(empAge>18 and empAge<50
insert into tb_emp values('小强','男',55,'湖北武汉','12345678901',1);

--给两张表添加外键关联
--将tb_emp表中的depId字段外键关联tb_depart表中的departId字段
--关联表的字段如果省略 就是指该表的主键
--一张表中可以很多个外键
alter table tb_emp add constraint fk_emp_depart foreign key(depId) references tb_depart(departId);

--添加约束
alter table tb_emp add constraint dk_sex default('男') for empSex;
create table tb_test(
tid int,
tname varchar(10),
tage int default 17
)
insert into tb_emp(empName,empAge,empAddress,empTelephone,depId) values('大强',45,'湖北武汉','12345678901',1);

--查询部门人数小于2的部门id
select depId from tb_emp group  by depId having count(*)<2;

--查询每个部门平均年龄小于30的部门id
select depId from tb_emp group by depId having avg(empage)<30;

--查询显示出所有男学生的姓名及其名子的长度
select username,len(username) as 名字长度 from tb_user where userSex='男';

--随机取三条记录
select top 3 * from tb_user order by newID();

--查询数据库中所有的表名
select name from sysobjects where type='U';

--删除重复记录
delete from tb_user where userId not in (select max(userId) from tb_user group by userName,userPass);

--查询不重复记录
select distinct userName,userPass from tb_user;

--复制表结构
--1.如果新表不存在
select * into newtable from tb_user;--复制表结构及其数据       
select * into newtable from tb_user where 1=2;--只复制表结构
--2.如果存在
insert into newtable select * from oldtable  ---表结构一致
insert into newtable(列名1,列名2,.....) select 列名1,列名2....from oldtable --表结构不一致

--创建一个数据库  指定配置文件和日志文件
create database student on(
name='student_data',
filename='d:\test\student_data.mdf',
size=3,
maxsize=10,
filegrowth=1
)
log on(
name='student.log',
filename='d:\test\student_log.ldf',
size=1,
maxsize=5,
filegrowth=1
);

--在创建表的时候 通常情况下都会给定一个主键
--主键一般都是数值类型的
--主键一般都是自增长 在向表中插入记录的时候 主键的值由自增长而来 不需要我们给定
--当主键自增长了以后  一般不会改变主键
create table tb_student(
 stuid int identity(1,1) primary key,--定义主键 自增长
 stuname varchar(10) not null, --not null 非空
 stuage int not null,
 stuBirthday datetime not null,
 stuDesc  varchar(100)
);

SQL Server中的分页语句：
--pageSize=3
select top 3 * from tb_user;--第一页
select top 3 * from tb_user where userId not in (select top 3 userId from tb_user order by userId);--第二页
select top 3 * from tb_user where userId not in (select top 6 userId from tb_user order by userId);--第三页
--第二页
select top 3 * from tb_user where userId not in (select top (3*(2-1)) userId from tb_user order by userId);
--通式
select top pageSize * from tb_good where goodId not in (select top (pageSize*(currentPage-1)) goodId from tb_good order by goodId);

Orcal中的分页语句：
--第一页
select * from (select rownum as num,b.* from tb_book b) rs where rs.num>0 and rs.num<=3;
--第二页
select * from (select rownum as num,b.* from tb_book b) rs where rs.num>3*(2-1) and rs.num<=3*2;
--分页通式
select * from (select rownum as num,b.* from 表名 b) rs where rs.num>pageSize*(currentPage-1) and rs.num<=currentPage*pageSize;

--分组并排序
select * from (select a.*,row_number() over (partition by a.name order by a.id asc) rm from tb_csii a where a.name = '222222') where rm <= 10;

--rownum排序（rownum的编号不易控制）
select a.*,rownum from tb_csii a where a.name = '222222' and rownum < 10;

--oracle数据库实用查询
--查指定用户（例如scott）用到的表空间（实用）
select T.OWNER 用户名,T.TABLE_NAME 表名,t.TABLESPACE_NAME 表空间名 From dba_tables t Where t.OWNER = '大写用户名';
--查询指定用户的默认表空间、临时表空间
Select USERNAME 用户名,default_tablespace 默认表空间,temporary_tablespace  临时表空间 from dba_users  Where username = 'SCOTT';
--查看当前用户每个表占用空间的大小
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
--查看每个表空间占用空间的大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name;
--查看Oracle当前用户下的用户名、默认表空间
select username,default_tablespace from user_users;
--查看当前用户的角色
select * from user_role_privs;
--查看用户下所有的表，及其表所在的表空间等信息（实用）
select * from user_tables;

--查询oracle各表空间占用的磁盘大小,需DBA权限(实用)
select tablespace_name,sum(bytes)/1024/1024 from dba_segments group by tablespace_name;

--释放指定表所占的表空间(注:并不能释放所占磁盘空间),慎用
alter table mptransstat deallocate unused keep 0;

--查询表占用空间
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type ='TABLE' group by segment_name;

--查看索引占用空间
select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' group by segment_name;

--Oracle查询当前用户下的表个数
select count(*) from tabs;

--DB2查看指定表所在的表空间名
select tabname, tbspace from syscat.tables where tabname='OT_TRANS';

--DB2查看缓冲池信息
SELECT BPNAME,NPAGES,PAGESIZE FROM SYSCAT.BUFFERPOOLS;

--DB2创建缓冲池
CREATE BUFFERPOOL EPAYBUFFERPOOL IMMEDIATE  SIZE 250 PAGESIZE 8 K ;

--DB2修改缓冲池
ALTER BUFFERPOOL EPAYBUFFERPOOL IMMEDIATE SIZE -1;

2014/10/28 星期五