MySQL数据库

目录

---

初识数据库

一. 数据库的好处

• 1、持久化数据
• 2、结构化查询
• 3、共享性、安全性
• 4、使用一套完整的DBMS管理，比较规范和简单

二. 数据库中的相关概念描述

DBMS：数据库管理系统，软件
DB：数据库
SQL：结构化查询语言，用于和数据库进行通信

三. MYSQL产品（数据库管理系统）的介绍

• 背景：
  MySQL是由 瑞典的一家AB公司推出的产品，08年被sun收购了，09年sun被oracle收购
• 特点：
  1、开源、免费、成本低
  2、性能高
  3、系统兼容性很好
  4、体积小

四. MYSQL产品的安装

• DBMS分类：
  共享性的文件系统 Access
  基于C/S架构的系统 ：oracle、SQLserver 、MySQL等

• my.ini配置文件：
  baseDir mysql安装路径
  dataDir 数据文件存放路径
  port 端口号
  character_set_server 字符集
  default_character_set字符集
  Engine 存储引擎，默认是InnoDB

五. 启动和停止MySQL服务

方式一：计算机——右击——管理——服务
方式二：命令行(管理员身份)
net start 服务名
net stop 服务名

六. 登录和退出MySQL服务端

• 登录：
  mysql -uroot -p密码
mysql -uroot -p
密码

• 退出：
  exit或ctrl+C

七. SQL语言的分类和语法规范

• 1、sql语言分类：
  DQL:select
  DML:insert 、update、delete
  DDL：create、alter、drop
  DCL：commit、rollback
• 2、语法规范
  
  1. 忽略大小写，建议关键字大写，表名和列名小写
  2. 每句话结尾用分号
  3. 常见注释
     #单行注释
/*多行注释*/
-- 单行注释
  4. 每条sql命令，可以换行写，但关键字不能换行

八. 常见命令

• 查看所有的数据库
  show databases
• 启用某个指定库
  use 库名
• 查看库中所有表
  show tables
show tables from 库名

• 显示某个指定表的结构
  desc 表名

• 查看当前版本

  • 方式一：在进入客户端之前
    mysql --version
mysql -V

  • 方式二：在进入客户端之后
    select version();

---

DQL查询语句

一. 基础查询

• 1、查询常量
  select 常量值;
• 2、查询表达式
  select 14*2；
• 3、查询函数
  select database();
• 4、查询某个表的指定字段
  select 字段名 from 表名;
• 5、查询多个字段
  select 字段名,字段名 from 表名;
• 6、起别名
  
  • 方式一：使用as
    select 字段名 as 别名1,字段名 as 别名2 from 表名;
  • 方式二：使用空格
    select 字段名 别名1,字段名 别名2 from 表名;
    注意：别名可以用双引号引起来，尤其是别名有特殊符号

二. 条件查询

• 1、按条件表达式
  条件运算符：> < >= <= = <=> <> !=
• 2、按逻辑表达式
  逻辑运算符：
and &&
or ||
not ！

• 3、模糊查询

  • ①like
    一般和通配符搭配使用
    %: 任意多个字符，包含0个
    _: 任意单个字符
    案例：查询第三个字符为a的员工信息
    select * from employees where last_name like '__a%';

  • ②between and
    在…之间
    包含临界值

  • ③in
    在…列表中

  • ④ is null/is not null
    select * from employees where manager_id is null;

三. 排序查询

• 语法：order by 排序的字段 asc|desc
  注意：
  1、order by一般放在查询的最后，除limit分页外
  2、asc 升序，desc 降序，默认升序
  3、可以按单个字段、多个字段、别名、函数、表达式排序

四. 分组查询

• 一）分组函数
  概念：分组函数又称为聚合函数，或统计函数，或组函数
  分类：
  sum求和
  avg平均值
  count计数
  max最大值
  min最小值

特点：
1. sum和avg只支持数值型，其他的支持任何类型
2. 分组函数都忽略null
3. 和分组函数一起查询的字段必须为分组的字段
4. count(*) 计算结果集的行数，效率较高
count(1) 计算结果集的行数
示例：
select max(salary) from employees;

• 二）分组查询
  语法：
  select 分组函数,分组的字段
  from 表1,…
  where 分组前筛选
  group by 分组的字段
  having 分组后筛选
  order by 排序的字段或表达式

特点：
1、可以按多个字段分组，多个字段之间没有顺序要求，用逗号隔开
2、筛选分类：

位置	关键字	针对的基准表
分组前筛选	group by前面	where 原始表
分组后筛选	group by后面	having 分组后的结果集

where——group by——having
3、分组可以和多表连接、排序搭配

五. 连接查询

概念：当要查询的字段来自于多张表，则需要使用连接查询
笛卡尔乘积：
表1：m行，表2：n行，结果集：m*n行
发生原因：没有加连接条件，则所有行都实现了完全匹配
解决方法：添加上连接条件

• 一）传统模式下的连接
  等值连接：
  语法：
  select last_name,department_name from employees e,departments d where e.department_id = d.department_id

特点：
1、可以实现n表连接，至少需要n-1个连接条件
2、多个表没有顺序要求，没有主从表之分
3、查询结果 为 多个表的交集部分
4、可以加筛选、分组、排序
5、一般为表起别名，提高阅读性和性能
综合案例：查询部门编号>50的领导的名称和部门编号、所在城市
SELECT last_name,d.department_id,city
FROM departments d,employees e,locations l
WHERE d.`manager_id`=e.`employee_id`
AND d.`location_id`=l.`location_id`
and d.department_id>50
order by d.department_id;

非等值连接
综合案例：查询每个员工的工资级别
SELECT grade_level,e.`salary`,e.`employee_id`
FROM job_grades j,employees e
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

• 二）join连接
  内连接：
  语法：
  select last_name,department_name from employees e
[inner] join departments d on e.department_id = d.department_id;

  特点：
  1.和等值连接的原理以及效果一样
  2.可以加筛选条件，只是用where连接
  join连接，将筛选条件和连接条件分离，提高阅读性
  3.其他特点和等值连接一样，比如不分主从表以及可以添加筛选、分组、排序

  外连接：
  左外：left outer join ...on
  右外: right outer join ... on
  全外：full outer join ...on mysql 语法不支持
  特点：
  1、外连接查询结果=内连接的结果+主表有从表没有的记录
  2、左连接，left join左边的是主表。右连接，right join 右边是主表
  3、一般应用在查询主表有从表没有的记录
  4、小技巧：要看查询的字段来自于哪个表，则哪个表就是主表
  案例：
  select g.id
from student s
right outer join grade g on s.gradeid=g.id
where s.id is null;

• 三）自连接
  案例：查询 员工名和直接上级的名

  • 方式一：使用等值连接
    select e.last_name,m.last_name
from employees e,employees m
where e.manager_id = m.employee_id;
  • 方式二：使用内连接
    select e.last_name,m.last_name
from employees e
inner join employees m
on e.manager_id = m.employee_id;

六. 子查询

概念：一个查询语句内部又嵌套了select语句，被嵌套的select语句称为子查询或内查询，外面的查询语句称为外查询或主查询
特点：
1、子查询一般放在小括号内
2、子查询一般放在条件右侧
3、子查询优先于外查询执行
4、子查询根据查询的结果行数不同，分为单行子查询和多行子查询
单行子查询，查询结果只有一行，使用单行操作符：> < >= <= <> =
多行子查询，查询结果多行，使用多行操作符：any、some、all、in、not in等

七. 分页查询

语法：
select 字段，...
from 表1，表2，...
【where 条件】
【group by 分组的字段】
【having 条件】
【order by 排序的字段】
limit 子句
特点：

① limit 条目数 ：查询指定条目数的记录，默认从第一条开始
limit 起始索引，条目数：查询指定条目数的记录，从指定的起始索引开始，索引从0开始
②limit子句一定放在查询最后

③ 假设要显示 第page页的记录，每一页显示 pageSize条目
select * from 表 limit (page-1)*pageSize,pageSize;

page	起始索引	条目数
1	0	10
2	10	10
3	20	10

八. 常见的单行函数

• 1.字符函数
  length:字节长度
  instr:子串第一次出现的索引，如果找不到返回0.默认索引索引从1开始
  substr:截取子串
  trim:去前后指定字符
  concat:拼接字符串
  lpad:用指定字符左填充指定长度，如果不够就从右边截取
  rpad:用指定字符右填充指定长度，如果不够就从右边截取
  upper：变大写
  lower:变小写

• 2、数学函数
  ceil：向上取整
  floor：向下取整
  round:四舍五入指定精度
  rand:随机数
  mod:取余 select mod(-10,-3); -10-(-10/-3)*（-3）
  truncate：截断
  3、日期函数
  now:获取当前日期+时间
  curdate:获取当前日期，不包含时间
  curtime:获取当前时间，不包含日期
  datediff：两个日期差（天数）
  year:获取年
  month：获取月
  day：获取日

---

DML操纵语言

一. 数据的插入

语法：insert into 表名(字段，字段) values(值1，值2);
- 特点：
- 1、值和字段要一一对应
- 2、如果是字符型或日期型，值要用单引号引起来；如果是数值型，则不用单引号
- 3、字段和值的个数必须一致
- 4、不能为null的字段必须插入值；
可以为null的字段可以不用插入值，但必须遵循以下写法：
① 字段和值都不写
② 字段写上，值用null代替
- 5、字段列表可以不写，默认是所有字段
insert into 表名 values(值，值);

• 分类：

  • 插入单行有以下两种方式：
    方式一：insert into 表（字段，字段） values(值，值);
    方式二：insert into 表 set 字段=值，字段=值;

  • 插入多行有以下两种方式：
    方式一:
    insert into 表（字段，字段） values(值，值),(值，值);

    方式二：
    insert into 表（字段，字段）
select 值1，值2 union 【all】
select 值1，值2

二. 数据的更新

• 方式一：单表的更新
  语法：update 表名 set 字段=值,字段=值 【where 条件】

• 方式二：多表的更新
  语法：update 表名1 别名1，表名2 别名2
set 字段=值，字段=值
where 连接条件 and 筛选条件

三. 数据的删除

• 方式一：使用delete
  语法：
  单表删除：
  delete from 表 【where 条件 】
  多表删除：
  delete a,b from 表1 a,表2 b where 连接条件 and 筛选条件

• 方式二：使用truncate
  语法：
  truncate table 表名;

两种删除的区别：【面试题】

1.当删除有标识列的表时，truncate删除后，再插入标识列从1开始，而delete从断点处开始
2.truncate删除后事务不能回滚，delete删除后事务可以回滚
3.truncate效率高
4.truncate不能删除多个表
5.truncate不能加where条件

---

DLL定义语言

一. 库的管理

• 1、创建库
  create database [if not exists] 库名;
• 2、删除库
  drop database [if exists] 库名;

通用的写法：
drop database if exists 旧库名;
create database 新库名;

二. 表的管理

• 1、创建表
  create table [if not exists] 表名(
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
....
)
• 2、修改表
  ①添加列
  alter table 表名 add column 列名 列类型 【列级约束】;
  ②删除列
  alter table 表名 drop column 列名;
  ③修改列的类型或列级约束
  alter table 表名 modify column 列名 新类型 【新的约束】;
  ④修改列名
  alter table 表名 change column 列名 新列名 类型;
  ⑤修改表名
  alter table 表名 rename 【to】 新表名

• 3、删除表
  drop table 【if exists】 表名

  通用的建表：
  drop table if exists 表;
create table 表()

• 4、复制表
  只复制结构：
  create table 表名 like 旧表

  复制结构+数据
  create table 表名 select 字段,... from 旧表 [where 条件];

三. 数据类型

数值型
字符型
日期型
二进制Blob

• 一) 数值型
  
  • 1、整数
    ①分类：
    tinyint（1）、smallint（2）、mediumint（3）、int/integer（4）、bigint（8）
    ②如果超出了整型的范围，则报警告，存储的是临界值
    ③默认是有符号整型，如果想设置成无符号则需要添加 unsigned
    ④可以为整型设置宽度，写法：int(10)，代表意思是：显示宽度为10，但一般没效果，除非搭配zerofill，用0填充
  • 2、浮点型或定点型
    
    • ①分类：
      浮点型：
      float(M,D)
      double(M,D)
      定点型：
      decimal(M,D)
      dec(M,D)
    • ②M：整数部位+小数部位的 总个数
      D:小数部位的个数
      M和D都可以省略，但浮点型省略M和D，则精度为实际插入数值的精度
      定点型省略M和D，则精度为M=10，D=0
    • ③如果插入数值超过精度范围，则报警告，实际插入的为临界值
    • ④定点型的精度更高，一般用于货币运算等
• 二） 字符型

• ①分类
  char(M)、varchar(M)、text、
  binary(M)、varbinary(M)、enum、set

• ②char和varchar的区别

类型	M	意思	节省空间	效率
char	最多字符个数，可以省略M,默认为1	固定长度字符	不节省	高
varchar	最多字符个数，不可以省略M	可变长度字符	节省	低

- ③text用于存储较大文本

• 三） 日期型

  • ①分类
    date、datetime、year、time、timestamp
  • ②各自存储
    date：日期
    datetime：日期+时间
    year：年
    time：时间
    timestamp：日期+时间
  • ③datetime和timestamp的区别

  类型	字节	存储范围	是否受时区、sqlmode的影响
  datetime	8	1000-9999	不受
  timestamp	4	1970-2038	受

• 四） 常见约束

  • 1、六大约束
    NOT NULL非空
    PRIMARY KEY 主键
    DEFAULT默认
    FOREIGN KEY外键
    UNIQUE唯一
    CHECK检查

    ①主键和唯一的区别

    类型	功能	是否可以为空	一个表允许几个	是否支持组合
    主键	该字段的值不能重复	不可以	最多一个	是
    唯一	同上	可以	可以多个	是

    ②外键的特点
    a、设置主从表的关系，从表中添加外键
    b、从表的外键列的值必须来自于主表的相应列的值
    c、主表的相应列 必须是主键
    d、主表和从表的相应列的类型、表达的意思必须一致、名字无要求
    c、插入时，先插入主表
    删除时，先删除从表

  • 2、创建表时添加约束
    语法：
    create table 表名(
字段名 字段类型 列级约束，
字段名 字段类型 列级约束，
constraint 约束名 约束类型（字段）#表级约束
)

    通用案例：
    create table 表名(
id int not null primary key,#主键+非空
name varchar(20) unique,#唯一
sex char default ‘男’,#默认
age int check (age between 0 and 120),#检查
gradeid int,
foreign key(gradeid) references grade(id) #外键
)

    总结：

    类型	位置	是否可以起约束名	支持哪些约束
    列级约束	列的后面，用于约束对应的列	不可以	除了外键
    表级约束	表的最后，用于约束小括号内的列	可以，通过constraint关键字	除了非空和默认

  • 3、修改表时添加约束
    语法：
    表级约束的方式：
    alter table 表名 add 【constraint 约束名】 约束类型（字段）;
    列级约束的方式：
    alter table 表名 modify column 字段名 字段类型 字段约束;

  • 4、修改表时删除约束
    语法：
    表级约束的方式：
    alter table 表名 drop 约束类型 约束名;
    案例：
    alter table 表名 drop primary key;
alter table 表名 drop index 约束名;
alter table 表名 drop foreign key 约束名;

    列级约束的方式：
    alter table 表名 modify column 字段名 字段类型 字段约束;

---

DCL控制语言

一. 事务的特点（ACID）

原子性（Atomicity）： 一个事务不可再分，要么同时执行，要么同时失败
一致性（Consistency）：一个事务执行完，数据从一个状态切换到另一个状态，数据是完整的
隔离性（Isolation）：一个事务不受另一个事务的干扰
持久性（Durability）：一个事务一旦提交，则永久的持久化本地，除非其他事务对其进行更新

二. 开启事务 ★

set autocommit=0;#取消自动提交的功能
start transaction;#开启新事务
…..
commit;#提交事务
rollback;#回滚事务

三. 当多个事务同时去操作同一个数据库的相同数据时，容易出现并发问题

脏读：一个事务读取了另一个事务未提交的数据
不可重复读：一个事务执行时，两次读取的数据不一样
幻读：一个事务读取了另一个事务未提交的数据（针对于插入）

解决方式：设置隔离级别
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化

语法：
查看隔离级别：select @@tx_isolation;
设置隔离级别：
set @@tx_isolation ='read-uncommitted';
set transaction isolation level read uncommitted;#对当前会话窗口有效
set global transaction isolation level read uncommitted;#对全局会话窗口有效

---

其他高级语言

视图

一. 视图的概念

视图的数据来自于表，视图的数据动态生成的，使用方式和表一样

类型	存储	数据的生成	使用
视图	存储了语法逻辑，没有存储数据，没有占物理空间	调用视图时，动态生成	增删改查
表	存储数据，占物理空间	一直有	增删改查

二. 视图的好处

• 1、将复杂的查询语句提取到视图，调用方便，而且效率也高
• 2、与表独立开来，比较安全

三. 创建视图

create view 视图名
as
查询语句;

四. 修改视图

方式一：
create or replace view 视图名
as
查询语句;

方式二：
alter view 视图名
as
查询语句;

五. 删除视图

drop view 视图名1，视图名2,....；

六. 视图的使用（和表的使用语法相同）

select * from 视图;
insert into 视图 values(值);
update 视图 set 字段=值 where 条件
delete from 视图 where 条件

desc 视图名
注意：具备以下关键字，不能增删改视图
1、分组、distinct、having、group by
2、select 中包含子查询
3、join关键字
4、常量视图
5、from一个不能更新的视图
6、where子查询中用到了from 后的表

储存过程

一. 存储过程和函数的好处

• 1、提高了sql的重用性，调用来讲，语法简洁
• 2、提高了效率

二. 创建存储过程

语法：
create procedure 存储过程名(in/out/inout 参数名 参数类型，...)
begin
存储过程体
end

1、in 仅仅做输入
out 仅仅做输出
inout 输入+输出
一个存储过程可以有多个输入、多个输出、多个输入和输出

三. 调用存储过程

语法：
call 存储过程名（参数列表）
注意：
1.out类型的参数，需要加变量，语法为 call 存储过程名（@变量名）;
2.inout类型的参数，需要加赋好值的变量，
语法为：
set @变量名=值;
call 存储过程名（@变量名）;
四、删除存储过程
drop procedure 存储过程

PLSQL

一. 变量

• 1、局部变量
  语法：
  declare 变量名 变量类型 default 值;
  赋值：
  方式一：
  set 变量名=值;
select 变量名:=值;
set 变量名:=值;
  方式二：
  select 字段|表达式|常量 into 变量名 [from 表];
  应用在：begin end中

• 2、用户变量
  语法：
  set @变量名=值;
select @变量名:=值;
set @变量名:=值;
  赋值：
  方式一：
  set @变量名=值;
  select @变量名:=值;
  set @变量名:=值;
  方式二：
  select 字段|表达式|常量 into @变量名 [from 表];
  应用在：select 后面或begin end中

• 3、全局变量

二. 分支

• 1、if函数
  语法：if(条件表达式,值1，值2)
  应用在：select 后面或begin end中

• 2、case语句
  语法：
  1》
  case 字段或表达式
when 值1 then 语句1或新值1;
when 值1 then 语句1或新值1;
。。。
else 语句n或值n
end;

  2》
  case
when 条件1 then 语句1或新值1;
when 条件2 then 语句1或新值1;
。。。
else 语句n或值n
end;
  应用在：select后面或begin end 里面

• 3、if elseif

  语法：
  if 条件1 then 语句1;
elseif 条件2 then 语句2;
else 语句n;
end if;
  应用在：begin end里面

三. 循环

1.while
语法：
【标签:】while 循环条件 do
循环体
end while 【标签】;

2.repeat
语法：
【标签:】repeat
循环体
until 循环结束条件 end repeat 【标签】;

函数

一. 函数的特点和好处

特点：类似于java中的方法，将具体功能的实现细节加以隐藏和封装，对外暴露了一个函数名，供外界调用
好处：
1、提高代码的重用性
2、提高效率

二. 创建函数

语法：
create function 函数名（参数名 参数类型,参数名 参数类型,...） returns 返回类型
begin
函数体
end

三. 调用函数

select 函数名（实参列表）;

四. 删除函数

drop function 函数名;

五. 存储过程和函数的区别

类型	关键字	返回值	调用语法	应用场景
存储过程	procedure	可以有0个或多个	call	一般用于执行较复杂的过程体，更新、创建等
函数	function	只能有一个返回值	select	一般用于查询单个值并返回