SQL查询的艺术学习笔记--复杂查询

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,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转义符