面试数据库常用操作语句

数据库复习资料准备

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’)