mysql

阅读数: 次 2020-10-15

一.数据库管理

1.MySql服务器管理数据库位置

1	C:\ProgramData\MySQL\MySQL Server 5.5\data

2.查看所有的数据库名

1	show databases;

3.创建一个数据库

1	create database 数据库名;

4.删除一个数据库

1	drop database 数据库名;

二:表文件管理

1.查看表

1 2	USE 数据库名; show tables;

2.创建表

use  数据库;
create table 表文件名(
字段名  数据类型名,
段名  数据类型名  #最后一个字段不要以","
)

3.删除一个表文件

1	drop table 表文件名；

4.查看表文件字段信息

1	show create table 表文件名;

5.为表文件添加字段

1	alter table 表文件名 add 新字段名数据类型名;

6.删除表文件字段

1	alter table 表文件名 drop 字段名;

三:表文件数据行管理

1.插入命令

负责要求MySql服务器向指定的表文件添加数据行

1 2	insert into 表文件名(字段名1,字段名2，字段名3)values(值1，值2，'值3'); #插入字符串的时,value值中需要加单引号

2.查询命令

负责要求MySql服务器将指定的表文件数据行展示

1	select * from 表文件名;

3.删除命令

负责要求MySql服务器将指定的表文件数据行删除

1
2
3

delete  from 表文件名; #删除表文件中所有的数据行

delete  from 表文件名  where 判断条件; #将满足条件的数据行进行删除

4.更新命令

负责要求MySql服务器将指定的表文件数据行更新

update 表文件名 set 字段名1=值,字段名2=值 ；将所有数据行指定字段的值赋值新值

update 表文件名 set 字段名1=值,字段名2=值 where 判断条件; 将满足条件的数据行字段信息更新

四:高级插入命令

1.一次性向表文件插入多行数据

INSERT INTO
表文件名(字段1，字段2，字段3)
values
(值1，值2，值3)，
(值10，值20，值30);

2.简化版插入命令

如果插入数据时每一个字段都能赋值，此时插入命令不需要指定
字段名，插入值顺序此时需要与表文件字段顺序保持一致

1	insert into 表文件名 values(值1，值2，值3)

3.数据行备份命令

可以将表A中数据行复制到表B,(表A与表B字段结构完全一致)

1	insert into B select * from A

五:表文件备份命令

将表文件A进行一份备份，备份生成的表文件B

1	create table B select * from A

六:==临时表==

1.临时表定义：由查询命令在执行时，在内存中生成的表文件

2.临时表作用：每一个查询命令在执行时，实际上操作都是上一个查询命令生成的临时表

3.临时表生命周期：
    在当前查询命令执行完毕后，MySql服务器自动将上一个查询命令生成的
    临时表从内存中销毁。导致用户最终看到的临时表只能查询语句中，最后
    一个查询生成的临时表
    
4.七个查询命令与临时表之间关系：
 1.七个查询命令中，只有FROM命令不需要操作临时表，FROM负责将硬盘上表文件加载到内存
   中生成第一个临时表，剩下的六个查询命令操作的都是上一个查询命令生成的临时表

 2.七个查询命令中，只有GROUP BY命令在执行完毕后，才有机会在内存中生成多个临时表。
   其他五个查询命令在执行完毕后，只能在内存中生成一个临时表

 3.七个查询命令中，只有HAVING命令在执行完毕后，不会生成新临时表。负责将GROUP BY
   生成的临时表中不满足条件的临时表从内存中进行删除处理

七:查询命令

#==七个查询执行顺序==

1	from-->where-->group by-->having-->select-->order by-->limit

1.where

1.执行顺序：FROM-->WHERE-->SELECT

2.书写顺序：
    SELECT  字段名，函数， 子查询
    FROM    表文件
    WHERE   关系运算/逻辑运算


where 后面可以接判断条件
例如:
1) > < = >=
2) and 与运算符
	or 或运算符
3) between and 区间运算符
4) in: where 字段名 in(值1,值2)
5) not in: where 字段名 not in(值1,值2)
6) is null : 查询指定字段的值为null
7) is not null: 查询指定字段值不是null

2.like 模糊查询

	% 是一个通配符,表示一个任意长度额字符串
1) where 字段值 like 's%'  --->前置条件模糊查询
2) where 字段值 like '%s'  --->后置条件模糊查询
3) where 字段值 like '%s%'  --->包含条件模糊查询
4) where 字段值 like '_A%'  --->第二个字母是A的,_是通配符,表示任意一个字符

3.聚合函数

1) max(字段): 返回指定字段的最大值
2) min(字段): 返回指定字段的最小值
3) sum(字段): 返回指定字段下所有数据相加之和
4) avg(字段): 返回指定下所有数据平均值
5) count(字段): 返回指定字段下内容不等于null的数据个数
6) count(*): 统计临时表下所有数据行个数,不考虑数据行是否存在null

4.group by 分组查询

七个查询命令中，只有GROUP BY有机会在执行完毕后，一次生成==多个==临时表

1.执行顺序：
    FROM-->WHERE-->GROUP BY-->SELECT
    
2.书写顺序:
    SELECT  字段名,函数，子查询(一般是根据分组字段属性选)
    FROM    表名
    WHERE   定位数据行条件
    GROUP BY  分组字段
***如果同时出现WHERE与GROUP BY;GROUP BY应该在where之后

3.执行原理：
  GROUP BY 分组字段
  执行时,首先根据分组字段数据种类，将临时表数据行进行分类。
  然后将具有相同特征的数据行读取出来保存到一个全新临时表

5.group by 的多字段查询

1.多字段分组时，分组字段出现顺序对于最终查询结果没有
  任何影响的。
  Group by deptno,job 与  group by job,deptno
  给出最终结果是一致的

2.多字段分组时，GROUP By一次只能根据一个分组字段进行分组
  GROUP BY DEPTNO,JOB ,此时GORUP BY需要执行两次

3.多字段分组时，从第二个分组字段开始，操作的是上一个分组字段生成的临时表。
GROUP BY DEPTNO,JOB.当执行group by job时，操作临时表由
Group by deptno来生成

6.降序,升序

一般和order by合用

1 2	desc 降序 asc 升序

7.order by

1.执行顺序
FROM-->WHERE-->GORUP BY-->HAVING-->SELECT-->ORDER BY 
2.书写顺序
   SELECT 字段，函数，子查询
   FROM   表名
   WHERE  定位数据行判断条件
   GROUP BY 分组字段1，分组字段2.。。
   HAVING 进行临时表删除判断条件
   ORDER BY 排序字段1，排序字段2 desc or asc
3.执行原理：
   order by 负责将SELECT生成临时表数据行进行重新排序。
   然后将排序后数据行保存到一个全新临时表

4.例子:
 order by 不仅仅可以写字段命，也可以写字段在【当前临时表位置】
select deptno,ename,job,sal from emp where deptno=20 order by 4 desc

8.having


1.执行顺序：

   FROM-->WHERE-->GROUP BY-->HAVING-->SELECT

2.书写顺序：
    HAVING命令不能独立出现在查询语句。"只能出现在GROUP BY 后面"

    SELECT 字段名，函数名，子查询
    FROM   表名
    WHERE  数据行定位判断
    GROUP BY  分组字段1，分组字段2
    HAVING  通过统计临时表信息决定是否删除临时表

3.执行原理：
   having命令在group by之后执行的。负责将GROUP BY生成临时表中不满足条件的临时表从内存中删除掉

4.七个查询命令中，只有having在执行完毕后不会生成全新临时表

9.==limit== 截至

MySql服务器中，表文件字段位置从1开始计算，表文件数据行位置从0开始计算

例子:查询销售人员工资排在前两名的职员信息

1	select * from emp where job='salesman' order by sal desc limit 0,2

1.执行顺序：

 FROM-->WHERE-->GROUP BY-->HAVING-->SELECT-->ORDER BY-->LIMIT

2.书写顺序:
   SELECT 字段，函数，子查询
   FROM   表
   WHERE  定位数据行条件【关系运算/逻辑运算/特殊运算符】
   GROUP BY 分组字段1，分组字段2
   HAVING 对临时表进行删除判断条件
   ORDER BY 排序字段1，排序字段2
   Limit  起始行位置，从起始行开始向下截取行数

3.执行原理：
   从临时表指定位置的数据行开始，向下截取指定数量的数据行。将截取出来的数据行保存到一个全新的临时表

八:多表查询

1.表关系

表文件字段分类：

1.主键字段：用于存放主键编号的字段就是主键字段。每一个表都应该存在一个主键字段。主键编号相当于身份证编号，用于对表文件中
数据行身份进行确认。
主键字段中既不能出现null值，也不能出现重复值

2.非主键字段：描述主键编号

3.外键字段：外键字段只存在于【多方表】，描述多方数据与一方数据之间依赖关系
==外键字段的值应该来自于一方表中主键值==

2.多表查询的本质

1.将多张表数据行合并到同一个临时表，然后使用【六个查询命令】根据需求对临时表数据行进行定位分组排序等操作最终为用户提供需要的数据的查询过程

2.多表查询关键点：
如何将多张表数据合并到同一个临时表

3.==多张表合并方案==

1.==连接查询合并方案==：[最常见]

1）首先确保两张表之间存在了【隶属关系】，才可以使用连接查询合并方案
2）将两张表中数据行【沿着水平方向进行拼接】,最终得到一个拥有【完整隶属关系】新数据行

2.==联合查询合并方案==：
1）不需要两张表之间存在了【隶属关系】
2）将两张表中数据行【沿着垂直方向进行堆砌】

4.jion连接

1.将两个表文件数据行沿着水平方向进行拼接，拼接后数据行存在内存的一个临时表

2.命令格式 FROM 一方表 JOIN 多方表
FROM 多方表 JOIN 一方表

3.JOIN两边出现表位置，对于最终查询结果==没有==任何影响

4.JOIN生成临时表字段由一方表字段与多方表字段相加而来(m+n)

5.JOIN生成临时表字段名由==表名.字段==名组成，防止在同一个临时表出现同名字段问题

6.JOIN生成临时表数据行由一方表(n行)每一个数据行与多方表(m行)所有数据行拼接而成==(n*m)==行—–>笛卡尔积

7.JOIN生成临时表必然存在【脏数据行】，可以达到50%以上。【脏数据行】对后续统计分析带来巨大错误，因此在JOIN工作完毕后，必须要将【合法数据行】从当前临时表读取出来存放一个全新临时表，为后续操作做准备

5.连接查询过滤方案 on

概念:

JOIN生成临时表必然存在【脏数据行】，【脏数据行】对后续操作带来巨大影响。因此在JOIN生成临时表之后，必须要做的一件事将【合法数据行】与【脏数据行】进行==剥离==这个行为称之为【连接查询过滤方案】

语法:

FROM 一方表 JOIN 多方表
ON 定位合法数据行条件【连接查询过滤方案】

合法数据行与脏数据行区分原则：

1.如果表文件存在了明显的【外键字段】
ON 当前数据行来自于一方表主键字段值 = 当前数据行来自于多方外键字段值

2.如果表文件没有存在明显的【外键字段】

ON 根据实际隶属关系进行判断

例子:

SELECT  DEPT.DNAME, EMP.JOB, COUNT(*)

FROM DEPT JOIN EMP

ON  DEPT.DEPTNO = EMP.DEPTNO--->(on一般接的是主键值和对应的外键值)

WHERE  DEPT.DEPTNO =20

GROUP BY EMP.JOB

6.union联合查询

1.要求参与合并的两个临时表的字段结构必须保持一致【字段个数，字段类型顺序】

2.将两个临时表数据行沿着垂直方向堆砌到同一个临时表

3.联合查询生成临时表的字段只能来自于==【第一个临时表字段】==

4.UNION命令在执行时，自动将两个临时表内容相同数据行进行过滤，类似于Java中Set集合

5.UNION ALL命令在执行时，不会将临时表重复的数据进行过滤

需求： 将部门编号，部门名称，职员编号，职员名称展示出来

SQL:    select deptno,dname      from  dept   #第一个临时表
        UNION                                #联合查询合并方案
        select empno,ename        from emp    #第二个临时表

7.select子查询

1
2
3

概念:
 1.查询时无法从当前临时表得到需要的数据，此时MySql服务器允许开发人员通过【完整查询语句】从别处得到
  2.需要数据，从而保证查询正常执行，这个起到帮助作用【完整查询语句】相当于外援。在技术上被称为【子查询】

子查询可以在哪些场景中提供数据


 SELECT 字段，函数，子查询

 FROM   将硬盘上表文件加载到内存中生成一个临时表
        指向在内存中已经存在的临时表，此时可以借助于子查询提供这个临时表

 WHERE  可以通过子查询得到进行判断的数据

 GROUP BY 分组字段名1，分组字段2

 HAVING 可以通过子查询得到删除临时表时需要判断数据

ORDER BY 排序字段1，排序字段2

Limit  起始行位置，向下截取行数

九:字符串类型

1.varchar

varchar(m): 定长可变字符串
 定长： 当前字段可以存储的字符个数是固定的
	 ename  varchar(3)  # 3表示ename字段可以存储3个英文字符或则3个中文
 可变： ename字段在硬盘上存储字符空间可以根据实际情况进行【缩小】
		ename varchar(3)
		insert into test1 values('abc')  #硬盘  【a】【b】【c】
		insert into test1 values('ef')   #硬盘  【e】【f】

2.char

char(m):    定长不可变字符串
定长：
		当前字段可以存储的字符个数是固定的
		sex char(1)    #1表示sex字段可以存储1个英文字母或则1个中文
不可变:
		sex字段在硬盘上存储字符空间是固定的

sex char(3)
insert into test1 values('abc')   【a】 【b】 【c】
insert into test1 values('ef')    【e】 【f】 【空格】

针对char类型字段进行数据读取时，MySql服务器自动将字符串中【结尾处空格去掉】

如果插入字符串以空格结尾，不要添加到由char类型修饰的字段

十:约束

1.非空约束 not null： 要求管理的字段不能存储null值

2.唯一性约束 unique：要求管理的字段存储值不能有重复，但是可以存储null值

3.主键约束 primary key：  管理主键字段，要求主键字段内容不能为null同时也不能出现重复值

4.外键约束:管理外键字段，要求外键字段的值应该来自于关联的一方表中主键字段的值
	      但是可以存储null值

LTER TABLE 多方表
ADD CONSTRAINT  外键约束对象名  foreign key(外键字段名)
REFERENCES  一方表(主键字段名)

十一:index索引

1.索引作用：
	在表文件数据行增加时候，不会大幅度降低查询语句执行效率

2.索引工作原理
	索引工作原理，事先将字段中内容进行排序，在WHERE
	命令进行定位时，避免对表中所有的数据行进行遍历
	将会提升查询速度
	
3.索引管理:
1)将字段内容交给某个索引进行管理
	create index 索引名  on 表名（字段）

2)查询表文件关联的索引
	show index from 表名
	***在为表文件绑定【主键约束】，【唯一性约束】，【外键约束】时
	mysql服务器自动将当前字段数据进行排序并交给指定索引管理

 3）删除表文件指定索引
	DROP INDEX 索引名 ON 表名

十二:explain执行计划

1.命令格式:   explian  查询语句

2.命令作用：  展示当前查询得到结果是否通过索引来进行定位
	         DBA通过执行计划了解在本次查询过程，是否使用预先创建好的索引

3.执行计划对于查询语句执行效率判断
 1）在执行计划中，通过type属性展示查询语句执行效率
 2）执行效率分类:[慢----快]

ALL：WHERE对表文件所有数据行进行遍历，才得到了查询结果
	 这种级别查询语句，随着数据行增加导致执行速度大幅度
	 降低(DBA来说，极力避免这种级别查询)

type:WHERE对表文件所有数据行进行遍历，只不过在SELECT
	 抓取字段内容时，从索引中抓取。
	 这种级别虽然在抓取数据速度有所提升，但是依然面对
	 随着数据行增加导致执行速度大幅度（DBA来说，极力避免这种级别查询）

range:WHERE不会对表文件数据行进行遍历，而是直接从索引得到
	  定位的数据行行数，将大幅度提升查询效率。
	  这是DBA进行SQL优化时要保证最低级别。但是这种级别存在[不稳定性]
	  当字段内容发生变化时，导致索引失效
				        
ref: WHERE不会对表文件数据行进行遍历，而是直接从索引得到
	 定位的数据行行数,同时在这种情况根据定位条件一次只能
	 得到一个数据行，属于比较稳定执行效率.DBA努力达到的优化程度

const: 根据主键字段上索引进行定位，是执行效率最快的。但是
	   在实际使用过程中，几乎不会被用到的。

十三:view视图

1.定义：
    是MySql服务器中一个对象，用于存储查询语句。
	目的提供查询语句使用效率，避免在多处地方
	重复性开发相同查询语句

 2.命令：
	1）将查询语句交给一个视图对象管理
		CREATE VIEW  视图对象名  AS  查询语句
 	2）通过视图对象调用管理查询语句
 		select *  from 视图对象名
3.额外功能：
	  视图对象存储一个查询语句，同时视图对象拥有对当前查询语句关联的表文件操作能力
	1）通过视图对象对关联表文件数据行进行插入
	2）通过视图对象对关联表文件数据行进行删除
	3）通过视图对象对关联表文件数据行进行更新
 4.视图作用：【面试】
 1）提高了查询语句复用性，避免了在多处地方重复进行查询语句开发行为
 2）隐藏业务中涉及表关系，开发人员通过视图进行操作时是不会知道其具体
		     操作的表

十四:存储引擎

1.定义：
	是MySql服务器对应表文件内容管理方式。目前MySql服务器主要采用
	INNODB,MyIsam

2.管理命令:
  1)查看MySql服务器支持存储引擎种类
	show engines;
	
  2)修改MySql服务器默认存储引擎
 		 C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini下
		 default-storage-engine=INNODB  进行修改 myIsam

  3)设置表文件依赖存储引擎
	 show create table 表名；
	 alter table 表名  engine=存储引擎

3.MyIsam存储引擎特征:
  1.在对表文件内容进行修改时，不会进行备份操作。
	提高操作执行效率，但是在操作完毕后无法取消本次操作
  2.MyIsam存储引擎 使用三个文件存储表文件信息
	test1.frm : 存储表文件字段信息
 	test1.myd : 存储表文件数据行信息
  	test1.myi : 存储表文件字段关联的索引信息

4.INNODB存储引擎:
 1.在对表文件内容进行修改时，首先进行一次备份。
   在进行操作，因此执行效率相对较慢。
   但是在执行完毕后，由于有备份存在，可以使用
   备份取消当前操作，增加数据安全性

  2.INNODB存储引擎，使用一个文件存储表文件信息
	test1.frm:  存储表文件字段信息
				存储表文件数据行信息
				存储表文件字段关联的索引信息

十五:事务(transaction)

==事务—->备份==

1.介绍：
	事务是MySql服务器提供一个管理对象，用于对当前表文件备份进行管理

2.使用:
	start transaction; #通知MySql服务器提供一个事务对象，这个事务对象对接下来操作产生所有备份进行管理

	delete from emp where deptno=30 # 生成emp.bak

	delete from dept where deptno=30 #声明dept.bak

	rollback;   #通知mysql服务器将本次操作中所有备份信息覆盖到表文件，来取消本次操作

	commit;     #通知mysql服务器将本次操作中生成所有备份信息进行删除，称之为提交操作