SqlServer数据库【基础-更删改查】

一、创建语句

（1）创建数据库

1.检查系统中是否存在这个数据库，存在则删除

格式：

if exists(select * from sysdatabases where name='数据库名')
drop database 数据库名
go

例子：

if exists(select * from sysdatabases where name='musicDB')
drop database musicDB
go

2.创建数据库

格式：

create database 数据库名
go

例子：

create database musicDB
go

（2）创建表格

1. 检查数据库中是否存在这个表，存在则删除

格式：

if exists(select * from sysobjects where name='表名')
drop table 表名

例子：

if exists(select * from sysobjects where name='T_speciality')
drop table T_speciality;

2.创建表

格式：

create table 表名(
列名1 数据类型,
列名2 数据类型
…………
)
go

例子：

create table T_speciality(
specialityid varchar(20) primary key,
specialityname varchar(20)
)
go

二、删除语句

（1）删除数据库

格式：

drop database 数据库名

例子：

drop database mystyle

（2）删除表格

格式：

drop table 表格名称

例子：

drop table tbl_Music

（3）清空数据

格式：

truncate table 表名

例子：

truncate table tbl_Music

（4）删除某行数据

格式：

delete from 表名 where 列名='条件'

例子：

delete from tbl_Music where musicName='菊花台'

注意：当没有where条件时则会清空整张表的数据

（5）删除某列数据

格式：

alter table 表名 drop column 列名

例子：

alter table tbl_Music drop column singer_id

注意：使用该语句则会将该列的所有的数据都删除，包括列名

三、修改语句

（1）修改表格

1.修改表名

格式：

exec sp_rename '旧表名', '新表名';

例子：

exec sp_rename 'tbl_Music', 'musics';

注意：更改对象名的任一部分都可能会破坏脚本和存储过程。所以不赞成修改表名

2.修改表属性

增加一列
格式：

alter table 表名
add 列名 字符类型

例子：

alter table tbl_Music
add score varchar(20)

3.修改列名名字

格式：

exec sp_rename '表名.旧列名','新列名','column'

例子

exec sp_rename 'musics.musicid','id','column'

注意：这样修改同样会破坏储存过程，建议先删除这一列再添加新的列。

4.修改列数据类型

格式：

alter table 表名 alter column 列名 新的数据类型

例子：

alter table musics alter column clicknumber varchar(20)

注意：主键不能修改，字符型的不能转为int型的。

5.修改表格数据

格式：

update  表名 set 列名='修改的值'   
where 条件

例子：

update  musics set clicknumber='200'   
where id=3

注意：主键不能修改

四、查询语句

数据库查询的基本格式为：

• select ----输出（显示）你要查询出来的值
• from -----查询的依据，数据来源
• where -----筛选条件（对依据（数据库中存在的表））
• group by -----对筛选后的数据 进行分组
• having ----- 筛选条件（对分组后的结果再次筛选）
• order by -----将结果进行排序
  基本语法格式顺序不能变，但可以缺省

（1）单表查询

1.查询全部

格式：

select * from 表名
-- “*”代表所有列

例子：

select * from musics

2.查询指定列

格式：

select 列名 as '别名',列名
from 表名
-- 也可以不给列取别名

例子：

select id as '账号',musicname
from musics

（2）多表查询

内连接	全连接	左连接	右连接
Inner join	Full Outer join	Left join	Right join

• 内连接：此语句的结果为同时匹配表a和表b的记录集。即内连接取的是两个表的交集。
• 外连接：（全连接，左右连接）语句的结果为表a与表b的并集，即任意一个表的内容都将被查询出来，如果另一个表无对应的项，则显示为null
• 三种连接只是表格的对应顺序不一样

1.内连接

格式：

select *
from 表名1 别名1
inner join 表名2 别名2
on 别名1.主键=别名2.外键 

例子：

select *
from [dbo].[tbl_Music] t1
inner join [dbo].[tbl_Menu] t2
on t1.musicId=t2.musicId 

结果展示：

2.外连接

格式：

select *
from 表名1 别名1
[full/left/right]join 表名2 别名2
on 别名1.主键=别名2.外键 

例子：

select *
from [dbo].[tbl_Music] t1
full join [dbo].[tbl_Menu] t2
on t1.musicId=t2.musicId 

结果展示：

（3）嵌套查询

格式：

select *
from 表名1 别名1
inner join(
子查询语句
) 别名2
on 别名1.共同拥有的键名=别名2. 共同拥有的键名
go

例子：

select t4.userName as '用户名',t4.menuName as'歌单名',t3.musicName as '歌曲名',t3.clickNumber as'点击数量'
from [dbo].[tbl_Music] t3
inner join(
select t1.userId,t1.userName,t2.musicId,t2.menuName
from [dbo].[tbl_User] t1
inner join [dbo].[tbl_Menu] t2
on t1.userId=t2.userId) t4
on t3.musicId=t4.musicId
go

注意：
子查询语句中不能使用select *，应为使用select * 查询了所有的内容，就会由两个相同的列，并且在查询中必须把与主查询相同的键给查询出来，否则无法进行连接查询。

（4）聚合查询

1.聚合函数

聚合函数如下所示：

• min —最小
• max —最大
• sum —和
• avg —平均
• count —统计（个数）
  聚合函数一般和分组group by一起使用
  例：

select avg（age）from student group by sex

2.聚合查询

SELECT 所有查询的内容
FROM 表
GROUP BY 分组条件

例子：

- • 统计每个客户经理所经手的质押业务数
SELECT t1.agency_id AS '客户经理编号',COUNT(*)AS '客户数量'
FROM
t_agency_info t1
RIGHT JOIN
t_impawn_info t2
ON t1.agency_id=t2.agency_id
GROUP BY t2.agency_id

结果展示：

注意：
一旦使用min ，max ，sum，avg ，count 只能查询相对应的聚合数，和分组条件，其他的都不能再查询了。

（5）排序

排序分为降序和升序。
升序：

select *  from emp order by id asc

降序：

select * from emp order by id desc

按多列排序：

select * from emp order by id asc,name asc

限制固定行数显示：

select top 2 id,name from emp where deptno=2

返回百分之多少行：

select top 20 percent id,name from emp where deptno=2

（6）常用的系统函数

日期参数及缩写如下表所示：

日期部分	缩写
year	yy, yyyy
month	mm, m
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n

例子：

-- 获取当前年份
 select datename(yy,GETDATE());
 select datename(yyyy,GETDATE());
 select datename(year,GETDATE());

五、插入语句

（1）给表格添加新的列

格式：

alter table 表名
add 列名 数据类型
go

例子：

alter table tbl_Music
add score varchar(20)
go

（2）添加表格数据

方法一：安照列名依次给出数据值，不能交换顺序，数据类型不能出错

insert into demo values
 ('zhang',25,1)，
('li',28,2)

方法二：安照指定顺序给出数据值

insert into demo (nname,age,sex) values ('san',18,0)

方法三：交换顺序，数值按照列名给就行

insert into demo (nname,sex,age) values ('san',1,22)

方法四：没给数值的列显示null

insert into demo (nname) values ('li')

2.复制表中数据到另一个表中：

insert into demo1(nname,nage,asex) select nname,age,sex from demo

注意事项

• 1:每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；
  * 2:每个数据值的数据类型、精度和小数位数必须与相应的列匹配；
• 3:不能为标识列指定值，因为它的数字是自动增长的；
• 4:如果在设计表的时候就指定了某列不允许为空，则必须插入数据；
• 5:插入的数据项，要求符合检查约束的要求
• 6:具有缺省值的列，可以使用default（缺省）关键字来代替插入的数值