数据库常用的基本操作
1 --------------------------------对数据库操作-----------------------------------
2 --建立数据库
3 create database Test
4
5 --使用数据库
6 use Test
7
8 --删除数据库
9 DROP DATABASE Test
10
11 --------------------------------对表操作----------------------------------------
12 --建立表
13 create table Person
14 (
15 ID int primary key not null,--编号
16 Name varchar(10) not null,--姓名
17 Birth datetime not null,--生日
18 Address varchar(50),--地址
19 Phone varchar(50),--电话
20 )
21 create table person1
22 (
23 pID int primary key not null,--编号
24 Sex varchar(10) not null,--性别
25 )
26
27 --创建新表,并从原有表中复制指定内容
28 create table person2
29 (
30 pID varchar(2),
31 pName varchar(30),
32 pSex varchar(2),
33 )
34 insert into person2 select * from person where personSex='男'
35
36 --删除表
37 drop table person
38
39 --增加列
40 alter table person add State varchar(10)
41
42 --删除列
43 alter table person drop column State
44
45 --修改列的数据类型
46 alter table person alter column State varchar(20)
47
48 --增加行
49 insert into Person values(1,'张超',1983-6-28,'北京','010-88256483')
50 insert into Person values(2,'李华',1983-5-20,'北京','010-88462582')
51 insert into Person values(3,'王梅',1983-2-23,'梅州','0753-6570211')
52 insert into person1 values(1,'男')
53 insert into person1 values(2,'男')
54 insert into person1 values(3,'女')
55
56 --修改行
57 update person set Name='张英' where ID=1
58
59 --删除行
60 delete person where ID=2
61
62 --删除表中所有数据
63 truncate table person
64
65 --简单查询
66 select * from person
67 select * from person1
68
69 --条件查询
70 select * from person where ID=1
71 select * from person where ID=1 and ID is not null
72 select * from person where ID=1 or ID is null
73 select * from person where ID is not null
74 select * from person where ID between 0 and 3 and contains(Address,'北京') --从属运算
75 select * from person where ID>0 and ID<=3
76 select * from person where Address like '北_' --通配一个字符
77 select * from person where ID in(1,2,3) --从属运算
78 select * from person where Address like '北%' --通配多个字符
79 select * from person where ID like '%'
80 select count(*) from person where ID=3 --返回满足条件的行的个数
81
82 --联合查询
83 select * from person where ID>0 union select * from person where ID<=3
84 select (p.Name,p1.Sex) from (person p,person p1)--给不同的表取别名并联合查询指定列
85 select * from operator intersect select * from operator2 --intersect相交,返回两个表中共有的行
86
87 --表关联查询
88 select distinct * from person as p --distinct防止重复
89 join person1 as p1
90 on ID=pID
91 where ID=1
92
93 --限制查询
94 select top 10 percent with ties * from person order by ID asc--升序
95 select top 10 percent with ties * from person order by ID desc--降序
96 select top 10 * from person order by ID asc
97 select * from person group by ID having ID<3 --进行分组查询
98
99 --对查询进行输出控制
100 select ID as 编号,Name as 姓名,Birth as 生日,Address as 地址,Phone as 电话
101 from person order by ID desc
102
103 --------------------------------对视图操作----------------------------------------
104 --可以象操作表一样操作视图,但应少用insert update delete等语句
105
106 --创建视图
107 create view myView
108 as
109 select * from Orders
110
111 --修改视图
112 alter view myView
113 as
114 select * from Orders
115
116 --删除视图
117 drop view myView
118
119 --查看视图
120 select top 10 * from myView where OrderID>=10250--显示头10条记录
121
122 -----------------------------对索引的操作--------------------------------------
123 use test
124 select * from person
125
126 --创建索引
127 create index index_person on person(ID)
128
129 --创建unique类型的索引
130 create unique index index_person on person(ID)
131
132 --删除索引
133 drop index person.index_person
134
135 -----------------------------对存储过程的操作----------------------------------
136 --建立存储过程
137 create procedure dbo.GetDataById
138 (
139 @id int
140 )
141 as
142 select * from T_Person where id=@id
143 return
144
145 --修改存储过程
146 alter procedure dbo.GetDataById
147 (
148 @id int
149 )
150 as
151 select * from T_Person where id=@id
152 return
153
154 --执行存储过程
155 exec GetDataById 2
156
157 --删除存储过程
158 drop procedure dbo.GetDataById
2 --建立数据库
3 create database Test
4
5 --使用数据库
6 use Test
7
8 --删除数据库
9 DROP DATABASE Test
10
11 --------------------------------对表操作----------------------------------------
12 --建立表
13 create table Person
14 (
15 ID int primary key not null,--编号
16 Name varchar(10) not null,--姓名
17 Birth datetime not null,--生日
18 Address varchar(50),--地址
19 Phone varchar(50),--电话
20 )
21 create table person1
22 (
23 pID int primary key not null,--编号
24 Sex varchar(10) not null,--性别
25 )
26
27 --创建新表,并从原有表中复制指定内容
28 create table person2
29 (
30 pID varchar(2),
31 pName varchar(30),
32 pSex varchar(2),
33 )
34 insert into person2 select * from person where personSex='男'
35
36 --删除表
37 drop table person
38
39 --增加列
40 alter table person add State varchar(10)
41
42 --删除列
43 alter table person drop column State
44
45 --修改列的数据类型
46 alter table person alter column State varchar(20)
47
48 --增加行
49 insert into Person values(1,'张超',1983-6-28,'北京','010-88256483')
50 insert into Person values(2,'李华',1983-5-20,'北京','010-88462582')
51 insert into Person values(3,'王梅',1983-2-23,'梅州','0753-6570211')
52 insert into person1 values(1,'男')
53 insert into person1 values(2,'男')
54 insert into person1 values(3,'女')
55
56 --修改行
57 update person set Name='张英' where ID=1
58
59 --删除行
60 delete person where ID=2
61
62 --删除表中所有数据
63 truncate table person
64
65 --简单查询
66 select * from person
67 select * from person1
68
69 --条件查询
70 select * from person where ID=1
71 select * from person where ID=1 and ID is not null
72 select * from person where ID=1 or ID is null
73 select * from person where ID is not null
74 select * from person where ID between 0 and 3 and contains(Address,'北京') --从属运算
75 select * from person where ID>0 and ID<=3
76 select * from person where Address like '北_' --通配一个字符
77 select * from person where ID in(1,2,3) --从属运算
78 select * from person where Address like '北%' --通配多个字符
79 select * from person where ID like '%'
80 select count(*) from person where ID=3 --返回满足条件的行的个数
81
82 --联合查询
83 select * from person where ID>0 union select * from person where ID<=3
84 select (p.Name,p1.Sex) from (person p,person p1)--给不同的表取别名并联合查询指定列
85 select * from operator intersect select * from operator2 --intersect相交,返回两个表中共有的行
86
87 --表关联查询
88 select distinct * from person as p --distinct防止重复
89 join person1 as p1
90 on ID=pID
91 where ID=1
92
93 --限制查询
94 select top 10 percent with ties * from person order by ID asc--升序
95 select top 10 percent with ties * from person order by ID desc--降序
96 select top 10 * from person order by ID asc
97 select * from person group by ID having ID<3 --进行分组查询
98
99 --对查询进行输出控制
100 select ID as 编号,Name as 姓名,Birth as 生日,Address as 地址,Phone as 电话
101 from person order by ID desc
102
103 --------------------------------对视图操作----------------------------------------
104 --可以象操作表一样操作视图,但应少用insert update delete等语句
105
106 --创建视图
107 create view myView
108 as
109 select * from Orders
110
111 --修改视图
112 alter view myView
113 as
114 select * from Orders
115
116 --删除视图
117 drop view myView
118
119 --查看视图
120 select top 10 * from myView where OrderID>=10250--显示头10条记录
121
122 -----------------------------对索引的操作--------------------------------------
123 use test
124 select * from person
125
126 --创建索引
127 create index index_person on person(ID)
128
129 --创建unique类型的索引
130 create unique index index_person on person(ID)
131
132 --删除索引
133 drop index person.index_person
134
135 -----------------------------对存储过程的操作----------------------------------
136 --建立存储过程
137 create procedure dbo.GetDataById
138 (
139 @id int
140 )
141 as
142 select * from T_Person where id=@id
143 return
144
145 --修改存储过程
146 alter procedure dbo.GetDataById
147 (
148 @id int
149 )
150 as
151 select * from T_Person where id=@id
152 return
153
154 --执行存储过程
155 exec GetDataById 2
156
157 --删除存储过程
158 drop procedure dbo.GetDataById
