SQL学习笔记 – 备忘录

sql运行顺序
(1)from 　　(2) join 　　(3) on 　　(4) where 　　(5)group by 　　(6) avg,sum…. （组函数）　　(7)having 　　(8) select 　　(9) distinct 　　(10) order by

自增主键创建

create table student(
   id int identity(1,1),
   name varchar(100)
)

set IDENTITY_INSERT student ON
insert into student(id,name)values(1,'student1');
insert into student(id,name)values(2,'student2');
set IDENTITY_INSERT student OFF

连接表后保存生成新表

select s.id,s.name,s.sex,f.score 
into newtable
from student s left join final f on s.id=f.id
select * from newtable

插入和修改视图

create view [view_stu] as
    select * from newtable 
    where id_c=20
select * from [view_stu]

alter view [view_stu] 
as
    select * from newtable where id=3
select * from [view_stu]

order排序
ASC/DESC

大小写转换
mysql：UCASE/LCASE
sql server：UPPER/LOWER

截取字符串
mysql：MID(column_name,start[,length])
sql server：LEFT/RIGHT(column_name,integer)
/SUBSTRING(column_name,start,length)

group by
范例表格


1、应用

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

2、多表连接

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name

排除查询可用
!= xx
not like ‘xx’
not in (xx)
xx