SQL Server 数据库基础编程.docx

SQL Server 数据库基础编程 Go批处理语句 用于同时执行多个语句 使用、切换数据库use mastergo 创建、删除数据库方法1、-判断是否存在该数据库，存在就删除if (exists (select * from sys.databases where name = testHome) drop database testHomego-创建数据库，设置数据库文件、日志文件保存目录create database testHomeon( name = testHome, filename = c:datastudents.mdf )log on( name = testHome_log, filename = c:datatestHome_log.ldf)go方法2（设置文件大小）、if (exists (select * from sys.databases where name = testHome) drop database testHomegocreate database testHome-默认就属于primary主文件组，可省略on primary ( -数据文件的具体描述 name = testHome_data, -主数据文件的逻辑名 fileName = c:testHome_data.mdf, -主数据文件的物理名 size = 3MB, -主数据文件的初始大小 maxSize = 50MB, -主数据文件增长的最大值 fileGrowth = 10% -主数据文件的增长率)-日志文件的具体描述，各参数含义同上log on ( name = testHome_log, fileName = c:testHome_log.ldf, size = 1MB, fileGrowth = 1MB)go方法3（设置次数据文件）、if (exists (select * from sys.databases where name = testHome) drop database testHomegocreate database testHome-默认就属于primary主文件组，可省略on primary ( -数据文件的具体描述 name = testHome_data, -主数据文件的逻辑名 fileName = c:testHome_data.mdf, -主数据文件的物理名 size = 3MB, -主数据文件的初始大小 maxSize = 50MB, -主数据文件增长的最大值 fileGrowth = 10% -主数据文件的增长率),-次数据文件的具体描述( -数据文件的具体描述 name = testHome2_data, -主数据文件的逻辑名 fileName = c:testHome2_data.mdf, -主数据文件的物理名 size = 2MB, -主数据文件的初始大小 maxSize = 50MB, -主数据文件增长的最大值 fileGrowth = 10% -主数据文件的增长率)-日志文件的具体描述，各参数含义同上log on ( name = testHome_log, fileName = c:testHome_log.ldf, size = 1MB, fileGrowth = 1MB),( name = testHome2_log, fileName = c:testHome2_log.ldf, size = 1MB, fileGrowth = 1MB)go 基本数据类型精确数字类型类型描述bigintbigint 数据类型用于整数值可能超过 int 数据类型支持范围的情况，范围：-263 到 263-1，存储空间8字节int整数数据类型，范围在-231 到 231-1，存储空间4字节smallint整数，范围在-215 到 215-1，存储空间2字节tinyint范围在0 到 255，存储空间1字节bit可以取值为 1、0 或 NULL 的整数数据类型，每8个bit占一个字节，16bit就2个字节，24bit就3个字节decimal带固定精度和小数位数的数值数据类型，有效值从 - 1038 +1 到 1038 - 1numeric同上money货币或货币值的数据类型，范围在-922,337,203,685,477.5808 到 922,337,203,685,477.5807smallmoney货币类型，-214,748.3648 到 214,748.3647近似数字类型类型描述float表示浮点数值数据的大致数值数据类型。浮点数据为近似值；范围-1.79E + 308 至 -2.23E - 308、0 以及 2.23E - 308 至 1.79E + 308realreal 的 SQL-92 同义词为 float(24)，范围在-3.40E + 38 至 -1.18E - 38、0 以及 1.18E - 38 至 3.40E + 38日期时间类型类型描述datetime表示某天的日期和时间的数据类型，范围在1753 年 1 月 1 日到 9999 年 12 月 31 日smalldatetime范围在1900 年 1 月 1 日到 2079 年 6 月 6 日字符串类型类型描述char固定长度或可变长度的字符数据类型，范围在范围为 1 至 8,000字节text最大长度为 231-1varchar固定长度或可变长度的字符数据类型，最大存储大小是 231-1 个字节Unicode字符串类型类型描述nchar字符数据类型，长度固定，在必须在 1 到 4,000 之间nvarchar可变长度 Unicode 字符数据。最大存储大小为 231-1 字节ntext长度可变的 Unicode 数据，最大长度为 230 - 1 (1,073,741,823) 个字符二进制字符串类型类型描述binary长度为 n 字节的固定长度二进制数据，范围从 1 到 8,000 的值。存储大小为 n 字节。varbinary可变长度二进制数据。n 可以取从 1 到 8,000 的值。最大的存储大小为 231-1 字节image长度可变的二进制数据，从 0 到 231-1 (2,147,483,647) 个字节 判断表或其他对象及列是否存在-判断某个表或对象是否存在if (exists (select * from sys.objects where name = classes) print 存在;goif (exists (select * from sys.objects where object_id = object_id(student) print 存在;goif (object_id(student, U) is not null) print 存在;go-判断该列名是否存在，如果存在就删除if (exists (select * from sys.columns where object_id = object_id(student) and name = idCard) alter table student drop column idCardgoif (exists (select * from information_schema.columns where table_name = student and column_name = tel) alter table student drop column telgo 创建、删除表-判断是否存在当前tableif (exists (select * from sys.objects where name = classes) drop table classesgocreate table classes( id int primary key identity(1, 2), name varchar(22) not null, createDate datetime default getDate()goif (exists (select * from sys.objects where object_id = object_id(student) drop table studentgo-创建tablecreate table student( id int identity(1, 1) not null, name varchar(20), age int, sex bit, cid int)go 给表添加字段、修改字段、删除字段-添加字段alter table student add address varchar(50) not null;-修改字段alter table student alter column address varchar(20);-删除字段alter table student drop column number;-添加多个字段alter table student add address varchar(22), tel varchar(11), idCard varchar(3);-判断该列名是否存在，如果存在就删除if (exists (select * from sys.columns where object_id = object_id(student) and name = idCard) alter table student drop column idCardgoif (exists (select * from information_schema.columns where table_name = student and column_name = tel) alter table student drop column telgo 添加、删除约束-添加新列、约束alter table student add number varchar(20) null constraint no_uk unique; -增加主键alter table student add constraint pk_id primary key(id); -添加外键约束alter table student add constraint fk_cid foreign key (cid) references classes(id)go-添加唯一约束alter table student add constraint name_uk unique(name);-添加check约束alter table student with nocheck add constraint check_age check (age 1);alter table student add constraint ck_age check (age = 15 and age = 50)-添加默认约束alter table student add constraint sex_def default 1 for sex;-添加一个包含默认值可以为空的列alter table student add createDate smalldatetime null constraint createDate_def default getDate() with values;- 多个列、约束一起创建-alter table student add /*添加id主键、自增*/ id int identity constraint id primary key, /* 添加外键约束*/ number int null constraint uNumber references classes(number), /*默认约束*/ createDate decimal(3, 3) constraint createDate default 2010-6-1 go -删除约束alter table student drop constraint no_uk; 插入数据insert into classes(name) values(1班);insert into classes values(2班, 2011-06-15);insert into classes(name) values(3班);insert into classes values(4班, default);insert into student values(zhangsan, 22, 1, 1);insert into student values(lisi, 25, 0, 1);insert into student values(wangwu, 24, 1, 3);insert into student values(zhaoliu, 23, 0, 3);insert into student values(mazi, 21, 1, 5);insert into student values(wangmazi, 28, 0, 5);insert into student values(jason, null, 0, 5);insert into student values(null, null, 0, 5);insert into student select bulise name, age, sex, cid from student where name = tony; -多条记录同时插入insert into student select jack, 23, 1, 5 union select tom, 24, 0, 3 union select wendy, 25, 1, 3 union select tony, 26, 0, 5; 查询、修改、删除数据-查询数据select * from classes;select * from student;select id, bulise name, age, sex, cid from student where name = tony;select *, (select max(age) from student) from student where name = tony;-修改数据update student set name = hoho, sex = 1 where id = 1;-删除数据（from可省略）delete from student where id = 1; 备份数据、表-备份、复制student表到stuselect * into stu from student;select * into stu1 from (select * from stu) t;select * from stu;select * from stu1; 利用存储过程查询表信息-查询student相关信息exec sp_help student;exec sp_help classes;SQL Server Transact-SQL 编程T-SQL语句用于管理SQL Server数据库引擎实例，创建和管理数据库对象，以及查询、插入、修改和删除数据。 变量 1、 局部变量（Local Variable） 局部变量是用户可以自定义的变量，它的作用范围是仅在程序内部，在程序中通常用来储存从表中查询到的数据或当做程序执行过程中的暂存变量。使用局部变量必须以开头，而且必须用declare命令后才能使用。 基本语法：声明变量declare 变量名 变量类型 变量名 变量类型为变量赋值set 变量名 = 变量值;select 变量名 = 变量值; 示例：-局部变量declare id char(10)-声明一个长度的变量iddeclare age int -声明一个int类型变量age select id = 22 -赋值操作 set age = 55 -赋值操作 print convert(char(10), age) + # + id select age, idgo简单hello world示例declare name varchar(20);declare result varchar(200);set name = jack;set result = name + say: hello world!;select result;查询数据示例declare id int, name varchar(20);set id = 1;select name = name from student where id = id;select name;select赋值declare name varchar(20);select name = jack;select * from student where name = name; 从上面的示例可以看出，局部变量可用于程序中保存临时数据、传递数据。Set赋值一般用于赋值指定的常量个变量。而select多用于查询的结果进行赋值，当然select也可以将常量赋值给变量。 注意：在使用select进行赋值的时候，如果查询的结果是多条的情况下，会利用最后一条数据进行赋值，前面的赋值结果将会被覆盖。 2、 全局变量（Global Variable） 全局变量是系统内部使用的变量，其作用范围并不局限于某一程序而是任何程序均可随时调用的。全局变量一般存储一些系统的配置设定值、统计数据。全局变量select identity;-最后一次自增的值select identity(int, 1, 1) as id into tab from student;-将studeng表的烈属，以/1自增形式创建一个tabselect * from tab;select rowcount;-影响行数select cursor_rows;-返回连接上打开的游标的当前限定行的数目select error;-T-SQL的错误号select procid;-配置函数set datefirst 7;-设置每周的第一天，表示周日select datefirst as 星期的第一天, datepart(dw, getDate() AS 今天是星期;select dbts;-返回当前数据库唯一时间戳set language Italian;select langId as Language ID;-返回语言idselect language as Language Name;-返回当前语言名称select lock_timeout;-返回当前会话的当前锁定超时设置（毫秒）select max_connections;-返回SQL Server 实例允许同时进行的最大用户连接数select MAX_PRECISION AS Max Precision;-返回decimal 和numeric 数据类型所用的精度级别select SERVERNAME;-SQL Server 的本地服务器的名称select SERVICENAME;-服务名select SPID;-当前会话进程idselect textSize;select version;-当前数据库版本信息-系统统计函数select CONNECTIONS;-连接数select PACK_RECEIVED;select CPU_BUSY;select PACK_SENT;select TIMETICKS;select IDLE;select TOTAL_ERRORS;select IO_BUSY;select TOTAL_READ;-读取磁盘次数select PACKET_ERRORS;-发生的网络数据包错误数select TOTAL_WRITE;-sqlserver执行的磁盘写入次数 输出语句 T-SQL支持输出语句，用于显示结果。常用输出语句有两种： 基本语法print 变量或表达式select 变量或表达式 示例select 1 + 2;select language;select user_name();print 1 + 2;print language;print user_name(); print在输出值不少字符串的情况下，需要用convert转换成字符串才能正常输出，而且字符串的长度在超过8000的字符以后，后面的将不会显示。 逻辑控制语句 1、 if-else判断语句 语法if else if else 示例if简单示例if 2 3 print 2 3;else print 2 3) print 2 3;else if (3 2) print 3 2;else print other;简单查询判断declare id char(10), pid char(20), name varchar(20);set name = 广州;select id = id from ab_area where areaName = name;select pid = pid from ab_area where id = id;print id + # + pid;if pid id begin print id + %; select * from ab_area where pid like id + %; endelse begin print id + %; print id + # + pid; select * from ab_area where pid = pid; endgo 2、 whilecontinuebreak循环语句 基本语法while begin break continue end 示例-while循环输出到declare i int; set i = 1;while (i 11) begin print i; set i = i + 1; endgo-while continue 输出到declare i int; set i = 1;while (i 11) begin if (i = 5) begin set i = i + 1; break; end set i = i + 1; endgo 3、 case 基本语法case when then when then when then else end 示例select *, case sex when 1 then 男 when 0 then 女 else 火星人 end as 性别from student;select areaName, 区域类型 = case when areaType = 省 then areaName + areaType when areaType = 市 then city when areaType = 区 then area else other endfrom ab_area; 4、 其他语句批处理语句goUse masterGo延时执行，类似于定时器、休眠等waitfor delay 00:00:03;-定时三秒后执行print 定时三秒后执行;SQL Server T-SQL高级查询高级查询在数据库中用得是最频繁的，也是应用最广泛的。 基本常用查询-selectselect * from student;-all 查询所有select all sex from student;-distinct 过滤重复select distinct sex from student;-count 统计select count(*) from student;select count(sex) from student;select count(distinct sex) from student;-top 取前N条记录select top 3 * from student;-alias column name 列重命名select id as 编号, name 名称, sex 性别 from student;-alias table name 表重命名select id, name, s.id, from student s;-column 列运算select (age + id) col from student;select + - + from classes c, student s where s.cid = c.id;-where 条件select * from student where id = 2;select * from student where id 7;select * from student where id 3;select * from student where id 3;select * from student where id = 3;select * from student where id 3;select * from student where id ! 2 and sex = 1;-or 或者select * from student where id = 2 or sex = 1;-between . and . 相当于并且select * from student where id between 2 and 5;select * from student where id not between 2 and 5;-like 模糊查询select * from student where name like %a%;select * from student where name like %ao%;select * from student where name not like %a%;select * from student where name like ja%;select * from student where name not like %j,n%;select * from student where name like %j,n,a%;select * from student where name like %ja,as,on%;select * from student where name like %ja_on%;-in 子查询select * from student where id in (1, 2);-not in 不在其中select * from student where id not in (1, 2);-is null 是空select * from student where age is null;-is not null 不为空select * from student where age is not null;-order by 排序select * from student order by name;select * from student order by name desc;select * from student order by name asc;-group by 分组按照年龄进行分组统计select count(age), age from student group by age;按照性别进行分组统计select count(*), sex from student group by sex;按照年龄和性别组合分组统计，并排序select count(*), sex from student group by sex, age order by age;按照性别分组，并且是id大于2的记录最后按照性别排序select count(*), sex from student where id 2 group by sex order by sex;查询id大于2的数据，并完成运算后的结果进行分组和排序select count(*), (sex * id) new from student where id 2 group by sex * id order by sex * id;-group by all 所有分组按照年龄分组，是所有的年龄select count(*), age from student group by all age;-having 分组过滤条件按照年龄分组，过滤年龄为空的数据，并且统计分组的条数和现实年龄信息select count(*), age from student group by age having age is not null;按照年龄和cid组合分组，过滤条件是cid大于1的记录select count(*), cid, sex from student group by cid, sex having cid 1;按照年龄分组，过滤条件是分组后的记录条数大于等于2select count(*), age from student group by age having count(age) = 2;按照cid和性别组合分组，过滤条件是cid大于1，cid的最大值大于2select count(*), cid, sex from student group by cid, sex having cid 1 and max(cid) 2; 嵌套子查询 子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也成为外部查询或外部选择。# from (select table)示例将一个table的查询结果当做一个新表进行查询select * from ( select id, name from student where sex = 1) t where t.id 2;上面括号中的语句，就是子查询语句（内部查询）。在外面的是外部查询，其中外部查询可以包含以下语句： 1、 包含常规选择列表组件的常规select查询 2、 包含一个或多个表或视图名称的常规from语句 3、 可选的where子句 4、 可选的group by子句 5、 可选的having子句# 示例查询班级信息，统计班级学生人生select *, (select count(*) from student where cid = classes.id) as num from classes order by num;# in, not in子句查询示例查询班级id大于小于的这些班级的学生信息select * from student where cid in ( select id from classes where id 2 and id all ( select age from student where cid = 3);select * from student where cid = 5 and age any ( select age from student where cid = 3);select * from student where cid = 5 and age some ( select age from student where cid = 3); 聚合查询1、 distinct去掉重复数据select distinct sex from student;select count(sex), count(distinct sex) from student;2、 compute和compute by汇总查询对年龄大于的进行汇总select age from student where age 20 order by age compute sum(age) by age;对年龄大于的按照性别进行分组汇总年龄信息select id, sex, age from student where age 20 order by sex, age compute sum(age) by sex;按照年龄分组汇总select age from student where age 20 order by age, id compute sum(age);按照年龄分组，年龄汇总，id找最大值select id, age from student where age 20 order by age compute sum(age), max(id);compute进行汇总前面是查询的结果，后面一条结果集就是汇总的信息。compute子句中可以添加多个汇总表达式，可以添加的信息如下： a、 可选by关键字。它是每一列计算指定的行聚合 b、 行聚合函数名称。包括sum、avg、min、max、count等 c、 要对其执行聚合函数的列 compute by适合做先分组后汇总的业务。compute by后面的列一定要是order by中出现的列。3、 cube汇总cube汇总和compute效果类似，但语法较简洁，而且返回的是一个结果集。select count(*), sex from student group by sex with cube;select count(*), age, sum(age) from stude