MySQL知识点（2）

一、数据库概述

数据库的底层原理都和IO流有关，只不过我们不用写代码了，只需要写SQL就行了

数据库和表在硬盘中是以文件的形式存在的

1.MySQL的卸载

2.SQL、DB、DBMS分别是什么，他们之间的关系

3.创建数据库和删除

创建数据库

Create database 数据库名

删除数据库

Drop database 数据库名

4.什么是表

5.SQL语句的分类

6.导入数据

7.什么是SQL脚本

二、条件查询

1.between…and的用法

2.给字段起别名

3.Is null和Is not null

4.and 和 or 的优先级问题

5.distinct去除重复

6.In 和 not in

7.Like模糊查询

8.数据排序

三、分组查询

1.分组函数

分组函数只能分完组了之后才能用

1.Ifnull()空处理函数

2.count(*)和count(具体字段)的区别

3.where后为什么不能用分组函数

2.分组查询

分组函数和分组查询一般都一起用，分组查询不用分组函数就没什么意义

1.group by 和having

语法规则

2.多字段联合分组查询

3.having 和 where的选择

只使用了原数据

使用的是计算后的数据

4.执行顺序

四、连接查询

1.连接查询的概述

2.连接查询的分类

3.关于表别名的作用

不写表别名前面的字段会去后面的每张表中寻找

4.笛卡尔积现象

两张表连接查询如果不写条件，那么A表的第一条数据会和B表的每条数据去联合，以此类推A表的第二条。。。，输出的数据是A表的个数*B表的个数

5.如何避免笛卡尔积现象

通过条件去筛选，如emp表中的TOM，部门号是20，去和dept表中的20匹配，但是20还是依次和dept的每一个部门号查找，找到一样的20就输出显示，查找次数不变，只是显示变了

6.内连接之等值连接

7.内连接之非等值连接

8.自连接

自连接也能非等值连接

自连接是两表匹配的东西出来，不一定两张表的数据全部出来

9.外连接

主表的的数据会全部出来，而副表匹配的会出来，不匹配的自动用null来匹配

案例：找出哪个部门没有员工

10.多张表进行连接

案例：找出每一个员工的部门名称以及工资等级

案例：找出每一个员工的部门名称、工资等级、以及上级领导

五、子查询

1.where后面嵌套子查询

2.from后面嵌套子查询

3.select后面嵌套子查询

查询D表中和E.deptno相匹配的部门名称

六、union（可以将查询结果拼接）

注意，拼接的两条语句的字段数必须一致，不能一个是两字段，一个是一字段

七、limit(重点，以后分页查询表全靠它)

数据库中可能一张表有很多数据，一次查询出一张表会导致卡顿，浏览器崩溃，这时就要用分页，例如：百度的每一页

执行顺序

八、表

1.表的创建

表在数据库当中一般建议以t_或tbl_开头

数据类型

1.char和verchar如何选择

Char的效率比varchar高

2.删除表

3.复制表

2.插入数据

1.

一次插入多行数据

insert into 表名(字段名1，字段名2…)
values(),(),()

没设置默认追，则自动写null

将查询结果插入到一张表中

3.修改数据

4.删除数据

5.修改表结构

1.修改表名：
  	ALTER TABLE 表名 RENAME TO 新表名；
 	例：将student表名更改为students
 	 ALTER TABLE student RENAME TO students;

2.表中添加新列
	（1）语法
	alter table 表名 add (列名
	数据类型 [,列名
	数据类型] ...);

	案例：向表中添加一列为evaluatetime  类型为DATE
	alter table emp1 add (evaluatetime  DATE);
	
	（2）注意事项
	新添加的列会按顺序续接原表的列进行排列。
	
3. 修改表中存在的列
	（1）、语法
	alter table 表名 modify (列名 数据类型 [,列名 数据类型] ...);
	
	案例：改变列的数据类型
	alter table emp1 modify (workscore varchar(20));
	
4. 表中删除一个列
	（1）、语法
	 alter table 表名 drop列名；
	 
	（2）、注意事项
	 一次只能删除一列；
	 所删除的列不是表中的唯一列；
	 删除列将无法回滚，所以删除前请确认无误再进行操作。
	 
5.添加约束
	 例：给student表添加主键name;
	  ALTER TABLE student ADD primary key(name);

6.约束

1.非空约束

Not null 只有列级约束，没有表级约束

2.唯一性约束

3.主键约束

外键约束

九、存储引擎（了解）

存储引擎就是在建一张表的时候，以什么形式来保存这张表

1.MylSAM存储引擎（为了节省空间的时候用）

一张表有三个文件组成

2.InnoDB存储引擎（默认的存储引擎）（为了安全的时候用）

3.MEMORY存储引擎（为了执行快的时候用）

十、事务

1.什么是事务

2.事务的原理

例子：

2.事务的结束标志

== 提交==：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步

回滚：失败的结束，将所有的DML语句操作历史记录全部清空

3.事务的特性

4.事务的隔离性级别

多线程的时候

脏读：指一个线程中的事务读取到了另外一个线程中未提交的数据。另一个线程中的事务直接回滚的话，我读取到的数据就消失了

不可重复读：做不到这个数据我从头到尾读到的是一样的（比如：A事务早上九点读到14 条记录，我的事务一直没有停止，但是之间B事务改了硬盘的数据，晚上十点时我只能读到13条记录）

可重复读：比如A事务早上九点读到14条记录，我的事务一直没有停止，之间B事务改了硬盘的数据，晚上十点时还是读到14条记录，永远读的是我开启事务时的数据

十一、索引

1.什么是索引，有什么用

2.创建索引对象和删除

3.什么时候考虑给字段添加索引

4.查看SQL的执行计划

5.索引的实现原理

索引会自动排序和分区

索引底层采用的数据结构是：B+Tree

6.索引的分类和什么时候失效

十二、视图

1.什么是视图

同一张表的数据，站在不同角度去看

2.创建和删除视图

3.索引的作用

十三、DBA常用命令

1.新建用户

格式

CREATE USER username IDENTIFIED BY 'password';

说明:username——你将创建的用户名, password——该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

例如：创建一个用户名为 p361密码为123的用户

create user p361 identified by '123';

2.授权

命令格式：

grant all privileges on dbname.tbname to 'username'@'login ip' identified by'password' with grant option;

	dbname=  * 表示所有数据库
	tbname=  * 表示所有表
	loginip=  % 表示任何ip
	password为空，表示不需要密码即可登录
	with grant option;  表示该用户还可以授权给其他用户

细粒度授权

@localhost:本地用户
@%：远程用户任何IP

该用户只能在本地IP上登录
首先以root用户进入mysql，然后键入命令：
grant select,insert,update,delete on *.* to p361 @localhost  identified by "123"; 

如果希望该用户能够在任何机器上登陆mysql，则将localhost改为 "%" 

粗粒度授权

我们测试用户一般使用该命令授权，
授予p361用户所有权限：
ALL PRIVILEGES，并且该用户能在任何机器上登录mysql
GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123";        
              
注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' identified by "123"  WITH GRANT OPTION;

		privileges包括：

                              alter：修改数据库的表

                              create：创建新的数据库或表

                              delete：删除表数据

                              drop：删除数据库/表

                              index：创建/删除索引

                              insert：添加表数据

                              select：查询表数据

                              update：更新表数据

                              all：允许任何操作

                              usage：只允许登录

3.回收权限

命令格式：
revoke privileges on dbname.tbname from username;

例子：回收p361用户的所有权限
revoke all privileges on *.* from p361;

use mysql
select * from user
进入 mysql库中
修改密码;
update user set password =  password('qwe') where user = 'p646';
刷新权限;
flush privileges

4.导出导入

1、导出
例子：导出test数据库，到D盘下，形成test.sql文件
在windows的dos命令窗口中执行：mysqldump test>D:\test.sql -uroot -p123

例子：导出test数据库下的emp表到D盘
在windows的dos命令窗口中执行：mysqldump test emp> D:\ test.sql -uroot –p123


2、导入
Use test ——使用test数据库
登录MYSQL数据库管理系统之后执行：source  D:\ test.sql

十四、数据库设计三范式（重点，面试经常问）

1.设计三范式的作用

设计三范式都是为了减少数据的冗余

提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度

2.第一范式

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分

问题

解决

3.第二范式（多对多设计）

第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖

问题，部分依赖
部分依赖只有联合主键的时候才会发生

解决

4.第三范式（一对多设计）

第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖

问题。传递依赖

解决

5.一对一设计

一个字段主键加外键

一对多的变形，在一对多的基础上添加了一个unique