数据库基础-Mysql基础

数据库基础

• 数据库相关的基本概念
• 数据库管理系统的基本概念
• 关系数据库基本知识
• MySql安装及配置
• MySQL数据库常用命令

数据库概念

持久化的概念

数据集存储的方法：

• 用大脑记住数据
• 写在纸上
• 写在计算机内存中
• 写成磁盘文件

企业数据存储面临的问题

• 存储大量数据
• 大量数据的检索和访问
• 保持数据信息的一致、完整
• 数据共享和安全
• 通过分析整合，产生新的有用的信息

数据库介绍

是用户存放数据、访问数据、操作数据的存储仓库，用户的各种数据被有组织的存放在数据库中。可以随时被有权限的用户查询、统计、添加、删除、和修改。

是长期存储在计算机内的，有组织、可共享的数据集合。数据库系统是由普通的文件系统发展而来
的。

数据库系统具有较高的数据独立性，即不依赖于特定的数据库应用程序；

数据库系统的数据冗余小，可以节省数据的存储空间；

另外数据库系统还很容易实现多个用户的数据共享。

数据库的发展史

• 萌芽阶段-----文件系统

  • 使用磁盘文件来存储数据

• 初级阶段-----第一代数据库

  • 出现了网状模型、层次模型的数据库

• 中级阶段-----第二代数据库

  • 关系型数据库和结构化查询语言

• 高级阶段------新一代数据库

  • “关系-对象”型数据库

数据库和应用程序

数据库管理系统 (DBMS)

• 数据库系统成熟的标志就是数据库管理系统的出现。数据库管理系统(DataBase Managerment System，简称DBMS)是管理数据库的一个软件，它充当所有数据的知识库，并对它的存储、安全、一致性、并发操作、恢复和访问负责。是对数据库的一种完整和统一的管理和控制机制。数据库管理系统不仅让我们能够实现对数据的快速检索和维护，还为数据的安全性、完整性、并发控制和数据恢复提供了保证。数据库管理系统的核心是一个用来存储大量数据的数据库。
• DBMS是所有数据的知识库，并对数据的存储、安全、一致性、并发操作、恢复和访问负责。
• DBMS有一个数据字典（有时被称为系统表），用于贮存它拥有的每个事物的相关信息，例如名字、结构、位置和类型，这种关于数据的数据也被称为元数据（metadata）。
• 一个真正的数据库系统由硬件和软件两个方面构成。比如我们要使用Oracle数据库，需要安装Oracle公司提供的数据库服务器软件和一台用于安装数据库管理系统的高性能的计算机服务器。
• 数据库系统的发展经历了层次模型、网状模型及关系模型几个阶段。当今应用最普遍的是关系型数据库管理系统。目前，市场上流行的几种大型数据库，如Oracle、DB2、Sybase、MS SQL Server、MySql等都是关系型数据库管理系统。
  

关系数据库概念

• 关系模型数据库采用表组织数据（表称为“关系”），一个数据库由许多个表组成，多个表数据之间存在着关系，在这些表上的数据操作依赖于关系，关系用来描述多个表之间的数据依存，包括了一对一、一对多、多对多的关系

• 桌面型的关系模型数据库

  ​ MS ACCESS

• 网络型的关系模型数据库

  • Oracle
  • SQLite
  • SQL Server
  • Mysql

概念模型：基于客户的想法和观点所形成的认识和抽象。

• 实体(Entity):客观存在的、可以被描述的事物。例如员工、部门。
• 属性(Attribute):用于描述实体所具有的特征或特性。如使用编号、姓名、工资等来属性来描述员工的特征。
• 关系(Relationship):实体之间的联系。
  • 一对一: 人 和 身份证
  • 一对多: 班级 和 学生
  • 多对多: 学生 和 课程

数据模型：也叫关系模型，是实体、属性、关系在数据库中的具体体现。

• 关系数据库:用于存储各种类型数据的”仓库”，是二维表的集合。
• 表:实体的映射
• 行和列:行代表一个具体的实体的数据。也叫一条记录。列是属性的映射，用于描述实体的。
• 主键和外键。
  

MySQL

MySQL环境搭建

安装phpstudy

启动mysql服务

添加环境变量

D:\phpstudy_pro\Extensions\MySQL5.7.26\bin

MySQL简介

常用字符集

• 字符集(charset):是一套文字符号及其编码的集合

• ASCII

  • 单字节7位编码，共可表示127个字符
  • 大小写英文字母，阿拉伯数字和标点符号以及33个控制符号
  • 美式字符集

• GB2312

  • 双字节编码
  • 包括对简体中文字符的编码，一共收录了7445个字符，包括6763个汉字和682个其他符号，未收录繁体中文汉字和一些生僻字
  • 与ASCII字符编码兼容
  • 早期中文标准

• GBK

  • 双字节编码
  • 完全兼容GB2312，收录了21886个字符，包括繁体和简体中文字符
  • GB2312的扩展，使用广泛

• GB18030

  • 2或4字节编码
  • 与ASCII编码兼容
  • 向下兼容GBK和GB2312标准，收录了27484个汉字
  • 中文新标准，但目前使用不广泛

• Unicode字符集包括全世界所有语言和文字符号

  • 它为每种语言中的每个字符设定了统一并且唯一的二进制编码

• Unicode的常用实现

  • UTF-16：用固定2字节存储一个Unicode字符
    • Java和windows XP内使用
  • UTF-8：用1-6个字节存储一个Unicode字符
    • 互联网和UNIX/Linux广泛支持，MySql Server内部也使用
    • 英文字母、数字和符号用1个字节存储，中文用3个字节存储

字符集选择

• 支持国际化：选择Unicode字符集，目前就是UTF-8

• 中文数据量大，选择GBK

SQL语言

• 结构化查询语言(Structured Query Language)
• 在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。
• 使用SQL语句，程序员和数据库管理员可以完成如下的任务
  • 改变数据库的结构
  • 更改系统的安全设置
  • 增加用户对数据库或表的许可权限
  • 在数据库中检索需要的信息
  • 对数据库的信息进行更新

SQL语句的书写规范

• 在数据库系统中，SQL语句不区分大小写(建议用大写) 。
• 但字符串常量区分大小写。
• SQL语句可单行或多行书写，以“;”结尾。
• 关键词不能跨多行或简写。
• 用空格和缩进来提高语句的可读性。
• 子句通常位于独立行，便于编辑，提高可读性。

mysql注释符

MySQL基础命令

连接mysql数据库

mysql -u root -p 	#登录本地数据库
mysql -u root -p -h 127.0.0.1 -P 3306	#登录指定IP、端口数据库，可用于远程登录

Mysql数据库操作

SHOW DATABASES #查看所有数据库名

CREATE DATABASE #创建数据库

• 语法：
  • CREATE DATABASE [IF NOT EXISTS]数据库名;
• 功能：用给定的名字创建一个数据库
• 如果数据库已经存在，发生一个错误。

DROP DATABASE

• 语法：
  • DROP DATABASE [IF NOT EXISTS]数据库名;
• 功能：删除数据库中得所有表和数据库
• 要小心使用这个命令

使用 USE 选用数据库

语法：

• USE 数据库名;
• 功能：把指定数据库作为默认(当前)数据库使用，用于后续语句。

库操作常用语句

create database mytest; # 创建名为mytest的数据库
alter database mytest character set utf8; # 修改数据库编码为utf8
show create database mytest; # 查看数据库状态
drop database mytest; # 删除数据库
select database();# 查询当前使用的数据库是哪一个

MySQL 数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
INT或INTEGER	4Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
FLOAT	4Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823466 351 E+38)	0，(1.175 494 351E-38，3.402 823466 E+38)	单精度浮点数值
DOUBLE	8Bytes	(-1.797 693 134 862 315 7E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858507 201 4 E-308，1.797 693 134 862315 7 E+308)	双精度浮点数值

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串

日期和时间类型

类型	大小( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间

Mysql 表的操作

创建数据表

• 创建MySQL数据表需要以下信息：
  表名
  表字段名
  定义每个表字段
• 语法： CREATE TABLE table_name (column_name column_type);

示例：

CREATE TABLE IF NOT EXISTS `library`(
`id` INT UNSIGNED AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

解析：

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。

• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。

• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

• ENGINE 设置存储引擎，CHARSET 设置编码。

删除数据表

DROP TABLE table_name ;

表操作常用语句

create table tablename(column_name1 colunm_type1, column_name2 colunm_type2); #创建表
alter table 表名 drop column 列名; # 删除指定列
alter table 表名 add column 列名 column_type; # 添加列
alter table 表名 change 旧列名 新列名 新列名属性; # 修改列名
alter table 表名 modify 列名 新的列名属性; # 修改列属性
show create table 表名; # 查看创建表的语句
desc 表名; # 查看表结构

增删改查

#插入数据
	insert into 表名 values(值1, 值2, ......);
	insert into table_name (列1, 列2,...) VALUES (值1, 值2,....); # 插入一条语句
	insert into table_name (列1, 列2,...) VALUES (值1, 值2,....),(值1, 值2,....);#同时插入多条语句

查询表中数据
	select * from 表名; # 查询表中所有内容
	select name from users; # 查询 users 表中 name 值；
	select * from users where age<30; # where条件语句，可用 "=" ">" "<" "!=" ">=" "<=" 可以使用 and or指定多个条件
	select * from users limit 1,2; # limit 第一个参数是指定开始位置，第二个参数是个数

删除数据
	delete from 表名 [WHERE Clause]; # 删除指定条件的语句
	delect from 表名; # 删除所有数据
	TRUNCATE 表名; # 删除所有数据，保留表的结构，重新创建表

更新数据
	update 表名 SET 字段1=new-value1, 字段2=new-value2 [WHERE Clause]; # 指定条件更新语句

where && like 模糊查询

where条件语句，可以写 “=” “>” “<” “!=” “>=” “<=” 可以使用 and or 指定多个条件

like 子句可以用来修饰 where 条件，

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 “author = ‘RUNOOB.COM’”。

但是有时候我们需要获取 author 字段含有 “COM” 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

• 可以在 WHERE 子句中指定任何条件。
• 可以在 WHERE 子句中使用LIKE子句。
• 可以使用LIKE子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• 可以在 DELETE 或 UPDATE 命令中使用WHERE…LIKE 子句来指定条件。

示例：

mysql> select * from users where username like 'admin%';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 8 | admin | admin |
| 9 | admin1 | admin1 |
| 10 | admin2 | admin2 |
| 11 | admin3 | admin3 |
| 14 | admin4 | admin4 |
+----+----------+----------+
5 rows in set (0.00 sec)

UNION 联合查询

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中使用 union 连接的前后两条语句查询内容的列数要相等

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

示例：

mysql> select * from users where id > 10 union select * from users where id < 3;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 11 | admin3 | admin3 |
| 12 | dhakkan | dumbo |
| 14 | admin4 | admin4 |
| 1 | Dumb | Dumb |
| 2 | Angelina | I-kill-you |
+----+----------+------------+
5 rows in set (0.00 sec)

ORDER BY 排序

order by 指定以哪一列数值进行排序，默认为 ASC 升序，可选 DESC 降序排列

select * from users order by 3 # 以第三列数组进行升序排列
select * from users order by id DESC; # 以 id 列数值进行降序排列

常用函数

current_timestamp() # 获取当前时间
now() # 获取当前时间
version(); # 查询当前数据库版本
@@version; # 查询数据库版本
user(); # 查看当前用户
database();# 查询当前使用的数据库

函数	描述	实例
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串select concat(id,username,password) from users;
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	SELECT IF(1 > 0,‘正确’,‘错误’) ->正确
group_concat(s1,s2)	将指定列所有字段值拼接并返回一行数据	select group_concat(username,0x3a,password) from security.users
sleep(n)	延时函数，等待n秒	select version() and sleep(5);