SQL Server 笔记：常用SQL 2

--this is demo for opertors ,logical opertors and 通配,update,delete,truncate
use cmdemo

create table person
(
id int primary key,
age tinyint,
addr varchar(50)
)

insert into person values(1,18,'binlang dongli')
insert into person values(2,17,'binlang dongli')
insert into person values(3,16,'binlang xili')
insert into person values(4,16,'bintang xiang')
insert into person values(5,15,'siming qu')
insert into person values(6,16,'xiamen daxue')
insert into person values(7,17,'binlang dongli')

select * from person where age=16 -- =二元运算符
select * from person where age=+16 -- +一元运算符
select * from person where age=16 and addr='binlang dongli'-- and 逻辑运算符

select * from authors where state='ut' and contract<>0
select * from discounts where discount between 6 and 8
select * from authors where state in ('ca','or','ks')
select * from authors where au_fname like 'm%' -- % 通配
select * from authors where au_fname like '_ean'-- _ 单配
select * from authors where phone like '[0-9]1%' -- 单配某个范围内的值
select * from authors where au_fname like '[^abmk]%' -- 单配不在某个范围内的值

update person set age=age+1 where age>17
update person set age=age+1 --no where :update all

delete from person where age=19
delete from person --no where :delete all

truncate table person

select * from person


==================================================
--opertors
=
<>
!= 和 <> 相同
>
>=
!> 不大于
<
<=
!<
===================================================
--logical opertors
And
or
not
=====================================================
select * from authors where state='ut' and contract<>0
select * from discounts where discount between 6 and 8
select * from authors where state in ('ca','or','ks')
select * from authors where au_fname like 'm%' -- % 通配
select * from authors where au_fname like '_ean'-- _ 单配
select * from authors where phone like '[0-9]1%' -- 单配某个范围内的值
select * from authors where au_fname like '[^abmk]%' -- 单配不在某个范围内的值

====================================================
--连接查询演示的准备
use cmdemo

--主表 班级表
create table c
(
cno int primary key,
cname char(10)
)

--子表 学生表
create table s
(
sno int primary key,
sname char(10),
cno int -- foreign key references c(cno)
)

insert into c values(1,'c1')
insert into c values(2,'c2')
insert into c values(3,'c3')
insert into c values(4,'c4')
insert into s values(1,'s1',0)
insert into s values(2,'s2',1)
insert into s values(3,'s3',2)
insert into s values(4,'s4',1)
insert into s values(5,'s5',2)
insert into s values(6,'s6',0)
insert into s values(7,'s7',0)
insert into s values(8,'s8',0)

select * from c -- count = 4
select * from s -- count = 8

--内联结
select sno,sname,s.cno,cname from s,c where s.cno=c.cno
select sno,sname,s.cno,cname from s inner join c on s.cno=c.cno -- same with above

--外连结之左连接
select sno,sname,s.cno,cname from s left join c on s.cno=c.cno


--外连结之右连接
select sno,sname,s.cno,cname from s right join c on s.cno=c.cno

--外连结之交叉连接
select sno,sname,s.cno,cname from s,c
select sno,sname,s.cno,cname from s cross join c -- same with above

select * from authors cross join publishers where authors.city=publishers.city

SELECT s_1.name AS m, s.name

--自连接
建立自反关系可以将同一表中的某一列与另一列链接起来。
例如，假定employee表中有emp_id列和mgr_id列。因为每个经理同时也是雇员，
所以通过在同一表中绘制一根关系连线将这两列链接起来。这种关系能确保添加
到该表中的每个经理标识符(manager ID)与已有的雇员标识符(employee ID)相匹配。


create table s
(
id smallint primary key,
name char(10),
mid tinyint
)

insert into s values(1,'a',2)
insert into s values(2,'b',0).
insert into s values(3,'c',2)

SELECT s_1.name AS m, s.name
FROM s INNER JOIN s s_1 ON s.mid = s_1.id --查出班长管理的学生纪录

====================================================
-- 插入缺省数据

drop table c
drop table s

create table c
(
cno int primary key,
cname char(10) default 'ACCP-class'
)

insert into c values(1,default)
insert into c(cno) values(2) --same as above

-- 基于select插入
-- select into
select sname,cname into sc from s,c where c.cno=s.cno
select * from sc
drop table sc
--创建表的空拷贝，但不包括索引和触发器
--无日志记载，不能rollback

use pubs
create table phone_lists
(
name char(50),
phone char(20)
)

insert phone_lists
select au_lname+au_fname as authorname,phone as phone
from authors
order by authorname -- expression 1

insert into phone_lists (name,phone)
select au_lname+au_fname as authorname,phone as phone
from authors order by authorname
-- expression 2 :this expression is same as expression 1

select * from phone_lists
drop table phone_lists
====================================================