金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > Server数据库学习,旺旺老师笔记

Server数据库学习,旺旺老师笔记

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-09 19:48

前言

1、什么是数据库?数据库特点?

一,认识SQL数据库

1.默认约束
2.主键约束,(唯一约束+非空约束),
设置了主键的字段在赋值的时候不能重复,stuID为主键则1,下一个就不能为1
3.唯一约束(允许一行为空),
4.check约束
5.主外建约束 (主表 子表)一对多

本文是个人学习SQL Server 数据库时的以往笔记的整理,内容主要是对数据库的基本增删改查的SQL语句操作约束,视图,存储过程,触发器的基本了解。

数据库就是存放数据的仓库

  美国Microsoft公司推出的一种关系型数据库系统。SQLServer是一个可扩展的、高性能的、为分布式客户机/服务器计算所设计的数据库管理系统,实现了与WindowsNT的有机结合,提供了基于事务的企业级信息管理系统方案。

添加完整性约束
外键约束:
学生基本信息表(主表)的学生编号是主键
考试成绩表的学生(子表)的编号是外键
一个表在不同的关系中既可以是主表又可以是子表
主外建是一对多

注:内容比较基础,适合入门者对SQL Server 数据库的了解!!!

海量存储、查找速度快、并发性问题控制、安全性、数据完整性(保存在数据库中的数据是正确的,真是的)

     其主要特点如下:
(1)高性能设计,可充分利用WindowsNT的优势。
(2)系统管理先进,支持Windows图形化管理工具,支持本地和远程的系统管理和配置。
(3)强壮的事务处理功能,采用各种方法保证数据的完整性。
(4)支持对称多处理器结构、存储过程、ODBC,并具有自主的SQL语言。 SQLServer以其内置的数据复制功能、强大的管理工具、与Internet的紧密集成和开放的系统结构为广大的用户、开发人员和系统集成商提供了一个出众的数据库平台

”数据字典“

正文

 

 

哪一个是外键表,就给他添加外建约束
3个一致:1 外键表外键与主键数据类型一致(语法一只)
2 长度:外键>=主键表;长了也没有意义
3 名称一致。

1.主键:

2、主键的作用?选择主键的注意事项?

二,SQL数据库的基本操作

如果sql语句写的错误很多,只需要看第一行和最后一行错误。
如果不多,就仔细看下。

主键的作用:保证表中的每条数据的唯一性
特点: 主键不能重复 不能为空
分类:
逻辑主键:选择为表中增加的那些“自动编号”列或者“GUID”列为主键(没有实际业务上的意义)的主键 (建议使用逻辑主键)
业务主键:选择表中那些在业务中有实际意义的列作为主键
》》》》》》》》》选择主键的策略,选什么样的列作为主键《《《《《《《《《
1》主键,建议选择那些一般不会被修改的列
2》选择单列,不选择多列(不用组合主键)
3》选择那些简单列(整数列(自动编号))

唯一标识表中的一条记录

--打开系统数据库
use master
go
--判断是否已经有这个数据库,如果有就删除
if exists(select 1 from sys.databases where name='StudentDB')
 drop database StudentDB
go
--新建数据库StudentDB
create database StudentDB
on
(
 name='StudentDB',
 filename='F:SQL数据库Student.mdf',
 size=5MB,
 filegrowth=20%
)
log on
(
 name='StudentDB_log',
 filename='F:SQL数据库StdentDB_log.ldf',
 size=5MB,
 filegrowth=20%
)
go

通配符----

 

尽量选择单列作为主键:

--打开StudentDB数据库
use StudentDB
go

select * from employee
-- 插入一条忘记加入的字段 直接在表的后面添上add
--alter table employee add emName varchar(20) not null
alter table employee
add constraint PK_emId primary key (emId)
alter table employee
add constraint CK_age CHECK(age between 20 and 55)
alter table employee
add constraint DF_address default('地址不详') for address
alter table employee
add constraint UQ_emName unique (emName)
--外键表
alter table employee
add constraint FK_emtypeId
foreign key(emtypeId) references mangertype(mantypeId)
标示列可以不是主键,种子和自增量必须是int,标示列不能编辑
先自增,再验证,最后插入
在数据关系图里 带钥匙的一方是主键表

2.char(),nchar(),varchar()之间的区别

     1>必须唯一(不能有重复)

-----------------------创建表-----------------------------------------------------------


》》》》》》》》》char(10)与varchar(10)的区别《《《《《《《《《
char(10) 固定长度,表示在数据库中存储的时候占用10个字节的空间,如果超出10个则报错,如果不够10个则用空格补全。
varchar(10) 可变长度,表示该列最多可以存储10个字节,如果实际存储不够10个字节,则会在存储的时候自动计算一下实际的存储个数,而动态的改变长度。【节省空间】

     2>该列不能为空值

--判断是否已经有这个这个表,如果有就删除
if exists(select 1 from sys.tables where name='Classes')
 drop database Classes
go

插入(insert)
1.匹配,
2.多行数据
备份表 select * into student1 from student
就生成一个新的student1

》》》》》》》》》char(10)与nchar(10)的区别《《《《《《《《《

     3>比较稳定的列(不经常更新的,最好是建好以后再也不更新)

/*
* 表名: 班级表
* 作用: 存放班级数据
* 设计者: ***
* 设计说明:暂无
*/
create table Classes
(
 --班级编号
 ClassNo  varchar(8)  primary key,--主键
 --班级名称
 ClassName nvarchar(10) not null
)
go

select * into student1 from student
select * from student1
--将字段名备份,但是所建的约束消失
select stuNoid,stuName,sex,age,address into student2 from student
select * from student2
--将字段名更改,加as即可
select stuNoid as haha,stuName as gaga,sex as hello,age,address into student3 from student
select * from student3
select * from student1
use wangwang
go
create table employee
(
empId int identity primary key,
empName varchar(20) not null,
sex bit not null,
age int ,
address varchar(20)

char(10) 可以存储10个字母或者5个汉字。 用来存储数据的时候,英文站1个字节,中文站2个字节。

     4>选择主键列的时候,推荐使用“逻辑主键”(例如:自动编号、guid等),不推荐“业务主键”(选择有实际意义的列作为主键(例如:身份证号,工号、学号等。))

--判断是否已经有这个这个表,如果有就删除
if exists(select 1 from sys.tables where name='Students')
 drop database Students
go

)
go
select * from employee
---接下来将student1表中的数值插入到employee中
/*insert into employee
(empId,empName,sex,age,address)
select
(stuNoId,stuName,sex,age,address)
from stdent1
where age>20
*/
----插入多行数据union方法
insert student1
(stuNoId,stuName,sex,age,address)
select--不支持default关键字要用‘’括起来,当两行数据一致时,默认为一行
'1','liuzhao','1','31','default' union
select
'1','liu','1','28','default' union
select
'1','zhao','1','31','default'
select * from student1

nchar(10) 表示可以存储10个字母或10个汉字,因为每个字符都是按照unicode方法来存储的。当使用nchar(10),来存储数据的时候无论存储的是中文还是英文都是每个字符占2个。

 

/*
* 表名: 学生表
* 作用: 存放学员信息
*/
create table Students
(
 --学号
 StudentNo  varchar(10)  not null primary key ,--主键  自增长:identity(为int类型时可以使用)
 --姓名
 StudentName  nvarchar(6)  not null,
 --性别
 StudentGender char(1)   not null,
 --生日
 StudentBirth smalldatetime not null,
 --所在班级
 ClassNo   varchar(8)  not null
)
go

select * from student1 order by age desc(asc)

模糊查询
alter table student add groupId int
select * from student
insert student
(stuNoId,stuName,sex,age,address)
select
'5','zhaoliu','1','30','南昌路' union
select
'6','zhao','1','31','南路' union
select
'7','liu','1','32','昌路'

---模糊查询
select * from student where address like '%南%'
select * from student where stuName like 'wangwu[2-4]'
select * from student where stuName like 'wangwu[^2-4]%'--取反
--加上% 可以将wangwua查出来

 

3、下表,分析为什么要有外键?

--判断是否已经有这个这个表,如果有就删除
if exists(select 1 from sys.tables where name='Courses')
 drop database Courses
go

select * from student where stuName like 'wangwu[^2-4]%'

use wangwang
go
create table person
(
perId int identity primary key,
personId int not null--身份证
)
go
--给身份证添加约束,假如身份证五位数字
/*select * from person
alter table person
add constraint CK_personId CHECK(personId like [0-4],[0-4],[0-4],[0-4],[0-4])
go*/
--更改
update student set age=age+1
select * from student where groupId=1
update student set age=age+2,stuName=stuName+'mm'
where groupId=1
--删除
delete from student where groupId=1
select * from student

delete from student-- 删除表

3. 创建数据库

 

/*
* 表名: 课程表
* 作用: 存放课程信息
*/
create table Courses
(
 --课程编号
 CourseNo  varchar(10)  not null,
 --课程名称
 CourseName  varchar(50)  not null,
 --先修课程编号
 CoursePrev  varchar(10)  null
)

truncate table student--删除表(效率快 )

SQL 结构化查询语言

--创建一个数据库
create database School

数据冗余-数据重复出现,占用空间多,想修改厂家信息得修改很多行,每次录入新的货物的话必须把厂家地址、厂家电话等信息重新录入一次。

--判断是否已经有这个这个表,如果有就删除
if exists(select 1 from sys.tables where name='Achievements')
 drop database Achievements
go

T-Sql是SQL的加强版

查询:
1.基础查询2.聚合函数3.链接查询4.子查询
--更改
update student set age=age+1
select * from student where groupId=1
update student set age=age+2,stuName=stuName+'mm'
where groupId=1
--删除
delete from student where groupId=1
select * from student

delete from student-- 删除表
truncate table student--删除表(效率快 )
-------------------------------查询
use wangwang
go
select * from student where address='南昌路'
/*select
colname1 as 别名,
colname2 as 别名,
from
tableName
where
条件
group by
分组
having
分组后的筛选
order by
排序
*/
--起别名

select *,'姓名'=stuName from student
select *,stuName as 'as用法' from student
--给表起名
select *,stuName as 'as用法' from student as Stu
alter table student add haha varchar(20)
select * from student
update student set haha='mm' where age>31
select * from student where haha is null
--string a=null与a=''是不一样的 null与空
select * from student where haha is null
select * from student where haha=''
select * from student order by stuNoId asc
-------------------分页处理,关键字--top--percent
select top 5 * from student--每页出现5行
select top 50 percent * from student--每页出现总体的百分之n,进1法
--排序
select * from student order by age desc--降序号
select * from student order by stuNoId asc--升序
-----函数。
select 'aaa' as '列明'
--查询an在后面的英语中出现的序列
select CHARINDEX('an','my name is wangwang')
--查询an在后面的英语中出现的序列,前面14个不算,从第15开始查找
select CHARINDEX('an','my name is wangwang',14)
-----查询长度
select LEN('123456')
select address,LEN(address) as ' cahngdu' from student
-----------ltrim()可取空格
select * from student where ltrim(address)=' 天津路'--左空格
select * from student where ltrim(ltrim(address))=' 天津路'--俩边空各
---截取字符串right
select RIGHT('sgdsggueui',4) as '截取后显示'
select left('sgdsggueui',4) as '截取后显示'
----------替换
select replace('sfsfhufhsh','sh','AA')--将sh换成AA
select replace('sfsfhufhsh','s','9')--将sh换成AA
------------日期函数
select GETDATE()--得到当前日期
select DATEADD(DD,10000,'93-07-22')--活一万天
select DATENAME(DW,GETDATE())--今天星期几
select DATENAME(WW,GETDATE())--今天是今年的第几周
select * from student
select convert(varchar(2),age)+address as '新的列明' from student
select age + convert( int,address) as '新的列明' from student--有错误


案例1
create table card
(
cId int identity primary key,
CaId varchar(20) not null
)
go
insert card values ('fifuejf789io0i658')

select * from card
update card set CaId = replace(CaId,'i','1')

--update replace(CaId,'i','1') from card

select REPLACE(replace(CaId,'i','1'),'o','0') from card
select * into card2 from card
select * from card2

--删除数据库
drop database School

使用外键:可以降低数据冗余量~不用太多字符串占硬盘空间~之间用整形数据代表编号就行~

/*
* 表名: 成绩表
* 作用: 存放成绩信息
*/
create table Achievements
(
 --学号
 StudentNo varchar(10)  not null,
 --课程编号
 CourseNo varchar(10)  not null,
 --成绩
 Score  decimal(5,2) not null
)
go

update card2 set card=REPLACE(REPLACE(CaId,'i','1'),'o','0')

SQL编程
变量分为:1.局部变量,以@符号前缀,先声明再赋值
赋值:set @变量名=值 select @变量名=值
set @name=zhangsan
select studentNo from student where studentName=@name
李文才:
declare @name varchar(8)
set @name='李文才'
select @No=studentNo from student where studentName=@name

declare @No int//定义
print'学号'+@No
查询出学号+-1的相邻的同学。
select * from student where student.studentNo=@No+1 or student.studentNo=@No-1


insert into Class(GradeId,ClassID) values (4,'201')

--创建数据库的时候,指定一些数据库的相关参数。
create database School
on primary --主数据文件
(
name='School',
size=10mb,
filename='c:school.mdf',
filegrowth=10%,
maxsize=100mb
)
log on --日志文件
(
name='School_log',
filename='c:school.ldf',
size=5mb,
filegrowth=5mb,
maxsize=50mb
)

假设两张表中都有主键,A表,B表。A表中的主键为AId。B表中的主键为Bid.

-------------------------------创建表约束--------------------------------------------------------------

@@IDENTITY

DECLARE @TAG VARCHAR(1)
SET @TAG='*'
PRINT @TAG+@TAG
PRINT @TAG+@TAG+@TAG
PRINT @TAG+@TAG+@TAG+@TAG
------DECLARE @xuehao int
set @xuehao =20011
select * from student where studentNo=@xuehao//学生记录查出来

declare @year int
set @year=DateDiff(DY,'1996-8-7',getdate())/365;年份
select * from student where student.brondate=@year+1 or student.brondate=@year-1;


create database MySchool
on primary
(
name = 'MySchool_data',
filename = 'D:projectMySchool_data',
size = 10mb,
maxsize = 100mb,
filegrowth=15%
)
log on
(
name = 'MySchool_log',
filename = 'D:projectMySchool_log',
size = 3mb,
maxsize = 20mb,
filegrowth=1mb
)

if exists(select * from sysobjects where name = 'Student')
drop table student
create table dbo.Student(
StudentNo int NOT NULL,
LoginPwd nvarchar(50) NOT NULL,
StudentName nvarchar(50) NOT NULL,
sex bit NOT NULL,
GradeId int NOT NULL,
Phone nvarchar(50) NULL,
Address nvarchar(255) NULL,
BornDate datetime NOT NULL,
Email nvarchar(50) NULL,
IdentityCard varchar(18) NOT NULL
)

if exists(select * from sysobjects where name = 'Subject')
drop table subject
create table dbo.Subject(
SubjectNO int IDENTITY(1,1) NOT NULL,
SubjectName nchar(50) NOT NULL,
ClassHour int NOT NULL,
GradeId int NOT NULL
)

if exists(select * from sysobjects WHERE name = 'Result')
drop table Result
create table dbo.Result(
StudentNo int NOT NULL,
SubjectNo int NOT NULL,
StudentResult int NOT NULL,
ExamDate datetime NOT NULL
)

if exists(select * from sysobjects where name = 'Grade')
drop table Grade
create table Grade(
GradeId int IDENTITY(1,1) NOT NULL,
GradeName nvarchar(50) NOT NULL
)

--向Grade表插入数据
INSERT INTO Grade VALUES('S1')
--向Subject表插入数据
INSERT INTO Subject VALUES('Winforms',20,1)

--向Student表插入数据
INSERT INTO Student VALUES('10000','GuoJing','郭靖',1,1,02088762106,'天津市河西区','1987-09-08 00:00:00','GuoJing@sohu.com',111111)

--向Result表插入数据
INSERT INTO Result VALUES('10001',2,70.6,'2013-02-15 00:00:00')


USE master --设置当前数据库为master,以便访问sysdatabases表
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name ='MySchool')
DROP DATABASE MySchool
GO

CREATE DATABASE MySchool
ON (
name='MySchool_data',
FILENAME='F:projectMySchool_data.mdf',
SIZE=10mb, --主数据文件的初始大小
MAXSIZE=100mb, --主数据文件增长的最大值
FILEGROWTH=15% --主数据文件的增长率
)
LOG ON
(
/*--日志文件的具体描述,各参数含义同上--*/
NAME='MySchool_log',
FILENAME='F:projectMySchool_log.ldf',
SIZE=3mb,
MAXSIZE=20mb, --日志文件增长的最大值
FILEGROWTH=1mb
)
GO

 

/*--查询得到Student表的信息--*/
USE MySchool
GO

SELECT * FROM sysobjects

/*--案例:使用SQL语句删除表Student学生--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')
DROP TABLE Student
/*--学时2--*/
/*--案例:使用SQL语句创建Student表--*/
CREATE TABLE [dbo].[Student](
[StudentNo] [int] NOT NULL,
[LoginPwd] [nvarchar](50) NOT NULL,
[StudentName] [nvarchar](50) NOT NULL,
[Sex] bit NOT NULL,
[GradeId] [int] NOT NULL,
[Phone] [nvarchar](50) NULL,
[Address] [nvarchar](255) NULL,
[BornDate] [datetime] NOT NULL,
[Email] [nvarchar](50) NULL,
[IdentityCard] [varchar](18) NOT NULL
)
GO

/*--案例:使用SQL语句创建Subject科目表--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Subject')
DROP TABLE Subject
CREATE TABLE [dbo].[Subject](
[SubjectNo] [int] IDENTITY(1,1) NOT NULL,
[SubjectName] [nchar](50) NOT NULL,
[ClassHour] [int] NOT NULL,
[GradeId] [int] NOT NULL
)
GO
/*--案例:使用SQL语句创建Result结果表--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Result')
DROP TABLE ResultResult
CREATE TABLE [dbo].[Result](
[StudentNo] [int] NOT NULL,
[SubjectNo] [int] NOT NULL,
[StudentResult] [int] NOT NULL,
[ExamDate] [datetime] NOT NULL
)
GO
/*--案例:使用SQL脚本创建Student学生表--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')
DROP TABLE Student
CREATE TABLE [dbo].[Student](
[StudentNo] [int] NOT NULL,
[LoginPwd] [nvarchar](50) NOT NULL,
[StudentName] [nvarchar](50) NOT NULL,
[Sex] [bit] NOT NULL,
[GradeId] [int] NOT NULL,
[Phone] [varchar](50) NULL,
[Address] [nvarchar](255) NULL,
[BornDate] [datetime] NOT NULL,
[Email] [varchar](50) NULL,
[IdentityCard] [varchar](18) NOT NULL,
)
GO
/*--案例:使用SQL脚本创建Grade年级表--*/
IF EXISTS(SELECT * FROM sysobjects WHERE name='Grade')
DROP TABLE Grade
CREATE TABLE [dbo].[Grade](
[GradeId] [int] IDENTITY(1,1) NOT NULL,
[GradeName] [nvarchar](50) NOT NULL
)
GO
alter table Student
add Constraint pk_StudentNo primary key(StudentNo)
--Student表中的唯一约束
alter table Student
add Constraint uq_IdentityCard unique(IdentityCard)
--student表中的默认约束
alter table Student
add Constraint df_Address default('地址不详') for Address
--student表中的检查约束
alter table Student
add Constraint ck_BornDate check(BornDate>'1980-01-01')
--外键约束
alter table Grade
add Constraint pk_GradeId PRIMARY key(GradeId)
alter table Student
add Constraint fk_Grade foreign key(GradeId)
references Grade(GradeId)
go
--向Subjiect添加约束
alter table Subject
add Constraint pk_SubjectNo primary key(SubjectNo)
alter table Subject
add Constraint ck_ClassHour check(ClassHour>0)
ALTER TABLE Subject --非空约束(课程名称)
ADD CONSTRAINT CK_SubjectName CHECK (SubjectName is not null)
alter table subject
add Constraint fk_GradeId foreign key(GradeId)
references Grade(GradeId)
go
--使用SQL语句创建Result表的约束
ALTER TABLE Result --主键约束(学号、科目号、日期)
ADD CONSTRAINT PK_Result PRIMARY KEY
(StudentNo, SubjectNo, ExamDate)

ALTER TABLE Result --默认约束(日期为系统当前日期)
ADD CONSTRAINT CK_ExamDate DEFAULT (getdate()) FOR ExamDate

ALTER TABLE Result --检查约束(分数不能大于100,小于0)
ADD CONSTRAINT CK_StudentResult CHECK
(StudentResult BETWEEN 0 AND 100)

ALTER TABLE Result --外键约束(主表Student和从表Result建立关系)
ADD CONSTRAINT FK_StudentNo
FOREIGN KEY (StudentNo) REFERENCES Student (StudentNo)

ALTER TABLE Result --外键约束(主表Subject和从表Result建立关系)
ADD CONSTRAINT FK_SubjectNo
FOREIGN KEY (SubjectNo) REFERENCES Subject (SubjectNo)
GO


DATEDIFF(DY, BornDate, GETDATE())/365
DatePart()函数用于返回日期或者时间的单独部分,比如年、月、日、小时、分
例如 DatePart(YY,BornDate)
返回出生年月中的年份

可以通过DatePart()函数得到学号20011的学生大一岁和小一岁的学生

declare @year datetime
select @year= BornDate from Student where StudentNo=20011
select StudentName,sex,datepart(YY,BornDate) as BornDate from Student where StudentNo=20011 or datepart(YY,BornDate)= @year+1 or datepart(YY,BornDate)=@year-1

--select * from Student where ABS(DATEDIFF(DAY,@year,BornDate))/365<=1

--select * from Student where DATEDIFF(DAY,@year,BornDate)/365<=1 and DATEDIFF(DAY,BornDate,@year)/365<=1

--切换数据库
use school
go

这时,A表中引用了B表中的Bid作为一列,这时,我们就叫A表为B表的外键表,而B表叫做主键表。

/*学号为主键*/
alter table Students
 add constraint PK_StudentNo primary key(StudentNo)
/*性别检查约束,只能是“M”或“F”*/
alter table Students
 add constraint CK_StudentGender check(StudentGender in ('F','M'))
/*性别默认为男*/
alter table Students
 add constraint DF_StudentGender default('M') for StudentGender
/*引用班级编号*/
alter table Students
 add constraint FK_Students_Classes_ClassNo foreign key(ClassNo) references Classes(ClassNo)
go

--select * from Student

--子查询 总是用小括号括起来,先执行螺号里面的子查询,然后才执行外围的父查询
--注意:将子查询和比较运算符联合使用,必须保证子查询的返回结果为一个。
--查看比李思文小的学生
--declarre @date datetime
--select BornDate as '李斯文的出生日期' from Student where StudentName='李斯文'
--select * from Student
--where BornDate > (select BornDate as '李斯文的出生日期' from

4. 创建表

当创建了主外键关系后,如果在外键表中有任何记录引用了主键表中的某条记录,则在主键表中不能删除该记录。同时主键表也不能删除。

/*社会自课程编号为主键*/
alter table Courses
 add constraint PK_CourseNo primary key(CourseNo)
/*课程名称唯一*/
alter table Courses
 add constraint UQ_CourseName unique(CourseNo)
/*先修课程引用自身表为外键*/
alter table Courses
 add constraint FK_CoursePrev foreign key(CoursePrev) references Courses(CourseNo)
go

Student where StudentName='李斯文')

高级查询
简单子查询的用法
declare @Birthday datetime
select @Birthday = BornDate from student where studentName='李斯文';
select studentName,sex from student where BornDate>@Birthday


select StudentResult from Result
where SubjectNo=(select SubjectNo from Subject where SubjectName='C# OOP') and
ExamDate=(select max(ExamDate) from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='C# OOP'))
--select max(ExamDate) from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='C# OOP')
--1.从课程名称查询出课程编号,2.从课程编号查询出最近(大)日期,从1和2一起查询出考试成绩。
--查询Java课程考试成绩为60分的学生名单--in 关键字:查询出来的结果可能是多个
select StudentName from Student where StudentNo in
(
select StudentNo from Result where SubjectNo=(select SubjectNo from Subject where SubjectName='Java Logic')
and
StudentResult=60
)


--in 用法 查询结果有多个
select StudentNo,studentName from Student where GradeId in (select

--创建表
create table Class
(
ClassId int identity(1,1) primary key,
ClassName varchar(50) not null,
ClassDesc varchar(50) not null
)

 

/*学号和课程组合主键*/
alter table Achievements
 add constraint PK_StudentNo_CourseNo primary key(StudentNo,CourseNo)
/*分数在0-100之间*/
alter table Achievements
 add constraint CK_Score check(Score between 0 and 100)
/*学号外键*/
alter table Achievements
 add constraint FK_Achievements_Students_StudentNo foreign key(StudentNo) references Students(StudentNo)
/*课程外键*/
alter table Achievements
 add constraint FK_Achievements_Students_CourseNo foreign key(CourseNo) references Courses(CourseNo)
go

GradeId from Grade where GradeName = 'S1')

聚合函数:sum avg min max count(null列不计算在rpu内)
分组:select groupId,avg(age) from student
group by groupId
having avg(age)<=30 刷选
where 和 having的区别:where 对表中的原始数据进行帅选,
having对分组后使用聚合函数计算后的数据进行帅选

--删除表
drop table Class

 

----------------------------添加值-----------------------------------------------------------------

having一般与group by结合使用

视图
视图名称
creatview view_name(view_colum_name)
As query_expression(查询表达式)

--向Class表中插入数据
insert into Class(ClassName,ClsDesc)values('大三','三年');

4、登陆数据库的方式及区别是什么?

--班级表(方法一)
insert into Classes(ClassNo,ClassName) values('20100301','T100')
insert into Classes(ClassNo,ClassName) values('20100308','T101')
insert into Classes(ClassNo,ClassName) values('20100322','T102')
go

[with check option](添加约束条件)

--分组查询 Group by 在select 语句查询的最后。

select studentResult ,sum(subjectNo) from Result group by studentResult
--出现Group by 一般就会出现聚合函数
--查询学生每一门课程的平均成绩
--要求显示学生姓名,课程名称,
--科目ji的平均分--如何将studentNo与studentName联系在一起?
select SubjectNo , AVG(studentResult)From Result group by subjectNo --left on student.
select SubjectName ,AVG(studentResult)from Result
left join subject on
subject.SubjectNO=result.subjectNo ///////left join 外表 on 俩表相等的字段
group by SubjectName --学生的平均分

--select studentNo ,AVG(studentResult) from Result where StudentNo=1000 group by studentNo
--查询课程的成绩 显示课程名称以及所对应的成绩
--left join 左链接!!!! 主从表的关系右边的是主表 on
select SubjectNo , AVG(studentResult)From Result group by subjectNo
select Result.StudentResult,SubjectName from Result
Left join Subject on --left join +主表 on后面加俩个表相同的字段

--insert into...values.. 这种写法每次只能插入一条数据

用户名验证:       通过用户名和密码登陆,在互联网使用较多

--学生表(方法二)
insert into Students(StudentNo,StudentName,StudentGender,StudentBirth,ClassNo)
     select '2010030101','张强','M','1988-10-19','20100301'
 union all select '2010030102','李晓芸','F','1989-11-29','20100301'
 union all select '2010030103','程向润','M','1989-05-11','20100301'
 union all select '2010030104','吴超','M','1990-06-03','20100301'
 union all select '2010030804','蒋永涛','M','1989-10-19','20100308'
go

subject.SubjectNO=result.subjectNo

事务 视图 索引
begin transaction
declare @error int
set @error=0
update bank set currentmoney=currentmoney-1000 where customername='张三'
set @error=@error+@@error 错误信息 全局变量
update bank set currentmoney=currentmoney+1000 where customername='李四'
set @error=@error+@@error
给客户一个可视化的结果,
if(@error<>0)
begin
print '转账失败,重重新操作'
rollback transaction 回滚
end

esle
begin
print '转账成功张三给李四转了1000'
commit transaction 提交给数据库
end
go

1.业务逻辑需要多个T_SQL语句来执行

create table bank
(
customername varchar(5)
currentmoney int
)

事务属性:原子性(各个步骤不可以再分,要么都执行,要么都不执行)
一致性 当事务完成时,数据必须处于一致状态
隔数离性 并发实物之间彼此隔离,独立,不应依赖其他
永久性
开始事务: begin transaction
提交事务:commit transaction
回滚事务:roollback transaction

--向Class表中插入多条数据
--重复数据不重复插入,union关键字本身就具有去掉重复的意思
--union | union all (重复插入)
insert into Class
select '大三','三年' union
select '三五','间谍' union
select '一一','多久' union
select '六七','得到'

Windows身份验证:当前操作系统的身份来验证,一般在局域网访问系统使用

--课程表(方法二)
insert into Courses(CourseNo,CourseName,CoursePrev)
  select 'CTB','计算机基础',null union all
  select 'STB','软件技术基础','CTB' union all
  select 'C','程序基础和C语言实现','STB' union all
  select 'SQL BASE','SQL SERVER应用开发','STB' union all
  select 'JAVA','JAVA面向对象程序设计','C' union all
  select 'SQL ADV','SQL SERVER数据库设计和实现','SQL BASE'
go

事务是解决可能存在的问题

begin transaction
declare @error int
set @error=0
insert into Result values(113,2,150,GETDATE())
set @error=@error+@@error

if(@error<>0)
begin
print '插入失败'
rollback transaction
end

else
begin
print '插入成功'
commit transaction
end

--将Class表中的数据备份到Student表中
--这种写法会将Class表中的所有数据插入到Student表中
--前提是Student表不存在,如果这个表存在则报错。
select * into Student from Class

5、、char和varchar的区别是什么?    最长长度8000

--成绩表(方法二)
insert into Achievements(StudentNo,CourseNo,Score)
  select '2010030101','CTB',85 union all
  select '2010030101','STB',87 union all
  select '2010030101','C',90 union all
  select '2010030102','CTB',80 union all
  select '2010030102','STB',91 union all
  select '2010030102','C',82 union all
  select '2010030103','CTB',77 union all
  select '2010030103','STB',95 union all
  select '2010030103','C',84 union all
  select '2010030104','CTB',65 union all
  select '2010030104','STB',77 union all
  select '2010030104','C',95 union all
  select '2010030801','CTB',83 union all
  select '2010030801','STB',86 union all
  select '2010030801','C',91
go

select * from Result

索引:根据索引键查找定数据行
汉语字典中的汉字按“页”存放,一般都有汉语拼音的目录(索引),偏旁部首目录等
sql中的数据也是按页存放
索引;shi sql编排数据的方法

--向一个已经存在的表中插入数据,数据的来源是Class表
insert into Student(ClassName,ClsDesc)
select ClassName,ClsDesc from Class

英文和数字都是占一个字节,其它字符占两个字节(中文)

--------------------------查询表-----------------------------------------------------------

唯一索引,主键索引,聚集索引,

存储过程
系统存储过用程的名字一般以sp_开头
扩展存储一般xp

调用存储
execute 过程名 [参数] 或者 exec 过程名 [参数]

create proc[edure] 存储过程名
@参数1 数据类型=默认值 output
。。。。。。。
@参数n 数据类型=默认值 output
as
sql语句
go

 

 

a)Char声明无论多少字节,它都会自动补齐,空格补齐(声明4,输入22,则会有两个空格补齐)~

select * from Classes
select * from Students
select * from Courses
select * from Achievements

--查询表中数据
select * from Class

不可变的~就是说声明多少输入少了它依然补齐声明存放的大小

以上就是SQL的基本操作

5.update 数据

b)Varchar声明多少字节,存多少数据就是多少,不会空格补齐~

大家可以留言,因为我可能有些地方写得不是很好,也有可能有错

--将所有年龄小于20岁的人的年龄都改成19(tage是Class表后加属性)
update Class set tage=19 where tage<20

可变的~就是说声明多少就存多少~

  

--将年龄为19岁的并且性别为0的人的姓名两边★改为☆
update Class set ClassName =replace (tname,'★','☆') where tage=19 and tgender=0

 

6.删除数据

6、nchar和nvchar区别是什么?   最长长度4000

delete from Class --删除所有数据 自动编号没有恢复到默认值 可以根据条件来删除
truncate table Class --重新设置了自动编号 删除只能一次性都清空,不能根据条件来删除 清除速度(性能)比delete语句快的多

nchar(4):Unicode编码~实际一个字符(任何字符)要两个字节存 固定长度

delete from Class where tage=19 or tage is null --删除19岁或者空值

nvchar(4)Unicode编码~实际一个字符(任何字符)要两个字节存 可变长度

》》》》》》》》》删除重复数据只保留一条(id最小的一条)《《《《《《《《《
》》》》》》》》》删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录 《《《《《《《《《
delete from people
where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1)
and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>1)

 

 

7、len函数和datalength函数返回的是什么?

7.条件查询,模糊查询

字符数         字节数

--查询数学没有及格的学生的学号
select
fid as 学号,
fmath as 分数
from MyStudent where fmath<60

select len(‘a’),datalength(‘a’)        输出都是1

--查询年龄在20-30岁之间的男学生
select
fname as 姓名 from MyStudent where fage between 20 and 30 and fgender='男'

select len(‘没’),datalength(‘没’)      输出1,2

--查询班级id 1 2 3 的所有学生
select * from MyStudent where classid in (1,2,3)

 

--查询所有姓赵的同学 (通配符%表示:任意多个任意字符)
select * from MyStudent where fname like '赵%'

8、创建数据库的关键字是什么?

--查询出姓名中只要包含一个‘民’字即可。
select * from MyStudent where fname like '%民%'

----创建 数据库  数据库名称

--查询所有姓赵的同学,并且姓名字数是3个
--通配符 _ :表示任意的单个字符。
select * from MyStudent where fname like '赵__'
select * from MyStudent where fname like '赵%' and len(fname)=3

CREATE DATABASE GuangzhouBlog

--查询出姓名中包含‘民’或‘用’的同学
--通配符[]:表示中括号中的任意个字符,只选一个匹配
--通配符 ^a :表示除了a这个字符都行。
select * from MyStudent where fname like '%[民用]%'

   ON  PRIMARY  --默认就属于PRIMARY主文件组,可省略

8.聚合函数

(

--查询数学成绩最高低分
select max(fMath) as 数学成绩最高分 from MyStudent
select min(fMath) as 数学成绩最低分 from MyStudent

 NAME='GuangzhouBlog',  --主数据文件的逻辑名

--平均分(计算平均分的时候对空值不处理)
select avg(fMath) as 平均分 from MyStudent

 FILENAME='E:Itcast广州广州Dotnet一期dbGuangzhouBlog_data.mdf',  --主数据文件的物理名

--求数据记录中的总条数(总人数)
select count(*) as 班级总人数 from MyStudent

 SIZE=3mb,  --主数据文件初始大小

select
最高分=(select max(fMath) as 数学成绩最高分 from MyStudent),
最低分=(select min(fMath) as 数学成绩最低分 from MyStudent),
平均分=(select avg(fMath) as 平均分 from MyStudent)

 MAXSIZE=10mb,  --主数据文件最大的值

--分数评级
--90以上 优秀
--80以上 良好
--70以上 中
--70以下 差
select chengji,
评级=
case
when shuxue>=90 then '优秀'
when shuxue>=80 then '良好'
when shuxue>=70 then '中'
else '差'
end
from Student

 FILEGROWTH=15%   --主数据文件的增长率

9.null 问题

)

--请查询出学生表中所有数学成绩为null的人的信息
--null在数据库中表示unknow(不知道),判断一个值是否为null,也就不能用=或者<>来判断
select * from MyStudent where fMath=null 错误(不返回任何数据)

LOG ON-- 日志文件

正确 select * from MyStudent where fMath is null

(

--查询所有fmath为非null的值
select * from MyStudent where fMath is not null

  NAME='GuangzhouBlog_Log',

--null值与任何数据运算后得到的还是null值。
update MyStudent set fage=fage+1 where fid=1

  FILENAME='E:Itcast广州广州Dotnet一期dbGuangzhouBlog_log.ldf',

10.分组group by

  SIZE=3mb,  --日志文件初始大小

--统计出mystudent表中,男女同学的个数

  MaxSize=20mb,

select
fgender as 性别, --这时,count(*)统计的是每一组的记录条数, 不是总条数
count(*) as 人数
from MyStudent group by fgender --先执行group by语句分组,分完组在统计每 组个数。 分出来几个组,那么count(*)就统 计几次

  FILEGROWTH=1MB

--查询班级的男同学的人数大于2的信息

)

--having是group by的条件对分组后的数据进行筛选(与where类似,都是筛选,只不过having是用来筛选分组后的组的)
select
classid as 班级号,
count(*) as 班级人数
from TblStudent
where fgender='男'
group by classid
having count(*)>2

 

》》》》》》》》》语句执行顺序《《《《《《《《《

9、怎么指定一个数据库来创建数据表?

select
--distinct / top 之类的关键字
fgender as 性别, --5》选择列
count(*) as 人数
from MyStudent --1》先从表中拿到数据
where fage>30 --2》从MyStudent的数据中筛选出所有年龄大于30岁的任的信息
group by fgender --3》按照性别分组,分完组得到一个新的结果集
having count(*)>500 --4》基于分组以后的结果集,然后再筛选,筛选出那些组中记录大于500的组
order by 人数 asc --6》最后把显示出来的结果排序

USE GuangzhouBlog -- 设置当前使用的数据库,即数据表要创建到哪个数据库

--语句执行顺序
from > where > group by > having > select > order by

--创建  表    表名

11.日期函数

CREATE  TABLE  Score

--请查询出所有入职一年以上的员工信息
select * from TblStudent
where dateadd(year,1,tsday)<getdate()

(

--计算两个时间差
--查询90年距今是多少年
select datediff(year,'1990-9-9',getdate())

   ScoreId INT IDENTITY(1,1),

--查询一个日期的特定部分
select year(getdate())
select datepart(year,getdate())

   SId  INT  NOT  NULL , 

--输出所有数据中通话时间最长的5条记录。
select top 5 *,'通话时长(秒)'=datediff(second,Startdatetime,Enddatetime) from Calltecords order by datediff(second,Stardatetime,enddatetime) desc

   English  INT  NOT  NULL, 

后记

   Math  INT  NOT  NULL

下篇分享视图、触发器等,分页查询、子查询、连表查询等

)

 

 

10、bit数据类型在sql语句和设计器里面分别用什么表示?

bit数据类型在写SQL语句时,用1和0表示,设计器里面就是用true和false表示

 

11、插入数据的sql语句是什么?

--a.不指定列,直接新增所有列的值

INSERT INTO Classes VALUES ('黑豹训练营一期',38,getdate(),0)

 

--b.指定列新增         insert  into  表(列值1,列值2…列值n)  values()

INSERT INTO Classes(className,count) VALUES ('黑豹训练营二期',138)

 

--c.insert into talbe select from table

从自己表里复制一些到自己表     insert  into  数据表(列值1,列值2….列值n) select  列值1,列值2….列值n  数据表

INSERT INTO Classes(className,count) SELECT classname,count FROM classes

 

--c.2INTO select    (前提是自己先建好Classes2表,列名相同)      insert  into  目标表(列值1,列值2….列值n) select  列值1,列值2….列值n  数据源表

--   从Classes复制一些数据到Classes2表

--   先执行后面的select语句,获得一个结果集(两列),然后循环结果集里的行,每次取出行里的两个值,

--   然后再执行insert 语句,生成values语法,并将循环到的两个值赋进去,最后,完成新增

INSERT INTO Classes2(className,count) --values('班级名',40)

     SELECT classname,count FROM classes

 

--c.3 insert select 常量union

INSERT INTO Score (SId,English,Math)

SELECT 1,10,99 UNION

SELECT 2,66,77 UNION

SELECT 2,66,88 UNION

SELECT 1,77,54 UNION

SELECT 1,88,99

 

--d.SELECT INTO

 复制一张表(是在复制的同时创建一张新表)      select  列值1,列值2,…..列值n  into  新表   from   数据源表

SELECT id,classname,count,adddate,isdel --(指定从数据源表中查询列出来,然后直接作为新表的列名)

     INTO Classes4 -- 这个是新表的名称,此时表并不存在

     FROM  classes -- 数据源表

 

12、更新数据表的sql语句是什么?         

update  表名称   set  要改变的列值   where   修改的条件

--1.普通更新

UPDATE classes SET count=45 WHERE id=3--将id=3的行的count列的值设置成45

 

--2.更新多个列

UPDATE classes SET count=55,className='黑豹训练营二.五期' WHERE  id=3

 

--3.更新多行

UPDATE classes SET COUNT=COUNT+2 WHERE count<50

 

--4.或or

UPDATE classes SET COUNT=COUNT+10 WHERE count=55 OR count<50

 

--5.与and

UPDATE classes SET COUNT=COUNT+2 WHERE count=56 and className='广州传智播客.Net训练营一期'

 

13、删除数据表的sql语句是什么?

--1.删除整个表的数据

DELETE FROM classes4 --DELETE classes4

 

--2.删除满足条件的数据

DELETE FROM classes3 WHERE COUNT<50 --and or

 

--3.删除多条ID记录

DELETE FROM classes WHERE id=4 OR id=5 OR id=6

DELETE FROM classes WHERE id in(4,5,6)

 

--4.TRUNCATE 能够清空表数据,并且重置主键ID使ID从1开始,而且还不会为每行删除的记录生成删除的日志,而仅仅生成一条日志,不会触发删除触发器

TRUNCATE TABLE classes2

--DELETE TABLE classes2

truncate只能操作表,不能操作数据库

 

14、查询数据表的sql语句是什么?

--1.查询表里所有的行和列数据*代表查询所有列

--                      (尽量不要使用*,因为会先到系统表里去查询classes表里的所有列名,然后再去查询classes表列数据)

SELECT * FROM classes

SELECT id,classname,count,adddate,isdel FROM classes--尽量使用指定列名的查询方式

 

--2.为列取别名

SELECT id AS 编号,classname 班级名,count,adddate,isdel FROM classes

 

--3.通过where 来筛选查询结果集的行

SELECT * FROM classes WHERE [count]>50

 

--4.直接使用select 显示常量和执行表达式

SELECT 1,'我爱北京天安门'

SELECT 1+1

SELECT getdate()

 

--5.使用top选择筛选行

SELECT * FROM classes

--5.1获得前几行

SELECT TOP 3 * FROM classes

     --获得人数最多的前三个班

SELECT TOP 3 * FROM Classes ORDER BY [count] DESC --降序排列 asc 升序排列

--5.2根据百分比来取行

SELECT TOP 50 PERCENT * FROM Classes ORDER BY [count]

 

--6.distinct去除重复项,先把数据表里的行都获取,然后把列逐个的取出放入结果集,在放之前,先检查结果集中是否已存在相同值,如果有,就不添加

SELECT DISTINCT [count] FROM Classes

SELECT DISTINCT [count],classname FROM Classes

 

 

 

15、有哪些聚合函数?

--1.Max求最大值

SELECT * FROM classes

SELECT max(id) from classes

 

--2.Sum求和

SELECT sum(id) from classes

 

--3.Min 求最小值

SELECT min(id) from classes

 

--4.Count 求行数

SELECT COUNT(id) from classes

 

--5.求平均数

SELECT avg(id) from classes

SELECT sum(id)/COUNT(id) from classes

 

16、类型转换

--1.Cast

SELECT * FROM classes

SELECT cast('2012-08-04' AS DATETIME) + 1

--SELECT '2012-08-04' + 1

 

--2.Convert

SELECT convert(DATETIME,'2012-08-04')+1

SELECT cast(adddate AS VARCHAR(10)) FROM Classes c

 

17、查询

--查询----------------------------------------------

--1.Between and

SELECT * FROM Students

SELECT * FROM Students s WHERE s.age BETWEEN 21 AND 26

 

--2. in

SELECT * FROM Students s WHERE age in(20,21,22)

SELECT * FROM Students s WHERE s.age IN (SELECT age FROM Students s WHERE gender=1)

 

18、什么是数据库约束?数据库约束有哪些?

数据库约束:是为了保证数据的完整性(正确性)而实现的一套机制

非空约束:设计器里面的表格数据不允许为空

主键约束:设置主键,唯一约束,值不能重复

唯一约束:设计器选定表格变量右键,索引键,添加一个列名,选择列名,选择是唯一

默认约束:找到某列的列属性有默认值绑定,添加默认值

检查约束:设计器变量右键添加Check约束,添加年龄一个表达式age>0 and age<100

外键约束:就是主外键,右键变量关系,删除一个关系,添加新的关系,选择主外键

 

19、什么是级联删除?

设置级联删除,能够将两个表中的主外键联系的数据删掉,如果没有设置级联删除,那么删除主外键有关系的id就不能删除

表中设计器打开-右键变量-关系=删除规则-级联

 

20、模糊查询的关键字是什么?4个通配符的含义 是什么?  

LIKE 关键字

a)%   包含零个或多个字符的任意字符串。

b)_    任何单个字符。

c)[ ]   指定范围(例如 [a-f])或集合(例如 [abcdef])内的任何单个字符。

d)[^]  不在指定范围(例如 [^a - f])或集合(例如 [^abcdef])内的任何单个字符。请将通配符和字符串用单引号引起来

 

如:把所有姓王的人查出来

-- %代表任意长度的任意字符

SELECT * FROM Students WHERE NAME LIKE '王%'

 

如:取姓王的,有两个、三个字符

-- _代表长度的任意字符

SELECT * FROM Students WHERE NAME LIKE '王_'  

SELECT * FROM Students WHERE NAME LIKE '王_ _' 

 

如:取一位、两位数,[]范围只能是[0-9]或[a-z]

-- []代表长度的[范围]内字符

SELECT * FROM Students WHERE id LIKE '[0-9]'    只能查出一位数

SELECT * FROM Students WHERE id LIKE '[0-9][0-9]'   能查两位数

SELECT * FROM Students WHERE name LIKE '[a-z]'

SELECT * FROM Students WHERE name LIKE '[a-z][a-z]'

 

-- ^ 代表取反,常结合[范围]通配符使用(只有MSSQLSERVER支持)

SELECT * FROM Students WHERE name LIKE '[^a-b]' --查询名字为一个字符,但不在a-b之间。

 

-- not 取反几乎所有数据库都支持

SELECT * FROM Students WHERE NAME not LIKE '[a-b]' --查询不是单个的a-b之间的字符的所有名字

 

21、数据库中的null表示什么?怎么进行空值判断?

数据库中的null表示“不知道”,而不是表示没有。因此select null+1结果是null,因为“不知道”加1的结果还是“不知道”

is null、is not null

 

SELECT * FROM Students WHERE cid is NULL

SELECT * FROM Students WHERE cid IS NOT NULL

--isnull函数isnull(值,为空默认值)--------------------------------

SELECT isnull(null,'默认值')

SELECT id,isnull(cid,0),name,age,gender,birthdate,regdate,isdel FROM Students

 

22、数据排序的关键字是什么?一般放在语句的什么位置?

order  by 一般要放到所有语句的后面

 

--order by 排序(升序-asc 降序-desc)---------------------------------

SELECT * FROM Students ORDER BY id--默认为asc 升序排列

SELECT * FROM Students ORDER BY id DESC --倒序排列

 

--按照字符排序

SELECT * FROM students ORDER BY NAME --如果排序列的值是字符类型,则按照字母的先后次序排列,若果有中文,则按照中文的拼音字母排列

 

--按照多个列排序

SELECT * FROM students ORDER BY name,age--当name相等时,按照年龄升序排列

SELECT * FROM students ORDER BY name,age DESC --当name相等时,按照年龄降序排列

 

23、数据分组的关键字是什么?必须放在语句的什么位置?

group  by必须放到WHERE语句之后

 

--分组(结果集不再是表数据,而是分组的组数据,每一行代表一个组)--------

--SELECT * FROM students GROUP BY cid

--再次强调:分组的结果集是组数据

SELECT cid,count(id) AS 人数 FROM students GROUP BY cid

 

--分组查询的结果集里的列,必须是group by 的列名或者用聚合函数求的的列值

SELECT age,count(id) AS 人数,max(id) as 每个组id最大的值 FROM students GROUP BY age

 

--分组:最后一次强调,查询的结果是组信息,不是表信息。组信息是程序员自己求出来的!!!

--     也就是说,查询的列,必须是组的聚合(行数,最大值,最小值,平均值)信息。

 

--按照多个条件分组(就可以看成是按照多个列的组合值来分组--也就是看组合值是否有相同的,如果有,就算成一个组)

--按照年龄和性别分组

SELECT age,gender FROM students GROUP BY age,gender

SELECT * FROM students

 

 

 

--按照班级和年龄分组

SELECT cid FROM students GROUP BY cid

SELECT cid,age FROM students GROUP BY cid,age

 

--先查询满足条件的人,再分组       一般不用这个方法!!!

--SELECT age FROM students WHERE age>22 GROUP BY age

 

24、having和where的区别是什么?

where是先查询满足条件,再分组;having是先分组再筛选

 

a)注意Having中不能使用未参与分组的列,Having不能替代where。作用不一样,Having是对组进行过滤。

b)Having 是Group By的条件对分组后的数据进行筛选(与Where类似,都是筛选,只不过having是用来筛选分组后的组的。)

c)在Where中不能使用聚合函数,必须使用Having,Having要位于Group By之后。

d)Having的使用几乎是与where一样的,也可以用in。

Having count(*) in (5,8,10)

--having 分组之后筛选条件

--先分组,然后再筛选满足条件的组

SELECT age,count(id)AS 总人数 FROM students GROUP BY age HAVING age>22 and COUNT(id) >3

--注意:常见错误

--此举报错,因为having是在select之前执行,也就是说执行having的时候压根儿就没有总人数这个列!

--SELECT age,count(id)AS 总人数FROM students GROUP BY age HAVING age>22 and 总人数>3

SELECT age,count(id)AS 总人数 FROM students GROUP BY age HAVING age>22 and COUNT(id) >3 order by  总人数 desc

 

25、SQL语句的执行顺序

5>…Select 5-1>选择列,5-2>distinct,5-3>top

1>…From 表

2>…Where 条件

3>…Group by 列

4>…Having 对组来做筛选条件

6>…Order by 列

 

26、类型转换

--类型转换

SELECT '您的班级编号'+ 1         报错!!!

SELECT convert(INT,'123')+ 1    不会报错!!!因为转成的是数字!!!

SELECT cast('123' as int)+ 1

--补充截取方法

SELECT left('abcd',2)

SELECT right('abcd',2)

--convert来转日期

SELECT getdate()

SELECT CONVERT(VARCHAR,GETDATE(),110)

SELECT CONVERT(VARCHAR,GETDATE(),102)

SELECT CONVERT(VARCHAR,GETDATE(),113)

 

27、Union集合运算符的基本原则是什么?

每个结果集必须有相同的列数;每个结果集的列必须类型相容

 

c)联合:将多个结果集合并成一个结果集。union(去除重复,相当于默认应用了distinct)、union all

d)Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复行,那么就用UNION ALL

 

--Union联合结果集

--联合结果集时要注意列的数量和数据类型必须一致或兼容

--union会自动合并相同数据行

SELECT * FROM Classes     有7条内容

UNION

SELECT * FROM Classes2    有1条内容

输出显示为合并成8条

 

--union all 联合结果集,不会去除重复行

SELECT * FROM Classes

UNION ALL

SELECT * FROM Classes2

 

SELECT * FROM students

UNION

sELECT NULL,NULL,'年龄统计',sum(age),null,null,null,null FROM students

--Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复行,那么就用UNION ALL

图片 1图片 2

SELECT [sid]
      ,[name]
      ,[age]
      ,[sex]
      ,[address]
  FROM [TestDB].[dbo].[UserInfo]
  UNION ALL
SELECT [sid]
      ,[name]
      ,[age]
      ,[sex]
      ,[address]
  FROM [TestDB].[dbo].[UserInfoTest]

View Code

 

28、len和datalength求出的是什么?

len-求字符个数  datalength-求字节数

 

SELECT len('james'),datalength('james')

都是输出5因为都是英文字母

SELECT len('中国第一'),datalength('中国第一')

第一个输出4,第二个输出8,中文一个字符占两个字节

 

LTRIM():字符串左侧的空格去掉

RTRIM () :字符串右侧的空格去掉

 

SELECT '         bb        '

select LTRIM(RTRIM('         bb        ')) AS '1'

LEFT()、RIGHT()  截取取字符串

 

29、日期函数

a)GETDATE() :取得当前日期时间

b)DATEADD (datepart , number, date ),计算增加以后的日期。参数date为待计算的日期;参数number为增量;参数datepart为计量单位。DATEADD(DAY, 3,date)为计算日期date的3天后的日期,而DATEADD(MONTH ,-8,date)为计算日期date的8个月之前的日期 c)DATEDIFF ( datepart , startdate , enddate ) :计算两个日期之间的差额。 datepart 为计量单位,可取值参考DateAdd。

d)DATEPART (datepart,date):返回一个日期的特定部分

Month()、year()、day()来代替

 

--日期函数

--获得当前日期

select getdate()

--日期操作dateadd(单位,数值,日期)

SELECT dateadd(MONTH,2,getdate())     加2个月

SELECT dateadd(MONTH,-2,getdate())    减2个月

SELECT dateadd(YEAR,-2,getdate())

 

SELECT * FROM classes ORDER BY adddate

--求日期差

SELECT datediff(DAY,GETDATE(),'2012-08-04')

--查询昨天和前天的所有添加的数据

SELECT * FROM Classes WHERE datediff(day,addDate,GETDATE())in(2,1)

--DELETE FROM classes WHERE datediff(day,addDate,GETDATE())in(2,1)

 

--求日期部分

SELECT datepart(year,getdate())

SELECT year(getdate())

SELECT MONTH(getdate())

SELECT DAY(getdate())

--练习:统计学员生日的日的个数

SELECT * FROM students

SELECT day(birthdate) AS 出生日,COUNt(*) FROM students GROUP BY day(birthdate)

SELECT DATEDIFF(minute,'2012-08-06','2012-08-05')

 

30、case的两种用法是什么?

--1.case语法。要注意,then后的数据类型要一致

--1.1等值判断:

SELECT id, case cid

                   WHEN 1 THEN '一班'

                   WHEN 2 THEN '二班'

                   ELSE '未知'

              END AS cid

,name,age FROM Students

 

--1.2区间判断

SELECT id, case

                   WHEN age>0 AND age <=20 THEN '小女孩'

                   WHEN age>20 AND age <=30 THEN '少女'

                   WHEN age>30 AND age<=40 THEN '阿姨'

                   WHEN age>40 AND age<=100 THEN '大妈'

                   ELSE '未知'

              END AS 怪物等级

,name,cid FROM Students

 

31、绝对值函数语法是什么?

SELECT ABS(-1)

 

32、子查询的概念什么?分哪两类?

a)概念:把一个查询的结果在另一个查询中使用就叫子查询。(将一个查询语句做为一个结果集供其他SQL语句使用)

b)子查询基本分类:

独立子查询

子查询可以独立运行

相关子查询

子查询中引用了父查询中的结果

 

--2.子查询(就是查询另一个sql语句的结果集)***************************************

SELECT * FROM (SELECT * FROM Area WHERE ar_uid=0) AS tempTable   //一定要别名!!!

 

--2.2使用子查询搜索外键表数据(单列多行)

SELECT * FROM Students WHERE cid in(--然后再根据黑猫班的id查询学员

    SELECT ID FROM dbo.Classes WHERE classNAME LIKE '%黑猫%'--先查出黑猫班的id

)

 

--2.3使用子查询的=、!=、<、<=、>、>= 符号时,子查询的结果里只能有一个值,不能有多个列和行

SELECT * FROM Students WHERE age > (SELECT age FROM Students WHERE name='地球超人')

 

SELECT * FROM Students WHERE Students.cid in(36,37)

 

--2.3.1Any 集合:=、!=、<、<=、>、>= 符号使用,相当于or

SELECT * FROM Students WHERE Students.cid >= Any(SELECT 36 UNION select 37)

SELECT * FROM Students WHERE Students.cid >= 36 or Students.cid >=37

 

--2.3.2All 集合:=、!=、<、<=、>、>= 符号使用,相当于and

SELECT * FROM Students WHERE Students.cid >= All(SELECT 36 UNION select 37)

SELECT * FROM Students WHERE Students.cid >= 36 and Students.cid >=37

 

--exists 表示是否存在

SELECT * FROM Students where exists (SELECT * FROM Classes WHERE classNAME LIKE '%黑猫%' and Classes.id=Students.cid)

SELECT * FROM Students where cid in (SELECT id FROM Classes WHERE classNAME LIKE '%黑猫%' and Classes.id=Students.cid)

 

 

33、分页的三种方式是什么?

--3.1 top+orderby 来实现分页(页容量:)效率低,不用

--获得第一页数据

SELECT TOP 10 * FROM Area

--获得第二页数据

SELECT TOP 10 * FROM

     (SELECT TOP 20 * FROM Area ORDER BY ar_id ASC) AS tempTable

ORDER BY ar_id DESC

 

--3.2 使用not in 分页

SELECT top 10 * FROM Area WHERE ar_id NOT IN (SELECT TOP 30 ar_id FROM Area)

 

--3.3使用and / between and /row_number()

SELECT * FROM Area

SELECT * FROM Area WHERE ar_id>=10 AND ar_id<=19--直接使用id来作为分页的条件不好,因为中间有id被删除了

 

--所以应该使用Row_Number() 生成一个连续的序号列over告诉Row_Number是按照某个列的顺序来生成序号

SELECT Row_Number() OVER (ORDER BY ar_id ASC) AS 序号,* FROM Area

SELECT Row_Number() OVER (ORDER BY ar_name ASC) AS 序号,* FROM Area

 

--使用row_number分页(微软推荐分页方法)

SELECT * FROM

     (SELECT Row_Number() OVER (ORDER BY ar_id) AS 序号,* FROM Area) as tempTable

WHERE  序号>=10 AND 序号<=19

 

 

SELECT * FROM Students

SELECT * FROM classes

 

 

34、连接查询有哪几种?

--4.1内连接-根据条件将两个表的指定列合成一行数据(查询出满足条件的行) 例:查询学员及其所在班级信息

SELECT * FROM Students INNER JOIN Classes ON students.cid=classes.id

--相当于如下多表查询语句:

SELECT * FROM Students,Classes WHERE students.cid=classes.id

--左右两张表中任何一行不满足条件,就都不输出

SELECT * FROM Students INNER JOIN Classes2 on students.cid=classes2.id

 

--4.2左外连接-保证左表中每行记录都显示,右表中如果有满足on条件的,就显示,没有就不显示

SELECT * FROM Students LEFT JOIN Classes2 on students.cid=classes2.id

 

--4.3右外连接-保证右表中每行记录都显示,左表中如果有满足on条件的,就显示,没有就不显示

SELECT * FROM Students RIGHT JOIN Classes3 on students.cid=classes3.id

 

--4.4交叉连接- 将左表的每一行与右表的每一行都连接一次,总行数= 左表行数* 右表行数(笛卡尔乘ji)

SELECT * FROM Students CROSS JOIN Classes3

 

 

35、什么是视图?视图和数据表的本质区别是什么?

a)视图是一张虚拟表,它表示一张表的部分数据或多张表的综合数据,其结构和数据是建立在对表的查询基础上

b)视图在操作上和数据表没有什么区别,但两者的差异是其本质是不同:数据表是实际存储记录的地方,然而视图并不保存任何记录。

c)相同的数据表,根据不同用户的不同需求,可以创建不同的视图(不同的查询语句)

d)视图的目的是方便查询,所以一般情况下不对视图进行增改,不能删

优点:

  • 筛选表中的行
  • 防止未经许可的用户访问敏感数据
  • 降低数据库的复杂程度

e)普通视图

  • 并不存储数据(虚拟表),访问的是真实表中的数据

f)使用视图注意事项:

  • 1.视图中的查询不能使用order by ,除非指定了top语句。

视图被认为是一个虚拟表,表是一个集合,是不能有顺序的。而order by 则返回的是一个有顺序的,是一个游标。

在视图中使用select top percent + order by 问题。

  • 如果指定列名,则列名必须唯一(使用*不考虑)
  • create view vw_name as 后不能跟begin end.

g)(*)索引视图

  • 在视图上创建唯一聚集索引

数据会保存在数据库中而不是引用表中的数据

 

--视图内部储存了一个查询sql语句,那么当我们查询视图时,就相当于是查询了视图里的sql语句的结果集(子查询)

--视图内的列名不能重复

SELECT * FROM vwStudentClass

SELECT * FROM vwcontact_group

 

--5.1新增视图数据

--错误:视图或函数'vwcontact_group' 不可更新,因为修改会影响多个基表

--INSERT INTO vwcontact_group(uname,cellphone,homephone,groupName,gid)

                    --VALUES ('jam',123123123,123123123,'小三们',1)

--通过:新增的列都来源于一张基表,所以可以新增

INSERT INTO vwcontact_group(uname,cellphone,homephone,groupName,groupId)

                    VALUES ('jam',123123123,123123123,'小三们',1)

                   

SELECT * FROM vwcontact_group

--5.2修改视图数据

--通过:因为只更新了视图里一张基表的列

UPDATE vwcontact_group SET uName='bobo' WHERE id=7

--错误:修改会影响多个基表。

UPDATE vwcontact_group SET uName='网秦',name='情网' WHERE id=7

 

SELECT * FROM vwcontact_group

--5.3删除视图数据

--错误:因为删除会影响多个基表

DELETE FROM vwcontact_group WHERE id=7

--通过:如果视图里只包含一个基表,那么就可以使用删除

SELECT * FROM vwTeacher

DELETE FROM vwTeacher WHERE id=4

 

--5.4视图中的查询不能使用order by ,除非指定了top语句

SELECT * FROM Teacher ORDER BY salary

 

--5.5代码创建视图

CREATE VIEW vwArticle_Cate

AS

SELECT a.id AS aid,a.cid,a.title,a.content,ac.id AS cateid,ac.name,ac.parentId FROM Article a INNER JOIN ArticleCate ac ON a.cid=ac.id

 

SELECT * FROM vwArticle_Cate

 

 

36、局部变量的声明?赋值?

a)声明

    DECLARE @变量名  数据类型

b)赋值
SET @变量名 =值      --set用于普通的赋值
SELECT @变量名 = 值  --用于从表中查询数据并赋值,可以一次给多个变量赋值

 

--6.1变量的声明

DECLARE @name VARCHAR(40),@age int

--6.2赋值

SET @name='我爱北京天安门~~~天安门前武警手里有灭火器!'

SELECT @age=age FROM Teacher --如果该列中有多个值,则将最后一个值给变量

--6.3显示变量

SELECT @name,@age--在结果集中显示

print @name --在消息框中打印

 

SELECT *,ac FROM Article a

SELECT @@version

 

备注:全局变量(系统变量):

全局变量必须以标记@@作为前缀,如@@version

全局变量由系统定义和维护,我们只能读取,不能修改全局变量的值

@@error变量,在每次执行完SQL语句后,都会为@@error变量赋值,如果上次执行的SQL语句有错,则将@@errro赋值为一个不为0的值,否则(执行没错),则将@@error赋值为0.

@@Identity最后一次插入的标识值

 

 

37、if  else  和  while

a)格式

IF(条件表达式)

  BEGIN --相当于C#里的{

    语句1
  ……

  END --相当于C#里的}

ELSE

 BEGIN

    语句1

    ……

  END

 

DECLARE @name VARCHAR(40)

SET @name='你很优秀很不错~好英俊!你信吗?!'

if(len(@name)>20)

     BEGIN

         SELECT '文字长度太长了'

     END

ELSE

     BEGIN

         SELECT '你的文字太短了'

     END

 

--练习:

--计算平均分数并输出,如果平均分数超过分输出成绩最高的三个学生的成绩,否则输出后三名的学生

DECLARE @avgScore INT

SELECT @avgScore = avg(math+english) FROM Score

if(@avgScore > = 120)

BEGIN

     SELECT TOP 3 * FROM Score ORDER BY math,english desc

END

ELSE

BEGIN

     SELECT TOP 3 * FROM Score ORDER BY math,english

END

 

--练习:

--计算平均分数并输出,如果平均分数超过分输出成绩最高的三个学生的成绩,否则输出后三名的学生

DECLARE @avgScore INT

SELECT @avgScore = avg(math+english) FROM Score

if(@avgScore > = 120)

BEGIN

     SELECT TOP 3 * FROM Score ORDER BY math,english desc

END

ELSE

BEGIN

     SELECT TOP 3 * FROM Score ORDER BY math,english

END

 

b)格式

WHILE(条件表达式)

  BEGIN --相当于C#里的{

    语句

    ……

      continue --退出本次循环

    BREAK    --退出整个循环

  END --相当于C#里的}

 

--计算-100之间所有奇数的和

DECLARE @totalNum INT =0,@times int=0 --声明变量同时给初始值

WHILE(@times<100)--循环

BEGIN

     if(@times%2!=0)--取模

     BEGIN

         set @totalNum+=@times

     END

     set @times+=1

END

SELECT @totalNum

 

--练习:如果english不及格的人超过半数(考试题出难了),则给每个人增加分,循环加,直到不及格的人数少于一半

 

DECLARE @failNum float--不及格人数

DECLARE @stuNum float--参加考试的总人数

WHILE(1=1)

BEGIN

     SELECT @failNum=count(*) FROM Score  WHERE English<60

     SELECT @stuNum=count(*) FROM Score  WHERE English IS NOT NULL

     --if(@failNum*2 > @stuNum)

     if(@failNum >= (@stuNum/2))

     BEGIN

         UPDATE Score SET English+=2

         PRINT '不及格人数超过了总人数一半'

     END

     ELSE

     BEGIN

         PRINT '不及格人数没有超过总人数一半'

         BREAK--退出循环

     END

END

SELECT @failNum AS 不及格人数

 

SELECT 5.0/2

 

SELECT * FROM Score

 

 

38、事务

a)事务:同生共死

b)指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成,必须作为一个整体执行

c)这些sql语句作为一个整体一起向系统提交,要么都执行、要么都不执行

d)语法步骤:

  • 开始事务:BEGIN TRANSACTION
  • 事务提交:COMMIT TRANSACTION
  • 事务回滚:ROLLBACK TRANSACTION

e)判断某条语句执行是否出错:

  • 全局变量@@ERROR;
  • @@ERROR只能判断当前一条T-SQL语句执行是否有错,为了判断事务中所有T-SQL语句是否有错,我们需要对错误进行累计;

 

 

为什么需要事务?

如,转账问题:

    假定钱从A转到B,至少需要两步:

A的资金减少

然后B的资金相应增加     

Update  bank  set  balance=balance-1000    where cid='0001'

Update  bank  set  balance=balance + 1000  where cid='0002'

--查看结果。

    SELECT * FROM bank

   注意约束:金额不能小于10

假设cid='0001'的balance为1000,cid='0002'的balance为10,则会出现第一个更新语句不能通过,因为金额不能小于10,但是第二条更新语句通过,增加了1000,这就是漏洞!

--8.1创建表,准备数据

create table bank

(

     cId char(4) primary key,

     balance money,           --余额

)

 

alter table bank

add constraint CH_balance check(balance >=10)

 

DELETE FROM bank

insert into bank values('0001',1000)

insert into bank values('0002',10)

 

 

--8.2事务:将多条语句作为一个执行单元,单元中任意语句出错的话,其他语句对数据库造成的影响都要取消掉

SELECT * FROM bank

 

DECLARE @errCount INT =0 --声明变量,用来累计错误号

---------------------------------事务开始------------------------

BEGIN TRANSACTION--开启事务***

 

UPDATE bank SET balance = balance - 1000 WHERE cId='0001'

SET @errCount = @errCount + @@error  --统计错误号

 

UPDATE bank SET balance = balance + 1000 WHERE cId='0002'

SET @errCount = @errCount + @@error  --统计错误号

 

if(@errCount>0)--如果统计的错误号大于,说明之前某语句执行时有错误,必须回滚事务

     BEGIN

         ROLLBACK TRANSACTION --一旦***回滚事务,那么事务中的任何对数据库的影响都会被还原

         PRINT '事务回滚完毕'

     END

ELSE

     BEGIN

         COMMIT TRANSACTION --一旦***提交事务,那么事务中所有的影响都会保存到数据库中

         PRINT '事务提交完毕'

     END

 

39、存储过程

a)存储过程---就像数据库中运行方法(函数)

b)和C#里的方法一样,由存储过程名/存储过程参数组成/可以有返回结果。

c)前面学的if else/while/变量/insert/select 等,都可以在存储过程中使用

优点:

执行速度更快 – 在数据库中保存的存储过程SQL语句都是编译过的

允许模块化程序设计 – 类似方法的复用

提高系统安全性 – 防止SQL注入

减少网络流通量 – 只要传输 存储过程的名称

l  系统存储过程

  • 由系统定义,存放在master数据库中

名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头

 

--9.1创建一个简单的存储过程

CREATE PROC up_justforfun

AS

BEGIN

     SELECT * FROM Students

     SELECT * FROM classes

END

--9.2调用不带参数的存储过程

EXEC up_justforfun

 

--9.3修改存储过程alert(警报)  alter(修改)

ALTER PROC up_justforfun

@age INT =20 --定义参数,并设置默认值

AS

BEGIN

     SELECT * FROM Students WHERE age>@age

     SELECT * FROM classes

END

--9.2调用带输入参数的存储过程

EXEC up_justforfun 30

EXEC up_justforfun

 

--9.3创建带多个参数的存储过程

create PROC up_justforfunWithParas

@age INT =20,

@name VARCHAR(20)

AS

BEGIN

     SELECT * FROM Students WHERE age>@age AND NAME LIKE @name

     SELECT * FROM classes

END

--9.4调用带多个参数的存储过程

EXEC up_justforfunWithParas 20,'%a%'

--9.5显示的传参(可以不为有默认值的参数传参了)

EXEC up_justforfunWithParas @age=30,@name='%a%'

EXEC up_justforfunWithParas @name='%a%'

 

--9.6创建带输出参数的存储过程

create PROC up_justforfunWithOutPutParas

@age INT =20,

@name VARCHAR(20),

@stuCount INT OUTPUT --定义:输出类型的参数

AS

BEGIN

     SELECT * FROM Students WHERE age>@age AND NAME LIKE @name

     SELECT @stuCount=COUNT(*) FROM Students--将总行数赋值给输出参数@stuCount

END

--9.7调用带输出参数的存储过程

DECLARE @studentCount int--先在外面定义一个变量

EXEC up_justforfunWithOutPutParas 30,'%a%',@studentCount OUTPUT --将变量作为输出参数传入存储过程

SELECT @studentCount AS 学生总人数 --将存储过程修改后的变量显示

 

 

 

SQL语句:两个单引号相当于一个单引号

 

 

 

 

 

 

 

 

习题:

练习:

--输出所有数据中通话时间最长的条记录。orderby datediff

select top 5* ,datediff(second,startdatetime,enddatetime) 时长 from callrecords

order by 时长 desc

--输出所有数据中拨打长途号码(对方号码以开头)的总时长。like、sum

select sum(datediff(second,startdatetime,enddatetime)) 长途号码总时长 from callrecords

where telnum like '0%'

--输出本月通话总时长最多的前三个呼叫员的编号。

select top 3 callernumber 呼叫员编号,sum(datediff(second,startdatetime,enddatetime)) 通话时长 from callrecords 呼叫员编号

 where month(startdatetime)=7

 group by callernumber

 order by 通话时长 desc

--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)

select top 3 callernumber 呼叫员编号 ,count(id) 打电话次数最多 from callrecords

where month(startdatetime)=7

group by callernumber 

order by count(id) desc

--按照月份分组。

SELECT month(startdatetime) 月份 ,count(id) 月份个数 FROM callrecords

GROUP BY month(StartDateTime)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

--练习

--按照班级人数排序

SELECT cid AS 班级,count(id) as 总人数 FROM students GROUP BY cid ORDER BY count(id)

--请统计总年龄超过的班级名称和总年龄,并按总年龄排序

SELECT cid AS 班级,sum(age) as 总年龄 FROM students GROUP BY cid having sum(age)>100 ORDER BY sum(age)

--统计各个性别在cid=2的班的总人数,并按照总人数排序

SELECT cid AS 班级,count(id) as 总人数 FROM students where cid=2 GROUP BY cid ORDER BY count(id)

SELECT cid AS 班级,count(id) as 总人数 FROM students GROUP BY cid having cid=2 ORDER BY count(id)

 

--练习题:

--显示学生表的所有列和班级表的班级名称列

SELECT s.*,c.classname FROM Students s INNER JOIN Classes c ON s.cid=c.id WHERE s.age>20

--询学生姓名、年龄、班级及成绩(忽略没有参加考试和没有所在班级的学员)

SELECT * FROM Students s INNER JOIN Classes c ON s.cid=c.id inner join Score sc ON s.id=sc.SID

--查询所有学生(参加及未参加考试的都算)及成绩

SELECT * FROM Students s left JOIN Score sc ON s.id=sc.SID

--请查询出所有没有参加考试(在成绩表中不存在的学生)的学生信息

SELECT * FROM Students s WHERE s.id NOT in(SELECT SID FROM Score)

 

--练习题:

--练习:查询所有英语及格的学生姓名、年龄及成绩

SELECT s.name,s.age,sc.english,sc.math FROM Students s INNER JOIN Score sc ON s.id=sc.SID WHERE sc.english>=60

--练习:查询所有参加考试的(english分数不为null)学生姓名、年龄及成绩

SELECT s.name,s.age,sc.english,sc.math FROM Students s INNER JOIN Score sc ON s.id=sc.SID WHERE sc.english IS NOT NULL

--练习:查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩,如果没有参加考试显示缺考,如果小于english&math60分显示不及格

SELECT s.name,s.age,CASE

                       WHEN  sc.english IS NULL THEN '缺考'

                       WHEN sc.english<60 THEN'不及格'

                       ELSE cast(sc.english AS VARCHAR)--因为case是生成一个列,这个列的数据类型必须统一

                    end as 英语考试,

                   

                    CASE

                       WHEN  sc.math IS NULL THEN '缺考'

                       WHEN sc.math<60 THEN'不及格'

                       ELSE cast(sc.math AS VARCHAR)--因为case是生成一个列,这个列的数据类型必须统一

                    end as 数学考试

FROM Students s left JOIN Score sc

ON s.id=sc.SID

 

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:Server数据库学习,旺旺老师笔记

关键词:

上一篇:没有了

下一篇:没有了