五十道SQL题链接
https://blog.csdn.net/qq_41835813/article/details/108329971
本人感觉这篇博客入门够用了
创建数据库
create DataBase db_HXX
on
(
name=HXX_Data,
filename='D:\Data\HXX_Data.mdf',size=5,
maxsize=15,
filegrowth=2
)
log on
(
name=HXX_LOG,
filename='D;\Data\HXX_LOG.ldf',size=3,
maxsize=10,
fileGrowth=1
)
unlimited 无设置
创建表
create table Student
(
sno char(10) primary key,
sfzh char(18) unique,
cname varchar(16) not null,
sex char(2) check(sex='男' or sex='女'),
dept varchar(30) default'软件学院',
birthday datetime,
score float check(score between 0 and 100),
polity char(8) check(polity='党员' or polity='团员' or polity='群众')
)
复合主键: consteaint pk_snocname primary key(sno,cname);
外键:consteaint pk_sc foreign key(sno) references db_student.dbo.tb_student(sno) 设置外键时必须有参考的主键
select查询
查询条件 | 谓词 |
---|---|
比较(比较运算符) | >,<,=,>=,<=,!=,<>不等于,!>,!< |
确定范围 | between a and b ,not between a and b |
确定集合 | in, not in 确定集合 |
字符匹配 | like, not like 字符匹配 |
空值 | is null , is not null |
多重条件(逻辑谓词) | and , or |
字符串中可以含有的通配符
通配符 | 功能 | 实例 |
---|---|---|
% | 代表0或多个字符 | ‘强%’强后可接任意字符串 |
_ (下划线) | 代表一个字符 | ‘强_ _’后面只有俩个字符 |
[ ] | 表示在某一范围的字符 | [0~9]在0~9之间 |
[^ ] | 表示不再某一范围的字符 | [^0~9] 不再0~9之间 |
常用的库函数及功能
函数名称 | 功能 |
---|---|
AVG | 按列计算平均值 |
SUM | 按列求和 |
MAX | 求一列中的最大值 |
MIN | 求一列中的最小值 |
COUNT | 按列统计个数 |
distinct 去重
1.起别名
- sno as 学号
- 学号 = sno
- sno 学号
2.组合 select sno+'('+sn+')' as 学号姓名
3.求字符串
- where left(sn,8)='20170114' 返回从字符串左边开始,指定个数字符串
- substring(sn,6,2) =‘14’ 截取字符串从6开始,指定2个字符的字符串
4.求年龄 year(getdate()) - year(birthday)
5.select top n(n个) * from 表 或者 top percent n (百分之n)
case when then else end
- 将离散的数值映射到其各自代表的含义值,或者将连续的数值映射到离散的区间带
- 将细颗粒单位转换为粗颗粒单位的聚合运算
可以和sum混合使用,案例请查看王章开头链接 第19题
limit:用于限定查询返回的记录行数
limit 5 -》前5
limit 5,5 -》6~10
6.like where name like '张%' 找姓张,名字字数不限 where name like '张_' 限定两个字
7.In where banji in('14','15') 找14 15班的人
use db_student17
select * from tb_student where SUBSTRING(sno,7,2) in('14','15')
Exists 作用与in关键字一样,所不同的是exists关键字的嵌套查询返回的不是集合,而是满足条件的逻辑值
select * from student where exists(select 1 from .....)
ANY ALL
6种比较运算符与ANY ALL 搭配
- >ANY :大于子查询结果中的某个值
- >ALL :大于子查询结果中的所有值
输出排序:(SELECT COUNT(1) from (SELECT SId,AVG(score) as 平均成绩 from sc GROUP BY SId) as a where a.平均成绩>b.平均成绩)+1
select SId,平均成绩,
(SELECT COUNT(1) from
(SELECT SId,AVG(score) as 平均成绩 from sc GROUP BY SId)
as a where a.平均成绩>b.平均成绩)+1 as 排名 from
(select SId,AVG(score) as 平均成绩 from sc GROUP BY SId) as b
GROUP BY SId
ORDER BY 平均成绩 DESC
8. is null
9.order by 排序 在where后 ,asc升序(默认) desc降序
select sno,sn,sex,SUBSTRING(sno,7,2) as 班级,year(GETDATE())-year(birthday) as 年龄 from tb_student where SUBSTRING(sno,7,2) in('14') order by 年龄 asc
10. sum 和 avg max min select sum(score),avg(score) from 表 where 条件
求俩个日期的时间间隔
SELECT id,DATEDIFF(time,'2020-1-1') from datedemo
SELECT id,TIMESTAMPDIFF(DAY,'2020-1-1',time) from datedemo
可选类型: year month quarter week day hour minute
11.count求总数
use db_student17
select count(*)from tb_student
12.group by 分组查询 求sno学了多少门课
use db_student17
select sno,count(*)from tb_score
group by sno
use db_Book
select CBS,COUNT(CBS)as 存书数量 from tb_BookInfo
group by CBS having COUNT(CBS)>100
求各个班有多少人,降序排序
use db_student17
select SUBSTRING(sno,5,4) as 班级 ,count(sno) as 人数 from tb_student
group by SUBSTRING(sno,5,4)
order by 人数 desc
13.with rollup 分配统计 输出一个总计
having 和where意思一样,但是having是对统计结果的筛选
14.多表查询(内连接)
select sno,sc.cno,cn,score
from tb_course c join tb_score sc
on c.cno = sc.cno
select sno,sc.cno,cn,score
from tb_course c,tb_score sc
where c.cno = sc.cno
select s.sno,sn,cn,score
from tb_student s,tb_score sc,tb_course c
where s.sno = sc.sno and sc.cno = c.cno
15.多表查询(外连接)
left outer join *左外部连接
right outer join *右外部链接
full outer join *完全外部链接
纵向表合并
把多张表结构相同的表按照垂直方向将他们进行合并,实际是记录的堆叠。
- UNION ALL :首尾相连
- UNION:排重+排序
SELECT CId,Cname,TId from course UNION all SELECT CId,Cname,TId from course1
将查询到的结果生成新表
CREATE TABLE course2 SELECT CId,Cname,TId from course UNION all SELECT CId,Cname,TId from course1
16.子查询(查自己 可以嵌套)
- =
- in 可以代替any 相当于任意一个
- ALL 全部
- >
- <
= 当查询结果只有一个时 可以使用比较运算符
use db_student17
select sno,dept
from tb_student
where dept=(select dept from tb_student where sn=’强’)
IN 当查询结果返回为一个集合时
例:查询所有年龄大于20岁的同学
use db_student17
select sn from tb_student where YEAR(birthday) in
(select YEAR(birthday) from tb_student where YEAR(birthday)>20)
ALL 当查询全部时
例:查询1比14班所有学生年龄都大的同学
use db_student17
select sn from tb_student where YEAR(birthday) > all
(select YEAR(birthday) from tb_student where SUBSTRING(sno,5,4)=0114)
数据表中数据的操纵
insert into 表 列 value 值 列值对应,()()俩条记录
insert into 表 列 select 插入结果集
INSERT INTO sc(SId,CId,score)
SELECT DISTINCT SId,03,(SELECT AVG(score) from sc WHERE SId = 02) as score
from sc where SId not in (SELECT SId from sc where CId = 03)
update 表 set 字段=值 where 条件 更新
基于一张表修改另一张表
update 表1 join 表2 on 表1.字段 = 表2.字段
set 表1.字段2 = 表2.字段2
修改表的数据类型 Alter table 表名 modify column 字段名 类型
修改表名和字段名
alter table 旧表名 rename to 新表名
alter table 表名 change 旧字段名 新字段名 类型
delete from 表 where 条件
往表新增字段 Alter table 表名 add column 新增字段 类型 。。
删除表字段 Alter table 表名 drop 字段名
清空表数据 delete from 表名
Truncate table 表名 可清除自增变量的编号 从1开始
无法删除,修改权限 set SQL_SAFE_UPDATES = 0
drop 删表
drop table 表名
truncate table 不记入日志记录
视图
创建视图:
create view 视图名(字段可有可无)
as
select(查询语句)
修改视图:
alter view 视图名(字段可有可无)
as
select(查询语句)
删除视图:
drop view 视图名
查询视图:
和查询一样
更新视图:
添加(insert into)
insert into 视图名(字段)
value(字段值)
修改(update set)
update 视图名
set 字段=值
where 条件
删除(delete)
delete from 视图名
where 条件
索引
索引类型:
- 普通索引
- 唯一索引
- 主键索引
普通索引
没有任何限制的索引,它对表中的变量的值不做任何限制,不管变量的值是否重复或者缺失都无关紧要,是使用最频繁的一种
索引的创建可以通过俩种途径
普通索引:
- 建表时设置索引:index 索引名称(变量名称)
- 对已有表添加索引,通过创建法或修改法:create index 索引名 on 表名(变量名)
- Alter Table 表名 add index 索引名 on (变量名)
唯一索引:
- 建表时设置索引:UNIQUE索引名称(变量名称,变量名称)
- create unique index 索引名称 on 表名(变量名)
- Alter table 表名 add unique 索引名 on (变量名)
主键索引:
对变量的要求比较严格:不重复,不为null
- primary key (变量名称,变量名称)
- Alter table 表名 add primary key 索引名称(变量)
何时创建索引
- 在where关键字后面的字段创建索引,加快判断速度
- 在order by关键字后面创建,加快排序
- 表连接on后面,加快连接速度
- 包含大量null不适合创建索引,因为所有不可以包含null值
- 大量相同取值的字段不适创建索引
索引无效情况
- where关键字后面条件使用in or != < > 均会导致索引失效,解决方法:将!=或< > 替换为 >and< 将表示is not null 替换为 >=CHR(0)
- 筛选或排序过程中,如果对索引链使用函数,则索引不能正常使用
- 筛选过程中字符型数字改写成数值型数字where id = '123'
- 使用like关键字做模糊匹配时,通配符%或_ 不可以写在最前面,如%月城
- 对于多列的组合索引,遵循左原则,对于字段A,B,C 设置索引index(A,B,C)则A>100 ,A=1and B>10 ,A>100and B<6 and C>12均使多列索引有效,而B>10 等会使索引无效
- join操作时,on关键字后面的字段类型必须保持一致
创建索引
create index 索引名 on 表(列)
查看索引
show index FROM sc
四种index_type:https://www.cnblogs.com/edgedance/p/7131484.html
删除索引
DROP index sc_id on sc 用于删除普通索引和唯一索引
alter table 表明 drop primary key 用于删除主键索引