Mysql数据库

MySql数据库

---

什么是数据库

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、 可共享的、统一管理的大量数据的集合。
数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的 数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、 更新、删除等操作。
关系数据库
– 关系型数据库和常见的"表格"比较相似，关系型数据库中表与表之间是有很多复 杂的关联关系的。 常见的关系型数据库有Mysql，SqlServer,Oracle等。
非关系型数据库（NoSQL）
– 大量的NoSql数据库如MongoDB、Redis、Memcache出于简化数据库结构、 避免冗余、影响性能的表连接、摒弃复杂分布式的目的被设计。

---

一、数据库操作

建库建表

创建数据库
create database <数据库名>;

删除数据库
drop database <数据库名>;
drop database if exists <数据库名>

查看所有数据库
show databases;

切换数据库
use <数据库名>;

查看所有的数据库引擎
show engines;

如何查看当前默认引擎
show variables like ‘storage_engine’;

如何创建表？
先选中这个表存储的数据库
create table 表名(
列名1 数据类型 [约束] [默认值],
列名2 数据类型 [约束] [默认值],
…
)[engine=存储引擎] [default charset=字符编码集];
存储引擎的选择：MYISAM[支持全文索引] INNODB[支持事务，外键] MEMORY[哈希索引]

MySQL支持的数据类型
① 数值类型：
整型：int(n)
小数类型：decimal(m,n)
② 字符串类型：
固定长度：char(n) 若长度小于n，则用空格填充
可变长度：varchar(n)

修改表

修改表名
alter table 旧表名 rename to 新表名
– 实例：把tb_temp1的名字修改成temp1
alter table tb_temp1 rename to temp1;

修改字段的数据类型
alter table 表名 modify 字段名 新的数据类型 [约束];

修改字段名
alter table 表名 change 旧字段名 新字段名 数据类型;

添加字段
alter table 表名 add 新字段名 数据类型 约束;

删除字段
alter table 表名 drop 字段名;

修改字段的排列位置
alter table 表名 modify 字段名 数据类型 first|after 已存在的列名;
alter table tb_temp1 modify cc1 int first;
alter table tb_temp1 modify cc2 int alter cc1;

如何修改表的存储引擎
alter table 表名 engine=值;

二、数据库DML操作

语法：
		
		插入   insert into 表名(列名1,列名2,...) values(值1,值2);
		
		-- 注意：没有指定列名，默认插入全部列的值
		--       没有指定的列的个数和值的个数要一致，顺序也要一致，数据类型也要匹配
		
		
		修改：  update 表名 set 列名1=新值,列名2=新值,... [where 条件];
		
		-- 注意：若没有where字句，则代表修改全部
		--       有where字句代表，修改满足条件的记录
		
		
		删除    delete  from 表名  [where 条件]
		
		-- 注意：drop delete truncate的区别
		--       drop table 表名;删除表
		--       delete from 表名 where 条件;删除表中的数据，一行一行
		--       truncate table 表名;快速删除数据，删除表再新建表
		

三、查询

单表查询

语法:
			
			select distinct *|字段名1,字段名2,...
			from 表名
			[where 过滤条件]
			[group by 分组字段1]
			[having 分组后的过滤条件]
			[order by 字段名 asc|desc,字段名2 asc|desc]
			[limit m,n  分页]

-- sql 语句执行顺序
1 from  2  where  3  group by ... having  4 select   5  order by 

多表查询

-- 1.笛卡尔积
    select * from department;  -- 4条记录
    select * from employee;    -- 14条记录
	select * from department,employee;  -- 56条记录
		
		
-- 2.等值连接（通过两张表之间的外键关联起来）
		select * from department d,employee e where d.id=e.department_id;
		
-- 3.内连接   表1  inner join 表2 on 关联条件
		select * from employee a inner join department b on a.department_id=b.id;
		
-- 4.外连接
		-- 左外连接  left outer join   左表所有的数据+满足条件的数据
		-- 右外连接  right outer join  满足条件的数据+右表所有的数据

-- 5 联合查询 并集 union 和 union all  区别：是否合并重复的记录
	
	union 合并会重复
	union all 合并不会重复		

四、视图

 1》视图的概念
     从一张或者多张表中查询出来的结果，是一张虚拟的表。
	 它没有真实的数据，数据来源于原表，就是SQL语句的结果集。
	 你可以像操作表一样去操作视图。
-- 因为视图没有数据，数据存储在原表中
-- 当对视图进行修改，删除时，相应的原表也会发生改变。若原表的数据发生改变
-- 视图中的数据也会发生改变。

 2》视图的作用
  1、简单化，直接查询视图就是你想要的结果，不用写复杂的sql了
   那些被经常用于查询的复杂的sql语句可以定义成视图。
    -- 查询每个部门的员工人数
     create or replace  view v_dept_num as
			 select a.id 部门编号,count(b.id) 部门人数
			 from department a left join employee b  
			 on a.id=b.department_id
			 group by a.id;

   -- 有了视图
    select * from v_dept_num;
  2、 安全性
    不允许用户访问表，因为表中有敏感的数据
    例如： 一个员工表，一般用户只能看到每个员工工号和姓名，工资和年龄等看不到
          其实一般的用户看到的只是这个表的视图
      select no,name,salary,age from emp;
      create or replace view v_emp  as  select no,name from emp;
      
  3、屏蔽真实表结构变化带来的影响
  4、实现了逻辑数据独立性


3》 如何查看视图？
   desc 视图名称;
   desc v_dept_num;

   show create view 视图名称;
   show create view v_dept_num;

   SHOW TABLE STATUS LIKE '视图名'
   show table status like 'v_dept_num';

4》视图的更新操作不能被执行的情况，具体如下：
	1）视图中不包含基本表中被定义为非空的列
	2）在定义视图的SELECT语句后的字段列表中使用了数学表达式
	3）在定义视图的SELECT语句后的字段列表中使用聚合函数
	4）在定义视图的SELECT语句中使用了DISTINCT，UNION，GROUP BY或HAVING子句

 -- 对视图进行修改update操作
 -- 例如这个操作就会跟新失败 update v_dept_num set 部门人数=100 where 部门编号=4;


5》 删除视图
    drop view if exists 视图名称;

五、事务

-- 事务：一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务

-- 事务的四个特性：
1、原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

2、一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。

3、隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

4、持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。
	 
-- 事务操作：transaction
     -- 开启事务 start
	 -- 结束事务 end
	 -- 提交事务 commit
	 -- 回滚事务 rollback

-- 事务的隔离级别：
1、Read uncommitted(未授权读取、读未提交)： 
如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。
2、Read committed（授权读取、读提交）： 
读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
3、Repeatable read（可重复读取）： 
可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，即使第二个事务对数据进行修改，第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。这样避免了不可重复读取和脏读，但是有时可能出现幻象读。（读取数据的事务）这可以通过“共享读锁”和“排他写锁”实现。
4、Serializable（序列化）： 
提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。