SQL语句的增删改查及数据库表的多表连接查询

本文旨在为想要了解和学习数据库操作的同学提供帮助，本文有点长，但没有赘述！请认真阅读！另文章内容转自宝宝要努力、cnby

SQL语句的增删改查

一、增：有2种方法

1.使用insert插入单行数据：

         语法：insert [into] <表名> [列名] values <列值>

　　 例：insert into Strdents (姓名,性别,出生日期) values ('王伟华','男','1983/6/15')

　　 注意：如果省略表名，将依次插入所有列

 

2.使用insert,select语句将现有表中的 数据添加到已有的新表中

        语法：insert into <已有的新表> <列名> select <原表列名> from <原表名>

　　例：insert into addressList ('姓名','地址','电子邮件')select name,address,email          

                from  Strdents 

      注意：查询得到的数据个数、顺序、数据类型等，必须与插入的项保持一致

 

二、删：有２中方法

1.使用delete删除数据某些数据

    语法：delete from <表名> [where <删除条件>]　　  

    例：delete from a where name='王伟华'（删除表a中列值为王伟华的行）　 

        注意：删除整行不是删除单个字段，所以在delete后面不能出现字段名

 

2.使用truncate table 删除整个表的数据

        语法：truncate table <表名>

　   例：truncate table addressList

　  注意：删除表的所有行，但表的结构、列、约束、索引等不会被删除；不能

         用于有外建约束引用的表

 

三、改　　使用update更新修改数据         

    语法：update <表名> set <列名=更新值> [where <更新条件>]

　　 例：update addressList set 年龄=18 where 姓名='王伟华'

　　 注意：set后面可以紧随多个数据列的更新值（非数字要引号）；where子句是可选的（非数字要引号），用来限制条件，如果不选则整个表的所有行都被更新

 

四、查

1.普通查询

    语法：select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列  

          名>[asc或desc]]

　　　1).查询所有数据行和列

　　　　例：select * from a

　　　　说明：查询a表中所有行和

　　　2).查询部分行列--条件查询

　　　　例：select i,j,k   from  a   where f=5

　　　　说明：查询表a中f=5的所有行，并显示i,j,k３列

　　　3).在查询中使用ＡＳ更改列名

　　　　例：select name as 姓名　from a where  gender='男'

　　　　说明：查询a表中性别为男的所有行，显示name列，并将name列改名为（姓名）显示

　　　4).查询空行

　　　　例：select name from a where email is null

　　　　说明：查询表a中email为空的所有行，并显示name列；SQL语句中用is null或者is not null

                  来判断是否为空行

　　　5).在查询中使用常量

　　　　例：select name '北京' as 地址 from　a

　　　　说明：查询表a，显示name列，并添加地址列，其列值都为'北京'

　　　6).查询返回限制行数(关键字：top )

　　　　例１：select top 6 name from a

　　　　说明：查询表a，显示列name的前６行，top为关键字（oracle 中没有top关键字

             用rownum替代）

                          select   *   from   a where   rownum<6  

　　　7).查询排序（关键字：order by , asc , desc）

　　　　例：select name

　　　　　　from a

　　　　　　where grade>=60

　　　　　　order by desc

　　　　说明：查询表中成绩大于等于60的所有行，并按降序显示name列；默认为ＡＳＣ升序

２.模糊查询

　　　1).使用like进行模糊查询

　　　　注意：like运算副只用语字符串，

　　　　例：select * from a where name like '赵%'

　　　　说明：查询显示表a中，name字段第一个字为赵的记录

　　　2).使用between在某个范围内进行查询

　　　　例：select * from a where age between 18 and 20

　　　　说明：查询显示表a中年龄在18到20之间的记录

　　  3).使用in在列举值内进行查询(in后是多个的数据)

        例：select name from a where address in ('北京','上海','唐山')

　　　　说明：查询表a中address值为北京或者上海或者唐山的记录，显示name字段

３.分组查询

　　　1).使用group by进行分组查询

　　　　例：select studentID as 学员编号, AVG(score) as 平均成绩  (注释:这里的score是列名)

　　　　　　from score (注释:这里的score是表名)

　　　　　　group by studentID

           2).使用having子句进行分组筛选

　　　　例：select studentID as 学员编号, AVG　　　　

from score

　　　　　　group by studentID

　　　　　　having count(score)>1

　　　　说明：接上面例子，显示分组后count(score)>1的行，由于where只能在没有分组

       时使用，分组后只能使用having来限制条件。

数据库表的多表连接查询

连接查询实际上是通过各个表之间共同列的关联性来查询数据的,它是关系数据库查询最主要的特征.
select 表1.字段名1，表2.字段名2，... 
from 表1，表2
where 连接条件
SQL-92标准所定义的FROM子句的连接语法格式为： 
FROM 表名 join_type 表名 [ON (连接条件)]
连接操作中的ON (连接条件) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构
成。 
连接查询分类：
1.自连接查询，对同一个表进行连接操作（可以理解为两个不同表的内连接，有时可与嵌套查询等价）
2.内连接查询，<又分为：自然连接、等值连接、不等值连接三种>
3.外连接查询，<又分为：左外连接、右外连接、全外连接三种>
4.交叉连接查询，也作无条件查询。
5.联合查询

一．自连接查询:

一个表自己与自己建立连接称为自连接或自身连接。
     进行自连接就如同两个分开的表一样，可以把一个表的某一行与同一表中的另一行连接起来。
例：
查询选学“101”课程的成绩高于“9505201”号学生成绩的所有学生记录，
并按成绩从高到低排列。
select x.* from sclass x,sclass y
where x.cno=''101'' and x.degree>y.degree and y.sno=''9505201'' and y.cno=''101''
order by x.degree desc

二. 内连接查询

内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。

1、等值连接：
所谓等值连接，是指表之间通过“等于”关系连接起来，产生一个临时表，
然后对该临时表进行处理后生成最终结果。其查询结果中列出被连接表中的所有列，
包括其中的重复列。

SELECT * 
FROM authors AS a INNER JOIN publishers AS p 
ON a.city=p.city

我们可以有两种方式，这两种是等效的 
一种是：SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e,DeptTB AS d WHERE e.deptid=d.deptid 
另外一个是：SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e INNER JOIN DeptTB AS d ON e.deptid=d.deptid 

3、自然连接：
在等值连接中消除重复列就是自然连接。（state,city在两个表中都存在）

SELECT a.*,p.pub_id,p.pub_name,p.country 
FROM authors AS a INNER JOIN publishers AS p 
ON a.city=p.city

三 外连接查询 （左外连接、右外连接、全外连接）

左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值

右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

 

A left join B 的连接的记录数与A表的记录数同
A right join B 的连接的记录数与B表的记录数同  这种说法是错误的，只有当表A与表B是一对一时才成立。

首先我们做两张表：员工信息表和部门信息表，在此，表的建立只为讲述连接的概念，所以字段非常的简单 
EmployeeTB（员工信息表）： 

employeeid employeename deptid 
0001  张三  01 
0002  李四  01 
0003  王五  02 
0004  赵六  02 
0005  郑七  NULL 

DeptTB（部门信息表） 
deptid  deptname 
01  技术部 
02  市场部 
03  工程部 

1左外联结 
但是有些情况下，我们需要知道所有员工的信息，即使他不属于任何部门。这样我们就可以采用外连接，在这里为左外连接，也就是连接中的左表的表中的记录，无论能不能在右表中找到匹配的项，都要检索，如果没有匹配的项目，那么右表中的字段值为NULL（空），在这里就代表，此员工不属于任何部门。 
检索语句为： 
SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e LEFT OUTER JOIN DeptTB AS d ON e.deptid=d.deptid 
检索的结果都是： 

employeeid employeename deptname 
0001  张三 技术部 
0002  李四 技术部 
0003  王五 市场部 
0004  赵六 市场部 
0005  郑七  NULL 

但是在这里，工程部同样不会被检索，因为，deptname是在连接的右边的表中，“工程部”在左表中不存在任何的记录，所以不会被检索。这里关注的是“连接中的左边的表” 


2、右外连接 
有时，我们需要知道，全部部门的信息，即使它没有任何的员工。在我们的查询中部门表在连接的右边，如果我们想知道右边表中的所有记录信息，那么就可以采用右外连接，如果此记录在左边的表中找不到匹配项，则相应字段（employeeid,employeename)为NULL 
检索语句为： 
SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e RIGHT OUTER JOIN DeptTB AS d ON e.deptid=d.deptid 
检索的结果都是： 

employeeid employeename deptname 
0001  张三 技术部 
0002  李四 技术部 
0003  王五 市场部 
0004  赵六 市场部 
NULL  NULL  工程部 

但在这里，郑七是不会被检索了，因为它在右表中找不到匹配项，这里关注的是“连接中的右边的表” 

3、完全外连接 
如果我们想知道所有的记录呢？无论员工有没有部门，部门有没有员工，我们都需要检索。这里就可以使用完全外连接。关注连接中的两部分。如果没有部门，部门为空，没有员工，员工信息为空。 
检索语句为： 
SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e FULL OUTER JOIN DeptTB AS d ON e.deptid=d.deptid 
检索的结果都是： 

employeeid employeename deptname 
0001  张三 技术部 
0002  李四 技术部 
0003  王五 市场部 
0004  赵六 市场部 
0005  郑七  NULL 
NULL  NULL  工程部 

四．交叉连接

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数
据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等
于6*8=48行。

 

三个表的左连接示例：

准备数据：

 

表t1 
字段名:t1_id,username,psw 
表t2 
字段名:t2_id,gname,t1_id //这里一个t1_id对应多个t2_id 
t3 
字段名:t3_id,realname,tel,t1_id //这里一个t1_id对应一个t3_id

DROP TABLE [t1] 
 CREATE TABLE [t1] (  [t1id] [int] NULL , [username] [varchar] (50) NULL , [psw] [varchar] (50) NULL )


 INSERT [t1] ( [t1id] , [username] , [psw] ) VALUES ( 1 , 'username1' , 'psw1' )
 INSERT [t1] ( [t1id] , [username] , [psw] ) VALUES ( 2 , 'username2' , 'psw2' )
 INSERT [t1] ( [t1id] , [username] , [psw] ) VALUES ( 3 , 'username3' , 'psw3' )


 DROP TABLE [t2] 
 CREATE TABLE [t2] (  [t2id] [int] NULL , [gname] [varchar] (50) NULL ,  [t1id] [int] NULL )


 INSERT [t2] ( [t2id] , [gname] , [t1id] ) VALUES ( 1 , 'ganme1' , 1 )
 INSERT [t2] ( [t2id] , [gname] , [t1id] ) VALUES ( 2 , 'ganme2' , 1 )
 INSERT [t2] ( [t2id] , [gname] , [t1id] ) VALUES ( 3 , 'ganme3' , 2 )
 INSERT [t2] ( [t2id] , [gname] , [t1id] ) VALUES ( 4 , 'ganme4' , 2 )
 INSERT [t2] ( [t2id] ) VALUES ( 5 )


 DROP TABLE [t3] 
 CREATE TABLE [t3] (  [t3id] [int] NULL , [realname] [varchar] (50) NULL , [tel] [varchar] (50) NULL ,  [t1id] [int] NULL )


 INSERT [t3] ( [t3id] , [realname] , [tel] , [t1id] ) VALUES ( 1 , 'realname' , '123' , 1 )
 INSERT [t3] ( [t3id] , [realname] , [tel] , [t1id] ) VALUES ( 3 , 'realname3' , '1234' , 3 )

遇到的麻烦是当一个realname在t2表中没有内容时,也要将此realname的username,psw,tel等显示出来,直接使用 
sql="select username,psw,gname,tel from t1,t2,t3 where t1.t1_id=t2.t1_id and t1.t1_id=t3.t1_id" 
得到的结果是:t2表中必须有某个realname的作品,才能查询出来他的信息,这肯定不是想要的结果,最后想到使用左连接,sql语句是: 
sql="select username,psw,gname,tel from (t1 left join t2 on t1.t1_id=t2.t1_id) left join t3 on t1.t1_id=t3.t1_id"

 思路：t2是数据结构中多的一表，因此先由t1与t2左连接得到最全的数据，然后再与t3连接，