1. SQL语法
之前咱们是通过可视化工具Navicat来操作MySQL数据，此时来学习下更为厉害的SQL语句，来操作数据库。
有了mysql这个数据库软件，就可以将程序员从对数据的管理中解脱出来，专注于对程序逻辑的编写
mysql服务端软件即mysqld。
帮我们管理好文件夹以及文件，前提是作为使用者的我们，需要下载mysql的客户端，或者其他模块来连接到mysqld，然后使用mysql软件规定的语法格式去提交自己命令，实现对文件夹或文件的管理。
该语法即sql（Structured Query Language 即结构化查询语言）
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：
1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
1.1.1. 登录MySQL
在确保咱们启动了MySQL服务端的前提下，可以使用客户端命令行，来登录数据库。
并且要确保配置了MySQL的环境变量。
确保了如上条件符合，此时通过命令行(CMD)登录MySQL数据库。
我们此时是在自己的机器上，启动了MySQL数据库，运行在本地机器上
想要连接MySQL需要提供以下信息：
主机名，运行MySQL的机器，默认是localhost
端口，默认3306
一个用户名
用户密码，默认密码为空，直接回车输入空密码
1.1.2. 查看已有的数据库
表信息不直接显示，得先查看有哪些数据库，使用show命令查看
查看当前数据库，有哪些数据仓库，SQL语言如下，注意以 分号 ; 结束一条SQL
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| king |
| king2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
1.1.3. 选择数据库
默认是没有连接任何数据库的，意思就是没有进入任何的数据仓库，通过use关键字进入数据仓库。
mysql> use king;
Database changed
1.1.4. 列出数据库下的所有表
当我们进入数据库后，为了获得数据库内的表信息，使用show命令
mysql> use king;
Database changed
mysql> show tables;
+----------------+
| Tables_in_king |
+----------------+
| fashi |
| sheshou |
| zhanshi |
+----------------+
3 rows in set (0.00 sec)
列出表内详细信息，可以使用命令show columns from 表名;
命令结果，返回每个字段是一行信息，包含字段名，数据类型，是否为null，键信息，默认值信息，额外信息(自增等)
如上命令还有个快捷命令，desc 表名;
mysql> desc fashi;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| price | decimal(10,2) | YES | | 888.00 | |
+-------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> describe fashi;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| price | decimal(10,2) | YES | | 888.00 | |
+-------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc fashi;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| price | decimal(10,2) | YES | | 888.00 | |
+-------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
show语句
show status; 显示MySQL服务器状态信息
Show create database; 显示创建数据库或表的语句
show grants; 显示用户的权限信息
show errors;
show warnings; 显示服务器错误信息
1.1.5. 数据库创建
SQL语法都是由简单的音乐单词构成，这些语法称为SQL关键字，接下来我们用SQL创建数据库。
我们刚才用了CMD终端来输入SQL语句，有同学可能觉着黑乎乎的屏，不美观，不好用，既然这样Navicat提供了图形化界面，让你编写SQL语句。
Navicat的查询功能
在新建的查询窗口中，填写SQL语句。
创建数据库
语法规则
create database 数据库名 DEFAULT CHARACTER SET utf8;
数据库命名规则
由字母、数字、下划线组成
不得使用关键字，如create select等
不得纯数字
库名最长128
查看所有数据库
show databases;
进入数据库
use 数据库名;
删除数据库
drop database 数据库名;
1.2. 数据表操作
1.2.1. 存储引擎
mysql中建立的库===>文件夹
库中建立的表===>文件
现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制。
比如处理文本用txt类型，处理表格用excel，处理图片用png等，看小电影用avi格式~~~
数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方
法。【就好比一张图片可以存储png格式，jpeg格式，png图像比jpeg图片质量更高】
因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和
操作此表的类型）
1.2.2. mysql支持的存储引擎
MariaDB [(none)]> show engines\G #查看所有支持的存储引擎
MariaDB [(none)]> show variables like 'storage_engine%'; #查看正在使用的存储引擎
1.2.3. 指定存储引擎，创建数据表
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
create table t4(id int)engine=blackhole;
1.2.4. 引擎解释
1、InnoDB 存储引擎**
支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其
特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。
从 MySQL 5.5.8 版本开始是默认的存储引擎。
InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。
InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。
InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。
2、MyISAM 存储引擎
不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。
数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。
究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。
3、NDB 存储引擎
年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。
4、Memory 存储引擎
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。
5、Infobright 存储引擎
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/, 上面有不少成功的数据 仓库案例可供分析。
6、NTSE 存储引擎
网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。
7、BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。
MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。
面试回答
Mysql 中 MyISAM 和 InnoDB 的区别有哪些？
1.文件构成上
innodb在磁盘存储文件是frm表结构文件 ibd数据文件。
myisam在磁盘存储文件是frm表结构文件 myd数据文件 myi索引文件
2.事务处理
Innodb提供可以回滚和事务崩溃的事务安全存储，外键，行级锁，用于处理巨大数据。
myisam类型不支持事务，特点是 表级锁，全文索引，适合数据仓库频繁查询的应用。
3.表级锁和行级锁区别？
myisam表进行读操作适，不会阻塞其他用户对同一个表的读请求，但是会阻塞通一表的写操作。
myiasam表进行写操作，会阻塞其他用户对同一表的读和写操作。
InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。
存储引擎演练
数据表路径
C:\mysql-5.7.16-winx64\data\king 注意和你自己的机器路径调节
创建四种引擎数据表
语法
create table 表名(字段名 字段类型)存储引擎选择;
分别向四种表中写入数据，查看结果
1.2.5. 数据表的增删改查
表介绍
表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段
创建表
法
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
示范，注意语法格式
mysql> create database db1 charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> create table hero(
-> id int ,
-> name varchar(50),
-> sex enum('male','female'),
-> price int ,
-> introduction varchar(50)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| hero |
+---------------+
1 row in set (0.00 sec)
插入表数据
mysql> insert into hero values(1,'妲己','female','888','爆发高，控制强');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> select * from hero;
+------+------+--------+-------+----------------+
| id | name | sex | price | introduction |
+------+------+--------+-------+----------------+
| 1 | 妲己 | female | 888 | 爆发高，控制强 |
+------+------+--------+-------+----------------+
1 row in set (0.01 sec)
mysql> select name,price from hero;
+------+-------+
| name | price |
+------+-------+
| 妲己 | 888 |
+------+-------+
1 row in set (0.00 sec)
插入指定字段，其余字段可以是默认值，注意最后一个字段不得有逗号
mysql> insert into hero(id) values(2);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> select * from hero;
+------+------+--------+-------+----------------+
| id | name | sex | price | introduction |
+------+------+--------+-------+----------------+
| 1 | 妲己 | female | 888 | 爆发高，控制强 |
| 2 | NULL | NULL | NULL | NULL |
+------+------+--------+-------+----------------+
2 rows in set (0.00 sec)
查看表结构
mysql> desc hero;
+--------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| introduction | varchar(50) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> show create table hero\G
*************************** 1. row ***************************
Table: hero
Create Table: CREATE TABLE `hero` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
`price` int(11) DEFAULT NULL,
`introduction` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
修改表结构
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
案例
#修改表名
mysql> alter table hero rename king_hero;
Query OK, 0 rows affected (0.01 sec)
#添加表字段
mysql> alter table king_hero add is_pay boolean;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查看添加后的表结构信息，boolean会被转化为tinyint
mysql> desc king_hero;
+--------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| introduction | varchar(50) | YES | | NULL | |
| is_pay | tinyint(1) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
#删除表字段
mysql> alter table king_hero drop is_pay;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
#修改表字段类型
mysql> alter table king_hero modify price decimal(5,2);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc king_hero;
+--------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| price | decimal(5,2) | YES | | NULL | |
| introduction | varchar(50) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#增加主键约束,自增，注意主键值是不能重复的！
mysql> alter table king_hero modify id int(11) not null primary key auto_increme
nt;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc king_hero;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| price | decimal(5,2) | YES | | NULL | |
| introduction | varchar(50) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
此时新增记录,主键id自动加一
mysql> insert into king_hero(name) values("孙悟空");
Query OK, 1 row affected (0.00 sec)
mysql> select * from king_hero;
+----+--------+--------+--------+----------------+
| id | name | sex | price | introduction |
+----+--------+--------+--------+----------------+
| 1 | 妲己 | female | 888.00 | 爆发高，控制强 |
| 2 | NULL | NULL | NULL | NULL |
| 3 | 孙悟空 | NULL | NULL | NULL |
+----+--------+--------+--------+----------------+
3 rows in set (0.00 sec)
1.2.6. 复制数据表
复制数据表可以有2种模式
1.复制表结构和数据,不会复制key，主键，外键和索引
mysql> create table new_king_hero select * from king_hero;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| king_hero |
| new_king_hero |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from new_king_hero;
+----+--------+--------+--------+----------------+
| id | name | sex | price | introduction |
+----+--------+--------+--------+----------------+
| 1 | 妲己 | female | 888.00 | 爆发高，控制强 |
| 2 | NULL | NULL | NULL | NULL |
| 3 | 孙悟空 | NULL | NULL | NULL |
+----+--------+--------+--------+----------------+
3 rows in set (0.00 sec)
2.仅复制表结构，不要数据，且不携带key信息
mysql> create table new3_king_hero select * from king_hero where 1>2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> select * from new3_king_hero;
Empty set (0.00 sec)
mysql> desc new3_king_hero;
+--------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| price | decimal(5,2) | YES | | NULL | |
| introduction | varchar(50) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
复制表结构，不要数据，携带key信息
mysql> create table new4_king_hero like king_hero;
Query OK, 0 rows affected (0.01 sec)
mysql> desc new4_king_hero;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| price | decimal(5,2) | YES | | NULL | |
| introduction | varchar(50) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1.2.7. 删除数据表
mysql>
mysql> drop table new4_king_hero;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table new3_king_hero;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table new2_king_hero;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table new_king_hero;
Query OK, 0 rows affected (0.00 sec)