SQLServer的基础SQL语句和例子

create table clerk
(cleId int primary key,--主键不能重复出现，而且必须给值
cleName nvarchar(50),
age int
)
insert into clerk values(1,'步乐',18)
insert into clerk values(2,'小吴',18)
--插入部分字段，要对应
insert into clerk (cleId,cleName) values(3,'步乐')


select * from clerk
--修改
update clerk set age=30 where cleName='小吴'


update clerk set age=20, cleName='小乐' where cleId=1
--主键可以修改，但不能重复
update clerk set cleId=4 where cleName='小吴'


--修改空值,空值不能用=表示，要用is null
update clerk set cleName='帅哥' where age is null
insert into clerk values(5,'小s当',18)
--删除表
delete from clerk
--删除表的数据
delete from clerk where age=18 and age<=20
create table emp
(Empno int primary key,
Ename nvarchar(30),
Job varchar(25),
MGR datetime,
Hiredate  char(20),
Sal numeric(8,2),
Comm numeric(8,2),
Deptno int foreign key references dept(Deptno)--因为根据我们的需要做成外键
)
--外键：1、只能指向主键，2、外键和主键的数据类型要一致

create table dept
(Deptno int primary key,
Dname nvarchar(30),
Loc nvarchar(30)
)

select *from dept
select *from emp

insert into dept values(10,'accounting','new york')
insert into dept values(20,'research','dallas')
insert into dept values(30,'sales','chicago')
insert into dept values(40,'operations','boston')


insert into emp(Empno,Ename,Job,MGR,Hiredate,Sal,Deptno) values
(7369,'smith','clerk',7902,'1980-12-17',800.00,20)
insert into emp values (7499,'allen','salesman',7698,'1981-2-20',1600.00,300.00,30)
insert into emp values (7521,'ward','salesman',7698,'1981-2-22',1250.00,500.00,30)
insert into emp(Empno,Ename,Job,MGR,Hiredate,Sal,Deptno) values
(7566,'jones','manager',7839,'1981-4-2',2975.00,20)
insert into emp values (7654,'martin','salesman',7698,'1981-9-28',1250.00,1400.00,30)
insert into emp(Empno,Ename,Job,MGR,Hiredate,Sal,Deptno) values
(7698,'blake','manager',7839,'1981-5-1',2850.00,30)
insert into emp(Empno,Ename,Job,MGR,Hiredate,Sal,Deptno) values
(7782,'clark','manager',7839,'1981-6-9',2450.00,30)
insert into emp(Empno,Ename,Job,MGR,Hiredate,Sal,Deptno) values
(7788,'scott','analyst',7566,'1987-4-19',3000.00,20)
insert into emp(Empno,Ename,Job,Hiredate,Sal,Deptno) values
(7839,'king','president','1981-11-17',5000.00,10)
insert into emp(Empno,Ename,Job,MGR,Hiredate,Sal,Deptno) values
(7876,'adams','clerk',7788,'1981-5-23',1100.00,20)
insert into emp(Empno,Ename,Job,MGR,Hiredate,Sal,Deptno) values
(7900,'james','clerk',7698,'1981-12-3',950.00,30)
insert into emp values (7844,'turner','salesman',7698,'1981-9-8',1500.00,1400.00,30)
insert into emp(Empno,Ename,Job,MGR,Hiredate,Sal,Deptno) values
(7902,'ford','analyst',7566,'1981-12-3',3000.00,20)
insert into emp(Empno,Ename,Job,MGR,Hiredate,Sal,Deptno) values
(7934,'miller','clerk',7782,'1981-1-23',1300.00,10)


--查询所有列：select * from emp(表名) where 条件

--查询指定列：select 字段1，字段2 from 表名 where 条件

--查询smith析薪水，工作，所在部门
select Sal,Job,Deptno from emp where Ename='smith'
--如何取消重复行:select distinct 字段 from 表名 where 条件
--(distinct只能消除完全一样的行，保留一行)
--统计共有多少部门
select distinct deptno from emp
--区别
select deptno from emp
--显示每个雇员的年工资(别名)
select ename,sal*13 年工资 from emp
--年工资(别名)+资金：isnull(Comm,0)判断是否为null
select ename,sal*13+isnull(Comm,0)*13 年工资 from emp

select *from emp where sal>3000

select * from emp where hiredate>'1982-1-1'

select *from emp where sal>2000 and sal<2500

--(sal>=2000andsal<=2500)
select * from emp where sal between 2000 and 2500

--显示首字符为S的员工姓名和工资:%表示后面为任意字符
select Ename,Sal from emp where Ename like 'S%'
--显示第三个字符为o的员工姓名和工资:_表示任意字符，%表示后面为任意字符
select ename,sal from emp where ename like '__o%'

--如何显示empno为7876,7934,7900..的雇员情况
select * from emp where empno=7876 or empno=7934 or empno=7900
--一般我们使用in这个 关键字，效率更高
select * from emp where empno in(7876,7934,7900)

--如何显示没有上级的雇员的情况
select * from emp where MGR is null

--查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的
--姓名首写字母为J
select * from emp where (sal>500 or job='manager') and ename like 'j%'

--如何按照工资从低到高的顺序显示雇员的信息
--order by 默认是升序排序
--order by asc(升序)
--order by desc
select * from emp order by sal desc
select * from emp order by hiredate asc
--按照部门号升序而雇员工资降序排序
--order by 可以根据不同的字段排序
select * from emp order by deptno,sal desc
select * from emp order by deptno,sal desc,empno asc

--统计每个人的年薪，并按照工资从低到高的顺序
select ename,(sal+isnull(comm,0))*13 年薪 from emp order by 年薪--好
select ename,(sal+isnull(comm,0))*13  from emp order by (sal+isnull(comm,0))*13 

--如何显示所有员工最高工资和最低工资
select min(sal),max(sal) from emp
--如何显示最低工资和该雇员的名字
select ename,sal from emp where sal=(select min(sal) from emp)
--显示所有员工的平均工资和工资总和
select avg(sal) 平均工资,sum(sal) 总工资 from emp
select ename,sal from emp where sal>(select avg(sal) from emp)
--统计共有多少员工
select count(*) 人数 from emp

--如何显示每个部门的平均工资和最高工资
select avg(sal) 平均工资,max(sal) 最高工资,deptno from emp group by deptno
--显示每个部门的每个岗位的平均工资和最低工资
select avg(sal),min(sal),deptno,job from emp group by deptno,
job order by deptno 
--显示平均工资低于工资2000的部门号和它的平均工资
--having往往和group by 结合使用，可以对分组查询结果进行筛选
select avg(sal),deptno from emp group by deptno having avg(sal)<2000
--group by、having、order by使用顺序：group by、having、order by
select avg(sal),deptno from emp group by deptno having avg(sal)>2000
order by avg(sal) asc
--创建数据库
--创建数据表
use handsome--使用某个数据库
go
create table hero
(
   heroId int,
   heroName varchar(50),
   heroNickName varchar(50),
   sex char(2),
   sal int
)
--删除一张表(把表的结构和表的数据一起删除)
drop table hero
--使用sql语句来添加数据
insert into hero values(1,'宋江','及时雨','男',20000)
insert into hero values(1,'吴用','智多星','男',15000)
insert into hero values(1,'小乐','傻B','男',30)
insert into hero values(1,'公孙胜','入支龙','男',80)
select * from hero
--查询工资低于100的同志
select * from hero where sal<100
--把工资低于100的同志工资提高10%(update)
--语法update 表名 set 字段名1=？，字段名2=？ where 条件
update hero set sal=sal*1.1,sex='女' where sal<100

delete from hero where sex='女'
--多表查询
select * from emp
select * from dept

--显示雇员名，雇员工资及所在部门的名字，部门号
--如果两张表都有相同名字的字段，则需要带表名（别名）
select ename,sal,loc,e.deptno from emp e,dept d where e.deptno=d.deptno

--如何显示部门为10的部门名、员工名和工资
select dname,ename,sal from emp e,dept d where e.deptno=10 and e.deptno=d.deptno

--显示雇员名、雇员工资及所在部门的名字，并按照部门排序
select ename,sal,dname from emp e,dept d where  e.deptno=d.deptno order by d.deptno asc

--显示某个员工的上级领导的姓名，比如显示ford的上级
--1.知道ford上级编号:elect mgr from emp where ename='ford'
select ename from emp where empno=(select mgr from emp where ename='ford')
--思考题：显示公司每个员工名字和他的上级的名字
--分析，把EMP表看做两张表(emp worker,emp boss)，分别是worker boss
--外连接（左外连接 右外连接）
select worker.ename 雇员,boss.ename 老板 from emp worker,emp boss where worker.mgr=boss.empno

--单行子查询（用=）：select deptno from emp where ename='smith'（一种）
--如何显示与SMITH同一部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='smith')

--多行子查询(用in)
--如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
--部门10的工作:select distinct job from emp where deptno=10(有二种)
select distinct job from emp where deptno=10
select * from emp where job in (select distinct job from emp where deptno=10)

--如何显示高于部门平均工资的员工信息
--分析
--1.把首先要知道各个部门的平均工资
select avg(sal),deptno from emp group by deptno
--2.把上面的查询结果，当做一个临时表
select * from emp,
(select avg(sal) myavg,deptno from emp group by deptno) tem
where emp.deptno=tem.deptno and emp.sal>tem.myavg

--显示第5个到第10入职的雇员（按照时间先后顺序）
--1.显示第1个到第4入职的雇员
select top 4 * from emp order by hiredate
--top后面的数表示要取出几条记录
select * from emp order by hiredate
--显示第5个到第10入职的雇员
select top 6 * from emp where empno not in 
(select top 4 empno from emp order by hiredate)
order by hiredate

--identity(1,1),表示字段自增，从1开始，每次+1
drop table test
create table test
(Id int primary key identity(1,1),
TestName varchar(20),
Pass varchar(20)
)
insert into test(TestName,Pass) values ('lhs','lhs2008')
--自动增长记录的两倍
insert into test(TestName,Pass) select TestName,Pass from test
 select * from test

--如何删除掉一张表重复记录
create table cat
(catId int,
catName varchar(10)
)
insert into cat values (2,'aa')
select * from cat
--把cat表的记录distinct后的结果，放入到temp
select distinct * into temp from cat
--把cat清空
delete  from cat
--把temp表的数据（没有重复的记录），插入到cat表
insert into cat select * from temp
--删除temp
drop table temp

--讲左外连接和右外连接
--思考题：显示公司每个员工名字和他的上级的名字
create table stu
(stuId int primary key identity(1,1),
stuName varchar(10)
)
insert into stu values('java')
insert into stu values('jack')
insert into stu values('jane')
insert into stu values('jans')

select * from stu

create table grd
(id int,
grade float(4)
)
insert into grd values(1,76)
insert into grd values(2,90)
insert into grd values(4,60)
insert into grd values(5,65)
insert into grd values(7,70)
--内连接
select s.stuid,g.id,s.stuname,g.grade from stu s,grd g where s.stuid=g.id
--左外连接：指左边的表的记录全部显示，如果没有匹配的记录就用null填
select s.stuid,g.id,s.stuname,g.grade from stu s left  join grd g on s.stuid=g.id
--右外连接：指左右边的表的记录全部显示，如果没有匹配的记录就用null填
select s.stuid,g.id,s.stuname,g.grade from stu s right  join grd g on s.stuid=g.id

sql server 的约束
--goods
create table goods
(goodsId nvarchar(50) primary key,
goodsName nvarchar(80) not null,
unitprice numeric(8,2) check (unitprice>0),--约束unitprice要大于零
category nvarchar(3) check (category in ('食物','日用品')),--约束category为食物或日用品
provider nvarchar(50)
)

--customer
create table customer
(customerId nvarchar(50) primary key,
cusName nvarchar(50) not null,
address nvarchar(100),
email nvarchar(100) unique,
sex nchar(1) check(sex in('男',' 女')) default '男',--约束sex为男或女，默认为男
carId nvarchar(18)
)

--purchase
create table purchase
(customerId nvarchar(50) foreign key references customer(customerId),
 goodsId nvarchar(50) foreign key references goods(goodsId),
nums int check (nums>0)
)
--如何备份数据库
--语句
backup database 你要备份的数据库名 to disk='你想放在那里的路径'
backup database aaa to disk='f:/lhs.bak'

--删除数据库
drop database aaa

--恢复数据库
restore database 你的数据库名 from disk='备份数据库的路径'
restore database aaa from disk='f:/lhs.bak'

--备份数据库表

select * from emp where deptno=30

select *from emp

select ename,empno,deptno from emp where job='clerk'

select * from emp where isnull(comm,0)>sal

select * from emp where comm>(sal*0.6)

select * from emp e where 
(deptno=10 and job='manager') or (deptno=20 and job='clerk')

--job<>'manager'表示：不把job中职位manager显示出来
select * from emp e where 
(deptno=10 and job='manager') or (deptno=20 and job='clerk')
or (job<>'manager' and job<>'clerk' and sal>=2000)

select distinct job from emp where comm>0

select * from emp where comm is null or comm<100

--datediff(year,hiredate,getdate()):计算从hiredate到getdate()的时间
--:year：按照年来算,hiredate:开始时间
--getdate()结束时间(获取系统时间)
select * from emp where datediff(year,hiredate,getdate())>30

--首字母为大写，其他为小写
select upper(substring(ename,1,1))+lower(substring(ename,2,len(ename))) from emp

select * from emp where ename like '_____'
select * from emp where len(ename)=5

select substring(ename,1,3) from emp

--replace(名字,'YY','XX')表示YY用XX来代替
select replace(ename,'a','A') from emp

select ename,hiredate from emp where datediff(year,hiredate,getdate())>10

--order by 默认是升序的（asc）
--降序（desc）
select * from emp order by ename

select ename,hiredate from emp order by hiredate asc

--先按照job排序,如果job相同按照sal来排序
select ename,job,sal from emp order by job desc,sal

--datepart(year,hiredate):把hiredate的年，取出来
select ename,datepart(year,hiredate) y,datepart(month,hiredate) m
from emp order by m,y

--列出至少有一个员工的所有部门

--1.得到每个部门的各有多少员工
select count(*),deptno from emp group by deptno

select count(*),deptno from emp group by deptno having count(*)>5 

select * from emp where sal>(select sal from emp where ename='smith')

select w.ename,d.ename from emp w,emp d where w.mgr=d.empno

select w.ename,w.hiredate,d.ename,d.hiredate from emp w,emp d 
where w.mgr=d.empno and w.hiredate>d.hiredate

select * from dept
select dname,ename from emp e right join dept d on e.deptno=d.deptno

select ename,dname from emp e,dept d where e.deptno=d.deptno and e.job='clerk'

select min(sal),job from emp group by job having min(sal)>1500

select * from emp where sal>(select avg(sal) from emp)
select * from emp where job=(select job from emp where ename='scott')

select sal from emp where deptno=30
select ename,sal from emp where sal in(select sal from emp where deptno=30)

select ename,sal from emp where sal>(select max(sal) from emp where deptno=30)

select count(*)总人数,avg(sal)平均工资,avg(datediff(year,hiredate,getdate()))服务年限,deptno
from emp group by deptno

select ename,dname,sal from emp e,dept d where e.deptno=d.deptno

select d2.dname,d2.loc,isnull(d.c,0) from dept d2 left join
(select count(*) c,deptno de from emp group by deptno) d
on d2.deptno=d.de

select min(sal),job from emp group by job

select min(sal) from emp where job='manager'

select (sal+isnull(comm,0))*12 年工资,ename from emp order by 年工资 desc