基础查询

基础查询

对指定的列消除重复项

select distinct address from student

使用as为指定的字段增加别名,as可省略

select name as 姓名,age as 年龄,sex as 性别,address as 城市 from student

使用as增加列,但不修改表

select *,'五班' as 班级 from student

查询创建的表的信息

select * from SYS.objects where name = 'student'

使用数据库名查询student在数据库内的ID

select object_id('student','u')

创建指定的表并复制全部的数据

if object_id('student_bak','u') is not null
drop table student_bak
select *into student_bak from student

查询备份表

select *from student_bak

创建指定的表并复制指定的数据

if OBJECT_ID('student_bak1','u') is not null
drop table student_bak1
select name,address into student_bak1 from student

查询备份表

select *from student_bak1

创建空表

if OBJECT_ID('student_bak2','u') is not null
drop table student_bak2
select *into student_bak2 from student where 1=2

查询备份表

select *from student_bak2

insert into 复制表

用此语法复制表首先表要存在才能复制,因此我们先创建表及给出相应的字段

if OBJECT_ID('student_bak3','u') is not null
drop table student_bak3

create table student_bak3
(姓名 varchar(10),
年龄 int,
性别 varchar(4),
地址 varchar(10)
)
select *from student_bak3
insert into student_bak3 select name,age,sex,address from student