MySQL笔记

MySQL

SQL（Structured Query Language）结构化查询语言

文件夹目录介绍

文件夹名称	内容
bin	命令文件
lib	库文件
include	头文件
Share	字符集、语言等信息

my.ini配置文件常用参数

参数	描述
default-character-set	客户端默认字符集
character-set-server	服务器端默认字符集
port	客户端和服务器端的端口号
default-storage-engine	MySQL默认存储引擎 INNODB

MySQL默认存在的数据库

数据库名称	描述
information_schema	信息数据库，其中保存着关于所有数据库的信息（元数据）。 元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。
mysql	核心数据库，主要负责存储数据库的用户、权限设置、关键字等， 以及需要使用的控制和管理信息，不可以删除。
performance_schema	性能优化的数据库，MySQL 5.5版本中新增的一个性能优化的引擎。
sys	系统数据库，MySQL5.7版本中新增的可以快速的了解元数据信息的系统库 便于发现数据库的多样信息，解决性能瓶颈问题。

基本命令

SHOW DATABASES; #显示当前MySQL中包含的所有数据库

DQL

(Data Query Language)数据查询语言

SQL语句执行顺序

1.FROM ：指定数据来源表
2.WHERE : 对查询数据做第一次过滤
3.GROUP BY :分组
4.HAVING : 对分组后的数据第二次过滤
5.SELECT : 查询各字段的值
6.ORDER BY : 排序   asc:升序 desc：降序 
【注意】如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
7.LIMIT  : 限定查询结果

基本查询

1.FROM ：指定数据来源表
2.WHERE : 对查询数据做第一次过滤
#查询薪资是11000的员工信息（编号、名字、薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000;
-----------------------------
3.GROUP BY：分组
#查询各个部门、各个岗位的人数
SELECT department_id , job_id， COUNT(employee_id)
FROM employees GROUP BY department_id , job_id;
-----------------------------
4.HAVING : 对分组后的数据第二次过滤
#统计60、70、90号部门的最高工资
SELECT department_id , MAX(salary)
FROM t_employees
GROUP BY department_id
HAVING department_id in (60,70,90)
-----------------------------
5.SELECT : 查询各字段的值
select 字段名1，字段名2... from 表名；
【注意】：如果查询所有字段，则可以使用*来替代字段列表
-----------------------------
6.ORDER BY : 排序   asc:升序 desc：降序
#查询员工的编号，名字，薪资。按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序）。
SELECT employee_id , name , salary
FROM t_employees
ORDER BY salary DESC , employee_id ASC;
-----------------------------
7.LIMIT  : 限定查询结果
#查询表中从第四条开始，查询 10 行
SELECT * FROM employees LIMIT 3,10;
-----------------------------
ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
	表达式1：哪个字段需要判断是否为null
	表达式2：如果该表达式1为null，替换它的值。
-----------------------------	
DISTINCT：去除重复 
#查询员工表中所有经理的ID。
SELECT DISTINCT manager_id 
FROM t_employees;
-----------------------------	
不等值判断（!= 、<>）
【注意】：!= 等价于 <>

NULL值判断

判断一个值是否为NULL
列名  IS NULL
列名 IS NOT NULL
-----------------------------
e.g
#查询没有提成的员工编号,提成
SELECT employee_id,commission_pct FROM t_employees
WHERE commission_pct IS NULL;

区间判断 between … and

e.g
#查询员工的薪资在60~100之间的员工名字，薪资
SELECT name,salary FROM employees
WHERE salary BETWEEN 60 AND 100;#闭区间
【注意】：在区间判断语法中，小值在前，大值在后，反之，得不到正确结果，

枚举查询 in

e.g
#查询部门编号为70、80的员工信息名字部门编号）
SELECT name ,department_id FROM t_employees
WHERE department_id IN(70,80);
【注意】：in的查询效率较低，可通过多条件拼接。

模糊查询 like

‘_’ 表示单一任意字符
‘%’ 表示任意长度的任意字符
e.g
#查询名字第二个字母为"L"的员工名字，薪资 
SELECT name , salary FROM t_employees
WHERE name LIKE '_L%';

分支查询

CASE
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	WHEN 条件3 THEN 结果3
	ELSE 结果
END
-----------------------------
e.g
#查询员工名字，薪资 , 薪资级别<对应条件表达式生成>）
SELECT name , salary , 
CASE
    WHEN salary>=100 THEN 'A'
    WHEN salary>=80 AND salary<100 THEN 'B'
    WHEN salary>=60 AND salary<80  THEN 'C'
    WHEN salary>=40 AND salary<60  THEN 'D'
ELSE 'E'
END as "LEVEL"#对该列取别名
FROM employees
【经验】：与switch类似

时间查询

【经验】：执行时间函数查询，会自动生成一张虚表（一行一列）

时间函数	描述
SYSDATE()	当前系统时间（日、月、年、时、分、秒）
CURDATE()	获取当前日期
时间函数	获取当前时间
WEEK(DATE)	获取指定日期为一年中的第几周
YEAR(DATE)	获取指定日期的年份
HOUR(TIME)	获取指定时间的小时值
MINUTE(TIME)	获取时间的分钟值
DATEDIFF(DATE1,DATE2)	获取DATE1 和 DATE2 之间相隔的天数
ADDDATE(DATE,N)	计算DATE 加上 N 天后的日期

字符串查询

字符串函数	说明
CONCAT(str1,str2,str…)	将 多个字符串连接
INSERT(str,pos,len,newStr)	将str 中指定 pos 位置开始 len 长度的内容替换为 newStr
LOWER(str)	将指定字符串转换为小写
UPPER(str)	将指定字符串转换为大写
SUBSTRING(str,num,len)	将str 字符串指定num位置开始截取 len 个内容

聚合函数

【注意】：聚合函数自动忽略null值，不进行统计

聚合函数	说明
SUM()	求所有行中单列结果的总和
AVG()	平均值
MAX()	最大值
MIN()	最小值
COUNT()	求总行数

Any和ALL

e.g
#.查询高于 60 部门所有人的工资的员工信息（高于所有）
select * from employees where SALARY > ALL(select  SALARY from employees WHERE DEPARTMENT_ID=60);
#。查询高于 60 部门的工资的员工信息（高于部分）
select * from employees where SALARY > ANY(select  SALARY from employees WHERE DEPARTMENT_ID=60);

【了解】Union和Unionall

#合并两张表的结果，去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
#合并两张表的结果，不去除重复记录（显示所有）
SELECT * FROM t1 UNION ALL SELECT * FROM t2;
【注意】：合并结果的两张表，列数必须相同，列的数据类型可以不同

表连接

SELECT 列名 FROM 表 1 连接方式 表 2 ON 连接条件

内连接查询（INNER JOIN ON）
e.g
#查询所有员工名字、部门名称
SELECT e.name,d.department_name FROM employees e 
INNER JOIN departments d 
on e.id = d.id
-----------------------------
左外连接（LEFT JOIN ON）
右外连接（RIGHT JOIN ON）
#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中,部门名称以NULL 填充）
SELECT e.employee_id , e.name , e.salary , d.department_name FROM employees e
LEFT JOIN t_departments d 
ON e.department_id = d.department_id;
【注意】：左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果，匹配不到，则返回 NULL 值填充,右连接则相反

DML

(Data Manipulation Language)数据操作语言

新增（INSERT）

INSERT INTO() 表名(列 1，列 2，列 3....) VALUES(值 1，值 2，值 3......) 
【注意】：表名后的列名和 VALUES 里的值要一一对应（个数、顺序、类型）
简写：
INSERT INTO() 表名 VALUES(值 1，值 2，值 3......)

e.g： #添加一条员工信息
INSERT INTO t_jobs(JOB_ID,SALARY) VALUES('JAVA_Le',9000);

修改（UPDATE）

UPDATE 表名 SET 列 1=新值 1 ,列 2 = 新值 2,.....WHERE 条件;
【注意】：多数情况下都要加 WHERE 条件，指定修改，否则为整张表更新

e.g： #修改编号为100 的员工的工资为 25000
UPDATE t_employees SET SALARY = 25000 WHERE EMPLOYEE_ID = '100';

删除（DELETE）

DELETE() FROM 表名 WHERE 条件
【注意】：删除时，如若不加 WHERE条件，删除的是整张表的数据

e.g #删除编号为135 的员工
DELETE FROM t_employees WHERE EMPLOYEE_ID='135';

清空整表数据（TRUNCATE）

TRUNCATE() TABLE 表名
【注意】：与 DELETE 不加 WHERE 删除整表数据不同，TRUNCATE 是把表销毁，再按照原表的格式创建一张新表

DDL：操作数据库表

DDL(Data Definition Language)数据定义语言

操作数据库

1.创建（Create）：
#创建数据库：
     create database 数据库名称;
#创建数据库，判断不存在，再创建：
	 create database if not exists 数据库名称;
#创建数据库，并指定字符集
	 create database 数据库名称 character set 字符集名;
-----------------------------
2.查询（Retrieve）：
#查询所有数据库的名称:
	 show databases;
#查询某个数据库的字符集:查询某个数据库的创建语句
	 show create database 数据库名称;
-----------------------------
3.修改（Update）：
#修改数据库的字符集
	 alter database 数据库名称 character set 字符集名称;
-----------------------------
4.删除（Delete）：
#删除数据库
	 drop database 数据库名称;
#判断数据库存在，存在再删除
	 drop database if exists 数据库名称;
-----------------------------
5.使用数据库
#查询当前正在使用的数据库名称
	 select database();
#使用数据库
	 use 数据库名称;

操作表

1.创建（Create）：
	create table 表名(
		列名1 数据类型1,
		列名2 数据类型2,
		....
		列名n 数据类型n
	);
【注意】：数据类型n之后不需要加逗号（,）
-----------------------------
2.查询（Retrieve）：
#查询某个数据库中所有的表名称
	 show tables;
#查询表结构
	 desc 表名;
-----------------------------
3.修改（Update）：
#修改表名
     alter table 表名 rename to 新的表名;
#修改表的字符集
	 alter table 表名 character set 字符集名称;
#添加一列
	 alter table 表名 add 列名 数据类型;
#修改列名称 类型
	 alter table 表名 change 列名 新列别 新数据类型;
	 alter table 表名 modify 列名 新数据类型;
#删除列
	 alter table 表名 drop 列名;
-----------------------------
4. 删除（Delete）
	 drop table 表名;
	 drop table  if exists 表名 ;

【了解】DCL(Data Control Language)数据控制语言

创建用户
CREATE USER 用户名 IDENTIFIED BY 密码
授权
GRANT ALL ON 数据库.表 To 用户名
撤销权限
REVOKE ALL ON 数据库.表名 FROM 用户名
删除用户
DROP USER 用户名

约束

primary key：主键约束 
非空且唯一，一张表只能有一个字段为主键
unique：唯一约束 
唯一，标识表中的一行数据，不可重复，可以为 NULL
auto_increment：自动增长
给主键数值列添加自动增长。从 1 开始，每次加 1。不能单独使用，和主键配合。
not null ：非空，此列必须有值。
default ：为列赋予默认值
当新增数据不指定值时，书写DEFAULT，以指定的默认值进行填充
-----------------------------
外键约束：
constraint 外键名称的别名 foreign key (外键列名称) references 主表名称(主表列名称)
-----------------------------
e.g
create table teacher(
	t_id int primary key auto_increment,
    t_name varchar(20)
)
create table stu(
	id int primary key auto_increment,-- 给id添加主键约束,自动增长
	name varchar(20) unique not null, -- 唯一约束,非空
    class varchar(20) default '302班', -- 设定默认值   
    foreign_id int 
    constraint t foreign key (foreign_id) references Teacher(t_id)
);

数据库设计

一对多(多对一)：
	如：部门和员工
	实现方式：在多的一方建立外键，指向一的一方的主键。
多对多：
	如：学生和课程
	实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

视图

1.创建视图:
CREATE VIEW 视图名 AS 查询数据源表语句: 
e.g:
#创建 t_empInfo 的视图，其视图从 t_employees 表中查询到员工编号、员工姓名、员工邮箱、工资
CREATE VIEW t_empInfo 
AS SELECT id,name,email,salary from t_employees;
-----------------------------
2.使用视图：
e.g
#查询 t_empInfo 视图中编号为 1 的员工信息
SELECT * FROM t_empInfo id = '1';
-----------------------------
3.修改视图：
#方式 1：如果视图存在则进行修改，反之，进行创建
CREATE  OR  REPLACE VIEW t_empInfo 
AS SELECT id,name,department_id from t_employees;
#方式 2：直接对已存在的视图进行修改
ALTER VIEW t_empInfo
AS SELECT id,name,email from t_employees;
-----------------------------
4.删除视图：
#删除t_empInfo视图
DROP VIEW t_empInfo;
【注意】:
1.视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
2.如果视图包含以下结构中的一种，则视图不可更新
  - 聚合函数的结果
  - DISTINCT 去重后的结果
  - GROUP BY 分组后的结果
  - HAVING 筛选过滤后的结果
  - UNION、UNION ALL 联合后的结果

事务

事务的概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
e.g 银行转账的例子

事务的原理

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL	语句均正常结束（commit），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（rollback）。

事务的操作

开启事务：start transaction
禁止自动提交：setAutoCommit = 0
回滚事务：rollback
提交事务：commit

事务提交的两种方式

MySQL数据库事务默认自动提交
一条DML语句会自动提交一次事务
手动提交：
Oracle数据库默认是手动提交事务
需要先开启事务，再提交

事务的四大特征

1.Atomicity(原子性)：是不可分割的最小操作单位，要么同时成功，要么同时失败
2.Durability(持久性)持久性：事务一旦提交或回滚后，数据库会持久化的保存数据
3.Isolation(隔离性)隔离性：多个事务之间，相互独立
4.Consistency(一致性)：事务操作前后，数据总量不变，e.g 转账，我减30元，你就涨30元

事务的隔离级别

概念：多个事务之间是隔离的相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别可以解决这些问题

【存在问题】：
脏读：一个事务，读取到另一个事务中没有提交的数据
不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
隔离级别：
1. read uncommitted：读未提交
   产生的问题：脏读、不可重复读、幻读
2. read committed：读已提交 （Oracle）
   产生的问题：不可重复读、幻读
3. repeatable read：可重复读 （MySQL默认）
   产生的问题：幻读
4. serializable：串行化
  可以解决所有的问题
【注意】：隔离级别从小到大安全性越来越高，但是效率越来越低
数据库设置隔离界别级别：
set global transaction isolation level 级别字符串；

【问题】：

mysql字符串char与varchar的区别

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

b