SQL查询的艺术学习笔记--数据插入操作

use seldata
--insert 使用
--insert 基本语法
/*
insert into table_anem
[column1,colunm2,...columnN]
values 
('values1','values2',...'valuesN‘)
说明：column可省略，默认为所有列
      注意插入值和列字段定义类型相同
*/
--创建数据表：
create  table cominfo
(
pname char(10) not null unique,
sex   char(2) not null,
birthday smalldatetime,
type int not null default 0,
address varchar(25),
movephone char(11),
homephone char(11),
email varchar(30)
)
--向表中插入数据
insert into  cominfo
values ('张三','男','1979/9/4',1,'山东省烟台市','13512122211',null,'[e-mail]')
insert into cominfo
values ('李二花','女','1989/9/4',1,null,'1351212216','0531789023','[e-mail]')
select * from cominfo
--测试不允许为空字段插入
insert into cominfo
values ('王五','男','1986/7/14',2,'东北大方','134121241232341','010254545','[e-mail]')
--测试插入null值
insert into cominfo
values ('五小三',null,'1974/9/14',3,'北京东区',null,null,'[e-mail]')
--修正
insert into cominfo
values ('五小三',' ','1974/9/14',3,'北京东区',null,null,'[e-mail]')
--不允行为空时，我们可以在用‘’在引号间什么也不加或者' '加一空格
delete   cominfo where pname='五小三'
select * from cominfo
--唯一值的插入
insert into cominfo
values ('五小三','男','1981-11-12',4,'大兴区',null,null,'[e-mail]')
/*会提示：
违反了 UNIQUE KEY 约束 'UQ__cominfo__1FC9734C0BC6C43E'。不能在对象 'dbo.cominfo' 中插入重复键。
语句已终止。因为第一列采用了unique约束*/ 
--修正：
insert into cominfo
values ('五小二','男','1981-11-12',4,'大兴区',null,null,'[e-mail]')
--特定字段数据插入
--按字段插入对应的值
insert into cominfo
(pname,sex,type,email)
values
('李彤彤','女',3,'[e-mail]')
delete from cominfo where pname='李彤彤'
--上面语句等值于下面,也就是只需要值和字段对应，与插入的顺序无关
insert into cominfo
(sex,pname,email,type)
values
('女','李彤彤','[e-mail]',3)
--不允许为空的字段插入
insert into cominfo
(pname,type,email)
values
('王一',3,'[e-mail]')
--会提示我们的sex字段不允许为空，无值插入
--有默认值字段插入,
insert into cominfo
(pname,sex,email,address)
values
('小毛毛','男','[e-mail]','上海东区')
select * from cominfo where address  like '上%'
--小毛毛记录中的type虽然有非空约束，但有默认值。所以无值会自动取默认值
--通过视图插入行
select * from cominfo
--语法如下：
insert into view_name
[column1,column2,...columnN]
values  (value1,value2....valueN)
--区别于表，在这里插入数据为视图名，而不再是表名
--基于cominfo表创建视图
create view view_cominfo
as  
select pname,sex,address,movephone
from cominfo
where address is not null
select * from view_cominfo
--通过视图添加数据行
insert into view_cominfo
(pname,sex,address,movephone)
values
('赵六','男','河南安阳','1351234544')
select * from view_cominfo
select * from cominfo
--通过视图更新数据缺点：
--视图不可能包括所有列，只能更新视图所包含的列
--如果表中有非空约束列，且该列没有默认值，则需要一定出现在视图中。
create view view2_cominfo
as 
select pname,address,movephone
from cominfo
where address is not null
select * from view2_cominfo
--创建一个不包括非空约束视图
--向该视图插入数据测试
insert into view2_cominfo
(pname,address,movephone)
values
('小猫猫','大兴安区','125456548')
--不能将值 NULL 插入列 'sex'，表 'seldata.dbo.cominfo'；列不允许有 Null 值。INSERT 失败。
--语句已终止。
--插入多行记录
use seldata
--insert into table values 后接多行记录插入
--语法：
insert into table_name
(column1,cloumn2,....cloumnN)
values
(values1,values2,....valuesN),
(valuesN1,valuesN2,....valuesNN)
insert into cominfo
(pname,sex,type,address,email)
values
('小猪','男',3,'东北安兴','[e-mail]'),
('小狼','女',2,'河西大口','[e-mail]')
select * from cominfo
--使用select 语句插入值
--insert...select 语句包括insert插入语句部分和select查询语句部分
--语法结构如下：
insert into table_name1
(column1,column2,...columnN)
--插入表及字段
select column1,column2,....columnN
from talbe_name2
--查询表及字段
where searchcondition
--条件
--注意：插入表和查询表为不同表 插入字段数等于查询字段数
--      插入字段类型和查询字段类型相同或可以相互转换 
--      通过查询插入语句主要用于创建查找表以提高查询速度或者备份表
create table myfriend
(
fname char(10) not null,
sex char(2) not null,
age int
)
--创建数据表
insert into myfriend
(fname,sex,age)
select pname,sex,DATEDIFF(year,birthday,getdate())
--datediff使用：datediff--时间差(时间差的部份时间为年year,
--差值时间1 birthday，差值时间2 getdate()) datediff得到时间为整数类型
from cominfo
where type in (0,1,2,3)
select * from myfriend
--从一张表得到数据插入一张表


--利用子查询向表中插入数据
insert into myfriend
(fname,sex,age)
select tname,sex,age 
from teacher
where cno in (select cno from course where cname in ('计算机软件基础','计算机硬件基础'))
select * from myfriend
--将多个表中需要的数据合并到一个临时表
select top 3 * from teacher
select top 3 * from course
select top 3 * from student
--创建表：
create table temp_teacher
(
tname char(8) not null,
dname varchar(20),
cname varchar(20),
ctime int,
sneme char(8)
)
sp_rename 'temp_teacher.sneme', 'sname', 'column'
--修改列名:sp_rename '需要修改的数据库带列名','修改后的列名','指定为修改列'
insert into temp_teacher
(tname,dname,cname,ctime,sname)
select  tname,t.dname,cname,ctime,sname
from teacher as t
left outer join course as c
on
c.cno=t.cno
left outer join student as s
on
s.cno=t.cno
and
s.mark=(select AVG(mark) from student where cno=s.cno)
--这里没有搞懂:从自连接和聚合函数再复习  搞懂了：书里有错误应该为MAX（mark)
select * from temp_teacher
--通过左外连接得到数据
select AVG(mark) from student
select sno,sname,s.dname,s.sex,s.cno,mark,cname,t.tname,t.dname
from student as s
left join course as c
on s.cno=c.cno
left join teacher as t
on s.cno=t.cno
order by c.cname


select * from student as s where 
s.mark=(select AVG(mark) from student  where cno=s.cno)
select * from student order by cno


use seldata
select tno as '教师号',tname as '教师姓名',cno as '课程号',
       sal as '工资',dname as '系别',sex as '性别',age as '年龄'
       from teacher
select tno,tname,t.cno,dname,cname
from teacher as t
left join course as c
on t.cno=c.cno
order by t.cno


select s.sname,s.dname,s.sex,s.cno,s.mark,
       t.tname,t.dname,c.cname
       from student as s
       left join teacher as t
       on s.cno=t.cno
       left join course as c
       on s.cno=c.cno
       order by t.tname
--表数据的复制<类似创建视图，表的创建是物理存在，而视图是查询的集合表现>
--MS-SQL语法：
select column1,column2,.....columnN
into newtable_name
from table_name
where serch_condition
--MySQL Oracle语法
create newtable_name
as 
select column1,column2,....columnN
from table_name
where serch_condition


select pname,sex,TYPE,email
into newcominfo
from cominfo
select * from newcominfo
--按字段复制表
select * into newcominfo1
from cominfo
--完整复制
select * from newcominfo1
drop table newcominfo,newcominfo1
--删除表可以在表后给个表名
select  pname as 姓名,sex as 性别,type as 关系, email as 邮件地址
into newcominfo
from cominfo where email is not null
select * from newcominfo