数据库的创建 ~ 创建表(约束) ~ SQL~数据操作~视图~索引

最新推荐文章于 2024-04-04 13:13:42 发布

Turing·

最新推荐文章于 2024-04-04 13:13:42 发布

阅读量2.3k

点赞数 3

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_41835813/article/details/89431798

版权

数据库专栏收录该内容

12 篇文章 1 订阅

订阅专栏

五十道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 用于删除主键索引

Turing·

关注

3
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
数据库的创建 ~ 创建表(约束) ~ SQL~数据操作~视图~索引

创建数据库create DataBase db_HXXon( 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, ...
复制链接

扫一扫