SQL Server 笔记：常用SQL

/*查询语句 */
use pubs /*打开数据库*/

select * from authors
====================================================
select au_lname as lastname,au_fname as firstname from authors
select 'lastname'=au_lname,'firstname'=au_fname from authors
/*the two sql expression have the same effect*/
select 'phone:'+phone as PhoneNo from authors
====================================================
--order by a field
select * from authors order by au_lname asc
select * from authors order by au_lname desc
====================================================
--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]%' -- 单配不在某个范围内的值
====================================================
常用字符串函数

--functions
charindex(string1,string2,startposition)
select charindex('ab','deabef',1)
select charindex('ab','ddddeabef',4) --测试：最后一个参数好像意义不大

left(string,number)
select left('abcdef',3)

len(string)
lower(string)
ltrim(string)
right(string,number)
rtrim(string)
upper(string)

类型显式转换：cast , convert
select cast(qty as varchar) from sales

select avg(cast(stor_id as integer)) from sales

select sum(convert(integer,stor_id)) from sales

====================================================
日期函数
dateadd(datepart,amount,date)
select dateadd(year,1,getdate())
select dateadd(month,1,getdate())

datediff(datepart,date1,date2)
select datediff(year,'2001/01/01 12:00:00',getdate())

datepart(datepart,date)
select datepart(month,getdate())

day(date)
getdate()
month()
year()
====================================================
算术函数
+
-
*
/
select 8+3+7

====================================================
数学函数
abs(number)
ceiling(number) --返回大于或等于给定数的最大整数值
select ceiling(10.5) --result:11

floor(number) --返回小于或等于给定数的最小整数值
select floor(10.5) --result:10

power(number,power)
select power(2,3)

round(number,precision) --返回一个截短位数的近似数
select round(10.25,1) --result: 10.3

square(number)
select square(5) --result:25
sqrt(number)
select sqrt(25) --result:5

====================================================
系统函数

convert(datatype,expression)
select convert(varchar(5),12345) --result:'12345'

current_user()
select current_user

datalength() --返回表达式的字节数
select datalength('hello') -- result 5

host_name()

system_user()

user_name()

====================================================
--distinct
select distinct state from authors

--count
select count(*) as count from sales

select avg(qty) as avgqty from sales

select sum(qty) as sumqty from sales

select max(qty) as maxqty from sales

select min(qty) as minqty from sales

select qty,count(qty) from sales group by qty having qty between 10 and 25

select qty,count(qty) from sales group by qty having sum(qty) >60

====================================================
--连接查询演示的准备
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

--自连接
建立自反关系可以将同一表中的某一列与另一列链接起来。
例如，假定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 --查出班长管理的学生纪录


====================================================
--子查询
select sno,sname,cno from s
where cno in
(
select distinct cno from c
)
====================================================
-- select into
select sname,cname into sc from s,c where c.cno=s.cno
select * from sc
drop table sc
--创建表的空拷贝，但不包括索引和触发器
--无日志记载，不能rollback
====================================================
-- union
select * from s where sno<=2
union
select * from s where sno>5
====================================================
-- 插入缺省数据

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插入
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
====================================================

Greate sample for select:

sample 1:

create table person(
id int,
name);

insert into person values(1,'chengming');
insert into person values(2,'tang xiaohua');


create table dad(
personid int, --references to person.id
sex boolean);

insert into dad values(1,true);

create table mum(
personid int,
length smallint);

insert into mum values(2,165);
select *,
case
when dad.personid is not null then 'dad'
when mum.personid is not null then 'mum'
end as persontype
from person left join dad on person.id=dad.personid
left join mum on person.id=mum.personid;

result:
id | name | personid | sex | personid | length | persontype
----+----------------------+----------+-----+----------+--------+------------
1 | chengming | 1 | t | | | dad
2 | tang tang | | | 2 | 165 | mum
(2 rows)

====================================================