MySQL语句
SQL功能 | 谓词 |
---|---|
数据定义(DDL) | CREATE DROP ALTER |
数据查询(DQL) | SELECT |
数据操纵(DML) | INSERT UPDATE DELETE |
数据控制(DCL) | GRANT REVOKE DENY |
—CREATE
语法描述
create table 表名(
字段名 字段类型 [constraint|not null|unique|primary key] [check] [default 默认值] [auto_increment][,...n]
[primary key(字段名[,...n])],
[foreign key(字段名) references 外表名(外表字段)]
[comment '注释说明']
)[auto_increment=1] [ENGINE=InnoDB] [DEFAULT CHARSET=utf8|gb2312|latin1] [;]
语句说明
子语句 说明
-------------------------------
create table 创建表
constraint 设置外键
not null 设置非空键
unique 设置唯一键
primary key 设置主键
check 字段值检查
default 为字段设置默认值
auto_increment 为字段设置自增长起始值
ENGINE 为数据表设置引擎
DEFAULT CHARSET 设置编码方式
相关操作
—DROP
语法描述
drop database|table 数据库名[,...n]|表名[,...n] [;]
语句说明
子语句 说明
-------------------------------
drop database 删除一个或多个数据库
drop table 删除一张或多张表
相关操作
删除一个数据库 drop database db1;
删除多个数据库 drop database db2,db3;
删除一张表 drop table tb1;
删除多张表 drop table tb2,tb3;
—ALTER
语法描述
alter table 表名 add|change|drop|modify|rename| ... [;]
语句说明
子语句 说明
-------------------------------
add 添加字段
change 修改字段
drop 删除字段
modify 修改字段类型
rename 修改表名
相关操作
表结构的修改
alter table 表名 add|change|drop|modify|rename| ... [;]
操作 语句
------------------------------------
添加字段 alter table 表名 add 字段名 字段类型 添加的位置(first/after 字段名);
修改字段 alter table 表名 change 原字段名 新字段名 新字段类型;
删除字段 alter table 表名 drop 字段名;
修改字段类型 alter table 表名 modify 字段名 新字段类型;
修改表名 alter table 表名 rename 新表名;
表字段属性的修改
alter table 表名 modify 字段名 字段类型 not null|unique|primary key|default '值'|increment=值| [;]
操作 语句
------------------------------------
非空约束-添加 alter table 表名 modify 字段名 字段类型 not null;
非空约束-删除 alter table 表名 modify 字段名 字段类型;
唯一约束-添加 alter table 表名 modify 字段名 字段类型 unique;
唯一约束-删除 alter table 表名 modify 字段名 字段类型;
主键约束-添加 alter table 表名 modify 字段名 字段类型 primary key;
主键约束-删除 alter table 表名 modify 字段名 字段类型;
外键约束-添加 alter table 表名 add [constraint 外键名] foreign key(字段名) references 表名(字段名);
外键约束-删除 alter table 表名 drop foreign key 外键ID;
默认值-添加 alter table 表名 modify 字段名 字段类型 default 默认值;
默认值-删除 alter table 表名 modify 字段名 字段类型;
自增长-添加 alter table 表名 modify 字段名 int auto_increment;
自增长-删除 alter table 表名 modify 字段名 字段类型; # 此操作需要清空表数据
自增长-设置 alter table 表名 auto_increment = 起始值;
—SELECT
语法描述
select [distinct] 字段名[,...n]|* [as 字段别名]
from 表名 [join 表名 on 连接条件]
[where 行选择条件]
[group by 分组依据列 [asc|desc]]
[having 组选择条件]
[order by 字段名[,...n] [asc|desc]]
[limit 查询行数 | 偏移行数 查询行数 | 查询行数 offset 偏移行数] [;]
# distinct表示去除重复字段或字段组
# *代表表中的所有字段
# asc表示升序排列 desc表示降序排列
语句说明
子语句 说明
-------------------------------
select 指定输出的字段(列)
from 指定数据的来源
where 指定数据的行选择条件
group by 对检索到的记录进行分组
having 对分组后结果的选择条件
order by 对查询的结果进行排序
相关操作
从指定表中输出指定列
SELECT [DISTINCT] 字段名[,...n]|* [AS 字段别名]
FROM 表名 [JOIN 表名 ON 连接条件]
操作 语句
------------------------------------
查询一列 select col_name from tb_name;
查询多列 select col_name1,col_name2 from tb_name;
查询所有列 select * from tb_name;
查询结果不重复 select distinct col_name from tb_name;
条件查询
WHERE 行选择条件
操作 语句
------------------------------------
指定条件查询 select col_name from tb_name where condition_col1='1';
通过and条件查询 select col_name from tb_name where condition_col1='1' and condition_col2='A';
通过or条件查询 select col_name from tb_name where condition_col1='1' or condition_col2='A';
通过not条件查询 select col_name from tb_name where not condition_col1='1';
通过between and条件查询select col_name from tb_name where condition_col1 between 1 and 100;# condition_col between A and B -> the range of condition_col is A<=condition_col<=B
通过in条件查询 select col_name from tb_name where condition_col1 in(1,100);# condition_col in(A,B,...) -> condition_col could be A or B or...
对行数据进行分组
GROUP BY 字段名 [ASC|DESC]
设置分组条件
HAVING 组选择条件
依据字段排序 限制输出行
ORDER BY 字段名 [ASC|DESC] [LIMIT 查询行数 | 偏移行数 查询行数 | 查询行数 OFFSET 偏移行数]
操作 语句
------------------------------------
查询表的n条数据 select * from tb_name order by id asc limit n;
查询表的最后n条数据 select * from tb_name order by id desc limit n;
—INSERT
—UPDATE
—DELETE
—GRANT
语法描述
grant 权限类型 on 数据库名.表名 from '用户名'@'主机名';
语句说明
子语句 说明
-------------------------------
grant 给用户授权
权限类型 包括insert,delete,update,select,drop,create,alter,all [privileges],index...
数据库名.表名 *.*代表所有数据库上的所有表 db.*代表某个数据库上的全部表
'用户名'@'主机名' 此处不区分""和''
相关操作
操作 语句
------------------------------------------
查看用户权限 show grants from '用户名'@'主机名';
授权用户在所有数据库上的所有权限 grant all [privileges] on *.* from '用户名'@'主机名';
授权用户在一个数据库的所有的权限 grant all [privileges] on db1.* from '用户名'@'主机名';
授权用户在一张表上所有的权限 grant all [privileges] on db1.tb1 from '用户名'@'主机名';
授权用户在一张表上的查询权限 grant select on db1.tb1 from '用户名'@'主机名';
授权权限操作后使其立即生效 flush privileges;
—REVOKE
语法描述
revoke 权限类型 on 数据库名.表名 from '用户名'@'主机名';
语句说明
子语句 说明
-------------------------------
revoke 回收用户权限
权限类型 包括insert,delete,update,select,drop,create,alter,all [privileges],index等(更多权限说明请查看用户权限部分)
数据库名.表名 *.*代表所有数据库上的所有表 db.*代表某个数据库上的全部表 db.tb 代表某个数据库上的某张表
'用户名'@'主机名' 此处不区分""和''
相关操作
操作 语句
------------------------------------------
回收用户在所有数据库的所有的权限 revoke all [privileges] on *.* from '用户名'@'主机名';
回收用户在一个数据库的所有的权限 revoke all [privileges] on db1.* from '用户名'@'主机名';
回收用户在一张表上所有的权限 revoke all [privileges] on db1.tb1 from '用户名'@'主机名';
回收用户在一张表上的查询权限 grant select on db1.tb1 from '用户名'@'主机名';
回收权限操作后使其立即生效 flush privileges;
—DENY
—FLUSH
语法描述
https://www.cnblogs.com/ggjucheng/archive/2012/11/07/2758021.html
flush hosts|logs|privileges|status|tables|query cache|user_resources|tables with read lock
语句说明
相关操作
flush hosts;
flush logs;
flush privileges;
flush status;
flush tables;
flush query cache;
选项说明
注意:使用flush命令需要reload权限
选项 含义
-------------------------------
•HOSTS
用于清空主机缓存表。如果有的主机更改了IP号或如果您得到了错误信息Host host_name
is blocked,则您应该刷新主机表。当在连接到MySQL服务器时,如果对于一个给定的主
机,接连出现错误“多于max_connect_errors”,此时,MySQL会假定出现了错误,并阻止
主机后续的连接申请。刷新主机表允许主机尝试再次连接。请参见A.2.5节,“主机的
host_name被屏蔽”。您可以使用max_connect_errors=999999999启动mysqld,以避免
此错误信息。
•DES_KEY_FILE
用于在服务器启动时,从采用--des-key-file选项指定的文件重新载入DES关键字。
•LOGS
用于关闭并重新打开所有的日志文件。如果您已经指定了一个更新日志文件或一个二进制日志
文件,同时没有扩展,则相对于前一个文件,日志文件的扩展号增加1。如果您在文件名称中使
用了一个扩展,则MySQL会关闭并重新打开日志文件。在Unix中,当相mysqld服务器发送一
个SIGHUP信号时,也会如此(例外情况是部分Mac OS X 10.3版本。在这些版本中,
mysqld忽略SIGHUP和SIGQUIT)。
如果服务器使用--log-error选项,则FLUSH LOGS会导致错误日志被重命名(使用后缀-
old),同时mysqld会创建一个新的空日志文件。如果没有给定--log-error选项,则不会进行重命名。
•PRIVILEGES
用于从mysql数据库中的授权表重新载入权限。
•QUERY CACHE
对查询缓存进行整理碎片,以更好得利用存储器。与RESET QUERY CACHE不同,本语句不会
从缓存中取消任何查询。
•STATUS
用于把多数状态变量重新设置为零。只在当调试查询时,您才应该使用此项。请参见1.7.1.3
节,“如何通报缺陷和问题”。
{TABLE | TABLES} [tbl_name [, tbl_name] ...]
当没有表被命名时,关闭所有打开的表,并迫使所有正在使用的表关闭。这也会刷新查询缓
存。此项含有一个或多个表名称,只刷新给定的表。和RESET QUERY CACHE语句一样,
FLUSH TABLES还会取消来自查询缓存的所有查询结果。
•TABLES WITH READ LOCK
对于所有带读取锁定的数据库,关闭所有打开的表,并锁定所有的表,直到您执行UNLOCK
TABLES为止。如果您拥有一个可以及时进行快照的文件系统,比如Veritas,则这是进行备
份的非常方便的方法。
•USER_RESOURCES
用于把所有每小时用户资源重新设置为零。这可以使已经达到了每小时连接、查询或更新限值
的客户端立刻重新恢复活性。FLUSH USER_RESOURCES不适用于同时连接的最大限值。请参
见13.5.1.3节,“GRANT和REVOKE语法”。
FLUSH语句被写入二进制日志,除非使用了自选的NO_WRITE_TO_BINLOG关键字(或其别名
LOCAL)。 注释:在任何情况下,FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE和
FLUSH TABLES WITH READ LOCK都不会被记入日志,因为如果它们被复制到一个从属服务
器上,会导致出现问题。
您也可以使用flush-hosts, flush-logs, flush-privileges, flush-status或
flush-tables命令访问含有mysqladmin应用程序的语句。
MySQL函数
MySQL编程
MySQL其他
—其他命令
库级操作
创建数据库 create database 数据库名;
查看所有数据库 show databases;
删除数据库 drop database 数据库名;
查看连接数据库 select database();
切换数据库 use 数据库名;
修改数据库名 rename database old_dbname to new_dbname;
表级操作
数据表的移动 rename table db1.a to db2.b;
修改数据表名 rename table old_tbname to new_tbname;
数据表的复制(复制结构)
create table new_tb like copy_tb;
数据表的复制(复制数据)
create table new_tb select * from copied_tb;
数据表的复制(复制结构+数据)
create table new_tb like copy_tb;
create table new_tb select * from copied_tb;
修改用户名和主机 rename user '用户名'@'主机' to '新用户名'@'新主机名';
—管理用户
创建用户 create user '用户名'@'主机名' identified by '设置登录密码';
删除用户 delete user mysql.user where user='用户名' and host='主机名';
使用grant创建用户 grant privileges on db.table to 'user'@'host' [identified by '密码'];
创建用户时设置明文密码 create user '用户名'@'主机名' identified by '设置登录密码';
创建用户时设置哈希密码 create user '用户名'@'主机名' identified by password '设置登录密码';
—用户权限
MySQL权限详解(1)
All/All Privileges权限代表全局或者全数据库对象级别的所有权限
Alter权限代表允许修改表结构的权限,但必须要求有create和insert权 限配合。如果是rename表名,则要求有alter和drop原表,create和 insert新表的权限
Alter routine权限代表允许修改或者删除存储过程、函数的权限
Create权限代表允许创建新的数据库和表的权限
Createroutine权限代表允许创建存储过程、函数的权限
Createtablespace权限代表允许创建、修改、删除表空间和日志组的权 限
Create temporary tables权限代表允许创建临时表的权限
Createuser权限代表允许创建、修改、删除、重命名user的权限
Createview权限代表允许创建视图的权限
MySQL权限详解(2)
• Delete权限代表允许删除行数据的权限
• Drop权限代表允许删除数据库、表、视图的权限,包括truncatetable命令
• Event权限代表允许查询,创建,修改,删除MySQL事件
• Execute权限代表允许执行存储过程和函数的权限
• File权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作,可使用 的命令包括load data infile,select ... into outfile,load file()函数
• Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权 限
• Index权限代表是否允许创建和删除索引
• Insert权限代表是否允许在表里插入数据,同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限
• Lock权限代表允许对拥有select权限的表进行锁定,以防止其他链接对此表 的读或写
MySQL权限详解(3)
• Process权限代表允许查看MySQL中的进程信息,比如执行showprocesslist,
• Reference权限是在5.7.6版本之后引入,代表是否允许创建外键
• Reload权限代表允许执行flush命令,指明重新加载权限表到系统内存中, refresh命令代表关闭和重新开启日志文件并刷新所有的表
• Replication client权限代表允许执行show master status,show slave status,show binary logs命令
• Replication slave权限代表允许slave主机通过此用户连接master以便建立主从 复制关系
• Select权限代表允许从表中查看数据,某些不查询表数据的select执行则不需 要此权限,如Select 1+1,Select PI()+2;而且select权限在执行update/delete 语句中含有where条件的情况下也是需要的
• Showdatabases权限代表通过执行showdatabases命令查看所有的数据库名
• Show view权限代表通过执行show create view命令查看视图创建的语句mysqladmin processlist, show engine等命令
MySQL权限详解(4)
• Shutdown权限代表允许关闭数据库实例,执行语句包括mysqladmin shutdown
• Super权限代表允许执行一系列数据库管理命令,包括kill强制关闭某个连接 命令,change master to创建复制关系命令,以及create/alter/drop server等命 令
• Trigger权限代表允许创建,删除,执行,显示触发器的权限
• Update权限代表允许修改表中的数据的权限
• Usage权限是创建一个用户之后的默认权限,其本身代表连接登录权限
系统权限表
• 权限存储在mysql库的user,db, tables_priv, columns_priv, and procs_priv这几个系统表中,待MySQL实例启动后就加载到内存中
• User表:存放用户账户信息以及全局级别(所有数据库)权限,决定了 来自哪些主机的哪些用户可以访问数据库实例,如果有全局权限则意味
着对所有数据库都有此权限
• Db表:存放数据库级别的权限,决定了来自哪些主机的哪些用户可以访 问此数据库
• Tables_priv表:存放表级别的权限,决定了来自哪些主机的哪些用户可以 访问数据库的这个表
• Columns_priv表:存放列级别的权限,决定了来自哪些主机的哪些用户可 以访问数据库表的这个字段
• Procs_priv表:存放存储过程和函数级别的权限
• User和db权限表结构
• User权限表结构中的特殊字段
• Plugin,password,authentication_string三个字段存放用户认证信息
• Password_expired设置成’Y’则表明允许DBA将此用户的密码设置成过期而 且过期后要求用户的使用者重置密码(alter user/set password重置密码)
• Password_last_changed作为一个时间戳字段代表密码上次修改时间,执 行create user/alter user/set password/grant等命令创建用户或修改用户密 码时此数值自动更新
• Password_lifetime代表从password_last_changed时间开始此密码过期的天 数
• Account_locked代表此用户被锁住,无法使用
• Tables_priv和columns_priv权限表结构
• Timestamp和grantor两个字段暂时没用
• Tables_priv和columns_priv权限值
• procs_priv权限表结构
• Routine_type是枚举类型,代表是存储过程还是函数
• Timestamp和grantor两个字段暂时没用
• 系统权限表字段长度限制表
• 权限认证中的大小写敏感问题
• 字段user,password,authencation_string,db,table_name大小写敏感
• 字段host,column_name,routine_name大小写不敏感
• User用户大小写敏感