韩顺平-sql-server数据库(2)

主讲韩顺平1.上节回顾2.sqlserver表的管理√3.基本查询√4.复杂查询√5.维护数据的完整性√6.修改表主讲韩顺平1.掌握sqlserver表的管理(创建/维护)2.掌握对sqlserver表的各种查询技巧主讲韩顺平■必须以字母,_开头■长度不能超过128字符■不要使用sqlserver的保留字■只能使用如下字符A-Z,a-z,0-9,$,#,_等主讲韩顺平■字符型char定长最大8000字符.（非unicode编码）char(10)‘小韩’前四个字符放‘小韩’,后添6个空格补全varchar变长最大8000字符.(非unicode编码)varchar(10)‘小寒’sqlserver分配四个字符.这样可以节省空间ntext可变长度Unicode数据,最大长度为2的30次方-1(1,073,741,823)个字符。text可变长度非Unicode数据的最大长度为2的31次方-1(2,147,483,647)个字符。区别:①text是字节格式存储英文的,也可以存中文但有时候会显示成乱码②ntext是多字节格式存储unicode的，也就是存储各种文字用的。主讲韩顺平■字符型nchar定长最大4000字符.(unicode编码)nchar(10)‘小韩’前四个字符放‘小韩’,后添6个空格补全nvarchar变长最大4000字符.(unicode编码)nvarchar(10)‘小寒’sqlserver分配四个字符.这样可以节省空间特别说明:①一般带有汉字的段用nvarchar,全英文或符号的用varchar,因为nvarchar为unicode字符集,该类型的字段无论是单个字母还是单个汉字都占两个字节,而varchar,字母占一个字节,汉字占两个,nvarchar处理汉字或其它unicode字符集的速度要比varchar字段快举例说明varchar和nvarchar的区别主讲韩顺平■数字型1)bit范围0到12)int范围负的2的31次方到正的2的31次方减13)bigint范围负的2的63次方到正的2的63次方减14)float存放小数5)numeric小数强烈建议如果要去存放小数最好使用numeric主讲韩顺平■日期类型datetime(表示日期)timestamp(时间戳)一般情况下用datetime表示日期主讲韩顺平■图片/视频image保存图片/视频,但是用的很少,一般用路径保存图片/视频,在软件公司往往使用图片服务器和图床技术.■binarybinary字段可以存放二进制数据，但是最大只能是8000字节。主讲韩顺平■建表(公司管理系统)--职员表sqlserver建议表的设计者,最好给表定义一个主键,用来标示唯一的一条记录.主讲韩顺平查询表是对表操作的最重要的操作，这个我们放在后面详细讲解主讲韩顺平■所有字段都插入insertintoempvalues(7521,'ward','salesman',7698,'1981-2-22',1250.00,500.00,30)主讲韩顺平■插入部分字段主讲韩顺平■改一个字段updatestudentsetsex='女'wherexh='A001';update表名set字段名=‘新值’where字段=‘值’■修改多个字段updatestudentsetsex='男',birthday='1980-04-01‘wherexh='A001';■修改含有null值的数据主讲韩顺平■删除全部数据deletefrom表名;■删除指定数据deletefrom表名where字段名=‘值’主讲韩顺平■介绍在我们讲解的过程中我们利用(emp,dept)为大家演示如何使用select语句，select语句在软件编程中非常的有用，希望大家好好的掌握主讲韩顺平■介绍主键和外键主讲韩顺平■查询所有列■查询指定列■如何取消重复行?查询SMITH的薪水,工作,所在部门主讲韩顺平■使用算数表达式?显示每个雇员的年工资■使用列的别名selectename姓名,sal*12as年收入fromemp;主讲韩顺平■使用where子句?如何显示工资高于3000的员工?如何查找1982.1.1后入职的员工?如何显示工资在2000到2500的员工情况■如何使用like操作符%:表示0到多个字符_:表示单个字符?如何显示首字符为S的员工姓名和工资?如何显示第三个字符为大写O的所有员工的姓名和工资■在where条件中使用in?如何显示empno为123,345,800...的雇员情况■使用isnull的操作符?如何显示没有上级的雇员的情况主讲韩顺平■使用逻辑操作符号?查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J■使用orderby字句?如何按照工资的从低到高的顺序显示雇员的信息?按照部门号升序而雇员的工资降序排列■使用列的别名排序selectename,sal*12年薪fromemporderby年薪asc;别名需要使用“号圈中主讲韩顺平■分页查询按雇员的id号升序取出主讲韩顺平■说明在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句■数据分组-max,min,avg,sum,count?如何显示所有员工中最高工资和最低工资?显示所有员工的平均工资和工资总和?计算共有多少员工扩展要求：?请显示工资最高的员工的名字，工作岗位?请显示工资高于平均工资的员工信息主讲韩顺平■groupby和having子句groupby用于对查询的结果分组统计,having子句用于限制分组显示结果.?如何显示每个部门的平均工资和最高工资?显示每个部门的每种岗位的平均工资和最低工资?显示平均工资低于2000的部门号和它的平均工资扩展要求：主讲韩顺平■对数据分组的总结1分组函数只能出现在选择列表、having、orderby子句种2如果在select语句种同时包含有groupby,having,orderby那么他们的顺序是groupby,having,orderby3在选择列中,如果有列、表达式、和分组函数,那么这些列和表达式必须有一个出现在groupby子句中，否则就会出错如selectdeptno,avg(sal),max(sal)fromempgroupbydeptnohavingavg(sal)2000;这里deptno就一定要出现在groupby中呵呵主讲韩顺平■说明多表查询是指基于两个和两个以上的表或是视图的查询.在实际应用中,查询单个表可能不能满足你的需求,（如显示sales部门位置和其员工的姓名),这种情况下需要使用到(dept表和emp表)?显示雇员名,雇员工资及所在部门的名字【笛卡尔集】?如何显示部门号为10的部门名、员工名和工资思考题:?显示雇员名,雇员工资及所在部门的名字,并按部门排序.问题?主讲韩顺平■自连接自连接是指在同一张表的连接查询。?显示某个员工的上级领导的姓名比如显示’FORD’的上级.selectename,'FORD'fromempwhereempno=(selectmgrfromempwhereename='FORD')思考题:显示公司每个员工名字和它的上级的名字.问题?主讲韩顺平■什么是子查询子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询■单行子查询单行子查询是指只返回一行数据的子查询语句请思考：如何显示与SMITH同一部门的所有员工?■多行子查询多行子查询指返回多行数据的子查询请思考:如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号主讲韩顺平■在from子句中使用子查询请思考：如何显示高于部门平均工资的员工的信息主讲韩顺平■在from子句中使用子查询这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此也叫作内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名.主讲韩顺平■分页查询按雇员的id号升序取出主讲韩顺平■用查询结果创建新表这个命令是一种快捷的建表方法.select*(这里可以选择字段)into另一个表名from表思考题：如何删除掉一张表重复记录temp_table是一张临时表aa:是你要处理表（有重复行的.）selectdistinct*intotemp_tablefromaadeletefromaainsertintoaaselect*fromtemp_tabledroptabletemp_table主讲韩顺平■约束约束用于确保数据库数据满足特定的商业规则。在sqlserver中，约束包括:notnull、unique,primarykey,foreignkey,和check五种.主讲韩顺平■notnull(非空)如果在列上定义了notnull,那么当插入数据时，必须为列提供数据。■unique(唯一)当定义了唯一约束后，该列值是不能重复的.但是可以为null。■primarykey(主键)用于唯一的标示表行的数据,当定义主键约束后，该列不但不能重复而且不能为null。需要说明的是:一张表最多只能有一个主键,但是可以有多个unqiue约束。主讲韩顺平■foreignkey(外键)用于定义主表和从表之间的关系.外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null■check用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000～2000之间如果不再1000～2000之间就会提示出错。主讲韩顺平■商店售货系统表设计案例现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：商品goods（商品号goodsId，商品名goodsName，单价unitprice，商品类别category，供应商provider);客户customer（客户号customerId,姓名name,住址address,电邮email性别sex,身份证cardId);购买purchase（客户号customerId,商品号goodsId,购买数量nums);请用SQL语言完成下列功能：1建表，在定义中要求声明：(1)每个表的主外键；(2)客户的姓名不能为空值；(3)单价必须大于0，购买数量必须在1到30之间；(4)电邮不能够重复;(5)客户的性别必须是男或者女,默认是男主讲韩顺平■添加一个字段ALTERTABLEdistributorsADDaddressvarchar(30);■修改字段的类型/或是名字(不能有数据)ALTERTABLEdistributorsALTERCOLUMNaddressTYPEvarchar(80),ALTERCOLUMNnameTYPEvarchar(100);//ALTERTABLEdistributorsRENAMECOLUMNaddressTOcity;■删除一个字段ALTERTABLEdistributorsDROPCOLUMNaddress;(ok)■修改表的名字ALTERTABLEdistributorsRENAMETOsuppliers;()■删除表droptable表名;主讲韩顺平