1、数据库概述
(1)用自定义文件格式保存数据的劣势
(2)DBMS(DataBase Management System 数据库管理系统)和数据库,平时谈到数据库可能有两种含义。 MSSQLServer Oracle等各种DBMS,存放一堆数据表的一个分类(Catalog)
不同品牌的DBMS有自己不同的特点,MYSQL、MSSQLSERVER、DB2、Oracle、Access、Sybase等,对于开发人员来讲大同小异。
除了Access、SQLServer CE等文件型数据库之外,大部分数据库都需要数据库服务器才能运行学习。开发时是连接本机的数据库,上线运行时是数据库运行在单独的服务器
2、数据库中的概念
Catalog(分类)(又叫做数据库DataBase,表空间,TableSpace)不同类的数据应该放到不同的数据库中
(1)便于对各个Catalog进行个性化管理
(2)避免命名冲突
(3)安全性更高
Table(表),书放到书架上,碗放到橱柜里,不同类型的资料放到不同的格子里,将这种区域叫做表(Table),不同的表根据放的数据不同进行空间的优化找起来也方便
3、主键
主键就是数据行的唯一标识,不会重复的列才能当主键,一个表中可以没有主键但是会非常难处理,因此没有特殊理由表都要决定主键
主键选用策略:
业务主键:使用有业务意义的字段做主键,比如身份证号、银行账户等
逻辑主键:使用没有任何业务意义的字段叫做主键,因为很难保证业务主键不会重复,不会变化(账户升位等)
因此推荐使用逻辑主键,完全给程序看了,业务人员是不会看的数据
4、表间关联,外键(Foreign-Key)
5、数据类型
Bit——boolean(0或1)
Char(10)——10个字符,不满足10补空格
Int——整数
Bigint——大整数
Nvarchar(50)——50个字符
Nvarchar(Max)——长度不限(中英文等)
Varchar(50)——纯英文50,不补空格
6、SQL语句入门
(1)SQL语句是和DBMS交谈的时候专用语句,不同的DBMS都认SQL语法
(2)SQL语句中字符串用单引号
(3)SQL语句是大小写敏感的,不敏感的是指SQL关键字,字符串值还是大小写敏感的
(4)创建表不仅可以手工完成,还可以执行SQL语句完成,在自动化部署数据导入中用的很多 CREATE TABLE T_PERSON(ID int not NUll,Name nvarchar(50) ,Age int NULL)
SQL主要分DDL(数据定义语言)和DML(数据操作语言)两类,CREATE TABLE,DROP TABLE均属于DDL,SELECT,INSERT均属于DML
7、主键选择
SQLServer中两种常用的主键数据类型 int(或bigint)+标识列(又称自动增长字段)、uniqueidentifier(又称GUID)
(1)用标识列实现字段自增可以避免并发等问题,不要开发人员控制自增,用标识列的字段,在Insert的时候不用指定主键的值,将字段的标识列设置为是,一个表只能有一个标识列
(2)GUID算法是一种可以产生唯一标识的高效算法,它使用MAC地址、纳秒级时间、芯片ID等算出来的,这样保证每次生成的GUID永远不重复。SQLServer中生成的GUID函数是newID()
Int自增字段的优点:占用空间小,无需开发人员干预,易读
缺点:效率低,数据导入导出时间很痛苦
GUID优点:效率高,数据导入导出方便
缺点:占用空间大,不易读
业界主流倾向于使用GUID
8、数据插入
(1)Insert语句可以省略表后的列名,但是不推荐
(2)如果插入的行某些字段的值不确定,那么Insert时候不指定哪些列
(3)可以给字段赋值,如果GUID类型主键的默认值设定成newID()就会自动生成,很少这么做
简单的Insert语句:INSERT INTO T_PERSON(ID,Name,Age) VALUE(1,'sss',34)
9、数据更新
(1)更新一个列:UPDATE T_PERSON Set Age=30
(2)更新多个列:UPDATE T_PERSON Set Age=50,Name='Lucy'
(3)更新一部分数据:UPDATE T_PERSON Set NickName=N'青年人' WHERE Age>15
注意:SQL等于判断是单个‘=’,而不是‘= =’
WHERE中可以使用的基本逻辑运算符:or,and,not,<,>,>=,<=,!=(或><)等
10、数据删除
(1)删除表中全部数据:DELETE FROM T_PERSON
(2)DELETE只是删除数据,表还在,和DROP TABLE不同
(3)DELETE也可以带WHERE子句来删除一部分数据,DELETE FROM T_PERSON WHERE Age>20
11、数据检索
(1)简单的数据检索检索:SELECT *FROM T_EMPLOYEE
(2)只检索需要的列 SELECT FNumber FROM T_EMPLOYEE
SELECT FName ,FAge FROM T_EMPLOYEE
(3)列别名:SELECT FNumber as 编号,Fname as 姓名 FROM T_EMPLOYEE
(4)还可以检索不与任何表关联的数据,SELECT 1+1,SELECT NewID(),select getdate (5)基础函数
总数:SELECT count(*)FROM T_EMPLOYEE
最高:SELECT max(Fsalary)FROM T_EMPLOYEE
最低:SELECT min(Fsalary)FROM T_EMPLOYEE
平均:SELECT avg(Fsalary)FROM T_EMPLOYEE
总和:SELECT sum(Fsalary)FROM T_EMPLOYEE
12、数据排序
ORDER BY子句位于SELECT语句的末尾,它允许指定按照一个列或多个列进行排序,还可以指定排序方式是升序(从小到大排列 ASC),还是降序(从大到小排列 DESC)
(1)按照年龄升序排序所有员工信息的列表
SELECT* FROM T_EMPLOYEE ORDER BY FAge ASC
(2)按照年龄从大到小排序,如果年龄相同则按照从工资从大到小排序
SELECT *FROM T_EMPLOYEE ORDER BY FAge DESC,FSalary DESC
(3)ORDER BY语句要放在WHERE子句之后
SELECT *FROM T_EMPLOYEE WHERE FAge>20 ORDER BY FAge DESC, FSalary DESC
13、通配符过滤
(1)通配符使用 LIKE
(2)单字符匹配的通配符是半角下划线“_”,它匹配单个出现的字符,以任意字符开头,剩余部分为“erry”
SELECT *FROM T_EMPLOYEE WHERE FName LIKE ‘_erry’
(3)多字符匹配的通配符为半角“%”,它匹配任意次数(零或多个)出现的任意字符k%匹配以k开头,任意长度的字符串。检索姓名中包含字母n的员工信息
SELECT *FROM T_EMPLOYEE WHERE FName LIKE ‘%n%’
14、空值处理
数据库中,一个到如果没有指定值,那么值就为null,数据库中的null表示不知道,而不表示没有,因此select null+1结果是null,因为不知道+1还是不知道
(1)SELECT *FROM T_EMPLOYEE WHERE FName=null
SELECT *FROM T_EMPLOYEE WHERE FName!=null
都没有任何返回结果,因为数据库也不知道
(2)SQL中使用 is null ,is not null来进行空值判断
SELECT *FROM T_EMPLOYEE WHERE FName is null
SELECT *FROM T_EMPLOYEE WHERE FName is not null
15、多值匹配
(1)【23、25、28】SELECT *FROM T_EMPLOYEE WHERE FAge in (23,25,28)
(2)【20——30】 SELECT *FROM T_EMPLOYEE WHERE FAge between 20 and 30
16、数据分组
(1)按照年龄进行分组统计各个年龄段的人数
SELECT FAge Count(*) FROM T_EMPLOYEE GROUP BY FAge
(2)GROUP BY子句存放到WHERE语句之后
(3)SELECT语句后的列名到表中(聚合函数除外)
错误: SELECT FAge, FSalary FROM T_EMPLOYEE GROUP BY FAge
正确: SELECT FAge,AVG(FSalary) FROM T_EMPLOYEE GROUP BY FAge
17、Having语句
(1) 在WHERE中不能使用聚合函数,必须使用Having,Having要位于 GROUP BY之后
SELECT FAge count(*) as 人数 FROM T_EMPLOYEE GROUP FAge HAVING COUNT(*)>1
(2) 注意:Having中不能使用未参数分组的列,Having不能替代WHERE,作用不一样,Having是对组进行过滤
18、限制结果集行数
SELECT Top 5* FROM T_EMPLOYEE ORDER BY FSalary DESC
检索按照工资从高到低排序检索从第六名开始一共三个人的信息
SELECT Top3* FROM T_EMPLOYEE WHERE FNumber NOT IN(SELECT Top5* FNumber FROM T_EMPLOYEE ORDER BY FSalary DESC)ORDER BY FSalary DESC
19、去掉重复数据
SELECT DISTINCT FDepartment FROM T_EMPLOYEE
DISTINCT是对整个结果集进行数据重复处理的,而不是对每一个列,因此下面语句并不会只保留Fdepartment进行重复值处理
SELECT DISTINCT FDepartment, FSubCompany FROM T_EMPLOYEE
20、联合结果集
简单的结果集联合
SELECT FNumber,FName,FAge FROM T_EMPLOYEE UNION SELECT FIdCardNumber, FName,FAge FROM T_TEMPEMPLOYEE
基本的原则:每个结果集必须有相同的列数,每个结果集的列必须类型相同
Union ALL
UNION 合并两个查询结果集,并且将其中完全重复的数据合并成一条
SELECT FNameFROM T_EMPLOYEE UNION ALL SELECT FName FROM T_TEMPEMPLOYEE
Union因为要进行重复的扫描,所以效率低,因此如果不是确定要合并重复行,那么就用UnionAll
应用:
查询每位员工的信息,包括工号,工资,并在最后一行加上所有员工工资合计
SELECT FNumber,FSalary FROM T_EMPLOYEE UNION SELECT ‘工资合计’,SUM(FSalary) FROM T_EMPLOYEE
21、数字函数
(1)ABS( )求绝对值
(2)CEILING()舍入到最大整数,3.33将被舍入到4, 2.89将被舍入到3, -3.61将被舍入到-3
(3)FLOOR()舍入到最小整数 3.33将被舍入到3, 2.89将被舍入为2 -3.61将被舍入为-4
(4)ROUND():四舍五入, SELECT ROUND(-3.61,0) SELECT ROUND(3.1415926,3)
22、字符串函数
(1)LEN( )
SELECT FName, LEN(FName) FROM T_EMPLOYEE
(2)LOWER()、UPPER():转大写,小写
(3)LTRIM:字符串左侧空格去掉
(4)RTRIM:字符串右侧空格去掉
(5)LTRIM(RTRIM(‘ by ’));:去掉左右两边空格
(6)SUBSTRING(String,Start_postion,length)参数String为主字符串,start_postion为子字符串在主字符串的起始位置,length为子字符串的最大长度
SELECT SUBSTRING(‘abcdef1111’,2,3)
23、日期函数
(1)GETDATE():取得当前日期时间
(2)DATEADD(datepart,number,date):计算增加以后的日期,参数date为待计算日期,参数datepart为计量单位,例如DATEADD(DAY,3,date)
DATEADD(MONTH,-8 ,date)为计算日期date的8个月之前的日期
(3)DATEDIFF(datepart,startdate,enddate)计算两个日期的差额,datepart为计量单位
(4)DATEPART(datepart,date)返回一个日期的待定部分
统计不同工龄的员工个数
SELECT DATEDIFF(YEAR,FIndate,GETDATE()),COUNT(*) FROM T_EMPLOYEE GROUP BY DATEPART(YEAR,FIndate)
统计员工取年份
SELECT DATEPART (YEAR, Flndate, ),COUNT(*) FROM T_EMPLOYEE GROUP BY DATEPART(YEAR,FIndate)
24、类型转换函数
CAST(Expression AS Date_type)
CONVERT(Date_type , Expresion)
25、空值处理函数
ISNULL(Expersion,Value):如果Expersion不为空,则返回Expersion,否则返回Value
SELECT ISNULL(FName,‘佚名’)as 姓名 FROM T_EMPLOYEE
26、CASE函数
用法1——单值判断(相当于Switch-Case)
例如:SELECT FName ,
(
CASE FLEVEl WHEN 1 THEN ‘VIP用户’
WHEN 2 THEN ‘高级用户’
WHEN 3 THEN ‘普通用户’
ELSE ‘客户类型错误’
END) as FLEVELName FROM T_CUSTOMER
用法二
SELECT FName,FWeight,
(CASE WHEN FWeight<40, THEN ‘瘦瘦’
WHEN FWeight>50 THEN ‘肥肥’
ELSE ‘OK’
END) as isnormal FROM T_EMPLOYEE
实例练习
表中有A、B、C三列,用SQL语句实现。当A列大于B列时,选择A列,否则选择B列,当B列大于C列时选择B列,否则选择C列
SELECT(CASE WHEN A>B THEN A ELSE B END), (CASE WHEN B>C THEN B ELSE C END) FROM T
27、练习1
单号 金额
RK1 10
RK2 20
RK3 -30
RK4 -10
将上面的表输出如下格式
单号 收入 支出
RK1 10 0
RK2 20 0
RK3 0 30
RK4 0 10
select Num,(case when Price>0 then Price else 0 end) as '收入',(case when Price<0 then ABS(Price) else 0 end) as '支出' from [Pe].[dbo].[P3]