bilibili视频地址

--------------------------------

--新建数据库

--------------------------------

create database testdb01

on primary  (

name=testdb01,

filename='E:\testdb\testdb01.mdf',

size=10MB,

maxsize=100MB,

filegrowth=5MB

)

log on (        --日志文件

name=testdb01_log,

filename='E:\testdb\testdb01_log.ldf',

size=10MB,

maxsize=100MB,

filegrowth=5MB

)

--------------------------------

--数据库的修改与删除

--------------------------------

--修改数据库名称--

alter database testdb01

modify name = testdb02;

--修改数据库name\size\maxsize\filegrowth--

alter database testdb02

modify file(

name=testdb01,  --虽然testdb01改名叫testdb02，但文件名没变

size=25MB,

maxsize=55MB,

filegrowth=10MB

);

--删除数据库

drop database testdb02

--查看数据库信息--

exec sp_helpdb testdb

--------------------------------

--数据库的备份与还原

--------------------------------

1. 差异备份（只备份与之前数据备份有差异的部分）

2. 完整备份

3. 备份与还原

右键->任务->备份，备份文件为.bak

然后把数据库删掉

右键->任务->还原->选择“设备”，选择之前生成的.bak->确定

4. 数据库分离（不删除数据文件）

右键->任务->分离->确定

“数据库”右键->附加（将分离的加回来）->添加需要附加的数据库

--------------------------------

--数据库的数据类型

--------------------------------

1. 数字类型

bigint        8字节    -2^63 ~2^63-1（过大）

int            4字节    -2^31 ~2^31-1（常用）

smallint    2字节    -2^15 ~2^15-1（过小）

tinyint      1字节                              （过小）

float

2. 时间类型

time                    时分秒          12:35:29.1234567（最大精度到秒后七位）

data                    年月日          2007-05-08

smalldatatime    年月日（空格）时分秒  2007-05-08  12:35:29

datatime                                2007-05-08  12:35:29.123

datatime2                                2007-05-08  12:35:29.1234567

3. 字符串类型

char[(n)]          n是定值，字符串为固定长度。n用于定义字符串长度，必须为1~8000

varchar[(n|max)]  可变长度。n用于定义字符串长度，可以为1~8000（常用）

nchar[(n)]        固定长度。是unicode字符串类型。n必须为1~4000。支持中文，一个占用两个字节

nvarchar[(n|max)]  可变长度。是unicode字符串类型。n可以为1~4000。支持中文，双字节占用一个位置同nchar[(n)] （常用）

MSDN上去了解

--------------------------------

--新建表

--------------------------------

use testdb

create table dbo.userinfo(

ID  int          primary key  not null,  --将ProductID设为主键，不允许为空

name varchar(10)              not null,

age  int                      null

)

use testdb

create table dbo.RulerInfo(  --在数据库testdb中创建表RulerInfo

ID int primary key not null,

name varchar(10) not null,

context varchar(50) null

)

----------------------------------------------------------------

--修改 数据类型、是否允许为空、更改和添加字段名称、删除表

----------------------------------------------------------------

--更改数据长度

use testdb --一定要指定在哪个数据库中修改，不然默认在在第一个数据库中修改

alter table userinfo

alter column name varchar(100)

--更改数据类型

use testdb

alter table userinfo

alter column age float not null

--更改是否允许为空

use testdb

alter table userinfo

alter column age float null

--更改字段名称（注意这里不要写 alter table userinfo ）

use testdb

exec sp_rename 'userinfo.userage','age','column' --exec sp_rename'表名.字段名','更改后的字段名','column'

--添加字段名称

alter table userinfo

add grade varchar(10) not null

--删除表结构

drop table userinfo

--------------------------------

--定义表的 主键、外键

--------------------------------

--增加主键

use testdb

alter table userinfo

add constraint KID primary key (ID)  --KID是主键的名称

--删除主键

use testdb

alter table userinfo

drop constraint KID 

--添加外键(一定是在从表中建立外键)

use testdb

alter table Stu_PkFk_Sc

add constraint F_s

foreign key(number)            --注意在主表Stu_PkFk_S中建立联系的列sno必须设置为主键

references Stu_PkFk_S(sno)

--删除外键

use testdb

alter table Stu_PkFk_Sc

drop constraint FK_s

--------------------------------

--新增表记录

--------------------------------

--插入单行数据

insert into userinfo02(userid, username, email)

values('Shuangj', '霜降', 'Shuangj@qq.com')

--插入多行数据

insert into userinfo02(userid, username, email)

values('Daxue', '大雪', 'Daxue@qq.com'),

('Xiaox', '小雪', 'Xiaox@qq.com');

--从其他表中copy数据

insert into userinfo02(userid, username, email)

select UserID, Name, Email from [dbo].[userinfo]

--------------------------------

--查询表记录

--------------------------------

select *

from [dbo].[userinfo02]  --查看userinfo02表里的数据，[dbo].[userinfo02]是直接从 userinfo02表拖过来的

--查看指定数据

select username, email

from [dbo].[userinfo02] 

--去重

select distinct userid

from [dbo].[userinfo02]

--查询前5行

select top 5 *

from [dbo].[userinfo02]

--------------------------------

--修改表记录

--------------------------------

select * from [dbo].[userinfo02]

where userid = 'Guyu'

update userinfo02

set userid = 'ID'

where ID = 1

--------------------------------

--删除表记录

--------------------------------

delete dbo.userinfo02

where ID = 10

select * from [dbo].[userinfo02]

--------------------------------

--条件限制where

--------------------------------

--精确限制条件  where 字段 = 值

select * from [dbo].[userinfo02]

where ID = '7'

update [dbo].[userinfo02] set userid = 'xiazhi'  --主键不能更新

where ID = '7'

--模糊限制条件  where 字段 like '%值%'

select * from [dbo].[userinfo02]

where username like '小%'

delete [dbo].[userinfo02] --有外键约束的必须先删除外键

where username like '小%'

--------------------------------

--between语法

--------------------------------

select * from [dbo].[userinfo02]

where ID not between 3 and 7  --加上not就是排除这个范围的

select * from [dbo].[userinfo02]    --字符串也可以

where userid between 'guyu' and 'xiaoxue' --包括头尾两值

--时间也可以

select * from [dbo].[userinfo02]

where createtime between  '2016' and getdate()

select getdate() --获取系统当前时间

--------------------------------

--where子查询in

--------------------------------

use SCHOOL

select * from dbo.Students

where Age in (19,20)

select * from dbo.Students

where StudentNo not in (select StudentNo from dbo.Student_Lesson)

--------------------------------------

--where子查询exists

--（返回True、False。主查询得到True后进行显示，得到False不显示）

--------------------------------------

select a.StudentNo, a.StudentName, a.Age, a.Gender from [dbo].[Students] as a  --给表Studentd取个别名叫a

where not exists (select * from [dbo].[Student_Lesson] b where a.StudentNo = b.StudentNo)

--------------------------------------

--返回记录排序[asc升序(默认)，desc降序]

--------------------------------------

use SCHOOL

select * from [dbo].[Student_Lesson]

order by LessonNo asc, StudentNo desc --在LessonNo的升序排序下，再按照StudentNo降序排序

-- 针对时间的排序

use testdb

select * from [dbo].[userinfo02]

order by createtime desc

--------------------------------------

-- 关联查询

--------------------------------------

use SCHOOL

-- inner join返回两个表中联结字段相等的记录

select * from [dbo].[Students] a

inner join [dbo].[Class] b

on a.ClassNo = b.ClassNo

-- left join (左关联) 返回左表中所有的记录和右表中联结字段相等的记录

select * from [dbo].[Students] a

left join [dbo].[Class] b

on a.ClassNo = b.ClassNo

-- right join (右关联)与左关联相反

select * from [dbo].[Students] a

right join [dbo].[Class] b

on a.ClassNo = b.ClassNo

-- 三个表的关联

select a.StudentNo, a.StudentName, c.LessonNo, C.LessonName from [dbo].[Students] a

inner join [dbo].[Student_Lesson] b

on a.StudentNo = b.StudentNo

inner join [dbo].[Lesson] c

on b.LessonNo = c.LessonNo

where a.StudentNo = 1501

--------------------------------------

-- 聚合函数

--------------------------------------

-- avg()（必须为数值型）平均值，NUll不参与计算

select avg(Score) from [dbo].[Score]

-- sum()（必须为数值）总和，NULL不参与计算

select sum(Score) as sum_score from [dbo].[Score] -- 取别名，显示

-- 将两列连起来

select Grade,  ClassName from [dbo].[Class]

select Grade + ClassName from [dbo].[Class]

select Grade + '_' + ClassName from [dbo].[Class]

-- min()（数值型、字符型）NUll不参与计算

select min(Score) as score_min from [dbo].[Score]

select min(Grade) as grade_min from [dbo].[Class] --字符按照字母顺序

-- max()

select max(Score) as score_max from [dbo].[Score]

select max(Grade) as grade_max from [dbo].[Class]

--------------------------------------

-- Count()、Sum()、Len()

--------------------------------------

-- sum()（必须为数值）总和，NULL不参与计算

-- count() 返回组中项数，返回的是整型，忽略NULL

select count(ClassNo) from [dbo].[Students]

-- count_big() 用于数据大小大于（2^23-1）

select count_big(ClassNo) from [dbo].[Students]

-- Len() 返回字符数

select *, len(Grade) as len_grade from [dbo].[Class]

-- Datalength() 返回字节数（一字符等于两字节）

select *, datalength(Grade) as len_grade from [dbo].[Class]

-- (这个返回的好像是包括空格的,varchar(n),值是2n)

--------------------------------------

-- 随机数的产生 rand()

--------------------------------------

select rand() -- 随机数

-- 随机整数

select floor(rand()*10)    -- 0-9

select ceiling(rand()*10)  -- 1-10

--------------------------------------

-- 当前时间的获取 getdate()

--------------------------------------

-- getdate()

select getdate()

-- getutcdate() 当前国际标准时间(用的少)

select getutcdate()

--------------------------------------

-- 时间格式转换convert()

--------------------------------------

-- convert()用不同格式显示时间

查表：

styleID      style格式

100或者0      month date year

110          month-date-year

select getdate()

select convert(varchar(50), getdate(), 111) -- 要转换成的类型(长度一定要给够)，要转换的时间，要转换成的style格式

-------------------------------------------------------------------

-- datediff() 两天之间相差的天数、小时数、分钟数...

-------------------------------------------------------------------

select datediff(SECOND, '2019-05-19 14:25:30', '2019-05-19 14:25:20')

select datediff(MINUTE, '2019-05-19 14:25:30', '2019-05-19 14:58:20')

select datediff(HOUR,  '2019-05-19 14:25:30', '2019-05-19 18:58:20')

select datediff(DAY,  '2019-05-20 ', '2019-05-26')

select datediff(MONTH, '2018-05-20 ', '2019-06-26')

select datediff(YEAR,  '2018-05-20 ', '2019-06-26')

-------------------------------------------------------------------

-- dateadd(datepart, number, date)

-- 在日期中加上天数、小时数、分钟数...

-------------------------------------------------------------------

select dateadd(DAY, 8, '2015-12-01 14:25:20')

select convert (varchar(10),  dateadd(DAY, 8, '2015-12-01'), 111)

-------------------------------------------------------------------

-- datepart() 对日期某部分的获取 （返回int类型）

-- datename() 对日期某部分的获取 （返回varchar类型）

-- day()、month()、year()  取当前日期的日、月、年

-------------------------------------------------------------------

-- datepart()

select datepart( YY, getdate())  -- 年 月 日的缩写 YY MM DD

select datepart( MM, getdate())

select datepart( DD, getdate())

select datepart( HH, '2015-10-25 14:25:20')

-- datename()

select datename(YY, '2015-10-25')

-- day()、month()、year()    没有hour()

select year(getdate())

select year('2015-10-25')

-------------------------------------------------------------------

-- charindex(expression1,expression2[, start_location])

-- 返回字符（串）在另一个字符串中的起始位置（默认从第一位开始找）

-- 没找到返回0

-- patindex() 支持通配符，如：partindex('%BC%', 'ABCD')

-------------------------------------------------------------------

-- charindex()

select charindex('cc', 'access cc', 3)

select charindex('cc', 'abcdefg')

-- patindex()

select patindex('cc', 'aabbccdd')

select patindex('cc', 'cc')          -- 只有前后完全一致时才找得到

select patindex('%cc%', 'aabbccdd')  -- 前后都加通配符的，同charindex()效果一致

select patindex('aa%', 'aabbccdd')

select patindex('%dd', 'aabbccdd')

-- 数据库中应用

use SCHOOL

select *, charindex('文', Remark) as newremark from [dbo].[Lesson]

where LessonNo = 1001

use SCHOOL

select *, patindex('%文%', Remark) as newremark from [dbo].[Lesson] -- 同 charindex()

where LessonNo = 1001

use SCHOOL

select *, patindex('%称', Remark) as newremark from [dbo].[Lesson]

where LessonNo = 1001

-------------------------------------------------------------------

-- stuff(列名，开始位置，长度，代替字符串)

--  删除指定长度字符、在指定起点插入字符

-- 代替字符串不可不写，为空可以写 ''

-------------------------------------------------------------------

select stuff('aabbccdd', 3, 5, '')

select stuff('aabbccdd', 3, 5, 'ee')

-- 数据库中应用

use SCHOOL

select *, stuff(Remark, 5, 3, '**') as newremark from [dbo].[Lesson]

where LessonNo = 1001

-------------------------------------------------------------------

-- substring(expression, start, length)

-- 返回截取指定长度的字符串

-------------------------------------------------------------------

select substring('aabbccdd', 3, 5) as substring

-- 数据库中应用

-- (截取长度超出字符串长度，则截取到末尾，不会补空格)

use SCHOOL

select *, substring(Remark, 5, 20) as newremark from [dbo].[Lesson]

where LessonNo = 1002 

-------------------------------------------------------------------

-- left(被截取的字符串，长度)、 right()

-- 从左、右边开始截取

-------------------------------------------------------------------

select left('aabbccdd', 5) 

select right('aabbccdd', 5) 

-- 数据库中应用

use SCHOOL

select *, left(Remark, 10) as newremark from [dbo].[Lesson]

where LessonNo = 1002 

use SCHOOL

select *, right(Remark, 10) as newremark from [dbo].[Lesson]

where LessonNo = 1002 

-------------------------------------------------------------------

-- 去空格

-- ltrim(expression) 删除左边空格

-- rtrim(expression) 删除右边空格

-------------------------------------------------------------------

select ltrim('      good morning!        ')

select rtrim('      good morning!        ')

select ltrim( rtrim('      good morning!        ') )

-- 数据库中应用

use SCHOOL

select *, ltrim( rtrim(LessonName) ) as newremark from [dbo].[Lesson]

-------------------------------------------------------------------

-- 字符串大小写转换

-- upper(expression) 删除左边空格

-- lower(expression) 删除右边空格

-------------------------------------------------------------------

select upper('Good Morning!')

select lower('Good Morning!')

-- 数据库中应用

use SCHOOL

select *, upper(StudentName) from [dbo].[Students]

select *, lower(StudentName) from [dbo].[Students]

-------------------------------------------------------------------

-- 替换所有的指定字符串

-- replace(被替代的字符串，指定字符串，替换的位置)

-------------------------------------------------------------------

select replace('Hey guys, how are you?', 'guys', '*')

-- 数据库中应用

use SCHOOL

select *, replace(Remark, '学', '*') as newremark from [dbo].[Lesson]

where LessonNo = 1004

-------------------------------------------------------------------

-- 重复字符串

-- replicate(expression, 次数)

-- space(个数) 返回指定个数的空格表达

-------------------------------------------------------------------

select replicate('abc', 5)

select 'A' + space(5) + 'A'

-- 数据库中应用

-- 合并两列，中间加空格

use SCHOOL

select Grade + space(2) + ClassName from [dbo].[Class]

-------------------------------------------------------------------

-- 倒置字符串

-- reverse(expression)

-------------------------------------------------------------------

select reverse('abcdefg')

-- 数据库中应用

use SCHOOL

select reverse(Remark), Remark from [dbo].[Lesson]

-------------------------------------------------------------------

-- 表达式的数据类型转换

-- cast(string_expression as data_type)

-------------------------------------------------------------------

select cast(123 as varchar(10)) -- 数字转换成字符串

select 'abc' + cast( 1 as varchar(5))

select cast(12.5 as int)  -- 浮点数转换成整数

select cast(12.5 as decimal(18, 2))  -- 长度18，2位小数

select cast('2016-01-01' as datetime)  -- varchar 转换成 datetime

-- 数据库中应用

use SCHOOL

select cast(Score as decimal(18, 2)),Score  from [dbo].[Score]

-------------------------------------------------------------------

-- 条件判断转换

-- 有两种格式

-- 简单 case 函数

-- CASE Gender WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END

-- case 搜索函数

-- CASE  WHEN Gender = '1' THEN '男' WHEN Gender = '2' THEN '女' ELSE '其他' END

-------------------------------------------------------------------

-- case 搜索函数

use SCHOOL

select *,

case when Score>=90 then '优'

when Score>=80 and Score<90 then '良'

when Score>=70 and Score<80 then '中'

else '差' end

from [dbo].[Score]

use SCHOOL

select *,

case when Score in (95, 96, 97, 98, 99, 100) then '优'

else '一般' end

from [dbo].[Score]

-- 简单 case 函数

use SCHOOL

select *,

case Gender when '男' then 'man'

else 'woman' end

from [dbo].[Students]