Sql基础(零基础学数据库_SqlServer版)

刚开始学c#时候的笔记,只是些基础的语句如有错误请批评指正,谢谢,(使用sqlserver2012以上)

一.  数据库概述

sqi全称 structrued query language

1、数据：能被计算机识别存储处理的符号的集合。包括：数字、符号、图片、声音、视频。 《英文全称data》

2、数据库：长期存储在计算机内，有组织，可共享的大量数据的集合。 英文database 简写db

3、数据库管理系统 （database management system）科学地组织和存储数据，高效地获取和维护数据的系统软件。

4、四大系统数据库：master数据库、model数据库、msdb数据库和tempdb数据库。他们是不可删除的。
　　1）master数据库：数据库称为主数据库，保存着放在sql server实体上的所有数据库。记录了sqlserver系统的所有系统级别信息。并且还记录所有的登录帐户、系统配置设置和已连接的server(服务)等信息。
　　2）model数据库：称为模板数据库，用作在系统上创建的所有数据库的模板。
　　3）msdb数据库：供sqlserver代理程序调度、警报和作业以及记录操作员时使用。
　　4）tempdb数据库称为临时数据库。保存所有的临时表和临时存储过程。 temporary 临时的
5、ssms是sql server management studio的缩写
6、有两种身份验证：
　　1）sql server 身份验证 （混合登录） 默认账户：sa
　　2）windows身份验证。
7、使用ssms创建、使用、删除数据库 删除的时候注意不能使用本数据库
8、使用t-sql语言创建、使用、删除数据库 （重点）
　　1）sql （structured query language 结构化查询语言）
　　2）创建数据库 create database 数据库名
　　使用数据库 use 数据库名
　　删除数据库 drop database 数据库名
9、 数据库登录如果发生错误2 ，应开启（mssqlserver）服务
启动mssqlserver服务：1、选中任务栏→右键选中任务管理器→打开服务，找到mssqlserver，启动
　　　　　　　　　　　　2、桌面→选中计算机，右键【管理】→服务和应用程序→①选中服务，找到mssqlserver服务，启动②sqlserver配置管理器→
10、常见的数据库管理系统： 微软的 sql server office access
　　甲骨文 oracle
　　mysql   等等
11、sql server 2012数据库系统的组成部分：
　　analysis services 分析服务（性能分析）
　　integration services 集成服务
　　reporting services 报表服务
　　dbms 数据库管理

二.  数据库操作

数据库操作

1、使用ssms创建、使用、删除数据库 删除的时候注意不能使用本数据库
2、使用t-sql语言创建、使用、删除数据库 （重点）
　　1）sql （structured query language 结构化查询语言）
　　2）创建数据库 create database 数据库名
　　使用数据库 use 数据库名
　　删除数据库 drop database 数据库名
3、数据库的备份与还原
　　备份
　　1）使用ssms来操作 （sql server management studio）
　　2）使用sql语句来操作 backup database class1512neta to disk='d:\class1512neta.bak'
　　backup database 数据库名 to disk='路径\备份数据库名.bak'

　　还原
　　1）使用ssms来操作 （sql server management studio）
　　2）使用sql语句来操作 restore database class1512neta from disk='d:\class1512neta.bak'
　　restore database 数据库名 from disk='路径\备份数据库名.bak'
4、数据库的分离和附加
　　分离
　　1）使用ssms来操作
　　2）使用sql语句来操作 exec sp_detach_db 数据库名
　　exec sp_detach_db class1512neta
　　附加
　　1）使用ssms来操作
　　2）使用sql语句来操作 exec sp_attach_db 数据库名,‘路径\db.mdf’,’路径\db.ldf’
　　exec sp_attach_db class1512neta ,'c:\program files (x86)\microsoft sql server\mssql11.mssqlserver\mssql\data\class1512neta.mdf','c:\program files (x86)\microsoft sql server\mssql11.mssqlserver\mssql\data\class1512neta_log.ldf'

5、修改数据库的容量
　　alter database class1512neta
　　modify file
　　(
　　name=class1512neta,
　　size=20mb
　　);
　　go
6、查看数据库
　　sp_helpdb 数据库名

三. 表操作

a、数据类型
　　1、整型 small int
　　　　　　int
　　　　　　big int
　　2、字符型
　　　　非unicode unicode
　　①定长字符 char（n） nchar（n）
　　②变长字符 varchar（n） nvarchar（n）
　　3、浮点型
　　　　①近似值 单精度 real
　　　　双精度 float
　　　　②精确值 decimal（5，2） 第一个参数表示总长度 第二个参数表示小数部分的位数
　　　　numeric
　　4、日期时间类型 datetime 8字节
　　　　date 3字节
　　　　time 5字节
　　5、货币类型 money
　　6、布尔型 bit 0 false
　　　　　　　　1 true
　　7、唯一标识符 uniqueidentifier
b、建数据表
　　1、ssms方式建立
　　2、sql方式建立
　　create table 表名
　　（
　　字段名1（列名） 数据类型,
　　字段名2（列名） 数据类型,
　　...
　　字段名n（列名） 数据类型
　　）
　　create table student
　　(
　　name varchar(20) not null,
　　age int ,
　　sex char(2) not null,
　　home varchar(200)
　　)
3、修改表结构
　　1）增加列
　　alter table 表名
　　add 列名1 数据类型，
　　列名2 数据类型

　　alter table student
　　add hobby varchar(20),
　　addr varchar(50)

2）删除列
　　alter table 表名
　　drop column 列名1,列名2,列名n

　　alter table student
　　drop column tel,addr
3)修改列的数据类型
　　alter table 表名
　　alter column 列名 数据类型

　　alter table student
　　alter column home varchar(100)

4、删除表
　　drop table 表名
　　drop table student

四. 约束

约束
1、主键约束：保证实体的唯一性 （什么样的字段可以选作主键，必须是唯一性的字段）
①ssms
②sql
primary key 主键
identity 自增 identity（3,2） 从3开始每次增加2 第一个参数从几开始，第二个参数每次增加几个
要想使用identity必须是int型
2、非空约束：保证字段不能为空
①ssms 把勾去掉则就是非空的
②sql
not null
3、检查约束 check(条件) and or not >= <= > < = <>(不等于)
between ? and ? (包含边界)
4、默认约束 default
5、唯一约束 unique
6、外键约束
foreign key references 表(列名)

create table student
(
stuid int primary key identity,--主键自增
stuname varchar(20) not null,
stuage int check(stuage between 0 and 120),
stusex char(2) check (stusex='男' or stusex='女'),
stuaddr varchar(50) default ('北京'),
stutel varchar(14) unique
)

create table score
(
stuid int foreign key references student(stuid),
stufen float
)

7、规则（rule） 对存储的数据表的列或自定义的数据类型中的值得约束
①创建规则
create rule 规则名 as @变量名表达式
create rule numcheck --rule 规则 numcheck 自己定义的规则名
as @num>5 --使用@定义变量 num 自己定义的变量名 >5条件表达式

②绑定规则
exec sys.sp_bindrule '规则名','表名.列名'
exec sys.sp_bindrule 'numcheck','test.testnum' --绑定名为numcheck的规则到test表的testnum列

③取消绑定
exec sys.sp_unbindrule '表名.列名' --取消test表的testnum列的规则

④删除规则
drop rule numcheck --drop rule 规则名 正在使用的规则不能被删除

 

五. 

数据更新
1、添加数据
1）部分列添加
insert into 表名 （列名1，列名2，...，列名n） values(值1,值2,...,值n)
insert into student (stuname,stuage,stusex,stutel)values ('关羽',45,'男','13010001100')
2)全部添加
insert into 表名 values(值1,值2,...,值n)
insert into student values ('刘备',45,'男',default,'13010001103')
2、更新数据
1）全部更新
update 表名 set 列名1=值1，列名2=值2，列名n=值n
update student set stuage=18,stusex='女'
2）带条件的更新
update 表名 set 列名1=值1，列名2=值2，列名n=值n where 条件
update student set stuage=55,stusex='男' where stuname='关羽'
3、删除数据
1）全部删除 (如果两表有外键关系，先删外键表，后删主键表)
delete from 表
delete from student
2）带条件的删除
delete from 表 where 条件
delete from student where stuname='刘玉洲' and stuaddr='河南'/*-

 

六 . 

t-sql语言核心
1、什么是t-sql？
t-sql是微软公司在关系型数据库管理系统sql server中的sql3标准的实现，是微软对sql的扩展。
2、将t-sql语句分为4大类,分为：①数据操作语句 insert delete update select
②数据定义语句
............create database drop database create table drop table alter
③数据控制语句 grant（授权） deny（拒绝） revoke（收回权限）
④附加的语言元素
3、sql-server定义了两种标识符：①规则标识符 第一，开头不能是数字 第二包含 字母 数字 汉字 _ @ # 第三不能是系统保留字（关键字）
②界定标识符 [] '' [0-9] '%张%'
4、常量
①数字常量 （整数 小数 货币）
②字符串常量 （‘’引起的 字母数字特殊符号 ）
③日期时间常量 ‘2017-08-07’ '170807'
④符号常量 两种 第一分隔符 第二标识符
5、变量
①全局变量 @@全局变量名
②局部变量 @局部变量名
declare @局部变量名 类型 declare @count int 定义count为int类型变量
set @count=（select count(*) from student） 将count赋值为某查询语句的结果
go
6、了解批处理和脚本
批处理是同时从应用程序发送到sql server并得以执行一组单条或多条t-sql语句。这些语句为了达到一个整体的目标而同时执行。

7、运算符：
算术运算符：+ - * / %
比较运算符：= > < >= <= <>
逻辑运算符：and or not all some any between exsits in like
连接运算符：+
按位运算符： & |^ ~

优先级： 算术
比较
逻辑

8、流程控制语句
①begin end begin end 开始结束
②if else if else 如果否则
③case when then 情况 当 则
④while 当（循环）
⑤goto 跳转
⑥return 返回
--循环结构举例
declare @count int --定义变量xount
set @count =0 --赋初值为0
while @count<10
begin
print 'count='+convert(varchar(8),@count)
select @count=@count+1;
end
0
print '执行总次数为'+convert(varchar(8),@count)

---分支结构举例

declare @sex varchar(10)
set @sex='男 '
select
case @sex
when '男' then '他是男生'
when '女' then '她是女生'
else 'error'
end

 七. 查询

第七单元：查询（简单查询）
1、查询所有列
select * from student

--查询指定列
select stuname from student
2、查询前n行
select top 3 * from student

select top 3 stuname from student

3、带where的查询
--查询学生表中id为5的学生的信息
select * from student where stuid=5

--查询年龄在17-19之间的女学生
select * from student where stuage between 17 and 19 and stusex='女'
--查询山西的男生

select * from student where stuhome='山西' and stusex='男'
--查询所有男同学的姓名、电话
select stuname,stutel from student where stusex='男'
--查询所有女生的姓名、籍贯
select stuname,stuhome from student where stusex='女'

4、查询结果加别名 字段 （as） 别名 as可以省略
select stuname 姓名,stuhome 籍贯 from student where stusex='女'

5、使用distinct去掉重复
--查询所有学生的性别 去掉重复
select distinct stusex from student

--查询所有学生的年龄去重
select distinct stuage from student

6、使用order by 排序 asc升序（默认） desc （降序）
--查询学生信息按年龄升序排列
select * from student order by stuage asc

--查询学生信息按姓名降序排列
select * from student order by stuname desc

--练习
--查询年龄大于18岁的男生或 所有女生的信息
select * from student where (stuage>18 and stusex='男') or stusex='女'

--查询年龄在17-19岁之间的所有女生的信息按姓名降序排列
select * from student where stusex='女' and stuage between 17 and 19 order by stuname desc
--查询籍贯是山西的男同学的姓名、电话按照年龄升序排序
select stuname,stutel from student where stusex='男' and stuhome='山西' order by stuage asc

7、聚合函数
1）sum求和函数
select sum(stuage) 年龄总和 from student
2）avg求平均值
select avg(stuage) from student
3)max求最大值
select max(stuage) from student
--查询年龄最大的女生的年龄
select max(stuage) from student where stusex='女'
4）min求最小值
select min(stuage) from student
5)count()计数
select count(stuid) from student
--查询年龄为17岁的女学生的个数
select count(*) from student where stuage=17 and stusex='女'

8、分组排序 group by 字段
重中之重： group by后面出现的字段 才能出现select 后面 或者聚合函数
--查询男女生各自的平均年龄
select stusex,avg(stuage) from student group by stusex
--查询各省学生的数量
select stuhome 省份, count(*) 人数 from student group by stuhome

--用having子句对分组结果进行筛选
--重中之重：having后面的条件只能是group by后面出现的字段或聚合函数
--查询各省人数大于1的省份及数量
select stuhome 省份, count(*) 人数 from student group by stuhome having count(*)>1

--查询各省人数中山东省的人数
select stuhome 省份, count(*) 人数 from student group by stuhome having stuhome='山东'

 

八. 查询2（多表查询、嵌套查询、子查询）
--1、模糊查询 like
--通配符 % 代表0到多个字符 _表示一个字符 [] 在范围内 [^]不在范围之内
--查询姓李的学生的信息
select * from student where stuname like '李%'

--查询带李字的学生的信息
select * from student where stuname like '%李%'

--查询李某的信息（姓名为两个字的）
select * from student where stuname like '李_'
--查询李某某的信息（姓名为三个字的）
select * from student where stuname like '李__'

--查询年龄23-25范围之内的学生信息
select * from student where stuage like '2[3-5]'

--查询手机号带6的学生信息
select * from student where stutel like '%6%'

--查询年龄不在的位数不在6、8、9的学生信息 []可以是连续的可以是不连续的
select * from student where stuage like '_[^6,8,9]'

-----------------------------------------------------------------------
--2、带in的查询
--查询学生年龄在16 18 19 25这几个年龄的学生的信息
select * from student where stuage in (16,18,19,25)

--3、带not in的查询
--查询学生年龄不在16 18 19 25这几个年龄的学生的信息
select * from student where stuage not in (16,18,19,25)

-----------------------------------------------------------------------
--4、多表查询、嵌套查询、子查询
--查询年龄大于平均年龄的学生
select * from student where stuage>(select avg(stuage) from student)
--查询学生编号大于王宝的学号的学生的信息

select * from student where stuid>(select stuid from student where stuname='王宝')

select * from category
select * from product
--查询手机类的所有商品
select* from product where categoryid=(select categoryid from category where categoryname='手机' )
--查询手机类商品按价格排序（降序）
select* from product where categoryid=
(select categoryid from category where categoryname='手机')
order by productprice desc

--查询价格在4000-5000的手机商品的信息
select * from product where productprice between 4000 and 5000
and categoryid=(select categoryid from category where categoryname='手机')

 

--查询各种商品的单价之和
select categoryname from category where categoryid in (select categoryid from product group by categoryid)
select category.categoryname,sum(product.productprice) from category inner join product on category.categoryid=product.categoryid group by category.categoryid,categoryname

----------------------------------------------------------------------------
--掌握次序函数row_number()
select * from student order by stuage desc
select row_number() over (order by stuage desc) 名次列,* from student

 

九. 

连接查询
--1、内连接 (inner join) 等值连接
select * from a inner join b on 连接条件
select * from a inner join b on a.id=b.id
create table a
(
id int primary key , --课程id
name varchar(20) --课程名称
)
create table b
(
stuid int primary key, --学生id
stuname varchar(20), --学生姓名
kid int, --课程id
)
select * from a
select * from b
--采用内链接
select * from a inner join b on a.id=b.kid
--左连接（左外连接）
select * from a left join b on a.id=b.kid
--右连接（右外连接）
select * from a right join b on a.id=b.kid
select * from b right join a on a.id=b.kid
--全外连接（full join）
select * from a full join b on a.id=b.kid
--自连接 本表与本表连接
create table employee
(
empid int primary key identity,
empname varchar(20) null,
empupid int
)
--查询所有员工的姓名、以及直接领导的姓名
select a.empname 员工姓名,b.empname 上级领导 from employee a inner join employee b on a.empupid=b.empid

--查询员工表中姓名相同的员工的信息
select a.empid,a.empname from employee a inner join employee b on a.empname=b.empname and a.empid<>b.empid

--查询null的值
is null
is not null

 

十. 函数

--a、字符串函数

--1、ascii() :返回一个字符或字符串最左的字符的ascii码 0 48 a 65 a 97
select ascii('a')
select ascii('zhello')

--2、char():将指定ascii码转换为字符
select char(65)
select char(92)

--3、left() ：返回字符串从左开始数 指定数量的字符串
select left('football',4)

--4、right()：返回字符串从右开始数 指定数量的字符串
select right('football',4)
select right('亲爱的',2)

--5、ltrim():去左边的空格
select ltrim(' i love you ')

--6、rtrim():去右边的空格
select rtrim(' i love you ')

select ltrim(rtrim(' i love you ')) --去左右空格
--7、str() :将数值类型转换为字符数据
select str(11314.1134,4,2) --第一个参数 数值 第二个参数是 总的位数（包括小数点） 第三个参数：小数点后保留几位 注意：如果指定位数小于数的实际位数会显示*
select str(1314.1138,9,3)

--8、reverse 字符串逆序
select reverse('abc')
select reverse('张三丰')

--9、len ：计算字符串长度
select len('woaini')
select len('我爱你吗')

--10、substring： 截取字符串 第一个参数：字符串本身 第二个参数从哪开始截取（从1开始）第三个参数：截取几个
select substring('noyes',1,3)
select substring('疾病发生科技发达',1,3)

--11、lower() 变小写
select lower('hello')

--12、upper() 变大写
select upper('hello')

--13、replace 字符串替换 第一个参数：字符串本身 第二个参数 要替换的字符串 第三个参数：替换后的字符串
select replace('i love you','love','hate')

--b、数学函数
--1、abs() 求绝对值
select abs(-8.8) select abs(5)

--2、sqrt() 求平方根 （要求非负数）
select sqrt(2)
select sqrt(3)

--3、rand() 返回一个浮点型的随机数 0-1之间的
select rand()

--4、round() 四舍五入 约等于 第一个参数 ：数值本身 第二个参数：保留有效位数
select round(3.141,2) select round(3.1415926,4)

--c、日期函数
--1、getdate() 获得当前系统日期
select getdate()

--2、day() 获得日期的天
select day('2012-08-31')
select day(getdate())

--3、month() 获得月份的值
select month('2012-08-31')
select month(getdate())

--4、year()获得年份的值
select year('2012-08-31')
select year(getdate())

--d、系统函数
--1、返回数据库的名称 db_name(数据id) 可以用 db_id('数据库名')
select db_name( db_id('class1512neta'))
select db_name(8)

--2、返回登录的用户名 suser_sname()
select suser_sname()
select host_name() 返回主机名

use class1512neta
--3、返回指定字段的长度值 col_length(表，字段)
select col_length('student','stusex')
select * from student
--e、转换函数
--1、cast(x as type)
select cast (108 as varchar(3))
--2、convert(数据类型,要转的值)
select convert(varchar(10),10086)

select convert(varchar(100),getdate())
--f、生成guid （uniqueidentifier 唯一标识符）
--1、newid()
create table student2
(
stuid uniqueidentifier,
stuname varchar(20)
)
insert into student2 values(newid(),'张三')
select * from student2

select newid()

 

十一.存储过程
1.概念 : 存储过程（stored procedure）是在大型数据库系统中，一组为了完成特定功能的sql 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

2.种类:
1系统存储过程
以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。
2本地存储过程
用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。
3临时存储过程
分为两种存储过程：
一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;
二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。
4远程存储过程
在sql server2005中，远程存储过程(remote stored procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和execute命令执行一个远程存储过程。
5扩展存储过程
扩展存储过程(extended stored procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。

3.优点
a.存储过程只编译一次,在创建时编译,提高数据库执行速度
b.避免复杂情况一条一条语句执行多次连接数据库而造成的资源浪费
c.存储过程可以重复使用
d.安全性高,可设定某些用户有制定存储过程的权限

4.定义一个存储过程

--存储过程的关键字是procedure或proc
-- 创建一个简单的存储过程
create proc 存储过程名 -- 注 proc是procedure的简写,可以写成create procedure 存储过程 修改 alter 删除 drop
as
存储过程主体

带输入输出参数的存储过程
create proc 存储过程名称
@title nvarchar(max) not null, -- 参数名 参数类型 约束 参数类别(默认是输入参数)
@info varchar(max) null output, -- output输入输出蚕食
@out varchar(max) out --输出参数
as
begin
sql语句
end

5. 事务
创建一个带事务的存储过程

create proc 存储过程名称
as
begin tran
begin try
--在这里写sql语句
sql语句1
sql语句2
sql语句3
...
end try
begin catch
--当执行存储过程发生异常时,查询异常信息,回滚事务
select error_line() errorline,
error_message(),
error_number()
--判断执行到catch时证明sql语句执行错误,事务回滚
if(@@trancount>10) rollback
end catch
--当sql执行到这里的时候证明sql语句执行完成,提交事务
if(@@trancount>10)
commit

 

(未完待续...)