MySQL数据库初体验
数据库的基本概念
1、数据（Data）
• 描述事物的符号记录
• 包括数字，文字，图形，图像，声音，档案记录等
• 以“记录”形式按统一的格式进行存储
2、表
• 将不同的记录组织在一起
• 用来存储具体数据
3、数据库
• 表的集合，是存储数据的仓库
• 以一定的组织方式存储的相互有关的数据集合
4、数据库管理系统（DBMS）
• 是实现对数据库资源有效组织、管理和存取的系统软件
5、数据库系统
• 是一个人机系统，由硬件、os、数据库、DBMS、应用软件和数据库用户组成
• 用户可以通过DBMS或应用程序操作数据库
6、访问数据库的流程
数据库系统发展史
1.第一代数据库
• 自20世纪60年代起，第一代数据库系统问世
• 是层次模型与网状模型的数据库系统
• 为统一管理和共享数据提供了有力的支撑
2.第二代数据库
• 20世纪70年代，第二代数据库—关系数据库开始出现
• 20世纪80年代，IBM公司的关系数据库系统DB2问世，开始逐步取代层次与网状模型的数据库，成为行业主流
• 到目前为止，关系数据库系统仍占领数据库应用的主要地位
3.第三代数据库
• 自20世纪80年代开始，适应不同领域的新型数据库系统不断涌现
• 面向对象的数据库系统，实用性强，适应面广
• 20世纪90年代后期，形成了多种数据库系统共同支撑应用的局面
• 一些新的元素被添加进主流数据库系统中
例如，Oracle支持的 “关系-对象"数据库模型
当今主流数据库介绍
1.SQL Server (微软公司产品)
• 面向Windows操作系统
• 简单、易用
2、Oracle (甲骨文公司产品)
• 面向所有主流平台，
• 安全、完善，操作复杂
3、DB2 (IBM公司产品)
• 面向所有主流平台
• 大型、安全、完善
4.MySQL (甲骨文公司收购)
• 免费、开源、体积小
关系数据库
• 关系数据库系统是基于关系模型的数据库系统
• 关系模型的数据结构使用简单易懂的二维数据表
• 关系模型可用简单的“实体关系”(E-R) 图来表示
• E-R图中包含了实体(数据对象)、关系和属性三个要素
1、实体
• 也称为实例，对应现实世界中可区别于其它对象的“事件”或“事物”
如银行客户、银行账户等
2、属性
• 实体所具体的某一特性，一个实体可以有多个属性
如“银行客户”实体集中的每个实体均具有姓名、住址、电话等属性
3、联系
• 实体集之间的对应关系称为联系，也称为关系
如银行客户和银行账户存在“储蓄”的关系
所有实体及实体之间联系的集合构成一个关系数据库
关系数据库的存储结构是二维表格
在每个二维表中
| • 每一行称为一条记录，用来描述一个对象的信息 |
| • 每一列称为一个字段，用来描述对象的一个属性 |
4、关系数据库
• Oracle , MySQL
• SQLServer、Sybase
• Informix、access
• DB2、FoxPRO
5、关系数据库的应用
12306用户信息系统------------Oracle,MySQL
淘宝账号系统---------------------SQLServer、Sybase
联通手机号信息系统----------- Informix、access
银行用户账号系统---------------DB2、FoxPRO
网站用户信息系统---------------PostgreSQL
非关系数据库
非关系数据库也被称作NoSQL (Not Only SQL)
存储数据不以关系模型为依据，不需要固定的表格式
1、非关系型数据库的优点
• 数据库可高并发读写
• 对海数据高效率存储与访问
• 数据库具有高扩展性与高可用性
2、常用的非关系数据库
Redis、mongoDB等
3、总结
非关系数据库大部分以键值对的方式进行存储（例如：name=xxx），之间没有特定的联系，安全性不是很高，非关系数据库很多将数据是写在内存当中，所以读写速度快
MySQL的介绍
1、MySQL数据库介绍
一款深受欢迎的开源关系型数据库
Oracle旗下的产品
遵守GPL协议，可以免费使用与修改
特点：
性能卓越、服务稳定
开源、无版权限制、成本低
多线程、多用户
基于C/S（客户端/服务器）架构
安全可靠
2、MySQL商业版与社区版
MySQL商业版是由MySQL AB公司负责开发与维护，需要付费才能使用
MySQL社区版是由分散在世界各地的MySQL开发者、爱好者一起开发与维护，可以免费使用
两者区别
商业版组织管理与测试环节更加严格，会比社区版更稳定
商业版不遵守GPL，社区版遵从GPL可以免费使用
商业版可获得7*24小时的服务，社区版则没有
3、 MySQL产品阵营
第一阵营：5.0-5.1阵营，可说是早期产品的延续
第二阵营：5.4-5.7阵营，整合了MySQL AB公司、社区和第三方公司开发的存储引擎，从而提高性能
第三阵营：6.0-7.1阵营，就是MySQL Cluster版本， 为适应新时代对数据库的集群需求而开发
下载网址:
http://www.dev.mysql.com/downloads
编译安装MySQL数据库
MySQL数据库自动补全命令的三种方式
方式一：临时使用自动补全功能
mysql -u root -p --auto-rehash
方式二：永久使用自动补全功能
在/etc/my.cnf 文件中 mysql段里添加一行
vi /etc/my.cnf
[mysql]
auto-rehash
方法三：使用软件mycli实现自动补全功能
安装相关依赖软件（需要配置yum官方或者阿里源进行安装）
yum -y install epel-release
yum -y install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gdbm-devel db4-devel libpcap-devel xz-devel gcc
下载python安装包（自己下载过可以忽略此步骤）
wget https://www.python.org/ftp/python/3.9.0/Python-3.9.0.tgz
创建目录，并添加配置
mkdir ~/.pip/
cat > ~/.pip/pip.conf <<EOF
[global]
index-url = https://mirrors.aliyun.com/pypi/simple/
[install]
trusted-host=mirrors.aliyun.com
EOF
安装相关依赖软件，解压python软件包并进行编译安装
yum -y install libffi-devel zlib* python-devel openssl-devel
tar -zxvf Python-3.9.0.tgz
cd Python-3.9.0/
./configure --prefix=/usr/local/python --with-ssl
make && make install
优化路径，安装pip3
yum -y install libffi-devel zlib* python-devel openssl-devel
tar -zxvf Python-3.9.0.tgz
cd Python-3.9.0/
./configure --prefix=/usr/local/python --with-ssl
make && make install
MySQL数据库管理
数据库–>数据表–>行（记录）：用来描述一个对象的信息
数据库–>数据表–>列（字段）：用来描述对象的一个属性
| 常用的数据类型 |
int ：整型
float ：单精度浮点 4字节32位
double ：双精度浮点 8字节64位
char ：固定长度的字符类型
varchar ：可变长度的字符类型
text ：文本
image ：图片
decimal(5,2) ：5个有效长度数字，小数点后面有2位
查看数据库结构
查看当前服务器中的数据库
SHOW DATABASES; #大小写不区分，分号“;”表示结束
查看数据库中包含的表
USE 数据库名;
SHOW TABLES;
查看表的结构
USE 数据库名;
DESCRIBE [数据库名.]表名;
可缩写成：DESC 表名;
查看mysql版本
（mysql -V #在linux系统中查看mysql版本）
select version(); #在mysql内查看mysql版本
SQL语句
SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。
SQL语句分类：
语句 代表的意思
DDL 数据定义语言，用于创建数据库对象，如库、表、索引等
DML 数据操纵语言，用于对表中的数据进行管理
DQL 数据查询语言，用于从数据表中查找符合条件的数据记录
DCL 数据控制语言，用于设置或者更改数据库用户或角色权
创建及删除数据库和表
创建新的数据库
CREATE DATABASE 数据库名;
创建新的表
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
删除指定的数据表
DROP TABLE [数据库名.]表名; #如不用USE进入库中，则需加上数据库名
删除指定的数据库
DROP DATABASE 数据库名;
管理表中的数据记录
向数据表中插入新的数据记录
INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);
例如：insert into ky12(id,name,score,passwd) values(1,'zhangsan',69.9,password('123456'));
#PASSWORD(‘123456’)：查询数据记录时，密码字串以加密形式显示；若不使用PASSWORD()，查询时以明文显示。
查询数据记录
SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];
查询全部数据
SELECT * FROM 表名
查询指定数据
当表里数据很多影响观看的时候 可以使用以列表方式竖向显示
select * from 表名\G;
一般要显示多少行到多少行 就取前一行开始 （列如 20-30行 就limit 19,11）
修改、更新数据表中的数据记录
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
修改一个数据
同时修改两个数据
在数据表中删除指定的数据记录
DELETE FROM 表名 [WHERE 条件表达式];
修改表名和表结构
ALTER TABLE 旧表名 RENAME 新表名;
ALTER TABLE 表名 ADD 要增加的字段；
修改字段（列）名，添加唯一键
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
删除字段
ALTER TABLE 表名 drop 字段名;
使用 if 判断 创建表并测试自增和填充
use kgc; #先进入指定的库
create table if not exists info (
id int(4) zerofill primary key auto_increment,
name char(20) not null,
sid int(18) not null unique key,
age int(3) not null,
address char(100));
分析：
#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL
数据表高级操作
克隆表，将数据表的数据记录生成到新的表中
方法一：先创建再导入
create table test01 like ky12; #通过 LIKE 方法，复制 info 表结构生成 test01 表
insert into test01 select * from ky12; #导入数据
方法二：创建的时候同时导入
create table test02 (select * from ky12);
show create table test02\G; #获取数据表的表结构、索引等信息
清空表——删除表内的所有数据
方法一：deldte删除
DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。
delete from 表名
方法二：truncate删除
TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录
创建临时表
临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表
create temporary table 表名 （ 字段 数据类型）
创建外键约束
主键表字段和外键表字段要求具有相同的数据类型，字符长度，参数属性
确保主键表的字段为主键
ALTER TABLE 主键表名ADD [constraint PK_ 主键名] primary key (主键字段);
为外键表设置外键约束并与主键表相关字段做关联
ALTER TABLE 外键表名ADD [constraint FK_ 主键名] FOREIGN KEY (外键字段) REFERENCES 主键表名(主键字段) ;
show create table 表\G #查看表约束
alter table student drop foreign key FK_proid; 解除外键约束
alter table student drop key FK_proid; 删除外键
删除外键
测试删除：从表的数据未删除，主表的数据无法删除
MySQL中6种常见的约束:
- 主键约束 (primary key)
- 外键约束 (foreign key)
- 非空约束 (not null)
- 唯一性约束（unique [keylindex])
- 默认值约束 (default)
- 自增约束 （auto_increment)
数据库用户管理
新建用户
CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
‘用户名’：指定将创建的用户名
‘来源地址’：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
‘密码’：
若使用明文密码，直接输入’密码’，插入到数据库时由Mysql自动加密；
若使用加密密码，需要先使用SELECT PASSWORD(‘密码’); 获取密文，再在语句中添加 PASSWORD ‘密文’;
若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）
### 明文创建
CREATE USER 'zjz'@'localhost' IDENTIFIED BY 'abc123';
### 密文创建
select password('abc123');
CREATE USER 'xian'@'localhost' IDENTIFIED BY password '*6691484EA6B50DDDE1926A220DA01FA9E575C18A';
查看用户信息
use mysql;
select User,authentication_string,Host from user;
创建后的用户保存在 mysql 数据库的 user 表里
重命名用户
rename user '原用户名'@'原来源地址' to '新用户名'@'新来源地址';
删除用户
drop user '用户名'@'来源地址';
修改当前登录用户密码
set password = password('密码');
修改其他用户密码
set password for '用户名'@'来源地址' = password('密码');
忘记 root 密码的解决办法
解决方案：
第一步：修改配置文件，添加配置，使登录mysql不使用授权表
第二步：重启服务，登录测试
第三步：使用update修改root密码，刷新数据库
第四步：再次修改my.conf配置文件，删除之前添加的配置命令
第五步：重新登录测试
数据库用户授权
授予权限
GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时，GRANT 语句用于修改用户信息。
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“”。例如，使用“kgc.”表示授权操作的对象为 kgc数据库中的所有表。
#‘用户名@来源地址’：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP 地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.accp.com”、“192.168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分， 则用户的密码将为空。
权限列表
all privilege权限如下:
- insert (插入数据)
- select (查询数据)
- update (更新表的数据)
- delete (删除表中数据)
- create (创建库,表)
- drop (删除库, 表)
- refernces
- index (建 立索引)
- alter (更改表属性)
- create temp orary tables
- lock tables (锁表)
- execute
- create view (创建视图)
- show view (显示视图)
- create routine (创建存储过程)
- alter routine (修改存储过程)
- event (事件)
- trigger on (创建触发器)
#允许用户zhangsan在本地查询kgc数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。
GRANT select ON kgc.* TO ' zhangsan'@'localhost' IDENTIFIED BY '123456';
#允许用户lisi 在所有终端远程连接mysql，并拥有所有权限。
GRANT ALL [PRIVILEGES] ON . TO 'lisi'@'8' IDENTIFIED BY '123456' ;
flush privileges;
浙公网安备 33010602011771号