操作数据库的常用SQL语句--摘自：无极网络教程官方博客
--创建数据库
use master
go
Set NOCOUNT ON
Go
if not exists (select * from Sysdatabases where name='mydb')
Begin
Create Database mydb on Primary
(
name ='mydb_mdf',
filename ='E:\Data\mydb_mdf.mdf',
size =3MB,
maxSize =100MB,
filegrowth =10%
)
log on
(
name ='mydb_ldf',
filename ='E:\Data\mydb_ldf.ldf',
size =3MB,
maxsize =100MB,
filegrowth =10%
)
End
--切换到港创建的数据库
go
use Mydb
--创建 学生信息表
go
If not Exists(Select * from Sysobjects Where Name='Student')
Begin
Create Table Student
(
stuid int identity(1,1) not null primary key, --编号
stuno varchar(50) not null, --学号
stuname varchar(50) not null, --姓名
stucls varchar(20) not null, --班级
stusex int default 1, --性别
regdate datetime default getdate(), --注册时间
graduatedate datetime default getdate() --毕业时间
)
End
go
--初始化数据
Insert Into Student (stuno,stuname,stucls,stusex,regdate)
Values ('qz08240401','张柏芝','qz082404',2,'1980-01-01')
Insert Into Student (stuno,stuname,stucls,stusex,regdate)
Values ('qz08240402','刘德华','qz082404',1,'1980-02-01')
Insert Into Student (stuno,stuname,stucls,stusex,regdate)
Values ('qz08240403','梅艳芳','qz082404',2,'1980-03-01')
Insert Into Student (stuno,stuname,stucls,stusex,regdate)
Values ('qz08240404','张学友','qz082404',2,'1980-04-01')
go
--创建课程表
If not Exists (Select * from Sysobjects where name='courses')
Begin
Create Table courses
(
cid int identity(1,1) not null primary key, --序号
cname varchar(50) not null, --课程名称
teacher varchar(50) not null --教师
)
End
go
--初始化课程表
Insert Into courses (cname,teacher) values ('高等数学','李老师')
Insert Into courses (cname,teacher) values ('大学英语','王老师')
Insert Into courses (cname,teacher) values ('数据结构','张老师')
Insert Into courses (cname,teacher) values ('PHP高级编程','邹老师')
go
--创建成绩表
If Not Exists (Select * from Sysobjects where name='marks')
Begin
Create Table marks
(
id int identity(1,1) not null primary key,
cid int not null,
stuno varchar(50) not null,
mark int default 0
)
End
go
--初始化成绩表
Insert Into marks (cid,stuno,mark) Values (1,'qz08240401',90)
Insert Into marks (cid,stuno,mark) Values (1,'qz08240402',95)
Insert Into marks (cid,stuno,mark) Values (2,'qz08240402',98)
Insert Into marks (cid,stuno,mark) Values (3,'qz08240402',100)
Insert Into marks (cid,stuno,mark) Values (1,'qz08240403',88)
Insert Into marks (cid,stuno,mark) Values (1,'qz08240404',100)
go
--创建雇员信息表
If not Exists (Select * From Sysobjects where name='Employers')
Begin
Create Table employee
(
Id int Identity(1,1) not Null Primary Key,
firstname varchar(50) not null,
lastname varchar(50) not null,
familyname varchar(50) not null,
department int not null,
age int check (age>0 and age<120),
payment money default 800
)
End
--创建部门信息表
go
If Not Exists (Select * From Sysobjects WHERE name='DepartMent')
Begin
Create Table DepartMent
(
departmentid int identity(1,1) not null Primary key,
departname varchar(50) not null
)
End
go
--初始化雇员信息表
Insert Into employee (firstname,lastname,familyname,department,age,payment)
Values ('john','denver','green',1,22,2000)
Insert Into employee (firstname,lastname,familyname,department,age,payment)
Values ('Aaron','denver','Alexander',1,22,8000)
Insert Into employee (firstname,lastname,familyname,department,age,payment)
Values ('john','denver','green',2,22,1500)
Insert Into employee (firstname,lastname,familyname,department,age,payment)
Values ('Alan','Albert','Alexander',2,22,3000)
Insert Into employee (firstname,lastname,familyname,department,age,payment)
Values ('Alan','Albert','john',8,22,3000)
go
--初始化Department
Insert Into department (departname) values ('办公室')
Insert Into department (departname) values ('人力资源部')
Insert Into department (departname) values ('企划部')
Insert Into department (departname) values ('技术部')
Insert Into department (departname) values ('市场部')
Go
SET NOCOUNT OFF
go
--查询学生信息并且显示中文的性别
Select stuid,stuname,stuno,stucls,stusex=
Case
when stusex=1 Then '男'
when stusex=2 Then '女'
else
'人妖'
End From Student
go
--查询学生成绩并且显示为中文等级
Select stu.stuid,stu.stuname,stu.stuno,stu.stucls,mark=
Case
when mark <60 Then '不合格'
When mark between 60 and 70 Then '合格'
when mark between 70 And 80 Then '良好'
when mark between 80 and 90 Then '优秀'
else
'天才'
End
From Student as stu inner join Marks as mk
On Stu.stuno=mk.stuno
go
--组合姓名
Select id,firstName+'.'+lastName As [name],department,payment From Employee
go
--求应缴社保和所得税
Select id,firstName+'.'+lastName As 全名,payment as 工资,payment*0.11 as 社保,payment*0.05 as 所得税
From Employee
go
--查询一个学生的某项成绩
Select distinct id,stuno,mark From Marks
go
--将学生信息按姓名升序排列
Select * From student Order By Stuname Asc
--将学生信息由注册时间升序排列
Select * From Student Order by regdate Asc
--将学生信息按班级顺序排列
Select * From Student Order by stucls Asc
Go
--将学生信息按姓名降序排列
Select * From student Order By Stuname Desc
--将学生信息由注册时间降序排列
Select * From Student Order by regdate Desc
--将学生信息按班级顺序降序排列
Select * From Student Order by stucls Desc
Go
--按照姓氏对学生降序排列，如果姓氏相同则按照入学时间倒序排列
Select * From Student Order By Stuname Desc,regDate Desc
Select * From Student Where Stuid >2 Order By Stuname Desc,regDate Desc
go
--统计每门学科的考试成绩的总分和平均分
Select Max(mark) As MaxMark,Min(Mark) As MinMark,AVG(Mark) As AvgMark,Sum(Mark) As TotalMark
From Marks Group By cid
--统计某个学生所有学科的成绩信息
Select Stu.stuno,Max(MK.mark) As MaxMark,
Min(Mk.mark) As MinMark,Avg(Mk.mark) As AvgMark,Sum(mk.mark) As Total
From Student As Stu Inner Join Marks as MK
On Stu.stuno=Mk.stuno
Inner Join courses As CU
On MK.cid=CU.cid
Group By STU.stuno,Mk.Cid
go
--统计所有学科所有成绩信息
Select courses.cid,Max(mark) As MaxMark,Min(Mark) As MinMark,
Avg(Mark) As AvgMark,Sum(Mark) As Total
From Marks right Join courses
On Marks.cid=courses.cid
Group By courses.cid
Order By courses.Cid
Go
Select * From Student
/*
Select 表1.字段名,表2.字段名.......表n.字段名
From 表1 inner join 表2
On 关系表达式 [表2 inner join 表3....]
得到的结果是多个表中公共的部分
*/
Select * From Student
Select * From Marks
Select Stu.stuid,stu.stuno,stu.stuname,MK.cid,MK.mark
From Student as stu Inner join Marks as MK
On stu.stuno=MK.stuno
Insert Into Student (stuno,stuname,stucls,stusex,regdate)
Values ('qz08240409','芙蓉姐姐','qz082404',2,'2004-05-06')
Insert into Marks (stuno,cid,mark) values ('qz08240410',1,90)
/*
Select 表1.字段名,表2.字段名.......表n.字段名
From 表1 left join 表2
On 关系表达式 [表2 left join 表3....]
得到的结果以关键字inner左边的表中的结果为准
*/
Select Stu.stuid,stu.stuno,stu.stuname,MK.cid,MK.mark
From Student as stu left join Marks as MK
On stu.stuno=MK.stuno
/*
Select 表1.字段名,表2.字段名.......表n.字段名
From 表1 right join 表2
On 关系表达式 [表2 right join 表3....]
得到的结果以关键字inner右边的表中的结果为准
*/
Select Stu.stuid,MK.stuno,stu.stuname,MK.cid,MK.mark
From Student as stu right join Marks as MK
On stu.stuno=MK.stuno
/*
Select 表1.字段名,表2.字段名.......表n.字段名
From 表1 full join 表2
On 关系表达式 [表2 full join 表3....]
得到的结果包含两变边表中所有的数据
*/
Select Stu.stuid,MK.stuno,stu.stuname,MK.cid,MK.mark
From Student as stu full join Marks as MK
On stu.stuno=MK.stuno
Select * From Marks
--去重复 Distinct
/*
Select distinct 字段名列表 [Where ]
*/
Select distinct stuno From Marks
/*
排序
Select [字段名列表] From 表名称 [Where][group by] Order by
字段名 Asc/Desc,字段名 Asc/Desc....
Asc:表示升序排列,默认的排序方式是升序排列，所以Asc可以省略
Desc:表示降序排列
*/
Select * From Student Order By stuno Asc
Select * From Student Order By stuno
Select * From Student Order By stuno Desc
Select * From Student Order By stuname Desc
Select * From Student Order By regdate Desc
--按照班级编号排序
Select * From Student Order By stucls Asc ,Stuno Desc
/*
分组：
Select 字段名列表 From 表 [Where ]
Group By 字段名列表
*/
Select * From Marks Order By cid
--获得所有学科的最高分、最低分、总分、平均分,参考人数
Select count(cid) as 人数,Max(Mark) As 最高分,
Min(Mark) As 最低分,Avg(Mark) As 平均分,Sum(Mark) as 总分
From Marks
Group By Cid
/*
列计算:可以将表中的某些列进行算术运算或者关系元素按，运算的结果为一个新的列
green.john
*/
Select * From Employee
Select FirstName+'.'+lastName As [name],payment
From Employee
/*
假设有以下订单表：
订单编号 商品编号 商品单价 购买数量 购买时间
Select 订单编号,商品编号,商品单价,购买数量，购买时间，商品单价*购买数量 As 订单总额
From 订单表
*/
浙公网安备 33010602011771号