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数据定义语言,用来维护存储数据的结构(数据库、表),代表指令为createdropalter等。
  • DML:Data Manipulation Language,数据操作语言,用来对数据进行操作(表中的内容)代表指令为insertdeleteupdate等,不过在 DML 内部又单独进行了一个分类,即 DQL(Data Query Language),数据查询语言,代表指令为select
  • DCL:Data Control Language,数据控制语言,主要是负责(用户)权限管理,代表指令为grantrevoke等。
  • TPL:,事物控制语言

Mysql基本操作

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

交互方式

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>
1
2
3
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配置文件全局设置。
  • 案例
1
$ mysql -uroot -pxxxxxxxxxx --prpmpt @\\h  #在连接客户端的时候指定需要转义\h-->\\h不然可能出现BUG。

输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

登陆后再次修改

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

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

1
2
3
4
5
6
7
8
9
10
11
12
root@localhost (none)> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Demo |
| mysql |
| performance_schema |
| rbac001 |
| sys |
+--------------------+
6 rows in set (0.06 sec)

进入Demo数据库

1
2
3
4
5
6
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配置文件并修改相关参数。

1
root@localhost Demo> \q

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 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服务。

1
[root@VM_115_109_centos ~]# systemctl restart mysqld

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[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)]>
1
2
3
4
5
6
[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语句的规范。

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

输出效果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[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]>
  1. MYSQL标准的规范
    • 关键字函数名称全部大写。
    • 数据库表名称字段名称全部小写
    • SQL语句必须分号结尾。

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

Mysql数据库的操作

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

创建数据库

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

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

示例

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
[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数据库。

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

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

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

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

1
2
3
4
5
6
7
[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的时候已经使用全局查询了,这里演示下模糊查询。

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

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

1
2
3
4
5
6
7
[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。

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

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

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

1
2
3
4
5
6
7
[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

示例

1
2
3
4
5
6
7
8
9
[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

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[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)

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

回顾总结。

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

Mysql数据表操作

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

创建数据表

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

1
2
3
4
5
6
7
8
9
10
# 进入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。

1
2
3
4
5
6
[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是指匹配模式,%是指匹配多个字符,_是指匹配单个字符。需要注意的是在匹配有带有_下划线的数据表名字时候,需要添加\反斜杠转义。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
# 查询当前数据库中全部表。
[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

1
2
3
4
5
6
7
8
9
[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 , ...)

1
2
3
4
5
6
7
# 插入一条模拟数据,默认全部字段都插入。也就没有指定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

1
2
3
4
5
6
7
8
9
# 先做一个简单的演示,使用*标识所有的列。
[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字段值不可为空。

1
2
3
4
5
6
7
8
9
10
11
12
# 模拟一条数据,并指定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 |
+----------+------+---------+

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 创建表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了。

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# 如果列约束 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 可以单独使用。一张表也只能存在一个主键。

1
2
3
4
5
6
# 创建一个表,并指定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)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 检查下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。每张表可以设置多个唯一约束,这个主键不同。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 创建一张主键自增并且有唯一约束的表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 默认值,如果没有明确字段赋值,则自动赋值为默认值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# 创建一张表,指定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 外键约束
插入数据需要注意

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 创建测试表 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关键字,同时可以使用表达式作为参数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 关键子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);而且这方式一次只能插入一条数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 模拟一条测试数据。
[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 ...

  • 这种方式,可以将查询结果插入到指定的数据表中。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 创建一张测试表。只有两个字段,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判断条件,会导致该表所有记录都被更新。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
# 这里使用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]
  • 但是呢多表更新实际上是需要使用要连接,这里不做过多的皆是,简单的演示一下。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 创建一张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将删除所有数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 删除一条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)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 在添加一条测试数据来验证下主键自增的规律。
[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]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 这里使用一张表进行删除。这里吧重复密码的账号进行删除,但是只保留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
  1. 每个表达式标识想要的一列,必须至少有一列;多列之间使用逗号分割。 号表示全部,table_name. 表示该命名表的所有列。
  2. 查询表达式可以使用[AS] alisa_name 赋予别名。同时别名可用与GROUP BY ,ORDER BY 或者HAVING子句。字段顺序、字段的别名会影响结果集。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 依然使用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是降序,若存在多个分组条件那就用逗号分割。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 依然使用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和以往不一样。

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

5.7中默认设置了sql_mode=only_full_group_by

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

1
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) 才行

1
2
3
4
5
6
7
8
9
# 回到 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],...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 使用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

  • 限制查询结果的返回集合。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 这会返回第一和第二条数据。这个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种常用的约束,下面主要介绍表之前的约束。
外键约束
  1. 父表和子表必须使用相同的存储引擎,而且禁止使用临时表。
  2. 数据表的存储引擎只能是InnoDB。
  3. 外键列和参照列必须具有相同的数据类型,其中数字的长度或是否有符号UNSIGNED必须一致;而字符的长度可以不同。
  4. 外键列和参照列必须创建索引,如果没有创建Mysql也会自动创建。

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

数据库存储引擎

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

1
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代码测试下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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));
}

输出

1
2
3
4
5
6
7
8
9
10
11
12
采用移位运算所以次方所以相比次方的数值会减一
TINTITN 有符号 -128127
TINTITN 无符号 0255.0
SMALLINT 有符号 -3276832767
SMALLINT 无符号 032767
MEDIUMINT 有符号 -83886088388607
MEDIUMINT 无符号 016777215
INT 有符号 -21474836482147483647
INT 无符号 04294967295
BIGINT 有符号 -92233720368547758089223372036854775807
BIGINT 无符号 0 到-1
# 这里可以看到BIGINT 无符号Java long数据类型已经满足不了了。

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

SQL测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
# 创建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中的花括弧中的数字是干嘛的

1
2
# 这个3个是干嘛的呢?
tinyint(3) unsigned

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 演示修改表结构,将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测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 创建测试表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测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 与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:日期,就是datetimedate部分;
    • TIME:时间,或者说是时间段,为指定的某个时间区间之间,包含正负时间;
    • TIMESTAMP:时间戳,但并不是真正意义上的时间戳,其是从1970年开始计算的,格式和datetime一致;
    • YEAR:年份,共有两种格式,分别为year(2)year(4).
SQL测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# 创建一日期测试表 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
  • 文本:TINYTEXTTEXTMEDIUMTEXTLONGTEXT
  • 枚举:ENMU
  • 集合:SET