mysql入门

数据库概述

什么是数据库

保存数据的仓库。是一个软件或者文件系统。把数据都保存这些特殊的文件中，并且需要使用固定的语言（SQL语言/语句）去操作文件中的数据。

数据的存储方式

需求：开发一个学生选课系统，学生系统中含有学生信息、老师信息、课程信息。需要将关系和数据进行保存，保存到哪里合适呢？

数据保存在内存
java
```
Student s = new Student("张三",18,"上海");
Teacher t = new Teacher("李四",19,"上海");
Course c = new Course("语文",90);
```
1
2
3
new 出来的对象存储在堆中.堆是内存中的一小块空间
优点：存储速度快缺点：断电/程序退出,数据就清除了
数据使用 IO 流技术保存在硬盘的普通文件中优点：永久保存缺点：IO 流的查找，增加，修改，删除数据比较麻烦。同时使用 IO 流技术需要频繁调用系统资源和将系统资源还给系统，这样操作效率比较低
数据保存在数据库优点：永久保存,通过 SQL 语句比较方便的操作数据库。是方式一和方式二的结合。可以解决上述两种方式的缺点。

数据库的优点

数据库是按照特定的格式将数据存储在文件中，通过 SQL 语句可以方便的对大量数据进行增、删、改、查操作，数据库是对大量的信息进行管理的高效的解决方案。

常见数据库管理系统

我们开发应用程序的时候，程序中的所有数据，最后都需要保存到专业软件中。这些专业的保存数据的软件我们称为数据库管理系统。我们学习数据库，并不是学习如何去开发一个数据库管理系统，我们学习的是如何使用数据库管理系统以及数据库中的数据记录的操作。而数据库管理系统是由第三方公司研发。

Oracle
大型关系型数据库管理系统，Oracle公司产品。系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、安全可靠的。但是它是收费的。
MySQL
中型关系型数据库管理系统，Oracle公司产品。早期由瑞典一个叫MySQL AB公司开发，后期被SUN公司收购，再后期被Oracle收购。体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。MySQL 6.x 版本也开始收费。
DB2
大型关系型数据库管理系统，IBM公司产品。收费的。常应用在银行系统中.
Infomix
中型关系型数据库管理系统，IBM公司产品。
SQL Server
中型关系型数据库管理系统，Microsoft公司产品。收费的。C#、.net等语言常使用。
Sybase
大型关系型数据库管理系统，Sybase公司产品。已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
SQL Lite
应用在手机端的数据库。
Office Access
小型关系型数据库管理系统，Microsoft公司产品。
Visual Foxpro
小型关系型数据库管理系统，Microsoft公司产品。

小结

说出数据库的概念：存储数据的仓库
说出常用的数据库管理系统：Oracle, MySQL, DB2, Informix

MySQL

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL 是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

访问与下载

官方网站：https://www.mysql.com/
Windows版本下载地址：https://downloads.mysql.com/archives/installer/
Mac版本下载地址: https://downloads.mysql.com/archives/community/

安装

运行mysql-installer-community-5.7.28.0.msi，进入安装步骤

同意协议，点击下一步

选择安装类型，点击下一步

注意：安装需要依赖环境，如需安装的功能缺少对应环境，需先安装环境

下一步的时候会提示缺少环境,此处的提示是说你期望安装的产品中缺少依赖,不一定提示的是数据库服务器安装的依赖,可能是其他功能组件的依赖缺少

此处发现没有 mysql server, 说明它对应的依赖缺少,可以先执行安装这些可以安装的,之后安装完依赖后再重新安装其他模块,我们此处的操作是先安装依赖,然后后退再次安装

下载安装前面提示的 mysql server 需要的 vc2013后,vc2013 如果在64位系统上面建议32和64的程序都安装,然后在上图的地方后退,重新 next 就可以看到可以安装 mysql server 了

检查是否存在失败项

进行第一步配置,除了需要设置密码外,后面的步骤基本上都是下一步

类型与网络配置

设置 root 密码

创建SQL服务与窗口

插件

应用配置

finish 后回到当前页面继续配置

连接服务器（先 check检查, 后下一步）

执行完此步骤下一步 finish 配置完成

配置环境变量

Windows
创建MYSQL_HOME：C:\Program Files\MySQL\MySQL Server 5.7
追加PATH：%MYSQL_HOME%\bin;
MacOS / Linux
终端中输入cd ~ 进入目录，并检查.bash_profile是否存在，有则追加，无则创建
创建文件 touch .bash_profile
打开文件 open .bash_profile
输入export PATH=${PATH}:/usr/local/mysql/bin 保存并退出终端

Windows MySQL的卸载

卸载 MySQL软件

打开控制面板，找到mysql字样的软件，依次删除

卸载MySQL服务

打开 cmd 工具
运行以下命令:
sc delete mysql57
sc delete mysql...

删除注册表

Win - R
输入regedit
搜索mysql并依次删除

Mac初始密码的重置

bash

chsh -s /bin/bash
cd /usr/local/mysql
sudo ./mysqld_safe --skip-grant-tables &
mysql -u root -p
update user set authentication_string=password("root") where user="root";

客户端工具

Navicat

Navicat是一套快速、可靠并价格相宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业的需要。Navicat 是以直觉化的图形用户界面而建的，让你可以以安全并且简单的方式创建、组织、访问并共用信息。

SQLyog

MySQL可能是世界上最流行的开源数据库引擎，但是使用基于文本的工具和配置文件可能很难进行管理。SQLyog提供了完整的图形界面，即使初学者也可以轻松使用MySQL的强大功能。其拥有广泛的预定义工具和查询、友好的视觉界面、类似 Excel 的查询结果编辑界面等优点。

PLSQL Developer

PL/SQL Developer是一个集成开发环境，由Allround Automations公司开发，专门面向Oracle数据库存储的程序单元的开发。有越来越多的商业逻辑和应用逻辑转向了Oracle，因此，PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL Developer侧重于易用性、代码品质和生产力，充分发挥Oracle应用程序开发过程中的主要优势的。

SQL语言

概念

SQL（Structured Query Language）结构化查询语言，用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。

经验：通常执行对数据库的“增删改查”，简称C（Create）R（Retrieve）U（Update）D（Delete）。

库、表、记录

服务器与数据库、表、记录的关系

实体类与表的对应关系

【掌握】DDL

Data Definition Language，数据定义语言对库和表的管理 create, alter, drop

数据库操作

创建数据库

sql

create database 数据库名;

sql

create database if not exists 数据库名;

sql

create database 数据库名 character set 字符名称;

列出数据库

sql

show databases;

查看数据库详情

sql

show create database 数据库名;

使用数据库

sql

use 数据库名;

修改数据库

sql

alter database 数据库名 character set 字符集名称;

删除数据库

sql

drop database 数据库名;

数据表操作

创建表

语法

sql

create table 表名 (
    字段名1 字段类型1 约束1,
    字段名2 字段类型2 约束2
);

sql

create table 新表名 like 旧表;

数据类型

整形
- tinyint
- smallint
- medinumint
- int/integer
- bigint
浮点型
- float
- double
- decimal(m, n)
日期
- time
- date
- datetime
- timestamp
字符串
- char
- varchar(m)

列出数据表

sql

show tables;

查看数据表详情

sql

desc 表名;

sql

show create table 表名;

修改表结构

sql

alter table 表名 add 字段名 数据类型;
alter table 表名 modify 字段名 新的数据类型;
alter table 表名 change 旧列名 新列名 数据类型;
alter table 表名 drop 列名;
rename table 旧表名 to 新的表名;

删除表

sql

drop table 表名;
drop table if exists 表名;
truncate 表名;

【重点】DQL

Data Query Language，数据查询语言
select

无条件查询

sql

select * from 表名;

sql

select 列名1, 列名2 from 表名;

`AS`关键字

可以给表、字段起别名

`DISTINCT`关键字

去重

sql

SELECT DISTINCT SALARY FROM t_employees;

查询结果参与运算

条件查询

sql

select 列名 from 表名 where 条件

比较运算符

>
>=
<
<=
=
<>
!=

逻辑运算符

AND / &&
OR / ||
NOT / !

条件中的关键字

`IN`关键字

sql

select 列名 from 表名 where 列名 in (值1，值2，值3);

`BETWEEN ... AND ...`关键字

sql

select 列名 from 表名 where 列名 between 小值 and 大值

`LIKE`关键字

一般用于模糊查询

sql

select 列名 from 表名 where 列名 like '字符串'

通配符

%
匹配任意字符任意次
_
匹配任意字符1次

查询为空的列

SELECT * FROM 表名 WHERE 列名 IS NULL
SELECT * FROM 表名 WHERE 列名 IS NOT NULL

查询结果排序

sql

-- 升序(默认)
SELECT 列名 FROM 表名 ORDER BY 列名 ASC;

sql

-- 降序
SELECT 列名 FROM 表名 ORDER BY 列名 DESC;

sql

-- 降序
SELECT 列名 FROM 表名 ORDER BY 列名 ASC, 列名 DESC;

聚合函数

`COUNT`

sql

SELECT COUNT(*) FROM t_employees;

`SUM`

sql

-- 统计工种为AD_VP的人数
SELECT COUNT(*) FROM t_employees WHERE JOB_ID='AD_VP';

`AVG`

sql

SELECT AVG(SALARY) FROM t_employees;

`MAX`

sql

SELECT MAX(SALARY) FROM t_employees;

`MIN`

sql

SELECT MIN(SALARY) FROM t_employees;

`IFNULL`

sql

SELECT AVG(IFNULL(SALARY, 0)) FROM t_employees;

分组查询

sql

-- 统计出所有工种的人数
-- 分组查询出的结果，列名必须是分组的依据列

-- 关于条件查询
-- 优先选择WHERE，WHERE搞不定了，再选择HAVING
-- HAVING必须搭配GROUP BY使用
-- WHERE是写在GROUP BY 前面的
-- HAVING是写在GROUP By 后面的
SELECT COUNT(*) AS person_count, JOB_ID 
FROM t_employees 
GROUP BY JOB_ID
HAVING person_count > 1;

分页查询

sql

-- 分页查询
SELECT * FROM t_employees LIMIT 20, 10;

【了解】合并查询

SELECT *FROM 表名 1 UNION SELECT* FROM 表名2
SELECT *FROM 表名 1 UNION ALL SELECT* FROM 表名2

合并两张表的结果（去除重复记录）

MYSQL

#合并两张表的结果，去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;

注意：合并结果的两张表，列数必须相同，列的数据类型可以不同

合并两张表的结果（保留重复记录）

sql

#合并两张表的结果，不去除重复记录（显示所有）
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

经验：使用 UNION 合并结果集，会去除掉两张表中重复的数据

小结

DQL的语法解析顺序

FROM，确定上哪去查
WHERE，确定哪些满足条件
GROUP BY 分组
HAVING 新条件，根据 GROUP BY 之后的结果再进行条件查询
ORDER BY 排序
LIMIT 限制
SELECT，筛选要查哪些字段

DQL的编写顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

【重点】DML

Data Manipulation Language，数据操作语言
insert, update, delete

新增记录

sql

INSERT INTO 表名 VALUES(值1, 值2);

sql

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

sql

INSERT INTO 表名 VALUES(值1, 值2), (值1, 值2), (值1, 值2), ...;

蠕虫复制

sql

CREATE TABLE student2 LIKE student1;
INSERT INTO student2 SELECT * FROM student1;

更新记录

sql

UPDATE 表名 SET 字段名=值;

sql

UPDATE 表名 SET 字段名=值 WHERE 条件;

sql

UPDATE 表名 SET 字段名1=值1, 字段名2=值2 WHERE 条件;

删除记录

sql

DELETE FROM 表名;

sql

DELETE FROM 表名 WHERE 条件;

sql

TRUNCATE TABLE 表名;

sql

-- 删除
-- 直接删除行，不会重建数据库或数据表
DELETE FROM t_employees WHERE EMPLOYEE_ID="305";

-- 删除
-- 直接删除表，再重建这张表
TRUNCATE TABLE t_employees;

【了解】DCL

创建mysql用户

sql

create user '用户名'@'主机名' identified by '密码'

给用户授权

sql

grant 权限 on 数据库名.表名 to '用户名'@'主机名'

撤销权限

sql

revoke 权限 on 数据库名.表名 from '用户名'@'主机名'

删除用户

sql

drop user '用户名'@'主机名'

修改密码

sql

mysqladmin -u root -p password 123456
set password=password('密码')
flush privileges;

案例

为root用户添加远程访问权限

sql

grant all privileges on *.* to 'root'@'%' identified by '远程密码';
flush privileges;

【了解】备份与还原

备份

sql

mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径;

还原

sql

mysql -u 用户名 -p 导入库名 < 硬盘SQL文件绝对路径;

【了解】行转列、列转行

列转行

运行前:

运行后:

SQL:

sql

-- 创建学生表
CREATE  TABLE test.stu_score
(
   username VARCHAR(20),        -- 学生姓名
   subject VARCHAR(30),        -- 科目
   score FLOAT               -- 成绩
);

-- 插入数据
use test;
INSERT INTO stu_score SELECT '张三', '语文', 80;
INSERT INTO stu_score SELECT '张三', '数学', 90;
INSERT INTO stu_score SELECT '张三', '英语', 70;
INSERT INTO stu_score SELECT '张三', '生物', 85;
INSERT INTO stu_score SELECT '李四', '语文', 80;
INSERT INTO stu_score SELECT '李四', '数学', 92;
INSERT INTO stu_score SELECT '李四', '英语', 76;
INSERT INTO stu_score SELECT '李四', '生物', 88;
INSERT INTO stu_score SELECT '码农', '语文', 60;
INSERT INTO stu_score SELECT '码农', '数学', 82;
INSERT INTO stu_score SELECT '码农', '英语', 96;
INSERT INTO stu_score SELECT '码农', '生物', 78;

SELECT  * from stu_score ;
-- 列转行
-- 这里的max是为了将无数据的值设为0，避免出现null
SELECT username ,
    MAX(CASE subject WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE subject WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE subject WHEN '英语' THEN score ELSE 0 END ) 英语,
    MAX(CASE subject WHEN '生物' THEN score ELSE 0 END ) 生物
FROM stu_score
GROUP BY username;

行转列

运行前:

运行后:

SQL:

sql

CREATE  TABLE test.stu_score2
(
   username VARCHAR(20),        -- 学生姓名
   math int,        -- 科目
   chinese int,
   english int,
   biological int
);
DROP  TABLE  test.stu_score2;
-- 插入数据
use test;
INSERT INTO stu_score2 SELECT '张三', 90, 80,70,85;
INSERT INTO stu_score2 SELECT '李四', 92, 80,76,88;
INSERT INTO stu_score2 SELECT '王五', 82, 60,96,78;

SELECT * FROM test.stu_score2;

-- 行转列
select username, '数学' COURSE , math as score from test.stu_score2
UNION
select username, '语文' COURSE, chinese as SCORE from test.stu_score2
UNION
select username, '英语' COURSE, english as SCORE from test.stu_score2
UNION
select username, '生物' COURSE, biological as SCORE from test.stu_score2
order by username,COURSE;

视图

概述

什么是视图

视图是虚拟表。与包含数据的表不同，视图只包含查询，可以使用它们动态地检索数据。

为什么要使用视图

重用SQL语句
简化复杂的SQL操作。在编写查询之后，可以轻松地重用它们，而无需知道底层查询本身的细节。
公开表的某些部分，而不是整个表。
保障数据安全。可以允许用户访问表的特定子集，用以代替访问整个表。
更改数据格式化和表示。视图返回的数据可以具有与底层表不同的格式化和表示。

视图的创建

语法：CREATE VIEW 视图名 AS 查询数据源表语句

创建视图

MYSQL

#创建 t_empInfo 的视图，其视图从 t_employees 表中查询到员工编号、员工姓名、员工邮箱、工资
CREATE VIEW t_empInfo 
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

使用视图

sql

#查询 t_empInfo 视图中编号为 101 的员工信息
SELECT * FROM t_empInfo where employee_id = '101';

视图的修改

方式一：CREATE OR REPLACE VIEW 视图名 AS 查询语句
方式二：ALTER VIEW 视图名 AS 查询语句

修改视图

MYSQL

#方式 1：如果视图存在则进行修改，反之，进行创建
CREATE  OR  REPLACE VIEW t_empInfo 
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY,DEPARTMENT_ID from t_employees;

#方式 2：直接对已存在的视图进行修改
ALTER VIEW t_empInfo
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

视图的删除

DROP VIEW 视图名

删除视图

sql

#删除t_empInfo视图
DROP VIEW t_empInfo;

注意：删除视图不会影响原表

视图的注意事项

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

学习目标总结

能够理解数据库的概念
能够安装 MySQL 数据库
能够启动、关闭及登录 MySQL
能够使用 SQL 语句操作数据库
能够使用 SQL 语句操作表结构
能够使用 SQL 语句进行数据的添加修改和删除的操作
能够使用 SQL 语句简单查询数据
能够使用 SQL 语句进行条件查询
能够使用 SQL 语句进行排序
能够使用聚合函数
能够使用 SQL 语句进行分组查询
能够完成数据的备份和恢复

mysql入门 ​

数据库概述 ​

什么是数据库 ​

数据的存储方式 ​

数据库的优点 ​

常见数据库管理系统 ​

小结 ​

MySQL ​

访问与下载 ​

安装 ​

配置环境变量 ​

Windows MySQL的卸载 ​

卸载 MySQL软件 ​

卸载MySQL服务 ​

删除注册表 ​

Mac初始密码的重置 ​

客户端工具 ​

Navicat ​

SQLyog ​

PLSQL Developer ​

SQL语言 ​

概念 ​

库、表、记录 ​

服务器与数据库、表、记录的关系 ​

实体类与表的对应关系 ​

分类 ​

【掌握】DDL ​

数据库操作 ​

创建数据库 ​

列出数据库 ​

查看数据库详情 ​

使用数据库 ​

修改数据库 ​

删除数据库 ​

数据表操作 ​

创建表 ​

语法 ​

数据类型 ​

列出数据表 ​

查看数据表详情 ​

修改表结构 ​

删除表 ​

【重点】DQL ​

无条件查询 ​

AS关键字 ​

DISTINCT关键字 ​

查询结果参与运算 ​

条件查询 ​

比较运算符 ​

逻辑运算符 ​

条件中的关键字 ​

IN关键字 ​

BETWEEN ... AND ...关键字 ​

LIKE关键字 ​

通配符 ​

查询为空的列 ​

查询结果排序 ​

聚合函数 ​

COUNT ​

SUM ​

AVG ​

MAX ​

MIN ​

IFNULL ​

分组查询 ​

分页查询 ​

【了解】合并查询 ​

合并两张表的结果（去除重复记录） ​

合并两张表的结果（保留重复记录） ​

小结 ​

DQL的语法解析顺序 ​

DQL的编写顺序 ​

【重点】DML ​

新增记录 ​

蠕虫复制 ​

更新记录 ​

删除记录 ​

【了解】DCL ​

创建mysql用户 ​

给用户授权 ​

mysql入门

数据库概述

什么是数据库

数据的存储方式

数据库的优点

常见数据库管理系统

小结

MySQL

访问与下载

安装

配置环境变量

Windows MySQL的卸载

卸载 MySQL软件

卸载MySQL服务

删除注册表

Mac初始密码的重置

客户端工具

Navicat

SQLyog

PLSQL Developer

SQL语言

概念

库、表、记录

服务器与数据库、表、记录的关系

实体类与表的对应关系

分类

【掌握】DDL

数据库操作

创建数据库

列出数据库

查看数据库详情

使用数据库

修改数据库

删除数据库

数据表操作

创建表

语法

数据类型

列出数据表

查看数据表详情

修改表结构

删除表

【重点】DQL

无条件查询

`AS`关键字

`DISTINCT`关键字

查询结果参与运算

条件查询

比较运算符

逻辑运算符

条件中的关键字

`IN`关键字

`BETWEEN ... AND ...`关键字

`LIKE`关键字

通配符

查询为空的列

查询结果排序

聚合函数

`COUNT`

`SUM`

`AVG`

`MAX`

`MIN`

`IFNULL`

分组查询

分页查询

【了解】合并查询

合并两张表的结果（去除重复记录）

合并两张表的结果（保留重复记录）

小结

DQL的语法解析顺序

DQL的编写顺序

【重点】DML

新增记录

蠕虫复制

更新记录

删除记录

【了解】DCL

创建mysql用户

给用户授权