数据库基础

插入

自增列插入

set identity_insert a on--打开
INSERT INTO a(id,name)
     VALUES(11,'a')
     go
set identity_insert a off;--关闭

一定要把自增列的字段名写出来不然依旧会报这个错，再次插入时在这个基础上自增

从一个表复制到另一个表

create table aa(
id int,
name nvarchar(50)
)
--全部复制
insert  aa  select id,name from a
--复制一部分
insert top(1)  aa select id,name from a
--复制个别字段
insert  aa(id) select id from a

--插入固定值
insert aa select 1,name from a
insert aa select id,'bbbbbb' from a
--插入存储过程
create proc sp_aa
as
select * from a 
go
insert aa exec sp_aa
--插入默认值 就是直插入数据库表中有默认值的字段
insert aa default values

 

删除

truncate table aa--直接删除表

与delete的区别

1.t删除后不再事务日志里记录delete记录 所以删除速度快但是不能根据事务日志文件恢复

2.delete删除后标识列不会重新排序 truncate会

3.truncate不能删除参与索引视图的表，delete会

4.有外键的情况下不能使用truncate

5.删除时 用delete会锁定所有行和页，用truncate不会

查询

--查询前十条
select top 10  * from aa order by id
--查询前百分之十条
select top 10 percent * from aa order by id
--查询前十条(按照id排序，id相同的但是超过十行时 吧超过的id一样的加上)
select top 10 with ties * from aa order by id desc
--查询不重复的数据
select distinct name from aa
--多个字段同事不重复
select distinct name,id from aa
--查询表里的标识列
select $identity from  aa
--查询表里的guid列
select $rowguid from  aa
--查询 cross join a表里的每条数据都和另一个表形成一条新的数据
select * from aa cross join a 
--jion on
select * from a  join aa on aa.id=a.bid
--left jion
select * from aa left join a on aa.id=a.bid
--full join
select * from aa full join a on aa.id=a.bid

--转换成时间格式 
convert(datetime,'1993.09.15')
--获取年，当前时间
year(getdate()) 
--模糊查询
/*通配符
 % 代替零个或多个任意字符串
 _代替单个字符
 []替代指定范围([a,f][abcdef])
 [^]替代不属于的范围
 '%a'最后一个字是a的
 '_a'最后一个字是a的并且只有两个字
 '[ab]d'最后一个是d 并且前面是 a或者b的
 '[^ab]d'最后一个是d 并且前面不是 a或者b的
 '%[_]%'带有_的
*/

例子

--学生表
create table Student(
S# int,Sname nvarchar(50),Sage int,Ssex nvarchar(50)) 
go
create table Course(C# int,Cname nvarchar(50),T# int) --课程表
create table  SC(S# int,C# int,score float) --成绩表
create table  Teacher(T# int,Tname nvarchar(50),) --教师表
insert into Student values(1,'李文静',20,'女')
insert into Student values(2,'李静',20,'男')
insert into Student values(3,'李文',20,'女')
insert into Student values(4,'李',20,'女')

insert into Course values(1,'001',1)
insert into Course values(2,'002',2)
insert into Course values(3,'003',1)
insert into Course values(4,'004',2)

insert into SC values(1,1,90)
insert into SC values(1,2,93)
insert into SC values(2,1,90)
insert into SC values(2,2,80)
insert into SC values(3,2,90)
insert into SC values(3,3,90)
insert into SC values(1,3,90)


insert into Teacher values(1,'叶平')
insert into Teacher values(2,'ggg')
insert into Teacher values(3,'李k')
--1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select a.S# from SC as a 
            join SC as b on a.S#=b.S# 
            and a.C#='001'
            and b.C#='002'
            and a.score>b.score
--2、查询平均成绩大于60分的同学的学号和平均成绩；
select S#,AVG(score) as 平均成绩 from SC  group by S# having AVG(score)>60
--3、查询所有同学的学号、姓名、选课数、总成绩；
select Student.S#,Student.Sname, COUNT(SC.C#) as 选课数,SUM(SC.score) as 总成绩 from Student left join SC on Student.S#=SC.S#  group by Student.S#,Student.Sname
select Student.S#,Student.Sname,COUNT(SC.C#) as 选课数,SUM(SC.score) as 总成绩 from SC,Student where Student.S#=SC.S#  group by Student.S#,Student.Sname

--4、查询姓“李”的老师的个数；
select  count(*) as geshu from Teacher where Teacher.Tname like '李%'

--5、查询没学过“叶平”老师课的同学的学号、姓名；
select S#,Sname from Student  where Student.S# not in(
                  select S# from SC  join Course on sc.C#=Course.C#
                                     join Teacher on Course.T#=Teacher.T# and Teacher.Tname='叶平')

--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select distinct Student.S#,Sname from Student join SC on (SC.C#='001' or SC.C#='002') and SC.S#=Student.S#

--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select SC.S#,Sname from Student,SC,Course,Teacher  where 
Student.S# =SC.S# 
and Course.C#=SC.C# 
and Course.T#=Teacher.T#
and Teacher.Tname='叶平' group by SC.S#,Sname having COUNT(SC.S#)>=(select COUNT(*) from Teacher,Course where Teacher.T#=Course.T# and Teacher.Tname='叶平')