-- 1.创建Docter表
if not exists(select * from sys.databases where name = 'HISDB')
	create database HISDB;
else
	print 'HISDB数据库已创建';
go

use HISDB
-- 创建医生表
if not exists(select * from sys.tables where name = 'doctor')
	create table doctor(
		ID int primary key identity(1,1),
		Name nvarchar(10) not null,
	)
else
	print 'doctor表已创建'
	
-- 2.用SQL(命令)或菜单方式创建patient表结构
-- 创建患者表
if not exists(select * from sys.tables where name = 'patient')
	create table patient(
		ID int primary key identity(1,1),
		Name nvarchar(10),
	)
else
	print '患者表已创建';
go

-- 3.用SQL(命令)或菜单方式创建curefee表结构
-- 创建治愈费用表
if not exists(select * from sys.tables where name = 'curefee')
	BEGIN
		create table curefee(
			ID int primary key identity(1,1),
			Patient_id INT NOT NULL,
			Amount INT NOT NULL,
			constraint FK_Curefee_Patient FOREIGN KEY (Patient_id) references patient(ID)
		)
		print '治愈费用表创建成功！'
	END
else
	print '治愈费用表已创建'
go

-- 4.用SQL(命令)或菜单方式在Doctor表中添加两列 sex char（2），age tinyint。
-- 给医生表添加列
if not exists(select * from sys.columns 
where object_id = OBJECT_ID('doctor') AND name IN ('sex', 'age')) 
	BEGIN
		Alter table doctor
		Add 
			sex char(2), -- 性别，存储2个字符（如 '男'/'女'/'M'/'F'）
			age tinyint; -- 年龄，使用 TINYINT 类型（0~255）
		print '成功添加sex和age列！'
	END
else
	print 'doctor表已添加过sex和age'
go

-- 5.为Doctor表添加默认值约束DF_SEX，默认值为“男”。
-- 给医生表添加约束
if not exists(select * from sys.objects where name = 'DF_SEX' AND type = 'D')
	BEGIN
		Alter table doctor
		ADD constraint DF_SEX default '男' for sex;
		print '成功给医生表添加了性别约束！'
	END
else
	print '默认值DF_SEX约束已创建'
go

-- 6.删除Doctor表约束DF_SEX，删除Doctor表列age
-- 1.删除约束
if exists(select * from sys.objects where name = 'DF_SEX' AND type = 'D')
	BEGIN
		Alter table doctor
		drop constraint DF_SEX
		print '检测到约束，已删除';
	END
else
	print '没有对应约束！'
	
	
	
-- 2.删除列
if exists(select * from sys.columns 
where object_id = OBJECT_ID('doctor') AND name = 'age')
	BEGIN
		Alter table doctor
		Drop column age
		print '检测到对应列，已删除age列'
	END
else
	print 'doctor表中没有age列！'
	
go

-- 7.创建学生管理数据库”XSGL”，并在其中创建以下3张表
create database XSGL;
go
use XSGL
if not exists(select * from sys.tables where name = 'student')
	BEGIN
		create table student(
			Sno char(9) primary key, -- 学生学号
			Sname char(6) not null, -- 姓名
			Ssex char(2), -- 性别
			Sage int, -- 年龄
			Sdept varchar(20), -- 所在院系
		)
		print '已创建学生表'
	END
else
	print '学生表已存在'
	
if not exists(select * from sys.tables where name = 'course')
	BEGIN
		create table course(
			Cno char(4) primary key, -- 课程编号
			Cname varchar(20) not null, -- 课程名称
			Cpno char(4), -- 先修课程号
			Ccredit int, -- 课程学分
		)
		print '课程表已创建'
	END
else
	print '课程表已存在'
	
if not exists(select * from sys.tables where name = 'sc')
	BEGIN
		create table sc(
			sno char(9), -- 学生学号
			Cno char(4), -- 课程编号
			Grade float, -- 成绩
			constraint FK_Sc_Key primary key(sno,Cno), -- 主键
		)
		print '成绩表已创建'
	END
else
	print '成绩表已存在'
	
	
-- 8.根据上述三张表结构，分析三张表之间的外键约束关系，并建立相关外键约束，
-- 并在该数据库的“数据库关系图”节点中，新建关系图，将这三张表添加进去，
-- 观察系统自动生成的三表关系图。
Alter table course
Add constraint FK_CPNO_CNO Foreign key (Cpno) references course(Cno);
print '成功给课程表添加了约束！'

Alter table sc
Add constraint FK_Sno_Student foreign key (sno) references student(Sno);
print '成功给成绩表添加了第一个约束！'

Alter table sc
Add constraint FK_Cno_Course foreign key (Cno) references course(Cno);
print '成功给成绩表添加了第二个约束！'

-- 9.1 限定grade字段的取值为大于等于0的数；
if not exists(select * from sys.objects where name = 'FK_Sc_Grade')
    BEGIN
		Alter table sc
		Add constraint FK_Sc_Grade check(Grade >= 0)
		print '成功创建Grade约束！'
	END
else
	print 'Grade约束已存在'	
	
	
-- 9.2 限定所在系sdept取值只能是“中医系”、“中药系”、“信息系”、“人文系”、“经济系”。
if not exists(select * from sys.objects where name = 'FK_Sdept_Student')
	BEGIN
		Alter table student
		Add constraint FK_Sdept_Student 
		check(sdept in ('中医系', '中药系', '信息系', '人文系', '经济系'))
		print '成功创建Sdept约束！'
	END
else
	print 'Sdept约束已存在'


-- 10.1 给学生表插入数据
insert into student (Sno, Sname,Ssex,Sage,Sdept) values
('S0001','安林','女',20,'中医系'),
('S0002','路霖','女',20,'中医系'),
('S0003','李森','男',19,'中药系'),
('S0004','李洋','男',19,'信息系'),
('S0005','杨青','男',19,'信息系');

select * from student;

-- 10.2 给课程表插入数据
insert into course (Cno,Cname,Cpno,Ccredit) values
('C001','计算机基础',null,3),
('C002','C语言','C001',4),
('C003','数据结构','C002',4),
('C004','数据库原理',null,5);

select * from course;

-- 10.3 给成绩表插入数据
insert into sc (sno,Cno,Grade) values
('S0001','C001',89),
('S0002','C001',78),
('S0003','C001',88),
('S0004','C003',87),
('S0004','C004',78),
('S0005','C003',88),
('S0005','C004',97);

select * from sc;

-- 10.4 拒绝
-- 因为课程表的先修课程号必须是已有的课程编号
-- 查看目前的表数据：
select * from student;
select * from sc;

if exists(select * from sys.objects where name = 'FK_Sno_Student')
	alter table sc
	Drop constraint FK_Sno_Student;
	print 'FK_Sno_Student约束已删除'
	Alter table sc
	Add constraint FK_Sno_Student foreign key (sno) 
	references student(Sno) on delete no Action;
	print '成绩表的级联约束已建立'
	
Delete from student where Sno = 'S0005';

-- 再次查看表数据，确定数据未删除
select * from student;
select * from sc;


-- 10.5 级联
-- 先声明级联操作
-- 拿成绩表和学生的关系举例子
-- 先把之前学生表的约束删除
select * from student;
select * from sc;
if exists(select * from sys.objects where name = 'FK_Sno_Student')
	alter table sc
	Drop constraint FK_Sno_Student;
	print 'FK_Sno_Student约束已删除'
	Alter table sc
	Add constraint FK_Sno_Student foreign key (sno) 
	references student(Sno) on delete cascade;
	print '成绩表的级联约束已建立'
	
Delete from student where Sno = 'S0005';

-- 再次查询表中数据
select * from student;
select * from sc;

-- 发现和S0005有关的成绩信息都删除了。

-- 10.6 设置空
-- 先把10.5 中更改的数据还原回来。
-- 先更改sc表的sno为非主键，然后再允许
-- 查看初始数据
select * from student;
select * from sc;

Alter table sc
Drop constraint FK_Sc_Key;
print '已删除成绩表的主键约束！'
Alter table sc
Alter Column sno char(9) NULL;
print '已允许sno为空！'
if exists(select * from sys.objects where name = 'FK_Sno_Student')
	alter table sc
	Drop constraint FK_Sno_Student;
	print 'FK_Sno_Student约束已删除'
	Alter table sc
	Add constraint FK_Sno_Student foreign key (sno) 
	references student(Sno) on delete set null;
	print '课程表的设置空约束已建立'
	
-- 现在删除学生信息
delete from student where Sno = 'S0001';
-- 再次查询表中数据
select * from student;
select * from sc;


-- 11. 删除sc表 重新创建
Drop table sc;
print 'sc表已删除！'

if not exists(select * from sys.tables where name = 'sc')
	BEGIN
		create table sc(
			sno char(9), -- 学生学号
			Cno char(4), -- 课程编号
			Grade float, -- 成绩
			constraint FK_Sc_Key primary key(sno,Cno), -- 主键
			constraint FK_Sno_Student foreign key (sno) references student(Sno),
			-- 参照学生表学号取值
			constraint FK_Cno_Course foreign key (Cno) references course(Cno),
			-- 参照课程表课程号取值
			constraint FK_Sc_Grade check(Grade >= 0),
		)
		print '成绩表已创建'
	END
else
	print '成绩表已存在'

use XSGL;
-- 先插入新的数据以满足进行查询
-- 给学生表插入新数据
INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES
('S0006', '王芳', '女', 21, '中医系'),
('S0007', '张伟', '男', 20, '中药系'),
('S0008', '刘洋', '男', 22, '信息系'),
('S0009', '陈晨', '女', 19, '中医系'),
('S0010', '赵敏', '女', 20, '中药系'),
('S0011', '周杰', '男', 21, '信息系'),
('S0012', '吴婷', '女', 19, '中医系'),
('S0013', '郑浩', '男', 20, '中药系'),
('S0014', '孙莉', '女', 22, '信息系'),
('S0015', '钱坤', '男', 21, '中医系'),
('S0016', '冯雪', '女', 20, '中药系'),
('S0017', '韩梅', '女', 19, '信息系'),
('S0018', '林涛', '男', 22, '中医系'),
('S0019', '徐静', '女', 21, '中药系'),
('S0020', '黄强', '男', 20, '信息系'),
('S0021', '欧阳明', '男', 20, '中医系'),
('S0022', '司马燕', '女', 21, '中药系'),
('S0023', '诸葛亮', '男', 22, '信息系'),
('S0024', '上官云', '女', 19, '中医系'),
('S0025', '东方红', '女', 20, '中药系'),
('S0026', '令狐冲', '男', 21, '信息系'),
('S0027', '慕容雪', '女', 19, '中医系'),
('S0028', '公孙策', '男', 20, '中药系'),
('S0029', '尉迟恭', '男', 22, '信息系'),
('S0030', '独孤剑', '男', 21, '中医系');
print '成功给学生表添加新数据'
go
-- 给课程表添加新数据
INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES
('C005', '中医基础理论', null, 4),
('C006', '中药学', 'C005', 5),
('C007', '方剂学', 'C006', 4),
('C008', '针灸学', 'C005', 3),
('C009', '计算机网络', 'C001', 4),
('C010', '操作系统', 'C001', 4),
('C011', '软件工程', 'C003', 3),
('C012', '人工智能', 'C004', 5);
print '成功给课程表添加新数据'
go

-- 给成绩表添加新数据
INSERT INTO SC (Sno, Cno, Grade) VALUES
('S0001', 'C002', 76),
('S0001', 'C003', 85),
('S0002', 'C002', 82),
('S0002', 'C005', 91),
('S0003', 'C006', 88),
('S0004', 'C002', 79),
('S0005', 'C002', 93),
('S0006', 'C001', 87),
('S0006', 'C005', 84),
('S0007', 'C006', 90),
('S0007', 'C008', 82),
('S0008', 'C001', 78),
('S0008', 'C009', 85),
('S0009', 'C005', 89),
('S0009', 'C008', 76),
('S0010', 'C006', 94),
('S0010', 'C007', 83),
('S0011', 'C003', 81),
('S0011', 'C010', 87),
('S0012', 'C005', 92),
('S0012', 'C008', 85),
('S0013', 'C006', 78),
('S0013', 'C007', 80),
('S0014', 'C004', 89),
('S0014', 'C010', 91),
('S0015', 'C005', 84),
('S0015', 'C008', 82),
('S0016', 'C006', 90),
('S0016', 'C007', 88),
('S0017', 'C001', 85),
('S0017', 'C009', 87),
('S0018', 'C005', 83),
('S0018', 'C008', 79),
('S0019', 'C006', 92),
('S0019', 'C007', 86),
('S0020', 'C004', 94),
('S0020', 'C010', 90),
('S0020', 'C002', 14),
('S0020', 'C001', 9);
print '成功给成绩表添加新数据'
go
select * from student;
select * from sc;
select * from course;

-- 1.按照出生年份升序显示所有的学生的学号、姓名、性别、出生年份及院系信息。
select Sno,Sname,Ssex,Sage,Sdept,(2025-Sage) as BirthYear from student
order by BirthYear ASC;


-- 2.统计各院系学生人数。
-- 加入数据
INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES
('S0006', '王明', '男', 18, '中医系'),
('S0007', '赵丽', '女', 21, '中药系'),
('S0008', '张伟', '男', 22, '信息系'),
('S0009', '陈静', '女', 20, '人文系'),
('S0010', '刘强', '男', 19, '经济系');

select Sdept,COUNT(*) '学生人数' from student
group by Sdept;


-- 3.按照课程号、成绩降序显示课程成绩在70到80之间的学生学号、课程号及成绩。
select sno,Cno,Grade from sc where Grade Between 70 and 80
order by Cno,Grade DESC;

-- 4.查询选修课程门数大于2的各个学生的学号和选修课程门数。
-- 添加数据
insert into sc (Sno,Cno,Grade) Values
('S0002','C003',50),
('S0004','C001', 85),
('S0004','C002', 35),
('S0006','C002', 90),
('S0007','C002', 32),
('S0008','C003', 80),
('S0009','C003', 58),
('S0010','C004', 27),
('S0002','C004', 40),
('S0002','C002', 77),
('S0001','C002', 90),
('S0001','C003', 50),
('S0001','C004', 38);

select sno,COUNT(*) as '选修门数' from sc
group by sno
having COUNT(*) > 2;



-- 5.查询所有学生的数据库原理与应用课程成绩信息，
-- 展示学号、姓名、所在系、课程名称和成绩。
select * from course;
select student.Sno,Sname,Sdept,'数据库原理与应用课程' as '课程名称',sc.Grade 
from student
join sc on sc.sno = student.Sno
where sc.Cno = 'C004';

-- 6.按课程号降序显示选修各个课程的总人数、最高分、最低分和平均分。
select Cno,COUNT(*) as '选修人数',
MAX(Grade) as '最高分',
MIN(Grade) as '最低分',
AVG(Grade) as '平均分'
from sc
group by Cno
order by Cno DESC;

-- 7.显示平均成绩大于“S0001”学生平均成绩的各个学生的学号、平均成绩。
select sno,AVG(Grade) as '平均成绩'
from sc
group by sno
having AVG(Grade) >
(select AVG(Grade) from sc where sno = 'S0001');

-- 8.查询各门课程的选修及格人数、及格比率。
select Cno, 
COUNT(CASE WHEN Grade>=60 then 1 END) as '及格人数',
CAST(ROUND(CAST(COUNT(CASE WHEN Grade>=60 then 1 END) as float) /
COUNT(*) *100 ,2) as varchar(10)) + '%' as '及格率'
from sc
Group by Cno

-- 9.查询选修课头数最多的学生学号及选修课头数最少的学生学号。
select sno as '选课最多的学号' from sc
group by sno
having COUNT(*) = 
(select MAX(ct) from (select COUNT(*) as ct from sc group by sno) as t)

select sno as '选课最少的学号' from sc
group by sno
having COUNT(*) = 
(select MIN(ct) from (select COUNT(*) as ct from sc group by sno) as t)

-- 10.显示各个院系男女生人数，
-- 其中在结果集中列标题分别指定“院系名称、男生人数、女生人数”。
select Sdept as '院系名称',COUNT(CASE WHEN Ssex = '男' THEN 1 END) as '男生人数',
COUNT(CASE WHEN Ssex = '女' THEN 1 END) as '女生人数' 
from student
group by Sdept;

-- 11.列出有两门以上课程（含两门）不及格的学生的学号及该学生的平均成绩。
select sno as '学号', AVG(Grade) as '平均成绩'
from sc
group by sno
having COUNT(CASE WHEN Grade < 60 THEN 1 END) >= 2;
go

-- 12.以“查询选修各门课程的选修及格人数、及格比率”建立一个视图，
-- 并测试该视图的查询结果。
CREATE VIEW CoursePassVIEW as
select Cno, 
COUNT(CASE WHEN Grade>=60 then 1 END) as '及格人数',
CAST(ROUND(CAST(COUNT(CASE WHEN Grade>=60 then 1 END) as float) /
COUNT(*) *100 ,2) as varchar(10)) + '%' as '及格率'
from sc
Group by Cno
go
select * from CoursePassVIEW;

-- 13.查询信息系专业每个学生的学号、姓名、选修课程的平均成绩。
select sc.sno, student.Sname,AVG(Grade) as '平均成绩' from sc
join student on student.Sno = sc.sno
where student.Sdept = '信息系'
group by sc.sno, student.Sname

-- 14.查询同时选修了c003和c004号课程的学生学号、姓名。
select sc.sno, student.Sname
from sc
join student on student.Sno = sc.sno
WHERE sc.Cno IN ('C003', 'C004')
group by sc.sno, student.Sname
having COUNT(sc.Cno) = 2

SELECT MAX(column_name)  FROM table_name  [WHERE conditions]

-- 在WHERE子句中使用
SELECT * 
FROM Orders
WHERE OrderDate = (SELECT MAX(OrderDate) FROM Orders);

-- 在HAVING子句中使用
SELECT department_id, AVG(salary)
FROM Employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT MAX(salary) FROM Interns);

-- 在SELECT子句中使用
SELECT 
    product_id,
    price,
    (SELECT MAX(price) FROM Products) AS max_price
FROM Products;

-- 找出选课数量等于最大选课数量的学生
SELECT student_id
FROM Course_Selections
GROUP BY student_id
HAVING COUNT(*) = (
    SELECT MAX(course_count) 
    FROM (
        SELECT COUNT(*) AS course_count 
        FROM Course_Selections 
        GROUP BY student_id
    ) counts
);

-- 找出各部门最新入职的员工
SELECT e.*
FROM Employees e
WHERE hire_date = (
    SELECT MAX(hire_date) 
    FROM Employees 
    WHERE department_id = e.department_id
);

COUNT(CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END)

-- 统计及格学生人数
SELECT 
    COUNT(CASE WHEN score >= 60 THEN 1 END) AS pass_count
FROM exam_results;

-- 统计各分数段人数
SELECT
    COUNT(CASE WHEN score >= 90 THEN 1 END) AS excellent,
    COUNT(CASE WHEN score >= 70 AND score < 90 THEN 1 END) AS good,
    COUNT(CASE WHEN score >= 60 AND score < 70 THEN 1 END) AS pass,
    COUNT(CASE WHEN score < 60 THEN 1 END) AS fail
FROM exam_results;

-- 统计各部门男女员工数
SELECT
    department,
    COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
    COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count
FROM employees
GROUP BY department;

-- 统计有及格成绩的不同学生人数
SELECT
    COUNT(DISTINCT CASE WHEN score >= 60 THEN student_id END) AS pass_students
FROM exam_results;

-- 统计各科及格率
SELECT
    subject,
    COUNT(*) AS total,
    COUNT(CASE WHEN score >= 60 THEN 1 END) AS pass_count,
    CAST(COUNT(CASE WHEN score >= 60 THEN 1 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS pass_rate
FROM exam_results
GROUP BY subject;

CREATE DATABASE bankDB
 ON
 (
  NAME='bankDB_data',
  FILENAME='d:\bankDB_data.mdf',
  SIZE=5mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= 'bankDB_log',
  FILENAME='d:\bankDB_log.ldf',
  SIZE=5mb,
  FILEGROWTH=15%
 )
GO

USE bankDB
GO
CREATE TABLE userInfo  --用户信息表
(
  customerID INT IDENTITY(1,1),
  customerName CHAR(8) NOT NULL,
  PID CHAR(18) NOT NULL,
  telephone CHAR(20) NOT NULL,
  address VARCHAR(50)
)
GO
CREATE TABLE cardInfo  --银行卡信息表
(
  cardID  CHAR(19) NOT NULL,
  curID  VARCHAR(10) NOT NULL,
  savingID INT NOT NULL,
  openDate  DATETIME NOT NULL,
  openMoney  MONEY NOT NULL,
  balance  MONEY NOT NULL,
  pass CHAR(6) NOT NULL,
  IsReportLoss  BIT  NOT NULL,
  customerID INT NOT NULL
)
GO
CREATE TABLE tradeInfo  --交易信息表
(
  tradeDate  DATETIME  NOT NULL,
  tradeType  CHAR(4)  NOT NULL,
  cardID  CHAR(19)  NOT NULL,
  tradeMoney  MONEY  NOT NULL,
  remark  TEXT   
)
GO
CREATE TABLE Deposit  --存款类型表
(
  savingID  INT  IDENTITY(1,1),
  savingName  VARCHAR(20) NOT NULL,
  descript VARCHAR(50)
)
GO

ALTER TABLE Deposit
  ADD CONSTRAINT  PK_savingID   PRIMARY KEY(savingID)
GO
/* userInfo表的约束
customerID    顾客编号    自动编号（标识列），从1开始，主键
customerName    开户名    必填
PID    身份证号    必填，只能是18位或15位，身份证号唯一约束
telephone    联系电话  必填，格式为xxxx-xxxxxxx或手机号11位或xxx-xxxxxxxx
address    居住地址    可选输入
*/
ALTER TABLE userInfo
  ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID),
      CONSTRAINT CK_PID CHECK( len(PID)=18 or len(PID)=15 ),
      CONSTRAINT UQ_PID UNIQUE(PID),
      CONSTRAINT CK_telephone CHECK([telephone] like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR [telephone] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR [telephone] like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
GO
/*cardInfo表的约束
cardID    卡号    必填，主健 , 银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxxx开始
curID    货币类型    必填，默认为RMB
openDate    开户日期    必填，默认为系统当前日期
openMoney    开户金额    必填，不低于1元
balance    余额    必填，不低于1元,否则将销户
pass    密码    必填，6位数字，默认为6个8
IsReportLoss    是否挂失  必填，是/否值，默认为”否”
customerID    顾客编号    必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡
savingID   存款类型ID号  必填，表示该开户卡对应何种类型存款类型（定期、活期、零存整取等）
*/

ALTER TABLE cardInfo     
  ADD CONSTRAINT  PK_cardID  PRIMARY KEY(cardID),
      CONSTRAINT  CK_cardID  CHECK(cardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
      CONSTRAINT  DF_curID  DEFAULT('RMB') FOR curID,
      CONSTRAINT  DF_openDate  DEFAULT(getdate()) FOR openDate,
      CONSTRAINT  CK_openMoney  CHECK(openMoney>=1),
      CONSTRAINT  CK_balance  CHECK(balance>=1),
      CONSTRAINT  CK_pass  CHECK(pass LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
      CONSTRAINT  DF_pass  DEFAULT('888888') FOR pass,
      CONSTRAINT  DF_IsReportLoss DEFAULT(0) FOR IsReportLoss,
      CONSTRAINT  FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID),
      CONSTRAINT  FK_savingID  FOREIGN KEY(savingID) REFERENCES deposit(savingID)
GO

/* tradeInfo表的约束
tradeType       必填，只能是存入/支取
cardID    卡号    必填，外健，可重复索引
tradeMoney    交易金额    必填，大于0
tradeDate    交易日期    必填，默认为系统当前日期
remark    备注    可选输入，其他说明
*/

ALTER TABLE tradeInfo
  ADD CONSTRAINT  CK_tradeType  CHECK(tradeType IN ('存入','支取')),
      CONSTRAINT  FK_cardID  FOREIGN KEY(cardID) REFERENCES cardInfo(cardID),
      CONSTRAINT  CK_tradeMoney  CHECK(tradeMoney>0),
      CONSTRAINT  DF_tradeDATE DEFAULT(getdate()) FOR tradeDate
GO

--存款类型表
INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息')
INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年')
INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年')
INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年')
INSERT INTO deposit (savingName) VALUES ('定活两便')
INSERT INTO deposit (savingName) VALUES ('通知')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年')
INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年')
INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息')
-- 查询该表信息
SELECT * FROM DEPOSIT
-- 向userinfo表和cardinfo表添加记录
INSERT INTO userInfo(customerName,PID,telephone,address )
     VALUES('张三','123456789012345','010-67898978','北京海淀')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1234 5678',1,1000,1000,1)
INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('李四','321245678912345678','0478-4444333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1212 1134',2,1,1,2)
INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('王五','567891234532124670','010-44443333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1212 1130',2,1,1,3)
INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('丁六','567891321242345618','0752-4334554')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010 3576 1212 1004',2,1,1,4)
-- 查询
SELECT * FROM userInfo
SELECT * FROM cardInfo
GO

/*
张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。
说明：当存钱或取钱（如900元）时候，会往交易信息表（tradeInfo）中添加一条交易记录，
      同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少900元）
*/
/*-----取款900交易信息表插入交易记录------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
      VALUES('支取','1010 3576 1234 5678',900)  
/*--------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'
/*------存款300-------------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
      VALUES('存入','1010 3576 1212 1130',300)  
/*-------------更新银行卡信息表中的现有余额---*/
UPDATE cardInfo SET balance=balance+300 WHERE cardID='010 3576 1212 1130'
/*------------存款1000-------------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
      VALUES('存入','1010 3576 1212 1004',1000)  
/*-------------更新银行卡信息表中的现有余额---*/
UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004'
/*------------取款1900-------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
      VALUES('支取','1010 3576 1212 1130',1900)  
/*-------------更新银行卡信息表中的现有余额-----*/
UPDATE cardInfo SET balance=balance-1900 WHERE cardID='010 3576 1212 1130'
-- 注意观察,是否有错。

-- 创建数据库
CREATE DATABASE bankDB
	ON
	(
		NAME='bankDB_data',
		FILENAME='d:\bankDB_data.mdf',
		SIZE=5mb,
		FILEGROWTH=15%
	)
	LOG ON
	(
		NAME= 'bankDB_log',
		FILENAME='d:\bankDB_log.ldf',
		SIZE=5mb,
		FILEGROWTH=15%
	)
GO

-- 2.创建新表
USE bankDB
GO
CREATE TABLE userInfo --用户信息表
(
	customerID INT IDENTITY(1,1),
	customerName CHAR(8) NOT NULL,
	PID CHAR(18) NOT NULL,
	telephone CHAR(20) NOT NULL,
	address VARCHAR(50)
)
GO
CREATE TABLE cardInfo --银行卡信息表
(
	cardID CHAR(19) NOT NULL,
	curID VARCHAR(10) NOT NULL,
	savingID INT NOT NULL,
	openDate DATETIME NOT NULL,
	openMoney MONEY NOT NULL,
	balance MONEY NOT NULL,
	pass CHAR(6) NOT NULL,
	IsReportLoss BIT NOT NULL,
	customerID INT NOT NULL
)
GO
CREATE TABLE tradeInfo --交易信息表
(
	tradeDate DATETIME NOT NULL,
	tradeType CHAR(4) NOT NULL,
	cardID CHAR(19) NOT NULL,
	tradeMoney MONEY NOT NULL,
	remark TEXT 
)
GO
CREATE TABLE Deposit --存款类型表
(
	savingID INT IDENTITY(1,1),
	savingName VARCHAR(20) NOT NULL,
	descript VARCHAR(50)
)
GO

-- 3.向各表添加约束
ALTER TABLE Deposit
	ADD CONSTRAINT PK_savingID PRIMARY KEY(savingID)
GO
/* userInfo表的约束
customerID    顾客编号    自动编号（标识列），从1开始，主键
customerName    开户名    必填
PID    身份证号    必填，只能是18位或15位，身份证号唯一约束
telephone    联系电话  必填，格式为xxxx-xxxxxxx或手机号11位或xxx-xxxxxxxx
address    居住地址    可选输入
*/
ALTER TABLE userInfo
	ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID),
	CONSTRAINT CK_PID CHECK( len(PID)=18 or len(PID)=15 ),
	CONSTRAINT UQ_PID UNIQUE(PID),
	CONSTRAINT CK_telephone CHECK([telephone] like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR [telephone] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR [telephone] like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
GO
/*cardInfo表的约束
cardID    卡号    必填，主健 , 银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxxx开始
curID    货币类型    必填，默认为RMB
openDate    开户日期    必填，默认为系统当前日期
openMoney    开户金额    必填，不低于1元
balance    余额    必填，不低于1元,否则将销户
pass    密码    必填，6位数字，默认为6个8
IsReportLoss    是否挂失  必填，是/否值，默认为”否”
customerID    顾客编号    必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡
savingID   存款类型ID号  必填，表示该开户卡对应何种类型存款类型（定期、活期、零存整取等）
*/

ALTER TABLE cardInfo
	ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID),
	CONSTRAINT CK_cardID CHECK(cardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
	CONSTRAINT DF_curID DEFAULT('RMB') FOR curID,
	CONSTRAINT DF_openDate DEFAULT(getdate()) FOR openDate,
	CONSTRAINT CK_openMoney CHECK(openMoney>=1),
	CONSTRAINT CK_balance CHECK(balance>=1),
	CONSTRAINT CK_pass CHECK(pass LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
	CONSTRAINT DF_pass DEFAULT('888888') FOR pass,
	CONSTRAINT DF_IsReportLoss DEFAULT(0) FOR IsReportLoss,
	CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID),
	CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID)
GO

/* tradeInfo表的约束
tradeType       必填，只能是存入/支取
cardID    卡号    必填，外健，可重复索引
tradeMoney    交易金额    必填，大于0
tradeDate    交易日期    必填，默认为系统当前日期
remark    备注    可选输入，其他说明
*/

ALTER TABLE tradeInfo
	ADD CONSTRAINT CK_tradeType CHECK(tradeType IN ('存入','支取')),
	CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID),
	CONSTRAINT CK_tradeMoney CHECK(tradeMoney>0),
	CONSTRAINT DF_tradeDATE DEFAULT(getdate()) FOR tradeDate
GO

-- 4.向表中添加数据
--存款类型表
INSERT INTO deposit (savingName,descript) VALUES ('活期','按存款日结算利息')
INSERT INTO deposit (savingName,descript) VALUES ('定期一年','存款期是1年')
INSERT INTO deposit (savingName,descript) VALUES ('定期二年','存款期是2年')
INSERT INTO deposit (savingName,descript) VALUES ('定期三年','存款期是3年')
INSERT INTO deposit (savingName) VALUES ('定活两便')
INSERT INTO deposit (savingName) VALUES ('通知')
INSERT INTO deposit (savingName,descript) VALUES ('零存整取一年','存款期是1年')
INSERT INTO deposit (savingName,descript) VALUES ('零存整取二年','存款期是2年')
INSERT INTO deposit (savingName,descript) VALUES ('零存整取三年','存款期是3年')
INSERT INTO deposit (savingName,descript) VALUES ('存本取息五年','按月支取利息')
select savingID 存款编号, savingName 存款类型, descript 存款描述 from Deposit

-- 向userinfo表和cardinfo表添加记录
INSERT INTO userInfo(customerName,PID,telephone,address )
	VALUES('张三','123456789012345','010-67898978','北京海淀')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
	VALUES('1010 3576 1234 5678',1,1000,1000,1)
INSERT INTO userInfo(customerName,PID,telephone)
	VALUES('李四','321245678912345678','0478-4444333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
	VALUES('1010 3576 1212 1134',2,1,1,2)
INSERT INTO userInfo(customerName,PID,telephone)
	VALUES('王五','567891234532124670','010-44443333')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
	VALUES('1010 3576 1212 1130',2,1,1,3)
INSERT INTO userInfo(customerName,PID,telephone)
	VALUES('丁六','567891321242345618','0752-4334554')
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
	VALUES('1010 3576 1212 1004',2,1,1,4)
-- 查询
SELECT customerID 顾客编号,
customerName 开户名,
PID 身份证号,
telephone 联系电话,
address 居住地址 FROM userInfo

SELECT cardID 卡号,
curID 货币类型,
openDate 开户日期,
openMoney 开户金额,
balance 余额,
pass 密码,
IsReportLoss 是否挂失,
customerID 顾客编号,
savingID 存款类型ID号 FROM cardInfo
GO

-- 5.交易模拟
/*
张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。
说明：当存钱或取钱（如900元）时候，会往交易信息表（tradeInfo）中添加一条交易记录，
      同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少900元）
*/
/*-----取款900交易信息表插入交易记录------------*/
select tradeType 存入或支取,
cardID 卡号,
tradeMoney 交易金额,
tradeDate 交易日期,
remark 备注 from tradeInfo;

SELECT cardID 卡号,
curID 货币类型,
openDate 开户日期,
openMoney 开户金额,
balance 余额,
pass 密码,
IsReportLoss 是否挂失,
customerID 顾客编号,
savingID 存款类型ID号 FROM cardInfo;
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
	VALUES('支取','1010 3576 1234 5678',900) 
/*--------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'
/*------存款300-------------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
	VALUES('存入','1010 3576 1212 1130',300) 
/*-------------更新银行卡信息表中的现有余额---*/
UPDATE cardInfo SET balance=balance+300 WHERE cardID='1010 3576 1212 1130'
/*------------存款1000-------------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
	VALUES('存入','1010 3576 1212 1004',1000) 
/*-------------更新银行卡信息表中的现有余额---*/
UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004'
/*------------取款1900-------------------*/
SELECT cardID 卡号,
curID 货币类型,
openDate 开户日期,
openMoney 开户金额,
balance 余额,
pass 密码,
IsReportLoss 是否挂失,
customerID 顾客编号,
savingID 存款类型ID号 FROM cardInfo
where cardID = '1010 3576 1212 1130';
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney)
	VALUES('存入','1010 3576 1212 1130',1900) 
/*-------------更新银行卡信息表中的现有余额-----*/
UPDATE cardInfo SET balance=balance+1900 WHERE cardID='1010 3576 1212 1130';




(二)
-- 创建QQ数据库
create database QQDB;

if exists(select * from sys.databases where name = 'QQDB')
	use QQDB;
-- 1.创建QQUSER表结构
create table QQUSER(
	QQID Bigint,
	Password Varchar(30),
	Lastlogtime Datetime,
	Online int,
	Level int,
	constraint PK_QQUSER primary key(QQID),
)

-- 2.创建用户基本信息表
create table BASEINFO(
	QQID Bigint, --主键
	Nickname Varchar(30), --昵称
	Sex char(2), --性别
	Age int, --年龄
	Province Varchar(30), --省份
	City Varchar(30), --城市
	Address Varchar(100), --地址
	Phone Varchar(30), --联系方式
	constraint PK_BASEINFO primary key(QQID),
)


-- 3.创建好友关系表
create table relation(
	QQID Bigint,
	RelationID Bigint,
	status Bit,
)

-- 4.为各表建立约束
-- （1）qq密码不得低于6位
Alter table QQUSER
Add constraint CK_PASSWORD check(len(Password)>=6);
-- （2）在线状态的值必须为0，1，2，分别表示在线、离线和隐身
Alter table QQUSER
Add constraint CK_ONLINE check(Online in (0,1,2));
-- (3)用户等级默认为0
Alter table QQUSER
Add constraint DK_LEVEL default 0 for Level;
-- (4)性别允许为空，输入则为'男'或者'女'
Alter table BASEINFO
Add constraint CK_SEX check(Sex is NULL or Sex in ('男','女'));
-- （5）年龄必须是1-100之间的整数
Alter table BASEINFO
Add constraint CK_AGE check(Age between 1 and 100);
-- (6)用户关系只能是0或者1
Alter table relation
Add constraint CK_STATUS check(status in (0,1));



-- 5.添加模拟数据
-- 向QQUSER表插入数据
INSERT INTO QQUSER (QQID, Password, Lastlogtime, Online, Level)
VALUES 
(564389, 'Jdsk23wed', '2020-2-4 10:23:22', 1, 5),
(564390, 'Fagddagg', '2020-5-7 18:44:56', 0, 8),
(564391, 'Dasgagda', '2020-3-7 18:33:02', 0, 9),
(564392, 'Afjkdsghk', '2020-3-5 12:04:05', 1, 8),
(564393, 'Afjdf2345', '2020-3-7 10:04:05', 0, 11),
(564394, 'Asdjfkljf', '2020-3-4 06:04:10', 1, 7);

-- 向BASEINFO表插入数据
INSERT INTO BASEINFO (QQID, Nickname, Sex, Age, Province, City, Address, Phone)
VALUES 
(564389, '涵养', '男', 18, '北京', '北京', '朝阳大街', '73853985946'),
(564390, '不洗澡的雨', '男', 25, '天津', '天津', '南开大学', '43729543'),
(564391, '叶落', '男', 78, '四川', '成都', '四川大学', '83408530280'),
(564392, '花开如梦', '女', 3, '安徽', '合肥', '科技大学', '803584032'),
(564393, '俗世', '男', 28, '江苏', '苏州', '苏州大学', '8084302806823'),
(564394, '香茗', '女', 11, '安徽', '芜湖', '师范大学', '3489852389328');

-- 向RELATION表插入数据
INSERT INTO RELATION (QQID, RelationID, Status)
VALUES 
(564389, 564390, 0),
(564389, 564391, 0),
(564389, 564394, 1),
(564389, 564392, 0),
(564390, 564389, 0),
(564390, 564392, 0),
(564390, 564393, 1);
go


select * from QQUSER;
select * from BASEINFO;
select * from relation;

-- 6.数据查询
-- （1）查询每个省份的QQ用户人数。

select Province 省份,COUNT(*) 人数 from BASEINFO
group by Province;

-- （2）查询至少30天没有登录账户的QQ用户。
select * from QQUSER
where DATEDIFF(day, Lastlogtime, getDate()) >= 30;


-- 7.修改数据
-- （1）将“564393”的登录状态改为隐身，最后一次登录时间设置为当前系统时间。
-- 数据更改前
select * from QQUSER where QQID = 564393;
Update QQUSER set Online = 2,Lastlogtime = GETDATE()
where QQID = 564393;
-- 数据修改后
select * from QQUSER where QQID = 564393;


-- （2）修改“花开如梦”的昵称为“繁花似锦”
-- 数据更改前
select * from BASEINFO;
Update BASEINFO set Nickname = '繁花似锦'
where Nickname = '花开如梦'
-- 数据更改后
select * from BASEINFO;


-- （3）将“564390”的好友“564392”拖入黑名单
-- 数据更改前
select * from relation where QQID = 564390 and RelationID = 564392;
Update relation set status = 1
where QQID = 564390 and RelationID = 564392;
-- 数据更改后
select * from relation where QQID = 564390 and RelationID = 564392;


-- 8.删除数据：删除多次在QQ中发布违法信息的用户“564390”
-- 首先删除与该用户相关的所有关系记录
delete from relation
where QQID = 564390 or RelationID = 564390;
-- 然后删除基本信息
delete from BASEINFO
where QQID = 564390;
-- 最后删除用户
delete from QQUSER
where QQID = 564390;

select * from QQUSER;
select * from BASEINFO;
select * from relation;