Server插入数据和删除数据,SQL多表查询

--用INSERT插入单行数据

   在SQL中,可以通过INSERT...VALUES语句直接向数据库表中插入数据。可以整行,也可以部分列。
    基本语法:
    INSERT INTO table_name
                [column1,column2...]
    VALUES      (values1,values2...)
    如果table_name后面没有指定列,默认是整行插入。使用语句时,要遵守以下准则:
    1、插入数据类型应与加入该字段的数据类型相同。
    2、数据的大小应在列规定的范围内。
    3、在VALUES中,列出的数据位置必须与字段的排列位置相对应。

    创建一个数据表
    ID(ID),INT类型,要求是主键,且非空唯一,自增长
    姓名(PName),CHAR类型,要求是非空且唯一
    性别(Sex),CHAR类型,非空
    生日(Birthday),SMALLDATETIME类型,可以为空
    类别(Type),用INT型数表示:0代表朋友,1同学 ,2同事,3亲戚,4其他关系。默认值为0
    家庭地址(Address),VARCHAR类型,可以为空
    移动电话(MovePhone),CHAR类型,可以为空
    家庭电话(HomePhone),CHAR类型,可以为空
    电子邮件(Email),VARCHAR类型,可以为空

    CREATE TABLE ComInfo
    (
        ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
        PName CHAR(12) NOT NULL UNIQUE,
        Sex CHAR(2) NOT NULL,
        Birthday SMALLDATETIME,
        Type INT NOT NULL DEFAULT 0,
        Address VARCHAR(16),
        MovePhone CHAR(11),
        HomePhone CHAR(11),
        Email VARCHAR(35)
    )

    --DROP TABLE dbo.ComInfo
    --DELETE FROM dbo.ComInfo WHERE ID=6
    SELECT * FROM dbo.ComInfo

    --向ComInfo插入两行数据
    INSERT INTO dbo.ComInfo           
    VALUES  ( '张三' , -- PName - char(12)
              '男' , -- Sex - char(2)
              '1979/9/4' , -- Birthday - smalldatetime
              1 , -- Type - int
              '山东烟台' , -- Address - varchar(16)
              '13576273872' , -- MovePhone - char(11)
              NULL , -- HomePhone - char(11)
              'zhangsan@163.com'  -- Email - varchar(35)
            ),
            ( '李二花' , -- PName - char(12)
              '女' , -- Sex - char(2)
              '1999/3/4' , -- Birthday - smalldatetime
              1 , -- Type - int
              NULL , -- Address - varchar(16)
              '13576756872' , -- MovePhone - char(11)
              '02755642454' , -- HomePhone - char(11)
              'lierhua@163.com'  -- Email - varchar(35)
            )
     

      注意:
          1、如果插入的某个字段为空,则该字段对应的values值为"NULL",不允许什么都不填。
          2、如果插入的字段定义为NOT NULL,插入数据时写入NULL,则违背了字段的非空约束,会报错,如果不确定值,是可以用空格代替的。
          3、许多数据库管理系统建立了UNIQUE属性,意味着这个字段必须唯一,并且只能出现一次。例如:PName字段就设有此属性,如果再次插入相同的名字,则会报错。
          4、一个规范化的表应有一个唯一字段或关键字段,这个字段在表之间连接数据时是有用的,它通常能够在使用索引时提高查询速度。
          5、插入多条数据时,只需要用","隔开,然后继续写下一条数据即可。

    --特定字段数据插入
        在插入数据时,只指定插入其中几个字段会怎么样呢?看看以下3种情况:
        1、如果该字段有一个缺省值,该值会被使用。前面在介绍表创建的时候我们已经讲过,允许通过DEFAULT关键字为列创建缺省默认值。例如,假设插入新记录时没有给字段new_column提供数据,而这个字段有一个缺省值"some value"。
        2、如果该字段可以接受空值,而且没有缺省值,则会被插入空值。
        3、如果该字段不能接受空值,而且没有缺省值,则就会出现错误。

        INSERT INTO dbo.ComInfo(PName,Sex,Email)     
        VALUES  ( '李彤彤' , -- PName - char(12)
                  '女' , -- Sex - char(2)
                  'litongtong@163.com'  -- Email - varchar(35)
                 )
        SELECT * FROM dbo.ComInfo
      

    注意:
            1、没有涉及到代码插入的列,值都为NULL,前提是这个列的属性是可空的,如果该列为NOT NULL,但是又没有插入数据,则会报错。
            2、如果该列有默认值,而又没有插入数据,则结果采用默认的值。

--通过视图插入行
    除了通过视图显示数据以外,我们还可以在INSERT语句中通过视图向视图底层的表(视图本身不存储数据,它所显示的数据都是其底层表的数据)中插入数据。通过视图向表中插入数据的语法与直接向表中插入数据的语法是一样的,只是在INSERT子句中使用的是视图名而不是表名。
     语法:
     INSERT INTO view_name
                 [column1,column2...]
     VALUES      (values1,values2...)

   创建视图并查询
    CREATE VIEW View1_ComInfo
    AS 
    SELECT PName,Sex,Address,MovePhone FROM dbo.ComInfo WHERE Address IS NOT NULL

    SELECT * FROM View1_ComInfo
    

  --向视图插入数据
      INSERT INTO View1_ComInfo VALUES('赵六','男','河南安阳','13909342343')
      SELECT * FROM dbo.ComInfo
      SELECT * FROM View1_ComInfo
     

      注意:
         1、跟前面一样,如果不指定添加哪几列,则默认添加整行。
         2、跟直接向表中插入一样,DBMS将对未指定值得任何列使用默认值,如Type列。如果没有指定默认值,则插入时将为NULL值。如果此列属性为NOT NULL,则会报错。
         3、视图最大的缺点是,如果表中某列并不包含在视图中,那么就不能给列添加数据。

    --使用SELECT语句插入值
      INSERT...SELECT语句实际上包含两部分:INSERT插入和SELECT查询,语法如下:
        INSERT INTO table_name
                    [column1,column2...]
        SELECT     column1,column2...
        FROM          table_name2
        WHERE       search_condition
        实际上,该语句是将查询语句的输出结果插入到表中。适用于INSERT...VALUES语句的规则同样也适用于INSERT...SELECT语句。另外,使用INSERT...SELECT语句,应遵循以下几点新准则。
          1、SELECT语句不能从正在被插入的表中选择数据,即table_name与table_name2不能为同一个表。
          2、在INSERT INTO语句中,列的数目必须等于从SELECT语句返回的列的数据。
          3、在INSERT INTO语句中,列的数据类型必须与从SELECT语句返回的列的数据类型相同。
        INSERT...SELECT语句通常用于创建查找表,提高检索性能。查找表可以包含分布在多个数据库的多个表中的数据。因为多个表连接处理起来比简单的查询更慢,我们对一个查找表执行SELECT查询,则明显快于执行又长又复杂的连接查询。
        注意:查找表通常存储在客户/服务器环境中的客户机中,用来降低网络信息流量。

        另外,INSERT...SELECT语句的另一个用途是备份表,备份将要删除、截断数据或重新装入数据的表。这个过程需要建立一个临时表,并且通过从原始表上选择任何内容,将包含在原始表的数据插入临时表。
        注意:许多数据库系统支持临时表。临时表的存在是相对数据库连接的寿命而言的,当连接终止时,它就被删除。

    --用INSERT...SELECT语句复制数据到表
      --创建一个新表MyFriend,包含FName,Sex,Age,然后将ComInfo中的朋友(Type值为0)和同学(Type值为1)相关信息复制到MyFriend中。
        CREATE TABLE MyFriend
        (
            ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
            FName CHAR(12) NOT NULL,
            Sex CHAR(2),
            Age INT
        )
        INSERT INTO dbo.MyFriend
                ( FName, Sex, Age )
        SELECT PName,Sex,DATEDIFF(YEAR,Birthday,GETDATE()) 
        FROM dbo.ComInfo WHERE Type IN(0,1)

        SELECT * FROM dbo.MyFriend
        SELECT * FROM dbo.ComInfo
      

     --因为在INSERT INTO语句中,列的数据类型必须与从SELECT语句返回的列的类型相同,MyFriend表中的Age列是INT,而ComInfo是SMALLDATETIME,所以要用DATEDIFF函数与当年年份比较,获取年龄。

  --利用子查询向表中插入数据
        SELECT * FROM dbo.TEACHER
        SELECT * FROM dbo.COURSE
     数据表sql下载地址:https://files.cnblogs.com/files/liuchenxing/sql.rar
     --将TEACHER中教"设计理论","数据库设计"课程的教师相关信息插入到MyFriend中。
        INSERT INTO dbo.MyFriend
                ( FName, Sex, Age )
        SELECT TNAME,TSEX,AGE FROM dbo.TEACHER WHERE CNO IN(SELECT CNO FROM dbo.COURSE WHERE CNAME IN('计算机软件基础','计算机硬件基础'))

        SELECT * FROM dbo.MyFriend
      

      --如果要频繁对多个表的数据进行查询,我们可以将多个表需要的数据合并到一个临时表中,作为新表,在执行SQL语句,则查询较为简单而且执行更快。

--将多个表中需要的数据合并到一个临时表中
      --创建Temp_Teacher表,存储所有教师的姓名,所在系,所教课程,学时以及这门课程的最高成绩的学生,其中教师姓名来自TEACHER,课程名,学时来自COURSE,最高成绩来自STUDENT。
        CREATE TABLE Temp_Teacher
        (
            ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
            TName CHAR(8) NOT NULL,
            Dname VARCHAR(20),
            Cname VARCHAR(20),
            CTime INT,
            Sname CHAR(8)
        )

        INSERT INTO dbo.Temp_Teacher
                ( TName, Dname, Cname, CTime, Sname )
        SELECT t.TNAME,t.DNAME,c.CNAME,c.CTIME,SNAME 
        FROM dbo.TEACHER AS t LEFT OUTER JOIN dbo.COURSE AS c ON c.CNO = t.CNO LEFT OUTER JOIN dbo.STUDENT AS s ON s.CNO = t.CNO 
        AND s.MARK=(SELECT AVG(MARK) FROM dbo.STUDENT WHERE CNO=s.CNO)

        SELECT * FROM dbo.Temp_Teacher
      

     注意:
            1、因为要求Temp_Teacher表哦要有所有教师信息,所以采用左连接(LEFT OUTER JOIN)的方式。
            2、其实,不难发现,这个可以通过视图来实现,但是视图本身不存储数据,只是在数据库中个存储一个定义,其操作的任然是各底层的数据,因此并不能提高查询速度。

    --表中数据的复制
      通过INSERT...SELECT语句,我们可以从其他表选择记录,插入到新表,但是前提是该新表在插入前已经创建完成。而在SQL中,可以通过INSERT...INTO语句直接将表中的数据复制到新表,而且该新表不需要预先创建。
        INSERT...INTO语法:
        SELECT    column1,column2...
        INTO    new_table
        FROM    table_name
        WHERE    search_condition
        INSERT...INTO语句首先创建表new_table,而后将表table_name中满足search_condition条件的列column1,column2...中的字段复制到表new_table中。
     关于INSERT...INTO,说明以下几点:
            1、创建的新表包含的字段与SELECT语句选择的字段相同。
            2、前面介绍的各种SELECT查询语句,包含HAVING、ORDER BY子句、各种函数、通配符、聚合函数等等,这里都可以用。
            3、它可以连接多表,将多个数据库表中的数据一次性复制到新表。
            4、不管有多少数据、来自多少个表,所有数据最终都将被复制到一个新表中,即语句中新建的表。

        --用INSERT...INTO语句复制表
            --将ComInfo中的数据复制到新表NEW1_ComInfo中。
            SELECT PName,Sex,Type,Email
            INTO NEW1_ComInfo
            FROM dbo.ComInfo

            SELECT * FROM dbo.NEW1_ComInfo

  表的着力连接

首先在本身的Student表中插入几条数据,由于自家的表已经成立落成了,所以就从未有过创立表的
sql 语句了,可是能够看笔者的上一篇小说:

         图片 1 

    SQL的一个重中之重特点正是能通过JOIN关键词,从五个交叉表中询问、解析数据。

  连接表的目标

插入数据sql语句:

    在关周全据库中,数据表设计的一个生死攸关尺度正是要防止冗余性。

1  insert into Student(S_StuNo,S_Name,S_Sex,S_Height)
2   select '001','项羽','男','190' union 
3   select '002','刘邦','男','170' union 
4   select '003','貂蝉','女','180' union 
5   select '004','天明','男','155' union 
6   select '005','少司命','女','175' 
    1. 减掉了冗余音信,节省了数据仓库储存款和储蓄空间。
    2. Server插入数据和删除数据,SQL多表查询。简化了数量修改、维护操作。

自然,也得以像下边那样写,不过自身个人习于旧贯用地方这种格局。

  学习本节须求的数据表:

1   insert into Student(S_StuNo,S_Name,S_Sex,S_Height) values ('001','项羽','男','190')
2   insert into Student(S_StuNo,S_Name,S_Sex,S_Height) values ('002','刘邦','男','170')
3   insert into Student(S_StuNo,S_Name,S_Sex,S_Height) values ('003','貂蝉','女','180')
4   insert into Student(S_StuNo,S_Name,S_Sex,S_Height) values ('004','天明','男','155')
5   insert into Student(S_StuNo,S_Name,S_Sex,S_Height) values ('005','少司命','女','175')

图片 2图片 3

图片 4

CREATE TABLE TEACHER
(
    ID INT IDENTITY (1,1) PRIMARY KEY ,  --主键,自增长
    TNO INT NOT NULL, --教工号
    TNAME CHAR(10) NOT NULL, --教师姓名
    CNO INT NOT NULL, --课程号
    SAL INT, --工资
    DNAME CHAR(10) NOT NULL, --所在系
    TSEX CHAR(2) NOT NULL, --性别
    AGE INT NOT NULL --年龄
)
INSERT INTO dbo.TEACHER VALUES( 1,'王军',4,400,'数学','男',32)
INSERT INTO dbo.TEACHER VALUES( 2,'李彤',5,6600,'生物','女',54)
INSERT INTO dbo.TEACHER VALUES( 3,'王永军',1,1000,'计算机','女',45)
INSERT INTO dbo.TEACHER VALUES( 4,'刘晓婧',2,8000,'计算机','女',23)
INSERT INTO dbo.TEACHER VALUES( 5,'高维',8,6000,'电子工程','男',54)
INSERT INTO dbo.TEACHER VALUES( 6,'李伟',7,230,'机械工程','女',23)
INSERT INTO dbo.TEACHER VALUES( 7,'刘辉',3,0,'生物','女',65)
INSERT INTO dbo.TEACHER VALUES( 8,'刘伟',9,500,'计算机','男',23)
INSERT INTO dbo.TEACHER VALUES( 9,'刘静',12,0,'经济管理','男',45)
INSERT INTO dbo.TEACHER VALUES( 10,'刘奕锴',13,70000,'计算机','女',65)
INSERT INTO dbo.TEACHER VALUES( 11,'高维',14,70000,'经济管理','男',61)

CREATE TABLE COURSE
(
    ID INT IDENTITY (1,1) PRIMARY KEY ,  --主键,自增长
    CNO INT NOT NULL, --课程号
    CNAME CHAR(30) NOT NULL, --课程名称
    CTIME INT NOT NULL, --学时
    SCOUNT INT NOT NULL, --容纳人数
    CTEST SMALLDATETIME NOT NULL, --考试时间
)
INSERT INTO dbo.COURSE VALUES( 4,'应用数学基础',48,120,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 5,'生物工程概论',32,80,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 1,'计算机软件基础',32,70,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 2,'计算机硬件基础',24,80,'2006-6-28')
INSERT INTO dbo.COURSE VALUES( 8,'模拟电路设计',28,90,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 7,'机械设计实践',48,68,'2006-7-14')
INSERT INTO dbo.COURSE VALUES( 3,'生物化学',32,40,'2006-7-2')
INSERT INTO dbo.COURSE VALUES( 9,'数据库设计',16,80,'2006-7-1')
INSERT INTO dbo.COURSE VALUES( 6,'设计理论',28,45,'2006-6-30')
INSERT INTO dbo.COURSE VALUES( 10,'计算机入门',25,150,'2006-6-29')
INSERT INTO dbo.COURSE VALUES( 11,'数字电路设计基础',30,125,'2006-6-20')
CREATE TABLE STUDENT
(
    ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    SNO CHAR(4) NOT NULL,    --学号
    SNAME CHAR(10) NOT NULL, --姓名
    DNAME CHAR(10) NOT NULL, --系
    SSEX CHAR(2) NOT NULL,   --性别
    CNO INT ,                --课程号
    MARK DECIMAL(3,1),       --成绩
    TYPE CHAR(4)             --课程类型
)
INSERT INTO dbo.STUDENT VALUES('9701','刘建国','管理工程','男',4,82.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9701','刘建国','管理工程','男',10,70,'选修')
INSERT INTO dbo.STUDENT VALUES('9701','刘建国','管理工程','男',1,78.5,'选修')
INSERT INTO dbo.STUDENT VALUES('9702','李春','环境工程','女',5,63,'必修')
INSERT INTO dbo.STUDENT VALUES('9702','李春','环境工程','女',10,58,'选修')
INSERT INTO dbo.STUDENT VALUES('9703','王天','生物','男',5,48.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9703','王天','生物','男',2,86,'选修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',4,76,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',1,92,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',2,89,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',9,80,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李华','计算机','女',8,70,'选修')
INSERT INTO dbo.STUDENT VALUES('9705','孙庆','电子工程','男',8,79,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孙庆','电子工程','男',1,59,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孙庆','电子工程','男',11,52,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孙庆','电子工程','男',6,68,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高伟','机械工程','男',13,93,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高伟','机械工程','男',12,88.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高伟','机械工程','男',1,78,'选修')
INSERT INTO dbo.STUDENT VALUES('9706','高伟','机械工程','男',10,76,'选修')

SQL Server语句把1个表的数额插入到另叁个表,有以下两种办法:

数据表

先是种,当要插入的表(Student_back)不存在时

 

1  --把表 Student 中性别为 '男' 的学生信息 插入到表 Student_back(不存在) 中
2   select * into Student_back from Student where S_Sex='男'

  1、轻松的贰表连接

图片 5

SELECT TNAME,DNAME,CNAME,CTEST FROM dbo.TEACHER,dbo.COURSE WHERE dbo.TEACHER.CNO=dbo.COURSE.CNO   --查询老师和系表的数据

未钦命具体列,就能够自行创制二个跟(Student)同样的表并把数量插入

 图片 6

图片 7

  依据上述SQL,可精通实行进程:

点名具体要插入的列就能够依据钦点的列创造一个表,并把多少插入

    • 系统率先实践FROM子句,这里FROM子句列出的有五个表TEACHE大切诺基和COURubiconSE,DBMS将总计那七个表的笛卡儿积,列出那多个表中央银行的富有极大概率构成,形成6在那之中间表。中间表的每条记下包罗了五个表中的持有行。
    • 日后系统将进行WHERE子句,依照“dbo.TEACHELacrosse.CNO=dbo.COU牧马人SE.CNO”关系对中间表进行找寻,去除那么些不满意该规则的笔录。
    • 终极系统实施SELECT语句,从实施WHERE子句后拿走的中间表的每条记下中,提取TNAME,DNAME,CNAME,CTEST那四个字段的新闻作为结果表。

第三种,当要插入的表自个儿便存在时

  注意:

1 --把表 Student 中性别为 '女' 的学生信息 插入到表 Student_back(存在)中
2   insert into Student_back(S_StuNo,S_Name,S_Sex,S_Height)
3   select S_StuNo,S_Name,S_Sex,S_Height  from Student
4   where S_Sex='女'

    由于两张表都存在同样的字段CNO,所以在询问的时候要指明来自哪张表,如dbo.TEACHE中华V.CNO、dbo.COU福特ExplorerSE.CNO,而别的全数重名的字段,也要拓展同样的处理,不然会报错。

图片 8

  问题:

因为字段 S_Id
是标记列,不能够有显示插入的值,所以那边不可不钦命要插入的列。

    表的总是时一句关系WHERE子句来定义的,实际花费中,也必定更要用到事关连接。假诺不指明连接关系吧?

后面说了成立表的sql语句,下边说说删除表的sql语句:

    SELECT TNAME,DNAME,CNAME,CTEST FROM dbo.TEACHER,dbo.COURSE
    SELECT COUNT(*) AS 总条数 FROM dbo.TEACHER,dbo.COURSE  --查询一共有几条数据

确切的说删除表的法子有 drop:

    图片 9

1 drop table Student_back

 

如此那般就删除表了,那样的删除不止会删除表中的多寡,还包蕴表结构、字段、视图、索引、触发器和信赖的自律等等。此方法慎用!!!

   2、三表查询(在WHERE子句中举行夺标查询)

truncate:

SELECT SNAME,dbo.STUDENT.SNAME,CNAME,CTEST,MARK,dbo.TEACHER.TNAME FROM dbo.TEACHER,dbo.COURSE,dbo.STUDENT WHERE dbo.TEACHER.CNO=dbo.COURSE.CNO AND dbo.COURSE.CNO=dbo.STUDENT.CNO

  使用别名,S,T,C
SELECT SNAME,S.SNAME,CNAME,CTEST,MARK,TNAME FROM dbo.TEACHER AS T,dbo.COURSE AS C,dbo.STUDENT AS S WHERE T.CNO=C.CNO AND C.CNO=S.CNO
  
1 truncate table Student_back

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图