Mysql数据库基础篇

MySQL概念

数据库的特点：
持久化存储数据的。其实数据库就是一个文件系统
方便存储和管理数据
使用了统一的方式操作数据库 – SQL

数据库：文件夹

表：文件

数据：数据

SQL

定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

SQL分类

1) DDL(Data Definition Language)数据定义语言
		用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
2) DML(Data Manipulation Language)数据操作语言
		用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
3) DQL(Data Query Language)数据查询语言
		用来查询数据库中表的记录(数据)。关键字：select, where 等
4) DCL(Data Control Language)数据控制语言(了解)
		用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

实际操作

DDL操作数据库、表

操作数据库

create

创建数据库，create database 数据库名
创建数据库，判断不存在，再创建： create database if not exists 数据库名称
创建数据库，并指定字符集  create database 数据库名称 character set 字符集名 utf8

show

查询所有数据库 show databases
查询某个数据库的字符集:查询某个数据库的创建语句 show create database 数据库名称

alter

修改数据库的字符集 alter database 数据库名称 character set 字符集名称

drop

删除数据库 drop database 数据库名称
判断数据库存在，存在再删除 drop database if exists 数据库

use

操作表

create

create table 表名(
				列名1 数据类型1,
				列名2 数据类型2,
				....
				列名n 数据类型n
			);
int：整数类型
double:小数类型
date:日期，只包含年月日，yyyy-MM-dd
datetime:日期，包含年月日时分秒	 yyyy-MM-dd HH:mm:ss
timestamp:时间错类型	包含年月日时分秒	 yyyy-MM-dd HH:mm:ss
varchar：字符串
复制表 create table 表名 like 被复制的表名

主键 primary key
唯一 Unique
非空 not null
默认 Default
外键 foreign key
索引 index
	# 创建员工表
	CREATE TABLE emp (
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(10) Unique,
		gender CHAR(1)  not null, -- 性别
		salary DOUBLE, -- 工资
		join_date DATE, -- 入职日期
		dept_id INT,
         index ename (NAME) -- 索引，index 索引名（要索引的列名）
        foreign KEY (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
	);

show

查询某个数据库中所有的表名称 show tables
查询表结构 desc 

alter

修改表名 alter table 表名 rename to 新的表名
修改表的字符集 alter table 表名 character set 字符集名称
添加一列 alter table 表名 add 列名 数据类型
修改列名称 类型 alter table 表名 change 列名 新列别 新数据类型
               alter table 表名 modify 列名 新数据类型
删除列 alter table 表名 drop 列名

alter外键和索引

创建索引
create index indexName on table_name (column_name)
修改表结构(添加索引)
alter table tableName add index index_name(column_name)
删除索引
drop index[indexName] on table_name

创建外键
-- foreign key (外键名) references 表名(列名)
foreign KEY (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
增加外键
alter table 表名 add foreign key  (外键名) references 表名(列名)
删除
alter table 表名 drop foreign key  (外键名)

索引的用法

1.表的主关键字 
自动建立唯一索引
2.直接条件查询的字段
-- 如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号）
select * from zl_yhjbqk where qc_bh='7001'

drop

drop table 表名;
drop table  if exists 表名 ;

DML 增删改表中数据

insert

1.向表格中插入新的行
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
2.从一个表中选取数据，然后把数据插入另一个表中
把所有的列插入新表 
SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

只把希望的列插入新表 
SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

delete

 delete from 表名 [where 条件]
 delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
 truncate table 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。

update

update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

DQL 查询表中的记录

select

select
		字段列表
	from
		表名列表
	where
		条件列表
	group by
		分组字段
	having
		分组之后的条件
	order by
		排序
	limit
		分页限定

where条件查询

1. where子句后跟条件
2. 运算符
		* > 、< 、<= 、>= 、= 、<>
		* BETWEEN...AND  
		* IN( 集合) 
		* LIKE：模糊查询
			* 占位符：
				* _:单个任意字符
				* %：多个任意字符
		* IS NULL  
		* and  或 &&
		* or  或 || 
		* not  或 !

order by排序

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
ASC: 升序，默认值
DESC: 降序

组合排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];

group by 分组

将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。
分组的目的就是为了统计，一般分组会跟聚合函数一起使用。
-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;

having 分组之后的条件

先分组再过滤  having 后面可以使用聚合函数
having子句必须于group by 子句同时使用，不能单独使用
使用having子句的作用是限定分组条件

where 和 having区别

where在分组前过滤，having在分组后过滤，两者之间不冲突

limit 分页限定

LIMIT offset,length;
offset：起始行数，从 0 开始计数，如果省略，默认就是 0
length： 返回的行数
在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。

DCL 定义数据库的访问权限和安全级别，及创建用户

管理用户，授权

1. 添加用户：* 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
2. 删除用户：* 语法：DROP USER '用户名'@'主机名';
3. 修改用户密码：   
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

权限管理：grant

1. 查询权限：
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';
2. 授予权限：
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给张三用户授予所有权限，在任意数据库任意表上GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3. 撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

一些特殊

多表查询：

1. 内连接查询：

隐式内连接：

使用where条件消除无用数据

-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

显式内连接：

select 字段列表 from 表名1 [inner] join 表名2 on 条件
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;	

2.外链接查询：

左外连接：

select 字段列表 from 表1 left [outer] join 表2 on 条件
查询的是左表所有数据以及其交集部分。
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT 	t1.*,t2.`name` FROM emp t1 left JOIN dept t2 ON t1.`dept_id` = t2.`id`;

右外连接：

select 字段列表 from 表1 right [outer] join 表2 on 条件；
查询的是右表所有数据以及其交集部分。
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT * FROM dept t2 right JOIN emp t1 ON t1.`dept_id` = t2.`id`;

3. 子查询

查询中嵌套查询，称嵌套查询为子查询。
			-- 查询工资最高的员工信息
			-- 1 查询最高的工资是多少 9000
			SELECT MAX(salary) FROM emp;
			-- 2 查询员工信息，并且工资等于9000的
			SELECT * FROM emp WHERE emp.`salary` = 9000;
			-- 一条sql就完成这个操作。子查询
			SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

---

事务

事务操作

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
      1. 开启事务： start transaction;
      2. 回滚：rollback;
      3. 提交：commit;
      UPDATE account SET balance = 1000;
      -- 张三给李四转账 500 元
      -- 0. 开启事务
		START TRANSACTION;
		-- 1. 张三账户 -500
		
		UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
		-- 2. 李四账户 +500
		-- 出错了...
		UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
		
		-- 发现执行没有问题，提交事务
		COMMIT;
		
		-- 发现出问题了，回滚事务
		ROLLBACK;    

事务的四大特征

	1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
	2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
	3. 隔离性：多个事务之间。相互独立。
	4. 一致性：事务操作前后，数据总量不变

数据库设计原理

第一范式 1NF 原子性，字段不可分割
第二范式 2NF 没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分
第三范式 3NF 不能存在传递依赖
第三范式是在第二范式上的基础上增加条件消除非主属性的传递依赖

数据库锁

悲观锁

假设会发生线程并发冲突，给每个线程都上锁，屏蔽所有一切可能违反完整性操作

java synchronized就是属于悲观锁，每次线程要修改数据时都要先获得锁，保证同一个时刻只有一个线程操作，其他线程被block

乐观锁

假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性

1.数据版本Version记录机制实现：为数据增加一个版本标识，在数据库表增加一个数字类型version字段实现。每次更新数据，version值加1。提交更新时，判断数据库表中对应的当前版本信息与第一次取出的version比对，如果相同，更新。不同，为过期数据。

2.时间戳timestamp：字段+时间戳 与 version类似，比较的是数据库中的数据时间戳 和 自己更新前取到的时间戳

索引的功能

大大加快数据的检索速度
加速表和表之间的连接
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

触发器trigger

在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。

【NEW 与 OLD 详解】
MySQL 中定义了 NEW 和 OLD，用来表示
触发器的所在表中，触发了触发器的那一行数据。
具体地：
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；使用方法： NEW.columnName （columnName 为相应数据表某一列名）


构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
create trigger audit_log
after insert on employees_test
for each row 
begin
insert into audit values(new.id,new.name);
end

视图view

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
create view actor_name_view as
select first_name as first_name_v, last_name last_name_v
from actor;