数据库第二天（基本类型和单表查询）

支持的数据类型

字符型

char   定长 最大8000个字符。（非unicode编码）

   unicode编码：用两个字节存放一个字符（一个英文字母或者一个汉字）

   非unicode编码：用一个字节存放一个字母，用两个字节存放一个汉字

char(10) “小韩”前四个字符放“小韩”，后面添6个空格补全。表格列宽长度为10。

   优点：定长字段在查找匹配中比varchar速度快。

varchar  变长 最大8000字符。（非unicode编码）

ntext 可变长unicode数据，最长长度为：2^30-1

text 可变长度非unicode数据，最大长度：2^31-1

区别：1.text是字节格式存储英文的，也可以存中文但有时候会显示成乱码（非unicode编码）

           2.ntext 是多字节格式存储unicode的，也就是存储各种文字用的

所以建议尽量使用unicode编码，以避免存储中文时出现乱码。

  nchar  定长，最大4000字符（unicode编码）

nvarchar 变长，最大4000字符（unicode编码）

特别说明：

1.一般带有汉字的段用nvarchar，全英文或符号的用varchar。因为nvarchar统一用两个字节表示单个字母或者单个汉字，所以nvarchar处理汉字或者其他unicode字符集的速度要比varchar字段快。

2.如果一些特殊字符在nvarchar中没有，则只能选合适的代码页用varchar，而这些特有的字符转换到nvarchar会丢失。

数字型

bit：0与1

int：-2^31~2^31-1     bigint：-2^63~2^63-1，如果待存储数超出范围，可以考虑用 char 存放成字符串，然后再转换

float：存放小数，不推荐使用

numeric 小数，推荐使用

numeric(p,s)  --p表示精度范围：指定小数点左边与右边可以存储的最大个数1~38，s表示小数的位数

日期类型

datetime（表示日期）

create table test
(bir datetime)

--sql server 提供一个专门的事件获取函数 getdate()
insert into test values(GETDATE())

timestamp（时间戳）：select @@DBTS

图片与视频

image 保存图片，但是用的很少。一般图片不存放于数据库中，而是使用图片服务器和图床技术，数据库中只存放图片的路径。

           在图片有安全要求时，可以考虑放入数据库中，而且图片不要过大。

                                                                              一般情况下，图片存于服务器中，而数据库存其路径

视频

存放于数据库中为：binary 字段，但是往往将视频文件保存在文件服务器上，sql server中只保留文件路径，存取效率高。

建表

sql server建议表的设计者，最好给表定义一个主键

    主键（primary key）不能够重复出现，而且必须赋值，不允许为null

1.插入部分字段：insert into 表名（字段列表）values（值的列表）

   insert into clerk (clerkID,clerkName)values(3,'林黛玉')

      须在表名后面指定字段列表。

2.修改一个字段：update 表名 set 字段名=' 新值 '  where 字段= ' 值 '

--把贾政的age修改成
update clerk set age=38 where clerkName='贾政'
--把号人的名字改为薛蟠，age改成
update clerk set clerkName='薛蟠',age=40 where clerkID=2

注意：age前的 ‘，’必不可少

主键可以修改成不存在的序号：update clerk set clerkID=4 where clerkID = 2

null值不能用等号去匹配：

 

--把age为null 的人的名字改为' 薛宝钗'
update clerk set clerkName='薛宝钗' where age is null

3.删除数据

  删除全部数据：delete from 表名

删除指定数据：delete from 表名 where 字段名=' 值 ' and 字段名=' 值'  or 字段名=' 值' 

表的基本查询

主键和外键

外键约束可以保证数据的完整性和合理性

查询所有列：select * from 表名 where 条件

查询指定列：select 字段1，字段2 from 表名 where 条件

如何消除重复行，保留一行：select distinct 字段 from 表名 where 条件

显示每个雇员的年工资（加年终奖）：

    select ename,sal*13+

isnull

(comm,0)*13 '年工资' from emp

        注：一个null与其他数运算后仍为 null ；取别名时，可用单引号、双引号或者不用引号

显示工资在2000~2500的员工：

select * from emp where sal between 2000 and 2500 （其范围是：[2000，2500]）

使用like操作符（模糊查询）

“%”：表示 0 到多个字符，即任意的；“_”：表示单个字符

--显示第三个字符为O的所有员工的姓名和工资
select ename ,sal from emp where ENAME like '__o%'

在where中使用in
--如何显示EMPNO为,345,800...的雇员情况
select * from emp where EMPNO=123 or EMPNO=345 or EMPNO=800
--一般使用in 关键词，效率更高
select * from emp where EMPNO in(123,345,800)

 

使用null操作符

--如何显示没有上级的雇员的情况
select * from emp where MGR is null

使用逻辑操作符号

--查询工资高于或者岗位是MANAGER的雇员，同时还要满足姓名首写字母为j
select * from emp where (SAL>500 or JOB='MANAGER') and ENAME like 'j%'

 

使用order by字句

--如何安装工资从低到高的顺序显示雇员的信息
-- order by :  默认是升序排序
-- order by ... desc 降序
select * from emp order by SAL asc

--按照部门号的升序而雇员的工资降序排列
select * from emp order by DEPTNO asc,SAL desc

--统计每个人的年薪,并按照从低到高的顺序排列
select ename,(sal+ISNULL(comm,0))*13 '年薪' from emp order by '年薪' asc 

分页查询

表的复杂查询

SQL语句的读取顺序是从右到左。

select语句优化：把能迅速将结果集降为最低的select语句放置最右边，如：

--如何显示员工的最低工资
select MIN(sal) from emp 
--如何显示员工的最低工资,同时显示其姓名
select ENAME,SAL from emp where SAL=(select min(sal) from emp) 

数据分组-max，min，avg，sum，cout

--显示所有员工的平均工资和总工资
select  AVG(SAL) 平均工资,SUM(SAL) 总工资 from emp
--把高于平均工资的雇员的名字和工资显示出来
select ename,sal from emp where SAL>(select AVG(sal) from emp)

--统计所有员工的个数
select COUNT(*) from emp

分组group by 和having子句

group by 用于对查询的结果分组统计

having 子句 用于限制分组显示的 结果

--如何显示每个部门的平均工资和最高工资
select AVG(sal) 平均工资,MAX(sal) 最高工资, deptno from emp group by deptno

上述中并显示其部门名称（用到多表查询，待）

二级分组和having子句

--显示每个部门的每种岗位的平均工资和最低工资
select AVG(sal),MIN(sal),DEPTNO,job from emp group by DEPTNO,JOB order by DEPTNO asc

--显示所有平均工资低于的部门号和它的平均工资
select AVG(sal),DEPTNO from emp group by DEPTNO having AVG(sal)<2000

 

注：having子句后的 AVG(sal) 不能用别名代替，因为sql语句一般从右至左顺序执行。而order by 后可接别名，执行顺序如下：

SQL 查询的执行顺序是：

1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、select 的字段；

8、使用order by对结果集进行排序。

对数据分组的总结