时间：2021-07-01 10:21:17 帮助过：2人阅读
SQL：结构化操作数据库的语言
一、 使用SQL语句来创建数据库
在硬盘上先创建一个文件夹library，用来保存创建数据库
创建的格式：
create database 数据库名字
on primary(
name = 主要文件的逻辑名,
filename = "保存数据库文件夹的路径\文件全名",
size = 初始化数据库（核心文件）大小,
maxsize =指定最大的范围/unlimited,
filegrowth = 文件增长的方式
（1 M / 2 %）
)log on(
name = 日志文件的逻辑名,
flename ="保存数据库文件夹的路径\日志文件全名",
size = 初始化日志文件的大小,
maxsize = 指定最大的范文/unlimited,
filegrowth = 文件增长的方式
（1 M / 2 %）
)
二、 常用数据类型
int ：整数
char和varchar区别：
char：定长字符串，char(10)，‘liu‘真实存储
会占用10个字符的空间，比较浪费空间，但是
运行速度较快，一般在字段值较为固定（比如电话号码）和主键，最好用char
varchar:可变长字符串，varchar(10)，‘liu‘真实存储会占用3个字符的空间，节约空间
四、 建表语句（必须记住）
use 数据库名称（在那个数据库里创建表格）
create table 表名(
列名1 数据类型 primary key,把该设置主键
列名2 数据类型 ,
列名3 数据类型 ,
...
)
五、 插入语句（往表中添加新的数据）
1）insert into 表名
values(列值1,列值2,列值3...)
a)往表中添加数据，所有的列都必须要赋值
b)列值的顺序、类型、个数都必须要跟表结构相匹配
c)如果该插入语句，有任何一个位置出错，则整条数据都无法插入
d) into 可以省略，建议大家最好都别省略
2) 只插入部分列的列值
格式：
insert into 表名（列名1，列名2...）
values（列值1，列值2...）
a) 表名后，添加要插入列的名字，values后面根据列名对应列值，而且列名跟列值的顺序必须完全一致
b) into 依然可以省略
六、修改操作：针对表中的数据，对某些列值进行修改
1)格式：
update 表名 set 列名1 = 列值1，列名2 = 列值2...
where 条件
2）where条件:对哪些数据进行修改，如果不加where条件，则整张表中所有的数据都会被修改
七、 删除操作：根据条件删除表中的数据
1）格式：delete from 表名 where 条件
2）如果where条件没有的，会删除表所有的数据
八、简单的查询操作
1）查询表中所有的数据
select * from 表名
2）查询表中某些列的值
select 列名1，列名2... from 表名
3）别名:给查询出来列名起一个另外的名字
a)select 列名1 as 别名1,列名2 as 别名2... from 表名
b）使用空格，给列起别名
select 列名1 别名,列名2 别名2 from 表名
c）使用“=”起别名
select 别名1 = 列名1，别名2 = 列名2
from 表名
九、 带条件的查询
select */列名 from 表名 where 条件
1）关系运算符：=，>,>=,<,<=,!=(<>)
2）逻辑运算符：and（逻辑与） ，or（逻辑或），not（逻辑非）
4) in:用来指代多个值
where 列名 in(列值1，列值2，列值3...)
5) is null :null，当前这条数据中，该列没有值，就null来表示
is null：当前该列没有值
is not null
用not来逻辑非type is null
6) 模糊查询：like
%：代表0个或多个字符
_:：代表任意一个字符
注意：where author like ‘%明‘：从结果上来看是正确的，但如果在企业开发中，表中数据特别多的，有的作者名字叫‘李明‘，这样就有问题了，作者的名字必须是3个字符
十、 约束：指定表中的数据，必须要符合某些要求，而这些要求就是约束
1、主键约束：主键约束就是用来定义表中的主键，主键用来标识表中的数据（主键是表中数据唯一的标识），而且非空，并且唯一（不重复）
a）可以再表中指定主键约束的名字
格式：
constraint 约束名 primary key(列名)
b）指定为主键的列，必须用not null(非空)来进行修饰
c）一张表中永远只能由一个主键，该主键可以由一个列或者多个列组合
多个列组合，定义为主键，联合主键
d）在表创建以后，再来指定主键（不推荐）
可以向任意的表中，追加各种约束
alter table 表名
add constraint 约束名字 约束类型 列
2)唯一性约束（unique）：唯一，不重复
a)唯一性约束用于指定一个或多个列的组合的值是不重复的，可以防止在插入数据的时候，出现重复值。
b)使用唯一约束修饰的列，可以出现null，但是只能出现一次
c) 主键约束和唯一约束的区别：
主键（约束）在表中只能出现一次（主键只有一个）
唯一约束可以出现多次（多个唯一约束）
主键不能为null
唯一约束可以为null
主键是用来标识数据
唯一强调的是该列的值，必须是独一无二
d）可以表的外界来指定唯一约束
alter table 表名
add constraint 约束名字 约束 (列)
e) 删除唯一约束
alter table 表名
drop constraint 约束名
f) 指定多个列的组合用一个唯一约束
3) 非空约束：插入数据时候，被非空约束修饰的列必须要有值not null
name这个列，用唯一和非空同时进行约束
name这个列必须要有值，而且该列的值必须要唯一
4）默认值约束
默认值约束是指给表插入新的数据时候，某些列，如果没有插入数值，可以再设计表，直接加入该列默认值，这样话，该列如果没有插入新的值，则使用默认值
反之，如果插入新的值，则不再使用默认值
2、外键约束
外键就是用来连接两张表之间的关联关系的列。
外键约束用来定义外键
第3范式：用来关联多张表，可以其中一张表设置一个列，该列的取值是另外一张表中主键的值，用这个列来维护两张表的关联关系，这个列就是外键
首先往父表中插入数据，然后再往子表插入数据
3、 检查约束：对输入的列中的值进行检查，符合要求的，才可以插入；
检查约束一般是设置在单个列上，用来限制并检查用户的输入
十一、 利用select语句复制表
1）把整张表中所有的数据全部复制到新表中
select * into 新表名 from 源表名
2）跟条件，把源表中部分数据赋值到新表中
select * into 新表名 from 源表名
where 条件
3) 指定列来创建新表（并复制对应的数据）
select 列名1，列名2，... into 新表名
from 源表名
4) 只复制表的结构，不复制表中的数据
select */列名 into 新表名 from 源表名
where 条件（false）
十二、 限定返回数据的条数
1）限制返回的行数
select top n 列名/* from 表名 where 条件
2）按百分比来返回对应数据
select top n percent 列名/* from 表名
where 条件
十三、 子查询：查询语句中又包含了查询语句
十四、 去掉重复数据（列）
select * from book
insert into book
values(‘00006‘,‘史记‘,‘机械出版社‘,‘司马迁‘,‘历史类‘,5000,2000)
insert into book
values(‘00007‘,‘大唐双龙传‘,‘清华大学出版社‘,‘黄易‘,‘武侠类‘,8000,1200)
查询所有图书的出版社名字
select pub from book
列之前加入关键distinct
select distinct pub from book
查询所有图书的类型（去掉所有重复类型名）
select distinct type from book
十五、排序：把数据查询出来以后，根据其中某一个列的值，来进行升序，或降序的排列
1）格式：
select 列名/* from 表名
where 条件
order by 列名 asc / desc
2）asc：默认值，表示按照该列的值进行
升序排列（由小到大）
desc：表示按照该列的值进行降序排列
降序（由大到小）
案例：按照进货量把所有的图书信息进行升序排列
select * from book
order by numinput
案例：查询所有清华大学出版社出版的图书信息，按照库存量，进行降序排列
select * from book
where pub=‘清华大学出版社‘
order by numstore desc
3) order by 列名1 asc/desc ,
列名2 asc/desc
首先根据列名1进行升序或者降序排列，如果列名1中的列值，要是一直，再根据列名2中的列值，进行升序或者降序排列
案例：查询所有图书的信息，然后按照进货量升序进行排序，如果进货量一致，则按照库存量进行降序排列
select * from book
order by numinput asc, numstore desc
案例：查询所有的图书信息，然后按照库存量降序排列，如果库存量一致，则按照进货量进行升序排列
select * from book
order by numstore desc, numinput asc
十六、 聚合函数（分组函数）
1）count(列名/*):获得该列或整张表的数据的个数
select COUNT(*) from book
count(*):表中所有数据的个数
select COUNT(bid) from book
select * from book
select COUNT(type) from book
count(列名):该列中列值的个数
2）sum(列名)：获得该列所有的列值之和
案例：求出所有进货量之和
select SUM(numinput) from book
案例：所有库存量之和
select SUM(numstore) from book
3）avg(列名)：获得该列平均值
案例：每种图书的库存量的平均值
select AVG(numstore) from book
案例：每种图书的进货量的平均值
select AVG(numinput) as 进货量的平均值
from book
4）max(列名)：获得该列最大值
案例：每种图书的进货量的平均值以及库存量的最大值
select AVG(numinput) 进货量平均值,
MAX(numstore) 库存量最大值
from book
5）min(列名)：获得该列的最小值
案例：求出每种图书的库存量的最小值
select MIN(numstore) from book
案例：求出每种图书的进货量的最大值，平均值，最小值，总和
select MAX(numinput) 进货量最大值,
AVG(numinput) 进货量平均值,
MIN(numinput) 进货量最小值,
SUM(numinput) 进货量总和
from book
案例：求出清华大学出版社所有图书的库存量最大值，平均值
select MAX(numstore) 库存量最大值,
AVG(numstore) 库存量平均值
from book
where pub = ‘清华大学出版社‘
案例：每个出版社的进货量最大值
清华：10000 机械：20000 海南：30000
十七、 如何使用聚合函数进行查询语句的分组
1）格式
select 列名 / 聚合函数
from 表名
group by 列名
2）首先根据group by 列名把表中所有的数据进行分组，分组以后，会在每一组的数据中使用聚合函数
案例：每个出版社图书的进货量的最大值
首先根据出版社，把所有的图书进行分组，然后每组图书使用MAX，求出每组中图书进货量的最大值
select MAX(numinput) 进货量最大值,
pub 出版社的名称
from book
group by pub
案例：求出每种类型的图书的进货量最大值以及平均值，最小值
select MAX(numinput) 进货量最大值,
AVG(numinput) 进货量平均值,
MIN(numinput) 进货量最小值,
type 图书的类型
from book
group by type
案例：求出每个出版社的图书的库存量的最大值及进货量的平均值
select MAX(numstore) 库存量最大值,
AVG(numinput) 进货量平均值,
pub 出版社名称
from book
group by pub
十八、分组并排序
1）是把所有的数据分组以后，每一组数据中使用聚合函数，最后查询的结果，进行排序（排序永远在最后）
2）格式：
select 列名 / 聚合函数
from 表名
where 条件
group by 列名
order by 列名/别名/聚合函数 asc/desc
案例：查询作者不是鸟山明的图书根据出版社进行分组，显示出来每个出版社，图书的进货量最大值，进货量的平均值
select MAX(numinput) 进货量最大值,
AVG(numinput) 进货量平均值,
pub 出版社名字
from book
where author !=‘鸟山明‘
group by pub
先执行where语句，再执行分组，然后再使用聚合函数
select * from book
案例：根据类型对图书进行分组，求出每组图书的进货量最大值，根据进货量最大值进行倒序排列
select MAX(numinput) 进货量最大值,
type 类型的名称
from book
group by type
order by MAX(numinput) desc
使用别名替代order by 聚合函数
select MAX(numinput) 进货量最大值,
type 类型的名称
from book
group by type
order by 进货量最大值 desc
案例：根据出版社对图书进行分组，求出每个出版社图书的进货量之和，进货量的平均值，然后根据进货量的平均值，升序显示每个出版社名称以及图书的进货量之和，平均值
select pub 出版社名称,
SUM(numinput) 进货量之和,
AVG(numinput) 进货量平均值
from book
group by pub
order by 进货量平均值 asc
案例：查询类型不为null的图书，根据type进行分组，显示每种类型图书的库存量的最大值，进货量的平均值及图书的type，按照库存量最大值进行升序排列，如果库存量最大值一致，按照进货量的平均值进行降序排列
select MAX(numstore) 库存量最大值,
AVG(numinput) 进货量平均值,
type 图书的类型
from book
where type is not null
group by type
order by 库存量最大值 asc,进货量平均值 desc
十九、 having子句：对分组以后的结果进行过滤
select 列名/聚合函数
from 表名
where 条件 - 第1次过滤
group by 列名 - 分组
having 子句 - 第2次过滤（分组以后）
order by 别名/列名/聚合函数 - 排序
案例：查询每个出版社，图书进货量最大值，并且显示进货量最大值超过10000
use library
select pub 出版社名字,
MAX(numinput) 进货量最大值
from book
group by pub
having MAX(numinput)>10000
案例：图书的类型不能为null，求出每种类型的图书库存量的平均值，进货量的总和，并且显示进货量总和超过10000的类型，及库存量的平均值进货量的总和
select AVG(numstore) 库存量平均值,
SUM(numinput) 进货量总和,
type 图书的类型
from book
where type is not null
group by type
having SUM(numinput) > 10000
案例：查询库存量超过100的图书，每个出版社图书库存量总和，进货量总和，显示进货量总和小于20000的图书，把结果按照库存量总和进行降序排列
select SUM(numinput) 进货量总和,
SUM(numstore) 库存量总和,
pub 出版社
from book
where numstore > 100
group by pub
having SUM(numinput) < 20000
order by SUM(numstore) desc
案例：查询库存量超过所有图书平均库存量的图书信息
分析：首先查询图书平均库存量，然后只要超过平均库存量就是我们需要数据
select AVG(numstore) from book
1671
select * from book where numstore>1671
select * from book
where numstore > (
select AVG(numstore) from book
)
查询进货量超过清华大学出版社进货量的平均值的图书信息
select * from book
where numinput > (
select AVG(numinput) from book
where pub = ‘清华大学出版社‘
)
二十、删除表和数据库
1）删除表：drop table 表名
案例：删除book1表
drop table book1
drop table book2
2）删除数据库：
drop database 数据库名
drop database worker
二十一、 identity函数：创建主键的值
1）identity(初始值，增量)
2）利用identity来自动给主键，创建主键的值，第1次调用identity，返回是当前初始值，从第2次开始每次调用identity
在原来值得基础上每次加上增量的值
use library
create table worker1(
id int identity(1,1) primary key,
name varchar(50)
)
insert into worker1(name) values(‘张辽‘)
insert into worker1(name) values(‘乐进‘)
insert into worker1(name) values(‘徐晃‘)
insert into worker1(name) values(‘关羽‘)
insert into worker1(name) values(‘陆逊‘)
select * from worker1
二十二、 SQLSERVER中常用函数
1）字符函数
len(str):统计str字符的个数
select LEN(‘liuyingqian‘)
lower(str):大写字母改为小写字母
select LOWER(‘HELLO‘)
upper(str):小写字母改为大写字母
select UPPER(‘World‘)
去掉空格
LTRIM(str):去掉左边的空格
select LTRIM(‘ java‘)
函数之间可以进行嵌套
select LEN(LTRIM(‘ java‘))
RTRIM(str):去掉右边的空格
select RTRIM(‘java ‘)
如何去掉左右两端的空格？
select LTRIM(RTRIM(‘ java ‘))
left(str,数字)：从左边开始，截取字符，指定数字个数字符
select LEFT(‘abc123‘,3)
right(str,数字)：从右边开始截取
select RIGHT(‘abc123‘,3)
2） 日期函数：
日期包含：年月日时分秒（毫秒）
getDate():获取当前日期
select GETDATE()
DateAdd(位置，数字，str)：在日期指定的位置添加数字
select DATEADD(YEAR,5,GETDATE())
select DATEADD(MM,20,‘2012-12-11‘)
select DATEADD(DD,15,GETDATE())
DateDiff(位置，日期1，日期2):两个日期对应位置之间的间隔
select DATEDIFF(YEAR,‘2012-1-1‘,‘2014-1-1‘)
select DATEDIFF(MM,‘2012-1-1‘,GETDATE())
select DATEDIFF(DD,‘1999-1-1‘,GETDATE())
DateName(位置，日期)：取得对应位置的日期的数字
select DATENAME(YEAR,GETDATE())
select DATENAME(MONTH,GETDATE())
select DATENAME(DD,GETDATE())
3) 数字函数
abs(数字)：求出数字的绝对值
select ABS(100)
select ABS(-100)
CEILING(小数)：向上取整
select CEILING(12.1)13
floor(小数)：向下取整
select FLOOR(12.9)12
round(小数，数字)：四舍五入
如果数字为负数，往小数点左边四舍五入
如果数字为正数，往小数点右边四舍五入
如果数字为0，整数
select ROUND(10.9,0)11
select ROUND(10.3,0)10
select ROUND(10.87,1)10.9
select ROUND(17,-1)20
POWER(底数，指数):
select POWER(10,3)
sign(数字)
如果数字为正数，返回值就是1
如果数字为负数，返回值就是-1
如果数字为0，返回值就是0
select SIGN(100)1
select SIGN(-999999)-1
select SIGN(0) 0
sqrt(数字)：求出平方根。2的平方是4,2就是4的平方根
select SQRT(99)
二十三、 视图：视图不是一个真实表，是一个虚拟的表，可以通过视图查询一个或多个表的信息
1）视图中不能用来保存任何数据，只是能用来查看表的数据。对视图任何的操作，都不能修改表中的数据
2）视图用处：可以简化查看表中的数据，尤其是多张表之间的数据
3）创建视图格式（视图很像是一条SQL查询语句的结果）
create view 视图名
as
查询语句
select * from book
案例：创建一个视图，查询清华大学出版社和历史类所有图书的名字，出版社，作者，类型
create view book_view1
as
select bname,pub,author,type
from book
where pub=‘清华大学出版社‘ or
type = ‘历史类‘
查询清华大学出版社和历史类所有图书的名字，出版社，作者，类型
第1种方式，我们通过SQL直接查询book表
select bname,pub,author,type from book
where pub=‘清华大学出版社‘ or type=‘历史类‘
第2种方式，我们通过book_view1视图去查询
select * from book_view1
案例：创建视图book_view2
查询书名，作者，进货量，以及库存量
并且进货量要大于10000，小于25000
create view book_view2
as
select bname,author,numinput,numstore
from book
where numinput between 10000 and 25000
4) 修饰视图
alter view 视图名
as
查询语句
案例：修改book_view2视图，查询书名，作者，
进货量，库存量，图书类型所有的图书信息
alter view book_view2
as
select bname,author,numinput,numstore,
type
from book
select * from book_view2
5) 删除视图：drop view 视图名
drop view book_view2
二十四、 索引：是建立列上的一种数据库对象，索引中记录当前表中的数据在物理存储的位置，通过索引可以提高查询（检索）的速度
1）索引分类：索引的（物理）顺序是否与表中数据的物理顺序一致
a）聚集索引：索引的顺序与表中的数据的物理存储顺序完全一致（默认情况下，表中的数据的位置，跟主键有关），只要表中定义了主键，默认情况主键上就是聚集索引，比如：书的目录
b）非聚集索引：索引的顺序跟表中的数据的物理存储顺序不一致
c）表中主键，默认就是聚集索引，而且只有一个，查询操作根据主键，查询速度最快
非聚集索引：可以我们来自己指定非聚集索引，
比如那些列经常作为索引的条件，就可以在这些列上，添加非聚集索引
eg：按照价格查询，按照作者查询...
2) 创建非聚集索引格式：
create index 索引名 on 表名（列名）
在book表中，bname这个列，添加非聚集索引
create index book_name_index1
on book(bname)
在bname列上添加索引以后，根据bname进行查询操作，速度会提高很多
3) 删除非聚集索引
drop index 表.索引名
drop index book.book_name_index1
4) 一个表最多可以创建250个非聚集索引（理论上，实际不可能，表中一定会有主键，一定会有一个聚集索引），所以一个表中最多创建249个非聚集索引和1个聚集索引
5) 索引的缺点：
a）创建索引会占用磁盘物理空间
b）建立索引虽然可以提高查询的速度，但是减慢了数据修改的速度
6）如果该表中的某些列，使用唯一（unique）性约束，该列上默认创建一个唯一索引
7）复合索引：可以用一个索引指定多个列
create index 索引名
on 表名（列名1，列名2...）
案例：在book表上，bname，author，type指定在一个复合索引中
create index book_bat_index
on book(bname,author,type)
=====================总结==================
1、 练习：创建一张表：worker
列：id（主键），name（非空，唯一），address（非空）
插入2条数据
2、修改操作
1)update 表名 set列名1 = 新的列值1，列名2 = 新的列值2….
Where 条件
2)多个列之间必须用“，”分隔，而且如果没有where条件，则整张表所有的数据都要进行修改
案例：修改id是1 的数据的名字，改为 祖大寿，地址 ：北京
update worker set name =‘祖大寿‘,address = ‘北京‘ where id =1;
select name,address from worker where id = 1;
3、删除操作
1)delete from 表名 where 条件
2) where条件指定要删除的数据，如果没有where条件，删除表中所有的数据
案例：删除id为1 的数据
delete from worker where id =1 ;
select name from worker where id = 1;
4、查看表的结构：