MySQL数据库基本使用

一 、数据库概述

数据库就是以一定格式进行组织的数据的集合。通俗来看数据库就是用户计算机上 一些具有特殊格式的数据文件的集合。

数据库也可以理解为表格，大家都知道表格都是由表名、表头、数据等几部分组成的，数据库跟这类似，只不过叫法不一样，这里叫数据库名、表名和字段。

1）数据库的特点

• 持久化存储
• 读写速度极高
• 保证数据的有效性
• 对程序支持性非常好，容易扩展

2）数据库管理系统

数据库管理系统（Database Management System，简称DBMS）是为管理数据库而设计的软件系统，包括三大部分构成:

数据库文件集合：主要是一系列的数据文件, 作用是存储数据.
数据库服务端：主要负责对数据文件以及文件中的数据进行管理.
数据库客户端：对外通信

3）数据库的分类

● 关系型数据库：所谓的关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据，本质上使用一张二维表来表示关系。

1. oracle：银行,电信等项目
2. ms sql server：在微软的项目中使用
3. sqlite：轻量级数据库，主要应用在移动平台
4. mysql：web时代使用最广泛的关系型数据库。

● 非关系型数据库：

1. 键值存储数据库（key-value）：可以通过key来添加、查询或者删除数据库,Memcached、Redis、MemcacheDB等
2. 列存储数据库（Column-oriented）：列存储数据库将数据存储在列族中，一个列族存储经常被一起查询的相关数据,Cassandra、HBase
3. 面向文档数据库（Document-Oriented）：面向文档数据库会将数据以文档形式存储,MongoDB、CouchDB
4. 图形数据库：图形数据库允许我们将数据以图的方式存储，Neo4J、InforGrid

二 、数据库的使用

1）常用的数据类型

整数类型：int、bit
浮点数类型，定点数类型和位类型：float、double、decimal
日期和时间类型：date，time、datetime
字符串类型：char、varchar、text
枚举类型（Enum）

特别说明的类型如下：

• decimal表示浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位.
• char表示-固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为 'ab ' .
• varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'
• 注意 : 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储 这个文件的保存路径。字符串 text 表示存储大文本，当字符大于 4000 时推荐使用, 比如技术博客.

2）数据约束

PK（Primary Key）：主键索引，物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned。
UQ（Unique）：唯一索引，数据不可重复； 此字段的值不允许重复。
NN（Not Null）：不允许为空，此字段不允许填写空值。
AI（Auto Increment）：自动增长，一般用在id自动生成增长。
Default：默认值，当不填写字段对应的值会使用默认值，如果填写时以填写为准。
Foreign key：外键，对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常。

3）存储引擎

SHOW ENGINES：查看数据库支持存储引擎：
SHOW ENGINESG：每行显示数据库支持存储引擎：
SHOW VARIABLES LIKE '%STOR%'：查看默认存储引擎
SHOW CREATE TABLE 表名；查看某个表用什么引擎
DEFAULT-STORAGE-ENGINE=存储引擎（需要重启服务器）：修改默认存储引擎MY.INI（修改文件数据）

三 、 数据库的相关操作

1）数据库的操作：DATABASE

1 - 创建

CREAT DATABASE [IF NOT EXISTS] database [CHARACTER SET UTF8]；-- 创建数据库

CREAT DATABASE IF NOT EXISTS database；-- 如果数据库不存在则创建数据库

-- 注意：[IF NOT EXISTS]：最好加上，这样可以避免麻烦，每操作一次数据库就多一次风险；[CHARACTER SET UTF8]：就是设置编码格式，也可以不指定

2 - 查看

SHOW DATABASES：-- 查看所有数据库

SHOW CREATE DATABASE database：-- 查看创建数据库的创建方式

SELECT VERSION()：-- 查看版本

SELECT DATEBASE()：-- 查看当前的数据库

SELECT NOW()：-- 显示时间

3 - 修改

ALTER DATABASE database DEFAULT CHARACTER SET 字符集：-- 修改数据库字符集

4 - 选择

USE database：-- 选择数据库

5 - 删除

DROP DATABASE [IF EXISTS] database：-- 删除指定数据；注意： [if exists]：如果你不确定数据库是否存在最好加上，存在就删除，不存在也不会报错

2）数据表的操作：TABLE

1 - 创建、添加、插入

CREATE TABLE [IF NOT EXISTS] 表名称(字段 类型 [字段约束] [，字段 类型 [字段约束],..])；-- 如果数据表不存在则创建一个数据表

CREATE TABLE table LIKE copyTable；-- 复制表的结构
INSERT INTO table SELECT * FROM copyTable；-- 复制表的结构
INSERT INTO table SELECT[部分字段,...] FORM copyTable；-- 复制表的部分结构

ALTER TABLE table ADD COLUMN 列名 类型 [字段约束]；-- 添加字段(列)
ALTER TABLE table ADD COLUMN 列名 类型 [字段约束] AFTER 指定列名；-- 在指定字段(列)后面添加字段(列)

INSERT INTO table VALUES(数值..，数值)[,(数值1...数值x)]；-- 按照表字段插入数据，插入多条数据可以用逗号隔开
INSERT INTO table (列名1...，列名x) VALUES(数值1..，数值x)[,(数值1...数值x)]；-- 选择字段插入数据，插入多条数据可以用逗号隔开

2 - 查看

SHOW TABLES；-- 显示当前数据库中所有的表

SHOW TABLES LIKE ‘xxx’；-- 模糊检索，x为表名的某一个字符，可以使用通配符%和_

SHOW CREATE TEABLE table；-- 查看表单创建语句

SHOW FULL COLUMNS FROM table；-- 返回字段的详细信息

DESC table；-- 查看表结构的描述信息
SHOW COLUMNS FROM table；-- 查看表结构的描述信息

3 - 修改、更新

ALTER TABLE oldTableName RENAME TO newTableName；-- 修改表的名字
RENAME TABLE oldTableName TO newTableName；-- 修改表的名字

ALTER TABLE table CHARACTER SET 字符集；-- 修改表所用的字符集

ALTER TABLE table MODIFY COLUMN 列名 类型 [字段约束]：-- 修改字段(列)约束和类型
ALTER TABLE table CHANGE COLUMN 旧列名 新列名 类型 [字段约束]；-- 修改字段(列)定义，并重命名字段(列)

UPDATE table SET 字段名(列名)=字段数据 WHERE 条件限制；-- 修改数据(行)，如果没有条件限制则更新到所有行的字段数据

4 - 删除

DROP TABLE table；-- 删除表

DELETE FROM table WHERE(条件限制)；-- 删除行，一定要加条件限制 不然整张表都会删除

ALTER TABLE table DROP COLUMN 列名；-- 删除字段(删除列)

3）表数据记录查询：SELECT

1 - 简单数据记录查询

SELECT [列1 列2...列] FROM table；-- 查询数据

SELECT DISTINCT(列名)[,列名[...]] FROM table；-- 避免重复数据查询

SELECT CONCAT(列名，"字符") FROM table；-- 字符串连接
SELECT CONCAT(CONCAT(列名，"字符")，列名) FROM table；-- 字符串嵌套连接

2 - 条件数据记录查询

SELECT * FROM table WHERE [条件运算1]；-- 查询数据
SELECT * FROM table WHERE [条件运算1] AND [条件运算2]；-- 带有关系运算符和逻辑运算符的条件数据查询
SELECT * FROM table WHERE [条件运算1] AND [条件运算2] OR [条件运算3]；-- 带有关系运算符和逻辑运算符的条件数据查询

SELECT * FROM table WHERE 列名 IS NULL；-- 查询数值为NULL的数据
SELECT * FROM table WHERE 列名 IN (a，b，...)；-- 查询列名中包含ab等的数据
SELECT * FROM table WHERE 列名 NOT IN (a，b，...)；-- 查询列名中不包含ab等的数据行

SHOW table LIKE ‘%xxx%’；-- 通配符‘_’，能匹配任意单个字符。通配符‘%’，能匹配任意长度字符串 0个或1个或多个。
SELECT * FROM table WHERE 列名 LIKE ‘通配符’；

SELECT * FROM table1,table2 WHERE 条件运算；-- 内连接查询，显示两个表的数据
SELECT * FROM table1 LEFT JOIN table2 ON 条件运算；-- 左连接查询，保留显示左边表的数据，右边表数据满足条件的话显示
SELECT * FROM table1 RIGHT JOIN table2 ON 条件运算；-- 右连接查询，保留显示右边表的数据，左边表数据满足条件的话显示

3 - 排序数据记录查询

SELECT 列1,列2... FROM table ORDER BY 列名1 [ASC|DESC][,列2[ASC|DESC]]；-- 按照指定字段顺序查询 ASC升序 DESC降序

4 - 限制数据记录查询数量

SELECT * FROM table LIMIT offset_start,row_count；-- offset_state第几个开始，row_count查询几个

5 - 统计函数和分组数据记录查询：SLECT * FROM ......，统计函数一般代替星号 * ；

• COUNT()：统计表中数据的数量
• AVG()：计算字段值平均值
• SUM()：计算字段值的总和
• MAX()：查询字段值的最大值
• MIN()：查询字段值的最小值

6 - 功能记录查询：function可以使用上面的部分函数

SELECT function(列名) FROM table GROUP BY 列名；-- 简单分组数据记录查询
SELECT GROUP_CONCAT(列名) FROM table GROUP BY 列名；--功能分组查询SELECT GROUP_CONCAT(列名),function(列名) FROM table GROUP BY 列名1,列名2,...；-- 多个字段分组查询

7 - 内连接查询：等值查询

SELECT [e.列名...[c.列名...]] FROM table_e,table_c WHERE e.列名=c.列名；-- 

4）字句查询：多表连接进行查询

返回结果为单行单列子查询

-- 当子查询的返回结果为单行单列数据记录时，盖子查询语句一般会在主查询语句的WHERE子句里，通常会包含比较运算符号（>，<，=，!= 等）
SELECT * FROM table1 WHERE 列名 >(SELECT 列名 FROM table2 WHERE 条件运算)；

返回结果为单行多列子查询

-- WHERE子句中的子查询除了是返回单行单列的数据记录外，还可以是返回单行多列的数据记录，不过这种子查询很少出现。
SELECT * FROM table1 WHERE (列名1,列名)=(SELECT 列名1,列名2 FROM table2 WHERE 条件运算)；

返回结果为多行多列子查询 - 带有关键字IN，NOT IN的子查询

-- 当主查询的条件是子查询的查询结果时，就可以通过关键字IN来进行判断。相反如果想实现主查询的条件不是子查询的查询结果时，就可以通过关键字NOT IN 来进行判断。
SELECT * FROM table1 WHERE 列名 IN(SELECT 列名 FROM table2 WHERE 列名 IN(xxx,xxx))；
SELECT * FROM table1 WHERE 列名 NOT IN(SELECT 列名 FROM table2 WHERE 列名 IN(xxx,xxx))；

返回结果为多行单列子查询 - ANY、ALL、EXISTS

/*带有关键字ANY的子查询
　=ANY：其功能与关键字IN一样
　>ANY(>=ANY)：比子查询中返回数据记录中最小的还要大于（大于等于）的数据记录。
　<ANY(<=ANY)：比子查询中返回数据记录中最小的还要小于（小于等于）的数据记录。
*/
SELECT * FROM table1 WHERE 列名 >=ANY(SELECT 列名 FROM table2 WHERE 条件运算)；
SELECT * FROM table1 WHERE 列名 <=ANY(SELECT 列名 FROM table2 WHERE 条件运算)；

/*带有关键字ALL的子查询
　=ALL：其功能与关键字IN一样
　>ALL(>=ALL)：比子查询中返回数据记录中最小的还要大于（大于等于）的数据记录。
　<ALL(<=ALL)：比子查询中返回数据记录中最小的还要小于（小于等于）的数据记录。
*/
SELECT * FROM table1 WHERE 列名 >=ALL(SELECT 列名 FROM table2 WHERE 条件运算)；
SELECT * FROM table1 WHERE 列名 <=ALL(SELECT 列名 FROM table2 WHERE 条件运算)；

/*带有关键字EXISTS子查询
　关键字EXISTS表示-布尔类型，当返回结果集时为true，不能返回结果集为false。
　查询时EXISTS对外表采用遍历方式逐条查询，每次查询都会比较EXISTS的条件语句，
　当EXISTS里的条件语句能返回记录行时则条件为真，此时返回当前遍历到的记录，
　反之如果EXISTS里的条件语句不能返回记录行，则丢弃当前遍历到的记录。
*/
SELECT * FROM table1 WHERE EXISTS(SELECT * FROM table2 WHERE 条件运算)；-- o为table1的引用，将外查询的表引用到内查询中