SQL

基础—高级

我们怎么存储数据

以前用各种书籍载体在上面按照特定的格式记录信息，就像现在的excel表格，

但是，如果当数据有几千万条的时候，查询就非常慢了

所以我们需要一种高效的解决方案：数据库(其实也是操作一种特殊格式的文件)

数据库不仅仅要存储数据，还要提供方便快捷的查询，修改，删除等功能

需要做到：海量存储，快速查询，多用户同时查询，用户访问安全性，数据存储完整性(正确性)等

?

什么是数据库

数据的仓库，货仓里有很多货架(库文件)，货架有不同的种类，不同的架子放不同的货物，

管理仓库的就管理员就对应DBA

数据如何存储

?

?

数据库概述

数据库软件：

现在市面上有很多种数据库：mssqlserver，oracle，db2，access, SQLite等

mssqlserver是微软出品的，大型数据库，和net结合度很高

oracle是甲骨文公司，被谷歌收购了，大型数据库，注重数据安全性

db2也是一个大型数据库

access是微软的一个小型文件数据库，在office套件中

?

数据库实例：用来区别数据库引擎的名字，如果不写那就是默认的"MSSQLSERVER"

SUNCODERBOOK 机器名

115.21.23.58 IP地址

?

验证方式：

windows验证：使用登陆windows的账号和密码

sqlserver验证：填写账号和密码，

????????

?

此错误是没有开数据库服务

?

数据库文件：磁盘上的文件

?

数据库服务：这个才是真正的数据库

数据库运行简易流程

成功安装完数据库之后，会有一个"SQL Server Management Studio"，

打开这个软件即可操作数据库，

但真正的数据库并不是这个软件，是系统内一个没有界面的服务程序，

由这个管理工具，发送指令操作这个服务，然后服务操作硬盘上一个".mdf"数据库文件

数据就是存在这个mdf文件里的

数据库中的概念

1. 我们所使用的如，mssqlserver oracle等数据库软件
2. 打开数据库管理软件可以看到的一个一个实际存放数据的集合

?

建库：建立数据库

注：一个数据库不仅仅只能是一个数据文件和一个日志文件，

?

表概念：在一个数据库(上述第二种概念)，如同仓库里面的货架一样，不同的数据放在不同的表里面，根据放的数据不一样，对使用空间进行优化

列，行概念：一张表里面的每一项，就是表示这个表要包含写什么类型的数据，列叫做字段

这张表包含 编号 用户名 用户密码 三列

一共有3条数据 每一行就是一条数据

SQLServer的管理

安装好数据库后，可以使用自带的数据库管理工具进行管理，也可以使用visusl studio进行管理，也可以使用cmd命令管理，

用管理工具管理需要连接数据库

服务器类型：数据库不仅仅只有数据库引擎，还有其他的引擎，数据库引擎就是以存储数据为主要功能的程序核心

à服务器名称：服务器的地址，如果是网络线上服务器，填写这个服务器的ip地址，开发的时候一般数据库就装在本机上，所以这里可以直接填写计算机名字或者是实例名字

à实例名：同一台服务器用来区分多个运行的数据库引擎的名字，开发的时候一般只有一个实例，所以填写"."就可以了，如果安装的数据库版本是express版本的话那么就是"./express"

à身份验证：登陆数据库的验证模式

windows 身份验证，就是使用本机当前登陆的用户的账号密码登陆，

sqlserver身份验证，就是使用安装时或安装后自定义的一个账号和密码登陆

以上两种模式，在数据库安装的时候会有选项供选择

创建数据库

用管理工具连接上数据库之后，在对象资源管理器上右键，有新建数据库选项，点击打开窗口，填写相关信息

住：初始大小，路径

选项中：兼容级别

?

展开新建的表：在"表"上右键，有新建表选项，

?

?

数据类型

工资：monney类型

Char,8000 固定长度 非unicode

Varchar,8000 可变长度 非unicode

Nchar,4000 固定长度 unicode

Nvarchar,4000 可变长度 unicode

?

如果实际开发中，对于位数能确定的数据，就用固定长度的

如果有中文的，要用unicode

?

身份证号：18位 ，数字，字母，

银行卡：19 数字， char

?

?

?

?

不允许保存修改，要求重新创建，

?

Unicode 是用两个字节表示一个字符

工具-选项—设计器-去掉勾"阻止保存"

主键（PrimaryKey）

主键的作用

业务主键：

逻辑主键

表间关联、外键（ForeignKey）

为什么要有主外键 示例见批注

?

>练习

• 创建一个HeiMaBlog数据库。
• 创建一个班级表：Class：
  • Id （班级编号，自动编号，主键）、
  • Name（班级名称）、
  • Descr（班级简介）。
• 创建一个学生信息表：Student
  • Id（学生编号，自动编号，主键）、
  • Name（学生姓名）、Gender（性别）、
  • Address（家庭地址）、Phone（电话）、
  • Age（年龄）、Birthday（出生日期）、
  • CardId（身份证号）、CId（班级Id）
• 分离数据库
  • 在需要分离的数据库上点右键-任务-分离
• 附加数据库(在其他计算机上，亲自测试！)
  • 在数据库节点上点右键-附加
• 打开数据之前，要打开数据库服务

?

SQL语句入门(脚本、命令)

什么是sql语句

?

使用sql语句创建数据库和表

CREATE DATABASE HeiMaBlog

ON PRIMARY --默认就属于PRIMARY主文件组，可省略

(

NAME=‘HeiMaBlog‘, --主数据文件的逻辑名

FILENAME=‘D:\ HeiMaBlog_data.mdf‘, --主数据文件的物理名

SIZE=5mb, --主数据文件初始大小

MAXSIZE=10mb, --主数据文件最大的值

FILEGROWTH=15% --主数据文件的增长率

)

LOG ON -- 日志文件

(

NAME=‘HeiMaBlog_log‘,

FILENAME=‘D:\HeiMaBlog_log.ldf‘,

SIZE=3mb, --日志文件初始大小

MaxSize=20mb,

FILEGROWTH=1MB

)

GO

主数据文件名和日志文件名不能一样，主数据文件大小最小是5MB,日志文件最小是1MB

?

"GO"关键字是明确告诉管理工具，先把上面的代码执行了，然后在执行下面，该关键字不处于sql语法本身

USE HeiMaBlog--将当前数据库设置为HeiMaBlog

GO

CREATE TABLE Score

(

ScoreId INT IDENTITY(1,1),

SId INT NOT NULL ,

English INT NOT NULL,

Math INT NOT NULL

--Name Varchar(50) not null

)

?

>创建表练习

• 创建数据库TestSchool
• 创建学生成绩表ScoreScoreId（成绩id,主键,自动编号）、SId（学生编号）、English（英语成绩）、Math（数学成绩）
• 创建老师表Teacher
• Id、Name、Gender、Age、Salary、Birthday

?

create table Teacher

(

Id int identity(1,1),

Name nvarchar(10) not null,

Gender bit not null default 0,

Age int not null default 10,

Salary money not null,

Birthday datetime not null default getdate(),

)

?

增删查改

数据插入

insert into students values(null,‘new‘,‘男‘,0,getdate())

default

nvarchar N，

set identity_insert teacher off

数据更新(数据修改)

1. Where子句，用id查找，速度快；可以用查询计划按钮来对比，消耗的资源。
2. ★and优先于or执行
3. set age=age+1;
4. 空值的判断where address=NULL → is null，is not null
5. 不等于号：<> ，!=两种都可以
6. ?

数据删除

delete truncate

drop table Teacher

>练习：

• 插入 几条老师信息 和成绩（注意：bit类型）
• 练习1：给studentId是1的英语成绩加10分
• 练习2：考试题偏难，所有人的成绩加5分
• 练习3：所有女学生的年龄减1岁
• 删除工资大于2000的老师
• ============将老师表清空========
• 删除所有老师
• 删除数据时候 把自增长列的值还原成种子

?

约束-保证数据完整性（数据检查）

非空约束

????建表的时候每一个字段后面的null复选框

?

主键约束(PK) primary key constraint

????设置主键就可以了，数据不重复 且 不能为空

?

唯一约束 (UQ)unique constraint

唯一 允许为空，但只能出现一次

右键"索引/键"里面有

?

默认约束 (DF)default constraint 默认值

????建表设置默认值的时候就添加了这个约束

?

检查约束 (CK)check constraint 范围以及格式限制

????在表的设计界面，在字段上右键，有check约束

????

?

外键约束 (FK)foreign key constraint 表关系：保证外键值来源于主键

增加外键约束时，设置级联更新、级联删除：

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

?

>练习

• Teacher表中
  • Gender 控制只能是男 女，默认女
  • Age 在30-40之间 默认30
  • Score表中
  • studentId 是外键 先要把Student表中的sId设置为主键
  • 测试外键约束：
  • 1：在学生表（主表）中删除在成绩表中被引用的学生记录。
  • 2：成绩表中添加一条新成绩，studentId在 学生表中没有。
• 保存SQL脚本。再次打开即可执行。

?

数据检索 Select

最基本的检索代码

1. 取别名，AS
2. 查询无关数据库信息：select getdate()
3. order by；desc，asc；汉字排序：用拼音的首字母a-z；多个排序一个个执行，就像嵌套????
4. ?

区间条件的查询

1. 《 between in

between 。。and。。。：数据库效率，在数据库内部做过特殊优化的；优先使用；。。。范围表识相当于>=,<=

1. 分页算法：select * from dbo.strdent where between (num-1)*5+1 and 5*num
2. In：select *from dbo.studeent where cid=1 or cid=2比较select *from dbo.studeent where cid in (1,2)
3. ?

?

Top、Distinct

select top 10 * from Teacher

查出前面10行

select top 20 percent * from Teacher

查出前面20%的数据

select distinct(age) from teacher

查询年龄列，但只要不重复的

select distinct age,name from teacher

查询两个列都不重复的，可以有其中一个是相等的

distinct：去除重复数据，是针对查询结果去除重复行；distinct先于top执行，也要写在top前面。

聚合函数

max, min, avg, count ······

1. 聚合函数,max(age),不能跟列字段一起查询；
2. min，avg，sum；count查询满足条件的条数；
3. where子句先执行；聚合函数对NULL值不计算，唯有id非空，可以做文章；
4. 如果max(Name),查询的是首字母顺序最大的，min最小

?

?

模糊查询(都是针对字符串操作的)

% _ [] ^ not like

1. 模糊查询：like ; not like
2. _：一个任意字符；
3. %：零个或任意多个字符；
4. []：代表一个字符的取值区间；
5. ^：结合[]的非运算；
6. 查询里面不区分大小写

空值处理

Is null

1. isnull...可以判断后面的是否可为null，如果为null就用第二个参数代替;is not null

数据排序

AES DESC 汉字

1. 数据排序：where子句放在order by前面，想原因，效率？

数据分组

1. 查询的结果集里是分组信息，不再是表里的行信息。
2. 数据分组：group by ;可以取到分组根据的信息、可以取到聚合函数信息；返回的是每个组的汇总信息

一个班级一张表，然后有个小组表，小组的表的信息只有"一组，二组，三组"这些信息，没有组员名字，年龄等信息，

一个小组包含多个组员的信息，要知道小组的名字，不知道到底要拿哪一个显示

根据"什么"分组，那么可以想象这个分组的名字就是"什么"

Having语句

1. 为分组结果 筛选
2. having子句：针对于分组之后的信息进行筛选过滤。对比where子句是对分组之前原来的表信息进行筛选。

SQL语句的执行顺序

5>…Select 5-1>选择列,5-2>distinct,5-3>top

1>…From 表

2>…Where 条件

3>…Group by 列

4>…Having 筛选条件

6>…Order by 列

从某某表查询大于多少小于多少的数据，然后根据某某分个组，筛选下不想要的组

?

>Group by 练习

• 从orders表中查询：
  • 1.热销售商品排名表，即按照每种商品的总销售数量排序。
  • 2.请统计销售总价超过3000元的商品名称和销售总价,并按销售总价降序排序。
  • 3.统计各个客户对"可口可乐"的喜爱度(既统计每个购买人对"可口可乐"的购买量)

?

类型转换函数

CAST CONVERT

1. select isnull(cast(age as nvarchar(10)),‘保密‘) from student;
2. ????convert(varchar(20),getdate(),105);--105是样式标志

联合结果集union(集合运算符)

Union all

使用注意点：

1. 同的列数；列有相同的数据类型（相容、可以进行隐式转换）；
2. 列名由第一个集合确定；
3. Union会去除重复项，union all则不会去除
4. 联合查询，只能有一个order by子句，这个order by子句是针对整个联合查询的，最后执行。

案例1

要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩

建库脚本à

案例2

查询每位老师的信息，包括姓名、工资，并且在最后一行加上平均工资和最高工资

建库脚本à

一次插入多条数据

1. 将结果集作为值插入数据库。
2. 结果集的列数量、类型都要一样。
3. 一种用select结果集，另外用select联合结果集
4. select * into score2 from score where 1<>1

字符串函数（*）

declare @str varchar(10) = ‘ aa ‘

定义一个变量，并且赋值

select LTRIM(@str)--aa --

1. 去除左空格

union all

select CONVERT(varchar,DATALENGTH(LTRIM(@str)))—5

1. 去除空格后的字节长度

union all

select RTRIM(@str)-- aa-

1. 去除右空格

union all

select CONVERT(varchar,DATALENGTH(RTRIM(@str)))—5

1. 去除右空格后的字节长度

union all

select LTRIM(RTRIM(@str))--aa—

1. 去除右空格又去除左空格

union all

select CONVERT(varchar,DATALENGTH(LTRIM(RTRIM(@str))))--2

1. 去除两边空格后字符数

select left(‘abcdefg‘,3)--abc

1. 从左向右取3个字符

select right(‘abcdefg‘,3)--efg

1. 取最右边的三个字符

select SUBSTRING(‘abcdefg‘,3,4)--cde

1. 索引从1开始从第三个字符截取四个长度

select SUBSTRING(‘abcdefg‘,3,40)--cdefg

9.1 从第三个字符截取四十个长度，长度不足，不会报错

select SUBSTRING(‘abcdefg‘,30,40)

9.2 从第三十个字符截取四十个长度，长度不足，不会报错

补充：

1. 字符串函数：select len();字符串长度，
2. select datalength()，求字节数，一个空格、数字和字符一个字节，一个汉字两个字节，中文标点符号两个字节，英文标点符号一个字节。
3. select lower(),select upper()
4. ?

?

日期函数

select GETDATE()--2013-02-27 10:25:32.532

获取当前日期

select DATEADD(day,2,getdate())--2013-03-01 10:27:327

在现有基础上加上两天

select DATEDIFF(day,‘2012-05-05‘,GETDATE())--298

取得日期的特定部分

select DATEPART(year,getdate())--2013

select DATEPART(MONTH,GETDATE())--2

select DATEPART(day,GETDATE())--27

select DATEPART(hour,getdate())--10

select DATEPART(minute,GETDATE())--43

select DATEPART(SECOND,GETDATE())--14

select DATEPART(MILLISECOND,GETDATE())--817

?

select * from users where DATEPART(year,dat)=1990

>练习

• 创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。
• 要求：
  • 输出所有数据中通话时间最长的5条记录
  • 输出所有数据中拨打长途号码（对方号码以0开头）的总时长
  • 输出本月通话总时长最多的前三个呼叫员的编号。
  • 输出本月拨打电话次数最多的前三个呼叫员的编号
  • 按照月份分组。

建库脚本à

?

1.sql_初阶（理论）