SQL常用语句的增删改查
–创建数据库
create database test
on primary
(name=’test’,–主数据文件的逻辑名称
filename=’D:\shujuku\test.mdf’–主数据文件的物理名称及地址
size=3mb,–主数据文件的初始大小
maxsize=10mb,–主数据文件的最大值
filegrowth=3mb–主数据文件的增长率
)
log on–日志文件
(
name=’test’,
filename=’D:\shujuku\test.ldf’，
size=2mb,
maxsize=100mb,
filegrowth=15%
)
use test–使用test数据库
–创建表
create table ClassIfo
(cId int not null primary key identity(1,1),
cTitle nvarchar(10)
)
创建表
select * from ClassIfo
use test
create table StudentInfo
(
sId int not null primary key identity(1,1),
sName nvarchar(10) not null,
sSex bit default(0),
sBirth date,
sPhone char(11),
sEamil varchar(20),
cid int not null,
foreign key(cid) references ClassIfo(cid)
)
–插入 insert into 表名 values
select * from ClassIfo
insert ClassIfo(cTitle) values(‘张三’)
insert into ClassIfo values(‘张三’),(‘李四’)
insert into ClassIfo values(‘张三’,’李四’)
insert ClassIfo(cTitle) values(‘张三’)
insert into ClassIfo values(‘张三’),(‘李四’)
–修改操作update 表名 set 列名=‘修改的值’，alter table 表名 相应指令
select * from ClassIfo
–为所有的列进行修改
update ClassIfo set cTitle=’展示柜’
–为指定的行进行修改
update ClassIfo set cTitle=’李四’ where cId>2
–删除操作 delete 表名 where
select * from ClassIfo
delete ClassIfo where cid>2
–清空表 truncate table 表名
–查询 select 列名 as 别名，列名 as 别名 from 表名 as 别名（as可以省略）
–取别名 as
select ctitle as 标题 from ClassIfo as al
select al.ctitle astitle from ClassIfo al
–select top n * from classinfo 查询前n行，所有列
–select top n percent * from classinfo 查询前百分之的行的所有列
insert into ClassIfo values(‘张三’),(‘李四’),(‘王五’)
select top 3 *
from ClassIfo
–排序 order by 列名 asc,列名 asc（从小到大）/desc（从大到小），写在from的后面，如果一个列名相等，在比较另外一个列名
select * from ClassIfo
order by cId desc
–distinct 消除重复行
select distinct cid from ClassIfo
–条件查询 写在where后面
select ctitle from ClassIfo
where cId=4
–取出1班或3班的学生的信息
select * from ClassIfo
where cId in(1,3) –in指的是1或3并不是连续的
–模糊查询
–查找姓张的学生信息，%代表一个字符或多个字符
select * from StudentInfo
where sname like ‘张%’
–查询姓名为两个字的姓黄的学生信息,表示一个字符，[^0-2],表示除了0-2之外的所有的数
select * from StudentInfo
where sname like ‘黄‘
–查询一个数值为空的条件用is不能用=
–多表连接查询，两个表之间有对应的关系，可以是内连接，也可以是外连接 select *from classinfo inner join studentinfo on classinfo.cid=studentinfo.cid
–right join–右连接匹配右边边特有的数据
–left join–左连接，匹配左边特有的数据
–full join–完全外连接，同时匹配左边和右边特有的数据
–inner join–内连接，匹配两表中完全匹配的数据
select cl.sname,st.ctitle from ClassIfo as cl inner join studentinfo on cl.cit=st.cid
select * from StudentInfo
–多张表的连接查询，classinfo表和studentinfo表有关系，studentinfo和subjectinfo表有关系，依次类推
–查询三张表的所有信息
select * from classinfo as cl
inner join studentinfo as stu on cl.cid=stu.cid
inner join subjectinfo as sub on stu.cid=sub.cid
–聚合函数
sum（最大值）、avg（平均值）、min（最小值）、count（）统计所有行数的个数
–查询学生表中一共有多少人
select count() as count1 from studentinfo
–查询班级编号为一的人数
select count(*) as count1 from studentinfo where cid=1
–使用count的时候，null不会计数
select count(sphone) from studentinfo
–查询分数最高的成绩
select max(score) from scoreinfo
–查询一班中分数最高的成绩
select max(score) from scoreinfo where cid=1
–开窗函数：over，放在聚合函数后面，作用是将聚合函数统计的信息分布到每一行中
–统计信息分布到行中
select scoreinfo.,avg(score) from scoreinfo where subid=1 –会报错
–正确写法如下
select scoreinfo.,avg(score) over() from scoreinfo where subid=1
–分组：group by
–统计每个班的男女生人数
select sgender,cid count() from studentinfo
group by sgender,cid
–统计学生编号大于2的各班级的男女生人数
select cid,sgender，count() from studentinfo
where sid>2 group by cid,sgender
–分完组之后，如果还想对分组的内容进行筛选，在group by 后面加having加条件，haveing不能使用未参与分组的列
–也可以使用in的方法，having count() in(2,5,8),where中不能使用聚合函数
–统计学生编号大于2的各班级的男女生人数大于3的信息
select cid,sgender,count() from studentinfo
where cid>2 group by cid,sgender having count(*)>3
–isnull(类型,’替换内容’)的用法
–查询英语成绩，如果英语成绩为空则显示缺考
select isnull(convert(varchar(20),singlish(所查询的列名)),’缺考’) from studentinfo
–完整的sql语句及执行顺序
select distinct top n *from
table1 join table2 on …
where …
group by … having …
order by …
–联合查询 只能是有相同列的两个并且每一个列对应的值类型相同才能查询 union（并集）、union all(所有的数，包括重复的数)
–except（补集，找它左边特有的部分），intersect(交集)
–查询cid和sid的交集
select cid from classinfo
intersect
select sid from studentinfo
–将横表变为纵表
select ‘最高成绩’ as 描述信息,max(tenglish) as 成绩 from studentinfo
union
select ‘最低成绩’ as 描述信息,min(tenglish) as 成绩 from studentinfo
–快速备份表 ：select 列名 into 备份表名 from 源表名 如果备份表不存在，则会新建一个表，表的结构完全一致
--但是不会包含约束条件，如果只想包含表的结构不包含数据可以加一个不存在的条件
--向已有的表中，备份另一个表 insert into 备份表名 select 列名 from 源表名–将数据备份到一个不存在的test表中
select * into test from classinfo
–将数据备份到一个不存在的test表中，只要结构
select * into test from classinfo where 1=2
–或
select top 0 * into test from classinfo
–将数据备份到一个存在的表中
insert into test select cid from calssinfo
–类型转换：select cast(类型1(被转换) as 类型2(目标))
select convert(数据类型1(目标),数据类型2(被转换))–将89.0000转换为89.0
select cast(89.0000 as decimal(3,1))
–计算字符的ascii值 select ascii(‘字符’)
–根据ascii的值转到字符 select char(数值)
–返回字符串长度 select len(‘字符串’)
–将字符串转换为小写 select lower(‘大写’)，将字符串转换为大写 select upper(‘小写’)
–截取字符串
–从左边截取字符串
select left(‘字符串’,2)–从左边开始截取两个字符
–从右边截取字符串
select right(‘字符串’,2)–从右边截取两个字符串
select substring(‘字符串’,2,3)–从第二个字符开始，一共截取3个字符
–切掉空格
select ‘哈哈’+ltrim(‘ hello world’)–去掉左边的空格 哈哈hello world
select ‘哈哈’+rtrim( ‘hello’ )–去掉右边的空格 哈哈 hello
–去空格需要嵌套
select ‘哈哈’+rtrim(ltrim(‘ hello world’))–去掉左边的空格 哈哈hello world
–视图(对select语句进行封装，方便使用)
–语法 creat view 视图名 as select的查询语句
–子查询(在一个查询中嵌套另一个查询)关键字有=，in，exits(in的效率低于exits)
–查询参加了考试的学生的信息
select * from studentinfo
where sid in(select distinct stuid from scoreinfo)
–或
select * from studentinfo
where sid exits(select * from scoreinfo where studentinfo.stuid=scoreinfo.sid)
–case语句 语法
–查询学生的所有信息，并且判断该学生是否及格
select *, 是否及格=case
when tenglish>60 then ‘及格’
else ‘差’
end from scoreinfo–可以判断一个区间的值 >=
–case语句第二种表述方式，但是不能用于区间
select *,是否及格=case tenglishscore when 60 then ‘及格’
when 59 then ‘不及格’
else ‘优秀’
end from scoreinfo –不能用域区间判断
–异常处理 ：begin try …
end try
如果上面的语句有错误则执行下面的语句
begin catch …
end catch
–事务 判断一任务是否完成，没有完成则会回到初始的状态
–语法：begin transaction … –开始事务
comit transaction –提交事务,没有错才会执行，执行完了以后就不会执行下面的语句
rollback transaction … –回滚,出错后执行该语句
–锁 防止对数据的误操作
语法：在事务的基础上把提交操作，这样即使修改了还是会回到初始状态
begin transaction …
rollback transaction –回滚
–存储过程 将select语句封装成一个存储过程，语法：create proc 名称 参数列表 as begin … end
调用：exec 名称 参数列表
–将字符串去空格
declare @temp varchar(100)
set @temp=’ abc ‘
select ltrim(rtrim(@temp))
–转换成存储过程
create proc trim
@str1 varchar(100)
as
begin select ltrim(rtrim(@str1))
end