MySQL的基本使用相关知识

登录到MySQL

mysql -h hostname -P hostport -u username -p

• -h 指定希望连接的主机，可以用来连接远程主机上的MySQL，如果忽略默认登录本机数据库

• -P 指定所连接主机开放的MySQL端口，如果忽略此项默认使用3306端口登录

• -u 指定连接数据库时使用的用户名称，如果忽略此项默认使用你的本机用户名

• -p 告诉服务器会使用密码来连接数据库，如果忽略此项使用无密码登录

使用密码登入时，会出现以下响应

Enter password:

输入密码后成功登录会得到以下响应

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.12 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

创建数据库

每个程序都需要数据库，可以在MySQL命令提示符下输入：

mysql> create database dbname;

创建成功后，可以得到以下响应:

Query OK, 1 row affected (0.00 sec)

设置用户和权限

一个MySQL可能有许多用户，而且不同的用户有不同的权限。

最少权限原则：一个用户（或者一个进程）应该拥有能够执行分配给他的任务的最低级别的权限

GRANT和REVOKE分别用来授予和取消MySQL用户的权限

权限分为四个级别：

• 全局

• 数据库

• 表

• 列

权限的类型和级别

MySQL权限有3个基本类型：

• 适用于一般用户的权限

• 适用于赋予管理员的权限

• 几个特定的权限

用户的权限

从系统的安全性方面考虑，适于常规用户的权限大多数是相对无害的。

权限应用于描述SELECT表，列允许用户从表中选择行INSERT表，列允许用户在表中插入行DELETE表，列允许用户修改现存表中的行INDEX表允许用户删除现存表的行ALTER表允许用户修改现存表的结构CREATE数据库，表允许用户创建新的数据库或表DROP数据库，表允许用户删除数据库或表

管理员权限
这些权限都可以授予非管理员用户，但是必须非常小心

FILE权限有些不同，对普通用户非常有用，可以将数据从文件载入数据库，否则必须把数据输入数据库，非常浪费时间。
但是必须注意，可能会载入其他用户数据库和潜在的密码文件，授予权限时必须非常小心，或者管理员载入数据

权限应用于CREATE TEMPORARY TABLES允许管理员创建临时表FILE允许数据从文件读入表，或从表读入文件LOCK TABLES允许使用LOCK TABLES语句PROCESS允许管理员查看属于所有用户的服务器进程REPLICATION CLIENT允许在复制主机和从机上使用SHOW STATUSREPLICATION SLAVE允许复制从服务器连接到主服务器SHOW DATABASES允许使用SHOW DATABASES查看所有数据库列表SHUTDOWN允许管理员关闭MySQL服务器SUPER允许管理员关闭属于任何用户的线程

特别的权限

权限应用于ALL授予用户和管理员表的所有权限USAGE不授予权限。这将创建一个用户并允许他登录，但是不允许进行任何操作，通常在以后授予更多的权限

GRANT命令

GRANT命令用来创建用户并赋予权限，常见形式是

GRANT privileges [columns]ON itemTO username [IDENTIFIED BY 'password'][REQUIRE ssl_options][WITH [GRANT OPTION | limit_options]]

• 占位符columns是可选的，可以用于对每个列指定权限，也可以使用单列的名称或者逗号分开的一组列的名称

• 占位符item是新权限所应用于的数据库或表。如果没有使用在任何特定数据库，可以使用*赋予全局权限。
  更常见的是，以dbname.*的形式指定数据库中所有表，以dbname.tablename的形式指定单个表，或者

通过指定tablename来指定特定的列。这些分别表示其他3个可以利用的权限：数据库、表、列。
如果在输入命令的时候正在使用一个数据库，tablename本身被解释为当前数据库的一个表

• username应该是用户登录数据库的用户名。用户名可以包含一个主机名，可以用来区分如fred@localhost、[e-mail]。这样可以区分来自不同域的相同用户名的用户。

• password应该是用户登录数据库的密码

• REQUIRE子句允许指定用户是否必须通过加密套接字连接，或者指定其他SSL选项。更多的参考MySQL手册

• WITH GRANT OPTION选项，如果指定，表示允许指定的用户向别人授予自己拥有的权限。

还可以指定如下所示的WITH选项:

• MAX_QUERIES_PER_HOUR n: 用户每小时执行的查询数量上限

• MAX_UPDATES_PER_HOUR n: 用户每小时执行的更新数量上限

• MAX_CONNECTIONS_PER_HOUR n: 用户每小时连接的数量

REVOKE命令

与GRANT相反的命令是REVOKE，用来从一个用户收回权限，语法与GRANT类似

REVOKE privileges [(columns)]ON itemFROM username

如果已经给出WITH GRANT OPTION子句，可以用下面方式撤销（以及其他所有权限）

REVOKE ALL PRIVILEGES, GRANTFROM username

使用GRANT和REVOKE的例子

1. 管理员的创建和撤销
   创建一个管理员，可以输入如下所示命令：

命令授予了一个用户名zhao，密码为secret的用户使用所有数据库的所有权限，并允许他向其他人授予这些权限

grant allon *to zhao identified by 'secret'with grant option;

如果不希望用户在系统中存在，可以按照以下方式撤销：

revoke all privileges, grantfrom zhao

1. 创建基本用户和授予权限
   创建一个没有任何权限的普通用户zhao：

grant USAGEon test.*to zhao identified by 'secret';

在跟用户zhao沟通之后，授予一些适当的权限:

grant select, insert, update, delete, index, alter, create, dropon test.*from zhao;

如果我们认为zhao的权限过高，可以按照下面方式减少一些权限

revoke alter, create, dropon test.*from zhao;

后来，当这个用户不需要使用数据库时，按照下面方式撤销所有权限

revoke allon test.*from zhao;

创建用户

在上面权限的介绍中插入一些关于创建用户时的权限基本知识，也了解一个数据库用户是如何创建的。
在许多web应用中，用户只需要SELECT,INSERT,DELETE,UPDATE的权限。因此可以按照如下方式设定权限：

grant select, insert, delete, updateon test.*to lily indetified by 'secret';

使用正确的数据库

如果开始使用MySQL，登录之后，要做的第一件事就是指定要使用的数据库。可以输入以下命令完成：

use dbname

也可以在登录时候指定数据库而避免选择数据库,如下所示

mysql -D dbname -h hostname -u username -p

例子：
我们使用一个名为books的数据库：
use books;
输入命令后，MySQL应该给出如下所示的响应:
Database changed
如果开始工作前没有选择数据库，MySQL会给出如下所示错误信息：
ERROR 1046 (3D000): No database selected

创建数据库表

创建数据库之后，就是创建实际的表。创建表的命令是CREATE TABLE,常见形式如下：
create table tablename(columns)

假如我们要创建一个订单的表单，orders(customerid, name, address, amount, date)
创建orders表，数据类型后面再详细说明，这里的比较简单：

create table orders(    customerid int unsigned not null auto_increment primary key,    name char(50) not null,    address char(100) not null,    amount float(6, 2),    date date not null);

理解关键字的意思

not null的意思是表中所有行此属性必须有一个值。如果没有指定，该列可以是（NULL）

auto_increment 是一个特殊的MySQL特性，可以在整数列中使用。意思是再插入行的时候，如果该字段为空，
那么MySQL自动产生一个唯一标示符。该值比本列中现存的最大值更大。在每个列中只能有这样一个值。
指定auto_increment的列必须是索引列

primary key,这个只用于单列主键。如果需要多列合并为主键需要使用primary key(prop1, prop2)

整数类型后面unsigned意思是这一列只能是0或者一个正数

理解列的类型

创建一个表的时候，需要确定列的数据类型。根据orders表分析一下。

• customerid：主键，数据类型是一个正数，并且是无符号的，自动增长

• name：char类型，并且分配50个字符的空间，也可以使用varchar类型，虽然varchar可以根据需要分配空间，但是char数据速度更快

• amount：float类型，指定了显示宽度和小数点后位数

• date：date类型，日期

更多的的类型可以查看MySQL手册

用SHOW和DESCRIBE来查看数据库

登录到MySQL并且使用数据库之后，输入show tables;可以查看所有表。
MySQL将显示数据库中所有的表单：

+-----------------+| Tables_in_blog  |+-----------------+| migrations      || password_resets || posts           || users           |+-----------------+

也可以使用show databases;来查看数据库列表:

+--------------------+| Database           |+--------------------+| information_schema || blog               || books              || dbname             || homestead          || mysql              || performance_schema || sys                || test               |+--------------------+

如果没有show databases权限，将只能看到权限范围内的数据库

要查看某个表的详细信息，可以使用describe命令：describe table_name;或者desc table_name

MySQL将显示你创建表时提供的信息,提供一个例子，我的博客数据库posts表单的设计：

+--------------+------------------+------+-----+---------+----------------+| Field        | Type             | Null | Key | Default | Extra          |+--------------+------------------+------+-----+---------+----------------+| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment || slug         | varchar(255)     | NO   |     | NULL    |                || title        | varchar(255)     | NO   |     | NULL    |                || content      | text             | NO   |     | NULL    |                || published_at | timestamp        | NO   |     | NULL    |                || created_at   | timestamp        | YES  |     | NULL    |                || updated_at   | timestamp        | YES  |     | NULL    |                |+--------------+------------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

创建索引

设计主键时将在这些列上面创建索引。

MySQL新用户可能面临一个常见的问题，数据库性能低下。但这个问题经常在数据库上没有创建任何索引的情况下发生。

要开始创建索引，可以使用自动创建的索引。如果发现需要对一个不是主键的列运行许多查询，可以在该列上添加索引来改善性能

创建索引的语法是create index:

create [UNIQUE | FULLTEXT] INDEX index_nameON table_name (index_column_name [(length) [ASC | DESC], ...])

FULLTEXT索引用来索引文本字段，length字段允许指定该字段的前length个字符被索引，ASC｜DESC可以指定索引的排序为升序或降序

理解MySQL的标示符

在MySQL中有5种类型的标识符: Database,Table,Column,index和Alias

类型最大长度是否区分大小写允许的字符Database64与操作系统相同操作系统目录名允许出现的字符，不包含 "","/","."Table64与操作系统相同操作系统目录名允许出现的字符，不包含 "/","."Column64否任何字符index64否任何字符Alias255否任何字符

选择列数据类型

MySQL中3种基本的列数据类型：数字、日期和时间、字符串。

数字类型

数字类型分为：整型和浮点型
对于浮点型，可以指定小数点后数字的位数。可以指定为最大值30或者最大显示长度－2
对于整型，可以指定为无符号型
对于所有数字类型，可以指定zerofill属性。当显示zerofill字段中的值时，空余部分用前导0来补充。如果将一个字段指定为zerofill，将自动成为unsigned数据类型

整数数据类型

类型取值范围存储空间（单位：字节）描述tinyint-127～128或0～2551非常小的整数bit-127～128或0～2551tinyint同义词Bool-127～128或0～2551tinyint同义词smallint－32768～32767或0～655352小型整数mediumint-8388608～8388607或0～167772153中型整数int(Integer)-2^31~2^32-1或0～2^32-14一般整数bigint-2^63~2^63-1或0~2^64-18大型整数

浮点数据类型

类型取值范围存储空间（单位：字节）描述float(精度)取决于精度可变可用于指定单精度和双精度浮点数float[(M, D)](-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)4单精度浮点数，相当于float(4),指定宽度和小数double[(M, D)](1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)8双精度浮点数，相当于float(8),指定宽度和小数double同上8double[(M, D)] 同义precision[(M, D)]同上8double[(M, D)] 同义real[(M, D)]同上8double[(M, D)] 同义decimal[(M[, D])]可变M+2浮点数，以char存储，范围取决于宽度Mnumeric[(M, D)]同上M+2decimal的同义词dec[(M, D)]同上M+2decimal的同义词fixed[(M, D)]同上M+2decimal的同义词

日期和时间类型

MySQL支持多种日期和时间类型，使用这些类型，可以以字符串或数字格式输入数据。如果不手动设置，特定行的timestamp列将被设置为最近修改改行的日期和时间

日期和时间数据类型

类型取值范围描述date1000-01-01~9999-12-31一个日期，以yyyy-mm-dd格式time-838:59:59~838:59:59一个时间，以hh:mm:ss形式显示datetime1000-01-01 00:00:00~9999-12-31 23:59:59日期和时间，以yyyy-mm-dd hh:mm:ss格式显示timestamp[(M)]1970-01-01 00:00:00～2037年某个时间（取决于UNIX限制）时间标签，在处理报告中有意义。显示格式取决于M的值year70-69（1970～2069） ／ 1901～2155年份。可以指定2位数字或4位数字的格式

timestamp显示类型

指定类型显示指定类型显示timestampYYYYMMDDHHMMSStimestamp(8)YYYYMMDDtimestamp(14)YYYYMMDDHHMMSStimestamp(6)YYMMDDtimestamp(12)YYMMDDHHMMSStimestamp(4)YYMMtimestamp(10)YYMMDDHHMMtimestamp(2)YY

字符串类型

字符串类型为3类：普通字符串，text和blob类型，特殊类型

普通字符串，即小段文本，包括char类型和varchar类型。可以指定每种类型宽度。无论数据大小是多少，char类型的列都会用空格填补空白，
但是varchar列宽随数据大小变化。MySQL获取char和varchar数据的时候，将会过滤多余的空格。这两种类型都有速度与存储空间的问题。

text和blob类型。这些类型大小可变，他们分别适用于长文本或二进制数据。blob全称为大二进制对象，支持任何数据，例如图像或声音数据

两种特殊类型，set和enum。set类型用来指定列中的值必须来自一个特定集合中的指定值。列值可以包含来自该集合的多个值。
在指定的集合中，最大可以有64个元素

enum就是枚举，与set类型相似，但是该类型的列可以只有一个指定集合中的值或者null，在枚举中最大还可以有65535个元素

常规字符串类型

类型取值范围描述[National]char(M) [Binary/Ascii/Unicode]0~255个字符固定长度为M的字符串，其中M的取值范围0～255.National关键字指定了应使用的默认字符集。Binary关键字指定了数据是否区分大小写。Ascii关键字指定了在该列使用lation1字符集。Unicode指定了使用Ucs字符集char0~255个字符char(1)的同义词[National]varchar(M) [Binary]1～255个字符出了可变长度，其他与上一项相同

text和blob类型

类型最大长度（字符数）描述tinyblob255小二进制大对象(blob)字段tinytext255小text字段blob65535常规大小blob字段text65535常规大小text字段mediumblob2^24-1(16777215)中型大小blob字段mediumtext2^24-1(16777215)中型大小text字段longblob2^32-1(4294967295)长blob字段longtext2^32-1(4294967295)长text字段

set和enum类型

类型集合最大值描述enum('value1','value2',... )65535该类型的列只可以容纳所列值之一或者nullset('value','value',...)64该类型的列可以容纳一组值或者null