MySQL数据类型以及基本使用详解
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.MySQL服务器的主要组件
我们知道MySQL的主要组件主要是由服务端（mysqld）和客户端（mysql）组成的。它们都共用一个配置文件（通常叫做my.cnf），这个配置文件很独特，它需要使用中括号括起来标明是为哪种组件使用的，例如[mysql]下面的指令就表示为客户端配置的参数，如果[mysqld]下面的指令就表示为服务端配置的参数。其实MySQL的客户端组件有很多个，本篇博客是用的mysql命令只是MySQL客户端之一，其他常见的客户端如：Navicat for Mysql，EMSSQL ManagerforMySQL等等。
在Linux操作系统中我还是推荐使用mysql客户端的，如果在windows的话我推荐使用Navicat for Mysql，mysql在Linux连接服务器是很简单，我们用mysql连接数据库的时候通常会用到三个参数，分别是“-u(指定登录数据库的用户名)”，“-h（指定登录数据库的主机名）”,“-p（指定登录数据库的密码）”，“-e（指定SQL命令即可执行）”等等。
二.MySQL用户的分类
其实在MySQL初始化时，它会默认创建两类账户，一种是管理员用户（root），另外一种是匿名用户。
root: //管理员用户
127.0.0.1 //IPV4本地回环地址
localhost //本机的主机名
Hostname //主机名
:: //IPV6本地回环地址
" ": //匿名用户
localhost
hostname
这两类用户是系统默认的，当然我们不推荐直接使用它们。因为它们很不安全（只要看了我这篇博客的人都能通过这种方式去连接你的MySQL，因为他们都知道mysql的用户信息。），第一，匿名用户访问无法记录来访者，谁都可以使用，就类似于FTP服务器。第二，管理员用户默认是没有密码的，这就更不全啦，就好像你的办公电脑没有设置密码你很没有安全感似的。因此，我们需要删除不必要的用户，比如MySQL服务默认的2个匿名用户，给我们需要留存的用户设置密码[“SET PASSWORD FOR 'username'@'hostname/IP' = PASSWORD('yinzhengjie');”]。
三.MySQL客户端
1.客户端命令
客户端命令表示是在客户端执行的命令，我们只需要成功连接数据库之后输入“help”，就可以看到mysql客户端的命令啦。客户端的命令有个特点就是不需要命令结束符，比如“;”。
mysql> help For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/ List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement. For server side help, type 'help contents' mysql>
mysql> status
--------------
mysql Ver 14.14 Distrib 5.5., for linux2. (x86_64) using readline 5.1 Connection id:
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.-log MySQL Community Server (GPL)
Protocol version:
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.003
-------------- mysql>
stsauts #查询运行状态
[root@yinzhengjie ~]# mysql -u root -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> \q
Bye
[root@yinzhengjie ~]#
\q #退出当前数据库
[root@yinzhengjie ~]# mysql -u root -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> \? For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/ List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement. For server side help, type 'help contents' mysql>
\? #获取帮助信息，相当于help指令。
[root@yinzhengjie ~]# mysql -u root -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
mysql> select user()\g
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
row in set (0.00 sec) mysql>
\g #直接将命令送到服务器端执行，这种情况适用于你不知道当前数据库的命令结束符是什么，换句话说，默认的命令结束符“;”被修改的情况下可以使用它
[root@yinzhengjie ~]# mysql -u root -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> use mysql
Database changed
mysql> select User,Host,Password from user;
+------+-------------+-------------------------------------------+
| User | Host | Password |
+------+-------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | yinzhengjie | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | :: | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| | localhost | |
| | yinzhengjie | |
+------+-------------+-------------------------------------------+
rows in set (0.00 sec) mysql>
mysql> select User,Host,Password from user\G
*************************** . row ***************************
User: root
Host: localhost
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
*************************** . row ***************************
User: root
Host: yinzhengjie
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
*************************** . row ***************************
User: root
Host: 127.0.0.1
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
*************************** . row ***************************
User: root
Host: ::
Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
*************************** . row ***************************
User:
Host: localhost
Password:
*************************** . row ***************************
User:
Host: yinzhengjie
Password:
rows in set (0.00 sec) mysql>
\G #不再显示为表格显示，而是以列的方式显示，这种情况适合在默认的表格显示不下时（也就是说，字段量非常大时），这个参数就很有用哟。
[root@yinzhengjie ~]# mysql -u root -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> select \c
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
rows in set (0.01 sec) mysql>
mysql> sdas sad as \c
mysql>
mysql>
\c #取消命令的执行，它很适合当你不想继续执行该行代码时使用，相当于Linux系统的ctrl+c哟。
[root@yinzhengjie ~]# mysql -u root -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
rows in set (0.00 sec) mysql> \umysql
Database changed
mysql>
mysql> use mysql
Database changed
mysql>
\u #使用默认的数据库，你可以理解称切换数据库目录，相当于Linux的cd命令哟
2.服务器端命令
服务器端命令简直就时多了去了，比客户端的也复杂的多，因此我在这里也不打算一一举例，而是用一些常用的命令进行扫盲模式。执行服务端的命令需要语句终止符，通常默认为分号(;)，当然这个命令结束符(;)是可以被修改的。
a>.SELECT命令的基本使用
MySQL服务器端有很多内建函数（ 简称BIF）。使用select命令去执行一个内建函数，并将该内建函数的执行结果返回给当前用户。
mysql> select user(); #查看数据库的用户信息。
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
row in set (0.02 sec) mysql>
mysql> select current_time(); #查看当前操作系统的时间。
+----------------+
| current_time() |
+----------------+
| :: |
+----------------+
row in set (0.00 sec) mysql>
select #执行内建函数案例
mysql> select +; #利用select做算术运算。
+---------+
| + |
+---------+
| |
+---------+
row in set (0.00 sec) mysql>
mysql> select *;
+-----+
| * |
+-----+
| |
+-----+
row in set (0.00 sec) mysql>
select #可用于算术运算的案例
b>.SHOW命令的基本使用
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
rows in set (0.00 sec) mysql>
SHOW DATABASES #显示自己有权限可以查看所有库
mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
rows in set (0.00 sec) mysql>
SHOW TABLES #显示某库的所有表
c>.CRETARE命令的基本使用
[root@yinzhengjie ~]# mysql -u root -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
rows in set (0.00 sec) mysql> create database yinzhengjie;
Query OK, row affected (0.01 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yinzhengjie |
+--------------------+
rows in set (0.00 sec) mysql>
CRETARE DATABASE database_name #创建数据库,相当于Linux的mkdir命令哟~因此，你可以去你的数据库目录去创建目录，不过要注意的是，你创建的目录权限要进行修改哟，如果你用root创建的目录，在数据库中mysql用户可能无权操作该目录哟
d>.DROP命令的基本使用
[root@yinzhengjie ~]# mysql -u root -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yinzhengjie |
+--------------------+
rows in set (0.00 sec) mysql> drop database yinzhengjie;
Query OK, rows affected (0.01 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
rows in set (0.00 sec) mysql>
DROP DATABASE database_name; 删除数据库
3.命令帮助的获取
不管是客户端还是服务端命令都是可以通过关键字“help”来获取帮助信息，当然这得需要MySQL展开其内部的帮助文档才能获取到相应的信息的。可能有的童鞋会问，如果展开它的帮助文档呢？其实在我们安装完MySQL的时候就以及默认展开啦。命令本身不区分字符大小写，但与文件系统相关的部分则根据os的不同，可能区分大小写（比如数据库名称和表名等等。）
mysql> help select
Name: 'SELECT'
Description:
Syntax:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]] SELECT is used to retrieve rows selected from one or more tables, and
can include UNION statements and subqueries. See [HELP UNION], and
http://dev.mysql.com/doc/refman/5.5/en/subqueries.html. The most commonly used clauses of SELECT statements are these: o Each select_expr indicates a column that you want to retrieve. There
must be at least one select_expr. o table_references indicates the table or tables from which to retrieve
rows. Its syntax is described in [HELP JOIN]. o The WHERE clause, if given, indicates the condition or conditions
that rows must satisfy to be selected. where_condition is an
expression that evaluates to true for each row to be selected. The
statement selects all rows if there is no WHERE clause. In the WHERE expression, you can use any of the functions and
operators that MySQL supports, except for aggregate (summary)
functions. See
http://dev.mysql.com/doc/refman/5.5/en/expressions.html, and
http://dev.mysql.com/doc/refman/5.5/en/functions.html. SELECT can also be used to retrieve rows computed without reference to
any table. URL: http://dev.mysql.com/doc/refman/5.5/en/select.html mysql>
mysql> help KEYWORD 获取帮助信息案例展示
4.MySQL的执行模式
a>.交互式执行模式
交互式执行模式，顾名思义，你需要登录数据库界面，然后执行相应的操作。
[root@yinzhengjie ~]# mysql -u root -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
rows in set (0.00 sec) mysql> create database yinzhengjie;
Query OK, row affected (0.01 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yinzhengjie |
+--------------------+
rows in set (0.00 sec) mysql>
MySQL交互式执行模式案例
b>.命令行执行模式
[root@yinzhengjie ~]# mysql -pyinzhengjie -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
[root@yinzhengjie ~]# mysql -pyinzhengjie -e "create database yinzhengjie;"
[root@yinzhengjie ~]# mysql -pyinzhengjie -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| yinzhengjie |
+--------------------+
[root@yinzhengjie ~]#
MySQL令行执行模式案例
c>.批处理执行模式
[root@yinzhengjie ~]# more yinzhengjie.sql
select user();
create database yinzhengjie;
show databases;
[root@yinzhengjie ~]#
[root@yinzhengjie ~]# mysql -uroot -p123 < yinzhengjie.sql
user()
root@localhost
Database
information_schema
mysql
performance_schema
test
yinzhengjie
[root@yinzhengjie ~]#
批处理模式执行命令案例
四.MySQL的数据类型
1.约束（constraint）
约束就是如果你填写了违反了某种法则的数据时（比如人的年龄不能超过200岁等等。）就会拒绝写入，而这种限定就叫做约束。对于关系型数据库来讲，约束通常有以下几种：
a>.主键约束
主键约束就是当你把某个字段或者某些字段合并起来当做主键来用了，这就意味着你往里面填写数据的时候就不能出现重复了。主键还有一个要求，即主键不能为空值（NULL）。对于一张表来讲，主键只能有一个。
b>.外键约束
假如在第一张表中有一个Name字段，而在第二张表中的第一个字段也有Name字段。那么很显然所有填入第一张表中的Name字段的数据都应该是参照了第二张表中的属性（换句话说，第一张表中的Name字段的填入规则必须和第二张表中的Name字段保持一致！）。这种参照（引用）方式叫做参照完整性约束，也叫做外键约束。
c>.惟一键约束
惟一键约束和主键类似，只不过它的主键是可以为空的（NULL），并且对于一张表来讲，主键可以有多个。
d>.检查式约束
无论是主键还是惟一键都没有办法实现把某一个字段的取值限制在合理的范围内。比如，要求用户输入年龄，某个用户手一哆嗦，本来想写40，结果写成了400，那么它真的有400岁吗？很显然并不是。因此就有了检查式约束，即用户自定义有效取值范围的，它通常是一个布尔（bool）表达式，比较能符合条件就允许你往数据库填写数据，如果不能符合条件就禁止填入。
2.键
键就是选取出来某个具有特殊意义的字段。对于关系型数据库（简称RDBMS）来讲，键指的是字段，因为它是用来当做查找标准或者处理标准所使用的。
a>.主键
主键就是能够惟一标识每一个记录的字段或字段的组合。主键是从候选键中挑选出来的一种方案。是需要被真正使用的。
打个比方，要求你在数据库存放中国排名前十的大学从建立到现在的学生信息，我们能用名字来做主键吗？显然是不能的，同理，我们也不能拿年龄当唯一键，更不能拿性别当唯一键啦。甚至学号也可能出现重复的情况（比如清华大学的学生和北大的学生的学号是一样的）。因此，我们可以将学校，姓名，年龄，性别以及学号组合起来当主键使用，这样出现重复的概念就极小啦。因此键未必是一个字段。
b>.候选键
做DBA的童鞋应该知道在数据库的一张表当中，能够唯一标识每一个记录的字段的组合可能不止一个，我们还以上面的例子来说，统计全国排名前十的大学的学员信息，我们可以用学校，姓名，年龄，性别学号来做唯一主键，当然也可以只用学校，姓名和学号来做唯一主键。也就是说选择主键的方式有很多种。因此，所有这些能够用于唯一标识每一个记录的键就叫做候选键。也就是说这些候选键都是可以拿来当主键用的，候选键只是一种概念，并没有被真正使用。
c>.外键
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。
打个比方，假如在第一张表中有一个Name字段，而在第二张表中的第一个字段也有Name字段。这个时候我们就可以说第一张表中的Name字段就是一个外键。也可以说第二张表中的Name字段是第一张表的一个外键。
3.数据类型
数据类型是用来比较方式（排序方式），存储空间（取值范围），能够参与的运算等等，这些功能都是由数据类型来确定的。
a>.字符型
1>.固定长度的字符类型，换句话说就是定义了一个字符长度，如果你存储的字节长度不足时，系统会自动给你用“0”进行占位操作，当字符类型的长度超过我们预定义的长度时，就会报错。用char(#number[最多存储255个字符])关键字来定义。（也就是说，char(10)，就表示你每个字符串存储的长度应该是10，比如你的单个字符串长度是7，那么还有3个空位系统自动会用“0”进行占位。也就没有字符存储的大小是一致的。）
2>.自动变化长度的字符类型，即varchar(#bumber[最多存储65535个字符，即2个字节])。和上面的char(#number)用法相似，只不过唯一不同的是存储的字符的长度是可变化的。也就是说，varchar(10)，就表示你每个字符串的长度应该是10，如果你的单个字符串长度是6，那么它就会以6个长度来存取的你的数据，而不会去可以给你填满其他的占位符，它是以你实际长度为准的。注意，char(#number)和varchar(#num)存储数据的时候都不区分大小写。如果你强烈要求要区分大消息的话也是可以的，和char(#number)类似的一种方法叫做binary(#number)，他们唯一不同就是char(#number)不区分大小写，而binary(#number)是区分大小写的。同理，和varchar(#number)对应的就是varbinary(#number)。
3>.其实char(#number),var(#number),binary(#number),varbinary(#number)它们的存储数据的大小是有上限的。如果你写博客，里面的正文数据长度一定是很大的。这个时候这四种定义类型就不再合适了，这个时候就有了面向对象存储的概念，就是不讲数据放在一个表中了，而是将数据放在一个指定的文件，而在表中存储的只是一个指针。当用户用到该数据时，就会去调用这个指针所对应的文件。而这种存储方式也是区分大小写了，相比之前学习的四种定义类型，test和blob数据类型存储的容量更大，test的类型拥有char(#number),binary(#number)的特点,而blob数据类型拥有varchar(#number),varbinary(#number)。而test和blob又被细分了4类，例如blob分为TinyBlob（最多存储255个字符，即一个字节）,Blob（最多存储64kb，需要减去2个字节的空间）,MediumBlob（最多存储16MB的空间，需要减去3个字节）,LongBlob（最多存储4G的空间，需要减去4个字节）。test分为TinyText(最多存储255个字符，即一个字节与char索引方式是不同的，TinyText不能使用全字段索引而char确实可以的)，Text（最多存储65535个字符，需要减去2个字节），MediumText（最多存储16777215个字符，需要减去3个字节），LongText（最多存储4294967295个字符，表示可以存储4个G，不过一般没人那么干，再问谁会将一个表中的一行字段占用4G的空间呢）等等。
4>.字符型（char、varchar和text）常用的属性修饰符：
A>.NOT NULL（非空约束）
B>.NULL（允许为空）
C>.default ‘string’（默认值，不适用于text类型）
D>.CHARACTER SET 字符集
mysql>show variables like '%char%'; #查看默认的字符集
mysql>show character set; #查看数据库支持的字符集
E>.collation '规则':排序规则
msyql>show collation; #查看数据库支持的排序规则
5>.binary,varbinaray和blob字符常用的属性修饰符
A>.NOT NULL
B>.NULL
C>.default 不适用与blob
b>.数值型
1>.精确数值型
精确数值型有两类，即整形（integer）和十进制（decimal）。在金融领域中，一般都用十进制存储数值型。而integer类型来讲，它有好几种变化形式分别用来表示所能够存储的范围大小的，比如：
A>.只占用一个字节的tinyint（微整型,取值范围是-128~127或者0~255）;
B>.占用两个字节的samllint（小整型，取值范围是-32768~32767或者0~65535）;
C>.占用三个字节的mediuint（中等整型，取值范围-8388608~8388607或者0~16777215）;
D>.占用四个字节的int（整型，取值范围-2147483648~2147483647或者0~4294967295）;
E>.占用八个字节的bingint（大整形，取值范围是-9223372036854775808~9223372036854775807或者0~18446744073709551615）等等。
整形的常用属性修饰符:
A>.AUTO_INCREMENT:自动增长（前提：非空，且唯一，支持索引,非负值[UNSIGNED]，注意：TRUNCATE 用来清空表中数据）
B>.LEST_INSERT_ID()：可以查看上次增长的数值，当插入多行时，只记录第一行
C>.UNSIGNED：无符号
D>.NULL
E>.NOT NULL
F>.DEFAULT
2>.近似数值型
近似数值型又分为单精度浮点型（float）和双精度浮点型（double）。
浮点型常用修饰符：（使用g,f来定义总共有多少数字和小数点后有多少数字）
A>.NULL
B>.NOT NULL
C>.UNSIGNSD
D>.DEFAULT
3>.位
按位（bit）实现数据存储的。这种方式不建议使用。
c>.日期时间型
1>我们在数据库中可能需要存储日期或时间，日期时间型其实本质上被我们存储为数值或字符。
A>.其中我们可以用占用三个字节的date（日期型）来存储时间；
B>.用占用三个字节的time（时间型）来存储时间;
C>.用占用八个字节的datetime（日期时间型）来存储日期和时间;
D>.当然，你也可以存储在四个字节的timestamp（时间戳，存储自1970年1月1号0点0分0秒至你所指定的时间为止所经过的秒数。）;
E>.对了还有一个字节来记录年的year类型。即Year（2）和year（4），前者表示方法是：“00~99”，默认值是：“00”；后者表示方法是：“1901~2155”，默认为“0000”。
2>.日期时间型常用的修饰符
A>.NULL
B>.NOT NULL
C>.DEFAULT
d>.布尔型
MySQL其实没有真正意义上的布尔型，而是使用的tinyint（微整型），而且只显示一位来表示的，要么是0，要么是1.
e>NULL
表示什么也没有存，注意空白字符不等于空哟，数字0也不代表空。
f>.内置类型（事实上ENUM和SET也是属于字符型哟）
1>.MySQL也有两种常见的内置类型，即ENUM（枚举）和SET（集合）。
2>.枚举类型是指将所有的情况的列举出来，我们从中挑选处一种即可。比如今天是星期几，我们通过枚举方法列出所有的可能性，将星期一至星期日的其中情况都枚举出来，这样用户输入的星期八则是不合法的。集合类型是指在一个范围内有很多元素，你可以把这些元素任意拼凑，怎么拼都行，但是每个元素必须是在给定范围内的，不能超出限定范围。就好比你是某学校的大一新生的班主任，现在你要对你的学生进行分组处理。其中每个组的每一个成员必须都是你的班级的学生，而不能讲其他班级的学生拉倒你的班级进行分组。
3>.ENUM和SET的修饰符
A>.NULL
B>.NOT NULL
C>.DEFAULT ‘’
4.表（table）
由行和列组成的二维关系，这也是他被称为关系型数据库类型的原因。
a>.表的创建（CREATE）
用法格式：“create table TabelName(VariableName VariableType,......);”
[root@yinzhengjie ~]# mysql -uroot -pyinzhengjie
ERROR (): Access denied for user 'root'@'localhost' (using password: YES)
[root@yinzhengjie ~]#
[root@yinzhengjie ~]# mysql -uroot -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yinzhengjie |
+--------------------+
rows in set (0.01 sec) mysql> use yinzhengjie
Database changed
mysql> create table Student (Name varchar(),Age tinyint,Gender ENUM('boy','girl')); ----->这里是创建一个表，该表有3个字段，分别是姓名（Name），年龄（Age）和性别（Gender），定义变量名称和Golang很相似。
Query OK, rows affected (0.01 sec) mysql>
创建一个表，格式参考：“ create table TabelName(VariableName VariableType,......);”
[root@yinzhengjie ~]# mysql -uroot -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yinzhengjie |
+--------------------+
rows in set (0.00 sec) mysql> use yinzhengjie
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_yinzhengjie |
+-----------------------+
| Student |
+-----------------------+
row in set (0.00 sec) mysql> desc Student;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| Name | varchar() | YES | | NULL | |
| Age | tinyint() | YES | | NULL | |
| Gender | enum('boy','girl') | YES | | NULL | |
+--------+--------------------+------+-----+---------+-------+
rows in set (0.00 sec) mysql>
查看表结构 ，格式参考：“desc TableName”
[root@yinzhengjie ~]# mysql -uroot -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yinzhengjie |
+--------------------+
rows in set (0.00 sec) mysql> use yinzhengjie
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_yinzhengjie |
+-----------------------+
| Student |
+-----------------------+
row in set (0.00 sec) mysql> drop table Student;
Query OK, rows affected (0.00 sec) mysql> show tables;
Empty set (0.00 sec) mysql>
删除一个表，格式参考：“drop table TableName”
注意，字段或字段类型还可以有修饰符 >.NOT NULL #表示插入数据时不允许为空
>.NULL #表示插入的数据可以为空
>.DEDAULT #字符要加引号，数字不能加引号
>.UNSIGNED #无符号(只能用在整型上)
>.AUTO_INCREMENT #自动增长类型的字段必须为主键或唯一键，即使你清空了一个表格的数据，它默认情况下是不会重置的哟。
>.PRIMARY KEY #定义主键
>.UNIQUE KEY #唯一键
[root@yinzhengjie ~]# mysql -uroot -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5.-log MySQL Community Server (GPL) Copyright (c) , , 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> use yinzhengjie
Database changed
mysql> create table Student (StuID int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name varchar() NOT NULL,Age tinyint UNSIGNED NOT NULL,Gender ENUM('boy','girl') NOT NULL DEFAULT 'boy'); ----->和之前创建的表名称一样，只不过在每个变量类型后面加了修饰符，来限定变量是否可用为空，并且新添加了一个INT类型的StuID字段，并且让该INT类型自动增长。
Query OK, rows affected (0.01 sec) mysql>
mysql> desc Student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| StuID | int() unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar() | NO | | NULL | |
| Age | tinyint() unsigned | NO | | NULL | |
| Gender | enum('boy','girl') | NO | | boy | |
+--------+---------------------+------+-----+---------+----------------+
rows in set (0.00 sec) mysql>
定义有修饰符的表案例
b>.表的插入（INSERT）
用法格式：“INSERT INTO TableName(VariableName1,VariableName2,...) VALUE|VALUES (VariableValue1,VariableValue2.....);”
mysql> desc Student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| StuID | int() unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar() | NO | | NULL | |
| Age | tinyint() unsigned | NO | | NULL | |
| Gender | enum('boy','girl') | NO | | boy | |
+--------+---------------------+------+-----+---------+----------------+
rows in set (0.00 sec) mysql> insert into Student(Name,Age,Gender) values ( "yinzihengjie",,'boy');
Query OK, row affected (0.01 sec)
mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
+-------+--------------+-----+--------+
row in set (0.00 sec) mysql>
只插入一条数据案例展示
mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
+-------+--------------+-----+--------+
row in set (0.00 sec) mysql> insert into Student(Name,Age,Gender) values ( "linus",,'boy'),("dengziqi",,'girl');
Query OK, rows affected (0.02 sec)
Records: Duplicates: Warnings: mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | linus | | boy |
| | dengziqi | | girl |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql>
同时插入（批量插入）多条数据用法展示
mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | linus | | boy |
| | dengziqi | | girl |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql> insert into Student(Name,Age) values ( "jay Chou",),("joker Xue",),("zhangweijian",);
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | linus | | boy |
| | dengziqi | | girl |
| | jay Chou | | boy |
| | joker Xue | | boy |
| | zhangweijian | | boy |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql>
如果批量插入的某个字段是都是一致的情况可以省去，点我看用法展示
c>.表的查询
用法格式：“select VariableName1,VariableName2 from TableName where ConditionalExpression;” 当然查询是可以用，删除，修改都是可以用like，rlike以及组合条件进行过滤，从而得到我们想要的字段。 like通配符：
%：任意长度的任意字符
_:匹配任意单个字符
rlink
正则表达式 组合条件
and
or
not
mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | linus | | boy |
| | dengziqi | | girl |
| | jay Chou | | boy |
| | joker Xue | | boy |
| | zhangweijian | | boy |
+-------+--------------+-----+--------+
rows in set (0.00 sec)
mysql> select Name,Age,Gender from Student where Name = 'yinzihengjie';
+--------------+-----+--------+
| Name | Age | Gender |
+--------------+-----+--------+
| yinzihengjie | | boy |
+--------------+-----+--------+
row in set (0.00 sec) mysql>
select中的where用法展示
mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | linus | | boy |
| | dengziqi | | girl |
| | jay Chou | | boy |
| | joker Xue | | boy |
| | zhangweijian | | boy |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql> select Name,Age,Gender from Student where Gender = 'boy' and Age > ;
+--------------+-----+--------+
| Name | Age | Gender |
+--------------+-----+--------+
| linus | | boy |
| jay Chou | | boy |
| zhangweijian | | boy |
+--------------+-----+--------+
rows in set (0.00 sec) mysql>
where的组合用法展示
Mysql服务器的工作特性的定义是通过服务器变量实现的，而MySQL服务器运行中的状态是通过状态变量输出的。
mysql> show create table Student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Student | CREATE TABLE `Student` (
`StuID` int() unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar() NOT NULL,
`Age` tinyint() unsigned NOT NULL,
`Gender` enum('boy','girl') NOT NULL DEFAULT 'boy',
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
row in set (0.00 sec) mysql>
查看类命令-查询创建表时使用的命令
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
rows in set (0.00 sec) mysql>
查看类命令-查询数据库支持的存储类型
mysql> show table status\G
*************************** . row ***************************
Name: Student
Engine: InnoDB
Version:
Row_format: Compact
Rows:
Avg_row_length:
Data_length:
Max_data_length:
Index_length:
Data_free:
Auto_increment:
Create_time: -- ::
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
row in set (0.00 sec) mysql>
查看类命令-查询当前数据库的存储类型
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | |
| dec8 | DEC West European | dec8_swedish_ci | |
| cp850 | DOS West European | cp850_general_ci | |
| hp8 | HP West European | hp8_english_ci | |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | |
| latin1 | cp1252 West European | latin1_swedish_ci | |
| latin2 | ISO - Central European | latin2_general_ci | |
| swe7 | 7bit Swedish | swe7_swedish_ci | |
| ascii | US ASCII | ascii_general_ci | |
| ujis | EUC-JP Japanese | ujis_japanese_ci | |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | |
| hebrew | ISO - Hebrew | hebrew_general_ci | |
| tis620 | TIS620 Thai | tis620_thai_ci | |
| euckr | EUC-KR Korean | euckr_korean_ci | |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | |
| greek | ISO - Greek | greek_general_ci | |
| cp1250 | Windows Central European | cp1250_general_ci | |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | |
| latin5 | ISO - Turkish | latin5_turkish_ci | |
| armscii8 | ARMSCII- Armenian | armscii8_general_ci | |
| utf8 | UTF- Unicode | utf8_general_ci | |
| ucs2 | UCS- Unicode | ucs2_general_ci | |
| cp866 | DOS Russian | cp866_general_ci | |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | |
| macce | Mac Central European | macce_general_ci | |
| macroman | Mac West European | macroman_general_ci | |
| cp852 | DOS Central European | cp852_general_ci | |
| latin7 | ISO - Baltic | latin7_general_ci | |
| utf8mb4 | UTF- Unicode | utf8mb4_general_ci | |
| cp1251 | Windows Cyrillic | cp1251_general_ci | |
| utf16 | UTF- Unicode | utf16_general_ci | |
| cp1256 | Windows Arabic | cp1256_general_ci | |
| cp1257 | Windows Baltic | cp1257_general_ci | |
| utf32 | UTF- Unicode | utf32_general_ci | |
| binary | Binary pseudo charset | binary | |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | |
+----------+-----------------------------+---------------------+--------+
rows in set (0.03 sec) mysql>
查看类命令-查询字符集（字符集是通过二进制转换为汉字的结果）
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | | Yes | Yes | |
| big5_bin | big5 | | | Yes | |
| dec8_swedish_ci | dec8 | | Yes | Yes | |
| dec8_bin | dec8 | | | Yes | |
| cp850_general_ci | cp850 | | Yes | Yes | |
| cp850_bin | cp850 | | | Yes | |
| hp8_english_ci | hp8 | | Yes | Yes | |
| hp8_bin | hp8 | | | Yes | |
| koi8r_general_ci | koi8r | | Yes | Yes | |
| koi8r_bin | koi8r | | | Yes | |
| latin1_german1_ci | latin1 | | | Yes | |
| latin1_swedish_ci | latin1 | | Yes | Yes | |
| latin1_danish_ci | latin1 | | | Yes | |
| latin1_german2_ci | latin1 | | | Yes | |
| latin1_bin | latin1 | | | Yes | |
| latin1_general_ci | latin1 | | | Yes | |
| latin1_general_cs | latin1 | | | Yes | |
| latin1_spanish_ci | latin1 | | | Yes | |
| latin2_czech_cs | latin2 | | | Yes | |
| latin2_general_ci | latin2 | | Yes | Yes | |
| latin2_hungarian_ci | latin2 | | | Yes | |
| latin2_croatian_ci | latin2 | | | Yes | |
| latin2_bin | latin2 | | | Yes | |
| swe7_swedish_ci | swe7 | | Yes | Yes | |
| swe7_bin | swe7 | | | Yes | |
| ascii_general_ci | ascii | | Yes | Yes | |
| ascii_bin | ascii | | | Yes | |
| ujis_japanese_ci | ujis | | Yes | Yes | |
| ujis_bin | ujis | | | Yes | |
| sjis_japanese_ci | sjis | | Yes | Yes | |
| sjis_bin | sjis | | | Yes | |
| hebrew_general_ci | hebrew | | Yes | Yes | |
| hebrew_bin | hebrew | | | Yes | |
| tis620_thai_ci | tis620 | | Yes | Yes | |
| tis620_bin | tis620 | | | Yes | |
| euckr_korean_ci | euckr | | Yes | Yes | |
| euckr_bin | euckr | | | Yes | |
| koi8u_general_ci | koi8u | | Yes | Yes | |
| koi8u_bin | koi8u | | | Yes | |
| gb2312_chinese_ci | gb2312 | | Yes | Yes | |
| gb2312_bin | gb2312 | | | Yes | |
| greek_general_ci | greek | | Yes | Yes | |
| greek_bin | greek | | | Yes | |
| cp1250_general_ci | cp1250 | | Yes | Yes | |
| cp1250_czech_cs | cp1250 | | | Yes | |
| cp1250_croatian_ci | cp1250 | | | Yes | |
| cp1250_bin | cp1250 | | | Yes | |
| cp1250_polish_ci | cp1250 | | | Yes | |
| gbk_chinese_ci | gbk | | Yes | Yes | |
| gbk_bin | gbk | | | Yes | |
| latin5_turkish_ci | latin5 | | Yes | Yes | |
| latin5_bin | latin5 | | | Yes | |
| armscii8_general_ci | armscii8 | | Yes | Yes | |
| armscii8_bin | armscii8 | | | Yes | |
| utf8_general_ci | utf8 | | Yes | Yes | |
| utf8_bin | utf8 | | | Yes | |
| utf8_unicode_ci | utf8 | | | Yes | |
| utf8_icelandic_ci | utf8 | | | Yes | |
| utf8_latvian_ci | utf8 | | | Yes | |
| utf8_romanian_ci | utf8 | | | Yes | |
| utf8_slovenian_ci | utf8 | | | Yes | |
| utf8_polish_ci | utf8 | | | Yes | |
| utf8_estonian_ci | utf8 | | | Yes | |
| utf8_spanish_ci | utf8 | | | Yes | |
| utf8_swedish_ci | utf8 | | | Yes | |
| utf8_turkish_ci | utf8 | | | Yes | |
| utf8_czech_ci | utf8 | | | Yes | |
| utf8_danish_ci | utf8 | | | Yes | |
| utf8_lithuanian_ci | utf8 | | | Yes | |
| utf8_slovak_ci | utf8 | | | Yes | |
| utf8_spanish2_ci | utf8 | | | Yes | |
| utf8_roman_ci | utf8 | | | Yes | |
| utf8_persian_ci | utf8 | | | Yes | |
| utf8_esperanto_ci | utf8 | | | Yes | |
| utf8_hungarian_ci | utf8 | | | Yes | |
| utf8_sinhala_ci | utf8 | | | Yes | |
| utf8_general_mysql500_ci | utf8 | | | Yes | |
| ucs2_general_ci | ucs2 | | Yes | Yes | |
| ucs2_bin | ucs2 | | | Yes | |
| ucs2_unicode_ci | ucs2 | | | Yes | |
| ucs2_icelandic_ci | ucs2 | | | Yes | |
| ucs2_latvian_ci | ucs2 | | | Yes | |
| ucs2_romanian_ci | ucs2 | | | Yes | |
| ucs2_slovenian_ci | ucs2 | | | Yes | |
| ucs2_polish_ci | ucs2 | | | Yes | |
| ucs2_estonian_ci | ucs2 | | | Yes | |
| ucs2_spanish_ci | ucs2 | | | Yes | |
| ucs2_swedish_ci | ucs2 | | | Yes | |
| ucs2_turkish_ci | ucs2 | | | Yes | |
| ucs2_czech_ci | ucs2 | | | Yes | |
| ucs2_danish_ci | ucs2 | | | Yes | |
| ucs2_lithuanian_ci | ucs2 | | | Yes | |
| ucs2_slovak_ci | ucs2 | | | Yes | |
| ucs2_spanish2_ci | ucs2 | | | Yes | |
| ucs2_roman_ci | ucs2 | | | Yes | |
| ucs2_persian_ci | ucs2 | | | Yes | |
| ucs2_esperanto_ci | ucs2 | | | Yes | |
| ucs2_hungarian_ci | ucs2 | | | Yes | |
| ucs2_sinhala_ci | ucs2 | | | Yes | |
| ucs2_general_mysql500_ci | ucs2 | | | Yes | |
| cp866_general_ci | cp866 | | Yes | Yes | |
| cp866_bin | cp866 | | | Yes | |
| keybcs2_general_ci | keybcs2 | | Yes | Yes | |
| keybcs2_bin | keybcs2 | | | Yes | |
| macce_general_ci | macce | | Yes | Yes | |
| macce_bin | macce | | | Yes | |
| macroman_general_ci | macroman | | Yes | Yes | |
| macroman_bin | macroman | | | Yes | |
| cp852_general_ci | cp852 | | Yes | Yes | |
| cp852_bin | cp852 | | | Yes | |
| latin7_estonian_cs | latin7 | | | Yes | |
| latin7_general_ci | latin7 | | Yes | Yes | |
| latin7_general_cs | latin7 | | | Yes | |
| latin7_bin | latin7 | | | Yes | |
| utf8mb4_general_ci | utf8mb4 | | Yes | Yes | |
| utf8mb4_bin | utf8mb4 | | | Yes | |
| utf8mb4_unicode_ci | utf8mb4 | | | Yes | |
| utf8mb4_icelandic_ci | utf8mb4 | | | Yes | |
| utf8mb4_latvian_ci | utf8mb4 | | | Yes | |
| utf8mb4_romanian_ci | utf8mb4 | | | Yes | |
| utf8mb4_slovenian_ci | utf8mb4 | | | Yes | |
| utf8mb4_polish_ci | utf8mb4 | | | Yes | |
| utf8mb4_estonian_ci | utf8mb4 | | | Yes | |
| utf8mb4_spanish_ci | utf8mb4 | | | Yes | |
| utf8mb4_swedish_ci | utf8mb4 | | | Yes | |
| utf8mb4_turkish_ci | utf8mb4 | | | Yes | |
| utf8mb4_czech_ci | utf8mb4 | | | Yes | |
| utf8mb4_danish_ci | utf8mb4 | | | Yes | |
| utf8mb4_lithuanian_ci | utf8mb4 | | | Yes | |
| utf8mb4_slovak_ci | utf8mb4 | | | Yes | |
| utf8mb4_spanish2_ci | utf8mb4 | | | Yes | |
| utf8mb4_roman_ci | utf8mb4 | | | Yes | |
| utf8mb4_persian_ci | utf8mb4 | | | Yes | |
| utf8mb4_esperanto_ci | utf8mb4 | | | Yes | |
| utf8mb4_hungarian_ci | utf8mb4 | | | Yes | |
| utf8mb4_sinhala_ci | utf8mb4 | | | Yes | |
| cp1251_bulgarian_ci | cp1251 | | | Yes | |
| cp1251_ukrainian_ci | cp1251 | | | Yes | |
| cp1251_bin | cp1251 | | | Yes | |
| cp1251_general_ci | cp1251 | | Yes | Yes | |
| cp1251_general_cs | cp1251 | | | Yes | |
| utf16_general_ci | utf16 | | Yes | Yes | |
| utf16_bin | utf16 | | | Yes | |
| utf16_unicode_ci | utf16 | | | Yes | |
| utf16_icelandic_ci | utf16 | | | Yes | |
| utf16_latvian_ci | utf16 | | | Yes | |
| utf16_romanian_ci | utf16 | | | Yes | |
| utf16_slovenian_ci | utf16 | | | Yes | |
| utf16_polish_ci | utf16 | | | Yes | |
| utf16_estonian_ci | utf16 | | | Yes | |
| utf16_spanish_ci | utf16 | | | Yes | |
| utf16_swedish_ci | utf16 | | | Yes | |
| utf16_turkish_ci | utf16 | | | Yes | |
| utf16_czech_ci | utf16 | | | Yes | |
| utf16_danish_ci | utf16 | | | Yes | |
| utf16_lithuanian_ci | utf16 | | | Yes | |
| utf16_slovak_ci | utf16 | | | Yes | |
| utf16_spanish2_ci | utf16 | | | Yes | |
| utf16_roman_ci | utf16 | | | Yes | |
| utf16_persian_ci | utf16 | | | Yes | |
| utf16_esperanto_ci | utf16 | | | Yes | |
| utf16_hungarian_ci | utf16 | | | Yes | |
| utf16_sinhala_ci | utf16 | | | Yes | |
| cp1256_general_ci | cp1256 | | Yes | Yes | |
| cp1256_bin | cp1256 | | | Yes | |
| cp1257_lithuanian_ci | cp1257 | | | Yes | |
| cp1257_bin | cp1257 | | | Yes | |
| cp1257_general_ci | cp1257 | | Yes | Yes | |
| utf32_general_ci | utf32 | | Yes | Yes | |
| utf32_bin | utf32 | | | Yes | |
| utf32_unicode_ci | utf32 | | | Yes | |
| utf32_icelandic_ci | utf32 | | | Yes | |
| utf32_latvian_ci | utf32 | | | Yes | |
| utf32_romanian_ci | utf32 | | | Yes | |
| utf32_slovenian_ci | utf32 | | | Yes | |
| utf32_polish_ci | utf32 | | | Yes | |
| utf32_estonian_ci | utf32 | | | Yes | |
| utf32_spanish_ci | utf32 | | | Yes | |
| utf32_swedish_ci | utf32 | | | Yes | |
| utf32_turkish_ci | utf32 | | | Yes | |
| utf32_czech_ci | utf32 | | | Yes | |
| utf32_danish_ci | utf32 | | | Yes | |
| utf32_lithuanian_ci | utf32 | | | Yes | |
| utf32_slovak_ci | utf32 | | | Yes | |
| utf32_spanish2_ci | utf32 | | | Yes | |
| utf32_roman_ci | utf32 | | | Yes | |
| utf32_persian_ci | utf32 | | | Yes | |
| utf32_esperanto_ci | utf32 | | | Yes | |
| utf32_hungarian_ci | utf32 | | | Yes | |
| utf32_sinhala_ci | utf32 | | | Yes | |
| binary | binary | | Yes | Yes | |
| geostd8_general_ci | geostd8 | | Yes | Yes | |
| geostd8_bin | geostd8 | | | Yes | |
| cp932_japanese_ci | cp932 | | Yes | Yes | |
| cp932_bin | cp932 | | | Yes | |
| eucjpms_japanese_ci | eucjpms | | Yes | Yes | |
| eucjpms_bin | eucjpms | | | Yes | |
+--------------------------+----------+-----+---------+----------+---------+
rows in set (0.00 sec) mysql>
查看类命令-查询字符集的排序方式
mysql> show variables like 'data%';
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| datadir | /data/mydata/ |
+---------------+---------------+
row in set (0.00 sec) mysql>
查看类命令-查看服务器变量,可以通过like命令进行过滤。
mysql> show global variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.5.-log |
+---------------+------------+
row in set (0.00 sec) mysql>
查看类命令-查看全局变量
mysql> show session variables like 'tmp_table_size';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | |
+----------------+----------+
row in set (0.00 sec) mysql>
查看类命令-查看会话的变量
mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | |
+---------------+-------+
row in set (0.00 sec) mysql>
查看类命令-服务器运行状态
d>.删除数据（delete）
用法格式：“ delete from TableName where ConditionalExpression;”
mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | linus | | boy |
| | dengziqi | | girl |
| | jay Chou | | boy |
| | joker Xue | | boy |
| | zhangweijian | | boy |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql> delete from Student where name like 'z%';
Query OK, row affected (0.00 sec) mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | linus | | boy |
| | dengziqi | | girl |
| | jay Chou | | boy |
| | joker Xue | | boy |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql>
模糊匹配（like）用法展示
mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | linus | | boy |
| | dengziqi | | girl |
| | jay Chou | | boy |
| | joker Xue | | boy |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql> delete from Student where Name rlike '^l.*';
Query OK, row affected (0.00 sec) mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | dengziqi | | girl |
| | jay Chou | | boy |
| | joker Xue | | boy |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql>
模糊匹配（rlike）用法展示
e>.更新数据（update）
用法格式：“update TableName set VariableType = VariableValue where ConditionalExpression;”
mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | dengziqi | | girl |
| | jay Chou | | boy |
| | joker Xue | | boy |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql> update Student set Age= where Name = 'yinzihengjie';
Query OK, row affected (0.04 sec)
Rows matched: Changed: Warnings: mysql> select * from Student;
+-------+--------------+-----+--------+
| StuID | Name | Age | Gender |
+-------+--------------+-----+--------+
| | yinzihengjie | | boy |
| | dengziqi | | girl |
| | jay Chou | | boy |
| | joker Xue | | boy |
+-------+--------------+-----+--------+
rows in set (0.00 sec) mysql>
update用法展示
五.MySQL内置的SQL模型（SQL_MODE）
1.MySQL内置的SQL模型常用的模式
MySQL有内置的SQL模型SQL_MODE：用来定义字符超出的操作和模仿别的数据库的类型，通过修改全局变量。 常用的模式有：
A>.TRADITIONAL 使用传统模式
B>.STRICT_TRANS_TABLES 仅对支持事务的表的严格模式
C>.STRICT_ALL_TABLES 对所有表使用严格模式
2.设定服务器变量的值：通常仅用于支持动态的变量
A>.支持修改的服务器变量
动态变量：可以在MySQL运行时修改
静态变量：与配置文件中修改其值，并重启后方能生效
B>.服务器变量从其生效范围来讲，分两类
全局变量：服务器级别，修改之后仅对新建立的会话生效
回话变量：会话级别，仅对当前会话有效
会话建立时，从全局继承各变量
C>.查看服务器变量
mysql> show [{global|session}] variables [like ''];
mysql> select @@{globa|session}.variable_name
mysql> mysql> select * from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME='SOME_VARIABLE_NAME'; 查看全局变量
mysql> mysql> select * from information_schema.SESSION_VARIABLES where VARIABLE_NAME='SOME_VARIABLE_NAME'; 查看会话变量
D>.修改变量
前提：默认仅管理员有权限修改全局变量
mysql> set {global|session} variable_name='value';
注意：无论是全局还是会话级别的动态变量修改，在重启mysql后都会失效，想永久有效，可以定义在配置文件中的响应段中[mysqld]