use idx
select * from sys.tables
use seldata
select * from sys.tables
create database seldata2
on
(
name=seldata2,
filename='d:\data\seldata2.mdf',
size=50MB,
maxsize=2000MB,
filegrowth=5%
)
log on
(
name=sledata_log,
filename='d:\data\seldata2.ldf',
size=30MB,
maxsize=200MB,
filegrowth=5%
)
--create data
use seldata2
create table course
(
cno int not null,
cname char(50) not null,
ctime int not null,
scount int not null,
ctest smalldatetime() not null
)
--creat table
insert into course values (4,'应用数学基础',48,120,'2006-07-10')
update course set ctest='2006-7-10' where cno=4
select * from course
alter table course alter column ctest smalldatetime
--修改字段数据类型
insert into course values (5,'生物工程概论',48,120,'2006-07-08')
insert into course values (1,'计算机软件基础',32,70,'2006-07-08')
insert into course values (2,'计算机硬件基础',24,80,'2006-06-28')
insert into course values (8,'模拟电路设计',28,90,'2006-07-10')
insert into course values (7,'机械设计实验',48,68,'2006-07-14')
insert into course values (3,'生物化学',32,40,'2006-07-02')
insert into course values (9,'数据库设计',16,80,'2006-07-01')
insert into course values (6,'设计理论',28,45,'2006-06-30')
insert into course values (10,'计算机入门',24,150,'2006-06-29')
insert into course values (11,'数字电路设计基础',30,125,'2006-06-20')
select * from course
--复制表
select * into seldata.dbo.course from seldata2.dbo.course
use seldata2
select * from course
use seldata
insert into seldata2.dbo.course select * from seldata.dbo.course
--复制表内容至data2.dbo.course
select * into seldata.dbo.course from seldata2.dbo.course
--用查询语句复制并建立整个表sledata.dbo.course
delete from course where 1=1
--删除表内数据
use seldata
select * from teacher where age>30 and sex='女'
order by age
--and 使用
select * from teacher where age>30 and sex='女' and dname='计算机'
order by age
--and 重复使用
select tname,dname,sal from teacher
where dname='计算机' or dname='生物' order by dname
--or使用。
--测试null 与true 结果集为true
select * from teacher where sal>1500 or age<45 order by sal
select tname,dname,sal from teacher
where (dname='计算机' or dname='生物') and sal>1000 order by sal
--in运算符的使用
select tname,dname,age,sex from teacher
where dname in('计算机','生物','机械工程') order by dname
--对应的not in
select tname,dname,age,sex from teacher
where dname not in ('计算机','生物','机械工程')
order by dname
--IN 和 or相同功能的实现
select tname,dname,age,sex from teacher where dname='计算机'
or dname='生物' or dname='机械工程' order by dname
--这条语句等于:select tname,dname,age,sex from teacher
-- where dname in('计算机','生物','机械工程') order by dname
--NOT 运算符
use seldata
select tname,dname,age,sex from teacher
where not dname='计算机' order by dname
--使用NOT取反操作
select * from teacher where not sal>1500 order by sal
select * from teacher where sal is not null
--关于NOT运算符和<>号运算符
/*not运算符和不等符在简单查询可以相互替代,但是NOT可以和其他
运算符组合使用 如:not null not between not in not like not exists*/
select tname,dname,age,sex from teacher
where age not between 40 and 50 order by sex
select tname,dname,age,sex from teacher
where not (age>=40 and age<=50) order by age
select tname,dname,age,sex from teacher
where age<40 or age>= 50 order by age
--like 模糊查询
--只有字符类型才可以用通配符进行查询
select tname,dname,age,sex from teacher
where dname like '计算机' order by sex
--not like NOT取反
select tname,dname,age,sex from teacher
where dname not like '计算机' order by sex
--该语句可以改写为:
select tname,dname,age,sex from teacher
where dname <> '计算机' order by sex
--复杂搜索条件查询之通配符:
/* '%'百分符号,可以代替任意个字符
'_'下划线符号,可以代替单个字符
‘[]’方括号符号,可以代替任意指定位置于方括号内字符
*/
--开始和结束用‘%’百分号通配符
select * from course
select * from course where cname like '计算机%'
--结尾用'%'
select cname,scount,ctest from course
where cname like '%基础' order by cname
--开始用'%'
select cname,scount,ctest from course
where cname like '%设计%'
--中间匹配
select cname,scount,ctest from course
where cname like '计算机%基础'
--两端匹配
-- ‘_’下划线通配符 注意要确定字符个数
select * from course
select cname,scount,ctest from course
where cname like '计算机__' order by cname
--计算机后两个字符课程
select * from course where cname like '______'
and not cname like '_____' order by cname
--注意:当我们取6个‘_’下划线时,由于like 特性
--会取值范围为 1-6个字符都认为是正确的。
--'[]'括号通配符
--满足括号内符号相似性即可 也是简单的数组
--[1,2,3]可能为1 12 123 23 13都适用
select * from course
select * from course where cname like '[计生]%'
select * from course where cname like '[计生]___'
--'[]' 方括号通配符取反 '[^]'
select cname,scount,ctest from course
where cname like '[计生]%' order by cname
select cname,scount,ctest from course
where cname like '[^计生]%' order by cname
--取反操作'[^]'
select cname,scount,ctest from course
where cname not like '[计生]%' order by cname
select cname,scount,ctest from course
where not cname like '[计生]%' order by cname
--ESCAP转义符
select * from sys.tables
use seldata
select * from sys.tables
create database seldata2
on
(
name=seldata2,
filename='d:\data\seldata2.mdf',
size=50MB,
maxsize=2000MB,
filegrowth=5%
)
log on
(
name=sledata_log,
filename='d:\data\seldata2.ldf',
size=30MB,
maxsize=200MB,
filegrowth=5%
)
--create data
use seldata2
create table course
(
cno int not null,
cname char(50) not null,
ctime int not null,
scount int not null,
ctest smalldatetime() not null
)
--creat table
insert into course values (4,'应用数学基础',48,120,'2006-07-10')
update course set ctest='2006-7-10' where cno=4
select * from course
alter table course alter column ctest smalldatetime
--修改字段数据类型
insert into course values (5,'生物工程概论',48,120,'2006-07-08')
insert into course values (1,'计算机软件基础',32,70,'2006-07-08')
insert into course values (2,'计算机硬件基础',24,80,'2006-06-28')
insert into course values (8,'模拟电路设计',28,90,'2006-07-10')
insert into course values (7,'机械设计实验',48,68,'2006-07-14')
insert into course values (3,'生物化学',32,40,'2006-07-02')
insert into course values (9,'数据库设计',16,80,'2006-07-01')
insert into course values (6,'设计理论',28,45,'2006-06-30')
insert into course values (10,'计算机入门',24,150,'2006-06-29')
insert into course values (11,'数字电路设计基础',30,125,'2006-06-20')
select * from course
--复制表
select * into seldata.dbo.course from seldata2.dbo.course
use seldata2
select * from course
use seldata
insert into seldata2.dbo.course select * from seldata.dbo.course
--复制表内容至data2.dbo.course
select * into seldata.dbo.course from seldata2.dbo.course
--用查询语句复制并建立整个表sledata.dbo.course
delete from course where 1=1
--删除表内数据
use seldata
select * from teacher where age>30 and sex='女'
order by age
--and 使用
select * from teacher where age>30 and sex='女' and dname='计算机'
order by age
--and 重复使用
select tname,dname,sal from teacher
where dname='计算机' or dname='生物' order by dname
--or使用。
--测试null 与true 结果集为true
select * from teacher where sal>1500 or age<45 order by sal
select tname,dname,sal from teacher
where (dname='计算机' or dname='生物') and sal>1000 order by sal
--in运算符的使用
select tname,dname,age,sex from teacher
where dname in('计算机','生物','机械工程') order by dname
--对应的not in
select tname,dname,age,sex from teacher
where dname not in ('计算机','生物','机械工程')
order by dname
--IN 和 or相同功能的实现
select tname,dname,age,sex from teacher where dname='计算机'
or dname='生物' or dname='机械工程' order by dname
--这条语句等于:select tname,dname,age,sex from teacher
-- where dname in('计算机','生物','机械工程') order by dname
--NOT 运算符
use seldata
select tname,dname,age,sex from teacher
where not dname='计算机' order by dname
--使用NOT取反操作
select * from teacher where not sal>1500 order by sal
select * from teacher where sal is not null
--关于NOT运算符和<>号运算符
/*not运算符和不等符在简单查询可以相互替代,但是NOT可以和其他
运算符组合使用 如:not null not between not in not like not exists*/
select tname,dname,age,sex from teacher
where age not between 40 and 50 order by sex
select tname,dname,age,sex from teacher
where not (age>=40 and age<=50) order by age
select tname,dname,age,sex from teacher
where age<40 or age>= 50 order by age
--like 模糊查询
--只有字符类型才可以用通配符进行查询
select tname,dname,age,sex from teacher
where dname like '计算机' order by sex
--not like NOT取反
select tname,dname,age,sex from teacher
where dname not like '计算机' order by sex
--该语句可以改写为:
select tname,dname,age,sex from teacher
where dname <> '计算机' order by sex
--复杂搜索条件查询之通配符:
/* '%'百分符号,可以代替任意个字符
'_'下划线符号,可以代替单个字符
‘[]’方括号符号,可以代替任意指定位置于方括号内字符
*/
--开始和结束用‘%’百分号通配符
select * from course
select * from course where cname like '计算机%'
--结尾用'%'
select cname,scount,ctest from course
where cname like '%基础' order by cname
--开始用'%'
select cname,scount,ctest from course
where cname like '%设计%'
--中间匹配
select cname,scount,ctest from course
where cname like '计算机%基础'
--两端匹配
-- ‘_’下划线通配符 注意要确定字符个数
select * from course
select cname,scount,ctest from course
where cname like '计算机__' order by cname
--计算机后两个字符课程
select * from course where cname like '______'
and not cname like '_____' order by cname
--注意:当我们取6个‘_’下划线时,由于like 特性
--会取值范围为 1-6个字符都认为是正确的。
--'[]'括号通配符
--满足括号内符号相似性即可 也是简单的数组
--[1,2,3]可能为1 12 123 23 13都适用
select * from course
select * from course where cname like '[计生]%'
select * from course where cname like '[计生]___'
--'[]' 方括号通配符取反 '[^]'
select cname,scount,ctest from course
where cname like '[计生]%' order by cname
select cname,scount,ctest from course
where cname like '[^计生]%' order by cname
--取反操作'[^]'
select cname,scount,ctest from course
where cname not like '[计生]%' order by cname
select cname,scount,ctest from course
where not cname like '[计生]%' order by cname
--ESCAP转义符