.gif)
MySQL 基础
MySQL
数据库简介
之前通过流操作文件的方式存储数据的弊端
执行效率低
进行增删改查操作，代码书写比较麻烦
一般只能保存文本数据
什么是DB
DB：Database 数据库，数据库指一个文件集合
什么是DBMS
DBMS：Database Management System 数据库管理系统，用于管理数据库文件的软件，常见的DBMS：MySQL Oracle DB2 SQLServer SQLite
数据库分类
关系型数据库： 经过数学理论验证可以保存现实生活中的各种关系 ，以表为单位保存数据 ;
非关系型数据库：用于解决某些特定场景比如：高并发访问时的数据缓存，举例：Redis 以key-value形式保存数据
常见关系型数据库介绍
MySQL： 市场占有率第一， Oracle公司产品，08年被Sun收购，09年Sun被Oracle收购， 开源， 由于担心MySQL闭源，MySQL创始人们离开Oracle创建了MariaDB（创始人女儿叫Maria）
Oracle： 市场排名第二，Oracle公司产品，闭源产品， 拉里埃里森 32岁
SQLServer： 市场排名第三，微软产品，闭源产品
DB2： IBM公司产品，闭源产品
Sqlite： 轻量级数据库 安装包几十k 应用在嵌入式设备或移动设备中
开源和闭源
开源：开发源代码，盈利方式：靠卖服务， 技术大拿为了刷存在感对开源产品会无偿进行版本维护和升级
闭源：不开放源代码，盈利方式：靠卖产品和卖服务，技术大拿会攻击闭源产品刷存在感，但是闭源公司会花钱养一帮大拿维护升级
SQL
Structured Query Language：结构化查询语言，SQL执行在客户端(windows的命令行，linux的终端，或三方的数据库客户端软件)或者通过Java代码使用JDBC执行
如何连接MySQL数据库
windows系统在开始菜单栏中->所有程序->MySQL或MariaDB->MySQL Client 然后输入密码
linux系统 在空白区域右键 终端，然后输入 mysql -uroot -p回车 exit; 退出;
SQL语句规范：
以；结尾
可以换行，关键字之间用空格分隔(可以有多个空格)
关键字不区分大小写
数据库相关SQL
开启数据库 mysql -uroot -p
查询所有 show databases；
创建数据库
格式：create database 数据库名;
指定字符集格式：create database 数据库名 character set utf8/gbk;
查看数据库详情，可以查看数据库的字符集
格式：show create database 数据库名;
删除数据库
格式：drop database 数据库名;
使用数据库
格式：use 数据库名;
表相关SQL
创建表
格式：create table 表名(字段1名 字段1类型，字段2名 字段2类型);
查看所有表
格式：show tables;
查看表详情
格式：show create table 表名;
数据库表引擎：
1.innodb：支持数据库的高级操作，比如：事务，外键等
2.myisam：不支持数据库的高级操作，只支持基础的增删改查操作
创建表指定引擎和字符集
格式：create table 表名(字段1名 字段1类型)engine=innodn/myisam ?default charset=utf8/gbk;
查看表字段
格式：desc 表名;
删除表
格式：drop table 表名;
对已创建的表进行修改
修改表名 -格式：rename table 原名 to 新名;
修改表引擎和字符集
格式：alter table 表名 engine=myisam/innodb charset=utf8/gbk;
添加表字段
添加到最后格式：alter table 表名 add 字段名 字段类型;
添加到最前面格式：alter table 表名 add 字段名 字段类型 first;
添加在某个字段后面：alter table 表名 add 字段名 字段类型 after XXX;
删除表字段
格式：alter table 表名 drop 字段名;
修改表字段类型和位置
格式：alter table 表名 modify 字段名 新类型 first / after XXX;
数据相关
1.插入数据
全表插入格式：insert into 表名 values (值1，值2)；//如果值为中文，且代码没有问题却执行报错，先执行下set names gbk;
指定字段插入格式：insert into 表名 (字段1名，字段2名) values (值1，值2);
批量插入格式：在以上两种格式后面写多组数据通过逗号分隔
2.查询数据
格式：select 字段信息 from 表名 where 条件;
修改数据
格式：update 表名 set 新字段信息 where 条件;
删除数据
格式：delete from 表名 where 条件;
主键
什么是主键：
表示每条数据唯一性的字段为主键；
什么是约束：
约束创建时给字段添加的限制条件；
什么是主键约束：
主键约束可以保证主键的值唯一且非空； 数据类型后面添加：primary key;
主键约束+自增
数据类型后面添加：primary key auto_increment; 自增数值只增不减，从历史最大值的基础上自增+1， 添加了自增之后，主键值能为0（0等效为null）;
注释
用于对表的字段进行描述
数据类型后面添加：comment 'xxx';
` 和 '
- ` :用于修饰表名和字段名
可加可不加 ``；
- ' :用于修饰字符串
必须加 ''；
数据冗余
主键： 表示数据唯一性的字段。
外键： 表示建立两张表关系的字段
由于表设计不够合理导致数据量增大时出现大量的重复数据，这种现象称为：数据冗余，通过拆分表的形式解决数据冗余问题。
事物
什么是事务：事务是数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL全部执行成功或全部执行失败;
开启事务：
begin;提交事务：
commit；回滚事务：
rollback/rollback to xx;
-保存回滚点：
savepoint xx；SQL分类
DDL (Date Definition Language 数据定义语言) -包括： create,drop,alter,truncate -truncate: 删除并创建新表，和使用delete清空表不同的是，自增数值会清0； -不支持事务
DML (Date Manipulation Language 数据操作语言) -包括： insert,update,dalete,select(DQL) -支持事务
DQL (Date Query Language 数据查询语言) -只包括： select
TCL (Transactions control Lanuage 事务控制语言) -包括： gegin,commit,rollback,savepoint xxx,rollback to xxx;
DCL (Data control Language)数据控制语言 -分配用户权限相关SQL(暂时用不上);
数据类型
整数 常用整数有 int(m) 和 bigint(m),m代表显示长度，需要结合zerofill关键字使用；
浮点数 double(m,d) m代表总长度，d代表小数长度。 decimal(m,d) m代表总长度，d代表小数长度，超高精度浮点数，需要涉及超高精度运算时使用；
字符串 char(m) 固定长度，m代表所占长度，效率高，最长255； varchar(m) 可变长度，m代表预定自定义长度，所占长度最终还是看字符长度，节省空间，最长65535，超过255建议使用text； text 可变长度，最大65535，保存大于255长度的文本
日期 date：只保存年月日 time：只保存时分秒 datetime：保存年月日时分秒，最大值9999-12-31，默认值为null； timestamop：时间戳，保存年月日时分秒，最大值2038-01-19，默认值为当前系统时间。
导入*.sql文件到数据库中
windows系统 把把*.sql文件保存到D盘根目录 在终端连接数据库后 中执行以下命令 source d:/tables.sql;
linux系统 把*.sql文件保存到桌面 在终端连接数据库后 执行以下命令 source /home/soft01/桌面/tables.sql;
执行 show tables;
is null 和is not null;
is null：值为null is not null：值不为null；
eg:
查询奖金为null的所有员工信息
select * from emp where comm is null;查询没有上级领导(mgr)的员工信息
select * from emp where mgr is null;查询员工表中 有奖金的员工姓名、工资sal、奖金comm
select ename,sal,comm from emp where comm is not null and comm>0;别名
select ename as '姓名',sal as '工资' from emp;
select ename '姓名',sal '工资' from emp;
select ename 姓名,sal 工资 from emp;
最后一种 如果报错 set names gbk;去重 distinct
distinct xx
and 和 or
add 和 java中的&&效果一样
or 和 java中的 || 效果一样
eg：
查询不是10号部门并且工资低于3000的员工信息
select * from emp where deptno!=10 and sal<3000;查询部门编号deptno为30或者上级领导mgr为7698的员工姓名、上级领导和部门编号
select ename,mgr,deptno from emp where deptno=30 or mgr=7698;in关键字
当查询字段为多个的时候使用in；
eg：
查询员工表中 工资为5000，1500，3000的员工信息
select * from emp where sal=5000 or sal=1500 or sal=3000;
select * from emp where sal in(5000,1500,3000);between x and y
包含x和y
查询员工工资在2000到4000之间的员工信息
select * from emp where sal>=2000 and sal<=4000;
select * from emp where sal between 2000 and 4000;模糊查询 like 像
_ :代表单个未知字符
%:代表0个和多个未知字符 eg：a开头：a% b结尾：%b 第二个字母是a：a% 第二字母为x倒数第三个字母为y： x%y 包含字母x：%x%；
分页查询 limit
limit (a,b) 跳过的条数 b请求的条数（每页条数）
和日期相关的函数
SQL语言的HelloWorld select 'helloworld';
获取当前日期+时间 now() select now();
获取当前的日期 current当前 select curdate();
获取当前的时间 select curtime();
从完整的年月日时分秒中获取年月日 和 获取 时分秒 select date(now()),time(now());
从完整的年月日时分秒中提取时间分量 extract extract(year/month/day/hour/minute/second from hiredate)
日期格式化 date_format(); date_format(时间,格式); %Y代表四位年 2018\ %y代表两位年 18 %m代表两位月 05 %c代表一位月 5 %d代表日 %H代表 24小时 %h代表 12小时 %i代表 分 %s代表 秒
把now()得到的时间格式转换成 年月日时分秒格式 select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒')
ifnull(x,y)
age=ifnull(x,y) 翻译：如果x值为null则age=y 否则age=x
字符串相关函数
拼接
concat(s1,s2) s1s2
select concat('aa','bb');
案例： 查询每个员工的姓名和工资 工资后面有单位元
select ename,concat(sal,'元') from emp;获取字符串长度
char_length(str)
查询：每个员工的姓名和姓名的长度
select ename,char_length(ename) from emp;获取字符串A在字符串B中出现的位置 从1开始。
instr(str,substr)
select instr("abcdefg",'c');插入字符串
insert(str,start,length,newstr)
select insert('abcdefg',3,2,'m');转换大小写
select upper('nba'),lower('NBa');去两端空白
select trim(' a b '); a b截取字符串
select substring('abcdefg',2); //bcdefg
select substring('abcdefg',2,3); //bcd 3代表长度重复
select repeat('ab',2); //abab替换
select replace('abcdefg','c','mm'); //abmmdefg反转
select reverse('abc'); //cba分组查询
group by 字段名 每个xxx就以xxx进行分组
having
聚合函数条件不能写在where后面，where后面只能写普通字段的条件
having后面写聚合函数的条件，虽然也支持普通字段的条件，但是不推荐使用
having要和分组查询结合使用
select ..... from 表名 where .... group by .... having.... order by.... limit... ; 子查询（嵌套查询）
写在SQL语句中的查询语句称为子查询 子查询可以嵌套无数层 子查询可以写在哪些位置？
写在where和having的后面当做查询条件的值。
可以写在创建表的时候
关联查询
同时查询多张表的查询方式称为关联查询 笛卡尔积
如果关联查询不写关联关系会得到两张表数据的乘积，这个乘积称为笛卡尔积
工作中切记不要出现，如果数据量大，会占用大量内存，甚至系统崩溃 等值连接和内连接
这两种都是关联查询的查询方式,得到的数据是两张表的交集数据。
等值连接： select * from A,B where A.x=B.x and A.age=18;
内连接： select * from A ?inner join B on A.x=B.x where A.age=18;外连接
查询一张表的全部数据和另外一张表的交集数据
格式： select * from A left/right join B on A.x=B.x where A.age=18;关联查询总结
三种查询方式：
等值连接和内连接查询结果一样，得到交集数据，推荐使用内连接
外连接分为左外和右外得到的是一张表的全部数据和另外一张表的交集数据表设计之关联关系
一对一
什么是一对一：有AB两张表，A表中的一条数据保存B表中的一条数据，同时B表一条数据也对应A表的一条数据，这两张表的关系称为一对一。
应用场景： 用户表和用户信息扩展表， 商品表和商品信息扩展表
如何建立关系：在主表中添加主键，从表中添加外键指向主表的主键
一对多
什么是一对多：有AB两张表：A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条。
应用场景： 员工表和部门表 商品表和商品分类表
如何建立关系： 在多的表中添加外键指向另外一张表的主键
多对多
什么是多对多：有AB两张表，A表中一条数据对应B表的多条数据，同时B 表的一条数据对应A表的多条数据，称为多对多。
应用场景: 学生表和老师表 用户和角色表
如何建立关系：创建一个关系表在关系表中有两个外键分别指向两个表的主键
视图
什么是视图：视图和表都是数据库中的对象，视图可以理解成一张虚拟的表，视图本质就是取代了一段SQL查询语句
作用： 重用SQL、 隐藏敏感信息 创建视图的格式： create view 视图名 as (子查询);//创建了一个虚拟的表 create table 表名 as (子查询); //创建了一张真实存在的表
分类： 简单视图（不包含去重、函数、分组、关联查询，可以进行增删改查）和复杂视图（和简单视图相反，只能查询）
插入数据的数据污染问题 通过with check option解决
修改和删除数据只能操作视图中存在的数据
创建视图起别名的话 后期操作只能使用别名
约束
什么是约束： 约束是给表字段添加的限制条件
非空约束 not null
字段值不能为null
create table t1(id int, age int not null);
-测试：
insert into t1 values(1,20);//成功
insert into t1 values(2,null);//失败唯一约束 unique
字段值不能重复
create table t2(id int,age int unique);
-测试：
insert into t2 values(1,20);//成功
insert into t2 values(2,20);//失败 数据重复默认约束 default
给字段设置默认值
create table t3(id int,age int default 20);
-测试：
insert into t3 (id) values (1); //默认值生效
insert into t3 values(2,18); //默认值不生效
insert into t3 values(3,null); //默认值不生效主键约束
唯一且非空
外键约束
外键约束：添加外键约束的字段，值可以为null，可以重复，但是不能是关联表中不存在的数据，被关联的数据不能被先删除，被关联的表不能先删除
格式介绍： constraint 约束名 foreign key(外键字段名) references 关联的表名(关联的字段名);
删除外键约束方式: alter table 表名 drop foreign key fk_dept;
删除主键约束: alter table 表名 drop primary key; 如果主键带自增，则主键不能删除
索引
什么是索引： 索引是数据库中用于提高查询效率的技术，类似于目录
为什么使用索引：如果不使用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘块，直到找到数据为止，使用索引后会将磁盘块以树桩结构进行保存，查询数据时会大大降低磁盘块的访问数量，从而提高查询效率。
那些情况需要建索引
1.主键自动创建唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.单键/组合索引的问题，组合索引性价比更高
5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6.查询中统计或分组字段
那些情况下不需要建索引
1.表记录太少 2.经常增删改的表或者字段 3.where条件里用不到的字段不需要创建索引 4.过滤性不好的不适合做索引（比如性别，查询后还是一大堆数据）
索引是越多越好吗？
不是，因为索引会占用存储空间，只针对常用的查询字段创建索引。
有索引就一定好吗？
如果数据量小，添加索引反而会降低查询效率。
如何创建索引
格式： create index 索引名 on 表名(字段名(字段长度));
索引分类(了解)
聚集索引： 给表添加主键约束的时候数据库会自动通过主键创建索引，这个索引称为聚集索引。 聚集索引的磁盘块中保存数据
非聚集索引： 通过其它字段创建的索引称为非聚集索引，非聚集索引的磁盘块中只保存磁盘块的地址没有数据（因为数据只需要有一份）
#如何查看索引
格式： show index from 表名;
#如何删除索引
格式： drop index 索引名 on 表名;
复合索引
通过多个字段创建的索引
格式： create index 索引名 on 表名(字段1,字段2);
事务
数据库中执行同一业务多条SQL语句的工作单元，可以保证全部执行成功，或全部执行失败
#事务的ACID特性 是保证事务正确执行的四大基本要素
Atomicity： 原子性，最小不可拆分，保证全部成功全部失败
Consistency： 一致性，从一个一致状态到另一个一致状态
Isolation： 隔离性，多个事务之间互不影响
Durability： 持久性，事务完成后数据提交到数据库中持久生效
事务相关指令
begin：开启事务
commit：提交事务
rollback：回滚事务
savepoint xxx：保存回滚点
rollback to xxx：回滚到指定的回滚点 查看数据库自动提交状态
show variables like '%autocommit%';修改自动提交的状态 0关闭 1开启
set autocommit=0/1;显示在一行
group_concat()
