****SQL语句不区分大小写
单行注释 - -
多行注释/ **/
ASCII 码中,一个英文字母(不分大小写)为一个字节,一个中文汉字为两个字节。
UTF-8 编码中,一个英文字为一个字节,一个中文为三个字节。如果为 String(100),则表示对于此字段,如果字段值全部为中文汉字,只能保存 33 个单位。
Unicode 编码中,一个英文为一个字节,一个中文为两个字节。
符号:英文标点为一个字节,中文标点为两个字节。例如:英文句号 . 占1个字节的大小,中文句号 。占2个字节的大小。
UTF-16 编码中,一个英文字母字符或一个汉字字符存储都需要 2 个字节(Unicode 扩展区的一些汉字存储需要 4 个字节)。
UTF-32 编码中,世界上任何字符的存储都需要 4 个字节
nvarchar(50),容纳50个字节(纯中文,纯字母或数字都50个),
varchar(50),容纳50个字节(纯中文25个字,纯字母或数字50个),一个中文占用两个字节,一个字母或数字占一个字节
声明变量 DECLARE @变量名 变量类型
DECLARE @subSysNo int
一、数据查询语言:select、from、where
sqlserver字符串用单引号’’
(一)查询语句
SELECT COL1, COL2,…, COLn FROM TABLE1, TABLE2,…,TABLEn
[WHERE CONDITIONS]–查询条件
[GROUP BY GROUP_BY_LIST]–查询结果分组
[HAVING CONDITIONS]–查询条件-统计结果作为条件
[ORDER BY ORDER_LIST[ASC|DESC]]–查询结果排序
(二)简单查询
1.查询表的全部行和列
select * from 表名
2.查询表的部分列
select 列名1,列名2…列名n from 表名
3.别名的使用(查询结果中列名会以使用的别名显示出来)
select 列名1 as ’ ‘,列名2 as’ 'from表名,as可省略
select [number] as 序号,[Name]as 姓名 from [System].[dbo].[scription]
3.查询空行
select name from a where email is null
说明:查询表a中email为空的所有行,并显示name列;SQL语句中用is null或者is not null来判断是否为空行
4.DISTINCT关键字-消除结果集中的重复行,它确保查询结果中的每一行都是唯一的
select distinct 列名 from 表名
select distinct [Name]from[System].[dbo].[scription]
查询结果等价于
select [Name]from[System].[dbo].[scription] group by [Name]
5.TOP关键字-指定结果集中数据的显示范围
select TOP n (列名)from 表名 -只显示前n条数据
select top 10[Name] from [System].[dbo].[scription]
6.order by–对查询结果进行排序,asc升序,desc降序,默认升序
select * from [System].[dbo].[Method] order by time asc
select * from [System].[dbo].[Method]where time=0 order by time desc
7.group by可以根据某一列进行分组查询, 若使用 GROUP BY ,select中选择列必须包含在聚合函数或 GROUP BY 子句中
select 【*/字段名,…】 from 【表名/查询结果集】
【where 查询条件】
group by 分组条件;
select studentID as 学员编号, AVG(score) as 平均成绩 --这里的score是列名)
from score --这里的score是表名)
group by studentID
select age, count(*) count_age from [System].[dbo].[scription]where age between 1 and 50 --筛选年龄1-50岁,`统计各年龄个数
group by age--按照年龄分组
order by count_age--统计年龄个数的排序
8.Having – 过滤条件查询。使用having子句进行分组筛选
HAVING 语法与 WHERE 语法类似。WHERE 搜索条件在进行分组操作之前应用;而 HAVING 搜索条件在进行分组操作之后应用。HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。
select studentID as 学员编号, AVG
from score
group by studentID
having count(score)>1
9.IN关键字
使用IN关键字在列举值内进行查询(in后是多个的数据)。not in 不包含,将条件值用逗号分隔,放在后面的括号中
delete from [System].[dbo].[Template]where id In(17,28,35)
select * from [System].[dbo].[Template]where id not In(17)
select top 8 *from [System].[dbo].[scription] WHERE Age in(20,18,35)--where条件后的前八个
(三)条件查询
1.where–普通条件查询
select 列名 from 表名 where 查询条件
select * from [System].[dbo].[Method] where time =10
select * from [System].[dbo].[Method] where time >10
–大于,<–小于,<>–不等于,>=–大于等于,<=–小于等于
select * from [System].[dbo].[Method] where time =10 and timepost =0
and–并且,or–或者,not–非
2.模糊查询
select 列名 from 表名 where 列名 between xx and xx (between and 是否包含边界值需要看使用的sql工具,有的包含两个边界值,有的只包含一个,not between and 包含边界值)
select * from [System].[dbo].[scription] where time between '2023-08-07' and '2023-08-23'
select userid from User --1 2 3 4 5
select userid from User where userid between 2 and 4 --2 3 4
select userid from User where userid not between 2 and 4 --1 5
通配符lik
‘’ 一个字符 like '李’
% 任意长度 like’李%’
[] 指定范围内
select * from [System].[dbo].[scription] where Name like '李%'
select * from [System].[dbo].[scription] where Name not like '李%'
select *from [System].[dbo].[Json]where jsondata like '%202308111947%'
3.查询空值运算符
select * from [System].[dbo].[scription] where [Address] is null
select * from [System].[dbo].[scription] where [Address] is not null
4.查询数据库中所有表
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY TABLE_NAME
5.查询表中所有字段信息
USE 数据库名
SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='表名'
identity 自增长
二、数据定义语言:create、alter、drop、truncate
创建数据库及其对象
create/alter/drop database/table/view/proc/index
(一)CREATE DATABASE - 创建数据库
1.select * from sysdatabases where name='cslog'--查询数据库文件是否存在 cslog为数据库名
2.CREATE DATABASE StudentDB--创建数据库
3.DROP DATABASE StudentDB--删除数据库
4.示例开始
use Informition --使用数据库
go --如果多条语句要一起执行,那么在每条语句之后需要加go关键字
create table studentinfo --创建表
(
userid varchar(50) primary key,
name varchar(50) not null,
age int,
phone int
)
go
create table teacherinfo
(
teacherid int primary key, --primary key主键,一个表只能出现一次;unique 唯一键 可以出现多次
teachername varchar(50) not null unique, --not null 非空且唯一
age int,
email varchar(50),
student varchar(50)
)
go
create table course
(
couserid int primary key,
coursename varchar(50) not null,
beixuan varchar(50) not null references studentinfo(userid) --references 外键关系(引用),创建有外键关系表的时候要先创建主表,再创建从表,外键可以引用另一张二维表上对应的主键
)
示例结束
5.修改表中的列信息
ALTER table studentinfo ALTER column age int NOT NULL --修改age列为非空
ALTER table studentinfo ADD unique (age); --增加唯一约束
6.指定表中增加列
alter table 表名 add 列名 数据类型
alter table studentinfo add city varchar(255) , age int not null
7.修改视图
alter view 视图名称 as 查询语句 //逻辑是先删除再创建
三、数据操纵语言 insert update delete
1.指定表中指定列插入数据,形成新行(非空字段必须写)
insert [into] 表名(字段名(列名),…) values(值,…);–into可省略
insert into studentinfo(userid,name,age)values(001,'张三',18)
2.指定表中插入新的行(所有非空字段必须写)
insert into studentinfo values(002,'李四',19,11111111)
3.更新update–用于修改表中的数据
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值;
UPDATE studentinfo SET phone = 2222222 WHERE userid = 1; --SET后边是要更新的内容,WHERE后边是条件。修改userid为1的手机号为22222
update[Informition].[dbo].[studentinfo] set sex='女', city ='北京' where userid=2
4.DELETE删除
DELETE FROM 表名称 WHERE 列名称 = 值;(删除的为整行)
delete from [Informition].[dbo].[studentinfo] where userid=1
四、约束
sql server约束
在SQL Server中,有几种类型的约束,包括:
1.PRIMARY KEY 约束:用于唯一标识表中的每一条记录,不能有重复值,不能有NULL值。
ALTER TABLE TableName
ADD CONSTRAINT PK_TableName PRIMARY KEY (ColumnName);
2.FOREIGN KEY 约束:用于与其他表的主键值相关联。
ALTER TABLE TableName
ADD CONSTRAINT FK_TableName FOREIGN KEY (ColumnName) REFERENCES ParentTableName(ParentColumnName);
3.UNIQUE 约束:确保列中的所有值都是唯一的。
ALTER TABLE TableName
ADD CONSTRAINT UQ_TableName UNIQUE (ColumnName);
4.DEFAULT 约束:为列设置默认值。
ALTER TABLE TableName
ADD CONSTRAINT DF_TableName DEFAULT 'default_value' FOR ColumnName;
5.CHECK 约束:确保列中的值符合指定的条件。
ALTER TABLE TableName
ADD CONSTRAINT CK_TableName CHECK (ColumnName > 0);
6.NOT NULL 约束:确保列中的所有值都不为NULL。
ALTER TABLE TableName
ADD CONSTRAINT NN_TableName NOT NULL (ColumnName);
五、其他
1.查询某一字段的重复数据
select * from 数据表 where 重复记录字段 in ( select 重复记录字段 from 数据表 group by 重复记录字段 having count(重复记录字段)>1)
select * from EncryInfo where Content in ( select Content from EncryInfo group by Content having count(Content)>1)
2.like使用
like%%可查出任意字符串,包括空字符串,但查不出NULL值
like 如需匹配全部,需加上OR 字段名 IS null
SELECT * FROM 表名 WHERE 字段名 LIKE ‘%%’ OR DelNum IS null
SELECT TOP 3 DelNum FROM [scription]
SELECT TOP 3 DelNum FROM [dbo].[scription] WHERE DelNum LIKE '%%'
SELECT TOP 3 DelNum FROM [dbo].[scription] WHERE DelNum LIKE '%%' OR DelNum IS NULL
3.EXEC 执行存储过程
EXEC 存储过程名 参数
4.case when then 的使用
case when then 结构有两种格式,分别是 确定条件列 和 不确定条件列。
(一)形态一:CASE WHEN 表达式 THEN 结果
(二)形态二:CASE 变量 WHEN 值 THEN 结果
①
case 列
when 值1 then 返回值1
when 值2 then 返回值2
else 默认返回值
end 列名
这种方式默认每个when 后面的条件是 ‘列=值’
②
case
when 条件1 then 返回值1
when 条件2 then 返回值2
else 默认返回值
end 列名
这种方式,条件 可以是该行记录任何情况的条件,只要返回布尔值就行。
select *,
case score
when 100 then '满分'
else score
end '分数'
from stucores ;
select *,
case
when score=100 then '满分'
when score<100 and score>=60 then '及格'
when score<60 then '不及格'
end '评级'
from stucores ;
五、函数
1.CONVERT 函数:
用于将一个数据类型转换为另一个数据类型。允许在查询中对数据进行显式的转换和格式化。
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
data_type:要将表达式转换为的目标数据类型。
length(可选):目标数据类型的长度或精度。对于某些数据类型,可以指定长度
expression:要进行转换的表达式或列。
style(可选):在某些数据类型转换中,可以指定样式选项。样式用于指定日期、时间或数字数据的格式
style,此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)相互转换的时候才用到。
年份可以显示为两位或四位数。默认情况下,SQL Server 将年份显示为两位数。若要显示包括世纪的四位数年份 (yyyy)(即 使年份数据是使用两位数的年份格式存储的),请给 style 值加 100 以获得四位数的年份。
--当前时间2024-03-06 14:47:42.120
SELECT CONVERT(CHAR(25), GETDATE(), 0) --03 6 2024 2:45PM
SELECT CONVERT(CHAR(25), GETDATE(), 100) -- 03 6 2024 2:45PM
SELECT CONVERT(CHAR(25), GETDATE(), 1) --03/06/24
SELECT CONVERT(CHAR(25), GETDATE(), 101) --03/06/2024
SELECT CONVERT(CHAR(18), GETDATE(), 2) --24.03.06
SELECT CONVERT(CHAR(18), GETDATE(), 102) --2024.03.06
SELECT CONVERT(CHAR(25), GETDATE(), 3) --06/03/24
SELECT CONVERT(CHAR(25), GETDATE(), 103) --06/03/2024
SELECT CONVERT(CHAR(25), GETDATE(), 4) --06.03.24
SELECT CONVERT(CHAR(25), GETDATE(), 104) --06.03.2024
SELECT CONVERT(CHAR(25), GETDATE(), 5) --06-03-24
SELECT CONVERT(CHAR(25), GETDATE(), 105) --06-03-2024
SELECT CONVERT(CHAR(25), GETDATE(), 6) --06 03 24
SELECT CONVERT(CHAR(25), GETDATE(), 106) -- 06 03 2024
SELECT CONVERT(CHAR(25), GETDATE(), 7) --03 06, 24
SELECT CONVERT(CHAR(25), GETDATE(), 107) --03 06, 2024
SELECT CONVERT(CHAR(25), GETDATE(), 8) --14:45:59
SELECT CONVERT(CHAR(25), GETDATE(), 108) -- 14:45:59
SELECT CONVERT(CHAR(25), GETDATE(), 9) --03 6 2024 2:45:59:427PM
SELECT CONVERT(CHAR(25), GETDATE(), 109) --03 6 2024 2:45:59:427PM
SELECT CONVERT(CHAR(25), GETDATE(), 10) -- 03-06-24
SELECT CONVERT(CHAR(25), GETDATE(), 110) -- 03-06-2024
SELECT CONVERT(CHAR(25), GETDATE(), 11) --24/03/06
SELECT CONVERT(CHAR(25), GETDATE(), 111) --2024/03/06
SELECT CONVERT(CHAR(18), GETDATE(), 12) --240306
SELECT CONVERT(CHAR(18), GETDATE(), 112) --20240306
SELECT CONVERT(CHAR(25), GETDATE(), 13) --06 03 2024 14:46:57:097
SELECT CONVERT(CHAR(25), GETDATE(), 113) -- 06 03 2024 14:46:57:097
SELECT CONVERT(CHAR(25), GETDATE(), 14) --14:46:57:097
SELECT CONVERT(CHAR(25), GETDATE(), 114) --14:46:57:097
SELECT CONVERT(CHAR(25), GETDATE(), 20) --2024-03-06 14:46:57
SELECT CONVERT(CHAR(25), GETDATE(), 120) --2024-03-06 14:46:57;
SELECT CONVERT(CHAR(25), GETDATE(), 21) --2024-03-06 14:47:42.120
SELECT CONVERT(CHAR(25), GETDATE(), 121) --2024-03-06 14:47:42.120
--+100后无对应的
SELECT CONVERT(CHAR(25), GETDATE(), 22) --03/06/24 2:47:42 PM
SELECT CONVERT(CHAR(25), GETDATE(), 23) --2024-03-06
SELECT CONVERT(CHAR(25), GETDATE(), 24) --14:47:42
SELECT CONVERT(CHAR(25), GETDATE(), 25) --2024-03-06 14:47:42.120
--100后无对应的
SELECT CONVERT(CHAR(25), GETDATE(), 126) -- 2024-03-06T15:02:02.350
SELECT CONVERT(CHAR(25), GETDATE(), 130) --26 ????? 1445 3:02:02:35
SELECT CONVERT(CHAR(25), GETDATE(), 131) --26/08/1445 3:02:02:350PM
示例:
–time格式为2024-03-05 15:52:17.387
–情况一:点击查询传时间格式2023-08-07,需做转换后可查
SELECT TOP 10 [time] FROM [ption] WHERE CONVERT(CHAR(10), time, 120) BETWEEN '2023-08-07' AND '2023-08-07'
–情况二:点击查询传时间格式2023-08-07,不做转换查不到数据
SELECT TOP 10 [time] FROM [ption] WHERE time BETWEEN '2023-08-07' AND '2023-08-07'
–情况三:点击查询传时间格式2024-03-06 03:00:00 做转换查不到数据
SELECT TOP 10 [time] FROM [ption] WHERE CONVERT(CHAR(10), time, 120) BETWEEN '2023-08-07 00:00:00' AND '2023-08-07 23:59:59'
–情况四:点击查询传时间格式2024-03-06 03:00:00 不做转换可查
SELECT TOP 10 [time] FROM [ption] WHERE time BETWEEN '2023-08-07 00:00:00' AND '2023-08-07 23:59:59`'
情况五:打开页面,默认传时间格式为 2023-08-07 2023-08-07 23:59:59,可查当日数据
SELECT TOP 10[time] FROM [ption] WHERE time BETWEEN '2023-08-07' AND '2023-08-07 23:59:59'
2.NEWID()函数
是一个内置函数,生成一个唯一标识符(GUID),该算法基于计算机的硬件和操作系统信息,以及当前的时间戳等因素
SELECT NEWID()
3.datediff ()函数
主要用来求一段时间内的信息
DateDiff (interval,date1,date2) 以interval 指定的方式,返回date2 与date1两个日期之间的差值 date2-date1
select datediff(day,'2004-09-01','2004-09-18') --返回:17