SQL Server 2005兼职讲师讲义（二）

第三章 SQLServer2005数据管理
1、创建、删除数据库
一、交互式：
创建后导出创建脚本如下：

USE [master]
GO
/****** 对象:  Database [testbase2]    脚本日期: 10/12/2011 13:47:02 ******/
CREATE DATABASE [testbase2] ON  PRIMARY
( NAME = N'testbase2_prim_sub_data1', FILENAME = N'E:\Database\test\testbase2_prim_sub1_data.mdf' , SIZE = 5120KB , MAXSIZE = 51200KB , FILEGROWTH = 20%),
( NAME = N'testbase2_prim_sub_data2', FILENAME = N'E:\Database\test\testbase2_prim_sub2_data.mdf' , SIZE = 5120KB , MAXSIZE = 51200KB , FILEGROWTH = 20%),
 FILEGROUP [testdase2_group1]
( NAME = N'testbase2_group1_sub1', FILENAME = N'E:\Database\test\testbase2_group1_sub1_data.ndf' , SIZE = 5120KB , MAXSIZE = 51200KB , FILEGROWTH = 5120KB ),
( NAME = N'testbase2_group1_sub2', FILENAME = N'E:\Database\test\testbase2_group1_sub2_data.ndf' , SIZE = 5120KB , MAXSIZE = 51200KB , FILEGROWTH = 5120KB ),
 FILEGROUP [testdase2_group2]
( NAME = N'testbase2_group2_sub1', FILENAME = N'E:\Database\test\testbase2_group2_sub1_data.ndf' , SIZE = 5120KB , MAXSIZE = 51200KB , FILEGROWTH = 5120KB ),
( NAME = N'testbase2_group2_sub2', FILENAME = N'E:\Database\test\testbase2_group2_sub2_data.ndf' , SIZE = 5120KB , MAXSIZE = 51200KB , FILEGROWTH = 5120KB )
 LOG ON
( NAME = N'testbase2_log1', FILENAME = N'E:\Database\test\testbase2_log1.ldf' , SIZE = 20480KB , MAXSIZE = 512000KB , FILEGROWTH = 10240KB ),
( NAME = N'testbase2_log2', FILENAME = N'E:\Database\test\testbase2_log2.ldf' , SIZE = 20480KB , MAXSIZE = 512000KB , FILEGROWTH = 10240KB )
 COLLATE Chinese_PRC_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'testbase2', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [testbase2].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [testbase2] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [testbase2] SET ANSI_NULLS OFF
GO
ALTER DATABASE [testbase2] SET ANSI_PADDING OFF
GO
ALTER DATABASE [testbase2] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [testbase2] SET ARITHABORT OFF
GO
ALTER DATABASE [testbase2] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [testbase2] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [testbase2] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [testbase2] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [testbase2] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [testbase2] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [testbase2] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [testbase2] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [testbase2] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [testbase2] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [testbase2] SET  ENABLE_BROKER
GO
ALTER DATABASE [testbase2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [testbase2] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [testbase2] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [testbase2] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [testbase2] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [testbase2] SET  READ_WRITE
GO
ALTER DATABASE [testbase2] SET RECOVERY FULL
GO
ALTER DATABASE [testbase2] SET  MULTI_USER
GO
ALTER DATABASE [testbase2] SET PAGE_VERIFY CHECKSUM 
GO
ALTER DATABASE [testbase2] SET DB_CHAINING OFF

二、语句：
1、新建数据库脚本：
CREATE DATABASE testbase1
ON
        (NAME=testbase1_data,
         FILENAME='E:\Database\test\testbase1_data.mdf')
LOG ON
        (NAME=testbase1_log,
         FILENAME='E:\Database\test\testbase1_log.ldf')
GO

2、新建数据库脚本：
CREATE DATABASE testbase2
ON
        PRIMARY
                (NAME=testbase2_prim_sub_data1,
                 FILENAME='E:\Database\test\testbase2_prim_sub1_data.mdf',
                 SIZE=5MB,
                 MAXSIZE=50MB,
                 FILEGROWTH=20%),
                (NAME=testbase2_prim_sub_data2,
                 FILENAME='E:\Database\test\testbase2_prim_sub2_data.mdf',
                 SIZE=5MB,
                 MAXSIZE=50MB,
                 FILEGROWTH=20%),
        FILEGROUP testdase2_group1
                (NAME=testbase2_group1_sub1,
                 FILENAME='E:\Database\test\testbase2_group1_sub1_data.ndf',
                 SIZE=5MB,
                 MAXSIZE=50MB,
                 FILEGROWTH=5MB),
                (NAME=testbase2_group1_sub2,
                 FILENAME='E:\Database\test\testbase2_group1_sub2_data.ndf',
                 SIZE=5MB,
                 MAXSIZE=50MB,
                 FILEGROWTH=5MB),
        FILEGROUP testdase2_group2
                (NAME=testbase2_group2_sub1,
                 FILENAME='E:\Database\test\testbase2_group2_sub1_data.ndf',
                 SIZE=5MB,
                 MAXSIZE=50MB,
                 FILEGROWTH=5MB),
                (NAME=testbase2_group2_sub2,
                 FILENAME='E:\Database\test\testbase2_group2_sub2_data.ndf',
                 SIZE=5MB,
                 MAXSIZE=50MB,
                 FILEGROWTH=5MB)
LOG ON
        (NAME=testbase2_log,
         FILENAME='E:\Database\test\testbase2_log1.ldf',
         SIZE=20MB,
         MAXSIZE=500MB,
         FILEGROWTH=10MB)
        (NAME=testbase2_log,
         FILENAME='E:\Database\test\testbase2_log2.ldf',
         SIZE=20MB,
         MAXSIZE=500MB,
         FILEGROWTH=10MB)
GO

3、数据库重命名
sp_renamedb 'testbase1', 'testbase4'

4、删除数据库
DROP DATABASE testbase4
GO

2、联机、脱机数据库
脱机时可以进行“移动数据库”操作；

一、交互式：
“数据库”，右键选择“脱机”或“联机”；

3、附件、分离数据库
一、交互式：

二、语句：
1、分离数据库：
sp_detach_db @dbname=testbase4

2、附加数据库：
把刚才分离的testbase4附加为testbase1：
if not exists(
        select * from sys.databases where name=n'testbase1'
)
create database testbase1
on primary(filename='e:\Database\test\testbase1_data.mdf')
for attach
go

4、备份、还原数据库
数据库恢复模式：
1、完整恢复模式：
备份所有事务日志；
2、大容量日志恢复模式：
备份部分事务日志，大规模大容量操作时不备份事务日志，减少日志空间开销；
3、简单恢复模式：
不备份事务日志；

三种模式的选择要在增大日志管理开销和数据安全风险之间进行权衡；

一、交互式：

二、语句：
（一）、完全数据库备份与简单恢复
1、完全备份：
use jspDev
go
backup database jspDev
        to disk='e:\Database\tmp\jspDev.bak'
        with init
go

   2、简单恢复：
use master
go
restore database jspDev
        from disk='e:\Database\tmp\jspDev.bak'
        with replace
go

    （二）、差异数据库备份与简单恢复
    1、差异备份：
    （1）完全备份
use jspDev
go
backup database jspDev
        to disk='e:\Database\tmp\jspDev_full.bak'
        with init
go
    （2）修改数据
修改2...
    （3）差异备份
use jspDev
go
backup database jspDev
        to disk='e:\Database\tmp\jspDev_diff1.bak'
        with differential
go
    （4）修改数据
修改4...
    （5）差异备份
use jspDev
go
backup database jspDev
        to disk='e:\Database\tmp\jspDev_diff2.bak'
        with differential
go
    （6）修改数据
修改6...

    2、简单恢复
    （1）恢复到修改2之后状态：
use master
go
restore database jspDev
        from disk='e:\Database\tmp\jspDev_full.bak'
        with norecovery
go
restore database jspDev
        from disk='e:\Database\tmp\jspDev_diff1.bak'
        with replace
go
   （2）恢复到修改4之后状态：
use master
go
restore database jspDev
        from disk='e:\Database\tmp\jspDev_full.bak'
        with norecovery
go
restore database jspDev
        from disk='e:\Database\tmp\jspDev_diff2.bak'
        with replace
go

（三）、事务日志备份与完全恢复：
1、更改数据库恢复模式：
图形操作：属性——>选项——>恢复模式，三种恢复模式可选；
T-SQL语句：
----简单模式
alter database jspDev
set recovery simple
go

----大容量日志模式
alter database jspDev
set recovery bulk_logged
go

----完全模式
alter database jspDev
set recovery full
go

2、事务日志备份：
（1）完全备份数据库
（2）修改数据：修改1...
（3）备份事务日志：jspDev_log1.bak
backup log jspDev
        to disk='e\Database\tmp\jspDev_log1.bak'
go
（4）修改数据：修改2...
（5）备份事务日志：jspDev_log2.bak
backup log jspDev
        to disk='e\Database\tmp\jspDev_log2.bak'
go
（6）修改数据：修改3...

3、完全恢复：
（1）恢复到修改1后的状态：
选择jspDev_full.bak和jspDev_log1.bak；
（2）恢复到修改2之后的状态：
选择jspDev_full.bak、jspDev_log1.bak和jspDev_log2.bak；

5、扩大、收缩数据库
一、交互式：
1、扩大数据库：
数据库属性——>文件，在“初始大小”里直接修改，或修改“自动增长”属性；

2、收缩数据库：
通过删除未使用的页的方法来收缩；
可以收缩数据库、数据文件、日志文件；

6、导入、导出数据
一、交互式：
使用导入导出向导：

二、使用bcp（bulk copy）：
D:\>bcp
用法:  bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]
1、表导出为execl：
D:\>bcp "jspDev.dbo.user2" out "e:\database\tmp\user2test2.xls" -T -c -SNCUT-ZHJ\SQLSERVER2005
2、查询导出为execl：
D:\>bcp "select * from jspDev.dbo.user2" queryout "e:\database\tmp\user2test1.xls" -T -c -SNCUT-ZHJ\SQLSERVER2005
3、表导出为txt：
D:\>bcp "jspDev.dbo.user2" out "e:\database\tmp\user2test111.txt" -T -c -Sncut-zhj\sqlserver2005 -Usa -P123

4、execl导入表：
D:\>bcp "jspDev.dbo.user2DTStest" in "e:\database\tmp\user2test111.xls" -T -c -Sncut-zhj\sqlserver2005 -Usa -P123

7、创建、删除表
一、交互式：
USE [jspDev]
GO
/****** 对象:  Table [dbo].[user2]    脚本日期: 10/12/2011 22:44:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[user2](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [userid] [char](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
        [password] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
        [name] [nvarchar](40) COLLATE Chinese_PRC_CI_AS NULL,
        [userType] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [jspDev]
GO
/****** 对象:  Table [dbo].[user2]    脚本日期: 10/12/2011 22:48:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[user2]') AND type in (N'U'))
DROP TABLE [dbo].[user2]

二、语句：
1、创建：
create table user2(
        id        int        identity(1,1)        not null,
        userid    char(20)           not null,
        password  nvarchar(20)       not null,
        name      nvarchar(50)       null,
        userType  int                null
)

2.删除：
drop table user2

8、修改表
一、交互式：
增删改列；

二、语句：
1、增列：
alter table user2
        add        sex        int        null
  
   2、删列：
alter table user2
        drop column sex

   3、改列：
alter table user2
        alter column sex char(2) null
  
  
第四章 数据查询（一）
SQL语句
1、trim()函数去掉两头空格
sql server中没有trim函数，但有ltrim()去除左空格，rtrim()去除右空格。
合起来用就是sql的trim()函数，即select ltrim(rtrim(UsrName))
select ltrim(rtrim(userid))+'.'+ltrim(rtrim(name)) as idname from user2
0001.武定杰123
0002.刘嵘456
0003.张颖

oracle中，trim一般都是用在删除字符串两边的空格。实际上，trim也可以用来删除字符串两边的指定字符。
并且trim指定删除的字符串只能是单个字符。如 trim('字符1' from '字符串2') ，字符1只能是单个字符。
1. trim()删除字符串两边的空格。
2. ltrim()删除字符串左边的空格。
3. rtrim()删除字符串右边的空格。
4. trim('字符1' from '字符串2')  分别从字符2串的两边开始，删除指定的字符1。
5. trim([leading | trailing | both] trim_char from string)　从字符串String中删除指定的字符trim_char。
  leading:从字符串的头开始删除。
  trailing:从字符串的尾部开始删除。
  borth:从字符串的两边删除。
6. tim()只能删除半角空格。

2、字符串连接符
SQL中的select语句我们经常需要经几个字符串连接起来作为一个字符串来充当输出结果，
在SQL Sever中用“+”连接符号，
在Oracle中的连接符号是“||”。
并且，sqlserver中要用ltrim和rtrim去掉空格，oracle中“||”有去掉空格的作用。

SQL> select ename||'.'||empno from emp;
ENAME||'.'||EMPNO
---------------------------------------------------
SMITH.7369
ALLEN.7499
WARD.7521

3、使用常量列：
sqlserver与oracle一样
select userid as id,name as username,'表' as 表名称from user2;
 id                          name          表名称
0001                        武定杰123        表
0002                        刘嵘456          表
0003                        张颖             表

4、限制行数（程序中实现分页）：
sqlserver用top关键字；
select top 5 * from user2
select top 20 percent * from user2

oracle用伪字段rownum；
rownum是oracle系统顺序分配给查询返回的行的编号：
SQL> select rownum,empno,ename from emp;
SQL> select empno,ename from emp where rownum<5;
SQL> select empno,ename from emp where rownum>5;
----语句错误，rownum是查询返回的行的编号
----可以如下实现：SQL> select * from (select rownum as no,empno,ename from emp) where no>5;
SQL> select rownum,empno,ename from emp order by ename;
----此时rownum不是从顺序的；
SQL> select rownum,empno,ename from (select * from emp order by ename);
----此时rownum是顺序的；

mysql用limit子句；
mysql> SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15

----为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

---如果只给定一个参数，它表示返回最大的记录行数目：
mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行
----换句话说，LIMIT n 等价于 LIMIT 0,n。

SQL函数
一、字符串常用函数
1、len：返回字符串的长度，一个汉字和一个英文字符一样长度都为1；
select len('test')-----------4
select name,len(name) from user2 where userid='0001'----武定杰123        6
   
2、charindex：返回指定字符串在另一个字符串中的位置
select charindex('test','mytest')-------------3
select name,charindex('23',name) from user2 where userid='0001'----武定杰123        5

3、lower/upper：大小写转换
select lower('TEsT')-----------test
select upper('teSt')-----------TEST

4、ltrim/rtrim：去掉字符串两端空格

5、replace：字符串替换
select replace('Mytest','test','xxx')---------Myxxx
update power set url=replace(url,'8081','8080')
  
6、left/right：返回指定字符串左边/右边指定长度的字符子串
select left('test123',4)-----test
select right('test123',3)-------123
select name,left(name,4) from user2 where userid='0001'----武定杰123   武定杰1

7、stuff：从指定字符串指定位置删除指定长度字符字串，并插入指定的另一字符串
SELECT STUFF('ABCDEFG', 2, 3, '我的音乐我的世界')--------A我的音乐我的世界EFG

    二、日期函数
1、getdate()：取得系统当前日期
select getdate()---------2011-10-20 10:59:53.107
select getdate() from user2---------26行-10-20 11:00:46.623

2、datepart：返回指定日期部分的整数形式
select datepart(day,'10/20/2011')-----------20
select datepart(month,'09/20/2011')---------9
select datepart(year,'10/20/2011')----------2010

3、datename：返回指定日期的字符串形式
select datename(dw,'10/20/2011')----------星期四

4、datediff：返回两个日期之间指定日期的区别
select datediff(dd,'07/22/2011','10/20/2011')---------90
select datediff(mm,'07/22/2011','10/20/2011')---------3
select datediff(yy,'07/22/2011','10/20/2011')---------0

5、dateadd：将指定数值添加到日期的指定部分
select dateadd(dd,4,'10/20/2011')----------2011-10-24 00:00:00.000

    三、数学函数
1、ABS：取绝对值
select ABS(-45.3)-------45.3

2、floor/ceiling：返回小于或等于/大于或等于指定数字的最大/最小整数
select floor(43.5)------43
select ceiling(43.5)-----44

3、power：取表达式的幂值
select power(5,3)----125

4、round：数值四舍五入为指定精度
select round(43.5218,2)--------43.5200
select round(43.5218,1)--------43.5000

5、sqrt：求指定数值的平方根
select sqrt(125)------11.1803398874989
select sqrt(9)------3

6、sign：正数返回+1，负数返回-1，0返回0
select sign(43.5)---- 1.0
select sign(-4)------ -1
select sign(0)------- 0

    四、系统函数
1、host_name：返回当前用户登录的计算机名
select host_name()--------NCUT-ZHJ

2、system_user：返回登录用户名
select system_user---------sa

3、current_user：返回当前用户名
select current_user---------dbo
   
4、user_name：根据指定id返回用户名
select user_name(1)---------dbo

5、datalenth：返回字符串的字节数，一个汉字两个字节
select datalength('test')-----4
select datalength('我的test')-----8

select name,datalength(name) as namelenth from user2 where userid='0001'---武定杰 12
select datalength('武定杰')-----9

6、convert：数据类型转换

案例分析一、
某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：
卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；
请编写SQL语句实现以上要求；
数据库表名：Card；密码字段名：PassWord；

Update   Card
Set         PassWord = Replace(Replace(PassWord ,'O','0'),'i','1')

案例分析二、
在数据库表中有以下字符数据，如：
13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2
现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排需，输出要排成这样：
13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2
数据库表名：SellRecord；字段名：ListNumber；

需要使用到ORDER BY进行排序，并且在ORDER BY的排序列中，也需要重新计算出排序的数字来
前半部分的数字，可以从先找到“-”符号的位置，然后，取其左半部分，最后再使用Convert函数将其转换为数字：        Convert(int, Left(ListNumber, CharIndex('-', ListNumber)-1))
后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到该位置的全部字符替换为空格，最后再使用Convert函数将其转换为数字：        Convert(int, Stuff(ListNumber,1, Charindex('-', ListNumber), ''))

SELECT ListNumber
FROM    SellRecord
ORDER BY
Convert(int, Left(ListNumber, CharIndex('-', ListNumber)-1)),
Convert(int, Stuff(ListNumber,1, Charindex('-', ListNumber), ''))

 

第五章  数据查询（二）
连接查询
一、连接类型

1、内连接（inner join）：只连接匹配的行
select student.sno,sname,cno,score
from student
inner join course on student.sno=course.sno
（1）等值或非等值连接：比较分量相等或不等；
select student.*,SC.* from student,SC where student.sno=SC.sno;
----查找学生表和选课表中学号相等的记录
----显示了两个表的所有属性，包括student.sno,SC.sno
（2）自然连接：特殊的等值连接，要求比较分量是相同属性列，且结果中去掉相同属性列；
select student.sno,sname,ssex,sage,sdept,cno,grade from student,SC where student.sno=SC.sno
（3）自身连接：同一个表自己与自己进行连接；
select first.cno,senond.cno from course first,course second where first.cpno=second.cno
----查找每一门课的先修课的先修课

2、外连接（outer join）：
（1）左外连接（left outer join或left join）：
包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行，而其他属性填NULL；
select student.sno,sname,ssex,sage,sdept,cno,grade
from student
left join SC on student.sno=SC.sno
----显示所有学生的选课情况
----没有选课的cno和cgrade用NULL填充
select student.sno,sname,cname,score
from student
left join SC on student.sno=SC.sno
left join course on SC.cno=course.cno
（2）右外连接（right outer join或right join）：
包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行，而其他属性填NULL；
（3）全外连接（full outer join或full join）：
包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行，而其他属性填NULL；

3、交叉连接（cross join）：生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

二、连接查询的执行过程：
        （1）嵌套循环法(NESTED-LOOP)
首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。
表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。
重复上述操作，直到表1中的全部元组都处理完毕

（2）排序循环法（scort-merge）
常用于=连接
首先按连接属性对表1和表2排序
对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续
找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续
重复上述操作，直到表1或表2中的全部元组都处理完毕为止

（3）索引连接（index-join）
对表2按连接字段建立索引
对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组

三、驱动表问题
驱动表由RDBMS做sql优化时自动选择(sqlserver)；
当进行多表连接查询时，
1.指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]
2.未指定联接条件时,行数少的表称为[驱动表]。

结果集=驱动表*参照表的笛卡尔积 (在此笛卡尔积中，驱动表在前)

如:select   *   from   驱动表，参照表
上例情况中的结果集将会是
----------------------------------------------------------
      驱1     参1
      驱1     参2
      驱1     参3
      驱1     参4
      驱1     参5
      驱2     参1
      驱2     参2
      驱2     参3
      驱2     参4
      驱2     参5

作业
create table score1(
scoreId                int                identity(1,1)        not null,
score                int,
courseNo        nchar(10),
courseName        varchar(50),
stuNo                nchar(10),
stuName                varchar(50)
)

--alter table score1
--column score int        not null

--insert into score1(score,courseNo,courseName,stuNo,stuName) values (
--(70,'001','计算机组成原理','0001','张一'),
--(80,'002','计算机操作系统','0001','张一')
--)

insert into score1(score,courseNo,courseName,stuNo,stuName) values
(70,'001','计算机组成原理','0001','张一')

insert into score1(score,courseNo,courseName,stuNo,stuName) values
(80,'002','计算机操作系统','0001','张一')

insert into score1(score,courseNo,courseName,stuNo,stuName) values
(60,'001','计算机组成原理','0002','张二')

insert into score1(score,courseNo,courseName,stuNo,stuName) values
(62,'002','计算机操作系统','0002','张二')

insert into score1(score,courseNo,courseName,stuNo,stuName) values
(60,'001','计算机组成原理','0004','张二二')

insert into score1(score,courseNo,courseName,stuNo,stuName) values
(62,'002','计算机操作系统','0004','张二二')

select * from score1

insert into score1(score,courseNo,courseName,stuNo,stuName) values
(70,'001','计算机组成原理','0002','李一')

insert into score1(score,courseNo,courseName,stuNo,stuName) values
(80,'002','计算机操作系统','0001','李一')

update score1 set score=74 where scoreId=3
update score1 set score=86 where courseNo='002' and stuNo='0002'

update score1 set stuNo='0002' where scoreId=4

select * from score1 where stuName like '张%'

select * from score1 where stuName like '张_'

select stuName as 学生姓名from score1 where stuName like '张%'

--alter table score1
--add column jige----0及格，不及格

select * from score1

insert into score1(score,courseNo,courseName,stuNo,stuName,jige) values
(59,'001','计算机组成原理','0005','李一一',0)

insert into score1(score,courseNo,courseName,stuNo,stuName,jige) values
(61,'002','计算机操作系统','0005','李一一',1)

select * from score1 where jige is null

select * from score1 where jige is not null

select * from score1 where score between 60 and 68

select * from score1 where stuName in ('张一','张二','李一')

select * from score1 where stuName not in ('张一','张二','李一')

select count(*) as 数量from score1 where stuName = '张一'

select count(scoreId) as 数量from score1 where stuName = '张一'

select count(1) as 数量from score1 where stuName = '张一'

select sum(score) as 数量from score1 where stuName = '张一'

select avg(score) as 数量from score1 where stuName = '张一'

select max(score) 计算机组成原理最高成绩from score1 where courseNO='001'

select min(score) 计算机组成原理最低成绩from score1 where courseNO='001'

select * from score1

SELECT courseNo, AVG(Score) AS 课程平均成绩
FROM Score1
GROUP BY CourseNo
--------因为该列没有包含在聚合函数或GROUP BY 子句中。

SELECT courseNo,courseName,count(score),sum(Score),avg(score)
FROM Score1
GROUP BY CourseNo,courseName

SELECT courseNo, count(Score) AS 课程成绩记录数
FROM Score1
GROUP BY CourseNo

select * from score1

select stuNo,stuName,avg(score) as avgscore
from score1
where stuName like '张%'
group by stuNo,stuName
having avg(score)>70

select stuno,coursen,max(score) from score1
--------选择列表中的列'score1.stuNo' 无效，因为该列没有包含在聚合函数或GROUP BY 子句中

create table students(
sCode                nchar(10),
sName                varchar(50)
)

drop table score

create table score(
id                                int,
studentsId                nchar(10),
courseId                nchar(10),
score                                        int
)

select * from students
select * from score

SELECT         S.SName,C.CourseID,C.Score
From         Score AS C
INNER JOIN         Students AS S
ON                 C.StudentsID = S.SCode

SELECT         S.SName,C.CourseID,C.Score
From         Students AS S
INNER JOIN         Score AS C
ON                 C.StudentsID = S.SCode

SELECT         S.SName,C.CourseID,C.Score
From         Students AS S
INNER JOIN         Score AS C
ON                 C.StudentsID <> S.SCode

use tkl_demo
go
SELECT      a.order_id,a.payMode, b.payMode_name
FROM       dbo.orderinfo AS a INNER JOIN
           dbo.payMode AS b ON a.payMode = b.payMode_id

use studytest
go
SELECT         S.SName,C.CourseID,C.Score
From                 Students AS S
LEFT JOIN         Score AS C
ON                 C.StudentsID = S.SCode

 

SELECT         S.SName,C.CourseID,C.Score
From                 Score AS C
LEFT JOIN         Students AS S
ON                 C.StudentsID = S.SCode

 

作业：实现增删改查SQL语句
1、用create语句建立一张表；
2、用insert  into语句插入数条记录；
3、用update语句修改一条符合条件的记录；
4、用delete语句删除一条符合条件的记录；
5、用select语句查出符合条件的记录；
6、用select语句的相关子句查询：
包括order  by,  between…and…,  in/not in, group by
7 、用sql函数查询（每类至少用一个）：
包括系统函数、聚合函数、数学函数、字符串处理函数、日期函数
8、尝试再create一张表，insert  into一些记录，使用连接查询。
----可以交电子版，必须把每条语句的运行结果附在语句下面
----每个人必须当面交给我，不能代交

insert into table 插入多条数据
方法1：-------sqlsever可以
insert into `ttt`
select '001','语文' union all
select '002','数学' union all
select '003','英语';
insert into score1(score,courseNo,courseName,stuNo,stuName,jige)
select 59,'001','计算机组成原理','0005','李一一',0 union all
select 61,'002','计算机操作系统','0005','李一一',1 union all
select 62,'003','计算机网络','0005','李一一',1 union all
select 68,'004','数据结构','0005','李一一',1

方法2：--------sqlserver不行，mysql行
INSERT INTO tab_comp VALUES(item1, price1, qty1),
(item2, price2, qty2),
(item3, price3, qty3);

sqlserver：
insert into score values (3,'004','0004',98),(4,'005','0005',89)
-------------',' 附近有语法错误。

mysql：
INSERT INTO users  VALUES ('3', '1231', '4561'),('4', '12311', '45611')

方法3：--------都可以
 INSERT INTO tab_comp(item1, price1, qty1) SELECT item1, price1, qty1 FROM tab_cc;