JavaWeb之MySQL

安装MySQL

略，注意一定要截图密码设置页面，不然后期修改数据库的登陆密码会比较麻烦。

MySQL环境搭建(MacOS系统)

• 安装完成后，在系统偏好设置中启动mysql
• 配置系统的环境变量，修改用户目录中.bash_profile文件，我这里用nano .bash_profile，在文件的末尾追加变量名

PATH=$PATH:数据库安装路径

• 我这里是PATH=$PATH:/usr/local/mysql/bin，保存并返回终端，使用：source .bash_profile使配置生效，关闭终端重新打开也可以。
• 配置完成后，在终端输入：mysql -uroot -p密码，回车，显示mysql> 则表示已经连接到数据库了，就可以使用数据库的命令操作数据库了
• 进入数据库后，一般会修改密码，输入命令：

SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘new password’);

检查MySQL的环境

• 在系统偏好中启动MySQL，记得要先启动服务，再操作。
• 在终端输入mysql -uroot -p密码(我的设置：mysql -uroot -p123456)命令登录到数据库，显示出mysql>则表示环境配置没有问题。

简要认识数据库

• 数据库的分类：关系型和非关系型，占市场主流的是关系型数据库。关系型数据库底层是以二维表及其之间的关系所组成的数据库。
• 数据库的结构：(类比Excel表记忆)，数据库—>数据表—>表头(字段信息的集合)和表记录

数据库常用命令：

数据库的操作重点是查询和设置编码。

show命令

• 辅助记忆格式：show + 目标对象;
• 查看所有数据库：show databases;
• 查看当前的数据库：show database();
• 查看所有表：show tables;
• 查看数据库编码格式：show variables like ‘character_set_database’;
• 查看指定的数据库格式：show create database 库名;
• 查看数据表编码格式：show create table 表名;

use命令

• 辅助记忆格式：use + 目标对象;
• use 库名;

drop命令

• 辅助记忆格式：drop + 目标对象;
• 删除数据库：drop database if exists 库名;
• 删除数据表：drop table if exists 表名;

delete命令

• 删除表记录：delete from 表名 [where 条件];

truncate命令

• 重置并清空数据表：truncate table 表名;

alter命令

• 辅助记忆格式：alter + 目标对象 目标名 具体内容;
• 修改数据库编码格式：alter database 库名 character set utf8;
• 修改数据表编码格式：alter table 表名 character set utf8;
• 修改字段编码格式：alter table 表名 change 原字段名 新字段名 varchar(10) character set utf8;
• 添加字段：alter table 表名 add 字段名 字段类型 [default 默认值] [约束];
• 修改字段：alter table 表名 modify 字段名 字段类型 [default 默认值] [约束];
• 删除字段：alter table 表名 drop 字段名;

rename命令

• 修改数据库名称：rename database 原库名 to 新库名;#旧版本数据库可用，新版不可用，所以可以通过后面的备份/恢复数据库来改名

create命令

• 辅助记忆格式：create + 目标对象 目标名 具体内容;
• 创建表信息：create table 表名(字段名1 字段类型 [default 默认值] [约束], …, 字段名n 字段类型 [default 默认值] [约束]);约束请参考后面的约束
• 创建数据库时指定数据库的字符集：create database 库名 character set utf8;
• 创建数据表时指定数据表的编码格式：create table 表名(字段名1 字段类型 [default 默认值] [约束], …, 字段名n 字段类型 [default 默认值] [约束]) default charset = utf8;

insert命令

• 插入表数据：insert into 表名 [(字段名1, …, 字段名n)] values(值 1, …, 值n);#注意省略字段时，值必须按对应的顺序赋值

select 命令

• 辅助记忆格式：select + 查询项 + from + 表名 + where/group by/order by子句(也就是select子句 + from 子句 + 功能子句)
• 通常分组子句group by放在筛选子句where后，放在having子句前，排序子句order by放在最后
• 查找表内容：select 查询项 from 表名;#查询项可以是字段的组合或字段的组合表达式，可以用as(别名)、distinct(去除重复)；

update命令

• 修改表数据：update 表名 set 字段1=值1, …, 字段n=值n [where 条件];

查询子语句

where子句

• 跟在from子句后面，格式：[where 条件]
• 筛选条件可以是常用辅助查询的关键字或通配符(参考后面)，注意where子句不能用列别名，可以表别名，原因参考后面的面试重点

group by子句

• 通过字段分组：select 查询项 from 表名 group by 字段名;

having子句

• having与where的功能类似，都是筛选，区别是where用在分组前，且不能使用列别名，having用在分组后

order by子句

• 升序排列：select 查询项 from 表名 order by 字段名 [asc];
• 降序排列：select 查询项 from 表名 order by 字段名 dasc;

子查询

所谓的子查询，其实就是将一个查询得出的结果，作为另外一个查询的条件。例如
select…
from…
where…(select…from…)

面试重点（五星级重要）

sql书写的顺序：

select from 子句  + 
where子句 + 
group by子句 +
having子句 + 
order by子句 + 
limit分页查询

sql的执行顺序：

1.from			—确定要查询哪张表(定义表别名)
2.where子句		—从整张表的数据中筛选，从sql的执行顺序可以看出where不能有列别名
3.select		—确定要显示哪些列(定义列别名)
4.group by子句	—根据指定的列进行分组
5.having子句		—对分组之后的数据进行筛选过滤
6.order by子句	—根据指定的列进行排序

函数

• database()：当前数据库
• count()：统计数量，count(*)统计查询结果的记录行数，不统计null值
• max()：求最大
• sum()：统计总和
• avg()：统计平均值
• year()：提取年份
• month()：提取月份
• day()：提取日期
• curdate()：获取当前日期
• curtime()：获取当前时间
• sysdate()：获取当前日期时间

\c终止符

• 终止MySQL语句的执行

约束

• 主键约束(primary key)：唯一且不能为NULL，可用auto_increment
• 非空约束(not null)：不能是NULL，可以重复
• 唯一约束(unique)：不能重复，可以是NULL
• 外键约束：用来通知数据库两张表列与列之间的对应关系，并让数据库帮我们维护这样的关系的键就叫外键，如果不添加外键，数据库不知道两张表之间的对应关系，数据库也帮我们维护这段关系，这就需要我们开发者自己维护。添加外键此时数据库会帮我们维护这段关系。
• 创建数据表时，在MySQL的语句末尾通过添加foreign子句添加外键，如：create table 子表(id int primary key auto_increment, name varchar(10), dept_id int default null ,foreign key(外键字段名) references 外表(外表主键名));
• 为已经创建的数据表添加外键：alter table 子表名 add foreign key(外键字段名) references 外表名(外表主键名)

常用辅助查询的关键字或通配符

• *：匹配所有
• is：判断是否是指定的内容，e.g.： is null
• in：查询某范围内的数据，e.g.：in(1, …, 7)
• ifnull(字段名, 替换值)：替换空值null
• and：与
• or：或
• between x and y：在x和y之间
• not：非，e.g.：not null
• like：模糊查询，通常配合通配符%使用，e.g.：like ‘pattern’
• limit：分页，e.g.：limit (页码-1)*每页记录数,每页显示的记录数
• distinct：对结果去重，e.g.：distinct 表达式，表达式通常是多个字段名
• as：别名，e.g.：表达式1 as 别名

备份\恢复数据库

• 备份不能备份数据库本身，只能备份表，所以能为数据库改名
• 备份：在终端中(切记，不要登陆数据库)输入命令：mysqldump -uroot -p 库名 > 备份所在路径
• 恢复：先登陆数据库创建数据库后，再返回终端，在终端中(切记，不要登陆数据库)输入命令：mysql -uroot -p 库名 < 备份所在路径，可以实现改名的功能
• 注意，密码输入是数据库的密码，恢复不需要sql文件权限是可执行

多表查询

• 如果是查询两张或以上的表，只想查询两张表中对应的数据，需要使用关联查询，否则会出现笛卡尔积的现象，如：select + 查询项 + from + 表名 1, …, 表名n where 关联条件;
• 如果是查询两张或以上的表，要获取其中一张表的所有记录，需要使用外连接查询，左外连接子句：left join 表名1 on 关联条件; 右外连接子句：right join 表名1 on 关联条件;