mysql基础原理操作学习

信安之路-WEB-第二关

人需要沉淀，有时间去反思，才能让自己变得更完美。

学习目的

1、什么是关系型和非关系型数据库，两者都包含哪些种类的数据库

2、选择一种关系型数据库进行学习（选择自己不熟悉的进行学习）

3、学习数据库中的字段类型并创建库和用户表，需要包含所有字段类型

4、学习数据库的增删改查，记录学习过程（重点是 sql 语句的理解）并形成报告

学习路线

什么是数据

描述事物的符号称为数据。数据有多种表现形式，可以是数字，也可以是文字、图形、图像、声音、语言等。在数据库中数据表示记录，例如，在学生管理数据库中，记录学生的信息包括学号、姓名、性别、年龄、籍贯和联系电话等，这些信息就是数据。信息是指对数据进行加工处理后提取的对人类社会实践和生产活动产生决策影响的数据。信息就是数据中所包含的意义。未经过加工的数据只是一种原始材料，它的价值在于记录了客观世界的事实。例如“2016010102，张三，男，26，山西，计算机学院，185”，对于这条学生记录，了解其含义的人会得到这样的信息：张三的学号是 2016010102，今年 26 岁，山西人，就读于计算机学院，他的联系电话是 185；而不了解其语义的人则无法理解其含义。所以，数据和信息是不可分的;

数据库（Database）指长期存储在计算机内的、有组织的、可共享的数据集合,数据库实际上就是一个文件集合，是一个存储数据的仓库，本质就是一个文件系统，数据库是按照特定的格式把数据存储起来，用户可以对存储的数据进行增删改查操作.

理解关系型数据库和非关系数据库区别

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单说，关系型数据库是由多张能互相连接的表组成的数据库

优点

1. 都是使用表结构，格式一致，易于维护。
2. 使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
3. 数据存储在磁盘中，安全。

缺点

1. 读写性能比较差，不能满足海量数据的高效率读写。
2. 不节省空间。因为建立在关系模型上，就要遵循某些规则，比如数据中某字段值即使为空仍要分配空间。
3. 固定的表结构，灵活度较低。

包括

Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access 和 MySQL

非关系型数据库

非关系型数据库又被称为 NoSQL（Not Only SQL )，意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。

优点

1. 非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2. 速度快，效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘。
3. 海量数据的维护和处理非常轻松。
4. 非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势。
5. 可以实现数据的分布式处理。

缺点

1. 非关系型数据库暂时不提供 SQL 支持，学习和使用成本较高。
2. 非关系数据库没有事务处理，没有保证数据的完整性和安全性。适合处理海量数据，但是不一定安全。
3. 功能没有关系型数据库完善。

包括

Neo4j、MongoDB、Redis、Memcached、MemcacheDB 和 HBase 等。

常见数据库信息

数据库是什么

数据库访问逻辑

数据库系统主要有以下 3 个组成部分：

1. 数据库：用于存储数据的地方。
2. 数据库管理系统：用于管理数据库的软件。
3. 数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据库库的软件补充。

常见数据库接口

ODBC

ODBC（Open Database Connectivity，开放数据库互连）为访问不同的 SQL 数据库提供了一个共同的接口。ODBC 使用 SQL 作为访问数据的标准。这一接口提供了最大限度的互操作性。一个应用程序可以通过共同的一组代码访问不同的 SQL 数据库管理系统。

一个基于 ODBC 的应用程序对数据库的操作不依赖任何 DBMS，不直接与 DBMS 打交道，所有的数据库操作由对应的 DBMS 的 ODBC 驱动程序完成。也就是说，不论是 MySQL 还是 Oracle 数据库，均可用 ODBC API 进行访问。由此可见，ODBC 的最大优点是能以统一的方式处理所有的数据库。

JDBC

Java Data Base（JDBC，Java 数据库连接）用于 Java 应用程序连接数据库的标准方法，是一种用于执行 SQL 语句的 Java API，可以为多种关系数据库提供统一访问，它由一组用 Java 语言编写的类和接口组成。

ADO.NET

ADO.NET 是微软在 .NET 框架下开发设计的一组用于和数据源进行交互的面向对象类库。ADO.NET 提供了对关系数据、XML 和应用程序的访问，允许和不同类型的数据源以及数据库进行交互。

PDO

PDO（PHP Data Object）为 PHP 访问数据库定义了一个轻量级的、一致性的接口，它提供了一个数据访问抽象层，这样，无论使用什么数据库，都可以通过一致的函数执行查询和获取数据。PDO 是 PHP 5 新加入的一个重大功能。

mysql学习

连接mysql

-u userName
-p passWd
-P port
-h hostName
-S file
语句：
mysql -uroot -p123 -h -P -S -e 
例子：mysql.exe -u root -proot -P 3306 -h 127.0.0.1

自带数据库介绍

show databases;

系统自带：information_schema、mysql、performance_schema、test

mysql-information_schema：

• 在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权 限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件

• SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表

• TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

• COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

• STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

• USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

• SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

• TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

• COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

• CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

• COLLATIONS表：提供了关于各字符集的对照信息。

• COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

• TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

• KEY_COLUMN_USAGE表：描述了具有约束的键列。

• ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

• VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

• TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

重点是前几个

mysql数据库

这个是mysql的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除，如果对mysql不是很了解，也不要轻易修改这个数据库里面的表信息。

performance_schema

mysql 5.5 版本 新增了一个性能优化的引擎： PERFORMANCE_SCHEMA这个功能默认是关闭的：

setup table ： 设置表，配置监控选项。
current events table : 记录当前那些thread 正在发生什么事情。
history table 发生的各种事件的历史记录表
summary table 对各种事件的统计表
杂项表，乱七八糟表。
setup_consumers 描述各种事件
setup_instruments 描述这个数据库下的表名以及是否开启监控。
setup_timers 描述监控选项已经采样频率的时间间隔

test数据库

这个是安装时候创建的一个测试数据库，和它的名字一样，是一个完全的空数据库，没有任何表，可以删除。

用户操作

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

授权

GRANT privileges ON databasename.tablename TO 'username'@'host'
例子：GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
     GRANT ALL ON *.* TO 'pig'@'%';

• privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
• databasename：数据库名
• tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

修改密码

mysqladmin.exe -u 用户名 -p 旧密码 password 新密码   #推荐
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
SET PASSWORD = PASSWORD("newpassword");#是当前登陆用户用:

撤销用户权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

删除用户

DROP USER 'username'@'host';

查看所有用户信息

select host,user from mysql.user

字段类型

整型
tinyint: 短整型，1个字节
int :整型，4个字节

double:浮点型，例如double(5,2)表示最多5位，其中2位为小数，即最大值为999.99。
float  单精度浮点数

字符型
varchar:可变长度字符串类型。varchar(10) ‘aaa’ 占3位
char:固定长度字符串类型。char(10) ‘aaa ’ 占10位

text:大文本字符串类型
blob:字节类型

日期时间类型
datetime:日期时间类型。yyyy-MM-dd hh:mm:ss
date：日期类型，格式为：yyyy-MM-dd
time:时间类型，格式为：hh:mm:ss
timestamp:时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值

NULL 空值

字符串类型和日期类型都要用单引号括起来。

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，
所以这个数据类型的字段可以存放这条记录最后被修改的时间。

字段修饰

NOT NULL：该字段不能为空
NULL：字段可以为空
UNSIGNED：该字段是一个无符号数（正数）
AUTO_INCREMENT：对于正数类型字段可用该修饰，可以实现该字段的自动增张
PRIMARY KEY：定义主键
UNIQ KEY：定义唯一键
DEFAULT：指定默认值

增删改查

新增数据

新增库

CREATE DATABASE test
DEFAULT
CHARACTER SET utf8 COLLATE utf8_general_ci
;

新增表

show tables # 查看库中表

建表

CREATE TABLE IF NOT EXISTS user( #创建user表当user表不存在
id int not null auto_increment, #ID int类型不为空 ,自增加
name varchar(20) not null,  #name carchar类型20字节 不为空
age tinyint not null, age tinyint类型不为空
PRIMARY KEY(id) #设置主键为ID
); 

查看表结构

插入数据

INSERT
INTO user(name, age) VALUES('Jack', 18);
// id是自增的，没必要指定值

删除数据

DELETE FROM user WHERE id=2;

修改数据

修改表结构（加字段）

ALTER TABLE user ADD COLUMN email varchar(100) not null AFTER age;

修改表中数据（删字段）

ALTER TABLE user DROP COLUMN email;

修改表结构(修改字段)

ALTER TABLE user CHANGE name name varchar(50) not null default '0';

查询数据

SELECT id,name,age FROM user;

读写文件

能读写文件的前提

1.拥有该File的读权限 or 该目录写的权限
2.当前用户的secure_file_priv属性的值不为NULL

修改mysql.ini 文件，在[mysqld] 下加入 secure_file_priv = 保存，重启mysql。

常用读文件函数

• load_file()

• load data infile()

• system cat

读取文件

select load_file("D:/phpStudy/PHPTutorial/WWW/index.php");

写入文件

<php? phpinfo(); ?>' into outfile 'D:/phpStudy/PHPTutorial/WWW/wreite.php

查找密码并破解

select * from mysql.user;

直接拿到在线网战解密

进阶

查看库中的所有表或者一个或者多个表的信息
> SHOW TABLE STATUS [LIKE "XXX"]

查看当前mysql所支持的字符集类型
> SHOW CHARACTER SET;

查看各种变量
> SHOW GLOBAL VARIABLES;
> SHOW SESSION VARIABLES;

查看各种状态信息
> SHOW GLOBAL STATUS
> SHOW SESSION STATUS

查看当前mysql一共执行过多少次select
> SHOW GLOBAL STATUS LIKE 'Com_select';

查看mysql的数据目录位置
> SHOW GLOBAL VARIABLES LIKE 'datadir';

DQL数据库操作
## 多条件查询连接关键字（and, or，not(取反)）
> select * from test where id not between '1' and '2'
## 模糊查询（%表示任意多个字符，_表示一个字符）
> select * from test where name like '%李四'
## 查询去重复
> select distinct * from test
## 排序asc(升序) desc（降序）先按照id进行排序，如果id相同的再通过name排序
> select * from test order by id desc,name desc

字符串函数
## upper()将字段的英文字母转化成大写 lower()将字段的英文字母转化成小写
select upper(name) from test
select lower(name) from test
## substring()截取字符串，concat(col,col)
select substring(name,1,1) from test
select concat(id,'-',name) from test

分组查询（group by）
## select 后使用 *，显示的是对分组查询后的每一组的第一条数据
## 只有出现 group by 时才可以使用 having（对分组后的数据进行过滤）
select type,sum(id),avg(id),count(*) from test where id >= 1 group by type having count(*) > 1 order by id desc

 分页查询（limit） 0 表示 数据的索引，3 表示 显示的条数
select * from test limit 0 , 3