8 MySQL

第一节、安装篇

1.1 引导安装 (小白)

1.1.1 安装包 https://www.mysql.com/downloads/

1.1.2 检查本是否存在安装

如何干净的卸载以前的版本 ？ 一定要标准卸载，就是控制面板->程序卸载。 然后删除残余文件。
卸载

这个图只是举例，找到MySQLxxxx相关的卸载。

如果控制面板中没有，MySQL 而 服务列表中有，则需要删除服务

删除服务使用 CMD 管理员方式运行， 执行删除服务 命令 sc delete 服务名。如 sc delete MySQL57

检查：
C:\Program Files 下 存在 MySQL 将其删除
C:\ProgramData 下 存在 MySQL 将其删除

1.1.3 提供必要环境( VC++运行库 ) 可选

1.1.4 安装 MySQL（管理员身份运行）

启动安装

1.2 离线安装(高手)

1.2.1 准备安装包

把内容解压到合适的目录(不要包含中文的路径下)

配置环境变量
MYSQL_HOME = E:/dev/mysql
Path: %MYSQL_HOME%/bin

配置的目的是可以在cmd中使用mysql bin中的命令

1.2.2 编写配置文件 my.ini

[client] 
port = 3306 
default-character-set = UTF8MB4 

[mysqld] 
port = 3306 
character-set-server = UTF8MB4
basedir=E:/dev/mysql
datadir=E:/dev/mysql/data
group_concat_max_len=20000

[WinMySQLAdmin] 
E:/dev/mysql/bin/mysqld.exe
1
2
3
4
5
6
7
8
9
10
11
12
13

1.2.3 执行初始化

管理员身份运行CMD,进入bin文件夹

E:\dev\mysql\bin> mysqld --initialize --console
2021-04-26T09:03:16.896073Z 0 [System] [MY-013169] [Server] E:\dev\mysql\bin\mysqld.exe (mysqld 8.0.24) initializing of
server in progress as process 8396
2021-04-26T09:03:16.906713Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-04-26T09:03:17.775180Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-04-26T09:03:20.148516Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: dxr8F9xpQx,h 临时密码
1
2
3
4
5
6

1.2.4 安装MySQL服务

E:\dev\mysql\bin>mysqld --install MySQL
Service successfully installed.
1
2

1.2.5 启动服务

E:\dev\mysql\bin>net start MySQL
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
1
2
3

1.2.6 连接修改密码

E:\dev\mysql\bin>mysql -uroot -pdxr8F9xpQx,h
mysqld: Can not perform keyring migration : Invalid --keyring-migration-source option.
2021-04-26T09:06:38.918463Z 0 [Warning] [MY-000057] [Server] Using a password on the command line interface can be insecure.
2021-04-26T09:06:39.019469Z 0 [System] [MY-010116] [Server] E:\dev\mysql\bin\mysqld.exe (mysqld 8.0.24) starting as process 5152
2021-04-26T09:06:39.023820Z 0 [ERROR] [MY-011084] [Server] Keyring migration failed.
2021-04-26T09:06:39.024334Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-04-26T09:06:39.024457Z 0 [System] [MY-010910] [Server] E:\dev\mysql\bin\mysqld.exe: Shutdown complete (mysqld 8.0.24)  MySQL Community Server - G
1
2
3
4
5
6
7

修改密码

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
1
2

1.2.7 测试登录

C:\Users\ADMIN>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.24 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  DBA
1
2
3
4
5
6
7
8
9
10
11
12
13

1.3 客户端(可视化)

Navicat(收费)

SQLyoung(第三方免费)

Workbench（官方软件）

第二节、MySQL 介绍

2.1 什么是数据库

所谓数据库就是存储数据的仓库，数据存储从时效性看，分为临时存储，永久存储。程序中的数据都是临时的，随着程序的关闭就没啦，文件存储是一种永久保存的方式，只要文件存在就一直保存。对于数据的永久保存也叫持久化。

数据库存储技术 就是一种解决企业数据存储方案。

2.2 数据库系统分类

2.2 关系型数据库管理系统(RDBMS)

用表来的形式来存储数据，表就是关系表，表本身就是关系结构。
常见的关系型数据

1. **MySQL **
2. SQL Server
3. Oracle
   

2.3 非关系型数据库(No-SQL)

1. Redis
2. MangoDB
3. HBase
   

2.4 MySQL数据库架构

2.4.1 MySQL 体系(C/S架构)

2.4.2 MySQL 工作流程

1. MySQL 服务启动并监听3306端口
2. 客户端连接MySQL建立连接通道。
3. 客服端向服务端发送 SQL语句
4. 服务端 验证 解析SQL 交给存储引擎处理
5. 服务端保存数据或者处理结果。

客户端 与 服务端 交流的语言就是SQL.

第三节、SQL 语言（针对所有关系型数据库的结构查询）

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。增删改查

3.1 SQL 语言标准

1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86
1989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89
1992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）
1999年，ISO/IEC 9075:1999，SQL:1999（SQL3）
2003年，ISO/IEC 9075:2003，SQL:2003
2008年，ISO/IEC 9075:2008，SQL:2008
2011年，ISO/IEC 9075:2011，SQL:2011

3.2 SQL 方言（）

然制定了SQL的标准，但是SQL标准不断演进的本质导致了各个厂商和数据库产品之间产生了很多的SQL方言。由于在ANSI委员会创建标准之前，特定于某个数据库厂商的用户社区会要求在数据库中添加某些功能，因此各个数据库厂商的SQL语言常常演化为某种SQL方言。偶尔，出于与其他技术竞争的压力，某些学术或研究机构也会引入一些新的特性。

不同数据库 差异化的语法，就是方言。

3.3 SQL 语言分类

结构化查询语言包含6个部分的细分。
1、数据查询语言（DQL）：Data Query Language
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其它类型的SQL语句一起使用。

2**、数据操作语言（DML）**：Data Manipulation Language
其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。

3、数据定义语言（DDL）：Data Definition Language
其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。

4、数据控制语言（DCL）：Data Control Language
它的语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

5、事务控制语言（TCL）：Transation Control Language
它的语句能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。

6、指针控制语言（CCL）：Cursor Control Language
它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

连接
mysql -uroot -p123456 

体验
创建数据库 
create database 数据名字
eg: create database  xjc_db;

打开数据库
use 数据库名
eg： use xjc_db;

创建表
create table 表名( 列名 类型,..... );
eg: create table jishi( id int , name varchar(30) ,  price double );

添加数据
insert into 表名  values(值,...)
eg: insert into jishi values( 8, '小美', 998.0 );

查询数据
select * from  表名
eg: select * from jishi;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

第四节、数据查询[重点]

4.1 准备数据

-- 创建雇员表
create table  emp(
	empno  int,
	ename  varchar(50),
	job  varchar(50),
	mgr  int,
	hiredate  date,
	sal  decimal(7,2),
	comm  decimal(7,2),
	deptno  int
) ;
insert into emp values(7369,'朱元璋','职员',7902,'1980-12-17',800,0.00,20);
insert into emp values(7499,'周星驰','销售员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'范冰冰','销售员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'赵四','经理',7839,'1981-04-02',2975,NULL,20);
insert into emp values(7654,'李冰冰','销售员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'李小龙','经理',7839,'1981-05-01',2850,NULL,30);
insert into emp values(7782,'江小白','经理',7839,'1981-06-09',2450,NULL,10);
insert into emp values(7788,'猪刚鬣','分析师',7566,'1987-04-19',3000,NULL,20);
insert into emp values(7839,'王思聪','总裁',NULL,'1981-11-17',5000,NULL,10);
insert into emp values(7844,'刘大锤','销售员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'令狐冲','职员',7788,'1987-05-23',1100,NULL,20);
insert into emp values(7900,'林平之','职员',7698,'1981-12-03',950,NULL,30);
insert into emp values(7902,'雄霸','分析师',7566,'1981-12-03',3000,NULL,20);
insert into emp values(7934,'吴奇隆','职员',7782,'1982-01-23',1300,NULL,10);

-- 创建部门表
create table dept(
	deptno  int,
	dname   varchar(14),
	loc     varchar(13)
);
-- 添加数据
insert into dept values(10, '财务部', 'NEW YORK');
insert into dept values(20, '市场部', 'DALLAS');
insert into dept values(30, '销售部', 'CHICAGO');
insert into dept values(40, '运营部', 'BOSTON');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

4.2 基础查询

SELECT … FROME _ _table_name

4.2.1 查询全部列

select * from emp;
1

4.2.2 查询部分列[投影查询]

select empno , ename, job, sal  from emp;
1

4.2.3 去重

select distinct cloumn_1,...,column_N from tbale_name
1

如果是多列，把多列的组合视为判断单元，看是否重复。

4.2.4 列运算

select column_1 + value ,... from table_name;
1

列元素不会影响 源数据值，只会在输出中体现。支持 + - * / % 运算符。

4.3 条件查询

SELECT … FROME _ __table_name [ _**WHERE **条件 ]

这里条件需要根据自己的筛选条件编写，通常使用运算符过滤和短语常见的运算符有 = != > >= < <= is null is not null between...and... 以及 and or not like in(...)

4.3.1 等值判断( = )

select  * from   emp   where empno = 7788 ;   -- 查询员工编号为 7788
select  * from   emp   where job = '销售员';   -- 查询职位 是销售的
1
2

4.3.2 不值判断( > >= < <= != )

select  * from   emp   where  sal >= 2000;    -- 查询薪资 超过 2000的
select  * from   emp   where  job != '经理';  -- 查询职位 不是 经理的
1
2

4.3.3 逻辑判断( and or not )

select  * from   emp   where  job='销售员'  and  sal>1500 ; -- 查询 职位是销售  薪资超过 1500的
select  * from   emp   where  job= '销售员' or  job='经理'; -- 查询 职位是销售或者 经理的
select  * from   emp   where  not job = '职员'              -- 查询 职位是 职员的
1
2
3

4.3.4 区间判断( between and )

select  * from   emp   where sal between  1500  and  3000; -- 查询 薪资在  1500 到 3000的
select  * from   emp   where sal>=1500 and sal <=3000;     -- 同上
1
2

4.3.5 空判断( is null 、is not null )

select  * from   emp   where comm is null  ;   -- 查询 奖金为空的
select  * from   emp   where comm is not null; -- 查询奖金 不为空的
1
2

4.3.6 枚举判断( in )

select  * from   emp   where deptno in( 10,30,80 ); -- 查询部门编号是  10 或 30 或  80 
select  * from   emp   where deptno =10 or deptno=30 or deptno =80; -- 同上 
1
2

in 里面的值 是一种或者关系

4.3.7 模糊查询( like )

select * from    emp   where ename like '李%';   -- 查询李开头的
select * from    emp   where ename like '%小%';  -- 查询包含小的
select * from    emp   where ename like '%冰'    -- 查询冰 结尾的
select * from    emp   where ename like '李_';   -- 查询李开头且 只有两个字的名字
1
2
3
4

% 一个或者多个 任意字符
_ 一个字符 任意字符

4.4 排序

**SELECT **… **FROM ** _ table_name _ ** [ WHERE **条件 ] ** [ORDER BY **列 asc | desc ]

4.4.1 升序(默认)

select * from    emp   order by  sal asc ;  -- 全部员工安装薪资 升序
select * from    emp   where job='销售员' order by sal desc;    -- 查询销售员 按照 薪资降序
1
2

4.4.2 降序

select * from    emp   order by  sal desc ;  -- 全部员工安装薪资 降序

1
2

4.4.3 多列排序

select * from    emp order by  sal desc , empno desc ; -- 多列排序
1
2

4.5 分组

**SELECT **… **FROM _table_name _[ WHERE _条件 _] [GROUP BY 列 _ [HAVING 条件 ] _] [ ORDER BY **列 asc | desc ]_ _

分组函数

• count( * ) 记录数
• min( 列 ) 最小
• max( 列 ) 最大
• avg( 列 ) 平均
• sum(列) 求和

-- 查询各职位 人数  最低薪资  最高薪资  平局薪资  总工资和 
select  job ,  count(*) , min(sal) ,max(sal) , avg(sal) , sum(sal)   from    emp   group by job   
-- 查询各部门 人数 平局薪资 
select  deptno, count(*) ,avg(sal)  from  emp group by deptno;
-- 查询各个部门 各个岗位 人数 （ 多列分组 ）
select  deptno,job, count(*)  from emp GROUP BY deptno,job;  
-- 查询各职位 人数  最低薪资  最高薪资  平局薪资  总工资和  只显示 人数小于3 的统计信息。
select  job ,  count(*) , min(sal) ,max(sal) , avg(sal) , sum(sal)   from    emp   group by job  having  count(*)<3 ; 
1
2
3
4
5
6
7
8

HAVING 从句 分组过滤, 第二次过虑。注意：分组SQL的 select 后只能出现 分组列 和聚合函数。

4.6 分页

**SELECT **… **FROM _table_name _[ WHERE _条件 _] [GROUP BY 列 _ [HAVING 条件 ] _] [ ORDER BY 列 asc | desc ]_ _ [ Limit ** [ offset , ] length ]

select * from  emp  limit 5,6; -- 跳过5条件取6条
select * from  emp  limit 3;   -- 一个值 默认跳过0条
1
2

### 4.7 查询小结 #### 4.8 查询SQL编写顺序 **SELECT **列名 **FROM** 表名 **WHERE **条件 **GROUP BY **分组列 **HAVING** 过滤 **ORDER BY** 排序列(asc|desc) **LIMIT **起始行,总条数 #### 4.9 查询SQL执行顺序

1. FROM: 指定数据来源
2. **WHERE: **对查询源数据过滤
3. **GROUP BY: **分组
4. **HAVING: **对分组后的数据二次过滤
5. **SELECT: **查询各字段值
6. **ORDER BY: **排序
7. **LIMIT: **限定结果
   

4.8 复杂查询

复杂查询指的是，通常不可一次查询或者需要同时从多张表中查询数据。

4.8.1 子查询

子查询也叫分步查询 或者 嵌套查询，通常是把一个查询结果作为另一个查询的条件或者数据源。子查询写在( ) 中， 具体来说分为以下几种情况。

• 单行子查询

  子查询结果为 单行单列的一个值，作为外层查询的条件，通常由 **=  >  >=   <  <= **  引发的都是单行子查询
  1

-- 示例1
-- 查询跟 猪刚鬣 同部门的员工
1. 查询猪刚鬣的部门号
   select  deptno   from emp where ename = '猪刚鬣'; 
2. 根据这个部门号查询员工
   select * from emp where deptno = 20;
3. 合成：
select * from emp where 
deptno = (select  deptno   from emp where ename = '猪刚鬣')   and ename !='猪刚鬣' ;

-- 示例2
-- 查询 高于销售平均工资的员工
select  *  from emp   where  sal > (   select avg(sal)  from emp where job='销售员'  ); 
1
2
3
4
5
6
7
8
9
10
11
12
13

• 多行子查询

   查询结果为  多行单列的情况，视为一个集合，可以使用 **in **处理 或者  **any  **和 **all **处理。
  1

-- 查询
-- 查询工资 大于各部门平均工资的员工
select * from emp where sal = any
       (select   max(sal)   from  emp group by deptno); 

-- 查询从事高薪岗位 （薪资高于2000的岗位）
select * from emp where job in( select distinct job from emp where sal >= 2000 )

1
2
3
4
5
6
7
8

• 多列子查询

  查询结果为  多行多列的情况，此时可以视为一张表，叫虚表（临时表），可以当做一张表使用。
  1

-- 查询 20号部门中的职员
select * from 
(select ename, job ,sal from emp where deptno = 20)   temp   where job = '职员'
1
2
3

• 相关子查询

  内层查询可以使用外层的数据。
  1

-- 示例
-- 查询各部门工资最高的员工是谁？
select * from emp a where sal = (  select  max(sal) from emp  where deptno = a.deptno  ) ;
1
2
3

4.8.2 查询结果合并

如果多个查询语句的查询结果结构相同(列数，类型)，可以合并成一个结果集

• **UNION **

SELECT ... FROM  TABLE_NAME  [其他从句]
UNION 
SELECT ... FROM  TABLE_NAME  [其他从句] ;

-- 联合结果
select ename,job from emp where sal>1000
union 
select sname,role from students;
1
2
3
4
5
6
7
8

自动去掉重复数据行

• UNION ALL

SELECT ... FROM  TABLE_NAME  [其他从句]
UNION ALL
SELECT ... FROM  TABLE_NAME  [其他从句] ;

-- 示例
select ename,job from emp where deptno in(10,30)
UNION all
select ename,job from emp where deptno in(10,20);
1
2
3
4
5
6
7
8

保留重复数据行

4.8.3 连表查询

在数据库设计时，为了减少数据的冗余，把数据分为多张表来存储，但是查询的时候又需要完整的数据，此时就需要同时从多张表中检索数据，就产生了连表查询的需求。连表查询一般就分为内连接 和 外连接。

       ![image.png](https://img-blog.csdnimg.cn/img_convert/d4a613e2e2b4d0b1ea4657677f4e00f2.png#crop=0&crop=0&crop=1&crop=1&height=268&id=xc5dI&margin=%5Bobject%20Object%5D&name=image.png&originHeight=358&originWidth=570&originalType=binary&ratio=1&rotation=0&showTitle=false&size=41631&status=done&style=none&title=&width=427)            ![image.png](https://img-blog.csdnimg.cn/img_convert/504ddec006ee7716f480407398f4c90c.png#crop=0&crop=0&crop=1&crop=1&height=136&id=eHK0x&margin=%5Bobject%20Object%5D&name=image.png&originHeight=136&originWidth=257&originalType=binary&ratio=1&rotation=0&showTitle=false&size=6904&status=done&style=none&title=&width=257)
                           （ 员工表 emp  ）                                                                                           （部门表  dept）    
1

4.8.4 内连接

指的的是只有满足条件的数据行 才会连接显示，不满足条件的数据行将被过滤掉。

• 简单写法(推荐)

SELECT ... FROM TABLE_NAME_A , TABLE_NAME_B , TABLE_NAME_N  WHERE 条件  [其他从句]

-- 连接查询
select * from emp , dept where emp.deptno = dept.deptno; -- MySQL 就使用这个

1
2
3
4
5

如果有N张表连接 那么至少需要 N-1 个连接条件才可以消除 笛卡尔积

• 标准写法

SELECT ... FROM TABLE_NAME_A INNER JOIN  TABLE_NAME_B  ON   连接条件  [其他从句]  ;
select ename,dname,emp.deptno from emp INNER JOIN dept  on emp.deptno=dept.deptno;  -- 标准
1
2

超过两张表 继续 INNER JOIN 即可

4.8.5 外连接

• 左外连接：左表数据完全显示，右表不配的行补NULL

SELECT ... FROM  TABLE_NAME_A  LEFT JOIN  TABLE_NAME_B  ON  条件 
 select *  from emp e  left join   dept d  on  e.deptno=d.deptno
1
2

• 右外连接：右表数据完全显示，左表不配的行补NULL

SELECT ... FROM  TABLE_NAME_A  RIGHT JOIN  TABLE_NAME_B  ON  条件
-- 示例
select *  from emp e  right join  dept d  on  e.deptno=d.deptno;

1
2
3
4

第五节、数据操作[重点] （CURD）

5.1 新增数据

**INSERT INTO **_table_name( [ COLUMN_A , … ] ) _**VALUES ( _VALUE_1 , … _ ) **

5.1.1 部分字段插入

INSERT INTO TABLE_NAME( 列名,...) VALUES(值,...);
1

一定要保证列与值的 (个数 顺序 类型) 要一致

5.1.2 全部字段插入

INSERT INTO TABLE_NAME VALUES(值,...);
1

5.1.3 批量插入

INSERT INTO TABLE_NAME( 列名,... ) VALUES( 值,... ),( 值,... ),( 值,... );
1

5.2 修改数据

**UPDATE **_table_name SET COLUMN=VALUE [ , COLUMN=VALUE , … ] _**WHERE **条件

5.2.1 单字段修改

-- 没有where 全表修改
update emp set comm = 1000;
-- 修改单列
update emp set sal=888 where ename='猪刚鬣';

1
2
3
4
5

修改数据 一定要 确保范围的正确性

5.2.2 多字段修改

-- 修改多列
update emp set sal = 5200 , job = '秘书' where ename='赵梦非';
1
2

5.3 删除数据

**DELETE FROM _table_name **WHERE ** **条件_

3.3.1 条件删除

delete from students sno=100;
1

删除数据一定要带条件否则，将删除表中所有数据。

3.3.2 清空表

**TRUNCATE **_table_name _

truncat emp;
1

清理自增计数器

第六节、库表操作 DDL

6.1 数据库操作

6.1.1 创建数据库

create database [if not exists] db_name [character set 字符集] ;
1

6.1.2 删除数据库

drop database db_name;
1

6.1.3 修改数据库编码

alter database db_name character set  utf8;
1

6.1.2 查询数据库创建语句

show create database  db_name;
1

6.2 数据类型

6.2.1 字符类型

char(n) & varchar(n)

char: 定长字符声明多少空间就要消费多少空间 char(4) |‘a’ |
varchar:可变字符，声明多空间是可用最大空间，如果没有超过则以实际占用为准 varchar(4) |‘a’|
总结：varchar 空间利用率好，推荐使用。除非一定固定长度的字段使用char

6.2.2 整数类型

tinyint & smallint & ** Int & ** bigInt
tinyint 同 byte
smallint 同 short
int 同 int
bigint 同 long

6.2.3 浮点数类型

float & double(M,D) & **decimal(M,D) **
float 同 java float
double 同 java double
decimal 货币类型，更精确声明范围和精度 M 长度 D 精度 decimal(6,2) 0000.00-9999.99

6.2.4 日期时间类型

**date **& **datatime **& timestap 时间戳
date yyyy-MM-dd
datetime yyyy-MM-dd hh:mm:ss
timestap yyyy-MM-dd hh:mm:ss

6.2.5 二进制大对象类型

blob & clob

6.3 数据表操作

6.3.1 创建表

create table table_name(
   cloumn_1 type [约束] ,   
   column_2 type [约束],
   .......
   column_3 type [约束]  -- 最后一列后面没有逗号,
)[charset = 字符集 ]
eg:
create table student(
   sno int ,
   sname varchar(30),
   birthday date,
   cost double
)
1
2
3
4
5
6
7
8
9
10
11
12
13

6.3.2 删除表

drop table table_name;
eg:
drop table students;
1
2
3

6.3.3 重命名

alter table old_table_name rename  [to]  new_table_name
eg:
alter table student to my_student
1
2
3

6.3.4 添加列

alter table table_name add [column] 列名 类型 约束;
eg:
alter table table_name add  address varchar(30) null;
1
2
3

6.3.5 修改列

修改类型 modify
alter table table_name modify [column] 列名 类型 约束;
eg:
alter table table_name modify  address varchar(50) null;

修改列名 change
alter table table_name change old_column new_cloumn 类型 约束;
eg:
alter table table_name change address addr varchar(50) null;
1
2
3
4
5
6
7
8
9

6.3.6 删除列

 alter table table_name drop column 列名 ;
 eg:
 alter table table_name drop column address;
1
2
3

第七节、约束[掌握]

7.1 实体完整性（数据验证）

所谓实体完整性就是，每条记录都是一个实体，这个实体总有一个唯一与其他行区分的标志，如果没有这标识，那么意味这这个实体是不完整的。

7.1.1 主键约束

数据表中每条记录需要一个唯一区分和标识的列，这个列就是主键列，主键约束要求是 唯一 和 非空

create table  person(
    pno int  primary key, 
    pname varchar(30),
    info varchar(60)
)
1
2
3
4
5

7.1.2 联合主键

把多个字段组合最为主键，这个组合不重复，这种主键设置需要单独设置，不可再列后面直接声明。

create table score(
  sno int ,  -- 学号
  cno int ,  -- 课程号
  score int,  
  primary key( sno,cno )  -- 主键约束
)
1
2
3
4
5
6

7.1.3 主键自动增长

存在主键约束的表，添加数据时不方便，每次需要确保主键不重复，所以需要引入一种自动编号的机制， auto_increment可以实现 整数类型主键 每次添加数据的时候 自动递增。

create table  person(
    pno int  primary key auto_increment , -- pno为主键 要求唯一非空
    pname varchar(30),
    info varchar(60)
)
1
2
3
4
5

自动增长的列 主键 类型为整型

7.2 域完整性

所谓域的完整性，就是对一个字段 完整性的限制(比如 是否为空 是否唯一 是否有默认值)。

7.2.1 非空约束

某字段不能为空

create table person(
    pno int primary key auto_increment,
    pname varchar(30) not null , -- 非空约束
    info varchar(60)
)
1
2
3
4
5

7.2.2 默认值约束

没有显示赋值就使用提前准备好的一个值

create table person(
    pno int primary key auto_increment,
    pname varchar(30) not null , -- 非空约束
    info varchar(60) null ,
    contry varchar(60) default '中国'
)
1
2
3
4
5
6

7.2.3 唯一约束

create table person(
    pno int primary key auto_increment,
    pname varchar(30) not null , -- 非空约束
    info varchar(60) null ,
    contry varchar(60) default '中国',
    tel varchar(30) unique
)
1
2
3
4
5
6
7

7.3 参考完整性

7.3.1 外键约束

create  table dept(
   deptid int primary key auto_increment,
   dname varchar(30) not null,
   loc varchar(30)
)
create table  emp (
   empid int primary key auto_increment,
   ename varchar(30) not null,
   deptno int ,
   -- 添加外键约束
   constraint fk_emp_dept_001 foreign key (deptno)  references  dept(deptid)
);
1
2
3
4
5
6
7
8
9
10
11
12

7.3.2 通过修改方式设置外键

create  table dept(
   deptid int primary key auto_increment,
   dname varchar(30) not null,
   loc varchar(30)
);

create table  emp (
   empid int primary key auto_increment,
   ename varchar(30) not null,
   deptno int 
);

 -- 添加外键约束
 alter table emp add constraint fk_emp_dept_001 foreign key (deptno)  references  dept(deptid) [ ON DELETE CASCADE ON UPDATE CASCADE ]
1
2
3
4
5
6
7
8
9
10
11
12
13
14

小结： MySQL 中存在 5中约束 主键约束 外键约束 非空约束 唯一约束 默认值约束

约束删除问题 很少使用，要知道

1. 删除外键约束

alter table employee2 drop foreign key fk_002;

2. 删除主键约束

alter table employee2 drop primary key;

alter table employee2 modify eid int null;
3. 删除非空约束 唯一约束 默认值 都是通过修改修改列信息完成

第八节、视图

8.1 概念

视图，虚拟表，从一个表或多个表中查询出来结果构成的表，作用和真实表一样，包含一系列带有行和列的数据。

作用：把复杂的查询变得简单

8.2 视图特点

• 优点

    简单化，可简化查询。
  		安全性，用户只能查询或修改他们所能见到得到的数据。
  		逻辑独立性，可以屏蔽真实表结构变化带来的影响。
  1

• 缺点

    性能相对较差，简单的查询也会变得稍显复杂。
  		修改不方便，特变是复杂的聚合视图基本无法修改。
  1

8.3 管理视图

8.3.1 视图创建

create view view_name as select....
1

create view view_simple_info as 
select 
   dname,nation,brand,ctype 
from 
   drivers d, cars c , cartypes t 
where 
   d.carid=c.cid and c.typeid = t.tid;
1
2
3
4
5
6
7

创建视图需要 create view 权限

8.3.2 视图使用

from 视图
select * from view_simple_info
1
2

8.3.3视图修改

1.create or replace view  VIEW_NAME as select .....
2.alter view VIEW_NAME as select .......
1
2

create or replace view view_simple_info
 as 
select dname,nation,brand,ctype ,serial_id
from drivers d, cars c , cartypes t 
where d.carid=c.cid and c.typeid = t.tid;
1
2
3
4
5

8.3.4视图删除

drop view VIEW_NAME
1

drop view myview;
drop view view_simple_info;
1
2

注意：

视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
如果视图包含以下结构中的一种，则视图不可更新
聚合函数的结果
DISTINCT 去重后的结果
GROUP BY 分组后的结果
HAVING 筛选过滤后的结果
UNION、UNION ALL 联合后的结果
1
2
3
4
5
6
7

第九节、索引（了解）

9.1 索引概念

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

9.2 单列与组合索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

9.3 创建索引时机 一句查询慢

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。

9.4 创建索引方式

9.4.1 直接创建

CREATE INDEX indexName ON table_name (column_name)
1

9.4.2 修改创建

ALTER table tableName ADD INDEX indexName(columnName)
1

9.4.3 删除索引

DROP INDEX indexName ON mytable; 
1

第十节、权限管理（dcl） 管理数据操作权限 作用保证我的数据安全

10.1 创建用户

create user '用户名' identified by '密码';
create user 'kangkang' identified by '123';
1
2

10.2 授权

 grant 权限 ON DB.table to  '用户名'
 grant select,delete on *.* to 'kangkang';
1
2

10.3 撤销权限

revoke  权限 on  DB.table from  '用户名'
REVOKE  select,delete ON empdb.* FROM 'kangkang';
1
2

10.4 删除用户

drop user  '用户名'
drop user 'kangakng'
1
2

10.5 远程登陆

** – 创建一个远程登陆的用户**
create user ‘root’@‘%’;
– 授权
grant all on . to ‘root’@‘%’ ;
– 设置远程登陆密码
alter user ‘root’@‘%’ identified with mysql_native_password by ‘java123456!’ ;

MySQL 8 和 MySQL5.X 是有区别的

作业：
1、使用 admin账户 只能 create select update insert 去创建 用户表 角色 权限表
2、查询当前用户的基本信息和角色信息以及权限信息
3、使用视图查询上面的信息
4、删除三张表数据
5、删除三张表结构，删除这个库 删除当前admin账户

第十一节、事务

10.1 事务概念

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

事务一系列事务逻辑完整性，要吗都成功，要吗都失败。

10.2 事物特性

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。说话算数

• **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

读未提交<读提交<可重复读<序列化

快>慢

10.3 MYSQL 事务演示

用 BEGIN, ROLLBACK, COMMIT来实现， 开发中有具体API实现事物控制。

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

改变 MySQL 的自动提交模式
SET AUTOCOMMIT=0 禁止自动提交 SET AUTOCOMMIT=1 开启自动提交

第十二节、函数（api）

MYSQL函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。

MySQL 数值函数

MySQL 字符串函数

MySQL 日期和时间函数

MySQL 聚合函数

MySQL 判断函数

case 喜欢使用到行转列的场景下比如：

案例代码

-- 函数执行场景 
-- 1. sql 中使用函数
-- 2. 直接select 调用
-- 演示直接调用
select  abs(-100);
select  POW(2,10);
 
-- 演示在SQL语句中使用
select    sal + ifnull( comm  , 0   )   from emp;

-- 字符串函数
select * from emp;
select ename, length(ename),left(ename,1), concat(ename,'|',job), SUBSTRING(ename,2,1) from emp;
-- 日期期间
select now() -- 系统日期时间
select SYSDATE(); -- 和now()相同
select CURRENT_DATE(); -- 只有日期
select CURRENT_TIME(); -- 只有时间
select ename,hiredate from emp where  month(hiredate)=5;  -- year() moth() date() 可以分别提取年 月 日

 -- 行转列案例
select 
    sname,  
		max( case when  subject='语文' then score end  ) as 语文,
		max( case when  subject='数学' then score end  ) as 数学
from sc  GROUP BY sname ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

第十三节、数据库设计

需求分析，通过需求分析还要结合原型 axture 去设计数据库，结合设计方法和设计"工具。实际上设计数据库就是数据建模。ER图就是一种辅助设计的工具。

1. ER 图

外键设计问题：

1. 1 v 1 外键任意（不管谁管理外键）
2. 1 v N 外键在多的一端。
3. N v N 引入中间表，分别持有两个表的外键。

2. 三范式

三范式目标是减少数据冗余，反范式设计是为了提高查询效率。

第一范式，要求将列尽可能最小的分割，希望消除某个列存储多个值的冗余的行为。 列不可在拆分

第二范式，要求唯一的主键，且不存在对主键的部分依赖，希望消除表中存在冗余。必须有主键

第三范式，要求没有间接依赖于主键的列，即仍然是希望消除表中冗余的列

反范式

3.PowerDesigner & PDMan

第十四、存储过程(了解)

存储过程，是MySQL中用于封装业务逻辑的SQL代码，普通的SQL只能检索更新删除添加数据，并不具备逻辑处理能力 。

简单说存储过程 就和 Java 中的方法作用相同，封装业务逻辑功能， 开发中使用的目的是屏蔽掉业务逻辑细节。开发者直接调用存储过程获得结果。

存储过程： 用于封装一段业务逻辑代码，他不强调返回值，但是可以通过输出参数类型。
存储函数： 用要函数来封装业务逻辑代码，函数强调有返回值。函数必须声明换回值。

自定义：自定一个方法（用得非常，增加数据压力，很难维护 太乱）

创建存储过程

create procedure 存储过程名称(  [ in|out|inout 变量名 类型 ,...  ]  )
begin
    //SQL
end;


create function 函数名称(  [in]  变量名  类型,....    ) returns 返回值类型
begin
     //SQL......
     return xxx;
end;

// 在MySQL中创建函数时出现这种错误的解决方法：
// set global log_bin_trust_function_creators=TRUE
1
2
3
4
5
6
7
8
9
10
11
12
13
14

名字自定义 ，需要避免与系统函数名重复 ，建议使用统一前缀如： xx_存储过程名
参数有方向性 ： in 表示输入 out 表示输出 inout调用输入执行后可以获得值
begin 和 end 之间为过程体 书写逻辑

定义变量 declare 变量名 类型 default 值
变量赋值 set 变量名 = 值

案例

CREATE   PROCEDURE `initData`()
begin
	 declare i int default 1 ;
	 WHILE (i<=100000) DO
			  INSERT INTO worker(id,w_name,sal,size) VALUES( i ,CONCAT('小泽',i),1000 ,'D');
		
        set i= i+1;
	 END WHILE;
   commit;
end
1
2
3
4
5
6
7
8
9
10

插入十万条测试数据

代码片段

--------------------- 同java if---------------------------------
IF search_condition THEN
	statement_list
ELSE
	statement_list
END IF;

------------------------类似java Switch--------------------------
CASE case_value
	WHEN when_value THEN
		statement_list
	ELSE
		statement_list
END CASE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

课程案例代码

-- 存储过程(不强调返回值,也可以返回数据通过out输出参数实现返回数据)
-- 存储函数(必须有返回值)
-- 1. 语法(基本定义)
create PROCEDURE hello()
begin
   select concat("hello","world");
end; 

call hello();

-- 2. 参数 输入参数(外界提供)  输出参数(向外界返回)  输入输出参数( 结合了输入和输出功能 )
create PROCEDURE hello2( in a int , in b int  )
begin
  select a+b;
end;

call hello2(1,2);

-- 3. 存储过程如何返回数据
create PROCEDURE hello3( in a int , in b int , out c int )
begin
   set c = a+b;
end;

-- 定义一个临时变量
set @num = 0;

call hello3(100,50,@num);

select @num;


-- 往一个表中插入10万行数据。（循环）
create procedure addData()
begin 
	declare i int; -- 声明变量
	set i = 1;     -- 变量赋值
	while (i<=100000) do
	     insert into test(id,title,content,create_date) values( i,CONCAT("标题",i),CONCAT("内容",i),now()); 
			 set i = i+1;
	end while; 
  commit;
end;

call addData(); -- 调用
select count(*) from test;
create index title_index on test(title); -- 测试索引提升查询
select * from test where title="标题100000" 


-- 使用判断
create PROCEDURE pro_sex( in gender varchar(30) )  
begin
    declare tips varchar(30);
	  if gender="男" then
		   set tips = "小哥哥";
    else 
		  set tips = "小姐姐";
		end if;	
		 select tips;	
end;

call pro_sex("女")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

作业：1、复习sql 整理笔记，要求面试题为主，不能少于20条
2、先用java JDBC 去做昨天晚上 t_user t_role t_auth
3、用mysql的数据模型建表。
4、重点阐述我的事务，：事务的定义 事务的四种特征，隔离级别（举例）