MySQL学习笔记

数据库

数据库介绍

之前通过IO流操作文件保存数据弊端
1、效率低
2、一般只能保存少量的数据
3、只能保存文本数据

什么是DB

• DataBase数据库，数据库实际上就是一个文件集合

什么是DBMS

• DataBaseManagementSystem 数据库管理系统，是用来管理数据库文件的软件，常见的DBMS：MySQL、Oracle、SQLServer....

数据库分类

1、关系型数据库
以表为单位保存数据，经过数学理论验证，关系型数据库可以保存现实生活中的任何关系
2、非关系型数据库
通常用于解决特点的问题如：数据缓存，Redis数据库（通过键值对）

主流关系型数据库介绍

1、MySQL：08年被Sun收购，09年Sun被Oracle公司，市场占有率第一，开源数据库

SQL

• Structured结构化 Query查询 Language语言，用户程序员和数据进行交互，通过sql对数据进行增删改查

数据库服务器

• 服务器就是一台高配置的电脑，数据库服务器就是在一台高配置的电脑上安装了提供数据库服务的软件（DBMS），则这台电脑就称为数据库服务器，数据库软件本身支持网络访问功能

连接并登陆MySQL数据库

• 连接数据库有以下几种方式：
  1、在命令行/终端
  2、通过三方客户端
  3、通过eciplse
• 如何连接数据库
  mysql -uroot -p
  -退出
  exit

数据库相关的SQL

• 查询所有数据库：show databases;
• 查询数据库详情(字符集)：show create database 数据库;
• 创建数据库指定字符集：create database db2 character set gbk;
• 删除数据库:drop database db2;
• 选中数据库:use db1;
• 修改数据库字符集:alter database db default character set gbk;

表相关SQL(选中数据库)

• 创建表：
  create table 表名 (字段名1 字段类型1, 字段名2,字段类型2...);
• 查询所有表:show tables;
• 查询表详情:show create table 表名;
• 查看表结构(表字段):desc table;
• 创建表指定引擎和字符集:
  格式：create table 表名 (字段1 字段1类型，...)engine=myisam charset=gbk;
• 删除表：drop table 表名;

• 添加字段： alter table 表名 add column 字段 类型;
  alter table 表名 add column 字段 类型 first;
  alter table 表名 add column 字段 类型 after xxx;

• 删除字段：alter table student drop column 字段;

• 修改字段类型[位置]： alter table 表名 modify column 字段名 新类型 [first/after xxx];

• 修改字段名称：alter table 表名 change column 旧字段名 新字段名 类型;

• 修改表名称： alter table 表名 rename to 新表名;
  rename table 原名 to 新名

• 修改表引擎和字符集:
  alter table 表名 engine=myisam charset=gbk;

• 数据库表的引擎
  1、innodb：支持数据库的高级操作，如：事务，外键等
  2、不支持高级操作，只支持基础的增删改查

数据相关SQL

• 插入数据：insert into 表名 values(值1，值2，...);
  insert into 表名 (字段1，字段2...) values(值1，值2...);

• 批量插入数据:
  insert into table values("张三",20),("李白",34),...;

中文问题:

• 在命令行执行 set names gbk;

• 显示？的是因为数据库或表的字符编码为gbk，改为utf-8;

• 查询数据:
  select * from 表 where 条件;

• 删除数据：
  delete from 表名 where 条件;

• 修改数据：
  update 表名 set 字段名=值 where 条件;

主键约束

• 什么是主键:表示表中的数据唯一性的字段称为主键
• 添加主键约束的字段，值唯一且非空。 primary key

主键+自增

• 格式:primary key auto_increment
  1、当自增字段的值为空时，会自动赋值并且数值加一。
  2、可以指定赋值。
  3、自增数值只增不减，不会因为删除数据而减少。
  4、在表中曾经出现过的最大值的基础上加1。

注释 comment

• 格式：comment '这里是注释内容'

`和'的区别

• `用来修饰表名和字段名，可以省略
• '用来修饰字符串

数据冗余

• 什么是冗余：如果表设计的不合理，随着数据量的增多，可能会出现大量的重复数据，这种重复数据成为数据冗余，可以通过拆分多个表的形式解决此问题。

事务

• 事务是数据库中执行sql语句的工作单元，可以保证事务内的SQL语句要么全部成功，要么全部失败。
  -如何使用事务：1、把数据库的自动提交改为手动提交，2、执行多条sql语句，此时sql会在内存中执行，3、当所有sql在内存中执行完后，手动提交，把多次改动一次性提交到数据库文件中。

• 查看数据库自动提交的状态
  show variables like '%autocommit%';

• 关闭和打开自动提交： 0:关闭 1：开启
  set autocommit=0;

• 提交：commit;

• 验证事务的步骤：
  1、 update person set money=money-1000 where id=2;
  2、此时select 8 from person 数据被改掉的，但是此时显示的内容是内存中的。
  3、打开新窗口select 8 from person，因为此时查询的是数据库中的文件，所有没有改变。
  4、在A窗口update person set money=money+1000 where id=1;
  5、此时A窗口中的数据改变（内存中数据），B窗口没有改变（数据库文件中的数据）
  6、回到A窗口执行commit，手动提交，此时B窗口的数据也发生改变了，因为A窗口中已经把两次内存中的改动提交到数据库文件中。

为什么使用事务？

当做某件事需要执行多条sql语句，如果不使用事务，则可能出现部分成功，部分失败，这样会使数据错乱，使用事务则可以保证事务要么全部成功，要么全部失败。

事务的执行流程：

所谓开启事务实际上就是把数据库的自动提交关闭改成手动提交，在手动提交之前多次SQL语句的执行只会对内存中的数据进行更改，当提交的时候会把多次SQL的执行结果一次性提交到数据库文件中

回滚 rollback

• 事务回滚会把内存中的数据回到上次提交的点
• 设置回滚点 ：savepoint 标示;
• 回滚到某个回滚点 ：rollback to 标示;
• 如果回滚到更早的时间点，则后面的不能再回滚。

SQL分类

1、DDL : Data Definition Language,数据定义语言，包括：create，alter，drop，truncate 不支持事务。
2、DML ： Data Manipulation Lauguage,数据操作语言，包括：insert，delete，update，select（DQL）。支持事务。
3、DQL ： Data Query Language 数据查询语言 ，只有select
4、TCL : Transaction Control Language 事务控制语言，包括：commit，rollback，savepoint，rollback to xxx
5、DCL ： Data Control Language 数据控制语言，用来分配用户权限相关的SQL

truncate

• 删除表并创建一个新表（空表）
• 格式：truncate table 表名
• truncate drop delete区别：执行效率drop>truncate>delete；
  drop只是删除表，truncate删除表并创建一个空表，delete只是删除数据自增数值不会清零。

数据库的数据类型

• 整数：int(m) bigint(m) m代表显示长度
  create table t(num int(10) zerofill); ---zerofill 零填充
• 浮点数： double(m,d) m总长度，d小数长度
  decimal(m,d) 超高精度浮点数，设计超高精度运算时使用。
• 字符串
  char(n) 固定长度 执行效率高 最大长度255
  varchar(n) 可变长度 更节省空间 最大长度65535（超高255建议使用text）
  text：可变长度 最大65535
• 日期类型
  date：保存年月日
  time：保存时分秒
  datetime：保存年月日时分秒，默认值null，最大值9999-12-31
  timestamp（时间戳）：保存年月日时分秒，默认值为当前时间，最大时间2038-1-19

create table t_date(
d1 date,
d2 time,
d3 datetime,
d4 timestamp);

insert into t_date values("2018-03-18","17:24:18","2018-09-10 17:27:20",null);

is null

1、查询没有上级领导的员工编号姓名和工资
select empno,ename,sal from emp where mgr is null;
2、查询emp表中没有奖金的员工姓名ename，工资sal，奖金comm

is not null

1、查询emp表中有奖金的员工信息

比较运算符 > < >= <= !=和<>

别名 as

1、select ename as "姓名",sal as "工资" from emp;
2、select ename "姓名",sal "工资" from emp;
3、select ename 姓名,sal 工资 from emp;

去重 distinct

select distinct deptno from emp;

and 和 or 可以用&& ||

• and 和java中的 && 效果一样
• or 和java中的 || 效果一样
  1、查询工资为5000,1500,3000,的员工信息
  select * from emp where sal=5000 or sal = 1500 or sal = 3000;
  select * from emp where sal in (5000,1500,3000);

[not] between x and y [不]在x，y之间(包含x，y)

1、查询工资在2000-4000之间的员工
select * from emp where sal between 2000 and 4000;
2、查询工资在2100到2800之外的员工
select * from emp where sal not between 2100 and 2800;

模糊查询 [not] like

• _ : 代表单个位置字符
• % ：代表0或多个字符
• 举例：
  1、名字以a开头 select * from emp where ename like "a%"
  2、a结尾 %a
  3、包含a %a%
  4、第二个字符是a _a%

排序 order by

• 如果有条件写下条件的后面，没有条件写在表名后面
  select ename,sal from emp order by sal desc;
  1、默认是升序 desc：降序 asc：升序

多字段排序

1、按员工部门升序，价格降序来排序
select ename,sal,deptno from emp order by deptno,sal desc;

分页查询 limit

• 格式 ：limit begin,size begin:跳过的条数 size：每页的数量

数值计算 +-*/

5%3等效5mod3

• 查询所有员工的姓名，工资及年终奖（工资5）
  select ename 年龄,sal 工资 , sal5 年终奖 from emp;
• 查询商品表中商品单价，库存，及总金额（单价库存）
  select price ,num,pricenum from t_item;

ifnull(x,y)函数

• age=ifnull(x,y) 如果x的值为null则赋值y，如果不为null则赋值x
  1、将emp表中奖金为null的全部改为0；
  update emp set comm=ifnull(comm,0);

聚合函数

• 对多行数据进行统计
  1、求和 ：sum(字段名)
  查询所有员工的工资总和 select sum(sal) from emp;
  查询20号部门工资总和 select sum(sal) from emp where deptno=20;
  2、平均值 : avg(字段名)
  3、最大值 ： max(字段名)
  4、最小值 : min(字段名)
  5、统计数量 : count(字段名|*)

和日期相关的函数

select 'helloworld'

• 获取当前的年月日时分秒
  select now();
• 获取当前年月日
  select curdate();
• 获取当前时分秒
  select curtime();
• 从年月日时分秒中 提取年月日 提取时分秒
  select date(now());
  select time(now());
• 提取时间分量 年 月 日 时 分 秒
  select extract(year from now())
  select extract(month from now())
  select extract(day from now())
  select extract(hour from now())
  select extract(minute from now())
  select extract(second from now())

日期格式化函数

• 格式 : date_format(日期，format);

• format : %Y 四位年 %y 两位年
  %m 两位月 %c 一位月
  %d 日
  %H 24小时 %h12小时
  %i 分
  %s 秒
  1、把now()格式改为 年月日时分秒
  select date_format(now(),"%Y年%m月%H时%i分%s秒");

• 把非标准格式转回标准格式
  str_to_date(非标准时间,format)
  select str_to_date('14.08.2018 08:00:00','%d.%m.%Y %H:%i:%s');

字符串相关函数

1、字符串的拼接 concat(s1,s2);
查询员工工资，以员工为单位 select concat(sal,"元") from emp;
2、获取字符串的长度 char_length(str);
查询员工姓名和名字的长度 select ename,char_length(ename) from emp;
3、获取字符串在另一个字符串出现的位置
格式1：instr(str,substr);
格式2：locate('d','abcd');
4、插入字符串 insert(str,start,length,newstr);
5、转大写 :upper('abc') 转小写: lower("NBA") 去空白：trim(' a b ')
6、截取字符串：left(str,len) right(str,len) substring(str,start,[length])
7、重复 repeat(str,count)
将str重复count次
8、替换 replace(str,old,new)
9、反转 reverse(str)

数学相关

1、向下取整 floor(num)
2、四舍五入 round(num) round(num,m) m代表小数位数
3、非四舍五入 truncate(num,m) m代表小数
4、随机数 rand() 0-1

分组查询 group by

• 各种关键字顺序：
  select * from 表名 where ... group by ... order by ... limit ...

having

• 一般要结合分组查询和聚合函数使用，用于聚合函数的内容添加条件
• 聚合函数的条件不能写在where后面
• 普通字段的条件写在where后面，聚合函数写在having后面
  select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;(错误)
  select deptno,avg(sal) from emp where group by deptno having avg(sal)>2000;(正确)

子查询(嵌套查询)

1、嵌套在SQL语句中的查询语句称为子查询
2、子查询可以嵌套n层
3、子查询可以写在哪些位置？

• 写在where或having后面当中当做查询条件的值
• 写在from后面 当一张新表 必须有别名
  select * from emp where sal>3000;
  select * from (select * from emp where sal > 1000) newtable;
• 写在创建表的时候
  create table emp_20 as (select * from emp where deptno=20);

关联查询

• 同时查询多张表的查询方式称为关联查询

笛卡尔积

• 如果关联查询不写关联关系则查询到的数据是两张表的乘积，这个乘积称为笛卡尔积
• 笛卡尔积是一种错误查询方式的结果，工作中切记不要出现

等值连接和内连接

1、等值连接：select * from A,B where A.x=B.x and A.age=18;
2、内连接：select * from A join B on A.x=B.x where A.age=18;

外连接

• 使用外连接查询得到的数据除了两张表的交集以外和另外一张主表的全部数据，哪个表为主表 left/right控制，left是以join左边表为主表，right以join右边的表为主表。

关联查询总结

• 关联查询的查询方式：等值连接、内连接、外连接
• 如果查询两张表的交集数据使用等值连接或内连接(推荐)
• 查一个表所有数据另外一个表交集数据使用外连接

关联关系之表设计

• 外键：用来建立关系的字段称为外键
• 主键：用来表示数据唯一性的字段称为主键

一对一

• 有AB两张表，A表中的一条数据对应B表中的一条数据，同时B表一条对应A表一条，这种关系称为一对一
• 如何建立关系：在从表中添加外键，外键的值指向主表的主键

一对多

• A表中的一条数据对应B表中的多条数据，B表中一条数据对应A表一条数据
• 如何建立关系：在多的一段添加外键指向另外一张表的主键

多对多

• A表中一条数据对应B表多条数据，B表一条数据对应A表多条数据
• 如何建立关系：需要创建新的关系表，表中添加两个外键，指向两个主表的主键

表设计之权限管理案例

• 创建三张主表(user(id,name) role(id,name) module(id,name))和两种关系表(u_r(uid,rid) 用户和角色 r_m (rid,uid)角色和权限)

视图

• 什么是视图：数据库中表和视图都是其内部的对象，视图可以理解成一张虚拟的表，视图本质上就是取代一段SQL查询语句
• 创建视图：create view 视图名 as 子查询 /创建出来一个虚拟表/
• 为什么使用视图：因为有些数据的查询需要书写大量SQL语句，每次书写浪费时间，使用视图可以起到SQL语句重用的作用,可以隐藏敏感信息。
  1、创建一个10号部门员工的视图
  create view v_emp_10 as (select * from emp where deptno=10);
  select * from v_emp_10;
  2、创建一个没有员工工资的视图
  create view v_emp_nosal as(select empno,ename,deptno from emp);

视图分类

1、简单视图：创建视图的查询中不包含：去重，函数，分组，关联查询的视图称为简单视图，简单视图可以对表中数据进行增删改查操作。
2、复杂视图：和简单视图相反，复杂视图只能进行查询。

对简单视图进行增删改查（操作和table一样）

1、插入数据
insert into view_name (字段....) values (值....);

• 数据污染：往视图中插入一条视图中不可见，但愿表中存在的数据称为数据污染。
• 通过 with check option 解决数据污染问题
  create view v_emp_20 as (select * from emp where deptno=20) with check option;
  insert into v_emp_20 (empno,ename.deptno) values(1000,"张三",10); //error
  insert into v_emp_20 (empno,ename.deptno) values(1000,"张三",20); //success
• 修改和删除只能操作视图中存在的数据
• 修改视图
  create or replace view v_emp_20 as (select * from emp where deeptno=20 and sal>2000);
• 删除视图 drop view 视图名;
• 视图别名：如果创建视图的子查询中使用别名，则对视图的增删改查时只能使用别名
  create view v_emp as (select ename name from emp);
  select name from v_emp; //只能使用别名

约束

• 什么是约束：约束就是给表字段添加限制条件
  1、非空约束 not null
  2、唯一约束 unique（不能重复）
  3、主键约束 primary key
  4、默认约束 dafault

外键约束

• 外键：用来建立外键的约束称为外键
• 外键约束：添加外键约束的字段，值可以为null，可以重复，但是不能是关联表中不存在的数据，外键指向的数据不能先删除，外键指向的表不能先删除。
• 如何使用外键约束：
  1、创建部门表
  create table dept(id int primary key auto_increment,name varchar(10));
  2、创建员工表
  create table emp(id int primary key auto_increment,name varchar(10),deptid int,constraint fk_dept foreign key (deptid) references dept(id));
• 格式：constraint 约束名称 foreign key(外键字段名称) references 表名(字段名)
• 工作中除非特定场景一般不使用外键约束，因为添加约束后会影响测试效率，一般通过代码建立逻辑外键

索引

• 什么是索引：索引是数据库中用来查询效率的技术，类似于目录

• 为什么使用索引：如果不使用索引，数据会零散的保存在磁盘中，查询数据需要挨个遍历每个磁盘块，直到找到数据为止，使用索引后会将磁盘块以树状结构保存，查询数据时会大大降低访问的磁盘块数量，从而提高查询效率。

• 索引是越多越好吗？
  索引会占用磁盘空间，只对常用的查询字段创建索引

• 有索引就一定好吗？
  如果表中数据量很少，添加索引反而降低查询效率

如何创建索引

• 格式：create index 索引名 on 表名(字段(长度));
• 如何查看索引：show index from 表名;
• 只要给表添加主键约束，则数据库会为此表自动创建主字段的索引。

删除索引

• drop index 索引名 on 表名;

复合索引

• 通过多个字段创建的索引称为符合索引
• 格式：create index 索引名 on 表名(字段1,字段2);

事务

• 数据库中执行SQL语句的工作单元，保证全部成功或全部失败

事务的ACID特征

• Atomicity：原子性，最小不可拆分，全部成功全部失败。
• Consistency：一致性，从一个一致状态到另一个一致状态。
• Isolation：隔离性，多个事务之间互不影响。
• Durability：持久性，事务完成后数据提交到数据库持久保存。

事务相关SQL

• 查看自动提交状态 show variables like "%autocommit%"
• 修改状态 set autocommit=0/1;
• 提交：commit;
• 回滚：rollback；
• 保存回滚点 savepoint s1；
• 回滚到某个回滚点 rollback to s1;

group_concat()

• 查询时字段连接，在一行显示