Mysql入门

最新推荐文章于 2023-01-04 20:42:01 发布

Sharry洗手溢

最新推荐文章于 2023-01-04 20:42:01 发布

阅读量1.2k

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43779187/article/details/127969322

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Mysql 入门

0. 前言

学习目标：

安装数据库，以 Windows OS 的Mysql 为例；
了解数据库的一些基本概念；
SQL语句的学习与练习；
了解事务、索引、SQL优化。

1. 准备工作

正式学习之前，我们要了解数据库的一些相关概念、将学习过程中所需的Mysql 准备好。

1.1 相关概念

名称	作用
数据库DataBase（DB）	存储数据的仓库，数据是有组织的进行存储
数据库管理系统DataBase Management System（DBMS）	操纵和管理数据库的大型软件
结构化查询语言Structured Query Language（SQL）	操作关系型数据库的编程语言，定义了一套操作关系型数据库的同一标准

数据库的种类：
层次式数据库（已过时）、网络型数据库（已过时）、关系型数据库、非关系型数据库；

常见的数据库：
SQL Server、MySQL、Oracle、DB2、Sqlite、redis（非关系型数据库）、mongodb（非关系型数据库）

Mysql是关系型数据库，本文我们侧重学习Mysql。

推荐教材：《数据库系统概论》

1.2 Mysql 安装与启动

（1）下载与安装：

下载：下载地址

安装：安装教程，转载自博客园

（2）启动：

安装完毕后，我们可以在 windows 的 dos 窗口启动Mysql、连接客户端

	net start mysql

（3）连接客户端

	mysql [-h 127.0.0.1] [-P 3306] -uroot -p
参数：
	-h：mysql服务所在的主机IP
	-P：mysql服务端口号，默认是3306
	-u：mysql数据库用户名，一般为约定俗成的root
	-p：mysql数据库用户名对应的密码
注意：[]内的参数是可选的，如果需要连接远程的mysql，就需要提供主机IP和端口号，如果是连接本地的mysql，就不用提供这两个参数；注意大小写。

1.3 常用工具

我们常用一些可视化工具来辅助我们操作数据库。对于mysql，此处推荐： navicat，sqlyog等。

下载地址：
navicat：navicat官网
sqlyog: sqlyog官网

2. Mysql 入门

2.1 数据模型

在上一个小节我们提到过，Mysql是一种关系型数据库（Relational Database Management System，RDBMS），是建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

二维表：类似于excel，由行和列组成的表。

2.2 SQL语言分类

我们使用SQL来操作数据库。SQL分为5类：

语言	作用	关键词举例
DDL（Data Definition Language）数据定义语言	定义数据，如创建表、修改表字段等	CREATE，ALTER，DROP
DQL（Data Query Language）数据查询语言	查询数据	SELECT
DML（Data Manipulation Language）数据操纵语言	操作数据，增删改等	INSERT，UPDATE，DELETE
DTL(Data Transaction Language) 事务控制语言	事务提交与回滚	COMMIT，ROLLBACK
DCL(Data Control Language)数据控制语言	权限授予与回收	GRANT，REVOKE

此阶段我们重点学习DDL、DQL与DML。

2.3 Mysql数据类型

Mysql支持的数据类型从大类可以分为以下几类：数值类型、字符串类型、日期、其他数据类型。

类别	常见类型
数值类型	int, tinyint, smallint, bigint, float, double, bool 等
字符串类型	varchar, char, text 等
日期	date, time, datetime, year 等
其他	set, point 等

现阶段我们应学习常见的数值、字符串、日期类型，至于其他类型，可以在实际业务中用到时再去学习或研究。

2.4 DDL数据库操作

在了解了上面的SQL语言分类、数据类型之后，我们就可以学习并使用这些SQL语句了。

Mysql 既是一个DB，也是一个DBMS。所以当我们连接了Mysql客户端后，可以进行数据库操作。

查询所有数据库：

show databases;

创建数据库：

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ];

删除数据库：

drop database [ if exists ] 数据库名;

切换数据库：

use 数据库名;

2.5 DDL表操作

当我们切换至某个数据库后，可以在这个数据库内进行表操作。

查询当前数据库所有表

show TABLES;

查看指定表结构

desc 表名;

查询指定表的建表语句

show create table 表名;

创建表结构

create table 表名(
	字段1 字段类型 [ COMMENT 注释1 ],
    字段2 字段类型 [ COMMENT 注释2 ],
    字段3 字段类型 [ COMMENT 注释3 ],
    .......
    字段N 字段类型 [ COMMENT 注释N ],
) [ COMMENT 表注释 ];

添加表字段

ALTER TABLE 表名 ADD 字段名 数据类型(长度) [COMMENT 注释] [ 约束 ];

修改字段数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型(长度) [COMMENT 注释] [ 约束 ];

删除字段

ALTER TABLE 表名 DROP 字段名;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

删除表

DROP TABLE [ IF EXISTS ] 表名;

删除并重新创建表

TRUNCATE TABLE 表名;

2.6 DML 数据操作

添加数据

// 指定字段名
INSERT INTO 表名(字段1,字段2,....) VALUES(值1,值2,....);
// 不指定字段名，值列表的顺序必须与字段的顺序一致
INSERT INTO 表名 VALUES(值1,值2,....);
// 批量添加数据
INSERT INTO 表名(字段1,字段2,....) VALUES(值1,值2,....),(值1,值2,....),(值1,值2,....);

修改数据

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

删除数据
```
DELETE FROM 表名 [ WHERE 条件 ];
```

2.7 DQL 数据查询

基本语法：

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

基础查询（不带任何条件）
条件查询（WHERE）
聚合函数（count、max、min、avg、sum）
分组查询（GROUP BY）
排序查询（ORDER BY）：排序规则 DESC（降序） || ASC （升序，默认）
分页查询（LIMIT）：参数（index，pageSize）index：记录的索引，从0开始，pageSize：每次查询多少条记录

2.8 综合练习

学习完上述语句后，我们来做一组综合练习来巩固一下：

准备数据库表：

DROP DATABASE IF EXISTS test1;
CREATE DATABASE test1;
USE test1;
##部门表
#DROP IF EXISTS TABLE DEPT;
CREATE TABLE DEPT(
DEPTNO int PRIMARY KEY,##部门编号
DNAME VARCHAR(14) , ##部门名称
LOC VARCHAR(13) ##部门地址
) ;
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
##员工表
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
EMPNO int PRIMARY KEY, #员工编号
ENAME VARCHAR(10), #员工姓名
JOB VARCHAR(9), #员工工作
MGR int, #员工直属领导编号
HIREDATE DATE, #入职时间
SAL double, #工资
COMM double, #奖金
DEPTNO int #对应dept表的外键
);
## 添加 部门 和 员工 之间的主外键关系

INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);
#工资等级表
#DROP IF EXISTS TABLE SALGRADE;
CREATE TABLE SALGRADE(
GRADE int, #等级
LOSAL double, #最低工资
HISAL double ); #最高工资
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

题目：

#1、查找部门30中员工的详细信息。

#2、找出从事clerk工作的员工的编号、姓名、部门号。

#3、检索出奖金多于基本工资的员工信息。

#4、检索出奖金多于基本工资60%的员工信息。

#5、找出10部门的经理、20部门的职员 的员工信息。

#6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。

#7、找出获得奖金的员工的工作。

#8、找出奖金少于100或者没有获得奖金的员工的信息。

#9、找出姓名以A、B、S开始的员工信息。

#10、找到名字长度为6个字符的员工信息。

#11、名字中不包含R字符的员工信息。

#12、返回员工的详细信息并按姓名排序。

#13、返回员工的信息并按工作降序工资升序排列。

#14、计算员工的日薪(按30天)。

#15、找出姓名中包含A的员工信息。

多表查询

#1、返回拥有员工的部门名、部门号。

#2、工资水平多于smith的员工信息。

#3、返回员工和所属经理的姓名。

#4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

#5、返回员工姓名及其所在的部门名称。

#6、返回从事clerk工作的员工姓名和所在部门名称。

#7、返回部门号及其本部门的最低工资。

#8、返回销售部(sales)所有员工的姓名。

#9、返回工资水平多于平均工资的员工。

#10、返回与SCOTT从事相同工作的员工。

#11、返回与30部门员工工资水平相同的员工姓名与工资。

#12、返回工资高于30部门所有员工工资水平的员工信息。

#13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。

#14、返回员工的姓名、所在部门名及其工资。

#15、返回员工的详细信息。(包括部门名)

#16、返回员工工作及其从事此工作的最低工资。

#17、计算出员工的年薪，并且以年薪排序。

#18、返回工资处于第四级别的员工的姓名。

#19、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资

#20.工资等级多于smith的员工信息。

3. 事务

3.1 事务的简介

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

3.2 事务的特性：ACID

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变是永久的。

3.3 并发事务问题

脏读：一个事务读到另一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不相同，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻觉”。

3.4 事务的隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下四种：

隔离级别	脏读	不可重复读	幻读
读未提交（Read uncommitted）	√	√	√
读已提交（Read committed）	×	√	√
可重复读（Repeatable Read）	×	×	√
串行化（Serializable）	×	×	×

MySQL数据库的默认事务隔离级别为：可重复读（Repeatable Read）

// 查看事务隔离级别
SELECT @@transaction_isolation;

注意：事务隔离级别越高，数据越安全，但是性能越低

4. 索引

索引是一个单独的，存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。

MySQL中索引的存储类型有两种：BTree和Hash

索引是在存储引擎中实现的。（InnoDB、MyISAM、Memory、Heap）

InnoDB/MyISAM只支持 BTree 索引

Memory/Heap 支持 BTree 和 Hash 索引

索引的优缺点：

优点：

提高数据的查询效率（类似于书的目录）
可以保证数据库表中每一行数据的唯一性（唯一索引）
减少分组和排序的时间（当使用分组和排序进行数据查询时）
- 被索引的列会自动进行分组和排序

缺点：

占用磁盘空间
降低更新表的效率（不仅要更新表中的数据，还要更新相应的索引文件）

4.1 索引的分类

普通索引和唯一索引

普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
唯一索引：要求索引列的值必须唯一，但允许有空值
- 如果是组合索引，则列值的组合必须唯一
- 主键索引是一种特殊的唯一索引，不允许有空值

单列索引和组合索引

单列索引：一个索引只包含单个列，一个表可以有多个单列索引
组合索引：在表的多个字段组合上创建的索引
- 只有在查询条件中使用了这些字段的左边字段时，索引才会被使用（最左前缀原则）
例如：表中有 gender,stuno,stuname,age,tel,… 字段，将stuno和stuname作为组合索引，在查询条件中如果使用了stuno字段，组合索引就会被使用。