初识MySQL

一、分组

1. 命令行登录(如果没有配置环境变量需要cd到bin目录下)

   C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql  -uroot  -p1234
   

2. MySQL中创建的数据库是共享的，在多用户情况下，可以设置用户权限来限制每个用户可以访问哪个数据库

   在某个数据库建表需要先表明使用哪个数据库
   use database1
   

3. DQL：select

   DML：insert,delete,update

   DDL：数据定义语言，主要对表的结构进行操作。create,drop,alter

   TCL：事务提交commit；事务回滚rollback

   DCL：数据控制语言，授权grant，撤销权限revoke

4. 一些常见指令

   source D:/aliyun/bjpowernode.sql  #直接导入sql
   desc table1  #查看表的结构
   select database()  #查看当前使用哪个数据库
   

5. DQL 字符串类型要加单引号

   注意not in ()的内容不能有null，需要is not null去除

   修改别名
   select empno as no from emp=select empno 'myno' from emp
   select sal*12 as '年薪' from emp  #字符串/中文使用单引号
   select sal salary from emp #空格重命名
   
   查询
   select sal from emp where sal between A and B; #找出sal>=A且<=B的范围查询
   select ename from emp where comn is null;找字段为null的数据，注意使用is null，不能使用=
   select ename from emp where comm is not null;找出字段不为空的数据
   # in /not in
   
   模糊查询：%表示任意数量任意字符，_表示一个任意字符，\_转义表示_字符
   select ename from emp where ename like'%o%'; #找出名字有o的
   select ename from emp where ename like '\_%' ;找出名字里第一个为_的
   
   排序：order by 默认升序， order by xx desc降序
   select ename,sal from emp order by sal desc;
   select ename,sal from emp order by sal asc,ename asc; 先按照薪资升序，相等则按照名字升序
   

6. 单行处理函数：n行输入n行输出

   select lower(ename) from emp ; 小写
   upper大写，length去长度，trim去掉空格，substr（字符串，从1开始的起始下标，截取长度）
   concat 字符串拼接，round 四舍五入控制保留精度，
   
   null只要参与运算结果一定是null,因此使用ifnull(含有null的数据字段，被当作哪个值)，返回结果如果不为null则为原值，否则变为规定值。
   
   显示改变特定记录：case 字段名 when 值A then ... when.. then... else ... end
   select job,sal,(case job when 'manager' then sal*0.001 when 'salesman' then comm else 1 end)as new from emp;
   

7. 多行处理函数：n行输入单行输出。先分组再使用，否则整张表默认为一组。其中null数据自动忽略

   分组函数不能用在where字句

   count 计数 count(comm)统计字段下不为null的元素总数；count(*)统计表中的数据总行数
   avg 平均数
   sum 求和
   max最大值
   min最小值
   

   分组查询:

   一般select 后只能跟group by 分组的字段+分组函数，其它没有意义

   group by可以跟多个字段，进行联合分组限制

   使用having对分组后结果再进行筛查，但是性能低，考虑时先where后having

   select...from... where...group by... having...order by...limit...执行顺序：1.from 2.where. 3.group by 4.having 5.select 6.order by 7.limit

   找出每个工作岗位工资和
   select job,sum(sal) sum from emp  group by job order by sum desc;
   找出同个部门，不同岗位下最高薪资==>多个联合字段作为group by
   select max(sal),job,deptno from emp group by job,deptnp
   找出每个部门平均薪资，要求显示平均薪资高于3000
   select avg(sal) avg from emp group by deptno having avg>3000;
   

   ---

   ---

二、查询

1. 结果去重distinct

   select distinct job,deptno from emp; #联合字段，表示对同时满足所有字段相同的记录去重
   统计工作岗位数量
   select count(distinct job) from emp;
   

2. 连接查询

   • 内连接:select...from A join B on... where...(先表连接，再where筛选)

   select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno 
   找出每个员工薪资等级
   select e.ename,s.grade from emp e inner join salgrade s on e.sal between s.losal and hisal;
   查询员工和领导名字
   select e.ename,t.ename from emp e inner join emp t on e.mgr=t.empno
   

   • 左/右外连接:select ... from A right join B on ... (B表是主表，会显示A和B匹配的数据+B中剩余所有记录(此时该记录中的A表所有字段都为NULL)，主表中所有记录都会显示出来)

   select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;
   

   • 多表连接条件 select ...from a join b on...join c on... join d on...

   找出每个员工的部门名称以及工资等级，显示员工名，薪资，部门名。薪资等级，上级领导
   select  ename,sal,grade,dname from emp join dept on emp.deptno=dept.deptno join salgrade on emp.sal between salgrade.losal and salgrade.hisal left join emp l on emp.mgr=l.empno
   

3. 子查询(嵌套查询)——注意一定要给子表一个表名，否则MySQL报错

   找出比最低工资高的员工姓名和工资
   select ename,sal from emp where sal>(select min(sal) from emp)
   找出每个岗位的平均工资和薪资等级
   select  job,sal,grade  from (select job,avg(sal) sal from emp group by job) t join salgrade on t.sal between salgrade.losal and salgrade.hisal;
   找出每个员工的部门名称,和员工名
   select ename,dname from emp join dept on emp.deptno=dept.deptno
   

4. union 合并查询结果集(效率更高，减少匹配次数。表连接中匹配次数是笛卡尔积1000>union100+100)

   查询工作岗位是manager和salesman的员工
   select ename from emp where job=‘salesman’ 
   union
   select ename from emp where job ='manager'
   

5. limit将查询结果集的一部分取出来，用于分页。limit a,b 表示起始位置从下标a(第一条记录下标为0)开始取出b条

   按照薪资降序取出排名在5到9名的员工
   select ename from emp order by sal ename desc limit 4,5;
   

   limit (pageNo-1)*pageSize,pageSize

---

三、表数据增删改

1. DDL语句

   建表
   create table t_mytable（
   字段名1 数据类型，
   字段名2 数据类型 default xx，    指定默认值
   字段名3 数据类型
   ）;
   删表
   drop table if exists t_student;
   插数据
   insert into t_student(字段名称1，字段名称2) values(值1，值2)
   

2. 数据类型

   varchar(最长255字符)：可变长度字符串,会根据实际的数据长度动态分配空间。但是效率低速度慢。——性别

   char：定长字符串，速度快，使用不当空间浪费。——性别字段

   int：最长11位

   date：短日期类型，包括年月日

   datatime：长日期类型,包括年月日时分秒

   clob：字符大对象，最多可以存储4G字符串，超过255个字符都要clog存储——一篇文章，说明

   blob：二进制大对象，——插入一个图片或者视频，需要使用IO流

   create table t_movie（
   no bigint,
   name varchar(32),
   description clob,
   playtime date,
   image blob)；
   

3. insert日期

   str_to_date('字符串日期'，'日期格式')：将字符串varchar类型传换成date类型，注意都要加单引号

   date_format：将date类型转换成具有一定格式的varchar字符串类型

   mysql datetime日期格式：%Y年 %m月 %d 日 %h时 %i分 %s秒,

   curdate()显示当前年月日 curtime()显示当前时分秒

   转date
   insert into t_date(birth) values(str_to_date('2000-06-02-02-33-33','%Y-%d-%d-%h-%s-%i'))
   转string
   select date_format(birth,'%m--%d--%Y')
   now()获取系统当前时间，并且是datetime类型
   insert t_date(birth) values(now())
   

4. 计算日期年限差计算

   timestampdiff(间隔类型,减date,被减date),其中间隔类型可以为year,month,day,hour,minute,second

   datediff(被减date,减date)返回两者日期相差的天数day

   timediff(被减date,减date)返回两个时间相差的时分秒

5. 修改update：update 表名 set 字段名1=值1，字段名2=值2 where 条件

   一次添加多条记录insert t_test(name,age) values('翠阳'，45),('村姑'，80),(...)；

   快速建表，通过查询结果复制已有的一张表 create emp2 as select * from emp;

   删除数据delete from 表名 where 条件(表中的数据删除了在硬盘中的空间不会被释放，，删除效率低，优点在于可以回滚)

   快速删除数据：truncate table t_test;效率高，物理删除不可恢复，不支持回滚。

---

四、约束

1. not null非空约束，约束的字段不能为空

   create table t_vip(
   name varchar not null
   );
   

2. unique 唯一性约束，约束的字段不能重复，但是可以插入多个null。

   联合唯一性约束
   create table t_user(
   userid varchar(255) not null unique,   #同时非空和唯一约束则变为主键约束
   name varchar(255),
   age int,
   unique(name,age)
   )
   

3. 主键约束primary key,一个表中的主键约束只能建立一个。主键值一般建议定长的，如int ,char。

   自然主键的主键值是一个自然数，用的比较多；业务主键的主键值和业务紧密关联。自然主键值用的更多，因为如果是业务主键，业务频繁变动则会影响主键值。

   复合主键
   create table t_user(
   name varchar(255),
   age int,
   primary key(name,age)
   )
   主键设置自增，可以帮助自动维护主键。
   create table t_test(
   id int primary key auto_increment)
   

4. 外键约束foreign key

   要设计一张表时，如果表上某些字段的值重复比较多，空间冗余浪费比较严重，那么可以考虑分表，把那些重复字段抽出来另外建立一张表，并在主表添加外键约束。

   建表先建立父表，再建立子表，删除时先删除子表，再删除父表。

   外键值可以为null。

   子表
   create table t_sub(  
   name varchar(255),
   cno int,  -————cno是外键字段，每一个值叫做外键值
   foreign key(cno) references t_sub(no))
   主表
   create table t_main(
   no int primary key)  注意主表需要建立主键
   

五、事务

1. 指定存储引擎,字符编码方式

   create table t_test(
   ...
   )engine=InnoDB  default  charset=utf-8
   查看表的存储引擎
   

2. MyISAM：使用三个文件表示，表格式文件(表字段结构定义)、数据文件、索引文件。

   优势在于可被压缩为只读表来节省空间。

3. InnoDB：重量级存储引擎。优点是非常安全，支持事务。数据和索引存储在tablespace逻辑空间中。

4. 事务：多个DML语句要么同时成功要么同时失败，不可再分。通过InnoDB日志文件来实现。

   commit提交事务：清空事务的日志文件，将数据持久化到数据库中。

   rollback回滚：将所有DML操作全部撤销，并清空事务性活动的日志。

   start transaction; 开启事务(连续开启事务会自动提交中间的操作)
   update
   insert
   collback;回滚，下一次再使用需要重新start transaction
   

   • 四个特性

     A原子性：事务时最小的工作单元，不可再分

     C一致性：同时成功或者失败，保证同一个事务中数据的一致性。

     I隔离性：A事务和B事务具有一定的隔离

     D持久性：事务提交后，将数据持久化保存在硬盘上。

   • 隔离级别

   查看隔离级别 select @@tx_isolation
   设置隔离级别 set global transaction isolation level 隔离级别。
   

   读未提交：read uncommitted。事务A可以读到B未提交的数据，脏读。

   读已提交：read committed。事务A只能读到事务B提交之后的数据，解决了脏读，存在不可重复读取数据。比如事务A正在给数据库A插入数据，此时读取A数据库只有三条数据(注意这里select读数据语句在前面插入数据语句之后，因为事务的特性使select读到未持久化的数据)，而事务A提交commit之后，再读数据有四条记录。读取到的数据条目个数不同。

   可重复读：repeatable read。事务A读到的数据永远不改变。可能出现幻读，事务提交了也读不到，读取到的永远是插入事务开启之前的数据(读到的是一个备份/快照)。

   序列化：serializable。所有事务排队不能并发。

   注意以上例子说明过程中，读数据都要开启事务。

六、索引

1. 索引：在表的字段上添加所引以提高查询效率，缩小扫描范围，避免全表扫描。

   MySQL索引是自平衡B树。主键和unique会自动添加索引。每一行记录在硬盘上面都有物理存储编号。

   数据库优化优先考虑索引。

2. 索引添加条件：①数据量庞大②该字段经常出现在where后面，以条件的形式存在。③该字段很少DML增删改操作(因为DML后索引需要重新排序)

3. 索引创建删除

   创建
   create index i_name on t_test(name)
   删除
   drop index i_name  on t_test
   查看
   explain select...           #如果type为ref属性则说明该条查询语句用到了索引
   

4. 索引失效

   • 模糊查询时以%任意字段开头，就不会走索引，因为索引检索必须知道开头字母才能比对。因此尽量避免以%开头。

   • 使用or时仅当两边的字段都设置了索引才不会失效，因此不建议使用or，可以用union代替。

   • 使用多字段联合/复合索引，没有按照从左到右顺序的列字段进行查找也会失效。

     create index unionindex on emp(ename,age)
     

   • 字段在等号左边参加了运算/在等号左边对索引字段使用函数

七、范式

1. 第一范式：任何一张表必须有主键，每个字段原子性不可再分

   第二范式：第一范式基础上，所有非主键字段完全依赖主键，不产生部分依赖(A和B字段联合pk，C依赖A，D依赖B)。使用多张表来表示多对多关系(AC一张表，BD一张表，再加关系表)，或者使用单一主键。

   第三范式，第二范式基础上，不产生传递依赖。

   多对多三张表，关系表两个外键；一对多两张表，多的表加外键；一对一拆表，外键唯一。

2. 满足客户需求下，用冗余换执行速度，空间换时间，因为表连接越多效率越低。

八、视图

1. 视图view 只有DQL查询语句才能创建，对视图进行DML会影响到原表数据的变化和修改。

   创建视图
   create view testview as select * from t_test;
   删除
   drop view testview;
   

   当某条比较复杂的SQL语句需要在不同位置反复使用，每次重新编写很长很麻烦，可以考虑把这条语句以视图对象形式新建，面向视图开发，相当于一个引用。可以大大简化开发，修改时直接修改视图对象一个位置，等效于同时对视图映射的SQL语句结果进行修改。

   视图对象存储在一个文件，只要不删除是永久存储的，关机不会消失。

2. DBA权限管理语句,数据备份导入导出(不需要登录数据库，直接在dos命令行界面操作)

   将数据库导出为.sql文件
   mysqldump sms>D:\aliyun\sms.sql -uroot -p1234
   将某张表导出.sql文件
   mysqldump sms emp>D:\aliyun\sms.sql -uroot -p1234
   

   数据库导入时要注意路径双\转义

   source D:\aliyun\\bjpowernode.sql;
   

九、字符编码

1. 数据库默认编码

   查看数据库默认编码————注意这里的编码方式不是系统变量，针对每个数据库都会输出对应的默认编码值
   show variables like '%character%';
   查看某个库的字符集设置
   show create database sms;
   查看某个表的字符集设置
   show create table emp;
   查看状态 
   status;
   

   • character_set_client：设置客户端使用的字符集

   • character_set_connection：设置连接数据库时的字符集

   • character_set_results：数据库给客户端返回使用的编码格式

     下面两个变量都属于默认值，如果建库或者建表时没有指定，则按默认值character_set_database配置。

   • character_set_database：设置默认创建数据库的编码格式

   • character_set_serve：整个数据库默认安装的编码格式

   

2. 设置字符集

   主动设置
   create table t_table1(...) default character set utf8;
   

   同时设置character_set_client,character_set_connection,character_set_results，即客户端和服务器之间传递字符的编码规则,但是重启mysql失效。

   set names gbk;
   

   CMD客户端和服务器之间编码采用gbk，而workbench采用utf8

3. 修改字符集

   修改数据库编码格式(相当于修改character_set_database)————永久有效
   alter database smst character set utf8;
   修改数据表编码格式————永久有效
   alter table emp character set utf8;
   在某个数据库下修改某个默认编码————重启服务后失效
   set character_set_database=xx
   

十、MySQL连接用法

假设数据库已有如下表：

select * from a;			select * from b;
+--------+------+			+--------+-------+
| userid | name |			| userid | money |
+--------+------+			+--------+-------+
|   1001 | x    |			|   1001 |    22 |
|   1002 | y    |			|   1002 |    30 |
|   1003 | z    |			|   1003 |     8 |
|   1004 | a    |			|   1009 |    11 |
|   1005 | b    |			+--------+-------+
|   1006 | c    |
|   1007 | d    |
|   1008 | e    |
+--------+------+

1.INNER JOIN：内连接，返回匹配的两个表中的所有内容(包括两个表中的on字段)

select * form a  INNER JOIN  b  ON a.userid=b.userid
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

2.LEFT JOIN：左连接；返回两表中匹配内容和左表未匹配内容+右表null

select * form a  LEFT JOIN  b  ON a.userid=b.userid
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

3.using：作用相当于ON，使用的字段必须是两个表中共同拥有的，返回时没有重复列。

select * form a  INNER JOIN  b  using(userid)
+--------+------+-------+
| userid | name | money |
+--------+------+-------+
|   1001 | x    |    22 |
|   1002 | y    |    30 |
|   1003 | z    |     8 |
+--------+------+-------+

4.NATURAL JOIN：按照a表和b表中严格相同的所有列名匹配连接，相同的列名不重复显示。

注意where要在on之后。

数据库远程连接权限问题

问题：SpringBoot配置文件application.yml中连接数据库配置文件的路径改为本机的ip之后启动项目报错，连接不上数据库。

解决：登录连接MySQL后，输入以下两条命令，允许远程连接权限，并重启MySQL服务。

grant all privileges on *.* to 'root'@'%' identified by '1234' with grant option;
flush privileges;