使用SQL数据库和Rstudio去操纵大数据
使用knitr和RODBC两个R包来进行！
14 Aug 2016
Go backlibrary(RODBC)
channel<-odbcConnect("MyTest",uid="ripley",case="tolower")sqlQuery(channel,"select * from USArrests")## state murder assault urbanpop rape
## 1 Alabama 13.2 236 58 21.2
## 2 Alaska 10.0 263 48 44.5
## 3 Arizona 8.1 294 80 31.0
## 4 Arkansas 8.8 190 50 19.5
## 5 California 9.0 276 91 40.6
## 6 Colorado 7.9 204 78 38.7
## 7 Connecticut 3.3 110 77 11.1
## 8 Delaware 5.9 238 72 15.8
## 9 Florida 15.4 335 80 31.9
## 10 Georgia 17.4 211 60 25.8
## 11 Hawaii 5.3 46 83 20.2
## 12 Idaho 2.6 120 54 14.2
## 13 Illinois 10.4 249 83 24.0
## 14 Indiana 7.2 113 65 21.0
## 15 Iowa 2.2 56 57 11.3
## 16 Kansas 6.0 115 66 18.0
## 17 Kentucky 9.7 109 52 16.3
## 18 Louisiana 15.4 249 66 22.2
## 19 Maine 2.1 83 51 7.8
## 20 Maryland 11.3 300 67 27.8
## 21 Massachusetts 4.4 149 85 16.3
## 22 Michigan 12.1 255 74 35.1
## 23 Minnesota 2.7 72 66 14.9
## 24 Mississippi 16.1 259 44 17.1
## 25 Missouri 9.0 178 70 28.2
## 26 Montana 6.0 109 53 16.4
## 27 Nebraska 4.3 102 62 16.5
## 28 Nevada 12.2 252 81 46.0
## 29 New Hampshire 2.1 57 56 9.5
## 30 New Jersey 7.4 159 89 18.8
## 31 New Mexico 11.4 285 70 32.1
## 32 New York 11.1 254 86 26.1
## 33 North Carolina 13.0 337 45 16.1
## 34 North Dakota 0.8 45 44 7.3
## 35 Ohio 7.3 120 75 21.4
## 36 Oklahoma 6.6 151 68 20.0
## 37 Oregon 4.9 159 67 29.3
## 38 Pennsylvania 6.3 106 72 14.9
## 39 Rhode Island 3.4 174 87 8.3
## 40 South Carolina 14.4 279 48 22.5
## 41 South Dakota 3.8 86 45 12.8
## 42 Tennessee 13.2 188 59 26.9
## 43 Texas 12.7 201 80 25.5
## 44 Utah 3.2 120 80 22.9
## 45 Vermont 2.2 48 32 11.2
## 46 Virginia 8.5 156 63 20.7
## 47 Washington 4.0 145 73 26.2
## 48 West Virginia 5.7 81 39 9.3
## 49 Wisconsin 2.6 53 66 10.8
## 50 Wyoming 6.8 161 60 15.6#查看所有数据库
sqlQuery(channel,"select name from master.dbo.sysdatabases")## name
## 1 master
## 2 tempdb
## 3 model
## 4 msdb
## 5 ydlTest1
## 6 ydlTest2
## 7 学生成绩管理系统#查看这个数据库中有拥有的表
sqlQuery(channel,"select name from sysobjects where xtype='U'")## name
## 1 夏令营2016名单
## 2 usarrests
## 3 Table_1
## 4 studentToCamp#日期和时间函数，返回的类型为datetime
sqlQuery(channel,"SELECT GETDATE()")##
## 1 2016-07-21 16:51:00#1. 返回字符串左边开始制定个数的字符串
sqlQuery(channel,"SELECT LEFT('abcdefg',2)")##
## 1 ab#2. 右边的话是RIGHT
#3. LEN() 返回字符串中字符的个数，其中不包括尾随的空格
sqlQuery(channel,"SELECT LEN('abcdefg')")##
## 1 7sqlQuery(channel,"SELECT LEN('我爱数据库')")##
## 1 5#中文也可以！！
#4. 返回字符串中指定的部分，格式为字符串起始位置和长度
sqlQuery(channel,"SELECT SUBSTRING('ABCDEFGHIGK',3,5)")##
## 1 CDEFG#5. 删除字符串左边的起始空格
sqlQuery(channel,"SELECT LTRIM(' abcdefg')")##
## 1 abcdefg#6. 删除右边的序列，再加上字符串
sqlQuery(channel,"SELECT RTRIM(' ABC ') + 'XY'")##
## 1 ABCXYsqlQuery(channel,"SELECT TOP 3 state FROM USArrests")#显示state字段的前三条记录## state
## 1 Alabama
## 2 Alaska
## 3 ArizonasqlQuery(channel,"SELECT TOP 3 state,murder FROM USArrests")#显示state和murder两个字段的前三条记录## state murder
## 1 Alabama 13.2
## 2 Alaska 10.0
## 3 Arizona 8.1sqlQuery(channel,"SELECT TOP 10 percent state FROM USArrests")#显示前10%的数据## state
## 1 Alabama
## 2 Alaska
## 3 Arizona
## 4 Arkansas
## 5 CaliforniasqlQuery(channel,"SELECT TOP 3 state FROM USArrests
ORDER BY murder")#按照muder这个字段进行排序，默认的是升序## state
## 1 North Dakota
## 2 Maine
## 3 New HampshiresqlQuery(channel,"SELECT TOP 3 state FROM USArrests
ORDER BY murder DESC")#按照muder这个字段进行排序，默认的是升序## state
## 1 Georgia
## 2 Mississippi
## 3 FloridasqlQuery(channel,"SELECT TOP 3 state,COUNT( * ) FROM USArrests
GROUP BY state")#次数## state
## 1 Alabama 1
## 2 Alaska 1
## 3 Arizona 1#现在可以理解GROUP BY这个语句的意思了吧！一般可以用来查询次数最多的前三种商品的商品编号和卖出次数
#例如"SELECT TOP 3 GoodsID,COUNT(*) FROM Table GROUP BY GoodID ORDER BY COUNT(*) DESC"sqlQuery(channel,"SELECT * FROM USArrests WHERE murder >13")## state murder assault urbanpop rape
## 1 Alabama 13.2 236 58 21.2
## 2 Florida 15.4 335 80 31.9
## 3 Georgia 17.4 211 60 25.8
## 4 Louisiana 15.4 249 66 22.2
## 5 Mississippi 16.1 259 44 17.1
## 6 South Carolina 14.4 279 48 22.5
## 7 Tennessee 13.2 188 59 26.9sqlQuery(channel,"SELECT * FROM USArrests WHERE murder <3")## state murder assault urbanpop rape
## 1 Idaho 2.6 120 54 14.2
## 2 Iowa 2.2 56 57 11.3
## 3 Maine 2.1 83 51 7.8
## 4 Minnesota 2.7 72 66 14.9
## 5 New Hampshire 2.1 57 56 9.5
## 6 North Dakota 0.8 45 44 7.3
## 7 Vermont 2.2 48 32 11.2
## 8 Wisconsin 2.6 53 66 10.8sqlQuery(channel,"SELECT * FROM USArrests WHERE murder >13
UNION
SELECT * FROM USArrests WHERE murder <3
ORDER BY murder")#这个是将他们的行合并## state murder assault urbanpop rape
## 1 North Dakota 0.8 45 44 7.3
## 2 Maine 2.1 83 51 7.8
## 3 New Hampshire 2.1 57 56 9.5
## 4 Iowa 2.2 56 57 11.3
## 5 Vermont 2.2 48 32 11.2
## 6 Idaho 2.6 120 54 14.2
## 7 Wisconsin 2.6 53 66 10.8
## 8 Minnesota 2.7 72 66 14.9
## 9 Alabama 13.2 236 58 21.2
## 10 Tennessee 13.2 188 59 26.9
## 11 South Carolina 14.4 279 48 22.5
## 12 Florida 15.4 335 80 31.9
## 13 Louisiana 15.4 249 66 22.2
## 14 Mississippi 16.1 259 44 17.1
## 15 Georgia 17.4 211 60 25.8sqlQuery(channel,"SELECT TOP 3 state FROM USArrests")## state
## 1 Alabama
## 2 Alaska
## 3 ArizonasqlQuery(channel,"SELECT TOP 5 state FROM USArrests")## state
## 1 Alabama
## 2 Alaska
## 3 Arizona
## 4 Arkansas
## 5 CaliforniasqlQuery(channel,"SELECT TOP 3 state FROM USArrests
INTERSECT
SELECT TOP 5 state FROM USArrests")#表示的是取交集## state
## 1 Alabama
## 2 Alaska
## 3 ArizonasqlQuery(channel,"CREATE TABLE Employee
(
EmpID int IDENTITY(1,1) NOT NULL,
EmpName ntext,
EmpAge int,
EmpPay money,
EmpJob ntext,
EmpAddress ntext
)")## character(0)sqlQuery(channel,"select name from sysobjects where xtype='U'")## name
## 1 夏令营2016名单
## 2 usarrests
## 3 Employee
## 4 Table_1
## 5 studentToCamp#增加字段
sqlQuery(channel,"ALTER TABLE Employee
ADD Email varchar(60)")## character(0)sqlQuery(channel,"SELECT * FROM Employee")## [1] EmpID EmpName EmpAge EmpPay EmpJob EmpAddress
## [7] Email
## <0 rows> (or 0-length row.names)#插入记录
sqlQuery(channel,"INSERT INTO Employee(EmpName,EmpAge,EmpPay) values ('虞达浪',22,0)")## character(0)sqlQuery(channel,"SELECT * FROM Employee")## EmpID EmpName EmpAge EmpPay EmpJob EmpAddress Email
## 1 1 虞达浪 22 0 NA NA NA#更改表的名称
sqlQuery(channel,"exec sp_rename 'Employee','new_name' ")## character(0)sqlQuery(channel,"select name from sysobjects where xtype='U'")## name
## 1 夏令营2016名单
## 2 usarrests
## 3 new_name
## 4 Table_1
## 5 studentToCampsqlQuery(channel,"DROP TABLE new_name")## character(0)#切换数据库
sqlQuery(channel,"USE 学生成绩管理系统")## character(0)sqlQuery(channel,"select name from sysobjects where xtype='U'")## name
## 1 系别信息
## 2 教师信息
## 3 辅导员信息
## 4 课程信息
## 5 sysdiagrams
## 6 考试安排
## 7 班级信息
## 8 new
## 9 学生信息
## 10 成绩信息#下面可以进行查询，基本的SELECT 语句
sqlQuery(channel,"SELECT 姓名,性别,家庭住址 FROM 学生信息")## 姓名 性别 家庭住址
## 1 苏俊丹 女 河南商丘
## 2 张苗苗 女 河南洛阳
## 3 赵希坤 男 河南安阳
## 4 阮志婷 女 江西南昌
## 5 程丽婷 女 河南洛阳
## 6 戚正韦 男 贵州贵阳
## 7 李家洋 男 四川成都
## 8 孟彬彬 女 四川成都
## 9 蔡莎莎 女 河南安阳
## 10 蔡金奎 男 江西南昌
## 11 陈亚克 男 宁夏银川
## 12 苏普秀 女 湖南长沙
## 13 蔡智勇 男 宁夏银川
## 14 侯志方 女 河南郑州
## 15 李俊 男 山东济南
## 16 陈曦 女 湖北武汉
## 17 郑小营 男 湖北武汉
## 18 王静 女 山东济南sqlQuery(channel,"SELECT 姓名 AS 学生姓名,性别 FROM 学生信息")## 学生姓名 性别
## 1 苏俊丹 女
## 2 张苗苗 女
## 3 赵希坤 男
## 4 阮志婷 女
## 5 程丽婷 女
## 6 戚正韦 男
## 7 李家洋 男
## 8 孟彬彬 女
## 9 蔡莎莎 女
## 10 蔡金奎 男
## 11 陈亚克 男
## 12 苏普秀 女
## 13 蔡智勇 男
## 14 侯志方 女
## 15 李俊 男
## 16 陈曦 女
## 17 郑小营 男
## 18 王静 女#DISTINCT语句
sqlQuery(channel,"SELECT 民族 FROM 学生信息")## 民族
## 1 汉族
## 2 汉族
## 3 汉族
## 4 汉族
## 5 汉族
## 6 回族
## 7 汉族
## 8 汉族
## 9 汉族
## 10 汉族
## 11 汉族
## 12 汉族
## 13 汉族
## 14 汉族
## 15 汉族
## 16 汉族
## 17 汉族
## 18 汉族sqlQuery(channel,"SELECT DISTINCT 民族 FROM 学生信息")## 民族
## 1 汉族
## 2 回族#条件查询
sqlQuery(channel,"SELECT * FROM 学生信息 WHERE 姓名='张苗苗'")## 学号 姓名 性别 出生日期 民族 所属班级
## 1 2005010102 张苗苗 女 1985-05-15 汉族 20050101
## 家庭住址
## 1 河南洛阳sqlQuery(channel,"SELECT * FROM 学生信息 WHERE 民族='汉族' AND 性别='女'")## 学号 姓名 性别 出生日期 民族 所属班级
## 1 2005010101 苏俊丹 女 1987-01-12 汉族 20050101
## 2 2005010102 张苗苗 女 1985-05-15 汉族 20050101
## 3 2005010202 阮志婷 女 1988-04-25 汉族 20050102
## 4 2005020101 程丽婷 女 1985-03-27 汉族 20050201
## 5 2005020202 孟彬彬 女 1986-11-15 汉族 20050202
## 6 2005030101 蔡莎莎 女 1985-07-13 汉族 20050301
## 7 2005040102 苏普秀 女 1985-02-26 汉族 20050402
## 8 2005040202 侯志方 女 1986-05-19 汉族 20050402
## 9 2005050102 陈曦 女 1988-09-25 汉族 20050501
## 10 2005050202 王静 女 1987-05-05 汉族 20050502
## 家庭住址
## 1 河南商丘
## 2 河南洛阳
## 3 江西南昌
## 4 河南洛阳
## 5 四川成都
## 6 河南安阳
## 7 湖南长沙
## 8 河南郑州
## 9 湖北武汉
## 10 山东济南#LIKE 与通配符
sqlQuery(channel,"SELECT * FROM 学生信息 WHERE 家庭住址 LIKE '河南%'")## 学号 姓名 性别 出生日期 民族 所属班级
## 1 2005010101 苏俊丹 女 1987-01-12 汉族 20050101
## 2 2005010102 张苗苗 女 1985-05-15 汉族 20050101
## 3 2005010201 赵希坤 男 1985-10-15 汉族 20050102
## 4 2005020101 程丽婷 女 1985-03-27 汉族 20050201
## 5 2005030101 蔡莎莎 女 1985-07-13 汉族 20050301
## 6 2005040202 侯志方 女 1986-05-19 汉族 20050402
## 家庭住址
## 1 河南商丘
## 2 河南洛阳
## 3 河南安阳
## 4 河南洛阳
## 5 河南安阳
## 6 河南郑州sqlQuery(channel,"SELECT * FROM 成绩信息 WHERE 考试编号='0801'")## 成绩编号 学生编号 考试编号 课程编号 分数
## 1 3 2005010101 801 1 89
## 2 4 2005010101 801 2 87
## 3 5 2005010102 801 1 82
## 4 7 2005010102 801 2 88
## 5 15 2005020101 801 3 85
## 6 16 2005020101 801 4 74
## 7 17 2005020102 801 3 78
## 8 18 2005020102 801 4 89
## 9 24 2005030101 801 5 89
## 10 25 2005030101 801 6 92
## 11 28 2005040201 801 7 62
## 12 29 2005040201 801 8 98
## 13 30 2005040202 801 7 84
## 14 31 2005040202 801 8 86
## 15 36 2005050101 801 9 95
## 16 40 2005050101 801 10 91
## 17 41 2005050102 801 9 80
## 18 43 2005050102 801 10 86
## 19 50 2005050201 801 9 96
## 20 53 2005050201 801 10 74
## 21 54 2005050202 801 9 89
## 22 55 2005050202 801 10 85
## 23 60 2005040101 801 7 85
## 24 61 2005040101 801 8 79
## 25 62 2005040102 801 7 98
## 26 63 2005040102 801 8 92
## 27 68 2005030102 801 5 95
## 28 69 2005030102 801 6 75
## 29 72 2005020201 801 3 85
## 30 73 2005020201 801 4 74
## 31 74 2005020202 801 3 56
## 32 75 2005020202 801 4 86
## 33 80 2005010201 801 1 83
## 34 81 2005010201 801 2 75
## 35 84 2005010202 801 1 89
## 36 85 2005010202 801 2 94sqlQuery(channel,"SELECT * FROM 成绩信息 WHERE 考试编号='0801' AND 课程编号='1'")## 成绩编号 学生编号 考试编号 课程编号 分数
## 1 3 2005010101 801 1 89
## 2 5 2005010102 801 1 82
## 3 80 2005010201 801 1 83
## 4 84 2005010202 801 1 89sqlQuery(channel,"SELECT * FROM 成绩信息
WHERE 考试编号='0801' AND 课程编号='1'
ORDER BY 分数 DESC")#升序的话就是不加DESC 或者加ASC## 成绩编号 学生编号 考试编号 课程编号 分数
## 1 3 2005010101 801 1 89
## 2 84 2005010202 801 1 89
## 3 80 2005010201 801 1 83
## 4 5 2005010102 801 1 82sqlQuery(channel,"SELECT * FROM 成绩信息
WHERE 考试编号='0801' AND 课程编号='1'
ORDER BY 分数 DESC,学生编号 DESC")#升序的话就是不加DESC 或者加ASC## 成绩编号 学生编号 考试编号 课程编号 分数
## 1 84 2005010202 801 1 89
## 2 3 2005010101 801 1 89
## 3 80 2005010201 801 1 83
## 4 5 2005010102 801 1 82#我要查看sqlQuery(channel,"SELECT * FROM 成绩信息 WHERE 考试编号='0801'")
#这个结果中关于不同课程的平均分
sqlQuery(channel,"SELECT 课程编号,AVG(分数) FROM 成绩信息 WHERE 考试编号='0801' GROUP BY 课程编号")## 课程编号
## 1 1 85
## 2 10 84
## 3 2 86
## 4 3 76
## 5 4 80
## 6 5 92
## 7 6 83
## 8 7 82
## 9 8 88
## 10 9 90sqlQuery(channel,"SELECT * FROM 成绩信息 WHERE 考试编号='0801' AND 课程编号='1'")## 成绩编号 学生编号 考试编号 课程编号 分数
## 1 3 2005010101 801 1 89
## 2 5 2005010102 801 1 82
## 3 80 2005010201 801 1 83
## 4 84 2005010202 801 1 89sqlQuery(channel,"UPDATE 学生信息 SET 姓名='李俊',出生日期='1996' WHERE 学号='2005050101'")## character(0)sqlQuery(channel,"SELECT * FROM 学生信息 WHERE 学号='2005050101'")## 学号 姓名 性别 出生日期 民族 所属班级
## 1 2005050101 李俊 男 1996-01-01 汉族 20050501
## 家庭住址
## 1 山东济南#在进行基本连接操作时 SELECT 字句的列表中，每个目标列前要加上基本表的名称
#FROM 子句应该包括所有使用的基表
#WHERE子句应定义一个同等连接
sqlQuery(channel,"SELECT A.姓名,A.性别,A.出生日期,A.民族,B.班级名,A.家庭住址
FROM 学生信息 A,班级信息 B
WHERE A.所属班级=B.班级编号")## 姓名 性别 出生日期 民族 班级名
## 1 苏俊丹 女 1987-01-12 汉族 计算机科学与技术1班
## 2 张苗苗 女 1985-05-15 汉族 计算机科学与技术1班
## 3 赵希坤 男 1985-10-15 汉族 计算机科学与技术2班
## 4 阮志婷 女 1988-04-25 汉族 计算机科学与技术2班
## 5 程丽婷 女 1985-03-27 汉族 英语系1班
## 6 戚正韦 男 1987-07-18 回族 英语系1班
## 7 李家洋 男 1985-09-25 汉族 英语系2班
## 8 孟彬彬 女 1986-11-15 汉族 英语系2班
## 9 蔡莎莎 女 1985-07-13 汉族 企业管理系1班
## 10 蔡金奎 男 1986-12-06 汉族 企业管理系1班
## 11 陈亚克 男 1985-12-12 汉族 国际贸易系2班
## 12 苏普秀 女 1985-02-26 汉族 国际贸易系2班
## 13 蔡智勇 男 1985-12-26 汉族 国际贸易系2班
## 14 侯志方 女 1986-05-19 汉族 国际贸易系2班
## 15 李俊 男 1996-01-01 汉族 艺术系1班
## 16 陈曦 女 1988-09-25 汉族 艺术系1班
## 17 郑小营 男 1984-05-12 汉族 艺术系2班
## 18 王静 女 1987-05-05 汉族 艺术系2班
## 家庭住址
## 1 河南商丘
## 2 河南洛阳
## 3 河南安阳
## 4 江西南昌
## 5 河南洛阳
## 6 贵州贵阳
## 7 四川成都
## 8 四川成都
## 9 河南安阳
## 10 江西南昌
## 11 宁夏银川
## 12 湖南长沙
## 13 宁夏银川
## 14 河南郑州
## 15 山东济南
## 16 湖北武汉
## 17 湖北武汉
## 18 山东济南#这是基本的多表连接，A B是为了简化表的名称用法为： >SELECT selection_list >FROM table1 INNER JOIN table2 [ON join_conditions] >WHERE search_conditions >ORDER BY
sqlQuery(channel,"SELECT A.班级名,A.班级人数,B.姓名,B.联系方式
FROM 班级信息 A inner join 辅导员信息 B
ON A.辅导员=B.辅导员编号")#Inner可以去掉## 班级名 班级人数 姓名 联系方式
## 1 计算机科学与技术1班 18 王艳 15878952653
## 2 计算机科学与技术2班 19 王艳 15878952653
## 3 英语系1班 21 张华 15865291489
## 4 英语系2班 20 李锡 13595621635
## 5 企业管理系1班 19 王强 13959623579
## 6 国际贸易系1班 18 周红颜 13156923953
## 7 国际贸易系2班 20 曹丹丹 15926021546
## 8 艺术系1班 26 蔡莎莎 15856139956
## 9 艺术系2班 21 王宏敏 15816832266sqlQuery(channel,"SELECT A.班级名,A.班级人数,B.姓名,B.联系方式
FROM 班级信息 A inner join 辅导员信息 B
ON A.辅导员=B.辅导员编号
WHERE B.性别='女'")## 班级名 班级人数 姓名 联系方式
## 1 计算机科学与技术1班 18 王艳 15878952653
## 2 计算机科学与技术2班 19 王艳 15878952653
## 3 英语系1班 21 张华 15865291489
## 4 国际贸易系1班 18 周红颜 13156923953
## 5 国际贸易系2班 20 曹丹丹 15926021546
## 6 艺术系1班 26 蔡莎莎 15856139956
## 7 艺术系2班 21 王宏敏 15816832266#可以用WHERE来限制辅导员的性别，在内连接中这个限制性语句可以在ON 和 WHERE中效果都一样左向外联接的结果集包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
sqlQuery(channel,"SELECT A.班级名,A.班级人数,B.姓名,B.联系方式
FROM 班级信息 A LEFT OUTER join 辅导员信息 B
ON A.辅导员=B.辅导员编号")## 班级名 班级人数 姓名 联系方式
## 1 计算机科学与技术1班 18 王艳 15878952653
## 2 计算机科学与技术2班 19 王艳 15878952653
## 3 英语系1班 21 张华 15865291489
## 4 英语系2班 20 李锡 13595621635
## 5 企业管理系1班 19 王强 13959623579
## 6 国际贸易系1班 18 周红颜 13156923953
## 7 国际贸易系2班 20 曹丹丹 15926021546
## 8 艺术系1班 26 蔡莎莎 15856139956
## 9 艺术系2班 21 王宏敏 15816832266
## 10 中文系1班 21 <NA> NA
## 11 历史系1班 NA <NA> NA#可以很鲜明地看到A 表示主表，不管在B表中有没有与A表中辅导员编号相同的记录都会返回A表的信息
#在外连接中限制性语句在ON和WHERE中有很大的区别
sqlQuery(channel,"SELECT A.班级名,A.班级人数,B.姓名,B.联系方式
FROM 班级信息 A LEFT OUTER join 辅导员信息 B
ON A.辅导员=B.辅导员编号 AND A.班级人数>20")## 班级名 班级人数 姓名 联系方式
## 1 计算机科学与技术1班 18 <NA> NA
## 2 计算机科学与技术2班 19 <NA> NA
## 3 英语系1班 21 张华 15865291489
## 4 英语系2班 20 <NA> NA
## 5 企业管理系1班 19 <NA> NA
## 6 国际贸易系1班 18 <NA> NA
## 7 国际贸易系2班 20 <NA> NA
## 8 艺术系1班 26 蔡莎莎 15856139956
## 9 艺术系2班 21 王宏敏 15816832266
## 10 中文系1班 21 <NA> NA
## 11 历史系1班 NA <NA> NAsqlQuery(channel,"SELECT A.班级名,A.班级人数,B.姓名,B.联系方式
FROM 班级信息 A LEFT OUTER join 辅导员信息 B
ON A.辅导员=B.辅导员编号
WHERE A.班级人数>20")## 班级名 班级人数 姓名 联系方式
## 1 英语系1班 21 张华 15865291489
## 2 艺术系1班 26 蔡莎莎 15856139956
## 3 艺术系2班 21 王宏敏 15816832266
## 4 中文系1班 21 <NA> NA右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
sqlQuery(channel,"SELECT A.班级名,A.班级人数,B.姓名,B.联系方式
FROM 班级信息 A FULL OUTER join 辅导员信息 B
ON A.辅导员=B.辅导员编号")## 班级名 班级人数 姓名 联系方式
## 1 计算机科学与技术1班 18 王艳 15878952653
## 2 计算机科学与技术2班 19 王艳 15878952653
## 3 英语系1班 21 张华 15865291489
## 4 英语系2班 20 李锡 13595621635
## 5 企业管理系1班 19 王强 13959623579
## 6 国际贸易系1班 18 周红颜 13156923953
## 7 国际贸易系2班 20 曹丹丹 15926021546
## 8 艺术系1班 26 蔡莎莎 15856139956
## 9 艺术系2班 21 王宏敏 15816832266
## 10 中文系1班 21 <NA> NA
## 11 历史系1班 NA <NA> NA
## 12 <NA> NA 李磊 13569485986sqlQuery(channel,"SELECT A.班级名,A.班级人数,B.姓名,B.联系方式
FROM 班级信息 A FULL OUTER join 辅导员信息 B
ON A.辅导员=B.辅导员编号
ORDER BY A.班级人数 DESC")## 班级名 班级人数 姓名 联系方式
## 1 艺术系1班 26 蔡莎莎 15856139956
## 2 艺术系2班 21 王宏敏 15816832266
## 3 中文系1班 21 <NA> NA
## 4 英语系1班 21 张华 15865291489
## 5 英语系2班 20 李锡 13595621635
## 6 国际贸易系2班 20 曹丹丹 15926021546
## 7 计算机科学与技术2班 19 王艳 15878952653
## 8 企业管理系1班 19 王强 13959623579
## 9 国际贸易系1班 18 周红颜 13156923953
## 10 计算机科学与技术1班 18 王艳 15878952653
## 11 历史系1班 NA <NA> NA
## 12 <NA> NA 李磊 13569485986交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
sqlQuery(channel,"SELECT A.班级名,A.班级人数,B.姓名
FROM 班级信息 A CROSS JOIN 辅导员信息 B
WHERE A.辅导员=B.辅导员编号")## 班级名 班级人数 姓名
## 1 计算机科学与技术1班 18 王艳
## 2 计算机科学与技术2班 19 王艳
## 3 英语系1班 21 张华
## 4 英语系2班 20 李锡
## 5 企业管理系1班 19 王强
## 6 国际贸易系1班 18 周红颜
## 7 国际贸易系2班 20 曹丹丹
## 8 艺术系1班 26 蔡莎莎
## 9 艺术系2班 21 王宏敏#WHERE子句范湖的结果是在前面的这些句子的基础上进行的sqlQuery(channel,"SELECT A.班级名,A.班级人数,B.姓名
FROM 班级信息 A,班级信息 B
WHERE A.班级人数=B.班级人数 AND A.班级编号<>B.班级编号 AND A.班级名='艺术系2班'")## [1] "42S22 207 [Microsoft][SQL Server Native Client 10.0][SQL Server]列名 '姓名' 无效。"
## [2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT A.班级名,A.班级人数,B.姓名\n FROM 班级信息 A,班级信息 B\n WHERE A.班级人数=B.班级人数 AND A.班级编号<>B.班级编号 AND A.班级名='艺术系2班''"#自连接就是虚拟化AB两个表，上面的这个语句是查询和‘艺术系2班’人数相同的班级是哪个？在T-SQL中有两类变量，一类是全局变量，一类是局部变量。 全局变量： 是由SQLServer预先定义并负责维护的一类变量主要用于保存SQLServer系统的某些参数值和性能统计数据，使用范围覆盖整个程序，用户对其只能引用而不能定义。 局部变量：是由用户根据需要定义的，使用范围只限于某一个批语句或者过程体内的一类变量。局部变量主要用于储存临时数据或者由存储过程返回的结果。
全部变量以@@开头，后面跟相应的字符串，如@@version 查看全部变量可以用
sqlQuery(channel,"SELECT @@language")##
## 1 简体中文#查看一当前的语言名称定义局部变量：
#Declare @variable1 data_type[,@variable2 data_type2,…]
#@variable1 为变量名一单个@开头
#data_type 为数据类型 可以是系统数据类型 也可以是用户自定义数据类型
sqlQuery(channel,"DECLARE @no_str varchar(8)
SET @no_str='20060112'
SELECT @no_str")##
## 1 20060112#就是这样，用DECLARE声明；SET赋值，用SELECT来进行显示但是游标少用比较好！ >在关系数据库中，我们对于查询的思考是面向集合的。而游标打破了这一规则，游标使得我们思考方式变为逐行进行.对于类C的开发人员来着，这样的思考方式会更加舒服。
FORWARD_ONLY 和 SCROLL 二选一：这个是两个重要的关键字
FORWARD_ONLY意味着游标只能从数据集开始向数据集结束的方向读取，FETCH NEXT是唯一的选项，而SCROLL支持游标在定义的数据集中向任何方向，或任何位置移动，如下图:
下面直接讲游标的用法——即操作游标
sqlQuery(channel,"SELECT 姓名 FROM 学生信息")## 姓名
## 1 苏俊丹
## 2 张苗苗
## 3 赵希坤
## 4 阮志婷
## 5 程丽婷
## 6 戚正韦
## 7 李家洋
## 8 孟彬彬
## 9 蔡莎莎
## 10 蔡金奎
## 11 陈亚克
## 12 苏普秀
## 13 蔡智勇
## 14 侯志方
## 15 李俊
## 16 陈曦
## 17 郑小营
## 18 王静sqlQuery(channel,"DECLARE STU_CURSOR SCROLL CURSOR FOR
SELECT 姓名 FROM 学生信息")#这个是声明变量## character(0)sqlQuery(channel,"OPEN STU_CURSOR")#打开游标## character(0)A<-sqlQuery(channel,"DECLARE @NAME VARCHAR(20)
FETCH LAST FROM STU_CURSOR
INTO @NAME
PRINT @NAME")#创建一个中间变量来存储中间的过程，再利用游标来得到数据
#这里我不知道为什么在knitr这边没有输出，但是直接在数据库中操作的话会直接地输出结果
sqlQuery(channel,"CLOSE STU_CURSOR")#打开游标## character(0)sqlQuery(channel,"DEALLOCATE STU_CURSOR")#释放游标## character(0)odbcClose(channel)