MySQL数据库(基础篇)

1.SQL

1.1 SQL 通用语法和分类

1.1.1 SQL 通用语法

1.1.2 SQL语句分类

1.2 SQL常用操作 DDL

1.2.1数据库操作

1.2.2表操作创建&查询

举例：建立当下数据库

解：

查看该表结构

1.2.3数据类型及案例

char比 varchar 性能更高

案例：

执行：

1.2.4 表操作修改&删除

解：

解：


解：

解：

1.3数据库常用操作 DML

1.3.1 插入

注意：

1.3.2更新和删除

1.4 数据查询—DQL

1.4.1 DQL 基础查询

尽量不要使用select * from 表名；，哪怕使用所有字段也不要用，影响效率

1.4.2 DQL 条件查询

条件

between…and…必须从小到大写

举例：

-- 年龄等于30
select * from employee where age = 30;
-- 年龄小于30
select * from employee where age < 30;
-- 小于等于
select * from employee where age <= 30;
-- 没有身份证
select * from employee where idcard is null or idcard = '';
-- 有身份证
select * from employee where idcard;
select * from employee where idcard is not null;
-- 不等于
select * from employee where age != 30;
-- 年龄在20到30之间
select * from employee where age between 20 and 30;
select * from employee where age >= 20 and age <= 30;
-- 下面语句不报错，但查不到任何信息
select * from employee where age between 30 and 20;
-- 性别为女且年龄小于30
select * from employee where age < 30 and gender = '女';
-- 年龄等于25或30或35
select * from employee where age = 25 or age = 30 or age = 35;
select * from employee where age in (25, 30, 35);
-- 姓名为两个字
select * from employee where name like '__';
-- 身份证最后为X
select * from employee where idcard like '%X';

1.4.3 聚合函数

举例：

-- 统计地址为广东省的员工
select count(id) from employee where workaddress = "广东省";
-- 统计员工的最大年龄
select max(age) from emp

1.4.4 分组查询

-- 根据性别分组，统计男性和女性数量（只显示分组数量，
-- 不显示哪个是男哪个是女）
select count(*) from employee group by gender;
-- 根据性别分组，统计男性和女性数量
select gender, count(*) from employee group by gender;
-- 根据性别分组，统计男性和女性的平均年龄
select gender, avg(age) from employee group by gender;
-- 年龄小于45，并根据工作地址分组
select workaddress, count(*) from employee where 
age < 45 group by workaddress;
-- 年龄小于45，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from employee where 
age < 45 group by workaddress having address_count >= 3;

1.4.5 排序查询

举例：

-- 根据年龄升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 两字段排序，根据年龄升序排序，若年龄相同则按入职时间降序排序
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;

1.4.6 分页查询

-- 查询第一页数据，展示10条
SELECT * FROM employee LIMIT 0, 10;
-- 查询第二页
SELECT * FROM employee LIMIT 10, 10;

1.4.7 DQL 执行顺序

1.5 DCL 数据控制语言

1.5.1 DCL 管理用户

查询用户

USER mysql;
SELECT * FROM user;

创建用户:

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码：

ALTER USER '用户名'@'主机名' IDENTIFIED WITH 
mysql_native_password BY '新密码';

删除用户：

DROP USER '用户名'@'主机名';

举例：

-- 创建用户test，只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test，能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with 
mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';

1.5.2 DCL 权限控制

常用权限

DCL - 权限控制

查询权限：

SHOW GRANTS FOR '用户名'@'主机名';

授予权限：

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限：

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

举例：

2.函数

2.1字符串函数

常用函数：

-- 拼接
SELECT CONCAT('Hello', 'World');
-- 小写
SELECT LOWER('Hello');
-- 大写
SELECT UPPER('Hello');
-- 左填充
SELECT LPAD('01', 5, '-');
-- 右填充
SELECT RPAD('01', 5, '-');
-- 去除空格
SELECT TRIM(' Hello World ');
-- 切片（起始索引为1）
SELECT SUBSTRING('Hello World', 1, 5);

2.2数值函数

常用数值函数

2.3日期函数

常见日期函数

案例：

2.4流程函数

案例1

案例2

3.约束

3.1 概述

分类：

案例：

解：

3.2 外键约束

添加外键

CREATE TABLE 表名(
    字段名 字段类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

– 例子

alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

3.3 外键删除更新行为

删除更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) 
REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;

4.多表查询

4.1多表关系

4.2 多表查询概述

合并查询（笛卡尔积，会展示所有组合结果）

select * from employee, dept;

消除无效笛卡尔积：使用相应的连接表

select * from employee, dept where employee.dept = dept.id;

4.3 内连接

内连接查询的是两张表交集的部分

隐式内连接

SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

显示内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;

显式性能比隐式高

4.4 外连接

左外连接：
查询左表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;

相当于查询表1的所有数据，包含表1和表2交集部分数据

右外连接：
查询右表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;

4.5 自连接

当前表与自身的连接查询，自连接必须使用表别名

语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

自连接查询，可以是内连接查询，也可以是外连接查询

举例：

4.6 联合查询 union，union all

语法：

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...

举例：

注意：

UNION ALL 会有重复结果，UNION 不会
联合查询比使用or效率高，不会使索引失效

4.7 子查询

4.7.1标量子查询

举例：

4.7.2 列子查询

举例：

4.7.3行子查询

举例：

4.7.4表子查询

举例1.

举例2.

5.事务

事务是一组操作的集合，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

5.1提交事务

方式一：

方式二：

基本操作：

-- 1. 查询张三账户余额
select * from account where name = '张三';
-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';
-- 此语句出错后张三钱减少但是李四钱没有增加
模拟sql语句错误
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
-- *****************我是分界线*********************
-- 查看事务提交方式
SELECT @@AUTOCOMMIT;
-- 设置事务提交方式，1为自动提交，0为手动提交，该设置只对当前会话有效
SET @@AUTOCOMMIT = 0;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 设置手动提交后上面代码改为：
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
commit;

方式二：

-- 开启事务
start transaction;
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
-- 提交事务
commit;
-- 回滚事务
rollback;

5.2事务的四大特性ACID

• 原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败
• 一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态
• 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的(磁盘)

5.3并发事务问题

5.4并发事务演示及隔离级别

并发事务隔离级别

√表示在当前隔离级别下该问题会出现

• Serializable 性能最低；
• Read uncommitted 性能最高，数据安全性最差
• 隔离级别越高，性能越差，安全性越高

查看事务隔离级别：
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别：
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };

SESSION 是会话级别，表示只针对当前会话有效，GLOBAL 表示对所有会话有效