数据库笔霸基础题oracle/mssql/mysql（篇幅简短）

1、       数据库(共20分)

数据库中有以下两张表，各表之间的关系大家根据字面意思理解，在这里就不详细列出:

-          部门表department，包括部门编号(deptId), 部门名称(deptname)

-          员工表employee, 包括员工编号(empid), 员工姓名(empname), 部门编号(deptid), 工资(e_wage)

请按要求写出基于oracle数据库的sql语句:

1)       列出工资大于7000的员工所属部门的编号. (2分)

2)       列出员工表中的部门名称(要求使用左连接).(2分)

3)       列出员工数少于3人的部门编号. (3分)

4)       列出工资最高的员工姓名. (3分)

5)       求各部门的平均工资(2分)

6)       求各部门的员工工资总额(3分)

7)       求每个部门中的最大工资值与最小工资，并且他的最小值小于5000, 最大值大于15000的部门信息.(3分)

8)       假如数据库中有一个和员工表结构相同的空表employee2, 请用一条SQL语句将employee表中的所有记录插入到employee2表中. (2分)

 

 2、Oracle中实现序列用什么关键字？

       Sequence

3、Oracle中字符串加起来  || length

例：

update tb_cites t set t.area_code = '0'||t.area_code where length(t.area_code)=2

4、Oracle中字符串截取 substr length

例：

update tempcitys b set b.code = substr(b.code,1,length(b.code)-1) where length(b.code)=4;

 5、Oracle中字符串转成时间

to_date('2013-01-16 19:00:00', 'yyyy-mm-dd hh24:mi:ss')  

 6、Oracle中修改表的字段的长度

alter table que_income_ftp_report modify REC_LOG varchar2(2560);

 7、Oracle中创建序列(sequence)脚本

create sequence LGSN_SEQUENCE
minvalue 1
maxvalue 999999999999999
start with 1
increment by 1
cache 20;

 8、Oracle中查找被锁以及解锁

--找出被锁
SELECT s.sid||','||s.SERIAL#, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l 
where ((s.sid = l.sid AND s.username IS NOT NULL)) and (terminal = 'CHINA-C742905EE')

--解锁
alter system kill session '383,32773';

 9、Oracle中修改表字段长度

    alert table tabName modify (col1 varchar(50));

10、Oracle中增加表字段

alter table table_name add (col1 type,col2 type); 
ALTER TABLE TABLE_AAA ADD (STOCKMAN NUMBER(18))//添加一列 
COMMENT ON COLUMN TABLE_AAA.STOCKMAN IS '管理人员' //给列添加注释 
ALTER TABLE TABLE_AAA ADD CONSTRAINT FK_TABLE_AAA_R_TABLE_BBB_S FOREIGN KEY (STOCKMAN) REFERENCES TABLE_BBB (ID)//给列添加外键

-- Alter table   
alter table KS_IDENTITY  
  storage  
  (  
    next 1  
  )  
;  
-- Add/modify columns   
alter table KS_IDENTITY add SESSION_TOKEN varchar2(50);  
  
-- Add/modify columns   
alter table TB_USER_REFINFO add LAST_UPDATETIME date;  

 

11、Oracle B-tree 索引结构、如何存储及其优势

 

12、Oracle索引的内部结构 

 

13、windows docs命令行连接Oracle数据库

写道

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 6月 21 19:28:52 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> conn /as sysdba

已连接。

 

或者

SQL> conn saoc/saoc@orcl

已连接。

 

 14、windows命令行创建Oracle用户脚本[unlimited tablespace]

写道

删除用户脚本：
drop user smh_msg cascade;

新增用户脚本：
create user username identified by "123456";
grant connect to username;
grant dba to username;
grant resource to username;
grant unlimited tablespace to username;
alter user username default role connect,dba,resource;

 

 15、Windows下为Oracle创建表空间

写道

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 6月 24 13:09:02 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> conn /as sysdba
已连接。
SQL> CREATE TABLESPACE TMSDB_DATA
2 LOGGING
3 DATAFILE 'D:\TABLESPACE\TMSDB_DATA01.DBF'
4 SIZE 32M
5 AUTOEXTEND ON
6 NEXT 32M MAXSIZE 2048M
7 EXTENT MANAGEMENT LOCAL;

表空间已创建。

SQL>

 

16、Windows下为Oracle创建临时表空间

写道

--创建临时表空间
CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE 'D:\TABLESPACE\test_temp01.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

 17、Windows下为Oracle创建用户默认表空间

写道

CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;

18、Window下为Oracle用户赋予权限

写道

grant connect to tms_user;
grant dba to tms_user;
grant resource to tms_user;
alter user tms_user default role connect,dba,resource;

 19、Windows下使用exp命令导出数据库[空库]

写道

exp tmsdb/[e-mail]/prodb file=d:\workdata\tms232biaojiegou.dmp owner=tms_user rows=n

 20、Windows下使用Imp命令导入数据库

写道

imp tms_user/tms_user@orcl file=d:\workdata\tms232biaojiegou.dmp full=y

 21、MySQL自增主键输入0自动转换为1的问题

写道

先给当前session设置sql_mode，执行set sql_mode = no_auto_value_on_zero;
然后执行插入主键为0的语句。

 22、MySQL修改字段

写道

ALTER TABLE GW_PLM_EE MODIFY COLUMN GW_PLM_EE_ID INT NOT NULL AUTO_INCREMENT;

 23、Oracle查询表字段的信息USER_TAB_COLUMNS

    Oracle查询表字段的信息（字段名称、数据类型、长度、字段描述、表名称等）

写道

select column_name,data_type,data_length,data_precision,
data-scale,table_name from user_tab_columns where table_name = 'TABLE_NAME';

 24、MSSQL查询表字段的信息

SELECT   
(case when a.colorder=1 then d.name else '' end) N'表名',   
a.colorder N'字段序号',   
a.name N'字段名',   
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',   
(case when (SELECT count(*)   
FROM sysobjects   
WHERE (name in   
(SELECT name   
FROM sysindexes   
WHERE (id = a.id) AND (indid in   
(SELECT indid   
FROM sysindexkeys   
WHERE (id = a.id) AND (colid in   
(SELECT colid   
FROM syscolumns   
WHERE (id = a.id) AND (name = a.name))))))) AND   
(xtype = 'PK'))>0 then '√' else '' end) N'主键',   
b.name N'类型',   
a.length N'占用字节数',   
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',   
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',   
(case when a.isnullable=1 then '√'else '' end) N'允许空',   
isnull(e.text,'') N'默认值',   
isnull(g.[value],'') AS N'字段说明'   
  
FROM syscolumns a left join systypes b   
on a.xtype=b.xusertype   
inner join sysobjects d   
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'   
left join syscomments e   
on a.cdefault=e.id   
left join sysproperties g   
on a.id=g.id AND a.colid = g.smallid   
order by object_name(a.id),a.colorder  

 25、Windows下重启MySQL命令

写道

开始->运行->cmd
停止：net stop mysql
启动：net start mysql
前提MYSQL已经安装为windows服务

 26、Linux下重启MySQL命令

写道

1、启动方式
使用 service 启动：service mysqld start
使用 mysqld 脚本启动：/etc/inint.d/mysqld start
使用 safe_mysqld 启动：safe_mysqld&

2、停止
使用 service 启动：service mysqld stop
使用 mysqld 脚本启动：/etc/inint.d/mysqld stop
mysqladmin shutdown

3、重启
使用 service 启动：service mysqld restart
使用 mysqld 脚本启动：/etc/inint.d/mysqld restart

4、查看mysql是否在监 听端口命令
netstat -tl | grep mysql
会看到如下类似内容
tcp 0 0 *:mysql *:* LISTEN

 27、Oracle查询用户所有表的语句

select * from user_tab_comments  

 28、Oracle不区分大小写查询

lower(t.yhid) lower_yhid