1.如何创建数据库
create database MySchool
on
(
name='MySchool_data',--mdf逻辑名称
filename='d:\database\MySchool_data.mdf',--文件的路径
size=5mb,--初始大小
filegrowth=10%,--增长量
maxsize = 100mb--最大值
)
log on
(
name='MySchool_log',
filename='d:\database\MySchool_log.ldf',
size=1mb,
filegrowth=1mb,
maxsize=10mb
)
go
2.如何删除数据库
drop database MySchool
3.如何创建表
--创建Student表
create table Student
(
sId int identity(1,1) primary key,
sClassId int not null,--班级的名字
sName nvarchar(10) not null,
sAge int,
sNo numeric(18,0),--18位数字 身份证号
sSex nchar(1),
sBirthday datetime,
sIntime datetime,--入学时间
)
Go
4.如何删除表
--删除表
--drop table Student
5.如何插入数据
--插入数据
insert into Class values('软件一班','优秀班集体')
insert into Class(cName) values('软件二班')
insert Class values('网络一班','优秀班级体')
6.如何更新数据
--更新
update Student set sSex='男'
update Student set sSex='女' where sName='甄姬'
update Student set sClassId='2' where sId>=5
update Student set sAge=19 where sName='刘备' or sAge=18
update Student set sClassId=3 where (sAge>=18 and sAge<=20 )or sAge=50
select * from Student
update Student set sAge +=1 --所有年龄加
7.删除语句
--truncate table student --删除数据
--删除语句--会记录日志不清除编号
delete from Student
select * from Student
delete from Student where sId=14
--清空表中的数据清空自动编号,不记录日志
truncate table Student--会还种子自增长列
8.约束条件
--主键约束
--alter table Teacher add constraint PK_Teacher_tId primary key(tId)
--唯一约束
alter table Student add constraint UQ_Student_sNo unique(sNo)
--默认约束
alter table Student add constraint DF_Student_sAge default(18) for sage
alter table Student add constraint DF_sIntime default(getdate()) for sintime
--检查约束
alter table Student add constraint CK_Student_sAge check(sAge>=18 and sAge<=40)
alter table Student add constraint CK_Student_sSex check(sSex='男' or sSex='女')
alter table Student add constraint CK_Student_sIntime check(sIntime>sBirthday)
--外键约束
--删除约束
alter table Student drop constraint FK_Student_sClassId
--外键约束
alter table Student add constraint FK_Student_sClassId
foreign key(sClassId) references Class(cId)
on delete cascade--级联删除
注意事项:
--子表中的数据,必须在主表中有对应的值
insert into Student(sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'甄姬',18,'女',12345678901234569,'1989-8-8')
--删除主表中数据,必须先删除主表中数据对应的字表中的数据
delete from Class where cId=1
select * from Class
select * from Student
9.查找语句
select sId, sClassId, sName, sAge, sNo, sSex, sBirthday, sIntime from Student
select sName,sAge,sSex from Student
9.1修改别名
--修改别名
select sName as '姓名',sAge as '年龄',sSex as '性别'from Student
select sName '姓名',sAge '年龄',sSex '性别' from Student
select '姓名'=sName,'年龄'=sAge from Student
9.2 带条件查询
--带条件查询
select sName '姓名',sAge '年龄',sSex '性别' from Student
where sAge>20
select 1+1 as 'mm'
select GETDATE () 'time'
9.3 Top
--top输出前几个数据
select top 5 * from Student
9.4 输出百分之几
--百分之几
select top 3 percent sName,sAge,sBirthday from Student
select * from Student
9.5 distinct去除重复的数据
--distinct去除重复的数据
select distinct * from Student ---去除完全一样的数据
select distinct sName from Student
select distinct sName,sNo from Student
--/
9.6 聚合函数
--聚合函数
select * from Score
select COUNT(*) from Student
select MAX(english) from Score
select MIN(english) from Score
select SUM(english) from Score
select AVG(english) from Score--AVG没有统计null的值
select SUM(english)/COUNT(*) from Score
select MAX(sBirthday),MIN (sBirthday) from Student
select sName,sAge+6 as 'age',sSex from Student
select english*2,studentid from Score
9.7回顾小结
--带条件查询
select studentid,english from Score where english <60
select * from Student where sAge >=20 and sAge <=30
--Between…and …在之间
--查询年龄在-30岁之间的男学生
select * from Student where sAge between 20 and 22 and sSex='男'
--查询成绩在-90分之间的所有学生
select * from Score where english between 80 and 90
select * from Student where sBirthday between '1988-1-1'and '1989-12-12'
select * from Student where sClassId=1 or sClassId=2 or sClassId=3 or sClassId=4
--||||||||
select * from Student where sClassId in(1,2,3,4)
---查询张姓同学
select * from Student where LEFT(sName,1)='张'
9.8 模糊查询like
--模糊查询like
select * from Student where sName like '张%'
select * from Student where sName like '%侯%'
select * from Student where sName like '张_'
select * from Student where sName like '%飞%' or sName like '%羽%'
select * from Student where Sname like '%[飞羽]%'--[0-9] [a-z] [123]
9.9 null
--null
select 1+null
select * from Score where english is null
select * from Score where english is not null
9.10 order by
--order by
--o出现在select语句的最后
select * from Student order by sAge
select * from Student order by sAge desc
--年龄最小的个学生
select top 5 * from Student order by sAge
select * from Score order by english asc,math desc
select * from Score where english >=60 and math >=60
order by english asc,math asc
9.11分组group by
--分组group by
--select 之后的列,必须出现在聚合函数或者group by子句中
select sClassId,COUNT (*) from Student group by sClassId
select sSex,COUNT (*) from Student group by sSex
9.12 having 对group by 分组过后的数据进行筛选
--班级人数超过个
--错误,聚合函数不能出现在where中select * from Student where COUNT (*)>3
--having 对group by 分组过后的数据进行筛选
select sClassId ,COUNT (*) from Student group by sClassId having COUNT (*)>1
----每班有多少个男同学
select sClassId,COUNT (*) from Student
where sSex='男'group by sClassId
9.13 union
--union
--select 的列数要相同,对应列数据要兼容
--排序去除重复数据
select sName,sSex from Student union
select tName,tSex from Teacher
select sName,sSex,sClassId from Student union
select tName,tSex,-1 from Teacher
--union all
select sName,sSex from Student union all
select tName,tSex from Teacher
--在一个表中查询出学生的英语最高成绩、最低成绩、平均成绩
select MAX(english) from Score union all
select MIN(english) from Score union all
select AVG(english) from Score
--查询每位老师的信息,包括姓名、工资,并且在最后一行加上平均工资和最高工资
select tName,tSalary from Teacher union all
select '平均工资',AVG (tSalary) from Teacher union all
select '最高工资', MAX (tSalary) from Teacher
---一次插入多条成绩
insert into Score (studentId,english,math)
select 1,80,60 union
select 2,60,70 union
select 3,90,50 union
select 4,70,60 union
select 5,50,60
--把现有表数据插入到新表中新表不能存在
select * into newStudent from Student
select * from newStudent
select sName,sSex into student1 from Student
--删除表student1
drop table student1
select distinct sName,sSex into student1 from Student
select * from student1
10. 数据库备份
--把现有表数据插入到现有表
insert into newStudent select sClassId, sName, sAge, sNo, sSex, sBirthday, sIntime, sCode from Student
11. 函数
11.1字符串函数
--字符串函数
--LEN
select LEN('abc')
select sName,LEN(sName) from Student
select sName,LEN (sName) from Student where LEN (sName)=3
--转换大小写
select LOWER('aBCd')
select UPPER('aBCd')
--去除空格
select LTRIM(' abcd ')+'ef'
select RTRIM(' abcd ')+'ef'
select RTRIM(LTRIM(' abcd '))+'ef'
--截取
select left('张三',1)
select RIGHT('张散散',2)
select 'IP1551616414'
select LEFT ('IP1551616414',2)
select RIGHT('IP1551616414',10) +1
--///
select SUBSTRING('天佑中华',1,2)
--从开始数,两个字符
11.2 distinct去除重复的
---distinct去除重复的
select distinct SUBSTRING(sName,1,1) from Student
select distinct left(sName,1) from Student
11.3 REPLACE 替换
--替换
select REPLACE ('我爱你','爱','恨')
select REPLACE(sName,'张','李') from Student
11.4时间函数
---
select GETDATE()
select DATEADD(DAY,3,GETDATE())
select DATEADD(DD,-3,GETDATE())
select DATEADD(WEEK,1,GETDATE())
select DATEADD(WEEKDAY,1,GETDATE())
select Dateadd(month,-10,getdate())
select YEAR(GETDATE())
select MONTH(GETDATE())
select DAY(GETDATE ())
select DATENAME(YEAR,'2011-12-21')
select DATENAME(QUARTER,'2011-1-21')--季度划分
select DATENAME(WEEK,GETDATE())
select DATENAME(WEEKDAY,GETDATE())
select DATENAME(HOUR,GETDATE())
select DATEPART(YEAR,GETDATE())
select DATEPART(WEEK,GETDATE())
select DATEPART (WEEKDAY,GETDATE())
select * from employee where YEAR(eintime)=YEAR(GETDATE())and MONTH(eintime)=MONTH(GETDATE())
select DATEDIFF (DAY,GETDATE (),'2012-12-21')
select DATEDIFF(HOUR,GETDATE (),'2013-12-23')
select DATEDIFF (DAY,'2012-8-12','2012-8-13')
select * from employee where DATEDIFF (DAY,eintime,GETDATE())=0 --当天入职的员工
select * from employee where YEAR (eintime)=YEAR(GETDATE()) --DATEDIFF(MONTH,eintime,GETDATE())=0 --当月入职的员工
select * from employee where DATEDIFF(MONTH,eintime,GETDATE())=0 --当月入职的员工
--统计不同入学年数的学生个数
select DATEDIFF(YEAR,eintime ,GETDATE()) ,COUNT(*) from employee
group by DATEDIFF(YEAR,eintime ,GETDATE())
--统计学生的生日年份个数
--1988
--1989
select YEAR(sBirthday),COUNT (*) from Student
group by YEAR(sBirthday)
-----//
--类型转换
--cast
select '平均成绩:'+cast(66 as varchar(4))--最后的()是长度,能装下成绩
select LEFT('IP1551616414',2)+cast((RIGHT('IP1551616414',10)+1) As varchar(10))--3.字符串和整数加的时候错误
select CAST(89.6781333324 as numeric(5,2))--2.浮点数,保留两位小数(四舍五入)
select CAST(89.6781333324 as int)--只保留整数位
select CAST(ROUND(89.6781333324,0) as int)
select CAST (89.67899999 as numeric(5,2))
select CAST(AVG(english)as numeric(5,2)) from Score
---convert
select '平均成绩:'+CONVERT (varchar,66)
select CONVERT (numeric(5,2),AVG(english))from Score
--2013-11-30
select CONVERT (varchar,GETDATE(),23)--年月日
select CONVERT (varchar(10),GETDATE(),21)---(10)截取个字符
--23:34:07
select CONVERT (varchar,GETDATE(),24)--时间
select sName,sAge,convert ( varchar(10),SBirthday,21),CONVERT (varchar(10), sintime,21) from Student
--isnull
select studentId,english from Score
select studentId,ISNULL(english,0)from Score --为null时返回,,否则返回English的值
select SUM(english)/COUNT(*) from Score
select AVG(english) from Score
select AVG(ISNULL(english,0)) from Score
--1.日期去掉时间部分
--2.浮点数,保留两位小数
--3.字符串和整数加的时候错误
--4.AVG‘平均值’null时没有算null
--USE MySchool
---------------------- ASP.Net+Unity开发、 .Net培训、期待与您交流! ----------------------详细请查看: www.itheima.com