7.mysql用户管理，总结-count,exists,in，view易错点

1.理论

/*创建本地用户teacher,密码为123456，本地用户student，无密码
*/
create user `teacher`@`localhost` identified by '123456';
CREATE USER `student`@`localhost`; 
/*通过查看系统数据库mysql中的user表查看存在用户*/
use mysql;
select * from user;
/*删除用户*/
drop user if exists `teacher`@`localhost`;
drop user if exists `student`@`localhost`
/*创建本地用户xiaoming，密码123456，
赋予myschool数据库中student表增加和查询权限
*/
create user `xiaoming`@`localhost`  identified by '123456';
grant select,insert on myschool.`student` to `xiaoming`@localhost;
update student set sex='男' where studentno=10000;
/*为xiaoming@localhost用户授予myschool数据库中
vw_student视图的查询权限
*/
grant select,show view on 
myschool.`vw_student` to `xiaoming`@localhost;
/*使用mysqladmin命令修改root账户密码(需在DOS命令行执行)
*/
mysqladmin –u root –p password "4321"
#修改当前用户密码
alter user `root`@`localhost` identified  by '1234';
/*创建ATM存取款机系统数据库bankdb
创建登录用户并授权
普通用户bankmaster，密码为1234
可以在任意主机登录MySQL服务器
具有数据库bankdb的所有权限
*/
create database if not exists bankdb;
create user `bankmaster`@`%` identified by '1234';
grant all on bankdb.* to `bankmaster`@`%` ;

use bankdb;
DROP TABLE IF EXISTS `userInfo`;
CREATE TABLE `userInfo`  #用户信息表
(
  `customerID` INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '用户编号',
  `customerName` CHAR(8) NOT NULL COMMENT '用户名称',
  `PID` CHAR(18) UNIQUE NOT NULL COMMENT '身份证号',
  `telephone` CHAR(20) NOT NULL COMMENT '手机号码',
  `address` VARCHAR(50) COMMENT '居住地址'
)COMMENT='用户表';

DROP TABLE IF EXISTS `deposit`;
CREATE TABLE `deposit`  #存款类型表
(
  savingID  INT(4)  AUTO_INCREMENT PRIMARY KEY,
  savingName  VARCHAR(20) NOT NULL,
  descrip VARCHAR(50)
)COMMENT='存款类型表';

DROP TABLE IF EXISTS `cardInfo`;
CREATE TABLE `cardInfo`  #银行卡信息表
(
  `cardID`  CHAR(19) NOT NULL PRIMARY KEY COMMENT '卡号',
  `password` CHAR(6) NOT NULL DEFAULT "888888" COMMENT '密码',
  `curID`  VARCHAR(10) NOT NULL DEFAULT "RMB" COMMENT '币种',
  `savingID` INT NOT NULL COMMENT '存款类型',
  `openDate`  TIMESTAMP default current_timestamp NOT NULL COMMENT '开户日期' ,
  `openMoney`  DECIMAL(20,2) NOT NULL DEFAULT 1 COMMENT '开户金额' ,
  `balance`  DECIMAL(20,2) NOT NULL DEFAULT 1 COMMENT '余额',
  `IsReportLoss` int  NOT NULL  DEFAULT 0 COMMENT '是否挂失',
  `customerID` INT NOT NULL
)COMMENT='银行卡信息表';

DROP TABLE IF EXISTS `tradeInfo`;
CREATE TABLE `tradeInfo`  #交易信息表
(
  id int not null primary key auto_increment,
  cardID  CHAR(16) NOT NULL COMMENT '卡号',
  tradeDate  TIMESTAMP default current_timestamp NOT NULL  COMMENT '交易日期',
  tradeMoney  DECIMAL(20,2) NOT NULL COMMENT '实际交易金额',
  tradeType  CHAR(4) NOT NULL COMMENT '交易类型', 
  remark  TEXT COMMENT '备注'  
)COMMENT='交易信息表';
/*--加约束--$*/
ALTER TABLE cardInfo     
   ADD CONSTRAINT  FK_customerID FOREIGN KEY(customerID) 
   REFERENCES userInfo(customerID),
   ADD CONSTRAINT  FK_savingID  FOREIGN KEY(savingID) 
   REFERENCES deposit(savingID);
ALTER TABLE tradeInfo
  ADD CONSTRAINT  FK_cardID  FOREIGN KEY(cardID) REFERENCES cardInfo(cardID);

#存款类型
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;

INSERT INTO userInfo(customerName,PID,telephone,address )
     VALUES('张三','123456789012345','010-67898978','北京海淀');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612345678',1,1000,1000,1);

INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('李四','321245678912345678','0478-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612121134',2,1,1,2);

INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('王五','567891234532124670','010-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612121130',2,1,1,3);

INSERT INTO userInfo(customerName,PID,telephone)
     VALUES('丁六','567891321242345618','0752-43345543');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612121004',2,1,1,4);

SELECT * FROM userInfo;
SELECT * FROM cardInfo;

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('支取','1010357612345678',900);  
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010357612345678';

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
      VALUES('存入','1010357612121134',5000);   
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010357612121134';

/*--------检查测试数据是否正确---------*/
SELECT * FROM cardInfo;
SELECT * FROM tradeInfo;

/*---------修改密码-----*/
#1.张三（卡号为1010357612345678）修改银行卡密码为123456
update cardInfo set password='123456'
where cardid='1010357612345678';
#2.李四（卡号为1010357612121134）修改银行卡密码为123123
update cardInfo set password='123123'
where cardid='1010357612121134';
#查询账户信息
SELECT * FROM cardInfo;

/*---------挂失帐号---------*/
#李四（卡号为1010357612121134）因银行卡丢失，申请挂失
update cardInfo set IsReportLoss=1
where cardid='1010357612121134';
#查看修改密码和挂失结果
SELECT 
cardid as 卡号,
curID as 货币,
savingName as 储蓄种类,
opendate as 开户日期,
openmoney as 开户金额,
balance as 余额,
`password` as 密码,
case 
  when IsReportLoss=0 then '未挂失' 
  else '挂失'
end as 是否挂失, 
customerName as 客户姓名
FROM cardInfo, deposit, userInfo
WHERE cardInfo.savingID=deposit.savingID 
AND cardInfo.customerID = userInfo.customerID;

/*---------统计银行的资金流通余额---------------*/
#统计说明:存款代表资金流入,取款代表资金贷出
select tradetype,sum(tradeMoney) from tradeInfo
group by tradetype

/*--------查询本周开户的卡号,显示该卡相关信息-----------------*/
SELECT c.cardID 卡号,u.customerName 姓名,c.curID 货币,
d.savingName 存款类型,c.openDate 开户日期,c.openMoney 开户金额,
c.balance 存款余额,
if(IsReportLoss=0,'未挂失','挂失') 账户状态
FROM cardInfo c INNER JOIN userInfo u 
ON (c.customerID = u.customerID)
INNER JOIN deposit d 
ON (c.savingID = d.savingID )
#WHERE WEEK(NOW()) = WEEK(openDate);---- 错误语法，即不能实现想要的结果，跨年周却不等于
where floor(datediff(now(),'1900-01-01')/7)
=floor(datediff(openDate,'1900-01-01')/7);

/*select week('2021-12-31'),week('2022-01-01');
select 
floor(datediff('2021-12-27','1900-01-01')/7),
floor(datediff('2022-01-03','1900-01-01')/7)*/

/*---------查询本月交易(单笔)金额最高的卡号----------------------*/

SELECT DISTINCT cardID FROM tradeInfo 
WHERE  tradeMoney=
(
   SELECT MAX(tradeMoney) FROM tradeInfo
     WHERE MONTH(tradeDate)=MONTH(NOW())
     AND YEAR(tradeDate)=YEAR(NOW())
)
and MONTH(tradeDate)=MONTH(NOW())
and YEAR(tradeDate)=YEAR(NOW());
/*---------查询挂失帐号的客户信息---------------------*/
 SELECT customerName AS 客户姓名,telephone AS 联系电话 FROM userInfo 
 WHERE customerID IN (
 SELECT customerID FROM cardInfo 
 WHERE IsReportLoss=1
 );
/*------催款提醒：例如某种业务的需要，
每个月末，如果发现用户帐上余额少于200元，将致电催款。---*/
SELECT customerName AS 客户姓名,telephone AS 联系电话,balance AS 存款余额 
FROM userInfo INNER JOIN cardInfo ON  userInfo.customerID=cardInfo.customerID 
WHERE balance<200;

#1.创建视图：为了向客户显示信息友好,查询各表要求字段全为中文字段名。
DROP VIEW IF EXISTS vw_userInfo;
CREATE VIEW vw_userInfo #客户信息表视图
AS 
SELECT customerID AS 客户编号,customerName AS 开户名, PID AS 身份证号,
telephone AS 电话号码,address AS 居住地址  FROM userInfo;
#使用视图
SELECT * FROM vw_userInfo;
#2.创建视图：查询银行卡信息
DROP VIEW IF EXISTS vw_cardInfo;
CREATE VIEW view_cardInfo  #银行卡信息表视图
AS 
SELECT c.cardID AS 卡号,u.customerName AS 客户,c.curID AS 货币种类,
 d.savingName AS 存款类型,c.openDate AS 开户日期,
 c.balance AS 余额,c.password 密码,
 if(IsReportLoss=0,'未挂失','挂失') AS 是否挂失
FROM cardInfo c, deposit d,userinfo u
WHERE c.savingID=d.savingID AND c.customerID=u.customerID;
#使用视图
SELECT * FROM view_cardInfo;
#3.创建视图：查看交易信息
DROP VIEW IF EXISTS view_tradeInfo;
CREATE VIEW view_tradeInfo #交易信息表视图
  AS 
  SELECT tradeDate AS 交易日期,tradeType AS 交易类型, cardID AS 卡号,
  tradeMoney AS 交易金额,
  remark AS 备注  FROM tradeInfo ;
#使用视图
SELECT * FROM view_tradeInfo;
#使用事务完成转账
delimiter $$#改变语句结束标志符号
create PROCEDURE tranfer_money#创建存储过程，类似自定义方法
(
    _fromcardid char(19),
    _tocardid char(19),
    _tranfermoney decimal(20,2)
)
begin
  declare _balance decimal(20,2);#声明变量
  start transaction;
    INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
                VALUES('支取',_fromcardid,_tranfermoney);   
    UPDATE cardInfo SET balance=balance-_tranfermoney 
    WHERE cardID=_fromcardid;
    INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) 
                VALUES('存入',_tocardid,_tranfermoney);  
    UPDATE cardInfo SET balance=balance+_tranfermoney 
    WHERE cardID=_tocardid;
    select balance from cardInfo where cardid=_fromcardid
    into _balance;
    if(_balance<0) then#if在触发器等等可以写，正常用法
      rollback;
    else
      commit;
    end if ;
end $$
delimiter ;#改回语句结束标志符号;

call tranfer_money('1010357612345678','1010357612121130',30);#call  调用方法

 

 2.效果图及练习

use myschool;
update student set sex='男' where studentno=10000;

3.总结

#视图里的列要不要加主键啊？为何用Navcat一打开就提示要设呀？
#view是没法改结构的，它本身只是个引用，不存储实际数据，你可以把原表上的主键列加到view中去，先drop view。
/*
总而言之就是，对视图进行数据增删改查也会同步到原表，
但是原表也会同步到视图数据，且原表能改变结构，而视图不能

count,exists,in，view易错点
count(3)，只要括号里面有字符就可以，结果都一样，关键是group by
exists(),只要true就执行
in,区别=就是筛选一条，in是多条