更多mysql学习文档
第1章 MySQL概述
一 作用 1.实现数据持久化 2.使用完整的管理系统统一管理,易于查询 二 区别 DB: 数据库(database):存储数据的“仓库”。它保存了一系列有组织的数据。 DBMS : 数据库管理系统(Database Management System)。数据库是通过DBMS创建和操作的容器 SQL :结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。 三 常见的数据库管理系统:MySQL、Oracle、DB2、SqlServer等 四 SQL的优点: 1、不是某个特定数据库供应商专有的语言,几乎所有DBMS都支持SQL 2、简单易学 3、虽然简单,但实际上是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作。 五 操作 SQL(Structural query language)语句分为以下三种类型: DML: Data Manipulation Language 数据操纵语言 对表中数据的增,删,改,查操作 DDL: Data Definition Language 数据定义语言 对表进行创建,删除,修改表等操作 DCL: Data Control Language 数据控制语言 权限和事务的处理。
第2章 说明和基本操作
#查看所有的库
show databases;
#选中某一个库
use 库名;
#查看所有的表
show tables;
第3章 DML语言(上)
一 查询操作
#select 字段名1,字段名2...... from 表名
select first_name,salary
from employees;
#查询所有的字段 select * from 表名
select * from employees;
二 别名
#第一种格式 :select 字段名 别名 from 表名
select first_name name from employees;
#第二种格式 :select 字段名 as 别名 from 表名
select first_name as name from employees;
#注意 :别名我们一般都会使用双引号引起来-尤其是在别名中有空格出现时必须使用双引号
select first_name as "first name" from employees;
三 过滤
#where 过滤条件
select salary
from employees
where salary > 1000;
四 运算符
#between...and....
select salary
from employees
where salary between 6000 and 10000; # 相当于salary >= 6000 and salary <= 10000
#in
select salary
from employees
where salary in (2000,3000)l #相当于 salary=2000 or salary=3000
like:模糊查询
#like : 模糊查询
#例 :查询员工的姓名中带有a字符的所有员工
SELECT first_name
FROM employees
WHERE first_name LIKE '%a%';#%表示任意个数任意字符
#例:查询员工的姓名第二个字符带有a的员工。
SELECT first_name
FROM employees
WHERE first_name LIKE '_a%';# _表示任意一个字符
#例:查询员工的姓名第二个字符为_的员工的姓名
SELECT first_name
FROM employees
WHERE first_name LIKE '_\_%'; # \_ 转义字符
#指定转义字符
SELECT first_name
FROM employees
WHERE first_name LIKE '_$_%' ESCAPE '$'; # escape:指定转义字符
五 排序
5.1格式
/*
order by 字段1 desc/asc,字段2 desc/asc.....
注意:
1.如果没有指明是升序还是降序默认是升序
*/
5.2二级排序
/*
例 :
select salary,department_id
from employees
order by salary,department_id;
*/
5.3可以按照别名排序
/*
select salary s
from employees
order by s;
*/
六 多表查询
6.1.说明
①连接的方式:
/*
自连接 vs 非自连接
等值连接 vs 非等值连接
内连接 vs 外连接
*/
②有sql92和sql99语法的区别
sql92语法
select e.first_name,d.department_name
from employees e,departments d
where e.department_id = d.department_id;
sql99语法
/*
select 字段名
from 表1 [inner] join / left [outer] join / right [outer] join 表2
on 连接条件
join 表3
on 连接条件
.........
*/
select e.first_name,d.department_name
from employees e join departments d
on e.department_id = d.department_id;
③内连接vs外连接
/*
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL).
*/
#内连接 :获取所有员工的姓名和部门名称
select e.first_name,d.department_name
from employees e join departments d
on e.department_id = d.department_id;
#左外连接 :获取所有的员工及部门名称
select e.first_name,d.department_name
from employees e left join departments d
on e.department_id = d.department_id;
#右外连接 :获取所有的部门及员工
select e.first_name,d.department_name
from employees e right join departments d
on e.department_id = d.department_id;
#将右外连接变成左外连接
select e.first_name,d.department_name
from departments d left join employees e
on e.department_id = d.department_id;
6.2常见的一个错误(迪卡尔集错误)
#原因 :没有连接条件或者连接条件错误
6.3 满外连接的实现(mysql不支持full join)
#满外连接:full join
#mysql不支持full join我们可以使用关键字union将左外连接的结果和右外连接的结果合并
SELECT e.`first_name`,d.`department_name`
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
UNION
SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
七 函数
7.1函数的分类
单行函数 ,组函数, 通用函数
7.2单行函数
LOWER('SQL Course') : 将内容全部转为小写
UPPER('SQL Course') :将内容全部转为大写
CONCAT('Hello', 'World') : 将两个字符进行拼接
SUBSTR('HelloWorld',1,5) : 将字符串从1的位置开始截取子串长度为5。(索引从1开始)
LENGTH('HelloWorld') : 字符串长度
INSTR('HelloWorld', 'W') :w在当前字符串中首次出现的位置
LPAD(salary,10,'*') :向右对齐
RPAD(salary, 10, '*') :向左对齐
TRIM('H' FROM 'HelloWorld') : 去除字符串两端指定的字符H
REPLACE('abcd','b','m') : 将当前字符串中的b替换成m
ROUND: 四舍五入
ROUND(45.926, 2) 45.93
TRUNCATE: 截断
TRUNCATE(45.926) 45
MOD: 求余
MOD(1600, 300) 100
now() : 当前日期和时间
7.3组函数
组函数
AVG() :求平均数
SUM(): 求和
MAX():求最大值
MIN() :求最小值
COUNT() : 求数量
#avg()和sum() : 注意只能运算数值类型的数据
#处理的数据不包括null
#max()和min() :注意只能运算数值,日期,字符串
count(字段名) : 统计的时候不包括null
count(*) : 统计表中所有的数据的条数(个别字段为null不受影响)。
count(数值) : 统计表中所有的数据的条数。
每条数据都会"存放"该数值然后统计该数值的个数。
效率比count(*)高
7.4通用函数
IFNULL(commission_pct,0) : 如果commission_pct中的内容为null那么就用0替换
----------------------等值------------------------
case 字段名
when 值1 then 相应的操作1
when 值1 then 相应的操作2
....
else
相应的操作
end
----------------------非等值----------------
case
when 条件1 then 相应的操作1
when 条件2 then 相应的操作2
....
else
相应的操作
end
7.5if函数
#if(条件表达式,值1,值2) :如果条件表达式的值为true结果为值1,否则为值2
7.6去重:distinct
#distinct 字段名: 去除重复的数据
八 分组和过滤(having)
8.1分组的格式
select 字段名1,字段名2
from 表名
group by 字段名1,字段名2......
#例:求各部门中不同工种的员工的最低薪水。
SELECT department_id,job_id,MIN(salary)
FROM employees
GROUP BY department_id,job_id;
#注意:select后面如果出现了组函数和其它字段,那么其它字段必须出现在group by的后面。
8.2过滤 having
having 过滤条件
having和where的不同点?
1.having后面可以现出组函数,where后面不能出现组函数
2.过滤条件如果可以出现在where或having后面,那么优先放在where后面(不影响结果)
#例:求各部门平均薪水大于6000的部门。
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)>6000;
第4章 DML语言(中)
一 子查询说明
1.子查询分为单行子查询和多行子查询
单行或多行指的是子查询的结果的条数。
2.单行子查询使用的运算符:
= <> >= <= > <
多行子查询使用的运算符:
in any all
3.在执行带有子查询的sql语句,先执行内(子)查询再执行外查询。
二 单行子查询
#例:谁的工资比 Abel 高?
#思路:1.要先查到Abel工资是多少 2.再查全表进行过滤
#方式一:
SELECT salary
FROM employees
WHERE last_name='Abel'; #11000
SELECT salary
FROM employees
WHERE salary > 11000;
#方式二:自连接
SELECT e.`salary`
FROM employees e JOIN employees e2
ON e.`salary` > e2.`salary` AND e2.`last_name`='Abel';
#方式三:子查询(子查询和自连接都可以使用的时候优先使用自连接)
SELECT salary
FROM employees
WHERE salary > (
#先执行子查询,子查询的结果给外查询使用
SELECT salary
FROM employees
WHERE last_name='Abel' #单行子查询
);
#例:题目:返回job_id与141号员工相同,salary比143号员工多的员工
# 姓名,job_id 和工资
#思路:1.先查找到141号员工的job_id和143号员工的薪水 2.再过滤
SELECT job_id
FROM employees
WHERE employee_id=141;#ST_CLERK
SELECT salary
FROM employees
WHERE employee_id=143;#2600
SELECT first_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
) AND salary > (
SELECT salary
FROM employees
WHERE employee_id=143
);
三 多行子查询
#题目:返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员
# 工号、姓名、job_id 以及salary
#思路:1.查找job_id为‘IT_PROG’的所有员工的薪水 2.其它部门的薪水和该部门的该薪水做比较
SELECT employee_id,first_name,job_id,salary
FROM employees
WHERE salary < ANY (
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
#题目:返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
# 的员工号、姓名、job_id 以及salary
SELECT employee_id,first_name,job_id,salary
FROM employees
WHERE salary < ALL (
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
第5章 DDL 创建和管理表
一 常用操作
#创建库
create database 库名;
#删除库
drop database 库名;
#选库
use 库名;
#查看库中所有的表
show tables;
#删除表
drop table 表名;
#查看表和库的信息
show create database/table 库名/表名;
#指定库的编码集
create database 库名 character set '编码集';
#指定表的编码集
create table 表名(
字段名 类型
.......
)character set ‘编码集’
二 创建表
①方式一 :白手起家
create table 表名(
id int,
name varchar(20),
........
)
②方式二 : 基于现有的表创建新表(把查询的结果创建成一张表,没有原表中的约束)
create table 表名
select 字段名1.......
from 表名;
三 对表中的列进行增,删,改的操作
对表名修改: alter table 原表名 rename to 新表名;
格式: alter table 表名 add/drop/modify/change column(可省略) …..
#添加列
ALTER TABLE person ADD COLUMN sex CHAR;
#修改列的名称
ALTER TABLE person CHANGE COLUMN age2 age3 CHAR;
#修改列的类型
ALTER TABLE person MODIFY COLUMN sex INT;
#删除列
ALTER TABLE person DROP COLUMN birthday;
清空表:
TRUNCATE TABLE emp;
第6章 DML语言(下)
一 插入数据
#插入单行数据
insert into 表名(字段名1,字段名2...) values(value1,value2......)
#插入多行数据
insert into 表名(字段名1,字段名2...) values(value1,value2......),(value1,value2......).....
#如果插入数据时是全字段插入,那么表名后面的字段可以省略
insert into 表名 values(value1,value2......)
#基于现有的表中的数据插入到当前表中
#注意:数据类型必须保持一致
insert into 表名(字段名1,字段名2....)
select 字段名1,字段名2.....
from 表名
二 删除数据
delete from 表名; #删除表中所有内容
delete from 表名 where 过滤条件;
三 修改数据
#修改表中所有的指定字段的值
update 表名 set 字段名1(列)=value1(新值),字段名2=value2...
update 表名 set 字段名1=value1,字段名2=value2... where 过滤条件
四 truncate和delete的区别?
1.truncate删除的内容不能回滚,delete删除的内容可以回滚(前提是要开启事务)
2.如果删除的数据不用回滚那么优先truncate因为效率高一些
第7章 DCL 数据库事务
一 说明:
事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态
案例 :AA给BB转账100
AA :1000
BB :1000
操作 :
1.AA-100 : 900
---------发生异常了--------
2.BB+100 : 执行不了
上面遇到的问题 :AA账号操作成功,另一个账号BB操作失败,这样是不行的。
二 在java代码中解决的思路
try{
1.开启事务 : set autocommit=false;
2.AA-100 : 900
---------发生异常了--------
3.BB+100 : 执行不了
commit;
}catch(Exception e){
回滚数据;
}finally{
set autocommit=true;
}
三 mysql中的操作
开启事务 :set autocommit=false;
提交事务 :commit;
回滚事务 : rollback;
第8章 约束
一 约束的说明
当我们向表中插入数据时,对某些字段的数据有所要求,比如不能为null,必须唯一,或者有默认值等等
那么我们就需要对表中的该字段进行约束。
二 有哪些约束?
NOT NULL 非空约束,规定某个字段不能为空
UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)
FOREIGN KEY 外键
DEFAULT 默认值
CHECK 检查约束(mysql不支持)
三 约束分类 :列级约束 vs 表级约束
约束: 列级约束 vs 表级约束
列级约束 :只能约束一列。
表级约束 :可以同时约束多列。
注意 :not null和default只有列级约束。
四 创建表时添加约束
方式一 :在字段后面添加约束,只对一个字段有效
CREATE TABLE stu(
id INT PRIMARY KEY,#主键约束(非空且唯一,只要是表一定有主键约束)
age INT DEFAULT 18,#如果没有给该字段插入数据那么默认插入18
NAME VARCHAR(20) NOT NULL,
sid INT UNIQUE #唯一维束内容不能相同
);
方式二 :如果需要对多个字段进行约束 - primary key,unique ,foreign key
CREATE TABLE stu2(
id INT,
age INT,
NAME VARCHAR(20),
passwd VARCHAR(20),
sid INT,
#添加表级约束--可以同时约束多个列
CONSTRAINT stu_id PRIMARY KEY(id),
CONSTRAINT stu_name_pwd UNIQUE(NAME,passwd)
);
五 表创建成功以后添加约束(DDL)
/*
primary key
添加约束 : alter table 表名 add primary key (字段名)
修改约束 : alter table 表名 modify 字段名 类型 primary key
删除约束 : alter table 表名 drop primary key
unique:
添加约束 : alter table 表名 add unique(字段名)
添加约束 : alter table 表名 add constraint 索引名 unique(字段名)
修改约束 :alter table 表名 modify 字段名 类型 unique
删除约束 :alter table 表名 **drop** index 索引名
*/
#表创建成功以后添加约束
CREATE TABLE s(
id INT,
NAME VARCHAR(20),
age INT
)
#添加约束
ALTER TABLE s ADD PRIMARY KEY(id);
INSERT INTO s(id,NAME,age) VALUES(1,"aa",20);
DROP TABLE s;
ALTER TABLE s MODIFY id INT PRIMARY KEY;
#删除约束 -- 仍然保留不能为null
ALTER TABLE s DROP PRIMARY KEY;
DESC s;
/*
not null
添加约束:alter table 表名 modify 字段名 字段类型 not null
删除约束 :alter table 表名 modify 字段名 字段类型 null
default:
设置默认约束 : alter table tb_name alter 字段名 set default value;
删除默认约束 : alter table tb_name alter 字段名 **drop** default;
foreign key:
添加约束 : ALTER TABLE 表名 ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id)
REFERENCES dept(dept_id);
删除约束 :alter table 表名 **drop** foreign key 索引名
*/
六 外键约束
①格式:
CONSTRAINT 索引名 FOREIGN KEY(字段名)
REFERENCES 主表名(字段名)
②注意 :
创建表:应该先创建主表再创建从表
添加内容:先添加主表再添加从表
删除内容:先删除从表再删除主表
③创建表后添加约束的操作
#删除索引
ALTER TABLE emp
DROP FOREIGN KEY 索引名;
#添加约束
ALTER TABLE emp
ADD [CONSTRAINT 索引名] FOREIGN KEY(dept_id)
REFERENCES dept(dept_id);
第9章 limit
一 使用 :
limit 索引值,数据的条数
#公式:
#(当前页数-1)*每页条数,每页条数
二 注意 :
索引值是从0开始的
三 作用 :
用来分批获取表中的数据(分页)
第10章 SQL优化
一 优化精髓
- 最大化利用索引;
- 尽可能避免全表扫描;
- 减少无效数据的查询;
理解优化原理后,要掌握SQL执行顺序
SQL语句:
1. SELECT
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>
执行顺序:
FROM
<表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN<join, left join, right join...>
<join表> # 指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中,聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>
二 优化策略
优化的前提是针对数据量大的场景小,如果数据量很小,那么等于画蛇添足
2.1 场景一:避免不走索引
1.尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描
SELECT * FROM t WHERE username LIKE '%陈%'
优化方式:尽量在字段后面使用模糊查询
SELECT * FROM t WHERE username LIKE '陈%'
如果需求是要在前面使用模糊查询,
- 使用MySQL内置函数INSTR(str,substr) 来匹配,作用类似于java中的indexOf(),查询字符串出现的角标位置
- 使用FullText全文索引,用match against 检索
- 数据量较大的情况,建议引用ElasticSearch、solr,亿级数据量检索速度秒级
- 当表数据量较少(几千条儿那种),别整花里胡哨的,直接用like ‘%xx%’。
2. 尽量避免使用in 和not in,会导致引擎走全表扫描
SELECT * FROM t WHERE id IN (2,3)
优化方式:如果是连续数值,可以用between代替。如下:
SELECT * FROM t WHERE id between 2 and 3
如果是子查询,可以用exists代替(谓词优化)
-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);
3. 尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描
SELECT * FROM t WHERE id = 1O OR id = 3
优化方式:可以用union代替or
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3
4. 尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描
SELECT * FROM t WHERE score IS NULL
优化方式:可以给字段添加默认值0,对0值进行判断
SELECT * FROM t WHERE score = 0
5.尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。
可以将表达式、函数操作移动到等号右侧
-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9
6. 当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描
SELECT username, age, sex FROM T WHERE 1=1
优化方式:用代码拼装sql时进行判断,没 where 条件就去掉 where,有where条件就加 and。
7. 查询条件不能用 <> 或者 !=
使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。
8. order by 条件要与where中条件一致,否则order by不会利用索引进行排序
-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;
2.2 场景二:SELECT语句其他优化
1. 避免出现select *
优化:只取自己想要的列
2. 避免出现不确定结果的函数
3.多表关联查询时,小表在前,大表在后
在MySQL中,执行 from 后的表关联查询是从左往右执行的(Oracle相反),第一张表会涉及到全表扫描,所以将小表放在前面,先扫小表,扫描快效率较高,在扫描后面的大表,或许只扫描大表的前100行就符合返回条件并return了。 例如:表1有50条数据,表2有30亿条数据;如果全表扫描表2。
4. 使用表的别名
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
5. 用where字句替换HAVING字句
避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。 where和having的区别:where后面不能使用组函数