MySQL基础（一）

本章是整理知识内容，为强化知识长期更新。

Mysql介绍

Mysql数据库是一种C\S结构，就是客户端和服务端。
关键字
1. DB : DataBase 数据库
2. DBMS：Database Management System数据库管理系统
3. DBS：Database System = DBMS + DB数据库系统
4. DBA：Database Administrator 数据库管理员
5. 行\列：二维表中存储的数据。
  1. 行row\record （一条记录）。
  2. 列column\field（一个字段）。
SQL

SQL：Structured Query Language，结构化查询语言（数据以查询为主，99% 都是在进行查询操作），SQL是关系型数据库的操作指令，是一种约束，但不强制，类似与W3C，所以不同的关系型数据SQL语句可能会有一定的差异性。

DDL：Data Definition Language数据定义语言，用来维护存储数据的结构（数据库、表），代表指令为create、drop和alter等。
DML：Data Manipulation Language，数据操作语言，用来对数据进行操作（表中的内容）代表指令为insert、delete和update等，不过在 DML 内部又单独进行了一个分类，即 DQL（Data Query Language），数据查询语言，代表指令为select。
DCL：Data Control Language，数据控制语言，主要是负责（用户）权限管理，代表指令为grant和revoke等。
TPL：``,事物控制语言

Mysql基本操作

本章部分内容在Centos中实现，Centos7-MySql部署记录

交互方式

客户链接认证，即链接服务端，认证 mysql -hPup

mysql -hPup介绍
- -h 主机地址，本地是localhost或者127.0.0.1，远程为IP地址。
- -P端口号，一般默认是3306，所以不指定就是3306。
- -u用户名,认证需要用户名。
- -p认证用户名，认证用户名对应的密码。
断开链接
- exit
- quit
- \q
案例

$ mysql -uroot -pxxxxxxxxxx

xxxx是密码若登陆成功则输出，这里的警告不用太在意，是指登陆没有指定对应的数据库实例。

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2029
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> \q；
mysql> exit;
mysql> quit;

这三个指令都是等处操作。

Mysql标识符

当我们进入mysql命令界面的时候，命令前面会出现一个标识符。我们可以自定义。

PROMPT介绍
- 使用PROMPT xxx指定标识符。
案例

参数	描述
\D	完整的日期
\d	当前数据库
\h	服务器的名称
\u	当前用户名

我们使用Mysql常用的参数配合PROMPT来自定义提示符信息，默认的刚才我们登陆之后发现默认的提示符是mysql>

这里演示两种使用方式
1. 连接客户端的时候通过参数指定。mysql -uroot -pxxxx --prompt xxx xxx分别代表密码和标识符。
2. 连接客户端之后，通过PROMPT指定。
3. 通过my.cof配置文件全局设置。
案例

$ mysql -uroot -pxxxxxxxxxx --prpmpt @\\h  #在连接客户端的时候指定需要转义\h-->\\h不然可能出现BUG。

输出

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2029
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

@localhost

登陆后再次修改

@localhost PROMPT \u@\h \d>
PROMPT set to '\u@\h \d>'
root@localhost (none)>

由于没有进入数据库，所以\d是none。那我们查询下当前的数据库，然后选择一个数据库并进入。

root@localhost (none)> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Demo               |
| mysql              |
| performance_schema |
| rbac001            |
| sys                |
+--------------------+
6 rows in set (0.06 sec)

进入Demo数据库

root@localhost (none)>USE Demo
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost Demo>

那么这里mysql的标识符就显示完整了。

还有一种发发就是在my.cof配置文件中指定全局的。

我用的是linux上的mysql，如果是windows在mysql安装目录中寻找my.cof配置文件并修改相关参数。

root@localhost Demo> \q

登出mysql后编辑my.cof文件。

[root@VM_115_109_centos ~]# vim /etc/my.cnf

修改文件添加prompt = [\\u@\\h][\\d]>\\_这个设置，注意这里\是需要转义的。

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
#
[mysql]
prompt = [\\u@\\h][\\d]>\\_

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#设置端口
port=3306
#设置mysql数据存放目录
datadir=/opt/db/mysql
# 服务端使用的字符集默认为8比特编码的latin1字符集  
character-set-server=utf8
# # 创建新表时将使用的默认存储引擎  
default-storage-engine=INNODB

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

保存退出并重启mysql。退出VIM ESC键然后: 在命令行输入wq回车。如果成功保存并退出就重启mysql服务。

[root@VM_115_109_centos ~]# systemctl restart mysqld

这里是Centos7.3的环境演示。再次登陆mysql。

[root@VM_115_109_centos ~]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[root@localhost][(none)]>

[root@localhost][(none)]> USE Demo
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
[root@localhost][Demo]>

到这里全局设置PROMPT就OK了。

Mysql常用命令

这里演示一些经常用到的MYSQL常用命令，方便快速了解Mysql。同时接触一些MYSQL语句的规范。

演示语句
- 显示当前服务器的版本：SELECT VERSION();
- 显示当前日期时间：SELECT NOW();
- 显示当前的用户：SELECT USER();

输出效果

[root@localhost][Demo]> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0.00 sec)

[root@localhost][Demo]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2018-04-29 04:22:31 |
+---------------------+
1 row in set (0.00 sec)

[root@localhost][Demo]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

[root@localhost][Demo]>

MYSQL标准的规范
- 关键字与函数名称全部大写。
- 数据库、表名称、字段名称全部小写。
- SQL语句必须分号结尾。

在不同的系统中大小写是区分的，如果SQL语句不够规范或者数据库、表名、字段名不规范命名，可能会出现严重的BUG。

Mysql数据库的操作

这里做下记录吧，应该是要专门写一篇关于MySQL数据库结构文章。

创建数据库

CREATE { DATABASES | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name。

简单解释下上面的语句，{}花括弧必须要填写的，|是或则选择其中一个即可。[]中括号可有可无。CHARACTER是库的约束项目，指定数据库的编码集。

示例

[root@localhost][Demo]> CREATE DATABASE  db1;
Query OK, 1 row affected (0.00 sec)

这里已经提示我们创建成功了一个数据库db1。检查下当前的数据库列表。

[root@localhost][Demo]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Demo               |
| db1                |
| mysql              |
| performance_schema |
| rbac001            |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

在来看看[IF NOT EXISTS] 这个参数。我们测试下。重复创建db1数据库。

[root@localhost][Demo]> CREATE DATABASE db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists

这里会警告我们，数据已经存在。在上IF NOT EXISTS在试试。

[root@localhost][Demo]> CREATE DATABASE IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)

这里警告会看不到了，我们能不能查询一下警告信息呢？当然是可以的。

[root@localhost][Demo]> SHOW WARNINGS;
+-------+------+----------------------------------------------+
| Level | Code | Message                                      |
+-------+------+----------------------------------------------+
| Note  | 1007 | Can't create database 'db1'; database exists |
+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

其实，使用IF NOT EXISTS 之后警告依然存在，只不过不会立刻显示出来。

查询数据库

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]

查询全部：SHOW DATABASES;
模糊查询：SHOW DATABASES LIKE 'pattern'
- 这里的pattern是指匹配模式，%是指匹配多个字符，_是指匹配单个字符。需要注意的是在匹配有带有_下划线的数据库名字时候，需要添加\反斜杠转义。

创建db1的时候已经使用全局查询了，这里演示下模糊查询。

[root@localhost][Demo]> SHOW DATABASES LIKE "db%";
+----------------+
| Database (db%) |
+----------------+
| db1            |
+----------------+
1 row in set (0.00 sec)

在检查一下数据库的编码级。

[root@localhost][Demo]> SHOW CREATE DATABASE db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

这里查询出创建数据库的指定编码集。是utf8，这里是应为我在my.cof文件中设置了character-set-server=utf8这个参数，所以即便创建数据库时utf8编码，数据库创建默认就是utf8。

那么我们创建一个非默认编码的数据库试试。

[root@localhost][Demo]> CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET gbk;
Query OK, 1 row affected (0.00 sec)

我们在检查下db2是不是指定的编码集。

[root@localhost][Demo]> SHOW CREATE DATABASE db2 ;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

当然并不打算使用gbk来做编码集。

修改数据库

ALTER {DATABASE | SCHEMA } [db_name] [DEFAULT ] CHARACTER SET [=] charset_name

示例

[root@localhost][Demo]> ALTER DATABASE db2 CHARACTER SET = utf8;
Query OK, 1 row affected (0.00 sec)
[root@localhost][Demo]> SHOW CREATE DATABASE db2;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

DROP {DATABASE| SCHEMA} [IF EXISTS] db_name

示例

[root@localhost][Demo]> SHOW CREATE DATABASE db2;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

[root@localhost][Demo]> DROP DATABASE db2;
Query OK, 0 rows affected (0.03 sec)

[root@localhost][Demo]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Demo               |
| db1                |
| mysql              |
| performance_schema |
| rbac001            |
| sys                |
+--------------------+
7 rows in set (0.01 sec)

注意删除数据库之前记得备份，不然会悲剧。

回顾总结。

新增：CREATE { DATABASES | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
查询：SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]
修改：ALTER {DATABASE | SCHEMA } [db_name][DEFAULT ] CHARACTER SET [=] charset_name
删除：DROP {DATABASE| SCHEMA} [IF EXISTS] db_name
SQL语句注意大小写。
数据库操作的语句属于DDL语句。

Mysql数据表操作

数据表，是数据库中重要的结构之一，是存储数据的基础。在关系型数据库中，实际上表就是一个二维的数据结构。

创建数据表

首先需要进入到某个数据库，上面的操作数据库中已经创建了一个库db1。

# 进入db1数据库
[root@localhost][(none)]> USE db1;
Database changed
[root@localhost][db1]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

进入后使用SELECT DATABASE(); 来查询当前进入的数据。

CRATE TABLE [IF NOT EXISTS] table_name(

column_name data_type

)

由于是二维表，所以column_name肯定是确认的，根据存储的数据类型在选择合适的data_type。

[root@localhost][db1]> CREATE TABLE emp(
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,
    -> salary FLOAT(8,2) UNSIGNED
    -> );
Query OK, 0 rows affected (0.12 sec)

创建一张员工表，存储了名字、年龄、工资。其中年龄和工资采用了无符号。

查看数据表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

查询当前数据库全部表：SHOW TABLES ;
查询其他数据库中表：SHOW TABLES FROM mysql
模糊查询当前数据库表：SHOW TABLES LIKE 'pattern'
- 这里的pattern是指匹配模式，%是指匹配多个字符，_是指匹配单个字符。需要注意的是在匹配有带有_下划线的数据表名字时候，需要添加\反斜杠转义。

# 查询当前数据库中全部表。
[root@localhost][db1]> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| emp           |
+---------------+
1 row in set (0.00 sec)

# 查询其他数据库中全部表。
[root@localhost][db1]> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

# 使用 LIKE 做模糊查询
[root@localhost][db1]> SHOW TABLES LIKE 'e%'
    -> ;
+--------------------+
| Tables_in_db1 (e%) |
+--------------------+
| emp                |
+--------------------+
1 row in set (0.00 sec)

查看表结构

SHOW COLUMNS FROM table_name

[root@localhost][db1]> SHOW COLUMNS FROM emp;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改表结构

平时用IDE用多了，估计很少会去写SQL修改表结构。

修改表结构分为两种

修改表本身
- 修改表名 RENAME TBALE ol_table_name TO new_table_name
- 修改表选项 ALTER TABLE table_name table_option [=] option
修改表字段
- 新增字段 ALTER TABLE table_name ADD [colums_name] data_type [data_type] [location]
- 修改字段 ALTER TABLE table_name MODIFY [columns_name] [data_type] [location]
- 重命名字段 ALTER TABLE table_name CHANGE ol_columns_name new_columns_name data_type [columns_type] [location]
- 删除字段 ALTER TABLE table_name DROP columns_name

插入表数据（一）

INSERT [INTO] table_name [(columns_name , ... )] VALUES(value , ...)

# 插入一条模拟数据，默认全部字段都插入。也就没有指定columns的时候所有的字段都需要赋值。
[root@localhost][db1]> INSERT emp VALUE('Tom',23,8432.34);
Query OK, 1 row affected (0.01 sec)

# 插入一条数据，只为部分字段赋值。
[root@localhost][db1]> INSERT emp(username , salary) VALUE('Jon',5600.32);
Query OK, 1 row affected (0.01 sec)

实际上插入数据的时候是有约束的。

简单的查询：SELECT expr，.. FROM table_name

# 先做一个简单的演示，使用*标识所有的列。
[root@localhost][db1]> SELECT * FROM emp;
+----------+------+---------+
| username | age  | salary  |
+----------+------+---------+
| Tom      |   23 | 8432.34 |
| Jon      | NULL | 5600.32 |
+----------+------+---------+
2 rows in set (0.00 sec)

在插入Jon的时候并没有指定age，实际上一般的业务上会出现数据不能为空的情况。比如说刚才创建emp所有的字段都是可以为空值。

列约束：NULL \ NOT NULL

NULL ，字段值可以为空；NOT NULL字段值不可为空。

# 模拟一条数据，并指定age 为空。
[root@localhost][db1]> INSERT emp(username,age) VALUE('Zo',NULL);
Query OK, 1 row affected (0.00 sec)
# 看下存储的结果
[root@localhost][db1]> SELECT * FROM emp;
+----------+------+---------+
| username | age  | salary  |
+----------+------+---------+
| Tom      |   23 | 8432.34 |
| Jon      | NULL | 5600.32 |
| Zo       | NULL |    NULL |
+----------+------+---------+

在创建一张表，并指定部分字段不能为空。

# 创建表emp1 ，指定username 不能为空。
[root@localhost][db1]> CREATE TABLE emp1(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL
    -> );
Query OK, 0 rows affected (0.10 sec)
# 查看下emp1的数据表结构。
[root@localhost][db1]> SHOW COLUMNS FROM emp1;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

从emp2结构中看到，此时的username 已经不能为NULL了。

# 模拟数据,尝试吧NULL赋值到username。
[root@localhost][db1]> INSERT emp1(username,age) VALUE(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null

列约束：AUTO_INCREMENT

AUTO_INCREMENT 自增必须定义成主键使用。

# 如果列约束 AUTO_INCREMENT 不配合主键使用会出现下列警告。
[root@localhost][db1]> CREATE TABLE emp2(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 修改创建表语句，并指定主键
[root@localhost][db1]> CREATE TABLE emp2(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.09 sec)
# 检查下emp2表结构。
[root@localhost][db1]> SHOW COLUMNS FROM emp2;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 插入一条数据试试。
[root@localhost][db1]> INSERT emp2(username) VALUE('Jon');
Query OK, 1 row affected (0.01 sec)
# 查看结果
[root@localhost][db1]> SELECT * FROM emp2;
+----+----------+
| id | username |
+----+----------+
|  1 | Jon      |
+----+----------+
1 row in set (0.00 sec)
# 如果插入的时候指定id的值，看看会发生什么？
[root@localhost][db1]> INSERT emp2(id,username) VALUE(10,'Tom');
Query OK, 1 row affected (0.01 sec)
# 查看结果。
[root@localhost][db1]> SELECT * FROM emp2;
+----+----------+
| id | username |
+----+----------+
|  1 | Jon      |
| 10 | Tom      |
+----+----------+
2 rows in set (0.00 sec)
# 也就是说，如果我们指定插入的值，是可以插入成功的。在插入一条看看
[root@localhost][db1]> INSERT emp2(username) VALUE('King');
Query OK, 1 row affected (0.03 sec)
# 再查看
[root@localhost][db1]> SELECT * FROM emp2;
+----+----------+
| id | username |
+----+----------+
|  1 | Jon      |
| 10 | Tom      |
| 11 | King     |
+----+----------+
3 rows in set (0.00 sec)
#  结果可以看到，指定主键后，在此插入若不指定会在最大的id上进行自增。

列约束：PRIMARY KEY

PRIMARY KEY 主键约束，上面我们使用了AUTO_INCREMENT而它是必须配合PRIMARY KEY使用。但是PRIMARY KEY 可以单独使用。一张表也只能存在一个主键。

# 创建一个表，并指定PRIMARY KEY 但是不使用AUTO_INCRMENT。
[root@localhost][db1]> CREATE TABLE emp3( 
	id SMALLINT UNSIGNED PRIMARY KEY , 
	username VARCHAR(20)
);
Query OK, 0 rows affected (0.09 sec)

主键有一个特性就是保证唯一性，所以不可以重复。

# 检查下emp3表结构，可以看到NULL列是NO。
[root@localhost][db1]> SHOW COLUMNS FROM emp3;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 我们来模拟一次主键重复的情况。
[root@localhost][db1]> INSERT emp3(id,username) VALUES(1,'Tom');
Query OK, 1 row affected (0.01 sec)
# 主键重复异常。
[root@localhost][db1]> INSERT emp3(id,username) VALUES(1,'Jon');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

列约束：UNIQUE KEY

UNIQUE KEY唯一约束，唯一的约束可以保证数据的唯一性，唯一约束的字段可以设置为NULL。每张表可以设置多个唯一约束，这个主键不同。

# 创建一张主键自增并且有唯一约束的表emp4。
[root@localhost][(none)]> CREATE TABLE emp4(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.09 sec)
# 查看下emp4表结构情况。
[root@localhost][Demo]> SHOW COLUMNS FROM emp4;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# 插入数据试试
[root@localhost][Demo]> INSERT emp4(username,age) VALUES('Tom',22);
Query OK, 1 row affected (0.01 sec)
# 重复插入Tom试试，这会提示错误。
[root@localhost][Demo]> INSERT emp4(username,age) VALUES('Tom',22);
ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username'

这里其实有一个疑问，为什么QNIQUE KYE可以为NULL，如果为NULL怎么表示唯一呢？

列约束：DEFAULT

DEFAULT 默认值，如果没有明确字段赋值，则自动赋值为默认值。

# 创建一张表，指定sex DEFAULT 值为 0,如果插入数据的时候不指定，那么这个字段就是0。
[root@localhost][Demo]> CREATE TABLE emp5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> sex TINYINT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.11 sec)

#检查表emp5结构
[root@localhost][Demo]> SHOW COLUMNS FROM emp5;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | tinyint(4)           | YES  |     | 0       |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# 插入模拟数据
[root@localhost][Demo]> INSERT emp5(username) VALUES('Tom');
Query OK, 1 row affected (0.02 sec)
[root@localhost][Demo]> INSERT emp5(username) VALUES('Jon');
Query OK, 1 row affected (0.01 sec)

# 查看数据，并没有指定sex的值，就会使用默认值。
[root@localhost][Demo]> SELECT * FROM emp5;
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | Tom      |    0 |
|  2 | Jon      |    0 |
+----+----------+------+
2 rows in set (0.00 sec)

列约束：小结

列的属性，除了数据类型的约束，就是上面几种列约束。

约束保证数据的完整性。
约束分为表级约束和列级别约束。
约束的类型包括
- NOT NULL 非空约束
- PRIMARY KEY 主键约束
- UNIQUE KEY 唯一约束
- DEFAULT 默认约束
- FOREIGN KEY 外键约束

插入数据需要注意

在插入数据的时候也是存在一些需要注意的地方，下面演示下。

# 创建测试表 users
[root@localhost][Demo]> CREATE TABLE users(
    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(20) UNIQUE KEY,
    -> pwd VARCHAR(30) NOT NULL,
    -> age TINYINT UNSIGNED NOT NULL,
    -> sex TINYINT UNSIGNED DEFAULT 0
    -> );
Query OK, 0 rows affected (0.07 sec)
#看下表的结构。
[root@localhost][Demo]> SHOW COLUMNS FROM users;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | smallint(6)         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | YES  | UNI | NULL    |                |
| pwd   | varchar(30)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | NO   |     | NULL    |                |
| sex   | tinyint(3) unsigned | YES  |     | 0       |                |
+-------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

# 插入数据模拟数据，不指定插入数据的位置。
[root@localhost][Demo]> INSERT users VALUES(NULL,'Tom','_23234awdasdwf',23,NULL);
Query OK, 1 row affected (0.03 sec)

# 再试一次，少插入一个位置的数据。如果省略了列名，就要需要将列数据补全，否则会造成插入失败的情况。
[root@localhost][Demo]> INSERT users VALUES(NULL,'Tom','_23234awdasdwf',23);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

插入时候使用DEFAULT关键字，同时可以使用表达式作为参数。

# 关键子DEFAULT也可以使用，sex字段就有DEFAULT,age 用到了表达式求值
[root@localhost][Demo]> INSERT users VALUES(NULL,'Rose','_23234awdasdwf', 2*3 + 11,DEFAULT);
Query OK, 1 row affected (0.00 sec)

# 一次性插入多条数据
[root@localhost][Demo]> INSERT users VALUES(NULL,'Atom','pwd',21,DEFAULT),(NULL,'Per','pwd',23,DEFAULT);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  17 | NULL |
|  4 | Rose | _23234awdasdwf |  17 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
+----+------+----------------+-----+------+

插入表数据（二）

INSERT [INTO] table_name SET columns_name ={ expr | DEFAULT},...

与第一种插入方式不同的是，此方法可以使用子查询（SubQuery）;而且这方式一次只能插入一条数据。

# 模拟一条测试数据。
[root@localhost][Demo]> INSERT users SET name = 'CoCo', pwd = 'pwd',age = 22;
Query OK, 1 row affected (0.01 sec)
# 查询结果。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  17 | NULL |
|  4 | Rose | _23234awdasdwf |  17 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  7 | CoCo | pwd            |  22 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

插入表数据（三）

INSERT [INTO] tablle_name [(columns_name,...)] SELECT ...

这种方式，可以将查询结果插入到指定的数据表中。

# 创建一张测试表。只有两个字段，id 和 name。
[root@localhost][Demo]> CREATE TABLE test(
    -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(30) 
    -> );
Query OK, 0 rows affected (0.07 sec)

# 尝试将users表中的数据插入到test中。
# 先看看当前users表中的数据。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  18 |    1 |
|  3 | Jon  | _23234awdasdwf |  17 |    1 |
|  4 | Rose | _23234awdasdwf |  17 |    0 |
|  5 | Atom | pwd            |  16 |    0 |
|  6 | Per  | pwd            |  18 |    0 |
|  8 | CoCo | agc            |  17 |    1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 尝试将 age > 17 的name插入到test中。注意了，插入几个值和查询的值要匹配。这里提示2条成功
[root@localhost][Demo]> INSERT test(name) SELECT name FROM users WHERE age > 17;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

#检查结果，这里可以看到 users 表中 age > 17 的name值插入到test中。
[root@localhost][Demo]> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
|  1 | Tom  |
|  2 | Per  |
+----+------+
2 rows in set (0.00 sec)

更新表数据（一）

UPDATE [LOW_PRIORITY] [IGNORE] tabel_refernce SET columns_name1 = { expre1 | DEFAULT} , columns_name2 = { expre2 | DEFAULT}] ... [WHERE where_condition]

单表更新，这里需要注意的是，如果不添加where判断条件，会导致该表所有记录都被更新。

# 这里使用users表中的数据。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  17 | NULL |
|  4 | Rose | _23234awdasdwf |  17 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  7 | CoCo | pwd            |  22 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 这条SQL语句表示让这个表中所有的age 都 + 5；
[root@localhost][Demo]> UPDATE users SET age = age + 5;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

# 查看结果，已经全部更新。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  28 | NULL |
|  3 | Jon  | _23234awdasdwf |  22 | NULL |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  26 |    0 |
|  6 | Per  | pwd            |  28 |    0 |
|  7 | CoCo | pwd            |  27 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 添加where_condition。将age > 25 的记录 age - 5；
[root@localhost][Demo]> UPDATE users SET age = age - 5 WHERE age > 25;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

# 查看结果
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  22 | NULL |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  7 | CoCo | pwd            |  22 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

更新表数据（二）

多表更新: UPDATE table_reference SET columns_name = { expr1 | DEFAULT } [ columns_name2 = { expr2 | DEFAULT }]... [WHERE where_condition]
但是呢多表更新实际上是需要使用要连接，这里不做过多的皆是，简单的演示一下。

# 创建一张user_names 表来存储用户的名字，其实没啥意义，就是演示下。
[root@localhost][Demo]> CREATE TABLE user_names(
    -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(40) NOT NULL,
    -> uid TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

# 检查下表结构
[root@localhost][Demo]> SHOW COLUMNS FROM user_names;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40)         | NO   |     | NULL    |                |
| uid   | tinyint(3) unsigned | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# 我们先吧用户的名字和主键都插入进来。
[root@localhost][Demo]> INSERT INTO user_names(uid,name) SELECT id , name FROM users ;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

[root@localhost][Demo]> SELECT * FROM user_names;
+----+------+-----+
| id | name | uid |
+----+------+-----+
|  1 | Atom |   5 |
|  2 | CoCo |   8 |
|  3 | Jon  |   3 |
|  4 | Per  |   6 |
|  5 | Rose |   4 |
|  6 | Tom  |   1 |
+----+------+-----+
6 rows in set (0.00 sec)

# 这个时候吧users表中某个用户的名字修改一次，在更新到user_names表中去。
[root@localhost][Demo]> UPDATE users SET name = 'Misa' WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 最后演示多表更新
[root@localhost][Demo]> UPDATE user_names AS un INNER JOIN users AS u ON un.uid = u.id SET un.name = u.name;
Query OK, 1 row affected (0.00 sec)
Rows matched: 6  Changed: 1  Warnings: 0

# 这里可以看到uid 为 1 的名字已经发生了变化。
[root@localhost][Demo]> SELECT * FROM user_names;
+----+------+-----+
| id | name | uid |
+----+------+-----+
|  1 | Atom |   5 |
|  2 | CoCo |   8 |
|  3 | Jon  |   3 |
|  4 | Per  |   6 |
|  5 | Rose |   4 |
|  6 | Misa |   1 |
+----+------+-----+
6 rows in set (0.00 sec)

删除表数据（一）

DELETE FROM table_name [WHERE where_condition]

单条删除，如果省略where_condition将删除所有数据。

# 删除一条id为7的记录。
[root@localhost][Demo]> DELETE FROM users WHERE id = 7;
Query OK, 1 row affected (0.01 sec)

# 检查结果
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Misa | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  22 | NULL |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
+----+------+----------------+-----+------+
5 rows in set (0.00 sec)

这里需要注意的是，如果是自增类型主键，当被删除了最大的主键之后。再次插入数据并不会在现在的最大主键上自增。

# 在添加一条测试数据来验证下主键自增的规律。
[root@localhost][Demo]> INSERT users VALUES(NULL,'CoCo','agc',22,1);
Query OK, 1 row affected (0.05 sec)
# 结果发现直接从6到8,而7是刚才被删除的记录。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Misa | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  22 | NULL |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  8 | CoCo | agc            |  22 |    1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

删除表数据（二）

DELETE table_name[.*] [, table_name[.*]] ... FROM table_referneces [WHERE where_condition]

# 这里使用一张表进行删除。这里吧重复密码的账号进行删除，但是只保留id最小的那一个。实际上这个操作没啥用。
[root@localhost][Demo]> DELETE t1 FROM users AS t1 LEFT JOIN (SELECT id , pwd  FROM users GROUP BY pwd HAVING count(pwd) >= 2 ) AS t2 ON t1.pwd  = t2.pwd WHERE t1.id > t2.id;
Query OK, 3 rows affected (0.02 sec)

# 在检查一下。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Misa | _23234awdasdwf |  18 |    1 |
|  5 | Atom | pwd            |  16 |    0 |
|  8 | CoCo | agc            |  17 |    1 |
| 10 | Pet  | abc            |  22 |    1 |
+----+------+----------------+-----+------+

查询表数据（一）

在通常情况下，大部分的SQL都是查询语句，所以查询最后说明。

SELECT select_expr[ select_expre ...]

[

FROM table_reference

[WHERE where_condition]

[GROUP BY {columns_name | position}] [ASC | DESC]

[HAVING where_condition]

[ORDER BY {columns_name | expre | position}] [ASC | DESC]

[LIMIT {[offset] row_count | row_count OFFSET}]

]

查询表达式 `SELECT_EXPR`

每个表达式标识想要的一列，必须至少有一列；多列之间使用逗号分割。* 号表示全部，table_name.* 表示该命名表的所有列。
查询表达式可以使用[AS] alisa_name 赋予别名。同时别名可用与GROUP BY ,ORDER BY 或者HAVING子句。字段顺序、字段的别名会影响结果集。

# 依然使用users来做测试数据。
# 只查询users表中id 和 name;并且使用使用alisa_name。
[root@localhost][Demo]> SELECT u.id AS uid , u.name AS uname FROM users AS u;
+-----+-------+
| uid | uname |
+-----+-------+
|   5 | Atom  |
|   8 | CoCo  |
|   3 | Jon   |
|   6 | Per   |
|   4 | Rose  |
|   1 | Tom   |
+-----+-------+
6 rows in set (0.00 sec)

# 使用陷阱,如果使用alisa_name 而没有使用AS 关键就会导致下面的问题。可能是少写了一个逗号，而结果确实一个结果集。
[root@localhost][Demo]> SELECT id name FROM users;
+------+
| name |
+------+
|    5 |
|    8 |
|    3 |
|    6 |
|    4 |
|    1 |
+------+
6 rows in set (0.00 sec)

条件表达式 `GROUP BY`

GROUP BY {columns_name | position} [ASC | DESC],...

如果没有指定WHERE子句，则显示全部数据。在WHERE表达式中，可以使用MYSQL内置函数或者是运算符。
ASC 是升序、DESC是降序，若存在多个分组条件那就用逗号分割。

# 依然使用users表中的测试数据，但是要将sex 为NULL的数据进行修改。
[root@localhost][Demo]> UPDATE users SET sex = 1 WHERE sex IS NULL;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  8 | CoCo | agc            |  22 |    1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 使用GROUP BY 来做分组。这里分组的结果就是0和1。
[root@localhost][Demo]> SELECT sex FROM users GROUP BY sex;
+------+
| sex  |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.00 sec)

分组条件 `HAVING`

[HAVING where_condition]

使用HAVING只对一部分数据来分组。需要注意的是，如果使用HAVING语句，那么在查询结果中就存在对应的字段。并切与函数进行配合。

这里需改修改一处配置信息，由于本人使用的是MYSQL5.7版本，所以GROUP BY和以往不一样。

# 检查当前sql_mode;
select @@sql_mode;

5.7中默认设置了sql_mode=only_full_group_by

修改下my.cof。在[mysqld]下面添加

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

就是不要only_full_group_by这个。

简单解释下only_full_group_by是干嘛的。

使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行

# 回到 HAVING分组条件上。
[root@localhost][Demo]> SELECT sex , age FROM users GROUP BY sex HAVING age > 21;
+------+-----+
| sex  | age |
+------+-----+
|    0 |  22 |
|    1 |  23 |
+------+-----+
2 rows in set (0.00 sec)

结果排序 `ORDER BY`

ORDER BY {columns_name | expr | position} [ASC | DESC],...

# 使用ORDER BY 来做排序 DESC ，由于默认是ASC升序；所以这里用DESC降序来演示。
[root@localhost][Demo]> SELECT * FROM users ORDER BY id DESC;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  8 | CoCo | agc            |  22 |    1 |
|  6 | Per  | pwd            |  23 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 对多个字段进行排序。这里可以看到虽然CoCo 和 Roes 都是22age 但是id采用了降序。所以CoCo排在前面。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age , id DESC;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  5 | Atom | pwd            |  21 |    0 |
|  8 | CoCo | agc            |  22 |    1 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  6 | Per  | pwd            |  23 |    0 |
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
+----+------+----------------+-----+------+

限制查询 `LIMIT`

LIMIT {offset,} row_count | row_count OFFSET offset

限制查询结果的返回集合。

# 这会返回第一和第二条数据。这个LIMIT和ID的排序没有一点关系的。
[root@localhost][Demo]> SELECT * FROM users LIMIT 2;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
+----+------+----------------+-----+------+

# 需要注意的是LIMIT限制查询和其他条件是没有关系的，它仅仅是在结果集中过滤。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age DESC LIMIT 2;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
|  6 | Per  | pwd            |  23 |    0 |
+----+------+----------------+-----+------+
2 rows in set (0.00 sec)

# 还有一种情况就是范围。先看看全部数据。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age DESC;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
|  6 | Per  | pwd            |  23 |    0 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  8 | CoCo | agc            |  22 |    1 |
|  5 | Atom | pwd            |  21 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 我们尝试获取结果集中第三条和第四条。这里需要注意的时候结果集的下标是从0开始的。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age DESC LIMIT 2 , 2;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
+----+------+----------------+-----+------+
2 rows in set (0.00 sec)

数据库表操作小结

插入
- INSERT [INTO] table_name [(columns_name , ... )] VALUES(value , ...)
- INSERT [INTO] table_name SET columns_name ={ expr | DEFAULT},...
- INSERT [INTO] tablle_name [(columns_name,...)] SELECT ...
更新
- UPDATE [LOW_PRIORITY] [IGNORE] tabel_refernce SET columns_name1 = { expre1 | DEFAULT} , columns_name2 = { expre2 | DEFAULT}] ... [WHERE where_condition]
删除
- DELETE FROM table_name [WHERE where_condition]
查询
- SELECT select_expr[ select_expre ...]
  [
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY {columns_name | position}] [ASC | DESC]
  [HAVING where_condition]
  [ORDER BY {columns_name | expre | position}] [ASC | DESC]
  [LIMIT {[offset] row_count | row_count OFFSET}]
  ]

约束

实际上分为两种，表约束和列约束。列属性的约束上面已经讲述了，现在来讲述下表约束。

列约束

在之前的插入表数据（一）中已经演示了5种常用的约束，下面主要介绍表之前的约束。

外键约束

父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
数据表的存储引擎只能是InnoDB。
外键列和参照列必须具有相同的数据类型，其中数字的长度或是否有符号UNSIGNED必须一致；而字符的长度可以不同。
外键列和参照列必须创建索引，如果没有创建Mysql也会自动创建。

由于本人开发中，使用外键的场景比较少，所以这里最后在补充。

数据库存储引擎

数据库默认的的存储引擎可以在my.cof中设置。

default-storage-engine=INNODB

Mysql可以将数据以不同的技术存储在文件（内存）中，这种技术就成为存储引擎。
每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。
引擎类型
- MyISAM
- InnoDB
- Memory
- CSV
- Archive
而确定使用那种引擎取决与引用场景。
- 并发控制：当多个连接对记录经行修改时候，保证数据的一致性和完整性
- 锁：共享锁（读锁）、排它锁（写锁）。
  - 读锁同一个时间内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
  - 写锁在任何时候只能有一个用户能写入资源，当其他写锁时会阻塞其他读锁或则写锁操作。
  - 锁颗粒
    - 表锁（开销很小的锁策略）、行锁（开销很大的锁策略）。
- 事务（ACID）：保证数据库的完整性
  - 原子性
  - 一致性
  - 隔离性
  - 持久性
- 索引

特点	MyISAM	InnoDB	Memory	Archive
存储限制	256TB	64TB	有	无
事务安全	-	支持	-	-
支持索引	支持	支持	支持	-
锁颗粒	表锁	行锁	表锁	行锁
数据压缩	支持	-	-	支持
支持外键	-	支持	-	-

看到这张表很清楚了，这就是为什么InnoDB用的很广泛的原因。

Mysql数据类型

数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它们决定了数据存储的格式，代表了不同的数据信息类型。我们的计算都是二进制来存储数据的，当然这还牵扯到一个问题就是正数和负数。

数据类型思维导图

数据类型存储范围说明

所谓的无符号就是unsigned，这样会使大小翻倍。

整型

TINYINT 迷你整数型
- 字节：1
SMALLINT 小整型
- 字节：2
MEDIUMINT 中整型
- 字节：3
INT 标准整型
- 字节：4
BIGINT 大整型
- 字节：8

用Java代码测试下。

public static void main(String[] args) {
        System.out.println("采用移位运算所以次方所以相比次方的数值会减一");

        System.out.println("TINTITN 有符号 "+ (-2L << 6) + " 到 " +((2L << 6) - 1));
        System.out.println("TINTITN 无符号 0 到 "+(Math.pow(2L,8) - 1));

        System.out.println("SMALLINT 有符号 "+ (-2L << 14) + " 到 " +((2L << 14) - 1));
        System.out.println("SMALLINT 无符号 0 到 "+((2L << 14) - 1));

        System.out.println("MEDIUMINT 有符号 "+ (-2L << 22) + " 到 " +((2L << 22) - 1));
        System.out.println("MEDIUMINT 无符号 0 到 "+((2L << 23) - 1));

        System.out.println("INT 有符号 "+ (-2L << 30) + " 到 " +((2L << 30) - 1));
        System.out.println("INT 无符号 0 到 "+((2L << 31) - 1));

        System.out.println("BIGINT 有符号 "+ (-2L << 62) + " 到 " +((2L << 62) - 1));
        System.out.println("BIGINT 无符号 0 到 "+ ((2L << 63) - 1));
    }

输出

采用移位运算所以次方所以相比次方的数值会减一
TINTITN 有符号 -128 到 127
TINTITN 无符号 0 到 255.0
SMALLINT 有符号 -32768 到 32767
SMALLINT 无符号 0 到 32767
MEDIUMINT 有符号 -8388608 到 8388607
MEDIUMINT 无符号 0 到 16777215
INT 有符号 -2147483648 到 2147483647
INT 无符号 0 到 4294967295
BIGINT 有符号 -9223372036854775808 到 9223372036854775807
BIGINT 无符号 0 到-1
# 这里可以看到BIGINT 无符号Java long数据类型已经满足不了了。

在数据优化的原则上，合理的选择存储数据类型。而不是盲目的选择数据类型大。比如我们的年龄用TINTINT 就够了。也就说在指定数据类型的时候，一般是采用从小原则，这样会对MYSQL的效率提高很多。当然做好设计。

SQL测试

# 创建int类型的表看看。
[root@localhost][Demo]> CREATE TABLE test_int(
    -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,
    -> int_1 TINYINT ,
    -> int_2 SMALLINT ,
    -> int_3 MEDIUMINT ,
    -> int_4 INT ,
    -> int_5 BIGINT
    -> );
Query OK, 0 rows affected (0.07 sec)
# 查看表结构。
[root@localhost][Demo]> SHOW COLUMNS FROM test_int;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| int_1 | tinyint(4)          | YES  |     | NULL    |                |
| int_2 | smallint(6)         | YES  |     | NULL    |                |
| int_3 | mediumint(9)        | YES  |     | NULL    |                |
| int_4 | int(11)             | YES  |     | NULL    |                |
| int_5 | bigint(20)          | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

#插入测试1，整型插入。成功。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,1,2,3,4,5);
Query OK, 1 row affected (0.01 sec)

#插入测试2,用单引号包裹数字。竟然成功了。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,'1','2','3','4','5');
Query OK, 1 row affected (0.01 sec)

#插入测试3，用字符串测试。肯定是插入不成功。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,'a','b','c','d','e');
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'int_1' at row 1

#测试下有符号的情况，这里提示int_1 超过范围了。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,-200,2,3,4,5);
ERROR 1264 (22003): Out of range value for column 'int_1' at row 1

#修改参数试试，成功了。TINYINT 有符号 是 -128 到 127。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,-128,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

#修改 int_1 的属性 修改成UNSIGNED。无符号。注意在修改字段属性的时候，如果已经存在数据请确认存在的数据是否有不合法的。
[root@localhost][Demo]> ALTER TABLE test_int MODIFY int_1 TINYINT UNSIGNED;
ERROR 1264 (22003): Out of range value for column 'int_1' at row 3

#为了继续测试，删除掉表中数据。
[root@localhost][Demo]> DELETE FROM test_int;
Query OK, 4 rows affected (0.03 sec)

#重新修改表字段结构。
[root@localhost][Demo]> ALTER TABLE test_int MODIFY int_1 TINYINT UNSIGNED;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 检查是否修改成功。可以看到 int_1 添加了UNSIGNED属性约束了。
[root@localhost][Demo]> SHOW COLUMNS FROM test_int;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| int_1 | tinyint(3) unsigned | YES  |     | NULL    |                |
| int_2 | smallint(6)         | YES  |     | NULL    |                |
| int_3 | mediumint(9)        | YES  |     | NULL    |                |
| int_4 | int(11)             | YES  |     | NULL    |                |
| int_5 | bigint(20)          | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
# 插入TINYINT 最大值试试，OK这里是成功的。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,255,2,3,4,5);
Query OK, 1 row affected (0.00 sec)

# 当超过TINYINT UNSIGNED最大的时候就会抛出异常信息。
[root@localhost][Demo]> INSERT INTO test_int VALUES(NULL,256,2,3,4,5);
ERROR 1264 (22003): Out of range value for column 'int_1' at row 1

这里就演示TINYINT存储范围的情况了，实际上其它证书型基本类似。

实际上有一点需要注意就是TYPE中的花括弧中的数字是干嘛的

# 这个3个是干嘛的呢？
tinyint(3) unsigned

这实际上是显示宽度的意思。当显示的宽度不够的时候可以使用0来填充，使用zreofuill进行零填充，而且零填充会直接导致该数值变成服务号。

# 演示修改表结构,将int_1字段再次修改。
[root@localhost][Demo]> ALTER TABLE test_int MODIFY int_1 TINYINT UNSIGNED ZEROFILL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#检查表结构是否发生变化
[root@localhost][Demo]> SHOW COLUMNS FROM test_int;
+-------+------------------------------+------+-----+---------+----------------+
| Field | Type                         | Null | Key | Default | Extra          |
+-------+------------------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned          | NO   | PRI | NULL    | auto_increment |
| int_1 | tinyint(3) unsigned zerofill | YES  |     | NULL    |                |
| int_2 | smallint(6)                  | YES  |     | NULL    |                |
| int_3 | mediumint(9)                 | YES  |     | NULL    |                |
| int_4 | int(11)                      | YES  |     | NULL    |                |
| int_5 | bigint(20)                   | YES  |     | NULL    |                |
+-------+------------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

# 插入新的数据
[root@localhost][Demo]> INSERT test_int VALUES(NULL,1,2,3,4,5);
Query OK, 1 row affected (0.01 sec)

# 刚才插入的int_1位置的值已经发生了变化。
[root@localhost][Demo]> SELECT * FROM test_int;
+----+-------+-------+-------+-------+-------+
| id | int_1 | int_2 | int_3 | int_4 | int_5 |
+----+-------+-------+-------+-------+-------+
|  5 |   255 |     2 |     3 |     4 |     5 |
|  6 |   001 |     2 |     3 |     4 |     5 |
+----+-------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

填充零的含义更多的就是保证数据的格式。

浮点型

FLOAT 单精度浮点型
- 字节：4
DOUBLE 双精度浮点型
- 字节：8

在SQL中，将小数型分配两种浮点型和定点型两种。

浮点型使用注意事项，如果直接使用float，则标识没有小数部分，如果用了float(M,D),其中M代表长度,D代表小数部分长度，M-D则为整数部分长度。

SQL测试

# 创建测试表test_float
[root@localhost][Demo]> CREATE TABLE test_float(
    -> id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
    -> float_1 float,
    -> float_2 float(6,2),
    -> float_3 float(4,1)
    -> );
Query OK, 0 rows affected (0.07 sec)

# 检查表结构
[root@localhost][Demo]> SHOW COLUMNS FROM test_float;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| float_1 | float               | YES  |     | NULL    |                |
| float_2 | float(6,2)          | YES  |     | NULL    |                |
| float_3 | float(4,1)          | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

#插入模拟数据
[root@localhost][Demo]> INSERT INTO test_float VALUES(NULL,2.1534,11,888.1);
Query OK, 1 row affected (0.00 sec)

#这里需要注意的时候 float(4,2)实际标识的整数位小于4，而不是小于等于。测试下
[root@localhost][Demo]> INSERT INTO test_float VALUES(NULL,2.1534,11,8888.1);
ERROR 1264 (22003): Out of range value for column 'float_3' at row 1
# 结果很明显，8888.1 插入失败。所以插入的时候也需要注意。

定点型

DECIMAL 定点数
- 字节：3

定点型数据，绝对的保证整数部分不会被四舍五入，也就是说不会损失经度，但是小数部分有可能损失精度，虽然理论上小数部分也不会丢失精度。

SQL测试

# 与float比较，看看是否会四舍五入。
[root@localhost][Demo]> CREATE TABLE test_decimal(
    -> id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> float_1 float(10,2),
    -> decimal_1 decimal(10,2)                                                                         
    -> );
Query OK, 0 rows affected (0.09 sec)

# 测试模拟数据
[root@localhost][Demo]> INSERT test_decimal VALUES(NULL,99999999.99 , 99999999.99);
Query OK, 1 row affected, 1 warning (0.00 sec)

# 果然啊，float的被四舍五入了，而decimal保留的小数位。
[root@localhost][Demo]> SELECT * FROM test_decimal;
+----+--------------+-------------+
| id | float_1      | decimal_1   |
+----+--------------+-------------+
|  2 | 100000000.00 | 99999999.99 |
+----+--------------+-------------+
1 row in set (0.00 sec)

日期类型

YEAR 年份
- 字节：1
TIME 时间
- 字节：3
DATE 日期
- 字节：3
DATETIME 日期时间
- 字节：8
TIMESTAMP 时间戳
- 字节：4

由于存在跨时区的问题，所以一般都是存数字日期，日期类型用的相对会比较少。

补充类型概况
- DATATIME：日期时间，其格式为yyyy-MM-dd HH:mm:ss，表示的范围是从 1000 年到 9999 年，有零值，即0000-00-00 0000:00；
- DATA：日期，就是datetime的date部分；
- TIME：时间，或者说是时间段，为指定的某个时间区间之间，包含正负时间；
- TIMESTAMP ：时间戳，但并不是真正意义上的时间戳，其是从1970年开始计算的，格式和datetime一致；
- YEAR ：年份，共有两种格式，分别为year(2)和year(4).

SQL测试

# 创建一日期测试表 test_date
[root@localhost][Demo]> CREATE TABLE test_date(
    -> id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> date_1 DATETIME,
    -> date_2 DATE,
    -> date_3 TIME,
    -> date_4 YEAR,
    -> date_5 TIMESTAMP
    -> );
Query OK, 0 rows affected (0.04 sec)

#检查表结构
[root@localhost][Demo]> SHOW COLUMNS FROM test_date;
+--------+---------------------+------+-----+-------------------+-----------------------------+
| Field  | Type                | Null | Key | Default           | Extra                       |
+--------+---------------------+------+-----+-------------------+-----------------------------+
| id     | tinyint(3) unsigned | NO   | PRI | NULL              | auto_increment              |
| date_1 | datetime            | YES  |     | NULL              |                             |
| date_2 | date                | YES  |     | NULL              |                             |
| date_3 | time                | YES  |     | NULL              |                             |
| date_4 | year(4)             | YES  |     | NULL              |                             |
| date_5 | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

# 添加测试数据
[root@localhost][Demo]> INSERT INTO test_date VALUES(NULL,'2017-05-06 13:15:00','2017-05-06','13:15:00',2018,'2017-05-06 13:15:00');
Query OK, 1 row affected (0.00 sec)

# 检查数据结果
[root@localhost][Demo]> SELECT * FROM test_date;
+----+---------------------+------------+----------+--------+---------------------+
| id | date_1              | date_2     | date_3   | date_4 | date_5              |
+----+---------------------+------------+----------+--------+---------------------+
|  1 | 2017-05-06 13:15:00 | 2017-05-06 | 13:15:00 |   2018 | 2017-05-06 13:15:00 |
+----+---------------------+------------+----------+--------+---------------------+
1 row in set (0.01 sec)

# 验证下时间戳是否会自动更新。
[root@localhost][Demo]> UPDATE test_date SET date_4 = 2017 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 检查下结果
[root@localhost][Demo]> SELECT * FROM test_date;
+----+---------------------+------------+----------+--------+---------------------+
| id | date_1              | date_2     | date_3   | date_4 | date_5              |
+----+---------------------+------------+----------+--------+---------------------+
|  1 | 2017-05-06 13:15:00 | 2017-05-06 | 13:15:00 |   2017 | 2018-05-02 04:36:31 |
+----+---------------------+------------+----------+--------+---------------------+
1 row in set (0.00 sec)
# 这里可以看到 date_5 已经发生了变化。

字符型

CHAR(M)
- 字节：M个字节，0 <= M <= 255
VARCHAR(M)
- 字节：L + 1个字节 , 其中L <= M 且 0 <= M <= 65535
TINYTEXT
- 字节：L + 1个字节，其中L < 2^8。
TEXT
- 字节：L + 2个字节，其中L < 2^16。
MEDIUMTEXT
- 字节：L + 3个字节，其中L < 2^24。
LONGTEXT
- 字节：L + 4个字节，其中 L < 2^32。
ENMU（'value1','value2'）
- 字节：1或者2字节，取决与枚举值的个数（最多65535个值）。
SET（'value1','value2'）
- 字节：1、2、3、4或者8字节，取决与Set成员的数目（最多64个成员）

这里涉及到定长和变长，比如说CHAR（M），如果存储的是ABC但是没有达到指定的长度就会在ABC后面添加空格直至满足长度大小。

字符串分类

定长：CHAR
变长：VARCHAR
文本：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举：ENMU
集合：SET