MySQL 基础中的：基本准备，DDL，DML，DQL，DCL，函数等

MySQL笔记总结

一，基本介绍&操作

1.1 理解

数据就是书，数据库就是图书馆

1.2 安装

1.2.1 MySQL服务安装

具体怎么安后面补起来，考试不考。

但是这个安的 MySQL 是啥要知道：

image-20220612165429868

image-20220612165443069

1.2.2 图形化界面软件安装

Navicat

SQLyog(我在用这个)

后面补起来，考试不考。

1.3 初始化

1.启动服务

启动：net start mysql

关闭：net stop mysql

注意：要用管理员权限打开命令提示符

image-20220612164156181

2. 登录服务

最标准：mysql -h 主机名 -P 端口 -u 用户名 -p密码

最简单： mysql -u root -p密码

注意：

一般来说主机名可以不写默认本地 localhost，

端口可以不写默认 3306，工作中一般会改

注意端口那里是P而不是p

密码可以紧跟-p,中间没空格，也可以不写他会像图中一样让你输入

image-20220612164452378

3. 总结

image-20220612163232896

1.4 其他

1.4.1 数据在数据库中的存储形式

image-20220612165704575

1.4.2 SQL 语句分类

image-20220612165746443

二，DDL_数据定义

DDL (Data Definition Language)

2.1 库

2.1.1 创建数据库

最简单的：create database 数据库名;

标准的：create database 数据库名 character set 指定字符集 collate 校对规则;

image-20220612172454149

image-20220612175212742

2.1.2 删除数据库

唯一一个：drop database 数据库名

2.1.3 查看数据库

显示所有数据库: show databases

显示数据库创建语句：show create database da_name

image-20220612180429725

2.1.4 备份恢复数据库

在dos命令行备份：mysqldump -u root -p -B ds_name > backups_name.sql

在 SQL 命令行恢复：source baskups_name.sql

也可以用笨方法恢复：直接粘贴文件中的所有语句再去执行一遍

注意：其实备份后的文件就是那个数据的从创建开始的所有 SQL 语句；一般存放在 MySQL 自己的 bin 文件夹中。

image-20220612182627992

image-20220612182653419

image-20220612182733764

image-20220612183027014

2.1.5 备份恢复数据库的表

image-20220612183050530

其实我推荐别指定在那个盘，直接用文件名.sql。因为你用那种具体的盘的位置，到时候恢复的时候会报Fail to open file,erro:2就是 MySQL 找不到那个文件，直接这样名字创建，文件就在 MySQL bin 下面就不会出问题。

2.2 表

2.2.1 创建表

image-20220612211242574

演示：

简约：create table table_name( field datetype 。。。);

其实其他的他又默认值的，咱也是这样简洁用的多。

image-20220612212235660

2.2.2 数据类型

2.2.2.1 总览

image-20220612213033364

2.2.2.2 整数

image-20220612213155195

2.2.2.3 小数

image-20220612213703055

2.2.2.3 无符号整数

image-20220612213248083

2.2.2.4 bit

image-20220612213455626

2.2.2.5 小数

image-20220612213909009

2.2.2.6 字符串

image-20220612214613288

image-20220612214625581

2.2.2.7 日期

image-20220612214706641

2.2.3 修改表

image-20220613093805552

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#添加列
ALTER TABLE demo01 
ADD (gender CHAR(1));
#修改列
ALTER TABLE demo01
MODIFY `name` VARCHAR(200);
#删除列
ALTER TABLE demo01
DROP word;
#修改表名
RENAME TABLE demo1 TO demo01;
#显示表结构
DESC demo01;
#修改表的字符集
ALTER TABLE demo01 CHARACTER SET utf8; 
#修改列名
ALTER TABLE demo01 CHANGE `name` `user_name` VARCHAR(63);
#查找
SELECT * FROM demo01;

2.2.4 表的复制

蠕虫复制：

1
2
3
4
5
6
7
8
9
10
-- 演示如何自我复制
-- 1. 先把 emp 表的记录复制到 my_tab01
INSERT INTO my_tab01
(id, `name`, sal, job,deptno)
SELECT empno, ename, sal, job, deptno FROM emp;
-- 2. 自我复制
INSERT INTO my_tab01
SELECT * FROM my_tab01;

SELECT COUNT(*) FROM my_tab01;

如何删除一张表的重复记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/*
思路
(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
(2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
(3) 清除掉 my_tab02 记录
(4) 把 my_tmp 表的记录复制到 my_tab02
(5) drop 掉 临时表 my_tmp
*/
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
create table my_tmp like my_tab02
-- (2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
insert into my_tmp
select distinct * from my_tab02;
-- (3) 清除掉 my_tab02 记录
delete from my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
insert into my_tab02
select * from my_tmp;
-- (5) drop 掉 临时表 my_tmp
drop table my_tmp;

select * from my_tab02;

三，DML_数据操纵

DML (Data Manipulation Language)

3.1 CRUD

C：create 添加

R：read 查找

U：update 更新

D：delete 删除

image-20220613102113955

3.2 Insert

向表中插入数据

image-20220613102404919

1
2
3
4
5
6
7
8
9
10
11
12
#insert 插入数据
#简约方式
INSERT INTO demo01
VALUES(1,'张三','男');
#标准方式
INSERT INTO demo01(id,user_name) 
VALUES(2,'李四');
#一次性插入多条语句
INSERT INTO demo01
VALUES(3,'王五','女'),(4,'托尼','男'),(5,'杰克','女');
#查询表中情况
SELECT * FROM demo01;

image-20220613104028164

补充：

  1. 如果添加是给表中所有字段都加就不用写table_name后的字段名称；
  2. 如果没有赋值，有默认值，可以创建表时指定也可以是各类型的默认值；
  3. 注意插入的数据顺序对应，长度合适；
  4. 如果数据是日期或者字符加上英文单引号；
  5. 某列某行上可以为空，前提是可以为 null；

3.3 update

修改表中数据

image-20220613103739700

细节：image-20220613104159098

代码：

1
2
3
4
5
6
7
8
#更新表中数据
update demo01
set gender = '女'
where id = 4;
#同时更新多行
update demo01
set user_name = '大石头',id = 666
where gender = '男';

image-20220613104533640

3.4 delete

image-20220613104803511

代码：

1
2
3
4
5
#删除一条数据
DELETE FROM demo01
WHERE id = 2;
#删除一个表所有数据
DELETE FROM demo01;

细节：image-20220613105217939

四，DQL_数据查询

DQL (Data Query Language)

4.1 语法

image-20220613105259184

解释:image-20220613105336698

代码演示：

1
2
3
4
5
6
7
#整表查询
SELECT * FROM demo01;
#去重整表查询
SELECT DISTINCT * FROM demo01;
#指定查询
SELECT id,user_name FROM demo01
WHERE gender = '女';

4.2 查询加工

查询中加列计算加as改名

image-20220613110948455

image-20220613110954887

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#演示查询中加计算和改名
CREATE TABLE student
(
`name` VARCHAR(55),
math INT,
chinese INT,
english INT
);
INSERT INTO student 
VALUES('张三',10,10,10),
('李四',20,20,20),
('王五',30,30,30);
SELECT `name`,(math+chinese+english)
 AS total_scores FROM student;

image-20220613111705902

4.3 运算符

image-20220613111820632

1
2
3
运算符在过滤查询中的使用
 select * from student
 where (math+chinese+english) between 40 and 100 and math = 20 ;

4.4 排序查找

image-20220613113748228

代码：

1
2
3
#按照总分降序输出
select *,(math+chinese+english) as total_scores from student
order by total_scores desc;

image-20220613114125636

4.5 加强表查询

  1. mysql 中日期类型可以直接比较，需要注意格式；
  2. %：表示0到多个任意字符 _：表示任意字符；
  3. 详细的要在实战中体现，这里就不在赘述；

4.6 分页查询

image-20220613171323018

4.7 数据分组的总结

image-20220613172216361

4.8 多表查询

就是我们要查询的信息来源于两个表以上，这时候注意我们的条件至少要是 表的个数-1

1
2
3
SELECT ename,sal,dname,emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno

4.9 自连接

在同一张表上的连接查询，将一张表看成两张表

注意给表取个别名，列也可以这样加一个

1
2
3
SELECT worker.ename AS '职员名' , boss.ename AS '上级名'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;

4.10 子查询

就是查询语句嵌套在其他 SQL 语句中

找 smith 同部门的人

1
2
3
4
5
6
7
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
)

找和10号部门的工作相同但是不是 10 号部门的人

1
2
3
4
5
6
7
select ename, job, sal, deptno
from emp
where job in (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) and deptno <> 10

子查询额结果也可以作为临时表使用

子查询中加 all/any：

image-20220613184414579

image-20220613185313496

当然了，你把 all 换成 any 就是查大于部门 30 中的任意一个就符合条件

查询一个表中各个类别最贵的商品：

1
2
3
4
5
6
7
8
select goods_id, ecs_goods.cat_id, goods_name, shop_price
from (
SELECT cat_id , MAX(shop_price) as max_price
FROM ecs_goods
GROUP BY cat_id
) temp , ecs_goods
where temp.cat_id = ecs_goods.cat_id
and temp.max_price = ecs_goods.shop_price

多列子查询

举例：找和 allen 部门和岗位完全相同的所有雇员，除了他自己：

1
2
3
4
5
6
7
SELECT *
FROM emp
WHERE (deptno , job) = (
SELECT deptno , job
FROM emp
WHERE ename = 'ALLEN'
) AND ename != 'ALLEN'

子查询作为临时表：

1
2
3
4
5
6
7
SELECT ename, sal, temp.max_sal, emp.deptno
FROM emp, (
SELECT deptno, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal

4.11 合并查询

下面的有笔误

image-20220613214210603

代码：

1
2
3
4
5
6
7
8
-- union all 就是将两个查询结果合并， 不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union 就是将两个查询结果合并， 会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

五，DCL_数据控制

DCL（Date control Language）

考完回来继续~

5.1 表外连接

image-20220613223248000

1
2
3
4
5
6
7
8
9
-- 使用左外连接实现
SELECT dname, ename, job
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
-- 使用右外连接实现
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno
--

5.2 约束

image-20220613224434578

5.2.1 primary key

image-20220613224541177

1
2
3
4
5
6
CREATE TABLE t20
(id INT ,
`name` VARCHAR(32) ,
email VARCHAR(32),
PRIMARY KEY(id,`name`) -- 在表定义最后写 primary key(列名)复合主键：也就是说两个元素一致才是叫重复
 );

5.2.2 not null (非空)

image-20220613224916183

5.2.3 unique（唯一）

image-20220613225007039

image-20220613225418310

5.2.4 foreign key (外键)

image-20220613230302981

1
2
3
4
5
6
7
8
9
10
11
12
-- 外键演示
-- 创建 主表 my_class
CREATE TABLE my_class (
id INT PRIMARY KEY , -- 班级编号
`name` VARCHAR(32) NOT NULL DEFAULT '');
-- 创建 从表 my_stu
CREATE TABLE my_stu (
id INT PRIMARY KEY , -- 学生编号
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT , -- 学生所在班级的编号
-- 下面指定外键关系
FOREIGN KEY (class_id) REFERENCES my_class(id))

5.2.5 check

image-20220613231721108

1
2
3
4
5
6
7
-- 测试
CREATE TABLE t23 (
id INT PRIMARY KEY,
`name` VARCHAR(32) ,
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);

5.3 自增长

image-20220613231905887

image-20220613234239604

5.4 索引

5.4.1 入门

可以优化查询速度，但是索引也会暂用空间

1
2
3
-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp 表的 empno 列创建索引
CREATE INDEX empno_index ON emp (empno)

注意创建的索引只对有索引的行有用。

5.4.2 索引原理

image-20220614094039715

5.4.3 索引类型

image-20220614094124795

5.4.4 索引使用

image-20220614094310600

5.4.5 适合

image-20220614095344974

5.5 事务

事务就是一种捆绑关系，把几句DML MySQL 语句捆在一起，要么都执行要么就都不执行。比如转账，没用事务你这边钱扣了，她那边没收到，你说麻烦不？所以这个事务的必要性呼之欲出了，它保证了数据的一致性。

image-20220614095827717

5.5.1 锁

image-20220614095932729

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 演示
-- 1. 创建一张测试表
CREATE TABLE t27
( id INT,
`name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION
set autocommit=off;
-- 3. 设置保存点
SAVEPOINT a
-- 执行 dml 操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;
SAVEPOINT b
-- 执行 dml 操作
INSERT INTO t27 VALUES(200, 'jack');
-- 回退到 b
ROLLBACK TO b
-- 继续回退 a
ROLLBACK TO a
-- 如果这样, 表示直接回退到事务开始的状态.
ROLLBACK
COMMIT

5.5.2 细节强调

5.5.2.1 回退事务

image-20220614100601670

5.5.2.2 提交事务

image-20220614100818618

5.5.2.3 细节

image-20220614100844428

5.5.3 隔离级别

5.5.3.1 介绍

image-20220614101257428

5.5.3.2 查看隔离级别

image-20220614101511845

5.5.3.3 事务隔离级别

image-20220614101818685

5.5.3.4 设置事务隔离级别

image-20220614102255276

5.5.4 ACID

image-20220614102542166

5.6 表类型和存储引擎

5.6.1 介绍

image-20220614102712168

5.6.2 引擎一览表

image-20220614102822695

5.6.3 细节

image-20220614102848675

5.6.4 怎么选

image-20220614102936751

5.6.5 修改引擎

alter table table_name engine = 存储引擎

5.7 视图

5.7.1 基本介绍

image-20220614103132421

image-20220614103142087

5.7.2 使用

image-20220614103250362

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 视图的使用
-- 创建一个视图 emp_view01， 只能查询 emp 表的(empno、 ename, job 和 deptno ) 信息
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno, ename, job, deptno FROM emp;
-- 查看视图
DESC emp_view01
SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;

5.7.3 细节讨论

image-20220614104114782

5.7.4 实践

image-20220614104225661

5.8 MySQL 管理

5.8.1 MySQL 用户

image-20220614104450566

5.8.2 创建用户

image-20220614104516446

5.8.3 删除用户

image-20220614104540411

5.8.4 用户改密码

image-20220614104608219

5.8.5 权限

image-20220614104800419

5.8.6 给用户授权

image-20220614105357308

image-20220614105427232

image-20220614105438686

5.8.7 细节

image-20220614105554910

六，函数

6.1 合计/统计函数

6.1.1 Count

image-20220613114418530

1
2
SELECT COUNT(*) FROM student;//4(空值要)
SELECT COUNT(`name`) FROM student;//3(空值不算)

image-20220613114926429

6.1.2 Sum

image-20220613120630608

image-20220613120637601

1
2
#Sum
SELECT SUM(math)/COUNT(*)AS math_avg FROM student;

6.1.3 avg

image-20220613121229060

1
2
#avg
SELECT AVG(math)AS math_avg FROM student;

6.1.4 max/min

image-20220613121557231

代码：

1
2
#max/min
SELECT MAX(math),MIN(english)FROM student;

image-20220613121833069

6.1.5 group by 分组

对列进行分组

image-20220613122020593

解释：比如这里有一个员工表，里面的员工有自己的部门和工资，我们想知道每个部门的平均工资是多少

1
2
3
#查各个部门平均工资
SELECT dept,FORMAT(AVG(salary),2)AS salary_avg FROM worker
GROUP BY dept;

image-20220613125513714

6.1.6 having

对分组后的结果进行再过滤

image-20220613122034221

解释：我们想找出每个部门中（分组了），工资高于10000的 (进一步筛选，用 having)

1
2
3
4
#查那个部门的最高工资=200
SELECT  MAX(salary)AS salary_max,dept  FROM worker
GROUP BY dept
HAVING salary_max = 200;

image-20220613125612572

6.2 字符串函数

image-20220613154524042

dual表 是亚元表，系统表可以用来做测试

6.3 数学函数

image-20220613155314246

6.4 时间日期函数

image-20220613160418958

image-20220613155730860

6.5 加密/系统函数

image-20220613160311320

6.6 控制流程函数

image-20220613161812310

1
2
3
4
5
6
7
#控制函数
SELECT IF(FALSE,'你好','我好')FROM DUAL;
SELECT IFNULL(NULL,'杰哥不要啊')FROM DUAL;
SELECT CASE
WHEN FALSE THEN 'jack'
WHEN FALSE THEN 'tony'
ELSE 'HL' END AS AAA

说在后面，至于 MySQL 编程基础，触发器，储存过程后面搞了，再回来补充