面试数据库常用操作语句
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数据库复习资料准备
1、创建/删除数据库：Create/ Drop database name
2、创建新表：Create table name(id int not null primary key, name char(20)) // 带主键
Create table name(id int not null, name char(20), primary key (id, name)) // 带复合主键Create table name(id int not null default 0, name char(20)) // 带默认值
3、删除表：Drop table name
4、表中添加一列：Alter table name add column size int
5、添加/删除主键：Alter table name add/drop primary key(size)
6、创建索引：Create [unique] index idxname on tabname(col)
7、删除索引：Drop index idxname
8、选择：Select *from table where 范围
9、删除重复记录Delete from name where id not in (select max(id) from name group by col1)
10、插入：Insert into table(field1, field2) values (value1, value)
11、删除：Delete from table where 范围
12、更新：Update table set field=value where 范围
13、查找：Select *from table where field like “”
14、排序：Select *from table order by field [desc]
15、总数：Select count as totalcount from table
16、求和：Select sum(field) as sumvalue from table
17、平均：Select avg(field) as avgvalue from table
18、最大：Select max(field) as maxvalue from table
19、最小：Select min(field) as minvalue from table
20、复制表：Select * into b from a where 范围Select top 0 * into b from a where 范围
21、拷贝表：Insert into b(a, b, c) select d,e,f from b;
22、子查询：
select ename from emp where deptno=(select deptno from dept where loc='NEW')；// 单查询select ename from emp where deptno in (select deptno from dept where dname like 'A%')；// 多行子查询
select deptno,ename ,sal from emp where (deptno,sal) IN (select deptno,MAX(sal) from emp group by deptno)；// 多列子查询
23、外连接查询：Select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.c
24、between用法：Select a,b,c, from table where a not between 数值1 and 数值2
25、in用法：select * from table1 where a [not] in (‘值1’,‘值2’,‘值4’,‘值6’)
26、两张关联表，删除主表中在副表中没有的信息：delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1)
27、日程提前五分钟提醒：select * from 日程where datediff(‘minute‘,f开始时间,getdate())>5
28、前10条记录：select top 10 * form table1 where 范围
29、包括所有在TableA 中但不在TableB和TableC 中的行：select a from tableA except (select a from tableB) except (select a from tableC)
30、随机取出10条数据：select top 10 * from tablename order by newid()
31、列出数据库里所有的表名：select name from sysobjects where type=‘U’
32、列出表里的所有的字段：select name from syscolumns where id=object_id(‘TableName’)