本章是整理知识内容，为强化知识长期更新。
Mysql介绍
Mysql数据库是一种C\S结构，就是客户端和服务端。
关键字
DB : DataBase 数据库
DBMS：Database Management System数据库管理系统
DBS：Database System = DBMS + DB数据库系统
DBA：Database Administrator 数据库管理员
行\列：二维表中存储的数据。
行row\record （一条记录）。
列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认证用户名，认证用户名对应的密码。
断开链接
案例
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命令界面的时候，命令前面会出现一个标识符。我们可以自定义。
参数
描述
\D
完整的日期
\d
当前数据库
\h
服务器的名称
\u
当前用户名
我们使用Mysql常用的参数配合PROMPT来自定义提示符信息，默认的刚才我们登陆之后发现默认的提示符是mysql>
这里演示两种使用方式
连接客户端的时候通过参数指定。mysql -uroot -pxxxx --prompt xxx xxx分别代表密码和标识符。
连接客户端之后，通过PROMPT指定。
通过my.cof配置文件全局设置。
案例
1 $ mysql -uroot -pxxxxxxxxxx --prpmpt @\\h
输出
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配置文件并修改相关参数。
登出mysql后编辑my.cof文件。
1 [root@VM_115_109_centos ~]
修改文件添加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 ~]
这里是Centos7.3的环境演示。再次登陆mysql。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 [root@VM_115_109_centos ~] 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语句的规范。
演示语句
显示当前服务器的版本：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]>
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 14 [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)
注意删除数据库之前记得备份，不然会悲剧。
回顾总结。
新增：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。
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 10 # 先做一个简单的演示，使用*标识所有的列。 [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 16 # 创建表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 30 # 创建测试表 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 17 # 模拟一条测试数据。 [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 37 38 # 创建一张测试表。只有两个字段，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 52 # 这里使用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 61 # 创建一张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 15 # 这里使用一张表进行删除。这里吧重复密码的账号进行删除，但是只保留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子句。字段顺序、字段的别名会影响结果集。
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 10 # 回到 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 27 # 使用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种常用的约束，下面主要介绍表之前的约束。
外键约束
父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
数据表的存储引擎只能是InnoDB。
外键列和参照列必须具有相同的数据类型，其中数字的长度或是否有符号UNSIGNED必须一致；而字符的长度可以不同。
外键列和参照列必须创建索引，如果没有创建Mysql也会自动创建。
由于本人开发中，使用外键的场景比较少，所以这里最后在补充。
数据库存储引擎 数据库默认的的存储引擎可以在my.cof中设置。
1 default-storage-engine=INNODB
特点
MyISAM
InnoDB
Memory
Archive
存储限制
256TB
64TB
有
无
事务安全
-
支持
-
-
支持索引
支持
支持
支持
-
锁颗粒
表锁
行锁
表锁
行锁
数据压缩
支持
-
-
支持
支持外键
-
支持
-
-
看到这张表很清楚了，这就是为什么InnoDB用的很广泛的原因。
Mysql数据类型
数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它们决定了数据存储的格式，代表了不同的数据信息类型。我们的计算都是二进制来存储数据的，当然这还牵扯到一个问题就是正数和负数。
数据类型思维导图
数据类型存储范围说明
所谓的无符号就是unsigned，这样会使大小翻倍。
整型
TINYINT 迷你整数型
SMALLINT 小整型
MEDIUMINT 中整型
INT 标准整型
BIGINT 大整型
用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 有符号 -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测试 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 单精度浮点型
DOUBLE 双精度浮点型
在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 插入失败。所以插入的时候也需要注意。
定点型
定点型数据，绝对的保证整数部分不会被四舍五入，也就是说不会损失经度，但是小数部分有可能损失精度，虽然理论上小数部分也不会丢失精度。
SQL测试 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 # 与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 年份
TIME 时间
DATE 日期
DATETIME 日期时间
TIMESTAMP 时间戳
由于存在跨时区的问题，所以一般都是存数字日期，日期类型用的相对会比较少。
补充类型概况
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测试 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)
VARCHAR(M)
字节：L + 1个字节 , 其中L <= M 且 0 <= M <= 65535
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
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
