数据库--mysql学习
新建表
1 | create table 表名（ |
添加数据
给指定字段添加数据
insert into 表名 (列1名，列2名) values(值1，值1);给全部字段添加数据
insert into 表名values (值1,值2,….) ;批量添加数据
insert into表名(列1名，列2名,…)values(值1,值2,…)，(值1，值2,…)，(值1，值2…);
insert into 表名 values (值1,值2,…),(值1,值2,…),(值1，值2,..);
插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。‘2002-03-03’
插入的数据大小，应该在字段的规定范围内。
修改表
修改数据
update 表名 set 列名1=值1，列名2=值1,….[where 条件]
eg 修改id为10010的性别和是否为党员
update S set ssex=‘男 ‘ ，ifdangyuan=1,….[where sno = 10010]eg 修改所有学生的入学日期为2022-9-1 不带where条件将更新整个表
update S set ruxueriqi = ‘2022-09-01’eg 删除某一列的值：
delate 表名 set 列名1=null [where 条件]
向已有表增加新列
新增一列
Alter table 表名 add 字段名及参数
eg: alter table 课程表 add 所属专业 nchar(5)新增多列
alter table 课程表 add 开课学院 nchar(10),任课教师 nchar(6)
删除数据
delete from 表名 [where 条件]
delete语句的条件可以也可以没有，没有条件回删除整张表的所有数据（清空表）
delete语句不能删除某一个字段（列）的值
删除某一个字段（列）的值可以用update
- eg 删除表中性别为女的员工们
delate from 表名 where sex=‘女’; - 删除表中所有行
delete from 表名;
删除表中已有列
alter table 表名 drop column 列名
eg: alter table 课程表 drop column 开课学院,任课教师
改变表已有列的信息
如把身高列的宽度增加一位
原来列 hight numeric(5,2),
alter table S
alter column hight numeric(6,2);
意思是要修改表S中的列hight的数据类型和长度
把表1更名为表2
select * into 课程表2 from 课程表1
drop table 课程表1
把原1表全部表头和内容复制2表，再把1表删除掉
对已有列更名
假设原表课程表2已有大量数据，不可能重新建表，现要把”所属专业”改为“从属专业
alter table 课程表 add 从属专业 char(20) update 课程表 set 从属专业=所属专业
注：更新，把所尾专业的内容数据复制到从属专业列 alter table 课程表 drop column 所属专业 select * from 课程表 不要在意列的左右顺序，也不要在意行数据的上下位置顺eg 把学生表的生日列修改为“出生日期”列:
alter table 学生表 add 出生日期 smalldatetime
update 学生表 set 出生日期=生日
alter table 学生表 drop column 生日注意，去过原有列有了约束之类的设置那么无法删除
把一个大表拆分成多个小表
把查询结果保存到一个新表中.(可拆分表,行向上拆分和合并)
select exps1 into tablename from …… 把查询结果不以视图显示，而是默默地存放到一个新表中去（物理表）
select * into T1 from 学生表 where 性别=’男’
select * from T1
select * into T2 from 学生表 where 性别=’女’
把多个结构相同的表合并成一个大表
主要数据类
数值型
- int 4字节 bignit 8字节 smallint 2字节 tinyint 1字节且是无符号数
- numeric(p,q) 小数，指定最多为p位，其中有q位小数
- 数据库中少用近似型float和double，多用准确型
字符串型
- char(n) 没有中文汉字使用
- varchar(n)没有中文汉字用，可变长度
- nchar(n)有中文汉字用，长度固定且不太长
- nvarchar(n)有中文汉字用，可变长度
- text长篇内容
TEXT类型通常用于存储较长的文本信息，例如备注、描述、文章内容等。与其他字符串类型（如VARCHAR）不同的是，TEXT类型可以存储更大的文本值，并且不需要指定最大长度。这使得它非常适合存储可变长度的文本数据，无论文本内容有多长
日期时间型
Datetime 占8字节空间来存放一个标准的日期时间，最早支持1753年。
Smalldatetime 占四字节，只支持 1900-1-1 -2079年
Datetime支持到毫秒，Smalldatetime支持到分钟
一个日期是相对于1900-1-1的间隔天数，一个日期对应的是一个整数。所以日期其实是一个整数，使用公式显现成一个日期格式 日期1-日期2=整数n 时间是一个相对于当天从00:00:00开始的秒数 时间间隔计算：t2-t1得间隔秒数
特别注意： 字符串常量，日期时间常量在sqlserver用单引号
存放二进制的文件内容型
- binary(n) 用于存储固定长度的二进制数据
- image 用于存储可变长度的二进制数据，通常用于存储图像、声音、视频等多媒体数据
- varbinary(max) QL Server 中更现代的用于存储可变长度二进制数据的数据类型，可以存储最大长度的二进制数据(推荐)
数据完整性控制
完整性：数据是否完好无缺，是否符合要求
约束作用在表中的字段上，可在创建表和修改表的时候添加约束
primary key 主码约束
建立表时,在列名后跟primary key
主码值不能为空，不能重复，用于唯一标记同一个表的不同数据一个表只能最多有一个主码，也可无主码。
此时这个属性叫主属性，其他列叫非主属性。
unique 唯一约束
建表时在最后加unique
限制一个表中某列值不能重复（比如微信号）但可以为空，对于候选码，一个表多个列可以分别使用unique约束
foreign key 外键约束
让多张表的数据之间建立连接，保证数据的一致性和完整性
立足于成绩表时，把成绩表的学号参考学生表的学号，把成绩表中的课程号参考课程表的课程号. 完成约束后，不存在于学生表的人，不存在于课程表的课程，就不能在成绩表中出现.
create table 学生表(学号 char(7) not null primary key,姓名 char(10) not null ,性别 char(2) ,年龄 tinyint,所在系 char(20))
create table 课程表(课程号 char(6) not null primary key,课程名 char(20) not null,学分 tinyint,学期 tinyint)
create table 成绩表( 学号 char(7) not null,课程号 char(6) not null,成绩 tinyint,
primary key(学号,课程号),
foreign key(学号) references 学生表(学号),
foreign key(课程号) references 课程表(课程号) )说明：一般表主属性是一列，有时有多列组成PK（PK可是属性，也可以是属性组）. PK是属性组时，要独立于列来实现主码约束。 , primary key(学号,课程号)： 完成后，在成绩表不允许同一个学生选两次同一门课
on delete cascade 外键约束
on delete cascade 是一种外键约束动作，用于在删除主表中的记录时同时删除与其相关联的从表中的记录
在表间参照完整性中，如果想实现级联删除，即如：删除学生表中的某个同学时，则成绩表中该外键对应的成绩记录会自动删除。可以这样实现：
create table 成绩表(学号 char(7) ,课程号 char(3), 成绩 tinyint,
foreign key(学号) references 学生表(学号) on delete cascade )
not null 非空约束
表示本列的内容值不能为空 建表时,在列名后跟not null
default 默认值约束
生成新数据元素行时的默认值 (比如把性别默认为女)
建表时
gender CHAR(1) DEFAULT ‘男’, – 默认值为 ‘男’
age int default 18 – 默认值为 18
check 检查约束
元组级约束（行）
条件中同时涉及到了多列就叫元组约束单一一列叫列级约束
如 check(性别 =’男’ and 身高 >=170 or 性别=’女’ and 身高>=160)
约束检查发生完成该元组数据编辑时。在编辑该列时，完成时，离开该列数据单元时，发生的条件检测。鼠标离开编辑行状态，或insert命令操作时发生
条件约束（列）
在创建表时候，直接在后面写check(约束条件)
数据排序（多列数据）
select 列1,列2… form 表 order by 列 排序方式
列：可以是列名，也可以是列序号，如3表示按第三列排序
排序方式：
asc升序（从小到大）是默认的
dese降序（从大到小）
同时排序多列数据
order by 最先排的列 排序方式 ,其次排的列 排序方式, …….
例子：把学生表所有人按性别分类，同类性别内按身高降序，如果性别和身高再相同，则进一步按年龄升序(年龄以生日为准)
select * 学号,姓名,性别,身高 ,生日
from 学生表 order by 性别 ,身高 desc,生日*desc
数据排序大小原理
数值按大小比较，英文按ASCLL比较大小
汉字始终比英文大，小写字母比大写字母大，这个顺序叫英文字典
字符串：abe>abcd 因为e>c 中国>人们 因为 Z>R
日期：今天>昨天，日期实际上对应的是一个整数。出生日期和年龄是反的
逻辑值true>false
例 对已存在表建立约束
constraint （约束），对表添加检查约束用（可也以省略）
对一个已经存在的表建立主码约束
前提是表内容是空的才行,且该列事先定义为not null 不允许为空值数据，才能在以后增加逐渐约束
alter table 学生表 add constraint stuidpk primary key(sutid)意思是给学生表的stuid这一列add constraint 加 primary key 主码约束 它的名称为sutidpk
对一个已经存在的表建立唯一约束
alter table 表名 add constraint 约束名 unique (列名)
eg 对课程表中的课程名称进行唯一限制
alter table C add constraint canmeU unique (cname)
对已有表添加默认值约束
column（列），修改列的属性包括默认值用修改列的方法
alter table Stu alter column gender set default ‘男’
alter table Stu 表示要修改的表是Stu
alter column gender 表示要修改的列是gender
对已有表添加check约束
alter table 表 add constraint
alter table 表名 add constraint 列名 check (列名 条件)
alter table 表名 add check (列名 条件)
下面两种都对
alter table S8 add hight2 numeric(3,2)
alter table S8 add hight numeric(3,2)
alter table S8 add constraint hight2 check (hight2 between 1.00 and 2.00 )
alter table S8 add check(hight between 1.00 and 2.00)alter table S add constraint stel check(stel like’….’)
alter table Stu add constraint Age check (age >=18 and age <=100)
注意事项和问题
注意
所有的属性值都必须去取原子值：每一个属性不能够再分开（比如生日年月日不能再分开，除非建就建两列）
表的行和列的上下左右顺序都是无意义的（属性的顺序无关性）
完整性实际例子
比方：网上订购的8斤水果，而我们收到了5.5斤，发和收方的数据不一致，被破坏了完整性。在数据库，一表的数据在数据对应依赖于另一个表，如果缺失和破坏了这个依赖，就发生完整性错误。如：在图书销售表中出现事先不存在的书店编号. 在成绩表中出现根本不存在于学生表的人
约束ID问题
为什么对已有表加主码约束要给这个约束一个ID如PK_MyTable_ID，而建表时候添加约束只需要在最后添加primary key 就可以，不需要给这个约束一个ID？
GPT回答：
在 SQL 中，给约束一个名字（比如
PK_MyTable_ID）并不是必需的，但是是一种良好的实践，特别是在以下情况下：
- 清晰性和可读性：给约束一个有意义的名字可以提高代码的可读性。当数据库结构变得复杂时，清晰的约束名可以帮助其他开发人员更好地理解数据库模式和约束。
- 易于识别：如果数据库中存在多个约束，给每个约束一个唯一的名字可以方便识别和引用这些约束。例如，在查询或日志中提到约束时，使用具有描述性名称的约束可以更清楚地表达意图。
- 易于管理：具有有意义的约束名可以帮助您更轻松地管理数据库结构。例如，您可能需要在将来删除或修改约束，有一个明确的约束名称可以使这些操作更简单。
虽然给约束一个名字并不是必需的，但是在实际开发中，这是一个良好的编程习惯，可以提高代码的可读性、维护性和可管理性。
关系代数理论
基本概念
- 对关系进行集合代数的运算
- 三要素：运算符，运算对象，运算结果
- 关系操作是集合操作方式：一次一集合
- 关系操作如查询，结果是元组集，把这些结果以view视窗（外模式）方式呈现给用户
集合运算 并差交关系乘
union并
- 将两个查询的结果集合合并乘一个结果集，合并时会去除重复的行
- 注意：连亘查询的结果集的列数和数据类型必须一致
- 例：从学生表1中找到姓杨的人，从学生表2中找到姓李的人，合成一个视图
- (select 学号,姓名,年龄,家庭住址 from 学生表2 where LEFT(姓名,1)=’李’) union (select 学号,姓名,年龄,家庭住址 from 学生表1 where LEFT(姓名,1)=’杨’)
except 差
从第一个查询结果中排除掉与第二个查询扯过相同的行，返回剩余的结果集
例：从学生表1中查出数据减去在学生表2中存放的数据
（select 学号,姓名,性别 from 学生表1 ）except （select 学号,姓名,性别 from 学生表2）
intersect 交
找出两个查询结果集中共同存在的行，并返回这些共同行组成的结果集
例：把学生表中年龄19以上人与学生表2中的李姓人求交集
(select 学号,姓名,性别,年龄 from 学生表 where 年龄>=19) intersect (select 学号,姓名,性别,年龄 from 学生表2 where LEFT(姓名,1)=’李’ )
关系乘（广义的笛卡尔积）
例如学生表中的所有学生把课程表中所有课程都选修了一次，叫学生集合与课程集合的笛卡尔积。现实中的学生选课是笛卡尔积的子集。
比如学生表有n个学生，课程表有m门课，那么这两个表的笛卡尔积就是每个学生选每一门课
空值
Null 什么都没有 “ “空格 有效值是空 “”空串双引号里没有空格
select * from 学生表 where 家庭住址 is null 对
select * from 学生表 where 家庭住址 =null 错
select * from 学生表 where 家庭住址=’’ 错判断一个值是否为空，必须使用 is null判断 ,因为null不是一般意义的简单类型的数据，是当对象概念的，不能使用关系运算比较的
count不计空值 null不算进去 空格可以算进去 所以保险起见最常用 select count(*) from 学生表 有一行算一行
专门的关系运算：数据库的表操作
选择 where
- 通过对关系中的元组（行）进行筛选，从中选取满足特定条件的行数据
- SELECT * FROM 表名 WHERE 条件，其中 条件 就是选择条件
- 选择操作使得我们可以根据特定条件从数据库表中检索出所需的数据，从而实现数据的筛选和过滤
投影 select
- 对关系的属性（列）进行操作，它选择部分列并返回一个新的关系，包含了原始关系中选定的列
- SELECT 列1, 列2 FROM 表名，其中 列1、列2 是需要投影的列名
- 投影操作常用于从数据库表中选择出特定的列，而不是选择整行数据，从而实现对数据的精细控制
连接 join 或 where
- 同时操作两个或多个有联系的表，将它们的行按照某种关联条件进行匹配，然后返回满足连接条件的所有可能组合
- SELECT * FROM 表1 JOIN 表2 ON 表1.列 = 表2.列
比较运算 = ！ > >= < <= like between
between是重点
between a and b a<b 要筛选出位于 a 和 b 之间的值，包括 a 和 b 本身
between a and b b<a 即筛选出位于 b 和 a 之间的值,结果和a<b的情况相反
a, b: 这两个值是范围的边界。它们可以是任何数据类型，包括枚举型。例如，它们可以是数字、日期、字符串或枚举值。 枚举型: 如果 a 和 b 是枚举型的值，它们表示了一个范围内的枚举值
逻辑运算：与，或，非（数据库中）
与 a && b a and b (a,b同时成立，结果才成立)
或 a|| b a or b (a,b中至少一个成立，结果就成立)
非 ! a not a (求反)
字符串匹配 like
%代表任意多个字符（包括0-n个） _ 必须只能代表一个任意字符 如：张
_ 可代表张三，张六（两个字）
张% 可代表：张 张三 张三四(以要以张字开头即可)
[ ] 在其中只能选一个字符去匹配
[^字符]不在其中选一个字符
集合包含判断 in
- 单值情况： Where x =单值
- 多值成集合：Where x in (集合值)
注意和说明
- 以上四大类，如果仅从关系数据库专业角度看，只分两大类： 传统的集合运算，专业的关系运算
- 说明：关系运算> < = != 只能用于单值比较。 包含于多值时，要使用集合运算判断in
例子
查出所有男生团员的计算机网络基础 ，计算机基础，高等数学上下的成绩情况（学号，课程号，成绩）
select * from 成绩表 where 学号 in (select 学号 from 学生表 where 性别=’男’ and 团员否=1) and 课程号 in(select 课程号 from 课程表 where 名称 in(‘计算机网络基础’,’计算机基础’,’高等数学上’,’高等数学下’))
主要函数
字符串函数
- concat(s1,s2,…sn) 字符串拼接，将s1,s2,s3…sn拼成一个字符串
- left(串，n) 取串左n个字符
- right(串，n) 取串右n个字符
- len(串) 求串长
- lower(str) 将字符串str全部转为小写
- upper(str) 将字符串str全部转为大写
- lpad(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
- rpad(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
- Ltrim(串) 去掉串左边多余的空格 Rtrim(串) 去掉串的右边多余空格
- trim(str) 去掉紫都城头部和尾部的空格（中间的空格依然保留）
- substring(str ,strart,len) 返回字符串Str从start位置起的len长度的字符串（第一个位置的字符为1，不是0！）
select concat(‘Hello’,’ mroning’);
select lowert(‘Hello’);
select upper(‘Hello’);
select lpad (‘001’,6,’-‘) 输出为 —001
select rpad (‘001’,6,’-‘) 输出为 001—
select trim(‘ Good Morning ’); 输出Good Morning
select substring(‘Good morning’,1,4); 输出Good
- 给所有同学的学号前面补0变成8位学号
update S set sno = lpad(sno,8,’0’ );
数值函数
ceil(x) 向上取整（有小数 的话整数部分直接进一位）
floop(x) 向下取整（去掉小数部分）
mod(x,y) 返回x/y的模
rand() 返回0~1内的随机数
round(x,y) 求参数x四舍五入的值，保留y位小数
生成一个6位数的随机验证码
select lapd (round(rand()*10000000,0),6,’0’ ) ;
转化数据类型函数 convert( ),cast()
convert(目标数据类型, 要转换的表达式或值, 是可选参数，用于指定转换的样式或格式);
它经常用于将日期时间数据类型转换为不同的日期格式，或者将字符串转换为数字，反之亦然
- select convert (varchar,getdate(),101)
CONVERT函数将GETDATE()返回的日期时间值转换为VARCHAR数据类型，然后使用格式代码101将其转换为 MM/DD/YYYY 格式的字符串- select convert( int,’12345’ )
将字符串12345转换成整数- select convert (date , getdate())
把当前系统的时间转化成日期，只留下年月日不包时间部分- onvert(nchar(10),生日,21) 其中21是针对日期的格式参数
cast(表达式 as 新类型[长度])
select CAST(身高 as numeric(4,1)) from 学生表
select 姓名+’同学的身高是:’+convert(nchar(4),身高) from 学生表select 姓名+’同学的身高是:’+cast(身高 as NCHAR(4)) from 学生表
日期相关函数
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期和时间
year(date) 获取制定date的年份 同理有month(date) day(date)
date_add(date,interbal expr type) 返回一个日期/时间值加上一个时间间expr后的时间值
datediff(date1,date2) 返回起始日期date1和结束时间date2之间的天数
Dateadd(日期时间的部分量，偏差值, 日期) 返回指定日期的指定分量的偏移后的日期结果
例： dateadd(year,95,getdate()) 求今天后的95天后是哪一天？其中的日期部分量：year, month,quarter，day,week,hour,minute,second,millisecond
中美日期区别： Dateadd(日期时间的部分量，偏差值, 日期) 返回指定日期的指定分量的偏移后的日期结果
例： dateadd(year,95,getdate()) 求今天后的95天后是哪一天？
其中的日期部分量：year, month,quarter，day,week,hour,minute,second,millisecond美式日期格式通常采用月/日/年的格式，如 12/25/21。
中国日期格式是年-月-日，精确到秒或者毫秒，如 2012-09-26 20:04:07.345
getdate() 函数
select getdate();返回当前系统时间
单独取年月日函数
SELECT YEAR(‘2024-03-17’); 2024
SELECT MONTH(‘2024-03-18’); 03
SELECT DAY(‘2024-03-17’); 17
日期减法函数 datediff()
select datediff(second,’2024-3-15’,’2024-3-15 10:33:20’)
后面的减去见面的，返回结果为秒，算出这一天已经过去了38000秒select datediff( second,convert (date ,getdate()) ,getdate());
同样计算这一天已经过去的秒数查询所有员工的入职天数，并根据入职天数倒宁排序
select name, datediff(curdate(),ruzhiriqi ) as ‘ entrydays ’ from 员工表 order by entrydays desc;求李大方比较李四方大多少岁
select
( select 生日 from 学生表 where 姓名=’李大方’)-
( select 生日 from 学生表 where 姓名=’李九二’)select convert(numeric(5,1), ( select 生日 from 学生表 where 姓名=’李大方’)- ( select 生日 from 学生表 where 姓名=’李九二’) )/365
方法3:
declare @sr1 smalldatetime,@sr2 smalldatetime
select @sr1=生日 from 学生表 where 姓名=’李大方’
select @sr2=生日 from 学生表 where 姓名=’李九二’
print datediff(day,@sr1,@sr2)
日期加法函数 dateadd()
- select dateadd(day , 105 , getdate())
后面的（今天日期）往后推105天后的日子 - 例子：把最近七天要过生日的人列出来
- select * from Stu where datediff( day , getdate(),出生日期) between 0 and 6
聚合函数
将一列数据作为一个整体，进行纵向计算所有的null 不参与计算
count不计空值 null不算进去 空格可以算进去 所以保险起见最常用 select count(*) from 学生表 有一行算一行
常见的聚合函数：
count 统计数量
max min ave sum 求最大值，最小值，平均值，求和
聚合函数应用
聚合函数 自定义函数 期末考试有可编程性 函数 系统函数 点进去可以找到那个函数的说明
可编程性 函数 标量值函数：简单的函数
可编程性 函数 表值函数：比如说去买多种东西，用一个兜子可以一次都拿走，这个表值函数就相当于这个兜子。可以返回一堆毫不相干的数据
count( )统计表中元组个数 （计数查询结果的行数，与列无关）
注意：如果给列名时，null不计算
select COUNT(性别) from 学生表
select COUNT(姓名) from 学生表
select COUNT(*) from 学生表 //常用
可见，空值当空气，啥也不参与。空值不是空格！
sum( ) avg( ) 纵向对数值型列求和，平均
一定要注意定义的时候的数据类型 字符串和数字型不可以一起相加或者算平均
统计聚合函数不可以有和统计内容无关的列，不然查不出来
select SUM(成绩) from 成绩表 where 课程号=’101’
select SUM(学号) from 成绩表 where 课程号=’101’ 错应为学号和课程号无关
select AVG(成绩) from 成绩表 where 课程号=’102’
select AVG(成绩) from 成绩表 where 课程号=’109’
没有109课程,结果为null,实际上是总和/数据个数，0/0 严格说要溢出，sqlserver做了溢出容错处理的
如果查出来是NULL 在数据库中可能有两个可能：可能真的是NULL,也可能数据溢出了
可能没有这个表，这个表的和是NULL 这个表是数量是0，null除以0也是null
一次性分组统计出两门课的平均成绩
select 课程号, AVG(成绩) as 科平均 from 成绩表
where 课程号 in(‘102’,’105’)
group by 课程号等效写法：
select AVG(成绩) as 科平均 from 成绩表
where 课程号 in(‘102’ )
select sum (成绩)/COUNT(*) as 科平均 from 成绩表 where 课程号 in(‘102’ )
有结构：成绩表（学号，语文，数学，英语，总分，平均分）
根据三科成绩，计算出所有学生的总分，和平均分,不能使用sum,average,因为这个玩意是纵向操作。本题是要求横向计算
方法1:结果不填写表里
Select 学号,语文,数学,英语, 语文+数学+英语 as 总分,总分/3 as 平均分 from 成绩表
方法2:结果填表
Update 成绩表 set 总分 =语文+数学+英语,平均分 = 总分/3
例子：统计学生表人数
select count(*) from S;
select count(sno) from S;
统计学生的平均，最大，年龄
select avg(sage) from S;
select max(sage) from S;
统计女同学的年龄之和
select sum(age) from S where ssex =‘女’ ;
max( ) min( ) 常用于对数值，日期列求大值，小值
求最高分和最低分的分数差；求年龄最小值
select MAX(成绩)-MIN(成绩) from 成绩表 where 课程号=’101’
select MAX(生日) from 学生表 求年龄最小值(生日最大值)
一次性求出101课程的最高，最低，平均，总分，选课人数
elect MAX(成绩) as 最高分,MIN(成绩) 最低分,
avg(成绩) as 平均分 ,sum(成绩) as 科总分,
count(*) as 选课人数 from 成绩表 where 课程号=’101’
说明：类似以上返回一堆数据，可以事先定义表值函数。
补例：
一次性求出D001课程的的课程号，最高，最低，平均，总分，选课人数
select 课程号, MAX(成绩) as 最高,MIN(成绩) 最低,
COUNT(*) as 选课人数,SUM(成绩) as 总分,
AVG(成绩) as 单科平均 from 成绩表 where 课程号=’D001’
group by 课程号
流程控制函数
if(value ,t,f) 如果value为true 返回t，否则返回f
ifnull(value1,value2) 如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1] …. else [default] end 如果val1为true返回res1,….否则返回default默认值
case[表达式] when [val1] then [res1]…else[default] end 如果表达式值为val1,返回res1,….否则返回default默认值
- 需求:查询emp表的员工姓名和工作地址(北京/上海—->一线城市，其他—->二线城市)
select name. (case workaddress when’北京’then’一线城市’when’上海’then’一线城市’else’二线城市’end )as’工作地址 rom 员工表; - 查询学生的学号名字和他们的成绩等级
select id, name ,
(case when math >= 85 then’优秀’when math >=60 then’及格’else’不及格’end )’数学’
(case when english >= 85 then’优秀’when english >=60 then’及格’else’不及格’end )’英语’
‘(case when chinese >= 85 then’优秀’when chinese >=60 then’及格’else’不及格’end )’语文’
from 成绩表;
随机函数 newid()
堆叠函数 stuff()
Stuff() 堆叠函数，把纵向的数据在横向上堆叠起来
Select (kechengID ) from 课程表 where kechengID in (select kechengID from 成绩表 where 学号=’32’)
查询结果值拼串：
select stuff((select ‘|’+rtrim(课程名称) from 课程表
where 课程号 in(select 课程号 from 成绩表 where 学号=’310001’) for xml path(‘’)),1,1,’’)
结果为: 大学英语|大学物理|C语言单表查询
基本查询
编写顺序： select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 habing 分组后条件列表 group by 排序字段列表 limit 分页参数
执行顺序：from where group by select order by limit having
查询多个列
select 列1，列2，列3…. from 表名select *from 表名
设置别名（as可以省略）
select 列1 as 别名1，列2 as 别名2 … from 表名
eg 查询所有员工的工作地址，起别名（as可以省略）
select workaddress as’工作地址’ form 表名 ;
结果是一列去除重复记录
select distinci 字段列表 from 表名select distinci workaddress form 表名
条件查询 where
select 字段列表 from 表名 where 条件 。关系和逻辑表达式作为条件
查询年龄等于88的员工
select * from 员工表 where age = 88;
查询年龄不等于88的员工
select * from 员工表 where age != 88;
select * from 员工表 where age <> 88;
查询电话号为空的学生
select *from S where sno is null
查询电话号不空的学生
select *from S where sno is no null
查询年龄在15岁(包含)到20岁(包含)之间的员工信息
select * from 员工表 whene age >= 15 && age <= 20;
select * from 员工表 whene age >= 15 and age <= 20；
select * from 员工表 whene age between 15 and 20;
查询性别为女且年龄小于25岁的员工信息
select * from 员工表 where gender = ‘女 and age < 25;
查询年龄等于18或20 或40的员工信息
select * from 员工表 where age = 18 or age = 20 or age =40;
select * from emp where age in(118,20,40) ;
查询姓名为两个字的员工信息
select *from 员工表 where name like ‘_ _’
使用下划线，一个下划线代表一个字
查询身份证号最后一位是X的员工信息
select *from 员工表 where name like ‘%X’
select *from 员工表 where name like ‘_(17个下划线)X’
查出1.7以上男生和1.6以上女生，且都是团员
Select * from 学生表
Where(性别=’男’ and 身高 >1.7 Or 性别=’女’ and 身高 >1.6 And 团员=1）
查询姓杨和张的人
- select * from 学生表where LEFT(姓名,1) in (‘杨’,’张’)
查询无人选课的课程号
思路：先查出无人选课的课程的名称和课程号 Select distinct 课程号 from 成绩表
Select * from 课程表 where 成绩 not in （select distinct 成绩 from 学生表）然后
Select 课程号，课程名称 from 课程表 whre 课程号 not in (Select distinct 课程号 from 成绩表)
查出选修了大学英语或大学物理的学生的姓名
select 学号 form 成绩表 where 课程号 in (select 课程号 from 课程表 where 课程名称 in(‘大学英语’ ,‘ 大学物理’))
查询前面部分元组行（常用在排序结果后）
从排序结果中取出前几个或前百分之的元组行：
取前n个： top n 取前百分之n ： n percent
要并列在上述后面紧跟with ties
注意百分号在右边，而且不能是百分号，是一个单词
取身高最高的前五名 （身高高的数字大在上面，是降序排序）
select top 5 sno sname ssex shight from Stu order by shight desc如果第五名和第六名一样高，按照性别女生在前：
select top 5 sno sname ssex shight from Stu order by shight desc，ssex desc如果要按百分比取，如果取百分之前20，把top 5 改成 20 percent
如果取5个人但是第5名和第6名并列，要把第5第6都取出来
select top 5 with ties sno sname ssex shight from Stu order by shight desc
随机挑选几个记录出来
随机挑选函数 newid()
比如用于软件给用户展示广告，每个用于登录后随机选择4条展示给用户
把学生表的信息随机排序罗列出来
select NEWID(), *from S order by newid();
这行代码运行多次查找出来的结果多次不同
随机选五个出来
select top 5 sno sname ssex shight from Stu order by newid()
子查询的结果值可以用于主查询的条件中的关系比较
如果查询比A高的同学，那么必须只有一个A作为标准，不然会报错
Select … form Stu where gender=’男’ and 身高 >(select min(身高) from Stu where 姓名 =’A’ or 姓名=’B’)
Select … form Stu where gender=’男’ and 身高>and(select身高from Stu where 姓名 =’A’ or 姓名=’B’)上面两个结果一样，下面两个结果一样
Select … form Stu where gender=’男’ and 身高 >(select max(身高) from Stu where 姓名 =’A’ or 姓名=’B’)
Select … form Stu where gender=’男’ and 身高>some(select身高from Stu where 姓名 =’A’ or 姓名=’B’)
eg 查出比李大方和李四方任意一个身高还要高的所有男生（就是找比他们俩里最高的还要高的人
select 学号,姓名,身高,性别 from 学生表 where 性别=’男’and 身高>some(select 身高 from 学生表 where 姓名=’李大方’or 姓名=’李四方’ )
说明：子查询的结果如果是一个集合，不是单一值，不能使用> < =这类关系运算. X=(集合)是不对的，但 x in(集合)是对的
两个查询的结果值（同类型的，可直接用于表达式计算）
实例：查出A比B大多少岁
Declare @name1 nchar(5),@name2 nchar(5)
Declare @age 1 int ,@age2 int
Set @name1= ‘一一’
Set @name1= ’二二’
Select @age1 = year(geydaye())-yeary(出生日期) from 学生表 where 姓名 = name1
Select @age2 = year(geydaye())-yeary(出生日期) from 学生表 where 姓名 = name2
If(@age1 >@age2)
begin
Print @name1 +‘比’+@name2+’大’ +convert(char(2),(@age1-@age2)岁
And 这两个相当于C语言中的花括号，如果是多句必须加，单句可以不加
Else
Print @name2 +‘比’+@name1+’大’ +convert(char(2),(@age2-@age1)岁
实例：查出李大方比李四方大多少岁？谁大谁在前。
declare @name1 nvarchar(5),@name2 nvarchar(5)
declare @age1 int ,@age2 int
set @name1=’李大方’
set @name2=’李四方’
select @age1=YEAR(getdate())-YEAR(出生日期) from 学生表 where 姓名=@name1
select @age2=YEAR(getdate())-YEAR(出生日期) from 学生表 where 姓名=@name2
if(@age1>@age2)
begin
print @name1+’比‘+@name2+’大’+convert(char(2), @age1-@age2)+’岁’
end
else
print @name2+’比‘+@name1+’大’+convert(char(2), @age2-@age1)+’岁’
例：上例如果在开发APP要使用这个结果咋办？使用一个变量保存结果，把变量传给其它语言
declare @sg float
select @sg=(select 身高 from 学生表 where 姓名=’李大方’)-
(select 身高 from 学生表 where 姓名=’李四方’)
print @sg
去掉表中重复值+数据查询例子
查看学生表有多少种不同的姓氏
select distinct LEFT(姓名,1) from 学生表
找学生表中没有选过课的人的学号
先找到学生表中选过一门课的人的学号，再找到除了他们的人的学号
Select 学号，姓名，性别 form 学生表 where 学号 not in （select distinct 学号 from 成绩表）
字符串匹配查询
查出学生表中所有姓杨，张，李的人,有如下办法
- Select * from 学生表 where left(姓名,1)=’杨’ or left(姓名,1)=’张’ or left(姓名,1)=’李’
- Select * from 学生表 where left(姓名,1) in(’杨’,’张’,’李’)
- Select * from 学生表 where left(姓名,1) like ‘[杨张李]’
- Select * from 学生表 where 姓名 like ‘[杨张李]%’
- Select * from 学生表 where substring(姓名,1,1) like ‘[杨张李]’
分组查询（一般结合统计聚合函数一起用）
Select 课程号 ,avg（成绩） as 科平均 from 成绩表 group by 课程号
下面的就不对了，因为和学号无关
Select 学号，课程号 ,avg（成绩） as 科平均 from 成绩表 group by 课程号
select 字段列 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
Select 课程号 ,avg（成绩） as 科平均 from 成绩表 group by 课程号 having AVG(成绩)<60
一组就是一门课，每一组进行一次聚合函数运算，对聚合运算统计结果运算不能用where要用having
执行顺序: where >聚合函数>having 。
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
where与having区别
执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组;
而having是分组之后对结果进行过滤。
判断条件不同: where朱能对聚合函数进行判断，而having可以。
一次性分组统计出两门课的平均成绩
select 课程号, AVG(成绩) as 科平均 from 成绩表
where 课程号 in(‘102’,’105’)
group by 课程号等效写法：
select AVG(成绩) as 科平均 from 成绩表
where 课程号 in(‘102’ )
select sum (成绩)/COUNT(*) as 科平均 from 成绩表 where 课程号 in(‘102’ )
根据性别分组，统计男同学好女同学的数量
select ssex, count(*) from S group by ssex;
根据性别分组，统计男同学好女同学的平均年龄
select ssex, avg(age) from S group by ssex;
查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress，count(*) from emp where age < 45 group by workaddress having count( *)>=3,
select workaddress，count(*) address_count from emp where age < 45 group by workaddress having address_count >=3,使用别名
分页查询
select 列名 from 表名 limit 起始索引,查询记录数
起始索引从o开始,起始索引=(查询页码-1)*每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现,MySQL中是LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10
查询第一页员工数据，每页展示十条记录
select * from 员工表 limit 0,10;
select * from 员工表 limit0,10;
查询第一页员工数据，每页展示十条记录
select * from 员工表 limit 10,10;
例子
查询年龄为20,21,22,23岁的女学生信息。
select * from S where ssex=‘女’ and age in (20,2,22,23);
查询性别为男，并且年龄在20-40岁(含)以内的姓名为三个字的学生。
select * from S where ssex=‘男’ and age between 20 and 40 name like ‘___’;
select * from S where ssex=‘男’ and （age between 20 and 40) name like ‘___’;
统计员工表中,年龄小于60岁的，男性员工和女性员工的人数。
select sex,count(*) from 员工表 where age < 60 group by sex;
查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select sex,count(*) from 员工表 where age < 60 group by sex;
查询性别为男，且年龄在20-40岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from 员工表 where sex = ‘男’ and age between 20 and 40 order by age ase ，entrydate asc limit 5 ;
多表查询
从多个表中查询数据（笛卡尔积，在查询时需要消除无效的笛卡尔积）
如后面加 where 学生表.sid = 成绩表.sid
select * from 学生表 , 成绩表 where 学生表.sid = 成绩表.sid
多表关系
- 一对多（部分和员工的关系，一个部门可以有多个员工，一个员工只能对应一部门；实现：在多的一方建立外键，指向一的一方的主键）；
- 多对一；
- 多对多（学生和课程的关系，一个学生可以选择多门课程，一门课程也可以被多个学生选择实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键）；
- 一对一（用户与用户详情之间的关系，比如学生和学生的学生号。用于单表拆分，将一张表的基础字段放在一张表中，其他的信息放在另一张表中，提升操作效率。实现：在任意一方加入外键，关联另一方的主键，并设置外键为唯一的unique）
连接查询
查询结果是脱离原表的，一旦从表中查出信息，信息就和原表没关系了，表也会被关闭
如果查询两个表，两个表都有学号，那么会显示学号不明确，这时候要对学号进一步确定，比如表.学号来进一步确定学号
内连接
相当于查询A、B交集部分数据
隐式内连接
显式内连接
查询每个员工的姓名，及关联的部门的名称(隐式内连接实现)
查询每个员工的姓名，及关联的部门的名称(显式内连接实现)
或者 标准化代码（老）：（内连接）
select a.sid, a.性别，a.性别，b.成绩 from 学生表 as a ，成绩表 as b where a.sid = b.sid
（是一种双重循环，两个表两个指针，两个指针指向的数据一样则选中数据）
select a.sno ,a.sname,a.ssex ,b.cno,b.grade from S as a ,G as b where a.sno = b.sno;group by 是对已经查询的数据进行分组，分组之后再按照它排序
select 课程号 ,max(成绩) as 最高，MIN（成绩） 最低，count(*) as 选课人数 ,sum(成绩） as 总分，ave(成绩） as 平均 from 成绩表 where 课程号 in（’D001’,’D002’,’D003’)
group by 课程号 order by 课程号 dese在这基础上，把选课人数三以上的排序
select 课程号 ,max(成绩) as 最高，MIN（成绩） 最低，count() as 选课人数 ,sum(成绩） as 总分，ave(成绩） as 蛋壳平均 from 成绩表 where 课程号 in（’D001’,’D002’,’D003’)
group by 课程号 having count() >3 order by 课程号 desecount(*)这里注意不能用别名 ，注意order by 一定是放在最后的
或者 标准化代码（新）：（内连接）
select a.sno ,a.sname,a.ssex ,b.cno,b.grade from S as a inner join G as b on a.sno = b.sno;学会混用 （内连接）
select a.* ,b.grade from S as a inner join G as b on a.sno = b.sno;–换了一下位置，A表和B表位置调换，查询结果一样，没有影响
select a.* ,b.grade from G as b inner join S as a on a.sno = b.sno;内连接中没有对应上的不会出现在查询里面，比如一个同学没有选课，成绩表没有它的学号，那么不会被查询出来
多次连接的时候，后面的第二次连接连接的是前面连接的结果，而不是前面的表
多个表连接：四个表五个表连接也是同理。如果没有and后面的限制课程和性别那么所有数据都会查出来
select 成绩表.学号，课程表.课程号，成绩，姓名
from 成绩表，学生表，课程表
where 成绩表.学号=学生表.学号 and 成绩表.课程号 = 课程表.课程号
and 性别 =’ 男’ and 课程名称 =’大学英语’
错的 下面的没写对
select S.sno ,C.cno ,grade,sname,cname
from S,C,G where S.sno=C.cno and C.tno= G.sno
视图看代码
create view xscjkc as …照片拍的那段代码
执行上面的之后，去视图里面找到xscjkc 右键点设计
select * from xscjkc where 性别 =’男’;
写好的视图可以看作前面的查询结果保存成的结果，作用于后续的查询，后面使用会方便很多
from 后面可以跟表也可以跟视图
外连接
- 左外连接:查询左表所有数据，以及右表中被左表提及的数据两张表交集部分数据
如查出学生表和成绩表的姓名，学号，课程号，成绩数据，如果学生表中有学生没有选课，也要把它查出来，他的课程号和成绩会显示Null（没有选的显示null)
select 成绩表.学号，课程表.课程号，成绩，姓名
from 学生表 left join 成绩表 on 成绩表.学号=学生表.学号 where课程号 is nullselect 成绩表.学号，课程表.课程号，成绩，姓名
from 学生表 left join 成绩表 on 成绩表.学号=学生表.学号 where课程号 is not null实际开发中null不好操作，传给前端的不是对象型是空串
select 成绩表.学号，姓名，性别，
case when 课程号 is null then’无选课’
else 课程号
end as 课程号，
成绩 from
学生表 left join 成绩表 on 成绩表.学号 =学生表.学号select C.tno,C.sname,S.ssex,
case when tno is null then ‘无选课’
else tno
end as cno,
grade from
S left join G on C.tno =S.sno
- 右外连接:查询右表所有数据，以及左表中被右表提及的数据两张表交集部分数据
自连接
自连接:一个表打开两次，当前表与自身的连接查询，自连接必须使用表别名
在学生表中找到和杨秋性别一样的其他同学信息
例子但是没成功
select a.sname ,a.ssex from S as a inner join S as b
on a.ssex =b.ssex
where a.sname = ‘ 杨秋’ and b.sname!= ‘杨秋’
group by 学号 having avg(成绩) 按学号分组，每一组也就是每个学生一个平均成绩
现实场景：查询哪些部门内部平均工资高于公司所有人的平均工资 select 部门,avg(工资 ）from 员工表 group by 部门 having AVG(工资 ）>(select avg(工资) fom 员工表）
全连接
a full join b
两个表的所有数据都要列出来，凡是对方表中无 对应的记录，则都以Null值代表
inner join 是要求a,b两表记录条件相交
full join 要求a,b两表记录相并如有A,B,C三个表，连接查询时，AB之间以B数据为准，BC连接时以C为数据为准。
…from a right join b on a.key1=b.key1
right join c on b.key2=c.key2
注意细节
- 如果给表起了别名，那么不能再用表本来的名字进行操作
- 存在性判断的时候，两个表是同时打开的（主表和子表同时打开）
- 如果建表的时候设置了不可以为空Null，那么给表添加数据的时候就不可以输入NULL。可以输入空字符串’’两个单引号
游标 cursor
存储过程（函数）
定义
变量 （自定义的变量）必须要加@ 声明变量语法 declare @变量名 类型[(宽度),类型…..]
打印 print @变量名
分支结构
格式：只能用在过程或函数中，不能嵌套到其它sql命令中内。但sql内部要实现条件判断可以用case when 子句。
If(条件)
begin
成立时语句序列
end
[else
begin
条件不成立时的语句序列
end】case when 用法（直接多分支，类似于switch功能，但它不是独立的命令语句 ）
格式1：
Case 表达式
When 常量1 then 结果1
When 常量2 then 结果2
…
When 常量n then 结果n
Else 结果n+1
End
与switch不大一样的是：每个then 后面的结果不需要加break;本系统只执行一个条件，不会从条件入口进来后，还要往下执行
格式2：用法比格式1要灵活得多
Case
When 条件1 then 结果1
When 条件2 then 结果2
…..
When 条件n then 结果n
Else 结果n+1
End
循环
While(条件)
Begin
循环体语句序列
End
在这里循环使用较少，因为数据库的查询 select本身就是从首行自动循环到尾行。
补充三种流程结构
While(条件)
Begin
循环体语句序列
End
If(条件)
Begin
成立时语句序列
End
Else
Begin
不成立时语句序列
End
自定义函数:标量函数，表值函数
(1)标量函数 回的是一个标准量，如一般数据类型int, char. 这种函数相对简单，通过函数体计算，返回调用处一个标量，与其它语言类似，有参数时通用函数名后面参数表带去，也可以无参函数（但空括号不能省）
Create function 函数名(形式参数表) returns 返回值的数据类型 Begin
函数体语句
Retrun
Endcreate function f1(@a int,@b int) returns int
begin
declare @t int
set @t=@a+@b
return @t
end
print dbo.f1(5,1)
（2）表值函数: 内联表值函数
一般函数返回的一个标量值，表值函数返回的是一个表(视图)，由此得名
这种表是一个关系，在结构表头与一般的物理表相没区别，可以随意存放多行多列数据—故使用表值函数可以返回大量数据
表值函数内部函数体的核心任务是，把所有需要的返回给调用处的数据计算出来，存放到内存表中。最后一次性返回。
格式：create function 函数名(形式参数表) returns table
As
Begin
函数体
End
表的自增列的设置
原因：如成绩表要删除一个学生的一门课情况，要两个关键字列组合。如果要删除一个图书销售表中的记录，要根据书店ID，书ID，销售日期，销售员ID，太麻烦了。使用记录行的自增ID列可方便解决。
给表的每个记录设置自增的数字列，它是唯一值的（表中已经有数据是无法设置的，事先空表时设置）
存储过程应用
create procedure 过程名 as 过程体语句(可是若干条命令) execute 过程名 来执行过程
Xx(arg1,arg2…); Int x=xx(arg1,arg2);
类似老的C：void 函数名( ) { 函数体}
过程本身不通过过程名带回来计算结果，所以类似于void（并不是通过形参的改变返回数据给实参）
无参传递过程，主调程序不需要传递实参给过程
2
3
4
5
6
7
create procedure p1 as
declare @xm nchar(6)
set @xm='杨春'
select 学号,姓名,生日 from 学生表 where 姓名=@xm
完成过程的建立，然后在其它主调程序中调用：exec 过程名
execute p1
有参过程：实用性强，通用性好
1 | create procedure p2 @xm nchar(6),@kch nchar(3) |
有参数，且把计算结果返回给调用处(不是select查询结果视图返回)
1 | 输入一个学号，课程号 ,调用过程查询该人该科成绩值，把计算得到成绩值结果返回给调用处 |
