MySql学习总结

create database 数据库名 创建数据库

drop database if exists 数据库名 如果数据库已经存在删除数据库

use 数据库名 选择数据库

create table userinfo(
id mediumint(8) not null auto_increment,
token varchar(60) not null,
wecha_id varchar(60) not null,
truename varchar(60) not null,
sex tinyint(1) not null,
reg_time int(11) not null,
primary key(id)
) --------------------------------------创建表

字段类型：

字段约束：

表级约束：

表类型：

describe 查询表结构

小贴士：
①在一个设计好的数据库中，对于每条记录来说，主键都是不变的、唯一的标识符。这里字段id被定义为主键，该字段中不允许有重复的值或者null值，并且MySQL会自动为该表的主键（即id字段）建立索引。
②如果没有指定表的类型，MySQL就默认创建表userinfo的类型为myisam，并且在/var/lib/mysql/experts目录下创建userinfo.frm（表定义文件）。

show create table userinfo 查看表类型

create table copyone like userinfo； 复制表结构
create table copytwo select * from userinfo；复制表结构及内容

drop table copyone； 删除表

alter table copytwo rename userinfotwo；修改表名

alter table userinfotwo add age int(3); 增加字段

alter table userinfo change sex user_sex char(2) not null; 修改表字段和类型

alter table userinfo modify user_sex int(1) not null; 只修改表类型，不改名

alter table userinfo drop user_sex; 删除表中的字段

delete from userinfotwo; 删除表中所有记录
truncate from userinfotwo;删除表记录
truncate命令不管表中有多少条记录，它都是删除表，然后重建该表，而delete命令是将表中所有记录一个一个删除。相比之下，truncate命令要比delete命令快得多，特别是记录非常多时尤为明显。

create index uindex on userinfo(name); 向已存在的表添加索引

对于类型为char和varchar的字段，建立索引时还可以指定索引长度值（对于类型为blob和text的字段，索引长度值是必须指定的）。

char 和 varchar 类型的区别如下：
【char的长度是不可变的，而varchar的长度是可变的，

也就是说，定义一个char[10]和varchar[10],
如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，而varchar就立马把长度变为4了，
取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的，
尽管如此，char的存取数度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找；
但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。
再者，char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；
而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。】

【例】要为表student的sname字段创建名为sna的索引，并指定索引长度值为10，可使用下面的命令。
create index sna on student (sname(10));
这里指定索引长度值为10，是基于大多数名字通常在前10个字符是不一样的考虑，这样创建的索引文件会更小一些，既可以节省磁盘空间，又可以加速insert等操作。

注意：索引可以加快数据查询的速度，但是它会占据一定的磁盘空间，而且它也会影响insert、update和delete命令的执行时间，因为每当表中的记录被添加、修改或删除时，索引都必须更新

drop index tindex on userinfo; 删除索引

mysql> show index from userinfo;
mysql> show keys from userinfo; 查看索引

在首次安装MySQL时，MySQL会自动授予任何用户都可以从本地连接MySQL服务器，但是只有MySQL管理员root（初始化密码为空）可以完全访问系统中的所有数据库（默认仅有mysql和test这两个数据库），而任何其他用户仅能访问test数据库。

为什么会是这样呢？原来当首次安装MySQL时，MySQL安装程序会在数据库mysql中设置5个MySQL授权表（如下表所示），由这5个授权表共同决定哪个用户可以连接服务器、从哪里连接以及连接后可以执行哪些操作。初始化时，表host、tables_priv和columnts_priv是空的，表user和db就决定了MySQL默认的访问规则。下面以MySQL管理员的身份查看一下表user和db的内容。

describe user; 查看user表结构；

可通过查询语句 select 字段名 from user ;查询你想了解的内容
比如：select host,user,password,select_priv from user；

在上图中，第1条记录表明，MySQL授予用户root可以从本地（localhost）连接到数据库服务器，并且对服务器中的所有数据库都拥有完全控制的权限（从表user的第4个字段起的所有关于权限的字段值都是“Y”）；第5条记录表明，任何其他用户（对应表user中的字段user值为空，相当于匿名用户）也可以从本地（localhost）连接到数据库，但是对系统中所有数据库都没有访问权限（从表user的第4个字段起的所有关于权限的字段值都是“N”）。

describe db;查看db表结构

从上图可见，表db定义了任何用户都可以从任何主机访问数据库test（或以test开头的），并且对该数据库拥有完全的访问权限（从表db的第4个字段起的所有关于权限的字段值都是“Y”）。这里的字符“%”被用作通配符，字符“_”被用作转义符。
虽然在表db中定义了允许任何用户可以从任何主机访问数据库test，但由于在表user中限制任何用户只能从本地（localhost）来连接数据库服务器，因此在这两个表的共同作用下，MySQL默认设置是任何用户只能从本地完全访问数据库test。

创建新用户步骤：
1、以MySQL管理员身份从本地连接到数据库服务器，可使用以下的命令实现。
mysql -u root -p
2、创建新用户guest，并为他设置密码，同时允许他从任何主机连接到数据库服务器，可使用下面的命令实现。
insert into mysql.user(host,user,password) values('%','guest',password('111111'));
此处必须使用password()函数，该函数会为密码加密，这样在表user的字段password中保存的就是经过加密的密码。
查询user表：

3、重载MySQL授权表，可使用下面的命令实现。
flush privilege；

检查下新建用户是否可用：

这里，用选项“-h”来指定所连接的数据库服务器的IP地址或域名
从上图可见，用户guest在输入正确的密码（guest）后，就可以从远程客户端连接到数据库服务器。连接成功后，用命令show databases查看当前用户可用的数据库，只能看到数据库尝试访问它成功。

小贴士：
如果在Linux系统中开启了防火墙，要让远程客户端连接到Linux系统中的数据库服务器，还需关闭防火墙功能或设置允许TCP端口3306通过。例如，可用使用下面的命令开放TCP端口3306。
iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

delete from mysql.user where user = 'guest'; 删除用户

注意，删除后要用flush privileges命令重载MySQL授权表。

update mysql.user set password = password('123456') where user = 'guest';修改用户密码

set password for guest@'%'=password('111111'); 另外一种简约更改密码命令

这里，guest@'%'的基本格式为“用户名@客户端的域名”。字符%是通配符，使用通配符时可用单引号将它括起来，例如，tom@'%.gdvcp.net'。当使用set password语句来更改用户的密码时，不需要执行flush privileges语句来重载MySQL授权表。

用户权限的设置
从前面可知，MySQL授权表是用来控制用户连接数据库服务器和访问数据库的权限，那么授权表中究竟有哪些用来设置权限的字段呢？实际上，MySQL授权表中权限字段有以下两种形式。
（1）在表user、db和host中，所有权限字段都被声明为ENUM('N','Y')，即每一个权限字段值都可以被设置为'N'或'Y'，并且缺省值为'N'。下表列出了表user、db和host中可设置权限的字段。

注：表中带“”号的字段是授权表user、db和hsot共有的字段，而不带“”号的字段仅是授权表user中的字段。
（2）在表tables_priv和columns_priv中，权限字段被声明为set类型，即可以从所定义的权限集合中选择任意个权限。下表列出了表tables_priv和columns_priv中权限字段及可设置的权限。

MySQL提供了两种修改授权表中的访问权限的方法，一种是使用insert、update和delete等DML语句来手工修改表中的信息；另一种是使用grant和revoke语句。前者比较直观，但由于各授权表中字段数很多，很容易出错，通常不推荐这么做。

grant语句的基本格式如下：
grant 权限列表 [(字段列表)] on 数据库名称.表名称
to 用户名@域名或IP地址
[indentified by '密码值'] [with grant option];

（1）授予哪个用户能连接，从哪里连接
例如，要授予用户guest可以从任意主机连接到数据库服务器，并具有完全访问数据库experts的权限，则可使用下面的命令。
grant all on experts.* to guest@'%' identified by '111111' with grant option;

这里需要说明的有以下几点。
■使用grant语句授权时，如果指定用户名不存在，MySQL就会创建这个新用户，显然用这种方法创建新用户比直接修改授权表user更好。
■在grant语句中的“权限列表”处使用关键字all，表示授予全部的权限，此外，还可以使用关键字usage，表示不授予任何权限。
■在grant语句中的“数据库名称.表名称”处可以使用通配符“”，本例中的“xsxk.”表示数据库xsxk中的所有表。
■grant语句中的“用户名@域名或IP地址”用来设置谁能连接，从哪里连接；用户名不能使用通配符，但可以用''（空字符串）表示任何用户（相当于匿名用户）；域名或IP地址可以使用通配符“%”，在使用通配符时必须使用单引号将域名或IP地址括起来。下表列出了设置“域名或IP地址”的几种常见情况。

■在grant语句中使用identified by子句，可设置用户连接数据库服务器时使用的密码，这里不需要使用函数password()，而直接使用明文，grant语句会自动将设置的明文密码加密并保存到授权表user中。此外，对于新用户，如果不指定identified by子句，那么MySQL将不为该用户设置密码（不安全）；对已有的用户，任何设置的密码将代替旧密码，如果不指定密码，则旧密码仍保持不变。

2）授予用户不同级别的访问权限
例如，要新建一个用户guest，让他能从子网192.168.16.0中任何主机连接到数据库服务器，可以读取数据库experts的内容，并且能修改表userinfo中字段truename的值，可使用下面的命令。
grant select on experts.* to tthuc@'192.168.16.%' identified by 'tthuc_1202' with grant option;

接下来浏览MySQL的各个授权表，看上面两条命令究竟做了些什么。
①使用下面的命令来查看mysql.user表中与用户guest有关的记录。
select host,user,password,select_prive from mysql.user where user='guest';
命令的执行情况如下图所示。

从上图可见，MySQL创建了用户guest并设置了密码（经过加密），该用户可以从子网192.168.16.0/24中的任何主机连接到数据库服务器，但是由于授权表user中与用户guest对应的用来设置全局权限的各个字段值（如select_priv）都为'N',因此MySQL并没有授予用户tom任何全局权限，该用户能否访问某个数据库还要看其他授权表中的设置。

②使用下面的命令来查看mysql.db表中与用户guest有关的记录。
select host,db,user,select_priv,insert_priv from mysql.db where user='guest';
命令的执行情况如下图所示。

从上图可见，由于授权表db中与用户guest对应的用来设置数据库级权限的各权限字段值中只有select_priv字段值为'Y'，而其他权限字段值（如insert_priv字段值）都为'N'，所以MySQL授予用户guest只能浏览数据库experts的内容。

③使用下面的命令来查看mysql.host表中的内容。
select * from mysql.host;
命令的执行情况如下图所示。

从上图可见，表mysql.host是空表。实际上，grant语句不会改变mysql.host文件的内容。

④使用下面的命令来查看mysql.tables_priv表的内容。

select db,user,table_name,table_priv,column_priv from mysql.tables_priv;

从上图可见，由于授权表tables_priv中与用户guest对应的用于设置表级权限的table_priv字段值为空，因此MySQL并没有给用户guest设置表级权限。但是因为对应的column_priv字段值为Update，所以将由mysql.columns_priv表来确定字段级权限。

⑤使用下面的命令来查看mysql.columns_priv表的内容。
select db,user,table_name,column_name,column_priv from mysql.columns_priv;

从上图可见，由于授权表columns_priv中与用户tom对应的用于指定字段级权限的column_priv字段值为Update，因此MySQL授予用户guest对表userinfo的truename字段有Update（修改）权限。

下面再对授予用户不同级别的访问权限做以下几点说明。
■grant语句中的“数据库名称.表名称”是用来设置权限运用的级别，权限可以是全局的（适用于所有数据库和所有表）、指定数据库的（适用于一个数据库中的所有表）或指定表的，如下表所示。

■grant语句中的“字段列表”是用来设置权限运用于指定表中的哪些字段，例如：
update(teacher)表示update权限运用于指定表（course）中的teacher字段；若要运用于指定表的多个字段，则可用“,”号分隔各个字段，如update(cname,teacher)。

■必须注意grant语句中的“权限列表”可指定的权限与权限运用级别有关，例如有些权限（file、process、reload和shutdown）作为管理权限只能用于全局级别，而对于字段级只能指定select、insert、update和references等4个权限。

注记：
MySQL是如何控制客户机访问的？
在使用MySQL时，客户机访问控制分以下两个阶段：
（1）第一阶段发生在连接数据库服务器，MySQL查找user表看看是否能够找到与用户的名字、正在连接的客户机以及所提供的密码相匹配的项。若不匹配，则不能连接；若匹配，则建立连接。
（2）第二阶段发生在建立连接之后，在此阶段中，对于该用户发布的每个命令，MySQL都会检查各个授权表以查看该用户是否具有充足的权限来执行该命令，该用户是否有权可由下面的逻辑表达式决定：
user OR (db AND host) OR tables_priv OR columns_priv
逻辑表达式中的各个授权表实际上是指各表中与该用户相关的记录；第二阶段继续，直到与该数据库服务器的会话结束为止。

（3）授予用户管理权限的权利
例如，MySQL管理员要授予用户admin可以从本地连接到数据库服务器，对数据库experts具有完全访问权限，并可将其所拥有的权限授予其他用户，可使用下面的命令。
grant all on experts.* to admin@localhost identified by 'sW56$Azx' with grant option;
注意，grant语句中的with grant option子句用来设置允许用户将自己所拥有的权限授予其他用户。

现在用户admin@localhost对数据库experts已经具有完全访问的权限，又可以将其所拥有的权限授予其他用户，那么当用户admin从本地连接到数据库服务器后，就可以使用下面的命令给其他用户（如bill）授权。
grant select on experts.userinfo to bill@localhost

值得注意的是，本例中当用户admin用grant语句为bill授权时，不能用identified by子句为用户bill设置密码，这是因为用户admin仅对数据库xsxk具有完全访问的权限。此外，授权时，只能用bill@localhost，而不能用bill@'%',这同样是因为用户admin只能把自己拥有的权限授予其他用户。授权后，用户bill可以从本地连接到数据库服务器（没有密码），然后只能浏览表xsxk.student的内容，而不能做其他操作。

每当为用户授权后，MySQL管理员都可以使用show grants语句来检查授予该用户的权限是否正确。例如，可使用下面的命令来检查用户admin@localhost的权限。
show grants for admin@localhost;

撤销权限用revoke
语句格式如下：
revoke 权限列表 [(字段列表)] on 数据库名称.表名称 from 用户名@域名或IP地址

值得注意的是，revoke语句中的“用户名@域名或IP地址”部分必须匹配原来grant语句中的“用户名@域名或IP地址”部分，而“权限列表”部分可以是原来grant语句所授权限的一部分权限。此外，revoke语句只能撤销权限，而不能删除用户账户，在授权表user中仍保留该用户的记录，这意味着该用户仍然可以连接到数据库服务器。所以，要完全删除该用户，还应该使用delete语句从user表中删除该用户记录。