黑马程序员--SQL基础

---------------------- ASP.Net+Android+IO开发、.Net培训、期待与您交流！ ----------------------

 

●     熟悉数据库

1） 数据库管理的历史

人工管理阶段、文件系统阶段、数据库系统阶段

2） 数据库的模型

层次结构模型、网状结构模型、关系结构模型

3） 数据库的三级模式和二级映像

三级模式：模式（概念级）、外模式（用户级）、内模式（物理级）

二级映像：外模式/模式映像（逻辑独立性）、模式/内模式映像（物理独立性）

4） 数据库设计的完整性

实体完整性、区域完整性、参照完整性

5） 设计关系型数据库的准则—范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖

第三范式（3NF）：数据库表中不存在非关键字段对任一候选关键字段的传递函数依赖

6）E—R图（实体—联系图）：

       构成E—R图的基本要素：实体（矩形）、属性（椭圆形）、联系（菱形）

       实体之间的3种联系类型：一对一（1：1）、一对多（1：n）、多对多（m：n）

7） SQL（结构化查询语言）可分为4类：

（1）      数据定义语言（Data Definition Language，DDL）：对数据库中的对象的创建、修改、删除的操作，这些对象主要有数据库、数据表、视图、索引等。

（2）      数据操纵语言（Data Manipulation Language，DML）：对数据库表中的数据进行增加、删除、修改的操作。

（3）      数据查询语言（Data Query Language，DQL）：对数据库表中的数据进行查询。

（4）      数据控制语言（Data Control Language，DCL）：对数据库中的对象权限进行权限设置和取消等操作，但是只有数据库的系统管理员才有权力去执行对数据库对象权限的操作。

●     数据库的概念

1） DBMS（DataBase Management System，数据库管理系统）；

2） Catalog（分类）：又叫数据库（DataBase）、表空间（TableSpace）；

3） Table（表）、Column（列）、Field（字段）

4） 主键（PrimaryKey）和外键（ForeignKey）：

业务主键：使用有业务意义的字段做主键；

逻辑主键：使用没有任何业务意义的字段做主键；

两种常用的主键数据类型：

int（或bigint）+标识列（又称自动增长字段）：

优点：占用空间小，无需开发人员干预，易读。缺点：效率低，数据导入导出麻烦；

uniqueidentifier（又称GUID、UUID）：

优点：效率高，数据导入导出方便。缺点：占用空间大，不易读；（MSSQLServer：newid()；C#中：Guid.NewGuid()）

●SQL常用语句

1） char(10)：不可变长度字符串，不足之处用空格填充

varchar(10)：可变长度字符串

nvarchar(10)：区别于varchar(10)，可能有中文时使用

bit在数据库图形界面中可选值为true、false，在SQL语句中可选值为0、1

2） SQL语句中字符串用单引号；SQL语句关键字大小写不敏感，但字符串内容大小写敏感；在SQL语句中，如果出现中文，前面最好加上“N”

3） 插入数据：

主键int：insert intoT_Person（Name，Sex，Age） values（N‘张三’，0，18）

主键uniqueidentifier：insert into T_Person（Id，Name，Sex，Age） values（newid()，N‘张三’，0，18）

outputinserted.Id在插入语句的values之前使用，输出插入后的Id：

insert intoT_Person（Name，Sex，Age） output  inserted.Id values（N‘张三’，0，18）

4） 更新数据：

（1）更新一个字段的所有数据：

update T_Personset Age=20；

（2）更新多个字段的所有数据：

update T_Personset Name=N‘张三’，Age=20；

（3）更新满足指定条件的数据：

update T_Personset Name=N‘青年人’where Age>20；

5） 删除数据：

drop table T_Person（删除整个表）

delete from T_Person（清空表中所有的数据）

delete from T_Personwhere Age >20（删除表中满足指定条件的数据）

6） 检索（查询）数据：

（1）检索与表无关的数据：

select 2+3 as 列1，newid() as 编号，getdate() as 日期

7） 数据排序（order by）和限制结果集行数（top n）：

（1）排序（order by）：

升序：order by……asc

降序：order by……desc

order by要放到where之后；

按照一个字段进行排序：

select * from T_Personwhere Age >20 order by Salary asc

按照多个字段进行排序：

select * from T_Personorder by Age asc，Salary desc（先按照Age进行升序排列，再将Age值相同的部分按照Salary降序排列）；

（2）top n和row_number()：

top n语法：select top n 字段名 from 表名 [order by语句]；

row_number()语法：row_number() over（order by语句）as 别名；

例如：查询工资从高到低排序的从第6个开始前3个的信息

select top 3 *from T_Person where Id is not in（select top 5 Id from T_Person order by Salary desc）order by Salary desc

等价于：

select * from（select row_number() over（order by Salary desc）as 行号，* from T_Person

）as T_NewPerson

where T_NewPerson.行号 between 6 and 8

8） 通配符过滤：

通配符过滤使用like（类似……的样子）；

通配符下划线“_”：代表单个任意字符；

通配符百分号“%”：代表任意长度（零或大于零）的任意字符；

       Nameis null、Name is notnull（不能写成Name =null）

9）多值匹配：

       in（值1，值2……）：字段的值为值1或值2……

       例如：

       select* from T_Person where Age in（18，20）等价于：

       select* from T_Person where Age=18 or Age=20

       between值1 and 值2：字段的值在值1和值2之间（包括等于号）

       例如：

       select* from T_Person where Age between 18 and 20 等价于：

       select* from T_Person where Age >=18 and Age <=20

10）聚合函数：

max()、min()、avg()、sum()、count()

11）数据分组（group by）：

       selectAge，count(*) fromT_Person

       whereSalary >2000

group by Age

having count(*)>2

注意：

       select和having后的字段必须是：聚合函数或者group by后出现过的字段

       where子句中不能出现聚合函数

12）消除重复的数据（distinct）：

       （1）消除一个字段中相同的数据：

       selectdistinct Name from T_Person

（2）消除多个字段中完全相同的数据：

select distinctName，Age from T_Person

13）联合结果集（union / union all）：

       union：默认会消除重复的数据（类似distinct）

       unionall：不会消除重复的数据

       用union联合的上下两个语句中的字段的个数必须一致，对应字段的类型必须相兼容；

       union常用的两个例子：

       例一：

       select‘正式工最高年龄’，max（Age）from T_Employee

       unionall

       select‘正式工最低年龄’，min（Age）from T_ Employee

       unionall

       select‘临时工最高年龄’，max（Age）from T_TempEmployee

       unionall

       select‘临时工最低年龄’，min（Age）from T_ TempEmployee

       例二：

       selectName，Salary fromT_Employee

       unionall

       select‘工资合计’，sum（Salary）from T_Employee

●SQL常用函数

1） 数字函数

（1）ABS()：求绝对值

（2）ceiling()：舍入到最大整数

如：3.33---> 3，2.89---> 3，-3.61---> -3

（3）floor()：舍入到最小整数

如：3.33---> 3，2.89---> 2，-3.61---> -4

（5）      round()：四舍五入

如：3.33---> 3，2.89---> 3，-3.61---> -4（精度为0时）

select round（3.1415926，3）结果为3.1420000

2） 字符串函数

（1）len()：计算字符串的长度

（2）lower()：将字符串中的字符都转为小写；upper()：将字符串中的字符都转为大写；

（3）ltrim()：去除字符串左边的空格；rtrim()：去除字符串右边的空格；

（4）substring（被截取的字符串，开始的位置，截取的长度）：截取子字符串（开始的位置从1开始）

3）日期函数

       （1）getdate()：获得当前的日期时间（年月日时分秒）

       （2）dateadd（日期单位，日期数量，指定的日期）：在指定的日期上增加指定日期单位的日期数量

       （3）datediff（日期单位，开始日期，结束日期）：计算两个日期之间的差额（计算时，只与指定的日期单位及比其大的日期单位有关，与比其小的日期单位无关）

       （4）datepart（日期单位，指定的日期）：取出指定日期的指定日期单位的数值

4）类型转换函数

       将指定的字符串转换成指定的数据类型：

       （1）cast（指定的字符串 as 数据类型）；

       （2）convert（数据类型，指定的字符串）；

5）空值处理函数

       SQL中null表示“不知道”；

       例如：

       select‘abc’+‘123’（结果为“abc123”）

       select‘abc’+‘’  （结果为“abc”）

       select‘abc’+ null  （结果为null）

       isnull（Name，‘匿名’）：当字段Name的值为null时返回‘匿名’，否则返回原值；

5） case函数的两种用法

第一种用法：

case 表达式

when value1 then返回自定义值1

when value2 then返回自定义值2

when value3 then返回自定义值3

……

else 返回自定义默认值

end

例如：

select Name，

(

case level

when 1 then  N‘普通客户’

when 2 then  N‘黄金客户’

when 3 then  N‘钻石客户’

else  N‘未知客户类型’

end

) as 客户类型 from T_Guest

第二种用法：

case

when 条件表达式1  then 返回自定义值1

when 条件表达式2  then 返回自定义值2

when 条件表达式3  then 返回自定义值3

……

else 返回自定义默认值

end

例如：

select Name，

(

case

when Salary<3000 then N‘低收入’

when Salarybetween 3000 and 5000 then N‘中等收入’

else  N‘高收入’

end

) as 收入水平 from T_Person

●     表连接（join）

inner join…on…可以简写为join…on…

1） 内连接（join…on…）

（1）等值连接：连接条件中用“=”连接两个条件列表

（2）不等值连接：连接条件中用“>”、“>=”、“<”、“<=”、“!=”、“< >”、“between…and…”、“in”、连接两个条件列表；这种连接方式通常需要和其它的等值运算一起使用，否则检索出来的数据很可能没有实际意义；

2）自连接（join…on…）

       把自身表的一个引用作为另一个表来处理

3）外连接

       （1）左外连接（left join…on…）：

       返回的结果不仅仅是符合连接条件的行记录，还包括了左表中未匹配成功的记录

       （2）右外连接（right join…on…）

       返回的结果不仅仅是符合连接条件的行记录，还包括了右表中未匹配成功的记录

       （3）全外连接（full join…on…）

       返回的结果不仅仅是符合连接条件的行记录，还包括了左右表中未匹配成功的记录

●     子查询

子查询就是将一个查询语句作为一个结果集供其它SQL语句使用

1） 子查询的结果集为一行一列

select * fromT_Person where Age >（select avg(Age) from T_Person）

2） 子查询的结果集为多行一列

select * fromT_Reader where JoinYear in（select PrintYear from T_Book）

3） 子查询的结果集为多行多列（即把子查询当成一个表）

select * from （select * from T_Person）as T_NewPerson

●     索引

索引相当于书的目录

索引会占用空间，添加、更新、删除数据的时候需要同步更新索引，因此会降低insert、update、delete的速度。所以只在经常检索的字段上创建索引。

即时创建了索引，仍然有可能造成全表扫描，比如like、函数、类型转换；

 

---------------------- ASP.Net+Android+IO开发、.Net培训、期待与您交流！ ----------------------