标签：name ScoreInfo -- 练习 SqlServer go id select
/*
创建数据库
创建数据库包含两个文件（数据库文件和日志文件）
*/
use master
go
-- 创建数据库
create database DragonDawson
go
-- 修改数据库字符集
alter database DragonDawson collate Chinese_PRC_CI_AS
go
-- 删除数据库
if db_id('DragonDawson') is not null
drop database DragonDawson
go
-- 查看数据库
select name from sys.databases
go
-- 创建表
use DragonDawson
go
create table t01(
tid int identity(1,1) NOT NULL,
tname varchar(12) not null,
tcourse varchar(25) not null,
tscore float not null
)
go
-- 查询表
select * from sysobjects
where name = 't01'
go
-- 删除表
if exists (select * from sysobjects where name= 't01')
drop table t01
go
-- 查看表
select name from sysobjects
go
-- 创建表
use DragonDawson
go
create table ScoreInfo(
id int identity(1001,1) not null,
name varchar(50) null,
course varchar(50) not null,
score float not null
)
go
select name from sysobjects
go
-- 查看表结构
sp_help ScoreInfo
go
--插入数据
insert into ScoreInfo([name], course, score) values ('张三','高级班', 80)
insert into ScoreInfo(name, course, score) values ('张三','架构班', 52)
insert into ScoreInfo(name, course, score) values ('张三','上位机班', 150)
insert into ScoreInfo(name, course, score) values ('李四','高级班', 44)
insert into ScoreInfo(name, course, score) values ('李四','数学', 111)
insert into ScoreInfo(name, course, score) values ('李四','英语', 110)
insert into ScoreInfo(name, course, score) values ('王五','高级班', 140)
insert into ScoreInfo(name, course, score) values ('王五','数学', 80)
insert into ScoreInfo(name, course, score) values ('王五','英语', 92)
insert into ScoreInfo(name, course, score) values ('王五','物理', 77)
insert into ScoreInfo(name, course, score) values ('王五','化学', 65)
-- 关键字处理 利用 【】
-- 修改表
update ScoreInfo
set [name] = '奥特曼',
course = '格斗术'
where id = 1001
go
select * from ScoreInfo
go
-- 查询当前语言
select @@language
go
-- 删除表
drop table ScoreInfo
go
sp_help ScoreInfo
go
select [name] '姓名' from
ScoreInfo
go
select * from ScoreInfo
go
select [name] '姓名',score '分值'
from ScoreInfo
where score >90
go
select [name] '姓名'
from ScoreInfo
where score between 60 and 80
/*
注意： between --- and 必须从小到大的区间
*/
-- 取前几条数据
select top 5 name '姓名', score '分数'
from ScoreInfo
--查询全部
select * from ScoreInfo
-- 百分比取前几条数据
select top 50 percent *
from ScoreInfo order by score desc
-- case when 判断
select * ,case when score < 90 then '不及格'
when score between 90 and 120 then '及格'
when score between 120 and 130 then '良好'
when score > 130 then '优秀'
else 'low'
end '等级'
from ScoreInfo order by score desc
go
-- case when 判断2
select *,case when course = '数学' then '找数学老师'
when course = '英语' then '找外教老师'
when course = '格斗术' then '暂时没有这个老师'
when course = '化学' then '找方老师'
else '还没请到'
end '对应老师'
from ScoreInfo order by course asc
go
-- in的使用 注意：这里不是区间，而是固定的某个值
select * from ScoreInfo
where score in(80,77)
go
select * from ScoreInfo
-- 模糊匹配查询
select * from ScoreInfo
where course like '%学%'
update ScoreInfo
set [name] = '钱四'
where id = 1008
select * from ScoreInfo
where [name] like '_四'
-- with关键字 查询 作用：相当于把一组已经查询到的数据定义为一张新的表并赋值给某个变量
with four as (
select * from ScoreInfo
where [name] like '_四'
)
select *,case when score>90 then '及格'
else '没及格'
end '过线'
from four order by score desc
-- exists关键字 相对于 in 的性能会更好
select * from ScoreInfo t0 where
exists(
select * from ScoreInfo t2 where t0.id = t2.id and t2.name = '张三'
)
select * from ScoreInfo t2 where t2.name = '张三'
-- 复制表
select * from ScoreInfo
select * into ScoreInfo2 from ScoreInfo
select * from sysobjects
sp_help ScoreInfo2
select * from ScoreInfo2
-- 去重
select distinct([name]) from ScoreInfo2
--查询指定数据库表名
select * from INFORMATION_SCHEMA.tables where TABLE_CATALOG = 'DragonDawson'
-- 聚合查询
select * from ScoreInfo2
select [name],sum(score) '总分',avg(score) '平均分',
case when avg(score) >80 then '过线'
else '未过线'
end '是否过线'
from ScoreInfo2
group by [name]
-- 分页查询
/*
原理：
默认取前pageSize条数据
当页数达到第二页时
直接排除第一页数据，取接下来所有数据的前pageSize条数据
*/
select * from ScoreInfo2
declare @pagesize int;
select @pagesize =3;
declare @pageindex int
select @pageindex = 2;
select top(@pagesize) * from ScoreInfo2
where id not in(
select top (@pagesize*(@pageindex-1)) id from ScoreInfo2 order by id
)order by id
/*
union/union all操作
可以把查询到的多个数据结构完全相同个表，合并起来
union：自动去重 union all 不会去掉重复
*/
select
id,
[name],
course,
score
FROM ScoreInfo where [name] ='张三' union all
select
id,
[name],
course,
score
from ScoreInfo2 where [name] = '张三'
/*
多表联查
*/
select * from ScoreInfo2
-- 开启SQL92标准
set ansi_nulls on
-- 开启关键字不冲突
set quoted_identifier on
create table companey(
id int identity(1,1) not for replication not null primary key,
[name] varchar(50) null,
createtime datetime null,
createid int not null,
lastmodifierid int null,
lastmodifytime datetime null,
)
sp_help companey
use DragonDawson
go
-- 开启允许插入
set identity_insert companey on
insert companey ([id], [name], [createtime], [createid], [lastmodifierid], [lastmodifytime])
values (1, '朝夕教育', convert(datetime,'2021-10-09 16:37:14.717',20), 1, 1, convert(datetime,'2021-10-09 16:37:14.717',20))
go
insert [companey] ([id], [name], [createtime], [createid], [lastmodifierid], [lastmodifytime])
values (2, '阿里巴巴', cast('2021-10-09T16:37:14.717' as datetime), 1, 1, cast('2021-10-09T16:37:14.717' as datetime))
go
insert [companey] ([id], [name], [createtime], [createid], [lastmodifierid], [lastmodifytime])
values (3, '百度', cast('2021-10-09T16:37:14.717' as datetime), 1, 1, cast('2021-10-09T16:37:14.717'as datetime))
go
insert [companey] ([id], [name], [createtime], [createid], [lastmodifierid], [lastmodifytime])
values (4, '腾讯', cast('2021-10-09T16:37:14.717' as datetime), 1, 1, cast('2021-10-09T16:37:14.717'as datetime))
go
set identity_insert [dbo].[company] off
set ansi_nulls on
go
set quoted_identifier on
go
drop table sysuser
use DragonDawson
create table [sysuser](
[id] [int] identity(1,1) not for replication not null,
[name] [varchar](50) null,
[companyid] [int] null,
[password] [varchar](50) null,
[status] [int] not null,
[phone] [varchar](12) null,
[mobile] [varchar](12) null,
[address] [varchar](500) null,
[email] [varchar](50) null,
[qq] [bigint] null,
[wechat] [varchar](50) null,
[sex] [int] null,
[lastlogintime] [datetime] null,
[createtime] [datetime] null,
[createid] [int] null,
[lastmodifytime] [datetime] null,
[lastmodifyid] [int] null
constraint [pk_sysuser] primary key clustered
(
[id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,
allow_page_locks = on, optimize_for_sequential_key = off) on [primary]
) on [primary]
go
set identity_insert companey off
set identity_insert sysuser on
insert [sysuser] ([id], [name], [password], [status], [phone], [mobile], [address], [email],
[qq], [wechat], [sex], [lastlogintime], [createtime], [createid], [lastmodifytime], [lastmodifyid],
[companyid]) values (1, '张三', '123456', 1, '18672713698', '18672713698', '武汉市','[email protected]', 123456789, null, 0, convert(datetime,'2020-12-15 00:00:00.000',20), convert(datetime,'2020-12-15 00:00:00.000',20), 1, convert(datetime,'2020-12-15 00:00:00.000',20), 1, 1)
go
insert [sysuser] ([id], [name], [password], [status], [phone], [mobile], [address], [email],
[qq], [wechat], [sex], [lastlogintime], [createtime], [createid], [lastmodifytime], [lastmodifyid],
[companyid]) values (3, '李四', '123456', 1, '18672713698', '18672713698', '武汉市','[email protected]', 123456789, null, 0, cast('2020-12-15T00:00:00.000' as datetime), cast('2020-12-15T00:00:00.000' as datetime), 1, cast('2020-12-15T00:00:00.000' as datetime), 1, 1)
go
insert [sysuser] ([id], [name], [password], [status], [phone], [mobile], [address], [email],
[qq], [wechat], [sex], [lastlogintime], [createtime], [createid], [lastmodifytime], [lastmodifyid],
[companyid]) values (5, '王五', 'pass', 1, 'phone', '18672713698', 'abcc', '', 123456798,'wechat', 1, cast('2021-10-09T15:03:43.673' as datetime), cast('2021-10-09T15:03:43.673' as datetime), 1, cast('2021-10-09T15:03:43.677' as datetime), 1, 1)
go
insert [sysuser] ([id], [name], [password], [status], [phone], [mobile], [address], [email],
[qq], [wechat], [sex], [lastlogintime], [createtime], [createid], [lastmodifytime], [lastmodifyid],[companyid]) values (6, '赵六', 'pass', 1, 'phone', '18672713698', 'abcc', '', 123456798,'wechat', 1, cast('2021-10-09T15:11:06.473' as datetime), cast('2021-10-09T15:11:06.473' as datetime), 1, cast('2021-10-09T15:11:06.473' as datetime), 1, 1)
go
insert [sysuser] ([id], [name], [password], [status], [phone], [mobile], [address], [email],
[qq], [wechat], [sex], [lastlogintime], [createtime], [createid], [lastmodifytime], [lastmodifyid],
[companyid]) values (7, '添加', 'pass', 1, 'phone', '18672713698', 'abcc', '', 123456798,'wechat', 1, cast('2021-10-09T16:01:45.010' as datetime), cast('2021-10-09T16:01:45.010' as datetime), 1, cast('2021-10-09T16:01:45.010' as datetime), 1, 1)
go
SET IDENTITY_INSERT sysuser OFF
select * from companey
update companey set createtime = cast('2021-10-09T16:01:45.010' as datetime),lastmodifytime = cast('2021-10-09T16:01:45.010' as datetime)
where id =1
select * from sysuser
select * from companey
-- 左连接
select * from companey c left join sysuser u on c.id = u.id
-- 内连接
select * from companey c inner join sysuser s on c.id = s.companyid
-- 递归查询
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MenueInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[MenuName] [varchar](40) NULL,
[ParentId] [int] NULL,
CONSTRAINT [PK_MenueInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[RowColumnConversion] Script Date: 2022/8/5 10:38:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET IDENTITY_INSERT [dbo].[MenueInfo] ON
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (1, N'一级菜单', NULL)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (2, N'一级-二级菜单-1', 1)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (3, N'一级-二级菜单-2', 1)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (4, N'一级-二级菜单-3', 1)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (5, N'一级-二级菜单-1-三级菜单-1', 2)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (6, N'一级-二级菜单-1-三级菜单-2', 2)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (7, N'一级-二级菜单-1-三级菜单-3', 2)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (8, N'一级-二级菜单-2-三级菜单-1', 3)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (9, N'一级-二级菜单-2-三级菜单-2', 3)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (10, N'一级-二级菜单-2-三级菜单-3', 3)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (11, N'一级-二级菜单-3-三级菜单-1', 4)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (12, N'一级-二级菜单-3-三级菜单-2', 4)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (13, N'一级-二级菜单-3-三级菜单-3', 4)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (14, N'一级-二级菜单-3-三级菜单-1-四级菜
单-1', 11)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (15, N'一级-二级菜单-3-三级菜单-1-四级菜
单-2', 11)
GO
INSERT [dbo].[MenueInfo] ([Id], [MenuName], [ParentId]) VALUES (16, N'一级-二级菜单-3-三级菜单-1-四级菜
单-3', 11)
GO
SET IDENTITY_INSERT [dbo].[MenueInfo] OFF
select * from menueinfo
with con(id,menuname,parentid,le) as(
select id,menuname,parentid,le=1 from MenueInfo where id=4
union all
select m.id,m.menuname,m.parentid,le=le+1 from MenueInfo m inner join con on m.parentid=con.id
)
select id,menuname,parentid,le from con
-- 视图的使用
create view todoList as select * from MenueInfo where MenuName like '%二%'
select * from todoList
/*
数据类型：
整数类：
bigint
int
smallint
tinyint
浮点类：
money
smallmoney
注意若有第5位，则做四舍五入
float
real
时间类：
date YYYY-MM-DD
datetime YYYY-MM-DD hh:mm:ss
字符类：
char
varchar
以下两个类型每个字符占两个字节
nchar
nvarchar
*/
-- T-SQL的使用
-- 定义变量
declare @dragon varchar(25)
select @dragon = 'hello sql server'
select @dragon -- 映射到结果集
print @dragon -- 打印到消息框
declare @num0 int
set @num0 = 1
while @num0<10
begin
if @num0%2 =0
print @num0
else
print N'奇数'
select @num0 = @num0+1
end
/*
查询当前数据库中的所有表
*/
use DragonDawson
exec sp_tables
/*
存储过程
*/
/*
基本操作
*/
drop proc if exists pagerInfo
drop proc if exists selectTable
go
create proc selectTable
@TableName LocationTableType READONLY
as
begin
declare @getInfo varchar(255)
declare @sqlList varchar(255)
set @sqlList = 'select * from '+@TableName
exec(@sqlList)
end
go
exec selectTable 'a'
go
/*
酒店管理
*/
create database HotelManagement
alter database HotelManagement collate Chinese_PRC_CI_AS
go
use HotelManagement
if exists (select name from sysobjects where [name] = 'HotelManagement')
drop table HotelManagement
drop table if exists department
create table department(
de_id int primary key identity(1001,1),
de_name char(20) not null
)
insert into department(de_name) values('前台')
go
insert into department(de_name) values('保洁')
go
insert into department(de_name) values('管理')
go
insert into department(de_name) values('服务')
go
insert into department(de_name) values('餐厅')
go
drop table if exists staff
create table staff(
s_id int primary key identity(1001,1),
s_name varchar(12) not null,
de_id int references department
)
insert into staff(s_name,de_id) values('张三',1005)
insert into staff(s_name,de_id) values('逍遥',1001)
insert into staff(s_name,de_id) values('盖聂',1002)
insert into staff(s_name,de_id) values('卫庄',1003)
insert into staff(s_name,de_id) values('白凤',1004)
insert into staff(s_name,de_id) values('天明',1004)
insert into staff(s_name,de_id) values('小兰',1004)
insert into staff(s_name,de_id) values('高启强',1004)
insert into staff(s_name,de_id) values('高企肾',1002)
insert into staff(s_name,de_id) values('安欣',1002)
insert into staff(s_name,de_id) values('李一桐',1002)
select s_id '员工id',s_name '员工姓名',de_name '部门' from staff s inner join department d on s.de_id=d.de_id
order by d.de_name desc
select count(de_name) '员工数量',d.de_name '部门名称' from staff s inner join department d on s.de_id = d.de_id
group by de_name
use HotelManagement
select * from staff
update staff
set s_name=' 张三 老铁 '
where s_name='高企肾'
update staff
set s_name = RTRIM(s_name)
with subName as (
select s_name from staff
)
-- substring 使用
select SUBSTRING(s_name,1,2) from staff
select REPLACE(s_name,' ','') from staff
标签：name,
ScoreInfo,
--,
练习,
SqlServer,
go,
id,
select
From： https://www.cnblogs.com/Z-Dragon/p/17228686.html