java day19第十九课数据库Mysql
Mysql
(1)mac软件包brew下载工具homebrew
(2)使用brew: 命令行输入：brew
(3)mac下如何查看mysql目录？find /usr/local/ -iname "mysql"
(4)mysql数据库安装 brew install mysql
(5)mysql数据库卸载 brew uninstall mysql
(6)设置和重设密码
(7)数据库可视化工具：Navicat,PhpMyAdmin,workbench,squel pro
1、查看当前服务器下面，有哪些库（databases）
show databases；（分号结束）
2、创建一个数据库
create database name[字符集声明，整理声明];
3、删除数据库
drop database name;
4、选择数据库
use databasename
5、查看表中列属性
desc tablename
6、查看数据下面所有表
show tables;
7、删除一张表
drop table tableName;
8、如何修改表名
rename table oldname to newname;
9、主键
primary key
10、自动递增
not null auto_increment
列类型学习
mysql三大列类型
数值型：
整型
Tinyint（微小的类型：1字节 0～2^8 - 1）Tinyint(M) unsigned(无符号类型（非负）) zerofill（0填充，默认无符号）
smallint（2字节0～2^16 - 1）
mediumint(0～2^24 - 1)
int(0～2^32 - 1)
bigint
小数型
Float(M，D) 浮点型
decimal(M，D) 定点型（更精确）
M：精度（总位数，不包含点）
D：标度（小数位）
字符串型：
char(M) 定长字符串 0<=M<=255
Varchar(M) 变长字符串 0<=M<=65535（约2w－6w字符（受字符集影响））
Text 文本类型 约2w－6w字符（受字符集影响）
区别：
char M：M个字符，如果存的小于M个字符，实占M个字符
Varchar M：M个字符，存的小于M个字符，实占为N，N<=M,实占N个字符
怎么选呢？
1、空间利用效率
2、速度
日期时间类型
Date 年－月－日 日期
如：2017-6-13 范围：‘1000-01-01——9999-12-31’
Time 09:00:00时间
格式：hh:mm:ss 范围：‘－838：59：59——＋838：59：59’
Datetime 年－月－日 hh：mm：ss 时间（时间类型）
如：2017-6-13 09:00:00 范围：‘1000-01-01 00:00：00——9999-12-31 23:59:59’
时间戳：用int来表示
是1970－01-01 00:00：00到当前的秒数
一般存注册时间，登陆时间等，并不是用datetime存储而是用时间戳进行存储
因为大特time虽然直观，但是不便于计算。
Year 年 年类型 1个字节 表示 1901-2155［0000表示错误时的选择］；
如果输入两位，‘00-69’表示2000-2069 ‘70-99’表示 1970-1999
整型系列所占字节与存储的关系，
定性：占字节越多，存储范围越大
增、删、改、查之案例过程分析
增：
用户注册表单收集，提交数据，注册页面收集到表单的数据后，形成insert语句，user表插入该条数据，用户注册完成。
注意：往哪张表天加？给哪几列添加值？分别是什么值。
insert into 表名 (列1,列2...列N) values (值1,值2...值N);
改：
前台用户中心，用户新昵称，根据新昵称和用户id，形成update语句，完成昵称到修改
注意：改哪张表？你需要给改哪几列的值？分别改为什么值？在哪些行生效。
update 表名 set 列1 = 值1 列2 = 值2 ... 列N ＝ 值N where 表达式;
查：
管理员后台点击会员列表，此时，网页形成select语句，查询出所有会员的数据，完成会员的查看。
select * from 表名;
删：
管理员后台选中某用户，并且删除，捕捉此用户的id，根据用户id形成相应的delete语句。执行delete语句，完成用户的删除。
delete from 表名 where 表达式;
建表练习：
有如下字段：姓名，年龄，性别，email，手机号，简介，薪资，生日。
查询的五种子句：
where (条件查询)
select * from 表名 where 表达式;
表达式：
1、比较运算符
< 小于 ; !=或<> 不等于 ;
<= 小于等于; >= 大于等于;
= 等于; > 大于;
in 在某集合内; between 在某范围内;
2、逻辑运算符
not 或 | 逻辑非
or 或 || 逻辑或
and 或 && 逻辑与
group by(分组)
作用：把行 按字段分组 一般和统计函数配合使用
语法：group by col1,col2,...colN
运用：常见于统计场合，如按栏目计算帖子数，统计每个人的平均成绩等。
统计函数：
max()： 求最大
min()： 求最小
sum()： 求总和
avg()： 求平均
count()： 求总行数
having (筛选)
having和where类似，可筛选数据
where后等表达式怎么写，having就怎么写
数据在表中，表在硬盘或内存以文件的形式存在，where就是针对表文件发挥作用
查询出来的结果也可以看作一张表，其文件一般是临时存在于缓冲区，having就是针对查询结果发挥的作用
order by (排序)
按一个或多个字段对查询结果进行排序
按价格的高低进行排序，按年龄的大小进行排序
默认为升序排列
asc 升序
desc 倒序，降序
有可能一个字段不能实现排序，可以按多字段排序
order by col1 asc/desc,col2 asc/desc ... colN asc/desc
,
limit (限制结果条数)
在语句的最后，起到限制数据数目的作用
Limit [offset],[N]
offset ： 偏移量
N ： 取出数据的数目
offset如果不谢，则相当于limit 0，N 也可以简写 limit N
练习：设有成绩表stu如下：
姓名 科目 分数
张三 语文 50
张三 数学 90
张三 英语 80
李四 语文 54
李四 地理 45
王五 地理 33
查询两门及两门以上不及格同学的平均分
子查询
where 型子查询，指把内层查询的结果作为外层查询的比较条件
例：查询最新商品（以id为最大为最新）
select * from goods where goods_id = (select max(goods_id) from goods)
From 型子查询 把内层的查询结果当成临时表，供外层sql再次查询
注意：把临时查询出来的结果看成临时表，加as临时表名
Exists 子查询 把外层的查询结果，拿到内层，看内层的查询是否成立
比如查询还在就读的学生：select * from stu where exists( select * from classes where stu_id = classes.id);
连接
union(联合)
把2次或多次查询结果合并起来
要求：两次查询的列数一致
建议：查询的每一列相对应的列类型也一样
可以来自多张表
多次sql语句取出来的列名可能不一致，此时以第一次取第一个sql的列名为准
如果不通的语句中取出的行（每个列的值相同）那么相同的行会合并（合并：去重）
在子句中 order by 配合limit使用才有意义，如果order by 不配合limit使用，会被语法分析起优化分析时去除。
内连接
inner join (内联结：返回满足条件得所有行)
内连接，也叫等值连接，inner join 产生同时符合A和B的一组数据。
nature join (自然联结：也是返回满足条件得所有行)
外连接
Left join (左联结：等价于 left outer join)
返回左表中所有的行，如果左表中的行没有匹配右表中的行，就会返回 NULL。
Right join (右联结)
与左联结相反。
左连和右连的区别
以左表为准，去右表找匹配数据，找不到匹配，用null补齐。
左右连接可以相互转化
可以把右连接转换为左连接（推荐使用左连接代替右连接，兼容性好一些）
内连接：查询左右都有的数据 即：不要左/右连接中null的数据，也可以理解为左右连接的交集。
Full join （全联结：返回左表右表中所有的行，如果在另一个表中无匹配，则该表的行显示 NULL）现在mysql不支持full join 我们可以通过left join 和right join 中间用union 联合查询替代此方法
Cross join (交叉联结：两个表中所有可能的组合)
cross join：交叉连接，得到的结果是两个表的乘积，即笛卡尔积
笛卡尔（Descartes）乘积又叫直积。
假设集合A={a,b}，集合B={0,1,2}，
则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。
可以扩展到多个集合的情况。
类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。
union join （联合联结：返回只有一个表有值，另一个表为空的数据组合。）
实际用的很少，很多 DBMS 不支持该查询。
增加列，修改列，删除列
alter table 表名 add 列声明
增加的列默认是在表带最后一列
可以用after来声明新增的列在哪一些后面
alter table 表名 add 列声明 after 列名
如果新增放在最前面
alter table 表名 add 列声明 first
修改列
alter table 表名 change 旧列名（别改变的列名） 列声明
删除列
alter table 表名 drop 列名
视图(view)
如果某个查询结果出现的非常频繁，也就是用这个查询结果作为子查询出现的比较频繁，
create table newstu like stu;
insert into newstu select * from stu;
上面两句，事项保存一个查询结果到表里面，供其他查询用。
视图的定义：
视图是由查询结果形成的一张虚拟表
视图的创建语法
create view 视图名 as select 语句
视图的删除语法
drop view 视图名；
视图的修改
不能修改视图，要更改视图定义，必须删除视图，然后重新创建它。DB2提供的alter view 语句 只用于修改引用类型。
视图与表的关系
视图是表带查询结果，自然表的数据改变了，影响视图的结果
视图改变了呢？
视图增删改也会影响表
但是视图不是总能增删改
视图的数据与表带数据一一对应时，可以修改，
对于视图insert视图中的包含所有表中没有默认值的列
视图的algorithm
algorithm ＝ merge/temptable/undefined
merge:当引用视图时，引用视图的语句与定义视图的语句合并
意味着视图知识一个规则，当查询视图时，把查询视图的语句（比如where那些）与创建时的语句where子句等合并，分析形成一条select语句
temptable：当引用视图时，根据视图的创建语句建立一个临时表
temptable时根据创建语句瞬间创建一张临时表，然后查询视图的语句从该临时表查询数据。
undefined：未定义，自动让系统帮你选。
为什么要视图
1、可以简化查询
2、可以进行权限控制
把表带权限封闭，开发相应的视图权限，视图里面，只开放部分数据。
字符集
设置(告诉)服务器接收的字符集
set character_set_client = gbk；
告诉服务器转换成什么编码
set character_set_connection=gbk;
告诉服务器给我（查询结果）什么编码
set character_set_results = gbk;
如果以上三者，都为同一个字符集，则可以简写为 set names gbk
思考什么时候会产生乱码？
1、client 声明与实际不符
2、results 与客户端字符集不符
什么时候会丢失数据？
通俗的理解 就是connection 和服务器的字符集比client 小的时候。
校对集（了解部分）
指的是字符集的排序规则，一种字符集可以有1个或者多个排序规则
以我们的utf8为例，默认的是utf8_general_ci；我们可以指定 用collate utf8_bin
我们可以通过排序规则上面来理解。因为我们的排序就是按照某种规则来进行比较排序的
show collation 查看搜所有的校对集
show character set 查看所有的字符集
阶段小结：
union的用法：
合并并查询的结果（取select结果的并集）
对于重复的行去掉，
如果不去除重复的行，可以用union all
union的要求
各select查出的列数一致，
如果子句中用了order by limit 子句要记得用（）包起来
如果只有order by 没有limit order by 被优化掉，不起作用。
左连接｀右连接｀内连接
select ta left/right/inner join tb on ta.列 = tb.列 where
左连接与右连接可以相互转换 a left join b ＝ b right join a；
内连接：inner join
左右连接的交集，通俗的讲是两张表都可以匹配上的行
视图 view
视图是一张虚拟表
没有真实的数据存在，只是与表的一种查询产生的关系
语法：create [algorithm=merge/temptable/undefined] view viewname as select ....
merge:
视图： sql1，where id > 100;
查视图 sql2,where id <200;
形成sql where id>100 and id<200 合并为一条查询语句，查询的都是基表
temptable:
视图： sql1，where id > 100;
查视图 sql2,where id <200;
sql2 查询时 针对的是一张临时表（也就是sql1查询出来的结果），临时表是针对于基表查询出来的。
字符集和校对集
客户端－－－>转换器－－－>服务器
客户端<－－－
客户端使用的字符集：set character_set_client = gbk/utf8
转换器转换后的字符:set character_set_connection = gbk/utf8
返回给客户端的字符集：set character_set_results = gbk/utf8
如果以上三者都是字符集一样的N那么刻意简写为set names N
校对集
校对集就是排序的规则
a B c D 升序排列 a B c D
以二进制升序来拍：B D a c
一种字符集对应一种或多种校对集
触发器（trigger）
作用：监视某种情况并触发某种操作
用触发器可以解决：在某表发生变化时，触发某个操作
触发器：
能监视：增、删、改
能出发操作：增、删、改
触发地点：table
触发时间：after/before
创建触发器语法
create trigger triggername after/before insert/update/delete on 表名
for each row （这句话在mysql里面是固定的）
begin
sql语句（一句或多句，insert/update/delete 范围内；delimiter (改变结束标记)）
end；
删除触发器的语法
drop trigger 触发器名
如何在触发器引用行的值
对于insert 而言，新增的行 用new来表示
行中的每一列的值用new.列名来表示
对于delete来说，原本有一行，后来被删除，想引用被删除的这一行，old来表示，old.列名就可以引用被删除行中的值
对于update来说
被修改的行
修改前的数据，用old表示，old.列名引用被修改之前行中的值
修改后的数据，用new来表示，new.列名引用被修改之后行中的值
alter和before的区别
after是先完成数据的增删改再触发触发器
触发的语句先于监听的增删改，无法影响前面的增删改动作。
before 是先完成触发，再增删改。
触发的语句先于监听的增删改，我们有机会判断，修改即将发生的操作。
如何查看触发器
show triggers
存储引擎
数据库对同样的数据，有着不同的存储方式和管理方式，在mysql中，称为存储引擎。
表里的数据存储在硬盘上，具体怎么存储？
存储的方式有很多种。
对于用户来说：同一张表，无论用什么引擎存储，用户看到的数据是一样的。
对于服务器来说：
指定的引擎：create table t(id int) engine myisam charset utf8;
myisam： 批量插入速度快，不支持事物 ，锁表
innodb：批量插入相对较慢，支持事物，锁行
全文索引：目前5.5版本myisam和innodb都支持
事物
通俗的讲事物，指一组操作，要么都成功操作，要么都失败 －－－原子性（Atomlcity）
在所有的操作都没有执行完毕之前，其他的中间的改变过程都不能看到 －－－隔离性 (Consistency)
事物发生前和发生后，数据的总额依然匹配 －－－ 一致性(Isolation)
事物产生的影响不能撤销 －－－持久性(Durability)
上述也叫事物的ACID特性
如果出了错误，事物也不允许撤销，只能通过补偿事物
关于事物的引擎
选用innodb
开启事物
start transaction
commit 提交
rollback 回滚
注意：当一个事物commit或者rollback之后就结束了
再注意：有一些语句会造成事物的隐式提交，比如start transaction
备份与恢复
系统运行中,增量备份与整体备份
例：每周日整体备份一次，周一至周六备份当天
如果周五出现了问题，可以用周日的备份+周一+周二+周三+周四的备份来恢复
备份的工具
可以采用第三方的收费工具
我们所学就使用系统自带的免费备份功能 即mysqldump
mysqldump 可以导出
表
/usr/local/mysql-5.7.18-macos10.12-x86_64/bin/mysqldump -h localhost -uroot -p day01 表1 表2>/Users/chenbo/Desktop/day.sql
导出的是建表语句和insert语句
如何导出库中的所有表？
/usr/local/mysql-5.7.18-macos10.12-x86_64/bin/mysqldump -h localhost -uroot -p day01>/Users/chenbo/Desktop/day.sql
如何导出一个库？
/usr/local/mysql-5.7.18-macos10.12-x86_64/bin/mysqldump -h localhost -uroot -p －B 库1 库2 库3 > 地址／备份文件名称
如何导出所有库？
/usr/local/mysql-5.7.18-macos10.12-x86_64/bin/mysqldump -h localhost -uroot -p －A > 地址／备份文件名称
恢复
其实我们备份的就是sql语句，我们只要可以把我们的sql语句运行起来就可以了
1、登陆到mysql命令行
对于库级的备份文件
Mysql>source 目录／filename.sql（备份文件地址）
对于表级的备份文件
Mysql>use 库名
Mysql>source 目录／filename.sql（备份文件地址）
2、不登陆到mysql命令行
针对库级的备份文件
mysql -u root -p < 备份文件地址
针对表级的备份文件地址
mysql -u root -p 库名< 备份文件地址
在数据库表中，对字段建立索引可以大大提高查询速度 即针对数据所建立的目录 。
坏处：降低了增、删、改的速度
增大了表的文件大小（索引文件甚至可能比数据文件还大）
btree 二叉树索引 查寻次数为 log2 N
hash 哈希索引 查询次数为1；
索引的类型
普通索引 index 为了加快查询速度
唯一索引 unique index 行上的值不能重复
主键索引 primary key 主键不能重复
主键必唯一，唯一索引不一定是主键，一张表中只能有一个主键，但是可以有多个唯一全文索引
全文索引 fulltext index
在mysql默认中，对于中文意义不大
因为英文有空格，标点符号来拆成单词，进而对单词进行索引，而中文，没有空格来隔开单词，mysql无法识别每个中文词。
关于全文索引的用法
match(全文索引名) against ('单词')
查看表中的所有索引：
show index from 表名
关于全文索引的停止词
全文索引不针对非常频繁的词做索引
如：this,is,you,my等等
创建索引
alter table 表名 add index/unique/fulltext 索引名（列名）
/primary key
alter table 表名 add primary key （列名） 不要加索引名，因为主键只有一个
创建索引的原则
1、不要过度索引
2、在where 条件最频繁的列上加索引
3、尽量索引加在散列值，过于集中的值加索引意义不大。
删除索引
alter table 表名 drop index 索引名
存储过程（procedure）
概念类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。
在封装的语句体里面可以用if else，case ， while等控制结构。可以进行sql来编程
查看现有的的存储过程
show procedure status;
调用存储过程
call 存储过程的名字（）
声明变量
declare
体会存储过程
create procedure qq()
begin
sql...
end!
存储过程和函数的区别
一个是名称不同
二是存储过程没有返回值
删除存储过程
drop procedure 存储过程名字
