黑马程序员-数据库基础语法总结

来源：互联网发布：java web 分层编辑：程序博客网时间：2024/06/05 16:16

---------------------- ASP.Net+Unity开发、.Net培训、期待与您交流！ ----------------------

1.如何创建数据库

create database MySchool

(

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

)

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,--入学时间

)

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