一文带你从入门到精通——MySQL（一）

目录

一. MySQL基础知识

1.1 引言

1.2 数据库

1.2.1 数据库简介

1.2.2 数据库分类

1.3 SQL语句

1.3.1 SQL通用语法

1.3.2 SQL数据类型

1.3.3 SQL字段约束

1.3.3.1 主键约束

1.3.3.2 非空约束

1.3.3.3 唯一约束

1.3.3.4 默认约束

1.3.3 SQL分类

1.3.3.1 常用DDL

1.3.3.2 常用DML   

1.3.3.3 常用DQL

一. MySQL基础知识

1.1 引言

        MySQL是目前最好的 RDMS（Relational Database Management System，关系数据库管理系统） 应用软件之一，它最早由瑞典MySQL AB公司开发，后被SUN公司收购，目前为Oracle甲骨文公司旗下产品。

1.2 数据库

1.2.1 数据库简介

        在介绍MySQL的具体内容之前，我们首先需要了解一个与之紧密相关事物——数据库。那什么是数据库呢，顾名思义，它就是一个存储数据的仓库，其本质是一个文件系统，它可以存储和管理大量的数据，例如可以存储大量的商品信息、用户信息、用户浏览信息等，当然也可以对这些数据进行管理和维护，例如新增数据、修改数据、删除数据、查看数据等。

1.2.2 数据库分类

        目前数据库可以分为两个大类: 关系型数据和非关系型数据库。

        关系型数据库即遵循SQL标准的数据库，其特点是有结构、安全性高，强调以二维表格的形式存储数据，常见的有MySQL、Oracle、SQL Server等数据库；而非关系型数据库是一种NoSQL的数据库，特点是没有结构、查询效率高，但不是很安全，强调以key-value对的形式存储数据，常见的有HBase、Redis等数据库。

1.3 SQL语句

        了解数据库之后，我们应该如何对数据库中所存储的数据进行维护呢？答案是使用SQL语句，它能够帮助我们快速地进行对数据库中任何对象的一系列增删改查等操作。

1.3.1 SQL通用语法

        SQL是一种结构化查询语言，也是所有关系型数据库都要遵循的一种规范，需要严格遵循以下的通用语法：

        1. SQL语句可以单行或多行书写，以分号结尾。  

        2. 可使用空格和缩进来增强语句的可读性。

        3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

        4. 可以使用 /**/，-- ，#的方式完成注释，注意: --后面必须有一个空格。

SELECT field_name 
FROM table_name
WHERE conditions;
-- 这是单行注释，不能换行
#这也是单行注释
/*
这是多行注释，
可以任意换行
*/

1.3.2 SQL数据类型

        与其他编程语言一样，SQL中也有各种的数据类型，用于存储不同的数据，主要可以分为以下四种类型：

字符串类型：char（char_length）、varchar（char_length），其中char表示定长字符串，varchar表示可变长字符串

整数类型：tinyint（4）、smallint（6）、 mediumint（9）、int（11）、bigint（20），这五种类型的所能表示的整数范围依次增加，括号里的数表示该类型的默认长度

浮点类型：float、double、decimal（x, y），其中float表示单精度浮点数，double表示双精度浮点数，decimal后的x用于指定有效数值，y用于指定小数位数

日期时间：datetime、date、time，其中datetime的格式为‘%Y-%m-%d %h-%i-%s’，date的格式为‘%Y-%m-%d’，time的格式为‘%h-%i-%s’

1.3.3 SQL字段约束

        字段约束用于限制相应字段所存储的数据，这里主要介绍主键约束、非空约束、唯一约束以及默认约束，外键约束后续会单独介绍。

1.3.3.1 主键约束

        主键约束关键字：primary key，其作用是限制主键所对应的字段中所插入的数据不能为空且不能重复，可以视为同时向该字段添加了唯一约束和非空约束，一个表中只能有一个主键。

        主键的添加一般有以下三种方式：

-- 主键的添加方式一：建表的时候添加主键约束
CREATE TABLE table_name
(
    field_name1 field_type1 PRIMARY KEY,
    field_name2 field_type2 field_constraint
);

-- 主键的添加方式二：建表后使用add关键字添加主键
ALTER TABLE table_name
    ADD PRIMARY KEY(field_name);

-- 主键的添加方式三：建表后使用change关键字添加主键
ALTER TABLE table_name
    CHANGE field_name field_name field_type PRIMARY KEY;

        关于主键的删除需要注意，不能使用直接change关键字删除主键，需要先使用drop关键字删除掉主键中的唯一索引，再使用change关键字删除非空约束。

-- 首先使用drop删除主键,但只是把唯一索引删除,非空约束保留
ALTER TABLE table_name
    DROP PRIMARY KEY;

-- 使用change删除非空约束
ALTER TABLE table_name
    CHANGE field_name field_name field_type;

        主键自增关键字：auto_increment，对于一个主键来说，可以使用auto_increment关键字为其添加自增功能，使得主键的值默认从1开始,每次自动加1，并且在插入数据的时候可以使用null或者0占位表示自动使用自增。

        主键自增的添加一般也有两种方式：

-- 主键自增的添加方式一：建表的时候添加主键自增 
CREATE TABLE table_name
(
    field_name1 field_type1 PRIMARY KEY AUTO_INCREMENT,
    field_name2 field_type2 field_constraint
);

-- 主键的添加方式二：建表后添加主键自增，如果主键不存在可以一同添加
ALTER TABLE table_name
    CHANGE field_name field_name field_type AUTO_INCREMENT;
ALTER TABLE table_name
    CHANGE field_name field_name field_type PRIMARY KEY AUTO_INCREMENT;

        关于带有自增的主键删除需要注意，总共三步操作才能彻底删除带有自增的主键，首先需要使用change关键字删除自增功能，再使用drop关键字删除掉主键中的唯一索引，最后使用change关键字删除非空约束

-- 使用change删除自增效果
ALTER TABLE table_name
    CHANGE field_name field_name field_type;

-- 使用drop删除主键,只是把唯一索引删除,非空约束保留
ALTER TABLE table_name
    DROP PRIMARY KEY;

-- 使用change删除非空约束
ALTER TABLE table_name
    CHANGE field_name field_name field_type;

1.3.3.2 非空约束

        非空约束关键字：not null，其作用是限制添加非空约束后的字段所对应数据不能为null值，一个表中可以有多个非空约束

-- 非空约束的添加方式一：建表的时候添加非空约束
CREATE TABLE table_name
(
    field_name1 field_type1 NOT NULL,
    field_name2 field_type2 field_constraint
);

-- 非空约束的添加方式二：建表后添加非空约束
ALTER TABLE table_name
    CHANGE field_name field_name field_type NOT NULL;

-- 非空约束的删除方式
ALTER TABLE table_name
    CHANGE field_name field_name field_type;

1.3.3.3 唯一约束

        非空约束关键字：unique，其作用是限制添加唯一约束后的字段所对应数据不能重复，一个表中可以有多个唯一约束

-- 唯一约束的添加方式一：建表的时候添加唯一约束
CREATE TABLE table_name
(
    field_name1 field_type1 UNIQUE,
    field_name2 field_type2 field_constraint
);

-- 唯一约束的添加方式二：建表后使用change关键字添加唯一约束
ALTER TABLE table_name
    CHANGE field_name field_name field_type UNIQUE;

-- 唯一约束的添加方式三：建表后使用create关键字添加唯一约束
CREATE UNIQUE INDEX unique_index_name ON table_name(field_name);

-- 唯一约束的删除方式
DROP INDEX unique_index_name/field_name ON TABLE_NAME;

1.3.3.4 默认约束

        非空约束关键字：default，其作用是为添加默认约束后的字段所对应数据提前设置默认值，在插入数据时，如果用户没有指定该字段的值，可以使用提前设置好的默认值，一个表中可以有多个默认约束

-- 默认约束的添加方式一：建表的时候添加默认约束
CREATE TABLE table_name
(
    field_name1 field_type1 DEFAULT default_value,
    field_name2 field_type2 field_constraint
);

-- 默认约束的添加方式二：建表后添加默认约束
ALTER TABLE table_name
    CHANGE field_name field_name field_type DEFAULT default_value;

-- 默认约束的删除方式
ALTER TABLE table_name
    CHANGE field_name field_name field_type;

1.3.3 SQL分类

        SQL语句主要可以分为四个大类，DDL、DML、DQL以及DCL。

        DDL（Data Definition Language）即数据定义语言，一般用来定义数据库对象，完成对数据库、表、字段等对象的增删改操作。     
        DML（Data Manipulation Language）即数据操作语言，一般用来对数据库中表所记录的数据进行增删改操作。
        DQL（Data Query Language）即数据查询语言，一般用来查询数据库中表所记录的数据。
        DCL（Data Control Language） 即数据控制语言，一般用来定义数据库的访问权限、安全级别以及创建用户等操作。

1.3.3.1 常用DDL

/*
创建数据库, 如果名为database_name的数据库存在则会报错，
加入if not exists则可以解决此问题，如果名为database_name的数据库存在，
执行第二条SQL语句则不会创建新的数据库，CHARSET=utf8用于指定字符集（可省）
*/
CREATE DATABASE database_name;
CREATE DATABASE IF NOT EXISTS database_name CHARSET = utf8;

/*
删除数据库, 如果名为database_name的数据库不存在则会报错，
if exists是用于解决名为database_name的数据库不存在的问题，
如果名为database_name的数据库不存在，执行第二条SQL语句则不会删除任何数据库，
*/
DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

-- 使用/切换数据库
USE database_name;

-- 创建表，field_type用于指定字段类型，不可省略；field_constraint用于添加字段约束，可以省略；
-- if not exists用于解决表已经存在的问题，可以省略 
CREATE TABLE IF NOT EXISTS table_name
(
    field_name1 field_type1 field_constraint1,
    field_name2 field_type2 field_constraint2
);

-- 删除表，if exists关键字用于解决表不存在的问题，可以省略
DROP TABLE IF EXISTS table_name;

-- 修改表名
RENAME TABLE old_table_name TO new_table_name;

-- 添加字段，关键字column可以省略，field_constraint可以省略  
ALTER TABLE table_name
    ADD COLUMN field_name field_type field_constraint;

-- 删除字段，关键字column可以省略 
ALTER TABLE table_name
    DROP COLUMN field_name;

-- change关键字可以修改字段名和字段类型，关键字column可以省略，field_constraint可以省略  
ALTER TABLE table_name
    CHANGE COLUMN old_field_name new_field_name field_type field_constraint;

-- modify关键字只能修改字段类型，关键字column可以省略，field_constraint可以省略   
ALTER TABLE table_name
    MODIFY COLUMN field_name field_type field_constraint;

1.3.3.2 常用DML   

-- 插入数据记录
-- 注意1: 具体值要和前面的字段名以及顺序一一对应上
-- 注意2: 如果要插入的是所有字段,那么字段名可以省略(默认代表所有列都要插入数据)
-- 注意3: 如果要插入多条记录,values后多条数据使用逗号分隔
INSERT INTO table_name (field_name1, field_name2)
VALUES (value1, value2),
       (value3, value4);

-- 修改数据记录，如果没有加条件就是修改对应字段的所有数据
UPDATE table_name
SET field_name = new_value
WHERE conditions;

-- 删除数据记录，如果没有加条件就是删除所有数据
DELETE
FROM table_name
WHERE conditions;

-- 清空所有数据，第一条语句有警告，第二条语句没有警告且table关键字可省略
DELETE
FROM table_name;
TRUNCATE TABLE table_name;

        在清空表中的数据时有两种方式，delete from以及truncate table，那么这两种方式有那些共同点和不同点呢？

1.3.3.3 常用DQL

        更多基于select的DQL后面会单独介绍，这里介绍其他常用的DQL。

-- 查看所有的数据库名
SHOW DATABASES;

-- 查看当前使用的数据库
SELECT DATABASE();

-- 查看指定库的建库语句
SHOW CREATE DATABASE 数据库名;

-- 查看所有表: 
SHOW TABLES;

-- 查看指定表的建表语句: 
SHOW CREATE TABLE table_name;

-- 查看字段信息 
DESC table_name;