Mysql随堂笔记

-- 关系型数据库-建立在关系模型上的数据库
-- 数据结构：二维表
-- 操作指令集合SQL （分为DFL，DML[DQL]和DCL） 即数据定义语言，数据操作语言（数据查询语言）和数据控制语言
-- 完整性约束：表与表之间的约束

-- show engines;  查看mysql的的默认引擎
-- show warnings； 查看警告

-- 查看所有数据库
-- show databases;

-- 创建数据库
-- Create database bedroom charset utf8;

-- 查看指定的数据库
-- show databases like 'bedroom\_%';  -- 查看以bedroom开头的数据库
-- show databases like 'bedroom_%';   -- 相当于'bedroom%'

-- 更改数据库
-- Alert databases bedroom charset GBK；-- 更改字符集  数据库名字一般不被修改

-- 删除数据库
-- drop databases 数据库名字；

-- 创建表名之前加上数据库名  或者在创建数据库之前加上  use bedroom;
/*Create table if not exists bedroom.person(
id varchar(10),
name varchar(10),
gender char(10),
age int,
position char(10)
)charset utf8;*/

-- show create table person;      查看表的创建语句 \g 相当于;  \G  相当于纵向的查看表格

-- show tables;  查看所有表

-- desc person;   describe person;    show columns form person;   查看表结构

-- rename table 老表名 to 新表名;        修改表名
-- Alert table 表名 charset=GBK;         修改字符集

-- Alter table person add column （字段名）first；

-- Alter table person modify 字段名 数据类型 【属性】【位置】    修改字段通常是修改属性或者数据类型

-- Alter table person change 字段名 旧字段 新字段 【属性】【位置】   修改字段

-- Alter table person drop 字段名；

--mysql> alter table 表名 modify column 字段名 类型;
例如

数据库中address表 city字段是varchar(30)

修改类型可以用（谨慎修改类型，可能会导致原有数据出错）

mysql> alter table address modify column city char(30);

修改长度可以用（修改长度，要保证不短于已有数据，以保证原有数据不出错）

mysql> alter table address modify column city varchar(50);

-- drop table 数据表1，数据表2···；    可以一次删除多张数据表

-- insert into person (字段名) values (字段),(字段);
-- insert into person values('001','Tony','male',20,'boss'),('002','Tom','male',20,'employee');

-- select * from person;                                  查看所有字段
-- select (字段名) from person where [条件  例如id=1]     按照条件查询

-- update 表名 set 字段=值【where 条件 例如where id=1】   更新数据

-- delete from 表名[where 条件]                           删除字段

-- show variables like 'character_set%';                  查看服务器对外处理默认的字符集
-- show character set;                                    查看当前的字符集 
-- set character_set_client=GBK;                          把服务器识别客户端的字符集改为GBK
-- set character_set_results=GBK;                          设置服务器给客户端的字符集为GBk
-- set names=GBK;                    快捷键把服务器与客户端之间的字符集设置为GBK 相当于把client result connection一次性设置

-- show collation；                   查看所有校对集
-- 创建表后面加上   charset UTF8 collate UTF8_bin            bin:binary  二进制比较
--                                                           cs;case sensitive      区分大小写
--                                                           ci:case insensitive    不区分大小写
-- order by 字段名 [asc|dec]    asc:升序（默认）  dec:降序
-- Alter table （表名）collate=（校对集）                     修改校对集

/*数据库的数据类型分为  数值型，字符串和时间和日期类型
数值型又分为整数型和小数型

而整数型分为五大类

Tinyint    迷你整型  占1个字节
Smallint   小整型    占2个字节
Mediumint  中整形     占3个字节
int        标准整型       占4个字节
bigint     大整型     占8个字节

可以用unsigned 来设置整型的无符号  也可以用用 Zerofill设置零填充，保证显示位数的统一，而且会导致自动设置为无符号

整形中有浮点型和定点型

而浮点型又可以直接用用float表示（没有小数部分）  float（M，D）M表示长度  D表示小数长度
浮点型可以直接插入，也可以用科学计数输入
整数部分不能超出长度精度，而小数部分可以超出精度（系统会四舍五入）
如果由于系统四舍五入导致位数增加一个长度  系统允许

定点型decimal(M,D)  M最大为65，D最大为30  定点型整数部分的精度得到绝对保证(不会四舍五入)，小数部分有可能
定点型的整数部分一定要保证精度，小数部分不用，系统会自动四舍五入

时间类型 分为 Datetime     占用8个字节   格式为YYYY-MM-DD  HH-MM-SS   (1000-01-01 00:00:00 ~ 9999-12-31 23:59:59)
     Date         占用4个字节   格式为YYYY-MM-DD             (1000-01-01 ~ 9999-12-31 )
              time         占用4个字节   格式为HH-MM-SS               (00:00:00 ~ 23:59:59)
              Timestamp    占用3个字节   格式为YYYY-MM-DD  HH-MM-SS   (1970-01-01 00:00:01 ~ 2038)  中间的时间可以为负数，表示过去的时间段
              year         占用1个字节   格式为YYYY                   ( 1901 ~ 2155)

字符串类型  分为六大类 char，varchar，text

char()存储按照给定的长度存储，最大长度玩255  varchar按照实际长度加上1到2个字节（在超过255个长度时为2个字节）存储最大长度为65536

在长度超过255的时候，通常用text文本格式和blob（二进制数据格式，一般不用）

enum的原理是：枚举在进行数据规范的时候（定义的时候），系统会建立一个数字和元素的对应关系（放在日志中），
然后再进行数字插入的时候，系统自动将字符串转换成对应的数字存储，在提取的数据的时候，系统自动将数字转换
成字符串显示。可以通过用提取数据加上 0 来检测该数据类型 若为数值类型 则结果为该数值 若该数据为字符串 结果为0

enum中可以直接插入数值  例如  insert into （表名）values('1','2');

集合（set）类似于枚举  但是相当于复选框
集合中每一个元素的位置都对应一个二进制位，被选中则位1  没选中则为0  最后的数值是从后面开始排（然后转化成十进制）

mysql的实际记录长度
mysql中规定：任何一条记录最长都不能超过65535字节（varchar永远达不到理论值，实际存储长度有字符集决定）
在UTF8下 varchar 的顶配是21844       21844*3+2=65534<65535
在GBK下  varchar 的顶配是32766       32766*2+2=65534<65535
 mysql记录中：如果记录中任何一个字段允许为空  那么系统会自动从整个系统中保留一个字节来存储null（若要释放null中存储的字节
 必须保证任何字段都不为空）
 
 create table （表名）(
 age Tinyint(1) not null,           加上一个字节把内存刚好占满
 name varchar(21844) not null  
 )charset UTF8;
 
create table （表名）(
age Tinyint(1) not null,
name varchar(32766) not null
)charset GBK;

mysql 中的text文本字符串：不占用存储长度，属于额外长度  但是text文本字符串也属于记录的一部分  一定中占据记录的部分长度
用来保存数据地址以及长度

列属性里面的空属性里面分为null 和 not null  
列属性里面分为comment  用于对字段的描述
列属性里面的默认值default    例如age tinyint unsigned default 0; (默认0岁)

另外还有主键 唯一键 自增长等属性

一张表只有一个字段可以主键，用来约束字段唯一的数据  即在第一次添加数据之后便不可以再次修改（除非删除主键）
添加主键的方法为  第一种：可以在创建表的时候直接添加  primary key  但是这种方法只能为一个字段添加主键
                  age char(10) primary key,
      第二种：也可以在创建表之后 为字段添加主键
                  create table 表名 (
                  age char(10),
                  name varchar(10),
                  primary key(age,name)              为age和name添加复合主键 
                  )charset UTF8;
                  第三种：在表创建完之后 再额外添加主键
                  Alter table modify age char(10) primary key;
                  Alter table 表名 add primary key(字段);

主键的约束 ：不允许字段中的数据修改

删除主键 

Alter table 表名 drop primary key;           
只有在删除主键后才能更新字段  数据直接删除主键，因为一张表只有一个主键

自增长 当对应的字段不给值 给默认值 或者为null 会被系统自动的触发 系统会从当前的最大值（起始值是1）自动做加 1 处理 得到不同的字段
show variables like 'auto_increment%';
得到 auot_increment_increment  为1  
  auto_increment_offset     也为1
     即起始值为1，而且增长步数为1
    
    
自增长(auto_increase)通常是和主键搭配

create table My_auto(
id int primary key auto_increase comment '自动增长',      称为逻辑主键
name char(10) not null
)charset UTF8;

1.任何字段要做自增长必须要求本身是一个索引key(key一栏有值)
2.字段类型必须是数值的整型
3.一张表最多只有一个自增长

insert into My_auto values(null,'Tony');
insert into My_auto values('Tim');
insert into My_auto values(default,'Mary');         这三种默认值也能触发自增长

insert into My_auto values(6,'American captain');   下次从6开始自增长
但是不能插入  不能插入比当前最大值还小的值

修改自增长
Alter table 表名 auto_increment='数值'               数值也不能比当前自增长数值的最大值  小

删除自增长
Alter table 表名 id int;                              直接重新定义字段不加上自增长就行了

唯一键
一种表中往往需要多种唯一的字段，数据不能重复，但是一个表中又只能有一个主键，唯一键解决了一张表中需要多个唯一字段的问题
唯一键的性质和主键差不多，唯一键允许自动为空，而且可以多个为空（但是空字段不参与比较）

在一张表没有主键的情况下，出现唯一键并且不为空的情况，系统会默认把这个唯一键当作主键 显示 pri

增加唯一键
         
方法一 在创建表的时候直接跟上unique/unique key
        creat table My_unique(
  id int unique comment'学号'
        )charset UTF8;
方法二 在字段后面直接再加上所有需要设置唯一键的语句
        create table My_unique(
        name char(10) not null,
        age int not null,
        unique key(name,age)                 key一栏会显示pri   但是 show create table My_unique2； 仍然显示uni
        )charset UTF8;
方法三  创建表之后追加唯一键
Alter table My_unique add unique(name);

唯一键约束  空的多个字段并不冲突  但是非空的唯一键会发生冲突

更新唯一键或者删除唯一键

更新唯一键：删除后再新增
删除唯一键：Alter table 表名 drop index '索引名字';  系统默认把唯一键字段名当作索引名  

索引的意义
1.提升搜索的效率
2.约束数据的有效性（唯一键等）

增加索引的前提条件：索引本身会产生索引文件（有可能比数据本身还大）非常耗费磁盘空间

如果某个字段要作为条件搜索经常使用，可以增加索引
如果某个字段要进行数据的有效性约束，也可以使用（主键和唯一键）

mysql中提供多种索引
1.主键（primary key）
2.唯一键（unique/unique key）
3.全文索引（filltext index）
4.普通索引（index）

全文索引：对全文的关键字进行搜索

英文状态下确定关键字很容易 英文之间存在着空格，可以进行分词搜索 而中文状态下，拆分（sphinx）变得非常困难

范式（Normals format）
是一种分层的结构规范，分为六层，每一层都比上一层更严格，若要满足这一层范式的要求是必须满足上一层范式。范式只为解决空间问题
所以数据库设计不可能完全按照范式要求来设计，一般情况下只满足三层范式即可
1NF；满足一范式后，取出的数据具有原子性，不必进行拆分即可直接拿来用，所以实现该范式的方法是将数据表进行拆分之后再进行存入

2NF：在出现复合主键（两个字段设置主键）的表中，其他字段依赖其中的部分字段（需要两个字段配合使用才具有唯一性），此不满足第二
范式
解决方案1：对该表格进行拆分，将两个设置主键的字段分开，配合其他字段，生成两个表格。
解决方案2：取消该表格的复合主键，可以选择加上 id 等逻辑主键

3NF：为满足第二范式，在一张表中加上id后，存在传递依赖，即一个字段依赖另一个字段，而另一个字段依赖表中的逻辑主键id，不满足第三
范式（例如，一张老师任课表中，老师的性别依赖老师字段，而老师字段依赖表中id主键，存在传递依赖，即违反第三范式）
解决方案：将存在传递依赖的字段和依赖的字段本身拿出来，单独做一个表，需要对应信息的时候，再把单独表的主键加进来
（具体体现为，老师的名字和性别单独形成一张表，并且加上id主键，而在主表的信息对应的时候，把单独表的逻辑主键id，加进主表
但是主表中就没有老师本人的名字和性别，实现了对信息的精确管理）

逆规范化
有时候，在查询一个表信息的时候还需要获取其他表中的数据，但是这种方法会显得效率很低，所以这时候会在主表存储数据的时候不添加其
他表的主键，而是添加其主要字段（体现为，在老师任课表的主表中不去存储其他表的主键id，而存储老师的姓名，从而提高查询的效率，但
是会增加数据的冗余）

实际上，逆规范化就是用磁盘空间来换效率

数据库的高级操作

当存在主键冲突(duplicate key)的情况下插入字段

通过更新来解决
insert into 表名 values()on duplicate key update 字段=新值;
通过替代来解决
replace into 表名 values();

蠕虫复制
creat table 新表名 like 旧表名;                 复制旧表的结构

insert into 表名 select * from 旧表名;          复制旧表的数据，并且成指数倍的增加

更新数据
update 表名 set 字段=值 [where 条件][limit 更新数量]          后加 limit 3   意思是更新前三个

当然 limit 也可以限制数据长度（数量）
select * from 表名 limit 3；                                  查询表中前三条记录
limit 还可以限制起始位置,长度
select * from 表名 limit 0,3;                    数据的记录是从 0 开始编号的
该用法主要用来实现分页
offset（起始位置）=（页码-1）*每页数据量        length  每页数据量

删除数据
delete from 表名 [where 条件][limit 限制数量]    限制删除数据的数量

删除表结构
truncate 表名;                                   清空表：重置增长等结构  

查询语句
select * from 表名;       等价于   selest all * from 表名;

查询中去除重复的语句
select distinct * from 表名;

select 字段名 as 别名;       比如select name as 名字;   name字段将在表中显示 名字

一般查询的数据都有 数据源
数据源分为单表数据源，多表数据源，查询语句

select * from 表名                    这里指的是单表数据源
select * from 表名1,表名2,···;        可以查询多表的数据，但是这样查询出来的结果是用一种表去匹配另一张表，数学上称之为
笛卡尔积，几乎不用该种方式，应该尽量避免

select * from (select * from 表名) as 表的别名;   称之为子查询

where 子句

where子句用来增加条件，筛选数据
where子句返回的结果是true（1）或者false（0）

比较条件：>=,<=,>,<,!=,<>(即不大于也不小于，就是等于),=（相当于等于的意思，并没有侧重于赋值）,like,between and,in/not in
逻辑运算符:&&(and),||(or),!(not)

增加值
update 表名 set 字段名=floor(rand()*20+20)；   ceil是向上取整，floor向下取整的意思，rand和java里面的random用法一样  插入20-40内的随机数

然后按条件查询
select * from 表名 where id=1||id=3||id=5;     按照逻辑进行查找
select * from 表名 where id in(1,3,5);         相当于id匹配区间内的值

找到学号在10到15号之内的
select * from 表名 where id>=10 &&(and) id<=15;   等价于  select * from 表名 where id between 10 and 15;
但是between 本身是闭区间

分组（group by 字段）的意思：是为了统计数据，按照字段进行数据统计

count();统计分组后的记录数，每一组有多少记录
min();统计该组的最小值
max();统计该组的最大值
avg();统计该组的平均值
sum();统计改组的总和

select sex,count (*),min(age),max(heigth),sum(id) from 表名 group by sex;
通过对sex分组，所以select 后面接sex，而count是对整个组的记录进行统计，所以是接 * ，然后按sex分组

而且 group by 字段 [asc|desc]  在对分组的结果合并以后会默认对结果进行排序 并且是升序

按照多字段分组
select id,sex,count(*) from 表名 group by id,sex;            先按照id分组，再按照sex分组
还可以通过 group_concat(name)  加上统计的名字

group by 字段名 with rolup;   任何一个分组后都只有小组，都需要向上级分组进行统计，根据当前分组的的字段，这就是回溯统计
回溯统计的时候会将字段置空

多字段回溯
考虑第一层分组会有一次回溯，第二层分组要考虑第一层分组组数，组数是多少，再加上一（总数统计）就是回溯统计的记录数

having 子句

having和where一样是进行字段判断的

where是针对磁盘数据进行判断，在进入内存之后，会进行分组操作，这时候就要求用having来处理
having几乎能做where能做的，而where不能做having所有的很多事情

1.分组统计的结果或者统计函数只能使用having能够使用
2.having能够使用别名，而where却不能，where是从磁盘读取数据，而磁盘中并没有别名
select 字段名1,count(*) as 别名 from 表名 group by 字段名1 having 别名>=2;

order  进行排序，根据某个字段按照升序或者降序进行排序，依赖校对集
order by 字段名 [asc|desc]               一般默认是升序

多字段排序           select * from 表名 order by 字段，字段;

连接查询
将多张表进行记录的连接（按照某个条件进行数据拼接），最终结果是，记录有可能会有变化，字段数一定会增加（至少是两张表的合并）

连接查询的意义：用户需要的数据来自多张表

连接查询分类
sql中将连接查询分为：内连接，外连接，自然连接和交叉连接

交叉连接（cross join）：从一张表中循环去除一条记录，每一条记录都去另外一张表中进行匹配，匹配一定成功，叫做笛卡尔积，
但是笛卡尔积并没有意义，要尽量避免
交叉连接的意义：保证连接结构的完整性
select * from 表名1 cross join 表名2;
表1和表2都是数据源

内连接[inner join]
从左表的表中取出一条记录，去与右边的表中的所有记录进行匹配，匹配必须是某个条件在左边的表中和右边的表中相同最终才会保留结果

select * from 表名1 [inner] join 表名2 on 表1名.字段= 表2名.字段;         on相当于连接条件

字段别名和表别名的使用
在查询数据的时候，不同表有相同字段，这时候需要加上别名才能区分，而表名太长，就可以用别名区分
select s.*,c.name as c_name,c.id as c_id                 使用表的别名
from 表1名 as s [inner] join 表2名 as c                  使用字段的别名
on s.id=c.id;

内连接可以没有 on 以后的条件，然后结果直接是笛卡尔积
内连接也可以用where代替 on （但是where没有on效率高）

外连接
outer join   以某张表为主表，拿出里面的所有记录，然后每条记录与另外的表进行连接，不管能不能匹配上条件，最终都会保留，能匹配
则正确保留，不能匹配，其他表中的字段都会置空null

外连接分为两种，以某张表为主表
     left join：左外连接，以左表为主表
  right join：右外连接，以右表为主表

select * from 左表 left/right join 右表 on 左表.字段=右表.字段;
虽然左表连接和右表连接有主表差异，但是显示的结果永远是左表的数据在左边，右表的数据在右边

自然连接
自然连接（natural join)这是自动匹配条件，系统以字段名作为匹配模式（同名字段就作为条件，多条同名字段都作为条件）

其实，内连接和外连接都可以模拟自然连接，就是使用同名字段或者合并字段
左表 left/right/innerjoin 右表 using (字段名);             使用同名字段作为连接条件，自动合并条件

外键
外键（foreign key）外面的键（键不在自己表中），如果一张表中有一个字段（非主键）指向另外一张表的主键，那么将该字段成为外键

增加外键
外键可以在创建表的时候或者创建表之后增加（但是要考虑数据问题）
创建表的时候增加外键  在所有字段后面  使用 foreign key(外键字段)references 外部表(主键字段);

创建表添加外键
create table 表名(
name char(10) not null comment '学生姓名',
c_id int comment '班级id'
foreign key(c_id) references 表名(主键字段)
)charset UTF8;

外键：要求字段本身是一个普通索引，如果不是，会先创建一个索引，然后再创建一个外键本身

在创建表格之后增加外键
Alert table 表名 [constraint (指定的外键名字)] add foreign key(c_id) references 外部表名(主键字段);
外键只能删除后增加  而不能修改

删除外键
Alert table 表名 drop foreign key 外键名（可以通过在创建外键的时候指定外键名，以便删除）

也可以通过查看表的创建语句来查看   show creat1 table 表名（含外键）;

外键默认的作用有两点
一个是对父表：父表数据进行写操作（删和改丢必须涉及到主键本身，如不涉及主键则没有影响）对应的数据在子表中被数据引用，就不允许
修改等操作

一个是对子表：子表数据进行写操作（增和该）的时候，如果对应的外键字段在父表找不到对应的匹配，那么操作会失败（例如子表中的外键
id与父表中的c_id匹配，而c_id中没有5，则在子表中无法添加id为4的字段）

外键的建立条件
1.外键要存在，首先必须保证存储引擎是innodb（默认的存储引擎），如果不是innodb，外键可以创建成功，但是没有约束效果
2.外键字段的字段类型必须与父表的主键字段类型一致
3.一张表的外键名（默认的外键名不会重复，如果自己设置外键名则要考虑）不能重复
4.增加外键的字段（外键已经存在的话），必须保证数据与父表主键对应

外键约束
所谓外键的约束，就是指的外键的作用
之前所将的外键作用，是默认的作用，其实可以通过对外键的需求，对外键进行定制操作
外键的约束有三种约束模式，都是针对父表的约束

1.district：严格模式（默认的），父不能删除或者更新一个已经被子表引用的数据
2.Cascade：级联模式，父表的操作，对应子表关联的数据也被跟着修改  （父表id改成3，子表c_id自动改成3）
3.set null ：置空模式，父表的操作，子表对应的数据（外键字段）被置空

通常的一个合理做法（约束模式），删除的时候子表置空，更新的时候子表级联操作
create table 子表名(
id int primary key auto_increment,
name char(10) not null,
c_id int,
foreign key c_id references 父表 (主键字段) on delete set null on update cascade           这里设置为删除置空和更新级联
)charset UTF8;
所以删除置空的前提条件（允许置空，否则创建外键失败）

联合查询
将多次查询（多条select语句），在记录上进行拼接（字段不会增加）

由多条select构成 每一条select语句获取的字段数必须严格一致（但是与字段类型无关）保留表格的基本格式
select 语句1
union [union选项]
select 语句2···

union选项有两个
   all：保留所有
   distinct：去掉重复的  默认的
 
联合查询的意义
1.查询对同一张表，但是需求不同的时候
2.多表查询，多张表的结构完全一样，保存的数据（结构）也完全一样的

在联合查询中  order by 不能直接使用，需要对查询语句使用括号才行，要使order by 生效，必须搭配limit

子查询（sub query）查询是在某个查询结果上进行的（一条select语句中包含了另一条select语句）

子查询分类：
 按位置查询（子查询在外部查询中出现的位置）
    form子查询：子查询跟在from之后     
 where子查询：子查询跟在where之后
 exists子查询：子查询跟在exists之后

    按结果查询：根据子查询得到的数据进行分类（理论上来说任何一个查询结果都可以理解为二维表）
    标量子查询：子查询得到的结果是一行一列
 列子查询：列子查询得到的结果是一行多列
 行子查询：行子查询得到的结果是一列多行
    上面几个出现的位置都在where之后
    表子查询：子查询出现的结果是多行多列（出现的位置是在from之后）
 
   
标量子查询
    想获取某个班上的所有学生
    1.确定数据源：获取所有学生
    select * from 表名 where c_id=?;
    2.获取班级id，可以通过班级名获取
    select id from My_class where c_name='班级名';           id值一定只有一个值（一行一列）

列子查询
    slect * from My_student where c_id in (select * from My_class);

列子查询的结果会进行比较，一行多列，需要使用in作为条件匹配，mysql中还有其他几种条件all，any，some
any 和some 作用一样（所有比较中null不参与比较，即结果中都没有null）

select * from My_student where c_id=any(select * from My_class);            选择c_id等于My_calss中的任何一个

select * from My_student where c_id=all(select * from My_class);            选择c_id等于My_class中的所有（不可能存在）
所以any 只要满足一个条件就为true  而all要满足所有条件才可以

行子查询
行子查询返回的结果可以是多行多列
select * from My_class where age=(select max(age) from My_student)and height=(select max(height) from My_class);
这条语句一样的能够找到，但是它使用了两个条件，而行子查询要求一个条件
行子查询需要构造行元素，行元素由多个字段组成

select * from My_class where (age,height)=(select max(age),max(height) from My_student);

其中 （age，height）被称为行元素

表子查询：子查询返回的结果是多行多列的二维表，子查询返回的结果是当作二维表来使用

需求：找出每个班中最高的学生

1.确定数据源：首先将学生按照身高进行降序或者升序
select * from My_student order by height desc;              
2.从每个班中选出第一个学生
select * from My_class group by c_id;                   按照降序排序，第一个就是最高的

表子查询
select * from (select * from My_student order by height desc)as student group by c_id;
 as student  通过添加别名把括号里面的结果当作一个表名   from 后面只能跟一个表名

exists查询
exists：就是存在的意思，用来判断某些条件是否满足（跨表），exists是接在where之后，exists返回的结果只有1（true）和0（false）

需求是查询所有的学生，前提是班级必须存在
查询数据源
select * from My_student where ？；
确定条件是否满足
exists （select * from My_class）;          查看是否成立

select * from My_student where exists (select * from My_class);

视图(view)
视图是一个种有结构（有行有列的二维表结构）但是没有结果（结果中不会真实存放数据）的虚拟表，虚拟表的数据来源不是自己定义的，
而是从基表中产生（基表的数据来源）

创建视图
create view 视图名字 as select语句;       select语句 可以是普通查询，连接查询，联合查询，子查询
视图分为单表视图（基表只有一个）和多表视图（基表最少是两个以上）
create view 视图名字 as slect * from My_student;
create view 视图名字 as slect s.*,s.c_name,s.c_room from My_student as s left join My_class as c on s.c_id=c.c_id; 
注意视图基表有多个基表的情况下，注意字段名不能重复，可以选择使用别名

查看视图
视图是一张虚拟表，表的所有查看方式都适用于视图，show tables [like]/desc 表名/show create tables/

show creat view 试图名字;       查看视图的创建语句

视图主要是为了查询，把视图当作表一样使用就行了
视图的执行的本质就是执行封装的select语句

修改视图
视图本来不可以修改  但是视图的开源可以修改
修改视图就是修改视图的来源语句（select语句）
Alert view 视图名字 as 新的select语句;

删除视图
drop view 视图;       删除视图

视图意义
1.视图可以节省sql语句：将一条复杂的select查询语句使用视图进行保存，以后可以直接对视图操作
2.数据安全：视图操作主要是针对查询的，如果对视图结构进行操作（删除），不会影响到基表数据
3.视图往往是在大项目中使用，而且是多系统使用，可以对外进行控制数据的可见性，可以隐藏某些数据（数据安全）
4.视图可以对外友好型，对每个系统进行不同的设计
5.视图可以更好的进行权限控制

不能向多表视图中插入数据，同样，多表视图也不能删除数据，但是可以向单表视图中插入数据

可以向单表视图插入数据，但是视图中必须提供有基表中存在的字段，若允许为null，则可以成功插入，若字段不允许为空，则插入失败

更新数据（不论是单表视图还是多表视图都可以更新视图）
update 视图名 set 字段名=字段值 where 条件;
如果在更新后，把某个字段值为null的字段更新，则更新后的值自动匹配到到原字段上
update 视图名 set id=3 where id=5;     (该视图中没有id=5的值，而更新完之后，id=3，而且依旧会匹配到另一张表中id=3的值)

更新限制:(with check option)：如果对在视图创建的时候对某个字段进行了限制，那么在对视图进行数据更新的时候，系统会进行验证
create veiw 视图名 as select * from 基表名 where age>30 with check option;
表示视图的数据来源都是都是年龄大于30岁的    而with check option 则限制了在视图更新的时候，不能将大于30的数据更新为小于30
而且  更新视图中查不到的数据时   会没有效果

视图算法
create view 视图名 as select * from 基表名 order by height desc;
select * from 视图 group by c_id;
结果和select * from 基表名 group by c_id order by height;     一样的结果

视图算法是系统对视图以及外部查询视图的select语句的一种解析
视图算法分为三种：默认（undefined）不是一种实际算法，只是告诉系统视图没有定义算法
      临时表算法(temptable)：系统先执行视图的select语句，后执行外部查询语句
                  合并算法(Merge)：系统先将视图的对应的select语句与外部查询试图的select语句进行合并，然后执行
                  （效率高:系统经常选择,一般默认选择）

算法指定：create algorithm=指定算法 view 视图名字 as select语句;
视图算法选择:如果视图的select语句中包含一个查询语句（五子句：where，having，limit，group by，order by，）而且很有可能顺序
比外部的查询语句要靠后（上面的select语句中，order by在group by之后，则使用temptable），一定使用算法temptable其他情况可以不用指定（默认即可）

数据备份与还原
备份：将当前已有的数据或者记录保留
还原：将已经保留的数据还原到表中

数据还原和备份是为了防止数据被盗，丢失，误操作，还有就是保护数据记录
数据备份和还原的方式有很多种：数据表备份，单表数据备份，SQL备份，增量备份

数据表备份
不需要通过sql来备份，直接进去数据库文件夹中把表结构文件（frm）和数据文件复制过来即可
但是根据不同的数据库引擎有不同的区别
存储引擎：数据库存储数据的方式主要分为两种：innodb和myisam
对比innodb和myisam的数据存储方式
innodb：只有表结构，数据文件全部存储到数据库文件中
myisam：表（frm），数据（MYD），索引（MYI）文件全部单独分开存储
（直接可以把这些文件复制到另一个数据库文件中，可以直接使用，而innodb就不行）

单表数据备份
每次只能备份一张表，只能备份数据（表结构不能备份）
通常的做法是：将表中的数据导出到文件
从表中选出一部分数据保存到外部的文件中（outfile）  
select *或者字段列表 into outfile ‘文件路径’ from 数据源;          前提是外部文件不存在，不然不能创建成功
而且保存的文件不能用txt格式打开（会破环结构）

高级备份
select *或者字段列表 into outfile ‘文件路径’ fields 字段处理 lines 行处理 from 数据源;
fields（字段处理）
            Enclosed by：字段使用什么内容包裹，默认是''（空字符串）
            Terminated by：字段以什么结束，默认是"\t"(tab键)
            Escaped by：特殊符号用什么方式处理，默认是'\\',使用反斜杠转义
lines（行处理）
            starting by:每行以什么开始，默认是'',空字符串
   Terminated by:每行以什么结束，默认是"\r\n",换行符

数据还原：将一个保存在本地的数据文件还原到表中（如果表结构不存在，那么无法实现）
load data infile ‘文件路径’ into table 表名[(字段列表)] fields 字段处理 lines 字段处理;  (怎么备份的就怎么处理)

sql备份
备份的sql语句，系统会对表结构以及数据进行处理，变成对应的sql语句，然后进行备份，还原的时候只要执行sql指令即可（主要针对表结构）
备份：
mysql没有提供备份指令，需要利用mysql提供的软件mysqldump.exe来进行备份，Mysqldump.exe也是客户端，需要操作服务器，必须连接认证
Mysqldump.exe/Mysqldump.exe -h(host)p()u(user)p(passward)     这里没有分号    [数据库名字1[数据库2]...]>外部文件目录(建议用.sql)
Mysqldump.exe -uroot -proot 数据库名>路径;             整库备份
 
sql还原数据 两种还原方式
方案一：Mysql.exe客户端还原
Mysql.exe/mysql -hpup 数据库名字<备份文件目录
方案二：使用sql指令还原
source 备份文件所在路径;

SQL备份的优缺点
优点：可以备份结构
缺点：会浪费空间（增加额外的SQL指令）

增量备份
不是针对数据或者SQL语句进行备份，是针对Mysql服务器的日志进行备份

增量备份：指定时间段开始备份，备份数据不会重复，而且所有的操作都会备份（一般大项目都会用增量备份，成本小，周期短）

事务安全
事务：transaction  一系列要发生的连续操作
事务安全：一种保护连续操作同时实现的机制
事务安全的意义：保证数据操作的完整性

事务操作
分为自动事务（默认的）和手动事务
1.开启事务，告诉系统以下的所有操作(写操作)都不要直接写入数据表，而是存放在事务日志中
start transaction;

2.进行事务操作（某个账户减少和某个账户增加）  

注意：事务操作是针对数据的操作，对结构没有影响（回滚并不能使删除的表还原）

例如  update My_account set money=money-1000 where id =1   用赋值的原因是在业务的角度来说这个操作是有1000的支出而不是清0
（如果通过当前账户查询，会从数据表中查询结果，经过临时日志文件加工后返回，可以通过另外的账户来查看数据库，然后查看表的变化）
3.对方账户增加
4.关闭事务：选择性的将日志文件中的操作结果保存到数据表中（同步保存）或者直接清空全部的事务日志
a.提交事务：同步数据表（操作成功）
commit;
b.回滚事务：直接清空日志表（操作失败）
rollback;

事务操作原理：事务开启之后，所有的操作都会临时保存到事务日志，事务日志只有在得到commit命令后才会同步到数据表，其他的情况都会
清空（rollback，断开，断电）

回滚点
回滚点：在某个成功的操作完成后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面的操作都已经成功，可以在当前成功的
位置设置一个回滚点，可以供后续失败操作返回到该位置，而不是返回所有的操作

设置回滚点名：savepoint 回滚点名;
回到回滚点：rollbck to 回滚点名;

自动事务处理
在Mysql中，默认的都是自动事务处理，用户处理完会立即完全处理到数据表中
自动事务：系统通过autocommit变量控制
show variables like 'autocommit';            查看autocommit变量

set autocommit=0/off;                        关闭自动事务处理
关闭自动事务处理之后，需要手动选择（commit，rollback）来处理
通常默认都会使用自动事务处理

事务特性
事务有四大特性：ACID
A:atomic（原子性）：事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败
C:Consitency（一致性）：事务操作的前后，数据表的数据不会有什么变化
I:Isolation（隔离性）：事务操作是相互操作不受影响的（统一数据库中开启两个事务，一个没有处理
（用commit或者rollback），另一个就不能更新数据，会等待解锁（释放：commit或者rollback））
D:Durability（持久性）：数据一旦提交，就会永久的改变表的数据

锁机制：innodb默认是行锁，如果在操作过程中，没有用到索引，系统就会自动使用全表索引，升级为表锁
行锁：只有当前行被锁住，别的用户不能操作
表锁：整张表被锁住，别的用户不能使用
页锁：整个一页都被锁住，包括好多条sql命令，别的拥护不能使用

变量分为系统变量和自定义变量
自定义变量：大部分的时候用户不需要使用系统变量，系统变量是用来控制系统服务器表现的，autocommit和
auto_increment_increment等

查看系统变量：show variables;

查看系统变量值：select @@version,@@autocommit,@@auto_increment_offset;
查看具体变量值：任何一个有数据返回的结果都是由select查看
select @@变量名;

修改系统变量
修改系统变量分为两个级别：局部级别和全局级别
回话级别：临时修改，当前客户端当次连接有效
set 变量名=值;   set @@变量名=值;

全局变量：一次修改，永久生效，对所有客户端都生效
set global 变量名=值;

自定义变量
系统为了区分系统变量，规定用户自定义变量必须使用一个@符号
set @变量名=值;
查看自定义变量
select @变量名;

在Mysq中,"="很多时候默认的当作赋值符号处理，Mysql为了区分比较和赋值的概念，重新定义了一个新的
赋值符号 :=

Mysql中允许从表中获取数据然后赋值给变量，两种方式
方案一是变赋值边查看结果
set 变量名:=值 from 数据源;            这里就只能用  :=   用 = 会理解为比较

方案二
只有赋值不看结果，要求很严格，数据记录最多只允许获取一条，Mysql不支持数组
select 字段列表 from 表名 into 变量列表;

所有自定义的变量都是会话级别，当前客户端当次连接有效
所有自定义变量不区分数据库，可以跨数据库处理（用户级别）

触发器（trigger）：事先为某张表绑定一段代码，当表中的某些内容发生该改变的时候（增删改），系统会
自动触发代码，执行

触发器：事件类型，触发时间，触发对象
时间类型：增删改，三种类型insert，delete和update
触发时间：before和after
触发对象：表中的每一条记录（行）

一张表中最多只能拥有一种触发时间的一种类型的触发器，也就是说一张表中最多只能有6个触发器

创建触发器
在Mysql高级结构中，没有大括号，都是用对应的字符符号代替
触发器基本语法
  
Delimiter  自定义符号                临时修改语句结束符，后续代码中只有碰到自定义符号才算结束

Create trigger 触发器名字 出发时间 事件类型 on 表名 for each row
begin  (相当于左大括号：开始)
       里面就是触发器的内容，每行内容必须以语句结束符 ；结束
end    (相当于右大括号：结束)

将临时结束符号修改过来
Delimiter ;                 把结束符号修改为 ;

查看触发器
查看所有触发器或者模糊匹配
show trigger [like 'pattern'];

查看触发器创建语句
show create trigger 触发器名字;

所有触发器都会保存在一张表中 Information_schema.triggers

触发器删除
触发器不能更新，只能先删除，再增加
Drop trigger 触发器名字;

触发器记录:不管触发器是否触发了，只要当某种操作准备执行，系统会将当前操作的记录的当前状态和即将
执行之后的新的状态分别保留下来，供触发器使用，其中，要操作的当前状态保存在old中，要操作之后的可
能状态保存在new中
old代表的是旧记录，new代表的是新记录
删除的时候是没有new的，新增的时候是没有old的
使用方式：old.字段名/new.字段名（表示的是即将会发生的结果）

触发器
delimiter $$

create trigger after order after insert on my order for each row
begin
        触发器内容开始：新增一条订单，old没有，new代表新的记录
update 触发器名 set 库存字段=库存字段-new.新增订单数目字段 where id=new.订单里面的类别
end
$$
delimiter ;

分支结构
分支结构：实现准备多个代码块，按照条件执行某段代码
在Mysql中只有if分支
基本语法
delimiter %%
create trigger after order after insert on my order for each row
begin
        select 库存字段 from 表名 where id=new.订单里面的类别 @库存;
        if @库存<new.订单里卖的数目 then
                  库存不够:触发器没有提供一个能够组织事件发生的能力
        insert into xxxx values（xxxx）;
        end if;
end
$$
delimiter ;

循环结构
循环结构：某段代码在在指定条件下重复执行
while循环（不存在for循环）
while 判断条件 do
 满足条件要执行的代码
 变更循环条件
end while;

循环控制：在循环内部进行循环判断和循环控制
mysql中没有continue和break，但是有替代品
Iterate：迭代，类似continue，后面的代码不执行，循环重新来过
leave：离开，类似break，整个循环接受

使用方式：Iterate/leave 循环名;

定义循环名字
循环名字：while 条件 do
循环体
循环控制
Iterate/Leave 循环名;
end while;

函数
函数：讲一段代码块封装到一个结构中，在需要执行代码的时候，调用结构即可（实现结构的复用）
函数分为两类：系统函数和自定义函数

系统函数
系统定义好的函数，直接调用就行了
任何函数都有返回值，因此函数的调用都是select
Mysql中，字符串的基本操作单位（最常见的是字符）

substring（字符串截取）是以字符为单位的   mysql中下标是以1开始的，因为0是代表false，而且截取中文
没有问题，因为截取单位是字符

char_length：字符长度
length：字节长度        （gbk下，中文占两个字节）

Instr：判断字符串是否在某个具体位置出现，若出现，则返回具体位置，若没有，则为0

Lpad：左填充，将字符串按照某个指定的填充方式填充到指定长度（字符为单位）Rpad同样

Insert：替换，找到目标位置，指定长度的字符串，替换成自动字符串

strcmp：字符串比较，比它小就显示-1，比他大就显示1，等于i就显示0

自定义函数
函数要素：函数名，参数列表（形参列表，实参列表），返回值，函数体（作用域）

创建函数
create function 函数名（[参数列表]）returns 返回值类型
begin
        函数体
  返回值：return  数据（数据类型一定是上面指定的数据类型）
end  （只有一条语句的时候可以省略）

自定义函数和系统函数的调用是一致的

查看函数
show function status [like 'pattern'];
查看函数的创建语句
show create function 函数名;

删除函数
Drop function 函数名;

函数
Delimiter $$
create function 函数名(参数名 参数类型)returns 返回值类型
begin
       set @en=1;
       set @cn=0;
       while @en<参数 do
              set @cn=@en+@cn;          Mysql中没有复合运算（+=等）
              set @en=@+1;
  end while;
        return @en;
end
$$
delimiter ;

在函数内部用@定义的变量在函数外部也可以访问

delimiter $$
create function 函数名(参数名 参数类型)returns int
begin
       declare i int default 1;          (声明循环变量，结果变量)
       declare res int default 0;
       Mywhile:while i<参数 do
       if i%5=0
           set i=i+1;
           Iterate Mywhile;
  end if;
        set res=res+i;
        set i=i+1;
        end while;
        return res;
end
$$
delimiter ;

作用域
Mysql中的全局变量能在全局使用，局部变量能在函数内部使用
全局变量：使用set关键字定义，使用@符号标志
局部变量：使用declare声明，没有@符号，所有的局部变量都必须声明在函数体开始之前
全局变量可以当作局部变量使用，而局部变量不能当作全局变量使用

存储过程
存储过程简称过程（procedure）是一种用来处理数据的方式
存储过程是一种没有返回值的函数

创建过程
create procedure 过程名字（[参数列表]）
begin
       过程体
end

查看过程
show procedure status[like 'pattern'];
查看创建过程语句
show create procedure 过程名;
调用过程
call 过程名;

过程只能先新增后添加
Drop procedure 过程名;

存储过程
函数的参数需要数据类型指定，过程比函数更严格
过程还有自己的类型限定，三种类型
in：数据只是外部传给内部使用（值传递），也可以是数值也可以是变量
out：只允许过程内部使用（不用外部数据），给外部使用的，（引用传递：外部的数据会被清空才进入内部）
之能是变量
inout：外部可以在内部使用，内部修改可以在外部使用，典型的引用传递，只能传变量

out和inout都是引用传递：内部修改会影响外部

存储过程对于变量的操作（返回）是滞后的：是在存储过程调用结束的时候，才会重新将内部修改的值
赋值给外部传入的全局变量

在存储过程结束以后，系统变量会将局部变量重新返回给全局变量（out和inout）
这就是存储过程的意义：存储过程并不返回值，但是可以通过把内部的结果返回给外部使用