1.理论1-视图，事务

table#表
view#视图，虚拟表（物理不存在，在查询的基础）

/*
1.实体完整性（主键）
2.域的完整性（check）,mysql通过触发器替代
3.引用完整性（外键）

1对1
多对1
1对多
多对多，应该中间加入临时表，变成1对多连接
E-R图绘图软件---visio(不专业)、PowerDesigner

*/

/*
事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
多个操作作为一个整体向系统提交，要么都执行、要么都不执行 
事务是一个不可分割的工作逻辑单元 
ACID:
1.原子性（Atomicity）
事务是一个完整的操作，事务的各步操作是不可分的(原子的)，
要么都执行，要么都不执行
2.一致性（Consistency）
事务开始之前以及事务完成以后，数据必须处于一致状态
3.隔离性（Isolation）
并发事务之间彼此隔离、独立，
它不应以任何方式依赖于或影响其他事务
4.永久性（Durability）
事务完成后,它对数据库的修改被永久保持
*/

/*
视图是一张虚拟表,结构和数据是建立在对表的查询基础上
视图中不存放数据,数据存放在视图所引用的原始表中
可以对视图进行insert,update,delete操作,受影响的是视图
所引用的原始表
*/

/* type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >
  unique_subquery > index_subquery > range > index > ALL
 一般来说，得保证查询至少达到range级别，最好能达到ref。*/
 
 /*mysqldump备份工具,在dos下执行---工具要运行，必须在bin目录下
*/
mysqldump -uroot -p myschool>路径/myschool.sql#备份到路径
#MySQL数据库的恢复/导入
#方法一：用SOURCE语法(MySQL命令行里执行,要先创建和选择数据库)
#先创建和选择数据库
create database school2;
use school2;
source 路径/myschool.sql;#备份所在路径
#方法二：用mysql客户端,在dos下执行,要先创建数据库
mysql -uroot -p myschool2<d:/java2201/mysql/myschool.sql

#利用SQL语句导出、导入数据
#SHOW VARIABLES like '%secure%';查看导出路径
#导出
SELECT result.* INTO OUTFILE 
'D:/mysql/Uploads/javaresult.txt'
from result inner join course
on result.subjectno=course.subjectno
where subjectname='java';
#导入
use myschool;
create table if not exists javaresult
(select * from result where 0>1);
LOAD DATA INFILE   
'D:/mysql/Uploads/javaresult.txt'
INTO TABLE  javaresult;
 

 2.理论2---三大范式（够用）3以后了解即可

#第二范式:除了主键以外,其他各列都要"完全"依赖于该主键
/*
order(订单表)
pk(orderid,productid)违反第二范式
orderid  orderdate  productid  price ...
001      3/5        p001       20
001      3/5        p002       30
002      5/3        p001       20
....
*/
#第三范式:除了主键以外,其他各列都要直接的依赖于该主键,而不是间接依赖
#依赖不能传递
order(订单表)违反第三范式
pk(orderid)
orderid  orderdate   customerid  customername
001       3/5         c001         microsoft
002       5/3         c002         ibm
....
product(产品表)
pk(productid)
productid  price  ....
p001       20
p002       30

正确的设计:

customer(客户表)
pk(customerid)
customerid  customername ....
c001        microsoft
c002        ibm

order(订单表)
pk(orderid) fk(customerid references customer(customerid))
orderid  orderdate  customerid  .....
001       3/5       c001         
002       5/3       c002        
..... 

product(产品表)
pk(productid)
productid  price  ....
p001       20
p002       30
...

orderdetail(订单明细表)
pk(id) fk(orderid,productid)
id  orderid   productid   qty ...
1   001       p001        100
2   001       p002        200
..... 

3.练习

/*
事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
多个操作作为一个整体向系统提交，要么都执行、要么都不执行 
事务是一个不可分割的工作逻辑单元 
ACID:
1.原子性（Atomicity）
事务是一个完整的操作，事务的各步操作是不可分的(原子的)，
要么都执行，要么都不执行
2.一致性（Consistency）
事务开始之前以及事务完成以后，数据必须处于一致状态
3.隔离性（Isolation）
并发事务之间彼此隔离、独立，
它不应以任何方式依赖于或影响其他事务
4.永久性（Durability）
事务完成后,它对数据库的修改被永久保持
*/
DROP TABLE if exists account;
CREATE TABLE IF NOT EXISTS  account (
  id int(11) not null auto_increment,
  name varchar(32) not null,
  cash decimal(9,2) not null,#
  PRIMARY KEY (id)
);
INSERT INTO  account   (name,cash) VALUES ('A',2000.00);
INSERT INTO  account   (name,cash) VALUES ('B',10000.00);
/*事务处理:方式1-set autocommit*/  #----对增加删除修改，添加事务，查询不需要

select * from account;#事务开始之前

#模拟从A账户转账3000到B账户
set autocommit = 0;#关闭自动提交模式
update account set cash=cash-3000 where name='A';
update account set cash=cash+3000 where name='B';
select * from account;#事务进行中
#commit;#提交事务，事务结束--选择1
rollback;#提回滚事务，事务结束---选择2

select * from account;#事务完成后
set autocommit = 1;#开启自动提交模式

/*事务处理:方式2-start transaction*/

select * from account;#事务开始之前

#模拟从A账户转账3000到B账户
start transaction;#开始一个事务
update account set cash=cash-3000 where name='A';
update account set cash=cash+3000 where name='B';
select * from account;#事务进行中
#commit;#提交事务，事务结束--选择1
rollback;#提回滚事务，事务结束---选择2

select * from account;#事务完成后

/*事务处理:方式3-begin*/

select * from account;#事务开始之前

#模拟从A账户转账3000到B账户
begin;#开始一个事务
update account set cash=cash-3000 where name='A';
update account set cash=cash+3000 where name='B';
select * from account;#事务进行中
#commit;#提交事务，事务结束--选择1
rollback;#提回滚事务，事务结束---选择2

select * from account;#事务完成后

#创建视图vw_javaresult,查询java最近一次的考试成绩
#(学生姓名,考试时间,考试成绩)
drop view if exists vw_javaresult;
create view if not exists vw_javaresult as
select studentname,examdate,studentresult
from student join result
on student.studentno=result.studentno
join course
on course.subjectno=result.subjectno
where result.subjectno=
(
select subjectno from course where subjectname='java'
)
and examdate=
(
select max(examdate) from result join course
on course.subjectno=result.subjectno
where subjectname='java'
);
#使用视图vw_javaresult
select * from vw_javaresult order by studentresult;
select * from vw_javaresult order by studentresult desc;
/*
创建视图:vw_studentsumresult.查看学生总成绩:
编写代码查询每个学生各学期参加考试的总成绩，
每门课成绩以该生参加的最后一次考试为准
没有参加考试的学生信息也要输出
输出列为:(学生姓名,年级名称,总成绩)  有的不支持if
*/
/*  我的  ------------------------------
drop view if exists vw_studentsumresult;
create view  vw_studentsumresult as#if not exists不可写，会报错
select studentname,gradename,subjectname,examdate,studentresult
from result r
join student s
on r.studentno=s.studentno
join course c
on r.subjectno=c.subjectno
join grade g
on c.gradeid=g.gradeid
where examdate=
(
select max(examdate) from result
where studentno=r.studentno and subjectno=r.subjectno
)
and studentno  not in
( select studentno from result
where studentno=r.studentno and subjectno=r.subjectno
)
order by convert(studentname using gbk),gradename,subjectname
;

select studentname,gradename,subjectname,examdate,studentresult
from result r
join student s
on r.studentno=s.studentno
join course c
on r.subjectno=c.subjectno
join grade g
on c.gradeid=g.gradeid
where examdate=
(
select max(examdate) from result
where studentno=r.studentno and subjectno=r.subjectno
)
union#未参加考试的
select studentname,gradename,subjectname,examdate,studentresult
from result r
join student s
on r.studentno=s.studentno
join course c
on r.subjectno=c.subjectno
join grade g
on c.gradeid=g.gradeid
where r.studentno not in
(
select studentno from student
left join result
on student.studentno=result.studentno
where result.studentno is null
)

*/
###老师
drop view if exists vw_studentsumresult;
create view  vw_studentsumresult as#if not exists不可加入
select studentname,
/*
case
    when gradename is null then '缺考'
    else gradename
end gradename,
case
    when total is null then '缺考'
    else total
end total
*/
#有的不支持if
if(gradename is null,'缺考',gradename) 'gradename',
if(total is null,'缺考',total) 'total'
from student
left join#左外连接，临时表

(
select studentno,gradename,sum(studentresult) total
from result r
join course c
on r.subjectno=c.subjectno
join grade g
on g.gradeid=c.gradeid
where examdate=
(
select max(examdate) from result
where studentno=r.studentno and subjectno=r.subjectno
)
group by studentno,gradename
) temp1#临时表

on student.studentno=temp1.studentno;

#使用视图vw_studentsumresult
select * from vw_studentsumresult;
/*
视图是一张虚拟表,结构和数据是建立在对表的查询基础上
视图中不存放数据,数据存放在视图所引用的原始表中
可以对视图进行insert,update,delete操作,受影响的是视图
所引用的原始表
*/
drop view if exists vw_student;
create view vw_student
as 
select * from student where studentno>20000;

update vw_student set studentname=concat(studentname,'abc')
where studentno=20011;

#给student表identitycard创建一个唯一索引
alter table student add unique key(identitycard);
alter table student drop index identitycard;#删除
#邮箱Email，添加常规索引
alter table student add index (email);
drop index email on student;
#查看索引
show index from student;
show keys from student;
/* 创建一个表 aricles ,注意数据引擎是MyISAM格式,只有MyISAM才支持全文索引*/
DROP TABLE IF   EXISTS  articles; 
CREATE TABLE  IF NOT EXISTS articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200), 
      body TEXT,
      FULLTEXT (title,body)
 )ENGINE = MyISAM;#数据引擎是MyISAM格式
 INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');
 INSERT INTO articles (title,body)  
 ( select title,body from articles);
 
 /* 使用全文索引查询 */
SELECT * FROM articles  
WHERE MATCH (title,body) AGAINST ('database');

/*EXPLAIN函数,分析select语句,注意以下要在dos中登录mysql后执行*/
use myschool;
EXPLAIN 
SELECT * FROM articles  WHERE MATCH (title,body) AGAINST ('database') \G;
/*不使用全文检索*/
EXPLAIN SELECT * FROM articles  WHERE  title 
like '%database%' or body like "%database%" \G;
 /* type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >
  unique_subquery > index_subquery > range > index > ALL
 一般来说，得保证查询至少达到range级别，最好能达到ref。*/
 
 /*mysqldump备份工具,在dos下执行---工具要运行，必须在bin目录下
*/
mysqldump -uroot -p myschool>E:/myschool1.sql#路径/myschool.sql#备份到路径
#MySQL数据库的恢复/导入
#方法一：用SOURCE语法(MySQL命令行里执行,要先创建和选择数据库)
#先创建和选择数据库
create database school1;
use school1;
source E:/myschool1.sql#路径/myschool.sql;#备份所在路径
#方法二：用mysql客户端,在dos下执行,要先创建数据库
mysql -uroot -p myschool2<E:/myschool.sql

#利用SQL语句导出、导入数据
#SHOW VARIABLES like '%secure%';查看导出路径
#导出
SELECT result.* INTO OUTFILE 
'D:/mysql/Uploads/javaresult.txt'
from result inner join course
on result.subjectno=course.subjectno
where subjectname='java';
#导入
use myschool;
create table if not exists javaresult
(select * from result where 0>1);
LOAD DATA INFILE   
'D:/mysql/Uploads/javaresult.txt'
INTO TABLE  javaresult;

week一年的第几周，特殊情况下不能判断是否同一周