数据库之MySQL初体验

一、数据库简介

1.1 数据库概述

数据库（database，DB）是一个以某种有组织的方式存储的数据集合，可以理解为用来存放数据的仓库，即通过一些特殊的软件将数据保存在特殊的文件中。

在数据库领域中，存放具体数据的结构称为表。

• 表(table) 是存放某种特定类型数据的结构化清单
• 列(column) 就是表中的一个字段，所有表都是由一个或多个列组成的
• 行(row) 就是表中的一个记录

1.2 使用数据库的意义

我们之前使用文件来保存数据，使用IO流来操作数据，这种操作数据的方式太过繁琐，写起来性能也不好，也不方便管理数据。

使用数据库的优势：

• 更加方便的对开发中的数据进行管理
• 操作数据的方式更加简单快捷
• 比文件保存性能更加优越

1.3 数据库的分类

• 层级数据库
• 网状型数据库
• 关系型数据库：把复杂的数据结构归结为简单的二元关系（即二维表格形式）

当下流行的数据库绝大多数都是关系型数据库。

二、MySQL

2.1 MySQL概述

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 旗下产品。

MySQL本质是一款关系型数据库的产品，安装mysql其实就是安装了mysql(数据库）的服务。

特点：

• 体积小
• 速度快
• 总体拥有成本低
• 代码开源

2.2 Navicat概述

Navicat是一套快速、可靠并价格相当便宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设计。

Navicat 是以直觉化的图形用户界面而建的，让开发人员可以以安全并且简单的方式创建、组织、访问并共用信息。

三、SQL（结构化查询语言）

3.1 SQL概述

SQL(Structured Query Language ): 结构化查询语言 , 专门用来操作数据库，定义了操作所有关系型数据库的一种规则。

通用规则：

1. SQL语句可以单行或者多行书写，以分号结尾
2. 可以使用空格或者缩进来增强语句的可读性
3. MySQL数据库的SQL语句不区分大小写
4. 单行注释 --注释内容
5. 多行注释 /*注释内容*/

方言：各个数据库厂商针对结构化查询语言的扩展。每个数据库厂商的方言，只能自己数据库用（方言没有通用性）。

3.2 SQL的组成

• DDL(Data Definition Language) 数据定义语言：建库，建表(表结构)，删除，修改数据库和表结构
• DML(Data Manipulation Language) 数据操作语言：插入数据，更新数据，删除数据
• DQL(Data Query Language) 数据查询语言：查询数据
• DCL(Data Control Language) 数据控制语言：和数据库用户，以及用户角色相关
• CCL (Cursor Control Language) 指针控制语言 ：在数据库中编写数据库代码时使用，由于方言太多，实际开发中涉及的很少
• TPL(Transaction Processing Language) 事务处理语言：和事务处理相关

3.2.1 DDL

DDL用于创建数据库，创建数据表(表结构)，删除数据库，修改数据库和数据表结构。

语法：

--连接数据库
mysql -u用户名 -p密码  

--创建数据库 (常用)
CREATE DATABASE [if not exists] database01;

--  删除数据库
drop database [if exists] database01; 

--使用数据库;
USE database01;

--展示数据库
show databases; 

创建表的格式（重音符 关键字 取消关键字）

create table 表名(
  列名 类型,
  列名 类型,
  列名 类型
)

字段的常见类型

• int ：整数类型

• float：单精度浮点数

• double：双精度浮点数

• bit：位类型（里面只能存0和1)

• decimal(m,d): 浮点类型数，没有精度丢失，m表示长度(整数+小数的长度) d表示小数位 如decimal(11,2) 表示9位整数 2位小数

• char(length) :字符串，length最大为255，如char(10)， 添加的数据为’abc’，长度是10，abc+空格补齐

• varchar(length) : 可变字符串 , length最大为255，如 varchar(10)，添加的数据为 abc 长度就为3

• date：日期格式，yyyy-MM-dd

• datetime：日期格式，yyyy-MM-dd HH:mm:ss 占用8个字节

• timestamp：日期格式，yyyy-MM-dd HH:mm:ss 取值范围1970-2038，占用4个字节，性能优于datetime

-- 创建表
CREATE TABLE stu(
 id INT , 
 `name` VARCHAR(20),
 english DOUBLE,
 math DOUBLE ,
 chinese DOUBLE 
);

-- 删除表
drop table stu;

--清除表中数据，并且重新构建表结构
truncate table stu; 

3.2.2 DML

DML操作的是表中的数据。

3.2.2.1插入数据

• insert into 表名称(列名称,列名称,列名称,列名称…) values(列值,列值,列值,列值,列值,列值…);

insert into student(id,name,age,sex,address,math,english) values(1,'张三',55,'男','北京',66,78);

• 批量插入 insert into 表名称(列名称,列名称,列名称,列名称…) values(列值,列值,列值,列值,列值,列值…),(列值,列值,列值,列值,列值,列值…),…;

INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
			(1,'张三',55,'男','北京',66,78),
			(2,'李四',45,'女','深圳',98,87),
			(3,'王五',55,'男','天津',56,77);

添加时的注意事项:

1. 数字不需要加引号
2. 数据库中不区分大小写,字符串使用单引号（sql语句中不支持双引号）
3. 缩写语法的时候不能省略任何字段
4. 值和字段的顺序 类型 必须匹配

3.2.2.2 删除数据

• delete from 表名称 [where 条件]

注意：

1. 当不指定where条件时，是删除表中全部数据
2. 删除不能删除列，只能删除行

常用条件：

条件	描述
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
!=	不等于
between 值1 and 值2	在值1和值2之间(包括值1和值2)
in(值1,值2…值n)	在值1,值2,…值n之中任意值都满足
is null	为null
is not null	不为null

条件拼接：

关键字	描述
and	条件1与条件2
or	条件1或条件2
not	非条件1

--删除student表
delete from student;

--删除id为1的数据
delete from student where id = 1;

--删除id为1和id为2的学生
delete from student where id = 1 or id = 2;

--删除年龄是20,23,50的学生
delete from student where age in (20,23,50);

truncate table 表名称和delete from 表名称的区别：

• truncate语法属于DDL，它是把表删除了，然后重建的表结构，相当于把整个文件夹删除了，再重新建了个同名的空文件夹
• delete from语法算DML，它只是把表中数据全部删除了，相当于把文件夹的内容都逐一删除，没有操作文件夹

3.2.2.3 修改数据

语法：

update 表名称 set 列名称1=列值,
				  列名称2=列值,
				  列名称3=列值,
				  列名称n=列值,
				  ....
				  [where条件]

注意：如果没加条件，则是更新所有数据

--这条语句是错误的。它会把表中所用学生的年龄都改成31,因为没有加条件。
update student set age = 31;

--正确的写法
update student set age = 31 where id = 7;

--更新名称为德玛西亚的用户，年龄为21，数学成绩为75，英语成绩为60
update student set age=21 ,math=75,english=60 where name = '德玛西亚';//sql语句中，不支持双引号

--更新所有女同学的英语成绩为原成绩+10
update student set english = english+10 where sex = '女';

3.2.3 DQL

DQL用于查询数据。

3.2.3.1 基础语法

• select 列名1,列名2,列名n… from 表名 where 条件

查询全部列可以简写为：

• select * from 表名 where 条件

--查询所有学生的姓名，英语成绩和数学成绩
select name,english,math from student;

--查询所有学生
select * from student;

--查询所有学生年龄
select age from student;

去重操作

• 关键字：distinct
• 用法：select distinct 列名 from 表名 where 条件

--查询所有学生年龄，去重操作
select distinct age from student;

别名

• 作用：给查询结果的列名起别名
• 标准格式：select 列名 as 别名 … from 表名 where 条件
• 简写格式：select 列名 别名 … from 表名 where 条件

--查询所有学生的姓名，英语成绩和数学成绩，用中文显示列名
select name as 张三,english as 英语成绩 ,math as 数学成绩 from student;
select name 张三,english 英语成绩 ,math 数学成绩 from student;

3.2.3.2 基本条件查询

基础运算

--查询math分数大于80分的学生
select * from student where math > 80;

--查询english分数小于或等于80分的学生
select * from student where english <= 80;

--查询age等于20岁的学生
select * from student where age = 20;

--查询age不等于20岁的学生
select * from student where age != 20;

--查询英语成绩为null的学生
select * from student where english is null;

逻辑运算

--查询age大于35且性别为男的学生(两个条件同时满足)
select * from student where age > 35 and sex = '男';

--查询age大于35或性别为男的学生(两个条件其中一个满足)
select * from student where age > 35 or sex = '男';

--查询id是1或3或5的学生
select * from student where id in(1,3,5);
			
--查询english成绩大于等于75，且小于等于90的学生
select * from student where english between 75 and 90;

3.2.3.3 模糊查询

关键字 like
特殊符号：

• % 表示任意内容
• _表示占位符，有一个就代表要有一个字符

用法：

• like ‘固定内容%’ 以固定内容开头
• like ‘%固定内容’ 以固定内容结束
• like ‘%固定内容%’ 包含固定内容

--查询姓张的学生
select * from student where name like '张%';

--查询姓名中包含'三'字的学生
select * from student where name like '%三%'

--查询姓张，且姓名有三个字的学生
select * from student where name like '张__';

3.2.3.4 排序查询

排序语法：

order by 列名  asc | desc
asc 升序（默认值）
desc 降序

--使用年龄降序排序
select * from student order by age desc;

--在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
select * from student order by age desc,math desc;

3.2.3.5 聚合查询

聚合查询是把表中指定列的内容聚合到一起。

聚合函数：

• sum(列名) 求和
• max(列名) 求最大值
• min(列名) 求最小值
• avg(列名) 求平均值
• count(列名) 获取表中列名对应列的数据个数，只有不为null的值才会被计数
• count(*) 获取表中总行数

--查询学生总数
select count(id) from student ;
select count(*) from student ;

--查询年龄大于40的学生人数
select count(*) from student where age > 40;

--查询数学成绩平均分
select avg(math) from student;

--查询数学成绩最高分
select max(math)  from student;

--查询数学成绩最低分
select min(math)  from student;

3.2.3.6 分组查询

分组的含义：把具有相同特征的数据分成一组。

语法：

group by 列名

分组后加条件：

having 条件

组合用法：

group by 列名 having 条件

注意：使用分组查询之后，查询的列名就不能再是*（全部列）了，只能是被分组的列，以及聚合函数。

--按性别分组
select sex from student group by sex;
--查询男女各多少人
select sex,count(*) from student group by sex;
--查询年龄大于25岁的人,按性别分组
select sex,count(*) from student where age > 25 group by sex;

--统计每组的人数 , 查询年龄大于25岁的人
select age,count(*) from student where age > 25  group by age;    先过滤条件后分组
select age,count(*) from student  group by age having age > 25;	  先分组再过滤条件

--按年龄分组 , 统计每组的人数 ,并只显示年龄人数大于等于2的数据
select age,count(*) from student  group by age having count(*) >= 2; 

having和where对比:

• having用于分组后的筛选，如果被筛选的列是原来表中不存在的列,则只能用having
• where是在分组前过滤条件

3.2.3.7 分页查询

分页：把数据分批次查询出来

分页的思想：拿时间换空间

分页的意义：解决数据量太大，一次读取完可能会导致内存溢出；分为多次读取，可以减少内存消耗

语法格式：

limit 开始记录数索引,每次查询的条数
limit ------------- mysql方言
第一个参数：查询数据的开始记录索引，从0开始
第二个参数：每次查询多少条数据出来

-- 查询学生表中前5条数据
select * from student limit 0,5;

计算开始记录索引的公式：（当前页-1）* 每页显示的条数
计算总页数： 总条数 % 每页显示的条数 == 0 ？总条数 / 每页显示的条数 : 总条数 / 每页显示的条数+1

3.2.3.8 单表查询总结

select 列名 as 别名 from 表名 where 条件 group by 分组列名  order by 排序列名  limit 分页条件

3.3 数据库备份与恢复

#1.快速创建一张摸一样的表
CREATE TABLE newStu LIKE student;

#2.复制数据- 蠕虫复制
INSERT INTO newstu SELECT * FROM student;

3.4 数据完整性

3.4.1 数据完整性概述

数据完整性的作用是保证数据的完整和准确，防止用户的误输入引起数据错误。

实现数据完整性的方式就是添加约束。

3.4.2 约束

约束就是一种规定或者规范，规定数据书写的格式。如果数据库没有约束那么可以任意添加数据，将会导致数据冗余现象。

约束的种类：

• 主键约束 使用primary key 主键=唯一+非空(一张表只有一个主键)
• 唯一约束 使用unique 字段，加入唯一约束后只能存在一个值，不能重复
• 非空约束 使用not null 字段，加入非空约束后不能为null值
• 默认值约束 使用default 值, 不加入数据的时候 默认为default定义的值

3.4.3 实体的完整性

为了保证实体的完整性，即保证表中每条数据的唯一性，这就需要通过添加主键的方式使用主键约束。

主键特点：唯一且非空

添加方式(创建表时)：

第一种：
create table 表名(
		列名 数据类型   primary key ,
		列名 数据类型,
		列名 数据类型);

第二种：
create table 表名(
		列名 数据类型,
		列名 数据类型,
		列名 数据类型,
		primary key(列名));
		
第一种：只能给某一个列设为主键
第二种：可以在括号内写多个列名，用逗号分隔

添加语法(修改表时)：

alter table 表名 add primary key(列名)

主键自增长：无需设置主键，由数据库维护主键的值，前提主键必须是整型。
使用方式：

create table 表名(
	列名 数据类型   primary key auto_increment,
	列名 数据类型,
	列名 数据类型
);

3.4.4 域完整性

域完整性是保证每一列数据的准确性。

域完整性的几个条件：

• 数据类型限定，如int类型只能输入数值，date类型只能是符合格式的日期
• 非空限定(not null) 不能为空
• 唯一限定(unique) 值唯一，不能重复

如以下代码所示：

create table stu(
				id int primary key auto_increment,
				name varchar(20) not null unique,
				gender varchar(10),
				age int,
				birthday date,
				telephone varchar(20)
			);