【Mysql】Mysql基本概念和操作

一.数据库基础

什么是数据库

存储数据用文件就可以了,为什么还要弄个数据库？

文件保存数据有以下几个缺点：

1)文件的安全性问题 2)文件不利于数据查询和管理 3)文件不利于存储海量数据 4)文件在程序中控制不方便

---

数据库存储介质： 磁盘,内存,

---

为了解决上述问题,专家们设计出更加利于管理数据的东西——数据库,它能更有效的管理数据.数据库的水平是衡量一个程序员水平的重要指标

如何看待数据库:

软件角度：可以认为数据库是程序和磁盘之间的软件层,帮助用户更方便的进行文件管理.数据库实际上也是用文件保存数据的.

文件角度：数据库实际上是一个网络服务器,我们通过数据库客户端访问数据库服务器后台的数据库库表结构.

---

问:mysql或者数据库,属于哪一个层的?

属于应用层,mysql不是OS内置的,需要我们自己安装,底层一定是直接或者间接访问OS的文件接口

---

服务器,数据库,表关系

所谓安装数据库服务器,只是在机器上安装了一个数据库管理系统程序,这个管理程序可以管理多个数据库,一般开发人员会针对每一个应用创建一个数据库,为保存应用中实体的数据,一般会在数据库中创建多个表,以保存程序中实体的数据 Mysql服务器的端口号为:3306,

数据库服务器、数据库和表的关系如下

---

连接层干的事情:

1.用户验证 2.选择连接的协议(tcp,本地套接字) 3.创建线程对客户端进行服务

server层:

1.检查sql语句的语法 -> 比如sql语句的语法是否规范 2.语义分析 3.权限检查-查看当前用户是否有操作该表的权限

4.分析sql语句的查找方案,选出一个执行效率最高的方案 5.执行sql

server会将任务交给存储引擎,存储引擎去磁盘之中找到数据后,将其返回给server层

存储引擎

相当于Linux当前的文件系统,不同的存储引擎可以决定数据的存储方式,常用的有INNODB MyISAM

---

数据逻辑存储

MySQL是关系型数据库,数据存储是有逻辑的,按行列存储就是它的逻辑.通常我们把一行称之为一条记录.一列被称为属性内容.

数据库的本质就是一堆的表结构,每一个表结构就是行列式的数据

存储引擎

存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新查询数据等技术的实现方法. MySQL的核心就是插件式存储引擎,支持多种存储引擎.

查看存储引擎: show engines

---

二.SQL分类

SQL也有种类之分,因为不同的人使用SQL的需求不同.

---

三.数据库的基本操作

3.1. 连接MySQL

mysql -u 用户名 -h 连接的ip地址 -P端口号 -p 密码  #或者:mysql -uroot -p 
1

如果没写 -h默认是连接本地127.0.0.1 如果没写 -P,默认是连接3306端口号, mysql客户端可以跨网络连接服务器

如果想退出Mysql:只需要输入quit或者exit即可. 也可以直接使用 ctrl+d

---

如果想重启MySql : service mysqld restart 启动mysql：``service mysqld start`

停止mysql：service mysqld stop 查看mysql是否启动：service mysqld status

---

3.2. 查看数据库

show databases;	#查看当前的数据库有哪些
1

---

3.3. 使用具体的数据库、查看数据表

创建表之前,一定要先进入数据库

use 数据库名;		#使用数据库
show tables; 	   #查看这个数据库的表
1
2

---

3.4. 创建数据库

创建一个数据库:本质就是在在指定的数据库路径下,建立一个目录文件

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] 
 
create_specification: 
[DEFAULT] CHARACTER SET charset_name 
[DEFAULT] COLLATE collation_name
1
2
3
4
5

• 大写的表示关键字

• []表示可选项 if not exists表示:该数据库不存在才创建

• CHARACTER SET：指定数据库采用的字符集 COLLATE：指定数据库字符集的校验规则

如果创建数据库没有指定字符集和校验规则时,首先是按配置文件中的来,如果没有,使用默认字符集：utf8,校验规则是：utf8_ general_ ci.

---

例子:如果系统没有test的数据库,则创建一个使用utf8字符集,并附带校验规则的名字为test数据库,如果有则不创建.

创建数据库以后,就会在对应的目录下创建对应的数据库目录,之后创建的表都会保存在/var/lib/mysql/test/这个目录下.

我们的默认配置文件中:datadir = /var/lib/mysql 数据库中的库和表都会存在该路径下

---

创建数据库案例

• 创建一个名为db1的数据库

create database db1;
1

当我们创建数据库没有指定字符集和校验规则时,系统使用默认字符集：utf8,校验规则是：utf8_general_ ci

• 创建一个使用utf8字符集的 db2 数据库

create database db2 charset=utf8;
1

• 创建一个使用utf字符集,并带校对规则的 db3 数据库

create database db3 charset=utf8 collate utf8_general_ci;
1

---

3.5. 删除数据库

删除数据库, 本质就是在Linux当中把目录删掉

drop database (if exists) 数据库名;
1

执行删除之后的结果:

• 数据库内部看不到对应的内容.
• 对应的数据库文件夹被删除,级联删除,里面的数据表全部被删.

---

3.6. 查看当前所在的数据库

select database();
1

3.7. 查看已经创建好的数据库的创建语句

show create database 数据库名;
1

说明:

• MySQL 建议我们关键字使用大写,但是不是必须的
• 数据库名字的反引号 ’ ’ ,是为了防止使用的数据库名刚好是关键字
• /*!40100 default… */ 这个不是注释,表示当前mysql版本大于4.01版本,就执行这句话

---

3.8. 查看当前字符集

show variables like '%character%';
1

字符集主要是控制用什么语言,比如utf8就可以使用中文

---

常见字符集:

1)ASCII字符集:每个字符有1个表示方式,比如字符a对应的值就是97,采用的是一个字节的低7位标识字符,最高位为0

2)LATIN1字符集:相对于ASCII字符集做了一个拓展,启用了最高位

3)GBK字符集:支持中文,字符可以使用一个字节,也可以使用两个字节

4)UTF8:Unicode字符集,可以表示更多的语言,采用的是1-4字节

---

我们创建数据库的时候,就可以指定字符集,表示我们用哪种字符集来存储数据

create database 数据库名 charset = 字符集
1

---

如果字符集不匹配可能导致乱码问题:乱码的来源,本质就是因为前后端字符集不匹配

所以对于字符集的设置,尽量在数据链路当中的每一个点都保持一致,

---

创建数据库的时候,尽量将数据的字符集设置成utf8,4.1以上的版本默认字符集为latin1,如果想修改默认字符集,可以修改/etc/my.cnf的配置文件： 如果改了配置文件的内容,就需要重启Mysql

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip-grant-tables#不需要密码直接登录
max_connections=200#运行最大连接数
max_connect_errors=10#运行连接失败的次数.这也是为了防止有人从该主机试图攻击数据库系统
character-set-server=utf8#服务端使用的字符集默认为utf-8

[mysql]
default-character-set=utf8#客户端使用的字符集默认为utf8

[client]
port=3306#客户端默认端口号为3306
1
2
3
4
5
6
7
8
9
10
11
12
13
14

其中:datadir指明的路径就是MySQL各种数据保存的位置.可以简单理解：

• 创建库就是在该目录下创建一个目录；
• 创建表就是在库目录下创建对应的文件.

---

3.9. 修改数据库

对数据库的修改主要指的是修改数据库的字符集,校验规则

alter database 数据库名称 (alter_spacification)
1

其中alter_spacification就是对字符集和校验规则的更改: character set charset_name collate collation_nam

例子:修改字符集为gbk

当然上述也可以写成:alter database test charset=gbk

---

3.10查看连接情况

数据库本身就是一个客户端,查看谁在访问数据库 db为NULL 证明当前没有选择使用数据库

• 可以告诉我们当前有哪些用户连接到我们的MySQL,如果查出某个用户不是你正常登陆的,很有可能你的数据库被 人入侵了。以后大家发现自己数据库比较慢时,可以用这个指令来查看数据库连接情况

show processlist;
1

id : ID标识,要kill一个语句的时候很有用 user:当前连接用户 host:显示这个连接从哪个ip的哪个端口发出

db:数据库名 command:连接状态,一般是休眠(sleep),查询(query),连接(connect)

time :连接持续时间,单位是秒 state：显示当前sql语句的状态 info:显示这个sql语句

---

四.校验规则对数据库的影响

4.1查看数据库支持的字符集校验规则

show variables like '%collation_database%';
1

校验规则影响了用户对数据查询的排序以及是否对大小写敏感.,不同字符集有不同的校对规则,每一个字符集都有一个默认的校对规则 ,比如：uft8默认为utf8_general_ci.

其中:后缀为_bin:区分大小写 后缀为_ ci:不区分大小写

---

数据存储时采用的规则是字符编码规则utf8,提取数据时采用的校验规则是utf8_general_ci.

校验规则就是如何看待数据库中的数据,显然二者应该一一对应,否则可能出现乱码.校验规则还会影响条件查询结果和排序结果

---

五.对数据表的操作

5.1. 查看当前数据库里面的表

show tables;
1

5.2. 创建表

create table [if not exists] 表名字 ( 
	fileld1 datatype,  #file表示列名 datatype表示列的类型
    fileld2 datatype,
	fileld3 datatype
)[character set 字符集 collate 校验规则 engine 存储引擎];
1
2
3
4
5

fifield 表示列名 datatype:表示列的类型

character set 字符集,如果没有指定字符集,则以所在数据库的字符集为准

collate 校验规则,如果没有指定校验规则,则以所在数据库的校验规则为准

例子:

create table users ( 
id int, 
name varchar(20) comment '用户名', 
password char(32) comment '密码', 
birthday date comment '生日' 
) character set utf8 engine MyISAM;  #users 表存储引擎是 MyISAM 
1
2
3
4
5
6

其中可以加上comment选项:用来做这一列的说明,相当于是注释

---

注意:不同的存储引擎,创建表的文件不一样,users 表存储引擎是 MyISAM ,在Linux下对应的目录下中有三个不同的文件,分别是：users.frm :表结构 users.MYD:表数据 users.MYI:表索引

---

5.2.1. 查看已经创建好的表的创建语句

show create table 表名字;
#显示详细过程,可能会有特殊提示,所以更建议使用下面的方式
show create table 表名字\G;
1
2
3

---

5.3. 查看表结构

desc 表名称;  
1

Field:字段名称. Type:字段类型. Null:value是否可以为空. Key:索引类型. Default:默认值. Extra:额外属性

5.4.查看表内容

select * from 表名称; 
1

---

5.4. 删除表,删除列

drop table 表名称; #删除整张表
alter table 表名称 drop 字段的名称;	#删除表的某一列字段,该列的数据也随之删除.
1
2

---

5.5. 新增列

alter table 表名称 add 新增列的名称 字段的类型 after 字段名称; 	#after表示在谁后面新增,如果不加,默认新增在最后一列
1

只能添加在后面,不能添加到前面 只能after

5.6. 修改列的类型

alter table 表名称 modify 字段名称 字段的类型
1

• modify是覆盖式的修改

5.7. 表的重命名

alter table 表名称 rename to 重命名后的名字 #有没有to都可以
1

5.8. 列的重命名

alter table 表名称 change 字段名 修改后的名称 字段属性
1

5.9. 插入数据

全列增加

insert into 表名称 values(表字段对应的值)
1

指定列进行插入

insert into 表名称 (表中指定列的名称,...) values(指定表字段对应的值...)
1

一次性插入多行数据

insert into 表名称 (表中列的名称,...) values(指定表字段对应的值...),(指定表字段对应的值...);
#可以简写为:
insert into 表名称 values(表字段对应的值),(表字段对应的值)  #此时必须是全列插入！否则不能报错！
1
2
3

---

---

导入别人的库:

1)先用rz语句,把sql文件导入到Linux上,或者直接拖拽到Linux上

2)然后在当前路径下启动mysql,在mysql当中导入sql语句:

• source ./sql文件名字

然后mysql就会帮你执行这个文件下的sql语句,

---

这也叫做数据恢复！ 把所有的sql语句记录下来,然后导入

补充:

• mysql服务: 本质是网络服务进程和文件的关系
• 客户端只是把语句传送给服务器,服务进程帮我们进行各种文件操作
• mysql本质也是一个网络服务
• mysql连接成功也能执行一些命令
  • system clear : 清屏

---